I am seeing the following errors when starting up Akonadi when using Postgresql 9.2: DBUpdater: query error: "ERROR: function convert_from(text, unknown) does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts. (42883) QPSQL: Unable to create query" " " Query was: "ALTER TABLE CollectionTable ALTER remoteId TYPE text USING convert_from(remoteId,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE CollectionTable ALTER remoteRevision TYPE text USING convert_from(remoteRevision,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE CollectionTable ALTER name TYPE text USING convert_from(name,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE CollectionTable ALTER cachePolicyLocalParts TYPE text USING convert_from(cachePolicyLocalParts,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE CollectionTable ALTER queryString TYPE text USING convert_from(queryString,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE CollectionTable ALTER queryLanguage TYPE text USING convert_from(queryLanguage,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE FlagTable ALTER name TYPE text USING convert_from(name,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE MimeTypeTable ALTER name TYPE text USING convert_from(name,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE PartTable ALTER name TYPE text USING convert_from(name,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE PimItemTable ALTER remoteId TYPE text USING convert_from(remoteId,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE PimItemTable ALTER remoteRevision TYPE text USING convert_from(remoteRevision,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02) QPSQL: Unable to create query" " " Query was: "ALTER TABLE ResourceTable ALTER name TYPE text USING convert_from(name,'utf8');" Target version was: 24 Mandatory: false Failed to commit transaction for database update Nepomuk Query Server not available Reproducible: Always Steps to Reproduce: 1. Configure to use system postgresql database server (not internal) 2. Start Akonadi 3. Running OpenSUSE 12.3 with Postgres 9.2.4.
this is because you have the column already converted to TEXT (CollectionTable.remoteId). Is there something you can remember about your initial installation procedure ?
Not really. I just selected PostgreSQL. This machine has gone through a number of upgrades but before doing this I nuked everything in the Akonadi data directory to start over. I have been having a lot of issues dealing with Microsoft Office 365's crappy IMAP implementation which can't seem to handle multiple IMAP connections. I did start out with SQLite before switching to PostgreSQL. I ran into problems with the internal PostgreSQL server not working (complains that it cannot connect to the PostgreSQL server) so I enabled the external server. Note that the problem connecting to the internal PostgreSQL server seems to happen on all of my OpenSUSE machines (running 12.3).
ok. there is an easy way to discover the current status. Check the date type of the columns ALTER are trying to achieve in your paste. previous state should be BYTEA. after upgrade should be TEXT. You can check that with tools like pgadmin3 or psql (easy way is to become postgres user with sudo, then) : $ psql akonadi_db then # \d flagtable -- for example This flagtable content should contains data like: 4 | \ANSWERED 5 | \FLAGGED 6 | \DELETED 7 | \SEEN 8 | \DRAFT *NO* double escape, just a single backslash if the column has already been 'upgraded' to TEXT. If you have TEXT and double escape, please report.
$ psql -d akonadi psql (9.2.4, server 9.1.8) WARNING: psql version 9.2, server version 9.1. Some psql features might not work. Type "help" for help. akonadi=# \d flagtable Table "public.flagtable" Column | Type | Modifiers --------+---------+-------------------------------------------------------- id | integer | not null default nextval('flagtable_id_seq'::regclass) name | text | not null Indexes: "flagtable_pkey" PRIMARY KEY, btree (id) "flagtable_name_key" UNIQUE CONSTRAINT, btree (name) Referenced by: TABLE "pimitemflagrelation" CONSTRAINT "pimitemflagrelation_flag_id_fkey" FOREIGN KEY (flag_id) REFERENCES flagtable(id) ON UPDATE CASCADE ON DELETE CASCADE
It looks like this may have been caused by an improper upgrade from PostgreSQL 9.1 to 9.2. I am no longer seeing this problem after having performed a proper upgrade of the database.