Re: [Postgres-xc-developers] Fix for bug 3485997: UPDATE fails on table with foreign key

Hi Michael,

Just found the reason why referential integrity check does not work on
datanodes.
Here is the code in RI_FKey_Check (ri_triggers.c):

#ifdef PGXC
/*
 * XXX Referential integrity is not yet supported. We just come out of the
 * function without actually performing any integrity checks. This must be
 * fixed when we support local/global referential integrities.
 */
return PointerGetDatum(NULL);
#endif

I guess it will work if these lines will be removed.

23 марта 2012 г. 3:32 пользователь Michael Paquier <
mic...@gm...> написал:

>
>
> On Thu, Mar 22, 2012 at 5:50 PM, Andrei Martsinchyk <
> and...@gm...> wrote:
>
>> Hi Michael,
>>
>> So you find "A tuple cannot be fetched from Coordinator" error is better
>> then "could not read block 0 in file "base/12052/32771": read only 0 of
>> 8192 bytes" ?
>> I think both are equally misty for the end user.
>> I have reported the bug some time ago (ID: 3485997) and proposed a patch,
>> that fixes the problem.
>> The idea is to skip AFTER UPDATE/DELETE triggers if target relation is
>> remote. The triggers enforcing the foreign key are still fired on the
>> datanodes.
>> Is something wrong with my patch?
>>
> Your patch does not protect data consistency on foreign constraints, which
> is perhaps worse than the bug referenced.
> With this test case:
>
> create table x1 (id int, value timestamp);
> create table x2 (id int, value timestamp);
> alter table x1 add primary key (id);
> alter table x2 add foreign key (id) references x1(id);
> insert into x1 values (1, now());
> insert into x2 values (2, now());
>
> XC inserts the tuple.
>
> Postgres returns this error:
> ERROR:  insert or update on table "x2" violates foreign key constraint
> "x2_id_fkey"
> DETAIL:  Key (id)=(2) is not present in table "x1".
>
>
>> 22 марта 2012 г. 9:31 пользователь Michael Paquier <
>> mic...@gm...> написал:
>>
>> >
>> > Hi,
>> >
>> > Please find attached a patch that handles smoothly the following case:
>> > postgres=# create table x1 (id int, value timestamp);
>> > CREATE TABLE
>> > postgres=# create table x2 (id int, value timestamp);
>> > CREATE TABLE
>> > postgres=# alter table x1 add primary key (id);
>> > NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
>> "x1_pkey" for table "x1"
>> > ALTER TABLE
>> > postgres=# alter table x2 add foreign key (id) references x1(id);
>> > ALTER TABLE
>> > postgres=# insert into x1 values (1, now());
>> > INSERT 0 1
>> > postgres=# insert into x2 values (1, now());
>> > INSERT 0 1
>> > postgres=# update x1 set value=now() where id=1;
>> > ERROR: could not read block 0 in file "base/12052/32768": read only 0
>> of 8192 bytes
>> > postgres=# update x2 set value=now() where id=1;
>> > ERROR: could not read block 0 in file "base/12052/32771": read only 0
>> of 8192 bytes
>> >
>> > What happens here is that the update query tries to fetch a tuple on
>> Coordinator to check foreign key consistency by using a trigger mechanism.As
>> > As we do not target that in 1.0, I propose simply to block such cases
>> with the patch attached.
>> > Opinions?
>> > --
>> > Michael Paquier
>> > http://michael.otacoo.com
>> >
>> >
>> ------------------------------------------------------------------------------
>> > This SF email is sponsosred by:
>> > Try Windows Azure free for 90 days Click Here
>> > http://p.sf.net/sfu/sfd2d-msazure
>> > _______________________________________________
>> > Postgres-xc-developers mailing list
>> > Pos...@li...
>> > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> >
>>
>>
>>
>> --
>> Best regards,
>> Andrei Martsinchyk                   mailto:and...@gm...
>>
>
>
>
> --
> Michael Paquier
> http://michael.otacoo.com
>



-- 
Best regards,
Andrei Martsinchyk                   mailto:and...@gm...