On a side note, I already have a recursive plpgsql function (running
successfully on both 7.4 and 8.0) which is able to see its own changes
(in case it needs to reset the sequence) Please see below.
CREATE OR REPLACE FUNCTION parent() RETURNS bigint AS '
LOCK TABLE sequence IN ACCESS EXCLUSIVE MODE;
var__id := 0;
FOR var_result_seq IN EXECUTE ''SELECT sequence_id FROM sequence'' LOOP
var_query := ''SELECT sponsor_id FROM sponsor WHERE sponsor_id>'' ||
FOR var_result IN EXECUTE var_query LOOP
var_id := var_result.sponsor_id;
-- if nobody was found
IF var_id = 0 THEN
-- reset seq
EXECUTE ''UPDATE sequence SET sequence_id='' || quote_literal(''0'');
-- and start over
-- this next run is able to see that the field is set
-- found somebody
-- increment sequence
EXECUTE ''UPDATE sequence SET sequence_id='' ||
-- return id and exit
' LANGUAGE 'plpgsql';
On 2/1/07, Burak Seydioglu <buraks78(at)gmail(dot)com> wrote:
> Searched for CommandCounterIncrement and it is apparently a mechanism
> to allow transactions to see their own updates.
> From http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html
> "Read Committed is the default isolation level in PostgreSQL. When a
> transaction runs on this isolation level, a SELECT query sees only
> data committed before the query began; it never sees either
> uncommitted data or changes committed during query execution by
> concurrent transactions. (However, the SELECT does see the effects of
> previous updates executed within its own transaction, even though they
> are not yet committed.) In effect, a SELECT query sees a snapshot of
> the database as of the instant that that query begins to run. Notice
> that two successive SELECT commands can see different data, even
> though they are within a single transaction, if other transactions
> commit changes during execution of the first SELECT. "
> So basically, my transactions should be able to see its own updates.
> SInce they can not, I assume CommandCounterIncrement is not called and
> this is related to the plpgsql language implementation. Using PHP, for
> instance, would address this issue, correct? And last of all, is there
> any way to to call CommandCounterIncrement within the plpgsql? - I
> know this sounds retarded but I am desperate at this point...
> Production server is 7.4. Localhost is 8.0.8. Unfortunately, I could
> not get this transaction to work properly on both servers.
> Thank you for you time. I really appreciate your help.
> On 2/1/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Burak Seydioglu" <buraks78(at)gmail(dot)com> writes:
> > > For some reason, the consecutive second_func() calls do not see the
> > > newly inserted data. So the total for the next second_func() call
> > > always remains zero. Please see the code below.
> > > Is this because setQuerySnapshot() is not called for embedded plpgsql
> > > functions but only for the first_function() call?
> > For operations within a single transaction, what counts is
> > CommandCounterIncrement not SetQuerySnapshot.
> > > I am runnging 7.4 btw.
> > I believe we changed the rules for this in 8.0 ... can you upgrade?
> > regards, tom lane
In response to
pgsql-novice by date
|Next:||From: Burak Seydioglu||Date: 2007-02-02 22:01:17|
|Subject: Re: Is setQuerySnapshot called for embedded plpgsql function calls?|
|Previous:||From: chrisj||Date: 2007-02-02 19:06:23|
|Subject: is there more documentation?|