On Fri, Mar 18, 2016 at 7:08 AM, Sándor Daku <daku.san...@gmail.com> wrote:
> > On 18 March 2016 at 10:55, Leonardo M. Ramé <l.r...@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&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.
