Tag Archives: SQL

How to Log Which Line Initiated a Query in Rails

Posted on by
Reply

Create a file (something like) config/initializers/active_record_log_subscriber.rb

…and paste this into its contents:

module LogQuerySource
  def debug(*args, &block)
    return unless super

    backtrace = Rails.backtrace_cleaner.clean caller

    relevant_caller_line = backtrace.detect do |caller_line|
      !caller_line.include?('/initializers/')
    end

    if relevant_caller_line
      logger.debug("  ↳ #{ relevant_caller_line.sub("#{ Rails.root }/", '') }")
    end
  end
end
ActiveRecord::LogSubscriber.send :prepend, LogQuerySource

Now, whenever a line of code initiates a SQL query the line will be logged.  This greatly improves the ease of tracking down inefficiencies such as like N + 1 queries.

If you would like to learn more about exactly how this mix-in works and how the original author went about figuring out how to add it, you can read more about it here.

 

Converting TEXT columns to JSONB in PostgreSQL

Posted on by
1

Recently I had a text field being used to store JSON data from my web application. I hadn’t yet discovered the amazingness of the PostgreSQL JSONB datatype.

JSONB is an amazing datatype because Postgres understands how to query the structure and the keys and values can be indexed.

So if you have a text fields you want to convert to JSONB (provided the existing data is in properly formed JSON already) You can change the datatype and convert the existing data using this simple SQL command:

alter table tbl_name alter column col_name type jsonb using col_name::JSON;

You may be inclined to covert the text using the to_json function like: ‘USING to_json(col_name);’ – BUT DON’T DO THAT! What happens with that is you get a single string encapsulating the valid JSON, containing your JSON. Effectively a double-encoding bug.  The former (casting the column to JSON is the correct way).

Extracting Specific Date-time Components in Postgres

Posted on by
Reply

When programming, sometimes it’s useful to extract various time components from a Date-time field in SQL queries.  For example, you might want to filter the date field by, hour, or year (or both).  Fortunately, Postgres has a easy way to pull this data out within the query itself:

SELECT EXTRACT(hour FROM a_datetime_field) FROM a_table;

If the field was the value ‘4/5/2009 13:09:00’ then that above query would return “13” in the select.

In a stranger, practical example, I was moving a database from one server to another and for some unknown reason, all the dates in a table were fudged so that instead of being the year 2009, it was 142009, and the seconds were also stuffed up – the result being that any queries I ran against the table threw ADO.NET exceptions because the dates couldn’t be parsed properly.  I needed to run a query like this one to set things right again:

update atable
  set datetimeadded = cast( '2009-' || EXTRACT(month FROM datetimeadded) || '-' ||
    EXTRACT(day FROM datetimeadded) || ' ' || EXTRACT(hour FROM datetimeadded) || ':' ||
    EXTRACT(minute FROM datetimeadded) as timestamp),
  datetimeupdated =  cast('2009-' || EXTRACT(month FROM datetimeupdated) || '-' ||
    EXTRACT(day FROM datetimeupdated) || ' ' || EXTRACT(hour FROM datetimeupdated) || ':' ||
    EXTRACT(minute FROM datetimeupdated) as timestamp);

The casts are needed to get the update to cooperate, but basically this recreates the date time piece-by-piece based on the original date-time value.