Differences in Escaped bytea's when creating a plain pg_dump

[WR]

Hello community,

I've some trouble in restoring a plain text pg_dump.
Postgres version is 13 x64 running on Windows10x64 installed from EDB 
package.


The database has bytea_output = 'escape' option because of some 
processing software needs it for historical reasons.


Dump command is:

pg_dump --file=mydump.sql --format=plain --verbose --encoding=UTF8 
--schema=public --host=localhost --username=myuser --inserts dbname


We have two tables that have a bytea-row.

But when I look at the dumpfile there is a difference between the 
escaped bytea-string. (note: both INSERT's from the same run of pg_dump 
in the dumpfile)



SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

[snip]

INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476, 
2000, 2400, 2400, 
'\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000', 
500, 0, 'sinus(0|0|0;30;5;0;0,5;0)', '2021-08-31 11:53:22.442801', 0, 1);


[snip]

INSERT INTO public.profiles VALUES (1, 1, 's', 152, 
'\\037\\003\\000\\000\\000\\037\\003\\000\\000\\000\\037\\003\\000\\000\\000\\037\\003\\000\\000\\000!\\003\\000\\000\\000!\\003\\000\\000\\000!\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000', 
'2016-08-25 00:00:00+02');


[snip]

When I restore them via pgadmin4 query tool, both INSERTS do work.

But when I read them with my c++ written software from file and send 
them with pqxx-Library as a transaction, the first bytea-string 
generates a fault 21020. (0x00 is not a valid utf8 sequence). I also 
checked the read string in c++ debugger, the single backslashes in the 
one case and the double backslashes in the other case are there.


So my questions are:
Why do we get one bytea-string with double backslashes (which works) and 
another one with single backslashes (which actually not works with 
pqxx-transactions)?


Can I convince pg_dump somehow, to generate double backslashes in all 
bytea-strings?


Why does pgadmin understand both formats. pqxx-transaction does not?


Thank you for this great database-system. I really like it.
Wolfgang



--
May the source be with you

Re: Differences in Escaped bytea's when creating a plain pg_dump

[WR]

Am 23.06.2022 um 17:13 schrieb WR:

Hello community,

I've some trouble in restoring a plain text pg_dump.
Postgres version is 13 x64 running on Windows10x64 installed from EDB 
package.


The database has bytea_output = 'escape' option because of some 
processing software needs it for historical reasons.


Dump command is:

pg_dump --file=mydump.sql --format=plain --verbose --encoding=UTF8 
--schema=public --host=localhost --username=myuser --inserts dbname


We have two tables that have a bytea-row.

But when I look at the dumpfile there is a difference between the 
escaped bytea-string. (note: both INSERT's from the same run of pg_dump 
in the dumpfile)



SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

[snip]

INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476, 
2000, 2400, 2400, 
'\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000', 
500, 0, 'sinus(0|0|0;30;5;0;0,5;0)', '2021-08-31 11:53:22.442801', 0, 1);


[snip]

INSERT INTO public.profiles VALUES (1, 1, 's', 152, 
'\\037\\003\\000\\000\\000\\037\\003\\000\\000\\000\\037\\003\\000\\000\\000\\037\\003\\000\\000\\000!\\003\\000\\000\\000!\\003\\000\\000\\000!\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000', 
'2016-08-25 00:00:00+02');


[snip]

When I restore them via pgadmin4 query tool, both INSERTS do work.

But when I read them with my c++ written software from file and send 
them with pqxx-Library as a transaction, the first bytea-string 
generates a fault 21020. (0x00 is not a valid utf8 sequence). I also 
checked the read string in c++ debugger, the single backslashes in the 
one case and the double backslashes in the other case are there.


So my questions are:
Why do we get one bytea-string with double backslashes (which works) and 
another one with single backslashes (which actually not works with 
pqxx-transactions)?


Can I convince pg_dump somehow, to generate double backslashes in all 
bytea-strings?


Why does pgadmin understand both formats. pqxx-transaction does not?


Thank you for this great database-system. I really like it.
Wolfgang





Hello again,

I've found one mistake in the data of the second table 
(public.profiles). They seem to be really "double escaped" somehow. So 
they are not valid anymore.


Now I know pg_dump doesn't make any difference between the two tables. 
The only valid data is from table (public.oned_figures) with one 
backslash. That was my fault, sorry.


But one question is left.

When I read this valid data into a c++ std::string (and I checked that 
the single backslashes are still there). Why can't I put this 
SQL-command to a pqxx-transaction and execute it. It looks like the 
pqxx-transaction unescapes the bytea-string and then it finds the 0x00 
bytes, which are not allowed in text-strings but should be in bytea-strings.




--
May the source be with you

Re: Differences in Escaped bytea's when creating a plain pg_dump

[WR]

I'm back at my problem today:




Example:


postgres=# set standard_conforming_strings to off;
SET


postgres=# set escape_string_warning to off;
SET


postgres=# select '\000'::bytea;
ERROR:  invalid byte sequence for encoding "UTF8": 0x00


postgres=# select '\\000'::bytea;
 bytea 
---

 \x00
(1 row)


I made some test with pgadmin. Pgadmin (5.2) also reports this error 
now. And it doesn't matter if standard_conforming_strings is on or off.



SET standard_conforming_strings = off;

INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476, 
2000, 2400, 2400, '\000', 500, 0, 'sinus(0|0|0;30;5;0;0,5;0)', 
'2021-08-31 11:53:22.442801', 0, 1);


And

SET standard_conforming_strings = on;

INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476, 
2000, 2400, 2400, '\000', 500, 0, 'sinus(0|0|0;30;5;0;0,5;0)', 
'2021-08-31 11:53:22.442801', 0, 1);



Both do report:

WARNUNG:  nicht standardkonforme Verwendung von Escape in 
Zeichenkettenkonstante

LINE 8: ...(1, 'Figure_Wolle1', 476, -476, 2000, 2400, 2400, '\000\000&...
 ^
HINT:  Verwenden Sie die Syntax für Escape-Zeichenketten, z.B. E'\r\n'.

ERROR: FEHLER:  ungültige Byte-Sequenz für Kodierung »UTF8«: 0x00


The warning can be avoided by adding the E before the string constant.

The only solution to avoid the error is, to double-backslash.

INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476, 
2000, 2400, 2400, E'\\000', 500, 0, 'sinus(0|0|0;30;5;0;0,5;0)', 
'2021-08-31 11:53:22.442801', 0, 1);


I also  dumped the table again after INSERT, and the data was correct 
(but again without E and with single backslash)


What I cant understand: why does pg_dump produce the string without the 
E and without double-backslash, when it is needed? Now I have to write a 
correction routine in c++, what fixes the dumps, before using them.



--
May the source be with you

Re: Differences in Escaped bytea's when creating a plain pg_dump

[WR]

Another strange thing is:

in my first mail I wrote: running the dump in in pgadmin works, in the 
last mail I wrote pgadmin also produces the error. I played a little bit 
how this could be happend.


Everytime ich used the following sql text in the querytool:

SET standard_conforming_strings = on;

INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476, 
2000, 2400, 2400, '\000', 500, 0, 'sinus(0|0|0;30;5;0;0,5;0)', 
'2021-08-31 11:53:22.442801', 0, 1);


After each run I deleted the line with a View/Edit Data Panel of the Table.

First run worked.
Second run worked.
Then I changed to SET standard_conforming_strings = off;
Third run worked.
Fourth run throw the error
Then I changed back to  SET standard_conforming_strings = on;
Fifth run throw the error too.
And only adding E and second backslash helped.

I could reproduce this behavior everytime I close the query tool and 
opened it again.


But this looks more like a pgadmin-bug.



--
May the source be with you

Re: Access a newer Version of PGDB (v13) with an older libpq (v10 x86)

[WR]

Am 03.05.2021 um 10:41 schrieb Laurenz Albe:

On Sat, 2021-05-01 at 12:59 +0200, Wolfgang Rißler wrote:

Am 30.04.2021 um 16:16 schrieb Tom Lane:

I would recommend trying to use a reasonably late-vintage libpq; we do
fix bugs in it on a regular basis.
The common stumbling block for cross-version situations is that the
client makes assumptions about system catalog contents that are not
valid in some other server release.  libpq proper doesn't really touch
the catalogs, so it's mostly impervious to that problem; but you'll need
to test your applications.


Of course we'll do. One thing is, that we load and write bytea's. And as
I read, there have been some changes. All other Operations are less
problematic.


Version 9.0 changed the default "bytea" output format to hexadecimal.
If you want the old text output format, set "bytea_output" to "escape".

If you output "bytea"s in binary form, nothing has changed.

This is a useful hint. I did this already in my PG10 <-> libpq10(x86) 
testinstallation.


Thank you.



--
May the source be with you

Obsolete or dead serverconnections after reboot

[WR]

Hello community,

actually I have a problem wit PG13 on Windows 10 (both x64), Postgres 
version is 13.0.2.21090 from EDB installer.


I have to develop a logical replication cluster, where sometimes there 
happens a shutdown of one host. Logical replication works nice, thank 
you for that.


My issue is, when I do a normal Windows shutdown, when there are 
connections open to this database server and later this host comes up 
again in statistics I find the old connections from before shutdown. 
They don't go away and keep staying there over reboots. Only a restart 
of the Windows service throws them away.


The problem is, that they block a place in der connections counter of 
the server and after a while I cant login any more, because the maximum 
user-connection count is exceeded.


Example:
select pid, datid, application_name, usename , client_addr from 
pg_stat_activity;

before reboot (one active psql session, left it open on server shutdown):

  pid  | datid  | application_name | usename  |  client_addr
---++--+--+---
 13548 | 156501 | psql | postgres | 192.168.2.49

after reboot (and newly started psql session):

  pid  | datid  | application_name | usename  |  client_addr
---++--+--+---
 13548 | 156501 | psql | postgres | 192.168.2.49
 13764 | 156501 | psql | postgres | 192.168.2.49

As we can see at pid column, the old connection is still there, but I 
have only on psql session open.


After restarting the postgres service( and psql reconnect):

  pid  | datid | application_name | usename  |  client_addr
---+---+--+--+---
 12132 | 91805 | psql | postgres | 192.168.2.49

Everything is like expected: one psql-session, one row in the statistics

Is there a way to avoid this (without restarting the service after every 
reboot). Is this a bug or a normal behavior?


Thank you,
Wolfgang

--
May the source be with you

Re: Obsolete or dead serverconnections after reboot

[WR]

Hello Vijaykumar Jain,

thank you for fast answer, today I'm not able to access the hardware, 
I'll be back tomorrow and will do the required tests.


Wolfgang

Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:

select pg_stat_reset();

Can you run above function, to check if stats are reset and things are 
fine without a pg restart?
I not able to understand how new connection stats are added, along with 
old stale states, if I assume stats collector process is having issues , 
or the stats folder is corrupt etc.

That would also mean, all table stats would be off or not updated too?
Is that so? Or analyse works fine on tables without a restart?

On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain 
> wrote:




Is there a way to avoid this (without restarting the service
after every
reboot). Is this a bug or a normal behavior?


I have less knowledge of windows.
https://www.enterprisedb.com/blog/postgresql-shutdown




Do you see shutdown/termination messages in the db logs or windows
event logs when the machine is rebooted?

You get the same pid and query, does it also has the same age( time
since it started),
I mean is the stats table updated with new data for stale
connections or they remain static.

Do you see the same issue when the machine is power cycled.

Maybe windows might be preserving the memory state on disk and
reading it back on reboot (like sleep) unless there are instructions
to shutdown the db server on reboot. Idk.

What are the state of the connections in pg_stat_activity abd
process explorer before and after reboot. The sockets exists and
active,  or are residual in pg stats only.

Re: Obsolete or dead serverconnections after reboot

[WR]

Hello Vijaykumar Jain,

at first: select pg_stat_reset(); doesn't help, the pg_stat_activity is 
the same after it.


I added some interesting rows after two reboots (which have been 
complete power cycles)


artea=# select pid ,application_name , client_addr ,client_port 
,backend_start ,query_start,state from pg_stat_activity;
  pid  |  application_name   |  client_addr   | 
client_port | backend_start | query_start  | state

---+-+---+-+---+---+
 6 | |   | | 2021-07-21 
12:38:06.76295+02  |  |
  9320 | |   | | 2021-07-21 
12:38:06.77185+02  |  |
 11292 | psql| 
2003:fc:1f03:f200:71ae:2cc5:2e77:9003 |   59545 | 2021-07-22 
07:52:20.110569+02 | 2021-07-22 07:52:24.727718+02 | idle
  9624 | arteasubartlt15wolleartlt34 | 
2003:fc:1f03:f200:71ae:2cc5:2e77:9003 |   59574 | 2021-07-22 
07:56:16.235684+02 | 2021-07-22 07:56:16.278479+02 | active
 11396 | psql| 192.168.2.49   |   59550 
| 2021-07-22 07:54:03.736197+02 | 2021-07-22 07:54:06.488585+02 | idle
 10448 | psql| 
2003:fc:1f03:f200:71ae:2cc5:2e77:9003 |   59589 | 2021-07-22 
07:58:14.715886+02 | 2021-07-22 07:59:01.652215+02 | active
 15788 | |   | | 2021-07-21 
12:38:06.736352+02 |  |
 11216 | |   | | 2021-07-21 
12:38:06.722957+02 |  |
 14092 | |   | | 2021-07-21 
12:38:06.739031+02 |  |

(9 Zeilen)

(Sorry for bad formatting)

So you can see we have two idle connections, which are those from before 
the reboots (one had a IPv4 name resolution and two did it by IPv6, psql 
commandline was the same). The backend_start is the same before and 
after reboot, so they are the same instances of connections.


I scanned the logfiles and I did not find a shutdown of PostgresServer 
on reboot time.


But when I restart the Windows-service postgres manually, then I get 
those messages:


On stop:
2021-07-22 08:27:33.451 CEST [11216] LOG:  fahre herunter
(I'm shutting down)
2021-07-22 08:27:33.512 CEST [14000] LOG:  Datenbanksystem ist 
heruntergefahren

(DB is now shutdown)
On start:
2021-07-22 08:27:39.565 CEST [11500] LOG:  PostgreSQL 13.2, compiled by 
Visual C++ build 1914, 64-bit startet
2021-07-22 08:27:39.744 CEST [17304] LOG:  Datenbanksystem wurde am 
2021-07-22 08:27:33 CEST heruntergefahren

(DB was shutdown on 2021-07-22 08:27:33)
2021-07-22 08:27:39.787 CEST [11500] LOG:  Datenbanksystem ist bereit, 
um Verbindungen anzunehmen

(DB is ready to accept connections)

(sorry, its in german, tried to translate)

So maybe, the DB-service seems not to stopped propperly on shutdown of 
the host?


Greets, Wolfgang



Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:

select pg_stat_reset();



--
May the source be with you

Re: Obsolete or dead serverconnections after reboot

[WR]

Hello Ninad Shah,

I think, nobody holds the connections, because the state is idle. But 
I'm not shure what means: the connection is idle.


One interesting fact was: when I dont stop the psql commandline on the 
client (and dont start another query, wht ends up in a connection reset, 
while the server is down), psql uses the same connection after reboot 
and the state goes from idle back to active.


So there are two questions: what will happen to idle connctions after a 
while, if the client doesnt exist anymore.
And is this desired behavior, the the postgres-Server-Service does not a 
shutdown on reboot on windows. (seem my mail to Vijaykumar Jain)


Thanks you, Greeting from germany,
Wolfgang

Am 21.07.2021 um 16:10 schrieb Ninad Shah:

Hello,

Would you be able to verify the process trees for those PIDs on Windows? 
You may be able to see who holds the connections?



Regards,
Ninad Shah

On Wed, 21 Jul 2021 at 19:15, WR <mailto:wolle...@freenet.de>> wrote:


Hello Vijaykumar Jain,

thank you for fast answer, today I'm not able to access the hardware,
I'll be back tomorrow and will do the required tests.

Wolfgang

Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:
 > select pg_stat_reset();
 >
 > Can you run above function, to check if stats are reset and
things are
 > fine without a pg restart?
 > I not able to understand how new connection stats are added,
along with
 > old stale states, if I assume stats collector process is having
issues ,
 > or the stats folder is corrupt etc.
 > That would also mean, all table stats would be off or not updated
too?
 > Is that so? Or analyse works fine on tables without a restart?
 >
 > On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain
 > mailto:vijaykumarjain.git...@gmail.com>
 > <mailto:vijaykumarjain.git...@gmail.com
<mailto:vijaykumarjain.git...@gmail.com>>> wrote:
 >
 >
 >
 >         Is there a way to avoid this (without restarting the service
 >         after every
 >         reboot). Is this a bug or a normal behavior?
 >
 >
 >     I have less knowledge of windows.
 > https://www.enterprisedb.com/blog/postgresql-shutdown
<https://www.enterprisedb.com/blog/postgresql-shutdown>
 >     <https://www.enterprisedb.com/blog/postgresql-shutdown
<https://www.enterprisedb.com/blog/postgresql-shutdown>>
 >
 >
 >
 >     Do you see shutdown/termination messages in the db logs or
windows
 >     event logs when the machine is rebooted?
 >
 >     You get the same pid and query, does it also has the same
age( time
 >     since it started),
 >     I mean is the stats table updated with new data for stale
 >     connections or they remain static.
 >
 >     Do you see the same issue when the machine is power cycled.
 >
 >     Maybe windows might be preserving the memory state on disk and
 >     reading it back on reboot (like sleep) unless there are
instructions
 >     to shutdown the db server on reboot. Idk.
 >
 >     What are the state of the connections in pg_stat_activity abd
 >     process explorer before and after reboot. The sockets exists and
 >     active,  or are residual in pg stats only.
 >
 >






--
May the source be with you

Re: Obsolete or dead serverconnections after reboot

[WR]

I also looked for the shutdown mode:

since there is no -m parameter in the commandline for starting the 
windows-postgres-service, the shutdown mode seems to be the default:

fast



Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:

select pg_stat_reset();

Can you run above function, to check if stats are reset and things are 
fine without a pg restart?
I not able to understand how new connection stats are added, along with 
old stale states, if I assume stats collector process is having issues , 
or the stats folder is corrupt etc.

That would also mean, all table stats would be off or not updated too?
Is that so? Or analyse works fine on tables without a restart?

On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain 
> wrote:




Is there a way to avoid this (without restarting the service
after every
reboot). Is this a bug or a normal behavior?


I have less knowledge of windows.
https://www.enterprisedb.com/blog/postgresql-shutdown




Do you see shutdown/termination messages in the db logs or windows
event logs when the machine is rebooted?

You get the same pid and query, does it also has the same age( time
since it started),
I mean is the stats table updated with new data for stale
connections or they remain static.

Do you see the same issue when the machine is power cycled.

Maybe windows might be preserving the memory state on disk and
reading it back on reboot (like sleep) unless there are instructions
to shutdown the db server on reboot. Idk.

What are the state of the connections in pg_stat_activity abd
process explorer before and after reboot. The sockets exists and
active,  or are residual in pg stats only.





--
May the source be with you

Re: Obsolete or dead serverconnections after reboot

[WR]

Hello Vijaykumar Jain,

it really seems that the reason for the problem is the not executed 
shutdown of the service. I also sent a message to EDB. Maybe they will 
answer although I'm not a registered customer.


In some earier logfiles I can find the shutdown messages like yours, but 
not in all of them.


I'll try reinstalling the package. But I have this problem on two 
machines. Maybe I'll try an actual build of the package.


Thanks so far for the service, I'm really greatful that someone helped 
me so fast. Maybe I'm back here, when I got an answer from EDB.


Wolfgang


Am 22.07.2021 um 11:04 schrieb Vijaykumar Jain:
On Thu, 22 Jul 2021 at 12:41, WR <mailto:wolle...@freenet.de>> wrote:


Hello Vijaykumar Jain,

at first: select pg_stat_reset(); doesn't help, the pg_stat_activity is
the same after it.


one thing, i forgot to mention. After a pg_stat_reset(), I would run, 
*vacuum analyze* on the dbs, so that stats are rebuilt.

else queries may have some bad plans due to lack of estimates.
my assumption was, stats were corrupt, but it should have been there in 
the logs

or the stats collector was broken.
autovacuum would do it, but it would take its own time.

I added some interesting rows after two reboots (which have been
complete power cycles)

artea=# select pid ,application_name , client_addr ,client_port
,backend_start ,query_start,state from pg_stat_activity;
    pid  |      application_name       |              client_addr   
    |

client_port |         backend_start         | query_start          |
state

---+-+---+-+---+---+
   6 |                             |       |             |
2021-07-21
12:38:06.76295+02  |              |
    9320 |                             |       |             |
2021-07-21
12:38:06.77185+02  |              |
   11292 | psql                        |
2003:fc:1f03:f200:71ae:2cc5:2e77:9003 |       59545 | 2021-07-22
07:52:20.110569+02 | 2021-07-22 07:52:24.727718+02 | idle
    9624 | arteasubartlt15wolleartlt34 |
2003:fc:1f03:f200:71ae:2cc5:2e77:9003 |       59574 | 2021-07-22
07:56:16.235684+02 | 2021-07-22 07:56:16.278479+02 | active
   11396 | psql                        | 192.168.2.49       | 
  59550

| 2021-07-22 07:54:03.736197+02 | 2021-07-22 07:54:06.488585+02 | idle
   10448 | psql                        |
2003:fc:1f03:f200:71ae:2cc5:2e77:9003 |       59589 | 2021-07-22
07:58:14.715886+02 | 2021-07-22 07:59:01.652215+02 | active
   15788 |                             |       |             |
2021-07-21
12:38:06.736352+02 |              |
   11216 |                             |       |             |
2021-07-21
12:38:06.722957+02 |              |
   14092 |                             |       |             |
2021-07-21
12:38:06.739031+02 |              |
(9 Zeilen)

(Sorry for bad formatting)

you can use \x (extended mode on) on psql. it will dump the results in a 
mode that can be pasted as text fine.



So you can see we have two idle connections, which are those from
before
the reboots (one had a IPv4 name resolution and two did it by IPv6,
psql
commandline was the same). The backend_start is the same before and
after reboot, so they are the same instances of connections.


I just installed EDB 13.3  on windows. It is managed as a windows 
service (set as automatic), when I rebooted.

shutdown
the machine, it shutdown and terminated connections fine, and restarted 
back fine.
2021-07-22 14:27:19.171 IST [4636] LOG:  disconnection: session time: 
0:03:10.662 user=postgres database=postgres host=::1 port=53494
2021-07-22 14:27:21.805 IST [16120] ERROR:  canceling statement due to 
user request
2021-07-22 14:27:21.810 IST [8080] LOG:  background worker "logical 
replication launcher" (PID 16120) exited with exit code 1

2021-07-22 14:27:21.811 IST [8080] LOG:  received fast shutdown request
2021-07-22 14:27:21.813 IST [8080] LOG:  aborting any active transactions
2021-07-22 14:27:21.821 IST [11884] LOG:  shutting down
2021-07-22 14:27:21.841 IST [8080] LOG:  database system is shut down

startup
2021-07-22 14:28:01.373 IST [7268] LOG:  starting PostgreSQL 13.3, 
compiled by Visual C++ build 1914, 64-bit
2021-07-22 14:28:01.376 IST [7268] LOG:  listening on IPv6 address "::", 
port 5432
2021-07-22 14:28:01.378 IST [7268] LOG:  listening on IPv4 address 
"0.0.0.0", port 5432
2021-07-22 14:28:01.505 IST [8228] LOG:  database system was shut down 
at 2021-07-22 14:27:21 IST
2021-07-22 14:28:01.550 IST [7268] LOG:  database system is ready to 
accept connections


i do not see new connections open, until explicitly do so
then
2021-07-22 14:30:20.733 IST 

Re: Obsolete or dead serverconnections after reboot

[WR]

Hello,

unfortunately, it's my companys choice of OS.
In private life I'm riding the penguin.

One last thing: I have 2 Laptops, where the shutdown doesn't work, and 
one VirtualBox machine where it works like a charm. Maybe it has 
something to do with the power management.


As a workaround I set the value
idle_in_transaction_session_timeout
to 5min. So the server kills this idle connections from before the power 
cycle (and all others too) after 5 minutes.


Thank you, guys

(PS.: still no answer from EDB)

Am 22.07.2021 um 16:51 schrieb Ninad Shah:
Factually, Windows itself has a number of issues. Hence, it is always 
suggested to use Linux with it.


It can be explored further by querying the table from which 
pg_stat_activity gathers data.



Regards,
Ninad Shah


On Thu, 22 Jul 2021 at 12:51, WR <mailto:wolle...@freenet.de>> wrote:


Hello Ninad Shah,

I think, nobody holds the connections, because the state is idle. But
I'm not shure what means: the connection is idle.

One interesting fact was: when I dont stop the psql commandline on the
client (and dont start another query, wht ends up in a connection
reset,
while the server is down), psql uses the same connection after reboot
and the state goes from idle back to active.

So there are two questions: what will happen to idle connctions after a
while, if the client doesnt exist anymore.
And is this desired behavior, the the postgres-Server-Service does
not a
shutdown on reboot on windows. (seem my mail to Vijaykumar Jain)

Thanks you, Greeting from germany,
Wolfgang

Am 21.07.2021 um 16:10 schrieb Ninad Shah:
 > Hello,
 >
 > Would you be able to verify the process trees for those PIDs on
Windows?
 > You may be able to see who holds the connections?
 >
 >
 > Regards,
 > Ninad Shah
 >
 > On Wed, 21 Jul 2021 at 19:15, WR mailto:wolle...@freenet.de>
 > <mailto:wolle...@freenet.de <mailto:wolle...@freenet.de>>> wrote:
 >
 >     Hello Vijaykumar Jain,
 >
 >     thank you for fast answer, today I'm not able to access the
hardware,
 >     I'll be back tomorrow and will do the required tests.
 >
 >     Wolfgang
 >
 >     Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:
 >      > select pg_stat_reset();
 >      >
 >      > Can you run above function, to check if stats are reset and
 >     things are
 >      > fine without a pg restart?
 >      > I not able to understand how new connection stats are added,
 >     along with
 >      > old stale states, if I assume stats collector process is
having
 >     issues ,
 >      > or the stats folder is corrupt etc.
 >      > That would also mean, all table stats would be off or not
updated
 >     too?
 >      > Is that so? Or analyse works fine on tables without a restart?
 >      >
 >      > On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain
 >      > mailto:vijaykumarjain.git...@gmail.com>
 >     <mailto:vijaykumarjain.git...@gmail.com
<mailto:vijaykumarjain.git...@gmail.com>>
 >      > <mailto:vijaykumarjain.git...@gmail.com
<mailto:vijaykumarjain.git...@gmail.com>
 >     <mailto:vijaykumarjain.git...@gmail.com
<mailto:vijaykumarjain.git...@gmail.com>>>> wrote:
 >      >
 >      >
 >      >
 >      >         Is there a way to avoid this (without restarting
the service
 >      >         after every
 >      >         reboot). Is this a bug or a normal behavior?
 >      >
 >      >
 >      >     I have less knowledge of windows.
 >      > https://www.enterprisedb.com/blog/postgresql-shutdown
<https://www.enterprisedb.com/blog/postgresql-shutdown>
 >     <https://www.enterprisedb.com/blog/postgresql-shutdown
<https://www.enterprisedb.com/blog/postgresql-shutdown>>
 >      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
<https://www.enterprisedb.com/blog/postgresql-shutdown>
 >     <https://www.enterprisedb.com/blog/postgresql-shutdown
<https://www.enterprisedb.com/blog/postgresql-shutdown>>>
 >      >
 >      >
 >      >
 >      >     Do you see shutdown/termination messages in the db logs or
 >     windows
 >      >     event logs when the machine is rebooted?
 >      >
 >      >     You get the same pid and query, does it also has the same
 >     age( time
 >      >     since it started),
 >      >     I mean is the stats t

Re: Obsolete or dead serverconnections after reboot

[WR]

Hello Ninad Shah,

Thank you for clarification.
The state of the mentioned connections was "Idle" in pg_stat_activity.

Now on my Laptops the shutdown of the postgres-service works too.

I had to switch off the "Schnellstart" (in english something like "fast 
start" or "fast boot"), which can be found under Settings > System > 
Energy Options > Behavior when pushing the Power-Button / Closing the 
Computer


There in the lower part of the dialog "Settings for Shutdown"

The Option is then disabled first, but can be enabled with a link in the 
upper Part of the dialog.


(Dont ask me why it is so complicated, and what this Option has to do 
with the Power Button / closing the Display, it affects every kind of 
shutdown)


Thanks you for your help.
Wolfgang



Am 23.07.2021 um 16:31 schrieb Ninad Shah:
Just to make it clear, "idle in transaction" and "idle" are different 
terms. "Idle in transaction" refers to connections that are waiting for 
either commit or rollback.


Additionally, this could be a bug as well. However, it's difficult to 
conclude at this stage. You may report this to postgresql-bugs group.



Regards,
Ninad Shah

On Fri, 23 Jul 2021 at 17:25, WR <mailto:wolle...@freenet.de>> wrote:


Hello,

unfortunately, it's my companys choice of OS.
In private life I'm riding the penguin.

One last thing: I have 2 Laptops, where the shutdown doesn't work, and
one VirtualBox machine where it works like a charm. Maybe it has
something to do with the power management.

As a workaround I set the value
idle_in_transaction_session_timeout
to 5min. So the server kills this idle connections from before the
power
cycle (and all others too) after 5 minutes.

Thank you, guys

(PS.: still no answer from EDB)

Am 22.07.2021 um 16:51 schrieb Ninad Shah:
 > Factually, Windows itself has a number of issues. Hence, it is
always
 > suggested to use Linux with it.
 >
 > It can be explored further by querying the table from which
 > pg_stat_activity gathers data.
 >
 >
 > Regards,
 > Ninad Shah
 >
 >
 > On Thu, 22 Jul 2021 at 12:51, WR mailto:wolle...@freenet.de>
 > <mailto:wolle...@freenet.de <mailto:wolle...@freenet.de>>> wrote:
 >
 >     Hello Ninad Shah,
 >
 >     I think, nobody holds the connections, because the state is
idle. But
 >     I'm not shure what means: the connection is idle.
 >
 >     One interesting fact was: when I dont stop the psql
commandline on the
 >     client (and dont start another query, wht ends up in a connection
 >     reset,
 >     while the server is down), psql uses the same connection
after reboot
 >     and the state goes from idle back to active.
 >
 >     So there are two questions: what will happen to idle
connctions after a
 >     while, if the client doesnt exist anymore.
 >     And is this desired behavior, the the postgres-Server-Service
does
 >     not a
 >     shutdown on reboot on windows. (seem my mail to Vijaykumar Jain)
 >
 >     Thanks you, Greeting from germany,
 >     Wolfgang
 >
 >     Am 21.07.2021 um 16:10 schrieb Ninad Shah:
 >      > Hello,
 >      >
 >      > Would you be able to verify the process trees for those
PIDs on
 >     Windows?
 >      > You may be able to see who holds the connections?
 >      >
 >      >
 >      > Regards,
 >      > Ninad Shah
 >      >
 >      > On Wed, 21 Jul 2021 at 19:15, WR mailto:wolle...@freenet.de>
 >     <mailto:wolle...@freenet.de <mailto:wolle...@freenet.de>>
 >      > <mailto:wolle...@freenet.de <mailto:wolle...@freenet.de>
<mailto:wolle...@freenet.de <mailto:wolle...@freenet.de>>>> wrote:
 >      >
 >      >     Hello Vijaykumar Jain,
 >      >
 >      >     thank you for fast answer, today I'm not able to
access the
 >     hardware,
 >      >     I'll be back tomorrow and will do the required tests.
 >      >
 >      >     Wolfgang
 >      >
 >      >     Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:
 >      >      > select pg_stat_reset();
 >      >      >
 >      >      > Can you run above function, to check if stats are
reset and
 >      >     things are
 >      >      > fine without a pg restart?
 >      >      > I not able to understand how new connection stats
are added,
 >      >