[jira] [Comment Edited] (SENTRY-2333) Create index AUTHZ_PATH_FK_IDX at table AUTHZ_PATH for Postgres only when it does not exist

["Arjun Mishra (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SENTRY-2333?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16576585#comment-16576585 ] 

Arjun Mishra edited comment on SENTRY-2333 at 8/10/18 5:18 PM:
---------------------------------------------------------------

Tested locally
{noformat}
postgres=> $BLOCK$
postgres$> BEGIN
postgres$>     BEGIN
postgres$>         CREATE INDEX "AUTHZ_PATH_FK_IDX" ON "AUTHZ_PATH"( "AUTHZ_OBJ_ID" );
postgres$>     EXCEPTION
postgres$>         WHEN duplicate_table
postgres$>         THEN RAISE NOTICE 'index ''AUTHZ_PATH_FK_IDX '' on ''AUTHZ_PATH'' already exists, skipping';
postgres$>     END;
postgres$> END;
postgres$> $BLOCK$;
postgres=> $BLOCK$
postgres$> BEGIN
postgres$>     BEGIN
postgres$>         CREATE INDEX "AUTHZ_PATH_FK_IDX" ON "AUTHZ_PATH"( "AUTHZ_OBJ_ID" );
postgres$>     EXCEPTION
postgres$>         WHEN duplicate_table
postgres$>         THEN RAISE NOTICE 'index ''AUTHZ_PATH_FK_IDX '' on ''AUTHZ_PATH'' already exists, skipping';
postgres$>     END;
postgres$> END;
postgres$> $BLOCK$;
NOTICE:  index 'AUTHZ_PATH_FK_IDX ' on 'AUTHZ_PATH' already exists, skipping
postgres=> \d "AUTHZ_PATH"
                        Table "public.AUTHZ_PATH"
    Column    |          Type           | Collation | Nullable | Default
--------------+-------------------------+-----------+----------+---------
 PATH_ID      | bigint                  |           | not null |
 PATH_NAME    | character varying(4000) |           |          |
 AUTHZ_OBJ_ID | bigint                  |           |          |
Indexes:
    "AUTHZ_PATH_PK" PRIMARY KEY, btree ("PATH_ID")
    "AUTHZ_PATH_FK_IDX" btree ("AUTHZ_OBJ_ID")
Foreign-key constraints:
    "AUTHZ_PATH_FK" FOREIGN KEY ("AUTHZ_OBJ_ID") REFERENCES "AUTHZ_PATHS_MAPPING"("AUTHZ_OBJ_ID") DEFERRABLE
{noformat}


was (Author: arjunmishra13):
Tested locally
{noformat}
postgres=> DO $BLOCK$
postgres$> BEGIN
postgres$>     BEGIN
postgres$>         CREATE INDEX "AUTHZ_PATH_FK_IDX" ON "AUTHZ_PATH"( "AUTHZ_OBJ_ID" );
postgres$>     EXCEPTION
postgres$>         WHEN duplicate_table
postgres$>         THEN RAISE NOTICE 'index ''AUTHZ_PATH_FK_IDX '' on ''AUTHZ_PATH'' already exists, skipping';
postgres$>     END;
postgres$> END;
postgres$> $BLOCK$;
DO
postgres=> DO $BLOCK$
postgres$> BEGIN
postgres$>     BEGIN
postgres$>         CREATE INDEX "AUTHZ_PATH_FK_IDX" ON "AUTHZ_PATH"( "AUTHZ_OBJ_ID" );
postgres$>     EXCEPTION
postgres$>         WHEN duplicate_table
postgres$>         THEN RAISE NOTICE 'index ''AUTHZ_PATH_FK_IDX '' on ''AUTHZ_PATH'' already exists, skipping';
postgres$>     END;
postgres$> END;
postgres$> $BLOCK$;
NOTICE:  index 'AUTHZ_PATH_FK_IDX ' on 'AUTHZ_PATH' already exists, skipping
DO
postgres=> \d "AUTHZ_PATH"
                        Table "public.AUTHZ_PATH"
    Column    |          Type           | Collation | Nullable | Default
--------------+-------------------------+-----------+----------+---------
 PATH_ID      | bigint                  |           | not null |
 PATH_NAME    | character varying(4000) |           |          |
 AUTHZ_OBJ_ID | bigint                  |           |          |
Indexes:
    "AUTHZ_PATH_PK" PRIMARY KEY, btree ("PATH_ID")
    "AUTHZ_PATH_FK_IDX" btree ("AUTHZ_OBJ_ID")
Foreign-key constraints:
    "AUTHZ_PATH_FK" FOREIGN KEY ("AUTHZ_OBJ_ID") REFERENCES "AUTHZ_PATHS_MAPPING"("AUTHZ_OBJ_ID") DEFERRABLE
{noformat}

> Create index AUTHZ_PATH_FK_IDX at table AUTHZ_PATH for Postgres only when it does not exist
> -------------------------------------------------------------------------------------------
>
>                 Key: SENTRY-2333
>                 URL: https://issues.apache.org/jira/browse/SENTRY-2333
>             Project: Sentry
>          Issue Type: Sub-task
>          Components: Sentry
>    Affects Versions: 2.1.0
>            Reporter: Arjun Mishra
>            Assignee: Arjun Mishra
>            Priority: Major
>         Attachments: SENTRY-2333.01.patch, SENTRY-2333.02.patch, SENTRY-2333.03.patch, SENTRY-2333.04.patch
>
>




--
This message was sent by Atlassian JIRA
(v7.6.3#76005)