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

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