#63 Strange behaviour when doing insert (...) values (...(SELECT ... FROM...WHERE));

[Joakim Lundgren]

I have set up a three node Postgres-XL cluster using postgres-xl-v9.2-src.tar like this

node_name node_port node_host nodeis_primary nodeis_preferred
coord1C 20015 db-4 FALSE FALSE
coord2C 20016 db-5 FALSE FALSE
coord3C 20017 db-6 FALSE FALSE
datanode1D 5434 db-4 TRUE TRUE
datanode2D 5435 db-5 FALSE FALSE
datanode3D 5436 db-6 FALSE FALSE

Each coord and datanode also have slave nodes in a "circular" manner, that
is coord1 master on db-4 has slave coord1 on db-5, coord2 master on db-5
has slave coord 2 on db-6 and coord3 master on db-6 has slave coord3 on
db-4.

Have also GTM-proxies on each node. GTM on db-4 and GTM-slave on db-5

Have created following objects.

CREATE SEQUENCE content_type_seq START WITH 1;

CREATE TABLE IF NOT EXISTS content_type (
id int NOT NULL default nextval('content_type_seq'),
name varchar(255) NOT NULL,
CONSTRAINT content_type_pk PRIMARY KEY (id),
CONSTRAINT content_type_uk1 UNIQUE (name)
) DISTRIBUTE BY REPLICATION;

ALTER SEQUENCE content_type_seq OWNED BY content_type.id;

INSERT into content_type (name) values ('image/jpeg');

CREATE SEQUENCE dent_seq START WITH 1000000;

CREATE TABLE IF NOT EXISTS dent (
id numeric(20) NOT NULL default nextval('dent_seq'),
store_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
account_id numeric(20) NOT NULL,
originator varchar(64) NOT NULL,
content_type int NOT NULL,
uuid bytea NOT NULL,
content bytea DEFAULT NULL,
CONSTRAINT dent_pk PRIMARY KEY (id),
CONSTRAINT dent_uk UNIQUE (uuid)
) DISTRIBUTE BY REPLICATION;

ALTER SEQUENCE dent_seq OWNED BY dent.id;

Now to the problem:

1. I access the database from node -db-4
2. I do an insert into the dent table like this.

INSERT INTO dent (account_id, originator, content_type, content, uuid)
VALUES ( 1000, 'lasse', (SELECT id FROM content_type WHERE name =
'image/jpeg'),
'x7b3232373337353664643631733739323336134383635366436376535366536373232326332323735363936342e2e2e',
'x803031393538613836423462343535383838366163633861336166')

The INSERT is performed but when I access the database the autogenerated
key (ID) in the dent table, has different values depending which node I
access. I got key 1000000 on db-4, 1000001 on db-5 and 1000002 on db-6!

If I exchange the SELECT statement in the VALUES clause to a constant and
do an insert like this

INSERT INTO dent (account_id, originator, content_type, content, uuid)
VALUES ( 1000, 'lasse', 1,
'x7b3232373337353664643631733739323336134383635366436376535366536373232326332323735363936342e2e2e',
'x803031393538613836423462343535383838366163633861336166')

the result turns out to be the expected, that is I get the autogenerated
key to be the same on each node, that is 1000003 on each node.

Have any of you come accross this problem? Any solution? And yes, I used
pgxc_ctl to set up the cluster, so I don't expect it to be installation
related.

Thanks for any ideas!

/Regards Joakim Lundgren