NO : Postgresql re-establishing connection

Andy Hilton andyh at totallybrilliant.com
Sat Dec 4 15:11:07 EST 2010 

David

looks like a treasure trove - just one question - how are you getting the error code here / where are you testing/finding it ?

Good advice on the transaction rollback part......

Andy

On Dec 3, 2010, at 12:30 PM, David McKeone wrote:

> This is a very tricky issue to get around if you are doing anything with transactions.  As far as I know the $connectstatus and $status is not reliable for determining if a query is going to work or not.  You just have to send it and hope for the best.
> 
> The best we've been able to do is to send a query, if it fails we do a reconnect (but only on certain $sqlstate error conditions) then try to send it again.   The problem is that if you happen to lose network connectivity in the middle of a transaction then PostgreSQL will roll back your transaction since your session died.  We haven't found a great way to handle this yet, since in most cases it means you have to stop everything go back to the beginning of whatever activity you were trying to accomplish and then start again after reconnecting.  All we really do is keep track of whether we are in a transaction or not and give an error if we lose connectivity in the middle.
> 
> Additionally we also have a timer based modal window that notifies the user of the situation, because sometimes users do dumb things like turn off their internet or walk out of wireless range and we want them to go back to a good spot and then click continue so the program doesn't have to exit.
> 
> Here is a list of all the errors that PostgreSQL will raise and what we do for them:  (cTryAgain is our flag for if we attempt a reconnect)
> 
> Do cSqlStateErrorList.$define(cErrorCode,cErrorDescription,cTryAgain)
> Do cSqlStateErrorList.$add('00000','Successful completion',kFalse)
> Do cSqlStateErrorList.$add('01000','Warning',kFalse)
> Do cSqlStateErrorList.$add('01003','Null value eliminated in set function',kFalse)
> Do cSqlStateErrorList.$add('01004','String data right truncation',kFalse)
> Do cSqlStateErrorList.$add('01006','Privilege not revoked',kFalse)
> Do cSqlStateErrorList.$add('01007','Privilege not granted',kFalse)
> Do cSqlStateErrorList.$add('01008','Implicit zero bit padding',kFalse)
> Do cSqlStateErrorList.$add('0100C','Dynamic result sets returned',kFalse)
> Do cSqlStateErrorList.$add('01P01','Deprecated feature',kFalse)
> Do cSqlStateErrorList.$add('02000','No data',kFalse)
> Do cSqlStateErrorList.$add('02001','No additional dynamic result sets returned',kFalse)
> Do cSqlStateErrorList.$add('03000','Sql statement not yet complete',kFalse)
> Do cSqlStateErrorList.$add('08000','Connection exception',kTrue)
> Do cSqlStateErrorList.$add('08001','Sqlclient unable to establish sqlconnection',kTrue)
> Do cSqlStateErrorList.$add('08003','Connection does not exist',kTrue)
> Do cSqlStateErrorList.$add('08004','Sqlserver rejected establishment of sqlconnection',kTrue)
> Do cSqlStateErrorList.$add('08006','Connection failure',kTrue)
> Do cSqlStateErrorList.$add('08007','Transaction resolution unknown',kFalse)
> Do cSqlStateErrorList.$add('08P01','Protocol violation',kFalse)
> Do cSqlStateErrorList.$add('09000','Triggered action exception',kFalse)
> Do cSqlStateErrorList.$add('0A000','Feature not supported',kFalse)
> Do cSqlStateErrorList.$add('0B000','Invalid transaction initiation',kFalse)
> Do cSqlStateErrorList.$add('0F000','Locator exception',kFalse)
> Do cSqlStateErrorList.$add('0F001','Invalid locator specification',kFalse)
> Do cSqlStateErrorList.$add('0L000','Invalid grantor',kFalse)
> Do cSqlStateErrorList.$add('0LP01','Invalid grant operation',kFalse)
> Do cSqlStateErrorList.$add('0P000','Invalid role specification',kFalse)
> Do cSqlStateErrorList.$add('21000','Cardinality violation',kFalse)
> Do cSqlStateErrorList.$add('22000','Data exception',kFalse)
> Do cSqlStateErrorList.$add('22001','String data right truncation',kFalse)
> Do cSqlStateErrorList.$add('22002','Null value no indicator parameter',kFalse)
> Do cSqlStateErrorList.$add('22003','Numeric value out of range',kFalse)
> Do cSqlStateErrorList.$add('22004','Null value not allowed',kFalse)
> Do cSqlStateErrorList.$add('22005','Error in assignment',kFalse)
> Do cSqlStateErrorList.$add('22007','Invalid datetime format',kFalse)
> Do cSqlStateErrorList.$add('22008','Datetime field overflow',kFalse)
> Do cSqlStateErrorList.$add('22009','Invalid time zone displacement value',kFalse)
> Do cSqlStateErrorList.$add('2200B','Escape character conflict',kFalse)
> Do cSqlStateErrorList.$add('2200C','Invalid use of escape character',kFalse)
> Do cSqlStateErrorList.$add('2200D','Invalid escape octet',kFalse)
> Do cSqlStateErrorList.$add('2200F','Zero length character string',kFalse)
> Do cSqlStateErrorList.$add('2200G','Most specific type mismatch',kFalse)
> Do cSqlStateErrorList.$add('22010','Invalid indicator parameter value',kFalse)
> Do cSqlStateErrorList.$add('22011','Substring error',kFalse)
> Do cSqlStateErrorList.$add('22012','Division by zero',kFalse)
> Do cSqlStateErrorList.$add('22015','Interval field overflow',kFalse)
> Do cSqlStateErrorList.$add('22018','Invalid character value for cast',kFalse)
> Do cSqlStateErrorList.$add('22019','Invalid escape character',kFalse)
> Do cSqlStateErrorList.$add('2201B','Invalid regular expression',kFalse)
> Do cSqlStateErrorList.$add('2201E','Invalid argument for logarithm',kFalse)
> Do cSqlStateErrorList.$add('2201F','Invalid argument for power function',kFalse)
> Do cSqlStateErrorList.$add('2201G','Invalid argument for width bucket function',kFalse)
> Do cSqlStateErrorList.$add('22020','Invalid limit value',kFalse)
> Do cSqlStateErrorList.$add('22021','Character not in repertoire',kFalse)
> Do cSqlStateErrorList.$add('22022','Indicator overflow',kFalse)
> Do cSqlStateErrorList.$add('22023','Invalid parameter value',kFalse)
> Do cSqlStateErrorList.$add('22024','Unterminated c string',kFalse)
> Do cSqlStateErrorList.$add('22025','Invalid escape sequence',kFalse)
> Do cSqlStateErrorList.$add('22026','String data length mismatch',kFalse)
> Do cSqlStateErrorList.$add('22027','Trim error',kFalse)
> Do cSqlStateErrorList.$add('2202E','Array subscript error',kFalse)
> Do cSqlStateErrorList.$add('22P01','Floating point exception',kFalse)
> Do cSqlStateErrorList.$add('22P02','Invalid text representation',kFalse)
> Do cSqlStateErrorList.$add('22P03','Invalid binary representation',kFalse)
> Do cSqlStateErrorList.$add('22P04','Bad copy file format',kFalse)
> Do cSqlStateErrorList.$add('22P05','Untranslatable character',kFalse)
> Do cSqlStateErrorList.$add('22P06','Nonstandard use of escape character',kFalse)
> Do cSqlStateErrorList.$add('23000','Integrity constraint violation',kFalse)
> Do cSqlStateErrorList.$add('23001','Restrict violation',kFalse)
> Do cSqlStateErrorList.$add('23502','Not null violation',kFalse)
> Do cSqlStateErrorList.$add('23503','Foreign key violation',kFalse)
> Do cSqlStateErrorList.$add('23505','Unique violation',kFalse)
> Do cSqlStateErrorList.$add('23514','Check violation',kFalse)
> Do cSqlStateErrorList.$add('24000','Invalid cursor state',kFalse)
> Do cSqlStateErrorList.$add('25000','Invalid transaction state',kFalse)
> Do cSqlStateErrorList.$add('25001','Active sql transaction',kFalse)
> Do cSqlStateErrorList.$add('25002','Branch transaction already active',kFalse)
> Do cSqlStateErrorList.$add('25003','Inappropriate access mode for branch transaction',kFalse)
> Do cSqlStateErrorList.$add('25004','Inappropriate isolation level for branch transaction',kFalse)
> Do cSqlStateErrorList.$add('25005','No active sql transaction for branch transaction',kFalse)
> Do cSqlStateErrorList.$add('25006','Read only sql transaction',kFalse)
> Do cSqlStateErrorList.$add('25007','Schema and data statement mixing not supported',kFalse)
> Do cSqlStateErrorList.$add('25008','Held cursor requires same isolation level',kFalse)
> Do cSqlStateErrorList.$add('25P01','No active sql transaction',kFalse)
> Do cSqlStateErrorList.$add('25P02','In failed sql transaction',kFalse)
> Do cSqlStateErrorList.$add('26000','Invalid sql statement name',kFalse)
> Do cSqlStateErrorList.$add('27000','Triggered data change violation',kFalse)
> Do cSqlStateErrorList.$add('28000','Invalid authorization specification',kFalse)
> Do cSqlStateErrorList.$add('2B000','Dependent privilege descriptors still exist',kFalse)
> Do cSqlStateErrorList.$add('2BP01','Dependent objects still exist',kFalse)
> Do cSqlStateErrorList.$add('2D000','Invalid transaction termination',kFalse)
> Do cSqlStateErrorList.$add('2F000','Sql routine exception',kFalse)
> Do cSqlStateErrorList.$add('2F002','Modifying sql data not permitted',kFalse)
> Do cSqlStateErrorList.$add('2F003','Prohibited sql statement attempted',kFalse)
> Do cSqlStateErrorList.$add('2F004','Reading sql data not permitted',kFalse)
> Do cSqlStateErrorList.$add('2F005','Function executed no return statement',kFalse)
> Do cSqlStateErrorList.$add('34000','Invalid cursor name',kFalse)
> Do cSqlStateErrorList.$add('38000','External routine exception',kFalse)
> Do cSqlStateErrorList.$add('38001','Containing sql not permitted',kFalse)
> Do cSqlStateErrorList.$add('38002','Modifying sql data not permitted',kFalse)
> Do cSqlStateErrorList.$add('38003','Prohibited sql statement attempted',kFalse)
> Do cSqlStateErrorList.$add('38004','Reading sql data not permitted',kFalse)
> Do cSqlStateErrorList.$add('39000','External routine invocation exception',kFalse)
> Do cSqlStateErrorList.$add('39001','Invalid sqlstate returned',kFalse)
> Do cSqlStateErrorList.$add('39004','Null value not allowed',kFalse)
> Do cSqlStateErrorList.$add('39P01','Trigger protocol violated',kFalse)
> Do cSqlStateErrorList.$add('39P02','Srf protocol violated',kFalse)
> Do cSqlStateErrorList.$add('3B000','Savepoint exception',kFalse)
> Do cSqlStateErrorList.$add('3B001','Invalid savepoint specification',kFalse)
> Do cSqlStateErrorList.$add('3D000','Invalid catalog name',kFalse)
> Do cSqlStateErrorList.$add('3F000','Invalid schema name',kFalse)
> Do cSqlStateErrorList.$add('40000','Transaction rollback',kFalse)
> Do cSqlStateErrorList.$add('40001','Serialization failure',kFalse)
> Do cSqlStateErrorList.$add('40002','Transaction integrity constraint violation',kFalse)
> Do cSqlStateErrorList.$add('40003','Statement completion unknown',kFalse)
> Do cSqlStateErrorList.$add('40P01','Deadlock detected',kTrue)     ;; V80000 DM
> Do cSqlStateErrorList.$add('42000','Syntax error or access rule violation',kFalse)
> Do cSqlStateErrorList.$add('42501','Insufficient privilege',kFalse)
> Do cSqlStateErrorList.$add('42601','Syntax error',kFalse)
> Do cSqlStateErrorList.$add('42602','Invalid name',kFalse)
> Do cSqlStateErrorList.$add('42611','Invalid column definition',kFalse)
> Do cSqlStateErrorList.$add('42622','Name too long',kFalse)
> Do cSqlStateErrorList.$add('42701','Duplicate column',kFalse)
> Do cSqlStateErrorList.$add('42702','Ambiguous column',kFalse)
> Do cSqlStateErrorList.$add('42703','Undefined column',kFalse)
> Do cSqlStateErrorList.$add('42704','Undefined object',kFalse)
> Do cSqlStateErrorList.$add('42710','Duplicate object',kFalse)
> Do cSqlStateErrorList.$add('42712','Duplicate alias',kFalse)
> Do cSqlStateErrorList.$add('42723','Duplicate function',kFalse)
> Do cSqlStateErrorList.$add('42725','Ambiguous function',kFalse)
> Do cSqlStateErrorList.$add('42803','Grouping error',kFalse)
> Do cSqlStateErrorList.$add('42804','Datatype mismatch',kFalse)
> Do cSqlStateErrorList.$add('42809','Wrong object type',kFalse)
> Do cSqlStateErrorList.$add('42830','Invalid foreign key',kFalse)
> Do cSqlStateErrorList.$add('42846','Cannot coerce',kFalse)
> Do cSqlStateErrorList.$add('42883','Undefined function',kFalse)
> Do cSqlStateErrorList.$add('42939','Reserved name',kFalse)
> Do cSqlStateErrorList.$add('42P01','Undefined table',kFalse)
> Do cSqlStateErrorList.$add('42P02','Undefined parameter',kFalse)
> Do cSqlStateErrorList.$add('42P03','Duplicate cursor',kFalse)
> Do cSqlStateErrorList.$add('42P04','Duplicate database',kFalse)
> Do cSqlStateErrorList.$add('42P05','Duplicate prepared statement',kFalse)
> Do cSqlStateErrorList.$add('42P06','Duplicate schema',kFalse)
> Do cSqlStateErrorList.$add('42P07','Duplicate table',kFalse)
> Do cSqlStateErrorList.$add('42P08','Ambiguous parameter',kFalse)
> Do cSqlStateErrorList.$add('42P09','Ambiguous alias',kFalse)
> Do cSqlStateErrorList.$add('42P10','Invalid column reference',kFalse)
> Do cSqlStateErrorList.$add('42P11','Invalid cursor definition',kFalse)
> Do cSqlStateErrorList.$add('42P12','Invalid database definition',kFalse)
> Do cSqlStateErrorList.$add('42P13','Invalid function definition',kFalse)
> Do cSqlStateErrorList.$add('42P14','Invalid prepared statement definition',kFalse)
> Do cSqlStateErrorList.$add('42P15','Invalid schema definition',kFalse)
> Do cSqlStateErrorList.$add('42P16','Invalid table definition',kFalse)
> Do cSqlStateErrorList.$add('42P17','Invalid object definition',kFalse)
> Do cSqlStateErrorList.$add('42P18','Indeterminate datatype',kFalse)
> Do cSqlStateErrorList.$add('44000','With check option violation',kFalse)
> Do cSqlStateErrorList.$add('53000','Insufficient resources',kFalse)
> Do cSqlStateErrorList.$add('53100','Disk full - The server has indicated that there is insufficient disk space left',kFalse)
> Do cSqlStateErrorList.$add('53200','Out of memory',kTrue)    
> Do cSqlStateErrorList.$add('53300','Too many connections',kFalse)
> Do cSqlStateErrorList.$add('54000','Program limit exceeded',kFalse)
> Do cSqlStateErrorList.$add('54001','Statement too complex',kFalse)
> Do cSqlStateErrorList.$add('54011','Too many columns',kFalse)
> Do cSqlStateErrorList.$add('54023','Too many arguments',kFalse)
> Do cSqlStateErrorList.$add('55000','Object not in prerequisite state',kFalse)
> Do cSqlStateErrorList.$add('55006','Object in use',kTrue)    
> Do cSqlStateErrorList.$add('55P02','Cant change runtime param',kFalse)
> Do cSqlStateErrorList.$add('55P03','Lock not available',kTrue)   
> Do cSqlStateErrorList.$add('57000','Operator intervention',kTrue)  
> Do cSqlStateErrorList.$add('57014','Query canceled',kFalse)
> Do cSqlStateErrorList.$add('57P01','Admin shutdown',kTrue)   
> Do cSqlStateErrorList.$add('57P02','Crash shutdown',kFalse)
> Do cSqlStateErrorList.$add('57P03','Cannot connect now',kFalse)
> Do cSqlStateErrorList.$add('58030','Io error',kFalse)
> Do cSqlStateErrorList.$add('58P01','Undefined file',kFalse)
> Do cSqlStateErrorList.$add('58P02','Duplicate file',kFalse)
> Do cSqlStateErrorList.$add('F0000','Config file error',kFalse)
> Do cSqlStateErrorList.$add('F0001','Lock file exists',kFalse)
> Do cSqlStateErrorList.$add('P0000','Plpgsql error',kFalse)
> Do cSqlStateErrorList.$add('P0001','Raise exception',kFalse)
> Do cSqlStateErrorList.$add('XX000','Internal error',kFalse)
> Do cSqlStateErrorList.$add('XX001','Data corrupted',kFalse)
> Do cSqlStateErrorList.$add('XX002','Index corrupted',kFalse)
> 
> Hope that helps,
> __________________________________
> David McKeone
> Arts Management Systems Ltd.
> mailto:david at artsman.com
> http://www.artsman.com
> Phone: (403) 536-1203 Fax: (403) 536-1210
> 
> 
> 
> 
> On 2010-12-03, at 5:12 PM, Christine Penner wrote:
> 
>> I don't know about the $connectstatus but $state will not change if the network connection is lost. I have done quite a bit of playing around with this and noticed that unless I actually logoff $state always says its logged on. I have been looking for a way to test this myself. I'll try the $connectstatus and see if that works.
>> 
>> Christine
>> 
>> At 06:06 AM 03/12/2010, you wrote:
>>> The Postgresql dam has the $connectstatus method which returns the status of the connection.
>>> 
>>> The dam also has a $reset method to reconnect to the server.
>>> 
>>> In my super table class $construct I experimented with the following although have not proven it works perfectly yet. I cannot remember if the test $connectstatus hangs if connection is lost which would defeat the purpose.
>>> 
>>> I test the connection and then do a $reset
>>> Do oSession.$connectstatus() Returns isConnected
>>> If isConnected<>kPgSqlConnectionOK&oSession.$reset.$cando
>>> Do oSession.$reset
>>> End If
>>> 
>>> There is also a session property $state so one could test that as well
>>> 
>>> If oSession.$state=kSessionStateLoggedOn
>>> do something such as $reset
>>> end
>>> 
>>> Ken
>>> 
>>> 
>>> On Dec-3-10, at 7:37 AM, Andy Hilton wrote:
>>> 
>>>> Postgres Gurus
>>>> 
>>>> Is there an easy way (or any way !) within Omnis to re-establish a connection to Postgres in the event of a momentary lapse of network ?
>>>> 
>>>> In MySQL I can use their ping command which does a nice job of re-establishing a connection if it was broken, and am looking for something similar in Postgres ?
>>>> 
>>>> Andy_____________________________________________________________
>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>> 
>>> Ken Dimson
>>> kdimson at dimson.com
>>> 
>>> 
>>> 
>>> _____________________________________________________________
>>> Manage your list subscriptions at http://lists.omnis-dev.com
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com

More information about the omnisdev-en mailing list