pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with trigger that updates a row prior to a potentially aborted deletion?
9+ messages / 3 participants
[nested] [flat]
* Help with trigger that updates a row prior to a potentially aborted deletion?
@ 2006-03-01 13:30 Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-01 14:30 ` Re: Help with trigger that updates a row prior to a potentially Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-03-01 18:23 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 9+ messages in thread
From: Simon Kinsella @ 2006-03-01 13:30 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I have a situation where a DELETE operation may (correctly) fail due to a
RESTRICT FK constraint. If so, I need to set a flag in the row indicating
that it has been marked for deletion so that I can disregarded in subsequent
queries.
I'm trying to achieve this with a BEFORE DELETE trigger, which would set the
'marked_for_deletion' field to TRUE before attempting the delete proper.
Then if the DELETE fails the row would still be tagged and I'd be happy.
Problem is, when the DELETE operation fails with a RESTRICT VIOLATION error
the entire operation is rolled back, including the BEFORE triggers, leaving
me back where I started.
Is there anyway to get the DELETE operation, or more specifically the FK
constraint, to fail silently, i.e. to skip over the failed operation and not
throw an exception? I'm really racking my brains on this one but not really
getting anywhere!
I have sample data defs to play with if that would be helpful.
Thanks in advance!
Simon Kinsella
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help with trigger that updates a row prior to a potentially
2006-03-01 13:30 Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
@ 2006-03-01 14:30 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2006-03-01 14:30 UTC (permalink / raw)
To: Simon Kinsella <simon@bluefiresystems.co.uk>; +Cc: pgsql-sql
On Wed, 1 Mar 2006, Simon Kinsella wrote:
> Hi all,
>
> I have a situation where a DELETE operation may (correctly) fail due to a
> RESTRICT FK constraint. If so, I need to set a flag in the row indicating
> that it has been marked for deletion so that I can disregarded in subsequent
> queries.
>
> I'm trying to achieve this with a BEFORE DELETE trigger, which would set the
> 'marked_for_deletion' field to TRUE before attempting the delete proper.
> Then if the DELETE fails the row would still be tagged and I'd be happy.
> Problem is, when the DELETE operation fails with a RESTRICT VIOLATION error
> the entire operation is rolled back, including the BEFORE triggers, leaving
> me back where I started.
>
> Is there anyway to get the DELETE operation, or more specifically the FK
> constraint, to fail silently, i.e. to skip over the failed operation and not
> throw an exception?
Not with the standard constraint trigger, no. In general, constraint
checks happen after the action and as such can't skip over an operation
since it's already happened.
You might be able to do this within a function however if you do the
update and then start an exeption checking block to do the delete.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help with trigger that updates a row prior to a potentially aborted deletion?
2006-03-01 13:30 Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
@ 2006-03-01 18:23 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-01 20:41 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
1 sibling, 1 reply; 9+ messages in thread
From: Andrew Sullivan @ 2006-03-01 18:23 UTC (permalink / raw)
To: pgsql-sql
On Wed, Mar 01, 2006 at 01:30:23PM -0000, Simon Kinsella wrote:
> I'm trying to achieve this with a BEFORE DELETE trigger, which would set the
> 'marked_for_deletion' field to TRUE before attempting the delete proper.
> Then if the DELETE fails the row would still be tagged and I'd be happy.
> Problem is, when the DELETE operation fails with a RESTRICT VIOLATION error
> the entire operation is rolled back, including the BEFORE triggers, leaving
> me back where I started.
Yes. In 8.1, you could use a subtransaction for the DELETE, which I
think would allow you to rollback at that point and still leave the
UPDATE in place.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help with trigger that updates a row prior to a potentially aborted deletion?
2006-03-01 13:30 Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-01 18:23 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Andrew Sullivan <ajs@crankycanuck.ca>
@ 2006-03-01 20:41 ` Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-02 12:19 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 9+ messages in thread
From: Simon Kinsella @ 2006-03-01 20:41 UTC (permalink / raw)
To: pgsql-sql
Ok thanks, will check this out. Is that the same as savepoints, or something
different? (am using 8.1.2)
At the moment I'm investigating using a rule (rewrite the DELETE as an
UPDATE to set the flag, then use an AFTER UPDATE trigger to attempt to
delete the row if the flag was set). Not sure if it's going to work but if
so I'll post back.
Thanks!
Simon
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Andrew Sullivan
Sent: Wednesday, March 01, 2006 6:24 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help with trigger that updates a row prior to a
potentially aborted deletion?
On Wed, Mar 01, 2006 at 01:30:23PM -0000, Simon Kinsella wrote:
> I'm trying to achieve this with a BEFORE DELETE trigger, which would
> set the 'marked_for_deletion' field to TRUE before attempting the delete
proper.
> Then if the DELETE fails the row would still be tagged and I'd be happy.
> Problem is, when the DELETE operation fails with a RESTRICT VIOLATION
> error the entire operation is rolled back, including the BEFORE
> triggers, leaving me back where I started.
Yes. In 8.1, you could use a subtransaction for the DELETE, which I think
would allow you to rollback at that point and still leave the UPDATE in
place.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the
marketplace.
--Philip Greenspun
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help with trigger that updates a row prior to a potentially aborted deletion?
2006-03-01 13:30 Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-01 18:23 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-01 20:41 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
@ 2006-03-02 12:19 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-03 00:19 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
0 siblings, 1 reply; 9+ messages in thread
From: Andrew Sullivan @ 2006-03-02 12:19 UTC (permalink / raw)
To: pgsql-sql
On Wed, Mar 01, 2006 at 08:41:20PM -0000, Simon Kinsella wrote:
> Ok thanks, will check this out. Is that the same as savepoints, or something
> different? (am using 8.1.2)
Yes, same thing.
> At the moment I'm investigating using a rule (rewrite the DELETE as an
> UPDATE to set the flag, then use an AFTER UPDATE trigger to attempt to
> delete the row if the flag was set). Not sure if it's going to work but if
> so I'll post back.
In a message you sent that I read after I sent mine, you also said
you had to be able to handle deletes from the table with CASCADE. My
suggestion won't work for that, I don't think (but it might be worth
a try). The only other thing I can think of is just flag everything,
and use a daemon to go around and perform the actual deletes for you.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help with trigger that updates a row prior to a potentially aborted deletion?
2006-03-01 13:30 Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-01 18:23 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-01 20:41 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-02 12:19 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Andrew Sullivan <ajs@crankycanuck.ca>
@ 2006-03-03 00:19 ` Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-03 12:42 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 9+ messages in thread
From: Simon Kinsella @ 2006-03-03 00:19 UTC (permalink / raw)
To: pgsql-sql
Hi Andrew,
I think I may have cracked this problem by combining a RULE ON DELETE which
calls a function instead of the standard DELETE op. No triggers. It was a
real nightmare as it was my first attempt at a rule and it kept ending up in
circular self-references. In the end though it boiled down to a pretty
compact solution. It's getting late now but I'll be testing it out properly
tomorrow. I can post it up then if you're interested.
Thanks for your help,
Simon
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Andrew Sullivan
Sent: Thursday, March 02, 2006 12:20 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help with trigger that updates a row prior to a
potentially aborted deletion?
On Wed, Mar 01, 2006 at 08:41:20PM -0000, Simon Kinsella wrote:
> Ok thanks, will check this out. Is that the same as savepoints, or
> something different? (am using 8.1.2)
Yes, same thing.
> At the moment I'm investigating using a rule (rewrite the DELETE as an
> UPDATE to set the flag, then use an AFTER UPDATE trigger to attempt to
> delete the row if the flag was set). Not sure if it's going to work
> but if so I'll post back.
In a message you sent that I read after I sent mine, you also said you had
to be able to handle deletes from the table with CASCADE. My suggestion
won't work for that, I don't think (but it might be worth a try). The only
other thing I can think of is just flag everything, and use a daemon to go
around and perform the actual deletes for you.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help with trigger that updates a row prior to a potentially aborted deletion?
2006-03-01 13:30 Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-01 18:23 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-01 20:41 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-02 12:19 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-03 00:19 ` Re: Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
@ 2006-03-03 12:42 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 9+ messages in thread
From: Andrew Sullivan @ 2006-03-03 12:42 UTC (permalink / raw)
To: pgsql-sql
On Fri, Mar 03, 2006 at 12:19:22AM -0000, Simon Kinsella wrote:
> Hi Andrew,
>
> I think I may have cracked this problem by combining a RULE ON DELETE which
> calls a function instead of the standard DELETE op. No triggers. It was a
Ah. Yes, likely. Yeah, you can't do that.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help with trigger that updates a row prior to a potentially aborted deletion?
@ 2006-03-01 14:19 Simon Kinsella <simon@bluefiresystems.co.uk>
0 siblings, 0 replies; 9+ messages in thread
From: Simon Kinsella @ 2006-03-01 14:19 UTC (permalink / raw)
To: pgsql-sql
Hello Achilleus
Thanks for your feedback. On changing the return to NULL:
According to the docs, if I return NULL in the BEFORE trigger itself, all
subsequent triggers and the row-level op itself (the actual delete) will be
skipped completely, which is no good. I will confirm this to make sure
though.
On your suggestion of manually updating, I have been trying something like
this with interesting (but undesirable!) results:
CREATE OR REPLACE FUNCTION fn_trg_mark_ref_as_deleted() RETURNS TRIGGER AS
$$
BEGIN
UPDATE ref_table SET deleted = TRUE
WHERE ref_id = OLD.ref_id;
RETURN OLD;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER mark_ref_as_deleted BEFORE DELETE ON ref_table
FOR EACH ROW EXECUTE PROCEDURE fn_trg_mark_ref_as_deleted();
(I'm returning OLD for the reason above). Oddly, this does indeed set the
soft-delete flag but never deletes the row, even if there are no constraint
dependencies.
I'm going to keep playing but any other suggestions would be very welcome :)
Here are some sample schema and defs for anyone who's interested:
----------------------------------------------------------------------------
--------
-- SCHEMA DEFS:
BEGIN;
CREATE TABLE ref_table
(
ref_id INTEGER NOT NULL,
deleted BOOLEAN DEFAULT FALSE,
CONSTRAINT ref_table_pkey PRIMARY KEY (ref_id)
);
CREATE TABLE dep_table
(
dep_id INTEGER NOT NULL,
ref_id INTEGER NOT NULL,
CONSTRAINT dep_table_pkey PRIMARY KEY (dep_id)
);
ALTER TABLE dep_table ADD CONSTRAINT dep_table_depends_on_ref_table
FOREIGN KEY (ref_id)
REFERENCES ref_table (ref_id)
MATCH FULL ON DELETE NO ACTION ON UPDATE CASCADE NOT DEFERRABLE;
CREATE OR REPLACE FUNCTION fn_trg_mark_ref_as_deleted() RETURNS TRIGGER AS
$$
BEGIN
UPDATE ref_table SET deleted = TRUE
WHERE ref_id = OLD.ref_id;
RETURN OLD;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER mark_ref_as_deleted BEFORE DELETE ON ref_table
FOR EACH ROW EXECUTE PROCEDURE fn_trg_mark_ref_as_deleted();
COMMIT;
----------------------------------------------------------------------------
--------
-- SAMPLE DATA:
BEGIN;
DELETE FROM dep_table;
DELETE FROM ref_table;
INSERT INTO ref_table (ref_id) VALUES (1);
INSERT INTO ref_table (ref_id) VALUES (2);
INSERT INTO ref_table (ref_id) VALUES (3);
INSERT INTO ref_table (ref_id) VALUES (4);
INSERT INTO ref_table (ref_id) VALUES (5);
INSERT INTO dep_table (dep_id,ref_id) VALUES (100,1);
INSERT INTO dep_table (dep_id,ref_id) VALUES (101,1);
INSERT INTO dep_table (dep_id,ref_id) VALUES (102,2);
INSERT INTO dep_table (dep_id,ref_id) VALUES (103,2);
INSERT INTO dep_table (dep_id,ref_id) VALUES (104,3);
COMMIT;
----------------------------------------------------------------------------
--------
-- SAMPLE QUERIES (which don't do what I would like!):
DELETE FROM ref_table WHERE ref_id = 1 -- Ideally should sets the 'deleted'
flag and not remove the row. (works OK)
DELETE FROM ref_table WHERE ref_id = 5 -- Ideally should remove the row
completely. (Does not work OK)
----------------------------------------------------------------------------
--------
Thanks all,
Simon Kinsella
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Help with trigger that updates a row prior to a potentially aborted deletion?
@ 2006-03-01 14:43 Simon Kinsella <simon@bluefiresystems.co.uk>
0 siblings, 0 replies; 9+ messages in thread
From: Simon Kinsella @ 2006-03-01 14:43 UTC (permalink / raw)
To: pgsql-sql
Yes I originally started working on a function based approach like you
suggest, but realised it wouldn't cover the situation where the delete
operation is fired as a result of a CASCADE ON DELETE constraint from a
parent table, rather than as a manual query. I suppose I could ditch that
particular cascading contraint and replace it with a trigger function that
calls my custom delete function. Not sure if I like that though ;)
Thanks for your feedback,
Simon Kinsella
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Wednesday, March 01, 2006 2:31 PM
To: Simon Kinsella
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help with trigger that updates a row prior to a
potentially aborted deletion?
On Wed, 1 Mar 2006, Simon Kinsella wrote:
> Hi all,
>
> I have a situation where a DELETE operation may (correctly) fail due
> to a RESTRICT FK constraint. If so, I need to set a flag in the row
> indicating that it has been marked for deletion so that I can
> disregarded in subsequent queries.
>
> I'm trying to achieve this with a BEFORE DELETE trigger, which would
> set the 'marked_for_deletion' field to TRUE before attempting the delete
proper.
> Then if the DELETE fails the row would still be tagged and I'd be happy.
> Problem is, when the DELETE operation fails with a RESTRICT VIOLATION
> error the entire operation is rolled back, including the BEFORE
> triggers, leaving me back where I started.
>
> Is there anyway to get the DELETE operation, or more specifically the
> FK constraint, to fail silently, i.e. to skip over the failed
> operation and not throw an exception?
Not with the standard constraint trigger, no. In general, constraint checks
happen after the action and as such can't skip over an operation since it's
already happened.
You might be able to do this within a function however if you do the update
and then start an exeption checking block to do the delete.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-03-03 12:42 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-01 13:30 Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-01 14:30 ` Re: Help with trigger that updates a row prior to a potentially Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-03-01 18:23 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-01 20:41 ` Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-02 12:19 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-03 00:19 ` Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-03 12:42 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-03-01 14:19 Re: Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
2006-03-01 14:43 Re: Help with trigger that updates a row prior to a potentially aborted deletion? Simon Kinsella <simon@bluefiresystems.co.uk>
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