Akonadi SchemaVersionTable is broken with PostgreSQL Version 12 changes

--000000000000d096bb059481a05e
Content-Type: text/plain; charset="UTF-8"

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)

--000000000000d096bb059481a05e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Dear development team,</div><div><br></div><div>As pe=
r the PostgreSQL release documents:<br>E.1. Release 12<br><a href=3D"https:=
//www.postgresql.org/docs/12/release-12.html#id-1.11.6.5.5">https://www.pos=
tgresql.org/docs/12/release-12.html#id-1.11.6.5.5</a><br><br>PostgreSQL Ver=
sion 12 removes:<br><br>obsolete pg_constraint.consrc column<br>obsolete pg=
_attrdef.adsrc column<br><br><br>Akonadi executes this query that fails due=
 to changes in the PostgreSQL schema<br><br>DB=3Dakonadi USER=3Dakonadi SEL=
ECT pg_attribute.attname, pg_attribute.atttypid::int, pg_attribute.attnotnu=
ll, pg_attribute.attlen, pg_attribute.atttypmod, pg_attrdef.adsrc FROM pg_c=
lass, pg_attribute LEFT JOIN pg_attrdef ON (pg_attrdef.adrelid =3D pg_attri=
bute.attrelid AND pg_attrdef.adnum =3D pg_attribute.attnum) WHERE pg_table_=
is_visible(pg_class.oid) AND pg_class.relname =3D &#39;schemaversiontable&#=
39; AND pg_attribute.attnum &gt; 0 AND pg_attribute.attrelid =3D pg_class.o=
id AND pg_attribute.attisdropped =3D false ORDER BY pg_attribute.attnum<br>=
DB=3Dakonadi USER=3Dakonadi SELECT ERROR: =C2=A042703: column pg_attrdef.ad=
src does not exist at character 128<br>DB=3Dakonadi USER=3Dakonadi SELECT p=
g_attribute.attname, pg_attribute.atttypid::int, pg_attribute.attnotnull, p=
g_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_vi=
sible(pg_class.oid) AND pg_class.relname =3D &#39;schemaversiontable&#39; A=
ND pg_attribute.attnum &gt; 0 AND pg_attribute.attrelid =3D pg_class.oid AN=
D pg_attribute.attisdropped =3D false ORDER BY pg_attribute.attnum<br>DB=3D=
akonadi USER=3Dakonadi ALTER TABLE SchemaVersionTable ADD COLUMN version IN=
TEGER NOT NULL DEFAULT 0<br>DB=3Dakonadi USER=3Dakonadi ALTER TABLE ERROR: =
=C2=A042701: column &quot;version&quot; of relation &quot;schemaversiontabl=
e&quot; already exists<br><br><br>This causes akonadi to repeatedly try to =
update the SchemaVersionTable and quits with error.<br><br>org.kde.pim.akon=
adiserver: Starting up the Akonadi Server...<br>org.kde.pim.akonadiserver: =
Running DB initializer<br>org.kde.pim.akonadiserver: &quot;\nSql error: ERR=
OR: =C2=A0column \&quot;version\&quot; of relation \&quot;schemaversiontabl=
e\&quot; already exists\n(42701) QPSQL: Unable to create query\nQuery: ALTE=
R TABLE SchemaVersionTable ADD COLUMN version INTEGER NOT NULL DEFAULT 0&qu=
ot;<br>org.kde.pim.akonadiserver: Unable to initialize database.<br>org.kde=
.pim.akonadiserver: Shutting down AkonadiServer...<br><br><br>The schema in=
formation for these dropped columes can be obtained using the functions<br>=
<br>pg_constraint.consrc	pg_get_constraintdef(pg_constraint.oid)<br>pg_attr=
def.adsrc	pg_get_expr(pg_attrdef.adbin, pg_class.oid)<br><br>So the Akonadi=
 query:<br><br>SELECT pg_attribute.attname, pg_attribute.atttypid::int, pg_=
attribute.attnotnull, pg_attribute.attlen, pg_attribute.atttypmod, pg_attrd=
ef.adsrc<br>FROM pg_class, pg_attribute<br>LEFT JOIN pg_attrdef ON (pg_attr=
def.adrelid =3D pg_attribute.attrelid AND pg_attrdef.adnum =3D pg_attribute=
.attnum)<br>WHERE pg_table_is_visible(pg_class.oid)<br>AND pg_class.relname=
 =3D &#39;schemaversiontable&#39;<br>AND pg_attribute.attnum &gt; 0<br>AND =
pg_attribute.attrelid =3D pg_class.oid<br>AND pg_attribute.attisdropped =3D=
 false ORDER BY pg_attribute.attnum ;<br><br>should probably be changed to =
something like:<br><br>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<br>FROM pg_class<br>LE=
FT JOIN pg_attribute ON ( pg_attribute.attrelid =3D pg_class.oid )<br>LEFT =
JOIN pg_attrdef ON =C2=A0( pg_attrdef.adrelid =3D pg_attribute.attrelid<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0AND =C2=A0 pg_attrdef.adnum =3D pg_attribute.attnum )<br>WHERE pg_table_=
is_visible(pg_class.oid)<br>AND pg_class.relname =3D &#39;schemaversiontabl=
e&#39;<br>AND pg_attribute.attnum &gt; 0<br>AND pg_attribute.attisdropped =
=3D false<br>ORDER BY pg_attribute.attnum;</div><div><br></div><div>This wi=
ll produce something like the following table:<br><br><span style=3D"font-f=
amily:monospace">=C2=A0 attname =C2=A0 | atttypid | attnotnull | attlen | a=
tttypmod | adsrc<br>------------+----------+------------+--------+---------=
--+-------<br>=C2=A0version =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 23 | t =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A04 | =C2=A0 =C2=A0 =C2=
=A0 =C2=A0-1 | 0<br>=C2=A0generation | =C2=A0 =C2=A0 =C2=A0 23 | t =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A04 | =C2=A0 =C2=A0 =C2=A0 =
=C2=A0-1 | 0<br>(2 rows)</span></div><div><br></div></div>

--000000000000d096bb059481a05e--