Can't query system tables during transaction

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.