|From:||Jesper Krogh <jesper(at)krogh(dot)cc>|
|To:||pgsql-hackers(at)postgresql(dot)org, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>|
|Subject:||Re: Admission Control|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
On 2010-06-28 21:24, Kevin Grittner wrote:
> Jesper Krogh<jesper(at)krogh(dot)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"
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.
|Next Message||Magnus Hagander||2010-06-28 20:21:01||Re: Keepalives win32|
|Previous Message||Tom Lane||2010-06-28 19:59:05||Re: Propose Beta3 for July|