PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Ensure Deletion of Rows Before Insertion Using CTEs

I am using CTE to check database rows, delete rows by filtering and finally to insert row into the database.

Below is the go function to Insert a row:

func (vm VoteModel) Insert(vote *Vote) error {
    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    // begin atomic transaction
    tx, err := vm.DB.BeginTx(ctx, nil)
    if err != nil {
        return err
    }

    // Use a CTE to check if the poll_choice_id belongs to the poll_id and to delete any existing vote
    insertVoteQuery := `
        WITH valid_choice AS (
            SELECT 1
            FROM poll_choices
            WHERE id = $1 AND poll_id = $2
            LIMIT 1
        ),
        delete_existing_vote AS (
            DELETE FROM votes
            WHERE user_id = $3 AND poll_id = $2
            RETURNING 1
        )
        INSERT INTO votes (user_id, poll_id, poll_choice_id)
        SELECT $3, $2, $1
        FROM valid_choice
        RETURNING id, added_at, version
    `
    insertArgs := []interface{}{vote.PollChoiceID, vote.PollID, vote.UserID}

    err = tx.QueryRowContext(ctx, insertVoteQuery, insertArgs...).Scan(
        &vote.ID,
        &vote.AddedAt,
        &vote.Version,
    )
    if err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return rbErr
        }
        switch {
        case errors.Is(err, sql.ErrNoRows):
            return ErrRecordNotFound
        default:
            return err
        }
    }

    // commit
    err = tx.Commit()
    if err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return rbErr
        }
        return err
    }

    return nil
}

Suppose I have these rows of votes table:

 id | user_id | poll_id | poll_choice_id |         added_at          | version 
----+---------+---------+----------------+---------------------------+---------
 10 |      11 |      12 |              5 | 2024-05-21 13:39:58+05:30 |       1
 16 |       3 |      18 |             34 | 2024-05-22 21:57:44+05:30 |       1 <-- existing user vote
 24 |       9 |      18 |             35 | 2024-05-24 10:54:47+05:30 |       1
(3 rows)

When I try to vote to a poll which I already voted with another poll choice like and my user id is 3:

{
    "poll_id": 18,
    "poll_choice_id": 31
}

I get error:

votes_user_id_poll_id_key : "violates unique constraint"

But I thought I deleted any user and poll related votes with models go Insert() function in this line:

delete_existing_vote AS (DELETE FROM votes WHERE user_id = $3 AND poll_id = $2 RETURNING 1 )

What am I missing? I could have separated the query transactions and make extra db calls, but I want to minimize number of db calls.

Tags:

2

Answers


  1. 0

    the answer comes from the manual :

    The sub-statements in WITH are executed concurrently with each other
    and with the main query. Therefore, when using data-modifying
    statements in WITH, the order in which the specified updates actually
    happen is unpredictable […]
    Trying to update the same row twice in a single statement is not
    supported. Only one of the modifications takes place, but it is not
    easy (and sometimes not possible) to reliably predict which one. This
    also applies to deleting a row that was already updated in the same
    statement: only the update is performed. Therefore you should
    generally avoid trying to modify a single row twice in a single
    statement. In particular avoid writing WITH sub-statements that could
    affect the same rows changed by the main statement or a sibling
    sub-statement. The effects of such a statement will not be
    predictable.

    Login or Signup to reply.
  2. 0

    It is not necessary to delete the existing row just Insert it. All supported versions of Postgres support an insert ... on conflict ... clause (see Insert documentation). With that you handle the existing row with an Update. In this case something like:

    insert into votes( user_id, poll_id, poll_choice_id)
     values ($1,$2,$3)
         on conflict (user_id, poll_id)
         do update 
            set poll_choice_id = excluded.poll_choice_id;

    Alternately, with version 15 or greater you can use the merge statement. So (see Merge documentation):

    merge into votes  
     using (select $1, $2, $3) as n(u, p, pc)
        on (user_id, poll_id) = (n.u,n.p)
      when matched then 
           update set poll_choice_id = n.pc
      when not matched then
           insert( user_id, poll_id, poll_choice_id)
           values( n.u, n.p, n.pc);

    See Demo (for each) here.
    NOTE: For demo I have also updated columns, if for no other reason than visualization, added_at and version.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search