Re: [Postgres-xc-bugs] Trigger (before or after) delete problem - ERROR: type "a" does not exist

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Sorry that I did not reply long.   Maybe this is a bug.   What version
did you use?    If you used 1.2, there could be some remaining
conflict with native PG code in schema qualification.

Simpler reproduction will be helpful.

Best;
---
Koichi Suzuki

2014-03-18 4:22 GMT+09:00 André Toshio Nogueira Nishitani <ato...@gm...>:
> Hi,
>
> I don't know if this is a bug, I just messed up something or I forgot about
> some PGXC restriction, but I can't create a trigger (before or after) delete
> in a schema other then the public schema.
> The hosts configuration is the following:
>
> HOST 1:
> - GTM
> - COORDINATOR
> - DATANODE 1
>
> HOST 2:
> - GTM PROXY
> - DATANODE 2
>
> I believe the error can be replicated using the refint.example and setting a
> schema different from public.
> I tried functions other then check_foreign_key() and check_primary_key(),
> but the result was the same.
> These are the refint.example I used to test and the result.
>
> refint.example:
> ----------------------------------------------------------------------------
> --Column ID of table A is primary key:
>
> CREATE TABLE test.A (
>     ID    int4 not null
> );
> CREATE UNIQUE INDEX AI ON test.A (ID);
>
> --Columns REFB of table B and REFC of C are foreign keys referenting ID of
> A:
>
> CREATE TABLE test.B (
>     REFB    int4
> );
> CREATE INDEX BI ON test.B (REFB);
>
> CREATE TABLE test.C (
>     REFC    int4
> );
> CREATE INDEX CI ON test.C (REFC);
>
> --Trigger for table A:
>
> CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON test.A FOR EACH ROW
> EXECUTE PROCEDURE
> check_foreign_key (2, 'cascade', 'id', 'test.b', 'refb', 'test.c', 'refc');
> /*
> 2    - means that check must be performed for foreign keys of 2 tables.
> cascade    - defines that corresponding keys must be deleted.
> ID    - name of primary key column in triggered table (A). You may
>       use as many columns as you need.
> B    - name of (first) table with foreign keys.
> REFB    - name of foreign key column in this table. You may use as many
>       columns as you need, but number of key columns in referenced
>       table (A) must be the same.
> C    - name of second table with foreign keys.
> REFC    - name of foreign key column in this table.
> */
>
> --Trigger for table B:
>
> CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON test.B FOR EACH ROW
> EXECUTE PROCEDURE
> check_primary_key ('refb', 'test.a', 'id');
>
> /*
> REFB    - name of foreign key column in triggered (B) table. You may use as
>       many columns as you need, but number of key columns in referenced
>       table must be the same.
> A    - referenced table name.
> ID    - name of primary key column in referenced table.
> */
>
> --Trigger for table C:
>
> CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON test.C FOR EACH ROW
> EXECUTE PROCEDURE
> check_primary_key ('refc', 'test.a', 'id');
>
> -- Now try
>
> INSERT INTO test.A VALUES (10);
> INSERT INTO test.A VALUES (20);
> INSERT INTO test.A VALUES (30);
> INSERT INTO test.A VALUES (40);
> INSERT INTO test.A VALUES (50);
>
> INSERT INTO test.B VALUES (1);    -- invalid reference
> INSERT INTO test.B VALUES (10);
> INSERT INTO test.B VALUES (30);
> INSERT INTO test.B VALUES (30);
>
> INSERT INTO test.C VALUES (11);    -- invalid reference
> INSERT INTO test.C VALUES (20);
> INSERT INTO test.C VALUES (20);
> INSERT INTO test.C VALUES (30);
>
> DELETE FROM test.A WHERE ID = 10;
> DELETE FROM test.A WHERE ID = 20;
> DELETE FROM test.A WHERE ID = 30;
>
> SELECT * FROM test.A;
> SELECT * FROM test.B;
> SELECT * FROM test.C;
> ----------------------------------------------------------------------------
>
>
> and the output was:
> ----------------------------------------------------------------------------
> CREATE TABLE
> CREATE INDEX
> CREATE TABLE
> CREATE INDEX
> CREATE TABLE
> CREATE INDEX
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> psql:refint.example:66: ERROR:  tuple references non-existent key
> DETAIL:  Trigger "bt" found tuple referencing non-existent key in "test.a".
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> psql:refint.example:71: ERROR:  tuple references non-existent key
> DETAIL:  Trigger "ct" found tuple referencing non-existent key in "test.a".
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> psql:refint.example:76: ERROR:  type "a" does not exist
> psql:refint.example:77: ERROR:  type "a" does not exist
> psql:refint.example:78: ERROR:  type "a" does not exist
>  id
> ----
>  40
>  50
>  10
>  20
>  30
> (5 rows)
>
>  refb
> ------
>    10
>    30
>    30
> (3 rows)
>
>  refc
> ------
>    20
>    20
>    30
> (3 rows)
> ----------------------------------------------------------------------------
>
> Regards,
> --
> André Toshio Nogueira Nishitani.
>
> Mestrando em Ciências da Computação
> ICMC - USP - São Carlos
>
> Sent from my IQTELL Virtual Workspace
>
> ------------------------------------------------------------------------------
> Learn Graph Databases - Download FREE O'Reilly Book
> "Graph Databases" is the definitive new guide to graph databases and their
> applications. Written by three acclaimed leaders in the field,
> this first edition is now available. Download your free book today!
> http://p.sf.net/sfu/13534_NeoTech
> _______________________________________________
> Postgres-xc-bugs mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>