Thread: Avoid a potential unstable test case: xmlmap.sql

Avoid a potential unstable test case: xmlmap.sql

From
Andy Fan
Date:

Hi:

In the test case of  xmlmap.sql, we have the query below under schema_to_xml.

explain (costs off, verbose) 
SELECT oid FROM pg_catalog.pg_class 
WHERE  relnamespace = 28601 
AND relkind IN ('r','m','v') 
AND pg_catalog.has_table_privilege (oid, 'SELECT') 
ORDER BY relname;

If the query is using SeqScan, the execution order of the quals is:

has_table_privilege(pg_class.oid, 'SELECT'::text) AND (pg_class.relnamespace = '28601'::oid) AND (pg_class.relkind = ANY ('{r,m,v}'::"char"[]))

based on current cost setting and algorithm.  With this plan,  
has_table_privilege(pg_class.oid, 'SELECT'::text)  may be executed
against all the relations (not just the given namespace), so if a
tuple in pg_class is scanned and before has_table_privilege is called,
the relation is dropped, then we will get error:

ERROR:  relation with OID xxx does not exist

To overcome this,  if disabling the seqscan, then only index scan on
relnamespace is possible,  so relnamespace = '28601'::oid will be filtered
first before calling has_table_privilege.  and in this test case, we are sure
the relation belonging to the current namespace will never be dropped, so
no error is possible.  Here is the plan for reference:

Seq Scan:

 Sort
   Output: oid, relname
   Sort Key: pg_class.relname
   ->  Seq Scan on pg_catalog.pg_class
         Output: oid, relname
         Filter: (has_table_privilege(pg_class.oid, 'SELECT'::text) AND (pg_class.relnamespace = '28601'::oid) AND (pg_class.relkind = ANY ('{r,m,v}'::"char"[])))

enable_seqscan to off

                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_class_relname_nsp_index on pg_catalog.pg_class
   Output: oid, relname
   Index Cond: (pg_class.relnamespace = '28601'::oid)
   Filter: (has_table_privilege(pg_class.oid, 'SELECT'::text) AND (pg_class.relkind = ANY ('{r,m,v}'::"char"[])))

Patch is attached. 

--
Best Regards
Andy Fan
Attachment

Re: Avoid a potential unstable test case: xmlmap.sql

From
Andy Fan
Date:
I overlooked the fact even in the bitmap index scan loose mode,  the recheck
is still executed  before the qual, so bitmap index scan is OK in this case.

 Sort
   Output: oid, relname
   Sort Key: pg_class.relname
   ->  Bitmap Heap Scan on pg_catalog.pg_class
         Output: oid, relname
         Recheck Cond: (pg_class.relnamespace = '28601'::oid)
         Filter: (has_table_privilege(pg_class.oid, 'SELECT'::text) AND (pg_class.relkind = ANY ('{r,m,v}'::"char"[])))
         ->  Bitmap Index Scan on pg_class_relname_nsp_index
               Index Cond: (pg_class.relnamespace = '28601'::oid)

v2 attached. 

--
Best Regards
Andy Fan
Attachment

Re: Avoid a potential unstable test case: xmlmap.sql

From
Alexander Lakhin
Date:
Hi Andy,

15.08.2023 14:09, Andy Fan wrote:
>
> Hi:
>
> In the test case of xmlmap.sql, we have the query below under schema_to_xml.
>

Please look at the bug #18014:
https://www.postgresql.org/message-id/flat/18014-28c81cb79d44295d%40postgresql.org
There were other aspects of the xmlmap test failure discussed in that thread as well.

Best regards,
Alexander



Re: Avoid a potential unstable test case: xmlmap.sql

From
Andy Fan
Date:

Please look at the bug #18014:
https://www.postgresql.org/message-id/flat/18014-28c81cb79d44295d%40postgresql.org
There were other aspects of the xmlmap test failure discussed in that thread as well.

Thank you Alexander for the information,   I will go through there for discussion. 

--
Best Regards
Andy Fan