[Meta] Make Drupal 8 work with PostgreSQL or remove support from core before release

Added: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar ID
Note, this problem exists currently also outside of entity context. There are a couple of exceptions in PostgreSQL logs regarding string -> int queries...

Added #2158229: Site search indexing broken on PostgreSQL

Added #2158339: Search results page broken on PostgreSQL as a follow up of #2158229: Site search indexing broken on PostgreSQL

As promised, here are the results of PG local testing. All in all absolutely not to bad as we only have two exceptions in the database tests themselves.
Some notes so... first of all some tests (8 in detail) failed to start because of to long table names (63chars limit in PG), mostly entity related. Second 90% of all failing tests are related to broken STRING -> INT comparison querys caused by the entity query builder. So if this is fixed nearly all/most tests will pass :-)
Unfortunately I'm off for 4 weeks and can't work on that before February, so feel free to pick and fix some issues already.

Added #2188615: Insert-query with multiple values fails on Postgresql

Added #998898: Make sure that the identifiers are not more the 63 characters on PostgreSQL

removed #911352: Document that foreign keys may not be used by all drivers (Never ending story and not really needed to make D8 work...)

cleanups...

Added #2195915: Cannot save text filter config using PostgreSQL if Comment is enabled [blocks installation!]

Added #2057693: PostgreSQL orderBy method adds fields it doesn't need to, leading to fatal errors when the result is used as an insert subquery

Marking #2195815: [meta] Search module tests do not pass on PostgreSQL and #2195915: Cannot save text filter config using PostgreSQL if Comment is enabled [blocks installation!] as closed - yay!

@ricardoamaro Great to finally see some good progress in PG testing :-) Looking at the logs it seems many test failures are caused by already identified issues. Namely (by priority):

#2279395: The PostgreSQL backend does not handle NULL values for serial fields gracefully
#1003788: PostgreSQL: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar ID
#998898: Make sure that the identifiers are not more the 63 characters on PostgreSQL

This should help with passing many more tests. However, there are many more exceptions in the log than I have locally. One thing I noticed is related to concurrency, in other words some tests fail when setting concurrency to any value higher than 1. Guess we have to spend a closer look on that. If I find the time I will run the docker image locally to trace this down.

Thanks again!
Moving: #2279363: file_managed.uri should not specify a prefixed unique key to fixed

Moved #2279395: The PostgreSQL backend does not handle NULL values for serial fields gracefully to fixed section...

#2181291: Prevent a query from aborting the entire transaction in pgsql moved to fixed - yay!

Moved #1837118: UPDATE foo SET bar=(SELECT...) is not supported to fixed section.

Added #1060476: Multiple issues when PostgreSQL is used with non-public schema to testbot breaking issues section. I found some migrate_drupal tests failing because of that ;-(

<?xml version="1.0"?>
<testsuite><testcase classname="Drupal\migrate_drupal\Tests\d6\MigrateAggregatorFeedTest" name="testAggregatorFeedImport()"><failure type="failure" message="Completion check">The test did not complete due to a fatal error.</failure></testcase><testcase classname="Drupal\migrate_drupal\Tests\d6\MigrateAggregatorFeedTest" name="setUp()"/><testcase classname="Drupal\migrate_drupal\Tests\d6\MigrateAggregatorFeedTest" name="display()"><failure type="failure" message="migrate">Migration failed with source plugin exception: SQLSTATE[3F000]: <strong>Invalid schema name:</strong> 7 ERROR:  schema "build_2014_10_10_141908" does not exist
LINE 4: LEFT OUTER JOIN build_2014_10_10_141908.simpletest971774migr...
                        ^</failure></testcase></testsuite>

Moved #1008128: Do not use a single underscore as table and index separator on PostgreSQL and SQLite to fixed section.

Moving #571548: Identifiers longer than 63 characters are truncated, causing Views to break on Postgres out of the way. Seems fixed in the meanwhile and it doesn't seem we have any failing tests because of this...

Moved #2232425: Database Schema field/column default value is not properly quoted via PDO::quote() to fixed!

Moved #1565972: Remove static caching of array in mapConditionOperator() to fixed. Already committed and just open for discussion...

Moved #1565972: Remove static caching of array in mapConditionOperator() to fixed. Already committed and just open for discussion...

Moved #1565972: Remove static caching of array in mapConditionOperator() to fixed. Already committed and just open for discussion...

Moved #1148856: Postgres schema doesn't support keylength on a unique index to nice to have section as it doesn't seem to break any tests currently...

Moved #567148: Use ONLY_FULL_GROUP_BY for MySQL out of the queue, this is MySQL only and not a PostgreSQL issue.

Here we go. Created sub issues of all failing test groups ;-)

Moving #2192877: File content list includes ambiguous column fid in Group By statement over to required patches section as it breaks tests discovered in #2356979: PostgreSQL: Fix tests in file test group

Moved #2357007: PostgreSQL: Fix tests in views_ui test group to fixed.

Moved #1600670: Cannot query Postgres database that has column names with capital letters to testbot required patches section...

Moved #2356993: PostgreSQL: Fix tests in rest test group out of the way, as fails were not related to PostgreSQL. The root cause is the docker web container not supporting application/json mime type...

Added #2386461: PostgreSQL: Fix tests in entity_reference test group

Moved #2386379: PostgreSQL: Fix tests in book test group over to fixed. No longer an issue in current HEAD!

Moved #2061879: Remove Schema::copyTable to required section (one database test fails with 'not implemented' error).

@stefan.r

Does it seem likely we'll get all tests to pass on PostgreSQL for D8 core over the next months?

Hopefully yes, as we are as close as never before. But that depends mainly from the time of @mradcliffe and me as currently no one else is working on the remaining issues.
I already spend hours and hours to trace down a problem that affects many of the currently failing test but with no luck so far. That is a - fix one, fix many - thing.
I just did a fresh testbot run today that I will attach to give an impression of where we stand. Some notes so..

• Please ignore the fails and exceptions in migrate for now as they are based on a MySQL dump file ;-)
• Please ignore round about 5% of fails and exceptions caused by random 'database is locked' errors (not PG related)

So we are on the right way :-)

Regarding the scripts, I can do a setup on my spare server, just not sure when I have the time. But it's on my agenda now!

@mradcliffe great to see you back on track!
While looking at the remaining fails I noticed that around 50% of the fails in the test groups are related to views. So #2357003: PostgreSQL: Fix tests in views test group should have attention first. There is a problem within the views sql query builder. For example I found this in taxonomy views tests:

SQLSTATE[42P01]: Undefined table: 7 ERROR: invalid reference to FROM-clause entry for table "taxonomy_term_field_data" LINE 12: ...rm_data_node__taxonomy_term_field_dataINNER.tid = taxonomy_t... ^ HINT: Perhaps you meant to reference the table alias "taxonomy_term_data_node__taxonomy_term_field_datainner".

And with more info:

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in
    test_groupwise[test_groupwise_term]: SQLSTATE[42P01]: Undefined table: 7
    ERROR:  missing FROM-clause entry for table
    "taxonomy_term_field_data"
    LINE 12: ...rm_data_node__taxonomy_term_field_dataINNER.tid = taxonomy_t...
                                                                  ^ in
    Drupal\views\Plugin\views\query\Sql->execute() (line 1442 of
    /var/www/core/modules/views/src/Plugin/views/query/Sql.php).
    Drupal\views\Plugin\views\query\Sql->execute(Object)
    Drupal\views\ViewExecutable->execute()
    Drupal\views\Tests\ViewTestBase->executeView(Object)
    Drupal\taxonomy\Tests\Views\RelationshipRepresentativeNodeTest->testRelationship()
    Drupal\simpletest\TestBase->run()
    simpletest_script_run_one_test('21',
    'Drupal\taxonomy\Tests\Views\RelationshipRepresentativeNodeTest')

Wonder how that could pass in MySQL?

Updated infos for test groups.

No more fails in shortcut tests in HEAD, moving #2356995: PostgreSQL: Fix tests in shortcut test group over to fixed section.

Great! Now that #2356967: PostgreSQL: Fix tests in comment test group is in, we have 100% pass in comment tests... (moved to fixed section)

#2192877: File content list includes ambiguous column fid in Group By statement is in. Moving

• #2356971: PostgreSQL: Fix tests in editor test group
• #2356975: PostgreSQL: Fix tests in field test group

to fixed.

I setup a (simple) PostgreSQL testbot on a spare server that runs all tests once a day against HEAD. This should help us to keep better track of PostgreSQL issues. And the first that I noticed is a new failing test in block_content. So created #2424951: PostgreSQL: Fix tests in block_content test group...

Just discovered failing tests because of INDEX ALREADY EXISTS, so opened #2425127: Prevent PostgreSQL from creating duplicated index names within schema to fix that globally as this happens in various test groups.

#2424951: PostgreSQL: Fix tests in block_content test group is in, so moved to fixed. There is consensus to remove copyTable #2061879: Remove Schema::copyTable, so moved out of needed section.

@mradcliffe: This is just with DCI_TESTGROUPS = --all where simpletest runs out of memory for the results reporting. It works for single test groups so... I already suggested to write the results in chunks and not in a whole but can't find the issue. But it's a known issue, please see #2347493: Writing test results leads to FATAL because of memory_limit

Moved #1013034: PostgreSQL constraints do not get renamed by db_rename_table() to required section, needed to make Database SchemaTest pass.

#2356991: PostgreSQL: Fix tests in search test group is in, moved to fixed!

@all: I'm sorry my fault! The test was run on my experimental speed improved bot code, so please forget the new fails and exceptions ;-) As soon as #1600670: Cannot query Postgres database that has column names with capital letters is in and we filter out random bot fails (like CommentPagerTest) and the fails that also occur with MySQL (StyleSerializerTest, SessionHttpsTest) we are that low in failing tests that we can create an issue for each single one. So please, next step forward #1600670: Cannot query Postgres database that has column names with capital letters :-)

IS cleanup...

Nice, #1600670: Cannot query Postgres database that has column names with capital letters is in and fixed many of the remaining fails and exceptions! We have 100% pass in #2356979: PostgreSQL: Fix tests in file test group now, so moving to fixed. Created #2443627: PostgreSQL: Fix sub-system tests in system test group to have better overview of failing system sub-tests.

Listed each single test failing to get better overview. We need separate issues for each test now.

Created #2443633: PostgreSQL: Fix locale\Tests\LocaleUpdateTest and #2443635: PostgreSQL: Fix config\Tests\ConfigEntityListTest.

Doh, all sub-issues created - happy hacking ;-)

Moved out #2350791: Link, Boolean, Email field tests attempt to query integer entity id with strings such as "manage" or "structure" of required section and into nice to have...

Moved #2443635: PostgreSQL: Fix config\Tests\ConfigEntityListTest to fixed, thanks all!

Moved #2356987: PostgreSQL: Fix tests in node test group to fixed, we have 100% pass now in node tests - thanks all!

Re #109: Yes, this is the first time I see Drupal\system\Tests\Installer\DistributionProfileTest fail. Can't reproduce locally, nor with bot so let's keep an eye on this before creating an issue.

Adding #1518506: Normalize how case sensitivity is handled across database engines. Would have saved me a hour of debug if that one had the PostgreSQL tag set ;-) This is the root cause for #2443679: PostgreSQL: Fix taxonomy\Tests\TermTest to fail...

New fails in views/src/Tests/GlossaryTest.php, caused by #2381217: Views should set cache tags on its render arrays, and bubble the output's cache tags to the cache items written to the Views output cache.

Additional new fails in:
node\Tests\NodeListBuilderTest
system\Tests\Entity\EntityListBuilderTest

Caused by #2445743: Allow views base tables and entity types to define additional cache contexts, but that one was reverted meanwhile.

Created #2451749: PostgreSQL: Fix views\src\Tests\GlossaryTest.php.

Adding #2370593: Database::tableExists optimization for PostgreSQL to fixed.

House-cleaning. Moved #1072322: Figure out what to do with string concats to fixed as no longer an issue for D8.

Ups, now moved the right issue ;-)

Next step to the best pg-driver ever, #1668644: PostgreSQL: Impossible to change a field to serial, bigserial, or numeric is in :-)

New fails and an exception in:

• \views\Tests\Handler\SortRandomTest
• \system\Tests\Installer\InstallerLanguageDirectionTest

However, I ran both tests several times locally and they always pass. Guess theses are randoms as both do some kind of order by check. If we have fails again in some of the next bot runs we need to open an issue I think.

Moved #2357001: PostgreSQL: Fix tests in user test group to fixed. There is nothing left to do. The last exception in #2443685: PostgreSQL: Fix user\Tests\Views\RelationshipRepresentativeNodeTest will be fixed by #2443699: PostgreSQL: Fix views\Tests\Plugin\CacheTest on commit :-) Issues fixed:

• #2443681: PostgreSQL: Fix user\Tests\UserAccountLinksTest
• #2443683: PostgreSQL: Fix user\Tests\Views\HandlerFieldRoleTest
• #2443685: PostgreSQL: Fix user\Tests\Views\RelationshipRepresentativeNodeTest
• #2443687: PostgreSQL: Fix user\Tests\Views\HandlerFilterUserNameTest

House-cleaning! Fixed issues in #2443627: PostgreSQL: Fix sub-system tests in system test group:

• #2443653: PostgreSQL: Fix system\Tests\Condition\ConditionFormTest
• #2443655: PostgreSQL: Fix system\Tests\Entity\EntityReferenceFieldTest
• #2443657: PostgreSQL: Fix system\Tests\Entity\EntityQueryTest
• #2443659: PostgreSQL: Fix system\Tests\Entity\FieldSqlStorageTest
• #2443663: PostgreSQL: Fix system\Tests\Entity\EntityDefinitionUpdateTest

Fixed issues in #2356997: PostgreSQL: Fix tests in taxonomy test group:

• #2443673: PostgreSQL: Fix taxonomy\Tests\Views\RelationshipRepresentativeNodeTest

Closing #2357003: PostgreSQL: Fix tests in views test group (only one broken test left)
Fixed issues:

• #2462747: PostgreSQL: Fix views\Tests\ModuleTest
• #2443691: PostgreSQL: Fix views\Tests\Handler\AreaTitleWebTest
• #2443693: PostgreSQL: Fix views\Tests\Handler\ArgumentDateTest
• #2443695: PostgreSQL: Fix views\Tests\Handler\FieldGroupRowsTest
• #2452943: PostgreSQL: Fix views\Tests\Handler\FieldGroupRowsWebTest
• #2443697: PostgreSQL: Fix views\Tests\Handler\HandlerAllTest
• #2443699: PostgreSQL: Fix views\Tests\Plugin\CacheTest
• #2443701: PostgreSQL: Fix views\Tests\ViewExecutableTest

Opened: #2464481: PostgreSQL: deal with case insensitivity - happy hacking ;-)

Adding some new issues into nice to have section and removed one identified bot fail that was fixed meanwhile in HEAD.

@mradcliffe, thanks for taking track of new issues ;-) I'm currently low on time but think I can have a look at the broken tests over the weekend, let's see...

Nice, #2473903: PostgreSQL: Fix entity_reference\Tests\Views\EntityReferenceRelationshipTest is already fixed! Moving out of the way :-)

Created #2477251: PostgreSQL: Fix Connection::getFullQualifiedTableName().

Drupal\system\Tests\Bootstrap\DrupalSetMessageTest passes locally and on latest bot run. I'm pretty sure the exception was caused by the random SQLite database is locked errors we see from time to time...

Anyway, we need to open issues for:
- Drupal\views\Tests\Handler\FieldGroupRowsTest
- Drupal\views\Tests\Handler\FieldGroupRowsWebTest

They are failing locally also because of an ordering problem. This is the second or third time we have to fix those :-(

#2477251: PostgreSQL: Fix Connection::getFullQualifiedTableName() is committed! (Required for #2356985: PostgreSQL: Fix tests in migrate_drupal test group)

Opened #2480799: PostgreSQL: Fix views\Tests\Handler\FieldGroupRowsTest (again) because with have failing tests again.

Adding another nice to have issue #1326848: PostgreSQL: Add support for natural joins as this may affect contrib...

And another one that I have overseen #2480959: PostgreSQL: Fix views\Tests\Handler\FieldGroupRowsWebTest (again).
But happy to see #2443651: PostgreSQL: Fix system\Tests\Cache\DatabaseBackendUnitTest just committed which does fix #2443647: PostgreSQL: Fix system\Tests\Cache\ChainedFastBackendUnitTest also :) Thanks all!

We have overseen another one, opened #2484539: PostgreSQL: Fix views\Tests\Handler\FieldFieldTest. As this also fails SQLite I pinged @amateescu for feedback.

@mradcliffe, great news! :) Unfortunately I will be out of office in some hours until the end of the week, sorry. So I think it's best to let the legacy bot do it's job until I have the time (hopefully over the weekend) to test and make the switch. Anyway, will do my best to make that happen ASAP!

I'm working since yesterday to make the switch from legacy PostgreSQL and SQLite bots over to the new drupalci ones. So please DON'T PANIC :) if you see broken test runs around. There are also random exceptions in the new containers no matter of the underlying database, but I think with the help of @amateescu we will know the reason soon :)

Hopefully the bots are back on track now, we will see tomorrow ;) Updated the IS a bit to reflect the new bots...

Another one moved to fixed section (-64 exceptions!) #2477845: PostgreSQL: Fix broken migrate table creation.

Refactoring IS to list all remaining fails for better overview.

• #2477853: PostgreSQL: Add support for reserved field/column names
• #2494415: PostgreSQL: Fix migrate_drupal\Tests\d6\MigrateTaxonomyTermTest
• #2451749: PostgreSQL: Fix views\src\Tests\GlossaryTest.php
• #2443679: PostgreSQL: Fix taxonomy\Tests\TermTest

Moved #2494415: PostgreSQL: Fix migrate_drupal\Tests\d6\MigrateTaxonomyTermTest to fixed. Yay, just 2 open issues on our way to 100% pass left :)

Cleanups!
#2464481: PostgreSQL: deal with case insensitivity is no longer required to have 100% pass as @daffie created a new patch in #2443679: PostgreSQL: Fix taxonomy\Tests\TermTest to just fix the TermTest part (based on the patch in the case sensitive issue). Also moving #1518506: Normalize how case sensitivity is handled across database engines out of nice to have. The decision is to support case insensitivity also for PostgreSQL.

@daffie, stefan.r asked for a PG bot run some weeks ago and all was fine :) However, just started a new run with latest patch applied to make sure, let's see...

UPDATE: bot finished and all seems good :)
FYI: Because of #2296557: [policy] Require PHP 5.5 I changed all bots to run PHP-5.5 as of today! So let's hope we don't see any new fails after the next run...

Okay, we have the first bot results for PHP-5.5 testing. Looks pretty good, but I see a new fail in database connection test for both, PostgreSQL and SQLite. Created #2508777: Database Connection test fails on PHP-5.5.

Adding #2509296: PostgreSQL: Re-think current PDO::ATTR_EMULATE_PREPARES setting to nice to have section.

In preparation of revisit on July 1st I like to mention that there are just two open issues on the way to 100% pass:

• #2477853: PostgreSQL: Add support for reserved field/column names
• #2443679: PostgreSQL: Fix taxonomy\Tests\TermTest

Both are RTBC! So actually I think we made it :)

Moving #2443679: PostgreSQL: Fix taxonomy\Tests\TermTest out of the way (even if there is some discussion for a better fix). The last remaining issue #2477853: PostgreSQL: Add support for reserved field/column names is RTBC again.

Champagne! What should I say :) After 18 months and 85 fixed issues it seems we finally made it - yay!
I don't think we should close this issue, better rename it and work further at the stuff within the nice-to-have section? Anyway, thanks @all!
BTW: Bot is running and will spit out the results within the next hour...

Well, bot finished with 0 fails and with just 2 of the usual exceptions :)
BTW: The root cause of the randoms seems to be identified (table exists exceptions), please see #2508567: DrupalCI SQLite random failures. I think this is something for @dawehner and/or @amateescu ;)

Thank you for the flowers @webchick :) I think we should wait with the commit for a day or so to give contributors the chance the get them on the list. I really don't remember all the helping hands :)
Thanks again for all your support!

Created #2564307: [meta] Remaining Drupal 10/11 PostgreSQL issues as follow up...