backend pid changing

From: Lewis Kapell
Subject: backend pid changing
Date: ,
(view: Whole thread, Raw)
Responses: Re: backend pid changing  (Matthew Wakeling)
Re: backend pid changing  (Tom Lane)
List: pgsql-performance

Tree view

backend pid changing  (Lewis Kapell, )
 Re: backend pid changing  (Matthew Wakeling, )
 Re: backend pid changing  (Tom Lane, )
  Re: backend pid changing  (Lewis Kapell, )
   PgPool parallel query performance rules of thumb  (John Beaver, )
    Re: PgPool parallel query performance rules of thumb  ("Mason Sharp", )

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.


pgsql-performance by date:

From: James Mansion
Subject: Re: insert/update tps slow with indices on table > 1M rows
From: andrew klassen
Subject: Re: insert/update tps slow with indices on table > 1M rows