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

Started by Alexander Strotmannabout 18 years ago5 messagesbugs

kiruu@gmx.de

about 18 years ago

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

Heikki Linnakangas

heikki.linnakangas@enterprisedb.com

about 18 years ago

In reply to: Alexander Strotmann (#1)

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

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

Tom Lane

tgl@sss.pgh.pa.us

about 18 years ago

In reply to: Alexander Strotmann (#1)

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

"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

Tom Lane

tgl@sss.pgh.pa.us

about 18 years ago

In reply to: Alexander Strotmann (#1)

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

Show quoted text

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@gmx.de

Import Notes

Reply to msg id not found: 48014610.1040404@gmx.de

Alexander Strotmann

kiruu@gmx.de

about 18 years ago

In reply to: Tom Lane (#4)

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

Show quoted text

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@gmx.de