CODEWITHSUNDEEP
sqlruby-on-railspostgresqlherokunewrelic
coreyward
coreyward

Reputation: 80051

Unexpected SQL queries to Postgres database on Rails/Heroku

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.

Upvotes: 15

Views: 5560

Answers (4)

jpbarbosa
jpbarbosa

Reputation: 571

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.

Upvotes: 0

Neil Lamoureux
Neil Lamoureux

Reputation: 809

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.

Upvotes: 11

thaJeztah
thaJeztah

Reputation: 29037

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

Upvotes: 3

Jeff Hawthorne
Jeff Hawthorne

Reputation: 568

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.

Upvotes: -5

Related Questions