As promised, here's a rebased version of this patch -- edits pending per discussion to decide the grammar to use.
-- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 5df1613901906cff4d0b0b7e480691b65d9d2e5c Mon Sep 17 00:00:00 2001 From: Alvaro Herrera <alvhe...@alvh.no-ip.org> Date: Wed, 30 Oct 2019 16:57:29 -0300 Subject: [PATCH v2 1/2] Add ALTER .. NO DEPENDS ON This new command removes any previously added dependency mark; necessary for completeness. Discussion: https://postgr.es/m/20200217225333.GA30974@alvherre.pgsql Reviewed-by: ahsan hadi <ahsan.h...@gmail.com> Reviewed-by: Ibrar Ahmed <ibrar.ah...@gmail.com> Reviewed-by: Tom Lane <t...@sss.pgh.pa.us> --- doc/src/sgml/ref/alter_function.sgml | 10 ++-- doc/src/sgml/ref/alter_index.sgml | 9 ++-- doc/src/sgml/ref/alter_materialized_view.sgml | 11 ++--- doc/src/sgml/ref/alter_trigger.sgml | 7 ++- src/backend/catalog/pg_depend.c | 49 +++++++++++++++++++ src/backend/commands/alter.c | 24 ++++++--- src/backend/nodes/copyfuncs.c | 1 + src/backend/nodes/equalfuncs.c | 1 + src/backend/parser/gram.y | 36 +++++++++----- src/include/catalog/dependency.h | 4 ++ src/include/nodes/parsenodes.h | 1 + .../expected/test_extdepend.out | 39 ++++++++++++++- .../test_extensions/sql/test_extdepend.sql | 18 ++++++- 13 files changed, 171 insertions(+), 39 deletions(-) diff --git a/doc/src/sgml/ref/alter_function.sgml b/doc/src/sgml/ref/alter_function.sgml index 03ffa5945a..70b1f24bc0 100644 --- a/doc/src/sgml/ref/alter_function.sgml +++ b/doc/src/sgml/ref/alter_function.sgml @@ -30,7 +30,7 @@ ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [ <replaceable class="param ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ] SET SCHEMA <replaceable>new_schema</replaceable> ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ] - DEPENDS ON EXTENSION <replaceable>extension_name</replaceable> + [ NO ] DEPENDS ON EXTENSION <replaceable>extension_name</replaceable> <phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase> @@ -153,10 +153,14 @@ ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [ <replaceable class="param </varlistentry> <varlistentry> - <term><replaceable class="parameter">extension_name</replaceable></term> + <term><literal>DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term> + <term><literal>NO DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term> <listitem> <para> - The name of the extension that the function is to depend on. + This form marks the function as dependent on the extension, or no longer + dependent on that extension if <literal>NO</literal> is specified. + A function that's marked as dependent on an extension is automatically + dropped when the extension is dropped. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml index 6d34dbb74e..de6f89d458 100644 --- a/doc/src/sgml/ref/alter_index.sgml +++ b/doc/src/sgml/ref/alter_index.sgml @@ -100,11 +100,14 @@ ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> </varlistentry> <varlistentry> - <term><literal>DEPENDS ON EXTENSION</literal></term> + <term><literal>DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term> + <term><literal>NO DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term> <listitem> <para> - This form marks the index as dependent on the extension, such that if the - extension is dropped, the index will automatically be dropped as well. + This form marks the index as dependent on the extension, or no longer + dependent on that extension if <literal>NO</literal> is specified. + An index that's marked as dependent on an extension is automatically + dropped when the extension is dropped. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/alter_materialized_view.sgml b/doc/src/sgml/ref/alter_materialized_view.sgml index 03e3df1ffd..9df8a79977 100644 --- a/doc/src/sgml/ref/alter_materialized_view.sgml +++ b/doc/src/sgml/ref/alter_materialized_view.sgml @@ -68,12 +68,6 @@ ALTER MATERIALIZED VIEW ALL IN TABLESPACE <replaceable class="parameter">name</r anyway.) </para> - <para> - The <literal>DEPENDS ON EXTENSION</literal> form marks the materialized view - as dependent on an extension, such that the materialized view will - automatically be dropped if the extension is dropped. - </para> - <para> The statement subforms and actions available for <command>ALTER MATERIALIZED VIEW</command> are a subset of those available @@ -110,7 +104,10 @@ ALTER MATERIALIZED VIEW ALL IN TABLESPACE <replaceable class="parameter">name</r <term><replaceable class="parameter">extension_name</replaceable></term> <listitem> <para> - The name of the extension that the materialized view is to depend on. + The name of the extension that the materialized view is to depend on (or no longer + dependent on, if <literal>NO</literal> is specified). A materialized view + that's marked as dependent on an extension is automatically dropped when + the extension is dropped. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/alter_trigger.sgml b/doc/src/sgml/ref/alter_trigger.sgml index 6cf789a67a..6d4784c82f 100644 --- a/doc/src/sgml/ref/alter_trigger.sgml +++ b/doc/src/sgml/ref/alter_trigger.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> ALTER TRIGGER <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable> -ALTER TRIGGER <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable> DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable> +ALTER TRIGGER <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable> [ NO ] DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable> </synopsis> </refsynopsisdiv> @@ -78,7 +78,10 @@ ALTER TRIGGER <replaceable class="parameter">name</replaceable> ON <replaceable <term><replaceable class="parameter">extension_name</replaceable></term> <listitem> <para> - The name of the extension that the trigger is to depend on. + The name of the extension that the trigger is to depend on (or no longer + dependent on, if <literal>NO</literal> is specified). A trigger + that's marked as dependent on an extension is automatically dropped when + the extension is dropped. </para> </listitem> </varlistentry> diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.c index 596dafe19c..fa38ee9477 100644 --- a/src/backend/catalog/pg_depend.c +++ b/src/backend/catalog/pg_depend.c @@ -278,6 +278,55 @@ deleteDependencyRecordsForClass(Oid classId, Oid objectId, return count; } +/* + * deleteDependencyRecordsForSpecific -- delete all records with given depender + * classId/objectId, dependee classId/objectId, of the given deptype. + * Returns the number of records deleted. + */ +long +deleteDependencyRecordsForSpecific(Oid classId, Oid objectId, char deptype, + Oid refclassId, Oid refobjectId) +{ + long count = 0; + Relation depRel; + ScanKeyData key[2]; + SysScanDesc scan; + HeapTuple tup; + + depRel = table_open(DependRelationId, RowExclusiveLock); + + ScanKeyInit(&key[0], + Anum_pg_depend_classid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(classId)); + ScanKeyInit(&key[1], + Anum_pg_depend_objid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(objectId)); + + scan = systable_beginscan(depRel, DependDependerIndexId, true, + NULL, 2, key); + + while (HeapTupleIsValid(tup = systable_getnext(scan))) + { + Form_pg_depend depform = (Form_pg_depend) GETSTRUCT(tup); + + if (depform->refclassid == refclassId && + depform->refobjid == refobjectId && + depform->deptype == deptype) + { + CatalogTupleDelete(depRel, &tup->t_self); + count++; + } + } + + systable_endscan(scan); + + table_close(depRel, RowExclusiveLock); + + return count; +} + /* * Adjust dependency record(s) to point to a different object of the same type * diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c index 11db9bfe92..57edea979b 100644 --- a/src/backend/commands/alter.c +++ b/src/backend/commands/alter.c @@ -421,7 +421,7 @@ ExecRenameStmt(RenameStmt *stmt) } /* - * Executes an ALTER OBJECT / DEPENDS ON [EXTENSION] statement. + * Executes an ALTER OBJECT / [DROP] DEPENDS ON EXTENSION statement. * * Return value is the address of the altered object. refAddress is an output * argument which, if not null, receives the address of the object that the @@ -433,7 +433,6 @@ ExecAlterObjectDependsStmt(AlterObjectDependsStmt *stmt, ObjectAddress *refAddre ObjectAddress address; ObjectAddress refAddr; Relation rel; - List *currexts; address = get_object_address_rv(stmt->objectType, stmt->relation, (List *) stmt->object, @@ -463,11 +462,22 @@ ExecAlterObjectDependsStmt(AlterObjectDependsStmt *stmt, ObjectAddress *refAddre if (refAddress) *refAddress = refAddr; - /* Avoid duplicates */ - currexts = getAutoExtensionsOfObject(address.classId, - address.objectId); - if (!list_member_oid(currexts, refAddr.objectId)) - recordDependencyOn(&address, &refAddr, DEPENDENCY_AUTO_EXTENSION); + if (stmt->remove) + { + deleteDependencyRecordsForSpecific(address.classId, address.objectId, + DEPENDENCY_AUTO_EXTENSION, + refAddr.classId, refAddr.objectId); + } + else + { + List *currexts; + + /* Avoid duplicates */ + currexts = getAutoExtensionsOfObject(address.classId, + address.objectId); + if (!list_member_oid(currexts, refAddr.objectId)) + recordDependencyOn(&address, &refAddr, DEPENDENCY_AUTO_EXTENSION); + } return address; } diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 1525c0de72..491452ae2d 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3638,6 +3638,7 @@ _copyAlterObjectDependsStmt(const AlterObjectDependsStmt *from) COPY_NODE_FIELD(relation); COPY_NODE_FIELD(object); COPY_NODE_FIELD(extname); + COPY_SCALAR_FIELD(remove); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 4f34189ab5..8408c28ec6 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1449,6 +1449,7 @@ _equalAlterObjectDependsStmt(const AlterObjectDependsStmt *a, const AlterObjectD COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(object); COMPARE_NODE_FIELD(extname); + COMPARE_SCALAR_FIELD(remove); return true; } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 1219ac8c26..3c78f2d1b5 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -320,7 +320,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <list> vac_analyze_option_list %type <node> vac_analyze_option_arg %type <defelt> drop_option -%type <boolean> opt_or_replace +%type <boolean> opt_or_replace opt_no opt_grant_grant_option opt_grant_admin_option opt_nowait opt_if_exists opt_with_data opt_transaction_chain @@ -9053,57 +9053,67 @@ opt_set_data: SET DATA_P { $$ = 1; } *****************************************************************************/ AlterObjectDependsStmt: - ALTER FUNCTION function_with_argtypes DEPENDS ON EXTENSION name + ALTER FUNCTION function_with_argtypes opt_no DEPENDS ON EXTENSION name { AlterObjectDependsStmt *n = makeNode(AlterObjectDependsStmt); n->objectType = OBJECT_FUNCTION; n->object = (Node *) $3; - n->extname = makeString($7); + n->extname = makeString($8); + n->remove = $4; $$ = (Node *)n; } - | ALTER PROCEDURE function_with_argtypes DEPENDS ON EXTENSION name + | ALTER PROCEDURE function_with_argtypes opt_no DEPENDS ON EXTENSION name { AlterObjectDependsStmt *n = makeNode(AlterObjectDependsStmt); n->objectType = OBJECT_PROCEDURE; n->object = (Node *) $3; - n->extname = makeString($7); + n->extname = makeString($8); + n->remove = $4; $$ = (Node *)n; } - | ALTER ROUTINE function_with_argtypes DEPENDS ON EXTENSION name + | ALTER ROUTINE function_with_argtypes opt_no DEPENDS ON EXTENSION name { AlterObjectDependsStmt *n = makeNode(AlterObjectDependsStmt); n->objectType = OBJECT_ROUTINE; n->object = (Node *) $3; - n->extname = makeString($7); + n->extname = makeString($8); + n->remove = $4; $$ = (Node *)n; } - | ALTER TRIGGER name ON qualified_name DEPENDS ON EXTENSION name + | ALTER TRIGGER name ON qualified_name opt_no DEPENDS ON EXTENSION name { AlterObjectDependsStmt *n = makeNode(AlterObjectDependsStmt); n->objectType = OBJECT_TRIGGER; n->relation = $5; n->object = (Node *) list_make1(makeString($3)); - n->extname = makeString($9); + n->extname = makeString($10); + n->remove = $6; $$ = (Node *)n; } - | ALTER MATERIALIZED VIEW qualified_name DEPENDS ON EXTENSION name + | ALTER MATERIALIZED VIEW qualified_name opt_no DEPENDS ON EXTENSION name { AlterObjectDependsStmt *n = makeNode(AlterObjectDependsStmt); n->objectType = OBJECT_MATVIEW; n->relation = $4; - n->extname = makeString($8); + n->extname = makeString($9); + n->remove = $5; $$ = (Node *)n; } - | ALTER INDEX qualified_name DEPENDS ON EXTENSION name + | ALTER INDEX qualified_name opt_no DEPENDS ON EXTENSION name { AlterObjectDependsStmt *n = makeNode(AlterObjectDependsStmt); n->objectType = OBJECT_INDEX; n->relation = $3; - n->extname = makeString($7); + n->extname = makeString($8); + n->remove = $4; $$ = (Node *)n; } ; +opt_no: NO { $$ = true; } + | /* EMPTY */ { $$ = false; } + ; + /***************************************************************************** * * ALTER THING name SET SCHEMA name diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h index ab5e92bdc6..2c6abe26a5 100644 --- a/src/include/catalog/dependency.h +++ b/src/include/catalog/dependency.h @@ -196,6 +196,10 @@ extern long deleteDependencyRecordsFor(Oid classId, Oid objectId, extern long deleteDependencyRecordsForClass(Oid classId, Oid objectId, Oid refclassId, char deptype); +extern long deleteDependencyRecordsForSpecific(Oid classId, Oid objectId, + char deptype, + Oid refclassId, Oid refobjectId); + extern long changeDependencyFor(Oid classId, Oid objectId, Oid refClassId, Oid oldRefObjectId, Oid newRefObjectId); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 518abe42c1..5e1ffafb91 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2936,6 +2936,7 @@ typedef struct AlterObjectDependsStmt RangeVar *relation; /* in case a table is involved */ Node *object; /* name of the object */ Value *extname; /* extension name */ + bool remove; /* set true to remove dep rather than add */ } AlterObjectDependsStmt; /* ---------------------- diff --git a/src/test/modules/test_extensions/expected/test_extdepend.out b/src/test/modules/test_extensions/expected/test_extdepend.out index 40533e90de..23ab5ecfa2 100644 --- a/src/test/modules/test_extensions/expected/test_extdepend.out +++ b/src/test/modules/test_extensions/expected/test_extdepend.out @@ -149,6 +149,41 @@ SELECT deptype, i.* ---------+------+--------+------+---------- (0 rows) -DROP TABLE a; +RESET search_path; DROP SCHEMA test_ext CASCADE; -NOTICE: drop cascades to extension test_ext5 +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to extension test_ext5 +drop cascades to table test_ext.a +-- Fourth test: we can mark the objects as dependent, then unmark; then the +-- drop of the extension does nothing +SELECT * FROM test_extdep_commands \gexec + CREATE SCHEMA test_ext + CREATE EXTENSION test_ext5 SCHEMA test_ext + SET search_path TO test_ext + CREATE TABLE a (a1 int) + + CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS + $$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$ + ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5 + + CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b() + ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5 + + CREATE MATERIALIZED VIEW d AS SELECT * FROM a + ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5 + + CREATE INDEX e ON a (a1) + ALTER INDEX e DEPENDS ON EXTENSION test_ext5 + RESET search_path +SET search_path TO test_ext; +ALTER FUNCTION b() NO DEPENDS ON EXTENSION test_ext5; +ALTER TRIGGER c ON a NO DEPENDS ON EXTENSION test_ext5; +ALTER MATERIALIZED VIEW d NO DEPENDS ON EXTENSION test_ext5; +ALTER INDEX e NO DEPENDS ON EXTENSION test_ext5; +DROP EXTENSION test_ext5; +DROP TRIGGER c ON a; +DROP FUNCTION b(); +DROP MATERIALIZED VIEW d; +DROP INDEX e; +DROP SCHEMA test_ext CASCADE; +NOTICE: drop cascades to table a diff --git a/src/test/modules/test_extensions/sql/test_extdepend.sql b/src/test/modules/test_extensions/sql/test_extdepend.sql index cc170ab709..54600ef56c 100644 --- a/src/test/modules/test_extensions/sql/test_extdepend.sql +++ b/src/test/modules/test_extensions/sql/test_extdepend.sql @@ -70,6 +70,20 @@ SELECT deptype, i.* refobjid=(SELECT oid FROM pg_extension WHERE extname='test_ext5')) OR (refclassid='pg_class'::regclass AND refobjid='test_ext.a'::regclass) AND NOT deptype IN ('i', 'a'); - -DROP TABLE a; +RESET search_path; +DROP SCHEMA test_ext CASCADE; + +-- Fourth test: we can mark the objects as dependent, then unmark; then the +-- drop of the extension does nothing +SELECT * FROM test_extdep_commands \gexec +SET search_path TO test_ext; +ALTER FUNCTION b() NO DEPENDS ON EXTENSION test_ext5; +ALTER TRIGGER c ON a NO DEPENDS ON EXTENSION test_ext5; +ALTER MATERIALIZED VIEW d NO DEPENDS ON EXTENSION test_ext5; +ALTER INDEX e NO DEPENDS ON EXTENSION test_ext5; +DROP EXTENSION test_ext5; +DROP TRIGGER c ON a; +DROP FUNCTION b(); +DROP MATERIALIZED VIEW d; +DROP INDEX e; DROP SCHEMA test_ext CASCADE; -- 2.20.1
>From 5ed871d60073a71795ebcd14f033b8745921bcf5 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera <alvhe...@alvh.no-ip.org> Date: Thu, 9 Apr 2020 18:57:53 -0400 Subject: [PATCH v2 2/2] Add tab-completion for ALTER INDEX .. [NO] DEPENDS ON We'd like to have tab-completion for the other object types too, but they don't have sub-command completion yet. Author: Ibrar Ahmed <ibrar.ah...@gmail.com> Discussion: https://postgr.es/m/caltqxtcogrfevp9uou5vftngsn+vhzuu9+9a0inarfyvohs...@mail.gmail.com --- src/bin/psql/tab-complete.c | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 0e7a373caf..f6fd623c98 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1709,7 +1709,7 @@ psql_completion(const char *text, int start, int end) /* ALTER INDEX <name> */ else if (Matches("ALTER", "INDEX", MatchAny)) COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME TO", "SET", - "RESET", "ATTACH PARTITION"); + "RESET", "ATTACH PARTITION", "DEPENDS", "NO DEPENDS"); else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH")) COMPLETE_WITH("PARTITION"); else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH", "PARTITION")) @@ -1755,6 +1755,10 @@ psql_completion(const char *text, int start, int end) "buffering =", /* GiST */ "pages_per_range =", "autosummarize =" /* BRIN */ ); + else if (Matches("ALTER", "INDEX", MatchAny, "NO", "DEPENDS")) + COMPLETE_WITH("ON EXTENSION"); + else if (Matches("ALTER", "INDEX", MatchAny, "DEPENDS")) + COMPLETE_WITH("ON EXTENSION"); /* ALTER LANGUAGE <name> */ else if (Matches("ALTER", "LANGUAGE", MatchAny)) -- 2.20.1
