Akonadi SchemaVersionTable is broken with PostgreSQL Version 12 changes

[Stuart]

Thanks, Done.

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

On Thu, Oct 10, 2019 at 11:53 AM Harald Sitter <sitter at kde.org> 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 <sfbarbee at gmail.com> 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)
> >
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/kde-frameworks-devel/attachments/20191010/17b6b856/attachment.html>