如何在Postgres中延迟外键约束

huangapple go评论92阅读模式
英文:

How to defer foreign key constraint in Postgres

问题

我尝试将外键约束设置为可延迟,这样在向查找/枢轴表插入数据时就不会进行检查,直到事务结束。然而,在代码中它不起作用,就像在psql shell中一样。和在psql shell中一样,我在代码中使用begin开始一个事务。

这是SQL语句:

create table campaign_r_company (
  campaign_id         uuid            not null references campaign(id) on delete cascade deferrable initially deferred,
  company_id          varchar(32)     not null,
  primary key (campaign_id, company_id)
);

这是代码:

tx, err := d.Begin()
if err != nil {
  return err
}

err = h(tx) // 在这个函数中会调用两个数据库查询

if err == nil {
  err = tx.Commit()
}

h(tx)函数:

_, err := cxt.Exec(fmt.Sprintf(`INSERT INTO hp_campaign (%s) VALUES (%s)`, proplist("", campaignProps), arglist(1, len(campaignProps))),
  id, v.Name, created, v.Updated,
)
if err != nil {
  return err
}

v.Id = id
v.Created = created

if (opts & StoreOptionStoreRelated) == StoreOptionStoreRelated {
  err := d.attach("company", "campaign_r_company", v.Companies, v.Id)
  if err != nil {
    return err
  }
}

attach()函数:

func (d *Database) attach(entityName string, tableName string, ids []string, campaignID string) error {

  for _, id := range ids {

    stmt := fmt.Sprintf(`INSERT INTO %s (%s) VALUES ($1, $2)`, tableName, fmt.Sprintf("campaign_id, %s_id", entityName))
    _, err := d.db.Exec(stmt, campaignID, id)

    if err != nil {
      return err
    }
  }
  return nil
}

错误信息:

insert or update on table "campaign_r_company" violates foreign key constraint "campaign_r_company_campaign_id_fkey"
英文:

I tried to set a foreign key constraint deferrable so that it won't be checked when I insert into a lookup/pivot table until end of transaction. However, it woks in psql shell but its just not working in the code. same as in the psql shell, I start a transaction with begin in code as well.

This is the sql:

create table campaign_r_company (
  campaign_id         uuid            not null references campaign(id) on delete cascade deferrable initially deferred,
  company_id          varchar(32)     not null,
  primary key (campaign_id, company_id)
);

Here's the code:

  tx, err := d.Begin()
  if err != nil {
    return err
  }
  
  
  err = h(tx) // there are two db queries will be called in this function
  
  if err == nil {
    err = tx.Commit()
  }

h(tx):

_, err := cxt.Exec(fmt.Sprintf(`INSERT INTO hp_campaign (%s) VALUES (%s)`, proplist("", campaignProps), arglist(1, len(campaignProps))),
	id, v.Name, created, v.Updated,
)
if err != nil {
	return err
}

v.Id = id
v.Created = created

if (opts & StoreOptionStoreRelated) == StoreOptionStoreRelated {
	err := d.attach("company", "campaign_r_company", v.Companies, v.Id)
	if err != nil {
		return err
	}

}

attach():

func (d *Database) attach(entityName string, tableName string, ids []string, campaignID string) error {

	for _, id := range ids {

		stmt := fmt.Sprintf(`INSERT INTO %s (%s) VALUES ($1, $2)`, tableName, fmt.Sprintf("campaign_id, %s_id", entityName))
		_, err := d.db.Exec(stmt, campaignID, id)

		if err != nil {
			return err
		}
	}
	return nil

}

Error:

insert or update on table "campaign_r_company" violates foreign key constraint "campaign_r_company_campaign_id_fkey"

答案1

得分: 1

根据更新的代码和后续的评论,我们现在知道问题是两个查询被分别执行,而不是在一个事务中执行。

英文:

From the updated code, and subsequent comments, we know now that the issue was that the two queries were executed separately and not in a single transaction.

答案2

得分: 0

如果您不使用手动事务管理,Go+PG会为您处理此事。在这种情况下,每个语句都是一个单独的事务,并且约束条件在每个事务结束时进行检查。因此,如果存在异常,将会引发异常。

英文:

If you do not use manual transaction management, Go+PG does this for you. In this case any statement is a single transaction and constraints are get checked at the end of each. So exception is raised.

huangapple
  • 本文由 发表于 2017年4月11日 22:53:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/43349430.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定