sql: Drupal compatibility with CockroachDB

[knz]

So Drupal is a very popular open source CMS which is used to power many public web sites.

Currently Drupal does not work with CockroachDB.

At least one issue is the following: sql: Support 'COMMENT ON TABLE foo is 'bar' #19472

Jira issue: CRDB-5870

[knz]

cc @awoods187 for prioritization

[knz]

Issue #19472 was closed but we do not know if that was sufficient to enable compatibility.

[lotyrin]

Just spun up Drupal and Cockroach, currently the install fails as a consequence of #20782

[jchristi]

#20782 has been resolved. @lotyrin is Drupal able to install now?

[lotyrin]

Building from git on OSX and in Docker (using the builder image) didn't work out for me. Once there's an unstable docker image with this change in it, I can easily try again.

[lotyrin]

Ironed out OSX build from git (had to use go get to get paths right).

@jchristi The bytea session variable seems to work, but Drupal sees it set to hex and tries to set it to escape at the schema level (ALTER DATABASE "drupal" SET bytea_output = 'escape';) which doesn't seem to be supported.

There is also a message regarding detecting encoding - "Drupal could not determine the encoding of the database was set to UTF-8", which is the result of SHOW server_encoding; and that variable being unavailable.

Further, it makes a call to pg_advisory_lock() which is not available.

[knz]

Thank you for trying it out! I have filed the related issues:

• sql: support database-level session variable defaults sql: support database-level session variable defaults #26947
• sql: support the session variable server_encoding sql: support the session variable server_encoding #26948

We also have a previous issue for pg_advisory_lock:

• sql: fill out pg_advisory_lock stubs sql: fill out pg_advisory_lock stubs #13546

@lotyrin can you detail how Drupal uses pg_advisory_lock()? What does it expect to do with it?

[lotyrin]

@knz No problem! Re: pg_advisory_lock(), that is a really good question, I will see if I can find out.

[lotyrin]

Drupal, when initializing a schema for use, adds a couple compatibility shim functions. Drupal.org was experiencing test failures due to race conditions within Postgres' implementation of CREATE OR REPLACE FUNCTION so they wrapped the sequence with pg_advisory_lock().

Call to pg_advisory_lock()
Issue
Test results with race condition

If cockroach supports atomic CREATE OR REPLACE FUNCTION where two simultaneous queries are both guaranteed to complete (not distinct test-if-exists followed by either a create or a replace, where the conditions of the initial test might have changed) then this lock is superfluous.

[knz]

what are these compatibility shim functions that drupal needs to add? Is this limited to the two functions rand() and substring_index()? If CockroachDB were to predefine these two, would there be a way to teach Drupal to avoid setting up these user-defined functions altogether?

[lotyrin]

Yep, just those two. Drupal shouldn't be creating any functions other than those, and it does check if they exist before trying to create them.

[lotyrin]

Modifying Drupal to skip these checks / functions and proceed further into in the installation, the next issue is that it checks the variable max_identifier_length.

If I skip that, I get an error about COMMENT ON TABLE not being implemented (Drupal checks if the comment is successful and doesn't continue). If I skip that it tries to COMMENT ON COLUMN with the same results.

If I skip that, it tries to use SAVEPOINT.

All in all, Drupal's pgsql driver seems to be fairly heavily invested in PostgreSQL implementation detail.

[knz]

Everything you've mentioned so far except for the last point we were planning to work on anyway. What does Drupal do with savepoints? -- Sent from my Android device with K-9 Mail. Please excuse my brevity.

[lotyrin]

The use of SAVEPOINT was added here: https://www.drupal.org/project/drupal/issues/2487269

It seems other RDBMS they support allowed continued use of a transaction in the case that queries have failed, but Postgres does not, so they wrap every query with a savepoint to emulate the behavior.

[knz]

Thank you heartily for performing this investigation. This gives us a lot to chew on, but at least now we understand the task at hand. -- Sent from my Android device with K-9 Mail. Please excuse my brevity.