Re: PATCH: Reducing lock strength of trigger and foreign key DDL

On Fri, Jan 16, 2015 at 10:59 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> Just consider:
> S1: CREATE TABLE flubber(id serial primary key, data text);
> S1: CREATE FUNCTION blarg() RETURNS TRIGGER LANGUAGE plpgsql AS $$BEGIN RETURN NEW; END;$$;
> S1: CREATE TRIGGER flubber_blarg BEFORE INSERT ON flubber FOR EACH ROW WHEN (NEW.data IS NOT NULL) EXECUTE PROCEDURE blarg();
> S2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> S2: SELECT 'dosomethingelse';
> S1: ALTER TABLE flubber RENAME TO wasflubber;
> S1: ALTER TABLE wasflubber RENAME COLUMN data TO wasdata;
> S1: ALTER TRIGGER flubber_blarg ON wasflubber RENAME TO wasflubber_blarg;
> S1: ALTER FUNCTION blarg() RENAME TO wasblarg;
> S2: SELECT pg_get_triggerdef(oid) FROM pg_trigger;
>
> This will give you: The old trigger name. The new table name. The new
> column name. The new function name.

Ouch. That's clearly no good. I'm struggling to understand whether
this is a problem with our previous analysis, or a problem with this
patch:

http://www.postgresql.org/message-id/20141028003356.GA387814@tornado.leadboat.com

pg_get_triggerdef_worker() relies on generate_function_name(), which
uses the system caches, and on get_rule_expr(), for deparsing the WHEN
clause. If we allowed only ADDING triggers with a lesser lock and
never modifying or dropping them with a lesser lock, then changing the
initial scan of pg_trigger at the top of pg_get_triggerdef_worker() to
use the transaction snapshot might be OK; if we can see the trigger
with the transaction snapshot at all, we know it can't have
subsequently changed. But allowing alterations of any kind isn't
going to work, so I think our previous analysis on that point was
incorrect.

I *think* we could fix that if generate_function_name() and
get_rule_expr() had an option to use the active snapshot instead of a
fresh snapshot. The former doesn't look too hard to arrange, but the
latter is a tougher nut to crack.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company