Hello,
Here is the SQL for the DDL of the schema:
/*** START OF DDL SQL ***/
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
CREATE TABLE table_z (
name character varying NOT NULL,
attr1 character varying NOT NULL,
attr2 character varying,
PRIMARY KEY ((name, attr1) HASH)
);
CREATE INDEX table_z_attr1_idx ON table_z (attr1);
CREATE INDEX table_z_attr2_idx ON table_z (attr2);
CREATE INDEX table_z_attr1_partial_idx ON table_z (attr1)
WHERE (attr1 NOT IN ('X', 'Y', 'Z'));
CREATE INDEX table_z_name_gin_idx ON table_z USING YBGIN (name gin_trgm_ops);
CREATE INDEX table_z_name_idx ON table_z (name);
CREATE INDEX table_z_name_like ON table_z (name varchar_pattern_ops);
CREATE TABLE table_x (
name character varying NOT NULL,
comm1 character varying NOT NULL,
comm2 character varying,
comm3 character varying,
comm4 character varying,
attr1 CHARACTER VARYING NOT NULL,
attr2 CHARACTER VARYING,
attr3 CHARACTER VARYING,
attr4 CHARACTER VARYING,
PRIMARY KEY (name HASH)
);
CREATE TABLE table_y (
name character varying NOT NULL,
comm1 character varying NOT NULL,
comm2 character varying,
comm3 character varying,
comm4 character varying,
attr1 character varying NOT NULL,
attr2 character varying,
attr3 character varying,
attr4 character varying,
attr5 numeric,
attr6 character(1),
attr7 character varying,
attr8 numeric(9,6),
attr9 numeric(8,6),
attr10 numeric(2,0),
attr11 character varying,
attr12 character varying,
attr13 character varying,
attr14 timestamp(3) with time zone,
attr15 boolean,
PRIMARY KEY(name HASH),
CONSTRAINT table_y_unique UNIQUE (comm1, attr1)
);
CREATE INDEX table_y_comm1 ON table_y (comm1);
CREATE INDEX table_y_attr1 ON table_y (attr1);
CREATE INDEX table_y_attr9 ON table_y (attr9);
CREATE INDEX table_y_attr9_in_rads ON table_y (radians(attr9));
CREATE INDEX table_y_attr10 ON table_y (attr10);
CREATE INDEX table_y_attr10_in_rads ON table_y (radians(attr10));
CREATE INDEX table_y_attr12 ON table_y (attr12);
CREATE INDEX table_y_comm2 ON table_y (comm2);
CREATE OR REPLACE VIEW view_w AS
SELECT name, comm1, comm2, comm3, comm4
FROM table_x
UNION ALL
SELECT name, comm1, comm2, comm3, comm4
FROM table_y;
CREATE OR REPLACE VIEW view_v AS
SELECT name, comm1
FROM view_w;
CREATE OR REPLACE VIEW ename AS
SELECT name
FROM table_z
UNION ALL
SELECT name
FROM view_v;
CREATE RULE view_w_delete_rule AS ON DELETE TO view_w
DO INSTEAD (
DELETE FROM table_x WHERE name = old.name;
DELETE FROM table_y WHERE name = old.name;
);
CREATE RULE view_v_delete_rule AS ON DELETE TO view_v
DO INSTEAD DELETE FROM view_w WHERE name = old.name;
CREATE RULE ename_delete_rule AS ON DELETE TO ename
DO INSTEAD (
DELETE FROM table_z WHERE name = old.name;
DELETE FROM view_v WHERE name = old.name;
);
/*** END OF DDL SQL ***/
Here is the SQL that inserts the sample data I had when the problem occurred:
INSERT INTO table_x (name, comm1, comm2, comm3, comm4, attr1) VALUES ('name_2', 'comm_1', 'comm_2_2', 'comm_3_2', 'comm_4_2', 'attr_1_2');
INSERT INTO table_y (name, comm1, comm2, comm3, comm4, attr1, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr14)
VALUES ('name_1', 'comm_1', 'comm_2_1', 'comm_3_1', 'comm_4_1', 'a', 'b', 'c', 0, 1, 'd', -199.000000, -99.000000, 1, 'x', '2022-01-13 15:28:08.28+02');
INSERT INTO table_z (name, attr1, attr2) VALUES ('name_1', 'mars', '10');
INSERT INTO table_z (name, attr1, attr2) VALUES ('name_1', 'venus', 'empty');
INSERT INTO table_z (name, attr1, attr2) VALUES ('name_1', 'jupiter', 'full');
INSERT INTO table_z (name, attr1, attr2) VALUES ('name_1', 'X', 'X_1');
INSERT INTO table_z (name, attr1, attr2) VALUES ('name_2', 'X', 'X_2');
The problem had occurred at some point when I was performing SELECT from ename view after deleting data from the views. (I was deleting data, then re-inserting it later on in the tables, and deleting it again, and so forth, for some tests I was doing.)
Your input is greatly appreciated.
Thank you