[BUGS] Bug in check constraint?
Hi, forgive me the poor English for the writing, for technique reading is a little better.. Sees, below, that it seems to have one bug in set transform_null_equals or, then, in <> NULL. This fact occurs in Versions: 7.4.5 and 8.0.0-rc2. -- Creation with transform_null_equals set to off set transform_null_equals to OFF; --drop table cntpagit1; Create table cntpagit1 (VALORPG numeric(10,2), DTPAGTO dates); ALTER TABLE CNTPAGIT1 ADD CONSTRAINT TTT CHECK ((VALORPG > 0 AND DTPAGTO <> NULL) OR (VALORPG = 0 AND DTPAGTO = NULL)); -- They see as it was in the Catalog -- Table: CNTPAGIT1 -- DROP TABLE CNTPAGIT1; CREATE TABLE cntpagit1 ( valorpg numeric(10,2), dtpagto date, CONSTRAINT ttt CHECK (valorpg > 0::numeric AND dtpagto <> NULL::date OR valorpg = 0::numeric AND dtpagto = NULL::date) ) WITH OIDS; ALTER TABLE cntpagit1 OWNER postgres; -- Result of sql. insert into cntpagit1 values(1, NULL); Query returned successfully: one row 20540 with OID inserted, 60 ms execution times. insert into cntpagit1 values(0, '20050115 '); Query returned successfully: one row 20541 with OID inserted, 60 ms execution times. -- Creation with transform_null_equals set to on set transform_null_equals to ON; -- drop table cntpagit1; Create table cntpagit1 ( VALORPG numeric(10,2), DTPAGTO dates); ALTER TABLE CNTPAGIT1 ADD CONSTRAINT TTT CHECK ((VALORPG > 0 AND DTPAGTO <> NULL) OR (VALORPG = 0 AND DTPAGTO = NULL)); -- They see as it was in the Catalog. -- Table: CNTPAGIT1 -- DROP TABLE CNTPAGIT1; CREATE TABLE cntpagit1 (valorpg numeric(10,2), dtpagto dates, --- *** Has one bug in the transformation of <> NULL for IS NOT NULL? *** CONSTRAINT ttt CHECK (valorpg > 0::numeric AND dtpagto <> NULL::date OR valorpg = 0::numeric AND dtpagto IS NULL) ) WITH OIDS; ALTER TABLE cntpagit1 OWNER postgres; -- Result of sql. insert into cntpagit1 values(1, NULL); Query returned successfully: one row 20545 with OID inserted, 70 ms execution times. insert into cntpagit1 values(0, '20050115 '); ERROR: new row will be relation "cntpagit1" violates check constraint "ttt" -- Creating the check with IS NOT NULL and IS NULL funcionou correctly. Regards, Luiz Gonzaga da Mata. Brasil. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug in check constraint?
"Luiz Gonzaga da Mata" <[EMAIL PROTECTED]> writes: > Sees, below, that it seems to have one bug in set transform_null_equals > or, then, in <> NULL. transform_null_equals only catches the exact syntax "something = NULL". It does not touch "something <> NULL". The latter is always going to yield NULL, by definition. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1393: Adding 'LIMIT 1' to the query halts forever
I've simplified the test case to the following: CREATE TABLE foo ( id integer NOT NULL, value integer NOT NULL ); INSERT INTO foo (id, value) SELECT random() * 1000, random() * 1000 FROM generate_series(1, 10); CREATE INDEX foo_id_idx ON foo (id); CREATE INDEX foo_value_idx ON foo (value); VACUUM ANALYZE foo; EXPLAIN ANALYZE SELECT * FROM foo WHERE id = -1 ORDER BY value; QUERY PLAN - Sort (cost=186.46..186.71 rows=99 width=8) (actual time=0.101..0.101 rows=0 loops=1) Sort Key: value -> Index Scan using foo_id_idx on foo (cost=0.00..183.18 rows=99 width=8) (actual time=0.067..0.067 rows=0 loops=1) Index Cond: (id = -1) Total runtime: 0.259 ms (5 rows) EXPLAIN ANALYZE SELECT * FROM foo WHERE id = -1 ORDER BY value LIMIT 1; QUERY PLAN - Limit (cost=0.00..25.79 rows=1 width=8) (actual time=631.964..631.964 rows=0 loops=1) -> Index Scan using foo_value_idx on foo (cost=0.00..2552.75 rows=99 width=8) (actual time=631.942..631.942 rows=0 loops=1) Filter: (id = -1) Total runtime: 632.135 ms (4 rows) Maybe I don't understand something about what EXPLAIN is showing, but why does Limit have an estimated cost of 0.00..25.79 when the thing it's limiting has a cost of 0.00..2552.75? Is that the cost of just the limit operation? Is it supposed to be the cumulative cost of everything up to that point? Is the planner preferring this plan because of the 25.79 cost? A workaround appears to be: EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM foo WHERE id = -1 ORDER BY value) AS s LIMIT 1; QUERY PLAN - Limit (cost=186.46..186.48 rows=1 width=8) (actual time=0.124..0.124 rows=0 loops=1) -> Subquery Scan s (cost=186.46..187.70 rows=99 width=8) (actual time=0.110..0.110 rows=0 loops=1) -> Sort (cost=186.46..186.71 rows=99 width=8) (actual time=0.099..0.099 rows=0 loops=1) Sort Key: value -> Index Scan using foo_id_idx on foo (cost=0.00..183.18 rows=99 width=8) (actual time=0.064..0.064 rows=0 loops=1) Index Cond: (id = -1) Total runtime: 0.313 ms (7 rows) I see that the Limit in this query has an estimated cost of 186.46..186.48, so I'm still wondering why the Limit in the previous query had a cost of 0.00..25.79. Is that my ignorance about how the planner works, or is it a bug? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1393: Adding 'LIMIT 1' to the query halts forever
Michael Fuhr <[EMAIL PROTECTED]> writes: > Limit (cost=0.00..25.79 rows=1 width=8) (actual time=631.964..631.964 > rows=0 loops=1) >-> Index Scan using foo_value_idx on foo (cost=0.00..2552.75 rows=99 > width=8) (actual time=631.942..631.942 rows=0 loops=1) > Filter: (id = -1) > Total runtime: 632.135 ms > (4 rows) > Maybe I don't understand something about what EXPLAIN is showing, > but why does Limit have an estimated cost of 0.00..25.79 when the > thing it's limiting has a cost of 0.00..2552.75? This represents the planner assuming that the indexscan will only need to be run 1/99th of the way to completion. That is, having estimated that there were 99 matching rows to be found, it assumes those are uniformly distributed in the index-by-value, and that the scan can stop as soon as the first one is found. Since in reality there aren't *any* matching rows, the index scan has to go all the way to the end :-(. Even if there were matching rows, they might be much further out in the index order than the uniform-distribution hypothesis predicts, because the id and value columns might have been correlated. Basically, what you're looking at here is that the planner is thinking it should go for a fast-start plan in a scenario where that bet loses. It's still a good bet though. I'm not sure how to formulate the notion that there's too much risk of a slow result in this scenario. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1393: Adding 'LIMIT 1' to the query halts forever
On Sun, Jan 16, 2005 at 02:56:11PM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > > Maybe I don't understand something about what EXPLAIN is showing, > > but why does Limit have an estimated cost of 0.00..25.79 when the > > thing it's limiting has a cost of 0.00..2552.75? > > This represents the planner assuming that the indexscan will only need > to be run 1/99th of the way to completion. Thanks -- I understood the rationale for considering a scan on this index but not why that plan was preferred. Your explanation provides the piece I was missing. > Basically, what you're looking at here is that the planner is thinking > it should go for a fast-start plan in a scenario where that bet loses. > It's still a good bet though. I'm not sure how to formulate the notion > that there's too much risk of a slow result in this scenario. Would it be accurate to say that the planner makes the bet most likely to win without regard to how badly it might lose? Is taking the downside into consideration a tough problem to solve, or is it simply not worthwhile in the large? Thanks again. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] Bug in check constraint?
> transform_null_equals only catches the exact syntax "something = NULL". It > does not touch "something <> NULL". The latter is always going to yield > NULL, by definition. Yes, I saw this in parser_expr.c and the documentation. 1)In code "dtpagto IS NULL" is not the same thing that !(dtpagto IS NULL), or either, a condition bolean. 2) if "dtpagto <> NULL" is not a valid codification and not checked, he would not be correct that a message of error while creating constraint. The fact not to occur the error message, can delude the programmer of the SGBD with in sample of test. CONSTRAINT ttt CHECK (valorpg > 0::numeric AND dtpagto <> NULL::date OR valorpg = 0::numeric AND dtpagto IS NULL) ) WITH OIDS; ALTER TABLE cntpagit1 OWNER postgres; -- Result of sql. insert into cntpagit1 values(1, NULL); Query returned successfully: one row 20545 with OID inserted, 70 ms execution times. insert into cntpagit1 values(0, '20050115 '); ERROR: new row will be relation "cntpagit1" violates check constraint "ttt" regards, Luiz Gonzaga da Mata. Brasil. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1393: Adding 'LIMIT 1' to the query halts forever
Michael Fuhr <[EMAIL PROTECTED]> writes: > Would it be accurate to say that the planner makes the bet most > likely to win without regard to how badly it might lose? Yes, I think that's a fair summary. > Is taking the downside into consideration a tough problem to solve, or > is it simply not worthwhile in the large? I don't know how to solve it, and whether it would be worthwhile would depend considerably on how expensive the proposed solution is ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] Error in 8.0 rc5 with repeat calls to array operator
Developers,
I'm finding an issue I thought was fixed with RC2, which is affecting my
ability to use arrays in RC5:
Summary: Fast repeated calls to INT[] = INT[] operator causes error
Severity: Query Failure
Version: 8.0.0rc5, previous builds of 8.0.0
Platform: Gentoo Linux
Description:
The following comparison works fine normally:
template1=# select '{}'::INT[] = '{}'::INT[];
?column?
--
t
(1 row)
However, we're found that if you run thousands of empty array comparisons in a
few seconds/minutes, eventually the empty array comparison breaks, and you
get:
ERROR: cache lookup failed for function 0
Thereafter, *any* attempt to compare arrays gets:
dm=# select '{}'::INT[] = '{}'::INT[];
ERROR: cache lookup failed for function 0
I'm working on a repeatable test case and seeing whether vacuum and/or restart
affects this, but since we're 24 hours from wrap, I thought I should raise
this issue ASAP.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Error in 8.0 rc5 with repeat calls to array operator
Folks, > I'm finding an issue I thought was fixed with RC2, which is affecting my > ability to use arrays in RC5: Oh, this may be a bug specifically in INTARRAY; that module is installed. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] Error in 8.0 rc5 with repeat calls to array operator
Josh Berkus writes:
> However, we're found that if you run thousands of empty array comparisons in
> a
> few seconds/minutes, eventually the empty array comparison breaks, and you
> get:
> ERROR: cache lookup failed for function 0
I tried
while true; do echo "select '{}'::INT[] = '{}'::INT[];" ; done | psql
regression >/dev/null
on a couple different platforms, with and without INTARRAY (which
isn't invoked by this query anyway, so I doubt it matters). I haven't
seen any misbehavior yet.
If you can reproduce this, please attach to the backend with gdb, set a
breakpoint at errfinish(), and get a stack traceback from the point of
the error.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1393: Adding 'LIMIT 1' to the query halts forever
On Sun, Jan 16, 2005 at 04:08:35PM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > > Is taking the downside into consideration a tough problem to solve, or > > is it simply not worthwhile in the large? > > I don't know how to solve it, and whether it would be worthwhile would > depend considerably on how expensive the proposed solution is ... Would the topic merit discussion in pgsql-hackers after the dust from the 8.0 release settles down? I know little of the theory behind query planning; I'd hate to waste the developers' time on a topic that's already been debated or that has little merit. If the topic is worthwhile, then I was thinking of a configuration setting that would allow the user to request either "the plan most likely to be the fastest" or "the plan least likely to be the slowest," or maybe something in between. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
