Bug: _prepare_session can fail on PostgreSQL 8.2 (and below)

[jamoore]

2011-06-10 10:17:33,909 INFO  [        ome.services.util.ServiceHandler] (l.Server-5)  Meth:    interface omeis.providers.re.RenderingEngine.resetDefaults
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call _prepare_session(?, ?, ?)}]; nested exception is org.postgresq
l.util.PSQLException: ERROR: relation with OID 78639 does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: relation with OID 78639 does not exist
2011-06-10 10:17:33,934 INFO  [        ome.services.util.ServiceHandler] (l.Server-5)  Excp:    org.springframework.jdbc.BadSqlGrammarException: CallableStatementCal
lback; bad SQL grammar [{? = call _prepare_session(?, ?, ?)}]; nested exception is org.postgresql.util.PSQLException: ERROR: relation with OID 78639 does not exist
2011-06-10 10:17:33,934 INFO  [        ome.services.util.ServiceHandler] (l.Server-5)  Excp:    ome.conditions.ApiUsageException: CallableStatementCallback; bad SQL 
grammar [{? = call _prepare_session(?, ?, ?)}]; nested exception is org.postgresql.util.PSQLException: ERROR: relation with OID 78639 does not exist
2011-06-10 10:17:35,872 INFO  [        ome.services.util.ServiceHandler] (l.Server-9)  Meth:    interface ome.api.IMetadata.loadAnnotations

[jmoore]

...temp table ... dicard apparently didn't exist in PG 8.2 and earlier: ​http://postgresql.1045698.n5.nabble.com/PSQLException-ERROR-could-not-open-relation-with-OID-xxxx-td3424803.html

Not sure of the impact of this.

[jmoore]

Reproduced this randomly from the command line:

jboss@nightshade ~/OMERO-CURRENT/var/log $ localhost -U omero omero3
Welcome to psql 8.2.18, the PostgreSQL interactive terminal.

omero3=> begin;
BEGIN
omero3=> select _prepare_session(0,0,0);
 _prepare_session 
------------------
 
(1 row)

omero3=> select * from _current_session;
 event_id | user_id | group_id 
----------+---------+----------
        0 |       0 |        0
(1 row)

omero3=> select _prepare_session(0,0,0);
 _prepare_session 
------------------
 
(1 row)

omero3=> select * from _current_session;
 event_id | user_id | group_id 
----------+---------+----------
        0 |       0 |        0
(1 row)

omero3=> select _prepare_session(0,0,5);
 _prepare_session 
------------------
 
(1 row)

omero3=> select * from _current_session;
 event_id | user_id | group_id 
----------+---------+----------
        0 |       0 |        5
(1 row)

omero3=> select _prepare_session(0,3,5);
 _prepare_session 
------------------
 
(1 row)

omero3=> select * from _current_session;
 event_id | user_id | group_id 
----------+---------+----------
        0 |       3 |        5
(1 row)

omero3=> select _prepare_session(1,3,5);
 _prepare_session 
------------------
 
(1 row)

omero3=> select * from _current_session;
 event_id | user_id | group_id 
----------+---------+----------
        1 |       3 |        5
(1 row)

omero3=> rollback;
ROLLBACK
omero3=> select * from _current_session;
ERROR:  relation "_current_session" does not exist
omero3=> select _prepare_session(0,3,5);
omero3=> begin;
BEGIN
omero3=> select _prepare_session(1,3,5);
ERROR:  relation with OID 78806 does not exist
CONTEXT:  SQL statement "INSERT INTO _current_session VALUES ( $1 ,  $2 ,  $3 )"
PL/pgSQL function "_prepare_session" line 5 at SQL statement
omero3=> 

[jburel]

Moved from sprint 2011-06-16 (14)

[jmoore]

Closing as 'wontfix' since we are now suggesting 8.3 or above PostgreSQL.

[jmoore]

This issue also plays a role in the grouped events of #5861.