Обсуждение: 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