0

I have a table that has these key columns. Other columns are present but these are the key columns for this question.

user_id: string,
external_id: string
disabled_by: string 

Additional notes:

  1. All columns have the not null constraint.
  2. unique constraint ('external_id_constraint_1') (external_id, disabled_by)
  3. unique constraint ('external_id_constraint_2') (user_id, external_id)

Operations:

Add external id

Adding an new external id for user 1 it would look like this:

insert into external_ids (user_id, external_id, disabled_by) values ('user1', 'exid2', '');

Disabling an external id

If the external id is later revoked for user 1:

update external_ids set disabled_by='admin1' where user_id='user1' and external_id='exid2';

Reenabling the external_id

A user can't have more than 1 entry for a given external_id. If a row exists ('user1', 'exid2', 'admin1') then the row needs to be updated:

update external_ids set disabled_by='' where user_id='user1' and external_id='exid2';

Rather than a new row being created. However another user between the disable and the reenable attempt could have claimed the external_id. If this has happened the update should do nothing.

Solutions attempted

I am trying to get a single postgres sql statement to handle this case.

Things I tried:

  1. an insert into .... on conflict on constraint ... except that I need to handle 2 different constraint violations differently.
  2. update does not have the ability to do the insert if there is nothing to update
  3. update doesn't have the ability to handle a constraint violation.

Question:

Is it possible to do this in a single sql statement or am I doomed to be frustrated.

  • Why do you think you need to do this in a single statement? – CL. Mar 25 at 8:52
  • @CL I assume you are asking this question because you will be answering the question. I hope you are not just wasting my time. The reason is: the software framework expects to execute a single sql command and return the results. Executing multiple sql commands becomes more complex. A stored procedure is similarly problematic as sql stored procedures require internal review and have deployment issues because of versioning problems. Now I am eagerly awaiting your answer. – Pat Mar 28 at 21:21
1

You can combine multiple data-modifying statements by putting them into WITHs:

WITH constraint_1 AS (
  SELECT 1
  FROM external_ids
  WHERE external_id = $2
    AND disabled_by = ''
),
constraint_2 AS (
  UPDATE external_ids
  SET disabled_by = ''
  WHERE user_id     = $1
    AND external_id = $2
    AND disabled_by <> ''
  RETURNING 1
)
INSERT INTO external_ids (user_id, external_id, disabled_by)
SELECT $1, $2, ''
WHERE NOT EXISTS (SELECT * FROM constraint_1)
  AND NOT EXISTS (SELECT * FROM constraint_2);

Like the equivalent stored procedure, this might have races with concurrent updates, depending on the transaction isolation level.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.