Thread
-
views/stored procedures
Jeff Davis <jdavis@wasabimg.com> — 2000-11-13T06:29:29Z
The previous thread regarding stored procedures made me wonder: what is the difference between a stored procedure and a view? I understand postgres uses query rewriting for views, is there a performance hit with that compared to stored procedures? Will postgres be adding stored procedures? Thanks, Jeff Davis [PS: I tried sending a similar message to the list yesterday evening, but it never appeared. I apologize if this is a re-post]
-
Sequences in transaction
igor <igor_kh@mailru.com> — 2000-11-13T11:17:42Z
Hi, Help me please to understand such a thing: Rollback of sequences doesn't work in transaction. Is it right? Or may be there is another way to restore it's value? Running PG 7.02 on RH 6.0 Thanks , Igor.
-
Re: Sequences in transaction
Camm Maguire <camm@enhanced.com> — 2000-12-04T22:56:04Z
Greetings! I've run into this too, and it appears to me not to roll back either. A pity, as it forced us to make a separate table to hold the last sequence value and update the table in a trigger. Take care, igor <igor_kh@mailru.com> writes: > Hi, > > Help me please to understand such a thing: > Rollback of sequences doesn't work in transaction. > Is it right? Or may be there is another way to restore > it's value? > Running PG 7.02 on RH 6.0 > > Thanks , > Igor. > > > > -- Camm Maguire camm@enhanced.com ========================================================================== "The earth is but one country, and mankind its citizens." -- Baha'u'llah
-
Re: Sequences in transaction
Mike Castle <dalgoda@ix.netcom.com> — 2000-12-04T23:29:43Z
On Mon, Dec 04, 2000 at 05:56:04PM -0500, Camm Maguire wrote: > Greetings! I've run into this too, and it appears to me not to roll > back either. A pity, as it forced us to make a separate table to hold > the last sequence value and update the table in a trigger. This is a dangerous thing if you ever have more than one update going on. One transaction reads the value, increments it, saves it back. Another transaction does the same thing. Now you have two transactions trying to use the same value. Sure, one will probably fail a uniqueness constraint, but then you have to increment again. Meanwhile, another transaction comes in and steals the next number, and one of the first two clashes again. And again. And again. And again. You have NO way of guaranteeing that starvation will never be an issue. Solution: Don't require that your sequence values be absolutely sequential. They're there to ensure uniquness and order. Not to be used as counters. mrc -- Mike Castle Life is like a clock: You can work constantly dalgoda@ix.netcom.com and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen -
Re: Sequences in transaction
Camm Maguire <camm@enhanced.com> — 2000-12-05T17:03:40Z
Greetings, and thank you for your insightful reply. I've been reading the faq's on this, and am trying to figure out the best way to do this within the design of postgresql. Problem is, I need to be able to *quickly* select a pair of *adjacent* rows in a table. t2.seq = t1.seq + 1 seems to work pretty well. Of course, I could instead add a 'backward seq value' column to the table, and add in an extra merge. In other words, my query currently looks like select dates.asof,t1.a + t2.b from dates, data t1, data t2 where t1.seq = dates.seq and t1.id = t2.id and t2.seq = t1.seq + 1 seq being a primary key in dates, and a foreign key in data. So is this better: select dates.asof,t1.a + t2.b from dates, data t1, data t2 where t1.seq = dates.seq and t1.id = t2.id and t2.seq = dates.nseq and add a trigger to dates to update nseq on insert,update and delete? Thanks! Mike Castle <dalgoda@ix.netcom.com> writes: > On Mon, Dec 04, 2000 at 05:56:04PM -0500, Camm Maguire wrote: > > Greetings! I've run into this too, and it appears to me not to roll > > back either. A pity, as it forced us to make a separate table to hold > > the last sequence value and update the table in a trigger. > > This is a dangerous thing if you ever have more than one update going on. > > One transaction reads the value, increments it, saves it back. Another > transaction does the same thing. Now you have two transactions trying to > use the same value. Sure, one will probably fail a uniqueness constraint, > but then you have to increment again. Meanwhile, another transaction comes > in and steals the next number, and one of the first two clashes again. And > again. And again. And again. You have NO way of guaranteeing that > starvation will never be an issue. > > Solution: > > Don't require that your sequence values be absolutely sequential. They're > there to ensure uniquness and order. Not to be used as counters. > > mrc > -- > Mike Castle Life is like a clock: You can work constantly > dalgoda@ix.netcom.com and be right all the time, or not work at all > www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc > We are all of us living in the shadow of Manhattan. -- Watchmen > > -- Camm Maguire camm@enhanced.com ========================================================================== "The earth is but one country, and mankind its citizens." -- Baha'u'llah
-
Re: Sequences in transaction
Mike Castle <dalgoda@ix.netcom.com> — 2000-12-05T18:01:38Z
On Tue, Dec 05, 2000 at 12:03:40PM -0500, Camm Maguire wrote: > need to be able to *quickly* select a pair of *adjacent* rows in a > table. t2.seq = t1.seq + 1 seems to work pretty well. Of course, I What's wrong with a select ... order by .. limit 2 ? mrc -- Mike Castle Life is like a clock: You can work constantly dalgoda@ix.netcom.com and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen -
Re: Sequences in transaction
Camm Maguire <camm@enhanced.com> — 2000-12-11T16:14:42Z
Greetings! I've just found .. nothing! This works pretty well to my surprise. Thanks so much for the suggestion. I did a little rewrite which builds a doubly-linked list table of dates, with prior date and next date columns maintained by triggers. I then retrieve adjacent pairs of data table rows via a merge with this table. This appears to be faster than issuing a subselect .... order by ... limit 1 for each data row, but your key idea (to me, at least) is that I can avoid sequential sequence numbers by making explicit reference to the order of the date values themselves. Thanks again! Mike Castle <dalgoda@ix.netcom.com> writes: > On Tue, Dec 05, 2000 at 12:03:40PM -0500, Camm Maguire wrote: > > need to be able to *quickly* select a pair of *adjacent* rows in a > > table. t2.seq = t1.seq + 1 seems to work pretty well. Of course, I > > What's wrong with a select ... order by .. limit 2 ? > > mrc > -- > Mike Castle Life is like a clock: You can work constantly > dalgoda@ix.netcom.com and be right all the time, or not work at all > www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc > We are all of us living in the shadow of Manhattan. -- Watchmen > > -- Camm Maguire camm@enhanced.com ========================================================================== "The earth is but one country, and mankind its citizens." -- Baha'u'llah