Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

In addition to that, I'd really like:

    CREATE TABLE business_data (
      customer_key TEXT,
      data JSONB  
    ) PARTITION ON customer_key
where it creates a partition for each unique value of customer_key. Seems like this ought not to be that hard.


You already can. PARTITION BY LIST.

CREATE TABLE … PARTITION OF … FOR VALUES IN ('the_customer');

https://www.postgresql.org/docs/current/ddl-partitioning.htm...


Yeah, I guess I just find it a bit frustrating that I have to setup partitions ahead of time using DDL. I would like it if postgres would just automatically create a partition on the fly when I insert new values into the specified column.


That would open up some non-trivial DoS attack vectors as things currently stand. Who would own the new partition? If all records for the company were removed, does the partition get deleted or remain empty? What happens if some performs a large insert with random customer ids?

However if you really want to go down this road, you could put a trigger on the main customer table for INSERT or UPDATE to run a SECURITY DEFINED (setuid) function that runs a CREATE TABLE … IF NOT EXISTS DDL query to your specifications. Just beware of corner cases and be extremely careful regarding secure access to modifications of that customers table.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: