Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8ff1c94

Browse files
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 asthe targets to truncate and invokes that API. Also it extends postgres_fdwso that it can issue TRUNCATE command to foreign servers, by addingnew 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 totruncate is also passed to FDW. FDW truncates the foreign data sourcesthat the passed foreign tables specify, based on those information.For example, postgres_fdw constructs TRUNCATE command using themand issues it to the foreign server.For performance, TRUNCATE command invokes the FDW routine forTRUNCATE once per foreign server that foreign tables to truncate belong to.Author: Kazutaka Onishi, Kohei KaiGai, slightly modified by Fujii MasaoReviewed-by: Bharath Rupireddy, Michael Paquier, Zhihong Yu, Alvaro Herrera, Stephen Frost, Ashutosh Bapat, Amit Langote, Daniel Gustafsson, Ibrar Ahmed, Fujii MasaoDiscussion:https://postgr.es/m/CAOP8fzb_gkReLput7OvOK+8NHgw-RKqNv59vem7=524krQTcWA@mail.gmail.comDiscussion:https://postgr.es/m/CAJuF6cMWDDqU-vn_knZgma+2GMaout68YUgn1uyDnexRhqqM5Q@mail.gmail.com
1 parent50e17ad commit8ff1c94

File tree

17 files changed

+725
-31
lines changed

17 files changed

+725
-31
lines changed

‎contrib/postgres_fdw/connection.c

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -92,7 +92,6 @@ static PGconn *connect_pg_server(ForeignServer *server, UserMapping *user);
9292
staticvoiddisconnect_pg_server(ConnCacheEntry*entry);
9393
staticvoidcheck_conn_params(constchar**keywords,constchar**values,UserMapping*user);
9494
staticvoidconfigure_remote_session(PGconn*conn);
95-
staticvoiddo_sql_command(PGconn*conn,constchar*sql);
9695
staticvoidbegin_remote_xact(ConnCacheEntry*entry);
9796
staticvoidpgfdw_xact_callback(XactEventevent,void*arg);
9897
staticvoidpgfdw_subxact_callback(SubXactEventevent,
@@ -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-
staticvoid
570+
void
572571
do_sql_command(PGconn*conn,constchar*sql)
573572
{
574573
PGresult*res;

‎contrib/postgres_fdw/deparse.c

Lines changed: 38 additions & 0 deletions
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(StringInfobuf,
2181+
List*rels,
2182+
List*rels_extra,
2183+
DropBehaviorbehavior,
2184+
boolrestart_seqs)
2185+
{
2186+
ListCell*lc1,
2187+
*lc2;
2188+
2189+
appendStringInfoString(buf,"TRUNCATE ");
2190+
2191+
forboth(lc1,rels,lc2,rels_extra)
2192+
{
2193+
Relationrel=lfirst(lc1);
2194+
intextra=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+
elseif (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

Lines changed: 200 additions & 1 deletion
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

Lines changed: 4 additions & 0 deletions
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)
[8]ページ先頭
©2009-2025 Movatter.jp