Better reporting for database connection issues

[mmoncure]

Bug report

test.sql:
INSERT INTO foo DEFAULT VALUES ON CONFLICT DO NOTHING;

...is parsing, but takes ~ 10 seconds to parse. This is slow enough to be considered bug IMNSHO.

[psteinroe]

Thanks for reporting! Can you share a bit about your setup / config / logs? This is definitely not normal.

[mmoncure]

Sure:
O/S mac

version: 0.3.1 (via pgtools binary suite, the compiling instructions have been pulled, heh)
lrwxr-xr-x@ 1 merlin.moncure staff 34B Apr 9 15:27 pgtools -> postgrestools_aarch64-apple-darwin

time ~/pgtools/pgtools check ~/Downloads/test.sql
Checked 1 file in 10s. No fixes applied.

real 0m10.077s
user 0m0.011s
sys 0m0.030s

command:
DT7FN214T3:output merlin.moncure$ ~/pgtools/pgtools -V
Version: 0.3.1

Checking, postgrestools.jsonc
I see: "connTimeoutSecs": 10

ok, so there we go. It's not super clear that INSERT introduced a connection, so maybe we could look at this as feedback issue -- if connection is attempted, and especially if fails, we ought to have feedbac.

restoring the connection, I get:

✖ relation "foo" does not exist

2 │ INSERT INTO foo DEFAULT VALUES ON CONFLICT DO NOTHING;
│ ^^^
3 │

✖ Error Code: 42P01

Ok, this makes sense, but it raises more questions; why does simple syntax checking write to the database? This is source level initialization script.

[psteinroe]

That makes sense! Can you try to pass --skip-db (ref)? This will skip all checks that require a database connection.

[mmoncure]

Yep, that definitely addresses the symptoms. I guess I would register two points of concern:

#1: The default behavior of 'check', to execute DML is dangerous IMO. Particularly if psql directives are not honored.. the structure of this file was something like:
\if init=1
create this...
insert that...
\endif

I can understand referencing a database for things like column completion, but not in this context. This seems a bit dangerous IMO, (based on very superficial analysis); need to study this more. My gut says parser/checker use the database as a parse cache of various objects rather try and execute DML unless you very specifically tell it to do that.

#2: As noted upthread, connection failure fails w/o output. From command line perspective, it ought to just fail? It might be perceived (as I did) as slow performance

[psteinroe]

thanks for your feedback!

The default behavior of 'check', to execute DML is dangerous IMO.

we do not execute anything. We are just PREPAREing the statements. This is minimal impact on the database, and all prepared statements are dropped when the sessions ends. This is a common practice of dev tools like sqlx.

As noted upthread, connection failure fails w/o output. From command line perspective, it ought to just fail? It might be perceived (as I did) as slow performance

Fully agree here! We need to report that better. Will keep this issue open for that.