#18 Cannot Insert Records

[Mike]

I create the database and run the ddl to create a table then try to do inserts and the insert does not work. I can load the table from csv files using the copy to xxxx from xxx.csv But Inserts do not work.

[postgres@cen-6-pgxl-coord ~]$ psql -U certi CRISTAL_SDP
psql (PGXL 9.2.0, based on PG 9.2.4 (Postgres-XL 9.2.0))
Type "help" for help.

CRISTAL_SDP=# INSERT INTO CRISTAL.CDSEX (CDEPIN,RORDIN,CTYENR,XTYPEN,NUMFIC)
CRISTAL_SDP-# VALUES('17',
CRISTAL_SDP(# '1',
CRISTAL_SDP(# 'CABECI',
CRISTAL_SDP(# 'O12',
CRISTAL_SDP(# '0') ;
INSERT 0 0

INSERT 0 Records. Why?? I can do a copy from to load the tables but can't insert records???

I have 4 servers, 1 gtm, 1 coordinator and 2 datanodes.

I set the following kernel values on each server:
sysctl -w kernel.shmmax=17179869184
sysctl -w kernel.shmall=4194304

pgxl definitions:
psql -c "CREATE NODE datanode1 WITH (TYPE = 'datanode', HOST = '192.168.1.43', PORT = 15432)" postgres
psql -c "CREATE NODE datanode2 WITH (TYPE = 'datanode', HOST = '192.168.1.44', PORT = 15433)" postgres
psql -c "CREATE NODE GROUP data_cluster WITH (datanode1, datanode2)" postgres
psql -c "SELECT pgxc_pool_reload()" postgres

192.168.1.41
mkdir /usr/local/pgsql/gtm
chown postgres /usr/local/pgsql/gtm
su - postgres
initgtm -D /usr/local/pgsql/gtm -Z gtm
gtm -D /usr/local/pgsql/gtm >gtm.log 2>&1 &

1:139778787112768:2014-07-09 16:09:09.475 EDT -LOG: Saving transaction restoration info, backed-up gxid: 12731
LOCATION: GTM_WriteRestorePointXid, gtm_txn.c:2649
1:139778787112768:2014-07-09 16:09:09.476 EDT -LOG: Started to run as GTM-Active.
LOCATION: main, main.c:641
1:139778787112768:2014-07-09 16:09:35.744 EDT -LOG: Any GTM standby node not found in registered node(s).
LOCATION: gtm_standby_connect_to_standby_int, gtm_standby.c:381
1:139778787112768:2014-07-09 16:09:48.058 EDT -LOG: Any GTM standby node not found in registered node(s).
LOCATION: gtm_standby_connect_to_standby_int, gtm_standby.c:381
1:139778787112768:2014-07-09 16:09:48.081 EDT -LOG: Any GTM standby node not found in registered node(s).
LOCATION: gtm_standby_connect_to_standby_int, gtm_standby.c:381
.
.
.

192.168.1.43
mkdir /usr/local/pgsql/datanode1
chown postgres /usr/local/pgsql/datanode1
su - postgres
initdb -D /usr/local/pgsql/datanode1 --nodename datanode1
postgres --datanode -p 15432 -D /usr/local/pgsql/datanode1 > datanode1.log 2>&1 &
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
.
.
.

192.168.1.44
mkdir /usr/local/pgsql/datanode2
chown postgres /usr/local/pgsql/datanode2
su - postgres
initdb -D /usr/local/pgsql/datanode2 --nodename datanode2
postgres --datanode datanode2 -p 15433 -D /usr/local/pgsql/datanode2 >datanode2.log 2>&1 &
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
.
.
.

192.168.1.42
mkdir /usr/local/pgsql/coord1
chown postgres /usr/local/pgsql/coord1
su - postgres
initdb -D /usr/local/pgsql/coord1 --nodename coord1
postgres --coordinator -D /usr/local/pgsql/coord1 >coord1.log 2>&1 &
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
.
.
.

[Pavan Deolasee]

Can you please share output of "SELECT * FROM pgxc_node" from each node? Is
it possible to have access to the machine for debugging? Also, if you have
an Intel machine around, would you mind trying on that?

Thanks,
Pavan

On Thu, Sep 10, 2015 at 11:00 PM, Pradyumna Muppirala pradyumnam@users.sf.net wrote:

Hi Pavan,
Thanks for quick response. Here are answers to some of your questions.

1. I am running all nodes (i.e. PostgresXL installations ) on multiple
   ports of same machine.
2. I did not notice any core files or any other suspicious errors. I
   will retry this another time after changing ulimit
3. My machine is a single machine Ubuntu 14.04 LTS AMD 64 bit processor
4. I can reproduce this problem from psql. Please review
   "PostgresXLInsertFailure.txt" file attached above.
5. I tried restarting all nodes atleast 10 to 15 times, during
   testing. But restart did not help at all.
6. Please review "PostgresXLInsertFailure.txt". That file as pgxc_node
   table contents also.

I was trying to test the setup process on local machine. I was successful
in installation. The only problem I faced is that Inserts are not happening.

If I notice any core files, I will pass them on. Could you please review
the detailed attachments , attached above?

thanks
Pradyumna

---

• [tickets:#18] http://sourceforge.net/p/postgres-xl/tickets/18/ Cannot
  Insert Records*

Status: open
Milestone: 9.2rc
Created: Thu Jul 10, 2014 10:48 AM UTC by Mike
Last Updated: Tue Sep 08, 2015 02:58 PM UTC
Owner: nobody

I create the database and run the ddl to create a table then try to do
inserts and the insert does not work. I can load the table from csv files
using the copy to xxxx from xxx.csv But Inserts do not work.

[postgres@cen-6-pgxl-coord ~]$ psql -U certi CRISTAL_SDP
psql (PGXL 9.2.0, based on PG 9.2.4 (Postgres-XL 9.2.0))
Type "help" for help.

CRISTAL_SDP=# INSERT INTO CRISTAL.CDSEX
(CDEPIN,RORDIN,CTYENR,XTYPEN,NUMFIC)
CRISTAL_SDP-# VALUES('17',
CRISTAL_SDP(# '1',
CRISTAL_SDP(# 'CABECI',
CRISTAL_SDP(# 'O12',
CRISTAL_SDP(# '0') ;
INSERT 0 0

INSERT 0 Records. Why?? I can do a copy from to load the tables but can't
insert records???

I have 4 servers, 1 gtm, 1 coordinator and 2 datanodes.

I set the following kernel values on each server:
sysctl -w kernel.shmmax=17179869184
sysctl -w kernel.shmall=4194304

pgxl definitions:
psql -c "CREATE NODE datanode1 WITH (TYPE = 'datanode', HOST =
'192.168.1.43', PORT = 15432)" postgres
psql -c "CREATE NODE datanode2 WITH (TYPE = 'datanode', HOST =
'192.168.1.44', PORT = 15433)" postgres
psql -c "CREATE NODE GROUP data_cluster WITH (datanode1, datanode2)"
postgres
psql -c "SELECT pgxc_pool_reload()" postgres

192.168.1.41
mkdir /usr/local/pgsql/gtm
chown postgres /usr/local/pgsql/gtm
su - postgres
initgtm -D /usr/local/pgsql/gtm -Z gtm
gtm -D /usr/local/pgsql/gtm >gtm.log 2>&1 &

1:139778787112768:2014-07-09 16:09:09.475 EDT -LOG: Saving transaction
restoration info, backed-up gxid: 12731
LOCATION: GTM_WriteRestorePointXid, gtm_txn.c:2649
1:139778787112768:2014-07-09 16:09:09.476 EDT -LOG: Started to run as
GTM-Active.
LOCATION: main, main.c:641
1:139778787112768:2014-07-09 16:09:35.744 EDT -LOG: Any GTM standby node
not found in registered node(s).
LOCATION: gtm_standby_connect_to_standby_int, gtm_standby.c:381
1:139778787112768:2014-07-09 16:09:48.058 EDT -LOG: Any GTM standby node
not found in registered node(s).
LOCATION: gtm_standby_connect_to_standby_int, gtm_standby.c:381
1:139778787112768:2014-07-09 16:09:48.081 EDT -LOG: Any GTM standby node
not found in registered node(s).
LOCATION: gtm_standby_connect_to_standby_int, gtm_standby.c:381
.
.
.

192.168.1.43
mkdir /usr/local/pgsql/datanode1
chown postgres /usr/local/pgsql/datanode1
su - postgres
initdb -D /usr/local/pgsql/datanode1 --nodename datanode1
postgres --datanode -p 15432 -D /usr/local/pgsql/datanode1 > datanode1.log
2>&1 &
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
.
.
.

192.168.1.44
mkdir /usr/local/pgsql/datanode2
chown postgres /usr/local/pgsql/datanode2
su - postgres
initdb -D /usr/local/pgsql/datanode2 --nodename datanode2
postgres --datanode datanode2 -p 15433 -D /usr/local/pgsql/datanode2

datanode2.log 2>&1 &
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
.
.
.

192.168.1.42
mkdir /usr/local/pgsql/coord1
chown postgres /usr/local/pgsql/coord1
su - postgres
initdb -D /usr/local/pgsql/coord1 --nodename coord1
postgres --coordinator -D /usr/local/pgsql/coord1 >coord1.log 2>&1 &
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
.
.
.

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/postgres-xl/tickets/18/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[Pradyumna Muppirala]

Hello Pavan,
PGXC_NODE output from each node is also present in postgreXLInsertFailure.txt file attached above.
Please review that once. If you feel that the best way to find out the problem is by debugging, I will start debugging then.

Thanks
Pradyumna

[Shahid Azeez]

I am having the same issue. Not able to insert values into tables.

INSERT INTO test (id, num) VALUES(1,1);
INSERT 0 0

select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+--------------+----------------+------------------+-------------
db2 | D | 20008 | localhost | t | f | -79866771
db3 | D | 20008 | 172.16.10.59 | f | f | -1472228704
coord3 | C | 20004 | 172.16.10.59 | f | f | 1638403545
coord2 | C | 20004 | localhost | f | f | -1197102633
(4 rows)

[Pradyumna Muppirala]

Hi Shahid,
I managed to fix this issue. Here is what I did.

1. Got the latest version PostgresXL 9.2 RC code from Git , exposed on sourceforge.net.
2. Individually setup each data node and coordinatator, by following separate setup process, instead of using utilities.
3. This leads to a situation where every node is initially configured as coordinator node.
4. Later I used alter node command to change the data nodes to be configured with node_type D
5. Then I explicitly created all other nodes in the cluster in every node, so that all nodes know each other.
6. And in my start up script, I made sure pgxc_pool_reload() is executed, by directly connecting to each data node and coordinator node using psql.

This process of configuration and initialization has helped me in sorting out my issue.

I hope this helps!
Best of luck.
Pradyumna

[Pradyumna Muppirala]

And also, please ensure that there are no port conflicts.