[sticky_notes] - PostgreSQL insert_id does not work

[GoogleCodeExporter]

Token: 5e57a38abd0d14627d89a9b1c0d01da3 (Don't modify this token!)
Version: 5.1.11 (14-01-2015)
PHP: 5.6.4
RCMAIL: 1.0.4
Database: pgsql
SERVER: Apache
----
I.  Issue Description:
Adding a new (first) notice results in the following error:
[21-Jan-2015 03:10:16 +0100]: DB Error: [7] FEHLER:  ungültige Eingabesyntax 
für ganze Zahl: „“
ZEILE 1: SELECT * FROM notes WHERE "user_id"='2' AND "id"='' LIMIT 1
                                                          ^ (SQL Query: SELECT * FROM notes WHERE "user_id"='2' AND "id"='' LIMIT 1) in /var/www/webmail.oopen.de/roundcubemail-1.0.4/program/lib/Roundcube/rcube_db.php on line 467 (POST /?_task=dummy&_action=plugin.sticky_notes_new_sticky_note?_task=&_action=)
[21-Jan-2015 03:10:16 Europe/Berlin] PHP Deprecated:  Non-static method 
Auth_SASL::factory() should not be called statically, assuming $this from 
incompatible context in 
/var/www/webmail.oopen.de/roundcubemail-1.0.4/program/lib/Roundcube/rcube_imap_g
eneric.php on line 549

II. Steps to reproduce the Issue:
1. create a new notice
2.
3.

Original issue reported on code.google.com by ch.kuche...@gmail.com on 21 Jan 2015 at 2:14

[GoogleCodeExporter]

Make sure that notes_seq is defined. For some reason your PostgreSQL does not 
return the database insert_id after creating a note.

Original comment by roland.l...@gmail.com on 21 Jan 2015 at 3:56

• Changed title: [sticky_notes] - PostgreSQL insert_id does not work
• Changed state: Worksforme

[GoogleCodeExporter]

Sequence notes_seq exists and seems to be ok:
-- snip                 Sequenz „public.notes_seq“
    Spalte     |   Typ   |        Wert         | Speicherung 
---------------+---------+---------------------+-------------
 sequence_name | name    | notes_seq           | plain
 last_value    | bigint  | 1                   | plain
 start_value   | bigint  | 1                   | plain
 increment_by  | bigint  | 1                   | plain
 max_value     | bigint  | 9223372036854775807 | plain
 min_value     | bigint  | 1                   | plain
 cache_value   | bigint  | 1                   | plain
 log_cnt       | bigint  | 0                   | plain
 is_cycled     | boolean | f                   | plain
 is_called     | boolean | f                   | plain
-- snap

I tried with default config.inc.php
   $config['db_sequence_notes'] = 'notes_id_seq';
and also with
   $config['db_sequence_notes'] = 'notes_seq';
but both failed with the same error message.

I don't konw, which is the right, both sequences exists.

Original comment by ch.kuche...@gmail.com on 21 Jan 2015 at 11:30

[GoogleCodeExporter]

Which PostgreSQL version are you using? We require 9.1 and it works on our test 
installation.

Original comment by roland.l...@gmail.com on 21 Jan 2015 at 11:49

[GoogleCodeExporter]

I tested with versions 9.3 and 9.4

Original comment by ch.kuche...@gmail.com on 21 Jan 2015 at 12:37

[GoogleCodeExporter]

As said it works on our test system (PostgreSQL 9.1). I can only fix things 
which I'm able to duplicate.

We don't offer free personal remote access but we refund support fee in case of 
a bug in our code.

For details check ...

https://myroundcube.com/services-support

Original comment by roland.l...@gmail.com on 21 Jan 2015 at 12:46

[GoogleCodeExporter]

Does SEQUENCE notes_id_seq exist?

Original comment by myroundc...@gmail.com on 21 Jan 2015 at 7:08

[GoogleCodeExporter]

Original comment by myroundc...@gmail.com on 21 Jan 2015 at 7:12

Attachments:

• notes_table.PNG
• notes_id_seq.PNG

[GoogleCodeExporter]

I was able to duplicate the issue. I have created the sequence in PHPPgAdmin 
and missed to adjust the user:

[22-Jan-2015 04:42:54 +0000]: DB Error: [7] ERROR:  permission denied for 
relation notes (SQL Query: SELECT * FROM notes
         WHERE "user_id"='2'
         ORDER BY "nid" ASC) in /xxx/xxx/xxx/roundcubemail-1.0.1/program/lib/Roundcube/rcube_db.php on line 467 (GET /?_task=dummy&_action=plugin.sticky_notes)

Original comment by myroundc...@gmail.com on 22 Jan 2015 at 4:48