"Okano, Naoki" <okano.naoki@jp.fujitsu.com> writes:
> Peter Eisentraut wrote:
>>> I have a feeling that this was proposed a few times in the ancient past
>>> but did not go through because of locking issues. I can't find any
>>> emails about it through. Does anyone remember? Have you thought about
>>> locking issues?
>>> Is this e-mail you are finding?
>>> https://www.postgresql.org/message-id/20140916124537.GH25887%40awork2.anarazel.de
>> No, that's not the one I had in mind.
That's still a thread well worth studying in detail. It does touch on
locking issues, in that it points out that we allow you to replace a
trigger function's body with CREATE OR REPLACE FUNCTION with no lock at
all on the relation(s) it's a trigger for. Even with very lax assumptions
about what lock level CREATE OR REPLACE TRIGGER needs, it can't match
"none". Now you could certainly argue that we're not being very safe by
allowing trigger functions to be changed that way, but that's the current
state of affairs.
Another thread that you really need to absorb in its entirety is
https://www.postgresql.org/message-id/flat/5447578C.2050807%40proxel.se
and you might also want to read the older threads that Robert Haas
links to early in that thread.
The locking-related point that strikes me most forcefully in that thread
is the concerns about whether a concurrent pg_dump run will produce a
consistent view of the table's triggers. This is problematic mainly
because pg_dump itself will see only the catalog entries that were current
when it started, but it relies heavily on ruleutils.c which will tend to
see committed changes immediately. Now, the existing behavior here is
probably not at all perfect, but that doesn't mean it's okay to make
things worse with CREATE OR REPLACE TRIGGER. A conservative conclusion
would be that C.O.R.T. needs to take AccessExclusiveLock so that it
can't run in parallel with pg_dump. Maybe that can be relaxed but it
requires some study. (CREATE TRIGGER doesn't have this issue because
pg_dump wouldn't see the new trigger at all and thus would never ask
ruleutils about it.)
Even if you don't care about pg_dump, I think that the question of whether
concurrent DML operations would always see (and act upon) instantaneously-
consistent versions of a table's trigger state is worth worrying about.
regards, tom lane
