Re: [GENERAL] Bug in ordered views?
Tom Lane wrote: Nis Jorgensen <[EMAIL PROTECTED]> writes: Try removing the DISTINCT ON from your view - that should make things clearer to you. When t.approved is true, the row is joined to all rows of the datum table satisfying the criteria. The sort order you specify does not guarantee a unique ordering of the rows, which explains the inconsistency between the two cases. More specifically, look at this: select t.test_id,d.projekt_id,d.datum,t.datum, t.id, t.approved, t.test_text FROM datum d JOIN test t ON (t.projekt_id = d.projekt_id OR t.approved IS TRUE) AND t.datum <= d.datum ORDER BY t.test_id DESC, d.projekt_id DESC, d.datum DESC, t.datum DESC; test_id | projekt_id | datum| datum| id | approved | test_text -+++++--+--- 2 | 2 | 2006-05-16 | 2006-05-16 | 4 | f| new 2 | 2 | 2006-05-16 | 2006-05-15 | 2 | t| old 2 | 2 | 2006-05-15 | 2006-05-15 | 2 | t| old 2 | 1 | 2006-05-16 | 2006-05-15 | 2 | t| old 2 | 1 | 2006-05-15 | 2006-05-15 | 2 | t| old 1 | 2 | 2006-05-16 | 2006-05-15 | 1 | t| old 1 | 2 | 2006-05-16 | 2006-05-15 | 3 | f| new * 1 | 2 | 2006-05-15 | 2006-05-15 | 3 | f| new * 1 | 2 | 2006-05-15 | 2006-05-15 | 1 | t| old 1 | 1 | 2006-05-16 | 2006-05-15 | 1 | t| old 1 | 1 | 2006-05-15 | 2006-05-15 | 1 | t| old (11 rows) The two rows I've marked with * are identical in all the columns that are used in the DISTINCT ON and ORDER BY clauses, which means it's unspecified which one you get out of the DISTINCT ON. I'm not entirely sure why adding the test_id condition changes the results, but it may be an artifact of qsort() behavior. Anyway you need to constrain the ORDER BY some more to ensure you get well-defined results from the DISTINCT ON. regards, tom lane Classical "pilot error". I recognized the missing order by a few minutes after sending my message. Sorry for the noise, but it looked totally reproducible, no matter what kind of where clause I added. Thanks anyway Sebastian ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Bug in ordered views?
Hello all, I think I found a little but annoying bug in views when ordering is involved. First, my version of Postgres: PostgreSQL 8.1.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518 Please try the following: CREATE TABLE datum ( projekt_id INTEGER NOT NULL, datum DATE NOT NULL, UNIQUE (projekt_id, datum) ) WITHOUT OIDS; CREATE TABLE test ( id SERIAL PRIMARY KEY, projekt_id INTEGER NOT NULL, datum DATE NOT NULL, approved BOOLEAN NOT NULL DEFAULT FALSE, test_id INTEGER, test_text TEXT ) WITHOUT OIDS; CREATE OR REPLACE VIEW bug AS SELECT DISTINCT ON (test_id,projekt_id,datum) t.id, d.projekt_id, d.datum, t.approved, t.test_id, t.test_text FROM datum d JOIN test t ON (t.projekt_id = d.projekt_id OR t.approved IS TRUE) AND t.datum <= d.datum ORDER BY t.test_id DESC, d.projekt_id DESC, d.datum DESC, t.datum DESC; INSERT INTO datum (projekt_id,datum) VALUES (1,now()); INSERT INTO datum (projekt_id,datum) VALUES (1,now()+'1d'::interval); INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES (1,now(),1,'old'); INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES (1,now(),2,'old'); UPDATE test SET approved = TRUE WHERE projekt_id = 1; INSERT INTO datum (projekt_id,datum) VALUES (2,now()); INSERT INTO datum (projekt_id,datum) VALUES (2,now()+'1d'::interval); INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES (2,now(),1,'new'); INSERT INTO test (projekt_id,datum,test_id,test_text) VALUES (2,now()+'1d'::interval,2,'new'); Now do a simple select: SELECT * FROM bug; id | projekt_id | datum| approved | test_id | test_text +++--+-+--- 4 | 2 | 16.05.2006 | f| 2 | new 2 | 2 | 15.05.2006 | t| 2 | old 2 | 1 | 16.05.2006 | t| 2 | old 2 | 1 | 15.05.2006 | t| 2 | old 3 | 2 | 16.05.2006 | f| 1 | new 1 | 2 | 15.05.2006 | t| 1 | old 1 | 1 | 16.05.2006 | t| 1 | old 1 | 1 | 15.05.2006 | t| 1 | old And now constrain the above select: SELECT * FROM bug WHERE test_id = 1; id | projekt_id | datum| approved | test_id | test_text +++--+-+--- 1 | 2 | 16.05.2006 | t| 1 | old 1 | 2 | 15.05.2006 | t| 1 | old 1 | 1 | 16.05.2006 | t| 1 | old 1 | 1 | 15.05.2006 | t| 1 | old Notice that the should be 1 line with test_text showing "new"! Did I miss anything or is it a bug? Sebastian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Bug with index-usage?
Tom Lane wrote: =?ISO-8859-1?Q?Sebastian_B=F6ck?= <[EMAIL PROTECTED]> writes: I patched my 8.1 installation with the following changes: http://www-new.commandprompt.com/projects/public/pgsql/changeset/23461 The error described above doesn't exist any more, but it's still buggy. Yup, you're right :-(. Looks like we haven't been doing adequate testing with complex OUTER JOIN clauses ... Fix committed. Thanks for the report! Thanks for the quick fix, everything looks good now! Sebastian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Bug with index-usage?
Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= <[EMAIL PROTECTED]> writes:
I get unpredictibale results selecting from a view depending on
index-usage.
It's not actually *using* the indexes, although presence of the indexes
does seem to be needed to trigger the bug:
regression=# explain SELECT * from test WHERE type = 'a';
QUERY PLAN
---
Nested Loop Left Join (cost=0.00..2.29 rows=1 width=40)
Join Filter: (("outer"."type" = 'c'::text) OR ("outer"."type" = 'b'::text))
-> Seq Scan on test2 (cost=0.00..1.16 rows=1 width=40)
Filter: (("type" = 'a'::text) AND (("type" = 'c'::text) OR ("type" =
'b'::text)))
-> Seq Scan on test2 t2 (cost=0.00..1.11 rows=1 width=0)
Filter: ("type" = 'a'::text)
(6 rows)
regression=# drop index index_b;
DROP INDEX
regression=# explain SELECT * from test WHERE type = 'a';
QUERY PLAN
---
Nested Loop Left Join (cost=0.00..2.24 rows=1 width=40)
Join Filter: (("outer"."type" = 'c'::text) OR ("outer"."type" = 'b'::text))
-> Seq Scan on test2 (cost=0.00..1.11 rows=1 width=40)
Filter: ("type" = 'a'::text)
-> Seq Scan on test2 t2 (cost=0.00..1.11 rows=1 width=0)
Filter: ("type" = 'a'::text)
(6 rows)
It looks like the problem is that the new 8.1 OR-index-qual code is
confused about when it can apply outer-join conditions. It shouldn't be
propagating the outer-join condition into the scan condition on test2,
but it is. Will fix.
regards, tom lane
Hi,
thanks for lookin into it.
I patched my 8.1 installation with the following changes:
http://www-new.commandprompt.com/projects/public/pgsql/changeset/23461
The error described above doesn't exist any more, but it's still
buggy. Just create a view which is left-joining to an other table. The
joined columns don't show up in the view.
CREATE OR REPLACE VIEW test_ AS
SELECT test2.*, test1.name
FROM test2
LEFT JOIN test1 ON test1.id = test2.test1_id
LEFT JOIN test2 AS t2 ON
test2.type IN ('c','b') AND
t2.type = 'a';
In 8.0 I get:
SELECT * from test WHERE type = 'a';
id | type | test1_id | name
+--+--+-
1 | a|1 | test1_1
2 | a|2 | test1_2
3 | a|3 | test1_3
(3 rows)
In 8.1 (with or without your patches) I get:
SELECT * from test_ WHERE type = 'a';
id | type | test1_id | name
+--+--+--
1 | a|1 |
2 | a|2 |
3 | a|3 |
(3 rows)
Hope you could repeat the problem. Otherwise, please contact me.
Sebastian
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Bug with index-usage?
Jaime Casanova wrote:
On 11/14/05, Sebastian Böck <[EMAIL PROTECTED]> wrote:
Hello,
I get unpredictibale results selecting from a view depending on
index-usage.
Please see the attached script for details.
Is it a bug or some "weird feature"?
Any help appreciated to get predictibale results
Sebastian
CREATE TABLE test1 (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
INSERT INTO test1 (name) VALUES ('test1_1');
INSERT INTO test1 (name) VALUES ('test1_2');
INSERT INTO test1 (name) VALUES ('test1_3');
CREATE TABLE test2 (
id SERIAL PRIMARY KEY,
type TEXT NOT NULL CHECK (type IN ('a','b','c')),
test1_id INTEGER REFERENCES test1
);
INSERT INTO test2 (type,test1_id) VALUES ('a',1);
INSERT INTO test2 (type,test1_id) VALUES ('a',2);
INSERT INTO test2 (type,test1_id) VALUES ('a',3);
INSERT INTO test2 (type,test1_id) VALUES ('b',1);
INSERT INTO test2 (type,test1_id) VALUES ('b',2);
INSERT INTO test2 (type,test1_id) VALUES ('b',3);
INSERT INTO test2 (type,test1_id) VALUES ('c',1);
INSERT INTO test2 (type,test1_id) VALUES ('c',2);
INSERT INTO test2 (type,test1_id) VALUES ('c',3);
CREATE OR REPLACE VIEW test AS
SELECT test2.*
FROM test2
LEFT JOIN test2 AS t2 ON
test2.type IN ('c','b') AND
t2.type = 'a';
SELECT * from test WHERE type = 'a';
CREATE INDEX index_a ON test2 (id) WHERE type = 'a';
CREATE INDEX index_b ON test2 (id) WHERE type = 'b';
CREATE INDEX index_c ON test2 (id) WHERE type = 'c';
SET enable_seqscan TO OFF;
SELECT * from test WHERE type = 'a';
i don't have my machine at hand but i don't think that even the select
is right, you have a join but without joining clauses you will get a
cartesian product...
what do you believe is the right answer... just for my probe later...
I think it should be:
id | type | test1_id
+--+--
1 | a|1
2 | a|2
3 | a|3
because a
EXPLAIN SELECT * from test WHERE type = 'a';
shows some weird assumptions
Index Scan using index_a on test2 (cost=0.00..4.69 rows=1 width=40)
Filter: (("type" = 'c'::text) OR ("type" = 'b'::text))
note that index_a is defined as:
CREATE INDEX index_a ON test2 (id) WHERE type = 'a';
Sebastian
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Bug with index-usage?
Scott Marlowe wrote: On Mon, 2005-11-14 at 11:07, Sebastian Böck wrote: Hello, I get unpredictibale results selecting from a view depending on index-usage. PostgreSQL uses a cost based planner. So, it tends to not use the plan you might expect, especially in "toy" test cases with small data sets. I.e. why use an index to look up 10 values, when they all fit on the same page. Just seq scan the data from the table. Fill up your table with REAL data (or a close substitute) and test again. Also, read up on the admin section, specifically the part on the postgresql.conf file and what the settings in there mean, then read http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html I think you didn't test my small script or don't see the same results. I don't speak about index-usage per se, I'm talkung about the results. Without indices I get: SELECT * from test WHERE typ = 'a'; id | typ | test1_id +-+-- 1 | a |1 2 | a |2 3 | a |3 (3 rows) But with defined indices I get: SELECT * from test WHERE typ = 'a'; id | typ | test1_id +-+-- (0 rows) By the way, this is 8.1 (forgot to mention in my first mail). Sebastian ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Bug with index-usage?
Hello,
I get unpredictibale results selecting from a view depending on
index-usage.
Please see the attached script for details.
Is it a bug or some "weird feature"?
Any help appreciated to get predictibale results
Sebastian
CREATE TABLE test1 (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
INSERT INTO test1 (name) VALUES ('test1_1');
INSERT INTO test1 (name) VALUES ('test1_2');
INSERT INTO test1 (name) VALUES ('test1_3');
CREATE TABLE test2 (
id SERIAL PRIMARY KEY,
type TEXT NOT NULL CHECK (type IN ('a','b','c')),
test1_id INTEGER REFERENCES test1
);
INSERT INTO test2 (type,test1_id) VALUES ('a',1);
INSERT INTO test2 (type,test1_id) VALUES ('a',2);
INSERT INTO test2 (type,test1_id) VALUES ('a',3);
INSERT INTO test2 (type,test1_id) VALUES ('b',1);
INSERT INTO test2 (type,test1_id) VALUES ('b',2);
INSERT INTO test2 (type,test1_id) VALUES ('b',3);
INSERT INTO test2 (type,test1_id) VALUES ('c',1);
INSERT INTO test2 (type,test1_id) VALUES ('c',2);
INSERT INTO test2 (type,test1_id) VALUES ('c',3);
CREATE OR REPLACE VIEW test AS
SELECT test2.*
FROM test2
LEFT JOIN test2 AS t2 ON
test2.type IN ('c','b') AND
t2.type = 'a';
SELECT * from test WHERE type = 'a';
CREATE INDEX index_a ON test2 (id) WHERE type = 'a';
CREATE INDEX index_b ON test2 (id) WHERE type = 'b';
CREATE INDEX index_c ON test2 (id) WHERE type = 'c';
SET enable_seqscan TO OFF;
SELECT * from test WHERE type = 'a';
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] deactivating/activating constraint
Oleg wrote: Dear All, is it possible to temporary deactivate a constraint in PostgreSQL? There is a constraint that does not allow me to write some data (see e-mail below). But after all datasets are written the constraint is valid. So I was wondering wether it is possible to deactivate a constraint write all records in all tables then activate constraint again. Somebody told me that it is possible in Oracle. Thanks a lot in advance Oleg Have you tried to make the Foreign Key deferrable and initially deferred? See: http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html HTH Sebastian ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Bug with view definition?
Tom Lane wrote: =?ISO-8859-1?Q?Sebastian_B=F6ck?= <[EMAIL PROTECTED]> writes: why is the last definition of a view not working, although the documentation says all three are equal? The documentation says no such thing... So I misinterpreted the following: http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html that says: FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2. It is also equivalent to FROM T1 INNER JOIN T2 ON TRUE CREATE OR REPLACE VIEW not_working AS SELECT one.* FROM one.one, two.two JOIN join1 ON join1.id = one.id; JOIN binds tighter than comma in FROM-lists, so that means FROM one.one CROSS JOIN (two.two JOIN join1 ON join1.id = one.id); which of course is illegal because the JOIN/ON condition refers to something that's not within the current JOIN. Your preceding example parenthesizes as FROM (one.one CROSS JOIN two.two) JOIN join1 ON join1.id = one.id; which is OK. Thanks for clarification Sebastian ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Bug with view definition?
Hello all, why is the last definition of a view not working, although the documentation says all three are equal? Testcase: CREATE SCHEMA one; CREATE SCHEMA two; CREATE TABLE one.one ( id SERIAL PRIMARY KEY ); CREATE TABLE two.two ( id SERIAL PRIMARY KEY ); CREATE TABLE join1 ( id SERIAL PRIMARY KEY ); CREATE OR REPLACE VIEW working AS SELECT one.* FROM one.one JOIN two.two ON TRUE JOIN join1 ON join1.id = one.id; CREATE OR REPLACE VIEW also_working AS SELECT one.* FROM one.one CROSS JOIN two.two JOIN join1 ON join1.id = one.id; CREATE OR REPLACE VIEW not_working AS SELECT one.* FROM one.one, two.two JOIN join1 ON join1.id = one.id; Thanks in advance Sebastian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Update on tables when the row doesn't change
Dawid Kuroczko wrote: Control question, I didn't check it, but would it be enough to change from: UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id; to: UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id AND text1 <> NEW.text1? ... I may be wrong. :) Yes, thats more elegant then my other (4th) solution. Was late yesterday evening ;) Be wary of the NULL values though. :) Either don't use them, add something like 'AND (text1 <> NEW.text1 OR text1 IS NULL OR NEW.text1 IS NULL)' or something more complicated. :) Thanks for the notice, but I have a special operator for this: CREATE OR REPLACE FUNCTION different (ANYELEMENT, ANYELEMENT) RETURNS BOOLEAN AS $$ BEGIN IF ($1 <> $2) OR ($1 IS NULL <> $2 IS NULL) THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE; CREATE OPERATOR <<>> ( LEFTARG = ANYELEMENT, RIGHTARG = ANYELEMENT, PROCEDURE = different, COMMUTATOR = <<>>, NEGATOR = ); Sebastian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Update on tables when the row doesn't change
Sorry, missed the SQL to test.
Sebastian
/* tables */
CREATE TABLE test (
id INTEGER PRIMARY KEY,
test TEXT NOT NULL
);
CREATE TABLE join1 (
id INTEGER PRIMARY KEY,
text1 TEXT NOT NULL
);
CREATE TABLE join2 (
id INTEGER PRIMARY KEY,
text2 TEXT NOT NULL
);
CREATE TABLE join3 (
id INTEGER PRIMARY KEY,
text3 TEXT NOT NULL
);
/* view */
CREATE OR REPLACE VIEW view_test AS
SELECT
id,
test,
text1,
text2,
text3
FROM test
LEFT JOIN join1 USING (id)
LEFT JOIN join2 USING (id)
LEFT JOIN join3 USING (id);
/* data */
INSERT INTO test (id) VALUES ('1','Test 1');
INSERT INTO test (id) VALUES ('2','Test 2');
INSERT INTO test (id) VALUES ('3','Test 3');
INSERT INTO join1 (id,text1) VALUES ('1','Test 1 1');
INSERT INTO join1 (id,text1) VALUES ('2','Test 1 2');
INSERT INTO join1 (id,text1) VALUES ('3','Test 1 3');
INSERT INTO join2 (id,text2) VALUES ('1','Test 2 1');
INSERT INTO join2 (id,text2) VALUES ('2','Test 2 2');
INSERT INTO join2 (id,text2) VALUES ('3','Test 2 3');
INSERT INTO join3 (id,text3) VALUES ('1','Test 3 1');
INSERT INTO join3 (id,text3) VALUES ('2','Test 3 2');
INSERT INTO join3 (id,text3) VALUES ('3','Test 3 3');
/* 1st way of separating updates
pro: no unnecessary updates on tables
con: the view gets evaluated 4 times
This was the whole thing being before change.
This can get *really* slow, if the view itself is not the fastest.
*/
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD NOTHING;
CREATE OR REPLACE RULE upd_ AS ON UPDATE TO view_test
WHERE NEW.test <> OLD.test
DO UPDATE test
SET test = NEW.test
WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_1 AS ON UPDATE TO view_test
WHERE NEW.text1 <> OLD.text1
DO UPDATE join1
SET text1 = NEW.text1
WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_2 AS ON UPDATE TO view_test
WHERE NEW.text2 <> OLD.text2
DO UPDATE join2
SET text2 = NEW.text2
WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_3 AS ON UPDATE TO view_test
WHERE NEW.text3 <> OLD.text3
DO UPDATE join3
SET text3 = NEW.text3
WHERE id = OLD.id;
/* 2nd way of separating updates
pro: ?
con: the view gets evaluated 4 times, why?
unnecessary updates on tables
First approach to reduce execution time of update, but view gets
also evaluated 4 times (no performance boost).
Here I discovered the problem that all underlying tables are getting
the updates, even if the data in that table doesn't change.
This can hurt you as well, if you log all updates.
*/
DROP RULE upd_ ON view_test;
DROP RULE upd_1 ON view_test;
DROP RULE upd_2 ON view_test;
DROP RULE upd_3 ON view_test;
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD (
UPDATE test SET test = NEW.test WHERE id = OLD.id;
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
);
/* 3rd way of separating updates
con: unnecessary updates on tables
pro: view gets evaluated only 1 time
Not adressing the problem of unnecessary updates, but the view
gets only evaluated one time.
*/
CREATE OR REPLACE FUNCTION upd (view_test) RETURNS VOID AS $$
DECLARE
NEW ALIAS FOR $1;
BEGIN
RAISE NOTICE 'UPDATE';
UPDATE test SET test = NEW.test WHERE id = OLD.id;
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD SELECT upd (NEW.*);
/* 4th way of doing it
pro: view gets evaluated only 1 time
no unnecessary updates on tables
con: ??
Here is the way I solved all my performance problems.
Only remainig issue: How can I eliminate the response of the select?
*/
CREATE OR REPLACE FUNCTION upd (view_test, view_test) RETURNS VOID AS $$
DECLARE
NEW ALIAS FOR $1;
OLD ALIAS FOR $2;
BEGIN
IF (NEW.test <> OLD.test) THEN
RAISE NOTICE 'UPDATE test';
UPDATE test SET test = NEW.test WHERE id = OLD.id;
END IF;
IF (NEW.text1 <> OLD.text1) THEN
RAISE NOTICE 'UPDATE join1';
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
END IF;
IF (NEW.text2 <> OLD.text2) THEN
RAISE NOTICE 'UPDATE join2';
Re: [GENERAL] Update on tables when the row doesn't change
Martijn van Oosterhout wrote: I'm sure I'm not the only one, but, what are you talking about? RULEs are not really obvious so it would help if you could post an example of what you mean... Have a nice day, Hi, I'm not really talking about rules. I'm talking about updates on *real* tables, and how to avoid unnecessary updates on these tables if the row doesn't change. The situation looks like this: I have a view which is a join of a lot of tables. I have lot of conditional ON UPDATE rules to that view that split one update to the view into updates on the underlying table. The condition of each rule is constructed in a way that the underlying table only gets an update if the corresponding values change. If I collapse all these rules into one conditional rule and pass all the updates to the underlying tables, I get a lot of unnecessary updates to these real tables, if the values don't change. Thats what I want to avoid. Sorry for not beeing that clear. Sebastian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Update on tables when the row doesn't change
Hi all, maybe it's a very silly question, but why does Postgres perform an update on the table even if no data changes? I recognized this recently doing a rewrite of my rules because they took to long. I had many conditional rules I collapsed to one unconditional rule, so that the views get constructed only once. If I split these updates to the underlying tables, I get a lot of updates which don't perform any "real" updates. Can I circumvent this behaviour of Postgres only by defining lot of rules / triggers on these underlying table are there some trickier ways? Any help appreciated, Sebastian ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Problem with GIST-index and timestamps
Oleg Bartunov wrote: On Thu, 28 Apr 2005, [ISO-8859-1] Sebastian B?ck wrote: Hello, i wanted to define and GIST-index on a table with a timestamp-column containing 'infinity' values, and it throws the following error: ERROR: cannot subtract infinite timestamps Is this a known limitation? I don't know. Ok, so let me ask wether it's a bug or not. How can i avoid or circumvent this? partial index should works Sure they do, but they're not quite useful in my situation. Thanks Sebastian ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Problem with GIST-index and timestamps
Hello, i wanted to define and GIST-index on a table with a timestamp-column containing 'infinity' values, and it throws the following error: ERROR: cannot subtract infinite timestamps Is this a known limitation? How can i avoid or circumvent this? Thanks in advance Sebastian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] multicolumn GIST index question
Ron Mayer wrote: Did anyone get multi-column GIST indexes working using both the gist_btree and postgis modules? Not quite stable yet. It seems to work fine for me on small test cases (shown at the bottom), but seems to crash my database for large ones. Any advice is welcome - including pointers to better lists to ask questions like this. For me it seems to work only if the geom-column is the first column in a multicolumn-index. Haven't investigated further so far... HTH Sebastian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] index not used in joins
Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Can you post the output from your "explain analyse" calls too? The statistics aren't going to be the same on different machines. Sure, here it is. Thanks. (PS - remember to cc the list too). [output of EXPLAIN ANALYZE] OK - so what you want to know is why index "test_999" is used in the second but not the first, even though both return the same rows. The fact is that the conditional index: CREATE INDEX test_999 ON test (datum) WHERE version = '999' OR approved IS NOT NULL; AFAIK looks at the WHERE clause of your query to determine where it can run. Don't forget that the planner needs to pick which index is best *before* it starts fetching data. So - in the first example there might be rows where e.g. t.version=998 which means test_999 would be a poor choice of index. But what if the table users contains only 1 row and the column "version" has a value of "999"? It still doesn't know that the only value in "version" is 999(*). Let's say there were 2000 rows and 1900 had the value 999 - the index is still useless because we'd have to do a sequential scan to check the remaining 200 rows. Are there any other options to speed up this kind of query? Well, your problem is the (version=X OR approved IS NOT NULL) clause. I must admit I can't quite see what this is supposed to do. The "test" table connects to the "users" table via "version" (and "datum", though not a simple check) unless the "test" has been "approved", in which case it applies to all users? Can you explain what the various tables/columns are really for? The whole thing is a multiuser facility managment application. Every user can plan things like he wants (different versions). All these changes apply to a common (approved) version. Things get complicated as everybody should be able to "travel" through the history via the "datum" field. That's why i need this "silly OR" in my where-clause. At the moment i get very exciting results using immutable functions, but i have another question. In the docs it is stated that: IMMUTABLE indicates that the function always returns the same result when given the same argument values; What if i define my functions like: CREATE OR REPLACE FUNCTION datum () RETURNS TIMESTAMP AS ' SELECT datum FROM public.benutzer; ' LANGUAGE sql IMMUTABLE; They normally (untill now) give the correct results, also if the values in the underlaying view changes. Can i relay on this or is it only luck. (*) Don't forget the statistics for column values are usually out-of-date compared to the actual data, so you can't rely on it. I'm aware of that. Thanks Sebastian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] index not used in joins
Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Can you post the output from your "explain analyse" calls too? The statistics aren't going to be the same on different machines. Sure, here it is. Thanks. (PS - remember to cc the list too). [output of EXPLAIN ANALYZE] OK - so what you want to know is why index "test_999" is used in the second but not the first, even though both return the same rows. The fact is that the conditional index: CREATE INDEX test_999 ON test (datum) WHERE version = '999' OR approved IS NOT NULL; AFAIK looks at the WHERE clause of your query to determine where it can run. Don't forget that the planner needs to pick which index is best *before* it starts fetching data. So - in the first example there might be rows where e.g. t.version=998 which means test_999 would be a poor choice of index. But what if the table users contains only 1 row and the column "version" has a value of "999"? Are there any other options to speed up this kind of query? Thanks so far Sebastian ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] index not used in joins
Hello all,
i have a problem with index usage and joins.
Attached is some SQL demonstrating my problem;
Why is the index only used in the 2nd query?
Can anybody explain me how to avoid/fix this.
Thanks in advance
Sebastian
CREATE TABLE users (
login NAME NOT NULL PRIMARY KEY,
datum TIMESTAMP,
version INTEGER
);
CREATE TABLE test (
datum TIMESTAMP NOT NULL,
version INTEGER NOT NULL,
approved TIMESTAMP
);
CREATE OR REPLACE VIEW v AS
SELECT t.*
FROM test AS t
INNER JOIN users AS u ON
t.datum <= u.datum AND
(t.version = u.version OR
t.approved IS NOT NULL);
CREATE OR REPLACE FUNCTION fill () RETURNS BOOLEAN AS '
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..1000 LOOP
EXECUTE ''INSERT INTO test (datum,version)
VALUES (now(),''|| i || '')'';
END LOOP;
RETURN TRUE;
END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION approved () RETURNS BOOLEAN AS '
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..1000 LOOP
EXECUTE ''INSERT INTO test (datum,version,approved)
VALUES (now(),''|| i || '',now())'';
END LOOP;
RETURN TRUE;
END;
' LANGUAGE plpgsql;
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT approved();
INSERT INTO users (login,datum,version) VALUES ('sb',now(),'999');
CREATE INDEX test_ ON test (datum);
CREATE INDEX test_999 ON test (datum) WHERE version = '999' OR approved IS NOT
NULL;
ANALYZE;
EXPLAIN ANALYZE SELECT * FROM v;
EXPLAIN ANALYZE SELECT t.* FROM test AS t INNER JOIN users AS u ON t.datum <=
u.datum AND (t.version = '999' OR t.approved IS NOT NULL);
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] infinite recursion detected in rules for relation "..."
Tom Lane wrote: =?ISO-8859-1?Q?Sebastian_B=F6ck?= <[EMAIL PROTECTED]> writes: infinite recursion detected in rules for relation "..." If you need a patch immediately, here it is. *** src/backend/rewrite/rewriteHandler.c.orig Sat Nov 6 12:46:35 2004 --- src/backend/rewrite/rewriteHandler.c Sat Nov 20 12:47:21 2004 *** *** 1267,1272 --- 1267,1274 newstuff = RewriteQuery(pt, rewrite_events); rewritten = list_concat(rewritten, newstuff); } + + rewrite_events = list_delete_first(rewrite_events); } } regards, tom lane Thanks for the quick patch! Everything is working now. Sebastian ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] infinite recursion detected in rules for relation "..."
Hello all,
i have a problem migrating my application from version
7.4 to 8.0. Everything is fine in 7.4 but with 8.0 i get
the following error:
infinite recursion detected in rules for relation "..."
I've been able to narrow the problem down to the
attached SQL.
I don't know wether its a bug or a "feature".
Can someone explain me this behaviour.
Thanks in advance
Sebastian
CREATE TABLE ref (
name TEXT NOT NULL PRIMARY KEY
);
CREATE TABLE test (
id SERIAL PRIMARY KEY,
col1 TEXT NOT NULL UNIQUE,
col2 TEXT NOT NULL REFERENCES ref ON UPDATE CASCADE
);
CREATE TABLE log (
id INTEGER NOT NULL,
col1 TEXT NOT NULL,
col2 TEXT NOT NULL
);
CREATE OR REPLACE RULE log AS ON UPDATE TO test
DO INSERT INTO log VALUES (OLD.id,OLD.col1,OLD.col2);
CREATE VIEW bug AS
SELECT * FROM test;
CREATE OR REPLACE RULE upd AS ON UPDATE TO bug
DO INSTEAD NOTHING;
CREATE OR REPLACE RULE upd_col1 AS ON UPDATE TO bug
WHERE NEW.col1 <> OLD.col1
DO UPDATE test SET col1 = NEW.col1 WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_col2 AS ON UPDATE TO bug
WHERE NEW.col2 <> OLD.col2
DO UPDATE test SET col2 = NEW.col2 WHERE id = OLD.id;
INSERT INTO ref (name) VALUES ('Name');
INSERT INTO test (col1,col2) VALUES ('Test 1','Name');
UPDATE bug SET col1 = 'Test' WHERE id = 1;
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Bug with updateable Views and inherited tables?
Tom Lane wrote: =?ISO-8859-1?Q?Sebastian_B=F6ck?= <[EMAIL PROTECTED]> writes: I investigated a little bit further and can be more precisely about the whole thing. This (wrong) behaviour only occurs, if the view has an order by clause. The bug is triggered by the combination of an inherited UPDATE target and an unflattenable sub-Query. I verified that it's been broken for as long as we've had such features :-(. I've applied the attached patch to 8.0. Thank you! > You could probably adapt it for 7.4, but I'm hesitant to put such a nontrivial change into a stable branch without a lot more testing. It isn't that necessary for me, just wondered 'bout the strange behaviour. Sebastian ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Bug with updateable Views and inherited tables?
Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= <[EMAIL PROTECTED]> writes:
Is this a known limitation with views, rules and
inherited tables i haven't heard of? Or is it a bug?
When you haven't shown us any details, it's impossible to tell.
Let's see the actual table, view, and rule definitions. (pg_dump -s
output would be good.)
Since my first attempt to send this message seems to be caught
by spamfilters, i'll repost it.
Sorry for the delay.
I investigated a little bit further and can be more precisely
about the whole thing. This (wrong) behaviour only occurs, if
the view has an order by clause.
To repoduce, simply run the attached script.
Sebastian
CREATE TABLE id (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE test_1 (
id INTEGER PRIMARY KEY,
name TEXT
) INHERITS (id);
CREATE TABLE test_2 (
id INTEGER PRIMARY KEY,
name TEXT
) INHERITS (id);
CREATE TABLE test_3 (
id INTEGER PRIMARY KEY,
name TEXT
) INHERITS (id);
CREATE VIEW working AS SELECT * FROM id;
CREATE RULE update_working AS ON UPDATE TO working
DO INSTEAD UPDATE id SET name = NEW.name WHERE id = OLD.id;
CREATE VIEW not_working AS SELECT * FROM id ORDER BY id;
CREATE RULE update_not_working AS ON UPDATE TO not_working
DO INSTEAD UPDATE id SET name = NEW.name WHERE id = OLD.id;
INSERT INTO test_1 (name) VALUES ('Test 1');
INSERT INTO test_1 (name) VALUES ('Test 2');
INSERT INTO test_2 (name) VALUES ('Test 3');
INSERT INTO test_2 (name) VALUES ('Test 4');
INSERT INTO test_3 (name) VALUES ('Test 5');
INSERT INTO test_3 (name) VALUES ('Test 6');
SELECT * FROM working;
UPDATE working SET name = 'working' WHERE id = '1';
UPDATE working SET name = 'working' WHERE id = '3';
UPDATE working SET name = 'working' WHERE id = '5';
SELECT * FROM working;
SELECT * FROM not_working;
UPDATE not_working SET name = 'should work' WHERE id = '2';
UPDATE not_working SET name = 'should work' WHERE id = '4';
UPDATE not_working SET name = 'should work' WHERE id = '5';
SELECT * FROM not_working;
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] Bug with updateable Views and inherited tables?
Hello all, i have a view defined as a simple select of a table. This table is inherited by a couple of others. All entries belong to the child-tables. I also have an unconditional update rule on the view. If i do an update to the view, the update is rewritten to update the father-table. And now a strange thing is happening: If i do an update, this update is done correctly only on the first defined child-table. If it applies to one of the other 8 child-tables nothing happens at all. If i update the father-table directly everything is working like expected (the update is "directed" to the right table). Is this a known limitation with views, rules and inherited tables i haven't heard of? Or is it a bug? My Postgres is version 7.4.5. Thanks in advance Sebastian ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] what about uniqueness of inherited primary keys
Andreas wrote: Seastian Böck wrote: for primary keys there is a simple (and at least working for me) solution as long as you can use the SERIAL type for your primary key. [...] Now the id column gets merged and you should have the desired behaviour. If you want objects.id to get referenced by other tables you have to work around with triggers and an extra table. For persons.id everything is working fine. This solution (workaround) is only working as long you don't insert id-values without updating the corresponding sequence. Hello Se(b)astian -- you left out the 'b' in your e-mail setup ;) right, your proposal does in a way behave like I wanted. Though the idea of integrity control by the db-server is still not there for parent id-colomns. Every user or application could mess up the primary key of the inherited table. That spoils a bit of the oo-approach, I fear. I rechecked that and the conclusion is very simple: it only works reliable if the id is autogenerated by the SERIAL type. It wouldn't be that bad, if the table contents weren't merged in SELECTs. Probaply one could do some trigger-magic to check the inserted id against an id-pool in another table. If one knew anything about triggers that is ... well ... miles to go before I sleep ... For all other situations take a look at Oliver's mail. Sebastian ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
