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@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
>>
>
>