Re: Problems Vacuum'ing

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> jseymour(at)LinxNet(dot)com (Jim Seymour) writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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