The following bug has been logged online:
Bug reference: 5358
Logged by: Gurjeet Singh
Email address: singh.gurjeet@gmail.com
PostgreSQL version: 8.4.2
Operating system: Windows Vista 64bit
Description: Throwing unexpected ERROR
Details:
I am using Postgres Plus Standard Server
version: PostgreSQL 8.4.2, compiled by Visual C++ build 1400, 32-bit
create table public.test( a bytea, c text )
select relname, pg_relation_size( 'public."' || relname || '"' )/1024
from (select distinct relname
from (select r.relname, c.attname, c.attnotnull, t.typname
from pg_namespace as s, pg_class as r, pg_attribute as c, pg_type as t
where s.oid = r.relnamespace
and r.oid = c.attrelid
and c.atttypid = t.oid
and s.nspname = 'public'
and t.typname in ('bytea', 'text') ) as s1
) as s2
where pg_relation_size( 'public."' || relname || '"' ) <> 0;
ERROR: relation "public.pg_type" does not exist
********** Error **********
ERROR: relation "public.pg_type" does not exist
SQL state: 42P01
When I comment out the last WHERE clause, the query runs fine.
It seems that the WHERE predicate is being pushed into the subqueries a bit
too soon.
Here's the EXPLAIN:
Subquery Scan s2 (cost=123.50..124.23 rows=21 width=64)
-> HashAggregate (cost=123.50..123.71 rows=21 width=64)
-> Nested Loop (cost=10.93..123.44 rows=21 width=64)
Join Filter: (r.relnamespace = s.oid)
-> Seq Scan on pg_namespace s (cost=0.00..1.08 rows=1
width=4)
Filter: (nspname = 'public'::name)
-> Nested Loop (cost=10.93..121.03 rows=107 width=68)
-> Hash Join (cost=10.93..82.03 rows=107 width=4)
Hash Cond: (c.atttypid = t.oid)
-> Seq Scan on pg_attribute c (cost=0.00..61.57
rows=2257 width=8)
-> Hash (cost=10.90..10.90 rows=2 width=4)
-> Seq Scan on pg_type t (cost=0.00..10.90
rows=2 width=4)
Filter: (typname = ANY
('{bytea,text}'::name[]))
-> Index Scan using pg_class_oid_index on pg_class r
(cost=0.00..0.35 rows=1 width=72)
Index Cond: (r.oid = c.attrelid)
Filter: (pg_relation_size(((('public."'::text ||
(r.relname)::text) || '"'::text))::regclass, 'main'::text) <> 0)
Best regards,
