Re: rule for inserting into partitions

Marc Evans wrote:
> Hello -
>
> I have a schema setup which I want to use partitions with. The intent is
> to partition based on the "created_at" column, seperating based on the
> year/month. What I am struggling with is a rule that I can use to
> automatically determine which partition data should be inserted into, such
> that I want to derive the table name based on the data being inserted. For
> example, a non-functional rule that shows my goal is:
>
> create or replace rule test_partition as insert into tests do instead
> insert into (select 'tests_' ||
> (extract(year from NEW.created_at) * 100 +
> extract(month from NEW.created_at))::text)
> values (id,created_at,data);
>
> In the above, the sub expression to derive the table name doe not parse.
> My question is, what could I do instead of the above to achieve the same?
> Options that I am aware of include:
>
> * Use a list of hard coded table name and range check combinations. This
> option doesn't scale over time, e.g. you are always needing to expand
> the list of table names over time.
>
> * Modify application code to directly insert into the partition. This is
> not very friendly to the programmer(s), and is far less flexible over
> time, should the partitioning logic need to change.
>
> * Create a function which returns the table name to be used, which the
> application code then uses to insert directly into. This to some extent
> resolves the above 2 issues, though requires cooperation of the
> application programmers, which I'd ideally like to avoid.
>
> Any suggestions?
>
> - Marc

You can view (1) as a positive, insofar as you can add/remove rules on
a monthly basis to "turn on" and "turn off" inserts into monthly
partitions as time goes on (i.e. "freeze" previous partitions). As for
(3), you can supply a stored procedure that does the INSERTs, and
guarantee cooperation by not giving INSERT permission to the underyling
table(s).