Re: order of rows in update

Στις Tuesday 02 September 2008 17:24:05 ο/η Richard Broersma έγραψε:
> On Tue, Sep 2, 2008 at 2:58 AM, Achilleas Mantzios
> <achill(at)matrix(dot)gatewaynet(dot)com> wrote:
> > is there an (implicit) way to make a multirow update execute on some rows prior to other rows?
> > It is needed in a case where a trigger is defined on the table as FOR EACH ROW, and it is mandatory
> > that the trigger is run for some certain rows before it is run on the rest of the rows.
> >
> > Is there anything reliable to achieve this without making poor assumptions of the future
> > versions, or should i just "SELECT ... ORDER BY ..." and then perform individual UPDATEs?
>
> The only way that I know how to do this is to create a named cursor of
> the rows that you want to update, and then for each record call
>
> UPDATE ... FROM ... WHERE CURRENT OF cursorname;
aha Thanks.
>
>
> But why are you even having this problem to begin with? What you are
> describing sounds like a database normalization problem.
>
I am using my version of DB mirror to do some kind of "Conditional row grained + FK dependency oriented lazy replication".
(The logic behind it is the cost of comms, because the slaves are servers in vessels in all 7 seas, where communication is done
via uucp connections over satellite dilaup, and the costs are really high, so the aim was to minimize the costs.
Regarding high costs, It was so in 2003/2004 when we started designing/deploying this system and the prices are still high
today.)

I have divided my tables into the following categories:
1) never replicated
2) unconditionally replicated to all slaves
3) explicitly conditionally replicated to a *certain* slave and only to this slave, based on the value of one column (smth like "vslid", where vessels
denote my slaves)
4) implicitly replicated to slaves, that is they are replicated to some slave *only* if they act as a parent (foreign) table in FK constraint
of some child table which is either case 3) or case 4)
So what i basically do is a depth first search of the Graph, denoted by the FK constraints.

For simplicitly, at some point, in the code i have to find out if some parent table has to be part of the graph search.
If that table belongs to case 3), i simply skip this "node", knowing it will be replicated because it is defined as such.
The problem arises when a table has a FK to itself, then i have to make sure that some rows will be mirrored before other rows.
I could rectify the code to deal correctly with cases like that, but it would add disproportinal complexity
in comparison to the problem it solves.

Thats why i want to force some rows to be updated before other rows, so that the dbmirror trigger will be called first.
>
> --
> Regards,
> Richard Broersma Jr.
>
> Visit the Los Angeles PostgreSQL Users Group (LAPUG)
> http://pugs.postgresql.org/lapug
>

--
Achilleas Mantzios