Skip site navigation (1) Skip section navigation (2)

Re: Is setQuerySnapshot called for embedded plpgsql function calls?

From: "Burak Seydioglu" <buraks78(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Is setQuerySnapshot called for embedded plpgsql function calls?
Date: 2007-02-02 21:17:08
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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.

	var_query TEXT;
	var_result RECORD;
	var_result_seq RECORD;
	var_id BIGINT;


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
to zero......
		RETURN parent();
        -- found somebody
                -- increment sequence
                EXECUTE ''UPDATE sequence SET sequence_id='' ||
		-- return id and exit
		RETURN var_id;
' 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
> "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.
> Burak
> 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 SeydiogluDate: 2007-02-02 22:01:17
Subject: Re: Is setQuerySnapshot called for embedded plpgsql function calls?
Previous:From: chrisjDate: 2007-02-02 19:06:23
Subject: is there more documentation?

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group