Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT

Поиск
Список
Период
Сортировка
От Christopher BROWN
Тема Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT
Дата
Msg-id CAHL_zcPsYn5vDVK9R7Ntx3+3MnPAikA_ekxQzr814fG7o21sLA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT  ("Charles Clavadetscher" <clavadetscher@swisspug.org>)
Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список 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 по дате отправления:

Предыдущее
От: Igor Sosa Mayor
Дата:
Сообщение: Re: Problem with pl/python procedure connecting to the internet
Следующее
От: "Charles Clavadetscher"
Дата:
Сообщение: Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT