PostgreSQL syntax check without running the query

Question

I want to verify the syntax of files containing sql queries before they can be committed in my CVS project.

In order to do that, I have a commitinfo script, but I have trouble finding out if the sql commands are valid. psql does not seem to have a dryrun mode, and constructing my own postgresql-dialact tester from the grammar (that is in the source) seems like a long stretch.

The scripts may contain multiple queries, so an EXPLAIN cannot be wrapped around them.

Any hints?

Deliver

i have related problem with SP on postgresql block not validated until its not called – Gonocyte

@triclosan: you may be interested in plpgsql lint that addresses exactly this shortcomming. Pavel Stěhule is the main developer. See this blog post. – Zoroaster

I'm not very experienced with postgres so this is probably a bad workaround not worthy of a real answer, but I just add a garbage line at the end of the script that I know will cause an error. If the first error it hits is the garbage line, I can be reasonably confident the rest of the script is ok. Unlike a transaction it preserves sequence values, and for simple scripts it's quicker and easier than downloading another utility. – Combustor

Answer 1

I recently wrote up a utility to statically check the syntax of SQL for PostgreSQL. It leverages ecpg, the embedded SQL C preproccessor for postgres, to check the SQL syntax, so it uses the exact same parser that is built in to Postgres itself.

You can check it out on github: http://github.com/markdrago/pgsanity. You can give the README a skim to get a better idea of how it works and to get directions for how to install it. Here's a short example of how pgsanity can be used:

$ pgsanity good1.sql good2.sql bad.sql
bad.sql: line 1: ERROR: syntax error at or near "bogus_token"

$ find -name '*.sql' | xargs pgsanity
./sql/bad1.sql: line 59: ERROR: syntax error at or near ";"
./sql/bad2.sql: line 41: ERROR: syntax error at or near "insert"
./sql/bad3.sql: line 57: ERROR: syntax error at or near "update"

Isosteric

That looks helpful. I will check into this soon – Deliver

Thanks for pgsanity! It's really handy. Is there any way to use pgsanity in systastic (github.com/scrooloose/syntastic)? It would be really awesome to run the check automatically when saving the file in vim. – Elihu

@Elihu I bet it wouldn't be hard to add it to syntastic. I've never used syntastic and I don't have the vim foo to add it on my own. But since pgsanity returns a 0 on success or a non-zero on failure I bet it would be relatively easy to add. – Isosteric

Great tool @MarkDrago saved me after hours of frustrated debugging. :-) – Buehler

Looks like this isn't being developed any more. Is that right? – Azine

The readme has updated 3 months ago, so someone is looking after it :) – Deliver

Answer 2

Use this trick to validate PostgreSQL code syntax:

DO $SYNTAX_CHECK$ BEGIN RETURN;
    -- insert your SQL code here
END; $SYNTAX_CHECK$;

Function is_sql(sql text) (link is to my GitHub)

Graduate

Will it be free of side effects if the query contains commit;? – Plump

@Plump yes, commit won't be run. You can verify with the following experiment: CREATE TABLE foo(id int); DO $$ BEGIN RETURN; BEGIN; INSERT INTO foo VALUES (1); COMMIT; END; $$; SELECT * FROM foo;. Even if you move the transaction's BEGIN outside of the DO block, the transaction still won't be committed. – Iodide

Looks like this trick accepts plpgsql syntax which isn't valid SQL. For example DO $$ BEGIN RETURN; IF true THEN select 1; END IF; END; $$; works while IF ... THEN ... END IF isn't valid outside of plpgsql. – Iodide

Hint for fellow noobs. If you get ERROR: syntax error at or near "END" then you are likely missing a semi-colon at the end of your SQL statement – Puppis

When your code SAVEPOINT my_savepoint; ...; ROLLBACK TO SAVEPOINT my_savepoint; gets inside the DO, you will get an error. But there is a workaround – Graduate

Answer 3

One way would be to put it into a transaction that you roll back at the end:

BEGIN;
<query>;
<query>;
<query>;
ROLLBACK;

Be aware that there are some effects that cannot be rolled back, like dblink calls, or anything written to the file system or incremented sequences.

I would advise cloning your database for testing purposes.

Zoroaster

This can only be done with an active connection. I would prefer to have a static check. And will this not break if I have BEGIN statements in my sql? – Deliver

@RobAu: Additional BEGIN; will be ignored. A WARNING will be issued. – Zoroaster

@RobAu: a static check will not work of dynamic queries. Well: not always. The only thing you can do is sandboxing and praying. – Mariomariology

this is a really bad idea. there are tons of valid sql statements that will throw errors depending on the state of the db. also transactions work on CRUD but not schema changes which is also SQL – Bourgeon

Answer 4

EXPLAIN (without ANALYZE) will parse the query and prepare an execution plan, without actually executing it.

https://www.postgresql.org/docs/current/static/sql-explain.html

Serge

Thanks for taking time to write an answer, but as I already explained in the question, I cannot use EXPLAIN. – Deliver

Be careful with this command! The pasted documentation specifically says "statement is actually executed when the ANALYZE option is used". So use BEGIN-ROLLBACK control statements – Osithe

Answer 5

I'm usually use Mimer online SQL validator, the only thing is that it check SQL syntax for standard SQL :

• SQL-92
• SQL-99
• SQL-03

and not specific for the PostgreSQL ... However if you write code following the standard you can use it and it work well ...

Gomphosis

The advantage of doing this is that you make it easier to switch databases. I love postgres, and it's been better in recent years, but for a long time its basic philosophy seemed to be "Standards? Where we're going, we don't need standards." – Bassoon

The disadvantage is that if you have table or column names that have uppercase letters and such, those identifiers need to be written in quotation marks, which I don't think is the convention in other databases. – Agility

Answer 6

A wonderful utility to verify SQL syntax: SQL Fiddle

Supports MySQL, Oracle, PostgreSQL, SQLite, MS SQL.

Oke

Answer 7

You could just wrap it in SELECT 1 ( <your query> ) AS a WHERE 1 = 0;

It'll fail on validation but it won't actually execute. Here's an example query plan:

Result  (cost=0.00..0.01 rows=1 width=0)
  One-Time Filter: false

Litigable

How do I wrap multiple sql statements in one select? – Deliver

Can you just run multiple select statements? Or you can use WITH blocks at the beginning. – Litigable

Answer 8

You can run queries iside postgresql function and raise exception in the end. All changes will be rolled back. For example:

CREATE OR REPLACE FUNCTION run_test(_sp character varying)
  RETURNS character varying AS
$BODY$
BEGIN
  EXECUTE 'SELECT ' || _sp;
  RAISE EXCEPTION '#OK';
EXCEPTION
  WHEN others THEN
    RETURN SQLERRM;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Another sollution - plpgsql_check extension (on github), the next incarnation of pgpsql_lint

Munn

you can write a wrapper returning void and include all DML to setup environment, run particular functions and queries, then exit with 'RAISE EXCEPTION'. try google for some kinde of pgunit. They use such technik – Munn