Re: Getting fancy errors when accessing information_schema on 10.5

Поиск
Список
Период
Сортировка
От Axel Rau
Тема Re: Getting fancy errors when accessing information_schema on 10.5
Дата
Msg-id 44D4C7B1-0DCF-4166-9DB8-B5D75EB2985E@Chaos1.DE
обсуждение исходный текст
Ответ на Re: Getting fancy errors when accessing information_schema on 10.5  (Laurenz Albe <laurenz.albe@cybertec.at>)
Ответы Re: Getting fancy errors when accessing information_schema on 10.5  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-admin
Дерево обсуждения


Am 30.10.2018 um 08:42 schrieb Laurenz Albe <laurenz.albe@cybertec.at>:

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 object type: 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?
Yes. Sorry for that.

Anyway, the error message does not make much sense.
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';
                                                                                                            QUERY PLAN                                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=28.78..44.52 rows=1 width=64)
   ->  Nested Loop  (cost=28.78..31.03 rows=1 width=132)
         ->  Hash Join  (cost=28.78..30.18 rows=1 width=72)
               Hash Cond: (s.seqrelid = c.oid)
               ->  Seq Scan on pg_sequence s  (cost=0.00..1.40 rows=40 width=4)
               ->  Hash  (cost=28.56..28.56 rows=22 width=72)
                     ->  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"))
         ->  Index Scan using pg_namespace_oid_index on pg_namespace nc  (cost=0.00..0.57 rows=1 width=68)
               Index Cond: (oid = c.relnamespace)
               Filter: ((NOT pg_is_other_temp_schema(oid)) AND (((nspname)::information_schema.sql_identifier)::text !~~ 'pg\_%'::text) AND (((nspname)::information_schema.sql_identifier)::text <> 'information_schema'::text))
   ->  Index Scan using pg_depend_depender_index on pg_depend  (cost=0.00..6.75 rows=1 width=4)
         Index Cond: ((classid = '1259'::oid) AND (objid = c.oid))
         Filter: (deptype = 'i'::"char")
(14 rows)


Perhaps this is some fancy kind of catalog corruption…

Maybe, as this does not happen with another instance.

Thanks, Axel
PS: The origin of the query is here:
---
PGP-Key:29E99DD6  ☀  computing @ chaos claudius

В списке pgsql-admin по дате отправления:

Предыдущее
От: Laurenz Albe
Дата:
Сообщение: Re: Getting fancy errors when accessing information_schema on 10.5
Следующее
От: Subodh Kumar
Дата:
Сообщение: creating table without columns