On Tue, Mar 2, 2010 at 7:51 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> "Gurjeet Singh" <singh.gurj...@gmail.com> writes:
> > 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
>
> That approach to generating a textual name for a relation is really
> pretty unworkable: it's on the hairy edge of being vulnerable to
> SQL injection attacks, not to mention being inefficient and unwieldy.
> Just pass r.oid to pg_relation_size, instead.
>
I have gotten on to that path already, thanks for the advice.
This query will never be used by an application, so no fear of SQL injection
there. I was in the middle of a migration effort when I brewed this query.
The main inner query is what I started with to migrate only specific tables,
and the started slapping on outer queries to monitor the amount of data
already transferred. So I was rather surprised to see this error at a stage
where I did not expect it to fail.
IMHO the outer-most WHERE clause is being pushed through the subqueries when
it should not be. I tried to stop the optimizer from doing that and it seems
putting a LIMIT clause on S1 subquery make Postgres happy.
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 limit 1000
) as s2
where pg_relation_size( 'public."' || relname || '"' ) <> 0 ;
>From SQL perspective there should be no difference between this query and
the one in the first post since there's only one qualifying record.
Predicate push-down is definitely a good optimization, but it should not
affect the result-set. I have no idea how to tell optimizer to stop such
push-downs.
I am leaning towards marking this as a bug.
Best regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
