New n-m relations modelling

[sjib]

With https://github.com/teksi/wastewater/pull/137/files a new way of handling n-m relations is introduced

Set relations DEFERRABLE INITIALLY DEFERRED

ALTER TABLE tww_od.re_maintenance_event_wastewater_structure ADD CONSTRAINT rel_maintenance_event_wastewater_structure_wastewater_structure FOREIGN KEY (fk_wastewater_structure) REFERENCES tww_od.wastewater_structure(obj_id) ON UPDATE CASCADE ON DELETE cascade DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE tww_od.re_maintenance_event_wastewater_structure ADD COLUMN fk_maintenance_event varchar(16)

and

ALTER TABLE tww_od.re_maintenance_event_wastewater_structure ADD CONSTRAINT rel_maintenance_event_wastewater_structure_maintenance_event FOREIGN KEY (fk_maintenance_event) REFERENCES tww_od.maintenance_event(obj_id) ON UPDATE CASCADE ON DELETE cascade DEFERRABLE INITIALLY DEFERRED;

and instead of re_table

/* CREATE TABLE tww_od.re_maintenance_event_wastewater_structure
(
  fk_wastewater_structure varchar (16) NOT NULL,
  fk_maintenance_event varchar (16) NOT NULL,
  CONSTRAINT rel_maintenance_event_wastewater_structure_wastewater_structure FOREIGN KEY (fk_wastewater_structure) REFERENCES tww_od.wastewater_structure(obj_id),
  CONSTRAINT rel_maintenance_event_wastewater_structure_maintenance_event FOREIGN KEY (fk_maintenance_event) REFERENCES tww_od.maintenance_event(obj_id),
  CONSTRAINT pkey_tww_re_maintenance_event_wastewater_structure_obj_id PRIMARY KEY (fk_wastewater_structure, fk_maintenance_event)
)
WITH (
   OIDS = False
); */

two indexes are created:

CREATE INDEX in_re_me_ws__fk_ws ON tww_od.re_maintenance_event_wastewater_structure USING btree (fk_wastewater_structure ASC);
CREATE INDEX in_re_me_ws__fk_me ON tww_od.re_maintenance_event_wastewater_structure USING btree (fk_maintenance_event ASC);

[sjib]

@3nids Is that documented correctly?

[sjib]

@3nids Question - how can you create indexes on a table that does not exist anymore?

[3nids]

The code removed was commented, the two indexes were already there. So no effective change on this file.

Regarding the documentation, I can't tell, I'll let you decide what to document or not.

I believe that any constraint should be deferrable initially deferred in the datamodel.

[sjib]

I believe that any constraint should be deferrable initially deferred in the datamodel.

@ponceta @cymed Do you agree with this suggestion and should I add this to every relation constraint in the data model? Or is there a risk that we create other problems?

Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE. The third class is always IMMEDIATE and is not affected by the SET CONSTRAINTS command. The first two classes start every transaction in the indicated mode, but their behavior can be changed within a transaction by SET CONSTRAINTS.

https://www.postgresql.org/docs/current/sql-set-constraints.html This seems to be a new thing that was introduced with Postgres 12.

As the road map https://github.com/teksi/Home/wiki/TEKSI-modules-releases-and-roadmap bases on Postgres 15 we should be in the ragen of +/-1 versions that support this

[cymed]

I am not deep enough into the deferring to tell whether all will work as expected