YSQL: SELECT query agains system catalog tables raises error: "Illegal state: Transaction for catalog table write operation 'pg_largeobject_metadata' not found"

[mtakahar]

Jira Link: DB-4794
This query is generated by SQLsmith. May be a duplicate of #9645 but it's not clear to me if that's the case. The query contains a TABLESAMPLE clause that has not been supported by Yugabyte DB yet (#4288) but hits the illegal state error without it, too.

select
   cast(nullif(
       pg_catalog.bit_and(
         cast(cast(null as int2) as int2)) over (partition by ref_0.hasindexes order by ref_0.tableowner,ref_0.schemaname),
     case when EXISTS (
         select
             ref_0.tablename as c0,
             sample_0.fdwacl as c1,
             sample_0.fdwacl as c2
           from
             pg_catalog.pg_foreign_data_wrapper as sample_0 tablesample system (9.1)
           where cast(null as tinterval) #< cast(null as reltime)
           limit 85) then cast(coalesce(pg_catalog.int2mod(
           cast(cast(null as int2) as int2),
           cast((select indnatts from pg_catalog.pg_index limit 1 offset 2)
              as int2)),

           pg_catalog.min(
             cast(cast(null as int2) as int2)) over (partition by ref_0.hastriggers,ref_0.tablename order by ref_0.tablespace)) as int2) else cast(coalesce(pg_catalog.int2mod(
           cast(cast(null as int2) as int2),
           cast((select indnatts from pg_catalog.pg_index limit 1 offset 2)
              as int2)),

           pg_catalog.min(
             cast(cast(null as int2) as int2)) over (partition by ref_0.hastriggers,ref_0.tablename order by ref_0.tablespace)) as int2) end
       ) as int2) as c0,
   ref_0.rowsecurity as c1,
   ref_0.schemaname as c2,
   cast(nullif(ref_0.tableowner,
     pg_catalog.current_user()) as name) as c3
 from
   pg_catalog.pg_tables as ref_0
 where pg_catalog.pg_my_temp_schema() <> pg_catalog.oidlarger(
     cast(pg_catalog.lo_create(
       cast(pg_catalog.pg_my_temp_schema() as oid)) as oid),
     cast(pg_catalog.pg_my_temp_schema() as oid))
 limit 128;

ERROR: Illegal state: Transaction for catalog table write operation 'pg_largeobject_metadata' not found

[regbo]

I can confirm that this also causes errors in Hibernate 6.1 beta.

[mtakahar]

The error message stopped saying Illegal state: on recent master but the rest is still the same.

yugabyte-# ERROR:  Transaction for catalog table write operation 'pg_largeobject_metadata' not found
yugabyte=# select version();
                                                              version                                                               
------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2-YB-2.19.3.0-b0 on x86_64-apple-darwin22.6.0, compiled by Apple clang version 14.0.3 (clang-1403.0.22.14.1), 64-bit
(1 row)

The pg_largeobject_metadata itself does exist:

yugabyte=# \d pg_largeobject_metadata
      Table "pg_catalog.pg_largeobject_metadata"
  Column  |   Type    | Collation | Nullable | Default 
----------+-----------+-----------+----------+---------
 lomowner | oid       |           | not null | 
 lomacl   | aclitem[] |           |          | 
Indexes:
    "pg_largeobject_metadata_oid_index" PRIMARY KEY, lsm (oid ASC)