From: | Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: prepared statement "cu1" already exists (but it does not) |
Date: | 2024-04-08 16:25:13 |
Message-ID: | AM9P191MB1286601145DE06E7B0D3DA57B0002@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Here a first PQtrace() file... assuming it can help.
Seb
________________________________
From: Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>
Sent: Monday, April 8, 2024 5:31 PM
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Cc: Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>
Subject: prepared statement "cu1" already exists (but it does not)
Hello,
In a specific case, I get the error
prepared statement "cu1" already exists
I understand when this can happen, but in fact I do de-allocate prepared statements when I should.
I am investigating on this for several hours now, I thought I could share my problem to see if this ring a bell.
I am using PostgreSQL 16.2 or 15.1 (I can try older versions if needed)
Platform is Debian 11
I am using the libpq C API to execute SQL statements, mixing:
*
PQprepare
*
PQexecPrepared
*
PQexecParams
*
PQexec
When a prepared statement handle is no longer needed, I do execute
deallocate cu1
(seems there is no C API to de-allocate a prepared statement, right?)
For result sets (SELECT), I do PQprepare / PQexecPrepared of "server cursors" with:
declare cu1 cursor for ...
When a server cursor is no longer needed, I do PQexec(conn, "close curs-name')
The problem occurs when doing a first transaction, where an INSERT fails because of a table UNIQUE constraint.
After the rollback, I restart a new TX with begin, several PQexec() of SELECT and UPDATE succeed, but on a subsequent PQprepare/PQexecPrepared using a "declare cu1 cursor for select ...", I the error "cu1 statement already exists"... and I don't see how cu1 can exist.
Before doing the rollback, I try to deallocate the prepared statement with deallocate cu1, but this fails with error:
current transaction is aborted, commands ignored until end of transaction block
I understand that the whole TX is aborted with PostgreSQL, and probably the deallocate is useless since stmt was prepared inside the TX?
I just want to make sure that doing a deallocate in this context does not confuse PostgreSQL.
I have tried to skip the deallocate in case of SQL error, but on the subsequent PQprepare/PQexecPrepared, I still get the error that the cu1 statement already exists.
Is it an issue if I use the same name for a prepared statement and the server cursor? I mean:
PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
PQexecPrepared(pgConn, "cu1", ... )
My understanding is that a prepared stmt and server cursor are 2 distinct objects.
Anyway: I tried to use distinct names but that did not help.
Note that when the INSERT succeeds, I do not get the error prepared statement "cu1" already exists
Any suggestion is welcome!
Seb
Attachment | Content-Type | Size |
---|---|---|
pgs_trace.txt | text/plain | 20.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Dominique Devienne | 2024-04-08 16:40:07 | Re: prepared statement "cu1" already exists (but it does not) |
Previous Message | Sebastien Flaesch | 2024-04-08 15:31:04 | prepared statement "cu1" already exists (but it does not) |