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