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

On 08/27/2015 04:49 AM, Christopher BROWN wrote:
> 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

I think you have a bigger problem. The failing row has 7 values where
you are sending 4 values. Given the 2 defaults for time that still only
adds up to 6. Also I not sure how you can get a NULL for access_mode as
the table you are selecting from store_ldap_profile_defaults, has
access_mode VARCHAR(1) NOT NULL CHECK (access_mode IN ('R', 'W')),

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com