pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbackend pid changing
4+ messages / 3 participants
[nested] [flat]
* backend pid changing
@ 2008-06-04 14:44 Lewis Kapell <lkapell@setonhome.org>
2008-06-04 15:06 ` Re: backend pid changing Matthew Wakeling <matthew@flymine.org>
2008-06-04 15:10 ` Re: backend pid changing Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Lewis Kapell @ 2008-06-04 14:44 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: backend pid changing
2008-06-04 14:44 backend pid changing Lewis Kapell <lkapell@setonhome.org>
@ 2008-06-04 15:06 ` Matthew Wakeling <matthew@flymine.org>
1 sibling, 0 replies; 4+ messages in thread
From: Matthew Wakeling @ 2008-06-04 15:06 UTC (permalink / raw)
To: pgsql-performance
On Wed, 4 Jun 2008, Lewis Kapell wrote:
> The client sends its authorization information immediately before
> sending the data, and also with the data chunk.
Well, I have no idea why the backend pid is changing, but here it looks
like you have a classic concurrency problem caused by checking a variable
twice. It seems you have client-side error recovery on the initial check,
but not on the second check. The solution is to eliminate the first check,
and implement proper error recovery on the second check, so that the
client can just get a new hash and try again.
Matthew
--
It's one of those irregular verbs - "I have an independent mind," "You are
an eccentric," "He is round the twist."
-- Bernard Woolly, Yes Prime Minister
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: backend pid changing
2008-06-04 14:44 backend pid changing Lewis Kapell <lkapell@setonhome.org>
@ 2008-06-04 15:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-04 15:18 ` Re: backend pid changing Lewis Kapell <lkapell@setonhome.org>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2008-06-04 15:10 UTC (permalink / raw)
To: Lewis Kapell <lkapell@setonhome.org>; +Cc: pgsql-performance
Lewis Kapell <lkapell@setonhome.org> writes:
> ... By examining the logs, I have observed that the
> backend pid for a particular client sometimes changes during a session.
That is just about impossible to believe, unless perhaps you have a
connection pooler in the loop somewhere?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: backend pid changing
2008-06-04 14:44 backend pid changing Lewis Kapell <lkapell@setonhome.org>
2008-06-04 15:10 ` Re: backend pid changing Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-06-04 15:18 ` Lewis Kapell <lkapell@setonhome.org>
0 siblings, 0 replies; 4+ messages in thread
From: Lewis Kapell @ 2008-06-04 15:18 UTC (permalink / raw)
To: pgsql-performance
We are not using connection pooling, however the clients are tunneling
through SSH. Forgot to mention that in my first message. Does that
make any difference to it?
Thank you,
Lewis Kapell
Computer Operations
Seton Home Study School
Tom Lane wrote:
> Lewis Kapell <lkapell@setonhome.org> writes:
>> ... By examining the logs, I have observed that the
>> backend pid for a particular client sometimes changes during a session.
>
> That is just about impossible to believe, unless perhaps you have a
> connection pooler in the loop somewhere?
>
> regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-06-04 15:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-04 14:44 backend pid changing Lewis Kapell <lkapell@setonhome.org>
2008-06-04 15:06 ` Matthew Wakeling <matthew@flymine.org>
2008-06-04 15:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-04 15:18 ` Lewis Kapell <lkapell@setonhome.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox