Обсуждение: truncate trigger for foreign data wrappers

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