Re: Referential integrity vulnerability in 8.3.3

Yes it is. But it the way to break integrity cos rows from table2
still refer to deleted rows from table1. So it conflicts with ideology isn't it?

On Tue, Jul 15, 2008 at 4:00 PM, Richard Huxton <dev(at)archonet(dot)com> wrote:
> Sergey Konoplev wrote:
>>
>> There is an oddity (or a bug) in situation with returning null before
>> delete trigger and referential integrity in PG 8.3.3. I tryed to find
>> a solution in Google and PG documentation and have noticed nothing
>> useful.
>
> [snip]
>>
>> CREATE OR REPLACE FUNCTION tr_stop()
>> RETURNS trigger AS
>> $BODY$begin
>> return null;
>> end;$BODY$
>> LANGUAGE 'plpgsql' VOLATILE;
>>
>> CREATE TRIGGER tr_stop
>> BEFORE DELETE
>> ON table2
>> FOR EACH ROW
>> EXECUTE PROCEDURE tr_stop();
>
> [snip]
>>
>> Now comming to a head. As I supposed earlier, deletion from table1 has
>> to be prevented by referential integrity when the trigger prevents
>> deletion of refered row from table2. But it doesn't.
>
> [snip]
>>
>> Will you explain me please why PG behave so cos IMHO it's a bit
>> illogical. Thanx.
>
> Your trigger doesn't prevent deletion, it just skips the row(s) in question
> from being affected. Raise an exception if you want to abort the
> transaction.
>
> See the manual - triggers chapter and plpgsql chapter for more details.
>
> --
> Richard Huxton
> Archonet Ltd
>

--
Regards,
Sergey Konoplev