Server Crash

Hi All,

We have recently deployed couple of new plperl SPROC in our postgres
production server and after that server start throwing error. Currently
we are using postgres 9.1 and planning to upgrade with PG 10.

But we are not sure if that will cause the same error in PG 10 as well.
We really need help.

Error Message:

2018-06-21 13:43:29 EDT [22212]: [4-1] user=cmsuser,db=forte
,host=10.10.1.3,port=10.10.1.3(56601),id=5b2bdea5.56c4,line=4DETAIL:
The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.

2018-06-21 13:43:29 EDT [3673]: [5-1] user=cmsuser,db=forte
,host=10.10.1.3,port=10.10.1.3(55649),id=5b2bd568.e59,line=5DETAIL:
The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.

Server Details:
Linux - Red Hat Enterprise Linux Server release 6.3 (Santiago)
Postgres - Postgres 9.1.13

below are the sample SPROC code:

CREATE OR REPLACE FUNCTION getauthcode(
claim character varying,
oflag character varying,
pacode character varying,
dispos character varying)
RETURNS text AS
$BODY$
use strict;
use warnings;
use SOAP::Lite;
use JSON;

try
{
my $host = `hostname`;
my $rv = spi_exec_query("select * from
getsprocurl('getauthcode','".$host."')");
my $url = $rv->{rows}[0]->{ret_url};
elog(NOTICE, 'Host Name ' . $host . ' URL '. $url );

my $soap = SOAP::Lite->new();
my $service = $soap->service($url);
my %params = ("claim" => $_[0], "oflag" => $_[1], "pacode" =>
$_[2], "dispos" => $_[3]);
my $response = $service->getAuthCode(%params);
my $json_array = decode_json($response);

for my $report ( $json_array) {
my $status = $report->{status}, '\n';
if ($status eq 'success')
{
return $report->{PriorApprovalCode};
}
else{
return $report->{message};
}
}
}
catch Exception with
{
my $ex = shift;
return 'SOAPFAULT: ' . date('H:i:s') . ' ' . exception($ex);
}

$BODY$
LANGUAGE plperlu VOLATILE STRICT
COST 100;

Appreciate for your help in advance.

Regards,

Anjul TYAGI

ü Go Green

On 27/06/18 10:51, Anjul Tyagi wrote:

Hi All,

can you please suggest on the issue we are facing?
 
 
 

Regards,

Anjul *TYAGI*

* *

*ü** **Go Green*

------ Original Message ------
From: "Anjul Tyagi" <anjul@ibosstech-us.com <mailto:anjul@ibosstech-us.com>>
To: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org <mailto:pgsql-bugs@lists.postgresql.org>>
Sent: 26-06-2018 18:17:26
Subject: Server Crash

Hi All,

We have recently deployed couple of new plperl SPROC in our postgres production server and after that server start throwing error. Currently we are using postgres 9.1 and planning to upgrade with PG 10. 

But we are not sure if that will cause the same error in PG 10 as well. We really need help.

Error Message:

2018-06-21 13:43:29 EDT [22212]: [4-1] user=cmsuser,db=forte ,host=10.10.1.3,port=10.10.1.3(56601),id=5b2bdea5.56c4,line=4DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

 

2018-06-21 13:43:29 EDT [3673]: [5-1] user=cmsuser,db=forte ,host=10.10.1.3,port=10.10.1.3(55649),id=5b2bd568.e59,line=5DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

Server Details: 
Linux - Red Hat Enterprise Linux Server release 6.3 (Santiago)
Postgres -  Postgres 9.1.13

below are the sample SPROC code:

CREATE OR REPLACE FUNCTION getauthcode(
    claim character varying,
    oflag character varying,
    pacode character varying,
    dispos character varying)
  RETURNS text AS
$BODY$
use strict;
use warnings;
use SOAP::Lite;
use JSON;

try
{
    my $host = `hostname`;
    my $rv = spi_exec_query("select * from getsprocurl('getauthcode','".$host."')");
    my $url = $rv->{rows}[0]->{ret_url};
    elog(NOTICE, 'Host Name  ' . $host . ' URL '. $url );
   
    my $soap = SOAP::Lite->new();
    my $service = $soap->service($url);
    my %params =  ("claim" => $_[0], "oflag" => $_[1], "pacode" => $_[2], "dispos" => $_[3]);
    my $response = $service->getAuthCode(%params);
    my $json_array = decode_json($response);
   
    for my $report ( $json_array) {
    my $status =  $report->{status}, '\n';
        if ($status eq 'success')
        {
            return $report->{PriorApprovalCode};
        }
        else{
            return $report->{message};
        }
    }   
}
catch Exception with
{
    my $ex = shift;
    return 'SOAPFAULT: ' . date('H:i:s') . ' ' . exception($ex);
}
 
$BODY$
  LANGUAGE plperlu VOLATILE STRICT
  COST 100;

Appreciate for your help in advance.
 

Regards,

Anjul *TYAGI*

* *

*ü** **Go Green*

Hi Anjul,

please avoid cross posting over multiple mailing lists.

Also asking again does not help, and usually is counterproductive:
members see a reply and they might put efforts on helping somebody who
did not get a reply yet.

Please take good note of it.

About your problem:

I would suggest you to upgrade to a newer version, since Postgres 9.1
is too old and does not get updates any longer.

Besides that, not being a perl expert i cannot help with your
procedure. I think anyway the problem might be somewhere else. I
suspect that your server crashes and what you read there is only a
consequence, not the cause.

Could you post any relevant log entry in /var/log/messages and maybe
anything else appearing in the postgres logfile?

What kind of machine are you running on? can we have more specs?

How is your server configured?

regards,

fabio pardi

On 27/06/18 10:51, Anjul Tyagi wrote:

Hi All,

can you please suggest on the issue we are facing?

Regards,

Anjul TYAGI

ü Go Green

------ Original Message ------
From: "Anjul Tyagi" <anjul@ibosstech-us.com>
To: "pgsql-bugs@lists.postgresql.org"
<mailto:pgsql-bugs@lists.postgresql.org>
<pgsql-bugs@lists.postgresql.org>
Sent: 26-06-2018 18:17:26
Subject: Server Crash

Hi All,

We have recently deployed couple of new plperl SPROC in our postgres
production server and after that server start throwing error.
Currently we are using postgres 9.1 and planning to upgrade with PG
10.

But we are not sure if that will cause the same error in PG 10 as
well. We really need help.

Error Message:

2018-06-21 13:43:29 EDT [22212]: [4-1] user=cmsuser,db=forte
,host=10.10.1.3,port=10.10.1.3(56601),id=5b2bdea5.56c4,line=4DETAIL:
The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.

2018-06-21 13:43:29 EDT [3673]: [5-1] user=cmsuser,db=forte
,host=10.10.1.3,port=10.10.1.3(55649),id=5b2bd568.e59,line=5DETAIL:
The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.

Server Details:
Linux - Red Hat Enterprise Linux Server release 6.3 (Santiago)
Postgres - Postgres 9.1.13

below are the sample SPROC code:

CREATE OR REPLACE FUNCTION getauthcode(
claim character varying,
oflag character varying,
pacode character varying,
dispos character varying)
RETURNS text AS
$BODY$
use strict;
use warnings;
use SOAP::Lite;
use JSON;

try
{
my $host = `hostname`;
my $rv = spi_exec_query("select * from
getsprocurl('getauthcode','".$host."')");
my $url = $rv->{rows}[0]->{ret_url};
elog(NOTICE, 'Host Name ' . $host . ' URL '. $url );

my $soap = SOAP::Lite->new();
my $service = $soap->service($url);
my %params = ("claim" => $_[0], "oflag" => $_[1], "pacode" =>
$_[2], "dispos" => $_[3]);
my $response = $service->getAuthCode(%params);
my $json_array = decode_json($response);

for my $report ( $json_array) {
my $status = $report->{status}, '\n';
if ($status eq 'success')
{
return $report->{PriorApprovalCode};
}
else{
return $report->{message};
}
}
}
catch Exception with
{
my $ex = shift;
return 'SOAPFAULT: ' . date('H:i:s') . ' ' . exception($ex);
}

$BODY$
LANGUAGE plperlu VOLATILE STRICT
COST 100;

Appreciate for your help in advance.

Regards,

Anjul TYAGI

ü Go Green

Hello

2018-06-21 13:43:29 EDT [22212]: [4-1] user=cmsuser,db=forte
,host=10.10.1.3,port=10.10.1.3(56601),id=5b2bdea5.56c4,line=4DETAIL:
The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.

This is just consequence of error. Similar message is reported by every
client connect process. Real error was before all of these messages.

Postgres - Postgres 9.1.13

You are using unsupported major version and even very old minor. Last
released 9.1 was 9.1.24 (Release Date: 2016-10-27). You missed bug
fixes for 2.5 years.
According versioning policy [1] bug fixing ceases for EOL versions.

I suggest upgrade to one of supported version. Postgresql 10 is good
choice.
If error still occurs on supported version - we need exact error
message from failed backend.

regards, Sergei

[1] https://www.postgresql.org/support/versioning/

On 27/06/18 12:40, Anjul Tyagi wrote:

Hi Fabio,

I understand your point, however i am in tremendous pressure to provide the solution.

I have attached the log file for review.

We have Red Hat machine and Postgres 9.1.13 version.

Appreciate for your response and help.
 
 
 

Regards,

Anjul *TYAGI*

* *

*ü** **Go Green*

------ Original Message ------
From: "Fabio Pardi" <f.pardi@portavita.eu <mailto:f.pardi@portavita.eu>>
To: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Sent: 27-06-2018 14:43:30
Subject: Re: Server Crash

Hi Anjul,

please avoid cross posting over multiple mailing lists.

Also asking again does not help, and usually is counterproductive: members see a reply and they might put efforts on helping somebody who did not get a reply yet.

Please take good note of it.

About your problem:

I would suggest you to upgrade to a newer version, since Postgres 9.1 is too old and does not get updates any longer.

Besides that, not being aperl expert i cannot help with your procedure. I think anyway the problem might be somewhere else. I suspectthat your server crashes and what you read there is only a consequence, not the cause.

Could you post any relevant logentry in /var/log/messages and maybe anything else appearing in the postgres logfile?

What kind of machine are you running on? can we have more specs?

How is your server configured?

regards,

fabio pardi

On 27/06/18 10:51, Anjul Tyagi wrote:

Hi All,

can you please suggest on the issue we are facing?
 
 
 

Regards,

Anjul *TYAGI*

* *

*ü** **Go Green*

------ Original Message ------
From: "Anjul Tyagi" <anjul@ibosstech-us.com <mailto:anjul@ibosstech-us.com>>
To: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org <mailto:pgsql-bugs@lists.postgresql.org>>
Sent: 26-06-2018 18:17:26
Subject: Server Crash

Hi All,

We have recently deployed couple of new plperl SPROC in our postgres production server and after that server start throwing error. Currently we are using postgres 9.1 and planning to upgrade with PG 10. 

But we are not sure if that will cause the same error in PG 10 as well. We really need help.

Error Message:

2018-06-21 13:43:29 EDT [22212]: [4-1] user=cmsuser,db=forte ,host=10.10.1.3,port=10.10.1.3(56601),id=5b2bdea5.56c4,line=4DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

 

2018-06-21 13:43:29 EDT [3673]: [5-1] user=cmsuser,db=forte ,host=10.10.1.3,port=10.10.1.3(55649),id=5b2bd568.e59,line=5DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

Server Details: 
Linux - Red Hat Enterprise Linux Server release 6.3 (Santiago)
Postgres -  Postgres 9.1.13

below are the sample SPROC code:

CREATE OR REPLACE FUNCTION getauthcode(
    claim character varying,
    oflag character varying,
    pacode character varying,
    dispos character varying)
  RETURNS text AS
$BODY$
use strict;
use warnings;
use SOAP::Lite;
use JSON;

try
{
    my $host = `hostname`;
    my $rv = spi_exec_query("select * from getsprocurl('getauthcode','".$host."')");
    my $url = $rv->{rows}[0]->{ret_url};
    elog(NOTICE, 'Host Name  ' . $host . ' URL '. $url );
   
    my $soap = SOAP::Lite->new();
    my $service = $soap->service($url);
    my %params =  ("claim" => $_[0], "oflag" => $_[1], "pacode" => $_[2], "dispos" => $_[3]);
    my $response = $service->getAuthCode(%params);
    my $json_array = decode_json($response);
   
    for my $report ( $json_array) {
    my $status =  $report->{status}, '\n';
        if ($status eq 'success')
        {
            return $report->{PriorApprovalCode};
        }
        else{
            return $report->{message};
        }
    }   
}
catch Exception with
{
    my $ex = shift;
    return 'SOAPFAULT: ' . date('H:i:s') . ' ' . exception($ex);
}
 
$BODY$
  LANGUAGE plperlu VOLATILE STRICT
  COST 100;

Appreciate for your help in advance.
 

Regards,

Anjul *TYAGI*

* *

*ü** **Go Green*

On 27/06/18 11:58, Anjul Tyagi wrote:

Hi Sergei,

thanks for your response. We have already plan to upgrade the same in
PG 10, but i am scared if we faced the same issue with upgraded
version as well.

Regards,

Anjul *TYAGI*

**

*ü****Go Green*

------ Original Message ------
From: "Sergei Kornilov" <sk@zsrv.org <mailto:sk@zsrv.org>>
To: "Anjul Tyagi" <anjul@ibosstech-us.com
<mailto:anjul@ibosstech-us.com>>; "pgsql-bugs@lists.postgresql.org"
<pgsql-bugs@lists.postgresql.org <mailto:pgsql-bugs@lists.postgresql.org>>
Sent: 27-06-2018 15:01:15
Subject: Re: Server Crash

Hello

2018-06-21 13:43:29 EDT [22212]: [4-1] user=cmsuser,db=forte
,host=10.10.1.3,port=10.10.1.3(56601),id=5b2bdea5.56c4,line=4DETAIL:
The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.

This is just consequence of error. Similar message is reported by
every client connect process. Real error was before all of these
messages.

Postgres - Postgres 9.1.13

You are using unsupported major version and even very old minor. Last
released 9.1 was 9.1.24 (Release Date: 2016-10-27). You missed bug
fixes for 2.5 years.
According versioning policy [1] bug fixing ceases for EOL versions.
I suggest upgrade to one of supported version. Postgresql 10 is good
choice.
If error still occurs on supported version - we need exact error
message from failed backend.
regards, Sergei
[1] https://www.postgresql.org/support/versioning/