Re: Postgres Crashing

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Mon, 3 Feb 2020 14:34:46 -0800

On 2/3/20 2:18 PM, Doug Roberts wrote:
Please reply to list also.
Ccing list.
Adrian,

Here is what the reset recirc function is doing.

CREATE OR REPLACE FUNCTION containers_reset_recirc
(
     in_uid INTEGER
)
RETURNS INTEGER
AS $BODY$
     DECLARE regex VARCHAR(50);
BEGIN
     SELECT concat(',*', in_uid, '=\d+,*') INTO regex;

     LOCK TABLE containers IN SHARE ROW EXCLUSIVE MODE;

     UPDATE containers
         SET recirculation_count =
             case
                 when substring(recirculation_count, regex) like ',%,' then
                     regexp_replace(recirculation_count, regex, ',')
                 else
                     regexp_replace(recirculation_count, regex, '')
                 end;

     RETURN in_uid;
END;

Containers add/update is basically updating a specific container using 
the values that were passed to the function.

So how did containers_reset_recirc() come to clash with 
containers_add_update()?

UPDATE containers
     SET type_uid = COALESCE(declared_type_uid, type_uid),
         carton_type_uid = COALESCE(declared_carton_type_uid, 
carton_type_uid),
         status_uid = COALESCE(declared_status_uid, status_uid),
         order_uid = COALESCE(in_order_uid, order_uid),
         wave_uid = COALESCE(in_wave_uid, wave_uid),
         length = COALESCE(in_length, carton_length, length),
         width = COALESCE(in_width, carton_width, width),
         height = COALESCE(in_height, carton_height, height),
         weight = COALESCE(in_weight, weight),
         weight_minimum = COALESCE(in_weight_minimum, weight_minimum),
         weight_maximum = COALESCE(in_weight_maximum, weight_maximum),
         weight_expected = COALESCE(in_weight_expected, weight_expected),
         first_seen_decision_point_id = 
COALESCE(first_seen_decision_point_id, in_last_seen_decision_point_id),
         first_seen_datetime = COALESCE(first_seen_datetime, 
last_seen_date_time),
         last_seen_decision_point_id = 
COALESCE(in_last_seen_decision_point_id, last_seen_decision_point_id),
         last_seen_datetime = COALESCE(last_seen_date_time, 
last_seen_datetime),
         recirculation_count = COALESCE(in_recirculation_count, 
recirculation_count),
         project_flags = COALESCE(in_project_flags, project_flags),
         passed_weight_check = COALESCE(in_passed_weight_check, 
passed_weight_check)
     WHERE uid = in_uid

Thanks,

Doug

On Mon, Feb 3, 2020 at 4:49 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx 
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:

    On 2/3/20 1:43 PM, Doug Roberts wrote:
     > Hello,
     >
     > I'm having an issue where a process in Postgres is crashing and
    cause
     > the server to go into recovery mode.
     >
     > I'm getting the following errors in the log.
     >
     > 2020-02-03 14:12:57.473 EST [11992] [0]WARNING:  57P02: terminating
     > connection because of crash of another server process
     > 2020-02-03 14:12:57.473 EST [11992] [0]DETAIL:  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.
     > 2020-02-03 14:12:57.473 EST [11992] [0]HINT:  In a moment you
    should be
     > able to reconnect to the database and repeat your command.
     > 2020-02-03 14:12:57.473 EST [11992] [0]CONTEXT:  while locking tuple
     > (4101,2) in relation "containers"
     > SQL statement "UPDATE containers
     >             SET type_uid = COALESCE(declared_type_uid, type_uid),
     >                 carton_type_uid = COALESCE(declared_carton_type_uid,
     > carton_type_uid),
     >                 status_uid = COALESCE(declared_status_uid,
    status_uid),
     >                 order_uid = COALESCE(in_order_uid, order_uid),
     >                 wave_uid = COALESCE(in_wave_uid, wave_uid),
     >                 length = COALESCE(in_length, carton_length, length),
     >                 width = COALESCE(in_width, carton_width, width),
     >                 height = COALESCE(in_height, carton_height, height),
     >                 weight = COALESCE(in_weight, weight),
     >                 weight_minimum = COALESCE(in_weight_minimum,
     > weight_minimum),
     >                 weight_maximum = COALESCE(in_weight_maximum,
     > weight_maximum),
     >                 weight_expected = COALESCE(in_weight_expected,
     > weight_expected),
     >                 first_seen_decision_point_id =
     > COALESCE(first_seen_decision_point_id,
    in_last_seen_decision_point_id),
     >                 first_seen_datetime = COALESCE(first_seen_datetime,
     > last_seen_date_time),
     >                 last_seen_decision_point_id =
     > COALESCE(in_last_seen_decision_point_id,
    last_seen_decision_point_id),
     >                 last_seen_datetime = COALESCE(last_seen_date_time,
     > last_seen_datetime),
     >                 recirculation_count =
    COALESCE(in_recirculation_count,
     > recirculation_count),
     >                 project_flags = COALESCE(in_project_flags,
    project_flags),
     >                 passed_weight_check =
    COALESCE(in_passed_weight_check,
     > passed_weight_check)
     >             WHERE uid = in_uid"
     > PL/pgSQL function
     >
    containers_add_update(integer,integer,integer,integer,integer,integer,double

     > precision,double precision,double precision,double precision,double
     > precision,double precision,double precision,integer,timestamp
    without
     > time zone,character varying,bigint,boolean) line 60 at SQL statement

     >
     > This happened when I was using a function to remove part of a comma
     > delimited string while updating a row. The update could potentially
     > touch every row in the table. The issue above occurred when a
    different
     > update function was being executed on the same table.

    The full content of containers_add_update() would be helpful as well as
    the content of the other function. If that is not possible some idea of
    the order in which they where run as well as where the LOCK TABLE below
    was inserted?

     >
     > If I use the following lock this issue seems to be resolved.
    However,
     > I'm not sure why the above issue occurred.
     >
     > LOCK TABLE containers IN SHARE ROW EXCLUSIVE MODE;
     >
     > Does anyone have any ideas?
     >
     > Thanks,
     >
     > Doug

    -- 
    Adrian Klaver
    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx