Thread: order of rows in update
Hi, 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? Thanx -- Achilleas Mantzios
On Tue, Sep 2, 2008 at 2:58 AM, Achilleas Mantzios <achill@matrix.gatewaynet.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; But why are you even having this problem to begin with? What you are describing sounds like a database normalization problem. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Στις Tuesday 02 September 2008 17:24:05 ο/η Richard Broersma έγραψε: > On Tue, Sep 2, 2008 at 2:58 AM, Achilleas Mantzios > <achill@matrix.gatewaynet.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 isdone 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 (smthlike "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) tablein 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 otherrows. 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
On Tue, Sep 2, 2008 at 11:56 PM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote: > 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 communicationis 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.) If you are ever in LA, you should come to a LAPUG meeting. One of our members also replicates over long distances. He is replicating from California to China using a highly configured slony in combination with other software. His software analyzes the possible routes he has to find the routes with the best latency. He has configures a revolving Master-Slave replication. Perhaps you guys to share notes? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Στις Wednesday 03 September 2008 15:28:04 ο/η Richard Broersma έγραψε: > On Tue, Sep 2, 2008 at 11:56 PM, Achilleas Mantzios > <achill@matrix.gatewaynet.com> wrote: > > 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 communicationis 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 stillhigh > > today.) > > If you are ever in LA, you should come to a LAPUG meeting. One of our > members also replicates over long distances. He is replicating from > California to China using a highly configured slony in combination > with other software. His software analyzes the possible routes he has > to find the routes with the best latency. He has configures a > revolving Master-Slave replication. Thanx for your help. I live in Greece, but it would be nice some time to visit America/LA :) > > Perhaps you guys to share notes? > > > -- > Regards, > Richard Broersma Jr. > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > http://pugs.postgresql.org/lapug > -- Achilleas Mantzios