Yii2: PostgreSQL 12 support

[MixMe]

Will Yii2 support PostgreSQL 12 ?

`SQLSTATE[42703]: Undefined column: 7 ERROR: column c.consrc does not exist
LINE 10: "c"."consrc" AS "check_expr"
^
HINT: Perhaps you meant to reference the column "c.conkey" or the column "c.conbin".
The SQL being executed was: SELECT
"c"."conname" AS "name",
"a"."attname" AS "column_name",
"c"."contype" AS "type",
"ftcns"."nspname" AS "foreign_table_schema",
"ftc"."relname" AS "foreign_table_name",
"fa"."attname" AS "foreign_column_name",
"c"."confupdtype" AS "on_update",
"c"."confdeltype" AS "on_delete",
"c"."consrc" AS "check_expr"
FROM "pg_class" AS "tc"
INNER JOIN "pg_namespace" AS "tcns"
ON "tcns"."oid" = "tc"."relnamespace"
INNER JOIN "pg_constraint" AS "c"
ON "c"."conrelid" = "tc"."oid"
INNER JOIN "pg_attribute" AS "a"
ON "a"."attrelid" = "c"."conrelid" AND "a"."attnum" = ANY ("c"."conkey")
LEFT JOIN "pg_class" AS "ftc"
ON "ftc"."oid" = "c"."confrelid"
LEFT JOIN "pg_namespace" AS "ftcns"
ON "ftcns"."oid" = "ftc"."relnamespace"
LEFT JOIN "pg_attribute" "fa"
ON "fa"."attrelid" = "c"."confrelid" AND "fa"."attnum" = ANY ("c"."confkey")
WHERE "tcns"."nspname" = 'public' AND "tc"."relname" = 'session'
ORDER BY "a"."attnum" ASC, "fa"."attnum" ASC

Error Info: Array
(
[0] => 42703
[1] => 7
[2] => ERROR: column c.consrc does not exist
LINE 10: "c"."consrc" AS "check_expr"
^
HINT: Perhaps you meant to reference the column "c.conkey" or the column "c.conbin".
)

↵
Caused by: PDOException
SQLSTATE[42703]: Undefined column: 7 ERROR: column c.consrc does not exist
LINE 10: "c"."consrc" AS "check_expr"
^
HINT: Perhaps you meant to reference the column "c.conkey" or the column "c.conbin".

in /app/vendor/yiisoft/yii2/db/Command.php at line 1290
`

in /app/vendor/yiisoft/yii2/db/Schema.php at line 674

[yii-bot]

Thanks for posting in our issue tracker.
In order to properly assist you, we need additional information:

• When does the issue occur?
• What do you see?
• What was the expected result?
• Can you supply us with a stacktrace? (optional)
• Do you have exact code to reproduce it? Maybe a PHPUnit tests that fails? (optional)

Thanks!

_This is an automated comment, triggered by adding the label status:need more info._

[samdark]

Will Yii2 support PostgreSQL 12 ?

Only if someone will contribute this support.

[batyrmastyr]

This particular error is solved by replacing "c"."consrc" AS "check_expr" with pg_get_constraintdef("c"."oid") AS "check_expr".
According to the docs function exists since 8.2 at least. Query works at least in Postgresql 9.4, in any older versions.

Output is a bit different

"c".consrc                                      ((srid > 0) AND (srid <= 998999))
pg_get_constraintdef("c"."oid")          CHECK (((srid > 0) AND (srid <= 998999)))
pg_get_constraintdef("c"."oid", true)    CHECK (srid > 0 AND srid <= 998999)

Plus pg_get_constraintdef returns definition of any constraint, for example FOREIGN KEY (address_id) REFERENCES build_building(address_id) ON UPDATE CASCADE ON DELETE RESTRICT instead of null in consrc.

[samdark]

Sounds like version detection is needed.

[batyrmastyr]

As far as I see, Yii itself doesn't care on check_expr value and you can return null without breaking anything. If somebody really relied on consrc's format, it's possible to simulate it with substr'ing output of pg_get_constraintdef("c"."oid")

By the way pg_get_constraintdef("c"."oid") and pg_get_constraintdef("c"."oid", true) works perfectly in Postgresql 8.4 (the eldest found in repositories) while yii states support of 9.x only, so version check is not required.

[batyrmastyr]

Digging into the docs:

• pg_get_constraintdef is supported since 7.4 (in earlier versions lacked functionality).
• It's possible to get consrc output using pg_get_expr("c".conbin, "c".conrelid) but recommended way is to use pg_get_constraintdef.
• > consrc is not updated when referenced objects change; for example, it won't track renaming of columns.

[samdark]

Since you're into it, do you want to prepare a pull request?

[batyrmastyr]

I'll do it.

[batyrmastyr]

Current progress:

• no fatal errors )
• pg_get_constraintdef breaks 3 tests, I'm going to fix tests.
• Added fetching schema on partitioned tables (introduced in Postgres 10). No tests yet.
• Postgres 12 introduced generated as identity columns (and pg_attribute.attgenerated in schema) as standarts compliant version of sequence / serial, but $column->autoIncrement returns false. Fixing it requires some kind of version detection.

Test results on [email protected], [email protected], [email protected].
There is a regression on 9.4 in CommandTest::testQueryCache with patch, maybe it's some random bug (test run requires 30 minutes so I'll retry later).

[batyrmastyr]

• added test for schema retrieval of partitioned tables for PostgreSQL 10+
• added support for generated as identity columns (including TableSchema::resetSequence)
• added tests for changing default value on serial columns
• removed "deleted logically but not physically" columns from schema. I don't think there is a way to test it in a reliable manner.

I'm a bit hesistant:

• to use Schema::resolveTableName to get reliable sequence name
• to add sequenceName field to pgsql\ColumnSchema as PostgreSQL allows to add several auto-incremental fields on a table.
• to create and drop tables, and to change default values in test cases (the latter _may_ to be error prone). Splitting sql statements by ';' in tests/framework/db/DatabaseTestCase::prepareDatabase doesn't allow to create tables depending on PostgreSQL version in postgres.sql.

[batyrmastyr]

• fixed CommandTest::testQueryCache

All tests pass if they passed before my patch (I did nothing to fix 2 years old CURRENT_TIMESTAMP bug and so on).