On Fri, 8 Dec 2006, Tom Lane wrote:
Marc Evans <Marc@xxxxxxxxxxxxxxxxxxx> writes:
I am trying to make use of table partitions. In doing so I would like to
use a rule to call a functioning which inserts the data into the proper
partition.
Basically, you're guaranteeing yourself large amounts of pain by
insisting on using a rule for this. I'd suggest using a trigger
instead. A "BEFORE INSERT" trigger on the parent table can redirect
the data to the appropriate place and then return NULL to prevent the
insertion into the parent.
Given your example, I'd do something like
create or replace function foobars_insert() returns trigger
language plpgsql as $$
begin
if new.created_at >= timestamp '2006-12-01 00:00:00' and
new.created_at < timestamp '2007-01-01 00:00:00' then
insert into foobars_200612 values(new.*);
elsif new.created_at >= timestamp '2007-01-01 00:00:00' and
new.created_at < timestamp '2007-02-01 00:00:00' then
insert into foobars_200701 values(new.*);
elsif ...
else
raise exception 'No partition for timestamp %', new.created_at;
end if;
return null;
end$$;
create trigger foobars_insert before insert on foobars
for each row execute procedure foobars_insert();
Obviously you have to adjust the function definition every time
you add or remove a partition, but you'll have a script for that
anyway, no?
BTW, I think using "new.*" this way only works as of 8.2.
In reply to myself, mainly so that when people search archives they may
benefit from the end result, I am including here the solution I finally
cam up with to provide lazy support for partitioned data insertion into
tables. The code shown here will automatically create a partition based on
the year/month, inserting the data into the partition. The column from the
insert is defined by the trigger call, defaulting to the year/month found
by gmtime (this could result in a small race problem when relying on the
default).
If you have suggestions about ways to improve this, or thoughts like OMG,
that code will be painfully high in overhead, please let me know how you
would approach the problem (short of hard-coding all partitions).
- Marc
CREATE OR REPLACE FUNCTION yearmonth_to_timestamp(INTEGER,INTEGER) RETURNS
TIMESTAMP AS $$
BEGIN RETURN ($1 || '-' || lpad($2,2,0) || '-01 00:00:00')::timestamp;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION create_date_partition(TIMESTAMP,TEXT,TEXT)
RETURNS VOID as $$
DECLARE
date ALIAS FOR $1;
table_name ALIAS FOR $2;
date_column ALIAS FOR $3;
year INTEGER := EXTRACT(year FROM date);
month INTEGER := EXTRACT(month FROM date);
ts TIMESTAMP := yearmonth_to_timestamp(year,month);
te TIMESTAMP;
BEGIN
IF month < 12 THEN
te := yearmonth_to_timestamp(year,month+1);
ELSE
te := yearmonth_to_timestamp(year+1,1);
END IF;
EXECUTE 'CREATE TABLE ' || table_name || '_' || year || lpad(month,2,0) || '(
CHECK (' || quote_ident(date_column) || ' >= ' || quote_literal(ts) || ' and ' ||
quote_ident(date_column) || ' < ' || quote_literal(te) || ')
) INHERITS (' || table_name || ')';
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION insert_date_partition() RETURNS TRIGGER AS $$
my %values = %{$_TD->{new}};
my $table_name = $_TD->{table_name};
my $column_name = $_TD->{args}[0];
my $date = $values{$column_name};
my ($year,$month,$day);
if (!defined($date)) {
($year,$month,$day) = (gmtime)[5,4,3];
$year += 1900;
$month += 1;
$date = sprintf "%d %02d %02d",$year,$month,$day;
} else {
($year,$month) = (split(/(\d\d\d\d)[-\/ ](\d\d?)[-\/ ]/,$date))[1,2];
}
my $partition = $table_name . '_' . sprintf "%04d%02d",$year,$month;
my $columns = join(',',keys(%values));
my $data = join(',',map { $_ =~ s/'/''/g; defined($_) ? "'$_'" : 'NULL';
} values(%values));
$insert = "INSERT INTO $partition ($columns) VALUES ($data)";
eval { spi_exec_query($insert); };
if ($@) {
my $create = "SELECT create_date_partition('$date'::timestamp,'$table_name'::text,'$column_name'::text)";
spi_exec_query($create);
spi_exec_query($insert);
}
return SKIP;
$$ LANGUAGE plperlu;
-- plperlu is used instead of plperl above because of the eval.
-- An example trigger would be:
CREATE TRIGGER test_table_partition_trigger
BEFORE INSERT ON test_table
FOR EACH ROW EXECUTE PROCEDURE insert_date_partition('created_at');
