Hi!

I continue the old theme what is named "When the session ends":
http://archives.postgresql.org/pgsql...6/msg00913.php

I want to trying this in live - I thought, because somewhere some
people thinking about replace a big ElevateDB database with
PostgreSQL.

The ElevateDB is uses "timeout" and "dead session timeout" parameter.
The first is interval when the server saying "this is inactive
connection", and the second is fully drop this dead session.
If the inactive is activated (reconnect) then we can work again.

So today I started and experience with two PC (Win7 server, WXP
client).
PG9, PGAdmin v14.
The statement timeout is 5 minute, the tcp_keepalive is default.

I started 3 client requests (pgadmin). I disconnect the cabel and
waiting for 10 minutes.
The server is already shown all connections (3).

I started 3 new requests. I disconnect the cabel and waiting for 10
minutes.
The server is already shown all connections (6).

I started 3 new requests. I disconnect the cabel and waiting for 10
minutes.
The server is already shown all connections (total = 9).

I started "begin; lock xx share exclusive; select * from xx".
I disconnect the cabel and waiting for 10 minutes.
The server is already shown all connections (total = 10).

There is more than 1 hours passed away, and the sessions are staying
alive!
And more problem that the "xx" table is always locked - so I cannot
modify it because the dead transaction is lock this resource.

I tried to update it, but I got error:
ERROR: canceling statement due to statement timeout
SQL state: 57014

I'm very wondering in this thing!
How can postgresql working in "no autocommit" mode if the Sessions
never dying???
Ok, there is a "max sessions" limit, but this is very problematic.

If you visualize this situation, you can see the negative feedback:
1.) user A is starting a transaction, locking a table (T)
2.) many people is working.
3.) user A terminated by Blue Screen of Windows, or his cable is
cutted by a blonde woman... :-)
4.) session of user A is alive
5.) other users trying to do something with table T. But they got
error, or they got waiting cursor...

In ElevateDB we can resolve this problem with wating of 5 minutes.
Because dead sessions are purged on 5 minutes, all of the resources
kept by them released at this point.
So after 5 minutes we will sure that other people can working.

In this PGSQL mechanism we cannot do anything!

Because the sessions are here, in "alive/idle" state, and they are
don't vanishing...

Please let me know what can we do without "patching/recompiling" the
server?

Ok, we can write this parameter:
http://technet.microsoft.com/en-us/l...36(WS.10).aspx

But may we cannot do this - because global, domain level deny on
registry, or other programs/system needs this, or somewhere they have
100-200 client machines

Thanks for your help:
dd

durumdara wrote:
[but aborted connections don't get closed on the server]

statement_timeout does not come into play here.

Could you post the result of

SELECT current_setting('tcp_keepalives_idle'),
current_setting('tcp_keepalives_interval'),
current_setting('tcp_keepalives_count');

Yours,
Laurenz Albe

Dear Laurenz!

On okt. 25, 11:54, "Laurenz Albe" <inv... (AT) spam (DOT) to.invalid> wrote:
PGADMIN shown:
-1, -1, 0

But: after (I started the XP client) 2 hours passed, and the sessions
starting to vanish one by one.
So may the timeout is "2 hours" now in the system (Win7/x64).

Read this:
http://technet.microsoft.com/en-us/l...36(WS.10).aspx
REG_DWORD 0x1–0xFFFFFFFF (milliseconds) 0x6DDD00 (7,200,000
milliseconds = 2 hours)

Interesting that this value is also providing 2 hours limit.

So: what I can do to set this value lower?
Is it enough to parameterize the registry on the server?

Why the PGSQL isn't supports a value that set timeout, when the server
"abort" the tcp connection on session?

This can override the default tcp parameters.

This can be a second based timer which starting a procedure.
That procedure is listing the sessions, and if a session is idle then
increment a value - else reset it (to 0). If this value reach the
limit, the system is removing the session, not matter what the OS TCP
do...

Thanks:
dd

Hi!

I continued the testing.
This value is commonly used to abort the TCP alives at a specific
time:

HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Param eters\KeepAliveTime

See here:
http://www.symantec.com/business/sup...&id=HOWTO56221

If the pgsql server is set to 0 in all tcp params, this timeout used.

And it was working: I opened many connections, locks in second PC,
next disconnected the cable, and after 5-6 minutes these Sessions
vanished.

But: my opinion that this "hacking" of the system is very wrong.
Why?

1.)
Many places the registry is taboo. In some places for local system
administrators don't have enough right to set these parameters!!!

2.)
The registry setting is global - so we can get side effects in other
programs.
For example if I have 4 PGSQL server, but one of them needs 10 minutes
timeout - others only 5 minutes...

3.)
This is used for PostgreSQL only.
Why don't PostgreSQL handle these kind of Session Timeouts?
Okay, the TCP/IP is out of your scope.
But you can do other things, like separate the Session from TCPIP.
For example:
Every TCPIP connection is join to a Session.
When a Session timeout reached (new parameter by server), the Session
(+ resources) closed, and only TCPIP connection remaining - without
the Session.
If some packet we got from TCPIP connection that released the Session,
we resend an error (Session closed, reconnection is not possible).
Then your TCPIP model (what based on OS settings in Windows) can
remaining.

Thanks for your read:
dd

durumdara <durumdara (AT) gmail (DOT) com> wrote:
If you want to control session timeouts without changing your TCP
settings, I think the usual way is to use a connection pooler in front
of your PostgreSQL server.

For example, pgbouncer's client_idle_timeout setting looks like it will
do what you want; see
http://pgbouncer.projects.postgresql...oc/config.html

-M-

Hi!


On okt. 25, 20:56, Matthew Woodcraft <matth... (AT) chiark (DOT) greenend.org.uk>
wrote:
But I don't know that pgbouncer CAN reset fully the connection?
For example:
If the connection locks resources, starting a transaction, prepare
statements, etc., plus create advisory locks, can the pgbouncer reset
this connection fully (unlock, rollback, unprepare, destroy adv.
locks)?

Thanks:
dd

durumdara wrote:
Probably I'm misunderstanding something crucial here, but
why don't you set tcp_keepalives_idle=600 or something similar
if two hours is too long for you?

These parameters are there specifically so that you can change
the settings for PostgreSQL without changing the operating system's
TCP settings.

Yours,
Laurenz Albe

durumdara <durumdara (AT) gmail (DOT) com> wrote:
I believe so, though I don't use pgbouncer myself.

It should roll back and then issue DISCARD ALL; see
http://www.postgresql.org/docs/9.1/s...l-discard.html

-M-

Dear Laurenz!

On okt. 27, 10:12, "Laurenz Albe" <inv... (AT) spam (DOT) to.invalid> wrote:
With your answer I found a solution, but I don't know it is working on
every machine or not.
In Win7/x64 it is working.

So:
In Windows the count is not usable.
It is ALWAYS 10!

The other parameters:

Full_TimeOut = Idle + (Interval * 10);

So if you determine that after 1 minutes you check the clients, and
you want to disconnect after 5 minutes:

Full_TimeOut = 5 * 60 = 300
Idle = 1 * 60 = 60
Interval_Dif = 300 - 60 = 240
Interval = 240 / 10 = 24

I experienced that I have two ways to set these values.

1.) Basically the postgresqls.conf
2.) set parameters before query (this override 1.)

Every of them effective, I tried it with two queries from another
machine.

I don't know that what happens in other machines.
So what is the count in other machines, like XP, etc... Hmmm...

Thanks for your help:
dd

durumdara wrote:
Yes, the documentation says that it does not work:
http://www.postgresql.org/docs/curre...EPALIVES-COUNT

That's how it is supposed to work.

Did you read the documentation before you asked?

The same thing, more or less. Setting these parameters works only
on operatin systems that support the feature.
The documentation will tell you details.

Yours,
Laurenz Albe