\! gs_ktool -d all
DELETE ALL
\! gs_ktool -g
GENERATE
1
DROP CLIENT MASTER KEY MyCMK CASCADE;
ERROR: client master key "mycmk" does not exist
CREATE CLIENT MASTER KEY MyCMK WITH ( KEY_STORE = gs_ktool , KEY_PATH = "gs_ktool/1" , ALGORITHM = AES_256_CBC);
CREATE COLUMN ENCRYPTION KEY MyCEK WITH VALUES (CLIENT_MASTER_KEY = MyCMK, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);
CREATE COLUMN ENCRYPTION KEY MyCEK2 WITH VALUES (CLIENT_MASTER_KEY = MyCMK, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);
CREATE TABLE so_headers (
id INTEGER ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC),
customer_id INTEGER ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK2, ENCRYPTION_TYPE = DETERMINISTIC),
ship_to VARCHAR (255)
);
CREATE TABLE so_items (
item_id INTEGER NOT NULL,
so_id INTEGER ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC),
product_id INTEGER,
qty INTEGER,
net_price NUMERIC,
PRIMARY KEY (item_id, so_id),
FOREIGN KEY (so_id) REFERENCES so_headers (id)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "so_items_pkey" for table "so_items"
ERROR: there is no unique constraint matching given keys for referenced table "so_headers"
CREATE TABLE so_items_r (
item_id INTEGER NOT NULL,
so_id int4 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC) REFERENCES so_headers(id) ON DELETE RESTRICT,
product_id INTEGER,
qty INTEGER,
net_price numeric,
PRIMARY KEY (item_id,so_id)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "so_items_r_pkey" for table "so_items_r"
ERROR: there is no unique constraint matching given keys for referenced table "so_headers"
CREATE TABLE so_items_c (
item_id int4 NOT NULL,
so_id int4 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC) REFERENCES so_headers(id) ON DELETE CASCADE,
product_id int4,
qty int4,
net_price numeric,
PRIMARY KEY (item_id,so_id)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "so_items_c_pkey" for table "so_items_c"
ERROR: there is no unique constraint matching given keys for referenced table "so_headers"
CREATE TABLE payments (
pay_id int,
so_id INTEGER ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC),
customer_id INTEGER ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK2, ENCRYPTION_TYPE = DETERMINISTIC),
FOREIGN KEY (so_id, customer_id) REFERENCES so_headers (id, customer_id)
);
ERROR: there is no unique constraint matching given keys for referenced table "so_headers"
CREATE TABLE so_items_a (
item_id INTEGER NOT NULL,
so_id int4 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC),
product_id INTEGER,
qty INTEGER,
net_price numeric,
PRIMARY KEY (item_id,so_id)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "so_items_a_pkey" for table "so_items_a"
INSERT INTO so_headers VALUES (1,101, 'Vina');
INSERT INTO so_headers VALUES (2,103, 'Paris');
INSERT INTO so_items VALUES (10001, 1, 1001, 100, 37.28);
ERROR: relation "so_items" does not exist on datanode1
LINE 1: INSERT INTO so_items VALUES (10001, 1, 1001, 100, 37.28);
^
INSERT INTO so_items VALUES (10002, 6, 1001, 100, 37.28);
ERROR: relation "so_items" does not exist on datanode1
LINE 1: INSERT INTO so_items VALUES (10002, 6, 1001, 100, 37.28);
^
INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
ERROR: relation "so_items" does not exist on datanode1
LINE 1: INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
^
SELECT * from so_items ORDER BY item_id;
ERROR: relation "so_items" does not exist on datanode1
LINE 1: SELECT * from so_items ORDER BY item_id;
^
INSERT INTO so_items_r VALUES (10001, 1, 1001, 100, 37.28);
ERROR: relation "so_items_r" does not exist on datanode1
LINE 1: INSERT INTO so_items_r VALUES (10001, 1, 1001, 100, 37.28);
^
INSERT INTO so_items_r VALUES (10002, 6, 1001, 100, 37.28);
ERROR: relation "so_items_r" does not exist on datanode1
LINE 1: INSERT INTO so_items_r VALUES (10002, 6, 1001, 100, 37.28);
^
INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
ERROR: relation "so_items" does not exist on datanode1
LINE 1: INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
^
SELECT * from so_items_r ORDER BY item_id;
ERROR: relation "so_items_r" does not exist on datanode1
LINE 1: SELECT * from so_items_r ORDER BY item_id;
^
INSERT INTO so_items_a VALUES (10001, 1, 1001, 100, 37.28);
INSERT INTO so_items_a VALUES (10002, 6, 1001, 100, 37.28);
INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
ERROR: relation "so_items" does not exist on datanode1
LINE 1: INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
^
SELECT * from so_items_a ORDER BY item_id;
item_id | so_id | product_id | qty | net_price
---------+-------+------------+-----+-----------
10001 | 1 | 1001 | 100 | 37.28
10002 | 6 | 1001 | 100 | 37.28
(2 rows)
INSERT INTO so_items_c VALUES (10001, 1, 1001, 100, 37.28);
ERROR: relation "so_items_c" does not exist on datanode1
LINE 1: INSERT INTO so_items_c VALUES (10001, 1, 1001, 100, 37.28);
^
INSERT INTO so_items_c VALUES (10002, 6, 1001, 100, 37.28);
ERROR: relation "so_items_c" does not exist on datanode1
LINE 1: INSERT INTO so_items_c VALUES (10002, 6, 1001, 100, 37.28);
^
INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
ERROR: relation "so_items" does not exist on datanode1
LINE 1: INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
^
SELECT * from so_items_c ORDER BY item_id;
ERROR: relation "so_items_c" does not exist on datanode1
LINE 1: SELECT * from so_items_c ORDER BY item_id;
^
DELETE from so_headers where id =2;
SELECT * from so_items ORDER BY item_id;
ERROR: relation "so_items" does not exist on datanode1
LINE 1: SELECT * from so_items ORDER BY item_id;
^
SELECT * from so_items_a ORDER BY item_id;
item_id | so_id | product_id | qty | net_price
---------+-------+------------+-----+-----------
10001 | 1 | 1001 | 100 | 37.28
10002 | 6 | 1001 | 100 | 37.28
(2 rows)
SELECT * from so_items_r ORDER BY item_id;
ERROR: relation "so_items_r" does not exist on datanode1
LINE 1: SELECT * from so_items_r ORDER BY item_id;
^
SELECT * from so_items_c ORDER BY item_id;
ERROR: relation "so_items_c" does not exist on datanode1
LINE 1: SELECT * from so_items_c ORDER BY item_id;
^
INSERT INTO payments VALUES (100001, 1, 101);
ERROR: relation "payments" does not exist on datanode1
LINE 1: INSERT INTO payments VALUES (100001, 1, 101);
^
INSERT INTO payments VALUES (100002, 1, 102);
ERROR: relation "payments" does not exist on datanode1
LINE 1: INSERT INTO payments VALUES (100002, 1, 102);
^
ALTER TABLE so_items_a ADD CONSTRAINT fkey_a FOREIGN KEY (so_id) REFERENCES so_headers (id);
ERROR: there is no unique constraint matching given keys for referenced table "so_headers"
ALTER TABLE so_items_a DROP CONSTRAINT fkey_a;
ERROR: constraint "fkey_a" of relation "so_items_a" does not exist
ALTER TABLE so_items_a ADD CONSTRAINT constraint_fk
FOREIGN KEY (so_id)
REFERENCES so_headers (id)
ON DELETE CASCADE;
ERROR: there is no unique constraint matching given keys for referenced table "so_headers"
DROP TABLE so_headers;
DROP TABLE so_items;
ERROR: table "so_items" does not exist
DROP TABLE so_items_r;
ERROR: table "so_items_r" does not exist
DROP TABLE so_items_a;
DROP TABLE so_items_b;
ERROR: table "so_items_b" does not exist
DROP TABLE so_items_c;
ERROR: table "so_items_c" does not exist
DROP TABLE payments;
ERROR: table "payments" does not exist
DROP CLIENT MASTER KEY MyCMK CASCADE;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to column encryption key: mycek
drop cascades to column encryption key: mycek2
\! gs_ktool -d all
DELETE ALL
1