Can't fix Pgsql Insert Command Issue.

On 2024-09-23 06:39 +0200, Mark Kostevych wrote:

We are facing a critical issue while inserting our records to
Postgresql Server. It was working well before but after 09/20, it
failed, and we can't find what to fix.

1) What has changed on 09/20? Any triggers defined on table "checks"?

2) What error message do you get if the statement fails?

3) Or is the statement just hanging, judging by the attached screenshot?
The table or rows may be locked. The wiki should help you with that:
https://wiki.postgresql.org/wiki/Lock_Monitoring

Here is a sample SQL Command for your reference.
Insert INTO checks (id, name, number, status, sub_total, tax_total, total, mandatory_tip_amount, open_time, close_time, employee_name, employee_role_name, employee_id, employee, guest_count, type, type_id, taxed_type, table_name, location, zone, autograt_tax, trading_day_id, trading_day, updated_at, non_revenue_total, revenue_total, outstanding_balance, comp_total, voidcomp_reason_text, voidcomp_type, voidcomp_value) VALUES ($$d2a187a5-9c61-425c-a956-ca730437cdbd$$,$token$806$token$,1108,$$Closed$$,316.80,18.96,335.76,0.00,$$2024-09-20T12:32:10-07:00$$,$$2024-09-20T13:33:58-07:00$$,$$Kendall Rucks$$,$$Server$$,$$d014cf80-5ebb-45e7-a919-fc38a3b46be4$$,$$398007_Kendall_Bird Streets Club_Server$$,7,$$Table$$,1,$$exclusive$$,$$806$$,$$Bird Streets Club$$,$$Lounge$$,0.00,$$912b2778-822f-4586-ae3e-5970379867d2$$,$$2024-09-20$$,$$2024-09-20T20:33:58.000Z$$,0,316.8,0.00, 0, $$$$, $$$$, NULL), ($$fbe112aa-e59f-4345-9b66-86a693a795d7$$,$token$Test$token$,2919,$$Closed$$,0.00,0.00,0.00,0.00,$$2024-09-20T13:02:34-07:00$$,$$2024-09-20T13:03:04-07:00$$,$$Ian Brown$$,$$Admin$$,$$bb71f90d-61c4-46d1-bb9c-116d9444ec30$$,$$manager$$,1,$$Tab$$,2,$$inclusive$$,$$$$,$$Poppy$$,$$$$,0.00,$$75f5b22a-e3c4-4c54-9c27-3faec5595024$$,$$2024-09-20$$,$$2024-09-20T20:03:06.000Z$$,0,0,0.00, 0, $$$$, $$$$, NULL), ($$10aef268-3973-4e74-b92e-761572bd7fc7$$,$token$L’Agence shoot$token$,7302,$$Closed$$,23373.00,0.00,23373.00,0.00,$$2024-09-20T13:14:51-07:00$$,$$2024-09-20T13:15:39-07:00$$,$$Sara Martinez$$,$$Manager$$,$$645cb249-d9a9-4965-9b57-23d4ede686ee$$,$$manager$$,1,$$Tab$$,2,$$exclusive$$,$$$$,$$Keys Los Angeles$$,$$$$,0.00,$$a7073607-3880-475f-91f6-46f23b7af08f$$,$$2024-09-20$$,$$2024-09-20T20:15:41.000Z$$,2337300,0,0.00, 0, $$$$, $$$$, NULL) ON CONFLICT(id) DO UPDATE SET sub_total = EXCLUDED.sub_total, tax_total = EXCLUDED.tax_total, employee=EXCLUDED.employee,total = EXCLUDED.total, open_time = EXCLUDED.open_time, close_time = EXCLUDED.close_time, updated_at = EXCLUDED.updated_at,voidcomp_reason_text = EXCLUDED.voidcomp_reason_text, voidcomp_type = EXCLUDED.voidcomp_type, voidcomp_value = EXCLUDED.voidcomp_value,comp_total=EXCLUDED.comp_total, non_revenue_total = EXCLUDED.non_revenue_total, revenue_total = EXCLUDED.revenue_total;

--
Erik

1) We didn't change anything on 09/20
2) We can't get any response while running pgsql command. It still shows running, no return any result.
3) I checked your link and attached result csv file here. Please check and let me know what you can find.
________________________________
From: Erik Wienhold <ewie@ewie.name>
Sent: Monday, September 23, 2024 8:33 AM
To: Mark Kostevych <mkostevych@hwoodgroup.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Jacob Shure <jacob@shurehw.com>; Michael Green <Michael@hwoodgroup.com>
Subject: Re: Can't fix Pgsql Insert Command Issue.

On 2024-09-23 06:39 +0200, Mark Kostevych wrote:

We are facing a critical issue while inserting our records to
Postgresql Server. It was working well before but after 09/20, it
failed, and we can't find what to fix.

1) What has changed on 09/20? Any triggers defined on table "checks"?

2) What error message do you get if the statement fails?

3) Or is the statement just hanging, judging by the attached screenshot?
The table or rows may be locked. The wiki should help you with that:
https://wiki.postgresql.org/wiki/Lock_Monitoring

Here is a sample SQL Command for your reference.
Insert INTO checks (id, name, number, status, sub_total, tax_total, total, mandatory_tip_amount, open_time, close_time, employee_name, employee_role_name, employee_id, employee, guest_count, type, type_id, taxed_type, table_name, location, zone, autograt_tax, trading_day_id, trading_day, updated_at, non_revenue_total, revenue_total, outstanding_balance, comp_total, voidcomp_reason_text, voidcomp_type, voidcomp_value) VALUES ($$d2a187a5-9c61-425c-a956-ca730437cdbd$$,$token$806$token$,1108,$$Closed$$,316.80,18.96,335.76,0.00,$$2024-09-20T12:32:10-07:00$$,$$2024-09-20T13:33:58-07:00$$,$$Kendall Rucks$$,$$Server$$,$$d014cf80-5ebb-45e7-a919-fc38a3b46be4$$,$$398007_Kendall_Bird Streets Club_Server$$,7,$$Table$$,1,$$exclusive$$,$$806$$,$$Bird Streets Club$$,$$Lounge$$,0.00,$$912b2778-822f-4586-ae3e-5970379867d2$$,$$2024-09-20$$,$$2024-09-20T20:33:58.000Z$$,0,316.8,0.00, 0, $$$$, $$$$, NULL), ($$fbe112aa-e59f-4345-9b66-86a693a795d7$$,$token$Test$token$,2919,$$Closed$$,0.00,0.00,0.00,0.00,$$2024-09-20T13:02:34-07:00$$,$$2024-09-20T13:03:04-07:00$$,$$Ian Brown$$,$$Admin$$,$$bb71f90d-61c4-46d1-bb9c-116d9444ec30$$,$$manager$$,1,$$Tab$$,2,$$inclusive$$,$$$$,$$Poppy$$,$$$$,0.00,$$75f5b22a-e3c4-4c54-9c27-3faec5595024$$,$$2024-09-20$$,$$2024-09-20T20:03:06.000Z$$,0,0,0.00, 0, $$$$, $$$$, NULL), ($$10aef268-3973-4e74-b92e-761572bd7fc7$$,$token$L’Agence shoot$token$,7302,$$Closed$$,23373.00,0.00,23373.00,0.00,$$2024-09-20T13:14:51-07:00$$,$$2024-09-20T13:15:39-07:00$$,$$Sara Martinez$$,$$Manager$$,$$645cb249-d9a9-4965-9b57-23d4ede686ee$$,$$manager$$,1,$$Tab$$,2,$$exclusive$$,$$$$,$$Keys Los Angeles$$,$$$$,0.00,$$a7073607-3880-475f-91f6-46f23b7af08f$$,$$2024-09-20$$,$$2024-09-20T20:15:41.000Z$$,2337300,0,0.00, 0, $$$$, $$$$, NULL) ON CONFLICT(id) DO UPDATE SET sub_total = EXCLUDED.sub_total, tax_total = EXCLUDED.tax_total, employee=EXCLUDED.employee,total = https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fEXCLUDED.total&amp;c=E,1,XUG2452gq_58ENqaRfMEFGdNhJ9Y_z2FI7HF6gCdqSbXwyJXq_2YLDglzgVaLmgtrXYSWrbQ-TlM3qyX3q-_eHy6z3G8A75wRGB4thE2&amp;typo=1, open_time = EXCLUDED.open_time, close_time = EXCLUDED.close_time, updated_at = EXCLUDED.updated_at,voidcomp_reason_text = EXCLUDED.voidcomp_reason_text, voidcomp_type = EXCLUDED.voidcomp_type, voidcomp_value = EXCLUDED.voidcomp_value,comp_total=EXCLUDED.comp_total, non_revenue_total = EXCLUDED.non_revenue_total, revenue_total = EXCLUDED.revenue_total;

--
Erik

On 2024-09-23 18:53 +0200, Mark Kostevych wrote:

Could you check please why our insert command doesn't work?

INSERT INTO public.checks(id, name, "number", status, sub_total, tax_total, total, mandatory_tip_amount, open_time, close_time, employee_name, employee_role_name, employee_id, employee, guest_count, "type", type_id, taxed_type, table_name, location, zone, autograt_tax, trading_day_id, trading_day, updated_at, non_revenue_total, revenue_total, outstanding_balance, comp_total, voidcomp_reason_text, voidcomp_type, voidcomp_value)
VALUES ('1cfa4fc4-de12-4adc-a5f1-25e27203aeaf','21',5807,'Closed',410.00,35.16,445.16,0.00,'2024-09-22T20:14:42-07:00','2024-09-22T21:45:40-07:00','Kristian Maxwell-McGeever','Server','da37c15c-2f2d-43f1-9959-070f231dd79a','666692_Kristian_Bird Streets Club_Server',4,'Table',1,'exclusive','21','Bird Streets Club','Restaurant',0.00,'d1abd697-04fa-40d6-8a9e-7c376dc2952d','2024-09-22','2024-09-23T12:19:41.000Z',0,410,0.00, 343, '', '', NULL);

I tried very simple insert command, but it still doesn't work. What
should I do?

The attached CSV only shows held locks, nothing that caught my eye. Can
you please check pg_blocking_pids() to make sure that there are really
no blocking sessions? https://wiki.postgresql.org/wiki/Lock_Monitoring
says that the listed queries only return row-level locks. (Not sure if
that is correct because the docs on pg_locks says that it normally does
not return row-level locks.)

First, get the PID of the session where you run your INSERT:

SELECT pg_backend_pid();
INSERT INTO public.checks ...

In a second session, get the info on all backends that block the first
session:

SELECT * FROM pg_stat_activity WHERE pid = ANY (pg_blocking_pids(:pid));

Please also provide the CREATE TABLE statements (or the output of psql's
\d) for table "checks" and all tables that are referenced by "checks".

Also, what is the exact Postgres version?

Does the INSERT also hang when executed via psql instead of pgAdmin?
Just to rule out that pgAdmin is the issue.

--
Erik

I tried to get the PID of the session but can't get the response. Same issue.

[https://res.public.onecdn.static.microsoft/assets/mail/file-icon/png/photo_16x16.png]Screenshot_2.png&lt;https://hwoodgroup-my.sharepoint.com/:i:/p/mkostevych/EZev3xZoyMRLkJf7RZTA5XwB8H1BcgtH09K9bjmcUx_YvA&gt;

​[https://res.cdn.office.net/assets/mail/file-icon/png/txt_16x16.png]Create Script.txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/ESPFd2CzRRpAt7mzbaiJt1IB3k1z9C67vZAC-RU4njjhLA&gt;
[https://res.cdn.office.net/assets/mail/file-icon/png/txt_16x16.png]Create Script(check_items).txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/Ef9QlV-ghRZHgCWZaczbSgsBuQGz8ISDxtvv7tQnCi8z6g&gt;
[https://res.cdn.office.net/assets/mail/file-icon/png/txt_16x16.png]Cretae Script(employees).txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/ET7M1kBp8udBsxQrJH03XGwBUl0Sw9SCjgiIpImCoswKdA&gt;

As you asked, I attached Create script for three tables(checks, check_items, employees). I think we don't have any issue on employees table.
Hope to get the solution asap. Thank you.

Mark
________________________________
From: Erik Wienhold <ewie@ewie.name>
Sent: Monday, September 23, 2024 7:50 PM
To: Mark Kostevych <mkostevych@hwoodgroup.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Jacob Shure <jacob@shurehw.com>; Michael Green <Michael@hwoodgroup.com>
Subject: Re: Can't fix Pgsql Insert Command Issue.

On 2024-09-23 18:53 +0200, Mark Kostevych wrote:

Could you check please why our insert command doesn't work?

INSERT INTO public.checks(id, name, "number", status, sub_total, tax_total, total, mandatory_tip_amount, open_time, close_time, employee_name, employee_role_name, employee_id, employee, guest_count, "type", type_id, taxed_type, table_name, location, zone, autograt_tax, trading_day_id, trading_day, updated_at, non_revenue_total, revenue_total, outstanding_balance, comp_total, voidcomp_reason_text, voidcomp_type, voidcomp_value)
VALUES ('1cfa4fc4-de12-4adc-a5f1-25e27203aeaf','21',5807,'Closed',410.00,35.16,445.16,0.00,'2024-09-22T20:14:42-07:00','2024-09-22T21:45:40-07:00','Kristian Maxwell-McGeever','Server','da37c15c-2f2d-43f1-9959-070f231dd79a','666692_Kristian_Bird Streets Club_Server',4,'Table',1,'exclusive','21','Bird Streets Club','Restaurant',0.00,'d1abd697-04fa-40d6-8a9e-7c376dc2952d','2024-09-22','2024-09-23T12:19:41.000Z',0,410,0.00, 343, '', '', NULL);

I tried very simple insert command, but it still doesn't work. What
should I do?

The attached CSV only shows held locks, nothing that caught my eye. Can
you please check pg_blocking_pids() to make sure that there are really
no blocking sessions? https://wiki.postgresql.org/wiki/Lock_Monitoring
says that the listed queries only return row-level locks. (Not sure if
that is correct because the docs on pg_locks says that it normally does
not return row-level locks.)

First, get the PID of the session where you run your INSERT:

SELECT pg_backend_pid();
INSERT INTO public.checks ...

In a second session, get the info on all backends that block the first
session:

SELECT * FROM pg_stat_activity WHERE pid = ANY (pg_blocking_pids(:pid));

Please also provide the CREATE TABLE statements (or the output of psql's
\d) for table "checks" and all tables that are referenced by "checks".

Also, what is the exact Postgres version?

Does the INSERT also hang when executed via psql instead of pgAdmin?
Just to rule out that pgAdmin is the issue.

--
Erik

On 2024-09-24 03:42 +0200, Mark Kostevych wrote:

I tried to get the PID of the session but can't get the response. Same issue.

You need to run SELECT pg_backend_pid() separately before the INSERT.

Screenshot_2.png<https://hwoodgroup-my.sharepoint.com/:i:/p/mkostevych/EZev3xZoyMRLkJf7RZTA5XwB8H1BcgtH09K9bjmcUx_YvA&gt;

Create Script.txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/ESPFd2CzRRpAt7mzbaiJt1IB3k1z9C67vZAC-RU4njjhLA&gt;
Create Script(check_items).txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/Ef9QlV-ghRZHgCWZaczbSgsBuQGz8ISDxtvv7tQnCi8z6g&gt;
Cretae Script(employees).txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/ET7M1kBp8udBsxQrJH03XGwBUl0Sw9SCjgiIpImCoswKdA&gt;

As you asked, I attached Create script for three tables(checks,
check_items, employees). I think we don't have any issue on employees
table.

Please attach scripts or quote them inline. External links are not
ideal.

What do these trigger functions on table "checks" do?:

* public.update_universal_location_id()
* public.update_date_parts_checks()

For the archive:

CREATE TABLE IF NOT EXISTS public.checks
(
id character varying COLLATE pg_catalog."default" NOT NULL,
name character varying COLLATE pg_catalog."default",
"number" bigint,
sub_total numeric(8,2),
tax_total numeric(8,2),
total numeric(8,2),
mandatory_tip_amount numeric(8,2),
open_time timestamp with time zone,
close_time timestamp with time zone,
employee_name character varying COLLATE pg_catalog."default",
employee_role_name character varying COLLATE pg_catalog."default",
employee_id character varying COLLATE pg_catalog."default",
employee character varying COLLATE pg_catalog."default",
guest_count smallint,
type character varying COLLATE pg_catalog."default",
type_id smallint,
taxed_type character varying COLLATE pg_catalog."default",
table_name character varying COLLATE pg_catalog."default",
location character varying COLLATE pg_catalog."default",
zone character varying COLLATE pg_catalog."default",
autograt_tax numeric(8,2),
trading_day_id character varying COLLATE pg_catalog."default",
trading_day date,
updated_at timestamp with time zone,
non_revenue_total bigint,
outstanding_balance numeric(8,2),
status character varying COLLATE pg_catalog."default",
revenue_total numeric(8,2),
comp_total numeric(8,2) DEFAULT 0,
visible boolean DEFAULT 'true',
void_total numeric(8,2) DEFAULT 0,
reason_code character varying COLLATE pg_catalog."default",
voidcomp_reason_text character varying COLLATE pg_catalog."default",
voidcomp_type character varying COLLATE pg_catalog."default",
voidcomp_value numeric,
parent_category character varying COLLATE pg_catalog."default",
category_name character varying COLLATE pg_catalog."default",
month integer,
day integer,
year integer,
universal_location_id integer,
CONSTRAINT checks_pkey PRIMARY KEY (id),
CONSTRAINT checks_employee_fkey FOREIGN KEY (employee)
REFERENCES public.employees (airtable_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.checks
OWNER to postgres;

GRANT SELECT ON TABLE public.checks TO hwood_read_only;

GRANT ALL ON TABLE public.checks TO postgres;

CREATE INDEX IF NOT EXISTS checks_location
ON public.checks USING btree
(location COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_checks_location
ON public.checks USING btree
(location COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_checks_trading_day
ON public.checks USING btree
(trading_day ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_checks_trading_day_location
ON public.checks USING btree
(trading_day ASC NULLS LAST, location COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;

CREATE TRIGGER set_universal_location_id_checks
AFTER INSERT OR UPDATE
ON public.checks
FOR EACH ROW
EXECUTE FUNCTION public.update_universal_location_id();

CREATE TRIGGER update_date_part_checks_trigger
BEFORE INSERT OR UPDATE
ON public.checks
FOR EACH ROW
EXECUTE FUNCTION public.update_date_parts_checks();

CREATE TABLE IF NOT EXISTS public.check_items
(
id character varying COLLATE pg_catalog."default" NOT NULL,
check_id character varying COLLATE pg_catalog."default" NOT NULL,
name character varying COLLATE pg_catalog."default",
date timestamp with time zone,
item_id character varying COLLATE pg_catalog."default" NOT NULL,
quantity bigint,
price numeric(8,2),
pre_tax_price numeric(8,2),
regular_price numeric(8,2),
cost numeric(8,2),
tax numeric(8,2),
comp_total numeric(8,2),
comp_tax numeric(8,2),
parent_category character varying COLLATE pg_catalog."default",
category character varying COLLATE pg_catalog."default",
CONSTRAINT check_items_pkey PRIMARY KEY (id),
CONSTRAINT check_item_fkey FOREIGN KEY (check_id)
REFERENCES public.checks (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT item_fkey FOREIGN KEY (item_id)
REFERENCES public.items (item_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.check_items
OWNER to postgres;

GRANT SELECT ON TABLE public.check_items TO hwood_read_only;

GRANT ALL ON TABLE public.check_items TO postgres;

CREATE TABLE IF NOT EXISTS public.employees
(
pos_id integer NOT NULL,
first character varying COLLATE pg_catalog."default",
last character varying COLLATE pg_catalog."default",
email character varying COLLATE pg_catalog."default",
mobile character varying COLLATE pg_catalog."default",
location character varying COLLATE pg_catalog."default",
paycom_code character varying COLLATE pg_catalog."default",
r365_code integer,
role character varying COLLATE pg_catalog."default",
reg_rate numeric(4,2),
employee_id character varying COLLATE pg_catalog."default",
airtable_id character varying COLLATE pg_catalog."default" NOT NULL,
role_id numeric,
"paycorIdProfileId" character varying COLLATE pg_catalog."default",
active boolean,
role_name character varying COLLATE pg_catalog."default",
CONSTRAINT employees_pkey PRIMARY KEY (airtable_id)
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.employees
OWNER to postgres;

GRANT SELECT ON TABLE public.employees TO hwood_read_only;

GRANT ALL ON TABLE public.employees TO postgres;

CREATE INDEX IF NOT EXISTS idx_employees_airtable_id
ON public.employees USING btree
(airtable_id COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_employees_email
ON public.employees USING btree
(email COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;

--
Erik