Re: 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(at)agliodbs(dot)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(at)agliodbs(dot)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(at)agliodbs(dot)com> wrote:
> On 12/16/2013 04:22 PM, Tom Lane wrote:
>> Josh Berkus <josh(at)agliodbs(dot)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(at)joh(dot)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