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
----------------------------------

Tue, 31 Oct 2000 00:19:35 +0600
Denis Perchine <dyp@perchine.com>
CommandCounterIncrement
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

Thu, 02 Nov 2000 11:43:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: CommandCounterIncrement 
> 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
----------------------------------

Thu, 2 Nov 2000 22:48:41 +0600
Denis Perchine <dyp@perchine.com>
Re: CommandCounterIncrement
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, Pennsylvania 19026

Sat, 4 Nov 2000 13:23:19 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: CommandCounterIncrement