More helpful error messages in case of conflicting triggers

dvarrazzo · dvarrazzo · commit 1d62d8d0c5a2 · 2013-04-17T00:57:01.000+01:00
Closes issue #5.
diff --git a/bin/pg_repack.c b/bin/pg_repack.c
@@ -886,10 +886,35 @@ repack_one_table(const repack_table *table, const char *orderby)
 	res = execute("SELECT repack.conflicted_triggers($1)", 1, params);
 	if (PQntuples(res) > 0)
 	{
-		ereport(WARNING,
-			(errcode(E_PG_COMMAND),
-			 errmsg("trigger %s conflicted for %s",
+		if (0 == strcmp("z_repack_trigger", PQgetvalue(res, 0, 0)))
+		{
+			ereport(WARNING,
+				(errcode(E_PG_COMMAND),
+				 errmsg("the table \"%s\" has already a trigger called \"%s\"",
+					table->target_name, PQgetvalue(res, 0, 0)),
+				 errdetail(
+					"The trigger was probably installed during a previous"
+					" attempt to run pg_repack on the table which was"
+					" interrupted and for some reason failed to clean up"
+					" the temporary objects.  Please drop the trigger or drop"
+					" and recreate the pg_repack extension altogether"
+					" to remove all the temporary objects left over.")));
+		}
+		else
+		{
+			ereport(WARNING,
+				(errcode(E_PG_COMMAND),
+				 errmsg("trigger \"%s\" conflicting on table \"%s\"",
+					PQgetvalue(res, 0, 0), table->target_name),
+				 errdetail(
+					"The trigger \"z_repack_trigger\" must be the last of the"
+					" BEFORE triggers to fire on the table (triggers fire in"
+					" alphabetical order). Please rename the trigger so that"
+					" it sorts before \"z_repack_trigger\": you can use"
+					" \"ALTER TRIGGER %s ON %s RENAME TO newname\".",
 					PQgetvalue(res, 0, 0), table->target_name)));
+		}
+
 		have_error = true;
 		goto cleanup;
 	}
diff --git a/doc/pg_repack.rst b/doc/pg_repack.rst
@@ -329,21 +329,23 @@ ERROR: permission denied for schema repack
 
     pg_repack must be executed by a superuser.
 
-pg_repack: query failed: ERROR: trigger "z_repack_trigger" for relation "tbl" already exists
-    The target table has already a trigger named ``z_repack_trigger``.  This
-    is probably caused by a previous failed attempt to run pg_repack on the
-    table, which for some reason failed to clean up the temporary object.
+WARNING: the table "tbl" has already a trigger called z_repack_trigger
+    The trigger was probably installed during a previous attempt to run
+    pg_repack on the table which was interrupted and for some reason failed
+    to clean up the temporary objects.
 
     You can remove all the temporary objects by dropping and re-creating the
     extension: see the installation_ section for the details.
 
-pg_repack: trigger conflicted for tbl
+WARNING: trigger "trg" conflicting on table "tbl"
     The target table has a trigger whose name follows ``z_repack_trigger``
     in alphabetical order.
 
     The ``z_repack_trigger`` should be the last BEFORE trigger to fire.
     Please rename your trigger to that it sorts alphabetically before
-    pg_repack's one.
+    pg_repack's one; you can use::
+
+        ALTER TRIGGER zzz_my_trigger ON sometable RENAME TO yyy_my_trigger;
 
 
 Restrictions
@@ -402,6 +404,7 @@ Releases
 * pg_repack 1.2
 
   * Added --jobs option for parallel operation.
+  * More helpful error messages.
   * Bugfix: correctly handle key indexes with options such as DESC, NULL
     FIRST/LAST, COLLATE (pg_repack issue #3).
 
diff --git a/lib/pg_repack.sql.in b/lib/pg_repack.sql.in
@@ -217,6 +217,7 @@ CREATE FUNCTION repack.conflicted_triggers(oid) RETURNS SETOF name AS
 $$
 SELECT tgname FROM pg_trigger
  WHERE tgrelid = $1 AND tgname >= 'z_repack_trigger'
+ ORDER BY tgname;
 $$
 LANGUAGE sql STABLE STRICT;
 
