Обсуждение: Partition

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.

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)

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

On 18 March 2016 at 10:55, Leonardo M. Ramé <l.rame@griensu.com> 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.

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)

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

I'm quite(but not completely) sure the dynamic constraint won't work.

Also the log data - I guess - will be actual so nothing goes to the _old table, except you keep nudging the records and use an update trigger to move the data around.

Oh, and you should keep the parent table empty. 

The correct way would be to define fixed date ranges for the child tables and keep adding new ones as time advances.(And dropping old ones if you want.)

log ->parent

log_201603 -> child of log, check date_time>'2016.03.01' and date_time<='2016.04.01'

log_201604 -> child of log, check date_time>'2016.04.01' and date_time<='2016.05.01'

Or take a look to the pg_partman extension which promises to do the legwork for you

Regards,

Sándor.

Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről küldték. www.avast.com