rework `disable_referential_integrity` for PostgreSQL.

[Toby Ovod-Everett & Andrey Nering & Yves Senn]

Closes #17726.
Closes #10939.

This patch makes three distinct modifications:

1. no longer fall back to disabling user triggers if system triggers can't be disabled
2. warn the user when referential integrity can't be disabled
3. restore aborted transactions when referential integrity can't be disabled

The motivation behind these changes is to make the behavior of Rails
transparent and less error-prone. To require superuser privileges is not optimal
but it's what Rails currently needs. Users who absolutely rely on disabling user triggers
can patch `disable_referential_integrity`.

We should investigate `SET CONSTRAINTS` as a possible solution which does not require
superuser privileges.

/cc @matthewd

1 files changed, 28 insertions, 9 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb b/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb
index 52b307c432..c1835380f9 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb
@@ -8,20 +8,39 @@ module ActiveRecord
 
         def disable_referential_integrity # :nodoc:
           if supports_disable_referential_integrity?
+            original_exception = nil
+
             begin
-              execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";"))
-            rescue
-              execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER USER" }.join(";"))
+              transaction(requires_new: true) do
+                execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";"))
+              end
+            rescue => e
+              original_exception = e
             end
-          end
-          yield
-        ensure
-          if supports_disable_referential_integrity?
+
+            begin
+              yield
+            rescue ActiveRecord::InvalidForeignKey => e
+              warn <<-WARNING
+WARNING: Rails was not able to disable referential integrity.
+
+This is most likely caused due to missing permissions.
+Rails needs superuser privileges to disable referential integrity.
+
+    cause: #{original_exception.try(:message)}
+
+              WARNING
+              raise e
+            end
+
             begin
-              execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";"))
+              transaction(require_new: true) do
+                execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";"))
+              end
             rescue
-              execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER USER" }.join(";"))
             end
+          else
+            yield
           end
         end
       end