PostgreSQL Question posted in
The official documentation can be found here.

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

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)?

Tags:

2

Answers


  1. 0

    Your primary key of the first table is PRIMARY KEY (station_code, user_id, event_dtm) and you refer it from the second table via

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

    You never specify it, so they both refer to the default values. In order to fix the issue, make sure that you pass the timestamp to the procedure, so it will enforce the same value for both inserts instead of allowing them to diverge (which is the reason for your problem):

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

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

    The reason for your problem is that the result of now() is different when you execute the second insert from what it was when you executed the first insert.

    Furthermore, please make sure that event_dtm has the same type for both. Finally, please make sure that user_id() is returning the same value at both inserts. Because if that differs, then you will need to pass a user_id too and use that for both inserts.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search