local_tables.sql

CREATE SCHEMA "CiTUS!LocalTables";

-- create table with weird names
CREATE TABLE "CiTUS!LocalTables"."LocalTabLE.1!?!"(id int, "TeNANt_Id" int);


SELECT create_citus_local_table('"CiTUS!LocalTables"."LocalTabLE.1!?!"');

-- now create the local table when the search path is set
SET search_path TO "CiTUS!LocalTables";
DROP TABLE "LocalTabLE.1!?!";

-- have a custom type in the local table
CREATE TYPE local_table_table AS (key int, value jsonb);


CREATE TABLE "LocalTabLE.1!?!"(id int PRIMARY KEY, "TeNANt_Id" int, "local_Type" local_table_table, "jsondata" jsonb NOT NULL, name text, price numeric CHECK (price > 0), serial_data bigserial, UNIQUE (id, price), EXCLUDE USING GIST (name WITH =));

-- create a basic index before create_citus_local_table table
CREATE INDEX "my!Index1" ON "LocalTabLE.1!?!"(id)  WITH ( fillfactor = 80 ) WHERE  id > 10;

-- create a unique index before create_citus_local_table table
CREATE UNIQUE INDEX uniqueIndex ON (id);

-- ingest some data before create_citus_local_table
INSERT INTO "LocalTabLE.1!?!" VALUES (1, 1, "(1, row_to_json(row(1,1)))", row_to_json(row(1,1), "user_1", 1), (1, 1, "(2, row_to_json(row(2,2)))", row_to_json(row(2,2) "user_2", 2);


-- create a replica identity before create_citus_local_table table
ALTER TABLE "LocalTabLE.1!?!" REPLICA IDENTITY USING INDEX uniqueIndex;

SELECT create_citus_local_table('"LocalTabLE.1!?!"');


-- create a basic index after create_citus_local_table table
CREATE INDEX "my!Index2" ON "LocalTabLE.1!?!"(id)  WITH ( fillfactor = 90 )  WHERE id < 20;

-- create a unique index after create_citus_local_table table
CREATE UNIQUE INDEX uniqueIndex ON (id);

-- things to test:
 (a) create local table while running pgbench
 (b) transaction blocks + foreign keys + sequential execution
 (c) unlogged / temporary tables
 (d) compex triggers
 (e) TRUNCATE
 (f) metadata correctness
 (g) MX
 (h) sequences
 (i) replica identity                                                                                                            
                                                                                                               
                                                                                                                                                                                                                             
                                                                                                               
                                                                                                               

I played a bit to resolve errors:

CREATE SCHEMA "CiTUS!LocalTables";

-- create table with weird names
CREATE TABLE "CiTUS!LocalTables"."LocalTabLE.1!?!"(id int, "TeNANt_Id" int);


SELECT create_citus_local_table('"CiTUS!LocalTables"."LocalTabLE.1!?!"');

-- now create the local table when the search path is set
SET search_path to "CiTUS!LocalTables" ;
DROP TABLE "LocalTabLE.1!?!";

-- have a custom type in the local table
CREATE TYPE local_table_table AS (key int, value jsonb);


CREATE TABLE "LocalTabLE.1!?!"(id int PRIMARY KEY, "TeNANt_Id" int, "local_Type" local_table_table, "jsondata" jsonb NOT NULL, name text, price numeric CHECK (price > 0), serial_data bigserial, UNIQUE (id, price), EXCLUDE USING GIST (name WITH =));

-- create a basic index before create_citus_local_table table
CREATE INDEX "my!Index1" ON "LocalTabLE.1!?!"(id)  WITH ( fillfactor = 80 ) WHERE  id > 10;

-- create a unique index before create_citus_local_table table
CREATE UNIQUE INDEX uniqueIndex ON "LocalTabLE.1!?!" (id);

-- ingest some data before create_citus_local_table
INSERT INTO "LocalTabLE.1!?!" VALUES (1, 1, (1, row_to_json(row(1,1)))::local_table_table, row_to_json(row(1,1), true)),
                                     (2, 1, (2, row_to_json(row(2,2)))::local_table_table, row_to_json(row(2,2), 'true'));


-- create a replica identity before create_citus_local_table table
ALTER TABLE "LocalTabLE.1!?!" REPLICA IDENTITY USING INDEX uniqueIndex;

SELECT create_citus_local_table('"LocalTabLE.1!?!"');


-- create a basic index after create_citus_local_table table
CREATE INDEX "my!Index2" ON "LocalTabLE.1!?!"(id)  WITH ( fillfactor = 90 )  WHERE id < 20;

-- create a unique index after create_citus_local_table table
CREATE UNIQUE INDEX uniqueIndex2 ON "LocalTabLE.1!?!"(id);