Hanging locks?

Kresimir Tonkovic wrote:

I have a situation regarding locks that nobody seems to own:

using psql:

Chipoteka=> select pg_class.relname,pg_locks.* from pg_class,pg_locks
where pg_class.relfilenode=pg_locks.relation;
relname | locktype | database | relation | page | tuple |
transactionid | classid | objid | objsubid | transaction | pid |
mode | granted
--------------+----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+-----------------+---------

valuta | relation | 366513 | 366657 | |
| | | | | 4518231 | |
AccessShareLock | t
jezik | relation | 366513 | 366567 | |
| | | | | 4202257 | |
AccessShareLock | t
jezik | relation | 366513 | 366567 | |
| | | | | 4518231 | |
AccessShareLock | t
serverconfig | relation | 366513 | 375491 | |
| | | | | 4518231 | |
AccessShareLock | t
mjesto | relation | 366513 | 366584 | |
| | | | | 4518231 | |
AccessShareLock | t
drzava | relation | 366513 | 366550 | |
| | | | | 4518231 | |
AccessShareLock | t
orgjed | relation | 366513 | 366596 | |
| | | | | 4202257 | |
AccessShareLock | t
serverconfig | relation | 366513 | 375491 | |
| | | | | 4202257 | |
AccessShareLock | t
serverconfig | relation | 366513 | 375491 | |
| | | | | 4518229 | |
AccessShareLock | t
drzava | relation | 366513 | 366550 | |
| | | | | 4202257 | |
AccessShareLock | t
valuta | relation | 366513 | 366657 | |
| | | | | 4518229 | |
AccessShareLock | t
jezik | relation | 366513 | 366567 | |
| | | | | 4518229 | |
AccessShareLock | t
valuta | relation | 366513 | 366657 | |
| | | | | 4202257 | |
AccessShareLock | t
pg_class | relation | 366513 | 1259 | |
| | | | | 4658945 | 5709 |
AccessShareLock | t
orgjed | relation | 366513 | 366596 | |
| | | | | 4518231 | |
AccessShareLock | t
pg_locks | relation | 366513 | 10342 | |
| | | | | 4658945 | 5709 |
AccessShareLock | t
drzava | relation | 366513 | 366550 | |
| | | | | 4518229 | |
AccessShareLock | t
mjesto | relation | 366513 | 366584 | |
| | | | | 4202257 | |
AccessShareLock | t
orgjed | relation | 366513 | 366596 | |
| | | | | 4518229 | |
AccessShareLock | t
mjesto | relation | 366513 | 366584 | |
| | | | | 4518229 | |
AccessShareLock | t
(20 rows)

from the shell:

kresot@kreso:~$ ps auxw|grep post
postgres 1388 0.0 0.4 151980 6304 ? S 09:16 0:00
/usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main
-c unix_socket_directory=/var/run/postgresql -c
config_file=/etc/postgresql/8.1/main/postgresql.conf -c
hba_file=/etc/postgresql/8.1/main/pg_hba.conf -c
ident_file=/etc/postgresql/8.1/main/pg_ident.conf -c
external_pid_file=/var/run/postgresql/8.1-main.pid
postgres 1390 0.0 5.7 152128 89944 ? S 09:16 0:00
postgres: writer process postgres 1391 0.0 0.1 10728 1884
? S 09:16 0:00 postgres: stats buffer process postgres
1392 0.0 0.0 10016 1424 ? S 09:16 0:00 postgres: stats
collector process kresot 5699 0.0 0.0 3224 636 pts/0 S+
11:11 0:00 grep post

The situation is the same after I restart postgres.

My environment: postgres 8.1.4 on debian.

Is this a bug, or am I doing something wrong?

To clarify,

This is a problem for me because I'm trying to drop this database, but
dropdb complains about other users using it. I suppose these locks are
what prevent dropdb from doing it's work.

Best regards,

--
Krešimir Tonković
Z-el d.o.o.
Industrijska cesta 28, 10360 Sesvete, Croatia
Tel: +385 1 2022 758
Fax: +385 1 2022 741
Web: www.chipoteka.hr
e-mail: z-el.tonkovic@chipoteka.hr

Kresimir Tonkovic <z-el.tonkovic@chipoteka.hr> writes:

I have a situation regarding locks that nobody seems to own:

The only way pid can be null in a pg_locks entry is if the lock is held
by a prepared transaction. See pg_prepared_xacts view.

regards, tom lane

Tom Lane wrote:

Kresimir Tonkovic <z-el.tonkovic@chipoteka.hr> writes:

I have a situation regarding locks that nobody seems to own:

The only way pid can be null in a pg_locks entry is if the lock is held
by a prepared transaction. See pg_prepared_xacts view.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

So i did
Chipoteka=> select * from pg_prepared_xacts;
transaction | gid | prepared
| owner | database
-------------+---------------------------+-------------------------------+-------+-----------
4202257 | 257_a3Jlc28vMTg4NDQ4_Mg== | 2006-10-20 12:22:08.72175+02
| jboss | Chipoteka
4518229 | 257_a3Jlc28vNTY3MzE3_Mg== | 2006-10-20 16:53:32.002687+02
| jboss | Chipoteka
4518231 | 257_a3Jlc28vNTY3MzI2_Mg== | 2006-10-20 16:53:32.036318+02
| jboss | Chipoteka

and then:

Chipoteka=> commit prepared '257_a3Jlc28vMTg4NDQ4_Mg==';
COMMIT PREPARED
Chipoteka=> commit prepared '257_a3Jlc28vNTY3MzE3_Mg==';
COMMIT PREPARED
Chipoteka=> commit prepared '257_a3Jlc28vNTY3MzI2_Mg==';
COMMIT PREPARED

and I'm free! :-)

Thanks!

--
Krešimir Tonković
Z-el d.o.o.
Industrijska cesta 28, 10360 Sesvete, Croatia
Tel: +385 1 2022 758
Fax: +385 1 2022 741
Web: www.chipoteka.hr
e-mail: z-el.tonkovic@chipoteka.hr