I'm developing an application with a simple user authentication/authorization system (as in Rails Recipes, Recipes 31 and 32). Of course, this requires two join tables (one between users and groups, and one between groups and permissions). I set config.cache_classes = true in my environments/development.rb in order to simulate the behavior of the production environment, and then took a look at the log. Whenever I access data from a model table (users, groups, permissions, orders), an SQL call is made to determine the table structure the first time the table is accessed, and that call is not made again. (An example is below.) SQL (0.005302) 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 However, SQL calls to determine the table structure of the groups_users and groups_permissions tables are made every single time I call user.groups, group.permissions, etc. (Example below.) SQL (0.001137) 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 = 'groups_users'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum I'm probably prematurely optimizing, but I wonder how much these additional SQL calls will affect database performance, both with the user/group/permission tables (which get called for every action) and other many-to-many join tables in my application. Has anyone else noticed this? Thanks, Chris (Ruby 1.8.4, Rails 1.2RC1, MacOS X 10.4.8 Intel, PostgreSQL 8.1.3)
on 2007-01-12 18:56
on 2007-01-13 08:06
I get the same behaviour - I think what's happening is that the column information is cached in the corresponding model class. Since join tables don't have a corresponding model, there's nowhere for the information to be cached. If you're really worried about this you can get around it by creating an explicit model for the join table and using the has_many :through option (see the API docs for ActiveRecord::Associations::ClassMethods).