Re: Postgres Crashing - Mailing list pgsql-general

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@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> 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@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com