loop with circular updates
Hi All.
I'm writing a plpgsql function that creates a table and loops over the
items in that table, using a FOR ... IN EXECUTE ... loop. The thing is, on
each iteration I update the table over which I am looping. Sometimes, the
result of this update makes it no longer necessary/desirable to process
subsequent records in the loop.
My results are confusing me, and I was wondering if it is possible that my
method doesn't work considering how postgres holds query results in memory
for loops. I read somewhere that it caches several records at a time,
retrieving more as needed. If this is the case (is it?), and I updated a
field of an item that is already cached, would I then see that change when
I retrieved that item in the loop?
I feel like this is a difficult concept to illustrate with words. Perhaps
my pl/pgsql vocabulary is still too small for asking this question, but I'm
hoping that someone here can see my concern and tell me whether or not I am
barking up the right tree, so to speak.
Thanks,
Meghan
Jessica M Salmon wrote:
Hi All.
I'm writing a plpgsql function that creates a table and loops over the
items in that table, using a FOR ... IN EXECUTE ... loop. The thing is, on
each iteration I update the table over which I am looping. Sometimes, the
result of this update makes it no longer necessary/desirable to process
subsequent records in the loop.
Can you tell us more about the app? Sounds like an ERP allocation or
something like that.
I'm not sure if my second email made it through; I haven't seen it on the
website yet. But, basically, what I am asking is this:
-is it true that when looping over query results in a plpgsql
for..in..execute loop, several records are stored in memory at one time?
and if so, then I am wondering:
-if I then update one of the records currently in memory, are these
changes visible when the loop gets to it?
I hope that someone can tell me these things.
Thanks,
Meghan
Kenneth Downs
<ken@secdat.com>
To
07/26/2006 08:50 Jessica M Salmon
AM <jmsalmon@fs.fed.us>
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] loop with circular
updates
Jessica M Salmon wrote:
Hi All.
I'm writing a plpgsql function that creates a table and loops over the
items in that table, using a FOR ... IN EXECUTE ... loop. The thing is, on
each iteration I update the table over which I am looping. Sometimes, the
result of this update makes it no longer necessary/desirable to process
subsequent records in the loop.
Can you tell us more about the app? Sounds like an ERP allocation or
something like that.
(See attached file: ken.vcf)
Attachments:
Jessica M Salmon <jmsalmon@fs.fed.us> writes:
-is it true that when looping over query results in a plpgsql
for..in..execute loop, several records are stored in memory at one time?
Yes, but that's got nothing to do with your issue.
-if I then update one of the records currently in memory, are these
changes visible when the loop gets to it?
*All* queries in Postgres see a snapshot as of the instant of query
start. This is a property of the MVCC rules and has nothing to do with
buffering. What's returned by a FOR IN EXECUTE is whatever was in the
database when the loop began.
regards, tom lane
Tom,
Yes, I see. good to get that straightened out in my head, thank you.
-Meghan
Tom Lane
<tgl@sss.pgh.pa.u
s> To
Jessica M Salmon
07/28/2006 09:46 <jmsalmon@fs.fed.us>
AM cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] loop with circular
updates
Jessica M Salmon <jmsalmon@fs.fed.us> writes:
-is it true that when looping over query results in a plpgsql
for..in..execute loop, several records are stored in memory at one time?
Yes, but that's got nothing to do with your issue.
-if I then update one of the records currently in memory, are these
changes visible when the loop gets to it?
*All* queries in Postgres see a snapshot as of the instant of query
start. This is a property of the MVCC rules and has nothing to do with
buffering. What's returned by a FOR IN EXECUTE is whatever was in the
database when the loop began.
regards, tom lane