|
Home > Archive > Slony1 PostgreSQL Replication > June 2005 > Select being blocked during sync to slave.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Select being blocked during sync to slave.
|
|
|
|
| Adam Haberlach 2005-06-10, 8:24 pm |
|
I saw this behavior as well, and from the process listing it appeared that
slony uses a COPY command to do the initial transfer. I believe that
generally creates a lock, or otherwise should.
Once the copy is completed, then the system will start playing the
transactions that have logged during the interim.
-----Original Message-----
From: slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
[mailto:slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org] On Behalf Of Marc Munro
Sent: Friday, June 10, 2005 3:26 PM
To: slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
Subject: [Slony1-general] Select being blocked during sync to slave.
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 is blocked by an AccessExclusiveLock. I am wondering what slony
is doing at this point and why it needs to take out such a serious lock.
Any and all explanations will be welcomed.
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
| |
| Jan Wieck 2005-06-13, 7:24 am |
| On 6/10/2005 7:52 PM, Adam Haberlach wrote:
>
> I saw this behavior as well, and from the process listing it appeared that
> slony uses a COPY command to do the initial transfer. I believe that
> generally creates a lock, or otherwise should.
The copy itself doesn't take that lock. What happens is that slony
manipulates the system catalog to disable all user triggers on the
table, delete all eventually existing data in the table, then use copy
to fill in the initial content at subscribe set time. The first part
requires an exclusive lock, and all actions for the whole subscribe set
are done within one transaction (all tables together).
Because of the "one transaction covers the whole subscibe set" detail,
using select count(*) can't work anyway.
Jan
>
> Once the copy is completed, then the system will start playing the
> transactions that have logged during the interim.
>
>
> -----Original Message-----
> From: slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> [mailto:slony1-general-bounces- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org] On Behalf Of Marc Munro
> Sent: Friday, June 10, 2005 3:26 PM
> To: slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> Subject: [Slony1-general] Select being blocked during sync to slave.
>
> 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 is blocked by an AccessExclusiveLock. I am wondering what slony
> is doing at this point and why it needs to take out such a serious lock.
>
> Any and all explanations will be welcomed.
>
>
> 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
>
>
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general
--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #
|
|
|
|
|