[
https://issues.apache.org/jira/browse/AMBARI-23552?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated AMBARI-23552:
------------------------------------
Labels: pull-request-available (was: )
> Switch to using Surrogate PK in Ambari DB tables, wherever applicable.
> ----------------------------------------------------------------------
>
> Key: AMBARI-23552
> URL: https://issues.apache.org/jira/browse/AMBARI-23552
> Project: Ambari
> Issue Type: Bug
> Components: ambari-server
> Reporter: Swapan Shridhar
> Assignee: Swapan Shridhar
> Priority: Major
> Labels: pull-request-available
> Fix For: 3.0.0
>
>
> The {{clusterservices}} table was given a new surrogate, auto-incrementing
> primary key:
> {code}
> @Entity
> @TableGenerator(name = "service_id_generator",
> table = "ambari_sequences", pkColumnName = "sequence_name", valueColumnName
> = "sequence_value"
> , pkColumnValue = "service_id_seq"
> , initialValue = 1
> )
> {code}
> 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:
> {code}
> 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));
> {code}
> By not using the surrogate PK, we also cause other tables, like
> {{serviceconfig}} to have to create compound FKs as well:
> {code}
> CONSTRAINT FK_serviceconfig_clstr_svc FOREIGN KEY (service_id,
> service_group_id, cluster_id) REFERENCES clusterservices (id,
> service_group_id, cluster_id),
> {code}
> This should just be a single FK to the surrogate ID.
> Same for some other other tables, too, like {{servicegroups}}:
> {code}
> 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));
> {code}
> It uses a surrogate auto-incrementing ID, but it's PK is a compound.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
