Instantly share code, notes, and snippets.

Embed
What would you like to do?
Identical table name and function name in PostgreSQL 11.1
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()
@dlangille

This comment has been minimized.

Copy link
Owner

dlangille commented Nov 29, 2018

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

@dlangille

This comment has been minimized.

Copy link
Owner

dlangille commented Nov 29, 2018

element_pathname() is declared as stable

@dlangille

This comment has been minimized.

Copy link
Owner

dlangille commented Nov 29, 2018

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=# 

@dlangille

This comment has been minimized.

Copy link
Owner

dlangille commented Nov 29, 2018

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;

@dlangille

This comment has been minimized.

Copy link
Owner

dlangille commented Nov 29, 2018

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