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.klaver@aklaver.com