Skip site navigation (1) Skip section navigation (2)

Re: Lost updates vs resumable connections/transactions

From: Jens Lechtenboerger <lechten(at)wi(dot)uni-muenster(dot)de>
To: Jan Wieck <JanWieck(at)Yahoo(dot)com>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: Lost updates vs resumable connections/transactions
Date: 2004-12-17 13:45:49
Message-ID: m21xdpkpgy.fsf@pcwi4002.uni-muenster.de (view raw or flat)
Thread:
Lists: pgsql-interfaces
Dear Jan and Greg,

thank you very much for your explanations so far!
I'll just continue ;)

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:

> On 12/16/2004 8:52 AM, Jens Lechtenboerger wrote:
>> Greg Stark <gsstark(at)mit(dot)edu> writes:
>>> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>>>
>>> If you really really want to expose the database session state I think he's on
>>> the right track using SQLRelay. This would let him handle reconnecting a user
>>> with her session even if she's connecting to a different Apache process.
>> But why should I have SQLRelay between me and the database?
>> I don't plan to use any of its "real" features.  It would just be a
>> proxy with a known address that maintains a database connection.
>> Obviously, the database server itself has a known address and
>> maintains database connections...
>
> Because ...
>
> actually knowing how the connection and session works on the PostgreSQL server
> side will help understanding it.
>
> When your client application (in your case an Apache work process executing a
> PHP script) connects to the DB via libpq PQconnect(), it internally does
> socket(2), connect(2). On the server side, the postmaster, which had done
> socket(2), bind(2), listen(3) and currently wating on a select(2) will see
> that the server socket is ready. It will now call accept(2) to get the file
> descriptor of the server side end of the connection. After that it will
> fork(2) off a new process that will become your database server backend
> process handling your session.
>
> [...Explanation about subsequent Apaches processes communicating over
> different sockets deleted...]
>
> It doesn't help if you tell how the API call would preferrably look like. We
> need to know what you think this new libpq functions would do internally. What
> are the system calls that actually make this wonder happen?

What about the following?

When a connection is established for the first time, a unique ID is
generated by the postmaster, which is passed on to the database
backend process.  The postmaster keeps a map about IDs and
associated backends.

When the connection is suspended, the ID is returned from the
backend to the Apache process, which stores it as part of the
session.  The socket is destroyed.  Then, (I don't know if/how
postmaster and backends communicate) the backend tells the
postmaster that its connection is suspended which is recorded in the
map kept at the postmaster.  Then the backend blocks until informed
by the postmaster that the connection should be resumed.
(Now, as Greg pointed out, this requires keeping resources such as
locks in memory.  Again: I don't assume that this mechanism is used
with thousands of users.  Programmers can be warned.)

When the connection is resumed, the postmaster creates a new socket
with the (new) Apache process (just as you described above), gets
the ID, informs the backend, and passes the new socket with
sendmsg(2) to the backend which gets it with recvmsg(2).  (I have
never passed sockets like this; I just read that it should work.)
Now the backend can continue the existing transaction with the new
Apache process.

Or not?

Jens


In response to

Responses

pgsql-interfaces by date

Next:From: Jan WieckDate: 2004-12-17 16:17:37
Subject: Re: Lost updates vs resumable connections/transactions
Previous:From: Tom LaneDate: 2004-12-17 02:17:43
Subject: Re: [INTERFACES] PL/Python: How do I use result methods?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group