• Started working on a menu API in Node yesterday
  • Wrote a script to sync database records

Keeping it simple

A few days ago I spun up a simple Node server with Express, started looking into packages to take the place of ActiveRecord, and began learning about MongoDB and how to use it. This was overwhelming, and I stopped because it seemed like too much all at once (spoiler alert: it was). Back then I though maybe I should start with using Postgres as my database since I was more familiar with it, but that was still a bigger step than I needed.

I really didn’t want to manage a whole DB just to test a few CRUD endpoints. After thinking over it for a bit I realized I was trying to do too much. I needed a data store not a database. Persisting the data across server reboots could come later, until then I just needed a way to save data between requests. I created arrays to hold the objects I wanted to retain and used a counter variable to keep track of which ID to use next. This gave me exactly what I needed and nothing more. Rather than learning how to use a new framework to do one thing I just wrote the functionality myself and gained a bit of experience with JavaScript arrays, objects, and exports.

TIL

  • How Postgres handles invalid statements inside a transaction

    I wrote a Rake task that queries a legacy database and inserts new records into a Postgres database; here’s what it looked like:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

    task :sync, [:since_date] => :environment do |_t, args|
  # select new active policies since the provided date
  new_policies = Policy.active.where("created_at > ?", args.since_date)

  PostgresPolicy.transaction do
    new_policies.each do |policy|
      account_record = PostgresAccount.find_by(account_number: policy.account_number)
      # ignore policies that belong to accounts that are not in the new system
      next unless account_record.present?

      begin
        PostgresPolicy.create(account_id: account_record.id, policy_number: policy.policy_number)
      # if the policy already exists rescue and move on
      rescue ActiveRecord::RecordNotUnique => e
        puts e.message
      end
    end
  end
end

    I was getting this error, though, after I rescued from a RecordNotUnique error:

    ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: org.postgresql.util.PSQLException:
ERROR: current transaction is aborted, commands ignored until end of transaction block:
SELECT  "accounts".* FROM "accounts" WHERE "accounts"."account_number" = 135385 LIMIT 1

    After some head scratching and internet searching I came across this Stack Overflow answer.

    The reason you get this error is because you have entered a transaction and one of your SQL Queries failed, and you gobbled up that failure and ignored it. But that wasn’t enough, THEN you used that same connection, using the SAME TRANSACTION to run another query. The exception gets thrown on the second, correctly formed query because you are using a broken transaction to do additional work. PostgreSQL by default stops you from doing this. - Eric Leschinski

    Since this script is a temporary solution and I’m already handling RecordNotUnique errors I removed the transaction block and no longer received the error.

    TL;DR: a failed query inside of a Postgres transaction will break the transaction.