Akonadi SchemaVersionTable is broken with PostgreSQL Version 12 changes

[Stuart]

Dear development team,

As per the PostgreSQL release documents:
E.1. Release 12
https://www.postgresql.org/docs/12/release-12.html#id-1.11.6.5.5

PostgreSQL Version 12 removes:

obsolete pg_constraint.consrc column
obsolete pg_attrdef.adsrc column


Akonadi executes this query that fails due to changes in the PostgreSQL
schema

DB=akonadi USER=akonadi SELECT pg_attribute.attname,
pg_attribute.atttypid::int, pg_attribute.attnotnull, pg_attribute.attlen,
pg_attribute.atttypmod, pg_attrdef.adsrc FROM pg_class, pg_attribute LEFT
JOIN pg_attrdef ON (pg_attrdef.adrelid = pg_attribute.attrelid AND
pg_attrdef.adnum = pg_attribute.attnum) WHERE
pg_table_is_visible(pg_class.oid) AND pg_class.relname =
'schemaversiontable' AND pg_attribute.attnum > 0 AND pg_attribute.attrelid
= pg_class.oid AND pg_attribute.attisdropped = false ORDER BY
pg_attribute.attnum
DB=akonadi USER=akonadi SELECT ERROR:  42703: column pg_attrdef.adsrc does
not exist at character 128
DB=akonadi USER=akonadi SELECT pg_attribute.attname,
pg_attribute.atttypid::int, pg_attribute.attnotnull, pg_attribute.attlen,
pg_attribute.atttypmod, pg_attrdef.adsrc FROM pg_class, pg_attribute LEFT
JOIN pg_attrdef ON (pg_attrdef.adrelid = pg_attribute.attrelid AND
pg_attrdef.adnum = pg_attribute.attnum) WHERE
pg_table_is_visible(pg_class.oid) AND pg_class.relname =
'schemaversiontable' AND pg_attribute.attnum > 0 AND pg_attribute.attrelid
= pg_class.oid AND pg_attribute.attisdropped = false ORDER BY
pg_attribute.attnum
DB=akonadi USER=akonadi ALTER TABLE SchemaVersionTable ADD COLUMN version
INTEGER NOT NULL DEFAULT 0
DB=akonadi USER=akonadi ALTER TABLE ERROR:  42701: column "version" of
relation "schemaversiontable" already exists


This causes akonadi to repeatedly try to update the SchemaVersionTable and
quits with error.

org.kde.pim.akonadiserver: Starting up the Akonadi Server...
org.kde.pim.akonadiserver: Running DB initializer
org.kde.pim.akonadiserver: "\nSql error: ERROR:  column \"version\" of
relation \"schemaversiontable\" already exists\n(42701) QPSQL: Unable to
create query\nQuery: ALTER TABLE SchemaVersionTable ADD COLUMN version
INTEGER NOT NULL DEFAULT 0"
org.kde.pim.akonadiserver: Unable to initialize database.
org.kde.pim.akonadiserver: Shutting down AkonadiServer...


The schema information for these dropped columes can be obtained using the
functions

pg_constraint.consrc pg_get_constraintdef(pg_constraint.oid)
pg_attrdef.adsrc pg_get_expr(pg_attrdef.adbin, pg_class.oid)

So the Akonadi query:

SELECT pg_attribute.attname, pg_attribute.atttypid::int,
pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod,
pg_attrdef.adsrc
FROM pg_class, pg_attribute
LEFT JOIN pg_attrdef ON (pg_attrdef.adrelid = pg_attribute.attrelid AND
pg_attrdef.adnum = pg_attribute.attnum)
WHERE pg_table_is_visible(pg_class.oid)
AND pg_class.relname = 'schemaversiontable'
AND pg_attribute.attnum > 0
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attisdropped = false ORDER BY pg_attribute.attnum ;

should probably be changed to something like:

SELECT pg_attribute.attname, pg_attribute.atttypid::int,
pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod,
pg_get_expr(pg_attrdef.adbin, pg_class.oid) AS adsrc
FROM pg_class
LEFT JOIN pg_attribute ON ( pg_attribute.attrelid = pg_class.oid )
LEFT JOIN pg_attrdef ON  ( pg_attrdef.adrelid = pg_attribute.attrelid
                     AND   pg_attrdef.adnum = pg_attribute.attnum )
WHERE pg_table_is_visible(pg_class.oid)
AND pg_class.relname = 'schemaversiontable'
AND pg_attribute.attnum > 0
AND pg_attribute.attisdropped = false
ORDER BY pg_attribute.attnum;

This will produce something like the following table:

  attname   | atttypid | attnotnull | attlen | atttypmod | adsrc
------------+----------+------------+--------+-----------+-------
 version    |       23 | t          |      4 |        -1 | 0
 generation |       23 | t          |      4 |        -1 | 0
(2 rows)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/kde-frameworks-devel/attachments/20191010/3419dea8/attachment.html>