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