Thread: Can't query system tables during transaction

Hi, ALL,
I'm trying to execute following:

SELECT 1 FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace
AND c.relname = 'foo' AND n.nspname = public;

inside the transaction.

I'm getting the following error:

ERROR:  current transaction is aborted, commands ignored until end of
transaction block

Does this mean I can't query system tables during the transaction?
What is the problem here if it's not and how do I find out the reason?
And if it is - how to work around it?

I can probably commit it and start a new transaction, but I fear I will
have the same issue there...

Thank you.

If it matters - I'm working with C++ and libpq.

On 10/4/20 1:14 PM, Igor Korot wrote:
> Hi, ALL,
> I'm trying to execute following:
> 
> SELECT 1 FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace
> AND c.relname = 'foo' AND n.nspname = public;
> 
> inside the transaction.
> 
> I'm getting the following error:
> 
> ERROR:  current transaction is aborted, commands ignored until end of
> transaction block

No it means another statement before this one threw an error and the 
transaction needs to be rolled back. Something like this:

track_stocks(5442)=> begin ;
BEGIN
track_stocks(5442)=> SELECT 1 FROM pg_class c, pg_namespace n WHERE 
n.oid = c.relnamespace
AND c.relname = 'stock-info' AND n.nspname = public;
ERROR:  column "public" does not exist
LINE 2: AND c.relname = 'stock-info' AND n.nspname = public;
                                                      ^
track_stocks(5442)=> SELECT 1 FROM pg_class c, pg_namespace n WHERE 
n.oid = c.relnamespace
AND c.relname = 'stock-info' AND n.nspname = 'public';
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block

track_stocks(5442)=> rollback ;
ROLLBACK

And now the correct query(Note the quoted schema name):

track_stocks(5442)=> begin ;
BEGIN
track_stocks(5442)=> SELECT 1 FROM pg_class c, pg_namespace n WHERE 
n.oid = c.relnamespace
AND c.relname = 'stock-info' AND n.nspname = 'public';
  ?column?
----------
(0 rows)

> 
> Does this mean I can't query system tables during the transaction?
> What is the problem here if it's not and how do I find out the reason?
> And if it is - how to work around it?
> 
> I can probably commit it and start a new transaction, but I fear I will
> have the same issue there...
> 
> Thank you.
> 
> If it matters - I'm working with C++ and libpq.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Igor Korot <ikorot01@gmail.com> writes:
> I'm trying to execute following:

> SELECT 1 FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace
> AND c.relname = 'foo' AND n.nspname = public;

I suppose you meant to put quotes around 'public'?

> I'm getting the following error:
> ERROR:  current transaction is aborted, commands ignored until end of
> transaction block

This has nothing to do with the current command, but with failure
of some previous command in the transaction.

            regards, tom lane

Hi,

On Sun, Oct 4, 2020 at 3:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Igor Korot <ikorot01@gmail.com> writes:
> > I'm trying to execute following:
>
> > SELECT 1 FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace
> > AND c.relname = 'foo' AND n.nspname = public;
>
> I suppose you meant to put quotes around 'public'?

I suppose so as well. ;-)

>
> > I'm getting the following error:
> > ERROR:  current transaction is aborted, commands ignored until end of
> > transaction block
>
> This has nothing to do with the current command, but with failure
> of some previous command in the transaction.

Thank you.
I will try to track down the error.

>
>                         regards, tom lane