Re: [SQL] (possible) bug with constraint exclusion

2008-01-11 Thread Tom Lane
"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

2008-01-11 Thread Rajesh Kumar Mallah
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

2008-01-11 Thread Kevin Jenkins

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

2008-01-11 Thread Shane Ambler

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

2008-01-11 Thread Kevin Jenkins

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

2008-01-11 Thread Tom Lane
"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?

2008-01-11 Thread Bruce Momjian

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?

2008-01-11 Thread Simon Riggs
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?

2008-01-11 Thread Erik Jones


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.

2008-01-11 Thread Pavel Stehule
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.

2008-01-11 Thread Daniel Caune
> 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.

2008-01-11 Thread Marcin Stępnicki
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.

2008-01-11 Thread Marcin Stępnicki
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.

2008-01-11 Thread Gerardo Herzig

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?

2008-01-11 Thread Richard Huxton

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