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

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
>