Обсуждение: Why no INSTEAD OF triggers on tables?
Hackers, I've looked in the archives, but I can't find a reason why INSTEAD OF triggers were never enabled for tables. I'm interested in them in order to return a rowcount to JDBC for INSERTs into partitioned tables. Was there a technical obstacle, or is this just a TUIT issue? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> I've looked in the archives, but I can't find a reason why INSTEAD OF
> triggers were never enabled for tables.
What would that mean exactly? And how would you do the actual update
when it came time to?
> Was there a technical obstacle, or is this just a TUIT issue?
It sounds more like a requestor-hasnt-thought-it-through issue.
regards, tom lane
On 12/16/2013 04:22 PM, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: >> I've looked in the archives, but I can't find a reason why INSTEAD OF >> triggers were never enabled for tables. > > What would that mean exactly? And how would you do the actual update > when it came time to? Well, I'm specifically thinking of master partition tables. In that case, we really want an INSTEAD OF trigger. It seems a little silly that I need to create a separate view, and then an INSTEAD OF trigger on the view, in order to get a rows-updated count back from an INSERT which hits a partitioned table. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Mon, Dec 16, 2013 at 9:16 PM, Josh Berkus <josh@agliodbs.com> wrote: > On 12/16/2013 04:22 PM, Tom Lane wrote: >> Josh Berkus <josh@agliodbs.com> writes: >>> I've looked in the archives, but I can't find a reason why INSTEAD OF >>> triggers were never enabled for tables. >> >> What would that mean exactly? And how would you do the actual update >> when it came time to? > > Well, I'm specifically thinking of master partition tables. In that > case, we really want an INSTEAD OF trigger. /me scratches head. So, put a BEFORE trigger, and make it return NULL. Same effect, different notation. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 12/17/13, 4:53 AM, Robert Haas wrote: >> Well, I'm specifically thinking of master partition tables. In that >> case, we really want an INSTEAD OF trigger. > > /me scratches head. > > So, put a BEFORE trigger, and make it return NULL. Same effect, > different notation. But it's not the same effect at all, that's the point: =# create view foov as select 1 as a; CREATE VIEW =# create function insteadof() returns trigger as $$ $# begin $# -- INSERT here $# return new; $# end $# $$ language plpgsql; CREATE FUNCTION =# create function before() returns trigger as $$ $# begin $# -- INSERT here $# return null; $# end $# $$ language plpgsql; CREATE FUNCTION =# create trigger t1 instead of insert on foov for each row execute procedure insteadof(); CREATE TRIGGER =# create trigger t2 before insert on bart for each row execute procedure before(); CREATE TRIGGER =# insert into foov values (1) returning *; a --- 1 (1 row) INSERT 0 1 local:marko=#* insert into bart values (1) returning *; a --- (0 rows) INSERT 0 0 Regards, Marko Tiikkaja
On Tue, Dec 17, 2013 at 3:27 AM, Marko Tiikkaja <marko@joh.to> wrote: > On 12/17/13, 4:53 AM, Robert Haas wrote: >>> >>> Well, I'm specifically thinking of master partition tables. In that >>> case, we really want an INSTEAD OF trigger. >> >> /me scratches head. >> >> So, put a BEFORE trigger, and make it return NULL. Same effect, >> different notation. > > > But it's not the same effect at all, that's the point: > > =# create view foov as select 1 as a; > CREATE VIEW > > =# create function insteadof() returns trigger as $$ > $# begin > $# -- INSERT here > $# return new; > $# end > $# $$ language plpgsql; > CREATE FUNCTION > > =# create function before() returns trigger as $$ > $# begin > $# -- INSERT here > $# return null; > $# end > $# $$ language plpgsql; > CREATE FUNCTION > > =# create trigger t1 instead of insert on foov for each row execute > procedure insteadof(); > CREATE TRIGGER > > =# create trigger t2 before insert on bart for each row execute procedure > before(); > CREATE TRIGGER > > =# insert into foov values (1) returning *; > a > --- > 1 > (1 row) > > INSERT 0 1 > > local:marko=#* insert into bart values (1) returning *; > a > --- > (0 rows) > > INSERT 0 0 Ah, interesting point. I didn't realize it worked like that. That does seem like a mighty useful thing to be able to do. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 12/16/2013 07:53 PM, Robert Haas wrote: > So, put a BEFORE trigger, and make it return NULL. Same effect, > different notation. NOT the same: Master partition table with BEFORE trigger: josh=# insert into a ( id, val ) values ( 23, 'test' ), ( 24, 'test'), (25,'test'); INSERT 0 0 ^^^ View with INSTEAD OF trigger: josh=# insert into a_v ( id, val ) values ( 23, 'test' ), ( 24, 'test'), (25,'test'); INSERT 0 3 ^^^ The difference here is that the INSTEAD OF trigger returns a rows-affected count, and the BEFORE trigger does not (it returns 0). Some drivers and ORMs, most notably Hibernate, check this rows-returned count, and error if they don't match the rows sent. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com