[BUGS] Possible bug in referential integrity system

[Richard Ellis]

The submit button on the form located at
http://www.postgresql.org/bugs/bugs.php?1 results in a report of a
parsing error, so I'm sending this here as the alternative.

Is the following a bug in the referential integrity system?  This is
for PG 7.0.0.  I realize that declaring the "ref" column in t2
additionally as "not null" would prevent this.  However, why does the
"references" check allow insertion of a null value into t2 when there
are no corresponding null value in the num column of t1?

If this is actually fixed in 7.0.2, then please accept my apologies.

===

=> create table t1 (num int4, name text);
CREATE
=> create table t2 (ref int4 references t1 (num), val text);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

=> insert into t1 values (1, 'Widget1');
INSERT 17518650 1

=> insert into t2 values ( (select num from t1 where name = 'Widget1'), 'Valuable');
INSERT 17518651 1
=> insert into t2 values ( (select num from t1 where name = 'widget2'), 'Bug?');
INSERT 17518652 1

=> select * from t2;
 ref |   val
-+--
   1 | Valuable
 | Bug?
(2 rows)

=> select * from t2 where ref is null;
 ref | val
-+--
 | Bug?
(1 row)

-- 
Microsoft is not the answer.Boycott Microsoft Home page
Microsoft is the question.  http://www0.vcnet.com/bms
No is the answer.

Microsoft: Bringing you ten-year old technology, tomorrow, maybe.

[BUGS] 7.0.3 postmaster memory leak with aborted transactions

[Richard Ellis]

Test case:

create table test (data text unique);

Outside of psql, run this command (This is for bash):

while true ; do echo "begin work; insert into test (data) values ('This
is a test string.'); commit work;" ; done | psql

Watch the resident set size of the postmaster that the psql is connected
to as the while loop pumps data into psql.  As transactions are aborted,
the RSS of the postmaster continues to grow.  Looks like postmaster is
leaking memory somewhere.

Postgresql 7.0.3, compiled for Linux/glibc2 with gcc 2.95.2.

-- 
Microsoft is not the answer.Boycott Microsoft Home page
Microsoft is the question.  http://www0.vcnet.com/bms
No is the answer.

Microsoft: Bringing you ten-year old technology, tomorrow, maybe.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [BUGS] is this my date problem

[Richard Ellis]

On Wed, Oct 01, 2003 at 07:57:18PM -0700, Theodore Petrosky wrote:
> here  is the  actual query:
> 
> agencysacks=# SELECT jobnumseq, (SELECT cname FROM
> clientinfo ci WHERE ci.acode = j.clientid) as client,
> shrtdesc, to_char(proofduedate, 'Dy FMMon DD,   
> HH12 am') FROM jobs j WHERE proofduedate BETWEEN
> to_timestamp('01 October 2003 00:01', 'DD Month 
> HH24:MI') AND to_timestamp ('01 October 2003 23:59',
> 'DD Month  HH24:MI') ORDER BY client,
>... 
> I am trying to create a 'today' type query. between
> october 1, 2003 00:01 am and october 1, 2003 23:59 

If you want a "today" type query, why are you using between?  This
should work, and be a whole lot more reliable:

SELECT jobnumseq, (SELECT cname 
   FROM clientinfo ci 
   WHERE ci.acode = j.clientid) as client,
   shrtdesc, 
   to_char(proofduedate, 'Dy FMMon DD,   HH12 am') 
FROM jobs j 
WHERE date(proofduedate) = '2003-10-01')
ORDER BY client,

or if you want to derive "today" automatically this should work:

SELECT jobnumseq, (SELECT cname 
   FROM clientinfo ci 
   WHERE ci.acode = j.clientid) as client,
   shrtdesc, 
   to_char(proofduedate, 'Dy FMMon DD,   HH12 am') 
FROM jobs j 
WHERE date(proofduedate) = date(now()))
ORDER BY client,

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster