Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 2 April 2015 at 22:23, Robert Haas robertmh...@gmail.com wrote: On Thu, Apr 2, 2015 at 5:02 PM, Andres Freund and...@anarazel.de wrote: I think the upshot is that INSTEAD OF triggers work in a particular way because that's what is needed to support updatable views. If triggers on tables should behave differently, maybe it should be a separate trigger type. Maybe it would be feasible to extend BEFORE triggers to support RETURNING, for example? What in the above prohibits extending the behaviour to tables? I have yet to see what compatibility or similarity problem that'd pose. It seems all mightily handwavy to me. Yeah. It's possible there's a better interface here than INSTEAD OF, and one of the things I didn't like about the OP was that it started by stating the syntax that would be used rather than by describing the problem that needed to be solved. It's generally better to start with the latter, and then work out the syntax from there. But having gotten that gripe out of my system, and especially in view of Dean's comments, it's not very clear to me what's wrong with using INSTEAD OF for this purpose. If you make BEFORE triggers do this via RETURNING, then you might have a trigger that returns multiple rows, which seems like it would introduce a bunch of new complexity for no obvious benefit. Yes, I'm inclined to agree. One of the reasons that INSTEAD OF triggers weren't supported on tables was the lack of an obvious use-case for it, but now having thought about partitioning, I think they would provide a fairly neat solution to that problem. I don't think that putting a view with INSTEAD OF triggers on top of the parent table and then always going through that view works quite as well, because there are still a few cases where a view doesn't work as well as a table. A view can't be the target of a foreign key, for example, so there'd be no way for a cascaded UPDATE to invoke the INSTEAD OF triggers. If you needed to handle the case of updates causing a change of partition, adding conditional INSTEAD OF triggers to the child tables would be a way to do that, retaining support for RETURNING, and keeping the logic localised to each partition, only invoking it when necessary. Supporting INSTEAD OF triggers on tables is not completely trivial to implement, but it doesn't look too hard either, and the more I think about it, the more I suspect that other use-cases will emerge to make that effort worthwhile. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
auto-updatable view work just for postgresql-9.3 and above (for other version
you still need to define DELETE/UPDATE trigger).
what i see is we just trying to have a work around either with BEFORE/AFTER
trigger or with auto-updatable view
in stright forwad/normale way is just to define INSTEAD OF trigger on the
master table that return NEW so it doesn't break RETURNING, and the actual
tuples returned by the trigger wouldn't actually be inserted in the master
table. after all, that what INSTEAD OF suppose to do.
tom lane : in partitioned table. normally (always), the data is stored in child
tables (i know this not the case for inheritence) . any data inserted in master
table is just an exception/error/design bug or this is just my case. what i
mean is, if some one define master table as empty table (even without having
INSTEAD OF trigger) is not wart (postgresql need to be more flexible, and let
user define thier database architecture the way they like).
also, it would be nice that the example :
INSERT INTO cities (name, population, altitude, state)
VALUES ('New York', NULL, NULL, 'NY');
in the inheritence doc to work, (if we maked passes syntax error checking and
planning phase) next step is to chose between rule and trigger (we already
have instead of rule. we just need instead of trigger ) maybe this not a user
defined one but implicitly.
-Original Message-
From: Dean Rasheed dean.a.rash...@gmail.com
To: Andres Freund and...@anarazel.de
Cc: Tom Lane t...@sss.pgh.pa.us; Robert Haas robertmh...@gmail.com; Aliouii
Ali aliouii@aol.fr; pgsql-hackers pgsql-hackers@postgresql.org
Sent: Wed, Apr 1, 2015 8:01 pm
Subject: Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 1 April 2015 at 18:37, Andres Freund and...@anarazel.de wrote:
On
2015-04-01 13:29:33 -0400, Tom Lane wrote:
As for partitioning, you could do
this:
create table parent(...);
create table child(...)
inherits(parent); -- repeat as needed
create view v as select * from
parent;
attach INSTEAD OF triggers to v
Now the application deals
only with v, and thinks that's the real
table.
Sure, but that's just
making things unnecessarily hard. That then
requires also defining
UPDATE/DELETE INSTEAD triggers which otherwise
would just work.
No,
because as defined above the view v would be auto-updatable, so
updates and
deletes on v would just do the matching update/delete
on
parent.
Regards,
Dean
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On Thu, Apr 2, 2015 at 5:02 PM, Andres Freund and...@anarazel.de wrote: I think the upshot is that INSTEAD OF triggers work in a particular way because that's what is needed to support updatable views. If triggers on tables should behave differently, maybe it should be a separate trigger type. Maybe it would be feasible to extend BEFORE triggers to support RETURNING, for example? What in the above prohibits extending the behaviour to tables? I have yet to see what compatibility or similarity problem that'd pose. It seems all mightily handwavy to me. Yeah. It's possible there's a better interface here than INSTEAD OF, and one of the things I didn't like about the OP was that it started by stating the syntax that would be used rather than by describing the problem that needed to be solved. It's generally better to start with the latter, and then work out the syntax from there. But having gotten that gripe out of my system, and especially in view of Dean's comments, it's not very clear to me what's wrong with using INSTEAD OF for this purpose. If you make BEFORE triggers do this via RETURNING, then you might have a trigger that returns multiple rows, which seems like it would introduce a bunch of new complexity for no obvious benefit. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 4/2/15 11:50 AM, Dean Rasheed wrote: Well actually the fact that the code is structured that way is somewhat academic. INSTEAD OF triggers on views don't support WHEN conditions -- deliberately so, since it would be difficult to know in general what to do if the trigger didn't fire. So ExecInsert is implicitly using the existence of the trigger to imply that it will fire, although arguably it would be neater for it to double-check that, and error out if for some reason the trigger didn't fire. In any case, that doesn't establish any kind of behavioural precedent for how a conditional INSTEAD OF trigger on a table ought to work. I think the upshot is that INSTEAD OF triggers work in a particular way because that's what is needed to support updatable views. If triggers on tables should behave differently, maybe it should be a separate trigger type. Maybe it would be feasible to extend BEFORE triggers to support RETURNING, for example? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 2015-04-02 16:42:43 -0400, Peter Eisentraut wrote: On 4/2/15 11:50 AM, Dean Rasheed wrote: Well actually the fact that the code is structured that way is somewhat academic. INSTEAD OF triggers on views don't support WHEN conditions -- deliberately so, since it would be difficult to know in general what to do if the trigger didn't fire. So ExecInsert is implicitly using the existence of the trigger to imply that it will fire, although arguably it would be neater for it to double-check that, and error out if for some reason the trigger didn't fire. In any case, that doesn't establish any kind of behavioural precedent for how a conditional INSTEAD OF trigger on a table ought to work. I think the upshot is that INSTEAD OF triggers work in a particular way because that's what is needed to support updatable views. If triggers on tables should behave differently, maybe it should be a separate trigger type. Maybe it would be feasible to extend BEFORE triggers to support RETURNING, for example? What in the above prohibits extending the behaviour to tables? I have yet to see what compatibility or similarity problem that'd pose. It seems all mightily handwavy to me. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 2 April 2015 at 14:59, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Apr 1, 2015 at 1:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: It would absolutely *not* be reasonable for WHEN conditions for triggers on tables to work completely differently than they do for triggers on views. That ship's sailed. Clue me in, because I'm confused. If no trigger fires, we do whatever an object of that type would normally do in the absence of any trigger, no? For a view, that's error out; for a table, that's perform the action on the underlying data. That doesn't seem terribly unprincipled. I dunno about unprincipled; but we have already laid down the definition of INSTEAD OF triggers, and they act as I described. Read the code if you doubt it: which path is taken in ExecInsert depends only on whether INSTEAD OF triggers *exist* on the rel, not whether any of them actually fired (indeed, it would be difficult even to know that from here). I believe this was intentional, not just a coding artifact; it stems from having wanted to throw the error for uninsertable view well upstream of here, rather than having it be conditional on what happens at runtime. What I am objecting to is Andres' claim that it would be okay for INSTEAD OF triggers on tables to act completely differently in this regard from those on views. We have laid down the definition for views, and it is that nothing happens if the trigger exists but doesn't fire. Well actually the fact that the code is structured that way is somewhat academic. INSTEAD OF triggers on views don't support WHEN conditions -- deliberately so, since it would be difficult to know in general what to do if the trigger didn't fire. So ExecInsert is implicitly using the existence of the trigger to imply that it will fire, although arguably it would be neater for it to double-check that, and error out if for some reason the trigger didn't fire. In any case, that doesn't establish any kind of behavioural precedent for how a conditional INSTEAD OF trigger on a table ought to work. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On Wed, Apr 1, 2015 at 1:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andres Freund and...@anarazel.de writes: On 2015-04-01 13:29:33 -0400, Tom Lane wrote: WHEN won't help; if there are any INSTEAD OF triggers, no insert will happen, whether the triggers actually fire or not. Well, right now it doesn't work at all. It seems pretty reasonable to define things so that the insert happens normally if there's no matching INSTEAD OF trigger. It would absolutely *not* be reasonable for WHEN conditions for triggers on tables to work completely differently than they do for triggers on views. That ship's sailed. Clue me in, because I'm confused. If no trigger fires, we do whatever an object of that type would normally do in the absence of any trigger, no? For a view, that's error out; for a table, that's perform the action on the underlying data. That doesn't seem terribly unprincipled. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
Robert Haas robertmh...@gmail.com writes: On Wed, Apr 1, 2015 at 1:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: It would absolutely *not* be reasonable for WHEN conditions for triggers on tables to work completely differently than they do for triggers on views. That ship's sailed. Clue me in, because I'm confused. If no trigger fires, we do whatever an object of that type would normally do in the absence of any trigger, no? For a view, that's error out; for a table, that's perform the action on the underlying data. That doesn't seem terribly unprincipled. I dunno about unprincipled; but we have already laid down the definition of INSTEAD OF triggers, and they act as I described. Read the code if you doubt it: which path is taken in ExecInsert depends only on whether INSTEAD OF triggers *exist* on the rel, not whether any of them actually fired (indeed, it would be difficult even to know that from here). I believe this was intentional, not just a coding artifact; it stems from having wanted to throw the error for uninsertable view well upstream of here, rather than having it be conditional on what happens at runtime. What I am objecting to is Andres' claim that it would be okay for INSTEAD OF triggers on tables to act completely differently in this regard from those on views. We have laid down the definition for views, and it is that nothing happens if the trigger exists but doesn't fire. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 2015-04-01 11:40:13 -0400, Robert Haas wrote: On Tue, Mar 31, 2015 at 8:49 AM, Aliouii Ali aliouii@aol.fr wrote: I don't see how this helps. The problem with partitioning is that you need a way to redirect the INSERT to another table, and there's no built-in way to do that, so you have to simulate it somehow. That issue seems largely separate from how the CREATE TRIGGER command is spelled. Maybe I'm missing something. Without INSTEAD OF you can't, to my knowledge, return a valid tuple from the top level table without also inserting into it. Returning NULL after redirecting the tuple into a child table will break RETURNING; not returning NULL will insert the tuple in the top level table. So the only way to do redirection that doesn't break RETURNING without rules is to insert the tuple in the child in the BEFORE trigger return NEW and delete the top level table row in an AFTER trigger. That sucks. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On Tue, Mar 31, 2015 at 8:49 AM, Aliouii Ali aliouii@aol.fr wrote: hi all, back in 2011(http://www.postgresql.org/message-id/1305138588.8811.3.ca...@vanquo.pezone.net), an question the same as this one was asked the anwser was : I think they're very useful on views, but I couldn't think of a use-case for having them on tables. ISTM that anything an INSTEAD OF trigger on a table could do, could equally well be done in a BEFORE trigger. no not really there is a use-case : in partitioned table ( instead of defining before trigger on the master table that return null as the doc states, it will be good things to have instead of trigger that return NEW) so that query like insert/update ... .. RETURNING will be handdy and gain some performance, otherwise we will have to do an insert and select to get the same jobs done I don't see how this helps. The problem with partitioning is that you need a way to redirect the INSERT to another table, and there's no built-in way to do that, so you have to simulate it somehow. That issue seems largely separate from how the CREATE TRIGGER command is spelled. Maybe I'm missing something. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 2015-04-01 13:15:26 -0400, Tom Lane wrote: Andres Freund and...@anarazel.de writes: On 2015-04-01 12:46:05 -0400, Robert Haas wrote: So, the idea is that INSTEAD OF would behave like BEFORE but the tuple it returns wouldn't actually be inserted? That wasn't clear to me from the OP, but I guess it would be a reasonable way to go. I'm not sure what the OP intended, but to me that's pretty much the only reasonable definition of INSTEAD OF for tables that I can think of. If you have such a trigger, it's impossible to insert any rows, which means the table doesn't need storage, which means it may as well be a view, no? So this still seems to me like a wart not a useful feature. I think it would create confusion because a table with such a trigger would act so much unlike other tables. For one you can't easily add partitions to a view (and constraint_exclusion = partition IIRC doesn't work if you use UNION ALL), for another there's WHEN for triggers that should allow dealing with that. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
Tom Lane wrote: Andres Freund and...@anarazel.de writes: On 2015-04-01 12:46:05 -0400, Robert Haas wrote: So, the idea is that INSTEAD OF would behave like BEFORE but the tuple it returns wouldn't actually be inserted? That wasn't clear to me from the OP, but I guess it would be a reasonable way to go. I'm not sure what the OP intended, but to me that's pretty much the only reasonable definition of INSTEAD OF for tables that I can think of. If you have such a trigger, it's impossible to insert any rows, which means the table doesn't need storage, which means it may as well be a view, no? The interesting difference, as per upthread, is that you can have child tables (partitions) and don't need a defining query but instead have a defined set of named and typed columns. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 2015-04-01 13:29:33 -0400, Tom Lane wrote: Andres Freund and...@anarazel.de writes: On 2015-04-01 13:15:26 -0400, Tom Lane wrote: If you have such a trigger, it's impossible to insert any rows, which means the table doesn't need storage, which means it may as well be a view, no? So this still seems to me like a wart not a useful feature. I think it would create confusion because a table with such a trigger would act so much unlike other tables. For one you can't easily add partitions to a view (and constraint_exclusion = partition IIRC doesn't work if you use UNION ALL), for another there's WHEN for triggers that should allow dealing with that. WHEN won't help; if there are any INSTEAD OF triggers, no insert will happen, whether the triggers actually fire or not. Well, right now it doesn't work at all. It seems pretty reasonable to define things so that the insert happens normally if there's no matching INSTEAD OF trigger. As for partitioning, you could do this: create table parent(...); create table child(...) inherits(parent); -- repeat as needed create view v as select * from parent; attach INSTEAD OF triggers to v Now the application deals only with v, and thinks that's the real table. Sure, but that's just making things unnecessarily hard. That then requires also defining UPDATE/DELETE INSTEAD triggers which otherwise would just work. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On Wed, Apr 1, 2015 at 12:04 PM, Andres Freund and...@2ndquadrant.com wrote: On 2015-04-01 11:40:13 -0400, Robert Haas wrote: I don't see how this helps. The problem with partitioning is that you need a way to redirect the INSERT to another table, and there's no built-in way to do that, so you have to simulate it somehow. That issue seems largely separate from how the CREATE TRIGGER command is spelled. Maybe I'm missing something. Without INSTEAD OF you can't, to my knowledge, return a valid tuple from the top level table without also inserting into it. Returning NULL after redirecting the tuple into a child table will break RETURNING; not returning NULL will insert the tuple in the top level table. So the only way to do redirection that doesn't break RETURNING without rules is to insert the tuple in the child in the BEFORE trigger return NEW and delete the top level table row in an AFTER trigger. That sucks. So, the idea is that INSTEAD OF would behave like BEFORE but the tuple it returns wouldn't actually be inserted? That wasn't clear to me from the OP, but I guess it would be a reasonable way to go. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
Andres Freund and...@anarazel.de writes: On 2015-04-01 13:29:33 -0400, Tom Lane wrote: WHEN won't help; if there are any INSTEAD OF triggers, no insert will happen, whether the triggers actually fire or not. Well, right now it doesn't work at all. It seems pretty reasonable to define things so that the insert happens normally if there's no matching INSTEAD OF trigger. It would absolutely *not* be reasonable for WHEN conditions for triggers on tables to work completely differently than they do for triggers on views. That ship's sailed. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 1 April 2015 at 18:37, Andres Freund and...@anarazel.de wrote: On 2015-04-01 13:29:33 -0400, Tom Lane wrote: As for partitioning, you could do this: create table parent(...); create table child(...) inherits(parent); -- repeat as needed create view v as select * from parent; attach INSTEAD OF triggers to v Now the application deals only with v, and thinks that's the real table. Sure, but that's just making things unnecessarily hard. That then requires also defining UPDATE/DELETE INSTEAD triggers which otherwise would just work. No, because as defined above the view v would be auto-updatable, so updates and deletes on v would just do the matching update/delete on parent. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
Andres Freund and...@anarazel.de writes: On 2015-04-01 13:15:26 -0400, Tom Lane wrote: If you have such a trigger, it's impossible to insert any rows, which means the table doesn't need storage, which means it may as well be a view, no? So this still seems to me like a wart not a useful feature. I think it would create confusion because a table with such a trigger would act so much unlike other tables. For one you can't easily add partitions to a view (and constraint_exclusion = partition IIRC doesn't work if you use UNION ALL), for another there's WHEN for triggers that should allow dealing with that. WHEN won't help; if there are any INSTEAD OF triggers, no insert will happen, whether the triggers actually fire or not. As for partitioning, you could do this: create table parent(...); create table child(...) inherits(parent); -- repeat as needed create view v as select * from parent; attach INSTEAD OF triggers to v Now the application deals only with v, and thinks that's the real table. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 2015-04-01 12:46:05 -0400, Robert Haas wrote: On Wed, Apr 1, 2015 at 12:04 PM, Andres Freund and...@2ndquadrant.com wrote: On 2015-04-01 11:40:13 -0400, Robert Haas wrote: Without INSTEAD OF you can't, to my knowledge, return a valid tuple from the top level table without also inserting into it. Returning NULL after redirecting the tuple into a child table will break RETURNING; not returning NULL will insert the tuple in the top level table. So the only way to do redirection that doesn't break RETURNING without rules is to insert the tuple in the child in the BEFORE trigger return NEW and delete the top level table row in an AFTER trigger. That sucks. So, the idea is that INSTEAD OF would behave like BEFORE but the tuple it returns wouldn't actually be inserted? That wasn't clear to me from the OP, but I guess it would be a reasonable way to go. I'm not sure what the OP intended, but to me that's pretty much the only reasonable definition of INSTEAD OF for tables that I can think of. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
Andres Freund and...@anarazel.de writes: On 2015-04-01 12:46:05 -0400, Robert Haas wrote: So, the idea is that INSTEAD OF would behave like BEFORE but the tuple it returns wouldn't actually be inserted? That wasn't clear to me from the OP, but I guess it would be a reasonable way to go. I'm not sure what the OP intended, but to me that's pretty much the only reasonable definition of INSTEAD OF for tables that I can think of. If you have such a trigger, it's impossible to insert any rows, which means the table doesn't need storage, which means it may as well be a view, no? So this still seems to me like a wart not a useful feature. I think it would create confusion because a table with such a trigger would act so much unlike other tables. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Tables cannot have INSTEAD OF triggers
hi all, back in 2011(http://www.postgresql.org/message-id/1305138588.8811.3.ca...@vanquo.pezone.net), an question the same as this one was asked the anwser was : I think they're very useful on views, but I couldn't think of a use-case for having them on tables. ISTM that anything an INSTEAD OF trigger on a table could do, could equally well be done in a BEFORE trigger. no not really there is a use-case : in partitioned table ( instead of defining before trigger on the master table that return null as the doc states, it will be good things to have instead of trigger that return NEW) so that query like insert/update ... .. RETURNING will be handdy and gain some performance, otherwise we will have to do an insert and select to get the same jobs done and about : If we did support INSTEAD OF triggers on tables, we would also need to decide how they interact with BEFORE/AFTER triggers - do they fire in between them, or do they replace them? I could see arguments for either behaviour. we already have the three trigger defined on view. the same behavior goes on table. in the doc http://www.postgresql.org/docs/9.4/static/trigger-definition.html it mention that if a one trigger return a no null value then fire the next one else ignore some guys on postgresql irc channel says that it is easy to implement :) . so it will be good to have it in the next minor or major release ..
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 11 May 2011 19:51, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: Why not? Is there a fundamental problem, or just that no one wanted to make it work? More the latter really. I think they're very useful on views, but I couldn't think of a use-case for having them on tables. ISTM that anything an INSTEAD OF trigger on a table could do, could equally well be done in a BEFORE trigger. (BTW, the current behaviour is per SQL spec.) I'm fairly sure there was a substantive issue, but memory fails as to what it was. You could try removing the error check and see what breaks ... The first thing that would break is the Assert that checks oldtuple (for UPDATE/DELETE), since the 'wholerow' attribute that INSTEAD OF triggers rely on isn't built for a table. This is probably fixable, I'm just not convinced that it's worth it. If we did support INSTEAD OF triggers on tables, we would also need to decide how they interact with BEFORE/AFTER triggers - do they fire in between them, or do they replace them? I could see arguments for either behaviour. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Tables cannot have INSTEAD OF triggers
Why not? Is there a fundamental problem, or just that no one wanted to make it work? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tables cannot have INSTEAD OF triggers
Peter Eisentraut pete...@gmx.net writes: Why not? Is there a fundamental problem, or just that no one wanted to make it work? I'm fairly sure there was a substantive issue, but memory fails as to what it was. You could try removing the error check and see what breaks ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
