Message-ID: <BLU0-SMTP2145D8DE7E99BCAB06AB71DB94A0@phx.gbl>
Subject: Re: Daily Rotated Insertion
MIME-Version: 1.0 (Apple Message framework v1257)
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_7AC1CFEB-2184-4C5F-B194-075F1B96F2D8"
From: Haifeng Liu <liuhaifeng@live.com>
In-Reply-To: 
 <CAL_0b1v0r16GzUER+V_jJo+Z9ySxtPZHCqzmM0rTXv3btM0_oA@mail.gmail.com>
Date: Tue, 27 Mar 2012 18:05:56 +0800
CC: pgsql-admin@postgresql.org
References: <BLU0-SMTP195BAAB8B6E63CE73CF5849B9450@phx.gbl>
	<CAL_0b1sTAbkyUQUzKfnY6+yHQqSgZ9JVbBV6HH6KC6tu6zH5fA@mail.gmail.com>
	<BLU0-SMTP4434590A1672562321D21ACB94A0@phx.gbl>
	<CAL_0b1v0r16GzUER+V_jJo+Z9ySxtPZHCqzmM0rTXv3btM0_oA@mail.gmail.com>
To: Sergey Konoplev <gray.ru@gmail.com>

--Apple-Mail=_7AC1CFEB-2184-4C5F-B194-075F1B96F2D8
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"


On Mar 27, 2012, at 3:33 PM, Sergey Konoplev wrote:

> On Tue, Mar 27, 2012 at 10:02 AM, Haifeng Liu <liuhaifeng@live.com> =
wrote:
>> Yes, that's what I am doing. There are many ways to implement =
partitioned table, I just want to find an elegant one. Bulk create a =
certain number of partitions is not good in my opinion. What I am trying =
now is let the trigger which switches insertions to catch exceptions and =
create new partitions on demand, meanwhile. update the trigger itself to =
use the new switch rule.
>>=20
>> This idea can use static statement to switch insertions, dynamic =
statements are only used to create partition and update the trigger, =
this should be good for performance. And compare to the static partition =
example given in the document, this solution can auto-update itself, =
there is no need for cron jobs to maintenance partition.
>=20
> Okay, I got what you mean. I used to use auto partitioning in one of
> my projects but it assumed errors catching on the application server
> side. However you can easily adapt the solution for your needs. I
> attached the script to the message.
>=20
> BTW, I created it several years ago. A lot of nice stuff like CREATE
> TABLE IF NOT EXISTS were added in the new versions of PostgreSQL. So I
> think may be you could get rid of catching exceptions completely with
> help of these new features.
>=20
> Please let me know if you decide to use this approach and modify it.

Thanks for sharing your script. Mine is done now, my goal is make the =
trigger more simple for most case, so I use a function to create trigger =
function and make the redirect rule static, and if an exception occurred =
when new DATE is found, the trigger will call the function to recreate =
itself.

Main ddls of my solution is posted as attachment.=

--Apple-Mail=_7AC1CFEB-2184-4C5F-B194-075F1B96F2D8
Content-Disposition: attachment; filename="auto-partition.sql"
Content-Type: application/octet-stream; name="auto-partition.sql"
Content-Transfer-Encoding: 7bit

-- Function: create_partition(date)

-- DROP FUNCTION create_partition(date);

CREATE OR REPLACE FUNCTION create_partition(dt date DEFAULT now())
  RETURNS integer AS
$BODY$declare tomorrow date;
begin
  tomorrow = dt + interval '1 days';

  execute 'CREATE TABLE "MY_TBL_'||dt||'"
(
  CONSTRAINT "MY_TBL_'||dt||'_pkey" PRIMARY KEY ("ID" ),
  CONSTRAINT "MY_TBL_'||dt||'_DATE_CHECK" CHECK ("DATE" >= DATE '''||dt||''' AND "DATE" < DATE '''||tomorrow||''' )
)
INHERITS ("MY_TBL")
WITH (
  OIDS=FALSE
);
ALTER TABLE "MY_TBL_'||dt||'"
  OWNER TO postgres;';
  return null;
exception when duplicate_table then
  return null;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION create_partition(date)
  OWNER TO postgres;


-- Function: my_tbl_redirect()

-- DROP FUNCTION my_tbl_redirect();

CREATE OR REPLACE FUNCTION my_tbl_redirect()
  RETURNS trigger AS
$BODY$

begin

  if (NEW."DATE" >= DATE '2012-03-25' AND NEW."DATE" < DATE '2012-03-26') then
    insert into "MY_TBL_2012-03-25" values(NEW.*);
  else -- for today, normal case
    insert into "MY_TBL_2012-03-26" values(NEW.*);
  end if;
  return null;

exception when undefined_table or check_violation then

  -- create missing table
  perform create_partition(date(NEW."DATE"));

  -- alter itself
  perform update_my_tbl_redirect(date(NEW."DATE"));

  -- insert again
  execute 'insert into "MY_TBL_'||date(NEW."DATE")||'"
    values('||quote_literal(NEW."ID")||',
    '||quote_literal(date(NEW."DATE"))||',
    '||quote_literal(NEW."OTHER_FIELD")||')';
  return null;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION my_tbl_redirect()
  OWNER TO postgres;


-- Function: update_my_tbl_redirect(date)

-- DROP FUNCTION update_my_tbl_redirect(date);

CREATE OR REPLACE FUNCTION update_my_tbl_redirect(dt date DEFAULT now())
  RETURNS integer AS
$BODY1$declare yesterday date;

begin
  yesterday = dt - interval '1 days';

  execute 'CREATE OR REPLACE FUNCTION my_tbl_redirect()
  RETURNS trigger AS
$BODY$

begin

  if (NEW."DATE" >= DATE '''||yesterday||''' AND NEW."DATE" < DATE '''||dt||''') then
    insert into "MY_TBL_'||yesterday||'" values(NEW.*);
  else -- for today, normal case
    insert into "MY_TBL_'||dt||'" values(NEW.*);
  end if;
  return null;

exception when undefined_table or check_violation then

  -- create missing table
  perform create_partition(date(NEW."DATE"));

  -- alter itself
  perform update_my_tbl_redirect(date(NEW."DATE"));

  -- insert again
  execute ''insert into "MY_TBL_''||date(NEW."DATE")||''"
    values(''||quote_literal(NEW."ID")||'',
    ''||quote_literal(date(NEW."DATE"))||'',
    ''||quote_literal(NEW."OTHER_FIELD")||'')'';
  return null;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION my_tbl_redirect()
  OWNER TO postgres;';

  return null;
end;$BODY1$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_my_tbl_redirect(date)
  OWNER TO postgres;


--Apple-Mail=_7AC1CFEB-2184-4C5F-B194-075F1B96F2D8
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="iso-8859-1"


> 
>> 
>> I am not sure if this idea work, still trying now.
>> 
>>> 
>>>> 
>>>> Any idea is appreciated. Thanks in advance.
>>>> 
>>>> 
>>>> Best regards.
>>>> liuhaifeng
>>>> --
>>>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-admin
>>> 
>>> 
>>> 
>>> --
>>> Sergey Konoplev
>>> 
>>> Blog: http://gray-hemp.blogspot.com
>>> LinkedIn: http://ru.linkedin.com/in/grayhemp
>>> JID/GTalk: gray.ru@gmail.com Skype: gray-hemp
>>> 
>>> --
>>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-admin
>>> 
>> 
>> 
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
> 
> 
> 
> -- 
> Sergey Konoplev
> 
> Blog: http://gray-hemp.blogspot.com
> LinkedIn: http://ru.linkedin.com/in/grayhemp
> JID/GTalk: gray.ru@gmail.com Skype: gray-hemp
> <auto_partitioning.sql>
> -- 
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin


--Apple-Mail=_7AC1CFEB-2184-4C5F-B194-075F1B96F2D8--