How to defer foreign key constraint in Postgres

Question

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"

Maybe your code is running with autocommit enabled? And at which point do you get that error message? — , Apr 11 '17 at 14:55
how is that? you start transaction, then you have two transactions inside it and you end it?.. what >// there are two db transactions in this function means?.. — Vao Tsun, Apr 11 '17 at 14:55
yea thats the idea. I start transaction, and then call a function which contains two db transactions. and end it in the end. — shangsunset, Apr 11 '17 at 14:57
You can't have "two transactions" inside one transaction. If you commit a transaction the constraints are checked and the transaction is ended. — , Apr 11 '17 at 14:58
@a_horse_with_no_name I get the error message when I insert into a lookup table inside of `h(tx)` — shangsunset, Apr 11 '17 at 14:59
@shangsunset could you show us the contents/logic of `h(tx)` too? — pozs, Apr 11 '17 at 15:07
@a_horse_with_no_name my mistake. I meant two query statements instead of two transactions. — shangsunset, Apr 11 '17 at 15:09
Are `ctx.Exec` and `d.db.Exec` inside `d.attach` using the same transaction? The one you pass to `h`? From the provided code it doesn't look like they do. — mkopriva, Apr 11 '17 at 15:26
@mkopriva oops... thats it! you are right I'm not using the same transaction. Thanks so much. — shangsunset, Apr 11 '17 at 15:31
@mkopriva I would love to accept your solution if you don't mind posting as an answer. Now that I'm using the same transaction, I don't need to set foreign key constraint as deferrable any more. — shangsunset, Apr 11 '17 at 18:36

score 1 · Accepted Answer · answered Apr 11 '17 at 18:55

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.

answered Apr 11 '17 at 18:55

mkopriva

35,176
4
57
71

score 0 · Answer 2 · answered Apr 11 '17 at 18:19

0

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.

answered Apr 11 '17 at 18:19

Eugene Lisitsky

12,113
5
38
59

How to defer foreign key constraint in Postgres

2 Answers2