[odb-users] Prepared statements alreadys exists

[Boris Kolpackov]

Hi Meike,

meike.talbach at women-at-work.org <meike.talbach at women-at-work.org> writes:

> Postgres logs this error:
> 
> Oct 24 15:12:53 test1 postgres[52801]: [3-1] ERROR:  timestamp out of range
> Oct 24 15:12:53 test1 postgres[52801]: [3-2] STATEMENT:  SELECT "test_history"."id", "test_history"."deviceid", "test_history"."type", "test_history"."latitude", "test_history"."longitude", "test_history"."address", "test_history"."daterecorded" FROM "test_history" WHERE "test_history"."deviceid" = $1 AND ("test_history"."daterecorded" >= $2) AND ("test_history"."daterecorded" <= $3)
> Oct 24 15:12:53 test1 postgres[52801]: [4-1] ERROR:  current transaction is aborted, commands ignored until end of transaction block
> Oct 24 15:12:53 test1 postgres[52801]: [4-2] STATEMENT:  deallocate "mc_test_database_History_query"
> Oct 24 15:54:19 test1 postgres[52801]: [5-1] ERROR:  prepared statement "mc_test_database_History_query" already exists
> Oct 24 15:54:19 test1 postgres[52801]: [5-2] STATEMENT:  SELECT "test_history"."id", "test_history"."deviceid", "test_history"."type", "test_history"."latitude", "test_history"."longitude", "test_history"."address", "test_history"."daterecorded" FROM "test_history" WHERE "test_history"."deviceid" = $1 ORDER BY "test_history"."daterecorded" ASC LIMIT 1
> 
> It seems that the prepared statement from the first call is not deallocated.
> Then the second call tries to recreate it and fails.

Thanks for the detailed information, always appreciated! I think I now
understand what's going on here. Below is a comment for the fix:

// When we try to execute an invalid statement, PG "poisons" the
// transaction (those "current transaction is aborted, commands
// ignored until end of transaction block" messages in the log).
// This includes prepared statement deallocations (quite a stupid
// decision, if you ask me).
//
// So what can happen in this situation is the deallocation fails
// but we ignore it because we are already unwinding the stack
// (i.e., the prepared statement execution has failed). Next the
// user fixes things (e.g., passes valid parameters) and tries to
// re-execute the same query. But since we have failed to deallocate
// the statement, we now cannot re-prepare it; the name is already
// in use.

And here is the fix itself:

http://scm.codesynthesis.com/?p=odb/libodb-pgsql.git;a=commit;h=231d0222fb75c448321677d258ed59da98eec7ee

Could you apply this patch to your libodb-pgsql and verify that your
code now works as expected?

Thanks,
Boris