Database Reference
In-Depth Information
You can even schedule this to be running every minute while you are trying to find the
specific PHP frontend, which keeps leaving open transactions behind, or you have a lazy
administration leaving psql connection open, or a flaky network that drops clients without the
server noticing it.
You can also kill the backend from command line
Another possibility to terminate a backend is by using a Unix/Linux command named
kill
N
,
which sends the signal to process
N
on the system where it is running. You have to be either
the root user or the user running the database backends (usually postgres) to be able to send
signals to processes.
Resolving an in-doubt prepared transaction
When using 2PC (two phase commit), you may end up in a situation where you kind of have
something locked, but cannot find a backend that holds the locks.
For example:
db=# select t.schemaname || '.' || t.relname as tablename,
db-# l.pid, l.granted
db-# from pg_locks l join pg_stat_user_tables t
db-# on l.relation = t.relid;
tablename | pid | granted
-----------+-------+---------
db.x | | t
db.x | 27289 | f
(2 rows)
has a lock on table
db.x
, which has no process associated with it.
Getting ready
Look at the recipe on Removing old prepared transactions in Chapter 9 Regular Maintenance
Is anybody using a specific table?
This one helps you when you are in doubt if some obscure table is used any more, or is it just
leftover from old times that just takes up space.
Getting ready
Make sure that you are a superuser, or at least have full rights on the table in question.
