Re: Updating from update trigger

Hello,

I'm writing a program with which I can keep track of all the CD's I
have. As a backend, I'm using Postgres to store my data. Now, I have
this trigger on a table (a scheme of the database at the bottom) which
executes a function called movecdsdown. From this function, the same
table is updated. This gives no problems when the trigger is called
because of an insert, and it does what it should do. However, I also
want to update the table when that table is updated. This of course
gives me problems, cause it will begin an endless loop. So, somehow I
need to disable the trigger, or the function must now if the function
was already called, but I have no idea on how to do this.

Here is a scheme of the database I'm using:

cd location locationdetails rental
renter

title (pk) location (pk) fk_cd (pk)
startdate (pk) firstname (pk)
creationdate width fk_location (pk)
followupnr (pk) lastname (pk)
type height part fk_cd
phonenumber
fk_storedloc parts column fk_firstname
email
fk_currentloc fitshalfsize row fk_lastname
size stack
enddate
homelocation

I think the foreign and primary keys speak for themselves. The trigger
is defined on the locationdetails table. The trigger function then
updates the row field of all rows where row > NEW.row. It works
perfectly for INSERT, but it doesn't work for UPDATE.

Greetings,

Jonathan Mezach

Is there anyone who has a solution to this problem?

Greetings,

Jonathan Mezach aka Taz
News Writer for HL2Zone (www.hl2zone.net)

-----Oorspronkelijk bericht-----
Van: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] Namens Jonathan Mezach
Verzonden: vrijdag 25 juli 2003 21:07
Aan: pgsql-general(at)postgresql(dot)org
Onderwerp: [GENERAL] Updating from update trigger

Hello,

I'm writing a program with which I can keep track of all the CD's I
have. As a backend, I'm using Postgres to store my data. Now, I have
this trigger on a table (a scheme of the database at the bottom) which
executes a function called movecdsdown. From this function, the same
table is updated. This gives no problems when the trigger is called
because of an insert, and it does what it should do. However, I also
want to update the table when that table is updated. This of course
gives me problems, cause it will begin an endless loop. So, somehow I
need to disable the trigger, or the function must now if the function
was already called, but I have no idea on how to do this.

Here is a scheme of the database I'm using:

cd location locationdetails rental
renter

title (pk) location (pk) fk_cd (pk)
startdate (pk) firstname (pk)
creationdate width fk_location (pk)
followupnr (pk) lastname (pk)
type height part fk_cd
phonenumber
fk_storedloc parts column fk_firstname
email
fk_currentloc fitshalfsize row fk_lastname
size stack
enddate
homelocation

I think the foreign and primary keys speak for themselves. The trigger
is defined on the locationdetails table. The trigger function then
updates the row field of all rows where row > NEW.row. It works
perfectly for INSERT, but it doesn't work for UPDATE.

Greetings,

Jonathan Mezach

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

You should probably rethink the application logic.
If there's no way to do it differently, you might be able to place the
location details in a separate table, and update them there - this way
on update of the "cd" table you will update also the "cd_locations"
table, avoiding the loop.
You should join the 2 tables using a sequence generated key, which
should be never changed once allocated to a cd/location pair.

In any case this trigger approach will not work correctly in multi-user
environments, unless you implement some kind of locking for the
insert/update access to the cd table. In the best case you will get
rolled back transactions, worst case - the locations get f**ed up.

HTH,
Csaba.

On Thu, 2003-07-31 at 12:46, Jonathan Mezach wrote:
> Is there anyone who has a solution to this problem?
>
> Greetings,
>
> Jonathan Mezach aka Taz
> News Writer for HL2Zone (www.hl2zone.net)
>
>
> -----Oorspronkelijk bericht-----
> Van: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] Namens Jonathan Mezach
> Verzonden: vrijdag 25 juli 2003 21:07
> Aan: pgsql-general(at)postgresql(dot)org
> Onderwerp: [GENERAL] Updating from update trigger
>
> Hello,
>
> I'm writing a program with which I can keep track of all the CD's I
> have. As a backend, I'm using Postgres to store my data. Now, I have
> this trigger on a table (a scheme of the database at the bottom) which
> executes a function called movecdsdown. From this function, the same
> table is updated. This gives no problems when the trigger is called
> because of an insert, and it does what it should do. However, I also
> want to update the table when that table is updated. This of course
> gives me problems, cause it will begin an endless loop. So, somehow I
> need to disable the trigger, or the function must now if the function
> was already called, but I have no idea on how to do this.
>
> Here is a scheme of the database I'm using:
>
> cd location locationdetails rental
> renter
>
> title (pk) location (pk) fk_cd (pk)
> startdate (pk) firstname (pk)
> creationdate width fk_location (pk)
> followupnr (pk) lastname (pk)
> type height part fk_cd
> phonenumber
> fk_storedloc parts column fk_firstname
> email
> fk_currentloc fitshalfsize row fk_lastname
> size stack
> enddate
> homelocation
>
> I think the foreign and primary keys speak for themselves. The trigger
> is defined on the locationdetails table. The trigger function then
> updates the row field of all rows where row > NEW.row. It works
> perfectly for INSERT, but it doesn't work for UPDATE.
>
> Greetings,
>
> Jonathan Mezach
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Answering my own half-way thought solution: of course you won't avoid
the infinite loop if you take the location info in a separate table,
cause if you do, then you can't update the location by just updating the
cd table.
So having this solution invalid, I just can think of using some query
rewriting rules.
I have absolutely no experience with this, so check out the docs:
http://www.postgresql.org/docs/7.3/static/rules.html

HTH,
Csaba.

On Thu, 2003-07-31 at 14:26, Csaba Nagy wrote:
> You should probably rethink the application logic.
> If there's no way to do it differently, you might be able to place the
> location details in a separate table, and update them there - this way
> on update of the "cd" table you will update also the "cd_locations"
> table, avoiding the loop.
> You should join the 2 tables using a sequence generated key, which
> should be never changed once allocated to a cd/location pair.
>
> In any case this trigger approach will not work correctly in multi-user
> environments, unless you implement some kind of locking for the
> insert/update access to the cd table. In the best case you will get
> rolled back transactions, worst case - the locations get f**ed up.
>
> HTH,
> Csaba.
>
> On Thu, 2003-07-31 at 12:46, Jonathan Mezach wrote:
> > Is there anyone who has a solution to this problem?
> >
> > Greetings,
> >
> > Jonathan Mezach aka Taz
> > News Writer for HL2Zone (www.hl2zone.net)
> >
> >
> > -----Oorspronkelijk bericht-----
> > Van: pgsql-general-owner(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)org] Namens Jonathan Mezach
> > Verzonden: vrijdag 25 juli 2003 21:07
> > Aan: pgsql-general(at)postgresql(dot)org
> > Onderwerp: [GENERAL] Updating from update trigger
> >
> > Hello,
> >
> > I'm writing a program with which I can keep track of all the CD's I
> > have. As a backend, I'm using Postgres to store my data. Now, I have
> > this trigger on a table (a scheme of the database at the bottom) which
> > executes a function called movecdsdown. From this function, the same
> > table is updated. This gives no problems when the trigger is called
> > because of an insert, and it does what it should do. However, I also
> > want to update the table when that table is updated. This of course
> > gives me problems, cause it will begin an endless loop. So, somehow I
> > need to disable the trigger, or the function must now if the function
> > was already called, but I have no idea on how to do this.
> >
> > Here is a scheme of the database I'm using:
> >
> > cd location locationdetails rental
> > renter
> >
> > title (pk) location (pk) fk_cd (pk)
> > startdate (pk) firstname (pk)
> > creationdate width fk_location (pk)
> > followupnr (pk) lastname (pk)
> > type height part fk_cd
> > phonenumber
> > fk_storedloc parts column fk_firstname
> > email
> > fk_currentloc fitshalfsize row fk_lastname
> > size stack
> > enddate
> > homelocation
> >
> > I think the foreign and primary keys speak for themselves. The trigger
> > is defined on the locationdetails table. The trigger function then
> > updates the row field of all rows where row > NEW.row. It works
> > perfectly for INSERT, but it doesn't work for UPDATE.
> >
> > Greetings,
> >
> > Jonathan Mezach
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>