[jira] [Updated] (AMBARI-23552) Switch to using Surrogate PK in Ambari DB tables

["Swapan Shridhar (JIRA)" <ji...@apache.org>]

     [ 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)