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, '[EMAIL PROTECTED]', 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, '[EMAIL PROTECTED]', 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
