[YSQL] Batch nested loops closing connection when querying metadata tables due to invalid cumulative relations when getting batched clauses

[mislam-yb]

Jira Link: DB-5419

Description

When using batched nested loops, queries to information_schema cause the connection to close due to the following error seen on the postgres logs:

TRAP: FailedAssertion("!(!bms_overlap(cur_relgroup, cumulative_rels))", File: "../../../../../../../src/postgres/src/backend/optimizer/util/restrictinfo.c", Line: 519)

This error occured for the latest debug build for 2.17.2.0, and was reproduced by @tanujnay112. This error is identical to #15878 but is caused by a separate issue. Hence tracking this as a separate defect.

Reproduction:

set yb_bnl_batch_size = 2;

SELECT
	    COALESCE(Json_agg(Row_to_json(info)), '[]' :: json) AS tables
	  FROM (
	    WITH partitions AS (
	      SELECT array(
	        WITH partitioned_tables AS (SELECT array(SELECT oid FROM pg_class WHERE relkind = 'p') AS parent_tables)
	        SELECT
	        child.relname       AS partition
	    FROM partitioned_tables, pg_inherits
	        JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
	        JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
	    where ((nmsp_child.nspname='public'))
	    AND pg_inherits.inhparent = ANY (partitioned_tables.parent_tables)
	      ) AS names
	    )
	    SELECT
	      pgn.nspname AS table_schema,
	      pgc.relname AS table_name,
	      CASE
	        WHEN pgc.relkind = 'r' THEN 'TABLE'
	        WHEN pgc.relkind = 'f' THEN 'FOREIGN TABLE'
	        WHEN pgc.relkind = 'v' THEN 'VIEW'
	        WHEN pgc.relkind = 'm' THEN 'MATERIALIZED VIEW'
	        WHEN pgc.relkind = 'p' THEN 'PARTITIONED TABLE'
	      END AS table_type,
	      obj_description(pgc.oid) AS comment,
	      COALESCE(json_agg(DISTINCT row_to_json(isc) :: jsonb || jsonb_build_object('comment', col_description(pga.attrelid, pga.attnum))) filter (WHERE isc.column_name IS NOT NULL), '[]' :: json) AS columns,
	      COALESCE(json_agg(DISTINCT row_to_json(ist) :: jsonb || jsonb_build_object('comment', obj_description(pgt.oid))) filter (WHERE ist.trigger_name IS NOT NULL), '[]' :: json) AS triggers,
	      row_to_json(isv) AS view_info
	      FROM partitions, pg_class as pgc  
	      INNER JOIN pg_namespace as pgn
	        ON pgc.relnamespace = pgn.oid
	    /* columns */
	    /* This is a simplified version of how information_schema.columns was
	    ** implemented in postgres 9.5, but modified to support materialized
	    ** views.
	    */
	    LEFT OUTER JOIN pg_attribute AS pga
	      ON pga.attrelid = pgc.oid
	    LEFT OUTER JOIN (
	      SELECT
	        nc.nspname         AS table_schema,
	        c.relname          AS table_name,
	        a.attname          AS column_name,
	        a.attnum           AS ordinal_position,
	        pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
	        CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END AS is_nullable,
	        CASE WHEN t.typtype = 'd' THEN
	          CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
	               WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
	               ELSE 'USER-DEFINED' END
	        ELSE
	          CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
	               WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
	               ELSE 'USER-DEFINED' END
	        END AS data_type,
	        coalesce(bt.typname, t.typname) AS data_type_name
	      FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
	        JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
	        JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
	        LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
	          ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
	        LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
	          ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
	      WHERE (NOT pg_is_other_temp_schema(nc.oid))
	        AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'm', 'f', 'p')
	        AND (pg_has_role(c.relowner, 'USAGE')
	             OR has_column_privilege(c.oid, a.attnum,
	                                     'SELECT, INSERT, UPDATE, REFERENCES'))
	    ) AS isc
	      ON  isc.table_schema = pgn.nspname
	      AND isc.table_name   = pgc.relname
	      AND isc.column_name  = pga.attname
	  
	    /* triggers */
	    LEFT OUTER JOIN pg_trigger AS pgt
	      ON pgt.tgrelid = pgc.oid
	    LEFT OUTER JOIN information_schema.triggers AS ist
	      ON  ist.event_object_schema = pgn.nspname
	      AND ist.event_object_table  = pgc.relname
	      AND ist.trigger_name        = pgt.tgname
	  
	    /* This is a simplified version of how information_schema.views was
	    ** implemented in postgres 9.5, but modified to support materialized
	    ** views.
	    */
	    LEFT OUTER JOIN (
	      SELECT
	        nc.nspname         AS table_schema,
	        c.relname          AS table_name,
	        CASE WHEN pg_has_role(c.relowner, 'USAGE') THEN pg_get_viewdef(c.oid) ELSE null END AS view_definition,
	        CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20 THEN 'YES' ELSE 'NO' END AS is_updatable,
	        CASE WHEN pg_relation_is_updatable(c.oid, false) &  8 =  8 THEN 'YES' ELSE 'NO' END AS is_insertable_into,
	        CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81) THEN 'YES' ELSE 'NO' END AS is_trigger_updatable,
	        CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73) THEN 'YES' ELSE 'NO' END AS is_trigger_deletable,
	        CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69) THEN 'YES' ELSE 'NO' END AS is_trigger_insertable_into
	      FROM pg_namespace nc, pg_class c
	  
	      WHERE c.relnamespace = nc.oid
	        AND c.relkind in ('v', 'm')
	        AND (NOT pg_is_other_temp_schema(nc.oid))
	        AND (pg_has_role(c.relowner, 'USAGE')
	             OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
	             OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
	    ) AS isv
	      ON  isv.table_schema = pgn.nspname
	      AND isv.table_name   = pgc.relname
	  
	    WHERE
	      pgc.relkind IN ('r', 'v', 'f', 'm', 'p')
	      and ((pgn.nspname='public'))
	    GROUP BY pgc.oid, pgn.nspname, pgc.relname, table_type, isv.*
	  ) AS info;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.