How do I cap number of rows in based on category using postgres policies?

Question

I have a database of orders and each order has a time_slot (of type TIME).

select id, time_slot from orders limit 5;

 10 | 13:00:00
 11 | 12:00:00
 13 | 11:00:00
 14 | 12:30:00
 15 | 11:30:00

I want to make sure that only a certain number of orders can be placed in a time slot; for example, let's say I only want 8 orders per time slot.

I am using Supabase, so I would like to implement using RLS policies.

I've been through several iterations, but none of them have worked. Some complain of infinite recursion. My current approach is the following: I have created a view of time slot load.

create or replace view time_slot_load as 
  select time_slot, count(*) 
  from orders
  group by time_slot;
select * from time_slot_load limit 5;

 11:00:00  |     1
 12:30:00  |     1
 11:30:00  |     1
 13:00:00  |     1
 12:00:00  |     7

I can then create a policy that checks against this view.

ALTER POLICY "Only 8 orders per time slot"
  ON public.orders
  WITH CHECK (
    (SELECT (load.count <= 8)
       FROM time_slot_load load
       WHERE (load.time_slot = orders.time_slot))
    );

But this is not working.

Is there some way I can do this using constraints or RLS policies? Any help is appreciated.

Answer 1

demo

table structure:

begin;

create table orders_count(
    time_slot tstzrange ,
    order_count integer default 0,
     constraint order_ct_max1000 check(order_count <=4));
create table 
    orders(orderid int primary key, realtimestamp timestamptz , order_info text);

with a as(
SELECT x as begin_,x + interval '1 hour' as end_
FROM   generate_series(
    timestamp '2022-01-01',
    timestamp '2022-01-02',
    interval  '60 min') t(x))
insert into orders_count(time_slot)  
       select tstzrange(a.begin_, a.end_,'[]') from a;
commit;

two table, one table for all the orders info, another table is track the timeslot's count, also make one constraint, within one timeslot no more than 4 orderid. Then create trigger for delete/update/insert action on table orders. for table orders_count, 20 years only 175200 rows (one hour one row).

main trigger function:

begin;
create or replace function f_trigger_orders_in()
returns trigger as
$$
begin
update orders_count set  order_count =  order_count + 1
where time_slot @> NEW.realtimestamp;
return null;
end;
$$ language plpgsql;

create or replace function f_trigger_orders_up()
returns trigger as
$$
begin
if OLD.realtimestamp <> NEW.realtimestamp THEN
update orders_count
set order_count = order_count -1
where time_slot @> OLD.realtimestamp;

update orders_count
set order_count  = order_count + 1
where time_slot @> NEW.realtimestamp;
end if;
return null;
end
$$ language plpgsql;

create or replace function f_trigger_order_delaft()
returns trigger as
$$
BEGIN
update orders_count set order_count = order_count -1 where time_slot  @> OLD.realtimestamp;
return  null;
end;
$$
language plpgsql;

triggers action:

create trigger trigger_in_orders 
    AFTER INSERT ON public.orders FOR EACH ROW execute procedure f_trigger_orders_in();
create trigger trigger_up_orders 
    after update on public.orders for each row execute procedure f_trigger_orders_up();
create trigger trigger_del_orders 
    AFTER DELETE ON public.orders  FOR EACH ROW execute procedure f_trigger_order_delaft();

Answer 2

I want to make sure that only a certain number of orders can be placed in a time slot; for example, let's say I only want 8 orders per time slot.

You cannot do that in PostgreSQL.
https://www.postgresql.org/docs/current/sql-createpolicy.html

check_expression:

Any SQL conditional expression (returning boolean). The conditional expression cannot contain any aggregate or window functions. This expression will be used in INSERT and UPDATE queries against the table if row-level security is enabled. Only rows for which the expression evaluates to true will be allowed. An error will be thrown if the expression evaluates to false or null for any of the records inserted or any of the records that result from the update. Note that the check_expression is evaluated against the proposed new contents of the row, not the original contents.

Why view won't work:
https://www.postgresql.org/docs/current/sql-createview.html
see Updatable Views section:

A view is automatically updatable if it satisfies all of the following conditions:

• The view's select list must not contain any aggregates, window functions or set-returning functions.