Re: Postgres Crashing
От | Adrian Klaver |
---|---|
Тема | Re: Postgres Crashing |
Дата | |
Msg-id | dec3d06b-af74-a254-a9a6-b5e53cb71de8@aklaver.com обсуждение исходный текст |
Ответ на | Postgres Crashing (Doug Roberts <h205881@gmail.com>) |
Ответы |
Re: Postgres Crashing
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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
В списке pgsql-general по дате отправления: