On Tue, Aug 7, 2012 at 3:45 PM, Jeff Janes <jeff.janes@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
