[Akonadi] [Bug 412818] New: Akonadi query on SchemaVersionTable is broken in PostgreSQL Version 12

[stuart]

https://bugs.kde.org/show_bug.cgi?id=412818

            Bug ID: 412818
           Summary: Akonadi query on SchemaVersionTable is broken in
                    PostgreSQL Version 12
           Product: Akonadi
           Version: unspecified
          Platform: unspecified
                OS: Linux
            Status: REPORTED
          Severity: major
          Priority: NOR
         Component: server
          Assignee: kdepim-bugs at kde.org
          Reporter: sfbarbee at gmail.com
  Target Milestone: ---

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)

-- 
You are receiving this mail because:
You are the assignee for the bug.