Re: New Event Trigger: table_rewrite

On 16 October 2014 10:18, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
>> Please find attached to this email a patch to implement a new Event
>> Trigger, fired on the the "table_rewrite" event. As attached, it's meant
>> as a discussion enabler and only supports ALTER TABLE (and maybe not in
>> all forms of it). It will need to grow support for VACUUM FULL and
>> CLUSTER and more before getting commited.
>
> And here's already a new version of it, including support for ALTER
> TABLE, VACUUM and CLUSTER commands, and documentation.

The patch itself looks fine overall. Docs look in place, tests OK.

API changes may need more thought. I'm not sure myself, they just look
fairly quick.

It would be more useful to work on the applications of this....

1. INSERT into a table
* Action start time
* Schema
* Tablename
* Number of blocks in table
which would then allow you to do these things run an assessment report
showing which tables would be rewritten.

2. Get access to number of blocks, so you could limit rewrites only to
smaller tables by putting a block limit in place.

3. It might be even cooler to contemplate having pg_stat_activity
publish an estimated end time.
We'd probably need some kind of time_per_block parameter for each
tablespace so we can estimate the time.

Doing 1 and 2 at least would make this a good feature. We can do a
later patch for 3, or similar, once this is accepted.

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