0001 LIBNDRXXAECPG(8)
0002 ================
0003 :doctype: manpage
0004
0005
0006 NAME
0007 ----
0008 libndrxxaecpg - Enduro/X PostgreSQL ECPG XA Driver
0009
0010
0011 SYNOPSIS
0012 --------
0013 libndrxxaecpg.so
0014 libndrxxaecpg.dylib
0015
0016 --------------------------------------------------------------------------------
0017 NDRX_XA_RES_ID=1
0018 NDRX_XA_OPEN_STR={"url":"tcp:postgresql://${EX_PG_HOST}/${EX_PG_DB}"
0019 ,"user":"${EX_PG_USER}"
0020 ,"password":"${EX_PG_PASS}"
0021 ,"compat":"PGSQL|INFORMIX|INFORMIX_SE"
0022 }"
0023 NDRX_XA_CLOSE_STR=$NDRX_XA_OPEN_STR
0024 NDRX_XA_DRIVERLIB=libndrxxaecpg.so
0025 NDRX_XA_RMLIB=-
0026 NDRX_XA_LAZY_INIT=1
0027 --------------------------------------------------------------------------------
0028
0029 DESCRIPTION
0030 -----------
0031 This is XA driver specifically written Enduro/X needs. It provides an XA switch
0032 emulation on top of PostgreSQL prepared transactions. PostgreSQL by default
0033 does not support XA switch. Also when transaction start, there is no possibility
0034 to identify the work unit performed. The identification of work done by some
0035 process on particular connection can be done by preparing the transaction. Thus
0036 there is no such thing as "active" transaction in terms of XA specification.
0037 Also there is no possibility for other processes to join the existing work and
0038 see work done by other session. Thus for example if one server process in same
0039 transaction performs some insert and other process tries insert on table which
0040 has foreign key to first insert, it will fail, as FK will not be seen. Thus
0041 Enduro/X needs to work on branch-transactions mode without join feature. The
0042 mode of PostgreSQL driver is the same as enabled by *NDRX_XA_FLAG* with value
0043 *NOJOIN*.
0044
0045 The emulation of XA protocol is done by following steps and assumptions:
0046
0047 1. When process joins to global transaction, new branch-transaction-id is
0048 acquired. The TMSRV logs the branch with status 'u'-unknown.
0049
0050 2. When process completes the work unit (server does tpreturn(3) or tpforward(3))
0051 or initiating process performs tpcommit(3), the xa_end() is called which in-turn
0052 runs PostgreSQL "PREPARE TRANSACTION <XID_STR>". "XID_STR" is based on PosgreSQL
0053 JDBC driver format. Thus JDBC version on PQ versions of tmsrv drivers can be mixed.
0054 When transaction is prepared OK, the TMSRV is reported with 'p'-prepared status.
0055 If prepare fails, this means that transaction is aborted by PosgreSQL, and in
0056 this case 'a'-aborted status is reported to TMSRV.
0057
0058 3. When TMSRV tries to commit, and branch is in 'u'-unknown or 'a'-aborted status,
0059 the global transaction is aborted and tpcommit caller receives *TPEABORT* error.
0060 If transaction is in status 'p', the prepare phase of global transaction is
0061 skipped/no operation and then commit is executed.
0062
0063 4. In case if work unit performs too long operation and tries to xa_end() after
0064 when TMSRV is already timed-out/rolled back the global transaction, the xa_end()
0065 status call to TMSRV fails with error *TPEMATCH*. In this case local process
0066 rolls back the prepared transaction.
0067
0068 5. When tpabort is executed, the xa_end() within tpabort() process performs
0069 abort without executing prepare.
0070
0071 The driver can be used for standard libpq programming and for using ECPG
0072 precompiler. Internally the libndrxxaecpg driver manages connection by help of
0073 ECPG routines. But thanks to their API, the connection handler can be extracted
0074 to used by libPQ.
0075
0076 The connection details are encoded in JSON based string which contains the
0077 database URL, user name and password.
0078
0079 To get connection handler for PG sessions, used *tpgetconn(3)* function.
0080 Which is available for this driver.
0081
0082 CONNECTION PARAMETERS
0083 ---------------------
0084 *url*::
0085 This is standard PostgreSQL connection URL. Typically it contains database
0086 host, port and database name. The Enduro/X standard environment variable
0087 substitution is used here.
0088
0089 *user*::
0090 This is PosgreSQL connection user name. Enduro/X standard environment
0091 substitution applies. Used where applies.
0092
0093 *password*::
0094 Password used for establishing connection. Enduro/X standard environment
0095 substitution applies. Used where applies.
0096
0097 *compat*::
0098 Compatibility mode. The default is *PGSQL*. Other options are *INFORMIX*
0099 and *INFORMIX_SE*.
0100
0101 EXTERNAL SYMBOLS
0102 ----------------
0103 *ndrx_G_PG_conname*::
0104 This is connection name currently associated with thread. It is thread is
0105 stored in thread local storage (TLS). Definition is
0106 *__thread char ndrx_G_PG_conname[65]*.
0107
0108 CONFIGURATION EXAMPLE
0109 ---------------------
0110 When starting to use Enduro/X PQ XA Driver, ensure that PosgreSQL PQ and ECPG
0111 libraries are installed. Particularity *libecpg* and *libpq* must be installed.
0112
0113 The typical configuration is done as a standard Enduro/X XA resource configuration,
0114 which can be set directly in environment variables or in *[@global]* section in
0115 application configuration (e.g. app.ini). This gives example of app.ini configuration.
0116
0117 Sample configuration *app.ini* for CCTAG *DB1_PQ*:
0118
0119 --------------------------------------------------------------------------------
0120
0121 [@global/DB1_PQ]
0122 NDRX_XA_RES_ID=1
0123 NDRX_XA_OPEN_STR={"url":"tcp:postgresql://localhost:5432/testdb",
0124 "user":"testuser", "password":"testuser1"}
0125 NDRX_XA_CLOSE_STR=${NDRX_XA_OPEN_STR}
0126 NDRX_XA_DRIVERLIB=libndrxxaecpg.so
0127 NDRX_XA_RMLIB=-
0128 NDRX_XA_LAZY_INIT=1
0129
0130 --------------------------------------------------------------------------------
0131
0132 Sample configuration of transaction manager in *ndrxconfig.xml* for CCTAG *DB1_PQ*:
0133
0134 --------------------------------------------------------------------------------
0135 <servers>
0136 ...
0137 <server name="tmsrv">
0138 <max>1</max>
0139 <srvid>1650</srvid>
0140 <cctag>DB1_PQ</cctag>
0141 <sysopt>-e /tmp/tmsrv-dom1.log -r -- -t1 -l/tmp</sysopt>
0142 </server>
0143 ...
0144 </servers>
0145 --------------------------------------------------------------------------------
0146
0147 LIBPQ C EXAMPLE
0148 ---------------
0149
0150 This is example of programming database with libpq.
0151
0152 File: test_expq.c
0153 --------------------------------------------------------------------------------
0154 #include <string.h>
0155 #include <stdio.h>
0156 #include <stdlib.h>
0157
0158 #include <atmi.h>
0159 #include <libpq-fe.h>
0160
0161 #define FAIL -1
0162 #define SUCCEED 0
0163
0164 int main(int argc, char** argv)
0165 {
0166 PGconn * conn;
0167 PGresult *res = NULL;
0168 ExecStatusType estat;
0169 int ret = SUCCEED;
0170
0171 /* open connection */
0172 if (EXSUCCEED!=tpopen())
0173 {
0174 fprintf(stderr, "Failed to open: %s\n", tpstrerror(tperrno));
0175 ret = FAIL;
0176 goto out;
0177 }
0178
0179 /* get the connection which was open by tpopen() */
0180 conn = (PGconn *)tpgetconn();
0181
0182 /* create some table... */
0183
0184 res = PQexec(conn, "CREATE TABLE manextest(userid integer UNIQUE NOT NULL);");
0185
0186 estat = PQresultStatus(res);
0187
0188 if (PGRES_COMMAND_OK != estat)
0189 {
0190 char *state = PQresultErrorField(res, PG_DIAG_SQLSTATE);
0191 char *msg = PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY);
0192
0193 fprintf(stderr, "Failed to create table: state: [%s]: %s\n", state, msg);
0194
0195 if (0==strcmp(state, "42P07"))
0196 {
0197 fprintf(stderr, "Table already exist - ignore error\n");
0198 }
0199 else
0200 {
0201 ret = FAIL;
0202 goto out;
0203 }
0204 }
0205
0206 /* start transaction */
0207 if (EXSUCCEED!=tpbegin(60, 0))
0208 {
0209 fprintf(stderr, "Failed to begin: %s\n", tpstrerror(tperrno));
0210 ret = FAIL;
0211 goto out;
0212 }
0213
0214
0215 /* insert data */
0216
0217 PQclear(res);
0218
0219 res = PQexec(conn, "insert into manextest(userid) values ((select COALESCE(max(userid), 1)+1 from manextest));");
0220
0221 estat =PQresultStatus(res);
0222
0223 if (PGRES_COMMAND_OK != estat)
0224 {
0225 char *state = PQresultErrorField(res, PG_DIAG_SQLSTATE);
0226 char *msg = PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY);
0227
0228 fprintf(stderr, "Failed to create table: state: [%s]: %s\n", state, msg);
0229 ret = FAIL;
0230 goto out;
0231 }
0232
0233 if (SUCCEED!=tpcommit(0))
0234 {
0235 fprintf(stderr, "Commit failed: %s\n", tpstrerror(tperrno));
0236 ret = FAIL;
0237 goto out;
0238 }
0239
0240 out:
0241 if (SUCCEED!=ret)
0242 {
0243 tpabort(0);
0244 }
0245 tpclose();
0246 tpterm();
0247
0248 }
0249
0250 --------------------------------------------------------------------------------
0251
0252 Build the program with:
0253
0254 --------------------------------------------------------------------------------
0255
0256 $ cc test_expq.c -o expqtest -I/usr/include/postgresql -lpq -lecpg -latmi -lnstd -lubf -lrt
0257
0258 --------------------------------------------------------------------------------
0259
0260 Run and test:
0261
0262 --------------------------------------------------------------------------------
0263
0264 $ ./expqtest
0265 Failed to create table: state: [42P07]: relation "manextest" already exists
0266 Table already exist - ignore error
0267
0268 $ psql -U testuser -d testdb -h localhost
0269
0270 testdb=> select * from manextest;
0271 userid
0272 ========
0273 2
0274 (1 row)
0275
0276 --------------------------------------------------------------------------------
0277
0278 ECPG C EXAMPLE
0279 --------------
0280
0281 The same code example above can be written in PosgreSQL embedded C code. Assuming
0282 that table is already created by previous example.
0283
0284 File: test_exec.pgc
0285 --------------------------------------------------------------------------------
0286 #include <stdio.h>
0287 #include <stdlib.h>
0288 #include <ndebug.h>
0289 #include <atmi.h>
0290 #include <ecpglib.h>
0291
0292 #define FAIL -1
0293 #define SUCCEED 0
0294
0295 int main(int argc, char** argv)
0296 {
0297 int ret = SUCCEED;
0298 EXEC SQL BEGIN DECLARE SECTION;
0299 long id;
0300 EXEC SQL END DECLARE SECTION;
0301
0302
0303 /* open connection */
0304 if (EXSUCCEED!=tpopen())
0305 {
0306 fprintf(stderr, "Failed to open: %s\n", tpstrerror(tperrno));
0307 ret = FAIL;
0308 goto out;
0309 }
0310
0311 /* start transaction */
0312 if (EXSUCCEED!=tpbegin(60, 0))
0313 {
0314 fprintf(stderr, "Failed to begin: %s\n", tpstrerror(tperrno));
0315 ret = FAIL;
0316 goto out;
0317 }
0318
0319 EXEC SQL SELECT COALESCE(MAX(USERID), 1)+1 into :id from manextest;
0320
0321 if ((sqlca.sqlcode < 0) || (sqlca.sqlcode == 100))
0322 {
0323
0324 fprintf(stderr, "failed to get max: error code [%ld] message [%s] rows %ld, warning %c\n",
0325 sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0]);
0326 ret = FAIL;
0327 goto out;
0328 }
0329
0330
0331 EXEC SQL INSERT INTO manextest(USERID) VALUES (:id);
0332
0333 if ((sqlca.sqlcode < 0) || (sqlca.sqlcode == 100))
0334 {
0335
0336 fprintf(stderr, "Failed to insert: error code [%ld] message [%s] rows %ld, warning %c\n",
0337 sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0]);
0338 ret = FAIL;
0339 goto out;
0340 }
0341
0342 if (SUCCEED!=tpcommit(0))
0343 {
0344 fprintf(stderr, "Commit failed: %s\n", tpstrerror(tperrno));
0345 ret = FAIL;
0346 goto out;
0347 }
0348
0349 out:
0350
0351 if (SUCCEED!=ret)
0352 {
0353 tpabort(0);
0354 }
0355
0356 tpclose();
0357 tpterm();
0358 }
0359
0360 --------------------------------------------------------------------------------
0361
0362 Build program with:
0363
0364 --------------------------------------------------------------------------------
0365
0366 $ ecpg test_exec.pgc
0367 $ cc test_exec.c -o exectest -I/usr/include/postgresql -lecpg -latmi -lnstd -lubf -lrt
0368 --------------------------------------------------------------------------------
0369
0370 Execute the test:
0371
0372 --------------------------------------------------------------------------------
0373 $ ./exectest
0374 Failed to create table: state: [42P07]: relation "manextest" already exists
0375 Table already exist - ignore error
0376
0377 $ psql -U testuser -d testdb -h localhost
0378
0379 testdb=> select * from manextest;
0380 userid
0381 ========
0382 2
0383 3
0384 (1 row)
0385
0386
0387 --------------------------------------------------------------------------------
0388
0389 For more unit tests please see 'atmitest/test067_postgres' unit test folder for
0390 ECPG, PQ source examples and configuration.
0391
0392 BUGS
0393 ----
0394 Report bugs to support@mavimax.com
0395
0396 SEE ALSO
0397 --------
0398 *libndrxxpq(8)* *ndrxconfig.xml(5)* *tmsrv(8)* *libndrxxawsmqs(8)* *libndrxxaoras(8)*
0399 *tpgetconn(3)*
0400
0401 COPYING
0402 -------
0403 (C) Mavimax, Ltd
0404
