Re: Partition

On 03/18/2016 02:55 AM, Leonardo M. Ramé wrote:
> Hi, I have read and re-read the Partitioning chapter
> (http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html), but I
> still don't see how to implement this use case:
>
> One table storing current data, let's call it the "master table", then
> one or more partitions with old data.
>
> For example, the master table is this:
>
>      create table log(
>        idlog serial not null,
>        date_time timestamp not null default now(),
>        log varchar(255),
>        primary key(idlog)
>      );
>
> The documentation says the master table should be empty, then a trigger
> must evaluate a condition, the date_time field for example, and insert
> the data in the corresponding table. This is a *rare* condition, because
> in the log example, new content is created without knowing its date and
> time in advance. For example:
>
>      insert into log(log) values('log this please.');
>
> The date_time column will set the now() value.

True but you can catch that value in the BEFORE trigger as
NEW.date_time. A default is still just a value being entered into the field.

>
> Now, by following the example, to create a child table I'll do
>
>      create table log_old( ) inherits (log);
>
> This raises the 1nst question, how can I define a *dynamic* check,
> for checking older than X days?. Is this possible?.
>
> An idea (didn't test):
>
>      check (date_time::date < now()::date - '30 day'::interval)

Where are you putting this CHECK?

FYI, should not need the casts to date. Interval will work with datetimes.

>
> Then, the trigger, after each insert should *move* old data to log_old.
>
> The only problem I see here is the master table isn't empty, but
> contains current data. The question is, will it work as expected?, I
> mean when I do "select * from log" I'll get an *union* of new and old
> data?.

If you use a BEFORE trigger on the master table and redirect the INSERT
to a partition and RETURN NULL from said trigger, then the INSERT will
not happen on the master.

>
> Regards,


--
Adrian Klaver
adrian.klaver@aklaver.com