Re: ALTER TABLE ... NOREWRITE option

On Wed, Dec 5, 2012 at 5:56 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2012-12-05 22:41:21 +0000, Simon Riggs wrote:
>> On 5 December 2012 22:21, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
>> > Simon Riggs <simon(at)2ndQuadrant(dot)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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company