Thread: Is setQuerySnapshot called for embedded plpgsql function calls?
I have two plpgsql functions. first_function() calls the second one
with a repetitive EXECUTE second_func() statement. Every time the
second_func() is called, it creates a record in the table and this new
data is then used in the consecutive second_func() call.
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?
I am runnging 7.4 btw.
Any input is appreciated. Thank you very much.
Burak
--
-- FIRST FUNCTION
--
CREATE OR REPLACE FUNCTION sales() RETURNS void AS '
DECLARE
var_result RECORD;
BEGIN
FOR var_result IN EXECUTE ''SELECT seller_id FROM seller'' LOOP
EXECUTE individual_sales(var_result.seller_id);
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
--
-- SECOND FUNCTION
--
CREATE OR REPLACE FUNCTION individual_sales(bigint,bigint) RETURNS void AS '
DECLARE
var_id ALIAS FOR $1;
var_sponsor ALIAS FOR $2;
var_query TEXT;
var_result RECORD;
var_total INTEGER;
BEGIN
var_query := ''SELECT SUM(sales) AS s FROM sales WHERE sponsor='' ||
quote_literal(var_id);
FOR var_result IN EXECUTE var_query LOOP
IF var_result.s IS NOT NULL OR var_result.s != 0 THEN
var_total := var_total + var_result.s;
END IF;
END LOOP;
--
-- insert sales stats for associate
--
EXECUTE
''INSERT INTO sales VALUES(''
|| quote_literal(var_id) || '',''
|| quote_literal(var_sponsor) || '',''
|| quote_literal(var_total) ||
'')'';
RETURN;
END;
' LANGUAGE 'plpgsql';
"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
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
> >
>
Created a pure PHP solution... Still the same result... Isn't this conflicting with the documentation quoted below... Or INSERTs are not considered updates? I am lost at this point... Burak 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 > > >