Re: Partition
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 ->parentlog_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 youRegards,Sándor.
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
В списке pgsql-general по дате отправления: