Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT - Mailing list pgsql-general
| From | Christopher BROWN |
|---|---|
| Subject | Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT |
| Date | |
| Msg-id | CAHL_zcPsYn5vDVK9R7Ntx3+3MnPAikA_ekxQzr814fG7o21sLA@mail.gmail.com Whole thread Raw |
| Responses |
Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT
Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT |
| List | pgsql-general |
Hello,
I'm new to this list but have been using PostgreSQL for a moment. I've encountered an error using PostgreSQL 9.4.4 which can be reproduced using the SQL below.
The trigger "init_store_ldap_profiles_trigger" fails if the function "init_store_ldap_profiles()" is written as below. If I rewrite it to use "SELECT * FROM ...", instead of "SELECT id, ref_ldap_department, ref_ldap_title, access_mode FROM ...", it works.
This is the error I get:
ERROR: null value in column "access_mode" violates not-null constraint
Detail: Failing row contains (1, 2015-08-27 13:37:24.306883, 2015-08-27 13:37:24.306883, 1, 1, 1, null).
Where: SQL statement "INSERT INTO application.store_ldap_profile (ref_store, ref_ldap_department, ref_ldap_title, access_mode) VALUES (NEW.id, r.ref_ldap_department, r.ref_ldap_title, r.access_mode)"
PL/pgSQL function init_store_ldap_profiles() line 8 at SQL statement
It seems that for some reason, the column "store_ldap_profile_defaults.access_mode" appears to be NULL when referred to using r.access_mode (r being the declared %ROWTYPE). I can modify the WHERE clause to add a dummy condition on "access_mode", and that works (as in, it doesn't solve my problem but the column value is visible to the WHERE clause).
Is this a bug or can I fix this in my SQL ?
Thanks,
Christopher
Here's the SQL :
CREATE SCHEMA application;
SET search_path TO application;
CREATE TABLE IF NOT EXISTS store (
id SERIAL PRIMARY KEY,
ctime TIMESTAMP NOT NULL DEFAULT now(),
mtime TIMESTAMP NOT NULL DEFAULT now(),
is_archived NUMERIC(1) CHECK (is_archived IN (1,0)) DEFAULT 0,
name VARCHAR(200) NOT NULL CHECK (length(name) > 0),
hrcompany VARCHAR(200) NOT NULL CHECK (length(hrcompany) > 0),
hrsite VARCHAR(200) NOT NULL CHECK (length(hrsite) > 0),
format VARCHAR(1) NOT NULL CHECK (format IN ('H', 'S')),
UNIQUE (hrcompany, hrsite)
);
CREATE INDEX ON store (mtime);
CREATE INDEX ON store (is_archived);
CREATE INDEX ON store (format);
CREATE TABLE IF NOT EXISTS ldap_department (
id SERIAL PRIMARY KEY,
ctime TIMESTAMP NOT NULL DEFAULT now(),
mtime TIMESTAMP NOT NULL DEFAULT now(),
code VARCHAR(20) NOT NULL CHECK (code ~ '[0-9]+'),
label VARCHAR(200) NOT NULL CHECK (length(label) > 0),
UNIQUE(code)
);
CREATE INDEX ON ldap_department (mtime);
CREATE TABLE IF NOT EXISTS ldap_title (
id SERIAL PRIMARY KEY,
ctime TIMESTAMP NOT NULL DEFAULT now(),
mtime TIMESTAMP NOT NULL DEFAULT now(),
code VARCHAR(20) NOT NULL CHECK (code ~ '[0-9]+'),
label VARCHAR(200) NOT NULL CHECK (length(label) > 0),
UNIQUE(code)
);
CREATE INDEX ON ldap_title (mtime);
CREATE TABLE IF NOT EXISTS store_ldap_profile_defaults (
id SERIAL PRIMARY KEY,
ref_ldap_department INTEGER NOT NULL,
ref_ldap_title INTEGER NOT NULL,
format VARCHAR(1) NOT NULL CHECK (format IN ('H', 'S')),
access_mode VARCHAR(1) NOT NULL CHECK (access_mode IN ('R', 'W')),
FOREIGN KEY (ref_ldap_department) REFERENCES ldap_department (id) ON DELETE CASCADE,
FOREIGN KEY (ref_ldap_title) REFERENCES ldap_title (id) ON DELETE CASCADE,
UNIQUE (ref_ldap_department, ref_ldap_title, format)
);
CREATE INDEX ON store_ldap_profile_defaults (format);
CREATE INDEX ON store_ldap_profile_defaults (access_mode);
CREATE TABLE IF NOT EXISTS store_ldap_profile (
id SERIAL PRIMARY KEY,
ctime TIMESTAMP NOT NULL DEFAULT now(),
mtime TIMESTAMP NOT NULL DEFAULT now(),
ref_store INTEGER NOT NULL,
ref_ldap_department INTEGER NOT NULL,
ref_ldap_title INTEGER NOT NULL,
access_mode VARCHAR(1) NOT NULL CHECK (access_mode IN ('R', 'W')),
FOREIGN KEY (ref_store) REFERENCES store (id) ON DELETE RESTRICT,
FOREIGN KEY (ref_ldap_department) REFERENCES ldap_department (id) ON DELETE CASCADE,
FOREIGN KEY (ref_ldap_title) REFERENCES ldap_title (id) ON DELETE CASCADE,
UNIQUE (ref_store, ref_ldap_department, ref_ldap_title)
);
CREATE INDEX ON store_ldap_profile (mtime);
CREATE INDEX ON store_ldap_profile (ref_store);
DROP TRIGGER IF EXISTS touch_store_ldap_profile_trigger
ON application.store_ldap_profile;
CREATE OR REPLACE FUNCTION touch_store_ldap_profile() RETURNS TRIGGER AS $$
BEGIN
UPDATE application.store SET mtime = now() WHERE id = NEW.ref_store;
RETURN NEW;
END; $$
LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER touch_store_ldap_profile_trigger
AFTER INSERT OR UPDATE ON application.store_ldap_profile
FOR EACH ROW EXECUTE PROCEDURE touch_store_ldap_profile();
DROP TRIGGER IF EXISTS init_store_ldap_profiles_trigger
ON application.store;
CREATE OR REPLACE FUNCTION init_store_ldap_profiles() RETURNS TRIGGER AS $$
DECLARE
r application.store_ldap_profile_defaults%rowtype;
BEGIN
FOR r IN
SELECT id, ref_ldap_department, ref_ldap_title, access_mode FROM application.store_ldap_profile_defaults WHERE format = NEW.format
LOOP
INSERT INTO application.store_ldap_profile (ref_store, ref_ldap_department, ref_ldap_title, access_mode) VALUES (NEW.id, r.ref_ldap_department, r.ref_ldap_title, r.access_mode);
END LOOP;
RETURN NEW;
END; $$
LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER init_store_ldap_profiles_trigger
AFTER INSERT ON application.store
FOR EACH ROW EXECUTE PROCEDURE init_store_ldap_profiles();
INSERT INTO ldap_department (code, label) VALUES
('03000', 'CAISSES');
INSERT INTO ldap_title (code, label) VALUES
('814', 'MANAGER SERV CAISSES'),
('837', 'RESPONSABLE SERVICE CAISSES');
INSERT INTO store_ldap_profile_defaults (ref_ldap_department, ref_ldap_title, format, access_mode) VALUES
((SELECT id FROM ldap_department WHERE code = '03000' LIMIT 1), (SELECT id FROM ldap_title WHERE code = '814' LIMIT 1), 'H', 'R'),
((SELECT id FROM ldap_department WHERE code = '03000' LIMIT 1), (SELECT id FROM ldap_title WHERE code = '837' LIMIT 1), 'H', 'W');
--SET search_path TO "$user",public;
pgsql-general by date: