On Mon, Nov 15, 2010 at 12:02 PM, Marti Raudsepp <marti@juffo.org> wrote:
> On Mon, Nov 15, 2010 at 18:25, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Marti Raudsepp <marti@juffo.org> writes:
>>> Would there be a problem with invoking this trigger from the session
>>> that first touches the table?
>>
>> Other than security?
>
> Right, I guess that would only make sense with SECURITY DEFINER.
>
> On Mon, Nov 15, 2010 at 18:22, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Nov 15, 2010 at 10:54 AM, Marti Raudsepp <marti@juffo.org> wrote:
>>> Just wondering, have you thought of any mechanisms how application
>>> code might detect that an unlogged table was truncated due to restart?
>
>> Yeah, this infrastructure doesn't really allow that. The truncate
>> happens way too early on in startup to execute any user-provided code.
>
> The truncate itself can be performed early and set a flag somewhere
> that would invoke a trigger on the first access. I suppose it cannot
> be called a "truncate trigger" then.
>
> Or maybe provide hooks for pgAgent instead?
>
> Do you see any alternatives to be notified of unlogged table
> truncates? Without notification, this feature would seem to have
> limited usefulness.
Well, you're only monitoring for a server restart. That's probably
something you need a way to monitor for anyway. I don't think we have
a function that exposes the time of the last server restart at the SQL
level, but maybe we should. You can monitor for it by watching the
logs, of course.
This is really intended for things like caches of session information
where loss is annoying (because users have to log back into the
webapp, or whatever) but not so critical that we want to take a
performance penalty to prevent it. It will also be helpful to people
who want to make PG run very very quickly even at the risk of data
loss, as in the recent discussion on -performance and some
conversations I had at PG West; it provides a more structured, and
hopefully also more performant, alternative to turning off fsync,
full_page_writes, and synchronous commit. For some such apps, it may
be sufficient to check for truncating at each reconnect, which will be
a whole lot easier than what they have to do now (which is rebuild the
entire cluster every time PG restarts).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company