Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
On Fri, Mar 03, 2006 at 12:19:22AM -, 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 | [EMAIL PROTECTED] 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 ---(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] Help with trigger that updates a row prior to a potentially aborted deletion?
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 -, 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 | [EMAIL PROTECTED] 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 ---(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
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
On Wed, Mar 01, 2006 at 08:41:20PM -, 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 | [EMAIL PROTECTED] 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
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 -, 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 | [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
On Wed, Mar 01, 2006 at 01:30:23PM -, 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 | [EMAIL PROTECTED] 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
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
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:[EMAIL PROTECTED] 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. ---(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
Re: [SQL] Help with trigger that updates a row prior to a potentially
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. ---(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] Help with trigger that updates a row prior to a potentially aborted deletion?
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
