Remove PostgreSQL driver from core

Yes, we lack a maintainer for PostgreSQL, but there is a user base.

Just this week I was consulting for a client that has Drupal 5, 99 enabled modules, including CCK, Views 2 and Panels 2 all on PostgreSQL. 300,000+ users, and quite busy.

The user base is silent, but it is out there.

It is a chronic problem we have in Drupal for several years, and there are lots of threads on it on the mailing list.

But this does not mean we just kill it.

What is the workload for maintainer?

ARE YOU SERIOUS?

PostgreSQL support is important. There would be a number of people willing to takeover as a maintainer, if necessary.

DT

Backing up Shiny here - what do you need?

Support for Drupal in the core Postgres community has been very strong; we'd like to see that continue.

I'm by no means a PostGres guru, so I'm sure there are better candidates for maintainer than I am, but I use it, and I love it, and I'm willing to help.

One thing that would help would be getting the database layer completely abstracted from MySQL.

Our company runs a website built on a 5.x core with PostgreSQL 8.1.11 and it would be a huge mistake to remove the driver. So to address the issues at hand here:

- has no identified maintainer
What is the workload for this module? I can see people who are very busy (such as I) that cannot take over maintainer status.

- has been suffering identified critical bugs for more than nine months (for example: the book module is completely broken on D6, see #223820: Book module tests for postgres error)
Nine months is an awful long time which probably coincides with the amount of time that there wasn't a maintainer. Find a maintainer and the bugs will be fixed.

- has no visible user base
Our site gets more unique visitors a day then most sites do in a six month to year long span. Even yesterday I submitted a patch for PostgreSQL for the Actions module: http://drupal.org/node/337108. So there is a user base even if it consists of just me and the few on the PostgreSQL group list :)

- is not tested and probably doesn't even work on D7 (I was unable to even have SimpleTest run)
This also goes along with having a maintainer, find a maintainer and you can fix the issue.

It would be unwise to segment the market off in this way. We built our entire infrastructure around Drupal and it would be awfully hard for us to move to a 6.x or 7.x core if the driver of the database was not supported. Ultimately it would lead to a decision from the higher ups to move off the platform completely.

@all - this is what we need:
* A few people with say an hour a week to test core patches. Confirm bug, apply patch, post on the issue to say whether it's broken or not. If you use postgresql at all, you can help with this - I can count the number of people who do so already on a fraction of one hand.

* A couple of people with decent PostgreSQL expertise, and ideally knowledge of PDO, to help answer questions and/or roll patches for trickier issues.

Knowledge of Drupal itself is not necessary for either case, and someone stepping up to actually help would get plenty of advice and information in via the #drupal irc channel if they need or want it.

People doing either of these things are sorely lacking, and have been for a long time. For example, this patch #225450: Database Layer: The Next Generation which opens up a properly abstracted database layer - one which allows for much better cross-database support, and made a big improvement to existing postgres support just by itself got very few reviews from people with postgresql installed during its progress. I personally had to go and install postgres for some of the final testing because no one, not one person, turned up from the postgresql community to do so in time to get it committed. This despite it being on the list of core/contrib issues that require a postgresql review for months, and Crell personally hunting around for people to try to get some help. Bjaspan, who wrote the postgres driver for that patch, is also primarily a MySQL user (afaik).

The fact is, postgres is unsupported in Drupal 7, what we have is a lump of PHP code which never gets run, because no-one using postgres ever tries it. Removing it would simply be admitting reality in this case. So if you want support, it's not a matter of convincing those of us working on core (who are also busy people, doing so as volunteers) to keep it in just because it would be a 'bad idea' to remove it, or calling us 'crazy', you'll need to get stuck in and help, or find some people who are prepared to. Expecting a small number of overstretched volunteers to do all the work maintaining a platform they don't use is unreasonable - especially if your company is making money from running Drupal on that platform.

Let's make a deadline, because deadlines get things done. I suggest that if by January 1 (the holidays are great time for core coding) Drupal 7 tests pass on postgresql then we keep it, otherwise we drop it.

Also, while i despise PostgreSQL, I am happy to put aside personal feelings and help anyone who makes Drupal better. Find me all the usual places if you need assistance with DBTNG, simpletest or anything.

I thought about this some more and it can mean two different things:

1. Stop supporting PostgreSQL totally.
2. Remove untested PostgreSQL PDO code from 7.x core until it is proven to work, then move it back in.

I think Damien means #2, and I kind of agree with it. We should open an issue with a patch and point the PostgreSQL folk to it, and mark it as critical.

Hopefully those with vested interest in PostgreSQL would chip in and help getting it tested and debugged so as to make it into core before 7.0 is out.

Another issue, unrelated to PostgreSQL but affects it, is that sites are 2 releases behind the current development version. So, the site quoted above is on Drupal 5.x, and is considering moving to 6.x, but 7.x is not on the radar.

So, we need to convey the message that testing dev releases is beneficial to them even thought they are not moving to it any time soon, because it ensures the long term viability of their platform that they will eventually go to.

Lots of cross-posts, resetting status.

In addition to kbahey's 1 & 2, it's also quite possible to maintain a database driver as a contributed project on Drupal.org - I don't hold any hope that a contrib driver would ever actually get maintained, but if people wake up in the middle of next year and see no postgres support in Drupal 7, it's not to late for them to contribute to a fully functional driver.

@pearlbear

Thanks for volunteering.

What you should do is checkout Drupal via CVS using the HEAD tag, and test it on a postgresql install, see what fails and submit patches for it. Also, check the issue queue under the postgresql database component and see all the open issues and submit patches for them.

What I would like to do is roll patches for *.install modules that are broken because their syntax is MySQL-centric.

So, maybe I could assist pearlbear in that regard. (Actually, what I'd really like is for that database abstraction layer to work, because after just using MySQL syntax, that's the biggest problem with install files for PostGres, and if the abstraction layer works, then PostGres is helped. So maybe I can assist pearlbear in that regard.)

shunting, in Drupal 6 and 7, all install files should be using hook_schema() and drupal_install_schema() - which is database agnostic.

-1 to removing postgresql!

First of all, let's look at the difference between mysql and postgresql licenses:
MySQL is a commercial product, with an FOSS exception:
http://www.mysql.com/about/legal/licensing/oem/
http://www.mysql.com/about/legal/licensing/foss-exception/
Postgresql is BSD licensed:
http://www.postgresql.org/about/licence
we should give users who (morally) don't want to use or (legally) can't use mysql an alternative.

Secondly, the problems identified with postgresql support are critical, yes, but do not warrant removal in my opinion. as they're fixable with the resources availble within the drupal community (who are possibly unaware of those problems)

However, I would agree with a re-write.

Also, the users of postgresql are probaly more silent, as they're probaly of the more "corporate type"
sourceforge.com might using postgresql+drupal
http://buytaert.net/sourceforge-using-drupal
http://www.postgresql.org/about/users

postgresql support for D7 seems a nice candidate for a spotlight!

Subscribing

We're not removing the PostgreSQL driver.

Dries, I disagree. Look at #20, even a postgresql user says it's reasonable to drop it if it does not work. (Did I mention that I spent some time with pearlbear looking at the problems?)

@23

catch:

All install files should indeed be doing that and being database agnostic is wonderful. But at least one contributed module (autosave) uses those hooks and does not install under PostGres.

I am running drupal on postgres, and exactly chose drupal at the time, because it was able to run on postgres. I apologize to not being able to contribute more on drupal because of my other OS projects. Big thx to all those, who already said and will say to be willing to maintain the postgres support!

What you should do is checkout Drupal via CVS using the HEAD tag, and test it on a postgresql install, see what fails and submit patches for it. Also, check the issue queue under the postgresql database component and see all the open issues and submit patches for them.

Thanks for the tips, I'll see if I can help there too :-)

DT

I would like to propose my help to maintain the PostrgeSQL driver :

1) I wrote pgAdmin 1&2 for PostgreSQL, PostgreSQL graphical client.
2) I am new to Drupal, but I am migrating a 500.000 messages board to Drupal.

So Drupal is important for me.

Tolmi's invitation for Hungarian Drupal and PostgreSQL users: http://lazycat.hu/story/drupal-7-postgresql-tamogatas-nelkul
See the picture! ;)

pp

-1 from me.

There are a variety of comments about how we need people to step up and test patches on Postgres, but isn't that raw testing now the purpose of the test bot? 99% of the time when reviewing a patch, it doesn't matter what the underlying database is, just that whatever is being fixed/changed/etc. is implemented correctly.

Looking ahead, I think it is important that we develop a framework so that all patches can be automatically tested on all database drivers that are in core. Otherwise, we're leaning biasedly toward MySQL, and that makes it easy to "forget" about the other database drivers.

Also, if patches could automatically be tested against other database drivers then it would be much easier to pull in experts on a particular database platform if a patch is failing under that database. You could flesh out a patch under MySQL and get the code/logic solid, then have the test bot hit it and if it fails under Postgres, get someone familiar with Postgres.. if it fails under SQLite, same thing. I can even see having a special page or an additional filter in the issues queue to see patches failing on some drivers but passing on others.

Does anyone else agree?

CalebD - see #337795: Test patches on multiple environments - however, before we can do that, postgres has to have a 100% pass rate on core simpletests, which means some manual patching and review up front - same as we had to do to get 100% passes with MySQL.

Just FYI, we really need some people who use pgsql over at #337794: PostgreSQL surge #1: make simpletest works again testing that patch. It's the first step to supporting pgsql in D7. We really need to see some of the people here who have firey passions about not removing pgsql support put forward some effort to help see this support improve.

If you need any help getting setup for core patch reviews or want tips/tricks, feel free to drop by #drupal. There are 300+ people there who would love to help you. :)

I am reviewing code from modules and I see the same mySQLs.
These are usually tiny details.
Example: http://drupal.org/node/337687

For now, I see very simple things:
* write "SELECT sum(foo) AS bar FROM table" and not "SELECT sum(foo) bar FROM table".
* Do not delete entries in TWO or MORE tables simultaneously. "DELETE t1, t2 FROM TABLE1 t1 LEFT JOIN TABLE2 t2 ..." This simply does not work under PostgreSQL. We need one query per table deletion. Which is a heck I admit".

On the converse, I can only invite Drupal developers to use PostgreSQL/pgadmin3 locally to test their module.
It is quite interesting to be able to write SQL99 code that will run smoothly anywhere.

MySQL was sold to a private company, future is not guaranteed.
Writing fully SQL99 compliant code is the guarantee that Drupal database will never get into trouble.

How to join Drupal developpers on IRC?
I am new to the project, and I don't want to only stay connected and read your discussions.

What I can do is try to 1) get back on experience and write a simple page gathering tips to write SQL99 compliant code and 2) make screencasts explaining the power of pgAdmin3 / PostgreSQL. 3) I may be interested in working on the new driver for D7, but I am new and I need to submit a series of patches before I understand Drupal deeply.

I agree that writing SQL99 code is not a question of automatic testing. This is only learning the standard SQL, like standard C or standard C++. Learning ISO, you don't loose time, on the converse you gain time. Beside, PostgreSQL is very good at gathering statistics and explining query strategies. Sometimes I read things like: we could split this table in two OR we could use replication OR ... Using PostgreSQL, you can drive to really monitor queries.

For example, in Drupal, I see a lot queries running twice or more in a single HTML page. This is because Drupal developers don't monitor their queries more that say 'I tested on a Dual core computer, etc ...' You should really test PostgreSQL . Developing on PostgreSQL produces code that runs under MySQL. The converse is not true.

Kind regards,
Jean-Michel

jmpoure

Welcome to Drupal. We look forward to your help in making Drupal more database neutral and working better with Postgres.

First, checkout Drupal from CVS, and start testing it against Postgres. When you find issues, you go to #2.

Second, everything in Drupal happens on the issue queue. Just create an issue with a patch in it for others to review. Search before you post, so that you do not post duplicate issues. Someone else may have opened an issue.

See the "surge" issues opened by Damien above.

IRC channel is #drupal and #drupal-dev on freenode, but you have to have issues to point it.

Thanks your for your welcome and support.
I plan to fix issues in several modules before I get deeper into D7.

I am migrating a 500.000 pages website to Drupal and I cannot rely on MySQL poor-and-dangerous performance.
Also I am starting a new business and relying on MySQL would be like having a sward on my head all the time.

So ... I plan to test D6 modules and fix tiny details (example: translation server) BEFORE I go deeper into D7.
Sorry for that, but it will take some time before I join IRC.

On the converse, if you need to discuss on precise issues,
contact me on jm@poure.com and I will join IRC.

Kind regards, Jean-Michel

If these are contrib modules, then post an issue with a patch in the issue queue for that contrib module.

If these are in core modules, then it is more complicated: getting them into a future 6.x release is possible but not likely (unless the D6 maintainer decide to include them).

So, it is best if you post fixes for 6.x (in case someone else needs them), AS WELL AS D7 patches so that they make it into 7.0 when it comes out and carry forward to future releases.

jmpoure: What would be really helpful is to have a single, separate issue that handles any sort of SQL syntax things like that that need fixing. We really try to keep patches to one "context" to help make reviewing them more easy. See http://webchick.net/please-stop-eating-baby-kittens.

So please start by:

1. searching first to see if the problems you found have already been reported. Also check http://groups.drupal.org/node/6980 since it has a big list all nicely centralized.
2. If so, then look for a patch that's there and test it if one exists. If not, create a patch (or at least post instructions on what a patch would entail so that someone else can make it).
3. If not, then create a new issue with the details there, and ping back this issue (and probably that wiki page) so that we can keep track of them all in one place.

Tacking on SQL syntax error reports to an issue that's about something else is going to cause them to get lost in the discussion... we really need those elevated to their own issues so they can be properly addressed.

One important note is missing from this conversation:
Removing postgresql driver from core, does not mean that you can't use Drupal 7 with postgresql database.

You just simply download the driver as any other Drupal module database driver and you can use it, right?!

Pasqualle: Correct. Removing it from core would have the following disadvantages, however:

a) Inability to be tested with testingbot, which means errors are more likely to creep in, unless we have someone very conscientious maintaining this in contrib (and if we do, where are they so they can just maintain it in core? :)).
b) Core's DB abstraction layer gets less robust testing, because it has only two DB backends and not 3. Though arguably, you could say that's the situation now anyway, since pgsql is so hosed.

(1) do we have a testingbot with pgsql database?
(2) do we have tests written especially for pgsql driver?
(3) Is it impossible to create an own testingbot with a contrib pgsql driver? (do you want me to create a feature request for that?)

we will have Oracle driver for D7, I am almost sure. So there will be more than 3 backends not less. It just not included, I don't see any problem with that. Anybody can run simpletests, and anybody can file an issue against core..

from the patch:

PostgreSQL can be added as a contribution module.

is this correct? database drivers are added same as modules? As I know db drivers have to be installed into different directory. Can we have a better PHPDoc here?

And we need a download link for additional db drivers in the install profile. (in the followup patch)

1. no - see #337795: Test patches on multiple environments
2. no, but it doesn't pass the basic core simpletests yet - the driver itself may or may not be an issue, general breakage definitely is.
3. I know that boombatower and amazon are helping people to get testing servers set up - feel free to contact them if you have one to offer, or follow-up on 1.

My 2 cents :
looks like their is a need for more some people to get involved into the postgresql stuff around drupal.
Maybe some kind of announcement before removing would help ? That's one of the most used database server around, so that's really a shame if drupal cannot handle it out of the box. User will certainly go into searching for this driver and probably get confused.

Subscribing.

Removal of PostgreSQL from core, even with the option of having PostgreSQL as a downloadable driver would be huge mistake.

Excuse me gentlemen, Obviously this must me a ploy of some dramatic affect to rally a postgres pro to stand up like a man and throwdown!! The time has COME!!!

@jmaturus quit calling me a man.

This surge seems to be going well.

I have tested patch #1 and patch #4 so far.
#4 has been added to CVS HEAD and fixes undefined functions and:
#1 has fixed issues with Simpletest Segfaulting apache so now all tests execute and the Book module passes all tests. (hopefully it can be moved into HEAD soon also).

(#248205: PostgreSQL surge #7: Require PostgreSQL 8.3) has been committed.

The only 5.x issue I can find quickly is http://drupal.org/node/76681. Postgres maintainers - please review and correct as needed, and find any other issues.

For anyone working on Postgres in D7, I am frequently available in #Drupal and willing to help anyone through the new API that is serious about making Postgres work properly. :-) I don't want to drop postgres support either, as if nothing else it makes a good canary driver to ensure we're doing things in a clean fashion, but shipping a buggy driver is, I agree, worse that not shipping one at all.

Please remember that the DB layer had a major change in Drupal 6 with Schema API and a total tear down in Drupal 7, so the issues that were present in Drupal 5 may well not be relevant anymore.

surge status update!

1. #337794: PostgreSQL surge #1: make simpletest works again has been committed for D7.
2. #337795: Test patches on multiple environments has been escalated to PIFT and postponed.
3. #337796: Make all tests pass on PostgreSQL is active.
4. #338586: PostgreSQL surge #4: fix undefined function call has been committed for D7.
5. #339588: Column type of int_unsigned causing pdoexception is active
6. #296624: do_search() fails hard on Postgres has been committed for D7, and will get ported to D6.
7. #248205: PostgreSQL surge #7: Require PostgreSQL 8.3 has been committed for D7.
8. #229051: Top Visitor Page problem with PostgreSQL 8.3 has been committed for D7, and will get ported to D6.

the road ahead is looking brighter, but we still have a long way to go!

(does anyone know more problems with PgSQL for 7.x)

and #229051: Top Visitor Page problem with PostgreSQL 8.3

some more:
#342503: Schema function findTables fails on postgres - wrong SQL
#323528: UPDATE queries should not use aliases committed to D7 and waits for a D6 port.
#342950: Compatibility stored procedures should be defined in includes/database/pgsql
#343082: Simpletests run significantly slower on PostgreSQL due to schema recreation

So there are 12 issues identified and 3 fixed of them. Good. If you guys fix one issue per day, then by January 1, we can have all tests pass.

Removing PostgreSQL support would kill Drupal for me - and I am just beginning to like it and chose it precisely because I needed a CMS/CMF which does not depend on MySQL.

Why not drop MySQL support and take full advantage of PostgreSQL as a BSD-licensed and ANSI-compliant database? -- just kidding!

But please, if you stop from putting MySQL-quirks into the code and adhere to the intersection of ANSI-SQL and MySQL, then the great database abstraction layer would do its duty and work. I just filed a bug report for project mailfix where a dreadfully wrong SQL statement, which obviously worked with MySQL, of course did not with PostgreSQL.

Requests to "please don't drop Postgres support!" that are not accompanied by an offer to help make Postgres work (which right now it doesn't) are a waste of time. Please do not post them. If you want Postgres to remain supported, there's a whole bunch of issues mentioned further up in this thread that could use your attention.

there is very much so not gonna happen as DamZ and I will be the pgsql maintainers. Closing

I just came to the issue to mark won't fix. I so hope we won't need to reopen in a year...