Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
None
Description
The clusterservices table was given a new surrogate, auto-incrementing primary key:
@Entity @TableGenerator(name = "service_id_generator", table = "ambari_sequences", pkColumnName = "sequence_name", valueColumnName = "sequence_value" , pkColumnValue = "service_id_seq" , initialValue = 1 )
However, the table doesn't use this for its PK. Instead, it combines it with 2 other columns. This would allow a single Service Group to be a part of 2 clusters and still be considered unique (which is incorrect). Compound PKs also present a problem in slower cloud-based databases as they can cause table locks on read which lead to deadlocks in the database:
CREATE TABLE clusterservices ( id BIGINT NOT NULL, service_name VARCHAR(255) NOT NULL, service_type VARCHAR(255) NOT NULL, cluster_id BIGINT NOT NULL, service_group_id BIGINT NOT NULL, service_enabled INTEGER NOT NULL, CONSTRAINT PK_clusterservices PRIMARY KEY (id, service_group_id, cluster_id), CONSTRAINT UQ_service_id UNIQUE (id), CONSTRAINT FK_clusterservices_cluster_id FOREIGN KEY (service_group_id, cluster_id) REFERENCES servicegroups (id, cluster_id));
By not using the surrogate PK, we also cause other tables, like serviceconfig to have to create compound FKs as well:
CONSTRAINT FK_serviceconfig_clstr_svc FOREIGN KEY (service_id, service_group_id, cluster_id) REFERENCES clusterservices (id, service_group_id, cluster_id),
This should just be a single FK to the surrogate ID.
Same for some other other tables, too, like servicegroups:
CREATE TABLE servicegroups ( id BIGINT NOT NULL, service_group_name VARCHAR(255) NOT NULL, cluster_id BIGINT NOT NULL, CONSTRAINT PK_servicegroups PRIMARY KEY (id, cluster_id), CONSTRAINT FK_servicegroups_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id));
It uses a surrogate auto-incrementing ID, but it's PK is a compound.
Attachments
Issue Links
- links to
