[PATCH] Foxy Fixtures

Looks very interesting – good job.

Seems the patch is missing documentation, though that is available for now through the link above.

Fixtures.identify is used in the test fixtures, and that method is documented where defined, but it's not mentioned in the linked README. Perhaps it should be?

Patch also overrides disable_referential_integrity(&block) for mysql_adapter only, so I suppose someone with the full set of DBMSs should get it working with the other bundled adapters.

Tests (tried MySQL only) do pass, anyway.

Thanks for the comments, Henrik. The updated patch adds documentation and full PostgreSQL support.

(In [8036]) Foxy fixtures. Adapter#disable_referential_integrity. Closes #9981.

While I like the way constraints are disabled for postgresql (a lot better than my way of having to make all FK's deferrable and then defer all constraints in a transaction when loading fixtues) one should be aware that this means that the postgresql user that is used for tests must have superuser privileges or the tests may fail with "ActiveRecord::StatementInvalid: PGError: ERROR: permission denied: "RI_ConstraintTrigger_2089822" is a system trigger".

This really is a problem. Because of the environment I'm running in, I cannot have the user be a superuser. It was bad enough getting createdb privileges. There's no way I'm going to get superuser privs. Does anyone have a workaround for this? Unless there is one, I'm effectively stuck at r8035.

Replying to vertebrate:

This really is a problem. Because of the environment I'm running in, I cannot have the user be a superuser. It was bad enough getting createdb privileges. There's no way I'm going to get superuser privs. Does anyone have a workaround for this? Unless there is one, I'm effectively stuck at r8035.

Try this: http://schuerig.de/michael/blog/index.php/2007/11/08/testing-with-foreign-key-constraints/

Basically you'll need to make sure all your FK constraints are deferrable and patch the fixture loading code to defer all constraints. The added benefit (unlike when using the current Foxy Fixtures loading method) is that the FK constraints are actually checked at the end of the transaction and thus the data is guaranteed to actually pass all its constraints.

The trouble is that this method can not easily be added to core rails as it requires foreign keys to be created in a specific way and Rails has no built-in support for managing foreign keys.

Replying to tarmo:

Try this: http://schuerig.de/michael/blog/index.php/2007/11/08/testing-with-foreign-key-constraints/ Basically you'll need to make sure all your FK constraints are deferrable and patch the fixture loading code to defer all constraints. The added benefit (unlike when using the current Foxy Fixtures loading method) is that the FK constraints are actually checked at the end of the transaction and thus the data is guaranteed to actually pass all its constraints. The trouble is that this method can not easily be added to core rails as it requires foreign keys to be created in a specific way and Rails has no built-in support for managing foreign keys.

Thanks for the link. That would only help though if foxy fixtures hadn't been applied, right? Basically with foxy fixtures, I have to go in and hack up the postgres adapter to remove the innards of disable_referential_integrity as well as defer FK checks inside a transaction. It sounds like maybe foxy fixtures shouldn't have been applied given that you can't realistically expect people to always be able to have superuser access.

It does not matter if foxy fixtures is available or not, you'd still have to patch (or hotpatch) rails to enable fk constraint deferring. I still think though that the foxy fixtures approach while broken for (hopefully) a small minority of users is a lot better for the majority of users compared to the old approach. We just need to find a workaround for the minority. Perhaps not disabling the triggers when there are no supseruser privileges (no idea how to detect that) and letting the user figure out how to reorder the fixtures so they'd load properly or let the user use some alternative way to load fixtures.

I only say it's because of foxy fixtures because it was that change that introduced the problem. Yes, it does fix some problems, and does look good overall. The real solution is for rails to locate the fk constraints, drop them, and then recreate them. I know it won't happen because of the mindset of the rails maintainers, but it really is the correct way to do it. For a workaround I like the idea of disabling if it's a super user, could do something like "select count(*) from pg_roles where rolname = 'appuser' and rolsuper = true;".

I guess the question is where would you put your fixture sequence data? Given that you could have fixtures for say test::unit, and others for rspec, perhaps a fixture_sequence file in the respective fixture dir. Could just create an array of fixture names, then some generic code in the fixture loading to find the needed fixture, and load any dependencies earlier in the chain.

Replying to vertebrate:

I only say it's because of foxy fixtures because it was that change that introduced the problem. Yes, it does fix some problems, and does look good overall. The real solution is for rails to locate the fk constraints, drop them, and then recreate them. I know it won't happen because of the mindset of the rails maintainers, but it really is the correct way to do it. For a workaround I like the idea of disabling if it's a super user, could do something like "select count(*) from pg_roles where rolname = 'appuser' and rolsuper = true;".

Please submit a patch. We don't use information_schema for the same reason: it's only accessible to superusers. But this is an issue in *production* not development. Why are you developing on a database without superuser access?

Regarding mindset, do remove the tinfoil hat. It's unneeded here.

bitsweat: Not sure what to submit a patch for. Yes it could stop the drop/adds, but I have no idea how to go about handling the fixture sequencing and such that would be required. Regarding information_schema views, they do not require a superuser. In postgres SELECT rights are granted to PUBLIC on information_schema views. Maybe on some other DBs it requires a super user? If you were mistaken about information_schema perms I could work on a patch to drop/add FK constraints, which would alleviate the need to come up with some crazy fixture sequencing elsewhere. If you're not mistaken, then I'm not sure at the moment how best to proceed.

As for why no superuser access...it's like developing any other application as a super user. Often when you release to production where you have restricted rights, you find that things don't work as expected.

I'm suggesting a patch to drop/add FK constraints without superuser privileges.

A good start would be some means to introspect the foreign key constraints. Two possible next steps:

1. mass enable/disable them, or
2. use tsort to sort them by dependency and do the fixture inserts in reverse order

I started working on a way to tsort them, which was going great until I came across a self-referencing table. That pretty much throws the whole dependency attempt out the window. So that leaves the drop/add. Drop of course is easy. For adds though since there's no builtin method of adding foreign keys, I'm sure it would effectively be duplicating parts of the current RedHill Consulting work, which is already quite tested compared to a brand new patch. Obviously there can't really be a plugin dependency for regular rails functionality. What would the preference be? Consider the RedHill work for merge into rails, or create a patch on its own, writing the FK functionality from scratch? Just not sure what the preferred rails way would be.

If RedHill has already done then introspection work, then by all means let's merge it.

The RedHill work as you may know also includes foreign key support in migrations, as well as for the schema dumper. Should I be looking to merge all of the FK support they have, or just cherry pick the relevant parts to this fix?

You have the need and can best determine the shortest path to fulfilling it. Put together a well-tested patch that solves your problem; you don't need to ask questions or permission at every step of the way.

That was the last question I had. :) Just wanted to make sure I did things in the most accepted way, rather than make something that was lacking, or completely overkill. Thanks for the answers.

To defer constraints on PostgreSQL you have to define the FKs as deferrable; then you can just override the method to disable RI as such.

module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter
      def disable_referential_integrity(&block)
        transaction {
          begin
            execute "SET CONSTRAINTS ALL DEFERRED"
            yield
          ensure
            execute "SET CONSTRAINTS ALL IMMEDIATE"
          end
        }
      end
    end
  end
end    

Replying to bwalding:

To defer constraints on PostgreSQL you have to define the FKs as deferrable; then you can just override the method to disable RI as such.

And unlike disabling all triggers this method does not disable unique index checks which may also allow invalid fixtures to be loaded because the index check is not run after the data has been loaded.

Replying to tarmo:

Replying to bwalding:

To defer constraints on PostgreSQL you have to define the FKs as deferrable; then you can just override the method to disable RI as such.

And unlike disabling all triggers this method does not disable unique index checks which may also allow invalid fixtures to be loaded because the index check is not run after the data has been loaded.

Aye, I much prefer this method. When I said "disable RI"; it's only a temporary disable too - once the transaction commits, everything is verified. It does make writing fixtures more painful however (as you have to fill in all the keys etc). But that's just another trade-off.