Re: [SQL] (possible) bug with constraint exclusion
"Rajesh Kumar Mallah" <[EMAIL PROTECTED]> writes: > Am I correct in understanding that the current behavior is inappropriate > and shall be corrected at some point of time in future versions ? It's a bug, it's patched: http://archives.postgresql.org/pgsql-committers/2008-01/msg00184.php regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] (possible) bug with constraint exclusion
On Jan 12, 2008 1:26 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Rajesh Kumar Mallah" <[EMAIL PROTECTED]> writes: > > looks like constraint exclusion is being too aggressive in excluding null > > values > > Hmm, you're right. Looks like I broke it here: > http://archives.postgresql.org/pgsql-committers/2007-05/msg00187.php > > > although its well known that check constraints apply on not null values > > only. > > No, that is not a correct statement either --- it's exactly that type of > sloppy thinking that got me into trouble with this patch :-( > > The problem is that predicate_refuted_by_simple_clause() is failing to > distinguish whether "refutes" means "proves false" or "proves not true". > For constraint exclusion we have to use the stricter "proves false" > interpretation, and in that scenario a clause "foo IS NULL" fails to > refute a check constraint "foo > 0", because the latter will produce > NULL which isn't false and therefore doesn't cause the check constraint > to fail. > > The motivation for that patch was to support IS NULL as one partition > of a partitioned table. Thinking about it I see that if the other > partitions have check constraints like "foo > 0" then the partitioning > is actually incorrect, because the other check constraints are failing > to exclude NULLs. The right way to set up such a partitioned table is > to include "foo IS NOT NULL" as part of the check constraint, or as > a special-purpose NOT NULL flag, except in the IS NULL partition. > The current constraint exclusion logic fails to notice attnotnull, > though. So the correct fix seems to be: Dear Tom, Thanks for the elaborate explanation on your part, owing to my limitations I could not understand all the parts of it. Am I correct in understanding that the current behavior is inappropriate and shall be corrected at some point of time in future versions ? thanks once again to all the developers for making PostgreSQL. regds mallah. > > * Fix predicate_refuted_by_simple_clause to not suppose that a strict > operator is proved FALSE by an IS NULL clause. > > * Fix relation_excluded_by_constraints to add "foo IS NOT NULL" clauses > to the constraint list for attnotnull columns (perhaps this should be > pushed into get_relation_constraints?). This buys back the loss of > exclusion from the other change, so long as the partitioning is done > correctly. > > regards, tom lane > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL question: Highest column value of unique column pairs
Thanks! How would I find the highest score in the union of the two tables? I tried this but it can't find unionTable: SELECT * FROM (select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from myscorestable) as unionTable WHERE unionTable.score= (SELECT max(unionTable.score) FROM unionTable); Shane Ambler wrote: Kevin Jenkins wrote: Hi, I have the following table which holds the result of 1 on 1 matches: FName1, LName1, Score1, FName2, LName2, Score2, Date John, Doe,85 Bill, Gates, 20 Jan 1. John, Archer, 90 John, Doe,120 Jan 5 Bob,Barker, 70 Calvin, Klien 8 Jan 8 John, Doe,60 Bill, Gates, 25 Jan 3. So columns 1 and 2 hold the first person. Column 3 holds his score. Columns 4 and 5 hold the second person. Column 6 holds his score. I want to return the most recent score for each person (be they an opponent or myself). And the resultant table shouldn't care if they are person 1 or 2. So the end result would be FName, LName, Score, Date John,Doe, 120Jan 5. John,Archer 90 Jan 5. Bob, Barker 70 Jan 8 Bill,Gates 25 Jan 3 Calvin Klien 8 Jan 8 Thanks for any help! First I would say you should have one person in a row and have another table to join them like you want. Try (untested just guessing) - select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from myscorestable order by 3 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SQL question: Highest column value of unique column pairs
Kevin Jenkins wrote: Hi, I have the following table which holds the result of 1 on 1 matches: FName1, LName1, Score1, FName2, LName2, Score2, Date John, Doe,85 Bill, Gates, 20 Jan 1. John, Archer, 90 John, Doe,120 Jan 5 Bob,Barker, 70 Calvin, Klien 8 Jan 8 John, Doe,60 Bill, Gates, 25 Jan 3. So columns 1 and 2 hold the first person. Column 3 holds his score. Columns 4 and 5 hold the second person. Column 6 holds his score. I want to return the most recent score for each person (be they an opponent or myself). And the resultant table shouldn't care if they are person 1 or 2. So the end result would be FName, LName, Score, Date John,Doe, 120Jan 5. John,Archer 90 Jan 5. Bob, Barker 70 Jan 8 Bill,Gates 25 Jan 3 Calvin Klien 8 Jan 8 Thanks for any help! First I would say you should have one person in a row and have another table to join them like you want. Try (untested just guessing) - select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from myscorestable order by 3 -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 1: 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
[SQL] SQL question: Highest column value of unique column pairs
Hi, I have the following table which holds the result of 1 on 1 matches: FName1, LName1, Score1, FName2, LName2, Score2, Date John, Doe,85 Bill, Gates, 20 Jan 1. John, Archer, 90 John, Doe,120 Jan 5 Bob,Barker, 70 Calvin, Klien 8 Jan 8 John, Doe,60 Bill, Gates, 25 Jan 3. So columns 1 and 2 hold the first person. Column 3 holds his score. Columns 4 and 5 hold the second person. Column 6 holds his score. I want to return the most recent score for each person (be they an opponent or myself). And the resultant table shouldn't care if they are person 1 or 2. So the end result would be FName, LName, Score, Date John,Doe, 120Jan 5. John,Archer 90 Jan 5. Bob, Barker 70 Jan 8 Bill,Gates 25 Jan 3 Calvin Klien 8 Jan 8 Thanks for any help! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] (possible) bug with constraint exclusion
"Rajesh Kumar Mallah" <[EMAIL PROTECTED]> writes: > looks like constraint exclusion is being too aggressive in excluding null > values Hmm, you're right. Looks like I broke it here: http://archives.postgresql.org/pgsql-committers/2007-05/msg00187.php > although its well known that check constraints apply on not null values only. No, that is not a correct statement either --- it's exactly that type of sloppy thinking that got me into trouble with this patch :-( The problem is that predicate_refuted_by_simple_clause() is failing to distinguish whether "refutes" means "proves false" or "proves not true". For constraint exclusion we have to use the stricter "proves false" interpretation, and in that scenario a clause "foo IS NULL" fails to refute a check constraint "foo > 0", because the latter will produce NULL which isn't false and therefore doesn't cause the check constraint to fail. The motivation for that patch was to support IS NULL as one partition of a partitioned table. Thinking about it I see that if the other partitions have check constraints like "foo > 0" then the partitioning is actually incorrect, because the other check constraints are failing to exclude NULLs. The right way to set up such a partitioned table is to include "foo IS NOT NULL" as part of the check constraint, or as a special-purpose NOT NULL flag, except in the IS NULL partition. The current constraint exclusion logic fails to notice attnotnull, though. So the correct fix seems to be: * Fix predicate_refuted_by_simple_clause to not suppose that a strict operator is proved FALSE by an IS NULL clause. * Fix relation_excluded_by_constraints to add "foo IS NOT NULL" clauses to the constraint list for attnotnull columns (perhaps this should be pushed into get_relation_constraints?). This buys back the loss of exclusion from the other change, so long as the partitioning is done correctly. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] trigger for TRUNCATE?
Added to TODO: > * Add ability to trigger on TRUNCATE > > http://archives.postgresql.org/pgsql-sql/2008-01/msg00050.php --- Simon Riggs wrote: > On Fri, 2008-01-11 at 08:24 +, Richard Huxton wrote: > > > I've always considered TRUNCATE to be DDL rather than DML. I mentally > > group it with DROP TABLE rather than DELETE> > > DDL/DML probably isn't the right split, since its then arguable as to > which group of commands it belongs in. > > I see we have 3 types of commands: > > 1. Commands that alter the rows in the table > e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group > > 2. Commands that change the shape of a table > e.g. ALTER TABLE add/drop column, change type, constraints etc > > 3. Commands that change the environment of a table > e.g. foreign keys, indexes, grants, set fillfactor, ANALYZE, VACUUM, > CLUSTER etc > > Type (1) commands need to be replicated always, sliding down the scale > to the type (3) which might well be site dependent. > > Applications seldom issue type 3 commands anyway, so its easy for a DBA > to arrange for them to be executed in multiple places and there isn't > any timing requirement usually to making that work. In some cases some > of these factors might be managed by replication controllers, so the DBA > doesn't need to touch at least some of these aspects. > > Applications do issue some type 2 commands, but usually they are for > TEMP tables. Type 2 commands do change replication, but might not need > to be exactly replicated on both sites. Again, some utilities exist to > ensure that DDL changes are correctly replicated, so there is slightly > less need for triggers on this. In many cases the application is locked > down completely anyway and almost no DDL is ever executed. If it is > executed it needs to be done in coordination with a change of > application version. > > Applications issue lots of type 1 commands and we can't always easily > change the SQL they execute. It's very common for an application to have > a single userid, so its not a problem for it to be the owner of the > table as well and hence TRUNCATE is usable. It is often written without > any thought for replication, which is usually an afterthought. (If we > allowed RULEs to translate TRUNCATE into DELETEs it would at least plug > the gap, but thats not a great planand I'm not suggesting it.) > > So the main gap in all of this is the lack of a TRUNCATE trigger, > probably also the lack of a specific TRUNCATE privilege as well. > > -- > Simon Riggs > 2ndQuadrant http://www.2ndQuadrant.com > > > ---(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 -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] trigger for TRUNCATE?
On Fri, 2008-01-11 at 08:24 +, Richard Huxton wrote: > I've always considered TRUNCATE to be DDL rather than DML. I mentally > group it with DROP TABLE rather than DELETE> DDL/DML probably isn't the right split, since its then arguable as to which group of commands it belongs in. I see we have 3 types of commands: 1. Commands that alter the rows in the table e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group 2. Commands that change the shape of a table e.g. ALTER TABLE add/drop column, change type, constraints etc 3. Commands that change the environment of a table e.g. foreign keys, indexes, grants, set fillfactor, ANALYZE, VACUUM, CLUSTER etc Type (1) commands need to be replicated always, sliding down the scale to the type (3) which might well be site dependent. Applications seldom issue type 3 commands anyway, so its easy for a DBA to arrange for them to be executed in multiple places and there isn't any timing requirement usually to making that work. In some cases some of these factors might be managed by replication controllers, so the DBA doesn't need to touch at least some of these aspects. Applications do issue some type 2 commands, but usually they are for TEMP tables. Type 2 commands do change replication, but might not need to be exactly replicated on both sites. Again, some utilities exist to ensure that DDL changes are correctly replicated, so there is slightly less need for triggers on this. In many cases the application is locked down completely anyway and almost no DDL is ever executed. If it is executed it needs to be done in coordination with a change of application version. Applications issue lots of type 1 commands and we can't always easily change the SQL they execute. It's very common for an application to have a single userid, so its not a problem for it to be the owner of the table as well and hence TRUNCATE is usable. It is often written without any thought for replication, which is usually an afterthought. (If we allowed RULEs to translate TRUNCATE into DELETEs it would at least plug the gap, but thats not a great planand I'm not suggesting it.) So the main gap in all of this is the lack of a TRUNCATE trigger, probably also the lack of a specific TRUNCATE privilege as well. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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: [SQL] trigger for TRUNCATE?
On Jan 11, 2008, at 2:24 AM, Richard Huxton wrote: Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: My thinking is that a TRUNCATE trigger is a per-statement trigger which doesn't have access to the set of deleted rows (Replicator uses it that way -- we replicate the truncate action, and replay it on the replica). In that way it would be different from a per-statement trigger for DELETE. Ah, right. I was thinking in terms of having TRUNCATE actually fire the existing ON DELETE-type triggers, but that's not really helpful --- you'd need a separate trigger-event type. So we could just say by fiat that an ON TRUNCATE trigger doesn't get any rowset information, even after we add that for the other types of statement-level triggers. I've always considered TRUNCATE to be DDL rather than DML. I mentally group it with DROP TABLE rather than DELETE> Not that DDL statement triggers wouldn't be just as useful for replication. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL stored function inserting and returning data in a row.
Hello > By the way, is there any performance difference between pure SQL and > PL/pgSQL stored functions? If I remember correctly there was such a > distinction between pure SQL statement and PL/PLSQL stored procedures > (Oracle), in the sense that PL/PLSQL stored procedures are executed > within the PL/PLSQL engine which sends pure SQL statements to the SQL > engine for execution. There is a little overhead between PL/PLSQL and > SQL engines. > create or replace function test1(integer) returns integer as $$select $1;$$ language sql immutable; create or replace function test2(integer) returns integer as $$begin return $1; end$$ language plpgsql immutable; postgres=# select count(*) from (select test1(i) from generate_series(1,10) g(i)) f; count 10 (1 row) Time: 123,532 ms postgres=# select count(*) from (select test2(i) from generate_series(1,10) g(i)) f; count 10 (1 row) Time: 123,877 ms but if you forgot immutable postgres=# create or replace function test3(integer) returns integer as $$begin return $1; end$$ language plpgsql; CREATE FUNCTION Time: 430,258 ms postgres=# select count(*) from (select test3(i) from generate_series(1,10) g(i)) f; count 10 (1 row) Time: 472,150 ms Regards Pavel Stehule ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL stored function inserting and returning data in a row.
> What about
> $$
> INSERT INTO ;
> select currval('seq_matchmaking_session_id');
> $$ language sql;
>
> ?
Indeed... :-( For some reason, I thought that it was not possible to
have to SQL statement in an SQL stored function.
By the way, is there any performance difference between pure SQL and
PL/pgSQL stored functions? If I remember correctly there was such a
distinction between pure SQL statement and PL/PLSQL stored procedures
(Oracle), in the sense that PL/PLSQL stored procedures are executed
within the PL/PLSQL engine which sends pure SQL statements to the SQL
engine for execution. There is a little overhead between PL/PLSQL and
SQL engines.
Regards,
--
Daniel
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL stored function inserting and returning data in a row.
On Jan 11, 2008 4:23 AM, Daniel Caune <[EMAIL PROTECTED]> wrote: Please ignore my post. I havent' read your message carefully enough. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] SQL stored function inserting and returning data in a row.
On Jan 11, 2008 4:23 AM, Daniel Caune <[EMAIL PROTECTED]> wrote: > Hi, > > Is there any way to define a SQL stored function that inserts a row in a > table and returns the serial generated? Maybe you just need INSERT ... RETURNING? http://www.postgresql.org/docs/8.2/interactive/sql-insert.html " Insert a single row into table distributors, returning the sequence number generated by the DEFAULT clause: INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did; " ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SQL stored function inserting and returning data in a row.
Daniel Caune wrote:
Hi,
Is there any way to define a SQL stored function that inserts a row in a
table and returns the serial generated?
CREATE TABLE matchmaking_session
(
session_id bigint NOT NULL DEFAULT
nextval('seq_matchmaking_session_id'),
...
);
CREATE FUNCTION create_matchmaking_sesssion(...)
RETURNS bigint
AS $$
INSERT INTO matchmaking_session(...)
VALUES (...)
RETURNING session_id;
$$ LANGUAGE SQL;
2008-01-10 22:08:48 EST ERROR: return type mismatch in function
declared to return bigint
2008-01-10 22:08:48 EST DETAIL: Function's final statement must be a
SELECT.
2008-01-10 22:08:48 EST CONTEXT: SQL function
"create_matchmaking_sesssion"
What about
$$
INSERT INTO ;
select currval('seq_matchmaking_session_id');
$$ language sql;
?
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] trigger for TRUNCATE?
Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: My thinking is that a TRUNCATE trigger is a per-statement trigger which doesn't have access to the set of deleted rows (Replicator uses it that way -- we replicate the truncate action, and replay it on the replica). In that way it would be different from a per-statement trigger for DELETE. Ah, right. I was thinking in terms of having TRUNCATE actually fire the existing ON DELETE-type triggers, but that's not really helpful --- you'd need a separate trigger-event type. So we could just say by fiat that an ON TRUNCATE trigger doesn't get any rowset information, even after we add that for the other types of statement-level triggers. I've always considered TRUNCATE to be DDL rather than DML. I mentally group it with DROP TABLE rather than DELETE> -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
