On 2010-06-28 21:24, Kevin Grittner wrote:
> Jesper Krogh<jesper@krogh.cc> wrote:
>
>
>> Sorry if I'm asking silly questions, but how does transactions and
>> connection pooler's interact?
>>
>
> That depends a great deal on the pooler and its configuration, as
> well as your client architecture. Our shop gathers up the
> information needed for our database transaction and submits it to a
> server application which has all the logic needed to use that data
> to apply the transaction. We determined long ago that it is a Very
> Bad Idea for us to have an open database transaction which is
> waiting for a user to do something before it can proceed.
>
The situation is more:
1) Grab a bunch of data (using pg_try_advisory_lock() to lock out other processes from grabbing the same).
2) Process the data (in external software).
3) Push results back into the database, including a flag telling that the data has been processed.
4) Release advisory locks.
Step 2 takes somewhere between a couple of seconds to a couple of
minutes depending on the task to be done.
It might not be "optimal" but it is extremely robust and simple
to wrap 1 to 4 within a BEGIN / COMMIT block.
On the application side is really nice not having to deal with
"partly processed" data in the database, which I can get around
with by just keeping the transaction open.
From my POV, a connection pooler doesn't buy anything, and
I cannot stop all processes from executing at the same time, allthough
it "most likely" will not happen. There is no "wait for user"
involved.
And that means somewhere in the 100+ backends, allthough they
are "mostly" idle, seen from a database perspective.
I have not hit any issues with the work_mem being too high, but
I'm absolutely sure that I could flood the system if they happened to
be working at the same time.
Jesper
--
Jesper
