
Hi all,

I was implement the "transaction idle timeout" function in PostgreSQL
(version 7.4.5 and 8.0.0beta2)

test=# SET trans_idle_timeout=10;
SET
test=# BEGIN;
BEGIN

(ps)
...
postgres: sygma test [local] idle in transaction (3)
postgres: sygma test [local] idle in transaction (2)
postgres: sygma test [local] idle in transaction (1)
postgres: sygma test [local] idle in transaction (aborted)

test=# INSERT INTO test (a,b) VALUES (1,'a');
ERROR:  current transaction is aborted, commands ignored until end of transaction block


The code is very simple and stable.

Changed files:

include/storage/proc.h
backend/libpq/pqcomm.c
backend/libpq/be-secure.c
backend/utils/misc/guc.c

TODO: SSL connection, manual, psql help & tab-competition

Do you interest it?


Regards, Sygma

Fri, 17 Sep 2004 08:47:02 +0200 (CEST)
=?ISO-8859-1?Q?Szima_G=E1bor?= <sygma@axelero.hu>
transaction idle timeout in 7.4.5 and 8.0.0beta2
On Fri, Sep 17, 2004 at 08:47:02AM +0200, Szima G?bor wrote:
 
> I was implement the "transaction idle timeout" function in PostgreSQL
> (version 7.4.5 and 8.0.0beta2)

It sounds interesting to me (for use in libpqxx, the C++ API), but perhaps
for a slightly unusual reason.  When a connection to the backend is lost
just as you're waiting for the result of a COMMIT, you can't be sure if the
transaction was rolled back or not.

If I could know (not influence, just "know") when a transaction times out,
then I could wait for this amount of time, reconnect to the backend, and
check for some record left in a special table by the transaction.  If it's
not there, I'll know "for sure" (insofar as anything can ever be sure) that
the transaction was not committed.  This is still guesswork in the current
situation.

There's one assumption, however, and that is that the transaction will time
out even if (part of) the timeout time was spent processing rather than
waiting for a command.  I'm not sure how many people would be willing to
pay that price.  Perhaps a connection timeout would be more appropriate for
this sort of thing.


Jeroen


Sat, 18 Sep 2004 17:19:01 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> It sounds interesting to me (for use in libpqxx, the C++ API), but perhaps
> for a slightly unusual reason.  When a connection to the backend is lost
> just as you're waiting for the result of a COMMIT, you can't be sure if the
> transaction was rolled back or not.

> If I could know (not influence, just "know") when a transaction times out,
> then I could wait for this amount of time, reconnect to the backend, and
> check for some record left in a special table by the transaction.  If it's
> not there, I'll know "for sure" (insofar as anything can ever be sure) that
> the transaction was not committed.  This is still guesswork in the current
> situation.

I don't see any reason for guesswork.  Remember the PID of the backend
you were connected to.  On reconnect, look in pg_stat_activity to see if
that backend is still alive; if so, sleep till it's not.  Then check to
see if your transaction committed or not.  No need for anything so
dangerous as a timeout.

			regards, tom lane

Sat, 18 Sep 2004 12:43:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2 
On Sat, Sep 18, 2004 at 12:43:05PM -0400, Tom Lane wrote:
 
> I don't see any reason for guesswork.  Remember the PID of the backend
> you were connected to.  On reconnect, look in pg_stat_activity to see if
> that backend is still alive; if so, sleep till it's not.  Then check to
> see if your transaction committed or not.  No need for anything so
> dangerous as a timeout.

I didn't know you could do that!  I'll look into it.  Thanks.


Jeroen


Sat, 18 Sep 2004 18:49:47 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2
On Sat, Sep 18, 2004 at 12:43:05PM -0400, Tom Lane wrote:
 
> I don't see any reason for guesswork.  Remember the PID of the backend
> you were connected to.  On reconnect, look in pg_stat_activity to see if
> that backend is still alive; if so, sleep till it's not.  Then check to
> see if your transaction committed or not.  No need for anything so
> dangerous as a timeout.

Looks like that only works if stats_command_string is set (and backend
version is at least 7.2), correct?  I couldn't find this table in the
online documentation, but can I assume that the query will have finished
executing (whether for good or for bad) when its current_query is either
empty or null?


Jeroen


Sat, 18 Sep 2004 19:26:03 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> On Sat, Sep 18, 2004 at 12:43:05PM -0400, Tom Lane wrote:
>> I don't see any reason for guesswork.  Remember the PID of the backend
>> you were connected to.  On reconnect, look in pg_stat_activity to see if
>> that backend is still alive; if so, sleep till it's not.

> Looks like that only works if stats_command_string is set (and backend
> version is at least 7.2), correct?

No, stats_command_string need not be set, only stats_start_collector.

> I couldn't find this table in the
> online documentation,

http://www.postgresql.org/docs/7.4/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE

> but can I assume that the query will have finished
> executing (whether for good or for bad) when its current_query is either
> empty or null?

No, because of the reporting delay.  I would recommend waiting for the
backend's row in pg_stat_activity to disappear entirely.  Under normal
circumstances that should occur quickly.  If there's a communications
problem, it might take awhile to detect connection loss ... but if
there's a communications problem, you probably aren't going to be able
to open a new connection, either.

			regards, tom lane

Sat, 18 Sep 2004 14:32:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2 
On Sat, Sep 18, 2004 at 02:32:32PM -0400, Tom Lane wrote:
 
> No, because of the reporting delay.  I would recommend waiting for the
> backend's row in pg_stat_activity to disappear entirely.  Under normal
> circumstances that should occur quickly.  If there's a communications
> problem, it might take awhile to detect connection loss ... but if
> there's a communications problem, you probably aren't going to be able
> to open a new connection, either.

Unfortunately, a communications problem is exactly the kind of scenario
we were talking about in the first place!  Might be a misguided firewall,
for instance.  (In which case we'd want the TCP connection to time out
quickly on the server as well to avoid piling up dead backends, but that's
another matter).

BTW is there really no other way to see if a query (for the same user who's
looking) is still executing?  It'd be really helpful if the check could be
done from the same client process that lost the connection in the first
place.


Jeroen


Sat, 18 Sep 2004 20:46:01 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2
On Sat, Sep 18, 2004 at 02:32:32PM -0400, Tom Lane wrote:
 
> No, stats_command_string need not be set, only stats_start_collector.

BTW, I've got this set (I'm even running as "postgres") but still I get
the "<command string not enabled>" message instead of current_query.  :(


Jeroen


Sat, 18 Sep 2004 20:49:58 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> On Sat, Sep 18, 2004 at 02:32:32PM -0400, Tom Lane wrote:
>> No, stats_command_string need not be set, only stats_start_collector.

> BTW, I've got this set (I'm even running as "postgres") but still I get
> the "<command string not enabled>" message instead of current_query.  :(

It has to be set in the backend you want the info from ...

			regards, tom lane

Sat, 18 Sep 2004 15:41:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2 
On Sat, Sep 18, 2004 at 03:41:24PM -0400, Tom Lane wrote:
> >> No, stats_command_string need not be set, only stats_start_collector.
> 
> > BTW, I've got this set (I'm even running as "postgres") but still I get
> > the "<command string not enabled>" message instead of current_query.  :(
> 
> It has to be set in the backend you want the info from ...

I set it in /etc/postgresql/postgresql.conf; then I restarted the postmaster
just to be sure, and checked from psql.  Same message, even about the same
session:

jtv@bulletproof:~$ sudo su postgres -c 'postgresql-8.0/bin/psql template1'
Password:
Welcome to psql 8.0.0beta1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# select * from pg_stat_activity;
 datid |  datname  | procpid | usesysid | usename  |        current_query         | query_start 
-------+-----------+---------+----------+----------+------------------------------+-------------
     1 | template1 |   11559 |        1 | postgres | <command string not enabled> | 
(1 row)

template1=# show stats_start_collector;
 stats_start_collector 
-----------------------
 on
(1 row)



Jeroen


Sat, 18 Sep 2004 21:50:56 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> On Sat, Sep 18, 2004 at 02:32:32PM -0400, Tom Lane wrote:
>> If there's a communications
>> problem, it might take awhile to detect connection loss ... but if
>> there's a communications problem, you probably aren't going to be able
>> to open a new connection, either.

> Unfortunately, a communications problem is exactly the kind of scenario
> we were talking about in the first place!  Might be a misguided firewall,
> for instance.  (In which case we'd want the TCP connection to time out
> quickly on the server as well to avoid piling up dead backends, but that's
> another matter).

Well, I think it would time out quickly --- anyway on the order of
minutes not hours.  By hypothesis, the situation you're worried about is
where the backend was unable to send you a COMMIT acknowledgement
message.  The kernel is going to realize that it didn't get an ACK back,
and is going to retry a few times, and is then going to declare the
connection lost.  The case where you may have a very long delay before
detection of connection loss is where the backend is sitting idle with
nothing to send.

			regards, tom lane

Sat, 18 Sep 2004 16:05:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2 
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> On Sat, Sep 18, 2004 at 03:41:24PM -0400, Tom Lane wrote:
>> No, stats_command_string need not be set, only stats_start_collector.
>> 
> BTW, I've got this set (I'm even running as "postgres") but still I get
> the "<command string not enabled>" message instead of current_query.  :(

Oh, we're talking at cross-purposes.  I was saying that you only need
stats_start_collector in order to have a row in pg_stat_activity
indicating that the backend is still there.  If you're going to insist
on examining the query column rather than just the PID, then yes you
need stats_command_string.

			regards, tom lane

Sat, 18 Sep 2004 16:09:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2 
On Sat, Sep 18, 2004 at 04:05:26PM -0400, Tom Lane wrote:
 
> Well, I think it would time out quickly --- anyway on the order of
> minutes not hours.  By hypothesis, the situation you're worried about is
> where the backend was unable to send you a COMMIT acknowledgement
> message.  The kernel is going to realize that it didn't get an ACK back,
> and is going to retry a few times, and is then going to declare the
> connection lost.  The case where you may have a very long delay before
> detection of connection loss is where the backend is sitting idle with
> nothing to send.

That's one load off my mind, thanks.


Jeroen


Sat, 18 Sep 2004 22:13:29 +0200
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2

Interesting.  So it times out a transaction to release its locks.  Does
it time from the start of the transaction?  I assume so.  We do have
statment_timeout so maybe transaction_timeout would be the proper name.
Timing out on just the idle state seems strange to me.

---------------------------------------------------------------------------

Szima G�bor wrote:
> 
> Hi all,
> 
> I was implement the "transaction idle timeout" function in PostgreSQL
> (version 7.4.5 and 8.0.0beta2)
> 
> test=# SET trans_idle_timeout=10;
> SET
> test=# BEGIN;
> BEGIN
> 
> (ps)
> ...
> postgres: sygma test [local] idle in transaction (3)
> postgres: sygma test [local] idle in transaction (2)
> postgres: sygma test [local] idle in transaction (1)
> postgres: sygma test [local] idle in transaction (aborted)
> 
> test=# INSERT INTO test (a,b) VALUES (1,'a');
> ERROR:  current transaction is aborted, commands ignored until end of transaction block
> 
> 
> The code is very simple and stable.
> 
> Changed files:
> 
> include/storage/proc.h
> backend/libpq/pqcomm.c
> backend/libpq/be-secure.c
> backend/utils/misc/guc.c
> 
> TODO: SSL connection, manual, psql help & tab-competition
> 
> Do you interest it?
> 
> 
> Regards, Sygma
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 20 Sep 2004 08:30:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2
Bruce Momjian wrote:
> Interesting.  So it times out a transaction to release its locks.  Does
> it time from the start of the transaction?  I assume so.  We do have
> statment_timeout so maybe transaction_timeout would be the proper name.
> Timing out on just the idle state seems strange to me.

Timing out on the start of the transaction isn't a good idea; what about 
long running transactions? You'd have to set the timeout very high (lets 
say to have nightly imports) which would make the "idle in transaction 
and not responding any more" errors detected very late in online situations.

So the timeout should start after each statement's completion.

Regards,
Andreas

Mon, 20 Sep 2004 12:41:41 +0000
Andreas Pflug <pgadmin@pse-consulting.de>
Re: transaction idle timeout in 7.4.5 and 8.0.0beta2