Re: BUG #4106: WHERE - clause in view works only sometimes

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(at)foo(dot)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(at)test(dot)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(at)gmx(dot)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'::text AND 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 filtering 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(at)gmx(dot)de
>>
>
>