Thread: truncate trigger for foreign data wrappers
Hello
I recently hit a road blocker when I tried to create a truncate trigger on a foreign table. trigger.c::CreateTrigger() function has explicit check to block truncate trigger on foreign tables.
However, trigger.c::ExecuteTruncate() does not seem to have any problems issuing before/after triggers to fdws.
Just tapping on the utility hook to catch truncate statement did not work when the fdw is inside inheritance hierarchy.
Is there a way to enable truncate triggers for foreign tables in the long run ? Or be able to detect somehow my fdw table is getting a truncate request ?
thanks
--
Murat Tuncer <mtuncer@citusdata.com> writes:
> I recently hit a road blocker when I tried to create a truncate trigger on
> a foreign table. trigger.c::CreateTrigger() function has explicit check to
> block truncate trigger on foreign tables.
That's good, because we don't implement TRUNCATE on foreign tables: there
is nothing in the FDW API that would support it. Not much point in
declaring a trigger for an event that can never happen.
regards, tom lane
On 2016-08-05 10:33:49 -0400, Tom Lane wrote: > Murat Tuncer <mtuncer@citusdata.com> writes: > > I recently hit a road blocker when I tried to create a truncate trigger on > > a foreign table. trigger.c::CreateTrigger() function has explicit check to > > block truncate trigger on foreign tables. > > That's good, because we don't implement TRUNCATE on foreign tables: there > is nothing in the FDW API that would support it. Not much point in > declaring a trigger for an event that can never happen. Well, allowing BEFORE triggers to return NULL or something, preventing the execution of the rest, would be such an implementation, and also independently useful.
On Fri, Aug 5, 2016 at 10:39 AM, Andres Freund <andres@anarazel.de> wrote: > On 2016-08-05 10:33:49 -0400, Tom Lane wrote: >> Murat Tuncer <mtuncer@citusdata.com> writes: >> > I recently hit a road blocker when I tried to create a truncate trigger on >> > a foreign table. trigger.c::CreateTrigger() function has explicit check to >> > block truncate trigger on foreign tables. >> >> That's good, because we don't implement TRUNCATE on foreign tables: there >> is nothing in the FDW API that would support it. Not much point in >> declaring a trigger for an event that can never happen. > > Well, allowing BEFORE triggers to return NULL or something, preventing > the execution of the rest, would be such an implementation, and also > independently useful. I guess. I think if we're going to add support utility commands on foreign tables, we ought to think about all of the different utility commands that someone might want and what exactly we want the behavior to be. For example, consider CLUSTER or CREATE INDEX or VACUUM or ANALYZE. We might interpret TRUNCATE or CLUSTER as a request to dispatch the same request for the remote side, but ANALYZE can't mean that: it has to mean gather local statistics. And what if the other side is not PG and supports other operations that we don't have, like OPTIMIZE TABLE or DISENGAGE FTL? That isn't, strictly speaking, a reason to reject a patch that just allows TRUNCATE triggers on FDWs to "return NULL or something", but I can't get very excited about such a patch because I think the utility is fairly marginal. I'd rather have a little more of a plan than that before we go start tinkering. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2016-08-05 13:32:18 -0400, Robert Haas wrote: > I think if we're going to add support utility commands on foreign > tables, we ought to think about all of the different utility commands > that someone might want and what exactly we want the behavior to be. > For example, consider CLUSTER or CREATE INDEX or VACUUM or ANALYZE. > We might interpret TRUNCATE or CLUSTER as a request to dispatch the > same request for the remote side, but ANALYZE can't mean that: it has > to mean gather local statistics. And what if the other side is not PG > and supports other operations that we don't have, like OPTIMIZE TABLE > or DISENGAGE FTL? That's not really comparable imo - we don't have triggers for those locally either. For better or worse we've decided that TRUNCATE is more like DML than DDL. Greetings, Andres Freund
On Fri, Aug 5, 2016 at 2:04 PM, Andres Freund <andres@anarazel.de> wrote: > On 2016-08-05 13:32:18 -0400, Robert Haas wrote: >> I think if we're going to add support utility commands on foreign >> tables, we ought to think about all of the different utility commands >> that someone might want and what exactly we want the behavior to be. > >> For example, consider CLUSTER or CREATE INDEX or VACUUM or ANALYZE. >> We might interpret TRUNCATE or CLUSTER as a request to dispatch the >> same request for the remote side, but ANALYZE can't mean that: it has >> to mean gather local statistics. And what if the other side is not PG >> and supports other operations that we don't have, like OPTIMIZE TABLE >> or DISENGAGE FTL? > > That's not really comparable imo - we don't have triggers for those > locally either. For better or worse we've decided that TRUNCATE is more > like DML than DDL. I agree, but I still think it's weird if foreign tables support TRUNCATE itself not but triggers on TRUNCATE. And I don't want to start supporting TRUNCATE itself without a bit more thought about where we go from there. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2016-08-05 14:05:02 -0400, Robert Haas wrote: > On Fri, Aug 5, 2016 at 2:04 PM, Andres Freund <andres@anarazel.de> wrote: > > On 2016-08-05 13:32:18 -0400, Robert Haas wrote: > >> I think if we're going to add support utility commands on foreign > >> tables, we ought to think about all of the different utility commands > >> that someone might want and what exactly we want the behavior to be. > > > >> For example, consider CLUSTER or CREATE INDEX or VACUUM or ANALYZE. > >> We might interpret TRUNCATE or CLUSTER as a request to dispatch the > >> same request for the remote side, but ANALYZE can't mean that: it has > >> to mean gather local statistics. And what if the other side is not PG > >> and supports other operations that we don't have, like OPTIMIZE TABLE > >> or DISENGAGE FTL? > > > > That's not really comparable imo - we don't have triggers for those > > locally either. For better or worse we've decided that TRUNCATE is more > > like DML than DDL. > > I agree, but I still think it's weird if foreign tables support > TRUNCATE itself not but triggers on TRUNCATE. You mean the other way round? To me this seems very comparable to INSTEAD triggers, but ...
Andres Freund <andres@anarazel.de> writes:
> On 2016-08-05 14:05:02 -0400, Robert Haas wrote:
>> I agree, but I still think it's weird if foreign tables support
>> TRUNCATE itself not but triggers on TRUNCATE.
> You mean the other way round? To me this seems very comparable to
> INSTEAD triggers, but ...
While I have no particular objection to allowing TRUNCATE triggers on
FDWs, I concur with Robert's feeling that we ought to sketch out a plan
for what utility commands on foreign tables should do in general, before
we go introducing individual new features. It would be annoying if we
stuck this in with little forethought and then found out it was
inconsistent with other stuff people want to add later.
In particular, it seems to me that rather than implement just this,
we really ought to provide an API that lets FDWs actually implement
TRUNCATE if they feel like it. Having the trigger and not TRUNCATE
capability itself just screams "half baked", wouldn't you say?
regards, tom lane
On 2016-08-05 16:35:02 -0400, Tom Lane wrote: > In particular, it seems to me that rather than implement just this, > we really ought to provide an API that lets FDWs actually implement > TRUNCATE if they feel like it. Having the trigger and not TRUNCATE > capability itself just screams "half baked", wouldn't you say? Both is fine with me. I do object to the position that we need an answer for all utility commands - that seems like a too high hurdle to pass - but implementing truncate for FDWs directly sounds good.
Andres Freund <andres@anarazel.de> writes:
> On 2016-08-05 16:35:02 -0400, Tom Lane wrote:
>> In particular, it seems to me that rather than implement just this,
>> we really ought to provide an API that lets FDWs actually implement
>> TRUNCATE if they feel like it. Having the trigger and not TRUNCATE
>> capability itself just screams "half baked", wouldn't you say?
> Both is fine with me. I do object to the position that we need an answer
> for all utility commands - that seems like a too high hurdle to pass -
> but implementing truncate for FDWs directly sounds good.
To clarify: I was certainly not suggesting that we need to implement
more than that in the first go. I was just saying that some sort of
long-term roadmap about utility commands for FDWs would be a good idea.
regards, tom lane
On 2016-08-05 16:44:20 -0400, Tom Lane wrote: > Andres Freund <andres@anarazel.de> writes: > > On 2016-08-05 16:35:02 -0400, Tom Lane wrote: > >> In particular, it seems to me that rather than implement just this, > >> we really ought to provide an API that lets FDWs actually implement > >> TRUNCATE if they feel like it. Having the trigger and not TRUNCATE > >> capability itself just screams "half baked", wouldn't you say? > > > Both is fine with me. I do object to the position that we need an answer > > for all utility commands - that seems like a too high hurdle to pass - > > but implementing truncate for FDWs directly sounds good. > > To clarify: I was certainly not suggesting that we need to implement > more than that in the first go. I was just saying that some sort of > long-term roadmap about utility commands for FDWs would be a good idea. Well, my problem with that is that I don't see TRUNCATE as being in the same camp as most other utility commands; thus I'm not sure there's really a coherent view for it and the rest. In the end it's just an optimized DELETE, and we didn't say that other DML needs to provide a view for utility commands either. - Andres