public inbox for [email protected]  

help / color / mirror / Atom feed

From: Sebastien Flaesch <[email protected]>
To: pgsql-general <[email protected]>
Cc: Sebastien Flaesch <[email protected]>
Subject: prepared statement "cu1" already exists (but it does not)
Date: Mon, 8 Apr 2024 15:31:04 +0000
Message-ID: <AM9P191MB1286DAAE95E27A8FB79376D5B0002@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: prepared statement "cu1" already exists (but it does not)
  In-Reply-To: <AM9P191MB1286DAAE95E27A8FB79376D5B0002@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox