0

In a web game with PostgreSQL 9.3 backend I sometimes have to ban users, by putting them into the following table:

create table pref_ban (
    id varchar(32) primary key,
    first_name varchar(64),
    last_name varchar(64),
    city varchar(64),
    last_ip inet,
    reason varchar(128),
    created timestamp default current_timestamp
);

I ban a user by running the following procedure, so that he/she can not enter the game at next login time (i.e. not the offender is not banned immediately):

create or replace function pref_delete_user(_id varchar,
_reason varchar) returns void as $BODY$
    begin

    insert into pref_ban  --THIS FAILS WITH "duplicate key"
    (id, first_name, last_name, city, last_ip, reason)
    select id, first_name, last_name, city, last_ip, _reason
    from pref_users where id = _id;

    create temporary table temp_gids (gid int not null) on commit drop;
    insert into temp_gids (gid) select gid from pref_scores where id=_id;

    delete from pref_games p
    using temp_gids t
    where p.gid = t.gid;

    create temporary table temp_rids (rid int not null) on commit drop;
    insert into temp_rids (rid) select rid from pref_cards where id=_id;

    delete from pref_rounds r
    using temp_rids t
    where r.rid = t.rid;

    delete from pref_users where id=_id;

    end;
$BODY$ language plpgsql;

However the INSERT statement in the above procedure sometimes fails with:

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "pref_ban_pkey" DETAIL: Key (id)=(GC1121680399) already exists. 

CONTEXT: SQL statement "insert into pref_ban (id, first_name, last_name, city, last_ip, reason) select id, first_name, last_name, city, last_ip, _reason from pref_users where id = _id" PL/pgSQL function pref_delete_user(character varying,character varying) line 4 at SQL statement

This happens when some game stats have been written after I have banned the user for the 1st time (because users are not banned immediately and game stats are sometimes still being written into the database).

This is okay for me, but I wonder, how could I ignore the INSERT failure?

I know that typically the following "UPSERT" scheme is being used with PostgreSQL:

update pref_ban set
    first_name = _first_name,
    last_name  = _last_name,
    city       = _city,
    last_ip    = _last_ip,
    ...
where id = _id;

if not found then
    insert into pref_ban(id,
        first_name,
        last_name,
        city,
        last_ip,
        ...)
    values (_id,
        _first_name,
        _last_name,
        _city,
        _last_ip,
        ...
        now());
end if;

but this is NOT what I am after here: because I don't need to update the banned user details. I would just like to exit the procedure on the INSERT failure.

Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
  • 3
    `insert into ... select ... where not exists (...) ` –  Jun 03 '14 at 10:05
  • This seems to work too: `begin insert ... exception when unique_violation then /* ignore */ end;` – Alexander Farber Jun 03 '14 at 10:43
  • 1
    @a_horse_with_no_name If you `LOCK TABLE ... IN EXCLUSIVE MODE` first, or know there are no concurrent deletes, or don't care about the race conditions. Sounds like the latter is probably actually the case, here, but it's worth thinking about for other readers... – Craig Ringer Jun 03 '14 at 12:20

0 Answers0