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

[Andy Fan]

>
>
> 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

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

[Alexander Lakhin]

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

[Andy Fan]

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


v2-0001-Avoid-a-potential-unstable-testcase.patch
Description: Binary data

Avoid a potential unstable test case: xmlmap.sql

[Andy Fan]

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


v1-0001-Avoid-a-potential-unstable-testcase.patch
Description: Binary data