Re: Akonadi SchemaVersionTable is broken with PostgreSQL Version 12 changes

Hey,

Please file bug reports about issues:
https://bugs.kde.org/enter_bug.cgi?product=3DAkonadi

On Thu, Oct 10, 2019 at 12:29 AM Stuart <[email protected]> wrote:
>
> 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 s=
chema
>
> DB=3Dakonadi USER=3Dakonadi SELECT pg_attribute.attname, pg_attribute.att=
typid::int, pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.attt=
ypmod, pg_attrdef.adsrc FROM pg_class, pg_attribute LEFT JOIN pg_attrdef ON=
 (pg_attrdef.adrelid =3D pg_attribute.attrelid AND pg_attrdef.adnum =3D pg_=
attribute.attnum) WHERE pg_table_is_visible(pg_class.oid) AND pg_class.reln=
ame =3D 'schemaversiontable' AND pg_attribute.attnum > 0 AND pg_attribute.a=
ttrelid =3D pg_class.oid AND pg_attribute.attisdropped =3D false ORDER BY p=
g_attribute.attnum
> DB=3Dakonadi USER=3Dakonadi SELECT ERROR:  42703: column pg_attrdef.adsrc=
 does not exist at character 128
> DB=3Dakonadi USER=3Dakonadi SELECT pg_attribute.attname, pg_attribute.att=
typid::int, pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.attt=
ypmod, pg_attrdef.adsrc FROM pg_class, pg_attribute LEFT JOIN pg_attrdef ON=
 (pg_attrdef.adrelid =3D pg_attribute.attrelid AND pg_attrdef.adnum =3D pg_=
attribute.attnum) WHERE pg_table_is_visible(pg_class.oid) AND pg_class.reln=
ame =3D 'schemaversiontable' AND pg_attribute.attnum > 0 AND pg_attribute.a=
ttrelid =3D pg_class.oid AND pg_attribute.attisdropped =3D false ORDER BY p=
g_attribute.attnum
> DB=3Dakonadi USER=3Dakonadi ALTER TABLE SchemaVersionTable ADD COLUMN ver=
sion INTEGER NOT NULL DEFAULT 0
> DB=3Dakonadi USER=3Dakonadi ALTER TABLE ERROR:  42701: column "version" o=
f relation "schemaversiontable" already exists
>
>
> This causes akonadi to repeatedly try to update the SchemaVersionTable an=
d 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 re=
lation \"schemaversiontable\" already exists\n(42701) QPSQL: Unable to crea=
te 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 th=
e 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.att=
notnull, pg_attribute.attlen, pg_attribute.atttypmod, pg_attrdef.adsrc
> FROM pg_class, pg_attribute
> LEFT JOIN pg_attrdef ON (pg_attrdef.adrelid =3D pg_attribute.attrelid AND=
 pg_attrdef.adnum =3D pg_attribute.attnum)
> WHERE pg_table_is_visible(pg_class.oid)
> AND pg_class.relname =3D 'schemaversiontable'
> AND pg_attribute.attnum > 0
> AND pg_attribute.attrelid =3D pg_class.oid
> AND pg_attribute.attisdropped =3D false ORDER BY pg_attribute.attnum ;
>
> should probably be changed to something like:
>
> SELECT pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.att=
notnull, pg_attribute.attlen, pg_attribute.atttypmod, pg_get_expr(pg_attrde=
f.adbin, pg_class.oid) AS adsrc
> FROM pg_class
> LEFT JOIN pg_attribute ON ( pg_attribute.attrelid =3D pg_class.oid )
> LEFT JOIN pg_attrdef ON  ( pg_attrdef.adrelid =3D pg_attribute.attrelid
>                      AND   pg_attrdef.adnum =3D pg_attribute.attnum )
> WHERE pg_table_is_visible(pg_class.oid)
> AND pg_class.relname =3D 'schemaversiontable'
> AND pg_attribute.attnum > 0
> AND pg_attribute.attisdropped =3D 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)
>