Re: BUG #5358: Throwing unexpected ERROR

On Tue, Mar 2, 2010 at 10:24 PM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>wrote:

> On Tue, Mar 2, 2010 at 7:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)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 just realized that it is the subquery pull-up that is leading to this
problem, not predicate push-down. Sleeping over it does really help I guess
:)

So instead of the LIMIT 1000, OFFSET 0 clause is the right choice for
preventing subquery pull-up without affecting the results.

I don't think the optimizer has the push-down capabiity; I may be wrong.

> I am leaning towards marking this as a bug.
>
>
Best regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device