Re: Lost updates vs resumable connections/transactions - Mailing list pgsql-interfaces

On 12/16/2004 8:52 AM, Jens Lechtenboerger wrote:
> Greg Stark <gsstark@mit.edu> writes:
> 
>> Jan Wieck <JanWieck@Yahoo.com> writes:
>>
>>> Even applications that have statefull enduser terminals (like SAP R/3 for
>>> example) never allow an open transaction over user interaction. 
>>
>> I'm not sure using SAP as your paragon of design excellence is a wise choice
>> here. From what I understand SAP implemented its own locking system because
>> the database it was based on didn't offer any locking at all.
>>
>> But your basic point is sound. For a web site I would definitely avoid using
>> anything like database locks and even avoid doing anything with application
>> locks if possible.
> 
> Well, I don't necessarily have to use locks.  I want any form of
> concurrency control that ensures serializability.  Optimistic
> approaches would be fine as well.
> 
>> 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.

Now the one (and only that one) Apache process, that is running your 
script, and that newly created backend (and only that one) have a socket 
each that are the connection for this session. No other process (well, 
we're not getting into IP spoofing or other hacker stuff please) can 
send or receive messages transmitted over this bidirectional channel. 
And there is no portable way to hand either side of the socket 
connection over to another process.

Now your PHP script ends. It can close the connection, which would 
result in an EOF condition on the server side, or leave it around for 
later reuse (persistent connection in PHP).

It doesn't really matter what it does, because now your user slams on 
the next submit button and "another" Apache work process will handle 
this new script request. Note that during your developer-is-single-user 
testing, you sometimes get the same Apache work process over and over 
again. But this isn't the case on a real production server under load.

And now what? How can this other Apache work process establish a new or 
take over the existing (persistent) connection that was/is used by the 
first Apache work process, which might not even exist any more because 
Apache dynamically adjusts the number of work processes to the current 
workload. Or that Apache process might be busy serving a 20MB PDF 
download for some time ... or whatever it does.

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?


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #