From gtm@oracom.com Thu Dec 20 13:27:56 2001 Received: from degroens.oracom.com (degroens.oracom.com [63.238.138.68]) by postgresql.org (8.11.3/8.11.4) with SMTP id fBKDRum96671 for ; Thu, 20 Dec 2001 08:27:56 -0500 (EST) (envelope-from gtm@oracom.com) Received: (qmail 22909 invoked from network); 20 Dec 2001 06:26:04 -0000 Received: from unknown (HELO catamount) (192.168.253.253) by 192.168.253.2 with SMTP; 20 Dec 2001 06:26:04 -0000 Message-ID: <00ad01c1895a$c3d5df80$4d00a8c0@catamount> From: "Glenn MacGregor" To: Subject: Transaction and cascade problem Date: Thu, 20 Dec 2001 08:32:26 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00AA_01C18930.DAF83660" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200112/214 X-Sequence-Number: 6037 This is a multi-part message in MIME format. ------=_NextPart_000_00AA_01C18930.DAF83660 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, I have postgres 7.1 set up with two tables (groups, users). groups has 2 c= olumns name, groupid where id is the primary key autoincrement and name is = unique. users has 3 columns name, userid, groupid. I have a constraint on= users which says that groupid must exists in the group table. I also have= a cascade delete on the groups table to delete any users that are in the g= roup I am removing. I regular (no transaction) mode everything works fine.= When I start a transaction and I add a group to the group table, then del= ete it before a commit or rollback I get the following error: ERROR: triggered data change violation on relation "groups" After that the transaction must be rolledback. Any clues? Thanks Glenn ------=_NextPart_000_00AA_01C18930.DAF83660 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi All,
 
I have postgres 7.1 set up with two tables= (groups,=20 users).  groups has 2 columns name, groupid where id is the prima= ry=20 key autoincrement and name is unique.  users has 3 columns name,= =20 userid, groupid.  I have a constraint on users which says that groupid= must=20 exists in the group table.  I also have a cascade delete on the groups= =20 table to delete any users that are in the group I am removing.  I regu= lar=20 (no transaction) mode everything works fine.  When I start a transacti= on=20 and I add a group to the group table, then delete it before a commit or rol= lback=20 I get the following error:
 
ERROR: triggered data change violation on = relation=20 "groups"
 
After that the transaction must be=20 rolledback.  Any clues?
 
    Thanks
 
       =20        =20 Glenn
------=_NextPart_000_00AA_01C18930.DAF83660-- From gtm@oracom.com Thu Dec 20 13:43:26 2001 Received: from degroens.oracom.com (degroens.oracom.com [63.238.138.68]) by postgresql.org (8.11.3/8.11.4) with SMTP id fBKDhQm97511 for ; Thu, 20 Dec 2001 08:43:26 -0500 (EST) (envelope-from gtm@oracom.com) Received: (qmail 23107 invoked from network); 20 Dec 2001 06:41:19 -0000 Received: from unknown (HELO catamount) (192.168.253.253) by 192.168.253.2 with SMTP; 20 Dec 2001 06:41:19 -0000 Message-ID: <00e401c1895c$ee8e8a40$4d00a8c0@catamount> From: "Glenn MacGregor" To: Subject: Transaction and cascade problem Date: Thu, 20 Dec 2001 08:47:57 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00E1_01C18933.05B267C0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200112/216 X-Sequence-Number: 6038 This is a multi-part message in MIME format. ------=_NextPart_000_00E1_01C18933.05B267C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, I have postgres 7.1 set up with two tables (groups, users). groups has 2 c= olumns name, groupid where id is the primary key autoincrement and name is = unique. users has 3 columns name, userid, groupid. I have a constraint on= users which syas that groupid must exists in the group table. I also have= a cascade delete on the groups table to delete any users that are in the g= roup I am removing. I regular (no transaction) mode everything works fine.= When I start a transaction and I add a group to the group table, then del= ete it before a commit or rollback I get the following error: ERROR: triggered data change violation on relation "groups" After that the transaction must be rolledback. Any clues? Thanks Glenn ------=_NextPart_000_00E1_01C18933.05B267C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi All,
 
I have postgres 7.1 set up with two tables= (groups,=20 users).  groups has 2 columns name, groupid where id is the prima= ry=20 key autoincrement and name is unique.  users has 3 columns name,= =20 userid, groupid.  I have a constraint on users which syas that groupid= must=20 exists in the group table.  I also have a cascade delete on the groups= =20 table to delete any users that are in the group I am removing.  I regu= lar=20 (no transaction) mode everything works fine.  When I start a transacti= on=20 and I add a group to the group table, then delete it before a commit or rol= lback=20 I get the following error:
 
ERROR: triggered data change violation on = relation=20 "groups"
 
After that the transaction must be=20 rolledback.  Any clues?
 
    Thanks
 
       =20        =20 Glenn
------=_NextPart_000_00E1_01C18933.05B267C0-- From josh@agliodbs.com Thu Dec 20 16:08:12 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBKG8Cm44197 for ; Thu, 20 Dec 2001 11:08:12 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.7) with HTTP id 532942; Thu, 20 Dec 2001 08:08:14 -0800 From: "Josh Berkus" Subject: Re: Transaction and cascade problem To: "Glenn MacGregor" , X-Mailer: CommuniGate Pro Web Mailer v.3.4.7 Date: Thu, 20 Dec 2001 08:08:14 -0800 Message-ID: In-Reply-To: <00ad01c1895a$c3d5df80$4d00a8c0@catamount> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200112/219 X-Sequence-Number: 6041 Glenn, > I have postgres 7.1 set up with two tables (groups, users). groups > has 2 columns name, groupid where id is the primary key autoincrement > and name is unique. users has 3 columns name, userid, groupid. I > have a constraint on users which says that groupid must exists in the > group table. I also have a cascade delete on the groups table to > delete any users that are in the group I am removing. I regular (no > transaction) mode everything works fine. When I start a transaction > and I add a group to the group table, then delete it before a commit > or rollback I get the following error: > > ERROR: triggered data change violation on relation "groups" Yes. For some technical reason ( I'm not clear on this ) you cannot both add and delete the same row within a PostgreSQL transaction. This is a known issue in 7.1.x; I do not know if it is fixed in 7.2. In the couple of functions where I need to add rows, then drop them, I have been taking the following steps: 1. Add new rows 2. When I'm done with the rows, flag them as invalid. 3. Put SQL in another, regularly-running (1/hour) function that deletes all invalid-flagged rows. Sort of a messy workaround, I know, but there it is. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco From tgl@sss.pgh.pa.us Thu Dec 20 17:09:19 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBKH9Jm67357 for ; Thu, 20 Dec 2001 12:09:19 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fBKH9Bi09264; Thu, 20 Dec 2001 12:09:11 -0500 (EST) To: "Josh Berkus" cc: "Glenn MacGregor" , pgsql-sql@postgresql.org Subject: Re: Transaction and cascade problem In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Thu, 20 Dec 2001 08:08:14 -0800" Date: Thu, 20 Dec 2001 12:09:11 -0500 Message-ID: <9261.1008868151@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200112/220 X-Sequence-Number: 6042 "Josh Berkus" writes: >> ERROR: triggered data change violation on relation "groups" > Yes. For some technical reason ( I'm not clear on this ) you cannot > both add and delete the same row within a PostgreSQL transaction. This > is a known issue in 7.1.x; I do not know if it is fixed in 7.2. Actually, we've just removed that error check in 7.2. The discussion concluded that we'd misinterpreted the spec in treating this condition as an error. You can simply dike out the error call (it's in src/backend/commands/trigger.c, IIRC) if it's getting in your way in 7.1. regards, tom lane From chriskl@familyhealth.com.au Fri Dec 21 02:59:28 2001 Received: from mail.iinet.net.au (symphony-03.iinet.net.au [203.59.3.35]) by postgresql.org (8.11.3/8.11.4) with SMTP id fBL2xSm77132 for ; Thu, 20 Dec 2001 21:59:28 -0500 (EST) (envelope-from chriskl@familyhealth.com.au) Received: (qmail 1412 invoked by uid 666); 21 Dec 2001 02:57:08 -0000 Received: from unknown (HELO houston.familyhealth.com.au) (203.59.231.6) by mail.iinet.net.au with SMTP; 21 Dec 2001 02:57:08 -0000 Received: (from root@localhost) by houston.familyhealth.com.au (8.11.6/8.11.6) id fBL2v7L15053 for pgsql-sql@postgresql.org; Fri, 21 Dec 2001 10:57:07 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Received: from mariner (mariner.internal [192.168.0.101]) by houston.familyhealth.com.au (8.11.6/8.9.3) with SMTP id fBL2v4p14853; Fri, 21 Dec 2001 10:57:04 +0800 (WST) From: "Christopher Kings-Lynne" To: "Josh Berkus" , "Glenn MacGregor" , Subject: Re: Transaction and cascade problem Date: Fri, 21 Dec 2001 10:57:55 +0800 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal In-Reply-To: X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/) X-Archive-Number: 200112/224 X-Sequence-Number: 6046 > > ERROR: triggered data change violation on relation "groups" > > Yes. For some technical reason ( I'm not clear on this ) you cannot > both add and delete the same row within a PostgreSQL transaction. This > is a known issue in 7.1.x; I do not know if it is fixed in 7.2. Hmmm...I seem to recall that it is fixed in 7.2. Correct me if I'm wrong. Chris From SHenshall@westcountrypublications.co.uk Fri Dec 21 14:15:41 2001 Received: from mail_exchange.westcountrypublications.co.uk ([195.171.163.135]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBLEFfm58941 for ; Fri, 21 Dec 2001 09:15:41 -0500 (EST) (envelope-from SHenshall@westcountrypublications.co.uk) Received: by MAIL_EXCHANGE with Internet Mail Service (5.5.2650.21) id ; Fri, 21 Dec 2001 14:12:28 -0000 Message-ID: From: "Henshall, Stuart - WCP" To: "'Glenn MacGregor'" , pgsql-sql@postgresql.org Subject: Re: Transaction and cascade problem Date: Fri, 21 Dec 2001 14:12:27 -0000 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C18A29.854F8D40" X-Archive-Number: 200112/231 X-Sequence-Number: 6053 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C18A29.854F8D40 Content-Type: text/plain; charset="iso-8859-1" IIRC this was for some perceived problem with foreign keys, however on hackers a while back it was talked about and I believe decided that it wasn't necessary after all and has been taken out. You might have more luck therefore with 7.2, or altering 7.1 to take this out (however I'm not 100% sure on this so check the hackers archives). - Stuart -----Original Message----- From: Glenn MacGregor [mailto:gtm@oracom.com] Sent: 20 December 2001 13:32 To: pgsql-sql@postgresql.org Subject: Transaction and cascade problem Hi All, I have postgres 7.1 set up with two tables (groups, users). groups has 2 columns name, groupid where id is the primary key autoincrement and name is unique. users has 3 columns name, userid, groupid. I have a constraint on users which says that groupid must exists in the group table. I also have a cascade delete on the groups table to delete any users that are in the group I am removing. I regular (no transaction) mode everything works fine. When I start a transaction and I add a group to the group table, then delete it before a commit or rollback I get the following error: ERROR: triggered data change violation on relation "groups" After that the transaction must be rolledback. Any clues? Thanks Glenn ------_=_NextPart_001_01C18A29.854F8D40 Content-Type: text/html; charset="iso-8859-1"
IIRC this was for some perceived problem with foreign keys, however on hackers a while back it was talked about and I believe decided that it wasn't necessary after all and has been taken out. You might have more luck therefore with 7.2, or altering 7.1 to take this out (however I'm not 100% sure on this so check the hackers archives).
- Stuart
-----Original Message-----
From: Glenn MacGregor [mailto:gtm@oracom.com]
Sent: 20 December 2001 13:32
To: pgsql-sql@postgresql.org
Subject: Transaction and cascade problem

Hi All,
 
I have postgres 7.1 set up with two tables (groups, users).  groups has 2 columns name, groupid where id is the primary key autoincrement and name is unique.  users has 3 columns name, userid, groupid.  I have a constraint on users which says that groupid must exists in the group table.  I also have a cascade delete on the groups table to delete any users that are in the group I am removing.  I regular (no transaction) mode everything works fine.  When I start a transaction and I add a group to the group table, then delete it before a commit or rollback I get the following error:
 
ERROR: triggered data change violation on relation "groups"
 
After that the transaction must be rolledback.  Any clues?
 
    Thanks
 
                Glenn
------_=_NextPart_001_01C18A29.854F8D40--