On 2009-07-02, Michaël Lemaire <adminsys@xxxxxxxxxx> wrote: > Richard Huxton <dev@xxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
