Re: Postgres Crashing - Mailing list pgsql-general

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