Bug#717117: [akonadi-backend-postgresql] continuous errors in postgresql 9.1 log

[Chris Knadle <Chris.Knadle@coredump.us>]

Package: akonadi-backend-postgresql
Version: 1.9.2-2
Severity: important

--- Please enter the report below this line. ---

Greetings.

Thanks for including akonadi-backend-postgresql in Sid... I'm hoping to use it 
when I can get it working.

I'm having a lot of trouble when trying to use PostgreSQL 9.1 as the backend 
for Akonadi; I can't tell for sure that this is the correct package to file 
this bug against, but this seems the most logical place to start.  [The next 
logical alternative is the 'kmail' package.]

When migrating from KMail1 to KMail2, KMail2 shows a dialog box indicating 
that all mail has been migrated, but email does not show up and PostgreSQL 
continuously spits out errors to the log with:

   2013-07-16 17:51:52 EDT ERROR:  invalid input syntax for type bytea at
     character 24
   2013-07-16 17:51:52 EDT STATEMENT:  EXECUTE qpsqlpstmt_7d ('\SEEN')
   2013-07-16 17:51:52 EDT ERROR:  invalid input syntax for type bytea at
     character 24
   2013-07-16 17:51:52 EDT STATEMENT:  EXECUTE qpsqlpstmt_7d ('\SEEN')
   ...

After letting the system run for about 24 hours, the postgresql log grows to 
about 650 MB containing about 9 million lines of the above, and about 200 
emails (out of thousands) show up in KMail2 (in various folders, and none of 
the messages in the "inbox" folder ever showed up) from my main IMAP account, 
and new ones show up very slowly -- which I believe seems to be related to the 
errors.  This is repeatable.

Steps to replicate
------------------
1.  Install akonadi-backend-postgresql, which pulls in dependencies:
        postgresql:amd64
        postgresql-9.1:amd64
        postgresql-client-9.1:amd64
        postgresql-client-common:amd64
        postgresql-common:amd64
    Modify /etc/postgresql/9.1/main/postgresql.conf to uncomment:
        standard_conforming_strings = on
    (this was suggested in akonadiconsole)
2.  'su' to root, then 'su - postgres' to become the postgres user
3.  Create a role for a normal user with CreateDB ability
    pgsql -d template1
    -> CREATE ROLE cknadle WITH CREATEDB LOGIN;
       \q
    exit; exit;  [to become regular user again]
4.  Create new database as a regular user
    psql -d template1
    -> CREATE DATABASE "akonadi-chris";
       \q
5.  Delete previous files for akonadi:
      rm -rf ~/.local/share/akonadi
      rm -rf ~/.config/akonadi/agent*
      rm -rf ~/.kde/share/akonadi_*
      rm -rf ~/.kde/share/akonadi-*
    Delete settings for kmail2 and kmail-migrator
      rm -rf ~/.kde/share/config/kmail2*
      rm -rf ~/.kde/share/config/kmail-migrator*
6.  Open akonadiconsole as user, set to use the akonadi-chris database,
    start Akonadi
7.  Start KMail2 and choose "Migrate"


Setps 5 through 7 work just fine when using the the Akonadi MySQL backend, and 
likewise KMail2 seems to work fine afterwards.

Please let me know if I can be of further assistance.
Thanks!

 -- Chris

--
Chris Knadle
Chris.Knadle@coredump.us

--- System information. ---
Architecture: amd64
Kernel:       Linux 3.9-1-amd64

Debian Release: jessie/sid
  500 unstable        www.deb-multimedia.org 
  500 unstable        ftp.us.debian.org 
  500 unstable        debian-packages.coredump.us 
  500 testing         security.debian.org 
  500 testing         ftp.us.debian.org 
  500 stable-updates  ftp.us.debian.org 
  500 stable          security.debian.org 
  500 stable          ftp.us.debian.org 
  500 sid             linux.dropbox.com 

--- Package information. ---
Depends              (Version) | Installed
==============================-+-===========
libqt4-sql-psql                | 4:4.8.5+dfsg-2


Recommends          (Version) | Installed
=============================-+-===========
akonadi-server                | 1.9.2-2
postgresql                    | 9.3+142really9.1+145


Package's Suggests field is empty.