Re: [Postgres-xc-general] consistency check on SPI tuple count failed & PL/pgsql

[Masataka S. <pg...@gm...>]

Unfortunately it's a restriction.

DML cannot be used in plpgsql functions.
 * http://postgres-xc.sourceforge.net/docs/1_2/release-xc-1-2.html#AEN126656

Regards.

On 19 June 2014 22:00, Thibault Marquand
<thi...@ec...> wrote:
> Hi,
> I have got some PL/pgsql script which are working on PostgreSQL 9.1.
>
> But on PGXC 1.2.1, I have got the error message : consistency check on
> SPI tuple count failed
> I read the oldest post on mail list about this error but in my case,
> T_TABLE_EXAMPLE is empty so could we talk about inconstencies ?
> I am working on replicated tables.
>
> DO $$
> DECLARE
>      CPA_ID BIGINT;
> [...]
> BEGIN
>
>      SELECT "CPA_ID" INTO CPA_ID FROM "T_TABLE_EXAMPLE" WHERE
> "CPA_FILENAME" = 'example_string' AND "CPA_DELETE" IS NULL;
>
>      IF CPA_ID IS NULL
>      THEN
>        INSERT INTO "T_TABLE" (version, "CPA_FILENAME") VALUES (0,
> 'example_string') RETURNING "CPA_ID" INTO CPA_ID;
>      END IF;
> [...]
> END$$;
>
> The entire error is :
> ERROR:  consistency check on SPI tuple count failed
> CONTEXT:  SQL statement "INSERT INTO "T_TABLE" (version, "CPA_FILENAME")
> VALUES (0, 'example_string') RETURNING "CPA_ID""
>      PL/pgSQL function inline_code_block line 20 at SQL statement
>
> Line 20 is the empty line just under SELECT query.
>
> And when I execute the SQL statement "INSERT INTO "T_TABLE" (version,
> "CPA_FILENAME") VALUES (0, 'example_string') RETURNING "CPA_ID""
> directly, it works. I can read T_TABLE_EXAMPLE and my data are on both
> datanodes.
>
> Thibault
>
> ------------------------------------------------------------------------------
> 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