Re: [BUGS] BUG #5358: Throwing unexpected ERROR
On Tue, Mar 2, 2010 at 10:24 PM, Gurjeet Singh wrote:
> On Tue, Mar 2, 2010 at 7:51 PM, Tom Lane wrote:
>
>> "Gurjeet Singh" 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.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [BUGS] BUG #5358: Throwing unexpected ERROR
On Wed, Mar 3, 2010 at 7:29 AM, Gurjeet Singh wrote: > 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. Maybe I'm just dense, but I don't understand what you're complaining about here. The SELECT DISTINCT already acts as an optimization fence, so why would you need another one? And what problem would you expect it to solve? ...Robert -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5358: Throwing unexpected ERROR
On Wed, Mar 3, 2010 at 8:37 AM, Robert Haas wrote:
> On Wed, Mar 3, 2010 at 7:29 AM, Gurjeet Singh
> wrote:
> > 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.
>
> Maybe I'm just dense, but I don't understand what you're complaining
> about here. The SELECT DISTINCT already acts as an optimization
> fence, so why would you need another one? And what problem would you
> expect it to solve?
>
>
I am complaining about the ERROR when I don't specify OFFSET or LIMIT.
The query isn't relevant. It is there just to illustrate the fact that two
supposedly equivalent forms of a query are not treated equivalent after all
by Postgres.
You don't put that OFFSET clause, you get an ERROR. You put in that OFFSET
clause and you get proper results.
I hope my complain is clearer now.
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
Re: [BUGS] Bug in triggers
2010/3/1 Robert Haas
>
> It's not obvious whether this is the same as one of the various other
> problems you've complained about. If it isn't, an English description
> of what you think the problem is would probably improve your odds.
> See also:
>
> http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>
> ...Robert
Thanks! This was long time ago, so i reposted it due empty responses.
i think this problem already discussed by Tom Lane, it is about "Row of
nulls OR null row", but i couldn't find this thread in archive.
So if you have null row in plpgsql and assign it to plpgsql var it will be
translated to row of nulls instead null row.
Here it is an example:
It is assign with direct function call:
> CREATE TYPE "type_subrow" AS (
> "typename" VARCHAR
> );
> CREATE TYPE "type_row" AS (
> "typename" VARCHAR,
> "subrow" type_subrow
> );
>
> CREATE OR REPLACE FUNCTION "test_bug"(in_row type_row) RETURNS void AS
> $body$
> DECLARE
> var type_row%rowtype;
> BEGIN
> var := in_row;
> RAISE NOTICE 'Original value: %', in_row;
> RAISE NOTICE 'Assigned value: %', var;
>
> IF var::TEXT <> in_row::TEXT THEN
> RAISE EXCEPTION 'var is not equals in_row';
> END IF;
> END;
> $body$
> LANGUAGE 'plpgsql';
>
> SELECT test_bug('("Test",)'::type_row);
>
Will output:
NOTICE: Original value: (Test,"()")
> NOTICE: Assigned value: (Test,"()")
>
As you see - subrow of type row is not null, it is ROW(NULL).
Now see how it will be in trigger:
ROLLBACK;
> BEGIN;
>
> CREATE TYPE "type_subrow" AS (
> "typename" VARCHAR
> );
> CREATE TABLE "type_row" (
> "typename" VARCHAR,
> "subrow" type_subrow
> );
>
> CREATE OR REPLACE FUNCTION "test_bug"() RETURNS trigger AS
> $body$
> DECLARE
> var type_row%rowtype;
> BEGIN
> var := NEW;
> RAISE NOTICE 'Original value: %', NEW;
> RAISE NOTICE 'Assigned value: %', var;
>
> IF var::TEXT <> NEW::TEXT THEN
> RAISE NOTICE 'var is not equals NEW';
> END IF;
>
> RETURN NEW;
> END;
> $body$
> LANGUAGE 'plpgsql';
>
> CREATE TRIGGER "t_bug" BEFORE INSERT
> ON type_row FOR EACH ROW
> EXECUTE PROCEDURE "test_bug"();
>
> INSERT INTO type_row VALUES('Test', NULL);
>
Will output:
NOTICE: Original value: (Test,)
> NOTICE: Assigned value: (Test,"()")
> NOTICE: var is not equals NEW
>
As you see - NEW.subrow is null.
But var.subrow is not null, it is ROW(NULL).
Do you understand what is the problem?
>
> 2010/2/26 Oleg Serov :
> > Up!, Anybody will answer on this bugreport?
> >
> > On Fri, Sep 26, 2008 at 2:57 PM, Oleg Serov wrote:
> >>
> >> Sorry, bug is not in triggers, it is in PL/PGSQL var assign mechanism
> >> here it is an example:
> >> ROLLBACK;
> >> BEGIN;
> >>
> >> CREATE TYPE "composite_type" AS (
> >>"type" VARCHAR,
> >>"type2" VARCHAR
> >> );
> >>
> >>
> >> CREATE TABLE "buggy" (
> >>"id" BIGINT NOT NULL,
> >>"bug" "composite_type",
> >>CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
> >> ) WITH OIDS;
> >>
> >>
> >> CREATE OR REPLACE FUNCTION "test_bug" () RETURNS pg_catalog.void AS
> >> $body$
> >> DECLARE
> >>tmp_old buggy%rowtype;
> >> BEGIN
> >>tmp_old := ROW(1, NULL)::buggy;
> >>IF tmp_old::text <> ROW(1, NULL)::buggy::text THEN
> >>RAISE EXCEPTION '% <> %', tmp_old, ROW(1, NULL)::buggy;
> >>END IF;
> >> END;
> >> $body$
> >> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> >>
> >> WILL THROW A EXCEPTION:
> >> ERROR: (1,"(,)") <> (1,)
> >>
> >>
> >> SELECT test_bug();
> >>
> >>
> >> 2008/9/26, Oleg Serov :
> >> > SQL code:
> >> >
> >> >
> >> > ROLLBACK;
> >> > BEGIN;
> >> > CREATE TYPE "composite_type" AS (
> >> > "typename" VARCHAR
> >> > );
> >> >
> >> >
> >> > CREATE TABLE "buggy" (
> >> > "id" BIGINT NOT NULL,
> >> > "bug" "composite_type",
> >> > CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
> >> > ) WITH OIDS;
> >> >
> >> > INSERT INTO buggy (id, bug) VALUES
> >> > (100196418052926086, NULL);
> >> >
> >> > CREATE OR REPLACE FUNCTION "public"."test_bug" () RETURNS trigger AS
> >> > $body$
> >> > DECLARE
> >> > tmp_old buggy%rowtype;
> >> > tmp_new buggy%rowtype;
> >> > BEGIN
> >> > RAISE NOTICE 'OLD: %', OLD;
> >> > RAISE NOTICE 'NEW: %', NEW;
> >> >
> >> > tmp_old := OLD;
> >> > RAISE NOTICE 'TMP OLD: %', tmp_old;
> >> >
> >> > RAISE NOTICE 'TMP OLD = OLD => %', tmp_old::text = OLD::text;
> >> >
> >> > tmp_old.id := NEW.id;
> >> > tmp_new := NEW;
> >> >
> >> > RAISE NOTICE 'TMP OLD: %', tmp_old;
> >> > RAISE NOTICE 'TMP NEW: %', tmp_new;
> >> >
> >> > RAISE NOTICE 'TMP OLD = TMP NEW => %', tmp_old::text =
> >> > tmp_new::text;
> >> > RAISE NOTICE 'TMP OLD = NEW => %', tmp_old::text = NEW::text;
> >> >
> >> >
> >> >
> >> > IF (tmp_old::text <> tmp_new::text) <> (tmp_old::text <>
> >> > NEW::text)
> >> > THEN
> >> > RAISE EXCE
[BUGS] BUG #5360: system column named "text"
The following bug has been logged online: Bug reference: 5360 Logged by: Sergey Manakov Email address: vial...@gmail.com PostgreSQL version: 8.4.2 Operating system: Fedora Core 8 Description:system column named "text" Details: Hi! I'am from Russia and write English ugly, but i'am must to try... I have some table with column named "remark" and without column named "text". I wrote SQL where try to select column named "text" by mistake: SELECT st.text FROM sometable st Where error expected, but query executed without error! Query result is one column contains text representation of ROWs. Is this a bug, or I'am look for description in docs badly? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5360: system column named "text"
"Sergey Manakov" writes: > I have some table with column named "remark" and without column named > "text". I wrote SQL where try to select column named "text" by mistake: > SELECT st.text > FROM sometable st > Where error expected, but query executed without error! Query result is one > column contains text representation of ROWs. > Is this a bug, or I'am look for description in docs badly? No, it's not a bug. It's equivalent to text(st) or st::text, that is the system thinks you're requesting a coercion of the rowtype value to text. The syntactic equivalence of f(foo) and foo.f is documented near the end of section 34.4.2: http://www.postgresql.org/docs/8.4/static/xfunc-sql.html#AEN43797 regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug in triggers
2010/3/3 Oleg Serov :
>
>
> 2010/3/1 Robert Haas
>>
>> It's not obvious whether this is the same as one of the various other
>> problems you've complained about. If it isn't, an English description
>> of what you think the problem is would probably improve your odds.
>> See also:
>>
>> http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>>
>> ...Robert
>
> Thanks! This was long time ago, so i reposted it due empty responses.
> i think this problem already discussed by Tom Lane, it is about "Row of
> nulls OR null row", but i couldn't find this thread in archive.
>
> So if you have null row in plpgsql and assign it to plpgsql var it will be
> translated to row of nulls instead null row.
> Here it is an example:
> It is assign with direct function call:
>>
>> CREATE TYPE "type_subrow" AS (
>> "typename" VARCHAR
>> );
>> CREATE TYPE "type_row" AS (
>> "typename" VARCHAR,
>> "subrow" type_subrow
>> );
>>
>> CREATE OR REPLACE FUNCTION "test_bug"(in_row type_row) RETURNS void AS
>> $body$
>> DECLARE
>> var type_row%rowtype;
>> BEGIN
>> var := in_row;
>> RAISE NOTICE 'Original value: %', in_row;
>> RAISE NOTICE 'Assigned value: %', var;
>>
>> IF var::TEXT <> in_row::TEXT THEN
>> RAISE EXCEPTION 'var is not equals in_row';
>> END IF;
>> END;
>> $body$
>> LANGUAGE 'plpgsql';
>>
>> SELECT test_bug('("Test",)'::type_row);
>
> Will output:
>
>> NOTICE: Original value: (Test,"()")
>> NOTICE: Assigned value: (Test,"()")
>
> As you see - subrow of type row is not null, it is ROW(NULL).
>
> Now see how it will be in trigger:
>
>> ROLLBACK;
>> BEGIN;
>>
>> CREATE TYPE "type_subrow" AS (
>> "typename" VARCHAR
>> );
>> CREATE TABLE "type_row" (
>> "typename" VARCHAR,
>> "subrow" type_subrow
>> );
>>
>> CREATE OR REPLACE FUNCTION "test_bug"() RETURNS trigger AS
>> $body$
>> DECLARE
>> var type_row%rowtype;
>> BEGIN
>> var := NEW;
>> RAISE NOTICE 'Original value: %', NEW;
>> RAISE NOTICE 'Assigned value: %', var;
>>
>> IF var::TEXT <> NEW::TEXT THEN
>> RAISE NOTICE 'var is not equals NEW';
>> END IF;
>>
>> RETURN NEW;
>> END;
>> $body$
>> LANGUAGE 'plpgsql';
>>
>> CREATE TRIGGER "t_bug" BEFORE INSERT
>> ON type_row FOR EACH ROW
>> EXECUTE PROCEDURE "test_bug"();
>>
>> INSERT INTO type_row VALUES('Test', NULL);
>
> Will output:
>
>> NOTICE: Original value: (Test,)
>> NOTICE: Assigned value: (Test,"()")
>> NOTICE: var is not equals NEW
>
> As you see - NEW.subrow is null.
> But var.subrow is not null, it is ROW(NULL).
>
> Do you understand what is the problem?
It does seem weird that assigning NEW to var changes the value; I'm
not sure why that happens. Is that what you're asking about?
...Robert
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug in triggers
I'm asking to fix this =)
On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas wrote:
> 2010/3/3 Oleg Serov :
> >
> >
> > 2010/3/1 Robert Haas
> >>
> >> It's not obvious whether this is the same as one of the various other
> >> problems you've complained about. If it isn't, an English description
> >> of what you think the problem is would probably improve your odds.
> >> See also:
> >>
> >> http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
> >>
> >> ...Robert
> >
> > Thanks! This was long time ago, so i reposted it due empty responses.
> > i think this problem already discussed by Tom Lane, it is about "Row of
> > nulls OR null row", but i couldn't find this thread in archive.
> >
> > So if you have null row in plpgsql and assign it to plpgsql var it will
> be
> > translated to row of nulls instead null row.
> > Here it is an example:
> > It is assign with direct function call:
> >>
> >> CREATE TYPE "type_subrow" AS (
> >> "typename" VARCHAR
> >> );
> >> CREATE TYPE "type_row" AS (
> >> "typename" VARCHAR,
> >> "subrow" type_subrow
> >> );
> >>
> >> CREATE OR REPLACE FUNCTION "test_bug"(in_row type_row) RETURNS void AS
> >> $body$
> >> DECLARE
> >> var type_row%rowtype;
> >> BEGIN
> >> var := in_row;
> >> RAISE NOTICE 'Original value: %', in_row;
> >> RAISE NOTICE 'Assigned value: %', var;
> >>
> >> IF var::TEXT <> in_row::TEXT THEN
> >> RAISE EXCEPTION 'var is not equals in_row';
> >> END IF;
> >> END;
> >> $body$
> >> LANGUAGE 'plpgsql';
> >>
> >> SELECT test_bug('("Test",)'::type_row);
> >
> > Will output:
> >
> >> NOTICE: Original value: (Test,"()")
> >> NOTICE: Assigned value: (Test,"()")
> >
> > As you see - subrow of type row is not null, it is ROW(NULL).
> >
> > Now see how it will be in trigger:
> >
> >> ROLLBACK;
> >> BEGIN;
> >>
> >> CREATE TYPE "type_subrow" AS (
> >> "typename" VARCHAR
> >> );
> >> CREATE TABLE "type_row" (
> >> "typename" VARCHAR,
> >> "subrow" type_subrow
> >> );
> >>
> >> CREATE OR REPLACE FUNCTION "test_bug"() RETURNS trigger AS
> >> $body$
> >> DECLARE
> >> var type_row%rowtype;
> >> BEGIN
> >> var := NEW;
> >> RAISE NOTICE 'Original value: %', NEW;
> >> RAISE NOTICE 'Assigned value: %', var;
> >>
> >> IF var::TEXT <> NEW::TEXT THEN
> >> RAISE NOTICE 'var is not equals NEW';
> >> END IF;
> >>
> >> RETURN NEW;
> >> END;
> >> $body$
> >> LANGUAGE 'plpgsql';
> >>
> >> CREATE TRIGGER "t_bug" BEFORE INSERT
> >> ON type_row FOR EACH ROW
> >> EXECUTE PROCEDURE "test_bug"();
> >>
> >> INSERT INTO type_row VALUES('Test', NULL);
> >
> > Will output:
> >
> >> NOTICE: Original value: (Test,)
> >> NOTICE: Assigned value: (Test,"()")
> >> NOTICE: var is not equals NEW
> >
> > As you see - NEW.subrow is null.
> > But var.subrow is not null, it is ROW(NULL).
> >
> > Do you understand what is the problem?
>
> It does seem weird that assigning NEW to var changes the value; I'm
> not sure why that happens. Is that what you're asking about?
>
> ...Robert
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
--
С уважением
Олег Серов
Re: [BUGS] Cache lookup failure for index during pg_dump
Euler Taveira de Oliveira wrote: > Tom Lane escreveu: > > The window for this sort of thing isn't very large, because the first > > thing pg_dump does is acquire AccessShareLock on every table it intends > > to dump, and past that point it won't be possible for anyone to modify > > the table's DDL. But it can happen. > > > I did not see it documented anywhere. Should we at least add a comment at the > top of pg_dump documenting this behavior? Attached is a proposed patch using > your own words. Applied, thanks. I also added the URL of the discussion. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
