Обсуждение: rule for inserting into partitions

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

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).