Thread: 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.
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.
David J.
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
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.
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.
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.
pá 19. 2. 2021 v 6:09 odesílatel Kevin Brannen <KBrannen@efji.com> napsal:
>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 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.