Partition

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
<https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&gt;
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

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

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
<https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail&gt;
<#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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general