object_reference.sql

\echo This extension must be loaded via 'CREATE EXTENSION object_reference;'
\echo You really, REALLY do NOT want to try and load this via psql!!!
\echo It will FAIL during pg_dump! \quit

/* EXCLUDED CODE: schema-restriction check below not currently enforced
DO $$
DECLARE
  c_schema CONSTANT name := (SELECT extnamespace::regnamespace::text FROM pg_extension WHERE extname = 'cat_tools');
BEGIN
  IF c_schema IS NULL THEN
    RAISE 'extension cat_tools is not installed';
  END IF;

  IF c_schema <> 'cat_tools' THEN
    RAISE 'having the cat_tools extension installed anywhere but the "cat_tools" schema is not currently supported'
      USING DETAIL = format('current schema for cat_tools is %s', c_schema)
    ;
  END IF;
END
$$;
*/

DO $$
BEGIN
  CREATE ROLE object_reference__usage NOLOGIN;
EXCEPTION WHEN duplicate_object THEN
  NULL;
END
$$;

DO $$
BEGIN
  CREATE ROLE object_reference__dependency NOLOGIN;
EXCEPTION WHEN duplicate_object THEN
  NULL;
END
$$;

/*
 * NOTE: All pg_temp objects must be dropped at the end of the script!
 * Otherwise the eventual DROP CASCADE of pg_temp when the session ends will
 * also drop the extension! Instead of risking problems, create our own
 * "temporary" schema instead.
 */
CREATE SCHEMA __object_reference;

CREATE FUNCTION __object_reference.exec(
  sql text
) RETURNS void LANGUAGE plpgsql AS $body$
BEGIN
  RAISE DEBUG 'sql = %', sql;
  EXECUTE sql;
END
$body$;

CREATE FUNCTION __object_reference.safe_dump(
  relation regclass
  , filter text DEFAULT ''
) RETURNS void LANGUAGE plpgsql AS $body$
BEGIN
  PERFORM pg_catalog.pg_extension_config_dump(relation, filter);
EXCEPTION WHEN feature_not_supported THEN
  RAISE WARNING 'I promise you will be sorry if you try to use this as anything other than an extension!';
END
$body$;

CREATE FUNCTION __object_reference.create_function(
  function_name text
  , args text
  , options text
  , body text
  , comment text
  , grants text DEFAULT NULL
) RETURNS void LANGUAGE plpgsql AS $body$
DECLARE
  c_clean_args text := cat_tools.routine__parse_arg_types_text(args);

  create_template CONSTANT text := $template$
CREATE OR REPLACE FUNCTION %s(
%s
) RETURNS %s AS
%L
$template$
  ;

  revoke_template CONSTANT text := $template$
REVOKE ALL ON FUNCTION %s(
%s
) FROM public;
$template$
  ;

  grant_template CONSTANT text := $template$
GRANT EXECUTE ON FUNCTION %s(
%s
) TO %s;
$template$
  ;

  comment_template CONSTANT text := $template$
COMMENT ON FUNCTION %s(
%s
) IS %L;
$template$
  ;

BEGIN
  PERFORM __object_reference.exec( format(
      create_template
      , function_name
      , args
      , options -- TODO: Force search_path if options ~* 'definer'
      , body
    ) )
  ;
  PERFORM __object_reference.exec( format(
      revoke_template
      , function_name
      , c_clean_args
    ) )
  ;

  IF grants IS NOT NULL THEN
    PERFORM __object_reference.exec( format(
        grant_template
        , function_name
        , c_clean_args
        , grants
      ) )
    ;
  END IF;

  IF comment IS NOT NULL THEN
    PERFORM __object_reference.exec( format(
        comment_template
        , function_name
        , c_clean_args
        , comment
      ) )
    ;
  END IF;
END
$body$;


-- Schema already created via CREATE EXTENSION
GRANT USAGE ON SCHEMA object_reference TO object_reference__usage;
CREATE SCHEMA _object_reference;
GRANT USAGE ON SCHEMA _object_reference TO object_reference__dependency;

SELECT __object_reference.create_function(
  '_object_reference.exec'
  , 'sql text'
  , 'void LANGUAGE plpgsql'
  , $body$
BEGIN
  RAISE DEBUG 'sql = %', sql;
  EXECUTE sql;
END
$body$
  , 'Execute arbitrary SQL with logging.'
);

SELECT __object_reference.create_function(
  '_object_reference._is_own_object'
  , $args$
  classid oid
  , objid oid
$args$
  , 'boolean LANGUAGE sql STABLE'
  , $body$
SELECT
  EXISTS(
    SELECT 1
      FROM pg_catalog.pg_depend d
      WHERE d.classid = _is_own_object.classid
        AND d.objid = _is_own_object.objid
        AND d.deptype = 'e'
        AND d.refclassid = 'pg_catalog.pg_extension'::regclass
        AND d.refobjid = e.oid
  )
  /*
   * The extension's own declared schema (object_reference) is a special
   * case: CREATE EXTENSION records the EXTENSION as depending on it (a
   * plain DEPENDENCY_NORMAL row, extension -> schema), not the schema as
   * an 'e' member of the extension the way every other object it creates
   * is -- so it never matches the pg_depend check above.
   */
  OR (_is_own_object.classid = 'pg_catalog.pg_namespace'::regclass AND _is_own_object.objid = e.extnamespace)
  /*
   * The extension's own pg_extension row is also its own special case: it
   * isn't a member of itself (no 'e' row with itself as both member and
   * owner), so treat it as one explicitly.
   */
  OR (_is_own_object.classid = 'pg_catalog.pg_extension'::regclass AND _is_own_object.objid = e.oid)
FROM (SELECT oid, extnamespace FROM pg_catalog.pg_extension WHERE extname = 'object_reference') e
$body$
  , 'Is the object a member of the object_reference extension itself? (pg_depend deptype = e membership, not just co-installation.)'
);

CREATE TABLE _object_reference.object(
  object_id       serial                  PRIMARY KEY
  , object_type   cat_tools.object_type   NOT NULL
--  , original_name text                    NOT NULL
  , object_names text[]                  NOT NULL
  , object_args  text[]                  NOT NULL
  , CONSTRAINT object__u_object_names__object_args UNIQUE( object_type, object_names, object_args )
  /* EXCLUDED CODE: TODO: this can't be a trigger because some objects won't exist when a dump is loaded
  , CONSTRAINT object__address_sanity
    -- pg_get_object_address will throw an error if anything is wrong, so the IS NOT NULL is mostly pointless
    CHECK( pg_catalog.pg_get_object_address(object_type::text, object_names, object_args) IS NOT NULL )
    */
);
SELECT __object_reference.safe_dump('_object_reference.object');
SELECT __object_reference.safe_dump('_object_reference.object_object_id_seq');
GRANT REFERENCES ON _object_reference.object TO object_reference__dependency;

CREATE TABLE _object_reference._object_oid(
  object_id       int                     PRIMARY KEY REFERENCES _object_reference.object ON DELETE CASCADE ON UPDATE CASCADE
  , classid       oid                     NOT NULL
  /* EXCLUDED CODE: TODO: needs to be a trigger
    CONSTRAINT classid_must_match__object__address_classid
      CHECK( classid IS NOT DISTINCT FROM cat_tools.object__address_classid(object_type) )
    */
  , objid         oid                     NOT NULL
  , objsubid      int                     NOT NULL
  , CONSTRAINT object__u_classid__objid__objsubid UNIQUE( classid, objid, objsubid )
);

SELECT __object_reference.create_function(
  '_object_reference._sanity'
  , $args$
  obj _object_reference.object
  , id _object_reference._object_oid
  , OUT names_ok boolean
  , OUT ids_ok boolean
  , OUT ids_exist boolean
$args$
  , 'RECORD LANGUAGE plpgsql STABLE'
  , $body$
DECLARE
  r record;
BEGIN
  ASSERT NOT obj IS NULL, 'obj may not be null';
  ASSERT id IS NULL OR obj.object_id = id.object_id, 'id must be null or object_ids must match';

  ids_exist := NOT (id IS NULL); -- Remember this is NOT the same as id IS NOT NULL!

  BEGIN
    r := pg_catalog.pg_get_object_address(obj.object_type::text, obj.object_names, obj.object_args);
    names_ok := true;

    -- Assume that if get_object_address worked then the names are at least valid
    ids_ok := r IS NOT DISTINCT FROM (id.classid::oid, id.objid, id.objsubid);
  EXCEPTION
    WHEN others THEN
      IF
        SQLSTATE IN(
          '22023' -- invalid_parameter_value
          , '3F000' -- invalid_schema_name
          , '42703' -- undefined_column
          , '42704' -- undefined_object
          , '42883' -- undefined_function
        )
        OR SQLSTATE LIKE '42P%' -- Matches a bunch of codes, including undefined_* and invalid_*_definition
      THEN
        names_ok := false;
        ids_ok := false; -- Should we see if pg_object_identity_as_address works??
      ELSE
        RAISE WARNING 'Unexpected error!!';
        RAISE; -- Unexpected, so re-raise
      END IF;
  END;
END
$body$
  , 'Check the sanity of object and _object_oid'
);

CREATE VIEW _object_reference._object_v AS
  SELECT 
      o.object_id
      , o.object_type
      , o.object_names
      , o.object_args
      , i.classid
      , i.objid
      , i.objsubid
      , s.*
    FROM _object_reference.object o
      LEFT JOIN _object_reference._object_oid i USING(object_id)
      , _object_reference._sanity(o, i) s
;
CREATE VIEW _object_reference._object_v__for_update AS
  SELECT 
      o.object_id
      , o.object_type
      , o.object_names
      , o.object_args
      , i.classid
      , i.objid
      , i.objsubid
      , s.*
    FROM _object_reference.object o
      LEFT JOIN _object_reference._object_oid i USING(object_id)
      , _object_reference._sanity(o, i) s
    FOR UPDATE OF o
;

SELECT __object_reference.create_function(
  '_object_reference._object_oid__add'
  , $args$
  object_id _object_reference._object_oid.object_id%TYPE
  , object_type _object_reference.object.object_type%TYPE DEFAULT NULL
  , classid _object_reference._object_oid.classid%TYPE DEFAULT NULL
  , objid _object_reference._object_oid.objid%TYPE DEFAULT NULL
  , objsubid _object_reference._object_oid.objsubid%TYPE DEFAULT NULL
$args$
  , '_object_reference._object_v LANGUAGE plpgsql'
  , $body$
DECLARE
  r_object_v _object_reference._object_v;
BEGIN
  IF object_type IS NULL THEN
    -- Should definitely exist
    SELECT INTO STRICT object_type, classid, objid, objsubid
        o.object_type, a.classid, a.objid, a.objsubid
      FROM _object_reference.object o
        , pg_catalog.pg_get_object_address(o.object_type::text, o.object_names, o.object_args) a
      WHERE o.object_id = _object_oid__add.object_id
    ;
  END IF;
  BEGIN
    INSERT INTO _object_reference._object_oid(object_id, classid, objid, objsubid)
      VALUES (object_id, classid, objid, objsubid);

    SELECT INTO STRICT r_object_v -- Record better exist!
        *
      FROM _object_reference._object_v__for_update o
      WHERE o.object_id = _object_oid__add.object_id
    ;
  END;

  IF NOT r_object_v.ids_ok THEN
    RAISE 'id mismatch for object_id %', object_id
      USING
        DETAIL = '_object_reference._object_v = ' || pg_catalog.row_to_json(r_object_v)
        , HINT = 'this should not be possible'
    ;
  END IF;

  RETURN r_object_v;
END
$body$
  , 'Check the sanity of object and _object_oid'
);

/*
 * fix_refs / post_restore
 */
SELECT __object_reference.create_function(
  '_object_reference.fix_refs'
  , 'warning_only boolean'
  , 'void LANGUAGE plpgsql'
  , $body$
DECLARE
  r_object_v _object_reference._object_v;
BEGIN
  FOR r_object_v IN
    SELECT * FROM _object_reference._object_v
  LOOP
    CASE
      WHEN r_object_v.names_ok AND r_object_v.ids_ok THEN
        NULL; -- All good!
      WHEN NOT r_object_v.names_ok THEN
        IF r_object_v.ids_exist THEN
          -- Only happens if things are out of sync, so intentionally treat this as an error
          RAISE 'names/args are out of sync on object_id %', r_object_v.object_id
            USING
              DETAIL = '_object_reference._object_v = ' || pg_catalog.row_to_json(r_object_v)
              , HINT = CASE WHEN r_object_v.ids_ok THEN
                  E'The IDs are OK though. This should not happen, but may be fixable.\n'
                    || 'Sanity-check the record and if OK then UPDATE _object_identity.object.'
                ELSE
                  'There is also a record in _object_identity._object_oid with invalid IDs. This should never happen.'
                END
          ;
        ELSE
          IF warning_only THEN
            RAISE WARNING 'names not ok for object_id %', r_object_v.object_id
              USING DETAIL = format(
                'pg_catalog.pg_get_object_address(%L, %L, %L)'
                , r_object_v.object_type
                , r_object_v.object_names
                , r_object_v.object_args
              )
            ;
          ELSE
            RAISE 'names not ok for object_id %', r_object_v.object_id
              USING DETAIL = format(
                'pg_catalog.pg_get_object_address(%L, %L, %L)'
                , r_object_v.object_type
                , r_object_v.object_names
                , r_object_v.object_args
              )
            ;
          END IF;
        END IF;

      -- at this point, names are OK but ids are not (or don't exist)
      WHEN NOT r_object_v.ids_exist THEN
        IF warning_only THEN
          -- This is a normal condition during a restore, so just fix it
          PERFORM _object_reference._object_oid__add(r_object_v.object_id);
        ELSE
          RAISE 'no record in _object_reference._object_oid for object_id %', r_object_v.object_id
            USING
              DETAIL = '_object_reference._object_v = ' || pg_catalog.row_to_json(r_object_v)
              , HINT = 'It should be safe to fix this by calling _object_reference.fix_refs()'
          ;
        END IF;
      WHEN r_object_v.ids_exist THEN
        IF warning_only THEN
          RAISE WARNING 'extraneous ID information for object_id %', r_object.object_id
            USING
              DETAIL = '_object_reference._object_v = ' || pg_catalog.row_to_json(r_object_v)
              , HINT = E'The names are OK though. This should not happen, but may be fixable.\n'
                    || 'Sanity-check the record and if OK then UPDATE _object_identity._object_v.'
          ;
        ELSE
          RAISE 'extraneous ID information for object_id %', r_object.object_id
            USING
              DETAIL = '_object_reference._object_v = ' || pg_catalog.row_to_json(r_object_v)
              , HINT = E'The names are OK though. This should not happen, but may be fixable.\n'
                    || 'Sanity-check the record and if OK then UPDATE _object_identity._object_v.'
          ;
        END IF;
    END CASE;
  END LOOP;
END
$body$
  , 'Fixes records in _object_reference._object_oid after a restore.'
);
SELECT __object_reference.create_function(
  'object_reference.post_restore'
  , ''
  , 'void SECURITY DEFINER LANGUAGE sql'
  , 'SELECT _object_reference.fix_refs(false)'
  , 'Ensures all object references are correct after a restore.'
  , 'object_reference__usage'
);


SELECT __object_reference.create_function(
  '_object_reference._repair'
  , ''
  , 'bigint SECURITY DEFINER LANGUAGE sql'
  , 'SELECT count(*) AS objects FROM _object_reference.object, _object_reference._object_oid__add(object_id)'
  , 'Ensures all object references are correct after a restore.'
  , 'object_reference__usage'
);

CREATE MATERIALIZED VIEW _object_reference._sentry_mv AS SELECT _object_reference._repair();
SELECT __object_reference.safe_dump('_object_reference._sentry_mv');

/*
 * Unsupported object types
 */
SELECT __object_reference.create_function(
  'object_reference.unsupported'
  , ''
  , 'cat_tools.object_type[] LANGUAGE sql IMMUTABLE'
  , $body$
SELECT cat_tools.objects__shared()
  || cat_tools.objects__address_unsupported()
  /*
   * pg_get_object_address() doesn't recognize "partitioned table" or
   * "partitioned index" (only the base "table"/"index" types it derives
   * from), so object identity tracking can't round-trip them.
   */
  || '{event trigger, partitioned table, partitioned index}'
$body$
  , 'Returns array of object types that are not supported.'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.unsupported_srf'
  , ''
  , 'SETOF cat_tools.object_type LANGUAGE sql IMMUTABLE'
  , $body$
SELECT * FROM unnest(object_reference.unsupported())
$body$
  , 'Returns set of object types that are not supported.'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.unsupported'
  , 'object_type cat_tools.object_type'
  , 'boolean LANGUAGE sql IMMUTABLE'
  , $body$
SELECT object_type = ANY(object_reference.unsupported())
$body$
  , 'Is a object_type unsupported?'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.unsupported'
  , 'object_type text'
  , 'boolean LANGUAGE sql IMMUTABLE'
  , $body$
SELECT object_reference.unsupported(object_type::cat_tools.object_type)
$body$
  , 'Is a object_type unsupported?'
  , 'object_reference__usage'
);

/*
 * Untested object types
 */
SELECT __object_reference.create_function(
  'object_reference.untested'
  , ''
  , 'cat_tools.object_type[] LANGUAGE sql IMMUTABLE'
  , $body$
SELECT '{
foreign table, foreign table column, aggregate, collation, conversion, language,
large object, operator, operator class, operator family, operator of access method,
function of access method, rule, text search parser, text search dictionary,
text search template, text search configuration, foreign-data wrapper, server,
user mapping, default acl, transform, access method, extension, policy
}'::cat_tools.object_type[]
$body$
  , 'Returns array of object types that have not been tested.'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.untested_srf'
  , ''
  , 'SETOF cat_tools.object_type LANGUAGE sql IMMUTABLE'
  , $body$
SELECT * FROM unnest(object_reference.untested())
$body$
  , 'Returns set of object types that have not been tested.'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.untested'
  , 'object_type cat_tools.object_type'
  , 'boolean LANGUAGE sql IMMUTABLE'
  , $body$
SELECT object_type = ANY(object_reference.untested())
$body$
  , 'Is a object_type untested?'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.untested'
  , 'object_type text'
  , 'boolean LANGUAGE sql IMMUTABLE'
  , $body$
SELECT object_reference.untested(object_type::cat_tools.object_type)
$body$
  , 'Is a object_type untested?'
  , 'object_reference__usage'
);


/*
 * OBJECT GROUP
 */
CREATE TABLE _object_reference.object_group(
  object_group_id         serial        PRIMARY KEY
  , object_group_name     varchar(200)  NOT NULL
);
CREATE UNIQUE INDEX object_group__u_object_group_name__lower ON _object_reference.object_group(lower(object_group_name));
SELECT __object_reference.safe_dump('_object_reference.object_group');

CREATE TABLE _object_reference.object_group__object(
  object_group_id         int     NOT NULL REFERENCES _object_reference.object_group
  , object_id             int     NOT NULL REFERENCES _object_reference.object
  , CONSTRAINT object_group__object__u_object_group_id__object_id UNIQUE( object_group_id, object_id )
);
SELECT __object_reference.safe_dump('_object_reference.object_group__object');

-- Trigger function for automatic object cleanup
SELECT __object_reference.create_function(
  '_object_reference._object_group__object__cleanup_trigger'
  , ''
  , 'trigger LANGUAGE plpgsql'
  , $body$
BEGIN
  PERFORM object_reference.object__cleanup(OLD.object_id);
  RETURN OLD;
END
$body$
  , 'Trigger function to automatically attempt cleanup of objects when removed from groups.'
);
CREATE TRIGGER object_group__object__cleanup
  AFTER DELETE ON _object_reference.object_group__object
  FOR EACH ROW
  EXECUTE FUNCTION _object_reference._object_group__object__cleanup_trigger();

-- __get
SELECT __object_reference.create_function(
  'object_reference.object_group__get'
  , $args$
  object_group_name _object_reference.object_group.object_group_name%TYPE
$args$
  , '_object_reference.object_group LANGUAGE plpgsql STABLE'
  , $body$
DECLARE
  r _object_reference.object_group;
BEGIN
  SELECT INTO STRICT r
    *
    FROM _object_reference.object_group ogo
    WHERE lower(ogo.object_group_name) = lower(object_group__get.object_group_name)
  ;
  RETURN r;
EXCEPTION WHEN no_data_found THEN
  RAISE 'object group "%" does not exist', object_group_name
    USING ERRCODE = 'no_data_found'
  ;
END
$body$
  , 'Get details about the specified object group'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.object_group__get'
  , $args$
  object_group_id _object_reference.object_group.object_group_id%TYPE
$args$
  , '_object_reference.object_group LANGUAGE plpgsql STABLE'
  , $body$
DECLARE
  r _object_reference.object_group;
BEGIN
  SELECT INTO STRICT r
    *
    FROM _object_reference.object_group ogo
    WHERE (ogo.object_group_id) = (object_group__get.object_group_id)
  ;
  RETURN r;
EXCEPTION WHEN no_data_found THEN
  RAISE 'object group id % does not exist', object_group_id
    USING ERRCODE = 'no_data_found'
  ;
END
$body$
  , 'Get details about the specified object group'
  , 'object_reference__usage'
);

-- __create
SELECT __object_reference.create_function(
  'object_reference.object_group__create'
  , $args$
  object_group_name _object_reference.object_group.object_group_name%TYPE
$args$
  , 'int LANGUAGE sql'
  , $body$
INSERT INTO _object_reference.object_group(object_group_name) VALUES(object_group_name)
  RETURNING object_group_id
$body$
  , 'Create a new object group.'
  , 'object_reference__usage'
);

-- __remove
SELECT __object_reference.create_function(
  'object_reference.object_group__remove'
  , $args$
  object_group_id _object_reference.object_group.object_group_id%TYPE
  , force boolean DEFAULT false
$args$
  , 'void LANGUAGE plpgsql'
  , $body$
DECLARE
  -- This is to ensure group exists
  c_object_group_id CONSTANT int := (object_reference.object_group__get($1)).object_group_id;
BEGIN
  IF force IS TRUE THEN
    DELETE FROM _object_reference.object_group__object
      WHERE object_group__object.object_group_id = c_object_group_id
    ;
  END IF;
  DELETE FROM _object_reference.object_group
    WHERE object_group.object_group_id = c_object_group_id
  ;
END
$body$
  , 'Remove a object group. If force is true, remove group even if it still references objects.'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.object_group__remove'
  , $args$
  object_group_name _object_reference.object_group.object_group_name%TYPE
  , force boolean DEFAULT false
$args$
  , 'void LANGUAGE sql'
  , $body$
SELECT object_reference.object_group__remove(
  (object_reference.object_group__get($1)).object_group_id
  , $2
);
$body$
  , 'Remove a object group. If force is true, remove group even if it still references objects.'
  , 'object_reference__usage'
);

-- __object__add
SELECT __object_reference.create_function(
  'object_reference.object_group__object__add'
  , $args$
  object_group_id _object_reference.object_group__object.object_group_id%TYPE
  , object_id _object_reference.object_group__object.object_id%TYPE
$args$
  , 'void LANGUAGE sql'
  , $body$
  INSERT INTO _object_reference.object_group__object AS ogo(object_group_id, object_id)
    VALUES($1, $2)
    ON CONFLICT (object_group_id, object_id) DO NOTHING
$body$
  , 'Add a object_id to a object group.'
  , 'object_reference__usage'
);

-- __object__remove
SELECT __object_reference.create_function(
  'object_reference.object_group__object__remove'
  , $args$
  object_group_id _object_reference.object_group__object.object_group_id%TYPE
  , object_id _object_reference.object_group__object.object_id%TYPE
$args$
  , 'void LANGUAGE plpgsql'
  , $body$
BEGIN
  DELETE FROM _object_reference.object_group__object AS ogo
    WHERE
      (
        ogo.object_group_id
        , ogo.object_id
      ) = (
        -- This is to ensure group exists
        (object_reference.object_group__get($1)).object_group_id
        , object_group__object__remove.object_id
      )
  ;

  IF NOT FOUND THEN
    -- We know group exists, so issue must be that object doesn't exist
    RAISE 'object id % does not exist', object_id
      USING ERRCODE = 'no_data_found'
    ;
  END IF;
END
$body$
  , 'Remove a object_id from a object group.'
  , 'object_reference__usage'
);


/*
 * REFERENCES
 */

SELECT __object_reference.create_function(
  'object_reference.object_group__dependency__add'
  , $args$
  table_name text
  , field_name name
$args$
  , 'void LANGUAGE plpgsql'
  , $body$
DECLARE
  -- Do this to sanitize input
  o_table CONSTANT regclass := table_name;
BEGIN
  PERFORM _object_reference.exec( format( 'ALTER TABLE %s ADD FOREIGN KEY( %I ) REFERENCES _object_reference.object_group', table_name, field_name ) );
END
$body$
  , 'Add a foreign key from <table_name>.<field_name> to the object_group table.'
  , 'object_reference__dependency'
);
-- s/object_group/object/
SELECT __object_reference.create_function(
  'object_reference.object__dependency__add'
  , $args$
  table_name text
  , field_name name
$args$
  , 'void LANGUAGE plpgsql'
  , $body$
DECLARE
  -- Do this to sanitize input
  o_table CONSTANT regclass := table_name;
BEGIN
  PERFORM _object_reference.exec( format( 'ALTER TABLE %s ADD FOREIGN KEY( %I ) REFERENCES _object_reference.object', table_name, field_name ) );
END
$body$
  , 'Add a foreign key from <table_name>.<field_name> to the object table.'
  , 'object_reference__dependency'
);

/*
 * OBJECT INFO FUNCTIONS
 */
SELECT __object_reference.create_function(
  'object_reference.object__describe'
  , $args$
  object_id int
$args$
  , 'text LANGUAGE sql'
  , $body$
SELECT pg_catalog.pg_describe_object(
  o.classid
  , o.objid
  , o.objsubid
)
FROM _object_reference._object_oid o
WHERE o.object_id = $1
$body$
  , 'Return a human-readable description of the object, matching pg_describe_object() format.'
  , 'object_reference__usage'
);

SELECT __object_reference.create_function(
  'object_reference.object__identity'
  , $args$
  object_id int
  , OUT type text
  , OUT schema text
  , OUT name text
  , OUT identity text
$args$
  , 'record LANGUAGE sql'
  , $body$
SELECT
  i.type::text
  , i.schema::text
  , i.name::text
  , i.identity::text
FROM _object_reference._object_oid o
  , LATERAL pg_catalog.pg_identify_object(o.classid, o.objid, o.objsubid) i
WHERE o.object_id = $1
$body$
  , 'Return object identification information matching pg_identify_object() format.'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.object__cleanup'
  , $args$
  object_id int
$args$
  , 'void LANGUAGE plpgsql'
  , $body$
BEGIN
  DELETE FROM _object_reference.object WHERE object.object_id = object__cleanup.object_id;
EXCEPTION WHEN foreign_key_violation THEN
  -- Object is still referenced elsewhere, ignore the error
  NULL;
END
$body$
  , 'Attempts to delete an object from the tracking system. Silently returns if the object is still referenced by other tables.'
  , 'object_reference__usage'
);

/*
 * OBJECT GETSERT
 */
SELECT __object_reference.create_function(
  '_object_reference._object_v__for_update'
  , $args$
  object_type _object_reference.object.object_type%TYPE
  , objid _object_reference._object_oid.objid%TYPE
  , objsubid _object_reference._object_oid.objsubid%TYPE
  , object_group_id int DEFAULT NULL
  , class_id regclass DEFAULT NULL
$args$
  , '_object_reference._object_v LANGUAGE plpgsql'
  , $body$
DECLARE
  c_classid CONSTANT regclass := cat_tools.object__address_classid(object_type);

  r_object_v _object_reference._object_v;
  r_address record;
  r_identity record;

  did_insert boolean := false;

  i smallint;
  sql text;
BEGIN
  ASSERT class_id IS NULL OR class_id = c_classid, format(
    'cat_tools.object__address_classid(object_type) %L <> class_id %L'
    , c_classid
    , class_id
  );
  IF object_reference.unsupported(object_type) THEN
    RAISE 'object_type % is not supported', object_type;
  END IF;

  SELECT INTO r_address * FROM pg_catalog.pg_identify_object_as_address(c_classid, objid, objsubid);

  IF r_address IS NULL THEN
    RAISE 'unable to find object'
      USING DETAIL = format(
        'pg_identify_object_as_address(%s, %s, %s) returned NULL'
        , c_classid
        , objid
        , objsubid
      )
    ;
  END IF;

  -- Refuse to track objects in temporary schemas
  SELECT INTO r_identity * FROM pg_catalog.pg_identify_object(c_classid, objid, objsubid);
  IF r_identity.schema IS NOT NULL AND (r_identity.schema LIKE 'pg_temp%' OR r_identity.schema LIKE 'pg_toast_temp%') THEN
    RAISE 'cannot track temporary object'
      USING DETAIL = format('object %s is in temporary schema %s', r_identity.identity, r_identity.schema)
      , ERRCODE = 'feature_not_supported'
    ;
  END IF;

  -- Refuse to track objects that are themselves members of this extension
  IF _object_reference._is_own_object(c_classid, objid) THEN
    RAISE 'cannot track an object that is a member of the object_reference extension itself'
      USING DETAIL = format('object %s belongs to the object_reference extension', r_identity.identity)
      , ERRCODE = 'feature_not_supported'
    ;
  END IF;

  -- Ensure the object record exists
  SELECT INTO r_object_v
      *
    FROM _object_reference._object_v__for_update o
    WHERE (o.object_type, o.object_names, o.object_args) = (_object_v__for_update.object_type, r_address.object_names, r_address.object_args)
  ;
  IF NOT FOUND THEN
    FOR i IN 1..10 LOOP
      did_insert := true;
      INSERT INTO _object_reference.object(object_type, object_names, object_args)
        VALUES(_object_v__for_update.object_type, r_address.object_names, r_address.object_args)
        ON CONFLICT ON CONSTRAINT object__u_object_names__object_args DO NOTHING
      ;
      -- Still a small race condition here...
      SELECT INTO r_object_v
          *
        FROM _object_reference._object_v__for_update o
        WHERE (o.object_type, o.object_names, o.object_args) = (_object_v__for_update.object_type, r_address.object_names, r_address.object_args)
      ;
      EXIT WHEN FOUND;
    END LOOP;
    IF NOT FOUND THEN
      RAISE 'fell out of loop!' USING HINT = 'This should never happen.';
    END IF;
  END IF;

  ASSERT r_object_v.names_ok, 'names do not match (should not be possible)' ;

  IF object_group_id IS NOT NULL THEN
    PERFORM object_reference.object_group__object__add(object_group_id, r_object_v.object_id);
  END IF;

  -- Handle _object_oid table
  CASE
    WHEN r_object_v.ids_ok THEN
      RETURN r_object_v;

    WHEN NOT r_object_v.ids_exist THEN
      /*
       * Just need to create IDs record.
       */

      /* 
       * This shouldn't normally happen, but could occur if a restore didn't
       * finish cleanly. We know it's safe to do this because names_ok is true.
       */
      IF NOT did_insert THEN
        RAISE WARNING 'missing record in _object_reference._object_oid for object_id %', r_object_v.object_id
          USING HINT = 'This indicates a restore did not finish cleanly.'
        ;
      END IF;
      r_object_v := _object_reference._object_oid__add(r_object_v.object_id, object_type, c_classid, objid, objsubid);

    WHEN r_object_v.ids_exist THEN
      RAISE 'ids are out of sync for object_id %', r_object_v.object_id
        USING DETAIL = format(
          E'_object_reference._object_v = %L,\n    arguments (%L, %s, %s, %s)'
          , pg_catalog.row_to_json(r_object_v, true)
          , object_type
          , objid
          , objsubid
          , object_group_id
        )
        , HINT = 'this shoud not happen if event trigger "zzz_object_reference_end" is working'
      ;
    ELSE
      RAISE 'unknown condition';
  END CASE;

  RETURN r_object_v;
END
$body$
  , 'Return details of a object record, creating a new record if one does not exist. Heavy-weight compared to a plain read of _object_reference._object_v -- use that instead when an existing record is all that''s needed.'
);

SELECT __object_reference.create_function(
  'object_reference.object__getsert_w_group_id'
  , $args$
  object_type   cat_tools.object_type
  , object_name text
  , secondary text DEFAULT NULL
  , object_group_id int DEFAULT NULL
  , loose boolean DEFAULT false
$args$
  , 'int LANGUAGE plpgsql'
  , $body$
DECLARE
  c_catalog CONSTANT regclass := cat_tools.object__catalog(object_type);
  c_loose CONSTANT boolean := coalesce(loose, false);

  v_objid oid;
  v_subid int := 0;
BEGIN
  RAISE DEBUG '% "%" (secondary %) uses catalog %', object_type, object_name, secondary, c_catalog;

  -- Some catalogs need special handling
  CASE c_catalog
  -- Functions
  WHEN 'pg_catalog.pg_proc'::regclass THEN
    /*
     * Need to handle functions specially to support all the extra options they
     * can have that regprocedure doesn't support.
     */
    -- TODO: allow this to parse object_name directly
    BEGIN
      v_objid := cat_tools.regprocedure(object_name, secondary);
    EXCEPTION WHEN undefined_function THEN
      IF c_loose THEN
        RETURN NULL;
      END IF;
      RAISE;
    END;
    secondary = NULL;

  -- Columns
  WHEN 'pg_catalog.pg_attribute'::regclass THEN
    v_objid := object_name::regclass;
    BEGIN
      -- Will throw error if column isn't valid
      v_subid := (cat_tools.pg_attribute__get(v_objid, secondary)).attnum;
    EXCEPTION WHEN undefined_column THEN
      IF c_loose THEN
        RETURN NULL;
      END IF;
      RAISE;
    END;
    secondary = NULL;

  -- Defaults
  WHEN 'pg_catalog.pg_attrdef'::regclass THEN
    BEGIN
      SELECT INTO STRICT v_objid
          oid
        FROM pg_catalog.pg_attrdef
        WHERE adrelid = object_name::regclass
          -- Will throw error if column isn't valid
          AND adnum = (cat_tools.pg_attribute__get(object_name::regclass, secondary)).attnum
      ;
    EXCEPTION WHEN no_data_found THEN
      IF c_loose THEN
        RETURN NULL;
      END IF;
      RAISE 'default value for %.% does not exist', object_name::regclass, secondary
        USING ERRCODE = 'undefined_object'
      ;
    END;
    secondary = NULL;

  -- Triggers
  WHEN 'pg_catalog.pg_trigger'::regclass THEN
    BEGIN
      SELECT INTO STRICT v_objid
          oid
        FROM pg_catalog.pg_trigger
        WHERE tgrelid = object_name::regclass
          AND tgname = secondary
      ;
    EXCEPTION WHEN no_data_found THEN
      IF c_loose THEN
        RETURN NULL;
      END IF;
      RAISE 'trigger "%" for table "%" does not exist', secondary, object_name::regclass
        USING ERRCODE = 'undefined_object'
      ;
    END;
    secondary = NULL;

  -- Constraints
  WHEN 'pg_catalog.pg_constraint'::regclass THEN
    DECLARE
      v_relid oid = 0;
      v_typid oid = 0;
    BEGIN
      CASE object_type
        WHEN 'table constraint'::cat_tools.object_type THEN -- conrelid
          v_relid := object_name::regclass;
        WHEN 'domain constraint'::cat_tools.object_type THEN -- contypid
          v_typid := object_name::regtype;
        ELSE
          RAISE 'unexpected object type % for a constraint', object_type;
      END CASE;

      BEGIN
        SELECT INTO STRICT v_objid
            oid
          FROM pg_catalog.pg_constraint
          WHERE conname = secondary
            AND conrelid = v_relid
            AND contypid = v_typid
          ;
      EXCEPTION WHEN no_data_found THEN
        -- At this point regclass or regtype should have thrown an error if the parent object doesn't exist
        IF c_loose THEN
          RETURN NULL;
        END IF;
        RAISE 'constraint "%" does not exist', secondary
          USING ERRCODE = 'undefined_object'
        ;
      END;
    END;
    secondary = NULL;

  -- Casts
  WHEN 'pg_catalog.pg_cast'::regclass THEN
    BEGIN
      SELECT INTO STRICT v_objid
          oid
        FROM pg_catalog.pg_cast
        WHERE castsource = object_name::regtype
          AND casttarget = secondary::regtype
        ;
    EXCEPTION WHEN no_data_found THEN
      IF c_loose THEN
        RETURN NULL;
      END IF;
      RAISE 'cast from "%" to "%" does not exist', object_name, secondary
        USING ERRCODE = 'undefined_object'
      ;
      IF c_loose THEN
        RETURN NULL;
      END IF;
    END;
    secondary = NULL;

  ELSE
    DECLARE
      c_reg_type name := cat_tools.object__reg_type(c_catalog);

      v_name_field text;
      sql text;
    BEGIN
      IF c_reg_type IS NULL THEN
        /*
         * Need to do a manual lookup of the OID based on what catalog it is
         *
         * Get first 3 letters of catalog name after the 'pg_', since that's
         * usually the field name. We also need to handle the possibility of
         * 'pg_catalog.' being part of c_catalog.
         */
        v_name_field := substring(regexp_replace(c_catalog::text, '(pg_catalog\.)?pg_', ''), 1, 3);

        sql := format(
          'SELECT oid FROM %s WHERE %I = %L'
          , c_catalog -- No need to quote
          , v_name_field
          , object_name
        );
      ELSE
        sql := format(
          'SELECT %L::%s'
          , object_name
          , c_reg_type -- No need to quote
        );
      END IF;
      RAISE DEBUG 'looking up % % via %', object_type, object_name, sql;
      BEGIN
        EXECUTE sql INTO STRICT v_objid;
      EXCEPTION WHEN no_data_found THEN
        IF c_loose THEN
          RETURN NULL;
        END IF;
        RAISE '% "%" does not exist', object_type, object_name
          USING ERRCODE = 'undefined_object'
        ;
      END;
    END;
  END CASE;

  IF secondary IS NOT NULL THEN
    RAISE 'secondary may not be specified for % objects', object_type;
  END IF;

  RETURN (_object_reference._object_v__for_update( object_type, v_objid, v_subid, object_group_id )).object_id;
END
$body$
  , 'Return a object_id for an object. Allows specifying a object group ID to add the object to. See also object__getsert().'
  , 'object_reference__usage'
);

SELECT __object_reference.create_function(
  'object_reference.object__getsert'
  , $args$
  object_type   cat_tools.object_type
  , object_name text
  , secondary text DEFAULT NULL
  , object_group_name _object_reference.object_group.object_group_name%TYPE DEFAULT NULL
  , loose boolean DEFAULT false
$args$
  , 'int LANGUAGE sql'
  , $body$
SELECT object_reference.object__getsert_w_group_id(
  $1, $2, $3
  , CASE WHEN object_group_name IS NOT NULL THEN
      (object_reference.object_group__get($4)).object_group_id
    END
  , $5
)
$body$
  , 'Return a object_id for an object. Allows specifying a object group name to add the object to. See also object__getsert_w_group_id().'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.object__getsert'
  , $args$
  object_type text
  , object_name text
  , secondary text DEFAULT NULL
  , object_group_name _object_reference.object_group.object_group_name%TYPE DEFAULT NULL
  , loose boolean DEFAULT false
$args$
  , 'int LANGUAGE sql'
  , $$SELECT object_reference.object__getsert( lower($1)::cat_tools.object_type, $2, $3, $4, $5 )$$
  , 'Return a object_id for an object. Allows specifying a object group name to add the object to. See also object__getsert_w_group_id().'
  , 'object_reference__usage'
);

/*
 * ddl_capture
 */
SELECT __object_reference.create_function(
  'object_reference.capture__get_all'
  , $args$
  OUT capture_level int
  , OUT object_group_id _object_reference.object_group.object_group_id%TYPE
  , OUT object_group_name _object_reference.object_group.object_group_name%TYPE
$args$
  , 'SETOF RECORD LANGUAGE plpgsql'
  , $body$
DECLARE
BEGIN
  RETURN QUERY SELECT c.capture_level, c.object_group_id, og.object_group_name
    FROM pg_temp.__object_reference__ddl_capture c
    JOIN _object_reference.object_group og USING(object_group_id)
    ORDER BY capture_level DESC
  ;
EXCEPTION WHEN undefined_table THEN
  RETURN;
END
$body$
  , 'Return stack of object groups that are being captured to.'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.capture__get_current'
  , $args$
  OUT capture_level int
  , OUT object_group_id _object_reference.object_group.object_group_id%TYPE
  , OUT object_group_name _object_reference.object_group.object_group_name%TYPE
$args$
  , 'RECORD LANGUAGE sql'
  , $body$
SELECT * FROM object_reference.capture__get_all() LIMIT 1
$body$
  , 'Return object group that object creation currently is being captured to.'
  , 'object_reference__usage'
);

SELECT __object_reference.create_function(
  'object_reference.capture__start'
  , $args$
  object_group_id _object_reference.object_group.object_group_id%TYPE
$args$
  , 'int SECURITY DEFINER LANGUAGE plpgsql'
  , $body$
DECLARE
  c_next_level int := coalesce(capture_level, 0) + 1 FROM object_reference.capture__get_current();
BEGIN
  -- Ensure object group exists
  PERFORM object_reference.object_group__get(object_group_id);

  INSERT INTO pg_temp.__object_reference__ddl_capture 
    SELECT c_next_level, capture__start.object_group_id
  ;
  RETURN c_next_level;

EXCEPTION WHEN undefined_table THEN
  CREATE TEMP TABLE __object_reference__ddl_capture(
    capture_level int PRIMARY KEY
    , object_group_id INT NOT NULL -- temp tables can't reference permanent ones
  );
  -- This breaks if run directly under plpgsql
  EXECUTE $code$
  CREATE CONSTRAINT TRIGGER verify_capture_stop AFTER INSERT
    ON pg_temp.__object_reference__ddl_capture 
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW -- CONSTRAINT triggers must be per-ROW
    EXECUTE PROCEDURE _object_reference._tg_capture_safety()
  $code$;

  INSERT INTO pg_temp.__object_reference__ddl_capture 
    SELECT c_next_level, capture__start.object_group_id
  ;
  RETURN c_next_level;
END
$body$
  , 'Begin capturing newly created objects to <object_group_id>. Returns current capture level.'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.capture__start'
  , $args$
  object_group_name _object_reference.object_group.object_group_name%TYPE
$args$
  , 'int LANGUAGE sql'
  , $body$
SELECT object_reference.capture__start(
  (object_reference.object_group__get(object_group_name)).object_group_id
);
$body$
  , 'Begin capturing newly created objects to <object_group_id>. Returns current capture level.'
  , 'object_reference__usage'
);

SELECT __object_reference.create_function(
  'object_reference.capture__stop'
  , $args$
  object_group_id _object_reference.object_group.object_group_id%TYPE
$args$
  , 'void SECURITY DEFINER LANGUAGE plpgsql'
  , $body$
DECLARE
  r record;
BEGIN
  SELECT INTO STRICT r
      *
    FROM object_reference.capture__get_current()
  ;
  IF r.capture_level IS NULL THEN
    RAISE 'not capturing DDL'
      USING HINT = 'Did you forget to call object_referenc.capture__start()?'
      -- TODO: use better status code
    ;
  END IF;

  IF r.object_group_id <> coalesce(object_group_id) THEN
    RAISE 'object_group mismatch'
      USING DETAIL = format(
        'currently capturing for group %L (id %s), expecting group %L (id %s)'
        , r.object_group_name
        , r.object_group_id
        -- This will error if the group doesn't exist
        , (object_reference.object_group__get(object_group_id)).object_group_name
        , capture__stop.object_group_id
      )
    ;
  END IF;

  DELETE FROM pg_temp.__object_reference__ddl_capture WHERE capture_level = r.capture_level;
EXCEPTION WHEN undefined_table THEN
  RAISE 'not capturing DDL'
    USING HINT = 'Did you forget to call object_referenc.capture__start()?'
  ;
END
$body$
  , 'Begin capturing newly created objects to <object_group_id>. Returns current capture level.'
  , 'object_reference__usage'
);
SELECT __object_reference.create_function(
  'object_reference.capture__stop'
  , $args$
  object_group_name _object_reference.object_group.object_group_name%TYPE
$args$
  , 'void LANGUAGE sql'
  , $body$
SELECT object_reference.capture__stop(
  (object_reference.object_group__get(object_group_name)).object_group_id
)
$body$
  , 'Begin capturing newly created objects to <object_group_id>. Returns current capture level.'
  , 'object_reference__usage'
);

SELECT __object_reference.create_function(
  '_object_reference._tg_capture_safety'
  , ''
  , 'trigger LANGUAGE plpgsql'
  , $body$
BEGIN
  IF EXISTS(SELECT 1 FROM pg_temp.__object_reference__ddl_capture) THEN
    RAISE 'attempted commit while still capturing DDL'
      USING HINT = 'Did you not start a transaction? Did you forget to call object_reference.capture__stop()?'
    ;
  END IF;

  RETURN NULL;
END
$body$
  , 'Trigger function to ensure capture__stop() is called an appropriate number of times.'
);
SELECT __object_reference.create_function(
  '_object_reference._etg_capture'
  , ''
  , 'event_trigger SECURITY DEFINER LANGUAGE plpgsql'
  , $body$
DECLARE
  c_group_id CONSTANT int := object_group_id FROM object_reference.capture__get_current();
      r record;
BEGIN
  /*
   * Self-recognition: skip while this extension's own event_trigger__disable()
   * is in effect (see below) -- i.e. this extension's own install/update
   * script is doing delicate internal restructuring right now. Checked via
   * to_regclass() rather than a catalog lookup that would error if the temp
   * table doesn't exist, which is the common case.
   */
  IF to_regclass('pg_temp.__object_reference__event_trigger_state') IS NOT NULL THEN
    RETURN;
  END IF;

  IF c_group_id IS NOT NULL THEN -- Would be NULL if table is empty
    RAISE DEBUG E'\n\n*** START ***';
    BEGIN
      FOR r IN
        SELECT classid, objid, objsubid, command_tag, object_type, schema_name, object_identity, in_extension
            -- Have to manually exclude command field :/
          FROM pg_catalog.pg_event_trigger_ddl_commands()
      LOOP
        RAISE DEBUG 'ddl: %', row_to_json(r);
      END LOOP;
    END;

    FOR r IN SELECT
    _object_reference._object_v__for_update(
          object_type::cat_tools.object_type
          , objid, objsubid
          , c_group_id
          , classid
        )
        , classid, objid, objsubid, command_tag, object_type, schema_name, object_identity, in_extension
      FROM pg_catalog.pg_event_trigger_ddl_commands()
      WHERE command_tag ~ '^CREATE' --'^(ALTER|CREATE)'
        AND NOT object_reference.unsupported(object_type::cat_tools.object_type)
        AND (schema_name IS NULL
            OR schema_name NOT LIKE 'pg_temp%' -- pg_my_temp_schema() doesn't seem worth it...
          )
        /*
         * __object_reference is this extension's own scratch install/update
         * schema (created and dropped within a single script, never an
         * extension member) -- self-recognition via the temp table above
         * can't cover the handful of bootstrap statements that run before
         * that table exists, so exclude it here too (object_identity
         * carries the name for the CREATE SCHEMA statement itself, where
         * schema_name is null).
         *
         * object_reference/_object_reference are excluded outright rather
         * than relying on _object_v__for_update()'s own _is_own_object()
         * guard: a brand-new object created by this extension's own
         * update/install script isn't yet recorded as an 'e' member in
         * pg_depend at the point its CREATE fires ddl_command_end (that
         * happens once the surrounding CREATE/ALTER EXTENSION completes),
         * so _is_own_object() can't see it as self-owned yet either --
         * confirmed by running into it: an active capture group during
         * ALTER EXTENSION UPDATE otherwise ends up with this extension's
         * own new functions as members.
         *
         * The schema-creation statement itself (CREATE SCHEMA
         * __object_reference/etc.) has schema_name = NULL, with the name
         * only available via object_identity -- checked separately, and
         * restricted to object_type = 'schema', so an unrelated object of
         * some other type whose identity happens to match one of these
         * three exact strings (e.g. a same-named extension) isn't caught
         * by this fallback.
         */
        AND coalesce(schema_name, '') NOT IN ('__object_reference', 'object_reference', '_object_reference')
        AND NOT (object_type = 'schema' AND object_identity IN ('__object_reference', 'object_reference', '_object_reference'))
    LOOP
      RAISE DEBUG 'registered %', row_to_json(r);
    END LOOP;
    RAISE DEBUG E'*** END ***\n\n';
  END IF;
END
$body$
  , 'Event trigger function to capture newly created objects in an object group.'
);


SELECT __object_reference.create_function(
  '_object_reference._etg_fix_identity'
  , ''
  , 'event_trigger SECURITY DEFINER LANGUAGE plpgsql'
  , $body$
DECLARE
  r_ddl record;
  r record;
BEGIN
  /*
   * Self-recognition: skip while this extension's own event_trigger__disable()
   * is in effect (see below) -- i.e. this extension's own install/update
   * script is doing delicate internal restructuring right now. Checked via
   * to_regclass() rather than a catalog lookup that would error if the temp
   * table doesn't exist, which is the common case.
   */
  IF to_regclass('pg_temp.__object_reference__event_trigger_state') IS NOT NULL THEN
    RETURN;
  END IF;

  /*
   * It's tempting to use pg_event_trigger_ddl_commands() to find exactly what
   * items have changed and worry about only those. That won't work because an
   * object_names array can depend on multiple names (ie: a column depends on
   * the name of it's table, as well as the name of the schema the table is in.
   * You might think we could simply recurse through pg_depend to handle this,
   * but not every name dependency gets enumerated that way. For example,
   * columns are not marked as dependent on their table.
   *
   * Rather than trying to be cute about this, we just do a brute-force check
   * for any names that have changed.
   */

  /*
   * Presumably there's no way for an objects type/classid to change, but be
   * safe and attempt the update to object_type. If it actually does change the
   * constraint on the table should catch it.
   */
  FOR r IN
    UPDATE _object_reference.object
      SET object_type  = (pg_catalog.pg_identify_object_as_address(classid, objid, objsubid)).type::cat_tools.object_type
        , object_names = (pg_catalog.pg_identify_object_as_address(classid, objid, objsubid)).object_names
        , object_args  = (pg_catalog.pg_identify_object_as_address(classid, objid, objsubid)).object_args
      FROM _object_reference._object_oid oo
      WHERE 
        oo.object_id = object.object_id
        AND (object_type::text, object_names, object_args) IS DISTINCT FROM
          (pg_catalog.pg_identify_object_as_address(classid, objid, objsubid))
      RETURNING *
  LOOP
    RAISE DEBUG 'modified_objects(): %', r;
  END LOOP;
END
$body$
  , 'Event trigger function to update any records with object names or args that have changed.'
);
SELECT __object_reference.create_function(
  '_object_reference._etg_drop'
  , ''
  , 'event_trigger LANGUAGE plpgsql'
  , $body$
DECLARE
  r_object_v _object_reference._object_v;
  r record;
BEGIN
  FOR r IN SELECT classid, objid, objsubid, object_type, schema_name, object_identity FROM pg_catalog.pg_event_trigger_dropped_objects() LOOP
    RAISE DEBUG 'dropped_objects(): %', r;
  END LOOP;

  /*
   * Multiple objects might have been affected
   * Could potentially be done with a writable CTE
   */
  FOR r_object_v IN
    SELECT _object_v.*
      FROM pg_catalog.pg_event_trigger_dropped_objects() d
        JOIN _object_reference._object_v USING( classid, objid ) -- Intentionally ignore objsubid
      WHERE
        /*
         * If an object that contains subobjects is being removed, we need to
         * also remove all subobjects. In this case, we know d.objsubid = 0
         */
        d.objsubid = 0

        /*
         * Otherwise, only remove the appropriate suboject.
         */
        OR d.objsubid = _object_v.objsubid
  LOOP
    RAISE DEBUG 'deleting object %', r_object_v;
    -- TODO: trap FK violation error on groups and output something better
    DELETE FROM _object_reference.object WHERE object_id = r_object_v.object_id;
  END LOOP;

  /*
   * We know that a restore will never drop objects, so force _object_v to be
   * correct at this point. We can't do this before we delete based on the drop
   * though.
   */
  PERFORM object_reference.post_restore();
END
$body$
  , 'Event trigger function to drop object records when objects are removed.'
);

/*
 * WARNING: avoid disabling event triggers at all where any other option
 * exists. ALTER EVENT TRIGGER is ordinary transactional DDL -- like any
 * other catalog write, it's invisible to other sessions until commit (no
 * special database-wide/immediate effect: verified empirically that a
 * concurrent session's DDL neither blocks on, nor is otherwise affected by,
 * another session's still-uncommitted DISABLE) and it takes no lock at all
 * on the event trigger itself. The real risk is TWO SESSIONS both trying to
 * alter the SAME event trigger concurrently: a second writer blocks on the
 * first the way any two concurrent writes to the same catalog row would,
 * and without care, the one that unblocks second can record and later
 * restore a "prior state" that was never actually the trigger's state
 * immediately before it acted (see the FOR UPDATE lock in
 * event_trigger__disable()'s body below, which exists specifically to close
 * that gap). Prefer a self-recognition check (a session-local flag, checked
 * from inside the trigger's own body) over calling this at all; reach for
 * it only when nothing else can make the trigger stay quiet, as is
 * currently true for zzz__object_reference_drop.
 *
 * General-purpose event-trigger disable/enable mechanism, for use by this
 * extension's OWN install/update scripts only (not part of the public API).
 * Not tied to "being mid-update" specifically -- it's a plain disable-with-
 * restore primitive for any event trigger that can't self-recognize that it
 * should stay quiet.
 *
 * zzz_object_reference__fix_identity and zzz_object_reference_capture check
 * whether this call is currently in effect for their OWN session (via
 * to_regclass() on the temp table below) and skip if so, so they never need
 * to be disabled this way. zzz__object_reference_drop cannot self-recognize
 * the same way without also touching _object_reference._object_v -- a view
 * an update script may itself be dropping and recreating -- from inside its
 * own body, so it must be truly disabled for the duration of such a
 * script's structural section.
 *
 * ALTER EVENT TRIGGER is ordinary transactional DDL, so if the calling
 * script's transaction rolls back, the DISABLE (and any ENABLE already run)
 * rolls back with it -- no separate cleanup-on-error logic is needed here.
 */
SELECT __object_reference.create_function(
  '_object_reference.event_trigger__disable'
  , $args$
  event_trigger_names name[]
$args$
  , 'void LANGUAGE plpgsql'
  , $body$
DECLARE
  v_name name;
  v_enabled "char";
BEGIN
  /*
   * WARNING: avoid disabling event triggers at all where any other option
   * exists -- this is a database-wide change with real race-condition risk
   * against concurrent sessions' DDL. See the warning above this function.
   */
  BEGIN
    -- Save old trigger state
    CREATE TEMP TABLE __object_reference__event_trigger_state AS
      SELECT evtname, evtenabled FROM pg_catalog.pg_event_trigger WHERE false
    ;
    ALTER TABLE pg_temp.__object_reference__event_trigger_state ADD PRIMARY KEY (evtname);
  EXCEPTION WHEN duplicate_table THEN
    RAISE 'event_trigger__disable() called while a previous call is still in effect'
      USING HINT = 'A previous event_trigger__enable() call may have been skipped.'
    ;
  END;

  IF array_length(event_trigger_names, 1) <> (SELECT count(DISTINCT x) FROM unnest(event_trigger_names) x) THEN
    RAISE 'event_trigger_names contains a duplicate name' USING DETAIL = event_trigger_names::text;
  END IF;

  FOREACH v_name IN ARRAY event_trigger_names LOOP
    /*
     * FOR UPDATE locks the row before we read it, so no other session's own
     * ALTER EVENT TRIGGER on the same trigger can land between our read and
     * our DISABLE below -- without it, a concurrent change there would
     * leave us recording (and later restoring) a state that was never
     * actually the trigger's state immediately before we disabled it.
     */
    SELECT evtenabled INTO v_enabled
      FROM pg_catalog.pg_event_trigger
      WHERE evtname = v_name
      FOR UPDATE
    ;

    IF NOT FOUND THEN
      RAISE 'event trigger "%" does not exist', v_name;
    END IF;

    INSERT INTO pg_temp.__object_reference__event_trigger_state(evtname, evtenabled)
      VALUES (v_name, v_enabled);

    PERFORM _object_reference.exec(format('ALTER EVENT TRIGGER %I DISABLE', v_name));
  END LOOP;
END
$body$
  , 'Disable the given event triggers, remembering their exact prior state; pair with event_trigger__enable().'
);
SELECT __object_reference.create_function(
  '_object_reference.event_trigger__enable'
  , ''
  , 'void LANGUAGE plpgsql'
  , $body$
DECLARE
  v_names name[];
  v_states "char"[];
  i int;
BEGIN
  BEGIN
    SELECT array_agg(evtname), array_agg(evtenabled)
      INTO v_names, v_states
      FROM pg_temp.__object_reference__event_trigger_state
    ;
  EXCEPTION WHEN undefined_table THEN
    RAISE 'event_trigger__enable() called without a matching event_trigger__disable()';
  END;

  /*
   * Drop our own bookkeeping table BEFORE re-enabling anything below:
   * dropping it is itself DDL, and if zzz__object_reference_drop is one of
   * the triggers being restored here, re-enabling it first would make this
   * DROP immediately fire it -- reacting to our own internal cleanup,
   * exactly the hazard this whole mechanism exists to avoid. (Confirmed by
   * running into it: with the table dropped after, an active capture group
   * elsewhere left a stale tracked row that this DROP's cascade into
   * post_restore() then found and errored on.)
   */
  DROP TABLE pg_temp.__object_reference__event_trigger_state;

  FOR i IN 1..coalesce(array_length(v_names, 1), 0) LOOP
    PERFORM _object_reference.exec(format(
      'ALTER EVENT TRIGGER %I %s'
      , v_names[i]
      , CASE v_states[i]
          WHEN 'O' THEN 'ENABLE'
          WHEN 'R' THEN 'ENABLE REPLICA'
          WHEN 'A' THEN 'ENABLE ALWAYS'
          WHEN 'D' THEN 'DISABLE'
        END
    ));
  END LOOP;
END
$body$
  , 'Restore event triggers disabled by event_trigger__disable() to their exact prior state.'
);

SELECT __object_reference.create_function(
  '_object_reference.etg_raise__start'
  , ''
  , 'event_trigger LANGUAGE plpgsql'
  , $body$
BEGIN
    RAISE WARNING 'etg_raise__start: % %', tg_event, tg_tag;
END;
$body$
  , $$Event trigger function to report on DDL activity. Example trigger:
CREATE EVENT TRIGGER start
  ON ddl_command_start
  --WHEN tag IN ( 'ALTER TABLE', 'DROP TABLE' )
  EXECUTE PROCEDURE _object_reference.etg_raise__start()
;
$$);
SELECT __object_reference.create_function(
  '_object_reference.etg_raise__drop'
  , ''
  , 'event_trigger LANGUAGE plpgsql'
  , $body$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT classid, objid, objsubid, object_type, schema_name, object_name, object_identity FROM pg_catalog.pg_event_trigger_dropped_objects() LOOP
    RAISE WARNING 'dropped_objects:
    classid: %
    objid: %
    objsubid: %
    object_type: %
    schema_name: %
    object_name: %
    object_identity: %
    '
      -- :^r" s/\([^ ]\+\):.*/, r.\1/
      , r.classid
      , r.objid
      , r.objsubid
      , r.object_type
      , r.schema_name
      , r.object_name
      , r.object_identity
    ;
  END LOOP;
END;
$body$
  , $$Event trigger function to report on DDL activity. Example trigger:
CREATE EVENT TRIGGER drop
  ON sql_drop
  --WHEN tag IN ( 'ALTER TABLE', 'DROP TABLE' )
  EXECUTE PROCEDURE _object_reference.etg_raise__drop()
;
$$);

CREATE EVENT TRIGGER zzz__object_reference_drop
  ON sql_drop
  EXECUTE PROCEDURE _object_reference._etg_drop()
;
CREATE EVENT TRIGGER zzz_object_reference__fix_identity
  ON ddl_command_end
  EXECUTE PROCEDURE _object_reference._etg_fix_identity()
;
CREATE EVENT TRIGGER zzz_object_reference_capture
  ON ddl_command_end
  EXECUTE PROCEDURE _object_reference._etg_capture()
;

/*
 * Drop "temporary" objects
 */
DROP FUNCTION __object_reference.create_function(
  function_name text
  , args text
  , options text
  , body text
  , comment text
  , grants text
);
DROP FUNCTION __object_reference.safe_dump(
  relation regclass
  , text
);
DROP FUNCTION __object_reference.exec(
  sql text
);
DROP SCHEMA __object_reference;

-- vi: expandtab sw=2 ts=2