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, 89 insertions, 0 deletions

diff --git a/activerecord/test/cases/adapters/postgresql/referential_integrity_test.rb b/activerecord/test/cases/adapters/postgresql/referential_integrity_test.rb
new file mode 100644
index 0000000000..98291f1bbf
--- /dev/null
+++ b/activerecord/test/cases/adapters/postgresql/referential_integrity_test.rb
@@ -0,0 +1,89 @@
+require 'cases/helper'
+require 'support/connection_helper'
+
+class PostgreSQLReferentialIntegrityTest < ActiveRecord::TestCase
+  self.use_transactional_fixtures = false
+
+  include ConnectionHelper
+
+  module MissingSuperuserPrivileges
+    def execute(sql)
+      if sql.match(/DISABLE TRIGGER ALL/) || sql.match(/ENABLE TRIGGER ALL/)
+        super "BROKEN;" rescue nil # put transaction in broken state
+        raise ActiveRecord::StatementInvalid, 'PG::InsufficientPrivilege'
+      else
+        super
+      end
+    end
+  end
+
+  def setup
+    @connection = ActiveRecord::Base.connection
+  end
+
+  def teardown
+    reset_connection
+    if ActiveRecord::Base.connection.is_a?(MissingSuperuserPrivileges)
+      raise "MissingSuperuserPrivileges patch was not removed"
+    end
+  end
+
+  def test_should_reraise_invalid_foreign_key_exception_and_show_warning
+    @connection.extend MissingSuperuserPrivileges
+
+    warning = capture(:stderr) do
+      e = assert_raises(ActiveRecord::InvalidForeignKey) do
+        @connection.disable_referential_integrity do
+          raise ActiveRecord::InvalidForeignKey, 'Should be re-raised'
+        end
+      end
+      assert_equal 'Should be re-raised', e.message
+    end
+    assert_match (/WARNING: Rails was not able to disable referential integrity/), warning
+    assert_match (/cause: PG::InsufficientPrivilege/), warning
+  end
+
+  def test_does_not_print_warning_if_no_invalid_foreign_key_exception_was_raised
+    @connection.extend MissingSuperuserPrivileges
+
+    warning = capture(:stderr) do
+      e = assert_raises(ActiveRecord::StatementInvalid) do
+        @connection.disable_referential_integrity do
+          raise ActiveRecord::StatementInvalid, 'Should be re-raised'
+        end
+      end
+      assert_equal 'Should be re-raised', e.message
+    end
+    assert warning.blank?, "expected no warnings but got:\n#{warning}"
+  end
+
+  def test_does_not_break_transactions
+    @connection.extend MissingSuperuserPrivileges
+
+    @connection.transaction do
+      @connection.disable_referential_integrity do
+        assert_transaction_is_not_broken
+      end
+      assert_transaction_is_not_broken
+    end
+  end
+
+  def test_does_not_break_nested_transactions
+    @connection.extend MissingSuperuserPrivileges
+
+    @connection.transaction do
+      @connection.transaction(requires_new: true) do
+        @connection.disable_referential_integrity do
+          assert_transaction_is_not_broken
+        end
+      end
+      assert_transaction_is_not_broken
+    end
+  end
+
+  private
+
+  def assert_transaction_is_not_broken
+    assert_equal "1", @connection.select_value("SELECT 1")
+  end
+end