Re: Akonadi SchemaVersionTable is broken with PostgreSQL Version 12 changes
Stuart <[email protected]> Thu, 10 Oct 2019 18:40:50 +0300
| Newsgroups | gmane.comp.kde.linux,gmane.comp.kde.devel.frameworks |
|---|---|
| Message-ID | <CALmuyMr_kq4Yf5dDsO3ehQGMOi9hoX++TkNf+Jte778ac_-Oyw@mail.gmail.com> |
--0000000000000a35770594903cab Content-Type: text/plain; charset="UTF-8" Thanks, Done. bug 412818 https://bugs.kde.org/show_bug.cgi?id=412818 On Thu, Oct 10, 2019 at 11:53 AM Harald Sitter <[email protected]> wrote: > Hey, > > Please file bug reports about issues: > https://bugs.kde.org/enter_bug.cgi?product=Akonadi > > 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 > 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) > > > --0000000000000a35770594903cab Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div>Thanks, Done. <br></div><div><br></div><div>bug 41281= 8</div><div><a href=3D"https://bugs.kde.org/show_bug.cgi?id=3D412818">https= ://bugs.kde.org/show_bug.cgi?id=3D412818</a></div></div><br><div class=3D"g= mail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Oct 10, 2019 at 1= 1:53 AM Harald Sitter <<a href=3D"mailto:[email protected]">[email protected]<= /a>> wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0= px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">H= ey,<br> <br> Please file bug reports about issues:<br> <a href=3D"https://bugs.kde.org/enter_bug.cgi?product=3DAkonadi" rel=3D"nor= eferrer" target=3D"_blank">https://bugs.kde.org/enter_bug.cgi?product=3DAko= nadi</a><br> <br> On Thu, Oct 10, 2019 at 12:29 AM Stuart <<a href=3D"mailto:sfbarbee@gmai= l.com" target=3D"_blank">[email protected]</a>> wrote:<br> ><br> > Dear development team,<br> ><br> > As per 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" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs= /12/release-12.html#id-1.11.6.5.5</a><br> ><br> > PostgreSQL Version 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 PostgreSQ= L schema<br> ><br> > DB=3Dakonadi USER=3Dakonadi SELECT pg_attribute.attname, pg_attribute.= atttypid::int, pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.a= tttypmod, 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.r= elname =3D 'schemaversiontable' AND pg_attribute.attnum > 0 AND = pg_attribute.attrelid =3D pg_class.oid AND pg_attribute.attisdropped =3D fa= lse ORDER BY pg_attribute.attnum<br> > DB=3Dakonadi USER=3Dakonadi SELECT ERROR:=C2=A0 42703: column pg_attrd= ef.adsrc does not exist at character 128<br> > DB=3Dakonadi USER=3Dakonadi SELECT pg_attribute.attname, pg_attribute.= atttypid::int, pg_attribute.attnotnull, pg_attribute.attlen, pg_attribute.a= tttypmod, 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.r= elname =3D 'schemaversiontable' AND pg_attribute.attnum > 0 AND = pg_attribute.attrelid =3D pg_class.oid AND pg_attribute.attisdropped =3D fa= lse ORDER BY pg_attribute.attnum<br> > DB=3Dakonadi USER=3Dakonadi ALTER TABLE SchemaVersionTable ADD COLUMN = version INTEGER NOT NULL DEFAULT 0<br> > DB=3Dakonadi USER=3Dakonadi ALTER TABLE ERROR:=C2=A0 42701: column &qu= ot;version" of relation "schemaversiontable" already exists<= br> ><br> ><br> > This causes akonadi to repeatedly try to update the SchemaVersionTable= and quits with error.<br> ><br> > org.kde.pim.akonadiserver: Starting up the Akonadi Server...<br> > org.kde.pim.akonadiserver: Running DB initializer<br> > org.kde.pim.akonadiserver: "\nSql error: ERROR:=C2=A0 column \&qu= ot;version\" of relation \"schemaversiontable\" already exis= ts\n(42701) QPSQL: Unable to create query\nQuery: ALTER TABLE SchemaVersion= Table ADD COLUMN version INTEGER NOT NULL DEFAULT 0"<br> > org.kde.pim.akonadiserver: Unable to initialize database.<br> > org.kde.pim.akonadiserver: Shutting down AkonadiServer...<br> ><br> ><br> > The schema information for these dropped columes can be obtained using= the functions<br> ><br> > pg_constraint.consrc pg_get_constraintdef(pg_constraint.oid)<br> > pg_attrdef.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_attrdef.adsrc<b= r> > FROM pg_class, pg_attribute<br> > LEFT JOIN pg_attrdef ON (pg_attrdef.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 'schemaversiontable'<br> > AND pg_attribute.attnum > 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_att= rdef.adbin, pg_class.oid) AS adsrc<br> > FROM pg_class<br> > LEFT JOIN pg_attribute ON ( pg_attribute.attrelid =3D pg_class.oid )<b= r> > LEFT JOIN pg_attrdef ON=C2=A0 ( pg_attrdef.adrelid =3D pg_attribute.at= trelid<br> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 AND=C2=A0 =C2=A0pg_attrdef.adnum =3D pg_attribute.attnum )<br> > WHERE pg_table_is_visible(pg_class.oid)<br> > AND pg_class.relname =3D 'schemaversiontable'<br> > AND pg_attribute.attnum > 0<br> > AND pg_attribute.attisdropped =3D false<br> > ORDER BY pg_attribute.attnum;<br> ><br> > This will produce something like the following table:<br> ><br> >=C2=A0 =C2=A0attname=C2=A0 =C2=A0| atttypid | attnotnull | attlen | att= typmod | adsrc<br> > ------------+----------+------------+--------+-----------+-------<br> >=C2=A0 version=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A023 | t=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4 |=C2=A0 =C2=A0 =C2=A0 = =C2=A0 -1 | 0<br> >=C2=A0 generation |=C2=A0 =C2=A0 =C2=A0 =C2=A023 | t=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 4 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 -1 |= 0<br> > (2 rows)<br> ><br> </blockquote></div> --0000000000000a35770594903cab--