Postgres Pro
Downloads
Home   >   mailing lists

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

From Steve Petrie, P.Eng.
Subject *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?
Date
Msg-id F5C26B5F346E442DA66B9532A0A5AC0B@Dell
Whole thread Raw
Responses Re: *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?
Re: *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?
List pgsql-general
Tree view
(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
 

pgsql-general by date:

Previous
From: Jeremy Harris
Date:
Subject: Re: Merge join vs merge semi join against primary key
Next
From: Chuck Martin
Date:
Subject: Re: Trouble setting up replication