Sample Header Ad - 728x90

Postgres trigger with after and on row appears to not run after writing to row

0 votes
1 answer
43 views
I have the following two tables:
CREATE TABLE qanda.group_questions (
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	category_id uuid NULL,
	business_model_id int4 NULL,
	industry_id int4 NULL,
	business_structure_id int4 NULL,
	CONSTRAINT group_questions_pkey PRIMARY KEY (id),
	CONSTRAINT group_questions_business_model_id_fkey FOREIGN KEY (business_model_id) REFERENCES public.business_models(id) ON DELETE CASCADE,
	CONSTRAINT group_questions_business_structure_id_fkey FOREIGN KEY (business_structure_id) REFERENCES public.business_structure(id),
	CONSTRAINT group_questions_category_id_fkey FOREIGN KEY (category_id) REFERENCES public.category(id) ON DELETE CASCADE,
	CONSTRAINT group_questions_industry_id_fkey FOREIGN KEY (industry_id) REFERENCES public.industry(id) ON DELETE CASCADE
);

CREATE TABLE qanda.plain_text_questions (
	id uuid NOT NULL,
	question text NOT NULL,
	user_prompt text NULL,
	CONSTRAINT plain_text_questions_pkey PRIMARY KEY (id),
	CONSTRAINT plain_text_questions_id_fkey FOREIGN KEY (id) REFERENCES qanda.group_questions(id)
);
When a row is inserted into the group_questions table the following trigger fires:
create trigger on_group_question_inserted after
insert
    on
    qanda.group_questions for each row execute function notify_web_service_on_insert();
The trigger calls a function that calls a web service, which then should insert data in the plain_text_questions table. However it fails with this error:
"Error inserting data:",
        {
          "code": "23503",
          "details": "Key (id)=(50ec4620-a27b-429b-b05f-4fe786f116a9) is not present in table \"group_questions\".",
          "hint": null,
          "message": "insert or update on table \"plain_text_questions\" violates foreign key constraint \"plain_text_questions_id_fkey\""
        }
The trigger function is:
CREATE OR REPLACE FUNCTION public.notify_web_service_on_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    details JSON;
    payload JSON;
    request extensions.http_request;
BEGIN
    -- Fetch details using the get_details_by_group_question_id function
    SELECT qanda.get_details_by_group_question_id(NEW.id) INTO details; -- this is selecting from qanda.group_questions demonstrating that the data is in the table

    -- Construct the JSON payload by adding the group_question_id
    payload := jsonb_set(details::jsonb, '{group_question_id}', to_jsonb(NEW.id::text));

    -- Construct the HTTP request structure
    request := (
        'POST',                                       -- Method
        'https://this_is_the_webservice/ ',                     -- URI
        ARRAY[extensions.http_header('Authorization', 'Bearer hello_world'),  -- Headers
              extensions.http_header('Content-Type', 'application/json')],
        'application/json',                           -- Content Type
        payload::text                                 -- Content
    )::extensions.http_request;

    -- Make the HTTP POST request
    PERFORM content FROM extensions.http(request);

    -- Return the NEW record to allow the insert operation to complete
    RETURN NEW;
END;
$function$
;
If I change the trigger to create trigger on_group_question_inserted before it fails, as it should. Based on my understanding before means before the row has been written to the table. But, after should mean it has been written. And due to the successful use of SELECT qanda.get_details_by_group_question_id(NEW.id) INTO details; that is correct. So, I'm left confused.
Asked by Magick (111 rep)
Jul 16, 2024, 08:54 PM
Last activity: Jul 17, 2024, 03:25 AM