validate synatax

On 10 December 2013 22:40, Peter Kroon <plakroon@gmail.com> wrote:

Hi,

How can I validate any query on PostgreSQL without executing the sql.
I was able with EXPLAIN to find some errors. However this only worked with
a SELECT statement. When i tried to create a TABLE it would not run.
I do not wish to install external packages. Preferably use only default
present features.

http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT#PostgreSQL_EXPLAIN

Any suggestions are welcome.

Best,
Peter

Why do you want to do that? Maybe just test it, there can be many more
problems then just not executing query, the query logic could be bad, even
if the syntax is correct. You can always run it inside transaction and
rollback at the end.

regards,
Szymon

Why do you want to do that?

I want to validate the SQL syntax and preferably in the browser using some
kind of linter.

You can always run it inside transaction and rollback at the end.

Sounds dangerous and will make the server very active because it is
executing the SQL.

Yes, the SQL should be tested even if the syntax is correct.

So 2 things to look for:
- Syntax validator
- Check query logic, like does the table exist

Best,
Peter

2013/12/10 Szymon Guz <mabewlun@gmail.com>

Peter Kroon <plakroon@gmail.com> writes:

Hi,

How can I validate any query on PostgreSQL without executing the sql.
I was able with EXPLAIN to find some errors. However this only worked with a SELECT statement. When i tried to create a TABLE it would not run.
I do not wish to install external packages. Preferably use only default present features.

http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT#PostgreSQL_EXPLAIN

Any suggestions are welcome.

/yomama
=# create or replace function foo() returns void as $$create table foo()$$ language sql;
CREATE FUNCTION
/yomama
=# create or replace function foo() returns void as $$create table foo() bogus syntax$$ language sql;
ERROR: syntax error at or near "bogus"
LINE 1: ...nction foo() returns void as $$create table foo() bogus synt...
^
/yomama
=# select version();
version
----------------------------------------------------------------------------------------------
PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
(1 row)

Best,
Peter

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10 December 2013 22:57, Peter Kroon <plakroon@gmail.com> wrote:

Why do you want to do that?

I want to validate the SQL syntax and preferably in the browser using some
kind of linter.

You can always run it inside transaction and rollback at the end.

Sounds dangerous and will make the server very active because it is
executing the SQL.

Yes, the SQL should be tested even if the syntax is correct.

So 2 things to look for:
- Syntax validator
- Check query logic, like does the table exist

Best,
Peter

2013/12/10 Szymon Guz <mabewlun@gmail.com>

On 10 December 2013 22:40, Peter Kroon <plakroon@gmail.com> wrote:

Hi,

How can I validate any query on PostgreSQL without executing the sql.
I was able with EXPLAIN to find some errors. However this only worked
with a SELECT statement. When i tried to create a TABLE it would not run.
I do not wish to install external packages. Preferably use only default
present features.

http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT#PostgreSQL_EXPLAIN

Any suggestions are welcome.

Best,
Peter

Why do you want to do that? Maybe just test it, there can be many more
problems then just not executing query, the query logic could be bad, even
if the syntax is correct. You can always run it inside transaction and
rollback at the end.

regards,
Szymon

So it seems like you want to have the impossible thing:

Syntax validator - you can always run explain... it should validate the
simple query syntax. However it will not work for more complicated syntax,
like imagine a procedure which creates a string from its arguments, and
this string is then evaluated as a query. There is no way to be sure that
it works other than just executing the procedure.

Query logic - another thing not doable without real query execution. What
you want is to have this query executed without execution. What's more, if
this would be doable, it won't give you enough information, as information
that the query runs now, will not mean that this query will run in the
future when you will be running that for real.

I really don't know how to create a simple validator (without execution)
which would say that the following query is OK:

begin; create table x(a int); insert into x(a) values (10); end;

This would simply be as complicated as the database itself, and I'm sure
that if I had to implement such a validator, I would just finish with
embedding the query in a transaction rolled back at the end, and run it on
some test database.

regards,
Szymon

On Wed, Dec 11, 2013 at 9:11 PM, Szymon Guz <mabewlun@gmail.com> wrote:

This would simply be as complicated as the database itself, and I'm sure
that if I had to implement such a validator, I would just finish with
embedding the query in a transaction rolled back at the end, and run it on
some test database.

Such a *query validator* needs to go through the database parser of
gram.y and in rewriter to check for some restrictions as well (maybe
some stuff in planner but I cannot recall what now), so you need to
minimize the execution time of the query in planner and executor for
DML/SELECT, and bypass utility execution for a DDL and a utility. You
could use the planner hook to generate a dummy plan that will 1) skip
the planner, 2) minimize the query execution and check only if the
query has a valid grammar for DML/SELECT, and the utility hook to skip
all the DDL/utility executions.

Regards,
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

you can use the transition.
eg:
begin;
creat table(...);
catch error if the statement not validated.
rollback;

jov
在 2013-12-11 上午5:43，"Peter Kroon" <plakroon@gmail.com>写道：

Perhaps creating a temporary table would be more efficient.
Then the rollback isn't necessary.

2013/12/11 Jov <zhao6014@gmail.com>

On 11 December 2013 15:35, Peter Kroon <plakroon@gmail.com> wrote:

Perhaps creating a temporary table would be more efficient.
Then the rollback isn't necessary.

Yep, but only if you're sure that you don't have queries which change other
parts of the database, including calling procedures which could change data
or structure. That's why I like the fact that even DDLs are transactional
in Postgres :)

Szymon