Re: Postgres Crashing

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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com