> 
> On Fri, Apr 02, 2004 at 12:02:22PM -0500, Jim Seymour wrote:
> 
> [...]
> 
> > Which is all well-and-good (tho, my ignorance readily conceded, four
> > minutes seems a mite... long), *except*: If I shut-down the
> > WebObjects application which, again, never accesses the db in
> > question, much-less any of its tables, this "time-out" doesn't seem
> > to apply.  (I tried it.)
> 
> Ok, so the WebObjects app keeps an idle open transaction?  (i.e. it
> issues a BEGIN as soon as the previous transaction is finished.) I'm not
> sure I read the code correctly --

I really couldn't say.  I don't know what the WebObjects app is
doing.  I know, or *believe* I know, it's only doing queries.  (It
may be doing temp tables internally, or some-such.)  Its interface
to pgsql is via the JDBC that comes with pgsql.  I don't know what
the Java code it generated looks like.

>                                   ISTM it would only matter when you try
> to vacuum a shared table, which this is not ...

That's what I would've thought.

Thanks for the follow-up.  I was beginning to wonder if anybody'd
noticed ;).

Jim


Fri,  2 Apr 2004 14:51:30 -0500 (EST)
jseymour@LinxNet.com (Jim Seymour)
Re: Problems Vacuum'ing
On Fri, Apr 02, 2004 at 02:51:30PM -0500, Jim Seymour wrote:
> > 
> > On Fri, Apr 02, 2004 at 12:02:22PM -0500, Jim Seymour wrote:

> > Ok, so the WebObjects app keeps an idle open transaction?  (i.e. it
> > issues a BEGIN as soon as the previous transaction is finished.) I'm not
> > sure I read the code correctly --
> 
> I really couldn't say.  I don't know what the WebObjects app is
> doing.  I know, or *believe* I know, it's only doing queries.  (It
> may be doing temp tables internally, or some-such.)  Its interface
> to pgsql is via the JDBC that comes with pgsql.  I don't know what
> the Java code it generated looks like.

Turn on query logging and see if the BEGIN is issued right after the
COMMIT/ROLLBACK, or whether it waits and issues it right before
SELECT/CREATE TEMP TABLE.

It doesn't matter if it's only doing queries; if it does them inside a
transaction, it would be enough to keep VACUUM from working "properly."

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La gente vulgar solo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"

Fri, 2 Apr 2004 16:35:03 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Problems Vacuum'ing

Alvaro Herrera <alvherre@dcc.uchile.cl> wrote:
> 
[snip]
> 
> Turn on query logging and see if the BEGIN is issued right after the
> COMMIT/ROLLBACK, or whether it waits and issues it right before
> SELECT/CREATE TEMP TABLE.
> 
> It doesn't matter if it's only doing queries; if it does them inside a
> transaction, it would be enough to keep VACUUM from working "properly."

Will the following do as well?

(Thanks to Jochem for the pointer for how to determine open
transactions and the pg_stat_activity hint.)

Logged into work.  WebObects application is running.  Database I'm
working with partly populated from earlier work.

postgres=# select * from pg_locks where transaction is not null;
 relation | database | transaction |  pid  |     mode      | granted 
----------+----------+-------------+-------+---------------+---------
          |          |     1245358 | 18020 | ExclusiveLock | t
          |          |     1245364 |   267 | ExclusiveLock | t
(2 rows)

postgres=# select * from pg_stat_activity;
 datid | datname  | procpid | usesysid |  usename   | current_query | query_start 
-------+----------+---------+----------+------------+---------------+-------------
 17142 | postgres |     267 |        1 | postgres   |               | 
 17144 | qantel   |   18020 |      103 | webobjects |               | 
(2 rows)


sysagent=> delete from ethers;
DELETE 368
sysagent=> delete from host_mac_hist;
DELETE 169
sysagent=> vacuum full analyze verbose ethers;
INFO:  vacuuming "public.ethers"
INFO:  "ethers": found 0 removable, 368 nonremovable row versions in 4 pages
DETAIL:  368 dead row versions cannot be removed yet.
Nonremovable row versions range from 64 to 88 bytes long.
There were 55 unused item pointers.
Total free space (including removable row versions) is 3724 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 3628 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "ethers_hostname_key" now contains 368 row versions in 275 pages
DETAIL:  0 index row versions were removed.
223 index pages have been deleted, 223 are currently reusable.
CPU 0.01s/0.03u sec elapsed 0.23 sec.
INFO:  "ethers": moved 0 row versions, truncated 4 to 4 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.ethers"
INFO:  "ethers": 4 pages, 0 rows sampled, 0 estimated total rows
VACUUM
sysagent=> vacuum full analyze verbose host_mac_hist;
INFO:  vacuuming "public.host_mac_hist"
INFO:  "host_mac_hist": found 0 removable, 169 nonremovable row versions in 2 pages
DETAIL:  169 dead row versions cannot be removed yet.
Nonremovable row versions range from 64 to 80 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 3556 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 3532 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "host_mac_hist": moved 0 row versions, truncated 2 to 2 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.host_mac_hist"
INFO:  "host_mac_hist": 2 pages, 0 rows sampled, 0 estimated total rows
VACUUM

Shut down WebObjects.  Clear both tables.  Do full vacuum.
Re-populate tables.  Then...

postgres=# select * from pg_locks where transaction is not null;
 relation | database | transaction | pid  |     mode      | granted 
----------+----------+-------------+------+---------------+---------
          |          |     1245558 | 3110 | ExclusiveLock | t
(1 row)

postgres=# select * from pg_stat_activity;
 datid | datname  | procpid | usesysid | usename  | current_query | query_start 
-------+----------+---------+----------+----------+---------------+-------------
 17142 | postgres |    3110 |        1 | postgres |               | 
(1 row)

sysagent=> delete from ethers;
DELETE 368
sysagent=> delete from host_mac_hist;
DELETE 169
sysagent=> vacuum full analyze verbose ethers;
INFO:  vacuuming "public.ethers"
INFO:  "ethers": found 10030 removable, 0 nonremovable row versions in 98 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 760736 bytes.
98 pages are or will become empty, including 98 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.01s/0.01u sec elapsed 0.01 sec.
INFO:  index "ethers_hostname_key" now contains 0 row versions in 275 pages
DETAIL:  10030 index row versions were removed.
271 index pages have been deleted, 271 are currently reusable.
CPU 0.00s/0.08u sec elapsed 0.08 sec.
INFO:  "ethers": truncated 98 to 0 pages
INFO:  analyzing "public.ethers"
INFO:  "ethers": 0 pages, 0 rows sampled, 0 estimated total rows
VACUUM
sysagent=> vacuum full analyze verbose host_mac_hist;
INFO:  vacuuming "public.host_mac_hist"
INFO:  "host_mac_hist": found 169 removable, 0 nonremovable row versions in 2 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 15668 bytes.
2 pages are or will become empty, including 2 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "host_mac_hist": truncated 2 to 0 pages
INFO:  analyzing "public.host_mac_hist"
INFO:  "host_mac_hist": 0 pages, 0 rows sampled, 0 estimated total rows
VACUUM

Again the difference: With WebObjects running, deleting rows and
trying to vacuum immediately, even full, fails.  Shut-down WebObjects
and I can.

Next test? ;)

Jim

Fri,  2 Apr 2004 19:08:54 -0500 (EST)
jseymour@LinxNet.com (Jim Seymour)
Re: Problems Vacuum'ing
jseymour@LinxNet.com (Jim Seymour) writes:
> Again the difference: With WebObjects running, deleting rows and
> trying to vacuum immediately, even full, fails.  Shut-down WebObjects
> and I can.

WebObjects is evidently holding an open transaction.  Ergo, anything
deleted after the start of that transaction isn't vacuumable.  You need
to do something about the client-side logic that is holding an open
transaction without doing anything ...

			regards, tom lane

Fri, 02 Apr 2004 19:35:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problems Vacuum'ing 

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> jseymour@LinxNet.com (Jim Seymour) writes:
> > Again the difference: With WebObjects running, deleting rows and
> > trying to vacuum immediately, even full, fails.  Shut-down WebObjects
> > and I can.
> 
> WebObjects is evidently holding an open transaction.  Ergo, anything
> deleted after the start of that transaction isn't vacuumable.  You need
> to do something about the client-side logic that is holding an open
> transaction without doing anything ...

It certainly isn't holding open a transaction in the database I'm
working with.  It's unclear to me it's holding any transaction open,
anywhere.  This is all that showed up:

postgres=# select * from pg_locks where transaction is not null;
 relation | database | transaction |  pid  |     mode      | granted 
----------+----------+-------------+-------+---------------+---------
          |          |     1245358 | 18020 | ExclusiveLock | t
          |          |     1245364 |   267 | ExclusiveLock | t
(2 rows)

postgres=# select * from pg_stat_activity;
 datid | datname  | procpid | usesysid |  usename   | current_query | query_start 
-------+----------+---------+----------+------------+---------------+-------------
 17142 | postgres |     267 |        1 | postgres   |               | 
 17144 | qantel   |   18020 |      103 | webobjects |               | 
(2 rows)

I don't know what those are, but they list no database or relation.  I
get this just be running psql (this time at home):

jseymour=> select * from pg_locks where transaction is not null;
 relation | database | transaction | pid |     mode      | granted 
----------+----------+-------------+-----+---------------+---------
          |          |        8938 | 307 | ExclusiveLock | t
(1 row)

jseymour=> select * from pg_stat_activity;
 datid | datname  | procpid | usesysid | usename  | current_query | query_start 
-------+----------+---------+----------+----------+---------------+-------------
 17144 | jseymour |     307 |      101 | jseymour |               | 
(1 row)

Without having touched a thing.

Jim

Fri,  2 Apr 2004 19:52:45 -0500 (EST)
jseymour@LinxNet.com (Jim Seymour)
Re: Problems Vacuum'ing
jseymour@LinxNet.com (Jim Seymour) writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> WebObjects is evidently holding an open transaction.

> It certainly isn't holding open a transaction in the database I'm
> working with.

Which database the transaction is in isn't real relevant... the logic is
done globally so that it will be correct when vacuuming shared tables.

> It's unclear to me it's holding any transaction open,
> anywhere.

Sure it is, assuming that PID 18020 is the session we're talking about.

> postgres=# select * from pg_locks where transaction is not null;
>  relation | database | transaction |  pid  |     mode      | granted 
> ----------+----------+-------------+-------+---------------+---------
>           |          |     1245358 | 18020 | ExclusiveLock | t
                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

This process has an open transaction number 1245358.  That's what an
exclusive lock on a transaction means.

>  17142 | postgres |     267 |        1 | postgres   |               | 
>  17144 | qantel   |   18020 |      103 | webobjects |               | 

These entries didn't make a lot of sense to me since the other examples
you mentioned did not seem to be getting executed in the 'postgres'
database --- but I assume PID 18020 is the one you are referring to as
webobjects.

			regards, tom lane

Fri, 02 Apr 2004 20:14:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problems Vacuum'ing 
On Fri, Apr 02, 2004 at 07:35:20PM -0500, Tom Lane wrote:
> jseymour@LinxNet.com (Jim Seymour) writes:
> > Again the difference: With WebObjects running, deleting rows and
> > trying to vacuum immediately, even full, fails.  Shut-down WebObjects
> > and I can.
> 
> WebObjects is evidently holding an open transaction.  Ergo, anything
> deleted after the start of that transaction isn't vacuumable.  You need
> to do something about the client-side logic that is holding an open
> transaction without doing anything ...

But, if I read the code correctly, the oldest xmin vacuum cares about
for a non-shared relation should be local to the database, shouldn't it?
If this is so, why does it matter that he has open transaction on a
different database?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
www.google.com: interfaz de lï¿½nea de comando para la web.

Fri, 2 Apr 2004 21:44:07 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Problems Vacuum'ing

On Fri, 2 Apr 2004, Alvaro Herrera wrote:

> On Fri, Apr 02, 2004 at 07:35:20PM -0500, Tom Lane wrote:
> > jseymour@LinxNet.com (Jim Seymour) writes:
> > > Again the difference: With WebObjects running, deleting rows and
> > > trying to vacuum immediately, even full, fails.  Shut-down WebObjects
> > > and I can.
> >
> > WebObjects is evidently holding an open transaction.  Ergo, anything
> > deleted after the start of that transaction isn't vacuumable.  You need
> > to do something about the client-side logic that is holding an open
> > transaction without doing anything ...
>
> But, if I read the code correctly, the oldest xmin vacuum cares about
> for a non-shared relation should be local to the database, shouldn't it?

AFAICS it's the oldest transaction at the start of any of the transactions
in this database, not the oldest transaction of any transaction in this
database.

Fri, 2 Apr 2004 18:45:28 -0800 (PST)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: Problems Vacuum'ing

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> jseymour@LinxNet.com (Jim Seymour) writes:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> WebObjects is evidently holding an open transaction.
> 
> > It certainly isn't holding open a transaction in the database I'm
> > working with.
> 
> Which database the transaction is in isn't real relevant... the logic is
> done globally so that it will be correct when vacuuming shared tables.

It had occurred to me, early on, that if anything had an open
transaction, that would perhaps cause what I was seeing.  So I
killed-off WebObjects.  Ran my tests.  Psql'd as yet another user,
to another database, and did something like

    begin;
    insert into foo (bar) values ('Hello');

And then ran my tests.  Vacuum'ing worked completely.

> 
> > It's unclear to me it's holding any transaction open,
> > anywhere.
> 
> Sure it is, assuming that PID 18020 is the session we're talking about.
> 
> > postgres=# select * from pg_locks where transaction is not null;
> >  relation | database | transaction |  pid  |     mode      | granted 
> > ----------+----------+-------------+-------+---------------+---------
> >           |          |     1245358 | 18020 | ExclusiveLock | t
>                              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

But I see entries like that if I just *start* *up* psql, without
doing anything:

Script started on Fri 02 Apr 2004 09:42:58 PM EST
$ psql
Password: 
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

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

jseymour=> select * from pg_locks where transaction is not null;
 relation | database | transaction | pid  |     mode      | granted 
----------+----------+-------------+------+---------------+---------
          |          |        8941 | 1480 | ExclusiveLock | t
(1 row)

jseymour=> select * from pg_stat_activity;
 datid | datname  | procpid | usesysid | usename  | current_query | query_start 
-------+----------+---------+----------+----------+---------------+-------------
 17144 | jseymour |    1480 |      101 | jseymour |               | 
(1 row)

jseymour=> \q
$ exit

script done on Fri 02 Apr 2004 09:43:27 PM EST

What does that entry for pid 1480, transaction 8941 mean?

> 
> This process has an open transaction number 1245358.  That's what an
> exclusive lock on a transaction means.
> 
> >  17142 | postgres |     267 |        1 | postgres   |               | 
> >  17144 | qantel   |   18020 |      103 | webobjects |               | 
> 
> These entries didn't make a lot of sense to me since the other examples
> you mentioned did not seem to be getting executed in the 'postgres'
> database --- but I assume PID 18020 is the one you are referring to as
> webobjects.

I ran the pg_locks and pg_stat_activity selects as user postgres.  The
postgres db has nothing to do with either the WebObjects application
nor the script that's been populating the db I've been experimenting
with.

The point there was to show that the WebObjects application had nothing
open other than whatever it is seems to be there when anything connects
to a database (?) with psql (?).

Regards,
Jim

Fri,  2 Apr 2004 21:49:50 -0500 (EST)
jseymour@LinxNet.com (Jim Seymour)
Re: Problems Vacuum'ing
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> But, if I read the code correctly, the oldest xmin vacuum cares about
> for a non-shared relation should be local to the database, shouldn't it?

It's the oldest xmin of any transaction that's local to your database,
but those xmin values themselves were computed globally --- so what
matters is the oldest transaction that was running when any local
transaction started.  In this case I expect it's the VACUUM's own
transaction that's seeing the other guy as determining its xmin.

We could fix this by making every transaction compute, and advertise in
the PGPROC array, both local and global xmin values.  In previous
iterations of this discussion we concluded that the extra cycles (which
would be spent in *every* transaction start) could not be justified by
making VACUUM better able to reclaim space in the face of misbehaving
clients.  That conclusion might be wrong, but it's not instantly obvious
that it is...

			regards, tom lane

Fri, 02 Apr 2004 22:21:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problems Vacuum'ing 
jseymour@LinxNet.com (Jim Seymour) writes:
> But I see entries like that if I just *start* *up* psql, without
> doing anything:

Sure.  You are "doing something" when you execute "select from
pg_locks" ... that command executes inside a transaction, just
like any other Postgres operation.  The problem you're facing
is that WebObjects is creating a transaction that persists for
a long period of time.

			regards, tom lane

Fri, 02 Apr 2004 22:58:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problems Vacuum'ing 