Unexpected SQL queries to Postgres database on Rai

问题:

I was diving into a really long request to one of my Rails applications using NewRelic and found a number of SQL queries that appear entirely foreign that are taking up a significant length of time. I've Google'd around but I've come up empty handed as to what they are, let alone whether I can prevent them from occurring.

SELECT COUNT(*) FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind in (?, ?) AND c.relname = ? AND n.nspname = ANY (current_schemas(false))

…and…

SELECT a.attname, format_type(a.atttypid, a.atttypmod), pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid = ?::regclass AND a.attnum > ? AND NOT a.attisdropped ORDER BY a.attnum

…each occurred 7 times, taking 145ms and 135ms (respectively) total.

SELECT DISTINCT(attr.attname) FROM pg_attribute attr INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[?] WHERE cons.contype = ? AND dep.refobjid = ?::regclass

…was performed 2 times at a cost of 104ms, and…

SHOW search_path

…commanded 45ms in a single call.

My gut says these are related to the Postgres Rails adapter, but I don't understand what triggers them or what they're doing, or (more importantly) why they fired during a typical request.

---

I just checked out the logs more thoroughly and it looks like the Dyno this request ran on had been transitioned to "up" just a few seconds earlier, so it's likely this request was the first.

回答1:

The tables pg_class, pg_attribute, pg_depend etc all describe table, columns and dependencies in postgres. In Rails, model classes are defined by the tables, so Rails reads the tables and columns to figure out the attributes for each model.

In development mode it looks up these values everytime the model is accessed, so if you've mad e a recent change, Rails knows about it. In production mode, Rails caches this so you would see these much less frequently, and so it really isn't a concern.

回答2:

These queries are used to get the "definition" of your tables and fields and are probably used by the framework to you're using to automatically generate models and/or validation rules in Ruby. (E.g. "Introspection")

I do not have experience with Ruby and the framework you're using, but I don't expect these queries to originate from SQL injection.

You can run the queries yourself in pgAdmin or psql to show the results they're producing and get an idea what information they get from the database

回答3:

I was getting these queries when using Apartment Gem for multitenancy with Postgres Schemas. Apparently each excluded_model - a model that uses default schema - was generating one "pg_class" query in every request.

The guys from Apartment fixed it in version 0.25.0.

回答4:

are queries generated from user input in your application? if so, if you don't have controls on user input, then maybe it's an sql injection from someone trying to hack your app.

http://en.wikipedia.org/wiki/SQL_injection

i'm not real familiar with rails, so i don't know if it has automatically created queries that you as the developer don't know about, but i wouldn't think so.