Creating a database constraint that checks existing rows

Questions / Help
,
1

I have a table, voucher_redemption, that has the following columns:
id, user_id, voucher_id, inseted_at, updated_at

I’m trying to create a constraint that prevents the same user redeeming a voucher more than once in 24 hours. Or in other words, I want postgres to reject the record if there is another with a matching user_id created within 24 hours.

This doesn’t seem to be possible using constraints, after asking on Stack Overflow I was pointed towards creating a custom function and calling that by a trigger, which is what I’m attempting below:

Ecto.Adapters.SQL.query!(
      Kraken.Repo, "
        CREATE FUNCTION check_one_redemption_per_user_per_day(integer, date)
        RETURNS BOOLEAN
        LANGUAGE plpgsql VOLATILE
        AS $$
        declare clashing_record boolean;
        BEGIN
          SELECT EXISTS into clashing_record(
            SELECT user_id FROM voucher_redemption
              WHERE user_id = $1
              AND inserted_at >= ($2 + INTERVAL '1 day')
          );
        END;
        $$
      "
    )

    Ecto.Adapters.SQL.query!(
      Kraken.Repo, "
        CREATE TRIGGER one_redemption_per_user_per_day
        AFTER INSERT ON voucher_redemption
        FOR EACH ROW
        EXECUTE FUNCTION check_one_redemption_per_user_per_day(user_id, inserted_at)
      "
    )

These are stored in sequential migrations, however when I execute them, the first succeeds, and the second fails with (Postgrex.Error) ERROR 42883 (undefined_function) function check_one_redemption_per_user_per_day() does not exist

I guess the first obvious question is, is this approach the right one? These transactions happen rarely so performance isn’t hugely important. And if this approach isn’t the correct one, is there a way to do this using Ecto?

Thanks in advance

2

I suspect this is because you have both statements in a single transaction and Postgres doesn’t support multiple DDL statements in a single transaction. I suspect if you split into two migrations you will be ok.

3

One can also disable ecto wrapping the migration in a transaction.

4

Both these functions are currently stored in sequential migrations. I originally tried flush() inbetween, then tried sequential migrations. Still no joy unfortunately

5

That should be possible with a check constraint on date range overlap: Prevent overlapping time ranges with Ecto and Postgres

1 Like
6

Now I read the function signatures more closely I suspect its this:

CREATE FUNCTION check_one_redemption_per_user_per_day(integer, date)
EXECUTE FUNCTION check_one_redemption_per_user_per_day(user_id, inserted_at)

The data type date in the CREATE FUNCTION won’t match with inserted_at which is, I assume, a timestamp. Perhaps if you change the function definition you might move forward:

CREATE FUNCTION check_one_redemption_per_user_per_day(integer, timestamp)

Postgres, a little bit like Elixir, allows polymorphic function definitions but the error message is not always that helpful.

7

Nice spot, I got excited for a moment but still getting the same error unfortunately. I’ll follow @LostKobrakai 's solution and look at the btree_gist extension, thanks again

8

That looks like exactly what I’m trying to do, much appreciated