Updated: Comment #118

Problem/Motivation

There are a number of significant issues with installing and running Drupal 8 on PostgreSQL.

Proposed resolution

Identify the major issues and implement workarounds or fixes. This should now be done in each individual related issue rather than tacking onto a huge patch.

These issues are listed in the Related Issues block to the right.

Comments #1 - #117 are related to this issue pre-meta status. The latest "full patch" is from Comment #109. This should be used as a started point for the sub-tasks to split off code into new patches.

  1. In transaction context, the first failure makes the transaction abort, and subsequent operations, even if they would normally succeed, also fail with the error "current transaction is aborted, commands ignored until end of transaction block". This differs from the behavior of MySQL. The workaround is to wrap all queries that occur inside a transaction with SAVEPOINT.
  2. Cache tables are missing at the end of the installation. Ensure cache tables exist when deleteAll/DeleteMultiple is called.
  3. Some things such as node/add/page fail because queries such as "select * from users where (uid IN ('autocomplete'))" which succeeds in MySQL, causes an exception in PostgreSQL because 'autocomplete' is not an integer. menu_get_item() needs to be fixed to get the correct route for user/autocomplete.
  4. (Update Feb. 2014. There is work in progress to completely remove menu_get_item() from core, so this issue might get fixed by #2177031: Remove menu_get_item() and every use of it. )

  5. Toolbar menu links do not appear because of the way a orderBy() override for pgsql adds fields in front of fields expected by Entity\Query. The workaround is to add a tag to the query to disable the special features of the override in specific cases.
  6. Prevent pgsql driver from trying to implode primary key fields that are defined by an array (column, key_length) as that syntax is not supported.

Remaining tasks

  • Create patches for known issues in their respective issue.
  • Create issues and relate those issues to this task as new bugs are discovered.

#2010368: Installer can't create new database on PostgreSQL
#2056627: Form API autocomplete is broken for routes
#1003788: PostgreSQL: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar ID
#2111979: PostgreSQL is unable to create a new database

Original report by hosef

When one installs Drupal 8 on a PostgreSQL database, the installation will hang while installing the Image module. You can get around the install hang by using the minimal profile, however, when you go view a page on the site(after trying either profile) you will get:

Page not found
The requested page "/" could not be found.

There don't appear to be any relevent entries in the Apache log, however the following shows up in the PostgeSQL log for each page load:

2013-05-22 22:05:01 PDT STATEMENT:  DELETE FROM cache_menu
2013-05-22 22:05:01 PDT ERROR:  current transaction is aborted, commands ignored until end of transaction block
2013-05-22 22:05:01 PDT STATEMENT:  SELECT 1 FROM information_schema.tables WHERE  (table_catalog = 'drupalcore') AND (table_schema = 'public') AND (table_name = 'cache_menu')
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Anonymous’s picture

Assigned: Unassigned »

Managed to reproduce the issue. And found the source of the issue. Working on a patch.

dcrocks’s picture

Could you give a general indication of what the source is? I am having a similar problem with SQLite.

Anonymous’s picture

Doh! Sorry about that. The culprit seems to be in \Drupal\Core\Database\Connection.php:533-541.

      if ($query instanceof StatementInterface) {
        $stmt = $query;
        $stmt->execute(NULL, $options);
      }
      else {
        $this->expandArguments($query, $args);
        $stmt = $this->prepareQuery($query);
        $stmt->execute($args, $options);
      }

While installing filters, one of the arguments being substituted into the SQL is an array. Since MySQL doesn't pass the query in as a StatementInterface, this isn't a problem because the connection calls $this->expandArguments(...) and is able to explode the array into the appropriate placeholders and replacement values.

PgSQL and SQLite, on the other hand, appear to pass statements as instances of StatementInterface. This route, the arguments never get expanded, so eventually (on \Drupal\Core\Database\Driver\pgsql\Insert.php:48) this results in a situation where $stmt->bindParam(...) is receiving an Array even though the default third argument tells it it should expect a string.

I think that both of these issues will probably be resolved in much the same way.

Anonymous’s picture

Assigned: » Unassigned

Won't be able to work on this issue for a few days and haven't made significant progress past the previous post. Anyone else is free to grab this.

mradcliffe’s picture

I'm getting more failures like this, but earlier in the installation process in Pg 9.2.4. Looking into it a bit tonight.

mradcliffe’s picture

FilterPluginManager calls CacheDecorator inappropriately, which causes a query to fail in the same way.

I split that into #2008644: FilterPluginManager::__construct calls CacheDecorator with incorrect parameters (expires).

I also am having issues with several cache tables not installing, specifically cache_menu, cache_page, and cache_views_results.

Pancho’s picture

Assigned: Unassigned » Pancho
Priority: Normal » Critical

Confirm that there is a number of bugs. Issue is critical per Priority levels of issues.

If the database doesn't preexist and is to be created, I also get a

Fatal error: Call to undefined method Drupal\Core\Database\Driver\pgsql\Connection::exec() in /var/www/d8-test/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php on line 220

Might have something to do with each other. I'll be digging into this right now.

[edit:] no that's not connected but a separate issue, see #2010368: Installer can't create new database on PostgreSQL

steinmb’s picture

FileSize
5.65 KB

PHP 5.4.13 (cli) (built: Apr 16 2013 11:46:33)
PostgreSQL 9.2.4

Confirm this bug. Installation will complete If you, like mention above, use the minimal profile but you end up with a broken site. Got this warning after the installation, though I do not think this is the issue.

Notice: A non well formed numeric value encountered in Drupal\menu_link\MenuLinkStorageController->save() (line 192 of core/modules/menu_link/lib/Drupal/menu_link/MenuLinkStorageController.php).

None of the registered routes works and cache_menu table is missing.

ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT 1 FROM information_schema.tables WHERE  (table_catalog = 'd8') AND (table_schema = 'public') AND (table_name = 'cache_menu')
ERROR:  relation "cache_menu" does not exist at character 13

Manually creating the table allow me to navigate the site though some errors is still found i postgres. At least I'm able to navigate.

ERROR:  relation "cache_page" does not exist at character 13
STATEMENT:  DELETE FROM cache_page
	WHERE  (expire <> '0') AND (expire < '1370598489')
ERROR:  relation "cache_path" does not exist at character 13
STATEMENT:  DELETE FROM cache_path
	WHERE  (expire <> '0') AND (expire < '1370598489')
ERROR:  relation "cache_filter" does not exist at character 13
STATEMENT:  DELETE FROM cache_filter
	WHERE  (expire <> '0') AND (expire < '1370598489')

Create missing cache_menu

CREATE TABLE cache_menu
(
  cid character varying(255) NOT NULL DEFAULT ''::character varying, -- Primary Key: Unique cache ID.
  data bytea, -- A collection of data to cache.
  expire integer NOT NULL DEFAULT 0, -- A Unix timestamp indicating when the cache entry should expire, or 0 for never.
  created integer NOT NULL DEFAULT 0, -- A Unix timestamp indicating when the cache entry was created.
  serialized smallint NOT NULL DEFAULT 0, -- A flag to indicate whether content is serialized (1) or not (0).
  tags text, -- Space-separated list of cache tags for this entry.
  checksum_invalidations integer NOT NULL DEFAULT 0, -- The tag invalidation sum when this entry was saved.
  checksum_deletions integer NOT NULL DEFAULT 0, -- The tag deletion sum when this entry was saved.
  CONSTRAINT cache_menu_pkey PRIMARY KEY (cid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE cache_menu
  OWNER TO postgres;
COMMENT ON TABLE cache_menu
  IS 'Storage for the cache API.';
COMMENT ON COLUMN cache_menu.cid IS 'Primary Key: Unique cache ID.';
COMMENT ON COLUMN cache_menu.data IS 'A collection of data to cache.';
COMMENT ON COLUMN cache_menu.expire IS 'A Unix timestamp indicating when the cache entry should expire, or 0 for never.';
COMMENT ON COLUMN cache_menu.created IS 'A Unix timestamp indicating when the cache entry was created.';
COMMENT ON COLUMN cache_menu.serialized IS 'A flag to indicate whether content is serialized (1) or not (0).';
COMMENT ON COLUMN cache_menu.tags IS 'Space-separated list of cache tags for this entry.';
COMMENT ON COLUMN cache_menu.checksum_invalidations IS 'The tag invalidation sum when this entry was saved.';
COMMENT ON COLUMN cache_menu.checksum_deletions IS 'The tag deletion sum when this entry was saved.';


-- Index: cache_menu_expire_idx

-- DROP INDEX cache_menu_expire_idx;

CREATE INDEX cache_menu_expire_idx
  ON cache_field
  USING btree
  (expire);

Attaching the complete log from postgreSQL during a minimal installation.

EDIT: Added info about what happen if you manually create cache_menu.

Pancho’s picture

Assigned: Pancho » Unassigned

Thanks, @steinmb, for the log!
Unassigning though because I don't have time for it this weekend.

fvideon’s picture

Assigned: Unassigned » fvideon
fvideon’s picture

Assigned: fvideon » Unassigned
Status: Active » Needs review
FileSize
3.16 KB

I don't feel this patch is ready for prime time, but it at least shows the issue and one possible fix. The reason some of the cache_* tables are not being created during setup is tied to having the database transaction support enabled. By design, cache operations are allowed on cache bins that don't yet have the backing cache_* table and exceptions are caught as needed. The set() method is the one that creates the table. This happens inside a catch clause after the first attempt to write to the table fails. (See core/lib/Drupal/Core/Cache/DatabaseBackend.php around line 147).

The problem is that in the transaction context, the first failure makes the transaction abort, and subsequent operations, even if they would normally succeed, also fail with the error "current transaction is aborted, commands ignored until end of transaction block". Most of the cache_* tables are created not in transaction context, but a few such as cache_menu are, and these fail to be created.

The fix I drew up just checks if it's in a transaction, and if so takes an appropriate action to find out if the table exists, and thus avoids aborting the transaction.

One drawback is that there are more queries needed to do this checking compared to just using the try/catch. A possible mitigation would be to use a static array of bool to keep track of which bins have been created, so it would only need to check once for each.

I'm setting this back to unassigned now since I won't be working on it over the weekend. I can probably work on it more next week unless someone else has a better fix, or gets to it first.

Damien Tournoud’s picture

This is so interesting. I wonder how we could have missed that before.

MySQL (or more precisely, InnoDB) basically does an implicit SAVEPOINT before each transaction. From browsing around, it doesn't seem that any of the behavior is configurable, so we will have to either perform an explicit rollback on error (on MySQL) or add a SAVEPOINT before every statement on PostgreSQL.

Pancho’s picture

Status: Needs review » Needs work

Indeed fascinating that we didn't hit this before!
In SQLite this is nicely configurable, defaulting to "ON CONFLICT ABORT" and says this was "the behavior specified by the SQL standard." So even though it seems complicated to handle, by default it seems to match InnoDB's behaviour, and that's why this code works fine on SQLite and only fails on PostgreSQL.
Oracle IMHO also implicitely sets savepoints.Don't know about SQL Server.
So while PostgreSQL's, also non-configurable, "all or nothing" rollback decision is definitely more predictable, it seems to be quite an exception. This might be an argument for adding explicit savepoints for PostgreSQL
On the other side, as long as we don't have PostgreSQL testbots, it might be better to simulate PostgreSQL's behaviour on MySQL (inserting explicit rollbacks) and SQLite (using "ON CONFLICT ROLLBACK").
I guess, we should probably figure out both ways and see how they turn out to work, before making the final decision.
In any case, this should be generally solved, so I'm setting this back to needs work.

Damien Tournoud’s picture

I suspect that we are hitting this now because PostgreSQL recently (in version 9, if I remember correctly) got support for transactional DDL. As a consequence the DDL statements that previously caused the previous (aborted) transaction to be cleared out do not anymore.

We should turn to PostgreSQL users. Given that PDO supports multi-statement queries, inserting a SAVEPOINT before every query will not have an impact on latency, which is a good thing, but might have an impact on the performance of execution of queries in general.

Note that adding a SAVEPOINT is what the psql command line tool does when ON_ERROR_ROLLBACK is enabled:

The on_error_rollback-on mode works by issuing an implicit SAVEPOINT for you, just before each command that is in a transaction block, and rolls back to the savepoint on error.

steinmb’s picture

A shot in the dark, but is this related to #1907230: PostgreSQL - duplicate key value violates unique constraint “drupal_cache_block_pkey” ? Damien should remember those..

Damien Tournoud’s picture

@steinmb: without looking at this in detail, I would say those are probably not related.

fvideon’s picture

Assigned: Unassigned » fvideon

Working on the approach of putting a savepoint before every query..

fvideon’s picture

Status: Needs work » Needs review
FileSize
2.15 KB

This uses the strategy of wrapping most everything with a savepoint (save for other savepoint operations).

I didn't see a way to do the savepoint release or rollback and return the correct results while putting everything in one PDO query. If someone knows a way to do that, we should use it. For now the release or rollback operation is handled as a separate query.

Cheers, Fred

Damien Tournoud’s picture

Shooting in the dark, but I assume that:

SAVEPOINT <name> ; <the query> ; RELEASE <name>

... would work as it should? If the query fails, the rest of the batch will be ignored and an exception will get thrown, and in the exception handle we do the ROLLBACK TO SAVEPOINT stuff.

fvideon’s picture

AFAIK the results of the compound query are the results of the last statement. If it ends with SAVEPOINT RELEASE, fetchAll always returns an empty array. Do you know a way around that?

Damien Tournoud’s picture

Tricky.

I assume that something like this should work, but I don't know the performance implications:

SAVEPOINT <name> ; DECLARE <cursor name> CURSOR FOR <the query> ; RELEASE <name> ; SELECT <cursor name>

... and we most likely have to CLOSE the cursor at one point.

I guess this is where it would start to make sense to subclass PDOStatement for PostgreSQL.

fvideon’s picture

Assigned: fvideon » Unassigned

Good idea to try a cursor. I just did a quick test using this form:

"SAVEPOINT sp; DECLARE acursor CURSOR FOR " .$sql . "; RELEASE SAVEPOINT sp; FETCH ALL acursor"

It works with a valid SELECT query and returns the correct results. With a bogus query it appears to do the right thing and throws a PDO Exception. The problem was that if I call it with the bogus query followed by the valid query, the valid query throws the dreaded "ERROR: current transaction is aborted, commands ignored until end of transaction block".

So it appears that the exception on the bogus query happens on the FETCH which is already outside of the SAVEPOINT, so it still aborts the outer transaction.

fvideon’s picture

Assigned: Unassigned » fvideon
Status: Needs review » Needs work

An update on #22: I was mistaken -- the CURSOR actually does appear to work as we'd want for SELECT. My initial test did not handle the ROLLBACK correctly, but once that was done, the valid query following the bogus query did succeed.

I will take another look at this.

fvideon’s picture

And on to the next quandary: We can handle DELETE, UPDATE and INSERT using this compound query form, but then the PDOStatement rowCount() method always returns zero.

'SAVEPOINT sp; ' . $sql . '; RELEASE SAVEPOINT sp;'

Any thoughts about how to handle that better?

fvideon’s picture

FileSize
3.34 KB

For this iteration the strategy using CURSOR is employed for SELECT queries to allow RELEASE to occur in the same operation. Queries other than SELECT still RELEASE as a separate operation since they can't use CURSOR, and we assume we may sometimes need a valid rowCount, etc.

The strategy employed for closing cursors is to let COMMIT or ROLLBACK do the implicit CLOSE, and just issue a unique name for each cursor.

fvideon’s picture

Assigned: fvideon » Unassigned
Status: Needs work » Needs review
Pancho’s picture

Issue tags: +PostgreSQL, +needs profiling

Nice work. Certainly needs testing, and probably also profiling on PostgreSQL.
Also, I hope this doesn't cover failing transactions on PostgreSQL, which are not even tested by our bots. What was the failing query again in this case?
Need to check if this potentially works out for other dbas just as well, such as Oracle, SQL Server etc.
In the end I'm still not sure we shouldn't mimic PostgreSQL's behaviour on MySQL instead, but @Damien, I trust in your experience.

Damien Tournoud’s picture

It seems like the ODBC driver for PostgreSQL does exactly the same thing. We could look into how they implemented it.

Pancho’s picture

Oh, that would certainly be an argument for this approach...
Where did you find this documented, so you don't necessarily have to investigate this yourself?

Damien Tournoud’s picture

Not sure where it is documented, but I have seen several log extracts that show that it does. For example, this confused user.

Pancho’s picture

This is where it seemed to be introduced to ODBC, and it seems this problem is why psqlodbc introduced savepoint support at all.
Didn't really find much in the Changelog, so we need to dig into the sourcecode.

Pancho’s picture

Here's the initial commit for savepoint support in psqlodbc.
Don't know if it is already actually implemented here. No time for looking into it more closely now, but possibly later tonight.

steinmb’s picture

Status: Needs review » Needs work
FileSize
7.71 KB

Took #25 for a spin, my test environment is identical to what I used in #8. No comments to the approach taken to solve the issue then I'm out of my depth and you need a deep postgres knowledge say anything useful, but I can help out test the issue. Attaching log from Postgres during installation.

#25 improve stuff. We are able finish a standard install but Drupal is still not working properly after the installation is done.

ERROR:  relation "cache_menu" does not exist at character 102
STATEMENT:  SELECT cid, data, created, expire, serialized, tags, checksum_invalidations, checksum_deletions FROM cache_menu WHERE cid IN ('links:tools:page:node:en:1:0')
ERROR:  relation "cache_menu" does not exist at character 30
STATEMENT:  SELECT 1 AS expression
	FROM
	cache_menu cache_menu
	WHERE ( (cid = 'links:tools:page:node:en:1:0') )
ERROR:  relation "cache_toolbar" does not exist at character 102
STATEMENT:  SELECT cid, data, created, expire, serialized, tags, checksum_invalidations, checksum_deletions FROM cache_toolbar WHERE cid IN ('1:en')
ERROR:  relation "cache_toolbar" does not exist at character 30
STATEMENT:  SELECT 1 AS expression
	FROM
	cache_toolbar cache_toolbar
	WHERE ( (cid = '1:en') )
ERROR:  relation "cache_page" does not exist at character 102
STATEMENT:  SELECT cid, data, created, expire, serialized, tags, checksum_invalidations, checksum_deletions FROM cache_page WHERE cid IN ('http://d8.dev/toolbar/subtrees/37kLT6Fe-nUOe4nTNwnGmcxfMq5B3qDO7POgtelSako')

Keep up the good work.

steinmb’s picture

The issue also need a proper summary.

fvideon’s picture

It appears to me (as described in #11) that the errors like 'relation "cache_*" does not exist' are normal, 'by design' behavior, since the cache tables are not created until the first set() method call, but may be queried before that.

steinmb’s picture

That might be so, but then perhaps set() never gets called or it fails in creating the missing tables. The error in #33 (except the log file) is from trying to use Drupal after the installation is finished. Menu and the toolbar both where missing menu links.

Putting the summary tag back, we should follow https://drupal.org/issue-summaries

fvideon’s picture

Status: Needs work » Needs review
FileSize
5.22 KB

Sorry about deleting the summary update tag -- that was unintentional.

A couple of new things:

I was looking at why node/add/page was throwing a PDO Exception on the freshly installed postgresql site, and it came down to a call to drupal_valid_path with argument 'user/autocomplete' which resulted in a query of the form:

select * from users where (uid IN ('autocomplete'))

Oddly (or so it seems to me) this works fine on Mysql, and it returns the row with uid = 0. On Postgresql it throws a PDO exception because 'autocomplete' is not of type integer.

The workaround implemented does a check in user_load() to make sure UID looks like a uint.

Secondly there was a problem with fetching the menu entries for the admin toolbar. This issue ended up being caused by the way the orderBy override in Driver/pgsql/Select.php is adding all the orderBy attributes to the query ahead of those added by Entity\Query. When processing results, Entity\Query expects specific attributes at indices 0 and 1 which are not there. The workaround was to add a tag to the query that causes the orderBy override to skip the step of adding fields. This works as long as Entity\Query does add all the fields it is using for sorting, and as far as I've seen it seems to.

fvideon’s picture

Updated summary

fvideon’s picture

Issue summary: View changes

Updates to reflect current status

steinmb’s picture

Status: Needs review » Needs work
FileSize
67.69 KB
89.26 KB
8.75 KB

PHP 5.4.13 (cli) (built: Apr 16 2013 11:46:33)
PostgreSQL 9.2.4
Attached PostgreSQL log from the installation.

Sorry for the delay...
Hmmm, toolbar is still not working properly. Getting simular error like this after the installation:

ERROR:  relation "cache_page" does not exist at character 102
STATEMENT:  SELECT cid, data, created, expire, serialized, tags, checksum_invalidations, checksum_deletions FROM cache_page WHERE cid IN ('http://d8.dev/toolbar/subtrees/37kLT6Fe-nUOe4nTNwnGmcxfMq5B3qDO7POgtelSako')
ERROR:  relation "cache_page" does not exist at character 102
STATEMENT:  SELECT cid, data, created, expire, serialized, tags, checksum_invalidations, checksum_deletions FROM cache_page WHERE cid IN ('http://d8.dev/toolbar/subtrees/37kLT6Fe-nUOe4nTNwnGmcxfMq5B3qDO7POgtelSako')
ERROR:  relation "cache_page" does not exist at character 102

Toolbar broken

toobar_pg.png

Another thing I notice is that breadcrumb is not working. Not sure if it is related or not.

Breadcrumb not working

breadcrumb_pg.png

fvideon’s picture

Very interesting. I am getting different results. The standard install succeeds for me, and the toolbar and breadcrumbs seem to be working correctly.

Here is more detail about my environment and test procedure. Maybe you can spot the significant difference.

PHP 5.4.15 (cli) (built: May 8 2013 21:26:53)
PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 32-bit

I started with freshly pulled code from 8.x master from about an hour ago with the patch from #37 applied and a one line fix to allow the database creation to work as discussed here: https://drupal.org/node/2010368

I started with no settings.php and no database. I chose a standard install, chose to use PostgreSQL and otherwise used defaults.

When setup completed, the site appears to be working as far as I can tell. I can see the database has 60 tables including cache_menu and cache_toolbar, but not cache_page.

When I use the site, I see these in the PostgreSQL log:

2013-06-24 14:03:23 PDT ERROR:  relation "cache_page" does not exist at character 102
2013-06-24 14:03:23 PDT STATEMENT:  SELECT cid, data, created, expire, serialized, tags, checksum_invalidations, checksum_deletions FROM cache_page WHERE cid IN ('http://localhost/toolbar/subtrees/W4On4fG0UMbbtKNYPmIWxZbYylAipJ-RIketj0ObDUo')

PHP.ini settings that I changed from default settings include:

  • Increase max_execution_time;
  • Added some xdebug config;
  • Enabled the following extensions: php_curl, php_gd2, php_mbstring, php_mysql, php_mysqli, php_pdo_mysql, php_pdo_pgsql, php_pgsql.

My PostgreSQL log from setup is attached.

mradcliffe’s picture

I had the same experience as fvideon in #40. Although same version of postgresql 9.2.4, Mac OSX 10.7, Running MAMP PHP 5.4.4, Apache 2.2.2.

I think the toolbar issue is an unfixed bug. Is that global still used..? I cannot find a follow-up issue for that @todo, but it's from #1137920: Fix toolbar on small screen sizes and redesign toolbar for desktop, which is a 300+ comment mega-issue that I don't really want to read through at the moment.

/*
 * @todo Replace this hack with something better integrated with DrupalKernel
 *   once Drupal's page caching itself is properly integrated.
 */
function _toolbar_initialize_page_cache() {
  $GLOBALS['conf']['system.performance']['cache']['page']['enabled'] = TRUE;
  drupal_page_is_cacheable(TRUE);
steinmb’s picture

Really strange.... Our setups and way to test is more and less identical. I'll fire up my vagrant machine and test on Linux and PostgreSQL 9.1.8.

steinmb’s picture

Ubuntu 12.04 LTS
Apache 2.2.22
PHP 5.4.15
PostgreSQL 9.1.8

Looking good! Standard installation, breadcrumb and toolbar all works as expected. Created a node and got these that prob. are unrelated to this issue.

2013-06-26 11:10:48 UTC ERROR:  invalid input syntax for integer: "" at character 192
2013-06-26 11:10:48 UTC STATEMENT:  SELECT base.nid AS nid, base.uuid AS uuid, base.vid AS vid, base.type AS type, base.langcode AS langcode, base.tnid AS tnid, base.translate AS translate
	FROM
	node base
	WHERE  (base.nid IN  (''))
2013-06-26 11:10:59 UTC ERROR:  relation "cache_page" does not exist at character 102
2013-06-26 11:10:59 UTC STATEMENT:  SELECT cid, data, created, expire, serialized, tags, checksum_invalidations, checksum_deletions FROM cache_page WHERE cid IN ('http://d8.dev/toolbar/subtrees/W4On4fG0UMbbtKNYPmIWxZbYylAipJ-RIketj0ObDUo')
2013-06-26 11:11:23 UTC ERROR:  relation "cache_page" does not exist at character 102
2013-06-26 11:11:23 UTC STATEMENT:  SELECT cid, data, created, expire, serialized, tags, checksum_invalidations, checksum_deletions FROM cache_page WHERE cid IN ('http://d8.dev/toolbar/subtrees/W4On4fG0UMbbtKNYPmIWxZbYylAipJ-RIketj0ObDUo')

I'll re-test with postgres 9.2.4. Perhaps there was something with head or my db. I'll drop it and recreate it and not only drop the tables.

steinmb’s picture

OS X 10.8.x
Apache 2.2.22
PHP 5.4.15
PostgreSQL 9.2.4

Re-tested on PostgreSQL 9.2.4 and this time did it work just fine, so go figure. So where do we move from here? Not sure how to profile this issue.

mradcliffe’s picture

I think the node translation query should be spun off into a new issue, and I think it's fairly major.

I think this issue should try to get installation issues solved for the most part. I don't think a consensus was reached about the performance implications of the approach in #25.

fvideon’s picture

Here is a straw man approach to profiling that I think would give fairly realistic metrics:

  1. Capture SQL operations for a set of common tasks such as: install Drupal, create node, display node, etc. This can be done by configuring PostgreSQL to do verbose logging. Do this both with the SAVEPOINT strategy enabled, and without.
  2. Make a PHP test script to read a set of SQL operations and run them one at a time through PDO.
  3. Profile the test script with each set of SQL ops using a PHP profiler. I have no experience with it, but it looks like some people are using xhprof.

Thoughts?

JimmyAx’s picture

Failed to install for me.

Debian 7
PostgreSQL 9.1
Drupal 8 from Git

An AJAX HTTP error occurred.
HTTP Result Code: 200
Debugging information follows.
Path: http://localhost:8000/core/install.php?langcode=sv&profile=standard&id=1&op=do_nojs&op=do
StatusText: OK
ResponseText: 
( ! ) Fatal error: Call to a member function id() on a non-object in .../core/modules/menu/menu.module on line 221
Call Stack
#TimeMemoryFunctionLocation
10.0001235288{main}(  )../install.php:0
20.0003252680install_drupal(  )../install.php:39
30.85485292536install_run_tasks(  )../install.core.inc:93
41.92647894520install_run_task(  )../install.core.inc:536
51.92667895344_batch_page(  )../install.core.inc:653
61.93037916864_batch_do(  )../batch.inc:65
71.93037916896_batch_process(  )../batch.inc:89
81.93307943736call_user_func_array
(  )../batch.inc:226
91.93307943776_install_module_batch(  )../batch.inc:226
101.93307944160module_enable(  )../install.core.inc:2032
111.93307944208Drupal\Core\Extension\ModuleHandler->enable(  )../module.inc:212
123.19498850944Drupal\Core\Extension\ModuleHandler->invoke(  )../ModuleHandler.php:698
133.19498851208call_user_func_array
(  )../ModuleHandler.php:300
143.19498851376menu_enable(  )../ModuleHandler.php:300

On line 221 in menu.module we got $link->plid = $system_link->id();. A var_dump on $system_link right after entity_load_multiple_by_properties gives an empty array.

mradcliffe’s picture

Re: #46 @fvideon: Yes, that makes sense to me, but I'm not an expert at profiling either. :|

JimmyAx’s picture

steinmb’s picture

@JimmyAx: did you try the patch?

JimmyAx’s picture

The one in #37? That one did not apply so I had to reroll it. This one is without the user_load() fix as that belongs to #1003788: PostgreSQL: PDOException:Invalid text representation when attempting to load an entity with a string or non-scalar ID

The patch is not an acceptable solution. While it appears to be working (I got through the entire installation) it generates a lot of errors in the PostgreSQL log due to a lot of tables missing (mostly cache_*). I'm attaching the log from the start of the installation until the first page of the site.

fvideon’s picture

Note that if you run through the D8 install with MySQL and look at PDOExceptions, you will see a lot of them in the form of

SQLSTATE[42S02]: Base table or view not found: 1146 Table '<tablename>' doesn't exist

I didn't see a way to make MySQL log these. One difference with PostgreSQL is that they do get logged with the default logging configuration.

Josh Waihi’s picture

Re @JimmyAx last comment (#51)

ERROR:  relation "variable" does not exist at character 19
STATEMENT:  SELECT value FROM variable WHERE name = 'install_task'

I found this issue came from Drupal attempting to find the install task. It doesn't even check to see if the table exists before querying it, it assumes the failure means that Drupal is not installed yet. Which is true. But its still a dirty hack. See install_verify_completed_task

I also get the same sort of issues, primarily with cache table. If I change the default cache handler to memory instead, I can install Drupal, but I still get a partially installed interface (missing menu items etc).

steinmb’s picture

@Josh sounds correct. #51 contain some code that allow (at least it did) you to install Drupal. You even end up with a working installation. It's not perfect and needs work. I'm not even sure if it's the right solution....

Josh Waihi’s picture

AFAIK, PostgreSQL has always had transactional DDL statements so I don't think that is the underlying cause.
IMO, transactional statements isn't preferable either. Why should a statement fail and still allow the transaction to commit?

Josh Waihi’s picture

FileSize
58.36 KB

I recorded all the postgres statements from an attempted install of Drupal 8. I parsed out all CREATE, ERROR, BEGIN & ROLLBACK statements and have attached them.

There are 10 tables that generate query failures: key_value, cache_config, cache, cache_bootstrap, cache_views_info, cache_field, cache_entity, cache_block, cache_views_results and cache_menu.

In most cases, it appears that Drupal attempts to query these tables before they've been actually created in the database. However, the cache_block table simply does not get created. Nor is there an error from PostgreSQL explaining why. This would suggest that Drupal opted not to install the table for some other reason.

xjm’s picture

xjm’s picture

xjm’s picture

Issue tags: +Testbot environments
mradcliffe’s picture

Assigned: Unassigned » mradcliffe

ah ha!

Drupal\Core\Database\Driver\pgsql\Schema.php is missing. pgsql driver does not implement Schema.php. I am confused about how tables are being created without it, but that's the problem with cache and probably other issues as well.

I'll work on this today.

mradcliffe’s picture

Wait, my git directory was all messed up. Welp.

mradcliffe’s picture

Status: Needs work » Needs review
FileSize
1.02 KB
5.37 KB

I added a band-aid fix in DatabaseBackend.php which ensures that the cache tables exist if the method fails. This gets me through the install again.

Sorry for the false alarm re: Schema.php.

mradcliffe’s picture

Status: Needs review » Needs work

#autocomplete_path is calling the following query and is uncaught PDOException in Database\Statement.php:

I'm not familiar enough to trace through to where this is. I know it's an access check, maybe called somewhere in url() and the access check behind UrlGenerator.

SELECT base.uid AS uid, base.uuid AS uuid, base.name AS name, base.langcode AS langcode, base.pass AS pass, base.mail AS mail, base.theme AS theme, base.signature AS signature, base.signature_format AS signature_format, base.created AS created, base.access AS access, base.login AS login, base.status AS status, base.timezone AS timezone, base.preferred_langcode AS preferred_langcode, base.preferred_admin_langcode AS preferred_admin_langcode, base.init AS init
	FROM 
	users base
	WHERE  (base.uid IN  ('autocomplete')) 
mradcliffe’s picture

mradcliffe’s picture

Issue summary: View changes

summary-related-issues issue link changed

mradcliffe’s picture

Issue summary: View changes

Add related blocker (?)

mradcliffe’s picture

Issue summary: View changes

Add to resolution section

mradcliffe’s picture

The cache issues are related to #2067429: Fix installer to not use Drupal\Core\Cache\DatabaseBackend, which probably points out an incompatibility with invoking cache backends.

mradcliffe’s picture

Status: Needs work » Needs review
FileSize
1.29 KB
5.76 KB

This patch adds comments to the quick fix in \Drupal\Core\Cache\DatabaseBackend.php.

Discussed with @effulgentsia, @alexpott, @msonnabaum, @xjm, and myself, and got buy-in for the quicker fix as long as this is passes testing (manual too) even if we don't know the exact root cause yet.

@msonnabaum mentioned that it would be interesting to create a separate cache backend that uses hstore in the future.

burningdog’s picture

I can't comment on the patch, because I don't understand the code, but after applying it installation works for me.

Postgres: 9.2.4
PHP: 5.3.27
Mac OS 10.6.8

Thank you! The only error I have in the drupal error log is generated by cron at http://drupal8.local/core/install.php?langcode=en&profile=standard and reads:

PDOException: SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ...id WHERE d.sid IS NULL OR d.reindex <> 0 ORDER BY d.reindex ... ^: SELECT DISTINCT n.nid FROM {node} n LEFT JOIN {search_dataset} d ON d.type = :type AND d.sid = n.nid WHERE d.sid IS NULL OR d.reindex <> 0 ORDER BY d.reindex ASC, n.nid ASC LIMIT 100 OFFSET 0; Array ( [:type] => node_search ) in Drupal\node\Plugin\Search\NodeSearch->updateIndex() (line 293 of core/modules/node/lib/Drupal/node/Plugin/Search/NodeSearch.php).
mradcliffe’s picture

@burningdog, this issue looks like it was introduced by #2003482: Convert hook_search_info to plugin system, which was a commit from last week, but in fact it is also a bug in Drupal 7 node_update_index(). The code was taken pretty much straight into the new NodeSearch class.

I did not find any current issue when I searched for node_update_index or search. Created #2087169: NodeSearch::updateIndex does not include order by column in select.

terikon’s picture

I confirm that #62 solved for me following installation error, on Windows and MySQL (not PostgreSQL):

SQLSTATE[42S02]: Base table or view not found: 1146 Table '***.cache_field' doesn't exist.

thekevinday’s picture

I can confirm that the problem happens with 8.0-alpha3, but ...

As of the October 7, 2013, this issue does not appear in postgresql. I have not applied the patch here and the sight is now functional.

I do not know when the problem went away, but the last commit I pulled was:
- http://drupalcode.org/project/drupal.git/commit/04f662ff72c3030615e7e454...

webchick’s picture

Status: Needs review » Postponed (maintainer needs more info)

Interesting! Can anyone else confirm?

mradcliffe’s picture

Status: Postponed (maintainer needs more info) » Active

Unfortunately I cannot confirm this with my current HEAD (commit ae7c20c7b5f1d65e66592328946a27eed808c434)

I haven't reached any transaction stoppage due to cache table creation, but comment_entity_statistic table creation borked and failed the install.

Postgres 9.2.4

ERROR:  syntax error at or near "Array" at character 494
STATEMENT:  CREATE TABLE comment_entity_statistics (
		entity_id bigint CHECK (entity_id >= 0) NOT NULL default 0,
		entity_type varchar(255) NOT NULL default 'node',
		field_id varchar(255) NOT NULL default 'node__comment',
		cid int NOT NULL default 0,
		last_comment_timestamp int NOT NULL default 0,
		last_comment_name varchar(60) NULL,
		last_comment_uid bigint CHECK (last_comment_uid >= 0) NOT NULL default 0,
		comment_count bigint CHECK (comment_count >= 0) NOT NULL default 0,
		PRIMARY KEY (entity_id, Array, Array)
	)
mradcliffe’s picture

I did a bisect and stepped back to 5089f20fc96295330aefb8587ca06c300b070de6, but that also failed. That commit didn't do anything. I checked out the commit above @thekevinday mentioned

commit 04f662ff72c3030615e7e454eeada7277a72e360 (HEAD)

I ran into the same exact issue in the previous comment under the standard install.

I tried a minimal install with the same commit, and this installed correctly. @thekevinday, did you try the standard install or just the minimal install?

Edit: Notice: A non well formed numeric value encountered in Drupal\menu_link\MenuLinkStorageController->save() (line 219 of core/modules/menu_link/lib/Drupal/menu_link/MenuLinkStorageController.php).

Edit 2: I enable menu, path, and toolbar. No menus appear in toolbar similar to previous comments in this issue

mradcliffe’s picture

Status: Active » Needs work
FileSize
5.76 KB

Setting back status.

I re-rolled #66 and ran into the same issue. It looks like some other commit from the last month killed support for comment_entity_statistics.

I've uploaded the re-rolled patch after fixing a conflict that occurred when rebasing to origin/8.x on my local branch. Doesn't need to be tested.

mradcliffe’s picture

It looks like postgres database driver in Drupal doesn't support this, which was introduced in 539 comment MONSTER MONSTER MONSTER Issue #731724

'primary key' => array('entity_id', array('entity_type', 32), array('field_id', 32)),

Edit: mysql and sqlite drivers have a createKeySql() implementation, but pgsql driver does not. Introduced in commit f4a58c3f3af1b5cb475ad427ab1dd69df926515f, which even has a title of "Postgres" :(

mradcliffe’s picture

Okay, fixed the issue with comment_entity_statistics by writing another helper function to basically ignore the schema syntax. I guess fillfactor could be used, but that's up to debate.

I ran into this posting a comment, which seems to me that core still has weird menu / routing issues.

PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "reply" LINE 5: WHERE (base.cid IN ('reply')) ^ in PDOStatement->execute() (line 54 of /Applications/MAMP/vhosts/drupal-git/core/lib/Drupal/Core/Database/Statement.php). 

So pretty much the state of postgres just got worse in the last couple of weeks :*(

mradcliffe’s picture

I think menu_get_item() needs to be solved completely before any more work can be done i.e. kill it with fire. I can't find all of the tasks that nuke it, but the one we're interested in now is the call in menu_get_custom_theme().

Is there a meta task for menu_get_item or an issue for menu_get_custom_theme yet?

mradcliffe’s picture

Status: Needs work » Needs review

Comment issue is not related to this issue because it does not prevent Drupal 8 from being installed. Needs follow-up created.

I'm setting this to needs review as #76 is back to installable state.

thekevinday’s picture

Sorry, its clear that I miscommunicated with my last post.
What I meant was up to the said commit in the repository and now things work.
Not that specific commit.

As for the install I used, I am using minimal install.

Okay, so I managed to identify where things started to work in the 8.x branch:
- http://drupalcode.org/project/drupal.git/commit/ef2e45b0e86fc6863b8ee6d3...
- With this exact commit, things started to work in drupal 8 on postgresql.
- All commits prior to this commit on the 8.x branch are applied in my testing.

I tested by doing the following:
- git reset ef2e45b --hard from the 8.x branch and then installing drupal 8 in minimal install mode.
- git reset 61fcb6f --hard from the 8.x branch and then installing drupal 8 in minimal install mode.

(61fcb6f is the commit immediately before the working commit of ef2e45b in the 8.x branch).

I have not done any testing in standard install mode.

mradcliffe’s picture

Title: Drupal 8 cannot be installed on PostgreSQL » Drupal 8 cannot be installed on PostgreSQL with standard install profile

Updated title.

mradcliffe’s picture

Title: Drupal 8 cannot be installed on PostgreSQL with standard install profile » Drupal 8 cannot be installed on PostgreSQL

Actually, sorry for the spam, but I'm going to remove that title change. My testing with the minimal install profile revealed that no menus were available after installing the toolbar module.

thekevinday’s picture

I just tested #76 and it seems to fix the remaining issues/warnings/errors.

Update:
- Prior to the patch in #76, menus do not appear.
- After applying the patch in #76, menus do appear.

thekevinday’s picture

Issue summary: View changes

Added related issue #1003788

dlu’s picture

Attempted to install D8 this morning (fresh pull of HEAD, not patches) and got the attached error. Not sure if this is the right place to post it. I'll look into it more this afternoon.

Screen shot of AJAX error during PostgreSQL install.

mradcliffe’s picture

The patch in #76 addresses that issue, dlu. Can you test?

dlu’s picture

I'll test it this afternoon. I'm working on https://drupal.org/node/1013034, thanks for this insight.

fvideon’s picture

I saw the error in #83 last week even with the patch from #76. I noticed that it happened consistently when installing the taxonomy module. This was the configuration:

Windows 8
PHP 5.5 x64 TS (Binary marked "experimental" on php.net)
Apache 2.4 x64

The problem went away when I switched to:

PHP 5.4.20 x86
Apache 2.4 x86

PostgreSQL 9.2 was the same in both cases.
I attributed it to running the experimental PHP build.

dlu’s picture

Issue summary: View changes

Updated resolution with comment_entity_statistics primary key issue, and removed general testing. At this point I think general testing is done.

dlu’s picture

I've successfully installed today's version of D8 (as of 0f8b4e794e1e4c453185f2856b768aaeaa86f83b) with the patch from #76 using PostgreSQL (9.3.1) and PHP 5.5.4 (Homebrew) on OS X 10.9. I'm working on https://drupal.org/node/1013034 and would be happy to do anything that would help with testing along the way. I didn't see any problems as the Taxonomy module installed.

Not sure if this means it can go to RTBC or not. [edit: nope, should have read the issue summary more closely.]

dlu’s picture

One more problem. Currently can't create a database, these patches only work if you're using an existing database. See #2111979: PostgreSQL is unable to create a new database.

JimmyAx’s picture

Title: Drupal 8 cannot be installed on PostgreSQL » Drupal cannot be installed on PostgreSQL
Status: Needs review » Needs work
Issue tags: +Needs backport to D7

This also affects 7.x as I was unable to install the latest dev snapshot to debug an issue.

Without the patch I get SQLSTATE[42P07]: Duplicate table: 7 ERROR: relation "cache_tags" already exists. With the patch I get the exact same error message so it looks like things have changed.

JimmyAx’s picture

Issue summary: View changes
mradcliffe’s picture

I fetched and rebased my local branch to 8.x, but was not able to reproduce any oddities. I still have a working installation. Adding a re-roll shortly.

mradcliffe’s picture

Issue summary: View changes
Status: Needs work » Needs review
FileSize
7.82 KB

Re-roll.

mgifford’s picture

91: 2001350-pgsql-fixes-91.patch queued for re-testing.

Status: Needs review » Needs work

The last submitted patch, 91: 2001350-pgsql-fixes-91.patch, failed testing.

mradcliffe’s picture

Status: Needs work » Needs review

I ran this test locally after fetching and rebasing to origin/8.x on my postgres branch, and it passed with flying colors. I did a git diff origin/8.x and the resulting patch was the same as patch in #91.

Let's try the test again.

mradcliffe’s picture

91: 2001350-pgsql-fixes-91.patch queued for re-testing.

Status: Needs review » Needs work

The last submitted patch, 91: 2001350-pgsql-fixes-91.patch, failed testing.

mradcliffe’s picture

I also confirmed that the test passes locally in MySQL via MAMP.

Not sure why the heck this is happening. Currrently HEAD is failing tests anyway, but not this test.

mgifford’s picture

I'm not sure.. I should be able to find this here, right:
$ locate ValidationTest.php | grep form

I can't write unit tests, but maybe someone else can translate this error:

The test did not complete due to a fatal error.
Completion check
ValidationTest.php
195
Drupal\system\Tests\Form\ValidationTest->testCustomRequiredError()

dcrocks’s picture

The testing log showed

Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 268435456 bytes) in /var/lib/drupaltestbot/sites/default/files/checkout/core/lib/Drupal/Core/Database/Connection.php on line 336
FATAL Drupal\system\Tests\Form\ValidationTest: test runner returned a non-zero error code (255).
Is this from the testing environment?

mradcliffe’s picture

Status: Needs work » Needs review

91: 2001350-pgsql-fixes-91.patch queued for re-testing.

mradcliffe’s picture

Yes, that's from the test environment. The test fail in HEAD (not ValidationTest) is passing now so let's try again.

Status: Needs review » Needs work

The last submitted patch, 91: 2001350-pgsql-fixes-91.patch, failed testing.

bzrudi71’s picture

To hopefully get any progress on PostgreSQL fixing I created a new META issue queue for PostgreSQL, please see #2157455: [Meta] Make Drupal 8 work with PostgreSQL or remove support from core before release for details.

Regarding patch in #76 - It currently still applies to HEAD and PostgreSQL can be installed using standard profile. (And works afterwards :-))
However, PostgreSQL log still shows some errors with current patch and PostgreSQL Version 9.3 which should be fixed...

bzrudi71’s picture

deimos’s picture

91: 2001350-pgsql-fixes-91.patch queued for re-testing.

The last submitted patch, 91: 2001350-pgsql-fixes-91.patch, failed testing.

InternetDevels’s picture

Status: Needs work » Needs review
FileSize
8.63 KB
1007 bytes

Re-roll with little fixes according to latest changes in core.

mradcliffe’s picture

Assigned: mradcliffe » Unassigned

I probably shouldn't be assigned, forgot to do this since September.

deimos’s picture

Status: Needs review » Reviewed & tested by the community
FileSize
0 bytes

Hi there guys,
previously I was working on Drupal 8 database driver for Oracle DB (#2037119: Roadmap for Drupal 8 oracle driver) and got a lot "funny" situation with Drupal database layer.
As result, I spent few hours to analyse this issue, all it's comments and pending patches.

Main idea of the issue (Drupal 8 installation on PostgeSQL) is fixed and installation on existing database works fine after applying the patch.
If apply #76 patch from #203955: Create database at installation time then installation works without existing database.

According to issue summary for me:
1. Transaction issue is solved.
2. All needed cache tables are creating.
3. Problem with autocomplete doesn't appears.
4. Toolbar links are present and breadcrumbs works are working corretly.
5. Problem with primary key fields that are defined by an array isn't appeared. comment_entity_statistics table was created with all defined indexes during installation.

I attached default postgresql log.

About errors in log-file: I am agree with #52. All errors is related to queries to missing tables and it isn't problem of PostgreSQL database driver because MySQL driver causes the same errors but just doesn't write them to log.

And we realy need this changes (patch from comment #107) to be committed to core because currently installation on PostgreSQL doesn't work at all. So, I set RTBC.

deimos’s picture

Oops, log-file is empty, attach correct one.

webchick’s picture

Assigned: Unassigned » Crell

Great work on this, folks!!! So happy to see this green. :)

I was going to commit this today, but there's quite a bit of DB internals here that I don't quite understand, so assigning it to Crell for a lookover.

webchick’s picture

Two small things to factor into the next re-roll:

  1. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php
    @@ -31,6 +31,13 @@ class Connection extends DatabaseConnection {
    +  private $_cursor_ctr;
    
    @@ -52,6 +59,8 @@ public function __construct(\PDO $connection, array $connection_options) {
    +    $this->_cursor_ctr = 0;
    

    We don't abbreviate variable names; should be spelled out "_cursor_counter".

  2. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php
    @@ -318,6 +318,21 @@ protected function _createKeySql($fields) {
    +  protected function _createPrimaryKeySql($fields) {
    

    This function needs docs.

Crell’s picture

Status: Reviewed & tested by the community » Needs work
  1. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php
    @@ -31,6 +31,13 @@ class Connection extends DatabaseConnection {
       /**
    +   * Last used cursor number.
    +   *
    +   * @var type int
    +   */
    +  private $_cursor_ctr;
    

    What Angie said. Also, default to using protected, not private.

  2. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php
    @@ -128,11 +137,16 @@ public function query($query, array $args = array(), $options = array()) {
    +      if (strpos($stmt->queryString,'SAVEPOINT mimic_innodb_not_released;') !== FALSE) {
    +        $this->connection->prepare("RELEASE SAVEPOINT mimic_innodb_not_released")->execute();
    +      }
    +
    

    I don't know PostgreSQL at all, so I'm not entirely sure what's going on here. However, string parsing SQL is what we try extremely hard to avoid. In what case is this even necessary, and why not do it in a more structured fashion?

  3. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php
    @@ -143,6 +157,10 @@ public function query($query, array $args = array(), $options = array()) {
         catch (\PDOException $e) {
    +      if (preg_match("/SAVEPOINT (mimic_innodb_(released|not_released))/",$stmt->queryString,$matches)) {
    +        $this->connection->prepare("ROLLBACK TO SAVEPOINT " . $matches[1])->execute();
    +      }
    

    As above.

  4. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php
    @@ -170,7 +188,27 @@ public function prepareQuery($query) {
    -    return parent::prepareQuery(preg_replace('/ ([^ ]+) +(I*LIKE|NOT +I*LIKE) /i', ' ${1}::text ${2} ', $query));
    +    $query = preg_replace('/ ([^ ]+) +(I*LIKE|NOT +I*LIKE) /i', ' ${1}::text ${2} ', $query);
    

    Ugh, I didn't even realize we had this in here.

  5. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php
    @@ -170,7 +188,27 @@ public function prepareQuery($query) {
    +    // While in transaction context, put a SAVEPOINT around every query that isn't
    +    // itself a SAVEPOINT operation.  This means that a failed query can't cause
    +    // the transaction to abort, which mimics the behavior of innodb.
    +    if ($this->inTransaction() &&
    +            (stripos($query,'SAVEPOINT ') === FALSE) &&
    +            (stripos($query,'RELEASE ') === FALSE)) {
    +      if (preg_match('/^[\s]*SELECT /i', $query)) {
    +        // In the case of SELECT the SAVEPOINT can also be released in the same
    +        // query.  Otherwise the RELEASE is a separate query.
    +        $csr = 'csr' . $this->_cursor_ctr++;
    +        $query = 'SAVEPOINT mimic_innodb_released; DECLARE '. $csr .' CURSOR FOR ' .
    +                 $query . '; RELEASE SAVEPOINT mimic_innodb_released; FETCH ALL ' . $csr;
    +      }
    +      else {
    +        $query = 'SAVEPOINT mimic_innodb_not_released; ' . $query;
    +      }
    +    }
    +
    +    return parent::prepareQuery($query);
    

    It looks like a lot of this code here and above is tucking multiple query statement into a single query string. I don't know PostgreSQL enough to know if that's the "right" way to do things, but I do know that all this string parsing is not the "right" way in Drupal.

    If there's a need to flag and track before/after concatenated queries, then we need an object that will do so. That is, we probably need to add a Postgres-specific set of methods (with their own interface and possibly trait or composable class) to the various query objects, and possibly even wrap the query string into an object to track internally. That way at least we can just deal with a few booleans that get compiled down rather than all this string parsing.

    As an aside, can someone explain to me why this is necessary? I mean I see the comment, but I don't know what triggered this to be necessary in the first place. (Again, Postgres illiterate here.)

  6. +++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php
    @@ -318,6 +318,21 @@ protected function _createKeySql($fields) {
    +  protected function _createPrimaryKeySql($fields) {
    

    Methods should never have a leading underscore. That's PHP 4 legacy that we have long since left behind. (If this is extending another method somewhere, that's a bug that should be fixed.)

  7. +++ b/core/lib/Drupal/Core/Entity/Query/Sql/Query.php
    @@ -206,6 +206,7 @@ protected function addSort() {
    +    $this->sqlQuery->addTag('pgsql_no_addfield_on_orderby');
    

    There should be no DB-specific code outside of the drivers themselves. Client code should never have to care what DB its on.

andypost’s picture

andypost’s picture

+++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Update.php
@@ -65,8 +65,7 @@ public function execute() {
-    $this->connection->query($stmt, $options);
...
-    return $stmt->rowCount();
+    return $this->connection->query($stmt, array(), $options);

+++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php
@@ -128,11 +137,16 @@ public function query($query, array $args = array(), $options = array()) {
+          $stmt->allowRowCount = TRUE;

The rowCount() change caused by #2146733: Select queries should not use rowCount() to calculate number of rows should be fixed in #2167507-16: Fix rowCount query usage in pgsql and sqlite drivers

webchick’s picture

Hey there folks, I just committed #2167507: Fix rowCount query usage in pgsql and sqlite drivers so we can factor that code out of the next re-roll.

Crell’s picture

Assigned: Crell » Unassigned
mradcliffe’s picture

Title: Drupal cannot be installed on PostgreSQL » [meta] Drupal cannot be installed on PostgreSQL
Category: Bug report » Task
Issue summary: View changes

I've changed this into a meta issue to address the four (or more) unfixed issues referenced in the issue summary separately.

I think some of those issues can be fixed and committed knowing that install state is still "broken". It's broken now so it won't be any worse off.

mradcliffe’s picture

Progress so far on sub-tasks:

  1. Created separate patches for #2181283: Prevent pgsql driver from trying to implode primary key fields that are defined by an array and #2181289: Prevent exceptions on missing cache table during cache clear for pgsql, which are currently needs review waiting on the mysql test bot. They are also needs review for any one doing patch review. Once both of those are done, then both can be marked RTBC respectively.
  2. Tried to implement the savepoint/release thing in #2181291: Prevent a query from aborting the entire transaction in pgsql, but had limited success. It needs a lot of work, rework, and/or scrapping and redoing.
mradcliffe’s picture

All sub issues are currently "Needs Review" and waiting on code review + manual testing. The manual testing part requires integrating ALL patches because they're all necessary to get through the installation.

My process for manual testing is as follows:

  1. Create branch for each issue based on 8.x and commit patch respectively: git checkout 8.x; git checkout -b postgres-ISSUENUM; curl -L PATCHURL | git apply; git commit -a
  2. Create merge branch based on 8.x: git checkout 8.x; git checkout -b postgres-merge-test
  3. Merge in each branch: git merge postgres-ISSUENUM
  4. Drop public schema, create new public schema
  5. Delete settings.php and config directory
  6. Install Drupal with standard install profile.
bzrudi71’s picture

Thanks mradcliffe for splitting the patch for better overview! I will do manual testing and review in the next days to see where we are. The 'old' patch worked for months, so I think the new issues will do also, but let's see...

bzrudi71’s picture

bzrudi71’s picture

As all required patches to make D8 install on PG again are in, I'm going to close this one as fixed :-)
All other remaining issues we have to take care of are now in #2157455: [Meta] Make Drupal 8 work with PostgreSQL or remove support from core before release. If one disagrees with that, please change status accordingly...

mradcliffe’s picture

The testing bot uses testing profile, not standard profile.

#2181291: Prevent a query from aborting the entire transaction in pgsql still blocks installing on the standard profile. It's critical in and of itself. Do you want to keep this issue closed?

bzrudi71’s picture

@mradcliffe Hmmh, D8 installs fine locally even in standard profile without #2181291: Prevent a query from aborting the entire transaction in pgsql on PG 9.3 for me. To make sure I'm not working with any testing code I did a git reset --hard origin/8.x first and it still works. All I can see from the PG logs is that the related error about the aborted transactions exists, but does no longer prevent from installing.

ERROR:  relation "cache_menu" does not exist at character 13
STATEMENT:  DELETE FROM cache_menu
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT 1 FROM information_schema.tables WHERE  (table_catalog = 'd8') AND (table_schema = 'public') AND (table_name = 'cache_menu') 
ERROR:  relation "cache_menu" does not exist at character 13
STATEMENT:  DELETE FROM cache_menu
ERROR:  current transaction is aborted, commands ignored until end of transaction block

Can you crosscheck please. If it fails for you we need for sure leave this open ;-)

mradcliffe’s picture

Do you have a working site afterward with menus?

mradcliffe’s picture

Issue summary: View changes

I successfully installed Drupal without it, and I do have menus. :-)

Adjusted issue summary.

bzrudi71’s picture

Issue summary: View changes
FileSize
135.36 KB

In short, yes! Site is working fine (screenshot attached)... BTW The broken menu was fixed in #2181285: Fix orderBy override in pgsql driver for toolbar menu links

ILMostro’s picture

I'm struggling to get drupal7 installed with postgresql9.3 on Fedora20. Searching for ERROR: relation "variable" does not exist at character 19 led me to this discussion; I don't see if this is resolved or whether it was even pin-pointed to a particular file, code base, function. Again, during install pg_log shows:

ERROR: relation "variable" does not exist at character 19
STATEMENT: SELECT value FROM variable WHERE name = 'install_task'

Installation fails, and "The requested page could not be found" with the Drupal Header showing on page.

cilefen’s picture