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
([email protected])
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 b09dce63f5..17e0f250c8 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8229,6 +8229,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
-- ===================================================================
@@ -8928,7 +9161,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 2ce42ce3f1..3c4c970313 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,84 @@ 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);
+ }
+ }
+ else
+ {
+ /* ExecForeignTruncate() 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);
+ }
+ 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 319c15d635..1a13799379 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2337,6 +2337,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
