Skip to content

Commit 8ff1c94

Browse files
MasaoFujiiMasaoFujii
committed
Allow TRUNCATE command to truncate foreign tables.
This commit introduces new foreign data wrapper API for TRUNCATE. It extends TRUNCATE command so that it accepts foreign tables as the targets to truncate and invokes that API. Also it extends postgres_fdw so that it can issue TRUNCATE command to foreign servers, by adding new routine for that TRUNCATE API. The information about options specified in TRUNCATE command, e.g., ONLY, CACADE, etc is passed to FDW via API. The list of foreign tables to truncate is also passed to FDW. FDW truncates the foreign data sources that the passed foreign tables specify, based on those information. For example, postgres_fdw constructs TRUNCATE command using them and issues it to the foreign server. For performance, TRUNCATE command invokes the FDW routine for TRUNCATE once per foreign server that foreign tables to truncate belong to. Author: Kazutaka Onishi, Kohei KaiGai, slightly modified by Fujii Masao Reviewed-by: Bharath Rupireddy, Michael Paquier, Zhihong Yu, Alvaro Herrera, Stephen Frost, Ashutosh Bapat, Amit Langote, Daniel Gustafsson, Ibrar Ahmed, Fujii Masao Discussion: https://postgr.es/m/CAOP8fzb_gkReLput7OvOK+8NHgw-RKqNv59vem7=524krQTcWA@mail.gmail.com Discussion: https://postgr.es/m/CAJuF6cMWDDqU-vn_knZgma+2GMaout68YUgn1uyDnexRhqqM5Q@mail.gmail.com
1 parent 50e17ad commit 8ff1c94

File tree

17 files changed

+725
-31
lines changed

17 files changed

+725
-31
lines changed

‎contrib/postgres_fdw/connection.c

+1-2
Original file line numberDiff line numberDiff line change
@@ -92,7 +92,6 @@ static PGconn *connect_pg_server(ForeignServer *server, UserMapping *user);
9292
static void disconnect_pg_server(ConnCacheEntry *entry);
9393
static void check_conn_params(const char **keywords, const char **values, UserMapping *user);
9494
static void configure_remote_session(PGconn *conn);
95-
static void do_sql_command(PGconn *conn, const char *sql);
9695
static void begin_remote_xact(ConnCacheEntry *entry);
9796
static void pgfdw_xact_callback(XactEvent event, void *arg);
9897
static void pgfdw_subxact_callback(SubXactEvent event,
@@ -568,7 +567,7 @@ configure_remote_session(PGconn *conn)
568567
/*
569568
* Convenience subroutine to issue a non-data-returning SQL command to remote
570569
*/
571-
static void
570+
void
572571
do_sql_command(PGconn *conn, const char *sql)
573572
{
574573
PGresult *res;

‎contrib/postgres_fdw/deparse.c

+38
Original file line numberDiff line numberDiff line change
@@ -56,6 +56,7 @@
5656
#include "utils/rel.h"
5757
#include "utils/syscache.h"
5858
#include "utils/typcache.h"
59+
#include "commands/tablecmds.h"
5960

6061
/*
6162
* Global context for foreign_expr_walker's search of an expression tree.
@@ -2172,6 +2173,43 @@ deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs)
21722173
deparseRelation(buf, rel);
21732174
}
21742175

2176+
/*
2177+
* Construct a simple "TRUNCATE rel" statement
2178+
*/
2179+
void
2180+
deparseTruncateSql(StringInfo buf,
2181+
List *rels,
2182+
List *rels_extra,
2183+
DropBehavior behavior,
2184+
bool restart_seqs)
2185+
{
2186+
ListCell *lc1,
2187+
*lc2;
2188+
2189+
appendStringInfoString(buf, "TRUNCATE ");
2190+
2191+
forboth(lc1, rels, lc2, rels_extra)
2192+
{
2193+
Relation rel = lfirst(lc1);
2194+
int extra = lfirst_int(lc2);
2195+
2196+
if (lc1 != list_head(rels))
2197+
appendStringInfoString(buf, ", ");
2198+
if (extra & TRUNCATE_REL_CONTEXT_ONLY)
2199+
appendStringInfoString(buf, "ONLY ");
2200+
2201+
deparseRelation(buf, rel);
2202+
}
2203+
2204+
appendStringInfo(buf, " %s IDENTITY",
2205+
restart_seqs ? "RESTART" : "CONTINUE");
2206+
2207+
if (behavior == DROP_RESTRICT)
2208+
appendStringInfoString(buf, " RESTRICT");
2209+
else if (behavior == DROP_CASCADE)
2210+
appendStringInfoString(buf, " CASCADE");
2211+
}
2212+
21752213
/*
21762214
* Construct name to use for given column, and emit it into buf.
21772215
* If it has a column_name FDW option, use that instead of attribute name.

‎contrib/postgres_fdw/expected/postgres_fdw.out

+200-1
Original file line numberDiff line numberDiff line change
@@ -8215,6 +8215,205 @@ select * from rem3;
82158215
drop foreign table rem3;
82168216
drop table loc3;
82178217
-- ===================================================================
8218+
-- test for TRUNCATE
8219+
-- ===================================================================
8220+
CREATE TABLE tru_rtable0 (id int primary key);
8221+
CREATE TABLE tru_rtable1 (id int primary key);
8222+
CREATE FOREIGN TABLE tru_ftable (id int)
8223+
SERVER loopback OPTIONS (table_name 'tru_rtable0');
8224+
INSERT INTO tru_rtable0 (SELECT x FROM generate_series(1,10) x);
8225+
CREATE TABLE tru_ptable (id int) PARTITION BY HASH(id);
8226+
CREATE TABLE tru_ptable__p0 PARTITION OF tru_ptable
8227+
FOR VALUES WITH (MODULUS 2, REMAINDER 0);
8228+
CREATE FOREIGN TABLE tru_ftable__p1 PARTITION OF tru_ptable
8229+
FOR VALUES WITH (MODULUS 2, REMAINDER 1)
8230+
SERVER loopback OPTIONS (table_name 'tru_rtable1');
8231+
INSERT INTO tru_ptable (SELECT x FROM generate_series(11,20) x);
8232+
CREATE TABLE tru_pk_table(id int primary key);
8233+
CREATE TABLE tru_fk_table(fkey int references tru_pk_table(id));
8234+
INSERT INTO tru_pk_table (SELECT x FROM generate_series(1,10) x);
8235+
INSERT INTO tru_fk_table (SELECT x % 10 + 1 FROM generate_series(5,25) x);
8236+
CREATE FOREIGN TABLE tru_pk_ftable (id int)
8237+
SERVER loopback OPTIONS (table_name 'tru_pk_table');
8238+
CREATE TABLE tru_rtable_parent (id int);
8239+
CREATE TABLE tru_rtable_child (id int);
8240+
CREATE FOREIGN TABLE tru_ftable_parent (id int)
8241+
SERVER loopback OPTIONS (table_name 'tru_rtable_parent');
8242+
CREATE FOREIGN TABLE tru_ftable_child () INHERITS (tru_ftable_parent)
8243+
SERVER loopback OPTIONS (table_name 'tru_rtable_child');
8244+
INSERT INTO tru_rtable_parent (SELECT x FROM generate_series(1,8) x);
8245+
INSERT INTO tru_rtable_child (SELECT x FROM generate_series(10, 18) x);
8246+
-- normal truncate
8247+
SELECT sum(id) FROM tru_ftable; -- 55
8248+
sum
8249+
-----
8250+
55
8251+
(1 row)
8252+
8253+
TRUNCATE tru_ftable;
8254+
SELECT count(*) FROM tru_rtable0; -- 0
8255+
count
8256+
-------
8257+
0
8258+
(1 row)
8259+
8260+
SELECT count(*) FROM tru_ftable; -- 0
8261+
count
8262+
-------
8263+
0
8264+
(1 row)
8265+
8266+
-- 'truncatable' option
8267+
ALTER SERVER loopback OPTIONS (ADD truncatable 'false');
8268+
TRUNCATE tru_ftable; -- error
8269+
ERROR: foreign table "tru_ftable" does not allow truncates
8270+
ALTER FOREIGN TABLE tru_ftable OPTIONS (ADD truncatable 'true');
8271+
TRUNCATE tru_ftable; -- accepted
8272+
ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'false');
8273+
TRUNCATE tru_ftable; -- error
8274+
ERROR: foreign table "tru_ftable" does not allow truncates
8275+
ALTER SERVER loopback OPTIONS (DROP truncatable);
8276+
ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'false');
8277+
TRUNCATE tru_ftable; -- error
8278+
ERROR: foreign table "tru_ftable" does not allow truncates
8279+
ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'true');
8280+
TRUNCATE tru_ftable; -- accepted
8281+
-- partitioned table with both local and foreign tables as partitions
8282+
SELECT sum(id) FROM tru_ptable; -- 155
8283+
sum
8284+
-----
8285+
155
8286+
(1 row)
8287+
8288+
TRUNCATE tru_ptable;
8289+
SELECT count(*) FROM tru_ptable; -- 0
8290+
count
8291+
-------
8292+
0
8293+
(1 row)
8294+
8295+
SELECT count(*) FROM tru_ptable__p0; -- 0
8296+
count
8297+
-------
8298+
0
8299+
(1 row)
8300+
8301+
SELECT count(*) FROM tru_ftable__p1; -- 0
8302+
count
8303+
-------
8304+
0
8305+
(1 row)
8306+
8307+
SELECT count(*) FROM tru_rtable1; -- 0
8308+
count
8309+
-------
8310+
0
8311+
(1 row)
8312+
8313+
-- 'CASCADE' option
8314+
SELECT sum(id) FROM tru_pk_ftable; -- 55
8315+
sum
8316+
-----
8317+
55
8318+
(1 row)
8319+
8320+
TRUNCATE tru_pk_ftable; -- failed by FK reference
8321+
ERROR: cannot truncate a table referenced in a foreign key constraint
8322+
DETAIL: Table "tru_fk_table" references "tru_pk_table".
8323+
HINT: Truncate table "tru_fk_table" at the same time, or use TRUNCATE ... CASCADE.
8324+
CONTEXT: remote SQL command: TRUNCATE public.tru_pk_table CONTINUE IDENTITY RESTRICT
8325+
TRUNCATE tru_pk_ftable CASCADE;
8326+
SELECT count(*) FROM tru_pk_ftable; -- 0
8327+
count
8328+
-------
8329+
0
8330+
(1 row)
8331+
8332+
SELECT count(*) FROM tru_fk_table; -- also truncated,0
8333+
count
8334+
-------
8335+
0
8336+
(1 row)
8337+
8338+
-- truncate two tables at a command
8339+
INSERT INTO tru_ftable (SELECT x FROM generate_series(1,8) x);
8340+
INSERT INTO tru_pk_ftable (SELECT x FROM generate_series(3,10) x);
8341+
SELECT count(*) from tru_ftable; -- 8
8342+
count
8343+
-------
8344+
8
8345+
(1 row)
8346+
8347+
SELECT count(*) from tru_pk_ftable; -- 8
8348+
count
8349+
-------
8350+
8
8351+
(1 row)
8352+
8353+
TRUNCATE tru_ftable, tru_pk_ftable CASCADE;
8354+
SELECT count(*) from tru_ftable; -- 0
8355+
count
8356+
-------
8357+
0
8358+
(1 row)
8359+
8360+
SELECT count(*) from tru_pk_ftable; -- 0
8361+
count
8362+
-------
8363+
0
8364+
(1 row)
8365+
8366+
-- truncate with ONLY clause
8367+
TRUNCATE ONLY tru_ftable_parent;
8368+
SELECT sum(id) FROM tru_ftable_parent; -- 126
8369+
sum
8370+
-----
8371+
126
8372+
(1 row)
8373+
8374+
TRUNCATE tru_ftable_parent;
8375+
SELECT count(*) FROM tru_ftable_parent; -- 0
8376+
count
8377+
-------
8378+
0
8379+
(1 row)
8380+
8381+
-- in case when remote table has inherited children
8382+
CREATE TABLE tru_rtable0_child () INHERITS (tru_rtable0);
8383+
INSERT INTO tru_rtable0 (SELECT x FROM generate_series(5,9) x);
8384+
INSERT INTO tru_rtable0_child (SELECT x FROM generate_series(10,14) x);
8385+
SELECT sum(id) FROM tru_ftable; -- 95
8386+
sum
8387+
-----
8388+
95
8389+
(1 row)
8390+
8391+
TRUNCATE ONLY tru_ftable; -- truncate only parent portion
8392+
SELECT sum(id) FROM tru_ftable; -- 60
8393+
sum
8394+
-----
8395+
60
8396+
(1 row)
8397+
8398+
INSERT INTO tru_rtable0 (SELECT x FROM generate_series(21,25) x);
8399+
SELECT sum(id) FROM tru_ftable; -- 175
8400+
sum
8401+
-----
8402+
175
8403+
(1 row)
8404+
8405+
TRUNCATE tru_ftable; -- truncate both of parent and child
8406+
SELECT count(*) FROM tru_ftable; -- empty
8407+
count
8408+
-------
8409+
0
8410+
(1 row)
8411+
8412+
-- cleanup
8413+
DROP FOREIGN TABLE tru_ftable_parent, tru_ftable_child, tru_pk_ftable,tru_ftable__p1,tru_ftable;
8414+
DROP TABLE tru_rtable0, tru_rtable1, tru_ptable, tru_ptable__p0, tru_pk_table, tru_fk_table,
8415+
tru_rtable_parent,tru_rtable_child, tru_rtable0_child;
8416+
-- ===================================================================
82188417
-- test IMPORT FOREIGN SCHEMA
82198418
-- ===================================================================
82208419
CREATE SCHEMA import_source;
@@ -8917,7 +9116,7 @@ DO $d$
89179116
END;
89189117
$d$;
89199118
ERROR: invalid option "password"
8920-
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, sslcrldir, sslsni, 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, async_capable, keep_connections
9119+
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, sslcrldir, sslsni, 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, async_capable, keep_connections
89219120
CONTEXT: SQL statement "ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw')"
89229121
PL/pgSQL function inline_code_block line 3 at EXECUTE
89239122
-- If we add a password for our user mapping instead, we should get a different

‎contrib/postgres_fdw/option.c

+4
Original file line numberDiff line numberDiff line change
@@ -108,6 +108,7 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
108108
*/
109109
if (strcmp(def->defname, "use_remote_estimate") == 0 ||
110110
strcmp(def->defname, "updatable") == 0 ||
111+
strcmp(def->defname, "truncatable") == 0 ||
111112
strcmp(def->defname, "async_capable") == 0 ||
112113
strcmp(def->defname, "keep_connections") == 0)
113114
{
@@ -213,6 +214,9 @@ InitPgFdwOptions(void)
213214
/* updatable is available on both server and table */
214215
{"updatable", ForeignServerRelationId, false},
215216
{"updatable", ForeignTableRelationId, false},
217+
/* truncatable is available on both server and table */
218+
{"truncatable", ForeignServerRelationId, false},
219+
{"truncatable", ForeignTableRelationId, false},
216220
/* fetch_size is available on both server and table */
217221
{"fetch_size", ForeignServerRelationId, false},
218222
{"fetch_size", ForeignTableRelationId, false},

0 commit comments

Comments
 (0)