Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

remove_index fails to remove named index, when using comma-spaced separated schema_search_path #7909

Closed
bluebird-communication opened this Issue Oct 11, 2012 · 31 comments

Comments

Projects
None yet
4 participants
rails 3.2.8
pg 0.14.1
Ubuntu 12.04
1.9.3p194 (2012-04-20 revision 35410) [i686-linux]

migration run in development environment, using PostgreSQL

all gems handled through bundler

All following instructions lead to the same error :

  • remove_index(:pub_responsables, :name => 'pub_responsables_nom_prenom_index')
  • remove_index(:pub_responsables, :name => :pub_responsables_nom_prenom_index)
  • remove_index('pub_responsables', :name => :pub_responsables_nom_prenom_index)
  • remove_index('pub_responsables', :name => 'pub_responsables_nom_prenom_index')

Error message :

"Index name 'pub_responsables_nom_prenom_index' on table 'pub_responsables' does not exist"

Although the table and index exist in the PostgreSQL database :
opf=# \d pub_responsables
                                  Table "s_cat_public.pub_responsables"
   Column   |            Type             |                           Modifiers                           
------------+-----------------------------+---------------------------------------------------------------
 id         | integer                     | not null default nextval('pub_responsables_id_seq'::regclass)
 nom        | character varying(50)       | not null
 prenom     | character varying(50)       | not null
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null
Indexes:
    "pub_responsables_pkey" PRIMARY KEY, btree (id), tablespace "tblspc_opf_indexes"
    "pub_responsables_nom_prenom_index" UNIQUE, btree (nom, prenom), tablespace "tblspc_opf_indexes"
Tablespace: "tblspc_opf_catalogues"

And if we perform a rails SQL execute, everything works like a charm :
execute('DROP INDEX pub_responsables_nom_prenom_index')
Index is removed in this case.
This reinforce the asumption that there is rails bug in remove_index method.

For info :
We uses different tablespace and schema to manage disk access speed and database user permissions.
Tablespace will never affect SQL command to fail. It just affect the request speed if table is stored on a quick hardisk.
Schema will not affect SQL command in this case because our PostgreSQL"SEARCH_PATH" include the schema name where the table belong to.
Moreover the table name is exclusive (no other table has the same name).

Member

senny commented Oct 11, 2012

I investigated a bit. The problem seems to be located in the index_name_for_remove. It calls index_name_exists? and if this method returns false the SQL is never run and the error you encountered is raised.

I checked the source to see how rails determines what indexes exist. I found:

def indexes(table_name, name = nil)
   result = query(<<-SQL, 'SCHEMA')
     SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid
     FROM pg_class t
     INNER JOIN pg_index d ON t.oid = d.indrelid
     INNER JOIN pg_class i ON d.indexrelid = i.oid
     WHERE i.relkind = 'i'
       AND d.indisprimary = 'f'
       AND t.relname = '#{table_name}'
       AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
    ORDER BY i.relname
  SQL

I'm no postgres expert but you could try to run this exact query and tell us what results you get in your environment.

@bluebird-communication there is also the method remove_index!, which does no checks and simply executes the drop index sql. I think this method is internal though and could change in the future.

def remove_index!(table_name, index_name) #:nodoc:
  execute "DROP INDEX #{quote_table_name(index_name)}"
end

Thanks for your help on this.

Though the use of alias 't', 'i' and 'd' make it difficult to understand the SQL query, I don't have much knowledge about the columns of pg_class and pg_index PostgreSQL private tables to undestand the logic behind the scene.

However, I performed the requested SQL query after replacing the table_name variable by the name of my table 'pub_responsables' :

...
SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid
FROM pg_class t
INNER JOIN pg_index d ON t.oid = d.indrelid
INNER JOIN pg_class i ON d.indexrelid = i.oid
WHERE i.relkind = 'i'
AND d.indisprimary = 'f'
AND t.relname = 'pub_responsables'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
ORDER BY i.relname;
...

And I got a successful result showing my index name :

...
relname | indisunique | indkey | pg_get_indexdef | oid
-----------------------------------+-------------+--------+-----------------------------------------------------------------------------------------------------+-------
pub_responsables_nom_prenom_index | t | 2 3 | CREATE UNIQUE INDEX pub_responsables_nom_prenom_index ON pub_responsables USING btree (nom, prenom) | 16721
(1 row)
...

Maybe the problem comes from the 'table_name' variable ?
Another question, what is this 'name' variable used for ? ( I didn't see any use of it within the querry)

I executed the SQL querry directly on the psql interface (not via a ruby method), so I'm also wondering about this ruby line :

result = query(<<-SQL, 'SCHEMA')

The 'SCHEMA' part could be the reason why the querry return nothing, if a specific schema is applied (like the 'public' default PostGreSQL schema)

This is confusing because I know that Rails framework also use the term "schema" to speak about something different from what a "schema" is in PostgreSQL, Oracle, or SQL standard.

In PostgreSQL you can make your tables belong to some kind of user-group named "schema".
The default postgreSQL schema is "public".
You can create ne schema.
If you need to restric access to the tables on different db-users, you can group your tables by different "schema".
Then you grant rights of access to those schema for each db-user : select, update, delete, insert, trigger, truncate, reference.

This usefull if your database is used by other applications than your rails framework or if you have dedicated db-users for database maintenance.

Member

senny commented Oct 16, 2012

@bluebird-communication thanks for the insight. I will revisit this problem when I got some spare time and report back.

Member

senny commented Oct 16, 2012

@bluebird-communication just to verify that I'm digging in the right place, can you run and send me the output of:

ActiveRecord::Base.connection.indexes('pub_responsables')

ActiveRecord::Base.connection.index_name_exists?('pub_responsables', 'pub_responsables_nom_prenom_index', false)
Member

senny commented Oct 16, 2012

I found the following helper method in the test-cases:

def with_schema_search_path(schema_search_path)
  @connection.schema_search_path = schema_search_path
  yield if block_given?
ensure
  @connection.schema_search_path = "'$user', public"
end

and this lead me to the following documentation in the postgres_adapter code:

# * <tt>:schema_search_path</tt> - An optional schema search path for the connection given
#   as a string of comma-separated schema names. This is backward-compatible with the <tt>:schema_order</tt> option.

@bluebird-communication did you configure the schema_search_path correctly?

Here it is the result of the two commands :

1.9.3-p194 :003 > ActiveRecord::Base.connection.indexes('pub_responsables')
(581.5ms) SELECT distinct i.relname, d.indisunique, d.indkey, m.amname, t.oid,
pg_get_expr(d.indpred, t.oid), pg_get_expr(d.indexprs, t.oid)
FROM pg_class t, pg_class i, pg_index d, pg_am m
WHERE i.relkind = 'i'
AND i.relam = m.oid
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = 'pub_responsables'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('s_rails',' s_projets',' s_cat_public',' s_cat_admin',' s_cat_dev',' s_historiques') )
ORDER BY i.relname

=> []
1.9.3-p194 :004 >
1.9.3-p194 :005 > ActiveRecord::Base.connection.index_name_exists?('pub_responsables', 'pub_responsables_nom_prenom_index', false)
(5.4ms) SELECT distinct i.relname, d.indisunique, d.indkey, m.amname, t.oid,
pg_get_expr(d.indpred, t.oid), pg_get_expr(d.indexprs, t.oid)
FROM pg_class t, pg_class i, pg_index d, pg_am m
WHERE i.relkind = 'i'
AND i.relam = m.oid
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = 'pub_responsables'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('s_rails',' s_projets',' s_cat_public',' s_cat_admin',' s_cat_dev',' s_historiques') )
ORDER BY i.relname

=> nil
1.9.3-p194 :006 >

And Yes the schema search path is correctly configured. Please see the following SQL output :

opf=#
opf=# show search_path;

search_path

s_rails, s_projets, s_cat_public, s_cat_admin, s_cat_dev, s_historiques
(1 row)

opf=#

Member

senny commented Oct 19, 2012

@bluebird-communication does rails recognize that search path? Does it change the output when you add the schema_search_path to the database.yml as described in the comment above?

For better understanding of my migration, here it is the migration :

require Rails.root.to_s + '/lib/opf_modules/opf_outils_pg.rb'

class CreatePubResponsables < ActiveRecord::Migration

include OutilsPG

def up

say "MODULE utilise : #{Rails.root.to_s}/lib/opf_modules/opf_outils_pg.rb"

# ======================   INITIALISATION =================================================================================================

initialiser_chemin_et_tb_par_defaut

# =========================================================================================================================================

# ======================   MIGRATION   ====================================================================================================

say ' '
say '*************************************************************************************************************'
say 'MIGRATION : creation de la table pub_responsables sur le ' + SCHEMA_PUBLIC + ' dans le tablespace ' + TABLESPACE_CATALOGUES
say ' '

# Créer la table pub_responsables sur le schéma SCHEMA_PUBLIC dans le tablespace TABLESPACE_PROJETS
create_table SCHEMA_PUBLIC + '.pub_responsables', :options => 'TABLESPACE ' + TABLESPACE_CATALOGUES do |t|
  # Appliquer des contraintes aux colonnes (non null, nombre de caractères maximum)
  t.string :nom, :null => false, :limit => 50
  t.string :prenom, :null => false, :limit => 50

  t.timestamps
end

# ==================== INDEXES ============================================================================================================

say 'MIGRATION : Ajout d\'un indexe univoque dans le tablespace' + TABLESPACE_INDEXES + ' pour les colonnes nom et prenom de la table "pub_responsables"'
say ' '

# Change de tablespace par défaut afin que l'index soit créé dans le tablespace TABLESPACE_INDEXES
definir_tablespace_courant(TABLESPACE_INDEXES)

# Création d'un index unique sur les champs nom et prenom dans le même schéma que la table qui le concerne
add_index(SCHEMA_PUBLIC + '.pub_responsables', [:nom, :prenom], :unique => true, :name => 'pub_responsables_nom_prenom_index')

deplacer_index_pkey_sur_tablespace_indexes('pub_responsables')

# ======================   ALOUER LES DROITS D'ACCES   ====================================================================================

allouer_acces_utilisateur

# ======================   RESTORATION DES PARAMETRES PAR DEFAUT   ========================================================================

initialiser_chemin_et_tb_par_defaut

# =========================================================================================================================================

end

ROLLBACK pour supprimer la table 'pub_responsable' et son indexe 'pub_responsables_nom_prenom_index'

Durant ce rollback, le chemin de schéma par défaut est utilisé. Celui-ci permet de retrouver n'importe qu'elle table car elles sont

nommées de façon univoque à l'aide de préfixes dépendant de leur schéma.

def down

# Supprimer l'indexe précédemment créé
execute('DROP INDEX pub_responsables_nom_prenom_index')
# remove_index('pub_responsables', :name => :pub_responsables_nom_prenom_index) ne fonctionne pas pour une raison encore inconnue

# Supprimer la table précédemment créée
drop_table SCHEMA_PUBLIC + '.pub_responsables'

end
end


And those are my modules :

module EnvOPF

CHEMIN_DE_SCHEMA_PAR_DEFAUT = 's_rails, s_projets, s_cat_public, s_cat_admin, s_cat_dev, s_historiques'

SCHEMA_PAR_DEFAUT = 's_rails'
SCHEMA_PROJETS = 's_projets'
SCHEMA_PUBLIC = 's_cat_public'
SCHEMA_ADMIN = 's_cat_admin'
SCHEMA_DEV = 's_cat_dev'
SCHEMA_HISTORIQUES = 's_historiques'

BD rapport

SCHEMA_RAPPORTS = 's_rapports'

BD archives

SCHEMA_ARCHIVES = 's_archives'

Tablespace par défaut de la base de données 'opf'. Ce tablespace a la charge de stocker toutes les données des tables spécifiques au framework et aux gems.

TABLESPACE_PAR_DEFAUT = 'tblspc_opf_rails'

TABLESPACE_PROJETS = 'tblspc_opf_projets'
TABLESPACE_CATALOGUES = 'tblspc_opf_catalogues'
TABLESPACE_HISTORIQUES = 'tblspc_opf_historiques'
TABLESPACE_ARCHIVES = 'tblspc_opf_archives'
TABLESPACE_INDEXES = 'tblspc_opf_indexes'
end


require Rails.root.to_s + '/lib/opf_modules/opf_constantes.rb'

module OutilsPG
include EnvOPF

private

# ==== CHEMINS et TABLESPACES ====

# Retablit le chemin de schéma et le tablespace par défaut
def initialiser_chemin_et_tb_par_defaut
  # Remarque : la méthode 'say' ne supporte pas des chaînes de caractères contenant des accents
  #say "MODULE utilise : #{Rails.root.to_s}/lib/opf_modules/opf_outils_pg.rb"
  #say ' '
  say '***************************************************************************'
  say 'INITIALISATION du chemin de schema par defaut, et du tablespace par defaut.'
  say ' '
  restorer_chemin_de_schema_par_defaut
  restorer_tablespace_par_defaut
end


# ==== TABLESPACE ====

# Initialiser le tablespace par défaut afin que toute manipulation par un gemset de rails se fasse sur le tablespace "tblspc_xxx_rails"
def restorer_tablespace_par_defaut
  say 'Retablir tablespace par defaut : ' + TABLESPACE_PAR_DEFAUT
  say ' '
  execute('SET DEFAULT_TABLESPACE = ' + TABLESPACE_PAR_DEFAUT)
end


# Change de tablespace par défaut afin que l'index soit créé dans le tablespace TABLESPACE_INDEXES
def definir_tablespace_courant(nom_tablespace)
  say 'Change le tablespace courant : ' + nom_tablespace.to_s
  say ' '
  execute('SET DEFAULT_TABLESPACE = ' + nom_tablespace.to_s)
end

# ==== SCHEMA SEARCH PATH ====

def restorer_chemin_de_schema_par_defaut
  say 'Retablir chemin par defaut : ' + CHEMIN_DE_SCHEMA_PAR_DEFAUT
  say ' '
  ActiveRecord::Base.connection.schema_search_path = CHEMIN_DE_SCHEMA_PAR_DEFAUT
end


# Définit un chemin de schemas par défaut pour la base de donnée.
# !!!  Ne fonctionne qu'avec PostgreSQL  !!!
#
# chemin : chaîne de caractères
#
def definir_chemin_de_schema(chemin)
  say 'Definit un chemin par defaut : ' + chemin.to_s
  say ' '
  ActiveRecord::Base.connection.schema_search_path = chemin.to_s
end

# ==== INDEXES ====

# La primary key créée automatiquement par Rails lors de la création d'une table est positionnée sur le tablesapce par défaut.
# Cette procédure permet rétablir la primary key sur le tablespace réservé aux indexes
#
# nom_table : nom de la table créée au pluriel
#
def deplacer_index_pkey_sur_tablespace_indexes(nom_table)
  say ' '
  say '**************************************************************************************'
  say 'Deplacement de la primary key cree par Rails vers le tablespace ' + TABLESPACE_INDEXES
  say ' '
  execute('ALTER INDEX ' + nom_table.to_s + '_pkey SET TABLESPACE ' + TABLESPACE_INDEXES)
end

# ==== UTILISATEURS ====  

def allouer_acces_utilisateur
  say ' '
  say '******************************************'
  say 'Allouer les acces a l\'utilisateur ' + UTILISATEUR
  say ' '
  revoquer_tous_les_droits_utilisateur_sur_bd_opf
  retablir_tous_les_droits_utilisateur_sur_bd_opf
end

# Révoquer à UTILISATEUR tous ses droits sur toutes les tables 
def revoquer_tous_les_droits_utilisateur_sur_bd_opf
  say 'Revoquer tous les droits de l\'utilisateur'
  say ' '
  execute('REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA ' + CHEMIN_DE_SCHEMA_PAR_DEFAUT + ' FROM ' + UTILISATEUR)
end

# Allouer à utilisateur les droits de sélection, insertion, mise à jour, et effacement sur toutes les tables des schémas
# SCHEMA_PAR_DEFAUT et SCHEMA_PROJETS
def retablir_tous_les_droits_utilisateur_sur_bd_opf
  say 'Retablir tous les droits de l\'utilisateur'
  say ' '
  execute('GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ' + SCHEMA_PAR_DEFAUT + ', ' + SCHEMA_PROJETS + ' TO ' + UTILISATEUR)
  execute('GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA ' + SCHEMA_ADMIN + ' TO ' + UTILISATEUR)
  execute('GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA ' + SCHEMA_PUBLIC + ', ' + SCHEMA_HISTORIQUES + ' TO ' + UTILISATEUR)
  execute('GRANT SELECT ON ALL TABLES IN SCHEMA ' + SCHEMA_DEV + ' TO ' + UTILISATEUR)
end

end

There is no schema_search_path in config/database.yml

I take care of if it in my migrations because Rails is unable to manage it correctly.

Schema_search_path is independent from Rails. It is related to PostgreSQL.
When rails place a request to the DB, PostgreSQL will automatically looks for any requested table,index, (and any db objects) in each schema specified by the schema_search_path.

Basically a schema_search_path is to a DB, what a Linux path is to linux system.

As you can see in my search path, I get rid of default "$user" and "public" from PostgreSQL schema. I only use specific schema.

Be carefull of the constant :
SCHEMA_PUBLIC = 's_cat_public'
which is not the default postgreSQL schema : "public".

This may be confusing when reading my code.

Member

senny commented Oct 19, 2012

I see. As stated before I'm no Postgres expert I just browsed the rails source to find related parts in the framework and I noticed that the tests for connection.index(table_name) work with a configured schema search path and these tests set connection.schema_search_path, which is also configurable in the database.yml

So I think you should at least try to add the option in and output connection.schema_search_path to verify it's set correctly and then call the index(table_name) method again to see if it changes the result.

Otherwise someone with more Postgres knowledge should jump in. @rafaelfranca thoughts?

Yup no worries.

I'm sorry I don't understand this : "add the option in and output connection.schema_search_path"

Are you talking about index(table_name) or remove_index(table_name) ?
If you mean to add an index I can't rely on Rails actual version of add_index method because there is no doc about how to use it with db schemas on api.rubyonrails.org
Moreover who could I rely on such method of the rails framework when the remode_index is not able to basically remove an index just by its name (without even managing any schema; as PostgreSQL cares about it).

Another question : why a method remove_index using the name of the index as parameter would need and tablename to remove the index ?
Index names likes tablenames should be uniq.
The only other parameter such method may possibly need, would be a schema name.

To add an index you need the table related to. But to remove an index you don't need it.
I see things like this for the remode_index method :

  • either it care about the index name for the user, so he has to provide tablename and one or more columns inorder Rails build an automatic index_name, or
  • the user give the index_name to the method and Rails just use it

Now if you want to add a DB schema support to Rails, you could add a schema parameter to the ConnectionAdapter methods.

Then I don't see why it's needed to do all this complex SQL instructions on PostgreSQL private tables just to remove an index ?

PostgreSQL private table are managed by PostgreSQL itself. The user shouldn't even think about it.
Puting the hands inside is not a good idea. This makes gem pg more dependent from postgreSQL version and internal implementation.

gem pg layer should keep as much as possible a layer of abstraction between Rails and PostgreSQL.
I think relying on official standard SQL instructions makes pg gem stronger to support all database respecting SQL standard (such as Oracle and so on)

But I may be wrong because missing something as I'm not an expert in PostgreSQL. I just use standard SQL instructions.

Thanks for the help anyway

P.S. "By the way, the 'say' method doesn't support accentuated characters'. Just noticed it.

Member

senny commented Oct 19, 2012

"add the option in and output connection.schema_search_path"

You should add the configuration option schema_search_path to your database.yml.

development:
  adapter: postgresql
  schema_search_path: "s_rails, s_projets, s_cat_public, s_cat_admin, s_cat_dev, s_historiques"

you can then boot up a rails console and verify if the search path has been set correctly:

ActiveRecord::Base.connection.schema_search_path

Then you can try to call the index methods again:

ActiveRecord::Base.connection.indexes('pub_responsables')
ActiveRecord::Base.connection.index_name_exists?('pub_responsables', 'pub_responsables_nom_prenom_index', false)

the remove_index method takes a table because it verifies if that table actually has the the given index. I'm not sure if this design was driven by database adapters other than postgres.

Ressource : http://docs.postgresql.fr/9.2/
This doc explains every postgreSQL column private table, every instructions, well everything about postgreSQL...
... but it's in french :/

However here is a translation of the intersting columns for us :

     Table pg_index
     ---------------------

Name    Type    References  Description
relname     name        Name of the table, view, index, etc. 
relnamespace    oid     pg_namespace.oid    OID of the namespace containing the relation. 
relkind     char        r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table, f = remote table


     Table pg_index
     ---------------------

Name    Type    References  Description
indexrelid  oid     pg_class.oid    OID entry in pg_class for the index 
indrelid    oid     pg_class.oid    OID entry in pg_class for the table on which the index is related to.

indkey  int2vector  pg_attribute.attnum     indnatts values table which point to columns to the indexed table. For instance, a value of 1 3 means that the first and third column of the table make the index key. A 0 in this table shows that the related index attribut is rather an expression on the columns instead of a simple column reference.

indisunique     bool        True if it's a unicty index 
indisprimary    bool        True if it's the primary key index of the table (indisunique must always be true when this field is true)

I didn't set the option schema_search_path to my database.yml but the result of the schema_search_path is successful :

1.9.3-p194 :002 >   ActiveRecord::Base.connection.schema_search_path

=> "s_rails, s_projets, s_cat_public, s_cat_admin, s_cat_dev, s_historiques"

However Activerecord is unable to find the index, though it is located in the "s_cat_public" schema :

1.9.3-p194 :008 >   ActiveRecord::Base.connection.indexes('pub_responsables')

(100.6ms) SELECT distinct i.relname, d.indisunique, d.indkey, m.amname, t.oid,
pg_get_expr(d.indpred, t.oid), pg_get_expr(d.indexprs, t.oid)
FROM pg_class t, pg_class i, pg_index d, pg_am m
WHERE i.relkind = 'i'
AND i.relam = m.oid
AND d.indexrelid = i.oid
AND d.indisprimary = 'f'
AND t.oid = d.indrelid
AND t.relname = 'pub_responsables'
AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('s_rails',' s_projets',' s_cat_public',' s_cat_admin',' s_cat_dev',' s_historiques') )
ORDER BY i.relname

 => [] 

Ohhh !
I found the bug : a method (from activerecord or pg gem or whatever) is adding one leading space to each schema name after the first one !
That explains why PostgreSQL can't find the schemas...

Do you think you can correct it ?

The problem comes from the indexes method of activerecord :

Somewhere inside this :

File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 818

  def indexes(table_name, name = nil)
     result = query("           SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid
       FROM pg_class t
       INNER JOIN pg_index d ON t.oid = d.indrelid
       INNER JOIN pg_class i ON d.indexrelid = i.oid
       WHERE i.relkind = 'i'
         AND d.indisprimary = 'f'
         AND t.relname = '#{table_name}'
         AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
      ORDER BY i.relname", name)


    result.map do |row|
      index_name = row[0]
      unique = row[1] == 't'
      indkey = row[2].split(" ")
      inddef = row[3]
      oid = row[4]

      columns = Hash[query("          SELECT a.attnum, a.attname
      FROM pg_attribute a
      WHERE a.attrelid = #{oid}
      AND a.attnum IN (#{indkey.join(",")})", "Columns for index #{row[0]} on #{table_name}")]

      column_names = columns.values_at(*indkey).compact

      # add info on sort order for columns (only desc order is explicitly specified, asc is the default)
      desc_order_columns = inddef.scan(%r(\w+) DESC/).flatten
      orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {}

      column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names, [], orders)
    end.compact
  end

As I said in previous post, the resulting built query displayed include erroneous prefixed space on each schema name :

('s_rails',' s_projets',' s_cat_public',' s_cat_admin',' s_cat_dev',' s_historiques')

instead of the query should include this :

('s_rails','s_projets','s_cat_public','s_cat_admin','s_cat_dev','s_historiques')

I'm not good enough at mastering Ruby to debug it.
Could anyone help ?

Member

senny commented Oct 25, 2012

@bluebird-communication I'll have a look at it when I got some spare time.

Member

senny commented Oct 28, 2012

@bluebird-communication You use the constant CHEMIN_DE_SCHEMA_PAR_DEFAUT to set the schema_search_path. And the same spaces that you receive in the output are present in your constant:

CHEMIN_DE_SCHEMA_PAR_DEFAUT = 's_rails, s_projets, s_cat_public, s_cat_admin, s_cat_dev, s_historiques'

Why don't you remove the spaces from that constant?

CHEMIN_DE_SCHEMA_PAR_DEFAUT = 's_rails,s_projets,s_cat_public,s_cat_admin,s_cat_dev,s_historiques'

This is because this is a common standard way to enumerate literals in SQL, ruby, PHP and so on...

For instance in Ruby :
my_array = [1, 'cat', 3.14, 'hello']
myhash = Hash["a", 100, "b", 200] #=> {"a"=>100, "b"=>200}

For instance, if you look at the begining of SQL querry :
SELECT distinct i.relname, d.indisunique, d.indkey, m.amname, t.oid, pg_get_expr(d.indpred, t.oid), pg_get_expr(d.indexprs, t.oid)

There are space after each comma between each column selection.
And PostgerSQL support such seach_path literal.

Force the user to not use space would be a valid workaround, but a method that allows spaces within an enumerator literal would make it compatible with Ruby and PostgreSQL standards.

The use of a "strip!"method on each schema string, or a a "delete!(' ')" on a search_path string would solve this issue in indexes method.

more info : I looked at PostgreSQL (english) doc : http://www.postgresql.org/docs/current/static/functions-info.html

I think the indexes method make a bad use of the PostgreSQL function : current_schemas.

current_schemas(boolean) returns an array of the names of all schemas presently in the search path. The Boolean
option determines whether or not implicitly included system schemas such as pg_catalog are included in the returned search path.

    Note: The search path can be altered at run time. The command is:

    SET search_path TO schema [, schema, ...]

This PostgreSQL function is returning an array where SQL is waiting for a string.

Mmm, well I'm not sure that the issue come from current_schema(false).
Because when I use the following postgreSQL command I got a successfull result :

opf=# select current_schemas(false);
                           current_schemas                            
----------------------------------------------------------------------
 {s_rails,s_projets,s_cat_public,s_cat_admin,s_cat_dev,s_historiques}
(1 row)

As Postgres accept my search_path,
and moreover the official doc describe (as above) a way to define search_path with 'space" 'in between comma and next schema' (which is common in programming),
and as well respond correctly to sql command : "select current_schema(false)" providing a list of schema without space :
it have to work in "indexes" rails method.

So I recommend to help on this by supporting comma-space separated schema for the search_path.
to get ride of space, one could use strip!, or delete!(" ") ruby method, or "select current_schemas(false)" to return a search_path without space in between comma and next-schema.

Owner

rafaelfranca commented Apr 9, 2013

@bluebird-communication mind to open a pull request?

To be honest, I don't know how to do that, and my understanding of rails libraries is limited. Some part of the code remain obscure to me. I don't want to put garbage in this beautifull tool that is RoR.

Member

senny commented Apr 11, 2013

@rafaelfranca I try to take a stab at this one later today.

Member

senny commented Apr 11, 2013

I took another look at this issue and I can confirm that there is something weird going on when removing indexes with a custom search_path. I'll try to work up a patch.

Contributor

AlexVPopov commented Oct 24, 2013

I have exactly the same problem as @bluebird-communication and doing the things @senny asks for delivers the same results as in @bluebird-communication.

Member

senny commented Oct 25, 2013

@AlexVPopov can you write an executable test-case to reproduce the problem? You can use this script as a foundation. Make sure to change the adapter to PG though. Once I can reproduce, I can investigate and see what causes the issue.

@senny senny added the PostgreSQL label Feb 4, 2014

Member

senny commented Mar 26, 2014

@bluebird-communication @AlexVPopov can you please provide an executable test-case to reproduce this behavior? You can use this script as a starting point. Make sure to use a PostgreSQL connection instead of the provided sqlite one.

Contributor

AlexVPopov commented Mar 26, 2014

@senny - Sorry, currently I don't have the issue, so I cannot create the test-case. In case I encounter it again, I will provide it.

Member

senny commented Apr 7, 2014

I'm closing this issue out of inactivity. If you are still experiencing this with master, 4-1-stable or 4-0-stable please report back with a way to reproduce. I'll happily reopen and investigate the cause.

Thank you for reporting 💛

@senny senny closed this Apr 7, 2014

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