From: | gmb <gmbouwer(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Performance on DISABLE TRIGGER |
Date: | 2015-03-03 16:24:35 |
Message-ID: | 1425399875049-5840221.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
David Steele wrote
>
> ALTER TABLE requires an exclusive lock - my guess is that another
> process has a lock on the table. It could even be a select.
>
> pg_locks is your friend in this case:
> http://www.postgresql.org/docs/9.4/static/view-pg-locks.html
Hi David
I'm a bit confused on how to interpret the result of the pg_locks view.
After running the following (as per linked page).
SELECT pl.* , psa.query FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON
pl.pid = psa.pid;
I get a result of 2 locks on the "ALTER TABLE ..." statement:
Expanded display is on.
-[ RECORD 1 ]------+-----------------
locktype | virtualxid
database |
relation |
page |
tuple |
virtualxid | 5/57182
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 5/57182
pid | 6128
mode | ExclusiveLock
granted | t
fastpath | t
query | ALTER TABLE tab DISABLE TRIGGER trig;
-[ RECORD 2 ]------+----------------
locktype | relation
database | 16393
relation | 22595
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 5/57182
pid | 6128
mode | AccessExclusiveLock
granted | f
fastpath | f
query | ALTER TABLE tab DISABLE TRIGGER trig;
Something else I noticed while checking out the Server Status window in the
pgAdmin tool:
The stats_activity query run there displays a "blocked by" column , which
I'm assuming is retrieved using data from pg_locks .
I assume I'm seeing the pid of the process which is causing the block.
THe process however, is a query generated by the pgADmin tool itself:
SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=1700
May this be the case of pg_catalog data being in need of maintenance ?
Regards
--
View this message in context: http://postgresql.nabble.com/Performance-on-DISABLE-TRIGGER-tp5839727p5840221.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2015-03-03 17:11:17 | Re: Performance on DISABLE TRIGGER (resend) |
Previous Message | Kai Groner | 2015-03-03 16:15:43 | Re: EXCLUDE constraint with not equals |