[Postgres-xc-general] DML cannot be used in plpgsql functions?

[Jan K. <jan...@tv...>]

Hello all,

I'm currently evaluating if we could use postgres-xc to make some
databases scale beyond one machine.
However I would like to combine sharding across hosts (distribute by
hash as provided by postgres-xc) with standard postgresql trigger
based partitioning.
The release notes for postgres xc claim that DML cannot be used in
plpgsql functions (here:
http://postgres-xc.sourceforge.net/docs/1_2_beta/release-xc-1-2.html )
I'm trying to understand exactly what this statement means. My
expectation was that an insert statement inside a plpgsql function
would somehow raise an error or fail.

However, when I have an insert trigger on a table like this:

CREATE TRIGGER partition_trg
  BEFORE INSERT
  ON history
  FOR EACH ROW
  EXECUTE PROCEDURE trg_partition('day');

and the trg_partition function looks like this:

--------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trg_partition()
  RETURNS trigger AS
$BODY$

DECLARE
prefix text := 'partitions.';
timeformat text;
selector text;
_interval INTERVAL;
tablename text;
startdate text;
enddate text;
create_table_part text;
create_index_part text;
BEGIN

selector = TG_ARGV[0];

IF selector = 'day' THEN
timeformat := 'YYYY_MM_DD';
ELSIF selector = 'month' THEN
timeformat := 'YYYY_MM';
END IF;

_interval := '1 ' || selector;
tablename :=  TG_TABLE_NAME || '_p' || TO_CHAR(TO_TIMESTAMP(NEW.clock)
at time zone 'gmt', timeformat);

EXECUTE 'INSERT INTO ' || prefix || quote_ident(tablename) || ' SELECT
($1).*' USING NEW;
RETURN NULL;

EXCEPTION
WHEN undefined_table THEN

startdate := EXTRACT(epoch FROM date_trunc(selector,
TO_TIMESTAMP(NEW.clock) at time zone 'gmt'));
enddate := EXTRACT(epoch FROM date_trunc(selector,
TO_TIMESTAMP(NEW.clock) at time zone 'gmt' + _interval ));

create_table_part:= 'CREATE TABLE '|| prefix || quote_ident(tablename)
|| ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock <
' || quote_literal(enddate) || '))) INHERITS ( public.'||
TG_TABLE_NAME || ') DISTRIBUTE BY HASH(itemid)';
create_index_part:= 'ALTER TABLE ' || prefix || quote_ident(tablename)
|| ' ADD CONSTRAINT ' || quote_ident(tablename) || '_pkey PRIMARY KEY
(itemid,clock)';

EXECUTE create_table_part;
EXECUTE create_index_part;

--insert it again
EXECUTE 'INSERT INTO ' || prefix || quote_ident(tablename) || ' SELECT
($1).*' USING NEW;
RETURN NULL;

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION trg_partition()
  OWNER TO postgres;

--------------------------------------------------------------------------------------------------------------------------------------

And I do an insert into history like this:

insert into history (itemid, clock, value) values ( 1,1,123);


The partition history_p1970_01_01 does get created and the record is
inserted fine and is visible accross all nodes coördinator nodes and
in the expected data node.
If I do a second insert into the table, resulting in an insert in the
same partition, for example like this:

insert into history (itemid, clock, value) values ( 100,1,123);

The results are a bit surprising: at first it looks like nothing
happened, the new record doesn't show up. I guess that's why it's not
supported. However when I disconnect the session that did the insert,
all of a sudden the data does become visible in the
history_p1970_01_01 partition. It's as if the data doesn't get
committed until the session is disconnected.

Should I throw the towel in the ring and give up on this, or is there
an easy workaround for this so that DML can in fact be used with some
reservations.

The only detail I could find on the subject was this post:
http://sourceforge.net/p/postgres-xc/mailman/message/31433999/
that appears to indicate that it could indeed probably work when
taking some things into account.

On another note, does this limitation apply to all pl/... languages or
only to pl/pgsql? If the cause of these problems is strictly related
to pl/pgsql and would not apply to say plv8 I could rewrite my
triggers.

Kind Regards,

Jan Keirse

-- 


**** DISCLAIMER ****

http://www.tvh.com/newen2/emaildisclaimer/default.html

"This message is delivered to all addressees subject to the conditions
set forth in the attached disclaimer, which is an integral part of this
message."