Thread: 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_BirdStreets Club_Server$$,7,$$Table$$,1,$$exclusive$$,$$806$$,$$BirdStreets 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$$,$$$$,$$KeysLos 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
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','BirdStreets
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> > > Create Script.txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/ESPFd2CzRRpAt7mzbaiJt1IB3k1z9C67vZAC-RU4njjhLA> > Create Script(check_items).txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/Ef9QlV-ghRZHgCWZaczbSgsBuQGz8ISDxtvv7tQnCi8z6g> > Cretae Script(employees).txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/ET7M1kBp8udBsxQrJH03XGwBUl0Sw9SCjgiIpImCoswKdA> > > 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