CODEWITHSUNDEEP
postgresql
Olivier Grégoire
Olivier Grégoire

Reputation: 35437

Truncate on foreign table

I use postgresql_fdw on postgresql 9.3.3.

This is what I have:

SERVER_A:

SERVER_A=> CREATE TABLE table_a (
SERVER_A(>   id integer,
SERVER_A(>   val varchar(10)
SERVER_A(> );
SERVER_A=> INSERT INTO table_a VALUES (1, "a");
INSERT 0 1

SERVER_ACTIVE:

SERVER_ACTIVE=> CREATE FOREIGN TABLE table_a (
SERVER_ACTIVE(>  id integer,
SERVER_ACTIVE(>  val varchar(10)
SERVER_ACTIVE(> ) SERVER SERVER_A;
CREATE FOREIGN TABLE

Now I check the elements.

SERVER_ACTIVE=> SELECT COUNT(*) FROM table_a;
 count
-------
     1
(1 row)

Now I want to truncate the table:

ACC_ACTIVE=> TRUNCATE TABLE table_a;
ERROR:  "table_a" is not a table
ACC_ACTIVE=> TRUNCATE FOREIGN TABLE table_a;
ERROR:  syntax error at or near "FOREIGN"
LINE 1: TRUNCATE FOREIGN TABLE table_a;

Why doesn't it work? How do I make it work? This table is supposed to hold several millions of records, I can't use DELETE. But even if I DELETE, I can't use VACUUM.

Upvotes: 3

Views: 2195

Answers (2)

Djamil Lakhdar-Hamina
Djamil Lakhdar-Hamina

Reputation: 121

Document is quite clear https://www.postgresql.org/docs/current/postgres-fdw.html. It says:

Now you need only SELECT from a foreign table to access the data stored in its underlying remote table. You can also modify the remote table using INSERT, UPDATE, or DELETE. (Of course, the remote user you have specified in your user mapping must have privileges to do these things.)

In short, postgres_fwd does not support TRUNCATE on a remote (foreign) table.

You may use DELETE FROM table; which deletes all rows in the table. It recreates TRUNCATE TABLE table in the context of deleting data from a remote (foreign) table.

I felt the above answer was a bit brief so I elucidated.

Upvotes: 2

tsnorri
tsnorri

Reputation: 2097

The documentation doesn't mention anything about the ability to TRUNCATE remote tables. (SELECT, INSERT, UPDATE and DELETE are listed as the available commands.) As a workaround, you could use dblink.

Upvotes: 2

Related Questions