Thank you for your comment! :D I have fixed it and attached the revised patch.
regards, 2021年2月7日(日) 2:08 Zhihong Yu <z...@yugabyte.com>: > Hi, > + if (strcmp(defel->defname, "truncatable") == 0) > + server_truncatable = defGetBoolean(defel); > > Looks like we can break out of the loop when the condition is met. > > + /* ExecForeignTruncate() is invoked for each server */ > > The method name in the comment is slightly different from the actual > method name. > > + if (strcmp(defel->defname, "truncatable") == 0) > + truncatable = defGetBoolean(defel); > > We can break out of the loop when the condition is met. > > Cheers > > On Sat, Feb 6, 2021 at 5:11 AM Kazutaka Onishi <oni...@heterodb.com> > wrote: > >> Hello, >> >> The attached patch is for supporting "TRUNCATE" on foreign tables. >> >> This patch includes: >> * Adding "ExecForeignTruncate" function into FdwRoutine. >> * Enabling "postgres_fdw" to use TRUNCATE. >> >> This patch was proposed by Kaigai-san in March 2020, >> but it was returned because it can't be applied to the latest source >> codes. >> >> Please refer to the discussion. >> >> https://www.postgresql.org/message-id/flat/CAOP8fzb-t3WVNLjGMC%2B4sV4AZa9S%3DMAQ7Q6pQoADMCf_1jp4ew%40mail.gmail.com#3b6c6ff85ff5c722b36c7a09b2dd7165 >> >> I have fixed the patch due to submit it to Commit Fest 2021-03. >> >> regards, >> >> -- >> ------------------ >> Kazutaka Onishi >> (oni...@heterodb.com) >> > -- ------------------ Kazutaka Onishi (oni...@heterodb.com) -- ------------------ Kazutaka Onishi (oni...@heterodb.com)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 6faf499f9a..a9ce323a67 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -2171,6 +2171,44 @@ deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs) deparseRelation(buf, rel); } +/* + * Construct a simple "TRUNCATE rel" statement + */ +void +deparseTruncateSql(StringInfo buf, + List *frels_list, + List *frels_extra, + DropBehavior behavior, + bool restart_seqs) +{ + ListCell *lc1, *lc2; + + appendStringInfoString(buf, "TRUNCATE "); + forboth (lc1, frels_list, + lc2, frels_extra) + { + Relation frel = lfirst(lc1); + int extra = lfirst_int(lc2); + + if (lc1 != list_head(frels_list)) + appendStringInfoString(buf, ", "); + if (extra != 0) + appendStringInfoString(buf, "ONLY "); + deparseRelation(buf, frel); + } + appendStringInfo(buf, " %s IDENTITY", + restart_seqs ? "RESTART" : "CONTINUE"); + switch (behavior) + { + case DROP_RESTRICT: + appendStringInfoString(buf, " RESTRICT"); + break; + case DROP_CASCADE: + appendStringInfoString(buf, " CASCADE"); + break; + } +} + /* * Construct name to use for given column, and emit it into buf. * If it has a column_name FDW option, use that instead of attribute name. diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 60c7e115d6..e50704d101 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -8247,6 +8247,239 @@ select * from rem3; drop foreign table rem3; drop table loc3; +-- =================================================================== +-- test for TRUNCATE +-- =================================================================== +CREATE TABLE tru_rtable0 (id int primary key, x text); +CREATE TABLE tru_rtable1 (id int primary key, y text); +CREATE FOREIGN TABLE tru_ftable (id int, x text) + SERVER loopback OPTIONS (table_name 'tru_rtable0'); +INSERT INTO tru_rtable0 (SELECT x,md5(x::text) FROM generate_series(1,10) x); +CREATE TABLE tru_ptable (id int, y text) PARTITION BY HASH(id); +CREATE TABLE tru_ptable__p0 PARTITION OF tru_ptable + FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE FOREIGN TABLE tru_ftable__p1 PARTITION OF tru_ptable + FOR VALUES WITH (MODULUS 2, REMAINDER 1) + SERVER loopback OPTIONS (table_name 'tru_rtable1'); +INSERT INTO tru_ptable (SELECT x,md5(x::text) FROM generate_series(11,20) x); +CREATE TABLE tru_pk_table(id int primary key, z text); +CREATE TABLE tru_fk_table(fkey int references tru_pk_table(id)); +INSERT INTO tru_pk_table (SELECT x,md5((x+1)::text) FROM generate_series(1,10) x); +INSERT INTO tru_fk_table (SELECT x % 10 + 1 FROM generate_series(5,25) x); +CREATE FOREIGN TABLE tru_pk_ftable (id int, z text) + SERVER loopback OPTIONS (table_name 'tru_pk_table'); +CREATE TABLE tru_rtable_parent (id int, a text); +CREATE TABLE tru_rtable_child (id int, a text); +CREATE FOREIGN TABLE tru_ftable_parent (id int, a text) + SERVER loopback OPTIONS (table_name 'tru_rtable_parent'); +CREATE FOREIGN TABLE tru_ftable_child () INHERITS (tru_ftable_parent) + SERVER loopback OPTIONS (table_name 'tru_rtable_child'); +INSERT INTO tru_rtable_parent (SELECT x, md5(x::text) FROM generate_series(1,8) x); +INSERT INTO tru_rtable_child (SELECT x, md5(x::text) FROM generate_series(10, 18) x); +-- normal truncate +SELECT * FROM tru_ftable; + id | x +----+---------------------------------- + 1 | c4ca4238a0b923820dcc509a6f75849b + 2 | c81e728d9d4c2f636f067f89cc14862c + 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 + 4 | a87ff679a2f3e71d9181a67b7542122c + 5 | e4da3b7fbbce2345d7772b0674a318d5 + 6 | 1679091c5a880faf6fb5e6087eb1b2dc + 7 | 8f14e45fceea167a5a36dedd4bea2543 + 8 | c9f0f895fb98ab9159f51fd0297e236d + 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 + 10 | d3d9446802a44259755d38e6d163e820 +(10 rows) + +TRUNCATE tru_ftable; +SELECT * FROM tru_rtable0; -- empty + id | x +----+--- +(0 rows) + +SELECT * FROM tru_ftable; -- empty + id | x +----+--- +(0 rows) + +-- 'truncatable' option +ALTER SERVER loopback OPTIONS (ADD truncatable 'false'); +TRUNCATE tru_ftable; -- error +ERROR: truncate on "tru_ftable" is prohibited +ALTER SERVER loopback OPTIONS (DROP truncatable); +ALTER FOREIGN TABLE tru_ftable OPTIONS (ADD truncatable 'false'); +TRUNCATE tru_ftable; -- error +ERROR: truncate on "tru_ftable" is prohibited +ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'true'); +TRUNCATE tru_ftable; -- accepted +-- partition table mixtured by table and foreign table +SELECT * FROM tru_ptable; + id | y +----+---------------------------------- + 12 | c20ad4d76fe97759aa27a0c99bff6710 + 13 | c51ce410c124a10e0db5e4b97fc2af39 + 14 | aab3238922bcc25a6f606eb525ffdc56 + 16 | c74d97b01eae257e44aa9d5bade97baf + 17 | 70efdf2ec9b086079795c442636b55fb + 18 | 6f4922f45568161a8cdf4ad2299f6d23 + 11 | 6512bd43d9caa6e02c990b0a82652dca + 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 + 19 | 1f0e3dad99908345f7439f8ffabdffc4 + 20 | 98f13708210194c475687be6106a3b84 +(10 rows) + +TRUNCATE tru_ptable; +SELECT * FROM tru_ptable; -- empty + id | y +----+--- +(0 rows) + +SELECT * FROM tru_ptable__p0; -- empty + id | y +----+--- +(0 rows) + +SELECT * FROM tru_ftable__p1; -- empty + id | y +----+--- +(0 rows) + +SELECT * FROM tru_rtable1; -- empty + id | y +----+--- +(0 rows) + +-- 'CASCADE' option +SELECT * FROM tru_pk_ftable; + id | z +----+---------------------------------- + 1 | c81e728d9d4c2f636f067f89cc14862c + 2 | eccbc87e4b5ce2fe28308fd9f2a7baf3 + 3 | a87ff679a2f3e71d9181a67b7542122c + 4 | e4da3b7fbbce2345d7772b0674a318d5 + 5 | 1679091c5a880faf6fb5e6087eb1b2dc + 6 | 8f14e45fceea167a5a36dedd4bea2543 + 7 | c9f0f895fb98ab9159f51fd0297e236d + 8 | 45c48cce2e2d7fbdea1afc51c7c6ad26 + 9 | d3d9446802a44259755d38e6d163e820 + 10 | 6512bd43d9caa6e02c990b0a82652dca +(10 rows) + +TRUNCATE tru_pk_ftable; -- failed by FK reference +ERROR: cannot truncate a table referenced in a foreign key constraint +DETAIL: Table "tru_fk_table" references "tru_pk_table". +HINT: Truncate table "tru_fk_table" at the same time, or use TRUNCATE ... CASCADE. +CONTEXT: remote SQL command: TRUNCATE public.tru_pk_table CONTINUE IDENTITY RESTRICT +TRUNCATE tru_pk_ftable CASCADE; +SELECT * FROM tru_pk_ftable; + id | z +----+--- +(0 rows) + +SELECT * FROM tru_fk_table; -- also truncated + fkey +------ +(0 rows) + +-- truncate two tables at a command +INSERT INTO tru_ftable (SELECT x,md5((x+2)::text) FROM generate_series(1,8) x); +INSERT INTO tru_pk_ftable (SELECT x,md5((x+3)::text) FROM generate_series(3,10) x); +SELECT * FROM tru_ftable a FULL OUTER JOIN tru_pk_ftable b ON a.id = b.id; + id | x | id | z +----+----------------------------------+----+---------------------------------- + 1 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | | + 2 | a87ff679a2f3e71d9181a67b7542122c | | + 3 | e4da3b7fbbce2345d7772b0674a318d5 | 3 | 1679091c5a880faf6fb5e6087eb1b2dc + 4 | 1679091c5a880faf6fb5e6087eb1b2dc | 4 | 8f14e45fceea167a5a36dedd4bea2543 + 5 | 8f14e45fceea167a5a36dedd4bea2543 | 5 | c9f0f895fb98ab9159f51fd0297e236d + 6 | c9f0f895fb98ab9159f51fd0297e236d | 6 | 45c48cce2e2d7fbdea1afc51c7c6ad26 + 7 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 7 | d3d9446802a44259755d38e6d163e820 + 8 | d3d9446802a44259755d38e6d163e820 | 8 | 6512bd43d9caa6e02c990b0a82652dca + | | 9 | c20ad4d76fe97759aa27a0c99bff6710 + | | 10 | c51ce410c124a10e0db5e4b97fc2af39 +(10 rows) + +TRUNCATE tru_ftable, tru_pk_ftable CASCADE; +SELECT * FROM tru_ftable a FULL OUTER JOIN tru_pk_ftable b ON a.id = b.id; + id | x | id | z +----+---+----+--- +(0 rows) + +-- truncate with ONLY clause +TRUNCATE ONLY tru_ftable_parent; +SELECT * FROM tru_ftable_parent; + id | a +----+---------------------------------- + 10 | d3d9446802a44259755d38e6d163e820 + 11 | 6512bd43d9caa6e02c990b0a82652dca + 12 | c20ad4d76fe97759aa27a0c99bff6710 + 13 | c51ce410c124a10e0db5e4b97fc2af39 + 14 | aab3238922bcc25a6f606eb525ffdc56 + 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 + 16 | c74d97b01eae257e44aa9d5bade97baf + 17 | 70efdf2ec9b086079795c442636b55fb + 18 | 6f4922f45568161a8cdf4ad2299f6d23 +(9 rows) + +TRUNCATE tru_ftable_parent; +SELECT * FROM tru_ftable_parent; + id | a +----+--- +(0 rows) + +-- in case when remote table has inherited children +CREATE TABLE tru_rtable0_child () INHERITS (tru_rtable0); +INSERT INTO tru_rtable0 (SELECT x,md5(x::text) FROM generate_series(5,9) x); +INSERT INTO tru_rtable0_child (SELECT x,md5(x::text) FROM generate_series(10,14) x); +SELECT * FROM tru_ftable; + id | x +----+---------------------------------- + 5 | e4da3b7fbbce2345d7772b0674a318d5 + 6 | 1679091c5a880faf6fb5e6087eb1b2dc + 7 | 8f14e45fceea167a5a36dedd4bea2543 + 8 | c9f0f895fb98ab9159f51fd0297e236d + 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 + 10 | d3d9446802a44259755d38e6d163e820 + 11 | 6512bd43d9caa6e02c990b0a82652dca + 12 | c20ad4d76fe97759aa27a0c99bff6710 + 13 | c51ce410c124a10e0db5e4b97fc2af39 + 14 | aab3238922bcc25a6f606eb525ffdc56 +(10 rows) + +TRUNCATE ONLY tru_ftable; -- truncate only parent portion +SELECT * FROM tru_ftable; + id | x +----+---------------------------------- + 10 | d3d9446802a44259755d38e6d163e820 + 11 | 6512bd43d9caa6e02c990b0a82652dca + 12 | c20ad4d76fe97759aa27a0c99bff6710 + 13 | c51ce410c124a10e0db5e4b97fc2af39 + 14 | aab3238922bcc25a6f606eb525ffdc56 +(5 rows) + +INSERT INTO tru_rtable0 (SELECT x,md5(x::text) FROM generate_series(21,25) x); +SELECT * FROM tru_ftable; + id | x +----+---------------------------------- + 21 | 3c59dc048e8850243be8079a5c74d079 + 22 | b6d767d2f8ed5d21a44b0e5886680cb9 + 23 | 37693cfc748049e45d87b8c7d8b9aacd + 24 | 1ff1de774005f8da13f42943881c655f + 25 | 8e296a067a37563370ded05f5a3bf3ec + 10 | d3d9446802a44259755d38e6d163e820 + 11 | 6512bd43d9caa6e02c990b0a82652dca + 12 | c20ad4d76fe97759aa27a0c99bff6710 + 13 | c51ce410c124a10e0db5e4b97fc2af39 + 14 | aab3238922bcc25a6f606eb525ffdc56 +(10 rows) + +TRUNCATE tru_ftable; -- truncate both of parent and child +SELECT * FROM tru_ftable; + id | x +----+--- +(0 rows) + -- =================================================================== -- test IMPORT FOREIGN SCHEMA -- =================================================================== @@ -8946,7 +9179,7 @@ DO $d$ END; $d$; ERROR: invalid option "password" -HINT: Valid options in this context are: service, passfile, channel_binding, connect_timeout, dbname, host, hostaddr, port, options, application_name, keepalives, keepalives_idle, keepalives_interval, keepalives_count, tcp_user_timeout, sslmode, sslcompression, sslcert, sslkey, sslrootcert, sslcrl, requirepeer, ssl_min_protocol_version, ssl_max_protocol_version, gssencmode, krbsrvname, gsslib, target_session_attrs, use_remote_estimate, fdw_startup_cost, fdw_tuple_cost, extensions, updatable, fetch_size, batch_size +HINT: Valid options in this context are: service, passfile, channel_binding, connect_timeout, dbname, host, hostaddr, port, options, application_name, keepalives, keepalives_idle, keepalives_interval, keepalives_count, tcp_user_timeout, sslmode, sslcompression, sslcert, sslkey, sslrootcert, sslcrl, requirepeer, ssl_min_protocol_version, ssl_max_protocol_version, gssencmode, krbsrvname, gsslib, target_session_attrs, use_remote_estimate, fdw_startup_cost, fdw_tuple_cost, extensions, updatable, truncatable, fetch_size, batch_size CONTEXT: SQL statement "ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw')" PL/pgSQL function inline_code_block line 3 at EXECUTE -- If we add a password for our user mapping instead, we should get a different diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c index 64698c4da3..668178524d 100644 --- a/contrib/postgres_fdw/option.c +++ b/contrib/postgres_fdw/option.c @@ -107,7 +107,8 @@ postgres_fdw_validator(PG_FUNCTION_ARGS) * Validate option value, when we can do so without any context. */ if (strcmp(def->defname, "use_remote_estimate") == 0 || - strcmp(def->defname, "updatable") == 0) + strcmp(def->defname, "updatable") == 0 || + strcmp(def->defname, "truncatable") == 0) { /* these accept only boolean values */ (void) defGetBoolean(def); @@ -211,6 +212,9 @@ InitPgFdwOptions(void) /* updatable is available on both server and table */ {"updatable", ForeignServerRelationId, false}, {"updatable", ForeignTableRelationId, false}, + /* truncatable is available on both server and table */ + {"truncatable", ForeignServerRelationId, false}, + {"truncatable", ForeignTableRelationId, false}, /* fetch_size is available on both server and table */ {"fetch_size", ForeignServerRelationId, false}, {"fetch_size", ForeignTableRelationId, false}, diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 0e977066a8..f2f77e6257 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -390,6 +390,10 @@ static void postgresExplainForeignModify(ModifyTableState *mtstate, ExplainState *es); static void postgresExplainDirectModify(ForeignScanState *node, ExplainState *es); +static void postgresExecForeignTruncate(List *frels_list, + List *frels_extra, + DropBehavior behavior, + bool restart_seqs); static bool postgresAnalyzeForeignTable(Relation relation, AcquireSampleRowsFunc *func, BlockNumber *totalpages); @@ -571,6 +575,9 @@ postgres_fdw_handler(PG_FUNCTION_ARGS) routine->ExplainForeignModify = postgresExplainForeignModify; routine->ExplainDirectModify = postgresExplainDirectModify; + /* Support function for TRUNCATE */ + routine->ExecForeignTruncate = postgresExecForeignTruncate; + /* Support functions for ANALYZE */ routine->AnalyzeForeignTable = postgresAnalyzeForeignTable; @@ -2772,6 +2779,90 @@ postgresExplainDirectModify(ForeignScanState *node, ExplainState *es) } } +/* + * postgresExecForeignTruncate + * It propagates TRUNCATE command to the remote host inside of the + * transaction block. + */ +static void +postgresExecForeignTruncate(List *frels_list, + List *frels_extra, + DropBehavior behavior, + bool restart_seqs) +{ + Oid server_id = InvalidOid; + ForeignServer *serv = NULL; + UserMapping *user = NULL; + PGconn *conn = NULL; + PGresult *res; + StringInfoData sql; + ListCell *lc; + bool server_truncatable = true; + + /* pick up remote connection, and sanity checks */ + foreach (lc, frels_list) + { + Relation frel = lfirst(lc); + Oid frel_oid = RelationGetRelid(frel); + ForeignTable *ft = GetForeignTable(frel_oid); + ListCell *cell; + bool truncatable; + + if (!OidIsValid(server_id)) + { + server_id = GetForeignServerIdByRelId(frel_oid); + serv = GetForeignServer(server_id); + user = GetUserMapping(GetUserId(), server_id); + conn = GetConnection(user, false); + + foreach (cell, serv->options) + { + DefElem *defel = (DefElem *) lfirst(cell); + + if (strcmp(defel->defname, "truncatable") == 0) + { + server_truncatable = defGetBoolean(defel); + break; + } + } + } + else + { + /* postgresExecForeignTruncate() is invoked for each server */ + Assert(server_id == GetForeignServerIdByRelId(frel_oid)); + } + + /* ensure the target foreign table is truncatable */ + truncatable = server_truncatable; + foreach (cell, ft->options) + { + DefElem *defel = (DefElem *) lfirst(cell); + + if (strcmp(defel->defname, "truncatable") == 0) + { + truncatable = defGetBoolean(defel); + break; + } + } + if (!truncatable) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("truncate on \"%s\" is prohibited", + RelationGetRelationName(frel)))); + } + /* set up remote query */ + initStringInfo(&sql); + deparseTruncateSql(&sql, frels_list, frels_extra, behavior, restart_seqs); + /* run remote query */ + if (!PQsendQuery(conn, sql.data)) + pgfdw_report_error(ERROR, NULL, conn, false, sql.data); + res = pgfdw_get_result(conn, sql.data); + if (PQresultStatus(res) != PGRES_COMMAND_OK) + pgfdw_report_error(ERROR, res, conn, true, sql.data); + /* clean-up */ + PQclear(res); + pfree(sql.data); +} /* * estimate_path_cost_size diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index 1f67b4d9fd..c89af59973 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -193,6 +193,11 @@ extern void deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root, extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel); extern void deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs); +extern void deparseTruncateSql(StringInfo buf, + List *frels_list, + List *frels_extra, + DropBehavior behavior, + bool restart_seqs); extern void deparseStringLiteral(StringInfo buf, const char *val); extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel); extern Expr *find_em_expr_for_input_target(PlannerInfo *root, diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 151f4f1834..8b028f67c9 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -2349,6 +2349,96 @@ select * from rem3; drop foreign table rem3; drop table loc3; +-- =================================================================== +-- test for TRUNCATE +-- =================================================================== +CREATE TABLE tru_rtable0 (id int primary key, x text); +CREATE TABLE tru_rtable1 (id int primary key, y text); +CREATE FOREIGN TABLE tru_ftable (id int, x text) + SERVER loopback OPTIONS (table_name 'tru_rtable0'); +INSERT INTO tru_rtable0 (SELECT x,md5(x::text) FROM generate_series(1,10) x); + +CREATE TABLE tru_ptable (id int, y text) PARTITION BY HASH(id); +CREATE TABLE tru_ptable__p0 PARTITION OF tru_ptable + FOR VALUES WITH (MODULUS 2, REMAINDER 0); +CREATE FOREIGN TABLE tru_ftable__p1 PARTITION OF tru_ptable + FOR VALUES WITH (MODULUS 2, REMAINDER 1) + SERVER loopback OPTIONS (table_name 'tru_rtable1'); +INSERT INTO tru_ptable (SELECT x,md5(x::text) FROM generate_series(11,20) x); + +CREATE TABLE tru_pk_table(id int primary key, z text); +CREATE TABLE tru_fk_table(fkey int references tru_pk_table(id)); +INSERT INTO tru_pk_table (SELECT x,md5((x+1)::text) FROM generate_series(1,10) x); +INSERT INTO tru_fk_table (SELECT x % 10 + 1 FROM generate_series(5,25) x); +CREATE FOREIGN TABLE tru_pk_ftable (id int, z text) + SERVER loopback OPTIONS (table_name 'tru_pk_table'); + +CREATE TABLE tru_rtable_parent (id int, a text); +CREATE TABLE tru_rtable_child (id int, a text); +CREATE FOREIGN TABLE tru_ftable_parent (id int, a text) + SERVER loopback OPTIONS (table_name 'tru_rtable_parent'); +CREATE FOREIGN TABLE tru_ftable_child () INHERITS (tru_ftable_parent) + SERVER loopback OPTIONS (table_name 'tru_rtable_child'); +INSERT INTO tru_rtable_parent (SELECT x, md5(x::text) FROM generate_series(1,8) x); +INSERT INTO tru_rtable_child (SELECT x, md5(x::text) FROM generate_series(10, 18) x); + +-- normal truncate +SELECT * FROM tru_ftable; +TRUNCATE tru_ftable; +SELECT * FROM tru_rtable0; -- empty +SELECT * FROM tru_ftable; -- empty + +-- 'truncatable' option +ALTER SERVER loopback OPTIONS (ADD truncatable 'false'); +TRUNCATE tru_ftable; -- error +ALTER SERVER loopback OPTIONS (DROP truncatable); +ALTER FOREIGN TABLE tru_ftable OPTIONS (ADD truncatable 'false'); +TRUNCATE tru_ftable; -- error +ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'true'); +TRUNCATE tru_ftable; -- accepted + +-- partition table mixtured by table and foreign table +SELECT * FROM tru_ptable; +TRUNCATE tru_ptable; +SELECT * FROM tru_ptable; -- empty +SELECT * FROM tru_ptable__p0; -- empty +SELECT * FROM tru_ftable__p1; -- empty +SELECT * FROM tru_rtable1; -- empty + +-- 'CASCADE' option +SELECT * FROM tru_pk_ftable; +TRUNCATE tru_pk_ftable; -- failed by FK reference +TRUNCATE tru_pk_ftable CASCADE; +SELECT * FROM tru_pk_ftable; +SELECT * FROM tru_fk_table; -- also truncated + +-- truncate two tables at a command +INSERT INTO tru_ftable (SELECT x,md5((x+2)::text) FROM generate_series(1,8) x); +INSERT INTO tru_pk_ftable (SELECT x,md5((x+3)::text) FROM generate_series(3,10) x); +SELECT * FROM tru_ftable a FULL OUTER JOIN tru_pk_ftable b ON a.id = b.id; +TRUNCATE tru_ftable, tru_pk_ftable CASCADE; +SELECT * FROM tru_ftable a FULL OUTER JOIN tru_pk_ftable b ON a.id = b.id; + +-- truncate with ONLY clause +TRUNCATE ONLY tru_ftable_parent; +SELECT * FROM tru_ftable_parent; +TRUNCATE tru_ftable_parent; +SELECT * FROM tru_ftable_parent; + +-- in case when remote table has inherited children +CREATE TABLE tru_rtable0_child () INHERITS (tru_rtable0); +INSERT INTO tru_rtable0 (SELECT x,md5(x::text) FROM generate_series(5,9) x); +INSERT INTO tru_rtable0_child (SELECT x,md5(x::text) FROM generate_series(10,14) x); +SELECT * FROM tru_ftable; + +TRUNCATE ONLY tru_ftable; -- truncate only parent portion +SELECT * FROM tru_ftable; + +INSERT INTO tru_rtable0 (SELECT x,md5(x::text) FROM generate_series(21,25) x); +SELECT * FROM tru_ftable; +TRUNCATE tru_ftable; -- truncate both of parent and child +SELECT * FROM tru_ftable; + -- =================================================================== -- test IMPORT FOREIGN SCHEMA -- =================================================================== diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index 854913ae5f..1ce103fee7 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -1057,6 +1057,46 @@ EndDirectModify(ForeignScanState *node); </sect2> + <sect2 id="fdw-callbacks-truncate"> + <title>FDW Routines for Truncate</title> +<programlisting> +void +ExecForeignTruncate(List *frels_list, List *frels_extra, + DropBehavior behavior, bool restart_seqs); +</programlisting> + <para> + Truncate a set of foreign tables defined by + <literal>frels_list</literal> belonging to the same foreign server. + This optional function is called during execution of + <command>TRUNCATE</command> for each foreign server being involved + in one <command>TRUNCATE</command> command (note that invocations + are not per foreign table). + + <literal>frels_extra</literal> is same length with + <literal>frels_list</literal>, that delivers extra information of + the context where the foreign-tables are truncated. + </para> + + <para> + If the <function>ExecForeignTruncate</function> pointer is set to + <literal>NULL</literal>, attempts to truncate the foreign table will + fail with an error message. + </para> + + <para> + <literal>behavior</literal> defines how foreign tables should + be truncated, using as possible values <literal>DROP_RESTRICT</literal> + and <literal>DROP_CASCADE</literal> (to map with the equivalents of + <command>TRUNCATE</command>). + </para> + + <para> + <literal>restart_seqs</literal> is set to <literal>true</literal> + if <literal>RESTART IDENTITY</literal> was supplied in the + <command>TRUNCATE</command>. + </para> + </sect2> + <sect2 id="fdw-callbacks-row-locking"> <title>FDW Routines for Row Locking</title> diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml index 8d6abd4c54..af360a6db7 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -63,9 +63,10 @@ <para> Now you need only <command>SELECT</command> from a foreign table to access the data stored in its underlying remote table. You can also modify - the remote table using <command>INSERT</command>, <command>UPDATE</command>, or - <command>DELETE</command>. (Of course, the remote user you have specified - in your user mapping must have privileges to do these things.) + the remote table using <command>INSERT</command>, <command>UPDATE</command>, + <command>DELETE</command>, or <command>TRUNCATE</command>. + (Of course, the remote user you have specified in your user mapping must + have privileges to do these things.) </para> <para> @@ -405,6 +406,29 @@ OPTIONS (ADD password_required 'false'); </listitem> </varlistentry> + <varlistentry> + <term><literal>truncatable</literal></term> + <listitem> + <para> + This option controls whether <filename>postgres_fdw</filename> allows + foreign tables to be truncated using <command>TRUNCATE</command> + command. It can be specified for a foreign table or a foreign server. + A table-level option overrides a server-level option. + The default is <literal>true</literal>. + </para> + <para> + Pay attention for the case when a foreign table maps remote table + that has inherited children or partition leafs. + <command>TRUNCATE</command> specifies the foreign tables with + <literal>ONLY</literal> clause, remove queries over the + <filename>postgres_fdw</filename> also specify remote tables with + <literal>ONLY</literal> clause, that will truncate only parent + portion of the remote table. In the results, it looks like + <command>TRUNCATE</command> command partially eliminated contents + of the foreign tables. + </para> + </listitem> + </varlistentry> </variablelist> </sect3> diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 420991e315..ac76dd93ba 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -57,6 +57,7 @@ #include "commands/typecmds.h" #include "commands/user.h" #include "executor/executor.h" +#include "foreign/fdwapi.h" #include "foreign/foreign.h" #include "miscadmin.h" #include "nodes/makefuncs.h" @@ -296,6 +297,21 @@ struct DropRelationCallbackState #define ATT_FOREIGN_TABLE 0x0020 #define ATT_PARTITIONED_INDEX 0x0040 +/* + * ForeignTruncateInfo + * + * Information related to truncation of foreign tables. This is used for + * the elements in a hash table that uses the server OID as lookup key, + * and includes a per-server list of all foreign tables involved in the + * truncation. + */ +typedef struct +{ + Oid server_oid; + List *frels_list; + List *frels_extra; +} ForeignTruncateInfo; + /* * Partition tables are expected to be dropped when the parent partitioned * table gets dropped. Hence for partitioning we use AUTO dependency. @@ -1561,6 +1577,7 @@ ExecuteTruncate(TruncateStmt *stmt) { List *rels = NIL; List *relids = NIL; + List *relids_extra = NIL; List *relids_logged = NIL; ListCell *cell; @@ -1597,6 +1614,7 @@ ExecuteTruncate(TruncateStmt *stmt) rels = lappend(rels, rel); relids = lappend_oid(relids, myrelid); + relids_extra = lappend_int(relids_extra, (recurse ? 0 : 1)); /* Log this relation only if needed for logical decoding */ if (RelationIsLogicallyLogged(rel)) relids_logged = lappend_oid(relids_logged, myrelid); @@ -1644,6 +1662,7 @@ ExecuteTruncate(TruncateStmt *stmt) rels = lappend(rels, rel); relids = lappend_oid(relids, childrelid); + relids_extra = lappend_int(relids_extra, -1); /* Log this relation only if needed for logical decoding */ if (RelationIsLogicallyLogged(rel)) relids_logged = lappend_oid(relids_logged, childrelid); @@ -1656,7 +1675,7 @@ ExecuteTruncate(TruncateStmt *stmt) errhint("Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly."))); } - ExecuteTruncateGuts(rels, relids, relids_logged, + ExecuteTruncateGuts(rels, relids, relids_extra, relids_logged, stmt->behavior, stmt->restart_seqs); /* And close the rels */ @@ -1682,16 +1701,21 @@ ExecuteTruncate(TruncateStmt *stmt) * this information handy in this form. */ void -ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged, +ExecuteTruncateGuts(List *explicit_rels, + List *relids, + List *relids_extra, + List *relids_logged, DropBehavior behavior, bool restart_seqs) { List *rels; List *seq_relids = NIL; + HTAB *ft_htab = NULL; EState *estate; ResultRelInfo *resultRelInfos; ResultRelInfo *resultRelInfo; SubTransactionId mySubid; ListCell *cell; + ListCell *lc1, *lc2; Oid *logrelids; /* @@ -1729,6 +1753,7 @@ ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged, truncate_check_activity(rel); rels = lappend(rels, rel); relids = lappend_oid(relids, relid); + relids_extra = lappend_int(relids_extra, -1); /* Log this relation only if needed for logical decoding */ if (RelationIsLogicallyLogged(rel)) relids_logged = lappend_oid(relids_logged, relid); @@ -1829,14 +1854,68 @@ ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged, */ mySubid = GetCurrentSubTransactionId(); - foreach(cell, rels) + Assert(list_length(rels) == list_length(relids_extra)); + forboth (lc1, rels, + lc2, relids_extra) { - Relation rel = (Relation) lfirst(cell); + Relation rel = (Relation) lfirst(lc1); + int extra = lfirst_int(lc2); /* Skip partitioned tables as there is nothing to do */ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) continue; + /* + * If truncating a foreign table, the foreign data wrapper callback + * for TRUNCATE is called once for each server with a list of all the + * relations to process linked to this server. The list of relations + * for each server is saved as a single entry in a hash table that + * uses the server OID as lookup key. Once the full set of lists is + * built, all the entries of the hash table are scanned, and the list + * of relations associated to the server is passed down to the + * TRUNCATE callback of its foreign data wrapper. + */ + if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + { + Oid frel_oid = RelationGetRelid(rel); + Oid server_oid = GetForeignServerIdByRelId(frel_oid); + bool found; + ForeignTruncateInfo *ft_info; + + /* if the hash table does not exist yet, initialize it */ + if (!ft_htab) + { + HASHCTL hctl; + + memset(&hctl, 0, sizeof(HASHCTL)); + hctl.keysize = sizeof(Oid); + hctl.entrysize = sizeof(ForeignTruncateInfo); + hctl.hcxt = CurrentMemoryContext; + + ft_htab = hash_create("TRUNCATE for Foreign Tables", + 32, /* start small and extend */ + &hctl, + HASH_ELEM | HASH_BLOBS | HASH_CONTEXT); + } + + /* + * Look after the entry of the server in the hash table, and + * initialize it if the entry does not exist yet. + */ + ft_info = hash_search(ft_htab, &server_oid, HASH_ENTER, &found); + if (!found) + { + ft_info->server_oid = server_oid; + ft_info->frels_list = NIL; + ft_info->frels_extra = NIL; + } + + /* save the relation in the list */ + ft_info->frels_list = lappend(ft_info->frels_list, rel); + ft_info->frels_extra = lappend_int(ft_info->frels_extra, extra); + continue; + } + /* * Normally, we need a transaction-safe truncation here. However, if * the table was either created in the current (sub)transaction or has @@ -1899,6 +1978,31 @@ ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged, pgstat_count_truncate(rel); } + /* + * Now go through the hash table, and process each entry associated to the + * servers involved in the TRUNCATE. + */ + if (ft_htab) + { + ForeignTruncateInfo *ft_info; + HASH_SEQ_STATUS seq; + + hash_seq_init(&seq, ft_htab); + + while ((ft_info = hash_seq_search(&seq)) != NULL) + { + FdwRoutine *routine = GetFdwRoutineByServerId(ft_info->server_oid); + + /* truncate_check_rel() has checked that already */ + Assert(routine->ExecForeignTruncate != NULL); + + routine->ExecForeignTruncate(ft_info->frels_list, + ft_info->frels_extra, + behavior, + restart_seqs); + } + } + /* * Restart owned sequences if we were asked to. */ @@ -1984,12 +2088,24 @@ truncate_check_rel(Oid relid, Form_pg_class reltuple) char *relname = NameStr(reltuple->relname); /* - * Only allow truncate on regular tables and partitioned tables (although, - * the latter are only being included here for the following checks; no - * physical truncation will occur in their case.) + * Only allow truncate on regular tables, foreign tables using foreign + * data wrappers supporting TRUNCATE and partitioned tables (although, the + * latter are only being included here for the following checks; no + * physical truncation will occur in their case.). */ - if (reltuple->relkind != RELKIND_RELATION && - reltuple->relkind != RELKIND_PARTITIONED_TABLE) + if (reltuple->relkind == RELKIND_FOREIGN_TABLE) + { + Oid server_id = GetForeignServerIdByRelId(relid); + FdwRoutine *fdwroutine = GetFdwRoutineByServerId(server_id); + + if (!fdwroutine->ExecForeignTruncate) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot truncate foreign table \"%s\"", + relname))); + } + else if (reltuple->relkind != RELKIND_RELATION && + reltuple->relkind != RELKIND_PARTITIONED_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is not a table", relname))); diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c index eb7db89cef..3c80f1f890 100644 --- a/src/backend/replication/logical/worker.c +++ b/src/backend/replication/logical/worker.c @@ -1803,6 +1803,7 @@ apply_handle_truncate(StringInfo s) List *rels = NIL; List *part_rels = NIL; List *relids = NIL; + List *relids_extra = NIL; List *relids_logged = NIL; ListCell *lc; @@ -1832,6 +1833,7 @@ apply_handle_truncate(StringInfo s) remote_rels = lappend(remote_rels, rel); rels = lappend(rels, rel->localrel); relids = lappend_oid(relids, rel->localreloid); + relids_extra = lappend_int(relids_extra, 0); if (RelationIsLogicallyLogged(rel->localrel)) relids_logged = lappend_oid(relids_logged, rel->localreloid); @@ -1882,8 +1884,12 @@ apply_handle_truncate(StringInfo s) * to replaying changes without further cascading. This might be later * changeable with a user specified option. */ - ExecuteTruncateGuts(rels, relids, relids_logged, DROP_RESTRICT, restart_seqs); - + ExecuteTruncateGuts(rels, + relids, + relids_extra, + relids_logged, + DROP_RESTRICT, + restart_seqs); foreach(lc, remote_rels) { LogicalRepRelMapEntry *rel = lfirst(lc); diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index b3d30acc35..2e30a09729 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -56,8 +56,12 @@ extern void AlterRelationNamespaceInternal(Relation classRel, Oid relOid, extern void CheckTableNotInUse(Relation rel, const char *stmt); extern void ExecuteTruncate(TruncateStmt *stmt); -extern void ExecuteTruncateGuts(List *explicit_rels, List *relids, List *relids_logged, - DropBehavior behavior, bool restart_seqs); +extern void ExecuteTruncateGuts(List *explicit_rels, + List *relids, + List *relids_extra, + List *relids_logged, + DropBehavior behavior, + bool restart_seqs); extern void SetRelationHasSubclass(Oid relationId, bool relhassubclass); diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h index 248f78da45..7488ce1ea6 100644 --- a/src/include/foreign/fdwapi.h +++ b/src/include/foreign/fdwapi.h @@ -159,6 +159,11 @@ typedef bool (*AnalyzeForeignTable_function) (Relation relation, typedef List *(*ImportForeignSchema_function) (ImportForeignSchemaStmt *stmt, Oid serverOid); +typedef void (*ExecForeignTruncate_function) (List *frels_list, + List *frels_extra, + DropBehavior behavior, + bool restart_seqs); + typedef Size (*EstimateDSMForeignScan_function) (ForeignScanState *node, ParallelContext *pcxt); typedef void (*InitializeDSMForeignScan_function) (ForeignScanState *node, @@ -246,6 +251,9 @@ typedef struct FdwRoutine /* Support functions for IMPORT FOREIGN SCHEMA */ ImportForeignSchema_function ImportForeignSchema; + /* Support functions for TRUNCATE */ + ExecForeignTruncate_function ExecForeignTruncate; + /* Support functions for parallelism under Gather node */ IsForeignScanParallelSafe_function IsForeignScanParallelSafe; EstimateDSMForeignScan_function EstimateDSMForeignScan; diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index b9e25820bc..e2c0bcea51 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -1807,9 +1807,9 @@ Inherits: fd_pt1 -- TRUNCATE doesn't work on foreign tables, either directly or recursively TRUNCATE ft2; -- ERROR -ERROR: "ft2" is not a table +ERROR: foreign-data wrapper "dummy" has no handler TRUNCATE fd_pt1; -- ERROR -ERROR: "ft2" is not a table +ERROR: foreign-data wrapper "dummy" has no handler DROP TABLE fd_pt1 CASCADE; NOTICE: drop cascades to foreign table ft2 -- IMPORT FOREIGN SCHEMA @@ -2032,9 +2032,9 @@ ALTER FOREIGN TABLE fd_pt2_1 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); -- TRUNCATE doesn't work on foreign tables, either directly or recursively TRUNCATE fd_pt2_1; -- ERROR -ERROR: "fd_pt2_1" is not a table +ERROR: foreign-data wrapper "dummy" has no handler TRUNCATE fd_pt2; -- ERROR -ERROR: "fd_pt2_1" is not a table +ERROR: foreign-data wrapper "dummy" has no handler DROP FOREIGN TABLE fd_pt2_1; DROP TABLE fd_pt2; -- foreign table cannot be part of partition tree made of temporary diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 1d540fe489..c63bfaf4b2 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -702,6 +702,7 @@ ForeignScanState ForeignServer ForeignServerInfo ForeignTable +ForeignTruncateInfo ForkNumber FormData_pg_aggregate FormData_pg_am