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

From: Sebastien Flaesch <[email-masked]>
Date: Tue, 9 Apr 2024 14:44:18 +0000

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 <[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.

Sebastien Flaesch <[email-masked]> 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

---

From: Tom Lane <[email-masked]>
Date: Tue, 09 Apr 2024 12:36:50 -0400

> 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

---

From: Sebastien Flaesch <[email-masked]>
Date: Tue, 9 Apr 2024 16:48:46 +0000

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

Seb
Sent: Tuesday, April 9, 2024 6:36 PM

                        regards, tom lane