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