Hi all,
Here is a first patch to allow these commands.
> ALTER TABLE <table> ENABLE TRIGGER <trigname>
> ALTER TABLE <table> DISABLE TRIGGER <trigname>
Bruce said to allow them only super-user,
but currently this patch allows also the table owner.
If we need to restrict these operations,
we have to add more user checks.
> From: Bruce Momjian <[email protected]>
> Date: 2005/06/29 20:49
> Subject: Re: [HACKERS] Open items
> To: Satoshi Nagayasu <[EMAIL PROTECTED]>
> Cc: PostgreSQL-development <[email protected]>
>
>
> Satoshi Nagayasu wrote:
>
>>How about enable/disable triggers?
>>
>>>From TODO:
>>
>>>Allow triggers to be disabled.
>>
>>http://momjian.postgresql.org/cgi-bin/pgtodo?trigger
>>
>>I think this is good for COPY performance improvement.
>>
>>Now I have user functions to enable/disable triggers, not DDL.
>>It modifies system tables.
>>But I can rewrite this as a DDL. (ALTER TABLE?)
>
>
> Yea, it is a TODO item, and should be pretty straight-forward to code,
> so sure, go ahead.
>
> It has to be something that is super-user-only.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> [email protected] | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
NAGAYASU Satoshi <[EMAIL PROTECTED]>
diff -ru pgsql.orig/src/backend/commands/tablecmds.c
pgsql/src/backend/commands/tablecmds.c
--- pgsql.orig/src/backend/commands/tablecmds.c 2005-06-28 14:08:54.000000000
+0900
+++ pgsql/src/backend/commands/tablecmds.c 2005-07-01 15:50:27.000000000
+0900
@@ -236,6 +236,8 @@
Oid newOwnerId);
static void ATExecClusterOn(Relation rel, const char *indexName);
static void ATExecDropCluster(Relation rel);
+static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
+ bool
enable);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
@@ -1993,6 +1995,11 @@
}
pass = AT_PASS_DROP;
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ case AT_DisableTrig: /* DISABLE TRIGGER */
+ ATSimplePermissions(rel, false);
+ pass = AT_PASS_MISC;
+ break;
case AT_SetTableSpace: /* SET TABLESPACE */
/* This command never recurses */
ATPrepSetTableSpace(tab, rel, cmd->name);
@@ -2155,6 +2162,12 @@
* Nothing to do here; Phase 3 does the work
*/
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd->name, true);
+ break;
+ case AT_DisableTrig: /* DISABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd->name, false);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -5465,6 +5478,15 @@
}
/*
+ * ALTER TABLE ENABLE/DISABLE TRIGGER
+ */
+static void
+ATExecEnableDisableTrigger(Relation rel, char *trigname, bool enable)
+{
+ EnableDisableTrigger(rel, trigname, enable);
+}
+
+/*
* ALTER TABLE SET TABLESPACE
*/
static void
diff -ru pgsql.orig/src/backend/commands/trigger.c
pgsql/src/backend/commands/trigger.c
--- pgsql.orig/src/backend/commands/trigger.c 2005-05-30 16:20:58.000000000
+0900
+++ pgsql/src/backend/commands/trigger.c 2005-07-01 15:53:45.000000000
+0900
@@ -3063,3 +3063,74 @@
afterTriggerAddEvent(new_event);
}
}
+
+/* ----------
+ * EnableDisableTrigger()
+ *
+ * Called by ALTER TABLE ENABLE/DISABLE TRIGGER
+ * to change 'tgenabled' flag in the pg_trigger.
+ * ----------
+ */
+void
+EnableDisableTrigger(Relation rel, const char *tgname, bool enable)
+{
+ Relation tgrel;
+ SysScanDesc tgscan;
+ ScanKeyData keys[2];
+ HeapTuple tuple;
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+
+ if (!allowSystemTableMods && IsSystemRelation(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system
catalog",
+ RelationGetRelationName(rel))));
+
+ tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+
+ ScanKeyInit(&keys[0],
+ Anum_pg_trigger_tgrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&keys[1],
+ Anum_pg_trigger_tgname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(tgname));
+
+ tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
+ SnapshotNow, 1,
keys);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(tgscan)))
+ {
+ HeapTuple newtup = heap_copytuple(tuple);
+ Form_pg_trigger pg_trigger = (Form_pg_trigger)
GETSTRUCT(newtup);
+
+ if ( pg_trigger->tgenabled==true && enable==false )
+ {
+ pg_trigger->tgenabled = false;
+ }
+ else if ( pg_trigger->tgenabled==false && enable==true )
+ {
+ pg_trigger->tgenabled = true;
+ }
+
+ simple_heap_update(tgrel, &newtup->t_self, newtup);
+
+ /* Keep catalog indexes current */
+ CatalogUpdateIndexes(tgrel, newtup);
+
+ heap_freetuple(newtup);
+ }
+ systable_endscan(tgscan);
+
+ heap_close(tgrel, RowExclusiveLock);
+
+ CommandCounterIncrement();
+
+ FreeTriggerDesc(rel->trigdesc);
+ RelationBuildTriggers(rel);
+}
diff -ru pgsql.orig/src/backend/parser/gram.y pgsql/src/backend/parser/gram.y
--- pgsql.orig/src/backend/parser/gram.y 2005-06-30 05:34:13.000000000
+0900
+++ pgsql/src/backend/parser/gram.y 2005-07-01 14:21:25.000000000 +0900
@@ -348,9 +348,9 @@
DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS
- DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP
+ DESC DISABLE DISTINCT DO DOMAIN_P DOUBLE_P DROP
- EACH ELSE ENCODING ENCRYPTED END_P ESCAPE EXCEPT EXCLUDING
+ EACH ELSE ENABLE ENCODING ENCRYPTED END_P ESCAPE EXCEPT EXCLUDING
EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
@@ -1389,6 +1389,22 @@
n->name = NULL;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> ENABLE TRIGGER <trig> */
+ | ENABLE TRIGGER name
+ {
+ AlterTableCmd *n =
makeNode(AlterTableCmd);
+ n->subtype = AT_EnableTrig;
+ n->name = $3;
+ $$ = (Node *)n;
+ }
+ /* ALTER TABLE <name> DISABLE TRIGGER <trig> */
+ | DISABLE TRIGGER name
+ {
+ AlterTableCmd *n =
makeNode(AlterTableCmd);
+ n->subtype = AT_DisableTrig;
+ n->name = $3;
+ $$ = (Node *)n;
+ }
| alter_rel_cmd
{
$$ = $1;
diff -ru pgsql.orig/src/backend/parser/keywords.c
pgsql/src/backend/parser/keywords.c
--- pgsql.orig/src/backend/parser/keywords.c 2005-06-30 05:34:14.000000000
+0900
+++ pgsql/src/backend/parser/keywords.c 2005-07-01 14:38:13.000000000 +0900
@@ -116,6 +116,7 @@
{"delimiter", DELIMITER},
{"delimiters", DELIMITERS},
{"desc", DESC},
+ {"disable", DISABLE},
{"distinct", DISTINCT},
{"do", DO},
{"domain", DOMAIN_P},
@@ -123,6 +124,7 @@
{"drop", DROP},
{"each", EACH},
{"else", ELSE},
+ {"enable", ENABLE},
{"encoding", ENCODING},
{"encrypted", ENCRYPTED},
{"end", END_P},
diff -ru pgsql.orig/src/include/commands/trigger.h
pgsql/src/include/commands/trigger.h
--- pgsql.orig/src/include/commands/trigger.h 2005-05-30 16:20:58.000000000
+0900
+++ pgsql/src/include/commands/trigger.h 2005-07-01 15:50:09.000000000
+0900
@@ -164,6 +164,7 @@
extern void AfterTriggerSetState(ConstraintsSetStmt *stmt);
+void EnableDisableTrigger(Relation rel, const char *tgname, bool enable);
/*
* in utils/adt/ri_triggers.c
diff -ru pgsql.orig/src/include/nodes/parsenodes.h
pgsql/src/include/nodes/parsenodes.h
--- pgsql.orig/src/include/nodes/parsenodes.h 2005-06-29 04:51:24.000000000
+0900
+++ pgsql/src/include/nodes/parsenodes.h 2005-07-01 14:20:14.000000000
+0900
@@ -822,6 +822,8 @@
AT_ClusterOn, /* CLUSTER ON */
AT_DropCluster, /* SET WITHOUT CLUSTER */
AT_DropOids, /* SET WITHOUT OIDS */
+ AT_EnableTrig, /* ENABLE TRIGGER */
+ AT_DisableTrig, /* DISABLE TRIGGER */
AT_SetTableSpace /* SET TABLESPACE */
} AlterTableType;
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
