Re: Postgres Hanging on Inserts

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: <akavan(at)cox(dot)net>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, July 30, 2003 9:25 AM
Subject: Re: [GENERAL] Postgres Hanging on Inserts

> Adam Kavan <akavan(at)cox(dot)net> writes:
> > I looked into pg_locks and they are all waiting to get an exclusive
> > lock on the same relation. Is there anyway for me to tell what this
> > relation is?
>
> To decipher the OIDs in pg_locks, join against pg_class.oid, or just do
> select relname from pg_class where oid = nnnn;
>
> > Does anyone know what it could be and how I can fix this
> > problem?
>
> Look for the process that already has a lock on the same relation, and
> find out what it's waiting for.
>
> regards, tom lane

I have found the problem (I think) below is the list of all the locks
pending on the relation. The relation is a hash index on the table that is
being INSERT'd rapidly. From what I can see pid 10024 and 10025 both have
an ExclusiveLock on the index, and they both are waiting to get an
ExclusiveLock on the relation. Those are the only locks either pid doesn't
have so I suspect that is what is causing the deadlock. Is there something
I've done wrong? Both pids are just doing simple inserts.

data=# select * from pg_locks where relation = 3731653 order by granted;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+-----------------+---------
3731653 | 16976 | | 10091 | ShareLock | f
3731653 | 16976 | | 10077 | ShareLock | f
3731653 | 16976 | | 10178 | ShareLock | f
3731653 | 16976 | | 10116 | ShareLock | f
3731653 | 16976 | | 10108 | ShareLock | f
3731653 | 16976 | | 10076 | ShareLock | f
3731653 | 16976 | | 10079 | ShareLock | f
3731653 | 16976 | | 10110 | ShareLock | f
3731653 | 16976 | | 10023 | ExclusiveLock | f
3731653 | 16976 | | 10177 | ShareLock | f
3731653 | 16976 | | 10208 | ShareLock | f
3731653 | 16976 | | 10166 | ShareLock | f
3731653 | 16976 | | 10142 | ShareLock | f
3731653 | 16976 | | 10160 | ShareLock | f
3731653 | 16976 | | 10214 | ShareLock | f
3731653 | 16976 | | 10226 | ShareLock | f
3731653 | 16976 | | 10031 | ShareLock | f
3731653 | 16976 | | 10237 | ShareLock | f
3731653 | 16976 | | 10075 | ShareLock | f
3731653 | 16976 | | 10109 | ShareLock | f
3731653 | 16976 | | 10207 | ShareLock | f
3731653 | 16976 | | 10190 | ShareLock | f
3731653 | 16976 | | 10041 | ShareLock | f
3731653 | 16976 | | 10130 | ShareLock | f
3731653 | 16976 | | 10043 | ShareLock | f
3731653 | 16976 | | 10026 | ShareLock | f
3731653 | 16976 | | 10074 | ShareLock | f
3731653 | 16976 | | 10092 | ShareLock | f
3731653 | 16976 | | 10158 | ShareLock | f
3731653 | 16976 | | 10024 | ExclusiveLock | f
3731653 | 16976 | | 10141 | ShareLock | f
3731653 | 16976 | | 10189 | ShareLock | f
3731653 | 16976 | | 10238 | ShareLock | f
3731653 | 16976 | | 10027 | ShareLock | f
3731653 | 16976 | | 10078 | ShareLock | f
3731653 | 16976 | | 10025 | ExclusiveLock | f
3731653 | 16976 | | 10159 | ShareLock | f
3731653 | 16976 | | 10225 | ShareLock | f
3731653 | 16976 | | 9951 | ShareLock | f
3731653 | 16976 | | 10029 | ShareLock | f
3731653 | 16976 | | 10196 | ShareLock | f
3731653 | 16976 | | 10028 | ShareLock | f
3731653 | 16976 | | 10128 | ShareLock | f
3731653 | 16976 | | 9951 | AccessShareLock | t
3731653 | 16976 | | 10024 | ExclusiveLock | t
3731653 | 16976 | | 10025 | ExclusiveLock | t
3731653 | 16976 | | 9951 | ShareLock | t
3731653 | 16976 | | 10023 | ShareLock | t
(48 rows)

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)