Thread: POSTGRES BUG - FIX IT PLEASE

I attach bug.template file with description of my problem.

Please fix it asap.


--
Keep in touch.
Sergey Mavrinsky.
Solvo Ltd.


If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports@postgresql.org.

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs@postgresql.org.

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches@postgresql.org instead.  Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        : Mavrinsky Sergey
Your email address    : mavr@solvo.ru


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : Intel Pentium 366Mz

  Operating System (example: Linux 2.0.26 ELF)     : RedHat 6.0

  PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.2

  Compiler used (example:  gcc 2.8.0)        : 2.91


Please enter a FULL description of your problem:
------------------------------------------------

I have found a following problem:

I have 2 tables

create table t1
(
    f1 integer,
    f2 integer
);

create table t2
(
    f1 integer references t1(f1),
    f2 integer
);

Then in a query:

begin transaction;
insert into t1(f1,f2) values(1,1);
delete from t1 where f1=1;

at that moment the following error occurs:

ERROR:  triggered data change violation on relation "t1"

by the way, when i create referentional constraint Postrgers server creates trigger...


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
create database db1;

create table t1
(
    f1 integer,
    f2 integer
);

create table t2
(
    f1 integer references t1(f1),
    f2 integer
);

begin transaction;
insert into t1(f1,f2) values(1,1);
delete from t1 where f1=1;
ERROR:  triggered data change violation on relation "t1"


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
 ?????????????

Sergey Mavrinsky writes:

> create table t1
> (
>         f1 integer,
>         f2 integer
> );
>
> create table t2
> (
>         f1 integer references t1(f1),
>         f2 integer
> );

> begin transaction;
> insert into t1(f1,f2) values(1,1);
> delete from t1 where f1=1;

> ERROR:  triggered data change violation on relation "t1"

You cannot change data twice within a transaction if there's a RI
constraint on the table.  This is per SQL, nothing we can do about it.

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

> > create table t1
> > (
> >         f1 integer,
> >         f2 integer
> > );
> > 
> > create table t2
> > (
> >         f1 integer references t1(f1),
> >         f2 integer
> > );
> 
> > begin transaction;
> > insert into t1(f1,f2) values(1,1);
> > delete from t1 where f1=1;
> 
> > ERROR:  triggered data change violation on relation "t1"
> 
> You cannot change data twice within a transaction if there's a RI
> constraint on the table.  This is per SQL, nothing we can do about it.                           ^^^^^^^^^^^^^^^
Is it true?! *Any reasons* for this? DELETE doesn't break integrity rules.
Just tested it in Oracle - deletion is allowed!
But yes, I know that Oracle doesn't always follow standards -:)
Can someone test it under Informix, others?

Vadim