Re: Determine server version from psql script

[Tom Lane]

Igor Korot  writes:
> On Sat, Mar 22, 2025, 8:58 PM David G. Johnston 
> wrote:
>> Then read the psql docs.  Your version has \if and you’ll find server
>> version listed as the available client variables.

> I was hoping for something like

> If server_version >= X:
>  CREATE OR REPLACE TRIGGER...

psql's \if doesn't (yet) have any native expression evaluation
ability, so you have to farm out the ">=" comparison.  The
psql docs suggest relying on the server to do it, which would
go along the lines of

select current_setting('server_version_num')::int >= 13 as v13
\gset
\if :v13
   ... do something
\else
   ... do something else
\endif

You could also do the comparison client-side, along the lines of

\set v13 `expr :SERVER_VERSION_NUM \>= 13`
\if :v13
  ... etc

But that introduces assorted platform dependencies and requires
close attention to correct shell quoting, so it's seldom
preferable IMO.

regards, tom lane

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

[Adrian Klaver]

On 3/18/25 23:41, Sebastien Flaesch wrote:
You are right Adrian, I did not search properly I found the header file 
here:


sf@toro:/opt3/dbs/pgs/17.4$ ls -l 
include/postgresql/server/catalog/pg_type_d.h
-rw-r--r-- 1 sf sf 9672 Mar 13 17:05 
include/postgresql/server/catalog/pg_type_d.h


I was not expecting this file to be in a "server" folder, when it's to 
be used for client apps.


Not surprising. As I understand it this is the code used to build the 
type entries in the system catalog pg_type. As was mentioned in your 
previous link:


https://www.postgresql.org/docs/17/libpq-exec.html#LIBPQ-PQFTYPE

the suggested way to get type information is:

"You can query the system table pg_type to obtain the names and 
properties of the various data types. "




And still, I do not trust the content.


Then do as suggested above.



Seb




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000

[Kevin Stephenson]

Christophe and Tom, thank you for your responses, but I'm still a bit confused. 
In my original email, the Test 2 case is allowing a ROLLBACK in the EXCEPTION 
clause without throwing an error. Is it a NOP ROLLBACK being applied to an 
aborted subTX, a real full ROLLBACK, or something else? Please advise.

Thanks,
Kevin Stephenson

From: Tom Lane 
Sent: Saturday, March 22, 2025 7:59 AM
To: Christophe Pettus 
Cc: Kevin Stephenson ; pgsql-gene...@postgresql.org 

Subject: Re: Nested Stored Procedures - ERROR: invalid transaction termination 
2D000

Christophe Pettus  writes:
> A procedure cannot issue top-level transaction control statements from within 
> an exception block, and attempting to do so raises the error you saw.  This 
> includes procedures that are called from within an exception block.

Yeah.  Postgres doesn't have autonomous transactions (not yet anyway),
and you can't fake them like that.

A way that does work, I believe, is to set up a second session with
dblink[1] and use that to issue the autonomous transaction.  Ugly
and inefficient for sure, but if you've gotta have it...

regards, tom lane

[1] 
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Fdblink.html&data=05%7C02%7C%7Ce846300d6b9c402760ec08dd69521aad%7C84df9e7fe9f640afb435%7C1%7C0%7C638782523529471489%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=2Rn9iT1VcDJgCXesww3AcwD16UIWE3HsEgniD0Byodk%3D&reserved=0

Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000

[Tom Lane]

Kevin Stephenson  writes:
> Christophe and Tom, thank you for your responses, but I'm still a bit 
> confused. In my original email, the Test 2 case is allowing a ROLLBACK in the 
> EXCEPTION clause without throwing an error. Is it a NOP ROLLBACK being 
> applied to an aborted subTX, a real full ROLLBACK, or something else? Please 
> advise.

The sub-transaction only exists for the body of the BEGIN construct,
up until EXCEPTION.  By the time control arrives at an exception
handler, we've rolled back the sub-xact and are executing in the outer
transaction again.  So if that's a top-level transaction, you can roll
it back, but if it's a subtransaction you can't.

regards, tom lane

Re: Determine server version from psql script

[Tom Lane]

Adrian Klaver  writes:
> On 3/22/25 17:31, Igor Korot wrote:
>> Is there a way to determine the server version from such a script?

> show server_version_num;

psql already populates its SERVER_VERSION_NUM variable from that
for you.

regards, tom lane

Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000

[Christophe Pettus]

> On Mar 22, 2025, at 21:37, Kevin Stephenson  wrote:
> 
> Christophe and Tom, thank you for your responses, but I'm still a bit 
> confused. In my original email, the Test 2 case is allowing a ROLLBACK in the 
> EXCEPTION clause without throwing an error. Is it a NOP ROLLBACK being 
> applied to an aborted subTX, a real full ROLLBACK, or something else? Please 
> advise.

That's an interesting question.  It appears to be a no-op, although a quick 
scan of the code doesn't reveal why.  Here's an illustrative test case:

xof=# CREATE OR REPLACE PROCEDURE outer() AS $$
BEGIN
   INSERT INTO t VALUES(3);
   BEGIN
  CALL inner();
  PERFORM 1/0;
   EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE 'in outer exception handler';
   END;
END;
$$ language plpgsql;
CREATE PROCEDURE

xof=# create or replace procedure inner() as $$
BEGIN
   BEGIN
  INSERT INTO t VALUES(1);
  PERFORM 1/0;
   EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  INSERT INTO t VALUES(2);
   END;
END; 
$$
language plpgsql;
CREATE PROCEDURE

xof=# call outer();
NOTICE:  in outer exception handler
CALL

xof=# table t;
 i  
---
 3
(1 row)

xof=# truncate t;
TRUNCATE TABLE

xof=# call inner();
CALL

xof=# table t;
 i  
---
 2
(1 row)

It clearly doesn't roll back the outer transaction.  The savepoint that BEGIN 
... EXCEPTION creates is released upon entry into the EXCEPTION block, so 
there's no savepoint in that context to roll back to.

Pragmatically, the answer is: don't put top-level transaction control 
statements in procedures where they might be invoked within an EXCEPTION block, 
either directly or indirectly.

Re: Determine server version from psql script

[David G. Johnston]

On Saturday, March 22, 2025, Igor Korot  wrote:

>
>
>> Is it actually running in psql?
>>
>
> Yes, i run "psql - d draft -a -f 
>

Then read the psql docs.  Your version has \if and you’ll find server
version listed as the available client variables.

David J.

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

[Sebastien Flaesch]

David,
That said, I am curious as to the education flow a developer, not linking in 
this specific header to their code, would go through in order to learn about 
type OIDs and make use of them in their project.  Maybe that flow is good, 
maybe not.  It's a rare flow and there are many things to do in the project.  
So my curiosity may not get satiated.  As you brought this up and are invested 
in the outcome you have more motivation than probably anyone else to dive into 
it and make concrete suggestions for change.

Any project using the PostgreSQL C API to implement an interface/module for 
another programming language will need to implement basic SQL handling 
functions to prepare, execute, fetch rows, AND introspect SQL statements parts 
like column information (name, type).

We have for ex a SQL handler class in Genero BDL, providing following methods:

  DEFINE h base.SqlHandle
  LET h = base.SqlHandle.create()
  CALL h.prepare("SELECT ")
  CALL h.open()
  DISPLAY h.getResultType(1) : type name of column #1
  DISPLAY h.getResultType(2) : type name of column #2
  ...


About the PQftype() doc, the header file catalog/pg_type_d.h is mentioned, but 
I think it is missing the fact that type OID constants can be recognized in 
that header file, with the "OID" suffix.

While I understand that it's additional work on doc maintenance, I would in 
fact expect an exhaustive list of built-in data type ids just like in:

https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types?view=sql-server-ver16

https://dev.mysql.com/doc/c-api/8.4/en/c-api-prepared-statement-type-codes.html

https://www.ibm.com/docs/en/informix-servers/15.0.0?topic=constants-sql-data-type#ids_esqlc_0123__sii-03-97666

But I can understand that this is maybe not natural for PostgreSQL 
implementors, because of the flexible data type system and the fact that even 
built-in type ids are generated.

Seb






From: David G. Johnston 
Sent: Thursday, March 20, 2025 5:25 PM
To: Sebastien Flaesch 
Cc: Tom Lane ; Adrian Klaver ; M 
Tarkeshwar Rao ; pgsql-gene...@postgresql.org 

Subject: Re: After upgrading libpq, the same function(PQftype) call returns a 
different OID


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

On Thu, Mar 20, 2025 at 8:42 AM Sebastien Flaesch 
mailto:sebastien.flae...@4js.com>> wrote:

/*
 * Backwards compatibility for ancient random spellings of pg_type OID macros.
 * Don't use these names in new code.
 */
#define CASHOID MONEYOID
#define LSNOID  PG_LSNOID

#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21
#define INT2VECTOROID 22
#define INT4OID 23
#define REGPROCOID 24

If I am missing something, then please point me to the correct .h file that 
contains #define constants without this scary comment.

OR ( I guess I start to understand the code... ) it this comment only for:

Yes, that blank line separating LSNOID and BOOLOID blocks the comment from 
applying to the items after the blank line.  That is a fairly common 
convention, using whitespace to break things up.  Also, assigning one macro to 
another is quite distinct from assigning a constant to a name; making the 
"backward compatibility" aspect of this comment only meaningfully apply to 
those two items.


And sorry if I consider constant names like these (without any prefix such as 
PG_TYPE_)

We spelled PG_TYPE_  as OID and put it on the end.  A boolean as an object is 
by definition a type.  Context clues are important, not every pattern gets 
spelled out in prose.


#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT8OID 20
#define INT2OID 21

Arrogance does not help here, clarity and better API doc would.


To my knowledge the current API doc for this hasn't had any comments of this 
sort for a very long time.  All documentation can be improved because every 
reader comes at it from a different starting point.  Do you have a concrete 
suggestion for what you think should be changed, and why?  My take away from 
this thread is that a single report isn't usually enough to go searching hard 
for ways to tweak the documentation for readability; nor has this one pointed 
out any outright errors to be fixed.  In short, we expect that some subset of 
readers will ask us questions on the mailing list because that is the reality 
of things.

That said, I am curious as to the education flow a developer, not linking in 
this specific header to their code, would go through in order to learn about 
type OIDs and make use of them in their project.  Maybe that flow is good, 
maybe not.  It's a rare flow and there are many things to do in the project.  
So my curiosity may not get satiated.  As you brought this up and are invested 
in the outcome you have more motivation than probably anyone else to dive into 
it and make concrete suggestions for change.

All that said, a co

Getting all the plans.

[Senthilnathan M]

Hi all,
  I came across this extension which shows all the plans considered by the
optimizer: https://github.com/misachi/pg_all_plans

  Is there a way we can get this information directly instead of using an
extension?  If not, does it make sense to add native support?

  Please feel free to redirect me if this is not the right mailing list for
this topic.

Thanks,
Senthil

Re: Getting all the plans.

[Tom Lane]

Senthilnathan M  writes:
>   I came across this extension which shows all the plans considered by the
> optimizer: https://github.com/misachi/pg_all_plans

>   Is there a way we can get this information directly instead of using an
> extension?  If not, does it make sense to add native support?

The reason it's an extension is that the idea has already been
rejected (many times) by the core project.

FYI, this particular version is not showing you anywhere near "all"
the considered plans.  That's impossible really because the planner
prunes the search space as heavily as it can.  Most potential plans
are not carried as far as generating a complete Path tree in the
first place.  Even the ones that do get to the "final rel" stage
will be thrown away if they are dominated by some other one on
all the planner's figures-of-merit.  So this is going to show you
only a small fraction of the possible plans.  If you are unhappy
because you suspect the planner rejected what would really have
been the best plan, the odds are good this won't help you because
the plan you want to see didn't survive long enough to be shown.

regards, tom lane

Nested Stored Procedures - ERROR: invalid transaction termination 2D000

[Kevin Stephenson]

Hi all,

I'm assessing the feasibility of implementing a full featured "DB as API" 
concept in PostgreSQL (PG) and have run across an apparent inconsistency in the 
transaction (TX) handling behavior with nested stored procedures. This apparent 
inconsistency is present in both 16.4 and 17.4 running on Linux. I'm using 
pgAdmin and other clients with the default autocommit behavior (i.e. no 
AUTOCOMMIT OFF magic START TRANSACTION; commands are being sent by the clients).

Per my understanding of the docs and some PG source code review:


  *
When a top-level stored procedure is called it implicitly creates a TX if there 
is no current TX.
  *
When a nested stored procedure is called it implicitly creates a subTX for that 
invocation.
  *
When a BEGIN/EXCEPTION block is used it implicitly creates a subTX for that 
block.

(It is not clear if a top-level procedure that uses BEGIN/EXCEPTION in the 
outermost block bothers with a subTX as it would be logically coincident with 
the main TX. A similar situation exists for nested procedures that use 
BEGIN/EXCEPTION, i.e., is there a coincident subTX inside a subTX?)

In my testing, as shown in the script below, when using structured exception 
handling in nested stored procedures with an autonomous TX workaround (for 
error logging), results in error 2D000 (see Test 3). Verbose logging shows it 
to be caused by function _SPI_rollback() at line 400 (16.4) or 399 (17.4) in 
spi.c. What seems inconsistent is that if the outer procedure does not use an 
EXCEPTION block (see Test 2 ), 2D000 is not thrown and the autonomous TX 
workaround works as desired.

Please advise if this is expected behavior.

Much thanks,
Kevin Stephenson

-- WARNING: Script contains DROP statements.
-- Greatly simplified schema for demonstration.
DROP TABLE IF EXISTS public.error_log;
CREATE TABLE public.error_log (
logging_routine_nametext NULL,
sqlstatetext NULL,
sqlerrm text NULL
);

DROP TABLE IF EXISTS public.dummy;
CREATE TABLE public.dummy (
datatext NULL
);

CREATE OR REPLACE PROCEDURE public.inner_proc()
LANGUAGE plpgsql AS $$
DECLARE
dummy_var int;

BEGIN
-- Assuming subTX implicitly starts under (main) TX 'A'
INSERT INTO public.dummy (data) VALUES ('inner_proc');
dummy_var = 1/0;

EXCEPTION
-- Assuming only subTX implicitly rolled back
WHEN OTHERS THEN
-- Autonomous TX workaround.
ROLLBACK; -- rollback TX 'A' and start new TX 'B'

INSERT INTO public.error_log (logging_routine_name, sqlstate, sqlerrm)
VALUES ('inner_proc', SQLSTATE, SQLERRM);

-- commit TX 'B' and start new TX 'C'
COMMIT;
-- Autonomous TX workaround finished.

-- Rethrow for caller to handle.
RAISE;

END;$$;

CREATE OR REPLACE PROCEDURE public.outer_proc_simple()
LANGUAGE plpgsql AS $$
BEGIN
-- TX 'A' starts here
-- Simple example with no exception handling in outer proc.
INSERT INTO public.dummy (data) VALUES ('outer_proc_simple');
CALL public.inner_proc();
-- TX 'C' in aborted state with uncaught exception bubbling up to caller.
END;$$;

CREATE OR REPLACE PROCEDURE public.outer_proc_complex()
LANGUAGE plpgsql AS $$
BEGIN
-- TX 'A' starts here
-- Complex example that allows additional error logging.
INSERT INTO public.dummy (data) VALUES ('outer_proc_complex');
CALL public.inner_proc();

EXCEPTION
WHEN OTHERS THEN
-- TX 'C' should already be in aborted state. Finish it off and start 
TX 'D'.
ROLLBACK;

INSERT INTO public.error_log (logging_routine_name, sqlstate, sqlerrm)
VALUES ('outer_proc', SQLSTATE, SQLERRM);

-- We want to rethrow again so commit TX 'D'.
COMMIT;
RAISE; -- app layer can handle as appropriate
END;$$;

-- Test 1 (Works as expected.)
CALL public.inner_proc();
/*
ERROR:  division by zero
CONTEXT:  ... (truncated for brevity)
*/
SELECT * FROM public.dummy;
-- empty result set
SELECT * FROM public.error_log;
-- inner_proc, 22012, division by zero

-- Test 2 (Works as expected.)
TRUNCATE TABLE public.dummy;
TRUNCATE TABLE public.error_log;
-- Note: Do not run TRUNCATEs and CALL in a single batch.
-- Creates an outer TX that would not be done in real use.
CALL public.outer_proc_simple();
/*
ERROR:  division by zero
CONTEXT:  ... (truncated for brevity)
*/
SELECT * FROM public.dummy;
-- empty result set
SELECT * FROM public.error_log;
-- inner_proc, 22012, division by zero

-- Test 3 (Fails?)
TRUNCATE TABLE public.dummy;
TRUNCATE TABLE public.error_log;
--
CALL public.outer_proc_complex();
/*
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function inner_proc() line 14 at ROLLBACK
SQL statement "CALL public.inner_proc()"
PL/pgSQL function outer_proc_complex() line 6 at CALL

SQL state: 2D000
*/
SELECT * FROM public.dummy;
-- empty result set
SELECT * FROM public.error_log;
-- outer_proc, 2D000, invalid transaction termination

-

Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000

[Christophe Pettus]

Hello,

> On Mar 22, 2025, at 08:38, Kevin Stephenson  wrote:
> • When a top-level stored procedure is called it implicitly creates a TX 
> if there is no current TX.
> • When a BEGIN/EXCEPTION block is used it implicitly creates a subTX for 
> that block.

These statements are correct.

> • When a nested stored procedure is called it implicitly creates a subTX 
> for that invocation.

This one is not.  (Although the behavior you are looking for may not depend on 
that.)

A procedure cannot issue top-level transaction control statements from within 
an exception block, and attempting to do so raises the error you saw.  This 
includes procedures that are called from within an exception block.

Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000

[Tom Lane]

Christophe Pettus  writes:
> A procedure cannot issue top-level transaction control statements from within 
> an exception block, and attempting to do so raises the error you saw.  This 
> includes procedures that are called from within an exception block.

Yeah.  Postgres doesn't have autonomous transactions (not yet anyway),
and you can't fake them like that.

A way that does work, I believe, is to set up a second session with
dblink[1] and use that to issue the autonomous transaction.  Ugly
and inefficient for sure, but if you've gotta have it...

regards, tom lane

[1] https://www.postgresql.org/docs/current/dblink.html

Re: Determine server version from psql script

[Igor Korot]

Hi, Adrian,

On Sat, Mar 22, 2025, 7:42 PM Adrian Klaver 
wrote:

> On 3/22/25 17:31, Igor Korot wrote:
> > Hi, All,
> > I have a big script that populated the DB for me.
>
> The language used for the script?
>

What do you mean?
Its just a text file with bunch of create table/insert into


> Is it actually running in psql?
>

Yes, i run "psql - d draft -a -f 



> >
> > I made it based on the latest available version.
> >
> > However,  i have version 13 installed on my Linux box and so trying to
> > execute "CREATE OR REPLACE TRIGGER..." will fail.
> >
> > Is there a way to determine the server version from such a script?
>
>  From here:
>
>
> https://www.postgresql.org/docs/13/runtime-config-preset.html#GUC-SERVER-VERSION-NUM
>
> show server_version_num;
>
> Though how you use that is going to depend on the answers to the first
> two questions.
>
> >
> > Thank you.
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Determine server version from psql script

[Igor Korot]

Hi, All,
I have a big script that populated the DB for me.

I made it based on the latest available version.

However,  i have version 13 installed on my Linux box and so trying to
execute "CREATE OR REPLACE TRIGGER..." will fail.

Is there a way to determine the server version from such a script?

Thank you.

Re: Determine server version from psql script

[Igor Korot]

Hi, David,

On Sat, Mar 22, 2025, 8:58 PM David G. Johnston 
wrote:

> On Saturday, March 22, 2025, Igor Korot  wrote:
>
>>
>>
>>> Is it actually running in psql?
>>>
>>
>> Yes, i run "psql - d draft -a -f 
>>
>
> Then read the psql docs.  Your version has \if and you’ll find server
> version listed as the available client variables.
>

I was hoping for something like

If server_version >= X:
 CREATE OR REPLACE TRIGGER...
else:
 CREATE TRIGGER...

Python-like syntax here as I'm not sure how to do it properly...

Thank you.


> David J.
>
>

Re: Nested Stored Procedures - ERROR: invalid transaction termination 2D000

[Tom Lane]

Christophe Pettus  writes:
> That's an interesting question.  It appears to be a no-op, although a quick 
> scan of the code doesn't reveal why.  Here's an illustrative test case:

This test case would be less confusing if the outer handler did

  RAISE NOTICE 'in outer exception handler: %', sqlerrm;

With that, the test shows

regression=# call outer();
NOTICE:  in outer exception handler: invalid transaction termination
CALL

What is happening is that inner() does PERFORM 1/0, fails and bounces
out to its exception handler, and then the ROLLBACK throws an error
because we're still inside outer()'s subtransaction.  So inner()'s
first INSERT has been rolled back and the second one is never
reached.  Back at outer()'s exception handler, we trap the error
from ROLLBACK, abort that subtransaction, and go on our merry way,
allowing the original INSERT (which was outside both subtransactions)
to complete.

regards, tom lane