AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and
CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are
adding triggers to (the PK table, in the case of ALTER TABLE). Is this
necessary? I don't see why we can't allow SELECT queries on the table to
proceed while the trigger is being added.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Neil Conway wrote:
> AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and
> CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are
> adding triggers to (the PK table, in the case of ALTER TABLE). Is this
> necessary? I don't see why we can't allow SELECT queries on the table to
> proceed while the trigger is being added.

Sorry, I forgot to mention: I think RowExclusiveLock or ExclusiveLock
would be sufficient instead.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Neil Conway wrote:
> AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and
> CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are
> adding triggers to (the PK table, in the case of ALTER TABLE). Is this
> necessary? I don't see why we can't allow SELECT queries on the table to
> proceed while the trigger is being added.

Attached is a patch that changes both to use ShareRowExclusiveLock, and
updates the documentation accordingly. I'll apply this later today,
barring any objections.

-Neil


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

If you want to be my friend forever, then fix CLUSTER so that it uses
sharerowexclusive as well

Chris

Neil Conway wrote:
> Neil Conway wrote:
>
>> AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and
>> CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are
>> adding triggers to (the PK table, in the case of ALTER TABLE). Is this
>> necessary? I don't see why we can't allow SELECT queries on the table
>> to proceed while the trigger is being added.
>
>
> Attached is a patch that changes both to use ShareRowExclusiveLock, and
> updates the documentation accordingly. I'll apply this later today,
> barring any objections.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Wed, 23 Mar 2005 12:40 pm, Christopher Kings-Lynne wrote:
> If you want to be my friend forever, then fix CLUSTER so that it uses
> sharerowexclusive as well
>
I don't think it's as easy as that, because you have to move tuples
around in the cluster operation. Same sort of issue as vacuum full I would suggest.

Russell Smith

> Chris
>
> Neil Conway wrote:
> > Neil Conway wrote:
> >
> >> AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and
> >> CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are
> >> adding triggers to (the PK table, in the case of ALTER TABLE). Is this
> >> necessary? I don't see why we can't allow SELECT queries on the table
> >> to proceed while the trigger is being added.
> >
> >
> > Attached is a patch that changes both to use ShareRowExclusiveLock, and
> > updates the documentation accordingly. I'll apply this later today,
> > barring any objections.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>>If you want to be my friend forever, then fix CLUSTER so that it uses
>>sharerowexclusive as well
>
> I don't think it's as easy as that, because you have to move tuples
> around in the cluster operation. Same sort of issue as vacuum full I would suggest.

Cluster doesn't move rows...

I didn't say it was easy. It would involve changing how cluster works.
It would keep the old table around while building the new, then grab
an exclusive lock to swap the two.

Chris

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Wed, Mar 23, 2005 at 10:42:01AM +0800, Christopher Kings-Lynne wrote:
> >>If you want to be my friend forever, then fix CLUSTER so that it uses
> >>sharerowexclusive as well
> >
> >I don't think it's as easy as that, because you have to move tuples
> >around in the cluster operation. Same sort of issue as vacuum full I
> >would suggest.
>
> Cluster doesn't move rows...
>
> I didn't say it was easy. It would involve changing how cluster works.
> It would keep the old table around while building the new, then grab
> an exclusive lock to swap the two.

Huh, cluster already does that.

I don't remember what the rationale was for locking the table, leaving
even simple SELECTs out. (In fact, IIRC the decision wasn't made by me,
and it wasn't discussed at all.)

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"I would rather have GNU than GNOT." (ccchips, lwn.net/Articles/37595/)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> Huh, cluster already does that.

It does and it doesn't. Something like the first thing it does is muck
with the old table's filenode IIRC, meaning that immediately the old
table will no longer work.

Chris

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Christopher Kings-Lynne wrote:
> If you want to be my friend forever, then fix CLUSTER so that it uses
> sharerowexclusive as well

Hmm, this might be possible as well. During a CLUSTER, we currently

- lock the heap relation with AccessExclusiveLock
- lock the index we're clustering on with AccessExclusiveLock
- create a temporary heap relation
- fill with data from the old heap relation, via an index scan
- swap the relfilenodes of the old and temporary heap relations
- rebuild indexes

We certainly can't allow concurrent modifications to either the table or
the clustered index while this is happening. Allowing index scans
*should* be safe -- an index scan could result in modifications to the
index (e.g. updating "tuple is killed" bits), but those shouldn't be
essential. We might also want to disallow SELECT FOR UPDATE, since we
would end up invoking heap_mark4update() on the old heap relation. Not
sure offhand how serious that would be.

So I think it should be possible to lock both the heap relation and the
index with ExclusiveLock, which would allow SELECTs on them. This would
apply to both the single relation and multiple relation variants of
CLUSTER (since we do each individual clustering in its own transaction).

.... except that when we rebuild the relation's indexes, we acquire an
AccessExclusiveLock on the index. This would introduce the risk of
deadlock. It seems necessary to acquire an AccessExclusiveLock when
rebuilding shared indexes, since we do the index build in-place, but I
think we can get by with an ExclusiveLock in the non-shared case, for
similar reasons as above: we build the new index and then swap relfilenodes.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Neil Conway wrote:
> So I think it should be possible to lock both the heap relation and the
> index with ExclusiveLock, which would allow SELECTs on them. This would
> apply to both the single relation and multiple relation variants of
> CLUSTER (since we do each individual clustering in its own transaction).
>
> ... except that when we rebuild the relation's indexes, we acquire an
> AccessExclusiveLock on the index. This would introduce the risk of
> deadlock. It seems necessary to acquire an AccessExclusiveLock when
> rebuilding shared indexes, since we do the index build in-place, but I
> think we can get by with an ExclusiveLock in the non-shared case, for
> similar reasons as above: we build the new index and then swap relfilenodes.

Certainly we need to upgrade to an exclusive table lock to replace the
heap table. Do we want to get a shared lock and possibly starve waiting
for an exclusive lock on the table to swap the new one in? Do we do
such escallation anywhere else?

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings