No column information caching?

rubyonrails-talk
1

I have recently been investigating using PosgreSQL (server version 8.3.12), with the 'pg' gem '0.11.0', with our Rails 3.0.7 app. I've noticed in the development.log that column information queries are made after each request. After inspecting the code for the postgresql_adapter and the mysql_adapter I noticed that the mysql_adapter executes a similar query but with the :skip_logging flag, while the postgresql_adapter goes ahead noisily and logs the query:

In PostgreSQL, the queries are similar to:

SQL (4.4ms) SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid = '"users"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum

These queries take real time in development mode, so being worried about performance I added some extra logging in both adapters to see if these queries are also being made in production mode, and to my surprise they were made repeatedly for tables with both adapters. I was under the impression that ActiveRecord caches table column information (at least in production mode) for a given table after the first query, and does not need to re-query this information unless the client calls the ActiveRecord::Base.reset_column_information method.

What's going on here? Am I missing a configuration option that enables table column information caching? Or am I just misunderstanding the basic concept?

Thanks,

Michael

2

I have recently been investigating using PosgreSQL (server version 8.3.12), with the 'pg' gem '0.11.0', with our Rails 3.0.7 app. I've noticed in the development.log that column information queries are made after each request. After inspecting the code for the postgresql_adapter and the mysql_adapter I noticed that the mysql_adapter executes a similar query but with the :skip_logging flag, while the postgresql_adapter goes ahead noisily and logs the query:

In PostgreSQL, the queries are similar to:

SQL (4.4ms) SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid = '"users"'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum

These queries take real time in development mode, so being worried about performance I added some extra logging in both adapters to see if these queries are also being made in production mode, and to my surprise they were made repeatedly for tables with both adapters. I was under the impression that ActiveRecord caches table column information (at least in production mode) for a given table after the first query, and does not need to re-query this information unless the client calls the ActiveRecord::Base.reset_column_information method.

What's going on here? Am I missing a configuration option that enables table column information caching? Or am I just misunderstanding the basic concept?

I think you'll find that (the local equivalent of) these queries happen all the time in all database servers when you're in development mode. Production mode enables all the caches, and you won't see them any more, or certainly not as often!

Walter

3

I guess what I'm saying is I'm seeing it far more in production mode than I would expect.