Thread: BUG #5107: Lock never released

The following bug has been logged online:

Bug reference:      5107
Logged by:          Christian DUPONT
Email address:      christian.dupont@cegelec.com
PostgreSQL version: 8.3
Operating system:   RHEL 5
Description:        Lock never released
Details:

Hi,

I use slony 1 v 1.2.14.

After an unexpected stop, several tables remained locked :

sl_log_1 -> RowExclusiveLock,
sl_log_status -> AccessShareLock,
sl_action_seq -> AccessShareLock,
h_jou_pmvdata -> RowExclusiveLock (data table).

This has happened a couple of times, even though I don't have a way to
reproduce it. A database restart does not help. Because of these locks, I
can't get rid (truncate / delete) of the tables nor even dump the db.

Only solution : destroy and rebuild from the master database (it is
replicated after all).

I would be glad to send any information needed to investigate.

"Christian DUPONT" <christian.dupont@cegelec.com> writes:
> I use slony 1 v 1.2.14.
> After an unexpected stop, several tables remained locked :

Is it possible that the locks are being held by a prepared transaction?
Next time it happens, look into the pg_prepared_xacts status view.

If it's not that, it seems like this must be a Slony issue, not a
problem with Postgres proper.  You'll probably get better results
if you ask about it on the Slony mailing lists.

            regards, tom lane

tgl@sss.pgh.pa.us (Tom Lane) writes:
> "Christian DUPONT" <christian.dupont@cegelec.com> writes:
>> I use slony 1 v 1.2.14.
>> After an unexpected stop, several tables remained locked :
>
> Is it possible that the locks are being held by a prepared transaction?
> Next time it happens, look into the pg_prepared_xacts status view.

The prepared statement idea seems pretty plausible; certainly it's
something that can survive a backend restart.

> If it's not that, it seems like this must be a Slony issue, not a
> problem with Postgres proper.  You'll probably get better results
> if you ask about it on the Slony mailing lists.

I can't think of anything offhand which would seize a lock so
tenaciously in Slony, except, evidently with a prepared statement as
an accomplice.
--
select 'cbbrowne' || '@' || 'gmail.com';
http://linuxdatabases.info/info/emacs.html
"I really only meant to point out how nice InterOp was for someone who
doesn't  have the weight of the  Pentagon behind him.   I really don't
imagine that the Air Force will ever be  able to operate like a small,
competitive enterprise like GM or IBM." -- Kent England