Search Postgresql Archives

Re: rule for inserting into partitions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux