Unexpected SQL queries to Postgres database on Rails/Heroku

Question

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.

Acetify

Answer 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.

Attached

I thought Rails used schema.rb to determine the structure of the database, rather than inspecting the database. – Acetify

No, schema.rb is code that creates your tables when you deploy; Rails does not parse this to determine the attributes. Instead, it looks at the table definitions in the database. – Attached

I meant to include a link that explains what schema.rb is for – Attached

I think you meant this link in your comment: guides.rubyonrails.org/… – Furniture

Answer 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

Fauve

Thanks — that's what I suspected, but I still don't know why they're occurring during a request like this. – Acetify

Usually frameworks 'cache' these results, but if you're currently developing and have debugging enabled, it may be that caching is disabled in debug mode, therefore it will perform those queries for each request – Fauve

This is a production application, but it's likely this was the first request to the Dyno (just updated question). Thanks! – Acetify

Found the root cause? @coreyward. I have similar problem here #38779189 – Thoughtful

Answer 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.

Vaniavanilla

Answer 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.

Inflame

That's unlikely. This was a GET request to a known/expected URL. – Acetify

gotcha. i'm learning php and reading a lot about that sort of thing, so thought i would throw it out there just in case :) looks like you got it covered in the other discussion though! – Inflame