Re: transaction timeout

> You misunderstood his point. In PostgreSQL
> parlance, a "cluster" is a
> single postmaster running on a single machine, with
> 1 or more
> databases. So, what he wanted to know was, if your
> application is
> hitting a database called fred, and you have a spare
> database named
> wilma, would "psql wilma" work when the database is
> "locked up?"

ok, I see. That's another thing to try next time.

> Can you elaborate on what you mean by a database
> that is "locked up?"

Here is the ps output from before the offending
process was killed. That one "idle in transaction"
process caused everything to lock.

2005 07 25 6:07:34 s17 79 > ps -efwww | grep
postgres
postgres 23281 1 0 Jul18 ? 00:00:29
/usr/bin/postmaster -p 5432
postgres 23285 23281 0 Jul18 ? 00:20:20
postgres: stats buffer process
postgres 23287 23285 0 Jul18 ? 00:18:08
postgres: stats collector process
postgres 12466 23281 0 Jul18 ? 00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 12474 23281 0 Jul18 ? 00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 26947 23281 0 Jul19 ? 00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 3514 23281 0 Jul19 ? 00:00:00
postgres: siam siam_production 172.16.11.50 idle
postgres 6881 23281 0 Jul19 ? 00:00:01
postgres: siam siam_production 172.16.11.71 idle
postgres 17750 23281 0 Jul20 ? 00:00:00
postgres: siam siam_production 172.16.10.159 idle in
transaction
postgres 26504 23281 0 Jul20 ? 00:00:00
postgres: siam siam_production 172.16.11.50 idle
postgres 12284 23281 0 Jul20 ? 00:00:00
postgres: siam siam_production 172.16.10.125 idle
postgres 16026 23281 0 Jul20 ? 00:00:00
postgres: siam siam_production 172.16.10.125 idle
postgres 25709 23281 0 Jul21 ? 00:01:00
postgres: siam siam_production 172.16.1.17 idle
postgres 27980 23281 0 Jul21 ? 00:04:08
postgres: siam siam_production 172.16.1.17 idle
postgres 14854 23281 0 Jul21 ? 00:00:03
postgres: siam siam_production 172.16.11.95 idle
postgres 19531 23281 0 Jul21 ? 00:00:02
postgres: siam siam_production 172.16.11.95 idle
postgres 17590 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 26917 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26933 23281 0 Jul22 ? 00:06:57
postgres: siam siam_production 172.16.1.17 idle
postgres 26934 23281 0 Jul22 ? 00:00:56
postgres: siam siam_production 172.16.1.17 idle
postgres 26939 23281 0 Jul22 ? 00:01:09
postgres: siam siam_production 172.16.1.17 idle
postgres 27362 23281 0 Jul22 ? 00:01:56
postgres: siam siam_production 172.16.1.17 idle
postgres 27365 23281 0 Jul22 ? 00:01:03
postgres: siam siam_production 172.16.1.17 idle
postgres 27398 23281 0 Jul22 ? 00:00:26
postgres: siam siam_production 172.16.1.17 idle
postgres 27856 23281 0 Jul22 ? 00:01:00
postgres: siam siam_production 172.16.1.17 idle
postgres 27858 23281 0 Jul22 ? 00:05:26
postgres: siam siam_production 172.16.1.17 idle
postgres 27863 23281 0 Jul22 ? 00:00:58
postgres: siam siam_production 172.16.1.17 idle
postgres 27865 23281 0 Jul22 ? 00:01:28
postgres: siam siam_production 172.16.1.17 idle
postgres 27869 23281 0 Jul22 ? 00:00:29
postgres: siam siam_production 172.16.1.17 idle
postgres 28295 23281 0 Jul22 ? 00:00:23
postgres: siam siam_production 172.16.1.17 idle
postgres 28313 23281 0 Jul22 ? 00:00:45
postgres: siam siam_production 172.16.1.17 idle
postgres 28315 23281 0 Jul22 ? 00:01:06
postgres: siam siam_production 172.16.1.17 idle
postgres 28725 23281 0 Jul22 ? 00:05:07
postgres: siam siam_production 172.16.1.17 idle
postgres 13559 23281 0 Jul22 ? 00:00:24
postgres: siam siam_production 172.16.1.17 idle
postgres 13595 23281 0 Jul22 ? 00:00:36
postgres: siam siam_production 172.16.1.17 idle
postgres 14017 23281 0 Jul22 ? 00:00:52
postgres: siam siam_production 172.16.1.17 idle
postgres 25206 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.11.146 idle
postgres 3742 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.10.142 idle
postgres 12016 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 13782 23281 0 Jul22 ? 00:00:13
postgres: siam siam_production 172.16.10.106 idle
postgres 13853 23281 0 Jul22 ? 00:00:02
postgres: siam siam_production 172.16.10.106 idle
postgres 14381 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 14923 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 17181 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.11.59 idle
postgres 6212 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production [local] VACUUM waiting
postgres 5952 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.1.17 SELECT
waiting
postgres 24644 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.100 SELECT
waiting
postgres 26271 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 26720 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 26721 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 27161 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 27162 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 28005 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 28450 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 28451 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.114 SELECT
waiting
postgres 3049 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.11.134 SELECT
waiting
postgres 3875 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.11.134 SELECT
waiting
postgres 4286 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.11.130 SELECT
waiting
postgres 4700 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.11.185 SELECT
waiting
postgres 13850 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 13851 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 13852 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 13854 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 13855 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 13856 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.145 SELECT
waiting
postgres 14268 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 14269 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 14270 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.138 SELECT
waiting
postgres 14685 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 14686 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.11.24 SELECT
waiting
postgres 15100 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.105 SELECT
waiting
postgres 15951 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.125 SELECT
waiting
postgres 16367 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.11.109 SELECT
waiting
postgres 25054 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.112 SELECT
waiting
postgres 25920 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.112 SELECT
waiting
postgres 25921 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.11.157 SELECT
waiting
postgres 25922 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.112 SELECT
waiting
postgres 26337 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.112 SELECT
waiting
postgres 26338 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.112 SELECT
waiting
postgres 10948 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.11.105 SELECT
waiting
postgres 12195 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.11.187 SELECT
waiting
postgres 12196 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.11.105 SELECT
waiting
postgres 22691 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.1.17 SELECT
waiting
postgres 23059 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.121 SELECT
waiting
postgres 15209 23281 0 04:00 ? 00:00:00
postgres: siam siam_production [local] SELECT waiting

Here is the ps output immediately after the hung
client was killed. As you can see, a whole shitload of
SELECTs suddenly woke up and finished. Unfortunately,
I don't have pg_stat_activity output.

2005 07 25 6:14:41 s17 78 > ps -efwww | grep postgres
postgres 23281 1 0 Jul18 ? 00:00:29
/usr/bin/postmaster -p 5432
postgres 23285 23281 0 Jul18 ? 00:20:21
postgres: stats buffer process
postgres 23287 23285 0 Jul18 ? 00:18:08
postgres: stats collector process
postgres 12466 23281 0 Jul18 ? 00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 12474 23281 0 Jul18 ? 00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 26947 23281 0 Jul19 ? 00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 3514 23281 0 Jul19 ? 00:00:00
postgres: siam siam_production 172.16.11.50 idle
postgres 6881 23281 0 Jul19 ? 00:00:01
postgres: siam siam_production 172.16.11.71 idle
postgres 26504 23281 0 Jul20 ? 00:00:00
postgres: siam siam_production 172.16.11.50 idle
postgres 12284 23281 0 Jul20 ? 00:00:00
postgres: siam siam_production 172.16.10.125 idle
postgres 16026 23281 0 Jul20 ? 00:00:00
postgres: siam siam_production 172.16.10.125 idle
postgres 25709 23281 0 Jul21 ? 00:01:00
postgres: siam siam_production 172.16.1.17 idle
postgres 27980 23281 0 Jul21 ? 00:04:08
postgres: siam siam_production 172.16.1.17 idle
postgres 14854 23281 0 Jul21 ? 00:00:03
postgres: siam siam_production 172.16.11.95 idle
postgres 19531 23281 0 Jul21 ? 00:00:02
postgres: siam siam_production 172.16.11.95 idle
postgres 17590 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.11.95 idle
postgres 26917 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26933 23281 0 Jul22 ? 00:06:57
postgres: siam siam_production 172.16.1.17 idle
postgres 26934 23281 0 Jul22 ? 00:00:56
postgres: siam siam_production 172.16.1.17 idle
postgres 26939 23281 0 Jul22 ? 00:01:09
postgres: siam siam_production 172.16.1.17 idle
postgres 27362 23281 0 Jul22 ? 00:01:56
postgres: siam siam_production 172.16.1.17 idle
postgres 27365 23281 0 Jul22 ? 00:01:03
postgres: siam siam_production 172.16.1.17 idle
postgres 27398 23281 0 Jul22 ? 00:00:26
postgres: siam siam_production 172.16.1.17 idle
postgres 27856 23281 0 Jul22 ? 00:01:00
postgres: siam siam_production 172.16.1.17 idle
postgres 27858 23281 0 Jul22 ? 00:05:26
postgres: siam siam_production 172.16.1.17 idle
postgres 27863 23281 0 Jul22 ? 00:00:58
postgres: siam siam_production 172.16.1.17 idle
postgres 27865 23281 0 Jul22 ? 00:01:28
postgres: siam siam_production 172.16.1.17 idle
postgres 27869 23281 0 Jul22 ? 00:00:29
postgres: siam siam_production 172.16.1.17 idle
postgres 28295 23281 0 Jul22 ? 00:00:23
postgres: siam siam_production 172.16.1.17 idle
postgres 28313 23281 0 Jul22 ? 00:00:45
postgres: siam siam_production 172.16.1.17 idle
postgres 28315 23281 0 Jul22 ? 00:01:06
postgres: siam siam_production 172.16.1.17 idle
postgres 28725 23281 0 Jul22 ? 00:05:07
postgres: siam siam_production 172.16.1.17 idle
postgres 13559 23281 0 Jul22 ? 00:00:24
postgres: siam siam_production 172.16.1.17 idle
postgres 13595 23281 0 Jul22 ? 00:00:36
postgres: siam siam_production 172.16.1.17 idle
postgres 14017 23281 0 Jul22 ? 00:00:52
postgres: siam siam_production 172.16.1.17 idle
postgres 25206 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.11.146 idle
postgres 3742 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.10.142 idle
postgres 12016 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 13782 23281 0 Jul22 ? 00:00:13
postgres: siam siam_production 172.16.10.106 idle
postgres 13853 23281 0 Jul22 ? 00:00:02
postgres: siam siam_production 172.16.10.106 idle
postgres 14381 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 14923 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.10.106 idle
postgres 17181 23281 0 Jul22 ? 00:00:00
postgres: siam siam_production 172.16.11.59 idle
postgres 6212 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production [local] VACUUM waiting
postgres 5952 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 24644 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.100 idle
postgres 26721 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.114 idle
postgres 27161 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.10.114 idle
postgres 22691 23281 0 Jul24 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 15209 23281 0 03:59 ? 00:00:10
postgres: siam siam_production [local] COPY
postgres 26975 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26976 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26977 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26978 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26979 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26980 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26981 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26982 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26983 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26984 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26985 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26986 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26987 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26988 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26989 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 idle
postgres 26990 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.1.17 SELECT
waiting
postgres 27041 23281 0 06:14 ? 00:00:00
postgres: siam siam_production 172.16.11.130 SELECT
waiting
costa 27091 26473 0 06:14 pts/0 00:00:00 grep
postgres

> No, VAcuum full shouldn't cause this kind of issue.
> Now, if the
> database is just running real slow, instead of
> actually locking up,
> that's possible with vacuum full.

no, there was *zero* load on the server.

thanks,

Eugene

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com