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

[鈴木 幸市 <ko...@in...>]

If my memory is correct, you can issue DML only once in plpgsql functions.

Regards;
---
Koichi Suzuki

2014/06/12 0:15、Jan Keirse <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."
> 
> ------------------------------------------------------------------------------
> HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
> Find What Matters Most in Your Big Data with HPCC Systems
> Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
> Leverages Graph Analysis for Fast Processing & Easy Data Exploration
> http://p.sf.net/sfu/hpccsystems
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general