Syntax checking DO blocks and ALTER TABLE statements?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Syntax checking DO blocks and ALTER TABLE statements?

Ron-2

How does one go about syntax checking this?

do $$
begin if exists (select 1 from information_schema.table_constraints
        where constraint_name = 'error_to_web_service_error') then
             raise notice 'EXISTS error_to_web_service_error';
     else
         ALTER TABLE web_service_error
            ADD CONSTRAINT error_to_web_service_error FOREIGN KEY (error_id)
            REFERENCES error_code(error_id)
            ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
     end if
end $$

(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in
similar DO blocks, and want to make sure the statements are clean.)

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Syntax checking DO blocks and ALTER TABLE statements?

David G Johnston
On Tue, Feb 16, 2021 at 3:43 PM Ron <[hidden email]> wrote:

How does one go about syntax checking this?

(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in
similar DO blocks, and want to make sure the statements are clean.)


Begin a transaction, execute the DO, capture an error if there is one, rollback the transaction.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Syntax checking DO blocks and ALTER TABLE statements?

Tim Cross

David G. Johnston <[hidden email]> writes:

> On Tue, Feb 16, 2021 at 3:43 PM Ron <[hidden email]> wrote:
>
>>
>> How does one go about syntax checking this?
>>
>> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in
>> similar DO blocks, and want to make sure the statements are clean.)
>>
>>
> Begin a transaction, execute the DO, capture an error if there is one,
> rollback the transaction.
>

As David points out, wrapping the whole thing in a transaction will at
least guarantee it all succeeds or it is all rollled back. This can be
frustrating if the statements are slow and there are a lot of them as it
can result in a very tedious do-run-fix cycle.

Something which can help is using an editor with good font highlighting
and parsing support. One interesting area I've not yet looked at is the
development of LSP (Language Server Protocol) servers for SQL. I've used
LSP for other languages with great success. The challenge with databases
is that there is enough variation between different vendor
implementations to make accurate parsing and validation tedious to
implement, so most solutions only focus on ANSI compliance. Still, that
can be very useful.

See https://github.com/lighttiger2505/sqls for one example of an LSP
server for SQL and https://microsoft.github.io/language-server-protocol/
for more background on LSP and what it can provide. Many editors,
including VSCode, VI, Emacs, TextMate etc now have some support for LSP.


--
Tim Cross


Reply | Threaded
Open this post in threaded view
|

Re: Syntax checking DO blocks and ALTER TABLE statements?

David G Johnston
On Tue, Feb 16, 2021 at 4:28 PM Tim Cross <[hidden email]> wrote:

David G. Johnston <[hidden email]> writes:

> On Tue, Feb 16, 2021 at 3:43 PM Ron <[hidden email]> wrote:
>
>>
>> How does one go about syntax checking this?
>>
>> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in
>> similar DO blocks, and want to make sure the statements are clean.)
>>
>>
> Begin a transaction, execute the DO, capture an error if there is one,
> rollback the transaction.
>

As David points out, wrapping the whole thing in a transaction will at
least guarantee it all succeeds or it is all rollled back. This can be
frustrating if the statements are slow and there are a lot of them as it
can result in a very tedious do-run-fix cycle.


I do presume that someone wanting to test their code in this manner would be doing so in a test environment and an empty database.  Which makes the execution time very small.

I personally would also solve the "lot of them" problem by using dynamic SQL, so one pretty much only has to test the code generator instead of all the actual executions - which can simply be confirmed fairly quickly once on a test database without the need for transactions.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Syntax checking DO blocks and ALTER TABLE statements?

Tim Cross

David G. Johnston <[hidden email]> writes:

> On Tue, Feb 16, 2021 at 4:28 PM Tim Cross <[hidden email]> wrote:
>
>>
>> David G. Johnston <[hidden email]> writes:
>>
>> > On Tue, Feb 16, 2021 at 3:43 PM Ron <[hidden email]> wrote:
>> >
>> >>
>> >> How does one go about syntax checking this?
>> >>
>> >> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping
>> in
>> >> similar DO blocks, and want to make sure the statements are clean.)
>> >>
>> >>
>> > Begin a transaction, execute the DO, capture an error if there is one,
>> > rollback the transaction.
>> >
>>
>> As David points out, wrapping the whole thing in a transaction will at
>> least guarantee it all succeeds or it is all rollled back. This can be
>> frustrating if the statements are slow and there are a lot of them as it
>> can result in a very tedious do-run-fix cycle.
>>
>>
> I do presume that someone wanting to test their code in this manner would
> be doing so in a test environment and an empty database.  Which makes the
> execution time very small.
>

True. However, it never ceases to amaze me how many places don't have
such environments. Far too often, my first task when commencing a new
engagement is to sort out environments and procedures to manage change.

> I personally would also solve the "lot of them" problem by using dynamic
> SQL, so one pretty much only has to test the code generator instead of all
> the actual executions - which can simply be confirmed fairly quickly once
> on a test database without the need for transactions.
>

Given the number, I think I would do the same. A good example of why
being 'lazy' can be a virtue. Faster and easier to write a procedure to
generate dynamic SQL than write out all those alter statements manually
or even write it using a scripting language and ODBC if there is
sufficient variation in the statements to make writing it in plsql
'messy'.

--
Tim Cross


Reply | Threaded
Open this post in threaded view
|

Re: Syntax checking DO blocks and ALTER TABLE statements?

Ron-2
On 2/16/21 5:44 PM, Tim Cross wrote:
> Given the number, I think I would do the same. A good example of why
> being 'lazy' can be a virtue. Faster and easier to write a procedure to
> generate dynamic SQL than write out all those alter statements manually
> or even write it using a scripting language and ODBC if there is
> sufficient variation in the statements to make writing it in plsql
> 'messy'.

In my case, the statements are generated by Ora2Pg, and the DO blocks are
generated by a bash script I wrote.  Input data can be messy, so want to
verify things before running.

Sure, vim is great at highlighting some problems, but certainly not all.

What I'm really looking for the PostgreSQL version of SQL Server's Ctrl-F5:
it runs just the parser and then stops,

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: Syntax checking DO blocks and ALTER TABLE statements?

Tim Cross

Ron <[hidden email]> writes:

> On 2/16/21 5:44 PM, Tim Cross wrote:
>> Given the number, I think I would do the same. A good example of why
>> being 'lazy' can be a virtue. Faster and easier to write a procedure to
>> generate dynamic SQL than write out all those alter statements manually
>> or even write it using a scripting language and ODBC if there is
>> sufficient variation in the statements to make writing it in plsql
>> 'messy'.
>
> In my case, the statements are generated by Ora2Pg, and the DO blocks are
> generated by a bash script I wrote.  Input data can be messy, so want to
> verify things before running.
>
> Sure, vim is great at highlighting some problems, but certainly not all.
>
> What I'm really looking for the PostgreSQL version of SQL Server's Ctrl-F5:
> it runs just the parser and then stops,

Sounds like exactly what LSP aims to provide. Don't know if the LSP SQL
servers available are mature enough yet, but that is definitely the
objective. Real benefit is that it is editor agnostic. Once your editor
has LSP support, all you need to do is configure the server details and
you get parsing, completion, re-factoring, definition lookup etc.

--
Tim Cross


Reply | Threaded
Open this post in threaded view
|

Re: Syntax checking DO blocks and ALTER TABLE statements?

Ron-2
On 2/16/21 6:19 PM, Tim Cross wrote:

> Ron <[hidden email]> writes:
>
>> On 2/16/21 5:44 PM, Tim Cross wrote:
>>> Given the number, I think I would do the same. A good example of why
>>> being 'lazy' can be a virtue. Faster and easier to write a procedure to
>>> generate dynamic SQL than write out all those alter statements manually
>>> or even write it using a scripting language and ODBC if there is
>>> sufficient variation in the statements to make writing it in plsql
>>> 'messy'.
>> In my case, the statements are generated by Ora2Pg, and the DO blocks are
>> generated by a bash script I wrote.  Input data can be messy, so want to
>> verify things before running.
>>
>> Sure, vim is great at highlighting some problems, but certainly not all.
>>
>> What I'm really looking for the PostgreSQL version of SQL Server's Ctrl-F5:
>> it runs just the parser and then stops,
> Sounds like exactly what LSP aims to provide. Don't know if the LSP SQL
> servers available are mature enough yet, but that is definitely the
> objective. Real benefit is that it is editor agnostic. Once your editor
> has LSP support, all you need to do is configure the server details and
> you get parsing, completion, re-factoring, definition lookup etc.

Naively, it seems that it should be easy to add this to psql.  The EXPLAIN
verb exists, so it shouldn't be hard to add SYNTAX, which does less work
than EXPLAIN.


--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

RE: Syntax checking DO blocks and ALTER TABLE statements?

Kevin Brannen
In reply to this post by Ron-2
>From: Ron <[hidden email]>
>
>How does one go about syntax checking this?
>
>do $$
>begin if exists (select 1 from information_schema.table_constraints
>        where constraint_name = 'error_to_web_service_error') then
>             raise notice 'EXISTS error_to_web_service_error';
>     else
>         ALTER TABLE web_service_error
>            ADD CONSTRAINT error_to_web_service_error FOREIGN KEY (error_id)
>            REFERENCES error_code(error_id)
>            ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
>     end if
>end $$
>
>(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in similar DO blocks, and want to make sure the statements are clean.)


I've always wondered why Pg doesn't have something like that built in, but I suppose the obvious answer is that no one has felt like scratching that itch.

Have you checked out:  https://github.com/okbob/plpgsql_check

I don't know if it'll do everything you want, but maybe it'd help at least some. It's on my to-do list to check out one day when I have time. :)

HTH,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.
Reply | Threaded
Open this post in threaded view
|

Re: Syntax checking DO blocks and ALTER TABLE statements?

Pavel Stehule


pá 19. 2. 2021 v 6:09 odesílatel Kevin Brannen <[hidden email]> napsal:
>From: Ron <[hidden email]>
>
>How does one go about syntax checking this?
>
>do $$
>begin if exists (select 1 from information_schema.table_constraints
>        where constraint_name = 'error_to_web_service_error') then
>             raise notice 'EXISTS error_to_web_service_error';
>     else
>         ALTER TABLE web_service_error
>            ADD CONSTRAINT error_to_web_service_error FOREIGN KEY (error_id)
>            REFERENCES error_code(error_id)
>            ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
>     end if
>end $$
>
>(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in similar DO blocks, and want to make sure the statements are clean.)


I've always wondered why Pg doesn't have something like that built in, but I suppose the obvious answer is that no one has felt like scratching that itch.

plpgsql_check is my cleaned previous project plpgsql_lint.  Main target of this work was integration to upstream. Unfortunately there is not an agreement on how this feature should be implemented  - there is a very fundamental difference in opinions, so this patch was rejected (I spent a lot of time working on this patch).  On second hand - with an outer development I had more space for faster more experimental development and I can quickly push new features to all supported pg releases (not just to fresh release). This is an advantage of extensions - the development can be much faster and because plpgsql_check is well isolated (it depends mostly only on plpgsql runtime), then the development and maintenance is not too difficult and expensive. There is only one harder task (for me) - making builds for MS Win.

Now plpgsql_check is a relatively bigger project - so it is hard to merge it to upstream, but it can live well on github simillary like PostGIS.

Regards

Pavel


Have you checked out:  https://github.com/okbob/plpgsql_check

I don't know if it'll do everything you want, but maybe it'd help at least some. It's on my to-do list to check out one day when I have time. :)

HTH,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.