resetting PK sequences in postgres doesn't work when you define a default_schema #596

Closed
set5think opened this Issue Dec 22, 2012 · 3 comments

2 participants

@set5think

This is from the adapters/shared/postgres.rb file on line 433:

get{setval(seq, db[table].select{coalesce(max(pk)+seq_ds.select{:increment_by}, seq_ds.s     elect(:min_value))}, false)}

In the instance of where I create a connection to postgres defining a default_schema like so:

postgres://localhost/db_name?default_schema=custom_schema

db[table] above doesn't take into account that default_schema has been set, and is still operating under the public schema in postgres.

This is my workaround (notice the table re-assignment) to get it working (works, but sort of feels hacked).

def reset_primary_key_sequence(table)
        return unless seq = primary_key_sequence(table)
        pk = SQL::Identifier.new(primary_key(table))
        db = self 
        seq_ds = db.from(LiteralString.new(seq))
        table = "#{db.default_schema || 'public'}__#{table}".to_sym
        get{setval(seq, db[table].select{coalesce(max(pk)+seq_ds.select{:increment_by}, seq_ds.select(:min_value))}, false)}
      end  

Unless I'm missing something obvious, it seems like the Sequel object doesn't have any concept of what the search_path in the Postgres connection is.

If I try to pass the table name in already schema-qualified, primary_key_sequence(table) fails, because the translation it makes is "default_schema"."schema.sequence_name" and thus returns and does nothing.

Let me know if you'd like me to add more info.

@jeremyevans
@set5think

Thanks for the patch. It seems like the most common use-case is just accessing multiple schemas, rather than supporting the same table names in multiple schemas. This trivial method might really be the first of a series of features that makes multiple-schema support in Postgres possible:

def set_search_path(path, opts={})
  self << set_search_path_sql(path, opts)
end

def set_search_path_sql(path, opts={})
  "SET search_path = #{path}"
end

I added the patch and a spec to my fork. By the time you get to it, I may have added a slightly more sophisticated search_path mechanism that determines whether you want to include public or not in your path.

@jeremyevans
Owner

Unfortunately, you can't use Database#<< to make connection level changes, since the change will only affect a single connection. You need to use an after_connect proc, or patch the adapter so that the queries are sent during Database#connect.

For people that want to modify the PostgreSQL search path, I recommend using an after_connect proc to do so. I'll consider patches that support a :search_path option when creating a Database to make setting the search path easier and more adapter-independent (after_connect requires adapter-dependent code).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment