Thread: bug in psql?
Is this a bug?
Executing of malformed (quote symbols) select in psql results in unexpected behaviour:
pg01:5432 postgres@db=# select 'any expression'any expression\d+;
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
════════╪══════════════════════════╪══════════╪══════════╪════════════╪══════════════
public │ geography_columns │ view │ postgres │ 0 bytes │
public │ geometry_columns │ view │ postgres │ 0 bytes │
public │ pg_buffercache │ view │ postgres │ 0 bytes │
public │ pg_stat_statements │ view │ postgres │ 0 bytes │
public │ raster_columns │ view │ postgres │ 0 bytes │
public │ raster_overviews │ view │ postgres │ 0 bytes │
public │ spatial_ref_sys │ table │ postgres │ 4616 kB │
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
════════╪══════════════════════════╪══════════╪══════════╪════════════╪══════════════
public │ geography_columns │ view │ postgres │ 0 bytes │
public │ geometry_columns │ view │ postgres │ 0 bytes │
public │ pg_buffercache │ view │ postgres │ 0 bytes │
public │ pg_stat_statements │ view │ postgres │ 0 bytes │
public │ raster_columns │ view │ postgres │ 0 bytes │
public │ raster_overviews │ view │ postgres │ 0 bytes │
public │ spatial_ref_sys │ table │ postgres │ 4616 kB │
...
Aleksey M Boltenkov.
so 15. 8. 2020 v 7:35 odesílatel Aleksey M Boltenkov <holybolt@rambler.ru> napsal:
Is this a bug?Executing of malformed (quote symbols) select in psql results in unexpected behaviour:pg01:5432 postgres@db=# select 'any expression'any expression\d+;
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
════════╪══════════════════════════╪══════════╪══════════╪════════════╪══════════════
public │ geography_columns │ view │ postgres │ 0 bytes │
public │ geometry_columns │ view │ postgres │ 0 bytes │
public │ pg_buffercache │ view │ postgres │ 0 bytes │
public │ pg_stat_statements │ view │ postgres │ 0 bytes │
public │ raster_columns │ view │ postgres │ 0 bytes │
public │ raster_overviews │ view │ postgres │ 0 bytes │
public │ spatial_ref_sys │ table │ postgres │ 4616 kB │...
I tested psql-12 and psql-14 and I don't see this issue
Pavel
Aleksey M Boltenkov.
"Aleksey M Boltenkov" <holybolt@rambler.ru> writes: > Is this a bug? > pg01:5432 postgres@db=# select 'any expression'any expression\d+; No. The "\d+;" is taken as a backslash command, and is executed. The rest of what you typed is still waiting in the query buffer. It's bit weird perhaps, since \d seems like a "do-something" command rather than a query buffer editing command. But psql uses the same rules for both cases. regards, tom lane
eksey M Boltenkov.
15.08.2020, 08:39, Pavel Stehule <pavel.stehule@gmail.com>so 15. 8. 2020 v 7:35 odesílatel Aleksey M Boltenkov <holybolt@rambler.ru> napsal:Is this a bug?Executing of malformed (quote symbols) select in psql results in unexpected behaviour:pg01:5432 postgres@db=# select 'any expression'any expression\d+;
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
════════╪══════════════════════════╪══════════╪══════════╪════════════╪══════════════
public │ geography_columns │ view │ postgres │ 0 bytes │
public │ geometry_columns │ view │ postgres │ 0 bytes │
public │ pg_buffercache │ view │ postgres │ 0 bytes │
public │ pg_stat_statements │ view │ postgres │ 0 bytes │
public │ raster_columns │ view │ postgres │ 0 bytes │
public │ raster_overviews │ view │ postgres │ 0 bytes │
public │ spatial_ref_sys │ table │ postgres │ 4616 kB │...I tested psql-12 and psql-14 and I don't see this issuePavelAleksey M Boltenkov.
You must have relations in search path, ifaiu.
engineer@pg02:~$ psql -U postgres -h localhost -p 5432 kaad
SET
Timing is on.
Null display is "¤".
Line style is unicode.
Unicode border line style is "single".
Unicode column line style is "single".
Unicode header line style is "double".
Output format is wrapped.
SET
Time: 0.234 ms
SET
Time: 0.206 ms
psql (12.4 (Ubuntu 12.4-1.pgdg18.04+1))
Type "help" for help.
SET
Timing is on.
Null display is "¤".
Line style is unicode.
Unicode border line style is "single".
Unicode column line style is "single".
Unicode header line style is "double".
Output format is wrapped.
SET
Time: 0.234 ms
SET
Time: 0.206 ms
psql (12.4 (Ubuntu 12.4-1.pgdg18.04+1))
Type "help" for help.
2020-08-15 09:29:18 localhost:5432 postgres@kaad=# select version();
version
═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)
version
═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)
Time: 6.682 ms
2020-08-15 09:29:24 localhost:5432 postgres@kaad=# select 'any expression'any expression\d+;
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
════════╪═══════════════════╪═══════╪══════════╪═════════╪═════════════
public │ geography_columns │ view │ postgres │ 0 bytes │
public │ geometry_columns │ view │ postgres │ 0 bytes │
public │ raster_columns │ view │ postgres │ 0 bytes │
public │ raster_overviews │ view │ postgres │ 0 bytes │
public │ spatial_ref_sys │ table │ postgres │ 4616 kB │
(5 rows)
2020-08-15 09:29:24 localhost:5432 postgres@kaad=# select 'any expression'any expression\d+;
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
════════╪═══════════════════╪═══════╪══════════╪═════════╪═════════════
public │ geography_columns │ view │ postgres │ 0 bytes │
public │ geometry_columns │ view │ postgres │ 0 bytes │
public │ raster_columns │ view │ postgres │ 0 bytes │
public │ raster_overviews │ view │ postgres │ 0 bytes │
public │ spatial_ref_sys │ table │ postgres │ 4616 kB │
(5 rows)
[more] - > ; -- semicolon is manually added
ERROR: 42601: syntax error at or near "any"
LINE 1: select 'any expression'any expression
^
LOCATION: scanner_yyerror, scan.l:1149
Time: 0.530 ms
ERROR: 42601: syntax error at or near "any"
LINE 1: select 'any expression'any expression
^
LOCATION: scanner_yyerror, scan.l:1149
Time: 0.530 ms
Aleksey M Boltenkov.
15.08.2020, 08:56, Tom Lane <tgl@sss.pgh.pa.us>"Aleksey M Boltenkov" <holybolt@rambler.ru> writes:
> Is this a bug?
> pg01:5432 postgres@db=# select 'any expression'any expression\d+;
No. The "\d+;" is taken as a backslash command, and is executed.
The rest of what you typed is still waiting in the query buffer.
It's bit weird perhaps, since \d seems like a "do-something"
command rather than a query buffer editing command. But
psql uses the same rules for both cases.
regards, tom lane
Yes, i have used it for a long, but it was always wellformed, so malformed query was some kind of surprise.
select format('insert into x.y ( field, ... ) select val, ... from %I.%I on conflict ( constraint ) do nothing;\dti+ x.y*', (regexp_match(tablename, '\d[\d_]+$'))[1], schemaname, tablename) from pg_tables where schemaname = 'x' and tablename ~ '^y' order by 1;
insert into x.y ( field, ... ) select '2020_07_29'::date, field, ... from x.y_archive_2020_07_29 on conflict ( constraint ) do nothing;\dti+ x.y_new_scheme*
insert into x.y ( field, ... ) select '2020_08_05'::date, field, ... from x.y_archive_2020_08_05 on conflict ( constraint ) do nothing;\dti+ x.y_new_scheme*
insert into x.y ( field, ... ) select '2020_08_12'::date, field, ... from x.y_archive_2020_08_12 on conflict ( constraint ) do nothing;\dti+ x.y_new_scheme*
insert into x.y ( field, ... ) select '2020_08_05'::date, field, ... from x.y_archive_2020_08_05 on conflict ( constraint ) do nothing;\dti+ x.y_new_scheme*
insert into x.y ( field, ... ) select '2020_08_12'::date, field, ... from x.y_archive_2020_08_12 on conflict ( constraint ) do nothing;\dti+ x.y_new_scheme*
\gexec
Aleksey M Boltenkov.
so 15. 8. 2020 v 8:35 odesílatel Aleksey M Boltenkov <holybolt@rambler.ru> napsal:
eksey M Boltenkov.15.08.2020, 08:39, Pavel Stehule <pavel.stehule@gmail.com>so 15. 8. 2020 v 7:35 odesílatel Aleksey M Boltenkov <holybolt@rambler.ru> napsal:Is this a bug?Executing of malformed (quote symbols) select in psql results in unexpected behaviour:pg01:5432 postgres@db=# select 'any expression'any expression\d+;
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
════════╪══════════════════════════╪══════════╪══════════╪════════════╪══════════════
public │ geography_columns │ view │ postgres │ 0 bytes │
public │ geometry_columns │ view │ postgres │ 0 bytes │
public │ pg_buffercache │ view │ postgres │ 0 bytes │
public │ pg_stat_statements │ view │ postgres │ 0 bytes │
public │ raster_columns │ view │ postgres │ 0 bytes │
public │ raster_overviews │ view │ postgres │ 0 bytes │
public │ spatial_ref_sys │ table │ postgres │ 4616 kB │...I tested psql-12 and psql-14 and I don't see this issuePavelAleksey M Boltenkov.You must have relations in search path, ifaiu.engineer@pg02:~$ psql -U postgres -h localhost -p 5432 kaad
SET
Timing is on.
Null display is "¤".
Line style is unicode.
Unicode border line style is "single".
Unicode column line style is "single".
Unicode header line style is "double".
Output format is wrapped.
SET
Time: 0.234 ms
SET
Time: 0.206 ms
psql (12.4 (Ubuntu 12.4-1.pgdg18.04+1))
Type "help" for help.2020-08-15 09:29:18 localhost:5432 postgres@kaad=# select version();
version
═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)Time: 6.682 ms
2020-08-15 09:29:24 localhost:5432 postgres@kaad=# select 'any expression'any expression\d+;
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
════════╪═══════════════════╪═══════╪══════════╪═════════╪═════════════
public │ geography_columns │ view │ postgres │ 0 bytes │
public │ geometry_columns │ view │ postgres │ 0 bytes │
public │ raster_columns │ view │ postgres │ 0 bytes │
public │ raster_overviews │ view │ postgres │ 0 bytes │
public │ spatial_ref_sys │ table │ postgres │ 4616 kB │
(5 rows)[more] - > ; -- semicolon is manually added
ERROR: 42601: syntax error at or near "any"
LINE 1: select 'any expression'any expression
^
LOCATION: scanner_yyerror, scan.l:1149
Time: 0.530 ms
it is expected result from my perspective
there is same error if you run your query without \d
postgres=# select 'any expression 'any expression;
ERROR: syntax error at or near "any"
LINE 1: select 'any expression 'any expression;
ERROR: syntax error at or near "any"
LINE 1: select 'any expression 'any expression;
Aleksey M Boltenkov.
15.08.2020, 09:46, Aleksey M Boltenkov <holybolt@rambler.ru>15.08.2020, 08:56, Tom Lane <tgl@sss.pgh.pa.us>"Aleksey M Boltenkov" <holybolt@rambler.ru> writes:
> Is this a bug?
> pg01:5432 postgres@db=# select 'any expression'any expression\d+;
No. The "\d+;" is taken as a backslash command, and is executed.
The rest of what you typed is still waiting in the query buffer.
It's bit weird perhaps, since \d seems like a "do-something"
command rather than a query buffer editing command. But
psql uses the same rules for both cases.
regards, tom laneYes, i have used it for a long, but it was always wellformed, so malformed query was some kind of surprise.select format('insert into x.y ( field, ... ) select val, ... from %I.%I on conflict ( constraint ) do nothing;\dti+ x.y*', (regexp_match(tablename, '\d[\d_]+$'))[1], schemaname, tablename) from pg_tables where schemaname = 'x' and tablename ~ '^y' order by 1;insert into x.y ( field, ... ) select '2020_07_29'::date, field, ... from x.y_archive_2020_07_29 on conflict ( constraint ) do nothing;\dti+ x.y_new_scheme*
insert into x.y ( field, ... ) select '2020_08_05'::date, field, ... from x.y_archive_2020_08_05 on conflict ( constraint ) do nothing;\dti+ x.y_new_scheme*
insert into x.y ( field, ... ) select '2020_08_12'::date, field, ... from x.y_archive_2020_08_12 on conflict ( constraint ) do nothing;\dti+ x.y_new_scheme*\gexecAleksey M Boltenkov.
Sorry, \gexec does not work, copy and paste.
Aleksey M Boltenkov.
15.08.2020, 09:49, Pavel Stehule <pavel.stehule@gmail.com>so 15. 8. 2020 v 8:35 odesílatel Aleksey M Boltenkov <holybolt@rambler.ru> napsal:eksey M Boltenkov.15.08.2020, 08:39, Pavel Stehule <pavel.stehule@gmail.com>so 15. 8. 2020 v 7:35 odesílatel Aleksey M Boltenkov <holybolt@rambler.ru> napsal:Is this a bug?Executing of malformed (quote symbols) select in psql results in unexpected behaviour:pg01:5432 postgres@db=# select 'any expression'any expression\d+;
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
════════╪══════════════════════════╪══════════╪══════════╪════════════╪══════════════
public │ geography_columns │ view │ postgres │ 0 bytes │
public │ geometry_columns │ view │ postgres │ 0 bytes │
public │ pg_buffercache │ view │ postgres │ 0 bytes │
public │ pg_stat_statements │ view │ postgres │ 0 bytes │
public │ raster_columns │ view │ postgres │ 0 bytes │
public │ raster_overviews │ view │ postgres │ 0 bytes │
public │ spatial_ref_sys │ table │ postgres │ 4616 kB │...I tested psql-12 and psql-14 and I don't see this issuePavelAleksey M Boltenkov.You must have relations in search path, ifaiu.engineer@pg02:~$ psql -U postgres -h localhost -p 5432 kaad
SET
Timing is on.
Null display is "¤".
Line style is unicode.
Unicode border line style is "single".
Unicode column line style is "single".
Unicode header line style is "double".
Output format is wrapped.
SET
Time: 0.234 ms
SET
Time: 0.206 ms
psql (12.4 (Ubuntu 12.4-1.pgdg18.04+1))
Type "help" for help.2020-08-15 09:29:18 localhost:5432 postgres@kaad=# select version();
version
═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)Time: 6.682 ms
2020-08-15 09:29:24 localhost:5432 postgres@kaad=# select 'any expression'any expression\d+;
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
════════╪═══════════════════╪═══════╪══════════╪═════════╪═════════════
public │ geography_columns │ view │ postgres │ 0 bytes │
public │ geometry_columns │ view │ postgres │ 0 bytes │
public │ raster_columns │ view │ postgres │ 0 bytes │
public │ raster_overviews │ view │ postgres │ 0 bytes │
public │ spatial_ref_sys │ table │ postgres │ 4616 kB │
(5 rows)[more] - > ; -- semicolon is manually added
ERROR: 42601: syntax error at or near "any"
LINE 1: select 'any expression'any expression
^
LOCATION: scanner_yyerror, scan.l:1149
Time: 0.530 msit is expected result from my perspectivethere is same error if you run your query without \dpostgres=# select 'any expression 'any expression;
ERROR: syntax error at or near "any"
LINE 1: select 'any expression 'any expression;
Aleksey M Boltenkov.
Thank you, i just was impressed by execution of part of malformed select expression by psql as cli part.
Aleksey M Boltenkov.