Increase minimum version requirement for Postgres to 9.1.2

Added a little table to the issue summary.

Adding PostgreSQL's EOL policy to the issue summary.

Given that for both PHP and MySQL "it's not supported upstream so we're not supporting it either" is an argument we've been giving a lot of credence to lately, the same should apply here. The PostgreSQL team dropped the 8.x series summer last year. We should do the same.

Also, I strongly suspect a far higher percentage of PostgreSQL users have root (and therefore can choose a version of their DB) than MySQL users, so they'd be less inconvenienced by a more aggressive version requirement. (Although "upstream has already abandoned it" is not particularly aggressive.)

Guessing some large enterprise setups still run on 8.4 (a client of mine does), maybe we could clarify in the issue summary what the benefits of requiring PostgreSQL 9.1 vs 8.4 would be?

+1 for 9.1.x.
Benefits:

1. Synchronous replication and hot standby
2. Changes in bytea output (maybe allows for some cleanups in pgdriver)
3. Overall improved performance
4. 64Bit Windows support (as of 9.0)
5. PostGIS 2.x support

@stefan.r wouldn't 'large enterprise clients' also stay on Drupal 7 for a while then?

Minimising the range of versions we support sounds good to me - we're not at a point where tests 100% pass yet so anything that improves that would be good.

@catch yes... and by the time they'd be ready to adopt D8 I'm sure they'll have upgraded from 8.4 anyway. And if PostgreSQL remains the second-rate citizen it is in D7 (due to poor adoption and poor d.org automated testing) they'd probably rather go for MySQL/MariaDB instead for any Drupal setups :)

In any case just as an anecdote, I checked what their upgrade plans are and they are talks of moving to RHEL 7.1 which has PostgreSQL 9.2 (which is in RHEL Software Collections for RHEL 6 ayway). So with Debian 7+ & Ubuntu 12+ LTS supporting PostgreSQL 9.1, bumping the version requirement shouldn't be a big problem as far as distro support is concerned.

@bzrudi71 just out of interest, do we have any concrete tests that we know would be passing on 9.1 that don't pass on 8.3 or is this just about citext? Because I was planning to work on a patch that would allow us to support case insensitivity without the citext requirement, so that citext support could be in a contrib module instead...

@stefan.r nope, none of the remaining failing tests are PostgreSQL version specific so D8 will even work fine with 8.3 (or lower). However, my vote still goes for 9.1.x because of the various reasons already mentioned here. Citext seems to cause to many problems for now and is not the reason for bumping to 9.1 - at least not for me ;)

@bzrudi71 thanks, could you clarify in the citext issue what kind of "problems" citext causes? All I see is a few test failures in https://www.drupal.org/node/2464481#comment-9787239 -- maybe that's because we hadn't patched Drupal yet to only use citext columns for case insensitive fields (and regular char columns for case sensitive fields)?

@stefan.r from what I remember:

1. I patched drupals PG connection __construct() to create the citext extension (if not exists) and the docker scripts to run as PG superuser
2. Changed all CHAR, VARCHAR, TEXT types to CITEXT in getFieldTypeMap().

So yes, the change was just globally to all fields/columns. As a side note, I noticed an increased bot run time by around 25 minutes or so, but that may be caused by the hack in __construct() trying to create citext if not exists every time.
I didn't investigate that further and focused on fixing the remaining PG test fails ;-)

So the citext argument for 9.1.2 comes from me because http://www.postgresql.org/docs/9.1/static/release-9-1-2.html describes a nuisance with citext columns upgrading from previous versions and so if we are to use citext we might start at 9.1.2 so our users won't hit this bump when they come out of the stone age :P

+1 for 9.1.x.

Can someone post a patch to bump the Postgres version to 9.1.2? Until there's code we're just chatting. :-) Should be a trivial patch.

Also tagging for a decision from the framework folks. And adding a quick'n'dirty checklist of who should be signing off here. If you're a Postgres or framework maintainer please update once you've decided where you stand. :-)

Upgrading the minimum version of PostgreSQL to 9.1.2

Pinging a Postgres maintainer.

There is a patch now. Removing "no patch" from title.

I cleaned up the issue summary a little as well, and added more tasks.

If we're going to do this, it would be ideal to do it in the next few days so it hits the next D8 beta. At 17 critical issues, it's possible (though certainly not a sure thing) that the next beta could be our last (!) beta.

We will have no testing for 8.x as the new drupalCI infrastructure supports 9.1 and 9.4 only, so let's do it! BTW We still see random exceptions caused by AlreadyInstalledExceptions on the PG bots, helping hands needed #2524426: [meta] DrupalCI pgsql Random exceptions, fails, testbot issues!

there's a patch and agreement on it, what stops to pull RTBC?

Nothing.

FYI: #2552769: Remove outdated custom functions for greatest() and concat()

This was still assigned to me. No objection, but I'm not optimistic about citext. It would make more sense to me to switch to case-sensitive, collation-sensitive *by default everywhere* (including on MySQL), and handle case and collation sensitivity manually in PHP. This is the only way to have a well-defined, consistent and efficient behavior on all database engines.

Since this September 9.2 no longer supported, looks we need to increase minimal version again