By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table table_name();
CREATE TABLE
create schema some_other_schema;
alter table table_name set schema some_other_schema;
CREATE SCHEMA
ALTER TABLE
create table your_table
( id int primary key
,payload text)partition by range(id);
create table your_table_partition partition of your_table default;
alter table your_table detach partition your_table_partition;
drop table your_table cascade;--doesn't affect your_table_partition
create table your_table
( id int primary key
,payload text)partition by range(id);
alter table your_table attach partition your_table_partition default;
CREATE TABLE
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE TABLE
ALTER TABLE
create extension postgres_fdw;
create server linked_local
foreign data wrapper postgres_fdw;--no options, defaults to all local
create user mapping for current_role
server linked_local;--again, all defaults
create foreign table f_your_table
( id int
,payload text)
server linked_local
options( schema_name 'public'
,table_name 'your_table');
begin; insert into f_your_table values(1,'first record inserted via fdw'); commit;
select * from your_table;--reading locally
CREATE EXTENSION
CREATE SERVER
CREATE USER MAPPING
CREATE FOREIGN TABLE
BEGIN
INSERT 0 1
COMMIT
| id | payload |
|---|---|
| 1 | first record inserted via fdw |
SELECT 1
begin; insert into your_table values(2,'second record inserted directly'); commit;
select * from f_your_table;--reading remotely, via fdw
BEGIN
INSERT 0 1
COMMIT
| id | payload |
|---|---|
| 1 | first record inserted via fdw |
| 2 | second record inserted directly |
SELECT 2
drop foreign table f_your_table;--dropping foreign table
select * from your_table;--doesn't remove the data at its source
DROP FOREIGN TABLE
| id | payload |
|---|---|
| 1 | first record inserted via fdw |
| 2 | second record inserted directly |
SELECT 2