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

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

From

"Steve Petrie, P.Eng."

Date:

10 October 2015, 21:44:30

(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,

Steve

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

From

Adrian Klaver

Date:

10 October 2015, 22:35:35

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.klaver@aklaver.com

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

From

rob stone

Date:

12 October 2015, 06:58:51

> 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,
>
> Steve

Hello Steve,

I really do not understand "why" you need the SQLSTATE code after
executing a "BEGIN" so as to go into transaction state.

AFAIK you can only retrieve the SQLSTATE error code when an error
actually occurs. So, if your query statement was successful, then PHP
does not expose a SQLSTATE code of 00000.

If I run the following code:-

----------------------------------------------
<?php

require_once '../actions/DataBaseFunctions.php';

$pgconn = conn_db();
$my_query = "SELECT * FROM rhubarb";
if (!pg_connection_busy($pgconn)) {
    $my_result = pg_send_query($pgconn, $my_query);
    pg_set_error_verbosity($pgconn, PGSQL_ERRORS_VERBOSE);
    $res1 = pg_get_result($pgconn);
    echo pg_result_error($res1) . PHP_EOL;
} else {
    echo 'Connection Busy' . PHP_EOL;
}

?>

--------------------------------------------------------------

it returns the following:-


ERROR:  42P01: relation "rhubarb" does not exist
LINE 1: SELECT * FROM rhubarb
                      ^
LOCATION:  parserOpenTable, parse_relation.c:986


SQLSTATE 42P01 is the error "undefined_table".


Note that you have to use pg_send_query to take advantage of
pg_get_result, etc.


HTH,

Rob

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

From

"Steve Petrie, P.Eng."

Date:

12 October 2015, 10:32:37

Hello Rob,

Thanks for your response.

[RS]
I really do not understand "why" you need the SQLSTATE code after
> executing a "BEGIN" so as to go into transaction state.
>
> AFAIK you can only retrieve the SQLSTATE error code when an error
> actually occurs. So, if your query statement was successful, then PHP
> does not expose a SQLSTATE code of 00000.

[SP]
Yes, I do know that postgres does not supply a sqlstate value if a command
completes successfully. That question was resolved earlier.

And yes, I am using pg_send_query(...) to execute the BEGIN; command, so as
to get a result resource returned. (Please see my forthcoming emailed
response to Adrian Klaver, wherein I provide the PHP source code that Adrian
requests.)

* * *
* * *

My present question is not about sqlstate, but about the value returned by
function pg_transaction_status(...) after a successful BEGIN; command.

As I understand them, the possible return values from function
pg_transaction_status(...) are:

//
// Return values from function pg_transaction_status($sql_conn);
//
// 0 = PGSQL_TRANSACTION_IDLE    (connection is currently idle, not in a
transaction)
// 1 = PGSQL_TRANSACTION_ACTIVE  (command in progress on the connection, a
query has been sent via the connection and not yet completed)
// 2 = PGSQL_TRANSACTION_INTRANS (idle, in a transaction block)
// 3 = PGSQL_TRANSACTION_INERROR (idle, in a failed transaction block)
// 4 = PGSQL_TRANSACTION_UNKNOWN (the connection is bad)
//

So I am expecting, after a successful BEGIN; command has completed, that the
value returned by function pg_transaction_status(...) will be:

   2 == PGSQL_TRANSACTION_INTRANS

But instead, my PHP program is showing:

   1 == PGSQL_TRANSACTION_ACTIVE

Which does not make sense to me, as the BEGIN; command has completed and my
PHP program has not yet initiated any new command on the postgres
connection.

Regards,

Steve

* * *

Steve Petrie, P.Eng.

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

----- Original Message -----
From: "rob stone" <floriparob@gmail.com>
To: "Steve Petrie, P.Eng." <apetrie@aspetrie.net>;
<pgsql-general@postgresql.org>
Sent: Sunday, October 11, 2015 11:58 PM
Subject: Re: [GENERAL] *** QUESTION *** After successful 'BEGIN;' command --
why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?


>
>> 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,
>>
>> Steve
>
> Hello Steve,
>
> I really do not understand "why" you need the SQLSTATE code after
> executing a "BEGIN" so as to go into transaction state.
>
> AFAIK you can only retrieve the SQLSTATE error code when an error
> actually occurs. So, if your query statement was successful, then PHP
> does not expose a SQLSTATE code of 00000.
>
> If I run the following code:-
>
> ----------------------------------------------
> <?php
>
> require_once '../actions/DataBaseFunctions.php';
>
> $pgconn = conn_db();
> $my_query = "SELECT * FROM rhubarb";
> if (!pg_connection_busy($pgconn)) {
> $my_result = pg_send_query($pgconn, $my_query);
> pg_set_error_verbosity($pgconn, PGSQL_ERRORS_VERBOSE);
> $res1 = pg_get_result($pgconn);
> echo pg_result_error($res1) . PHP_EOL;
> } else {
> echo 'Connection Busy' . PHP_EOL;
> }
>
> ?>
>
> --------------------------------------------------------------
>
> it returns the following:-
>
>
> ERROR:  42P01: relation "rhubarb" does not exist
> LINE 1: SELECT * FROM rhubarb
>                      ^
> LOCATION:  parserOpenTable, parse_relation.c:986
>
>
> SQLSTATE 42P01 is the error "undefined_table".
>
>
> Note that you have to use pg_send_query to take advantage of
> pg_get_result, etc.
>
>
> HTH,
>
> Rob
>

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

From

"Steve Petrie, P.Eng."

Date:

12 October 2015, 10:35:45

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@aspetrie.net

----- Original Message -----

From: "Adrian Klaver" <adrian.klaver@aklaver.com>

To: "Steve Petrie, P.Eng." <apetrie@aspetrie.net>; <pgsql-general@postgresql.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.klaver@aklaver.com

Attachment

pgsql-general - BEGIN; & pg_transaction_status.zip

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

From

"Daniel Verite"

Date:

12 October 2015, 12:22:36

    Steve Petrie, P.Eng. wrote:

> And yes, I am using pg_send_query(...) to execute the BEGIN; command, so as
> to get a result resource returned. (Please see my forthcoming emailed
> response to Adrian Klaver, wherein I provide the PHP source code that Adrian
> requests.)

After successfully calling pg_send_query($cnx, $query),
pg_transaction_status($cnx) will return PGSQL_TRANSACTION_ACTIVE
until the results have been collected by looping over pg_get_result($cnx).

This is for any query, not specifically $query="BEGIN".

Example:

<?php
 if (pg_send_query($dbcnx, "BEGIN")) {
  do {
   $res=pg_get_result($dbcnx);
   // error processing here
 } while ($res);
  echo pg_transaction_status($dbcnx);
?>

This will output 2, which corresponds to PGSQL_TRANSACTION_INTRANS
as expected.

OTOH if commenting the call to pg_get_result($dbcnx), then
it ouputs 1 (PGSQL_TRANSACTION_ACTIVE) as you mentioned.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

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

From

"Steve Petrie, P.Eng."

Date:

12 October 2015, 12:53:05

Hello Daniel,

Thanks for your response.

Ahhh -- so after a successful query, the PHP program must keep executing
pg_get_result($cnx) until a NULL result is obtained !!

And ONLY THEN does transaction status transition from
PGSQL_TRANSACTION_ACTIVE to PGSQL_TRANSACTION_INTRANS.

OK -- makes sense to me -- I will change the code accordingly.

* * *
* * *

BTW -- this PostgreSQL newbie (bye bye mysql) is getting a nice warm fuzzy
feeling, about PostgreSQL and its amazingly helpful community :)

Best,

Steve

* * *

Steve Petrie, P.Eng.

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

----- Original Message -----
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Steve Petrie, P.Eng." <apetrie@aspetrie.net>
Cc: "rob stone" <floriparob@gmail.com>; <pgsql-general@postgresql.org>
Sent: Monday, October 12, 2015 5:22 AM
Subject: Re: [GENERAL] *** QUESTION *** After successful 'BEGIN;' command --
why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?

Steve Petrie, P.Eng. wrote:

> And yes, I am using pg_send_query(...) to execute the BEGIN; command, so
> as
> to get a result resource returned. (Please see my forthcoming emailed
> response to Adrian Klaver, wherein I provide the PHP source code that
> Adrian
> requests.)

After successfully calling pg_send_query($cnx, $query),
pg_transaction_status($cnx) will return PGSQL_TRANSACTION_ACTIVE
until the results have been collected by looping over pg_get_result($cnx).

This is for any query, not specifically $query="BEGIN".

Example:

<?php
 if (pg_send_query($dbcnx, "BEGIN")) {
  do {
   $res=pg_get_result($dbcnx);
   // error processing here
 } while ($res);
  echo pg_transaction_status($dbcnx);
?>

This will output 2, which corresponds to PGSQL_TRANSACTION_INTRANS
as expected.

OTOH if commenting the call to pg_get_result($dbcnx), then
it ouputs 1 (PGSQL_TRANSACTION_ACTIVE) as you mentioned.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

From

Gavin Flower

Date:

14 October 2015, 22:33:44

On 12/10/15 22:52, Steve Petrie, P.Eng. wrote:
[...]
>
> BTW -- this PostgreSQL newbie (bye bye mysql) is getting a nice warm
> fuzzy feeling, about PostgreSQL and its amazingly helpful community :)
[...]

I can attempt to remedy your 'nice warm fuzzy feeling'!  :-)

More seriously:
(1) why did you consider PostgreSQL?
(2) what made you change?
(3) for you, in your situation:  how is the ease of use & functionality
of PostgreSQL compared to MySQL?

Probably best to start a new thread in pgsql-advocacy for your answers -
I'm sure many people would be interested in what you have to say!

Cheers,
Gavin

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

Attachment

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