Attempting to execute a sql file with \set ... raises errors

[schneems]

I am trying to execute a SQL file programmatically through the pg gem and I ran into this issue where I cannot give it a file with \set ON_ERROR_STOP true.

Expected

That I can execute a valid SQL file with pg gem somehow

Actual

This code:

DB = PG.connect(dbname: "mydbname")
DB_FILE_CONTENTS = File.read(File.expand_path("../../config/seed.sql", __FILE__))

def reseed_database!(db: DB)
  db.exec(DB_FILE_CONTENTS)
end

Causes this error:

An error occurred in a `before(:suite)` hook.
Failure/Error: db.exec(DB_FILE_CONTENTS)

PG::SyntaxError:
  ERROR:  syntax error at or near "\"
  LINE 1: \set ON_ERROR_STOP true
          ^
# ./spec/spec_helper.rb:114:in `exec'
# ./spec/spec_helper.rb:114:in `reseed_database!'
# ./spec/spec_helper.rb:108:in `truncate_reseed!'
# ./spec/spec_helper.rb:119:in `block (2 levels) in <top (required)>'

Maybe there's a better or different way to execute a SQL file with pg other than a Connection object. If so, could you let me know?

If there isn't a better way to do this I'm curious if this would be considered a bug?

Extra Context

I have a file that looks like this:

-- config/seed.sql
\set ON_ERROR_STOP true

-- SQL code goes here

I've got a workflow where want to apply my schema and this seed file. I can do this on the command line:

$ dropdb mydbname --if-exists && created mydbname
$ psql --dbname=mydbname --file=config/schema.sql --file=config/seed.sql

However, I'm running into an issue where I'm trying to truncate the database between test runs and re-seed the data. I'm doing it like this in a spec_helper.rb https://gist.github.com/schneems/21132fb03291266ab28bbfe723f75a99

This is the relevant code:

# The pg gem doesn't like this syntax
DB_FILE_CONTENTS = File.read(File.expand_path("../../config/seed.sql", __FILE__)).gsub("\\set ON_ERROR_STOP true", "")

def reseed_database!(db: DB)
  db.exec(DB_FILE_CONTENTS)
end

If I remove that gsub then I get an error from the pg gem:

An error occurred in a `before(:suite)` hook.
Failure/Error: db.exec(DB_FILE_CONTENTS)

PG::SyntaxError:
  ERROR:  syntax error at or near "\"
  LINE 1: \set ON_ERROR_STOP true
          ^
# ./spec/spec_helper.rb:114:in `exec'
# ./spec/spec_helper.rb:114:in `reseed_database!'
# ./spec/spec_helper.rb:108:in `truncate_reseed!'
# ./spec/spec_helper.rb:119:in `block (2 levels) in <top (required)>'

I'm using the latest version of PG and I'm on Rails 3.1.4:

$ psql --version
psql (PostgreSQL) 16.0 (Homebrew)
$ ruby -v
ruby 3.1.4p223 (2023-03-30 revision 957bb7cb81) [x86_64-darwin22]
$ cat Gemfile.lock | grep pg
    pg (1.5.4)
  pg

[larskanis]

The sequence \set ON_ERROR_STOP true is a psql internal command. It is interpret in psql on the client side. It has nothing to do with the PostgreSQL server nor with ruby-pg. They don't recognize psql's backslash commands.

Does the file seed.sql run successfully when you remove the \set by gsub? It should work when the file uses plain SQL commands. However if it contains COPY sequences like pg_dump generates per default, it needs special handling through PG::Connection#put_copy_data and a parser which separates between #exec and #put_copy_data. The COPY handling is another extension of psql. If pg_dump is executed with --insert then the SQL text should work with PG::Connection#exec without further efforts.

[esparta]

I did elaborate this answer directly on Mastodon, sorry about the duplication:

PostgreSQL, the server, only recognize DDL, DQL, DML, DCL and TCL Commands. When you send an string starting with \ to the server using pg then the gem will tell you what the PostgreSQL say: ERROR.

In other words, sentences starting with \ are only recognized by psql, the tool, as commands. psql would take that string and intercept them for their own purposes, but doesn't send it to PostgreSQL.

Maybe the documentation should be more clear about it:
https://www.postgresql.org/docs/current/app-psql.html

The same would happen with other \ commands such as \dt which in psql list the tables on the current database and schema:

DB = PG.connect(dbname: "mydbname")
r = DB.exec('\dt')
Traceback (most recent call last):
        2: from (irb):9
        1: from (irb):9:in `exec'
PG::SyntaxError (ERROR:  syntax error at or near "\")

If you want to accomplish the same as \dt using raw sql sent to PostgreSQL, then you need to use the PostgreSQL catalog and do what psql do internally:

DB = PG.connect(dbname: "mydbname")
r = DB.exec("SELECT tablename FROM pg_catalog.pg_tables where schemaname='public';")
r.to_a
# => => [{"tablename"=>"active_storage_blobs"}, {"tablename"=>"active_admin_comments"} .. ]

Hope this helps.

[schneems]

I was able to get it to work by removing the line and moving the error checking flag to the psql command directly:

psql -v ON_ERROR_STOP=1 --dbname=mydbname --file=config/schema.sql

Leaving this note to let others who might run into the same issue.

Does the file seed.sql run successfully when you remove the \set by gsub? It should work when the file uses plain SQL commands.

Yep, it works fine then. I guess it doesn't make sense for the pg gem to have a "run this command as if it was psql" as that might be hard to maintain. I wonder if there's a small set of syntax it might make sense to detect and annotate the error.

Like: "Note: Not all syntax in psql can be executed by the pg gem, for more information: "

However, I'm not sure how often people might run into something like this, maybe shelve the idea until/unless it comes up again?