Thread: 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)

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