Обсуждение: Syntax checking DO blocks and ALTER TABLE statements?

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.

David G. Johnston <david.g.johnston@gmail.com> writes:

> On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr@gmail.com> 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

David G. Johnston <david.g.johnston@gmail.com> writes:

> On Tue, Feb 16, 2021 at 4:28 PM Tim Cross <theophilusx@gmail.com> wrote:
>
>>
>> David G. Johnston <david.g.johnston@gmail.com> writes:
>>
>> > On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr@gmail.com> 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

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.

Ron <ronljohnsonjr@gmail.com> 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

On 2/16/21 6:19 PM, Tim Cross wrote:
> Ron <ronljohnsonjr@gmail.com> 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.

>From: Ron <ronljohnsonjr@gmail.com>
>
>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
thestatements 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
hasfelt 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
daywhen 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,
youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or
attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately
notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them
todisk. Thank you.