Re: Is setQuerySnapshot called for embedded plpgsql function calls? - Mailing list 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.

CREATE OR REPLACE FUNCTION parent() RETURNS bigint AS '
DECLARE
    var_query TEXT;
    var_result RECORD;
    var_result_seq RECORD;
    var_id BIGINT;
BEGIN

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>'' ||
quote_literal(var_result_seq.sequence_id);

    FOR var_result IN EXECUTE var_query LOOP
        var_id := var_result.sponsor_id;
    END LOOP;

    -- 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
    ELSE
                -- increment sequence
                EXECUTE ''UPDATE sequence SET sequence_id='' ||
quote_literal(var_id);
        -- return id and exit
        RETURN var_id;
    END IF;

END;
' LANGUAGE 'plpgsql';


On 2/1/07, Burak Seydioglu <buraks78@gmail.com> wrote:
> Searched for CommandCounterIncrement and it is apparently a mechanism
> to allow transactions to see their own updates.
> (http://library.n0i.net/programming/database/po-devfaq/)
>
> 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.
>
> Burak
>
>
> On 2/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Burak Seydioglu" <buraks78@gmail.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
> >
>