Re: Waiting for select - Mailing list pgsql-general

From Joshua D. Drake
Subject Re: Waiting for select
Date
Msg-id 42AA0C2B.9010405@commandprompt.com
Whole thread Raw
In response to Waiting for select  (Marc Munro <marc@bloodnok.com>)
List pgsql-general
Marc Munro wrote:
> Can someone explain this?  I seem to have a query which is being blocked
> by a lock.  I was under the impression that selects are never blocked.
> Am I missing something or is this bad behaviour?

Do you happen to be running a vacuum full?


>
> I am using slony and am synchronising a slave for the first time.  In
> the hope of seeing some progress on the slave I attempt to perform a
> select count(*) on one of the tables.
>
> The select just stops.  ps shows this:
>
> postgres  5987  0.0  0.4 19180 4188 ?        S    15:16   0:00 postgres: postgres testdb 192.168.1.111(33598) SELECT
waiting
>
> A query of blocking locks shows this:
>
>           object           | trans | pid  |        mode         | blocker
> ---------------------------+-------+------+---------------------+---------
>  testdb.campaign_cost_pk   |       | 5754 | AccessShareLock     |
>  testdb.csn_log_pk         |       | 5754 | RowExclusiveLock    |
>  testdb.pg_trigger         |       | 5754 | AccessShareLock     |
>  testdb.pg_trigger         |       | 5754 | RowExclusiveLock    |
>  testdb.sl_subscribe       |       | 5754 | AccessShareLock     |
>  testdb.csn_log_idx2       |       | 5754 | RowExclusiveLock    |
>  testdb.campaign           |       | 5754 | AccessShareLock     |
>  testdb.campaign           |       | 5754 | RowExclusiveLock    |
>  testdb.campaign           |       | 5754 | AccessExclusiveLock |
>  testdb.pg_rewrite         |       | 5754 | AccessShareLock     |
>  testdb.pg_rewrite         |       | 5754 | RowExclusiveLock    |
>  testdb.computer_sn_log    |       | 5754 | AccessShareLock     |
>  testdb.computer_sn_log    |       | 5754 | RowExclusiveLock    |
>  testdb.computer_sn_log    |       | 5754 | AccessExclusiveLock |
>  testdb.address_type_pk    |       | 5754 | AccessShareLock     |
>  testdb.campaign_pk        |       | 5754 | AccessShareLock     |
>  testdb.sl_log_1           |       | 5754 | AccessShareLock     |
>  testdb.sl_log_1           |       | 5754 | RowExclusiveLock    |
>  testdb.csn_cookie_idx1    |       | 5754 | AccessShareLock     |
>  testdb.pg_index           |       | 5754 | AccessShareLock     |
>  testdb.pg_index           |       | 5754 | RowShareLock        |
>  testdb.csn_log_idx3       |       | 5754 | RowExclusiveLock    |
>  testdb.csn_cookie_pk      |       | 5754 | AccessShareLock     |
>  testdb.sl_log_2           |       | 5754 | AccessShareLock     |
>  testdb.sl_log_2           |       | 5754 | RowExclusiveLock    |
>  testdb.sl_set             |       | 5754 | AccessShareLock     |
>  testdb.sl_set             |       | 5754 | RowShareLock        |
>  testdb.campaign_cost      |       | 5754 | AccessShareLock     |
>  testdb.campaign_cost      |       | 5754 | RowExclusiveLock    |
>  testdb.campaign_cost      |       | 5754 | AccessExclusiveLock |
>  testdb.sl_table           |       | 5754 | AccessShareLock     |
>  testdb.sl_table           |       | 5754 | RowShareLock        |
>  testdb.sl_table           |       | 5754 | RowExclusiveLock    |
>                            |  9182 | 5754 | ExclusiveLock       |
>  testdb.computer_sn_cookie |       | 5754 | AccessShareLock     |
>  testdb.computer_sn_cookie |       | 5754 | RowExclusiveLock    |
>  testdb.computer_sn_cookie |       | 5754 | AccessExclusiveLock |
>  testdb.pg_attribute       |       | 5754 | AccessShareLock     |
>  testdb.sl_config_lock     |       | 5754 | AccessExclusiveLock |
>  testdb.sl_trigger         |       | 5754 | AccessShareLock     |
>  testdb.csn_pk             |       | 5754 | AccessShareLock     |
>  testdb.pg_class           |       | 5754 | AccessShareLock     |
>  testdb.pg_class           |       | 5754 | RowShareLock        |
>  testdb.pg_class           |       | 5754 | RowExclusiveLock    |
>  testdb.address_type       |       | 5754 | AccessShareLock     |
>  testdb.address_type       |       | 5754 | RowExclusiveLock    |
>  testdb.address_type       |       | 5754 | AccessExclusiveLock |
>  testdb.pg_namespace       |       | 5754 | AccessShareLock     |
>  testdb.pg_namespace       |       | 5754 | RowShareLock        |
>  testdb.csn_log_idx1       |       | 5754 | RowExclusiveLock    |
>  testdb.computer_sn        |       | 5754 | AccessShareLock     |
>  testdb.computer_sn        |       | 5754 | RowExclusiveLock    |
>  testdb.computer_sn        |       | 5754 | AccessExclusiveLock |
>  testdb.address_type       |       | 5987 | AccessShareLock     |    5754
>                            |  9422 | 5987 | ExclusiveLock       |    5754
> (55 rows)
>
> All responses will be welcomed.
>
> __
> Marc


--
Your PostgreSQL solutions provider, Command Prompt, Inc.
24x7 support - 1.800.492.2240, programming, and consulting
Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit
http://www.commandprompt.com / http://www.postgresql.org

pgsql-general by date:

Previous
From: Marc Munro
Date:
Subject: Waiting for select
Next
From: Tom Lane
Date:
Subject: Re: Waiting for select