Re: Detecting renamed columns via pgouput in logical replication ?

[Andreas Andreakis]

Thx for replying David.

According to
https://www.postgresql.org/docs/10/protocol-logicalrep-message-formats.html it
looks like certain schema information is embedded via pgoutput such as
column names and types. However, if a new column name appears and a
previous not, then it is currently not possible to differentiate if a
column was renamed VS if a new column was added and an old dropped.

There are additional schema changes that are currently not covered, such as
changes in default values.

Are there plans to add comprehensive schema change detection abilities via
logical replication ? either by embedding more information into pgoutput or
perhaps by embedding the schema DDLs ?

cheers





On Sun, Apr 26, 2020 at 11:07 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thursday, April 16, 2020, Andreas Andreakis <
> andreas.andrea...@gmail.com> wrote:
>>
>>
>> Does this allow to detect column renames ?
>>
>
> “The database schema and DDL commands are not replicated“
>
>
> https://www.postgresql.org/docs/current/logical-replication-restrictions.html
>
> David J.
>

Re: Detecting renamed columns via pgouput in logical replication ?

[David G. Johnston]

Please don’t top-post.

On Monday, April 27, 2020, Andreas Andreakis 
wrote:

> Thx for replying David.
>
> According to https://www.postgresql.org/docs/10/protocol-logicalrep-
> message-formats.html it looks like certain schema information is embedded
> via pgoutput such as column names and types. However, if a new column name
> appears and a previous not, then it is currently not possible to
> differentiate if a column was renamed VS if a new column was added and an
> old dropped.
>

Correct.  Maybe you want event triggers?


>
> There are additional schema changes that are currently not covered, such
> as changes in default values.
>

Correct


>
> Are there plans to add comprehensive schema change detection abilities via
> logical replication ? either by embedding more information into pgoutput or
> perhaps by embedding the schema DDLs ?
>

You could search the mailing list archives for recent discussions but given
the general lack fo response to your email I’d say probably not.  Decent
odds if someone was they would have replied to your email.

David J.

Re: Detecting renamed columns via pgouput in logical replication ?

[David G. Johnston]

On Monday, April 27, 2020, David G. Johnston 
wrote:.
>
>
> Are there plans to add comprehensive schema change detection abilities via
>> logical replication ? either by embedding more information into pgoutput or
>> perhaps by embedding the schema DDLs ?
>>
>
> You could search the mailing list archives for recent discussions but
> given the general lack fo response to your email I’d say probably not.
> Decent odds if someone was they would have replied to your email
>

I forgot, there is also a commitfest website for the project you could
peruse.

David J.

HOST variable in psql

[Paul Förster]

Hi,

the use of :HOST in psql containing the socket directory for local connections 
is pretty useless for us, so we set it to the hostname in a global psqlrc file:

\set HOST `hostname -s`

This works great when invoking psql on the command line. But if I perform a \c 
to connect to a different database in the database cluster, then :HOST gets 
reset to the socket directory. On the one hand, this makes sense, on the other, 
the global psqlrc file does not seem to be executed again, thus leaving :HOST 
in an unuable state for us:

postgres=# \echo :HOST
mypghost
postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \echo :HOST
/tmp

The documentations (https://www.postgresql.org/docs/12/app-psql.html) says:

"HOST
The database server host you are currently connected to. This is set every time 
you connect to a database (including program start-up), but can be changed or 
unset."

"Files
psqlrc and ~/.psqlrc
Unless it is passed an -X option, psql attempts to read and execute commands 
from the system-wide startup file (psqlrc) and then the user's personal startup 
file (~/.psqlrc), after connecting to the database but before accepting normal 
commands."

Makes sense so far, except the last part "after connecting to the database but 
before accepting normal commands." This applies only to the first psql startup 
but not a subsequent \c. If I do a \c, then that is connecting to a database 
and that means, the psqlrc *should* be executed again, which it isn't.

Bug or feature? If feature, is there any remedy to this behavior?

Thanks in advance,
Paul

How to debug "background worker "parallel worker" (PID 32152) was terminated by signal 11: Segmentation fault"

[Radu Radutiu]

Hi,

Can you guide me how to debug postgresql crash?
I have a development VM (kvm) running Centos 8.1(up to date with all
patches, kernel 4.18.0-147.8.1.el8_1.x86_64,
postgresql12-server-12.2-2PGDG.rhel8.x86_64 ) with a very large partitioned
table (few hundred GBs) . Creating an index on partition tables crashed the
postgresql instance. I have in the log:

2020-04-26 17:35:50.065 EEST [8385] LOG:  background worker "parallel
worker" (PID 32152) was terminated by signal 11: Segmentation fault
2020-04-26 17:35:50.065 EEST [8385] DETAIL:  Failed process was running:
CREATE INDEX IDX_instrid
ON ach.achiteminfo USING btree
(instrid);
2020-04-26 17:35:50.065 EEST [8385] LOG:  terminating any other active
server processes
2020-04-26 17:35:50.065 EEST [32134] WARNING:  terminating connection
because of crash of another server process
2020-04-26 17:35:50.065 EEST [32134] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-04-26 17:35:50.065 EEST [32134] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2020-04-26 17:35:50.065 EEST [32146] WARNING:  terminating connection
because of crash of another server process
2020-04-26 17:35:50.065 EEST [32146] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-04-26 17:35:50.065 EEST [32146] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2020-04-26 17:35:50.065 EEST [32126] WARNING:  terminating connection
because of crash of another server process
2020-04-26 17:35:50.065 EEST [32126] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-04-26 17:35:50.065 EEST [32126] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2020-04-26 17:35:50.066 EEST [32130] WARNING:  terminating connection
because of crash of another server process
2020-04-26 17:35:50.066 EEST [32130] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-04-26 17:35:50.066 EEST [32130] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2020-04-26 17:35:50.066 EEST [32129] WARNING:  terminating connection
because of crash of another server process
2020-04-26 17:35:50.066 EEST [32129] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-04-26 17:35:50.066 EEST [32129] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2020-04-26 17:35:50.067 EEST [32132] WARNING:  terminating connection
because of crash of another server process
2020-04-26 17:35:50.067 EEST [32132] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-04-26 17:35:50.067 EEST [32132] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2020-04-26 17:35:50.067 EEST [32142] WARNING:  terminating connection
because of crash of another server process
2020-04-26 17:35:50.067 EEST [32142] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-04-26 17:35:50.067 EEST [32142] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2020-04-26 17:35:50.068 EEST [32143] WARNING:  terminating connection
because of crash of another server process
2020-04-26 17:35:50.068 EEST [32143] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-04-26 17:35:50.068 EEST [32143] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2020-04-26 17:35:50.068 EEST [32140] WARNING:  terminating connection
because of crash of another server process
2020-04-26 17:35:50.068 EEST [32140] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-04-26 17:35:50.068 EEST [32140] HINT:  In a moment you should be able
to reconnect to the database and repeat your command.
2020-04-26

Re: PostgreSQL Server version compatibility check with Window 2016

[Magnus Hagander]

Hello!

This address is for reporting issues with the PostgreSQL website, it is not
a help desk. For general support questions, please see
https://www.postgresql.org/support/ - in particular, consider the
pgsql-general public mailinglist, as you seem to have already done, and
received a reply there (though it was two years ago).

You should also note that 9.6.7 or 9.6.9 is not supported on any platform,
you need to run 9.6.17 if you run a 9.6 version. This is why there is no
download link to an unsupported 9.6.9 version.

And as stated on the Windows download page at
https://www.postgresql.org/download/windows/, you need at least version 10
to have one that's tested on Windows 2016. But as the page also says, "They
can generally be expected to run on other comparable versions".


//Magnus


On Mon, Apr 27, 2020 at 11:31 AM  wrote:

>
>
> Hello Postgress Help Desk,
>
>
>
> Hope you are doing well.
>
>
>
> We are running our Qlik Sense application with postgress 9.6.7 version  &
> window 2016 server.
>
>
>
> We have often getting environment related issues and Qlik sense vendor
> confirmed that postgress was not supported with windows 2016.
>
>
>
> We understand from the below link, you didn’t test completely postgress
> 9.6.9 with windows 2016.
>
>
>
>
> https://www.postgresql.org/message-id/f7b9eff1d97e4eabb7cee9c497c5b3a8%40CTSINCHNSXCM011.cts.com
> 
>
>
>
> Kindly check above link and let us know your recommendations .
>
>
>
> Note: Qlik Sense server(Sep 2019 version) will support postgress 9.6.x
> version only so we are looking PostgreSQL  9.6.9 compatibility check with
> windows 2016
>
>
>
> We have noticed from postgress official  download page , we couldn’t able
> to see PostgreSQL 9.6.9 version download link.
>
>
>
>
>
> Reference link:
> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
> 
>
>
>
>
>
>
>
>
> Can you please let us know recommendation on this?
>
>
>
>
>
> Regards,
>
> Balaji
>
> Agilent AMS – BI Qlik Sense
>
> Phone: +91-9790034281
>
> Qlik PDL: *pdlctsbiqlikse...@cognizant.com*
> 
>
> Cognizant Technology Solutions.
>
> *Escalation Contact: **megavannan.kanniap...@cognizant.com*
> 
>
> *Escalation Matrix* - *https://spark.it.agilent.com/docs/DOC-58251*
> 
>
> *Please cc: Support PDL in your reply for optimal support coverage*
>
>
>
> *From:* M, Balaji (Cognizant)
> *Sent:* Monday, April 13, 2020 9:27 PM
> *To:* pgsql-general@lists.postgresql.org
> *Cc:* C, Praveen kumar (Cognizant) 
> *Subject:* PostgreSQL Server version compatibility check with Window 2016
>
>
>
> Hello Postgress Help Desk,
>
>
>
> Hope you are doing well.
>
>
>
> We are running our Qlik Sense application with postgress 9.6.7 version  &
> window 2016 server.
>
>
>
> We have often getting environment related issues and Qlik sense vendor
> confirmed that postgress was not supported with windows 2016.
>
>
>
> We understand from the below link, you didn’t test completely postgress
> 9.6.9 with windows 2016.
>
>
>
>
> https://www.postgresql.org/message-id/f7b9eff1d97e4eabb7cee9c497c5b3a8%40CTSINCHNSXCM011.cts.com
> 
>
>
>
> Kindly check above link and let us know your recommendations .
>
>
>
> Note: Qlik Sense server(Sep 2019 version) will support postgress 9.6.x
> version only so we are looking PostgreSQL  9.6.9 compatibility check with
> windows 2016
>
>
>
> We have noticed from postgress official  download page , we couldn’t able
> to see PostgreSQL 9.6.9 version download link.
>
>
>
>
>
> Reference link:
> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
> 

Re: HOST variable in psql

[Tom Lane]

=?utf-8?Q?Paul_F=C3=B6rster?=  writes:
> the use of :HOST in psql containing the socket directory for local 
> connections is pretty useless for us, so we set it to the hostname in a 
> global psqlrc file:
> \set HOST `hostname -s`
> This works great when invoking psql on the command line. But if I perform a 
> \c to connect to a different database in the database cluster, then :HOST 
> gets reset to the socket directory. On the one hand, this makes sense, on the 
> other, the global psqlrc file does not seem to be executed again, thus 
> leaving :HOST in an unuable state for us:

AFAICS, psql is behaving as documented.  Why are you trying to override
HOST like that, instead of just using some other variable?

regards, tom lane

PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Matthias Apitz]

Hello,

I encounter from time to time that one of our ESQL/C clients hangs with
the details described below for ever, while accessing on startup a table 
'sisisinst' which has only some 50 rows, all of CHAR columns. Other ESQL/C 
clients
start up fine (they all check this table 'sisisinst' on startup to
verify the correct version of our application) Also SQL works
fine.

What can I do to get to the source of the problem? Thanks

matthias


ESQL/C statement:

   EXEC SQL PREPARE sid_sisisinst  FROM :select_anw;

host variable :select_anw contains:

(gdb) p select_anw
$2 = "SELECT ctid, * from sisisinst WHERE version  = :v1", '\000'

process PID 23957 is hanging in poll() on network socket to PostgreSQL server:
(Version 11.4 on Linux)

(gdb) bt
#0  0x7ff9020c2664 in poll () from /lib64/libc.so.6
#1  0x7ff90144c22f in pqSocketPoll () from 
/usr/local/sisis-pap/pgsql/lib/libpq.so.5
#2  0x7ff90144c0ff in pqSocketCheck () from 
/usr/local/sisis-pap/pgsql/lib/libpq.so.5
#3  0x7ff90144bf83 in pqWaitTimed () from 
/usr/local/sisis-pap/pgsql/lib/libpq.so.5
#4  0x7ff90144bf55 in pqWait () from 
/usr/local/sisis-pap/pgsql/lib/libpq.so.5
#5  0x7ff901447d3a in PQgetResult () from 
/usr/local/sisis-pap/pgsql/lib/libpq.so.5
#6  0x7ff901448478 in PQexecFinish () from 
/usr/local/sisis-pap/pgsql/lib/libpq.so.5
#7  0x7ff90144819a in PQprepare () from 
/usr/local/sisis-pap/pgsql/lib/libpq.so.5
#8  0x7ff901cc788d in prepare_common () from 
/usr/local/sisis-pap/pgsql/lib/libecpg.so.6
#9  0x7ff901cc7a26 in ECPGprepare () from 
/usr/local/sisis-pap/pgsql/lib/libecpg.so.6
#10 0x7ff9044a86ee in select_record (scroll=1, lock=0, key=2, 
sel_anw=0x7ffed86186c0 "SELECT ctid, * from %s", where_anw=0x7ffed8617330 
"%s = :v1", 
p_daten=0x7ff904dd7700 , i_between=0, 
p_oben=0x7ffed86171d0, order_by=0x0, 
auf_ab=0x0, group_by=0x0, having=0x0, into_temp=0x0, count=0x7ffed8617328) 
at sisisinst.pgc:925
#11 0x7ff9044a7a8d in sisisinst (zugriff=1, scroll=1, lock=0, key=2, 
sto=-2, 
p_daten=0x7ffed8618c30, sel_anw=0x7ffed86186c0 "SELECT ctid, * from %s", 
where_anw=0x7ffed8617330 "%s = :v1", p_btw_daten=0x0, order_by=0x0, 
auf_ab=0x0, group_by=0x0, 
having=0x0, into_temp=0x0, count=0x7ffed8617328) at sisisinst.pgc:450
#12 0x7ff9043718a5 in DB_rdir (tabmodul=0x7ff9044a7639 , key=2, 
scroll=1, lock=0, 
p_daten=0x7ffed8618c30) at dbcall.pgc:1665
#13 0x7ff90436f6c9 in DB_ChkVer () at dbcall.pgc:307
#14 0x7ff9043708d3 in DB_opdbP (mode=1) at dbcall.pgc:840
#15 0x7ff90436fff8 in DB_opdb () at dbcall.pgc:543
#16 0x00405efa in InitIndex (setid=FSTAB_Personen) at ./INDEX.c:1000
#17 0x00405241 in main (argc=2, argv=0x7ffed8619fa8) at ./INDEX.c:412

running PostgreSQL processes:

# ps ax | grep postgres:
  329 pts/13   S+ 0:00 grep --color=auto postgres:
 2354 ?Ss 0:17 postgres: checkpointer
 2355 ?Ss 0:09 postgres: background writer
 2356 ?Ss 0:10 postgres: walwriter
 2357 ?Ss 0:04 postgres: autovacuum launcher
 2358 ?Ss 0:00 postgres: archiver   last was 
0001000F0062
 2359 ?Ss 0:31 postgres: stats collector
 2360 ?Ss 0:00 postgres: logical replication launcher
14044 ?Ss 0:00 postgres: sisis sisis ::1(53426) idle
14100 ?Ss 0:00 postgres: sisis sisis ::1(53432) idle
16852 ?Ss 0:00 postgres: sisis sisis ::1(53486) idle
17575 ?Ss 0:00 postgres: sisis sisis ::1(48736) idle
17577 ?Ss 0:00 postgres: sisis sisis ::1(48742) idle
17583 ?Ss 0:00 postgres: sisis sisis 10.23.33.19(49630) idle
17584 ?Ss 0:00 postgres: sisis sisis 10.23.33.19(49632) idle
17585 ?Ss 0:00 postgres: sisis sisis 10.23.33.19(49634) idle
17587 ?Ss 0:00 postgres: sisis sisis ::1(48762) idle
17590 ?Ss 0:00 postgres: sisis sisis ::1(48770) idle
17593 ?Ss 0:00 postgres: sisis sisis ::1(48778) idle
17609 ?Ss 0:04 postgres: sisis sisis ::1(48794) idle
17612 ?Ss 0:00 postgres: sisis sisis ::1(48802) idle
17615 ?Ss 0:04 postgres: sisis sisis ::1(48810) idle
17618 ?Ss 0:00 postgres: sisis sisis ::1(48818) idle
17621 ?Ss 0:08 postgres: sisis sisis ::1(48826) idle
17866 ?Ss 0:00 postgres: sisis sisis 10.23.33.19(49758) idle
17868 ?Ss 0:00 postgres: sisis sisis ::1(48886) idle
17907 ?Ss 0:00 postgres: sisis sisis ::1(48900) idle
17948 ?Ss 0:00 postgres: sisis sisis ::1(48914) idle
21457 ?Ss 0:00 postgres: sisis sisis ::1(53664) idle
23958 ?Ss 0:00 postgres: sisis sisis ::1(53742) idle
 ^


lsof -P -p 23957 | grep 5432
INDEX   23957 sisis2u  IPv6  117475261  0t0   TCP 
localhost:53742->localhost:5432 (ESTABLISHED)

-- 
Matthias Apitz, ✉ g...@unixarea.de

Re: How to debug "background worker "parallel worker" (PID 32152) was terminated by signal 11: Segmentation fault"

[Tom Lane]

Radu Radutiu  writes:
> Can you guide me how to debug postgresql crash?

A stack trace would be pretty useful.

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

regards, tom lane

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Tom Lane]

Matthias Apitz  writes:
> I encounter from time to time that one of our ESQL/C clients hangs with
> the details described below for ever, while accessing on startup a table 
> 'sisisinst' which has only some 50 rows, all of CHAR columns. Other ESQL/C 
> clients
> start up fine (they all check this table 'sisisinst' on startup to
> verify the correct version of our application) Also SQL works
> fine.

> What can I do to get to the source of the problem? Thanks

Can you get a stack trace from the connected backend?  Or even just look
at what it's doing according to pg_stat_activity?  (If it's a reasonably
modern server, the wait-condition fields would be interesting.)

regards, tom lane

Re: HOST variable in psql

[Paul Förster]

Hi Tom,

> On 27. Apr, 2020, at 14:20, Tom Lane  wrote:
> AFAICS, psql is behaving as documented.  Why are you trying to override
> HOST like that, instead of just using some other variable?

I could but

a) HOST is HOST and should not point to a socket directory. A socket directory 
is not a HOST. This makes no sense, especially since I see no use for using a 
socket directory in any psql script. There are tons of use cases for the 
hostname, though.

b) it would require changing some scripts.

c) the docs are IMHO not 100% clear on this. I think the psqlrc should be 
executed on EVERY new connect, hence including \c, and this is how I interpret 
the docs too. But it doesn't...

also (slightly related) d) PostgreSQL unfortunately does not provide any 
functionality to determine the server hostname it runs on. There is an 
extension but I still don't see why PostgreSQL does not include this natively.

Cheers,
Paul

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Matthias Apitz]

El día Montag, April 27, 2020 a las 08:40:04 -0400, Tom Lane escribió:

> Matthias Apitz  writes:
> > I encounter from time to time that one of our ESQL/C clients hangs with
> > the details described below for ever, while accessing on startup a table 
> > 'sisisinst' which has only some 50 rows, all of CHAR columns. Other ESQL/C 
> > clients
> > start up fine (they all check this table 'sisisinst' on startup to
> > verify the correct version of our application) Also SQL works
> > fine.
> 
> > What can I do to get to the source of the problem? Thanks
> 
> Can you get a stack trace from the connected backend?  Or even just look
> at what it's doing according to pg_stat_activity?  (If it's a reasonably
> modern server, the wait-condition fields would be interesting.)

srap32dxr1:/opt/lib/sisis/catserver/bin # lsof -P | grep 53742
INDEX 23957  sisis2u IPv6  117475261
   0t0TCP localhost:53742->localhost:5432 (ESTABLISHED)
postmaste 23958   postgres9u IPv6  117478789
   0t0TCP localhost:5432->localhost:53742 (ESTABLISHED)

'INDEX' is the hanging proc; PID 23958 its server; gdb shows:

# gdb /usr/local/sisis-pap/pgsql/bin/postgres 23958
...
(gdb) bt
#0  0x7fd567776000 in epoll_pwait () from /lib64/libc.so.6
#1  0x0084476c in WaitEventSetWaitBlock ()
#2  0x00844647 in WaitEventSetWait ()
#3  0x006f89d2 in secure_read ()
#4  0x00707425 in pq_recvbuf ()
#5  0x00707709 in pq_discardbytes ()
#6  0x00707aba in pq_getmessage ()
#7  0x0086b478 in SocketBackend ()
#8  0x0086b4c4 in ReadCommand ()
#9  0x0086fda9 in PostgresMain ()
#10 0x007dde15 in BackendRun ()
#11 0x007dd595 in BackendStartup ()
#12 0x007d9c9b in ServerLoop ()
#13 0x007d9556 in PostmasterMain ()
#14 0x0070e2a5 in main ()

sisis=# select * from pg_stat_activity where client_port = 53742;

 datid | datname |  pid  | usesysid | usename | application_name | client_addr 
| client_hostname | client_port |  backend_start  | xact_start 
| query_start |  state_change   | wait_event_type | wait_event 
| state | backend_xid | backend_xmin | query |  backend_type  

 93831 | sisis   | 23958 |16384 | sisis   |  | ::1 
| |   53742 | 27.04.2020 12:37:46.941441 CEST |
| | 27.04.2020 12:37:46.943073 CEST | Client  | ClientRead 
| idle  | |  |   | client backend
(1 Zeile)


HIH && Kinde Regards

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Re: HOST variable in psql

[Tom Lane]

=?utf-8?Q?Paul_F=C3=B6rster?=  writes:
>> On 27. Apr, 2020, at 14:20, Tom Lane  wrote:
>> AFAICS, psql is behaving as documented.  Why are you trying to override
>> HOST like that, instead of just using some other variable?

> I could but

> a) HOST is HOST and should not point to a socket directory. A socket 
> directory is not a HOST. This makes no sense, especially since I see no use 
> for using a socket directory in any psql script. There are tons of use cases 
> for the hostname, though.

[ shrug... ]  When connecting via a Unix socket, psql does not have a
relevant "hostname" to report, so it reports what it's got.  You are
ascribing a meaning to HOST that it does not have, never has had, and
never can have in this case.

> c) the docs are IMHO not 100% clear on this. I think the psqlrc should be 
> executed on EVERY new connect, hence including \c, and this is how I 
> interpret the docs too. But it doesn't...

There has been some talk of providing a way to execute stuff on reconnect,
if memory serves, but nothing's been done about it.

regards, tom lane

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Matthias Apitz]

As the client seems to be waiting on the server and the server on the
client... could it have been caused by this in /etc/hosts (what our IT
department configured):

/etc/hosts:

127.0.0.1   localhost

# special IPv6 addresses
::1 localhost ipv6-localhost ipv6-loopback


i.e. the client has sent its request on IPv4 and the server answered on
IPv6?

I will remove the word 'localhost' from the IPv6 addr and restart
everything, or do you want to see any additional inforation?

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Re: HOST variable in psql

[Paul Förster]

Hi Tom,

> On 27. Apr, 2020, at 15:13, Tom Lane  wrote:
> [ shrug... ]  When connecting via a Unix socket, psql does not have a
> relevant "hostname" to report, so it reports what it's got.  You are
> ascribing a meaning to HOST that it does not have, never has had, and
> never can have in this case.

I'm not much of a C programmer, but there's AFAIK a C function to get the 
hostname. So it should IMHO be easy to put something into the psql init (and 
\c) code that calls the function and set the HOST variable accordingly. IMHO...

> There has been some talk of providing a way to execute stuff on reconnect,
> if memory serves, but nothing's been done about it.

thought so. So I'll have to work around that.

Thanks,
Paul

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Tom Lane]

Matthias Apitz  writes:
> El día Montag, April 27, 2020 a las 08:40:04 -0400, Tom Lane escribió:
>> Can you get a stack trace from the connected backend?

> (gdb) bt
> #0  0x7fd567776000 in epoll_pwait () from /lib64/libc.so.6
> #1  0x0084476c in WaitEventSetWaitBlock ()
> #2  0x00844647 in WaitEventSetWait ()
> #3  0x006f89d2 in secure_read ()
> #4  0x00707425 in pq_recvbuf ()
> #5  0x00707709 in pq_discardbytes ()
> #6  0x00707aba in pq_getmessage ()
> #7  0x0086b478 in SocketBackend ()
> #8  0x0086b4c4 in ReadCommand ()
> #9  0x0086fda9 in PostgresMain ()

Oh, that is *very* interesting, because there is only one caller of
pq_discardbytes:

/*
 * Allocate space for message.  If we run out of room (ridiculously
 * large message), we will elog(ERROR), but we want to discard the
 * message body so as not to lose communication sync.
 */
PG_TRY();
{
enlargeStringInfo(s, len);
}
PG_CATCH();
{
if (pq_discardbytes(len) == EOF)
ereport(COMMERROR,
(errcode(ERRCODE_PROTOCOL_VIOLATION),
 errmsg("incomplete message from client")));
...

What this code intends to handle is the case where the client has sent a
message that is so long that the backend hasn't enough memory to buffer
it.  What's actually happened, more likely, is that the received message
length is corrupt and just appears to be large, since the client-side
trace shows that libpq has sent what it has to send and is now waiting for
a reply.  If the received length were correct then the pq_discardbytes
call would have completed after eating the message.

So what it looks like is that something is corrupting data on its
way from the client to the server.  Flaky firewall maybe?  If you're
using SSL, maybe an SSL library bug?  I'm reduced to speculation at
this point.  It's hard even to say what to do to gather more info.
If you could reproduce it then I'd suggest watching the connection
with wireshark or the like to see what data is actually going across
the wire ... but IIUC it's pretty random, so that approach seems
unlikely to work.

If you're in a position to run a modified server, you could try
inserting a debug log message:

}
PG_CATCH();
{
+   elog(COMMERROR, "bogus received message length: %d", len);
if (pq_discardbytes(len) == EOF)
ereport(COMMERROR,

(This is in src/backend/libpq/pqcomm.c, around line 1300 as of HEAD.)
While this seems unlikely to teach us a huge amount, perhaps the
value of the incorrect length would be informative.

Are you always seeing this error at the exact same place so far as
the client side is concerned?  It's hard to see why a transport-level
problem would preferentially affect one particular query ...

regards, tom lane

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

[Tom Lane]

Matthias Apitz  writes:
> As the client seems to be waiting on the server and the server on the
> client... could it have been caused by this in /etc/hosts (what our IT
> department configured):

Doubt it.  Your connection evidently got through (at least) the
authentication exchange, so basic data transmission seems to be
working.  I speculate that there's something data-dependent about
the command that's failing.

As an example, it would not be terribly surprising to get a behavior
like this if there were something deciding to do an LF -> CRLF or
similar transformation on the data stream.  The substitution might
accidentally not trigger during authentication, only to break some
actual query a bit later.  But there really shouldn't be anything
making such changes in our data stream.

Still, looking at recent network-level configuration changes wouldn't
be a bad idea.

regards, tom lane

Re: How to debug "background worker "parallel worker" (PID 32152) was terminated by signal 11: Segmentation fault"

[Mahendra Singh Thalor]

On Mon, 27 Apr 2020 at 17:52, Tom Lane  wrote:
>
> Radu Radutiu  writes:
> > Can you guide me how to debug postgresql crash?
>
> A stack trace would be pretty useful.
>
> https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
>
> regards, tom lane
>
>

If you have given "-c" option while starting the server, then as your
background worker(PID: 32152) crashed with segment fault so you should
have core dump for this inside data folder. Check that core.32152 is
exist inside your data folder or not. If exists, then fire "gdb
postgres data/core.32152" and then "bt" to get stack trace.

-- 
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com