Bug 412818

SUMMARY

When backed by the postgres db, Akonadi executes the 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 ;"

In versions of PostGreSQL before 12, this query succeeds.  Due to the changes to the informationschema, as identified in PostGreSQL PostgreSQL release notes:

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


STEPS TO REPRODUCE
1. Upgrade PostGreSQL backend database to 12.x
2. Restart with Akonadictl start
3. 

OBSERVED RESULT

This causes the query to fail and Akonadi quits with the 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...


EXPECTED RESULT

When the query succeeds, Akonadi continues to process emails.

SOFTWARE/OS VERSIONS
Windows: 
macOS: 
Linux/KDE Plasma: ANY
(available in About System)
KDE Plasma Version: ANY
KDE Frameworks Version: ANY 
Qt Version: ANY

ADDITIONAL INFORMATION

The schema information for these dropped columns 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)