View Issue Details

IDProjectCategoryView StatusLast Update
0003698SymmetricDSTaskpublic2019-05-06 09:17
Reporterstegiszn Assigned To 
Priorityurgent 
Status newResolutionopen 
Summary0003698: PostgreSQL 10 and inherited tables
DescriptionHi, we have been using symmetric replication in our projects for 5 years. We use PostgreSQL ver 8 and 9 with inherited tables in DB structure. To work corectly with symmetric service we configure PostgreSQL for symmetric user with PostgreSQL conf param - sql_inheritance = off. This gives us possibility to read data from only master table - not all inherited ones. Now we have new PostgreSQL 10 version and param sql_inheritance was removed. I asked PostgreSQL team about this but they gave me a response that this param cannot be brought back. So my only solution is to run symmetric with SQL script with "only" clausule which will give me data only from asked table, not inherited ones.


For example:
if Symmetric asks a table about existing some record with specific id it will give one if it exists in any inherited table below. If this table have some references with other there will be critical error with them because there is not such a record in asked table.


/*Main structure*/
CREATE TABLE table_head(
  id int8,
  description varchar,
  PRIMARY KEY(id)
) WITHOUT OIDS;

CREATE TABLE table_head_inherited (PRIMARY KEY (id)) inherits (table_head);

CREATE TABLE table_pos(
  id int8,
  reference int8,
  description varchar,
  PRIMARY KEY(id)
) WITHOUT OIDS;
ALTER TABLE table_pos ADD FOREIGN KEY (reference) REFERENCES table_head(id) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE table_pos_inherited (PRIMARY KEY (id)) inherits (table_pos);
ALTER TABLE table_pos_inherited ADD FOREIGN KEY (reference) REFERENCES table_head_inherited(id) ON UPDATE CASCADE ON DELETE CASCADE;

INSERT INTO table_head_inherited VALUES(1, 'one');
INSERT INTO table_pos_inherited VALUES(1,1,'one');


/*Working SymmetricDS with inherited, wrong SQL*/

test=# SELECT * FROM table_head;
 id | description
----+-------------
  1 | one
(1 row)

test=# INSERT INTO table_pos VALUES(2,1,'two');
ERROR: insert or update on table "table_pos" violates foreign key constraint "table_pos_reference_fkey"
DETAIL: Key (reference)=(1) is not present in table "table_head".


/*Correct SQL*/
test=# SELECT * FROM ONLY table_head;
 id | description
----+-------------
(0 wierszy)


To work with SELECT * FROM table_name is wrong. The right solution is SELECT * FROM ONLY table_name.


Is there any possibility to run symmetric with new PostgreSQL 10 configuration with inherited tables in DB? Maybe some configuration parameter with run "select" script with "only" clausule?
Tagsdialect: postgresql

Activities

elong

2019-04-24 18:54

developer   ~0001438

Do you want the "select from only" to be used during an initial or partial load? We could add a parameter that adds the "only" during loads. How does inheritance affect triggers and batches based on change data capture?

stegiszn

2019-05-06 09:17

reporter   ~0001453

I think that the "only" parameter must be a global variable. As I mentioned before, this parameter was a global variable in PostgreSQL conf and it was the correct behavior of the server, but since version 10 and later it is no longer active. Each select, update and delete command will work with inheritance tables. It does not matter where this command is run - trigger, psql or other applications. Only the insert command will work without because this command is set to the exact table. Using this in the insert command will result in a PostgreSQL error.

Issue History

Date Modified Username Field Change
2018-09-04 11:27 stegiszn New Issue
2019-04-24 18:50 elong Tag Attached: dialect: postgresql
2019-04-24 18:54 elong Note Added: 0001438
2019-05-06 09:17 stegiszn Note Added: 0001453