Обсуждение: Getting fancy errors when accessing information_schema on 10.5

Hi all,

here is an example:

SELECT    sequence_name AS relname, sequence_schema AS schemaname
    FROM information_schema.sequences
    WHERE sequence_schema NOT LIKE 'pg\_%' AND sequence_schema != ‚information_schema'': SQLSTATE[42809]: Wrong object
type:7 ERROR: "pg_statistic" is not a sequence 

This does not happen as SUPERUSER.
Is this a known bug?

Any help appreciated.

Axel
---
PGP-Key:29E99DD6  ☀  computing @ chaos claudius

Axel Rau wrote:
> here is an example:
> 
> SELECT    sequence_name AS relname, sequence_schema AS schemaname
>     FROM information_schema.sequences
>     WHERE sequence_schema NOT LIKE 'pg\_%' AND sequence_schema != ‚information_schema'': SQLSTATE[42809]: Wrong
objecttype: 7 ERROR: "pg_statistic" is not a sequence
 
> 
> This does not happen as SUPERUSER.
> Is this a known bug?

The statement as you wrote it is syntactically incorrect.
Did some program mutilate your quotes in transfer?

Anyway, the error message does not make much sense.
Could you run EXPLAIN on the query and tell us the execution plan?

Perhaps this is some fancy kind of catalog corruption...

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Axel Rau <Axel.Rau@Chaos1.DE> writes:
>> Am 30.10.2018 um 08:42 schrieb Laurenz Albe <laurenz.albe@cybertec.at>:
>> Could you run EXPLAIN on the query and tell us the execution plan?

> EXPLAIN  SELECT sequence_name AS relname, sequence_schema AS schemaname
> FROM information_schema.sequences
> WHERE sequence_schema NOT LIKE 'pg\_%' AND sequence_schema != 'information_schema';
> ...
>                      ->  Seq Scan on pg_class c  (cost=0.00..28.56 rows=22 width=72)
>                            Filter: ((pg_has_role(relowner, 'USAGE'::text) OR has_sequence_privilege(oid, 'SELECT,
UPDATE,USAGE'::text)) AND (relkind = 'S'::"char")) 

Well, there's the problem: for some reason the planner is deciding to
execute the privilege test before the relkind check.

>> Perhaps this is some fancy kind of catalog corruption…

> Maybe, as this does not happen with another instance.

It doesn't happen for me either.  Looking at the planner code, it seems
like the relkind check should happen first because it'd be cheaper than
the OR condition.  Have you perhaps messed with the cost attributed to
pg_has_role(), has_sequence_privilege(), or chareq()?  You could
investigate with, eg,

select oid::regprocedure, procost from pg_proc
  where proname = 'has_sequence_privilege';

            regards, tom lane

Axel Rau <Axel.Rau@Chaos1.DE> writes:
>> Am 30.10.2018 um 13:17 schrieb Tom Lane <tgl@sss.pgh.pa.us>:
>> It doesn't happen for me either.  Looking at the planner code, it seems
>> like the relkind check should happen first because it'd be cheaper than
>> the OR condition.  Have you perhaps messed with the cost attributed to
>> pg_has_role(), has_sequence_privilege(), or chareq()?

> Not by intention. The instance has some history, it go back to 8.x I think.

>> You could investigate with, eg,
>> 
>> select oid::regprocedure, procost from pg_proc
>> where proname = 'has_sequence_privilege';

> nextcloud=> select oid::regprocedure, procost from pg_proc
> nextcloud->  where proname = 'has_sequence_privilege';
>                   oid                   | procost 
> ----------------------------------------+---------
>  has_sequence_privilege(name,text,text) |       1
>  has_sequence_privilege(name,oid,text)  |       1
>  has_sequence_privilege(oid,text,text)  |       1
>  has_sequence_privilege(oid,oid,text)   |       1
>  has_sequence_privilege(text,text)      |       1
>  has_sequence_privilege(oid,text)       |       1
> (6 rows)

Hm, nothing surprising there, but what about the other two?

            regards, tom lane

Tom Lane wrote:
> It doesn't happen for me either.  Looking at the planner code, it seems
> like the relkind check should happen first because it'd be cheaper than
> the OR condition.

It is still unclear why the execution plan looks like that, but maybe
it would be more robust to change "has_sequence_privilege" so that it
just returns FALSE if the argument is not a sequence.

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Tom Lane wrote:
>> It doesn't happen for me either.  Looking at the planner code, it seems
>> like the relkind check should happen first because it'd be cheaper than
>> the OR condition.

> It is still unclear why the execution plan looks like that, but maybe
> it would be more robust to change "has_sequence_privilege" so that it
> just returns FALSE if the argument is not a sequence.

I was wondering about that, but somewhere along there we'd be losing
all semblance of error checking on the OID argument, so it's not all
that attractive a solution.  I'd prefer to understand why this isn't
behaving the same as it does for other people before we resort to that.

Axel, would you try two more things on that DB?

explain select ((pg_has_role(relowner, 'USAGE'::text) OR has_sequence_privilege(oid, 'SELECT, UPDATE, USAGE'::text)))
frompg_class; 

explain select (relkind = 'S'::"char") from pg_class;

That's just to positively confirm that the planner thinks the former
expression is more expensive than the latter.

Assuming that it does, the only other answer I can think of is that
there's something wrong with the insertion sort code in
order_qual_clauses.  Pretty hard to see what, though.

            regards, tom lane

Axel Rau wrote:
> nextcloud=> explain select ((pg_has_role(relowner, 'USAGE'::text) OR has_sequence_privilege(oid, 'SELECT, UPDATE,
USAGE'::text)))from pg_class;
 
>                         QUERY PLAN                         
> -----------------------------------------------------------
>  Seq Scan on pg_class  (cost=0.00..28.56 rows=656 width=1)
> (1 row)
> 
> nextcloud=> explain select (relkind = 'S'::"char") from pg_class;
>                         QUERY PLAN                         
> -----------------------------------------------------------
>  Seq Scan on pg_class  (cost=0.00..28.56 rows=656 width=1)
> (1 row)

Hm, strange, for me the first query is slightly more expensive (because
of the OR), but the costs are pretty similar.

Another idea I have is to raise the costs of "has_sequence_privilege"
slightly.

Yours,
Laurenz Albe

Axel Rau <Axel.Rau@Chaos1.DE> writes:
> Am 30.10.2018 um 14:45 schrieb Tom Lane <tgl@sss.pgh.pa.us>:
>> Axel, would you try two more things on that DB?

> nextcloud=> explain select ((pg_has_role(relowner, 'USAGE'::text) OR has_sequence_privilege(oid, 'SELECT, UPDATE,
USAGE'::text)))from pg_class; 
>                         QUERY PLAN
> -----------------------------------------------------------
>  Seq Scan on pg_class  (cost=0.00..28.56 rows=656 width=1)
> (1 row)

> nextcloud=> explain select (relkind = 'S'::"char") from pg_class;
>                         QUERY PLAN
> -----------------------------------------------------------
>  Seq Scan on pg_class  (cost=0.00..28.56 rows=656 width=1)
> (1 row)

That is ... odd.  Is it possible that you have cpu_operator_cost set
to zero, or some very tiny number?

            regards, tom lane

Axel Rau <Axel.Rau@chaos1.de> writes:
>> Am 30.10.2018 um 16:04 schrieb Tom Lane <tgl@sss.pgh.pa.us>:
>> That is ... odd.  Is it possible that you have cpu_operator_cost set
>> to zero, or some very tiny number?

> Yes:

> cpu_index_tuple_cost = 0.01
> cpu_operator_cost = 0

Ah, well that explains why the clauses are seen as being the same cost
--- the function procost values are scaled by cpu_operator_cost, and
twice zero is still zero.

It probably is not a bright idea to have things set that way.

I somewhat agree with Laurenz's idea of bumping up the procost
settings for the privilege-check functions, as that would help
prevent this sort of issue in future --- but it won't do anything
to help if cpu_operator_cost is zero.

Another thought here is that the reason the relkind check ended up
at the end in the first place is that it was torn apart to make an
EquivalenceClass, and then reassembled when we found we couldn't
do anything very interesting with the EC, and the reassembled clause
just gets tacked onto the end of the relevant clause list since we
no longer have any idea where it was in the list originally.  So
that's how it is that order_qual_clauses found the entries in the
"wrong" order to start with.  So one kluge worth thinking about is
to give some preference in the order_qual_clauses sort to clauses
that came from an EC.  This isn't as totally random as it might
seem: EC clauses are less likely to have unpleasant side effects,
since you wouldn't normally expect an equality operator to fail.

But that only fixes it for cases like "relkind = something",
and a lot of the checks in the information_schema are more like
"relkind IN (x, y, z)".  Those aren't handled as ECs; worse,
they'll have cost higher than one cpu_operator_cost.  I'm a bit
surprised we've not seen other problems of this kind.

We may be forced to the other idea of reducing the relkind checks
to non-errors.  At least now we understand why.

I wonder though: if you apply has_sequence_privilege() to a
non-sequence, or similar cases for other has_xxx functions,
should that result in FALSE, or NULL?  It's not immediately
clear which answer is better.

            regards, tom lane

Axel Rau <Axel.Rau@chaos1.de> writes:
>> Am 30.10.2018 um 16:04 schrieb Tom Lane <tgl@sss.pgh.pa.us>:
>> That is ... odd.  Is it possible that you have cpu_operator_cost set
>> to zero, or some very tiny number?

> Yes:

> cpu_index_tuple_cost = 0.01
> cpu_operator_cost = 0

Ah, well that explains why the clauses are seen as being the same cost
--- the function procost values are scaled by cpu_operator_cost, and
twice zero is still zero.

It probably is not a bright idea to have things set that way.

I somewhat agree with Laurenz's idea of bumping up the procost
settings for the privilege-check functions, as that would help
prevent this sort of issue in future --- but it won't do anything
to help if cpu_operator_cost is zero.

Another thought here is that the reason the relkind check ended up
at the end in the first place is that it was torn apart to make an
EquivalenceClass, and then reassembled when we found we couldn't
do anything very interesting with the EC, and the reassembled clause
just gets tacked onto the end of the relevant clause list since we
no longer have any idea where it was in the list originally.  So
that's how it is that order_qual_clauses found the entries in the
"wrong" order to start with.  So one kluge worth thinking about is
to give some preference in the order_qual_clauses sort to clauses
that came from an EC.  This isn't as totally random as it might
seem: EC clauses are less likely to have unpleasant side effects,
since you wouldn't normally expect an equality operator to fail.

But that only fixes it for cases like "relkind = something",
and a lot of the checks in the information_schema are more like
"relkind IN (x, y, z)".  Those aren't handled as ECs; worse,
they'll have cost higher than one cpu_operator_cost.  I'm a bit
surprised we've not seen other problems of this kind.

We may be forced to the other idea of reducing the relkind checks
to non-errors.  At least now we understand why.

I wonder though: if you apply has_sequence_privilege() to a
non-sequence, or similar cases for other has_xxx functions,
should that result in FALSE, or NULL?  It's not immediately
clear which answer is better.

            regards, tom lane