Thread: [MASSMAIL]prepared statement "cu1" already exists (but it does not)

[MASSMAIL]prepared statement "cu1" already exists (but it does not)

From
Sebastien Flaesch
Date:
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

Re: prepared statement "cu1" already exists (but it does not)

From
Sebastien Flaesch
Date:
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)
 
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

Re: prepared statement "cu1" already exists (but it does not)

From
Dominique Devienne
Date:
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

Re: prepared statement "cu1" already exists (but it does not)

From
Tom Lane
Date:
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



Re: prepared statement "cu1" already exists (but it does not)

From
Sebastien Flaesch
Date:
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)
 
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
Attachment

Re: prepared statement "cu1" already exists (but it does not)

From
Sebastien Flaesch
Date:
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.

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)
 
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

Re: prepared statement "cu1" already exists (but it does not)

From
Tom Lane
Date:
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



Re: prepared statement "cu1" already exists (but it does not)

From
Sebastien Flaesch
Date:
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)
 
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