On Tue, Aug 7, 2012 at 3:45 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> absolutely -- the database needs to guard against other writers to the
>> table doing inserts in the meantime.
>
> But why must it? Why can't other writers simply obey the trigger,
> since its removal has not yet been committed? You could have the
> anomaly that a longer-running later-committing transaction used the
> old trigger while a shorter-running earlier-committing transaction
> used the new one (which isn't really an anomaly if the old and new are
> identical), but is that even barred if neither of them is in
> serializable mode? And since triggers can do pretty much anything
> they want internally, there isn't much of a transactional guarantee
> with them anyway.
Triggers give a 100% transactional guarantee, period. Yes, you can do
things in them that violate MVCC, like make dblink calls, but you can
do that from any SQL statement; they are no less transactionally
guaranteed than regular SQL. As to your wider point, you could in
theory interleave other work with adjustment of triggers although it
seems pretty complicated and weird. Also RI triggers (the most
important case) would need special handling since (like check
constraints) they are supposed to apply to the table as a whole, not
records inserted since trigger creation. Also serializable would be
right out as you noted.
>> there's no concept in SQL of
>> 'enforce this trigger for all writers, except for me' nor should there
>> be.
>
> Why shouldn't there be, other than the bother of implementing and
> documenting it? Sometimes theory needs to compromise with reality.
> When we don't provide slightly dangerous ways to make those
> compromises, people are forced to use very dangerous ways instead.
>
>>
>> one possible workaround is to hack your trigger function so that it
>> doesn't operate for particular roles. so your trigger might be:
>>
>> IF current_user = 'bulk_writer' THEN
>> return new;
>> END IF;
>> <expensive stuff>
>
> I don't know Craig's case, but often the most expensive of the
> "expensive stuff" is the bare fact of firing a trigger in the first
> place.
That's highly debatable. a function call is somewhat expensive but is
a fixed cpu cost. RI triggers or complicated queries can really get
expensive, especially with large tables.
merlin
