Re: 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(dot)klaver(at)aklaver(dot)com