Stored procedure with inserts to two related tables failing on foreign key constraint

Question

I have a stored procedure which attempts to write to two tables, related via a foreign key constraint.

Slightly simplified table definitions:

CREATE TABLE station_event
(
    station_code   VARCHAR(3)   NOT NULL,
    user_id        INTEGER      NOT NULL,
    event_dtm      TIMESTAMPTZ  NOT NULL DEFAULT now(),

    CONSTRAINT station_event_pk
        PRIMARY KEY (station_code, user_id, event_dtm)
);

CREATE TABLE location_station_event
(
    station_code   VARCHAR(3)      NOT NULL,
    user_id        INTEGER         NOT NULL,
    event_dtm      TIMESTAMPTZ(0)  NOT NULL DEFAULT now(),
    location_code  VARCHAR(8)      NOT NULL,
    location_no    INTEGER         NOT NULL,

    CONSTRAINT location_station_event_pk
        PRIMARY KEY (station_code, user_id, event_dtm),
        
    CONSTRAINT location_station_event_station_event_fk
        FOREIGN KEY (station_code, user_id, event_dtm)
        REFERENCES station_event (station_code, user_id, event_dtm)
);

Slightly simplified stored procedure definition:

CREATE FUNCTION location_station_apply (
    p_site_code         VARCHAR,
    p_location_no       INTEGER,
    p_station_code      VARCHAR
    )
RETURNS VOID AS $$
BEGIN
    INSERT INTO station_event (
                station_code,
                user_id
        )
        VALUES (
                p_station_code,
                user_id()
        );

    INSERT INTO location_station_event (
                station_code,
                user_id,
                location_no
        )
        VALUES (
                p_station_code,
                user_id(),
                p_location_no
        );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

The error I receive is:

ERROR: insert or update on table "location_station_event" violates foreign key constraint "location_station_event_station_event_fk" DETAIL: Key (station_code, user_id, event_dtm)=(CE, 1, 2024-05-24 10:21:56+01) is not present in table "station_event".

The function is unchanged between DEV and PROD environments, although DEV is running PostgreSQL v14 and PROD is still running v11. It is failing, as above, on DEV but running successfully on PROD.

Is there something I am missing which might be PostgreSQL version specific? Maybe there's a new config parameter (introduced after v11)?

Answer 1

Made minor changers in your table definitions, and function. Find below code:

Table Definition:

CREATE TABLE station_event (
station_code   VARCHAR(3)   NOT NULL,
user_id        INTEGER      NOT NULL,
event_dtm      TIMESTAMPTZ  NOT NULL DEFAULT now(),

CONSTRAINT station_event_pk
    PRIMARY KEY (station_code, user_id, event_dtm) );

CREATE TABLE location_station_event
(
    station_code   VARCHAR(3)      NOT NULL,
    user_id        INTEGER         NOT NULL,
    event_dtm      TIMESTAMPTZ  NOT NULL,
    location_code  VARCHAR(8)      NOT NULL,
    location_no    INTEGER         NOT NULL,

    CONSTRAINT location_station_event_pk
        PRIMARY KEY (station_code, user_id, event_dtm),
        
    CONSTRAINT location_station_event_station_event_fk
        FOREIGN KEY (station_code, user_id, event_dtm)
        REFERENCES station_event (station_code, user_id, event_dtm)
        
);

Made event_dtm consistent in both tables, but changed its default value in location_station_event table As this was causing to round up your micro-seconds in location_station_event and values were not matching, and was violating foreign key constraint.

Secondly made a minor change in your function to make it fool proof, added new variable which gets event_dtm value on inserting in station_event and insert that exact value in location_station_event

CREATE OR REPLACE FUNCTION location_station_apply (
    p_site_code         VARCHAR,
    p_location_no       INTEGER,
    p_station_code      VARCHAR
)
RETURNS VOID AS
$$
DECLARE
    v_event_dtm TIMESTAMPTZ;
BEGIN
    -- Insert into station_event and capture event_dtm
    INSERT INTO station_event (
        station_code,
        user_id
    )
    VALUES (
        p_station_code,
        user_id()
    ) RETURNING event_dtm INTO v_event_dtm;

    -- Insert into location_station_event
    INSERT INTO location_station_event (
        station_code,
        user_id,
        event_dtm,
        location_code,
        location_no
    )
    VALUES (
        p_station_code,
        user_id(),
        v_event_dtm,
        p_site_code,
        p_location_no
    );

END;
$$
LANGUAGE plpgsql SECURITY DEFINER;