Lists: | pgsql-hackers |
---|
From: | Jonathan Scher <js(at)oxado(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | CLUSTER, using SHARE UPDATE EXCLUSIVE lock? |
Date: | 2007-03-01 13:55:35 |
Message-ID: | 45E6DB57.6000107@oxado.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hello,
CLUSTER uses an ACCESS EXCLUSIVE lock. Why does it forbid concurrent reads?
Florian G. Pflug, on a post (
http://archives.postgresql.org/pgsql-hackers/2007-02/msg00081.php )
suggested that there were no way to let the old entry until the command
commit.
I would like Cluster to create a new table, copying old values to this
one, then delete the old one. This would allow Cluster to work with a
share update exlusive lock, in order to allow reading the table while
cluster is working.
How does Cluster actually works? Why making a Cluster command less
restrictive isn't even in the TO-list?
Best regards,
Jonathan Scher
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jonathan Scher <js(at)oxado(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: CLUSTER, using SHARE UPDATE EXCLUSIVE lock? |
Date: | 2007-03-01 15:31:38 |
Message-ID: | 24391.1172763098@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Jonathan Scher <js(at)oxado(dot)com> writes:
> CLUSTER uses an ACCESS EXCLUSIVE lock. Why does it forbid concurrent reads?
Because when it drops the old copy of the table there had better not be
any concurrent readers.
regards, tom lane
From: | Jonathan Scher <js(at)oxado(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: CLUSTER, using SHARE UPDATE EXCLUSIVE lock? |
Date: | 2007-03-01 17:23:37 |
Message-ID: | 45E70C19.9050808@oxado.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Tom Lane a écrit :
> Jonathan Scher <js(at)oxado(dot)com> writes:
>
>> CLUSTER uses an ACCESS EXCLUSIVE lock. Why does it forbid concurrent reads?
>>
>
> Because when it drops the old copy of the table there had better not be
> any concurrent readers.
>
> regards, tom lane
>
>
>
Then, is it possible to take a share update exclusive lock until the new
table is ready, then an access exclusive one only in order to switch
tables? I don't think it's already coded like that...
Regards
Jonathan Scher
From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Jonathan Scher <js(at)oxado(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: CLUSTER, using SHARE UPDATE EXCLUSIVE lock? |
Date: | 2007-03-01 18:47:56 |
Message-ID: | 20070301184756.GE1705@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Thu, Mar 01, 2007 at 06:23:37PM +0100, Jonathan Scher wrote:
> >Because when it drops the old copy of the table there had better not be
> >any concurrent readers.
> Then, is it possible to take a share update exclusive lock until the new
> table is ready, then an access exclusive one only in order to switch
> tables? I don't think it's already coded like that...
That's lock upgrading, which opens you up to deadlocks. If another
process grabs a lock after your update exclusive, you're not going to
be able to upgrade it.
Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
From: | Jonathan Scher <js(at)oxado(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: CLUSTER, using SHARE UPDATE EXCLUSIVE lock? |
Date: | 2007-03-02 10:34:11 |
Message-ID: | 45E7FDA3.7080507@oxado.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
> That's lock upgrading, which opens you up to deadlocks. If another
> process grabs a lock after your update exclusive, you're not going to
> be able to upgrade it.
>
>
OK I got the point.
Is it possible to create a new table-level lock mode that says "OK, now
I'm on share level, but soon I will upgrade it to exclusive". Then it
would be possible to allow any read-only operation, and to forbid all
operation that would need to upgrade.
I know that there is a sx_try_upgrade function on freeBSD kernel (man
sx), so I guess there is a way to do it, isn't it?
Regards,
Jonathan Scher