Thread: CommandCounterIncrement
Hello, Small technical question: what exactly CommandCounterIncrement do? And what exactly it should be used for? I use it to see data which is changed in current transaction. If to be more exact when I write BLOB in transaction each time I write additional piece I do CommandCounterIncrement. I ask this question because I found out that when I run postgres with verbose=4 I see lot's of StartTransactionCommand & CommitTransactionCommand pair in the place where BLOB is written. And I have a feeling that something is wrong. Looks like explicitly commit all changes. That's really bad... -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
Denis Perchine <dyp@perchine.com> writes:
> Small technical question: what exactly CommandCounterIncrement do?
It increments the command counter ;-)
> And what exactly it should be used for?
You need it if, within a chunk of backend code, you want subsequent
queries to see the results of earlier queries. Ordinarily a query
cannot see its own output --- else a command likeUPDATE foo SET x = x + 1
for example, would be an infinite loop, since as it scans the table
it would find the tuples it inserted, update them, insert the updated
copies, ...
Postgres' solution is that tuples inserted by the current transaction
AND current command ID are not visible. So, to make them visible
without starting a new transaction, increment the command counter.
> I ask this question because I found out that when I run postgres with
> verbose=4 I see lot's of StartTransactionCommand & CommitTransactionCommand
> pair in the place where BLOB is written. And I have a feeling that something
> is wrong. Looks like explicitly commit all changes. That's really bad...
These do not commit anything, assuming you are inside a transaction
block. Offhand I don't think they will amount to much more than a
CommandCounterIncrement() call in that case, but read xact.c if you want
to learn more.
regards, tom lane
> Denis Perchine <dyp@perchine.com> writes: > > Small technical question: what exactly CommandCounterIncrement do? > > It increments the command counter ;-) > > > And what exactly it should be used for? > > You need it if, within a chunk of backend code, you want subsequent > queries to see the results of earlier queries. Ordinarily a query > cannot see its own output --- else a command like > UPDATE foo SET x = x + 1 > for example, would be an infinite loop, since as it scans the table > it would find the tuples it inserted, update them, insert the updated > copies, ... > > Postgres' solution is that tuples inserted by the current transaction > AND current command ID are not visible. So, to make them visible > without starting a new transaction, increment the command counter. Perfect. That what I thought it is. > > I ask this question because I found out that when I run postgres with > > verbose=4 I see lot's of StartTransactionCommand & > > CommitTransactionCommand pair in the place where BLOB is written. And I > > have a feeling that something is wrong. Looks like explicitly commit all > > changes. That's really bad... > > These do not commit anything, assuming you are inside a transaction > block. Offhand I don't think they will amount to much more than a > CommandCounterIncrement() call in that case, but read xact.c if you want > to learn more. Yeps. I get this... But there's still a problem when people try to use BLOBs outside of TX. I like to detect it... -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
I have added this to the developers FAQ. However, the developers FAQ isn't accessible from the web site, and I have contacted Vince on this. --------------------------------------------------------------------------- 13) What is CommandCounterIncrement()? Normally, transactions can not see the rows they modify. This allows UPDATE foo SET x = x + 1</CODE> to work correctly. However, there are cases where a transactions needs to see rows affected in previous parts of the transaction. This is accomplished using a Command Counter. Incrementing the counter allows transactions to be broken into pieces so each piece can see rows modified by previous pieces. CommandCounterIncrement() increments the Command Counter, creating a new part of the transaction. --------------------------------------------------------------------------- > Denis Perchine <dyp@perchine.com> writes: > > Small technical question: what exactly CommandCounterIncrement do? > > It increments the command counter ;-) > > > And what exactly it should be used for? > > You need it if, within a chunk of backend code, you want subsequent > queries to see the results of earlier queries. Ordinarily a query > cannot see its own output --- else a command like > UPDATE foo SET x = x + 1 > for example, would be an infinite loop, since as it scans the table > it would find the tuples it inserted, update them, insert the updated > copies, ... > > Postgres' solution is that tuples inserted by the current transaction > AND current command ID are not visible. So, to make them visible > without starting a new transaction, increment the command counter. > > > I ask this question because I found out that when I run postgres with > > verbose=4 I see lot's of StartTransactionCommand & CommitTransactionCommand > > pair in the place where BLOB is written. And I have a feeling that something > > is wrong. Looks like explicitly commit all changes. That's really bad... > > These do not commit anything, assuming you are inside a transaction > block. Offhand I don't think they will amount to much more than a > CommandCounterIncrement() call in that case, but read xact.c if you want > to learn more. > > regards, tom lane > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026