SEO

Hi,

we are spotting constant deadlocks when altering tables. After I
restart the db the ALTER TABLE command runs without problems, but
when I try to alter some other table later on it deadlocks again. If I
kill the process that handles ALTER TABLE the deadlock is "unlocked",
but ALTER TABLE still won't run until I restart it.

I've checked the logs, but didn't find anything useful. How can I get
some more information on what is happening when the server deadlocks?

Thanks, Sebastjan

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Sun, 20 Jan 2008, Sebastjan Trepca wrote:

> we are spotting constant deadlocks when altering tables.

See http://www.postgresql.org/docs/8.2/i...t-locking.html
for information about the ACCESS EXCLUSIVE lock that ALTER TABLE takes.
Anything that is doing that needs to be in as short of a transaction as
possible, and it sounds like you have transactions that are lingering
around for way too long.
http://www.postgresql.org/docs/curre...nsactions.html
is an intro to transaction timing.

You can look at who has locks on what using pg_locks, see
http://www.postgresql.org/docs/8.2/s...-pg-locks.html

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Sun, 2008-01-20 at 16:54 -0500, Greg Smith wrote:
> You can look at who has locks on what using pg_locks, see
> http://www.postgresql.org/docs/8.2/s...-pg-locks.html

I use the following view to ferret out locking problems. Briefly, look
for rows with state='WAIT', then find RUN rows for the same
<db,schema,relation> that have an exclusive lock.

-Reece


CREATE OR REPLACE VIEW pgutils.locks AS
SELECT l.pid, d.datname AS "database", n.nspname AS "schema", c.relname
AS relation, l.locktype, l."mode",
CASE l."granted"
WHEN true THEN 'RUN'::text
ELSE 'WAIT'::text
END AS state, a.usename, a.current_query, to_char(now() -
a.query_start, 'HH24:MI:SS'::text) AS duration
FROM pg_locks l
JOIN pg_database d ON l."database" = d.oid
JOIN pg_class c ON l.relation = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_stat_activity a ON l.pid = a.procpid
ORDER BY l.pid, d.datname, n.nspname, c.relname, l."granted";

eg=> select * from pgutils.locks ;
pid | database | schema | relation | locktype | mode | state | usename | current_query | duration
-------+----------+------------+----------------------------+----------+-----------------+-------+---------+-------------------------------+----------
15716 | csb-dev | pg_catalog | pg_class | relation | AccessShareLock | RUN | rkh | select * from pgutils.locks ; | 00:00:00
15716 | csb-dev | pg_catalog | pg_class_oid_index | relation | AccessShareLock | RUN | rkh | select * from pgutils.locks ; | 00:00:00
15716 | csb-dev | pg_catalog | pg_class_relname_nsp_index | relation | AccessShareLock | RUN | rkh | select * from pgutils.locks ; | 00:00:00
....


--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/