Last active
November 29, 2018 22:46
-
-
Save dlangille/5917df6cd02f731700a729504470ad91 to your computer and use it in GitHub Desktop.
Identical table name and function name in PostgreSQL 11.1
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION element_pathname_insert() RETURNS TRIGGER AS $$ | |
BEGIN | |
RAISE WARNING 'element_pathname_insert says: %', NEW.id; | |
INSERT INTO element_pathname (element_id, pathname) | |
VALUES (NEW.id, element_pathname(NEW.id)); | |
RETURN NEW; | |
END | |
$$ LANGUAGE 'plpgsql'; | |
From the definition of the table: element | |
element_pathname_insert AFTER INSERT ON element FOR EACH ROW EXECUTE PROCEDURE element_pathname_insert() |
element_pathname() is declared as stable
The error is:
freshports.dev=# select Element_Add('/base/releng/12.0/release/tools/gce.conf', 'F');
WARNING: looping for base/releng/12.0/release/tools/gce.conf
WARNING: looping for releng/12.0/release/tools/gce.conf
WARNING: looping for 12.0/release/tools/gce.conf
WARNING: looping for release/tools/gce.conf
WARNING: looping for tools/gce.conf
WARNING: inserting tools
WARNING: element_pathname_insert says: 910902
ERROR: duplicate key value violates unique constraint "element_pathname_pathname"
DETAIL: Key (pathname)=() already exists.
CONTEXT: SQL statement "INSERT INTO element_pathname (element_id, pathname)
VALUES (NEW.id, element_pathname(NEW.id))"
PL/pgSQL function element_pathname_insert() line 4 at SQL statement
SQL statement "insert into element(id, parent_id, directory_file_flag, name, status)
values(element_id, element_parent_id, 'D', element_name, 'A')"
PL/pgSQL function element_add(text,character) line 92 at SQL statement
freshports.dev=#
The function is:
CREATE OR REPLACE FUNCTION element_pathname (integer, boolean) RETURNS text
AS $$
DECLARE
element_id ALIAS FOR $1;
leading_slash ALIAS for $2;
my_parent_id int4;
element_name text;
pathname text;
begin
RAISE WARNING ' element_pathname invoked with %', element_id;
pathname = '';
select name, parent_id
into element_name, my_parent_id
from element
where id = element_id;
IF FOUND THEN
pathname := '/' || element_name || pathname;
WHILE FOUND LOOP
select name, parent_id
into element_name, my_parent_id
from element
where id = my_parent_id;
IF FOUND THEN
IF my_parent_id IS NULL THEN
IF leading_slash THEN
pathname = '/' || element_name || pathname;
ELSE
pathname = element_name || pathname;
END IF;
EXIT;
ELSE
pathname = '/' || element_name || pathname;
END IF;
END IF;
END LOOP;
END IF;
return pathname;
END;
$$
LANGUAGE plpgsql STABLE;
The duplicate key issue is a red herring, that only occurs because the previous transaction retrieve a '' for element_pathname () and inserted it.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The problem? element_pathname(NEW.id) is returning an empty string, which is what it will do if it cannot find an entry for NEW.id