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

From: Sebastien Flaesch <[email-masked]>
Date: Mon, 8 Apr 2024 16:25:13 +0000

Here a first PQtrace() file... assuming it can help.
Seb
________________________________
From: Sebastien Flaesch <[email-masked]>
Sent: Monday, April 8, 2024 5:31 PM
To: pgsql-general <[email-masked]>
Cc: Sebastien Flaesch <[email-masked]>
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

---

From: Tom Lane <[email-masked]>
Date: Mon, 08 Apr 2024 13:36:51 -0400

> 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

---

From: Sebastien Flaesch <[email-masked]>
Date: Mon, 8 Apr 2024 17:44:01 +0000

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 <[email-masked]>
Sent: Monday, April 8, 2024 7:36 PM
To: Sebastien Flaesch <[email-masked]>
Cc: pgsql-general <[email-masked]>
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.

                        regards, tom lane