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

Question

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.

score 0 · Answer 1 · answered Jul 17 '24 at 03:25

The web service called by the trigger is using a different database session. While the trigger runs, your inserting transaction is still active, so its effects are not visible to other concurrent transactions.

If you really have to do it like that, you cannot use a trigger. You'd have to first commit the INSERT, then call the web service from your application.

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

1 Answers1