From: mueller@pmsun20.dial.eunet.ch
To: pgsql-sql@postgresql.org
Subject: RULE problem
Date: Tue, 2 Mar 1999 23:06:55 +0100 (MET)
Message-ID: <199903022206.XAA05788@dial.eunet.ch> (raw)
Hi
I have a problem using rules and do not know if I am wrong or if there
is a bug somewhere?
I have two tables A and B. I want to restrict deleting an instance of table
A if there is a instance in B referencing A.
I tried to set up a rule for this. Here's an example:
-- START EXAMPLE
-- **********************************************************************
-- * rule_test.sql *
-- * A test case for rule usage *
-- * *
-- * Last modification: 03/02/1999 pm, SWS *
-- **********************************************************************
-- Create table
CREATE TABLE a ( a INT PRIMARY KEY );
CREATE TABLE b ( b INT PRIMARY KEY, ref INT );
-- RULEs ----------------------------------------------------------------
-- Rules for database integrity.
-- Restrict REMOVE on A if there is still an entry in B referencing A
CREATE RULE demo AS ON DELETE TO a
WHERE EXISTS (
SELECT * FROM b
WHERE b.ref = CURRENT.a
)
DO INSTEAD NOTHING
;
-- FILL IN DATA ---------------------------------------------------------
INSERT INTO a VALUES ( '1' );
INSERT INTO a VALUES ( '2' );
INSERT INTO a VALUES ( '3' );
INSERT INTO b VALUES ( '1', '1' );
INSERT INTO b VALUES ( '2', '3' );
INSERT INTO b VALUES ( '3', '3' );
-- TRY TO REMOVE --------------------------------------------------------
DELETE FROM a WHERE a = '1'; -- Should not work because of b.1
DELETE FROM a WHERE a = '2'; -- Should work
DELETE FROM a WHERE a = '3'; -- Should not work because of b.2 and b.3
-- END EXAMPLE
The DELETE commands does not work and ends up in an ERROR in all 3 cases:
DELETE FROM a WHERE a = '1'; -- Should not work because of b.1
ERROR: ExecEvalExpr: unknown expression type 108
DELETE FROM a WHERE a = '2'; -- Should work
ERROR: ExecEvalExpr: unknown expression type 108
DELETE FROM a WHERE a = '3'; -- Should not work because of b.2 and b.3
ERROR: ExecEvalExpr: unknown expression type 108
And trying to understand what's going on I issued en EXPLAIN command,
crashing the backend:
pascal=> explain verbose delete from a;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while
processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
What I'm doing wrong here? Any hint? Thanks for any help.
Pascal Mueller
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mueller@pmsun20.dial.eunet.ch
Subject: Re: RULE problem
In-Reply-To: <199903022206.XAA05788@dial.eunet.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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