pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: [HACKERS] triggered data change violation
9+ messages / 5 participants
[nested] [flat]

* Re: [HACKERS] triggered data change violation
@ 2001-03-20 20:39  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Cedar Cox <cedarc@visionforisrael.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Tom Lane @ 2001-03-20 20:39 UTC (permalink / raw)
  To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql

Cedar Cox <cedarc@visionforisrael.com> writes:
> Added note:  The trigger is a BEFORE trigger.

AFAIK the "triggered data change" message comes out of the AFTER trigger
code.  You sure you don't have any AFTER triggers on the table?  Perhaps
ones added implicitly by a foreign-key constraint?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* triggered data change violation
@ 2001-03-20 21:13  Cedar Cox <cedarc@visionforisrael.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Cedar Cox @ 2001-03-20 21:13 UTC (permalink / raw)
  To: pgsql-hackers@postgresql.org; pgsql-sql


Added note:  The trigger is a BEFORE trigger.

---------- Forwarded message ----------
To: pgsql-interfaces@postgresql.org
Date: Tue, 20 Mar 2001 20:43:59 +0200 (IST)
Subject: triggered data change violation


ERROR:  triggered data change violation on relation "tblstsc2options"

What is this?  It doesn't happen unless I'm in a transaction.  I'm
INSERTing a record and then DELETEing it (in the same transaction) and on
delete I get this error.  If I commit and begin a new transaction before
the delete everything is fine.  Is it something my trigger causing?  I
don't have any UPDATE, INSERT, or DELETE statements in my trigger (and I
am returning old on delete).

Thanks,
-Cedar




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [HACKERS] triggered data change violation
@ 2001-03-20 21:13  Peter Eisentraut <peter_e@gmx.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 9+ messages in thread

From: Peter Eisentraut @ 2001-03-20 21:13 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Cedar Cox <cedarc@visionforisrael.com>; pgsql-hackers@postgresql.org; pgsql-sql

Tom Lane writes:

> Cedar Cox <cedarc@visionforisrael.com> writes:
> > Added note:  The trigger is a BEFORE trigger.
>
> AFAIK the "triggered data change" message comes out of the AFTER trigger
> code.  You sure you don't have any AFTER triggers on the table?  Perhaps
> ones added implicitly by a foreign-key constraint?

A "triggered data change violation" happens everytime you change twice
within a transaction a value (column) that is part of a foreign key
constraint (don't recall exactly which part).

This error shouldn't really happen, but I recall there were some
implementation and definition problems with deferred constraints.

...FAQ alert...

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [HACKERS] triggered data change violation
@ 2001-03-20 21:14  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Cedar Cox <cedarc@visionforisrael.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2001-03-20 21:14 UTC (permalink / raw)
  To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql

Cedar Cox <cedarc@visionforisrael.com> writes:
>> AFAIK the "triggered data change" message comes out of the AFTER trigger
>> code.  You sure you don't have any AFTER triggers on the table?  Perhaps
>> ones added implicitly by a foreign-key constraint?

> Not any that I wrote.  Ok, the table def is:

> CREATE TABLE tblStSC2Options (
>         SC2OptionID int4 NOT NULL,
>         SC2OptionName character varying(50) NOT NULL CHECK (SC2OptionName<>''),
>         SC2OptionValue float4 CHECK (SC2OptionValue>0),
>         SurID character varying(50) NOT NULL REFERENCES tblStSC2 ON UPDATE 
> CASCADE ON DELETE CASCADE,
                                               ^^^^^^^^^^^^^^^^^^^

Sure looks like a foreign key to me.  If you dump the table definition
with pg_dump you'll see some AFTER triggers.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Re: [HACKERS] triggered data change violation
@ 2001-03-20 21:15  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 0 replies; 9+ messages in thread

From: Bruce Momjian @ 2001-03-20 21:15 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Cedar Cox <cedarc@visionforisrael.com>; pgsql-hackers@postgresql.org; pgsql-sql

> A "triggered data change violation" happens everytime you change twice
> within a transaction a value (column) that is part of a foreign key
> constraint (don't recall exactly which part).
> 
> This error shouldn't really happen, but I recall there were some
> implementation and definition problems with deferred constraints.
> 
> ...FAQ alert...

Yes, I just got it in the TODO list a few weeks ago:

* INSERT & UPDATE/DELETE in transaction of primary key fails with 
  deferredTriggerGetPreviousEvent or "change violation" [foreign]

I personally think we could do better on the wording of that error
message, at least until we get it fixed.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [HACKERS] triggered data change violation
@ 2001-03-20 21:55  Cedar Cox <cedarc@visionforisrael.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 9+ messages in thread

From: Cedar Cox @ 2001-03-20 21:55 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql


On Tue, 20 Mar 2001, Tom Lane wrote:
> Cedar Cox <cedarc@visionforisrael.com> writes:
> > Added note:  The trigger is a BEFORE trigger.
> 
> AFAIK the "triggered data change" message comes out of the AFTER trigger
> code.  You sure you don't have any AFTER triggers on the table?  Perhaps
> ones added implicitly by a foreign-key constraint?

Not any that I wrote.  Ok, the table def is:

CREATE TABLE tblStSC2Options (
        SC2OptionID int4 NOT NULL,
        SC2OptionName character varying(50) NOT NULL CHECK (SC2OptionName<>''),
        SC2OptionValue float4 CHECK (SC2OptionValue>0),
        SurID character varying(50) NOT NULL REFERENCES tblStSC2 ON UPDATE 
CASCADE ON DELETE CASCADE,
        PRIMARY KEY (SC2OptionID)
);

And there is one other table, tblListRequestSentItems, which has a field:

        SC2OptionID int4 DEFAULT 0 NOT NULL REFERENCES tblStSC2Options,

Have I answered your question?  (I think so.)

-Cedar




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* triggered data change violation
@ 2002-01-12 13:41  Murray Prior Hobbs <murray@efone.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Murray Prior Hobbs @ 2002-01-12 13:41 UTC (permalink / raw)
  To: pgsql-sql


where on earth in the source do i find this error - i can only find it in a comment

am i blind or something!!

m

-- 
______________________________________________

Murray Prior Hobbs

eFone International Pty Ltd
http://efone.com
Phone: +61 3 9533 8188
Fax: +61 3 9533 8122
Mobile: +61 412 452 986





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: triggered data change violation
@ 2002-01-12 16:19  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Murray Prior Hobbs <murray@efone.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2002-01-12 16:19 UTC (permalink / raw)
  To: Murray Prior Hobbs <murray@efone.com>; +Cc: pgsql-sql

Murray Prior Hobbs <murray@efone.com> writes:
> where on earth in the source do i find this error -

If you're looking in development sources, you won't find it,
'cause it's gone.

It used to lurk in commands/trigger.c.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: triggered data change violation
@ 2002-01-12 22:54  Murray Prior Hobbs <murray@efone.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Murray Prior Hobbs @ 2002-01-12 22:54 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql


i know this is a nieve question

but if i get the following error "triggered data change violation in 
relation X"

and i grep through every single file in the source tree for even just 
the word "violation"

i expect to find it

i desire to find it though i don't need to find it

so any further help would be appreciated

m

ps i found it in the TODO docs - i read a lot of peoples banter on the 
subject of why it happens (and know that some might have been wrong in 
what causes it to happen - it seems only occures when the FK is composite)




Tom Lane wrote:

>Murray Prior Hobbs <murray@efone.com> writes:
>
>>where on earth in the source do i find this error -
>>
>
>If you're looking in development sources, you won't find it,
>'cause it's gone.
>
>It used to lurk in commands/trigger.c.
>
>			regards, tom lane
>






^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2002-01-12 22:54 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-20 21:13 triggered data change violation Cedar Cox <cedarc@visionforisrael.com>
2001-03-20 20:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-20 21:13   ` Peter Eisentraut <peter_e@gmx.net>
2001-03-20 21:15     ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-20 21:55   ` Cedar Cox <cedarc@visionforisrael.com>
2001-03-20 21:14     ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-12 13:41 triggered data change violation Murray Prior Hobbs <murray@efone.com>
2002-01-12 16:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-01-12 22:54   ` Murray Prior Hobbs <murray@efone.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