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

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