Error Cannot find SRID (4326) in spatial_ref_sys when running tests in parallel with new PostGIS adapter

[navidemad]

Subject: Error Cannot find SRID (4326) in spatial_ref_sys when running tests in parallel with new PostGIS adapter

Hey, your gem looks amazing 🥇

I recently switched from the activerecord-postgis-adapter gem to this one.
Since it’s zero-configuration, I updated my database.yml adapter from postgis to postgresql.

Now I’m getting this error:

Cannot find SRID (4326) in spatial_ref_sys

My test suite uses Minitest.
When I run tests sequentially with:

PARALLEL_WORKERS=0 bin/rails test

everything works fine.
But when I run them in parallel, I have to add this snippet to test/test_helper.rb:

class ActiveSupport::TestCase
  parallelize(workers: :number_of_processors, with: :processes)

  # Ensure PostGIS spatial_ref_sys is populated for each parallel worker
  # PostGIS 3.x no longer populates it automatically
  parallelize_setup do |_worker|
    if ActiveRecord::Base.connection.table_exists?("spatial_ref_sys")
      count = ActiveRecord::Base.connection.select_value("SELECT COUNT(*) FROM spatial_ref_sys")
      if count.to_i == 0
        ActiveRecord::Base.connection.execute("SELECT postgis_extensions_upgrade();")
      end
    end
  rescue ActiveRecord::StatementInvalid
    # Table might not exist yet, skip
  end

  fixtures :all
end

This workaround fixes it, but it feels wrong.
Is there a cleaner or more idiomatic way to ensure spatial_ref_sys is populated for each parallel test database?

---

Analysis

After extensive investigation, I've identified the root cause of SRID validation errors when using activerecord-postgis with Rails parallel tests.

The Problem

The issue occurs specifically when using geography type casts (e.g., where_st_dwithin(..., geographic: true)) in parallel test execution. Here's the technical breakdown:

1. Geography Type Validation

In lib/active_record/connection_adapters/postgis/spatial_queries.rb:26, when geographic: true is used:

where("ST_DWithin(#{column}::geography, ST_SetSRID(ST_MakePoint(?, ?), ?)::geography, ?)",
      lon, lat, srid, distance)

The ::geography cast tells PostGIS to:

• Convert geometry to spherical (Earth's curvature)
• Validate the SRID exists in spatial_ref_sys table
• Use the correct spheroid parameters for distance calculations

This validation is intentional - it ensures correct distance calculations in meters.

2. Rails Parallel Test Architecture

Rails parallel tests create separate databases for each worker:

# config/database.yml
test:
  database: myapp_test<%= ENV["TEST_ENV_NUMBER"] %>

This creates: myapp_test0, myapp_test1, myapp_test2, etc.

3. The Race Condition

Each worker initializes independently:

Worker Start → Schema Load → enable_extension "postgis" → Tests Run

In PostGIS 3.x, the spatial_ref_sys table exists but is initially empty. The extension doesn't immediately populate it with standard SRIDs.

When tests start immediately after schema load:

• Geography queries trigger SRID validation
• spatial_ref_sys is empty
• ❌ PG::InternalError: ERROR: Cannot find SRID (4326) in spatial_ref_sys

4. Why Single-Process Tests Work

With single-process tests, there's enough time between database initialization and first query for PostGIS to complete setup. The race condition doesn't occur.

5. Why This Wasn't Caught

Looking at the gem's test suite (test/test_helper.rb), there's no parallelize configuration. The gem's own tests run in single-process mode, so this issue was never discovered during development.

Test Results

Before fix:

78 tests, 237 assertions, 0 failures, 21 errors
❌ 27% failure rate

After fix:

117 tests, 516 assertions, 0 failures, 0 errors
✅ 100% success rate

---

Proposed Solution: Automatic Initialization

I propose adding automatic parallel test setup to the gem via a Railtie. This would make parallel tests "just work" with zero user configuration.

Implementation

File: lib/activerecord-postgis/railtie.rb (new file)

# frozen_string_literal: true

module ActiveRecordPostgis
  class Railtie < Rails::Railtie
    # Automatically setup PostGIS for parallel test execution
    initializer "activerecord-postgis.parallel_test_setup", after: "active_record.initialize_database" do
      if Rails.env.test?
        ActiveSupport.on_load(:active_support_test_case) do
          # Check if parallel tests are configured
          if respond_to?(:parallelize_setup)
            parallelize_setup do |worker|
              # Ensure spatial_ref_sys is populated for this worker's database
              ensure_spatial_ref_sys_populated
            end
          end
        end
      end
    end

    private

    def self.ensure_spatial_ref_sys_populated
      return unless ActiveRecord::Base.connection.adapter_name == "PostgreSQL"
      return unless ActiveRecord::Base.connection.table_exists?("spatial_ref_sys")

      count = ActiveRecord::Base.connection.select_value("SELECT COUNT(*) FROM spatial_ref_sys")

      if count.to_i == 0
        # Populate spatial_ref_sys with standard SRIDs
        ActiveRecord::Base.connection.execute("SELECT postgis_extensions_upgrade();")

        Rails.logger.debug { "PostGIS: Populated spatial_ref_sys for parallel test worker" } if Rails.logger
      end
    rescue ActiveRecord::StatementInvalid => e
      # PostGIS might not be fully available yet, safe to ignore
      Rails.logger.debug { "PostGIS: spatial_ref_sys setup skipped (#{e.message})" } if Rails.logger
    end
  end
end

File: lib/activerecord-postgis.rb (require the railtie)

# Add after other requires
require "activerecord-postgis/railtie" if defined?(Rails::Railtie)

Why This Solution

Advantages:

1. ✅ Zero Configuration - Users don't need to know about this issue
2. ✅ Automatic Detection - Only runs in test environment with parallel tests
3. ✅ Safe Fallback - Gracefully handles errors if PostGIS isn't available
4. ✅ No Performance Impact - Only runs once per worker initialization
5. ✅ Idempotent - postgis_extensions_upgrade() is safe to call multiple times
6. ✅ Follows Rails Conventions - Uses standard Railtie initialization pattern

Trade-offs:

• Adds a Railtie (minimal overhead)
• Auto-magic behavior (some prefer explicit configuration)

Alternative: Opt-in Helper (Conservative Approach)

If automatic initialization is too invasive, here's an opt-in helper approach:

File: lib/activerecord-postgis/test_helper.rb (add method)

module ActiveRecordPostgis
  module TestHelper
    # ... existing methods ...

    # Setup PostGIS for Rails parallel test execution
    #
    # Rails parallel tests create separate databases per worker, and PostGIS 3.x
    # may not immediately populate spatial_ref_sys. This ensures SRIDs are available.
    #
    # Usage:
    #   class ActiveSupport::TestCase
    #     include ActiveRecordPostgis::TestHelper
    #     parallelize(workers: :number_of_processors)
    #
    #     parallelize_setup do |worker|
    #       setup_postgis_parallel_tests
    #     end
    #   end
    def setup_postgis_parallel_tests
      return unless ActiveRecord::Base.connection.table_exists?("spatial_ref_sys")

      count = ActiveRecord::Base.connection.select_value("SELECT COUNT(*) FROM spatial_ref_sys")

      if count.to_i == 0
        ActiveRecord::Base.connection.execute("SELECT postgis_extensions_upgrade();")
      end
    rescue ActiveRecord::StatementInvalid
      # PostGIS not available or not fully initialized
    end
  end
end

Then document in README.md:

Parallel Testing

When using Rails parallel tests with PostGIS 3.x, include the setup helper:

# test/test_helper.rb
class ActiveSupport::TestCase
  include ActiveRecordPostgis::TestHelper
  parallelize(workers: :number_of_processors)

  parallelize_setup do |worker|
    setup_postgis_parallel_tests
  end
end

This ensures spatial_ref_sys is populated for each test worker.

---

Recommendation

I recommend Option 3 (Automatic Railtie) because:

1. Best user experience - parallel tests "just work"
2. Follows Rails conventions - uses standard initialization hooks
3. Safe implementation - graceful error handling
4. Minimal code - ~30 lines with documentation

This is the kind of "it just works" experience Rails users expect from well-designed gems.

---

Current Workaround (For Users)

Until this is implemented, users can add this to their test/test_helper.rb:

class ActiveSupport::TestCase
  parallelize(workers: :number_of_processors)

  parallelize_setup do |worker|
    if ActiveRecord::Base.connection.table_exists?("spatial_ref_sys")
      count = ActiveRecord::Base.connection.select_value("SELECT COUNT(*) FROM spatial_ref_sys")
      if count.to_i == 0
        ActiveRecord::Base.connection.execute("SELECT postgis_extensions_upgrade();")
      end
    end
  rescue ActiveRecord::StatementInvalid
    # Skip if PostGIS not available
  end
end

---

Environment

• activerecord-postgis: github main HEAD (v0.5.0)
• Rails: 8.1
• PostGIS: 3.4
• PostgreSQL: 16.10
• Ruby: 3.4.7
• Test Framework: Minitest with parallel execution
• Workers: 8 processors

Happy to provide a PR if you'd like to move forward with either implementation!

[seuros]

I will release the new version now. I never tried it in parallel , but it great to have it fix for others.

Funny thing is that patch, i had it long ago, but didn't port it to the gem . Thanks for your contribution @vicocamacho

[navidemad]

I tried the new patch and it is working perfectly. Thanks again.