Обсуждение: BUG #4106: WHERE - clause in view works only sometimes

The following bug has been logged online:

Bug reference:      4106
Logged by:          Alexander Strotmann
Email address:      kiruu@gmx.de
PostgreSQL version: 8.2
Operating system:   Windows XP / Windows 2003 Server
Description:        WHERE - clause in view works only sometimes
Details:

Hello,

we have this table:

CREATE TABLE rollenhistorie
(
  pnr integer NOT NULL,
  rolle character varying NOT NULL,
  seit timestamp without time zone NOT NULL,
  bis timestamp without time zone,
  CONSTRAINT rollenhistorie_pkey PRIMARY KEY (pnr, seit)
)
WITHOUT OIDS;

and this view:

CREATE OR REPLACE VIEW benutzer_mit_rolle_vw AS
 SELECT benutzer_ohne_alles_vw.pnr, benutzer_ohne_alles_vw.vorname,
benutzer_ohne_alles_vw.nachname, benutzer_ohne_alles_vw.nutzerkennung,
benutzer_ohne_alles_vw.passwort, benutzer_ohne_alles_vw.plz,
benutzer_ohne_alles_vw.ort, benutzer_ohne_alles_vw.strasse,
benutzer_ohne_alles_vw.hausnummer, benutzer_ohne_alles_vw.telefon_dienst,
benutzer_ohne_alles_vw.email, benutzer_ohne_alles_vw.anzahl_tage_erinnerung,
benutzer_ohne_alles_vw.gebietsschutz, rollenhistorie.rolle,
rollenhistorie.seit, rollenhistorie.bis
   FROM benutzer_ohne_alles_vw
NATURAL JOIN rollenhistorie
  WHERE rollenhistorie.bis IS NULL;

So the view joins the upper table with another view and filters the sets in
'rollenhistorie' by taking only the sets where 'bis' is NULL.
By questioning this view with this function:

CREATE OR REPLACE FUNCTION get_user_and_role_plpgsql()
  RETURNS SETOF benutzer_mit_rolle_vw AS
$BODY$
DECLARE
 rec RECORD;
BEGIN
 FOR rec IN Select * from benutzer_mit_rolle_vw
 LOOP
  RETURN NEXT rec;
 END LOOP;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

we get sometimes results where 'bis' is not NULL! But it is not
deterministic because in about 80% of request times the result is correct.
The work-around for us is putting the 'WHERE bis IS NULL' in the
function...

Kind regards

Alexander Strotmann

Alexander Strotmann wrote:
> PostgreSQL version: 8.2

Which version, exactly? Though I don't remember any changes that would
explain this, make sure you run the latest minor version, which is 8.2.7
at the moment.

> we get sometimes results where 'bis' is not NULL! But it is not
> deterministic because in about 80% of request times the result is correct.

Do you get the same results if you run the "SELECT * FROM
benutzer_mit_rolle_vw" query directly from psql? What does EXPLAIN
ANALYZE say?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

"Alexander Strotmann" <kiruu@gmx.de> writes:
> we have this table:
> ...
> and this view:
> ...
> So the view joins the upper table with another view

Another view?  Don't you think you've left out a lot of information that
would be needed for anyone trying to reproduce this failure?  Please
show the *full* definition of all tables and views involved.

And, as already noted, "8.2" isn't enough information about which
PG version you're running.

If it's an early 8.2.x release and the other view involves any outer
joins, then I could believe that this is explained by one of the outer
join planning bugs we've already fixed ... but without any details
that's only a wild guess.

            regards, tom lane

Alexander Strotmann <kiruu@gmx.de> writes:
> Ok, sorry, here it is all:

[ Please keep the mailing list cc'd ]

I couldn't reproduce a problem with these table definitions and some
simple dummy data.  Can you see any pattern to when it fails for you
and when it doesn't?  What plan do you get from
    EXPLAIN SELECT * FROM benutzer_mit_rolle_vw
?  What non-default postgresql.conf settings are you using?

            regards, tom lane


> Postgre version: PostgreSQL version 8.2.5

> we have this table:

> CREATE TABLE rollenhistorie
> (
>   pnr integer NOT NULL,
>   rolle character varying NOT NULL,
>   seit timestamp without time zone NOT NULL,
>   bis timestamp without time zone,
>   CONSTRAINT rollenhistorie_pkey PRIMARY KEY (pnr, seit)
> )
> WITHOUT OIDS;

> and this table:

> CREATE TABLE benutzer
> (
>   pnr serial NOT NULL,
>   vorname character varying(30) NOT NULL,
>   nachname character varying(30) NOT NULL,
>   nutzerkennung character varying(20) NOT NULL,
>   passwort character varying(32) NOT NULL,
>   plz character varying(10) NOT NULL,
>   ort character varying(30) NOT NULL,
>   strasse character varying(30) NOT NULL,
>   hausnummer character varying(5) NOT NULL,
>   telefon_dienst character varying(20) NOT NULL,
>   email character varying(40) NOT NULL,
>   anzahl_tage_erinnerung integer NOT NULL DEFAULT 30,
>   gebietsschutz boolean NOT NULL DEFAULT false,
>   CONSTRAINT benutzer_pkey PRIMARY KEY (pnr),
>   CONSTRAINT benutzer_nutzerkennung_key UNIQUE (nutzerkennung)
> )
> WITHOUT OIDS;

> and this view:

> CREATE OR REPLACE VIEW benutzer_ohne_alles_vw AS
>  SELECT benutzer.pnr, benutzer.vorname, benutzer.nachname, benutzer.nutzerkennung, benutzer.passwort, benutzer.plz,
benutzer.ort,benutzer.strasse, benutzer.hausnummer, benutzer.telefon_dienst, benutzer.email,
benutzer.anzahl_tage_erinnerung,benutzer.gebietsschutz 
>    FROM benutzer
>   WHERE NOT benutzer.nutzerkennung::text = 'system'::text AND NOT benutzer.nutzerkennung::text =
'marketingpool'::textAND NOT benutzer.nutzerkennung::text = 'deleted'::text AND NOT benutzer.nutzerkennung::text ~~
'dummy_%'::text;

> and this view:

> CREATE OR REPLACE VIEW benutzer_mit_rolle_vw AS
>  SELECT benutzer_ohne_alles_vw.pnr, benutzer_ohne_alles_vw.vorname,
> benutzer_ohne_alles_vw.nachname, benutzer_ohne_alles_vw.nutzerkennung,
> benutzer_ohne_alles_vw.passwort, benutzer_ohne_alles_vw.plz,
> benutzer_ohne_alles_vw.ort, benutzer_ohne_alles_vw.strasse,
> benutzer_ohne_alles_vw.hausnummer, benutzer_ohne_alles_vw.telefon_dienst,
> benutzer_ohne_alles_vw.email, benutzer_ohne_alles_vw.anzahl_tage_erinnerung,
> benutzer_ohne_alles_vw.gebietsschutz, rollenhistorie.rolle,
> rollenhistorie.seit, rollenhistorie.bis
>    FROM benutzer_ohne_alles_vw
> NATURAL JOIN rollenhistorie
>   WHERE rollenhistorie.bis IS NULL;

> So the view 'benutzer_mit_rolle_vw' joins the table 'rollenhistorie' with the view 'benutzer_ohne_alles_vw', which is
filteringout special users, and filters the sets in 
> 'rollenhistorie' by taking only the sets where 'bis' is NULL.
> By questioning this view with this function:

> CREATE OR REPLACE FUNCTION get_user_and_role_plpgsql()
>   RETURNS SETOF benutzer_mit_rolle_vw AS
> $BODY$
> DECLARE
>  rec RECORD;
> BEGIN
>  FOR rec IN Select * from benutzer_mit_rolle_vw
>  LOOP
>   RETURN NEXT rec;
>  END LOOP;
> END;$BODY$
>   LANGUAGE 'plpgsql' VOLATILE;

> we get sometimes results where 'bis' is not NULL! But it is not
> deterministic because in about 80% of request times the result is correct.
> The work-around for us is putting the 'WHERE bis IS NULL' in the
> function...


> Viele Grüße

> kiruu

> Email: kiruu@gmx.de

Hello Tom,

it seems that i can't reproduce it too at the moment. When i see the bug
again i will directly send you the data, but it must be something like that:

INSERT INTO benutzer (pnr, vorname, nachname, nutzerkennung, passwort,
plz, ort, strasse, hausnummer, telefon_dienst, telefon_privat, email,
anzahl_tage_erinnerung, gebietsschutz) VALUES (100, 'Alexander',
'Strotmann', 'alex', '534b44a19bf18d20b71ecc4eb77c572f', '11111',
'Test', 'Teststraße', '7', '+49 0251/123', NULL, 'foo@foo.de', 30, false);
INSERT INTO benutzer (pnr, vorname, nachname, nutzerkennung, passwort,
plz, ort, strasse, hausnummer, telefon_dienst, telefon_privat, email,
anzahl_tage_erinnerung, gebietsschutz) VALUES (101, 'Stephan',
'Künster', 'stephan', 'bf1f92de980819a99356289142b9590d', '22222',
'Test', 'Test-Weg', '444', '0251 123', NULL, 'test@test.de', 40, false);

INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (101, 'admin',
'2008-03-27 17:35:34.953', '2008-04-11 16:53:14.657134');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (100, 'admin',
'2008-04-12 14:13:57.215625', '2008-04-13 10:32:42.535246');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (100, 'vorg',
'2008-04-13 10:32:42.535246', '2008-04-13 10:32:48.113442');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (100, 'admin',
'2008-04-13 10:32:48.113442', '2008-04-13 10:33:14.770033');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (101, 'vorg',
'2008-04-11 16:53:14.657134', '2008-04-11 17:21:30.642962');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (100, 'vorg',
'2008-04-13 10:33:14.770033', '2008-04-13 10:40:27.713075');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (100, 'admin',
'2008-04-13 10:40:27.713075', NULL);
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (101, 'admin',
'2008-04-11 17:21:30.642962', '2008-04-11 18:09:59.498309');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (101, 'admin',
'2008-04-11 18:12:03.656148', '2008-04-13 10:40:48.947722');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (101, 'tl',
'2008-04-13 10:40:48.947722', '2008-04-13 10:41:44.417182');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (101, 'vorg',
'2008-04-11 18:09:59.498309', '2008-04-11 18:12:03.656148');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (101, 'vorg',
'2008-04-13 10:41:44.417182', '2008-04-13 10:49:02.454039');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (101, 'admin',
'2008-04-13 10:49:02.454039', '2008-04-13 11:00:57.23847');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (101, 'tl',
'2008-04-13 11:00:57.23847', '2008-04-13 11:02:44.646095');
INSERT INTO rollenhistorie (pnr, rolle, seit, bis) VALUES (101, 'admin',
'2008-04-13 11:02:44.646095', NULL);

Tom Lane schrieb:
> Alexander Strotmann <kiruu@gmx.de> writes:
>
>> Ok, sorry, here it is all:
>>
>
> [ Please keep the mailing list cc'd ]
>
> I couldn't reproduce a problem with these table definitions and some
> simple dummy data.  Can you see any pattern to when it fails for you
> and when it doesn't?  What plan do you get from
>     EXPLAIN SELECT * FROM benutzer_mit_rolle_vw
> ?
"Hash Join  (cost=1.35..18.60 rows=11 width=307)"
"  Hash Cond: (benutzer.pnr = rollenhistorie.pnr)"
"  ->  Seq Scan on benutzer  (cost=0.00..14.60 rows=226 width=284)"
"        Filter: (((nutzerkennung)::text <> 'system'::text) AND
((nutzerkennung)::text <> 'marketingpool'::text) AND
((nutzerkennung)::text <> 'deleted'::text) AND ((nutzerkennung)::text
!~~ 'dummy_%'::text))"
"  ->  Hash  (cost=1.21..1.21 rows=11 width=27)"
"        ->  Seq Scan on rollenhistorie  (cost=0.00..1.21 rows=11 width=27)"
"              Filter: (bis IS NULL)"


> What non-default postgresql.conf settings are you using?
>
I never changed something in postgresql.conf. So it should be everything
default.


Regards, Alex Strotmann

>             regards, tom lane
>
>
>
>> Postgre version: PostgreSQL version 8.2.5
>>
>
>
>> we have this table:
>>
>
>
>> CREATE TABLE rollenhistorie
>> (
>>   pnr integer NOT NULL,
>>   rolle character varying NOT NULL,
>>   seit timestamp without time zone NOT NULL,
>>   bis timestamp without time zone,
>>   CONSTRAINT rollenhistorie_pkey PRIMARY KEY (pnr, seit)
>> )
>> WITHOUT OIDS;
>>
>
>
>> and this table:
>>
>
>
>> CREATE TABLE benutzer
>> (
>>   pnr serial NOT NULL,
>>   vorname character varying(30) NOT NULL,
>>   nachname character varying(30) NOT NULL,
>>   nutzerkennung character varying(20) NOT NULL,
>>   passwort character varying(32) NOT NULL,
>>   plz character varying(10) NOT NULL,
>>   ort character varying(30) NOT NULL,
>>   strasse character varying(30) NOT NULL,
>>   hausnummer character varying(5) NOT NULL,
>>   telefon_dienst character varying(20) NOT NULL,
>>   email character varying(40) NOT NULL,
>>   anzahl_tage_erinnerung integer NOT NULL DEFAULT 30,
>>   gebietsschutz boolean NOT NULL DEFAULT false,
>>   CONSTRAINT benutzer_pkey PRIMARY KEY (pnr),
>>   CONSTRAINT benutzer_nutzerkennung_key UNIQUE (nutzerkennung)
>> )
>> WITHOUT OIDS;
>>
>
>
>> and this view:
>>
>
>
>> CREATE OR REPLACE VIEW benutzer_ohne_alles_vw AS
>>  SELECT benutzer.pnr, benutzer.vorname, benutzer.nachname, benutzer.nutzerkennung, benutzer.passwort, benutzer.plz,
benutzer.ort,benutzer.strasse, benutzer.hausnummer, benutzer.telefon_dienst, benutzer.email,
benutzer.anzahl_tage_erinnerung,benutzer.gebietsschutz 
>>    FROM benutzer
>>   WHERE NOT benutzer.nutzerkennung::text = 'system'::text AND NOT benutzer.nutzerkennung::text =
'marketingpool'::textAND NOT benutzer.nutzerkennung::text = 'deleted'::text AND NOT benutzer.nutzerkennung::text ~~
'dummy_%'::text;
>>
>
>
>> and this view:
>>
>
>
>> CREATE OR REPLACE VIEW benutzer_mit_rolle_vw AS
>>  SELECT benutzer_ohne_alles_vw.pnr, benutzer_ohne_alles_vw.vorname,
>> benutzer_ohne_alles_vw.nachname, benutzer_ohne_alles_vw.nutzerkennung,
>> benutzer_ohne_alles_vw.passwort, benutzer_ohne_alles_vw.plz,
>> benutzer_ohne_alles_vw.ort, benutzer_ohne_alles_vw.strasse,
>> benutzer_ohne_alles_vw.hausnummer, benutzer_ohne_alles_vw.telefon_dienst,
>> benutzer_ohne_alles_vw.email, benutzer_ohne_alles_vw.anzahl_tage_erinnerung,
>> benutzer_ohne_alles_vw.gebietsschutz, rollenhistorie.rolle,
>> rollenhistorie.seit, rollenhistorie.bis
>>    FROM benutzer_ohne_alles_vw
>> NATURAL JOIN rollenhistorie
>>   WHERE rollenhistorie.bis IS NULL;
>>
>
>
>> So the view 'benutzer_mit_rolle_vw' joins the table 'rollenhistorie' with the view 'benutzer_ohne_alles_vw', which
isfiltering out special users, and filters the sets in 
>> 'rollenhistorie' by taking only the sets where 'bis' is NULL.
>> By questioning this view with this function:
>>
>
>
>> CREATE OR REPLACE FUNCTION get_user_and_role_plpgsql()
>>   RETURNS SETOF benutzer_mit_rolle_vw AS
>> $BODY$
>> DECLARE
>>  rec RECORD;
>> BEGIN
>>  FOR rec IN Select * from benutzer_mit_rolle_vw
>>  LOOP
>>   RETURN NEXT rec;
>>  END LOOP;
>> END;$BODY$
>>   LANGUAGE 'plpgsql' VOLATILE;
>>
>
>
>> we get sometimes results where 'bis' is not NULL! But it is not
>> deterministic because in about 80% of request times the result is correct.
>> The work-around for us is putting the 'WHERE bis IS NULL' in the
>> function...
>>
>
>
>
>> Viele Grüße
>>
>
>
>> kiruu
>>
>
>
>> Email: kiruu@gmx.de
>>
>
>