rdsadmin
role (see RECORD 2, below):
SELECT pg_stat_activity, pg_locks.mode FROM pg_stat_activity JOIN pg_locks USING (pid) JOIN pg_class ON pg_locks.relation = pg_class.oid WHERE pg_class.relname = 'users' AND pg_locks.mode IN ('ShareUpdateExclusiveLock', 'ShareLock', 'ShareRowExclusiveLock', 'ExclusiveLock', 'AccessExclusiveLock'); -[ RECORD 1 ]----+------------------------------ datid | 1234 datname | my_database pid | 14641 usesysid | 16396 usename | postgres application_name | psql client_addr | 198.162.0.0 client_hostname | client_port | 5430 backend_start | 2016-01-15 22:05:06.161987+00 xact_start | 2016-01-15 22:14:39.301425+00 query_start | 2016-01-15 22:14:39.301425+00 state_change | 2016-01-15 22:14:39.301429+00 waiting | t state | active query | VACUUM FREEZE verbose users; mode | ShareUpdateExclusiveLock -[ RECORD 2 ]----+------------------------------ datid | 1234 datname | my_database pid | 22328 usesysid | 10 usename | rdsadmin application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | query |Further examination showed that this process was locking only the users table (and its indexes):mode | ShareUpdateExclusiveLock
SELECT locktype, relation::regclass AS tablename FROM pg_locks JOIN pg_stat_activity USING (pid) WHERE pid = 22328; locktype | tablename ------------+--------------------------------------------- relation | user_index_a relation | user_index_b relation | user_index_c relation | users_pkey virtualxid | relation | users (13 rows)
Has anyone else seen such a process? I'm curious as to what it is. My current best guess is a vacuum We opened a ticket with Amazon to ask them, so I'll update this post when Amazon replies.
EDIT 2016-01-17: the above is a braino for "an autovacuum." An Amazon rep wrote back the next day to say that it was, indeed, an autovacuum process, and included some vacuum-tuning tips. So good on them, although it's unfortunate that RDS's privilege system doesn't allow you to see the pg_stat_activity.query field when the rdsadmin role is working on a table you own.
This comment has been removed by the author.
ReplyDeletequirks of rds vacuum were discussed by Grant McAlister at recent PGSV conference:
ReplyDeletehttp://www.pgconfsv.com/sessions/amazon-rds-postgresql-whats-new-and-lessons-learned
Heh, I was in that session, but I didn't remember that! He covered a lot of material.
ReplyDeleteI recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
ReplyDeleteAmazon