[policy, no patch] Move PostgreSQL driver support into contrib

@catch I'm with you that Drupal 8 can't ship with broken support for SQLite and PostgreSQL. But I can't see why to completely remove it until there is an automated testing available. I'm working for months with Drupal 8 and PostgreSQL and believe me - once installed it runs pretty smooth just yet!
The only issues you may notice are just in the PostgreSQL logs and not visible on the frontend side. There was a problem with Search related to PostgreSQL for that I submitted 1-line-patches and the problem is gone.

Yes, there a a couple of issues left regarding #1003788: PostgreSQL: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar ID, but this is not a problem of PostgreSQL, it's a bug in Entity/Menu/Whatever core system and should be fixed there. Guess the same happens when using MySQL but is silently ignored from MySQL side IMO.

Better than removing PG support, we should have a PG test environment up and running ASAP to get better overview of what is left to fix for PG.

I don't really think we need patch-level testing for things like this, personally; I think once-per-day testing + e-mail to core committers would be plenty. Adding additional test runs to patch-level testing increases the time it takes to test patches by a factor of N so I'd rather do just one of those and then try and shoot for the most common set up for whatever version of core.

If a PG/SQLite fan would like to set up a test harness, here's info on how to do that with vagrant https://drupal.org/node/2144475 and a video on how to use it: http://vimeo.com/81293969

On this particular issue, I'd only rather do this as a last resort, personally. We already know we're introducing bugs that remove the "abstraction" from our DB abstraction layer, and this would only make it worse.

So if we get within 5 beta blockers or something and this still hasn't happened, revisit the decision at that time. We're a long ways off from that for now, let alone being within release, so this doesn't feel pressing to me atm.

Btw, #697220: Run tests on all supported database servers is the central place to coordinate this effort with the testbot maintainers. It currently supports on-demand testing of SQLite/PostgreSQL but can't support automated testing until the test failures are gone. https://drupal.org/comment/8252251#comment-8252251 shows how to help find them and fix them.

The best approach here would be to kill our home grown abstraction layer and use Doctrine DBAL instead. To be honest, the SQL abstraction layer is not very strategic for Drupal anymore, as SQL is less and less used directly.

" It'd be better than the status quo, but leaves us at 'secondary environments are major bugs' still or randomly adding critical bugs that could otherwise have been avoided."

If it's between that or quadrupling+ the patch test time, I'd gladly take that.

And random critical bugs can be avoided by a "Commit freeze" the 24 hours before a release. That's what I used to do anyway. :)

I agree with webchick on this one. Our testing world + the slow DDL on SQLite and Postgres make per-patch testing infeasible for the foreseeable future, but that doesn't mean we shouldn't still try to automate figuring out when we've broken something. Having 24 hours of commits to check is way better than the status quo of "sometime in the last 6 months, maybe?"

I don't know that Doctrine DBAL prevents you from inadvertently writing DB-specific SQL. I highly doubt it. Even if it could, that's a big API change and not OK to do this late in the D8 cycle.

Is there a way we could isolate certain "canary" tests for SQLite/Postgres, perhaps? Eg, identify and flag those that are likely to break on those DBs, and those that in practice shouldn't be doing anything DB-specific? Then we could run only certain tests on those DBs to speed them up. (I'm thinking annotations on the test class or the test methods, perhaps?)

SQLite testing is actually pretty fast (faster then MySQL, and scales better with concurrency). The issue is not technical, the issue is that nobody cares.

I don't want to divert this discussion to off topic items, but my development on drupal8 would be impaired if SQLite was not available.

@webchick Thanks for the vagrant links, didn't know about that;-) For now I startet run-tests.sh locally against PG. It seems this this will take a while, so I will attach the log later when done...

I'd like to point that we ported D8 Oracle driver #2037119-13: Roadmap for Drupal 8 oracle driver and currently only few isssues left

Let's file another meta for sqlite and having bots to test per-day regressions if any for both databases would be enough.
Suppose pg and sqlite are affected by #2146733: Select queries should not use rowCount() to calculate number of rows so much more tests would be green.

PS: I got near 100 sites running D6 on postgresql so would really like to port them to D8

@andypost nice to see that even Oracle support is on the way!
One question so, you say only a few issues left. I'm currently running /core/scripts/run-tests.sh --all against a PG instance and notice some problems already with some entity related tests.
Some entity tests create way to long relation names (e.g. simpletest662163entity_test_mul_revision__evfnaxzl_field_name_e...) and make PG fail because of the 63 chars limit for relations names.
As far as I know Oracle has that limit too and even below that, if I remember right the name limit is 30 chars, right?
So how did you come around this limit and do you have some test related patches to share which can help us make PG pass these tests :-)

So we are basically just blocked by having resources to get testbots running on sqlite and postgres?

No, not quite...

#697220: Run tests on all supported database servers has added support for testing in non-MySQL environments. You can see that if you look in the left sidebar on qa.drupal.org under the "Alternate Environment (PHP/DB) Test Results" heading. Click on one of the links there (for example, https://qa.drupal.org/pifr/test/600303 for D8) and you'll see different tabs you can click on below: PostgreSQL/5.3, Sqlite3/PHP 5.3, etc. that have test results on them.

In order to turn those on on an automated fashion (as opposed to having to manually click the links to check the results) we need a 100% passing greenboard of tests on each environment. Right now, PostgreSQL can't even install Drupal 8, SQLite has some kind of testbot bootstrap problem, and PHP 5.5 has 41 fail(s), and 15 exception(s). (Though PHP 5.4 is green. Woo!)

So we need PostgreSQL/SQLite users to get to the bottom of those test failures + supply patches to fix them, so we can turn on automated (most likely daily) testing and then stop breaking these alternate environments once and for all.

Postgres still wont run because troubles with passing connection array

@bzrudi71 approach is the same as D7 - we support table {long_identifiers} for mapping, and sure oracle limits them to 30 chars

While still waiting for run-tests.sh to complete I read a bit in the sources for the Oracle D7 driver and in particular the mapping relation stuff. As I'm pretty sure we could do the same for PG and D8, I think we shouldn't. This solution requires mapping to be done for every new db connection -doh! And even if this is not as slow as I think with many relations, we still shouldn't do it.

Here's why ;-)
1. PG let us create relation and index names with a max of 63chars/bytes. I looked to all the core relations and not a single one reaches that limit by far.
2. Creating new fields via UI even limits the machine_name to 32 chars! (incl. field_ prefix) by default so the longest possible relation name in core I can think of is:

node_revision__field_room_for_26_chars_here_xyz

with a length of 47 chars in total which leaves even enough room for _idx suffixes.

So doing the hack of a mapping relation or other hacks in the PG driver just to make a handful tests pass in PG is absolutely overhead! So let's just identify the broken tests and fix them.

Currently Simpletest adds a prefix of:

$this->databasePrefix = 'simpletest' . mt_rand(1000, 1000000);

doh, 17 chars for just a prefix - nod bad. Guess here is also some room to shorten the relation names for tests and make even more pass.

I do not understand why this issue is (1) critical and (2) filed against D8.

Quoting the relevant parts of the OP:

Having postgresql and sqlite in core is supposed to enforce database-agnostic code on us, however it doesn't because we don't yet have automated testing on those platforms.

We can't release with databases 'supported' but actually not at all

That has been the case since... Drupal 5.0.

Technically even since Drupal 4.0.0.

But yet, we released. We survived. Bugs got fixed.

I understand the concern being raised, but that's not a regression introduced in D8.

Lastly, #1808220: Remove run-tests.sh dependency on existing/installed parent site introduced a hard dependency on the SQLite database driver, which will be actively used by run-tests.sh as soon as #2206501: Remove dependency on Drush from test reviews has been deployed to PIFR/testbots.

In addition, I agree with @dcrocks — all of my core work on the installer + extension systems would be a PITA without the SQLite driver. My D8 reinstall.php script contains explicit support for deleting the SQLite database of typical "scratch" test sites.

IMHO, this issue should be moved to D9 (or closed with works as designed). Of course, we want to fix known bugs and look into improving the testbot situation.

My browser fails to re-find this issue, because its issue title severely lacks context; adding some essential keywords ;-)

Let's move a discussion about SQLite into its own issue.

As we discussed this morning, moving PostgreSQL support into contrib actually has a number of advantages:

1. Issues with the drivers themselves can probably be fixed more quickly, since contrib has a faster turnaround time generally.
2. Major issues can still be filed in the core queue when PostgreSQL support is broken by insufficiently abstracted code in core.
3. It gives us a chance to recruit new maintainers for these drivers as well.
4. The majority of users who use MySQL/MariaDB can begin to adopt Drupal 8.0 even if we are still resolving PostgreSQL-blocking bugs.

Let's get feedback from the PostgreSQL maintainers on what they would think of maintaining the driver in contrib.

In reply to #25: -1 here. We are shooting in the dark as long as we don't have a single PG testbot run result. Nobody knows where we stand as long as PG bot is broken. From the known PG issues we have already 50% "needs review" patches, just nobody cares about. I personally gave up submitting more patches as long as bot is broken :-( The last PG testbot run was months ago and it just failed because of "out of disk space" errors. That seems like a quick fix possibility to me and I have to wonder why it takes months. In other words: Let's get bot going and I'm pretty sure PG related issues will be fixed before the whole entity related bugs outstanding :-)

As a PostgreSQL Driver maintainer, fixing PostgreSQL bugs in core is a continual nightmare. Other systems in core break PostgreSQL all the time because they only develop and test in MySQL. DBTNG certainly improved things but without a testbot to fail patches in an issue queue that break a core database driver (core being the key word here), Core contributors continue to break Core without actually knowing that that is what they are doing. PostgreSQL support is currently apart of Core just as much as SQLite and MySQL/Maria support is but nobody knows that they break it. As a PostgreSQL Driver maintainer, its discouraging to fight an unwinnable battle, to be a blocker to a feature people rather see in than supported on a database most contributors don't use.

For PostgreSQL and any other database for that matter, to remain in Core, patch acceptance gateways need to include passes on all supported databases. Otherwise its simply not supported.

That being said, how does removing database drivers from core continue to encourage data storage flexibility? How do we ensure Core retains the ability to extend its reach to support other drivers like Oracle and SQL Server?

Not necessarily a proposed solution but a thought: Why can't we have both Core and Contrib drivers? We're namespace compatible now, two drivers won't conflict. If that gives PG maintainers the speed they need to develop and Core maintainers the means of keeping PG in core, then why not? Core could merge in stable releases on the contrib driver.

The meta is #2157455: [Meta] Make Drupal 8 work with PostgreSQL or remove support from core before release

The slowness of DDL needs own issue, suppose their friendly community could explain this in #2276809: Solve PostgreSQL DDL slowliness

Postgres testbot results are here:
https://qa.drupal.org/pifr/test/600303

They are on-demand only (not on patch, commit, or any time interval). But we can request results at any time until #697220: Run tests on all supported database servers is resolved. (I agree that the lack of regular test runs makes it insufficient to support postgres or other drivers and I think that we probably need to have regular postgres testing whether the driver is in core or contrib, at least so we know when it regresses.)

Edit: Looks like the postgres bot is running 5.3, while D8 now requires 5.4. So I take back some of what I just said. :)

I support moving PostgreSQL to contrib.

I think our time is better spent focusing on substitutable back-ends outside the relational storage area, as we have for reverse proxy caches, queues, locking, caching, full-text indexing, Views indexes, and (to a lesser degree) field and entity backends. With the strength of the current MySQL/MariaDB offering and so many potential FOSS and SaaS service integrations possible, substituting the RDBMS out just isn't, comparatively, a compelling way to get more features, performance, or scale.

An update regarding testing: according to jthorson, the on-demand Postgres testing we've had this past year on the legacy infrastructure is now entirely gone-forever broken, and he is estimating that it will be at least two months before we can support automated Postgres testing on the new QA infrastructure.

David is right but not for the reasons he says or especially for the reasons he says :) Let me try to phrase this in another way.

With CMI most of the simple tables are banished from core.

What we have are sophisticated subsystems that need storage specific drivers. And most of those drivers need PostgreSQL specific code. With 9.3 and 9.4 especially, the ability to store and query JSON efficiently while retaining the capability to JOIN makes PostgreSQL potentially a compelling offering for entity storage. Trees could use much more efficient encodings if the table can have functions in the indexes. So on, so on.

So yeah, we have SQL code that is supposed to be cross platform but the test fails prove they are not and there is no need for them to be now that everything is pluggable. We also squander the opportunity to use MySQL specific features because of this polite fiction of being cross SQL compatible. There's no need for that anymore, if you want to user another DB, SQL or not just replace all the storage related services.

Edit: DBTNG is still badly necessary for security reasons.

I agree with chx's arguments in #36, too. Even for parts of core backed by RDBMS, the important thing is a correct, tuned implementation, not one maximizing SQL portability.

Case in point:

An example is variable_set(), which we've had to replace in the last week with some MySQL/MariaDB-specific code for some customers to avoid deadlocks caused by the portable merge-query code. Yes, it's broken to call variable_set() so much in the first place, but it could be any other function designed for use on general, public pages.

The only way for us to make the solution truly DB-TNG friendly would be to write a custom merge driver for MySQL/MariaDB that picks a strategy (SELECT...FOR UPDATE or ON DUPLICATE KEY UPDATE) based on whether the merge query criteria are equivalent to the table's primary key. Even figuring out the ideal strategy substantially slows down the code path because the merge criteria have to be compared with the schema.

We could also have primary key vs. flexible merge queries available as distinct query types, but then we'd be preferring MySQL/MariaDB in the abstraction layer -- which is better, I think, than what we're doing right now by being skittish about treating MySQL/MariaDB as our prime target.

I think it's only fair to postpone this decision until after testbot 2.0 infrastructure supports PostgreSQL testing + 3 months or something to give time for PostgreSQL users to fix the bugs that shake out. If for some reason this is one of the last 5 criticals left blocking D8's release at the point testbot 2.0 is released, then I guess we'll have to make some tough calls at that time. But until then, this doesn't feel pressing to me at all.

Yep, fully agreed on that plan.

Sounds fine to me. It also gives any potential Postgres-interested parties longer to emerge and work their magic.

I have filed #2290771: [policy, no patch] Make default SQL drivers MySQL specific as a sorta followup.

Since this issue was last in heavy discussion, we have added support for swappable backends for various services. See https://www.drupal.org/node/2306083

That means contrib modules can provide PostgreSQL/SQLite versions of relevant services just as easily as they can a MongoDB or Cassandra version.

In light of that, should we go ahead and bump Postgres? We're well into beta and there are issues that still need Postgres work (eg, #2371709: Move the on-demand-table creation into the database API). If we have a Postgres testbot up and running in some public place yet then I am unaware of it.

@crell Current PostgreSQL issue tracking is done in #2157455: [Meta] Make Drupal 8 work with PostgreSQL or remove support from core before release. There is no (working) public PostgreSQL testbot avail, but a complete test run result from yesterday can be found here. I also like to mention that we spend a lot of work and effort into the TestbotNG which now fully supports PostgreSQL in various versions.

Yeah, the folks making effort to fix PostgreSQL work have been making great progress for months. I'm way more worried about other aspects of D8 than this one, personally.

Ah, great, thanks for the update! I'd not seen the movement over there.

Just wanted to point out that a lot of the recent fixes to make PostgreSQL tests pass were in fact not specific to PostgreSQL but improved reliability of both runtime code and tests e.g. by adding missing sorts, etc. So fixing PostgreSQL actually improves stability for everyone.

Changed the title over at #2157455: [Meta] Make Drupal 8 work with PostgreSQL or remove support from core before release to include "or remove support from core before release" (as a parallel to the update manager issue) so closing this one as a duplicate.