[jira] [Updated] (AMBARI-23552) Switch to using Surrogate PK in Ambari DB tables, wherever applicable.
[
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)
[jira] [Updated] (AMBARI-23552) Switch to using Surrogate PK in Ambari DB tables, wherever applicable.
[
https://issues.apache.org/jira/browse/AMBARI-23552?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Swapan Shridhar updated AMBARI-23552:
-
Summary: Switch to using Surrogate PK in Ambari DB tables, wherever
applicable. (was: Switch to using Surrogate PK in Ambari DB tables)
> 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
> 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)
[jira] [Updated] (AMBARI-23552) Switch to using Surrogate PK in Ambari DB tables
[
https://issues.apache.org/jira/browse/AMBARI-23552?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Swapan Shridhar updated AMBARI-23552:
-
Description:
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.
> Switch to using Surrogate PK in Ambari DB tables
>
>
> 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
> 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)
