Avoid a potential unstable test case: xmlmap.sql - Mailing list pgsql-hackers

From Andy Fan
Subject Avoid a potential unstable test case: xmlmap.sql
Date
Msg-id CAKU4AWpK8MW=2C9J_Lufe=omzX8ZU3g0oA8xVpwXLA6mh4JVig@mail.gmail.com
Whole thread Raw
Responses Re: Avoid a potential unstable test case: xmlmap.sql
Re: Avoid a potential unstable test case: xmlmap.sql
List pgsql-hackers

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

pgsql-hackers by date:

Previous
From: Michail Nikolaev
Date:
Subject: Re: Replace known_assigned_xids_lck by memory barrier
Next
From: Andy Fan
Date:
Subject: Re: Avoid a potential unstable test case: xmlmap.sql