Re: *** QUESTION *** After successful 'BEGIN; ' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?

Hello Adrian,

Thanks for your prompt reply.

[AK]
So what is the actual PHP code that is being run?

[SP]
Please see attached zip file <pgsql-general - BEGIN; & pg_transaction_status.zip> with the PHP source code and other components.

* * *
* * *

File <pgsql-general -- Steve Petrie - BEGIN; & pg_transaction_status - 20151010.txt> is an inventory of the components in the zip file.

The diagnostic HTML page displayed is in file <ABEND - Expressway Traffic Optimization (ETO) - 20151011.htm>.

The diagnostics page shows that the BEGIN; command has completed OK but the transaction status of the connection is PGSQL_TRANSACTION_ACTIVE instead of being PGSQL_TRANSACTION_INTRANS:

$sql_transaction_status<1> // PGSQL_TRANSACTION_ACTIVE
$sql_result_status_long<1> // PGSQL_COMMAND_OK

* * *
* * *

The action of interest occurs in PHP module <eto__sql_pg_ssi_0.php>

(Please be aware that you will see plenty of code in this module that still references mysql, as the work to convert to PostgreSQL is in progess. However, the code on the path being tested, is all referencing PostgreSQL)

A synopsis of the program control flow leading to the test of pg_transaction_status($sql_conn) is:

https://localhost/eto__sql_pg_test_2a.php
eto__sql_pg_test_2.php
etoSQLdbOpen(1, ABEND_EXIT, ABEND_NOROLLBACK);
pg_connect(...);
pg_connection_status($sql_conn);
pg_parameter_status($sql_conn, 'server_version');
etoSQLsetPostgresErrorVerbosity(PGSQL_ERRORS_TERSE);
pg_set_error_verbosity($sql_conn, $sql_error_verbosity);
pg_set_client_encoding($sql_conn, SQL_CHARSET_CLIENT)
pg_client_encoding($sql_conn)
etoSQLquery(($ac+Yia), $xo, $ro, SQL_NASUM_EQ0)
etoSQLinitCall();
etoSQLpostgres_query($sql_query, ($ac+Yha), $xo, $ro)
pg_send_query($sql_conn, $qs)
pg_connection_busy($sql_conn)
pg_get_result($sql_conn);
pg_result_status($sql_result, PGSQL_STATUS_LONG);
pg_affected_rows($sql_result);
pg_num_rows($sql_result);
etoSQLcheckQuery($qt, ($ac+Yha+Yf), $xo, $ro)

The actual test of the transaction status value is:

// Is transaction status INcorrect? (correct status == idle in a transaction block).
if
(
!(
($sql_transaction_status = pg_transaction_status($sql_conn))
==
PGSQL_TRANSACTION_INTRANS
)
)
// yes -- transaction status INcorrect.
{
$return_val = false;
$sql_abend_inc = 7;
}

At this point the PHP program has detected that $sql_transaction_status != PGSQL_TRANSACTION_INTRANS and comes to an abnormal termination, displaying a diagnostics HTML page.

* * *
* * *

The test environment where I am encountering the issue is:
Windows XP Professional 2002 SP 3
PHP 5.3.1
PostgreSQL 9.3.5
libpq 8.4.1
It is my undersanding that libpq 8.4.1 should be compatible with PostgreSQL 9.3.5.

(The production environment is not yet fully operational, but it uses DragonFlyBSD as OS and will use the most current stable versions available of the PostgreSQL components.)

* * *
* * *

If there is anything else that I can provide or do at my end, just let me know.

Thanks for your help.

Regards,

Steve

* * *

Steve Petrie, P.Eng.

ITS-ETO Consortium
Oakville, Ontario, Canada
(905) 847-3253
apetrie(at)aspetrie(dot)net

----- Original Message -----
From: "Adrian Klaver" <adrian(dot)klaver(at)aklaver(dot)com>
To: "Steve Petrie, P.Eng." <apetrie(at)aspetrie(dot)net>; <pgsql-general(at)postgresql(dot)org>
Sent: Saturday, October 10, 2015 3:35 PM
Subject: Re: [GENERAL] *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?

> On 10/10/2015 11:43 AM, Steve Petrie, P.Eng. wrote:
>> (Thanks to Adrian Klaver & Rob Stone, for their responses to my earlier
>> posting, subject "[GENERAL] BEGIN, END & ROLLBACK commands -- do they
>> produce a SQLSTATE value?". I worked around the unavailability of
>> sqlstate '00000' after a successful SQL command, by creating proxy PHP
>> variables $sql_state_proxy and $sql_state_class_proxy.)
>> *Now I have a question about the value expected to be returned by
>> function pg_transaction_status(...) after a successful execution of a
>> postgres 'BEGIN;' command.*
>> After my PHP program executes an (apparently) successful 'BEGIN;'
>> command, it is expecting function pg_transaction_status(...) to return a
>> value == PGSQL_TRANSACTION_INTRANS (==2 -- idle, in a transaction block).
>> However, function pg_transaction_status(...) is instead returning
>> a value == PGSQL_TRANSACTION_ACTIVE ( ==1 -- command in progress on the
>> connection, a query has been sent via the connection and not yet completed).
>> *Question: What value should function pg_transaction_status(...)return
>> after a successful 'BEGIN;' command?*
>> * * *
>> * * *
>> Here is a list of diagnostic info displayed by the PHP program when it
>> detects that
>> (pg_transaction_status(...) != PGSQL_TRANSACTION_INTRANS)
>>
>> $sql_conn<Resource id #15>
>> $sql_conn_type<resource>
>> *$sql_connection_status<0>* //
>> PGSQL_CONNECTION_OK pg_connection_status($sql_conn);
>> $sql_connection_status_type<integer><integer>
>> $sql_min_version<9.3>
>> $sql_srv_version<9.3.5>
>> *$sql_query<BEGIN;>*
>> $sql_result<Resource id #16>
>> $sql_result_type<resource>
>> *$sql_state<>* // pg_result_error_field($sql_result,
>> PGSQL_DIAG_SQLSTATE);
>> $sql_state_type<NULL>
>> $sql_state_class<>
>> $sql_state_proxy<00000>
>> $sql_state_class_proxy<00>
>> *$sql_transaction_status<1>* //
>> PGSQL_TRANSACTION_ACTIVE pg_transaction_status($sql_conn);
>> $sql_transaction_status_type<integer>
>> *$sql_result_status_long<1>* // PGSQL_COMMAND_OK pg_result_status
>> ($sql_result, PGSQL_STATUS_LONG);
>> $sql_result_status_long_type<integer>
>> *$sql_result_status_string<BEGIN>* // pg_result_status ($sql_result,
>> PGSQL_STATUS_STRING);
>> $sql_result_status_string_type<string>
>> *$sql_result_error<>* // pg_result_error ($sql_result);
>> $sql_result_error_type<string>
>> *$sql_last_error<>* // pg_last_error($sql_conn);
>> $sql_last_error_type<string>**
>>
>> Here are the corresponding relevant postgres log lines (in file
>> <postgresql.conf>: log_line_prefix = 'ETO::%e::'):
>>
>> ETO::00000::LOG: 00000: database system was shut down at 2015-10-06
>> 06:10:39 EDT
>> ETO::00000::LOCATION: StartupXLOG,
>> src\backend\access\transam\xlog.c:4888
>> ETO::00000::LOG: 00000: connection received: host=127.0.0.1 port=1035
>> ETO::00000::LOCATION: BackendInitialize,
>> src\backend\postmaster\postmaster.c:3850
>> ETO::57P03::FATAL: 57P03: the database system is starting up
>> ETO::57P03::LOCATION: ProcessStartupPacket,
>> src\backend\postmaster\postmaster.c:1986
>> ETO::00000::LOG: 00000: database system is ready to accept connections
>> ETO::00000::LOCATION: reaper, src\backend\postmaster\postmaster.c:2602
>> ETO::00000::LOG: 00000: autovacuum launcher started
>> ETO::00000::LOCATION: AutoVacLauncherMain,
>> src\backend\postmaster\autovacuum.c:424
>> ...
>> ETO::00000::LOG: 00000: connection received: host=127.0.0.1 port=1083
>> ETO::00000::LOCATION: BackendInitialize,
>> src\backend\postmaster\postmaster.c:3850
>> ETO::00000::LOG: 00000: connection authorized: user=its-eto_pg36
>> database=eto_sql_db
>> ETO::00000::LOCATION: PerformAuthentication,
>> src\backend\utils\init\postinit.c:239
>> ETO::00000::LOG: 00000: statement: set client_encoding to 'LATIN1'
>> ETO::00000::LOCATION: exec_simple_query, src\backend\tcop\postgres.c:890
>> ETO::00000::LOG: 00000: duration: 63.000 ms
>> ETO::00000::LOCATION: exec_simple_query,
>> src\backend\tcop\postgres.c:1118
>> *ETO::00000::LOG: 00000: statement: BEGIN;
>> ETO::00000::LOCATION: exec_simple_query,
>> src\backend\tcop\postgres.c:890
>> ETO::00000::LOG: 00000: duration: 0.000 ms
>> ETO::00000::LOCATION: exec_simple_query,
>> src\backend\tcop\postgres.c:1118
>> *ETO::00000::LOG: 00000: disconnection: session time: 0:00:00.297
>> user=its-eto_pg36 database=eto_sql_db host=127.0.0.1 port=1083
>> ETO::00000::LOCATION: log_disconnections,
>> src\backend\tcop\postgres.c:4444
>>
>> * * *
>> * * *
>> Thanks For Any Feedback,
>
> So what is the actual PHP code that is being run?
>
>
>> *Steve*
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com