Re: Is setQuerySnapshot called for embedded plpgsql function calls?
От | Burak Seydioglu |
---|---|
Тема | Re: Is setQuerySnapshot called for embedded plpgsql function calls? |
Дата | |
Msg-id | 1b8a973c0702021317sd0d569bn1d9384e07e3c4288@mail.gmail.com обсуждение исходный текст |
Ответ на | Is setQuerySnapshot called for embedded plpgsql function calls? ("Burak Seydioglu" <buraks78@gmail.com>) |
Список | 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 > > >
В списке pgsql-novice по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: Is setQuerySnapshot called for embedded plpgsql function calls?
Следующее
От: "Burak Seydioglu"Дата:
Сообщение: Re: Is setQuerySnapshot called for embedded plpgsql function calls?