Forums

How upvoted is PostgreSQL?

In retrospect that won't tell me anything unless you provide a fair amount of context. I suppose I need a new question...

I know PostgreSQL has been mentioned as a product that likely has a future on PA...could you give an indication how far down the road before we see my beloved PG?

Also, in case I somehow forgot to request an upvote on my behalf...I definitely would make that my personal strongest upvote if it has not yet been featureRequest.upvote(pgsql, a2j, strong=True)

+1 on this :)

I've added two more +1's :-)

It's really high on the list right now; we're still finishing off some infrastructural improvements so that we can handle multiple domains per user and native static file support (this has taken a while because it's involved switching from Apache to nginx, but it's now running through our testing environment so should be finished soon). But once that's done I think we can be pretty certain to move on to PostgreSQL.

Dancing joyously!!

That is great news giles!! Thank-you for being the messenger (+1 giles). Dare I ask what version you think you'll be looking at? I think 9.2 is pretty close to release...☺ I'd guess the timing won't quite work though, since it is still in beta. So probably 9.1?

Also will the Python extension be included? If yes...what version? Will we be able to choose between 2 and 3 (Python) or should we start to vote for our preference now?

What other extensions if any are you looking at? I've not used many, but I know some are quite popular such as PostGIS.

I am so excited about this. Nothing against MySQL, but I so prefer PG for...well...everything...:-)

Trying to stop dancing now...but it is such good news!!

Glad to be the bearer of good news :-)

Normally we use the version supported by our underlying Debian infrastructure; that would be 8.4.12, I think. But if there's a good reason to use a higher version, then it looks like 9.1 won't be that hard -- what do you think?

And we'll install whatever we can in terms of Python support and extensions; links to PyPI gratefully accepted :-)

This page shows there have been 15 releases between 8.4.12 and the upcoming 9.2, so 14 if we only count to 9.1.4. Of course only 3 of them are major releases, the rest are minor, so if we leave out the 12 minor updates and just highlight the big guns from each of the major updates we get:

9.0: This release of PostgreSQL adds features that have been requested for years, such as easy-to-use replication, a mass permission-changing facility, and anonymous code blocks. While past major releases have been conservative in their scope, this release shows a bold new desire to provide facilities that new and existing users of PostgreSQL will embrace. This has all been done with few incompatibilities. Major enhancements include:

• Built-in replication based on log shipping. This advance consists of two features: Streaming Replication, allowing continuous archive (WAL) files to be streamed over a network connection to a standby server, and Hot Standby, allowing continuous archive standby servers to execute read-only queries. The net effect is to support a single master with multiple read-only slave servers.
• Easier database object permissions management. GRANT/REVOKE IN SCHEMA supports mass permissions changes on existing objects, while ALTER DEFAULT PRIVILEGES allows control of privileges for objects created in the future. Large objects (BLOBs) now support permissions management as well.
• Broadly enhanced stored procedure support. The DO statement supports ad-hoc or "anonymous" code blocks. Functions can now be called using named parameters. PL/pgSQL is now installed by default, and PL/Perl and PL/Python have been enhanced in several ways, including support for Python3.
• Full support for 64-bit Windows.
• More advanced reporting queries, including additional windowing options (PRECEDING and FOLLOWING) and the ability to control the order in which values are fed to aggregate functions.
• New trigger features, including SQL-standard-compliant per-column triggers and conditional trigger execution.
• Deferrable unique constraints. Mass updates to unique keys are now possible without trickery.
• Exclusion constraints. These provide a generalized version of unique constraints, allowing enforcement of complex conditions.
• New and enhanced security features, including RADIUS authentication, LDAP authentication improvements, and a new contrib module passwordcheck for testing password strength.
• New high-performance implementation of the LISTEN/NOTIFY feature. Pending events are now stored in a memory-based queue rather than a table. Also, a "payload" string can be sent with each event, rather than transmitting just an event name as before.
• New implementation of VACUUM FULL. This command now rewrites the entire table and indexes, rather than moving individual rows to compact space. It is substantially faster in most cases, and no longer results in index bloat.
• New contrib module pg_upgrade to support in-place upgrades from 8.3 or 8.4 to 9.0.
• Multiple performance enhancements for specific types of queries, including elimination of unnecessary joins. This helps optimize some automatically-generated queries, such as those produced by object-relational mappers (ORMs).
• EXPLAIN enhancements. The output is now available in JSON, XML, or YAML format, and includes buffer utilization and other data not previously available.
• hstore improvements, including new functions and greater data capacity.

9.1: This release shows PostgreSQL moving beyond the traditional relational-database feature set with new, ground-breaking functionality that is unique to PostgreSQL. The streaming replication feature introduced in release 9.0 is significantly enhanced by adding a synchronous-replication option, streaming backups, and monitoring improvements. Major enhancements include:

• Allow synchronous replication
• Add support for foreign tables
• Add per-column collation support
• Add extensions which simplify packaging of additions to PostgreSQL
• Add a true serializable isolation level
• Support unlogged tables using the UNLOGGED option in CREATE TABLE
• Allow data-modification commands (INSERT/UPDATE/DELETE) in WITH clauses
• Add nearest-neighbor (order-by-operator) searching to GiST indexes
• Add a SECURITY LABEL command and support for SELinux permissions control
• Update the PL/Python server-side language

9.2: Currently in Beta 3, so not yet complete. The release notes are in progress here if you would like some fun reading.

For me the change to 9 brought with it some big updates, but like any major release, the changes only matter if you use them in your project. Would I like to see 9.1 (or 9.2), personally yes, but if that makes for several hours of extra work for PA, then I can wait. Of course at the same time you gotta ask, how long will it be before the underlying Debian infrastructure will support 9.x if we use 8.x now? If that will be a year or more, then it could still be worth some extra effort to get 9.x installed. In short what I'm trying to say, is that I'll be thankful with what we get, so base it on the big picture for PA.

I would suggest making both Python 2 and 3 available inside PG since it is supported and since we have the option of both on PA to begin with.

Sounds like 9.x is a must-have! 9.1 is in the backports repository for Debian squeeze (the version we use) so I guess we'll go for that -- it shouldn't be too much extra effort, at least in percentage terms ;-)

I'll make a note that we should make sure (if possible) that both Python versions are supported too.

This page in the PostgreSQL docs gives an overview to the idea of installing both Py2 and Py3 in the same environment. Apparently the only restriction is not to attempt using them both during a single PG session, but if one were to try, then it would be detected and thus prevented.

Thanks! That's a useful link, I've added it to the ticket.

9.2 RC1 is now out. It shouldn't be long before the backports have 9.2 available...☺

9.2 released. You can find the release notes here.

Actually I should have provided this link instead. It will take you to the release announcement that has pertinent links to other items such as:

• Downloads, including packages and installers
• Release Notes
• Documentation
• What's New in 9.2
• Press Kit

+1 for PostGIS support, it's the most used open source spatial-enabled database extension.

(Just wanted to say something to show off my new yellow star!)

@geotux: Congrats on joining the in crowd...☺

Thanks, a2j -- PostgreSQL is definitely coming, I've been using it for a non-PA server I run (Wordpress, soon to be ported to PythonAnywhere and Mezzanine thanks to the excellent post from dscapuano ) just to make sure there's nothing too surprising about it, and everything looks great so far...

I await with bated breath...:)

One of the things I like best about PG is the ability to write sophisticated stored procedures. I know MySql has them as well, but they came in late to that game ( have never bothered with Stored Procedures on MySql anyway).

There are cases where security can be improved by using stored procedures for some types of logic. A good example is anything that may result in SQL injection attacks. This is a lot less likely for most Python frameworks, but can be a real killer with PHP.

I have never had a problem with this in Django, but perhaps it may be an issue in other frameworks?

Like to get some answers on this from all you Pythonmongers ;-) dc

Apropos stored procedures: I tried to create a trigger today on MySQL. MySQL replied with "ERROR 1419: You do not have the SUPER privilege..."

From my read of the docs, the MySQL version in use at PA (5.1.57) should not require this privilege. Starting in 5.1.6 a specific "TRIGGER" privilege was introduced. In fact, "SHOW PRIVILEGES;" will show it. But we don't have that privilege granted. Is this a conscious policy decision, or could it be changed? Or am I doing something wrong?

Oh, no! My PG thread has been infected with MySQL...j/k I hope you get the response you are seeking catweazle. Even more, I look forward to PG arriving @ my beloved PA.

@a2j: Please forgive me for tracking off-topic MySQL dirt onto your nice PG carpet ;-) I have never used PG before, but I will be sure to give it a try, once it arrives on PA.

I apologize, I think I started the Mysql thing. Mysql is great for some things and can be a very speedy db when used with Innodb. However, Postgresql goes to another level if one takes advantage of the power built into it.

This will be a great addition to PA.

dc

No apology needed!! We're all having fun here!!

Bump...

Hey, good to see you back a2j! Good timing, PostgreSQL is currently top of the list for things to do after we've finished our current set of tasks.

That is great news. It could only get better if you told me we'd be getting 9.2.x...:-)

Giles said here:

Nothing yet... the problem is mostly infrastructural; we get quite a lot of good stuff -- backups, etc -- for free from Amazon AWS by using their RDS service for MySQL... and so we need to build a lot of it ourselves for PostgreSQL. Might take a little while :-(
Staff giles | 455 posts | PythonAnywhere staff | Feb. 12, 2013, 2:09 p.m.

Out of curiosity...if PG is passing all integration testing besides back-up any thoughts on including it and making it clear that for the time being it is a BETA feature that has ZERO backup support? Then you could work on the required infrastructure to complete the requirements to take it out of beta.

This would work especially well if we could just make our own SQL dumps to our user filesystem.

That is a possibility. We'll have a bit of a discussion here at PA central and see where that takes us.

@glenn: That's all I can ask. And I appreciate it!

@All: In case you didn't notice, a PG update was released on the 7th. This is an update for all supported versions. The current versions are now:

PostgreSQL 9.2.3, 9.1.8, 9.0.12, 8.4.16 and 8.3.23

Since I consider this the canonical PG thread I figured I'd update it with the latest announcement:

PostgreSQL 9.2.4, 9.1.9, 9.0.13 and 8.4.17 released.

Wow!! Version 9.2.4. Thank you so very much PA Staff!!!

:-)

Shh, it's a secret!

;-)

Oh, I meant ParaGuay had an earth quake that was 9.2 (.4) on the Richter scale.

Ah, of course!

I've been banging around on PG hoping to break it for you guys so we can bring it out of beta...:-> Err...I mean fix it and then bring it out of beta...☺

To that interest here are some bullet points:

• Are we planning to add the Python Language to our PG implementation?
• This sample DB fails beautifully. Some of it we may want to just let fail, but copying in data files seems pretty reasonable to me...U?
• user$db databases created in addition to the default user$default seem to just get deleted after a few days. Maybe it's because I left them empty, but now I have some that have data to see what happens.

I'll try not to overwhelm you and leave it there for now...THANKS!!!!!

Hi a2j!

So, in order:

Are we planning to add the Python Language to our PG implementation?

Yup, that's the plan!

This sample DB fails beautifully. Some of it we may want to just let fail, but copying in data files seems pretty reasonable to me...U?

I'll add a link to that to the ticket so that (if you're happy with us doing so) we can try to put it in as part of the functional test for the new feature.

user$db databases created in addition to the default user$default seem to just get deleted after a few days. Maybe it's because I left them empty, but now I have some that have data to see what happens.

Right now all DBs are cleared down when we deploy an update to PythonAnywhere regardless of whether they have data -- doing that just made it easier to get something that people could at least look at out there (and was a good way of making sure that it was clearly in pre-alpha!). That will obviously change for the final version :-)

@giles: Sorry for my delay in responding. I had a busy couple of days...☺

1) Super.

2) Perfect.

3) Makes sense, now that i know, not a concern.

I'll work on getting you some more bullets (Don't worry I'll list them not load them)

~a2j