Thread: [MASSMAIL]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
Here a first PQtrace() file... assuming it can help.
Seb
From: Sebastien Flaesch <sebastien.flaesch@4js.com>
Sent: Monday, April 8, 2024 5:31 PM
To: pgsql-general <pgsql-general@lists.postgresql.org>
Cc: Sebastien Flaesch <sebastien.flaesch@4js.com>
Subject: prepared statement "cu1" already exists (but it does not)
Sent: Monday, April 8, 2024 5:31 PM
To: pgsql-general <pgsql-general@lists.postgresql.org>
Cc: Sebastien Flaesch <sebastien.flaesch@4js.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
On Mon, Apr 8, 2024 at 5:31 PM Sebastien Flaesch <sebastien.flaesch@4js.com> wrote:
I understand when this can happen, but in fact I do de-allocate prepared statements when I should.
We've run into similar issues. We're in C++, and with RAII deallocate resources (Prepared Statements, Cursors) in Dtors.
But as you saw, when a TX is KO, any Dtor trying to release resources via libpq will fail. So what I do it record those
resources (stmt and cursor names basically) on the Connection (in our wrapper), and will release them at the first opportunity,
once the TX has rolled back for example. FWIW.
OTOH, we tend not to reuse names, generating random ones, since when using our wrappers, the name is an impl details basically.
We also tend to prepare outside transactions, so can't answer your question on whether prepared statements within TX are "scoped" or not. --DD
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
> I understand that the whole TX is aborted with PostgreSQL, and probably the deallocate is useless since stmt was
preparedinside the TX?
As you can quickly discover with some manual experimentation,
both PREPARE and DEALLOCATE are nontransactional, in the sense
that if they succeed then the prepared statement will exist
(or not) even if the surrounding transaction block is later
rolled back. This is pretty weird, and undocumented I think,
in terms of their role as SQL statements.
It makes a little more sense if you think about the equivalent
wire-protocol-level operations, which are meant to be used
by low-level client code that may not be aware of whether
there is a transaction block in progress.
> Is it an issue if I use the same name for a prepared statement and the server cursor? I mean:
From memory, I think those share the same "portal" namespace.
regards, tom lane
Thanks all for your help.
I have discovered another fact: When surrounding the execution of the failing INSERT with a savepoint + rollback to savepoint, the problem disappears: The DEALLOCATE commands do then succeed.
See attached trace...
Seb
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, April 8, 2024 7:36 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: prepared statement "cu1" already exists (but it does not)
Sent: Monday, April 8, 2024 7:36 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: prepared statement "cu1" already exists (but it does not)
EXTERNAL: Do not click links or open attachments if you do not recognize the sender.
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
> I understand that the whole TX is aborted with PostgreSQL, and probably the deallocate is useless since stmt was prepared inside the TX?
As you can quickly discover with some manual experimentation,
both PREPARE and DEALLOCATE are nontransactional, in the sense
that if they succeed then the prepared statement will exist
(or not) even if the surrounding transaction block is later
rolled back. This is pretty weird, and undocumented I think,
in terms of their role as SQL statements.
It makes a little more sense if you think about the equivalent
wire-protocol-level operations, which are meant to be used
by low-level client code that may not be aware of whether
there is a transaction block in progress.
> Is it an issue if I use the same name for a prepared statement and the server cursor? I mean:
From memory, I think those share the same "portal" namespace.
regards, tom lane
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
> I understand that the whole TX is aborted with PostgreSQL, and probably the deallocate is useless since stmt was prepared inside the TX?
As you can quickly discover with some manual experimentation,
both PREPARE and DEALLOCATE are nontransactional, in the sense
that if they succeed then the prepared statement will exist
(or not) even if the surrounding transaction block is later
rolled back. This is pretty weird, and undocumented I think,
in terms of their role as SQL statements.
It makes a little more sense if you think about the equivalent
wire-protocol-level operations, which are meant to be used
by low-level client code that may not be aware of whether
there is a transaction block in progress.
> Is it an issue if I use the same name for a prepared statement and the server cursor? I mean:
From memory, I think those share the same "portal" namespace.
regards, tom lane
Attachment
Tom,
> > Is it an issue if I use the same name for a prepared statement and the server cursor? I mean:
> From memory, I think those share the same "portal" namespace.
> From memory, I think those share the same "portal" namespace.
Can you please elaborate?
Is it supported to do:
PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
PQexecPrepared(pgConn, "cu1", ... )
?
So far this has always worked.
Seb
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, April 8, 2024 7:36 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: prepared statement "cu1" already exists (but it does not)
Sent: Monday, April 8, 2024 7:36 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: prepared statement "cu1" already exists (but it does not)
EXTERNAL: Do not click links or open attachments if you do not recognize the sender.
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
> I understand that the whole TX is aborted with PostgreSQL, and probably the deallocate is useless since stmt was prepared inside the TX?
As you can quickly discover with some manual experimentation,
both PREPARE and DEALLOCATE are nontransactional, in the sense
that if they succeed then the prepared statement will exist
(or not) even if the surrounding transaction block is later
rolled back. This is pretty weird, and undocumented I think,
in terms of their role as SQL statements.
It makes a little more sense if you think about the equivalent
wire-protocol-level operations, which are meant to be used
by low-level client code that may not be aware of whether
there is a transaction block in progress.
> Is it an issue if I use the same name for a prepared statement and the server cursor? I mean:
From memory, I think those share the same "portal" namespace.
regards, tom lane
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
> I understand that the whole TX is aborted with PostgreSQL, and probably the deallocate is useless since stmt was prepared inside the TX?
As you can quickly discover with some manual experimentation,
both PREPARE and DEALLOCATE are nontransactional, in the sense
that if they succeed then the prepared statement will exist
(or not) even if the surrounding transaction block is later
rolled back. This is pretty weird, and undocumented I think,
in terms of their role as SQL statements.
It makes a little more sense if you think about the equivalent
wire-protocol-level operations, which are meant to be used
by low-level client code that may not be aware of whether
there is a transaction block in progress.
> Is it an issue if I use the same name for a prepared statement and the server cursor? I mean:
From memory, I think those share the same "portal" namespace.
regards, tom lane
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
>>> Is it an issue if I use the same name for a prepared statement and the server cursor? I mean:
>> From memory, I think those share the same "portal" namespace.
> Can you please elaborate?
> Is it supported to do:
> PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
> PQexecPrepared(pgConn, "cu1", ... )
> ?
> So far this has always worked.
Ah, sorry, what I should have said is that the SQL PREPARE command
works with the same namespace as PQprepare (which is a thin wrapper
over the wire-protocol Parse command). There is a different namespace
for cursors. Cursors do share that namespace with the wire-protocol
"portal" concept, but libpq doesn't directly expose portals.
regards, tom lane
Ok thanks Tom,
My understanding from your last comment:
Since prepared statements and server cursors use different name spaces, it's save to use the same name as in
PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
Seb
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, April 9, 2024 6:36 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: prepared statement "cu1" already exists (but it does not)
Sent: Tuesday, April 9, 2024 6:36 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: prepared statement "cu1" already exists (but it does not)
EXTERNAL: Do not click links or open attachments if you do not recognize the sender.
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
>>> Is it an issue if I use the same name for a prepared statement and the server cursor? I mean:
>> From memory, I think those share the same "portal" namespace.
> Can you please elaborate?
> Is it supported to do:
> PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
> PQexecPrepared(pgConn, "cu1", ... )
> ?
> So far this has always worked.
Ah, sorry, what I should have said is that the SQL PREPARE command
works with the same namespace as PQprepare (which is a thin wrapper
over the wire-protocol Parse command). There is a different namespace
for cursors. Cursors do share that namespace with the wire-protocol
"portal" concept, but libpq doesn't directly expose portals.
regards, tom lane
Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
>>> Is it an issue if I use the same name for a prepared statement and the server cursor? I mean:
>> From memory, I think those share the same "portal" namespace.
> Can you please elaborate?
> Is it supported to do:
> PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
> PQexecPrepared(pgConn, "cu1", ... )
> ?
> So far this has always worked.
Ah, sorry, what I should have said is that the SQL PREPARE command
works with the same namespace as PQprepare (which is a thin wrapper
over the wire-protocol Parse command). There is a different namespace
for cursors. Cursors do share that namespace with the wire-protocol
"portal" concept, but libpq doesn't directly expose portals.
regards, tom lane