Message-ID: <4846AA49.5010601@setonhome.org>
Date: Wed, 04 Jun 2008 10:44:25 -0400
From: Lewis Kapell <lkapell@setonhome.org>
Organization: Seton Home Study School
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: backend pid changing
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I have a Windows application which connects to a Postgres (8.3) database 
residing on our company server.  Most of the application's users work 
from their homes, so the application was developed with a lot of 
security checks.

When a client connects to the database, a random hash is generated and 
sent to the client; this hash is also saved in a Postgres table along 
with the user id and the return value of pg_backend_pid().  When the 
client submits queries, it presents its hash value and the server 
cross-checks this, and the current value of pg_backend_pid(), against 
the values that were stored previously.

If there is a mismatch, the client is instructed to obtain a new hash 
and begin again.  The information about the mismatch is also recorded 
for future inspection.  By examining the logs, I have observed that the 
backend pid for a particular client sometimes changes during a session. 
  This seems to happen about a dozen times a day, total.  Usually this 
is not a problem, as the client will get a new hash and keep going.

Sometimes, however, this seems to happen in the middle of an operation. 
  This happens when the client has sent a large chunk of data that is to 
be stored in the database.  The client sends its authorization 
information immediately before sending the data, and also with the data 
chunk.  On rare occasions, the backend pid somehow seems to change 
during the time it takes for the data to be sent.  This causes errors 
and loss of time for the user.

I'm sure there are more details that would be needed to give a complete 
picture of what is going on, yet this message is pretty long already.  I 
am going to stop here and ask whether anyone can make sense of this. 
That is, make sense of what I have written, and also of why the backend 
pid would change during an operation as I have described.  Thanks to any 
who can offer information on this.

Lewis