README.md in pgslice-0.1.3 vs README.md in pgslice-0.1.4

- old
+ new

@@ -90,66 +90,99 @@ ```console $ pgslice prep locations created_at day BEGIN; -CREATE TABLE locations_intermediate ( - LIKE locations INCLUDING ALL -); +CREATE TABLE locations_intermediate (LIKE locations INCLUDING ALL); CREATE FUNCTION locations_insert_trigger() -RETURNS trigger AS $$ -BEGIN - EXECUTE 'INSERT INTO public.locations_' || to_char(NEW.created_at, 'YYYYMMDD') || ' VALUES ($1.*)' USING NEW; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; + RETURNS trigger AS $$ + BEGIN + EXECUTE 'INSERT INTO locations_' || to_char(NEW.created_at, 'YYYYMMDD') || ' VALUES ($1.*)' USING NEW; + RETURN NULL; + END; + $$ LANGUAGE plpgsql; CREATE TRIGGER locations_insert_trigger -BEFORE INSERT ON locations_intermediate -FOR EACH ROW EXECUTE PROCEDURE locations_insert_trigger(); + BEFORE INSERT ON locations_intermediate + FOR EACH ROW EXECUTE PROCEDURE locations_insert_trigger(); COMMIT; ``` ```console $ pgslice add_partitions locations --intermediate --past 1 --future 1 BEGIN; -CREATE TABLE locations_20160423 ( - CHECK (created_at >= '2016-04-23'::date AND created_at < '2016-04-24'::date) -) INHERITS (locations_intermediate); +CREATE TABLE locations_20160423 + (CHECK (created_at >= '2016-04-23'::date AND created_at < '2016-04-24'::date)) + INHERITS (locations_intermediate); ALTER TABLE locations_20160423 ADD PRIMARY KEY (id); CREATE INDEX ON locations_20160423 USING btree (updated_at, shopper_id); -CREATE TABLE locations_20160424 ( - CHECK (created_at >= '2016-04-24'::date AND created_at < '2016-04-25'::date) -) INHERITS (locations_intermediate); +CREATE TABLE locations_20160424 + (CHECK (created_at >= '2016-04-24'::date AND created_at < '2016-04-25'::date)) + INHERITS (locations_intermediate); ALTER TABLE locations_20160424 ADD PRIMARY KEY (id); CREATE INDEX ON locations_20160424 USING btree (updated_at, shopper_id); -CREATE TABLE locations_20160425 ( - CHECK (created_at >= '2016-04-25'::date AND created_at < '2016-04-26'::date) -) INHERITS (locations_intermediate); +CREATE TABLE locations_20160425 + (CHECK (created_at >= '2016-04-25'::date AND created_at < '2016-04-26'::date)) + INHERITS (locations_intermediate); ALTER TABLE locations_20160425 ADD PRIMARY KEY (id); CREATE INDEX ON locations_20160425 USING btree (updated_at, shopper_id); COMMIT; ``` ```console +$ pgslice fill locations +/* 1 of 3 */ +INSERT INTO locations_intermediate (id, latitude, longitude, created_at) + SELECT id, latitude, longitude, created_at FROM locations + WHERE id > 0 AND id <= 10000 AND created_at >= '2016-04-23'::date AND created_at < '2016-04-26'::date + +/* 2 of 3 */ +INSERT INTO locations_intermediate (id, latitude, longitude, created_at) + SELECT id, latitude, longitude, created_at FROM locations + WHERE id > 10000 AND id <= 20000 AND created_at >= '2016-04-23'::date AND created_at < '2016-04-26'::date + +/* 3 of 3 */ +INSERT INTO locations_intermediate (id, latitude, longitude, created_at) + SELECT id, latitude, longitude, created_at FROM locations + WHERE id > 20000 AND id <= 30000 AND created_at >= '2016-04-23'::date AND created_at < '2016-04-26'::date +``` + +```console $ pgslice swap locations BEGIN; ALTER TABLE locations RENAME TO locations_retired; ALTER TABLE locations_intermediate RENAME TO locations; + +ALTER SEQUENCE locations_id_seq OWNED BY locations.id; + +COMMIT; +``` + +```console +$ pgslice add_partitions locations --future 2 +BEGIN; + +CREATE TABLE locations_20160426 + (CHECK (created_at >= '2016-04-26'::date AND created_at < '2016-04-27'::date)) + INHERITS (locations); + +ALTER TABLE locations_20160426 ADD PRIMARY KEY (id); + +CREATE INDEX ON locations_20160426 USING btree (updated_at, shopper_id); COMMIT; ``` ## Upgrading