Re: PostgreSQL 10.12. background writer not cleaning buffers, small working set, big shared_buffers

[Rene Romero Benavides]

Btw, these are its wal / checkpoint related settings:

activemq1=# select name,setting,unit from pg_settings where category =
'Write-Ahead Log / Checkpoints';
 name | setting | unit
--+-+--
 checkpoint_completion_target | 0.9 |
 checkpoint_flush_after   | 32  | 8kB
 checkpoint_timeout   | 300 | s
 checkpoint_warning   | 30  | s
 max_wal_size | 4096| MB
 min_wal_size | 2048| MB

Thank you.

On Fri, Aug 14, 2020 at 1:39 AM Rene Romero Benavides <
rene.romer...@gmail.com> wrote:

> Hello Postgres community.
> In a few words I'm figuring out this stats from the background writer
> where you can see buffers_clean is 0 and buffers_backend = 44849371, I
> would like it to be the other way around, that's more efficient, right?
>
> -[ RECORD 1 ]-+--
> checkpoints_timed | 97504
> checkpoints_req   | 4
> checkpoint_write_time | 21171407908
> checkpoint_sync_time  | 684641
> buffers_checkpoint| 273934008
> buffers_clean | 0
> maxwritten_clean  | 0
> buffers_backend   | 44849371
> buffers_backend_fsync | 0
> buffers_alloc | 44744713
> stats_reset   | 2019-09-10 08:42:34.490899-07
>
> Using default settings
> postgres=# select name,setting,unit from pg_settings where name like
> '%bgwriter%'
> ;
>   name   | setting | unit
> -+-+--
>  bgwriter_delay  | 200 | ms
>  bgwriter_flush_after| 64  | 8kB
>  bgwriter_lru_maxpages   | 100 |
>  bgwriter_lru_multiplier | 2   |
>
> shared_buffers = '6GB'
> and the VM has a RAM of 24GB
>
> The working set is about 160MB, I know, shared buffers are oversized for
> this DB, and I know that the bgwriter is supposed to do work when the
> working set doesn't fit into shared_buffers, but backends cleaning their
> buffers produces undesirable sync waits, right ?
> Any advice ?
>
> Thank you.
>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
>
>
>

-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Block Corruption Resolution

[Brajendra Pratap Singh]

Hi ,

Is it possible to recover only corrupted blocks in postgresql versions
9.6/10/11/12?

Is the pg_healer extension supported by postgresql or good for the
corrupted block recovery? If yes then is this compatible with all
postgresql versions 9.6/10/11/12?

Thanks,
Brajendra

Re: Server goes to Recovery Mode when run a SQL

[PegoraroF10]

This is the way you can test to push your server immediatelly to recovery
mode. This one you´ll get the problem instantaneously, differently from the
last one which you have to wait a long time to happen.

A master detail relation with an additional table of configurations. Usually
this master has 20 or 40 thousand records and detail has 10 or 20 times
more. Configuration table is a key-value records, both text which I store
values of any type, just prefixing it with S: for string B: for boolean and
so on.

Tests were done on ...
PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

drop table if exists ctr;create table ctr(Contrato_ID integer primary key,
Matricula Date, Status smallint);
drop table if exists rec;create table rec(Receber_ID serial primary key,
Contrato_ID integer, Parcela smallint, TipoRecebimento smallint, Vencimento
Date, Quitacao Date, Valor Numeric, Calculo Numeric, ValorPago Numeric);
drop table if exists var;create table var(Var_ID serial primary key, Name
text, VarValue text);

Populate master detail tables with some random values ...

with Contratos(Contrato_ID, Matricula, Status, Parcelas, ValorParcela,
Quitadas) as (select g, (Current_Date - (trunc(random()*2000)||'
day')::interval)::Date, trunc(random()*9)+1,
greatest(trunc(random()*20)::integer,11), (random()*200+100)::numeric(15,2),
least(trunc(random()*20)::integer,5) from generate_series(1,2,1) g),
  Receber(Contrato_ID, Parcela, TipoRecebimento, Vencimento, Valor) as
(select Contrato_ID, Linha, trunc(random()*10), Matricula + ('1
month'::interval * linha), ValorParcela from Contratos join lateral (select
Linha from generate_series(1,Parcelas,1) Linha) g on true),
  insContratos as (insert into CTR select Contrato_ID, Matricula, Status
from Contratos returning *),
  insReceber as (insert into REC(Contrato_ID, Parcela, TipoRecebimento,
Vencimento, Quitacao, Valor, Calculo, ValorPago) select R.Contrato_ID,
Parcela, TipoRecebimento, Vencimento, case when Parcela<=Quitadas then
Vencimento else null end, Valor, Valor*.9, case when Parcela<=Quitadas then
Valor else null end from Receber R inner join Contratos using(Contrato_ID)
returning *)
select (select count(*) from insContratos), count(*) from insReceber;

populate configuration table ... (this table usually has 2 or 3 thousand
records but with these ones the problem occurred already)

insert into var(Name, VarValue) select * from (Values
('/Config/StatusVisitaObrigaRetorno','S:2,17'),
('/Config/TemWhatsApp','B:True'),
('/Config/TempoRetornarAtriuido','S:03:00'),
('/Config/TempoRetornarTrabalhando','S:25'),
('/Config/Timezone','S:America/Sao_Paulo'),
('/Config/TipoFonteFunilVendas','I:7'),
('/Config/TipoRecebimentoCancelamento','S:6,7,10'),
('/Config/TipoRecebimentoPadraoCobranca','S:4'),
('/Config/TitularIdadeMinima','F:18'),
('/Config/TreinamentoCaixa','B:True'),
('/Config/TreinamentoNaoAlunos','S:Palestra'),
('/Config/TurmaContrato','B:False')) x;

This one works because I used a CTE

explain (analyze, buffers, verbose, costs) with Vars(VarValue) as (select
string_to_array(substring(VarValue from 3),',')::smallint[] from Var where
Name = '/Config/TipoRecebimentoCancelamento')
select C.contrato_id,
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Vencimento < current_date) and (Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Vencimento < current_date) and (Quitacao is null)),0),
NullIf(sum(calculo) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Vencimento < current_date) and (Quitacao is null)),0),
NullIf(min(Vencimento::date) filter (where ((status <> 6) or
(TipoRecebimento = any(VarValue))) and (Quitacao is null)), null),
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Quitacao is null)),0),
NullIf(sum(Valor),0),
NullIf(sum(ValorPago),0)
from ctr C left join rec using(Contrato_ID) cross join Vars group by
C.Contrato_ID;

This is the one my server goes to recovery mode.

explain (analyze, buffers, verbose, costs) select C.contrato_id,
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Vencimento < current_date) and
(Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Vencimento < current_date) and
(Quitacao is null)),0),
NullIf(sum(calculo) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Vencimento < current_date) and
(Quitaca

Postgres Clusterisation best choice ?

[Anass El Bouanani]

Hi,

Please, can you help me to choose the best way to install an postres
clustersation architecture, with replication sites ? can I use Openshift ?
or there is a better solution ?

Thanks a lot for your help.

Best regards,

Re: Server goes to Recovery Mode when run a SQL

[Tom Lane]

PegoraroF10  writes:
> This is the way you can test to push your server immediatelly to recovery
> mode. This one you´ll get the problem instantaneously, differently from the
> last one which you have to wait a long time to happen.

When I try this I get

ERROR:  operator does not exist: smallint = text
LINE 2: ...eber_ID) filter (where ((status <> 6) or (TipoRecebimento in
 ^
HINT:  No operator matches the given name and argument types. You might need to 
add explicit type casts.

which makes me wonder whether you've got a buggy custom operator.

regards, tom lane

Re: PostgreSQL 10.12. background writer not cleaning buffers, small working set, big shared_buffers

[Rene Romero Benavides]

Any ideas on this guys?

Thank you.

On Fri, Aug 14, 2020 at 1:39 AM Rene Romero Benavides <
rene.romer...@gmail.com> wrote:

> Hello Postgres community.
> In a few words I'm figuring out this stats from the background writer
> where you can see buffers_clean is 0 and buffers_backend = 44849371, I
> would like it to be the other way around, that's more efficient, right?
>
> -[ RECORD 1 ]-+--
> checkpoints_timed | 97504
> checkpoints_req   | 4
> checkpoint_write_time | 21171407908
> checkpoint_sync_time  | 684641
> buffers_checkpoint| 273934008
> buffers_clean | 0
> maxwritten_clean  | 0
> buffers_backend   | 44849371
> buffers_backend_fsync | 0
> buffers_alloc | 44744713
> stats_reset   | 2019-09-10 08:42:34.490899-07
>
> Using default settings
> postgres=# select name,setting,unit from pg_settings where name like
> '%bgwriter%'
> ;
>   name   | setting | unit
> -+-+--
>  bgwriter_delay  | 200 | ms
>  bgwriter_flush_after| 64  | 8kB
>  bgwriter_lru_maxpages   | 100 |
>  bgwriter_lru_multiplier | 2   |
>
> shared_buffers = '6GB'
> and the VM has a RAM of 24GB
>
> The working set is about 160MB, I know, shared buffers are oversized for
> this DB, and I know that the bgwriter is supposed to do work when the
> working set doesn't fit into shared_buffers, but backends cleaning their
> buffers produces undesirable sync waits, right ?
> Any advice ?
>
> Thank you.
>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
>
>
>

-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: Fwd: changing password pgagent service account

[Diego]

yep, or the password does not meet the windows requirements.

On 2020-08-13 14:31, Adrian Klaver wrote:

On 8/13/20 10:19 AM, Henry Francisco Garcia Cortez wrote:
Good morning I need to help I installed pgagent but I want to change 
password pgagent service account


r5w83.png


To me it looks like you are creating an account rather then changing a 
password. Also looks like it might be failing on the user name length.



--
Ing. Henry G. Cortez

index numbers in pg_restore list

[Ben Madin]

Dear all,

I was hoping for some quick guidance on the structure of the pg_restore
list.

We have a database which we are restoring each day for a data warehouse.
For efficiency, we are not restoring a number of tables and functions, just
the tables, indexes and data.

(the database is being dumped on a postgresql 10.13 database, and restored
on a 12.4)

To do this we had been using a template list file, with the items we wanted
(around 1900 of them) However, after a week or so it started failing with a
message the ID 9601 was missing. on checking this was:

9601; 0 19580 TABLE DATA backoffice animalid angus

when we ran the list option on the dump, the above row was present, but the
index number is now 9602 (this was previously something else).

The text in the manual pages notes:

 the numbers at the start of lines refer to the internal archive ID
assigned to each item.

we were under the impression that this number was like an oid? I guess then
my question is:

" Is the index number ( the archive ID)  assigned at the time of creation
of the archive and not otherwise related to the item in the
original database?"

If this is mutable, I presume we need to write a script to grep the lines
we need out of the list of the current dump, not re-use the same list file?

cheers

Ben


-- 

[image: Ausvet Logo] 

Dr Ben Madin

BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <+61448887220>
E-mail:
b...@ausvet.com.au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia

Re: index numbers in pg_restore list

[Adrian Klaver]

On 8/14/20 9:16 AM, Ben Madin wrote:

Dear all,

I was hoping for some quick guidance on the structure of the pg_restore 
list.


We have a database which we are restoring each day for a data warehouse. 
For efficiency, we are not restoring a number of tables and functions, 
just the tables, indexes and data.


(the database is being dumped on a postgresql 10.13 database, and 
restored on a 12.4)


To do this we had been using a template list file, with the items we 
wanted (around 1900 of them) However, after a week or so it started 
failing with a message the ID 9601 was missing. on checking this was:


9601; 0 19580 TABLE DATA backoffice animalid angus

when we ran the list option on the dump, the above row was present, but 
the index number is now 9602 (this was previously something else).


The text in the manual pages notes:

  the numbers at the start of lines refer to the internal archive ID 
assigned to each item.


we were under the impression that this number was like an oid? I guess 
then my question is:


" Is the index number ( the archive ID)  assigned at the time of 
creation of the archive and not otherwise related to the item in the 
original database?"


From pg_dump.c:

 /*
 * Create archive TOC entries for all the objects to be dumped, 
in a safe

 * order.
 */

/* First the special ENCODING, STDSTRINGS, and SEARCHPATH 
entries. */

dumpEncoding(fout);
dumpStdStrings(fout);
dumpSearchPath(fout);

/* The database items are always next, unless we don't want 
them at all */

if (dopt.outputCreateDB)
dumpDatabase(fout);

/* Now the rearrangeable objects. */
for (i = 0; i < numObjs; i++)
dumpDumpableObject(fout, dobjs[i]);



If this is mutable, I presume we need to write a script to grep the 
lines we need out of the list of the current dump, not re-use the same 
list file?


cheers

Ben


--

Ausvet Logo 

Dr Ben Madin
BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 
E-mail:
b...@ausvet.com.au 
Website:
www.ausvet.com.au 
Skype:benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Server goes to Recovery Mode when run a SQL

[PegoraroF10]

Ok, it works if I cast TipoRecebimento to text.

(TipoRecebimento::text in (select substring(VarValue from 3) from Var where
Name = '/Config/TipoRecebimentoCancelamento'))

But now, how can I know what custom operator it´s trying to use, because
some day I can get in troubles again because that.



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Server goes to Recovery Mode when run a SQL

[PegoraroF10]

Correct, on yours it gives an error message, but on mine it tries to execute
and goes to recovery.

So, how can I know which custom operator is being used on that comparison ?



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: index numbers in pg_restore list

[Tom Lane]

Ben Madin  writes:
> " Is the index number ( the archive ID)  assigned at the time of creation
> of the archive and not otherwise related to the item in the
> original database?"

Yes, it's just assigned internally during pg_dump.  I think the numbers
would hold still as long as you make no DDL changes in the database, but
as soon as you do, all bets are off.

regards, tom lane

Re: index numbers in pg_restore list

[Ben Madin]

Thanks Tom and Adrian,

The clarity is helpful - We'll run up a solution to specifically choose the
elements.

cheers

Ben


On Sat, 15 Aug 2020 at 00:45, Tom Lane  wrote:

> Ben Madin  writes:
> > " Is the index number ( the archive ID)  assigned at the time of creation
> > of the archive and not otherwise related to the item in the
> > original database?"
>
> Yes, it's just assigned internally during pg_dump.  I think the numbers
> would hold still as long as you make no DDL changes in the database, but
> as soon as you do, all bets are off.
>
> regards, tom lane
>


-- 

[image: Ausvet Logo] 

Dr Ben Madin

BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <+61448887220>
E-mail:
b...@ausvet.com.au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia

Re: Server goes to Recovery Mode when run a SQL

[Tom Lane]

PegoraroF10  writes:
> So, how can I know which custom operator is being used on that comparison ?

You tell us.  I don't know of any common extension that would create
a "smallint = text" operator.  (A variant theory is that you didn't
make a new operator, but an implicit cast from smallint to text.
Either way, it almost certainly is a homebrew thing.  Or maybe what
you are running is not stock Postgres?)

It might be interesting to try some direct test, like

SELECT 22::smallint = 'foo'::text;

For me that gives the same "operator does not exist: smallint = text"
error, but I bet your database tries to execute it.

regards, tom lane

Re: Server goes to Recovery Mode when run a SQL

[PegoraroF10]

It works, shouldn´t but works. Results False

And I have created these two operators a long time ago. 

CREATE OPERATOR public.= (
FUNCTION = public.fntextonumero,
LEFTARG = text,
RIGHTARG = public.i32
);
CREATE OPERATOR public.= (
FUNCTION = public.fntextonumero,
LEFTARG = public.i32,
RIGHTARG = text
);

Droped them and works as expected, giving me same message like yours.

DROP OPERATOR public.= (text,public.i32);
DROP OPERATOR public.= (public.i32,text);




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Server goes to Recovery Mode when run a SQL

[Tom Lane]

PegoraroF10  writes:
> And I have created these two operators a long time ago. 

> CREATE OPERATOR public.= (
> FUNCTION = public.fntextonumero,
> LEFTARG = text,
> RIGHTARG = public.i32
> );
> CREATE OPERATOR public.= (
> FUNCTION = public.fntextonumero,
> LEFTARG = public.i32,
> RIGHTARG = text
> );

... so, what do those functions look like?  And, since there's evidently a
custom type or domain involved, what is i32 exactly?

In short: you have completely failed to provide a self-contained test
case.

regards, tom lane

Re: Server goes to Recovery Mode when run a SQL

[PegoraroF10]

And the operator function, just to you know, is only a conversion to text

CREATE FUNCTION public.fntextonumero(ftext text, finteger public.i32)
RETURNS boolean
LANGUAGE sql STABLE
AS $$
  SELECT ftext = finteger::text;
$$;

CREATE FUNCTION public.fntextonumero(finteger public.i32, ftext text)
RETURNS boolean
LANGUAGE sql STABLE
AS $$
  SELECT ftext = finteger::text;
$$;




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Server goes to Recovery Mode when run a SQL

[Alvaro Herrera]

On 2020-Aug-14, PegoraroF10 wrote:

> CREATE FUNCTION public.fntextonumero(ftext text, finteger public.i32)

How is public.i32 defined?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Server goes to Recovery Mode when run a SQL

[PegoraroF10]

Well, I didn´t know where was the problem exactly, then ...
The entire script to see that problem is 

create domain public.i32 as integer;

CREATE FUNCTION public.fntextonumero(ftext text, finteger public.i32)
RETURNS boolean
LANGUAGE sql STABLE
AS $$
  SELECT ftext = finteger::text;
$$;
CREATE FUNCTION public.fntextonumero(finteger public.i32, ftext text)
RETURNS boolean
LANGUAGE sql STABLE
AS $$
  SELECT ftext = finteger::text;
$$;

CREATE OPERATOR public.= (
FUNCTION = public.fntextonumero,
LEFTARG = text,
RIGHTARG = public.i32
);
CREATE OPERATOR public.= (
FUNCTION = public.fntextonumero,
LEFTARG = public.i32,
RIGHTARG = text
);

Then that script I´ve sent you this morning.
This way I think you can get the problem. Is this a Postgres problem ? Well,
I created these operators because Firebird accepts comparisons between
numeric and text values, and as we converted from that ... I found a way to
continue comparing them. The problem ocurred now because the IN operator. 



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Server goes to Recovery Mode when run a SQL

[Tom Lane]

PegoraroF10  writes:
> CREATE FUNCTION public.fntextonumero(finteger public.i32, ftext text)
> RETURNS boolean
> LANGUAGE sql STABLE
> AS $$
>   SELECT ftext = finteger::text;
> $$;

Huh.  The crash goes away if you change that to

   SELECT finteger::text = ftext;

It looks like ExecInitSubPlan is just assuming that the outer side
of the hashable comparison condition is on the left, and this
function is confusing it by swapping that around to the right.
Kinda surprising that we never identified that problem before ---
this code's been like this for years.

regards, tom lane

bug in psql?

[Aleksey M Boltenkov]

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 │
...

Aleksey M Boltenkov.

Re: bug in psql?

[Pavel Stehule]

so 15. 8. 2020 v 7:35 odesílatel Aleksey M Boltenkov 
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.
>
>

Re: bug in psql?

[Tom Lane]

"Aleksey M Boltenkov"  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

RE: bug in psql?

[Aleksey M Boltenkov]

eksey M Boltenkov.

 15.08.2020, 08:39, Pavel Stehule 

 so 15. 8. 2020 v 7:35 odesílatel Aleksey M Boltenkov 
 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.


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

Aleksey M Boltenkov.

RE: bug in psql?

[Aleksey M Boltenkov]

 15.08.2020, 08:56, Tom Lane "Aleksey M Boltenkov" 

 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*
\gexec



Aleksey M Boltenkov.

RE: bug in psql?

[Aleksey M Boltenkov]

 15.08.2020, 09:46, Aleksey M Boltenkov 

   15.08.2020, 08:56, Tom Lane "Aleksey M Boltenkov" 

   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*
 \gexec



 Aleksey M Boltenkov.

Sorry, \gexec does not work, copy and paste.
Aleksey M Boltenkov.

RE: bug in psql?

[Aleksey M Boltenkov]

 15.08.2020, 09:49, Pavel Stehule 

 so 15. 8. 2020 v 8:35 odesílatel Aleksey M Boltenkov 
 napsal:

   eksey M Boltenkov.

 15.08.2020, 08:39, Pavel Stehule 

 so 15. 8. 2020 v 7:35 odesílatel Aleksey M Boltenkov 
 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.


   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;



   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.

Re: bug in psql?

[Pavel Stehule]

so 15. 8. 2020 v 8:35 odesílatel Aleksey M Boltenkov 
napsal:

> eksey M Boltenkov.
>
> 15.08.2020, 08:39, Pavel Stehule 
>
>
> so 15. 8. 2020 v 7:35 odesílatel Aleksey M Boltenkov 
> 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.
>>
>>
>>
> 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;



>
> Aleksey M Boltenkov.
>
>