Thread: start of transaction (was: Re: [PERFORM] Help with count(*))
Tom Lane <tgl@sss.pgh.pa.us> writes: > That's defensible when the user issued the BEGIN himself. When the > BEGIN is coming from some interface library's autocommit logic, it's > a lot less defensible. If you consult the archives, you will find > actual user complaints about "why is now() returning a very old time?" > that we traced to use of interface layers that handle "commit()" by > issuing "COMMIT; BEGIN;". Hmmm... I agree this behavior isn't ideal, although I can see the case for viewing this as a mistake by the application developer: they are assuming that they know exactly when transactions begin, which is not a feature provided by their language interface. They should be using current_timestamp, and/or changing their language interface's configuration. That said, I think this is a minor irritation at best. The dual drawbacks of breaking backward compatibility and making the BEGIN semantics more confusing is enough to leave me satisfies with the status quo. If we do change this, I think Dennis' idea of making now() always return the same value within a given transaction is interesting: that might be a way to fix this problem without confusing the semantics of BEGIN. -Neil
Neil Conway <neilc@samurai.com> writes:
> Hmmm... I agree this behavior isn't ideal, although I can see the case
> for viewing this as a mistake by the application developer: they are
> assuming that they know exactly when transactions begin, which is not
> a feature provided by their language interface.
Well, actually, it's a bug in the interface IMHO. But as I said in the
last thread, it's a fairly widespread bug. We've been taking the
position that the interface libraries should get fixed, and that's not
happening. It's probably time to look at a server-side fix.
> If we do change this, I think Dennis' idea of making now() always
> return the same value within a given transaction is interesting:
You mean the time of the first now() call? I thought that was an
interesting idea also, but it's probably not going to look so hot
when we complete the TODO item of adding access to
the start-of-current-statement time. Having start-of-transaction be
later than start-of-statement isn't gonna fly :-(. If we were willing
to abandon that TODO item then I'd be interested in defining now() as
Dennis suggested.
regards, tom lane
Tom Lane wrote: > Neil Conway <neilc@samurai.com> writes: > > Hmmm... I agree this behavior isn't ideal, although I can see the case > > for viewing this as a mistake by the application developer: they are > > assuming that they know exactly when transactions begin, which is not > > a feature provided by their language interface. > > Well, actually, it's a bug in the interface IMHO. But as I said in the > last thread, it's a fairly widespread bug. We've been taking the > position that the interface libraries should get fixed, and that's not > happening. It's probably time to look at a server-side fix. > > > If we do change this, I think Dennis' idea of making now() always > > return the same value within a given transaction is interesting: > > You mean the time of the first now() call? I thought that was an > interesting idea also, but it's probably not going to look so hot > when we complete the TODO item of adding access to > the start-of-current-statement time. Having start-of-transaction be > later than start-of-statement isn't gonna fly :-(. If we were willing > to abandon that TODO item then I'd be interested in defining now() as > Dennis suggested. Defining now() as the first call seems pretty arbitrary to me. I can't think of any time-based interface that has that API. And what if a trigger called now() in an earlier query and you didn't even know about it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Tom Lane kirjutas E, 17.11.2003 kell 02:08: > Neil Conway <neilc@samurai.com> writes: > > Hmmm... I agree this behavior isn't ideal, although I can see the case > > for viewing this as a mistake by the application developer: they are > > assuming that they know exactly when transactions begin, which is not > > a feature provided by their language interface. > > Well, actually, it's a bug in the interface IMHO. But as I said in the > last thread, it's a fairly widespread bug. I'm not sure that it is a client-side bug. For example Oracle seems to _always_ have a transaction going, i.e. you can't be "outside" of transaction, and you use just COMMIT to commit old _and_start_new_ transaction. IIRC the same is true for DB2. For these database the BEGIN TRANSACTION command is mainly used for starting nested transactions, which we don't have. > We've been taking the > position that the interface libraries should get fixed, and that's not > happening. It's probably time to look at a server-side fix. Maybe "fixing" the interface libraries would make them incompatible with *DBC's for all other databases in some subtle ways ? ----------------- Hannu
Bruce Momjian kirjutas E, 17.11.2003 kell 02:31: > Defining now() as the first call seems pretty arbitrary to me. I can't > think of any time-based interface that has that API. And what if a > trigger called now() in an earlier query and you didn't even know about > it. That would be OK. The whole point of that previous discussion was to have now() that returns the same value over the span of the whole transaction. It would be even better to have now() that returns the time current transaction is COMMITted as this is the time other backend become aware of it ;) ----------- Hannu
Hannu Krosing wrote: > Bruce Momjian kirjutas E, 17.11.2003 kell 02:31: > > > Defining now() as the first call seems pretty arbitrary to me. I can't > > think of any time-based interface that has that API. And what if a > > trigger called now() in an earlier query and you didn't even know about > > it. > > That would be OK. The whole point of that previous discussion was to > have now() that returns the same value over the span of the whole > transaction. I think my issue is that there isn't any predictable way for a user to know when the now() time is recorded. By using start of transaction, at least we know for sure the point in time it is showing. > It would be even better to have now() that returns the time current > transaction is COMMITted as this is the time other backend become aware > of it ;) True, but implementing that would be very hard. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Hannu Krosing wrote:
>> It would be even better to have now() that returns the time current
>> transaction is COMMITted as this is the time other backend become aware
>> of it ;)
> True, but implementing that would be very hard.
Son, that was a *joke* ...
regards, tom lane
Hannu Krosing wrote: > Tom Lane kirjutas E, 17.11.2003 kell 02:08: > >>Neil Conway <neilc@samurai.com> writes: >> >>>Hmmm... I agree this behavior isn't ideal, although I can see the case >>>for viewing this as a mistake by the application developer: they are >>>assuming that they know exactly when transactions begin, which is not >>>a feature provided by their language interface. >> >>Well, actually, it's a bug in the interface IMHO. But as I said in the >>last thread, it's a fairly widespread bug. > > > I'm not sure that it is a client-side bug. For example Oracle seems to > _always_ have a transaction going, i.e. you can't be "outside" of > transaction, and you use just COMMIT to commit old _and_start_new_ > transaction. > > IIRC the same is true for DB2. Actually, in oracle a new transaction starts with first DDL after a commit. That does not include DML BTW. And Damn.. Actually I recently fixed a "bug" where I had to force a start of transaction in Pro*C, immediately after commit. Otherwise a real start of transaction could be anywhere down the line, causing some weird concurrency issues. Rather than fiddling with oracle support, I would hack my source code, especially this is not the first oracle bug I have worked around....:-( The fact that I couldn't control exact transaction start was such a irritation to put it mildly.. I sooooo missed 'exec sql begin work' in ecpg..:-) >>We've been taking the >>position that the interface libraries should get fixed, and that's not >>happening. It's probably time to look at a server-side fix. I hope that does not compramise transaction control I have with libpq/ecpg etc. And when we are talking about interface libraries, how many of them are within PG control and how many are not? With languages maintenend by postgresql group, it should behave correctly, right? E.g pl/perl,pl/python etc. And for other interface libraries, what are they exactly? php? Can't we just send them a stinker/patch to get that damn thing right(Whatever wrong they are doing. I have kinda lost thread on it..:-) Was it exact time of transaction start v/s now()?) Shridhar