Re: BUG #5358: Throwing unexpected ERROR - Mailing list pgsql-bugs

On Tue, Mar 2, 2010 at 7:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Gurjeet Singh" <singh.gurjeet@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.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device