Re: TRUNCATE on foreign tables

Stephen Frost <[email protected]> Thu, 2 Jan 2020 09:46:44 -0500
Newsgroups	gmane.comp.db.postgresql.devel.general
Message-ID	<[email protected]>
Greetings,

* Kohei KaiGai ([email protected]) wrote:
> 2020年1月2日(木) 20:56 Alvaro Herrera <[email protected]>:
> > On 2020-Jan-02, Kohei KaiGai wrote:
> > > 2020年1月2日(木) 12:16 Alvaro Herrera <[email protected]>:
> > > > I think this would need to preserve the notion of multi-table truncates.
> > > > Otherwise it won't be possible to truncate tables linked by FKs.  I
> > > > think this means the new entrypoint needs to receive a list of rels to
> > > > truncate, not just one.  (Maybe an alternative is to make it "please
> > > > truncate rel X, and be aware that relations Y,Z are also being
> > > > truncated at the same time".)
> > >
> > > Please check at ExecuteTruncateGuts(). It makes a list of relations to be
> > > truncated, including the relations that references the specified table by FK,
> > > prior to invocation of the new FDW callback.

Erm, sure it does, but we don't support having FKs on foreign tables
today, so that doesn't really help with this issue, does it?

> > > So, if multiple foreign tables are involved in a single TRUNCATE command,
> > > this callback can be invoked multiple times.
> >
> > Yeah, that's my concern: if you have postgres_fdw tables linked by FKs
> > in the remote server, the truncate will fail because it'll try to
> > truncate them in separate commands instead of using a multi-table
> > truncate.

I agree that the FDW callback should support multiple tables in the
TRUNCATE, but I think it also should include CASCADE as an option and
have that be passed on to the FDW to handle.

> Ah, it makes sense.
> Probably, backend can make sub-list of the foreign tables to be
> truncated for each
> pair of FDW and Server, then invoke the FDW callback only once with this list.
> FDW driver can issue multi-tables truncate on all the foreign tables
> supplied, with
> nothing difficult to do.

This doesn't really make sense as we don't track FK relationships in the
local server for foreign tables today- now, perhaps we should (and things
like primary keys too..), but I don't think that needs to be the job of
this particular patch.  Instead, I'd suggest we have the core code build
up a list of tables to truncate, for each server, based just on the list
passed in by the user, and then also pass in if CASCADE was included or
not, and then let the FDW handle that in whatever way makes sense for
the foreign server (which, for a PG system, would probably be just
building up the TRUNCATE command and running it with or without the
CASCADE option, but it might be different on other systems).

Just to be clear- I don't mean to suggest that we should explicitly
avoid the logic in TruncateGuts that builds up the list when CASCADE is
used, just saying that it's not going to actually do anything when we're
talking about foreign tables- and that's *fine*.  I don't think we need
to do more here until we're actually tracking remote FKs locally.

So, I think the patch just needs a bit of minor adjustment for that to
make it work for the case that Alvaro is concerned about.  One thing
that isn't really clear to me is if we should also support the 'ONLY'
option to TRUNCATE when it comes to FDWs; a table can't be both foreign
and partitioned, so it's not an issue there, but a foreign table CAN
be a child table of another foreign table.

Of course, if that's the case, things get pretty odd looking pretty
quickly if both sides see the table as a child table because we
actually end up scanning the foreign parent (which will include rows
from the child on the remote side) and then scanning the foreign child
*again*, resulting in duplicate rows coming back, so I'm not really sure
how much effort we should be thinking about putting into dealing with
child foreign tables..

Thanks,

Stephen
signature.asc (application/pgp-signature, 819 B)