Re: ALTER TABLE ... NOREWRITE option

On 5 December 2012 23:34, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Dec 5, 2012 at 5:56 PM, Andres Freund <andres@2ndquadrant.com> wrote:
>> On 2012-12-05 22:41:21 +0000, Simon Riggs wrote:
>>> On 5 December 2012 22:21, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
>>> > Simon Riggs <simon@2ndQuadrant.com> writes:
>>> >>> CREATE EVENT TRIGGER my_event_trigger
>>> >>>     ON table_rewrite
>>> >>>     EXECUTE PROCEDURE consider_whether_to_throw_an_error();
>>> >>
>>> >> +1, I was just thinking that myself.
>>> >
>>> > +1, and I think that can happen for 9.3, as soon as we agree on the list
>>> > of code points where we want that event to fire. ALTER TABLE variants
>>> > that are rewriting the heap, sure. CLUSTER? VACUUM FULL? TRUNCATE?
>>>
>>> Events needed
>>> * Table rewrite
>>> * Index rebuild
>>
>> Those should be fairly easy.
>>
>>> * Relation scan (index/table/toast etc)
>>> * AccessExclusiveLock
>>
>> I am worried about the overhead of looking for triggers for those two
>> though. Especially for RelationScans.
>
> Yep.  The other thing we have to consider very carefully is which of
> these locations are safe places to run arbitrary code.  In some cases,
> refactoring may be needed.  I suspect that, even for table_rewrite,
> it's not gonna be safe to inject that at the place where the table
> rewrite actually happens.  At that point, we've already done things
> like CheckTableNotInUse(); but the trigger could break that by opening
> a cursor that references the table and leaving it open.  If we're
> rewriting multiple tables, is it really safe to fire a trigger after
> the first one has been rewritten and before the second one gets
> rewritten?  Maybe, but I've got my doubts.
>
> Similarly, if you want to have an event trigger for an index rebuild,
> we'll probably have to figure out earlier than we currently do whether
> or not an index build is going to be required.  I think we currently
> defer that decision until after we've rewritten the table, and I
> suspect that's going to be too late to safely fire a trigger.
>
> So while I certainly think this is doable, I strongly suggest that we
> keep our initial goals modest.  Adding the new event trigger is a
> piece of cake.  Making sure that it doesn't break anything is not.

Yes, but it is also the trigger writers problem.

If they keep their goals modest, they'll work.

If I understand this, Dimitri is planning to include a ready-made
trigger, so this will both test and show how to use them, as well as
being a useful application.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services