Re: Admission Control

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
Date: 2010-06-28 20:06:00
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.


In response to


Browse pgsql-hackers by date

  From Date Subject
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