Re: Partition

On Fri, Mar 18, 2016 at 7:08 AM, Sándor Daku <daku(dot)sandor(at)gmail(dot)com> wrote:

>
> On 18 March 2016 at 10:55, Leonardo M. Ramé <l(dot)rame(at)griensu(dot)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
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> <#1995191727766771537_DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>

It would really be helpful for future reference if you provided the
PostgreSQL version and O/S, but nevertheless:

The best way to do it is to make a new version of the old master
and base the child/partitions off of that.
eg:

CREATE TABLE log_new(
idlog serial not null,
date_time timestamp not null default now(),
log varchar(255),
primary key(idlog)
);

CREATE TABLE log1()
INHERITS (log_new);
ALTER TABLE log1
ADD CONSTRAINT log1_pk PRIMARY KEY (idlog);
CREATE TABLE log2()
INHERITS (log_new);
ALTER TABLE log2
ADD CONSTRAINT log2_pk PRIMARY KEY (idlog);
CREATE TABLE log3()
INHERITS (log_new);
ALTER TABLE log3
ADD CONSTRAINT log3_pk PRIMARY KEY (idlog);

CREATE OR REPLACE FUNCTION log_insert_fn()
RETURNS TRIGGER AS
$$
BEGIN
IF NEW.date_time < '2015-01-01' THEN
INSERT INTO log1(idlog, date_time, log)
VALUES
( NEW.idlog, NEW.date_time, NEW.log );
ELSEIF NEW.date_time >= '2015-01-01' AND NEW.date_time <=
'2015-12-31'THEN
INSERT INTO log2(idlog, date_time, log)
VALUES
( NEW.idlog, NEW.date_time, NEW.log );
ELSE
INSERT INTO log3(idlog, date_time, log)
VALUES
( NEW.idlog, NEW.date_time, NEW.log );
END IF;
RETURN NEW;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER log_insert
BEFORE INSERT ON log_new
FOR EACH ROW
EXECUTE PROCEDURE log_insert_fn();

Then, at a convenient time, split the data:
INSERT INTO log_new SELECT * FROM log;

and finally
ALTER TABLE log RENAME TO log_old;
ALTER TABLE log_new RENAME TO log;

You can then either keep or drop log_old.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.