pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem With A Rule (clear version)
3+ messages / 2 participants
[nested] [flat]
* Problem With A Rule (clear version)
@ 2002-04-12 10:50 cnliou@eurosport.com
2002-04-12 17:32 ` Re: Problem With A Rule (clear version) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: cnliou@eurosport.com @ 2002-04-12 10:50 UTC (permalink / raw)
To: pgsql-sql
Many thanks for the patience! Tom,
I am afraid that this too-complete example makes
readers headache.
Thank you all!
CN
===============
CREATE TABLE table1 (
--the invoice table.
PRIMARY KEY(column10,column1,column2),
column10 VARCHAR(255), --id#='1'
column1 VARCHAR(255), --invoice#
column2 SMALLINT, --serial number
column6 NUMERIC, --amount
column7 "char" --indicator: either 'r' or 'E'
);
CREATE TABLE table2 (
--the voucher detail table.
CONSTRAINT c1 FOREIGN KEY (column10,column1)
REFERENCES table3 (column10,column1) ON UPDATE
CASCADE ON DELETE CASCADE,
PRIMARY KEY (column10,column1,column2),
column10 VARCHAR(255), --id#='1'
column1 VARCHAR(255), --voucher#
column6 "char", --indicator: either '1' or '2'
column7 NUMERIC, --amount
column8 VARCHAR(255), --matched column for
table1.column7
column9 VARCHAR(255) --matched column for
table1.column2
);
CREATE TABLE table3 (
--the voucher master table.
PRIMARY KEY (column10,column1),
column10 VARCHAR(255), --id#='1'
column1 VARCHAR(255), --voucher#
column4 "char", --status='N'
column5 VARCHAR(255) --matched column for
table1.column1
);
CREATE RULE rule1 AS ON UPDATE TO table1
DO UPDATE table2 SET column7=NEW.column6 WHERE
column10=NEW.column10
AND column1=(SELECT column1 FROM table3 WHERE
column10=NEW.column10 AND column4='N'
AND column5=NEW.column1)
AND column6='2'
AND column8=OLD.column7
AND column9=OLD.column2;
db1=# SELECT column10,column1,column2,column6,column7
FROM table1;
column10 | column1 | column2 | column6 | column7
----------+----------+---------+---------+---------
1 | Collect1 | 2 | 11 | r
1 | Collect1 | 1 | 130 | r
(2 rows)
db1=# SELECT column1,column5 FROM table3 WHERE
column10='1' AND column4='N' AND column5='Collect1';
column1 | column5
-------------+----------
AC200108083 | Collect1
(1 row)
db1=# SELECT
column10,column1,column6,column7,column8,column9 FROM
table2 WHERE column10='1' AND column1=(SELECT column1
FROM table3 WHERE column10='1' AND column4='N' AND
column5='Collect1') AND column6='2' AND column8='r'
AND column9='2';
column10 | column1 | column6 | column7 |
column8 | column9
----------+-------------+---------+-----------+------
---+---------
1 | AC200108083 | 2 | 11.000000 | r
| 2
(1 row)
db1=# UPDATE table1 SET column6=13 WHERE column2=1;
<postgresql waits forever here. So, ctrl-c to
interrupt psql.>
Cancel request sent
ERROR: Query was cancelled.
db1=# update table2 set column7=13 where column10='1'
and column1=(SELECT column1 FROM table3 WHERE
column10='1' AND column4='N' AND column5='Collect1')
and column6='2' and column8='r' and column9=1;
UPDATE 1
IF rule1 is changed to the following version, then
the update to table1 works:
CREATE RULE rule1 AS ON UPDATE TO table1
DO UPDATE table2 SET column7=NEW.column6 WHERE
column10=NEW.column10
AND column1='AC200108083'
AND column6='2'
AND column8=OLD.column7
AND column9=OLD.column2;
--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem With A Rule (clear version)
2002-04-12 10:50 Problem With A Rule (clear version) cnliou@eurosport.com
@ 2002-04-12 17:32 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2002-04-12 17:32 UTC (permalink / raw)
To: cnliou@eurosport.com; +Cc: pgsql-sql
<cnliou@eurosport.com> writes:
> I am afraid that this too-complete example makes
> readers headache.
I'm afraid you omitted some detail that's necessary to show the
problem ... because it works fine for me.
Please try to make a pg_dump script that can be loaded into a fresh
database to produce the problem. I'm not willing to hand-edit SELECT
output into INSERT statements again.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem With A Rule (clear version)
@ 2002-04-13 01:16 cnliou@eurosport.com
0 siblings, 0 replies; 3+ messages in thread
From: cnliou@eurosport.com @ 2002-04-13 01:16 UTC (permalink / raw)
To: pgsql-sql
Thank you again! Tom,
You have reminded me to narrow down the problem. I
tested it in a brand new simplified database where
only related tables and rule are created. The rule I
had problem works fine, and fast, in this simplified
database.
I wonder my working database is corrupted by some
orphan pg_class records, constraints, triggers, etc.
So, please ignore this rule problem as it is actually
ok.
Regards,
cn
--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-04-13 01:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-12 10:50 Problem With A Rule (clear version) cnliou@eurosport.com
2002-04-12 17:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-13 01:16 Re: Problem With A Rule (clear version) cnliou@eurosport.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox