Re: Delete triggers order in delete cascade (pg 8.3.7).

On 2009-07-02, Michaël Lemaire <adminsys(at)rodacom(dot)fr> wrote:
> Richard Huxton <dev(at)archonet(dot)com> wrote:
>
>> Michaël Lemaire wrote:
>> > Hi all.
>> >
>> > I've come across a problem with delete cascade.
>> >
>> > I have three tables A, B and C.
>> > Table B has a foreign key on A with delete cascade.
>> > Table C has a foreign key on B with delete cascade.
>> > So, we have this reference chain: C->B->A
>> > All three tables have an 'on delete' trigger.
>> >
>> > My problem is, when I delete a row from A, the delete triggers are
>> > fired in the order A then B then C, which is the opposite of what I
>> > expected (the row from B should be deleted before the A one, or the
>> > reference constraint would break).
>>
>> The "on delete cascade" are (sort of) implemented with system
>> triggers. So deleting a row from A triggers a delete on B where
>> fkey=X and so on.
>>
>> > This happens with 'after' and 'before' triggers.
>> > I really need the order to be C then B then A.
>>
>> Why? What are you trying to do?
>
> The delete triggers add 'command' rows in another table to notify
> another server of data changes (kind of a replication system but with
> data convertion).
>
> This other server's database doesn't have delete cascades (I can't
> change this for compatibility with other scripts). So delete commands
> must be issued in an order that don't break foreign keys.

they come out backwards, live with it.

when you select from the command table do

order by timestamp_column ascending sequence_column descending

and they'll magically come out in the "right" order.