Bug 326924

Summary: Problems when using Postgresql backend database
Product: [Frameworks and Libraries] Akonadi Reporter: Aaron Williams <aaronw>
Component: serverAssignee: kdepim bugs <kdepim-bugs>
Status: RESOLVED NOT A BUG    
Severity: normal CC: cedric
Priority: NOR    
Version: 4.11   
Target Milestone: ---   
Platform: openSUSE   
OS: Linux   
Latest Commit: Version Fixed In:
Sentry Crash Report:

Description Aaron Williams 2013-10-31 02:47:34 UTC 
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.
Comment 1 Cédric Villemain 2013-11-04 12:33:46 UTC 
this is because you have the column already converted to TEXT (CollectionTable.remoteId).

Is there something you can remember about your initial installation procedure ?
Comment 2 Aaron Williams 2013-11-04 16:37:36 UTC 
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).
Comment 3 Cédric Villemain 2013-11-04 17:21:13 UTC 
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.
Comment 4 Aaron Williams 2013-11-04 20:19:16 UTC 
$ 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
Comment 5 Aaron Williams 2013-11-04 23:37:37 UTC 
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.