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 > 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>
 > >> 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]

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

[Vijaykumar Jain]

On Thu, 22 Jul 2021 at 12:41, WR  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 [15240] LOG:  connection received: host=::1
port=64579
2021-07-22 14:30:20.745 IST [15240] LOG:  connection authorized:
user=postgres database=postgres application_name=psql


the connections i created early on before reboot were terminated, and did
not show up in pg_stat_activity after reboot.
The event logs (i am not pasting screenshots) also show normal shutdown and
restart.
I do not see new psql based connections automatically created unless i open
psql manually.
can you try setting
log_connections = on
log_disconnections = on
in the postgresql.conf file (this would require a restart)
then open a few psql connections, and do a reboot,
does it log connection states in logs?

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:
>

Fwd: Postgres.exe crashes and tears down all apps, recovers and is running again

[Beat Hoedl]

Hi

Have a bunch of processes connected to Postgres (10.0) on windows server 
2016. The software was running without problems for at least a year.


Each process is doing different actions.

Every about 5-10 days a postgres.exe running for one of the application 
processes crashes, tears down everything. Database recorves and then it 
works again as normal.


The wierd thing is it's not always the same application process which 
starts the whole sequence.



One success situation is as follows

2021-07-12 07:33:52.701 CEST [5744] LOG:  duration: 0.020 ms statement: 
COMMIT
2021-07-12 07:33:52.707 CEST [5744] LOG:  duration: 0.023 ms statement: 
DISCARD ALL
2021-07-12 07:33:52.707 CEST [5744] LOG:  duration: 0.005 ms statement: 
BEGIN
2021-07-12 07:33:52.707 CEST [5744] LOG:  duration: 0.011 ms statement: 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

.

The same potgres.exe crashes afterwards

2021-07-12 07:33:55.321 CEST [5744] LOG:  duration: 0.032 ms statement: 
COMMIT
2021-07-12 07:34:32.660 CEST [3972] LOG:  server process (PID 5744) was 
terminated by exception 0x



The COMMIT was the last successfull action in the database.  I verified 
the existence of the data in the database.

The new job at 07:34 caused the crash immediately.

Since I dont see DISCARD ALL/BEGIN/SET TRANSACTION my interpretation is  
this:
The possible (if any) corrupted shared memory is caused by an other 
postgres.exe process.
Therfore checked all actions before these crashes. But also her it's 
always a different one.


BTW: There is no virus scanner on the postgres folder and it's a 
productive system, I cant just update.


So the problem leaves me puzzled.
Would be great if somebody has some ideas how to continue investigation.


Thanks

Beat

Re: Fwd: Postgres.exe crashes and tears down all apps, recovers and is running again

[Julien Rouhaud]

On Thu, Jul 22, 2021 at 11:52:55AM +0200, Beat Hoedl wrote:
> 
> Have a bunch of processes connected to Postgres (10.0) [...]
> 
> So the problem leaves me puzzled.
> Would be great if somebody has some ideas how to continue investigation.

The first thing you need to do is to update to 10.17 and get the almost 4 years
of bug fixes that have been released since.

Re: Doubt on pgbouncer

[Ganesh Korde]

Try using HAProxy.

Regards,
Ganesh Korde.

On Sat, 3 Jul 2021, 9:06 pm Rama Krishnan,  wrote:

>
> Hi Team,
>
> How can I split read and write queries using pgbouncer
>
> Regards
>
> A.Rama Krishnan
>
>
> 
>  Virus-free.
> www.avast.com
> 
> <#m_3619964688060668000_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>

Why does VACUUM FULL pg_class sometimes wait for ShareLock on another transaction after getting AccessExclusiveLock on pg_class?

[Marcin Barczynski]

There was a long-running transaction consisting of two queries:

CREATE TEMPORARY TABLE abc AS SELECT * FROM def_view;
INSERT INTO xyz_table SELECT * FROM abc;

When I ran VACUUM FULL pg_class, it waited for ShareLock on that
transaction:

postgres=# select * from pg_locks where pid = 29563;
   locktype| database |  relation  | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction |  pid  |
 mode | granted | fastpath
---+--++--+---++---+-+---+--++---+-+-+--
 virtualxid|  ||  |   | 414/5739   |
| |   |  | 414/5739   | 29563 |
ExclusiveLock   | t   | t
 relation  |16517 |   1259 |  |   ||
| |   |  | 414/5739   | 29563 |
ShareLock   | t   | f
 relation  |16517 |   1259 |  |   ||
| |   |  | 414/5739   | 29563 |
AccessExclusiveLock | t   | f
 relation  |16517 | 1325035831 |  |   ||
| |   |  | 414/5739   | 29563 |
AccessExclusiveLock | t   | f
 transactionid |  ||  |   ||
113559773 | |   |  | 414/5739   | 29563 |
ExclusiveLock   | t   | f
 transactionid |  ||  |   ||
113551212 | |   |  | 414/5739   | 29563 |
ShareLock   | f   | f
 relation  |16517 |   2662 |  |   ||
| |   |  | 414/5739   | 29563 |
AccessExclusiveLock | t   | f
(7 rows)

Why?
What's more interesting is that from time to time vacuum succeeded despite
the fact that the long-running transaction was still running.

I tried to reproduce it by simulating the long-running transaction, and
running VACUUM FULL pg_class in another transaction, but to no avail:

psql #1:
q=# CREATE TABLE demo AS SELECT generate_series(1, 1000);
SELECT 1000
q=# CREATE VIEW demo_view AS SELECT * FROM demo;
CREATE VIEW
q=# CREATE TABLE result (val BIGINT);
CREATE TABLE

q=# BEGIN;
BEGIN
q=*# CREATE TEMP TABLE abc AS SELECT * FROM demo_view;
SELECT 1000
q=*# INSERT INTO result SELECT * FROM abc;
INSERT 0 1000
q=*#

psql #2:
q=# VACUUM FULL pg_class;
VACUUM

--
Regards,
Marcin Barczynski

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 > 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 [15240] LOG:  connection received: host=::1 
port=64579
2021-07-22 14:3

Re: Fwd: Postgres.exe crashes and tears down all apps, recovers and is running again

[Thomas Kellerer]

Beat Hoedl schrieb am 22.07.2021 um 11:52:

> BTW: There is no virus scanner on the postgres folder and it's a productive 
> system, I cant just update.


Quote from the Postgres homepage https://www.postgresql.org/support/versioning/

> For minor releases, the community considers not upgrading to be riskier than 
> upgrading.

Re: Why does VACUUM FULL pg_class sometimes wait for ShareLock on another transaction after getting AccessExclusiveLock on pg_class?

[Laurenz Albe]

On Thu, 2021-07-22 at 12:55 +0200, Marcin Barczynski wrote:
> There was a long-running transaction consisting of two queries:
> 
> CREATE TEMPORARY TABLE abc AS SELECT * FROM def_view;
> INSERT INTO xyz_table SELECT * FROM abc;
> 
> When I ran VACUUM FULL pg_class, it waited for ShareLock on that transaction:

There must have been something else using "pg_class", since the above
won't take any permament locks on "pg_class", nor should it block VACUUM.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Fwd: Postgres.exe crashes and tears down all apps, recovers and is running again

[Ron]

On 7/22/21 4:52 AM, Beat Hoedl wrote:
[snip]
BTW: There is no virus scanner on the postgres folder and it's a 
productive system, I cant just update.




There's *always* a maintenance window, even if it's just once a year on 
Christmas.  Or are you saying that Windows isn't being patched either?


--
Angular momentum makes the world go 'round.

Re: Fwd: Postgres.exe crashes and tears down all apps, recovers and is running again

[Julien Rouhaud]

On Thu, Jul 22, 2021 at 09:21:56AM -0500, Ron wrote:
> On 7/22/21 4:52 AM, Beat Hoedl wrote:
> [snip]
> > BTW: There is no virus scanner on the postgres folder and it's a
> > productive system, I cant just update.
> > 
> 
> There's *always* a maintenance window, even if it's just once a year on
> Christmas.  Or are you saying that Windows isn't being patched either?

Especially since the OP reported that they experience a crash every 5 to 10
days.

Re: Obsolete or dead serverconnections after reboot

[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  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  > > 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
> >  >  > 
> >  >  > >> 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: Doubt on pgbouncer

[Ninad Shah]

Pgbouncer doesn't provide such a functionality. Kindly consider using
pgpool.

On Thu, 22 Jul 2021 at 15:58, Ganesh Korde  wrote:

> Try using HAProxy.
>
> Regards,
> Ganesh Korde.
>
> On Sat, 3 Jul 2021, 9:06 pm Rama Krishnan,  wrote:
>
>>
>> Hi Team,
>>
>> How can I split read and write queries using pgbouncer
>>
>> Regards
>>
>> A.Rama Krishnan
>>
>>
>> 
>>  Virus-free.
>> www.avast.com
>> 
>> <#m_2585993038985863414_m_3619964688060668000_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>>
>

Re: Why does VACUUM FULL pg_class sometimes wait for ShareLock on another transaction after getting AccessExclusiveLock on pg_class?

[Marcin Barczynski]

On Thu, Jul 22, 2021 at 3:51 PM Laurenz Albe 
wrote:
>
> On Thu, 2021-07-22 at 12:55 +0200, Marcin Barczynski wrote:
> > There was a long-running transaction consisting of two queries:
> >
> > CREATE TEMPORARY TABLE abc AS SELECT * FROM def_view;
> > INSERT INTO xyz_table SELECT * FROM abc;
> >
> > When I ran VACUUM FULL pg_class, it waited for ShareLock on that
transaction:
>
> There must have been something else using "pg_class", since the above
> won't take any permament locks on "pg_class", nor should it block VACUUM.


Thanks for your reply. I dugged a bit deeper, and it turned out that VACUUM
FULL hung in heapam_index_build_range_scan.
It's PostgreSQL 13.3. Comments around heapam_handler.c:1482:

/*
* Since caller should hold ShareLock or better, normally
* the only way to see this is if it was inserted earlier
* in our own transaction.  However, it can happen in
* system catalogs, since we tend to release write lock
* before commit there.  Give a warning if neither case
* applies.
*/

/*
 * If we are performing uniqueness checks, indexing
 * such a tuple could lead to a bogus uniqueness
 * failure.  In that case we wait for the inserting
 * transaction to finish and check again.
 */

Not sure how to interpret those comments, as:
- on the one hand, the long running transaction created a temporary table,
so it had to update pg_class,
- but on the other hand pg_locks showed no sign of lock on any of 3 indexes
of pg_class, and locks once obtained are kept till the end of transaction.

Full stack trace below:

#0  0x7f3537b52e43 in ?? () from /lib64/libc.so.6
#1  0x0076d0ae in WaitEventSetWaitBlock (nevents=1,
occurred_events=0x7ffd7bae7620, cur_timeout=-1, set=0x26c2570) at
latch.c:1295
#2  WaitEventSetWait (set=set@entry=0x26c2570, timeout=timeout@entry=-1,
occurred_events=occurred_events@entry=0x7ffd7bae7620, nevents=nevents@entry=1,
wait_event_info=wait_event_info@entry=50331653) at latch.c:1247
#3  0x0076d52d in WaitLatchOrSocket (latch=0x2aacc9ed95e4,
wakeEvents=wakeEvents@entry=33, sock=sock@entry=-1, timeout=-1,
timeout@entry=0, wait_event_info=50331653) at latch.c:428
#4  0x0076d600 in WaitLatch (latch=,
wakeEvents=wakeEvents@entry=33, timeout=timeout@entry=0,
wait_event_info=) at latch.c:368
#5  0x0078625f in ProcSleep (locallock=locallock@entry=0x25c4c90,
lockMethodTable=lockMethodTable@entry=0x9fa160 ) at
proc.c:1292
#6  0x0077a4a2 in WaitOnLock (locallock=locallock@entry=0x25c4c90,
owner=owner@entry=0x261cf58) at lock.c:1859
#7  0x0077b7cf in LockAcquireExtended
(locktag=locktag@entry=0x7ffd7bae79a0,
lockmode=lockmode@entry=5, sessionLock=sessionLock@entry=false,
dontWait=dontWait@entry=false, reportMemoryError=reportMemoryError@entry=true,
locallockp=locallockp@entry=0x0) at lock.c:1101
#8  0x0077c024 in LockAcquire (locktag=locktag@entry=0x7ffd7bae79a0,
lockmode=lockmode@entry=5, sessionLock=sessionLock@entry=false,
dontWait=dontWait@entry=false) at lock.c:752
#9  0x00779758 in XactLockTableWait (xid=xid@entry=113551215,
rel=rel@entry=0x7f353a2bcdd8, ctid=ctid@entry=0x26c2194,
oper=oper@entry=XLTW_InsertIndexUnique)
at lmgr.c:673
#10 0x004ce843 in heapam_index_build_range_scan
(heapRelation=0x7f353a2bcdd8, indexRelation=0x7f353a387678,
indexInfo=0x26c2030, allow_sync=, anyvisible=false,
progress=true, start_blockno=0, numblocks=4294967295, callback=0x4e80e0
<_bt_build_callback>,
callback_state=0x7ffd7bae7e70, scan=0x26c2140) at heapam_handler.c:1482
#11 0x004e828a in table_index_build_scan (scan=,
callback_state=0x7ffd7bae7e70, callback=0x4e80e0 <_bt_build_callback>,
progress=true, allow_sync=true, index_info=0x26c2030, index_rel=, table_rel=)
at ../../../../src/include/access/tableam.h:1536
#12 _bt_parallel_scan_and_sort (btspool=btspool@entry=0x26c1fe0,
btspool2=0x0, btshared=0x7f353a3fa300, sharedsort=0x7f353a3fa2a0,
sharedsort2=0x0, sortmem=, progress=progress@entry=true) at
nbtsort.c:1970
#13 0x004e9017 in _bt_leader_participate_as_worker
(buildstate=, buildstate=) at nbtsort.c:1778
#14 _bt_begin_parallel (request=, isconcurrent=, buildstate=0x7ffd7bae7fb0) at nbtsort.c:1650
#15 _bt_spools_heapscan (indexInfo=0x26bf158, buildstate=0x7ffd7bae7fb0,
index=..., heap=0x7f353a2bcdd8) at nbtsort.c:394
#16 btbuild (heap=0x7f353a2bcdd8, index=0x7f353a387678,
indexInfo=0x26bf158) at nbtsort.c:326
#17 0x0053f3e6 in index_build
(heapRelation=heapRelation@entry=0x7f353a2bcdd8,
indexRelation=indexRelation@entry=0x7f353a387678,
indexInfo=indexInfo@entry=0x26bf158,
isreindex=isreindex@entry=true, parallel=parallel@entry=true) at
index.c:2962
#18 0x00541c73 in reindex_index (indexId=indexId@entry=2662,
skip_constraint_checks=, persistence=persistence@entry=112
'p', options=options@entry=0) at index.c:3598
#19 0x00542127 in reindex_relation (relid=relid@entry=1259,
flags=flags@entry=18, options=options@entry=0) at index.c:3821
#20 0x005ae37f in finish_heap_swap (OI

version 0 calling convention

[Mark Lybarger]

i have some sql functions written in c code using version 0 calling
convention.  it's working fine on postgresql 9.6, but i'm migrating to
v13.  has this calling convention been removed?

my function creation give an error,

SQL Error [42883]: ERROR: could not find function information for function
"test_func"
  Hint: SQL-callable functions need an accompanying
PG_FUNCTION_INFO_V1(funcname).

Re: version 0 calling convention

[Adrian Klaver]

On 7/22/21 9:18 AM, Mark Lybarger wrote:
i have some sql functions written in c code using version 0 calling 
convention.  it's working fine on postgresql 9.6, but i'm migrating to 
v13.  has this calling convention been removed?


https://www.postgresql.org/docs/13/xfunc-c.html#id-1.8.3.13.7

"Currently only one calling convention is used for C functions (“version 
1”). Support for that calling convention is indicated by writing a 
PG_FUNCTION_INFO_V1() macro call for the function, as illustrated below."




my function creation give an error,

SQL Error [42883]: ERROR: could not find function information for 
function "test_func"
   Hint: SQL-callable functions need an accompanying 
PG_FUNCTION_INFO_V1(funcname).



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

Re: version 0 calling convention

[Pavel Stehule]

čt 22. 7. 2021 v 18:21 odesílatel Adrian Klaver 
napsal:

> On 7/22/21 9:18 AM, Mark Lybarger wrote:
> > i have some sql functions written in c code using version 0 calling
> > convention.  it's working fine on postgresql 9.6, but i'm migrating to
> > v13.  has this calling convention been removed?
>
> https://www.postgresql.org/docs/13/xfunc-c.html#id-1.8.3.13.7
>
> "Currently only one calling convention is used for C functions (“version
> 1”). Support for that calling convention is indicated by writing a
> PG_FUNCTION_INFO_V1() macro call for the function, as illustrated below."
>
> >
> > my function creation give an error,
> >
> > SQL Error [42883]: ERROR: could not find function information for
> > function "test_func"
> >Hint: SQL-callable functions need an accompanying
> > PG_FUNCTION_INFO_V1(funcname).
>

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5ded4bd21403e143dd3eb66b92d52732fdac1945

Regards

Pavel


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

Re: Doubt on pgbouncer

[Rama Krishnan]

Ok, thank you sir

On Thu, 22 Jul, 2021, 20:23 Ninad Shah,  wrote:

> Pgbouncer doesn't provide such a functionality. Kindly consider using
> pgpool.
>
> On Thu, 22 Jul 2021 at 15:58, Ganesh Korde  wrote:
>
>> Try using HAProxy.
>>
>> Regards,
>> Ganesh Korde.
>>
>> On Sat, 3 Jul 2021, 9:06 pm Rama Krishnan,  wrote:
>>
>>>
>>> Hi Team,
>>>
>>> How can I split read and write queries using pgbouncer
>>>
>>> Regards
>>>
>>> A.Rama Krishnan
>>>
>>>
>>> 
>>>  Virus-free.
>>> www.avast.com
>>> 
>>> <#m_-3565042833678223020_m_2585993038985863414_m_3619964688060668000_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>>>
>>

Re: Why does VACUUM FULL pg_class sometimes wait for ShareLock on another transaction after getting AccessExclusiveLock on pg_class?

[Marcin Barczynski]

On Thu, Jul 22, 2021 at 5:08 PM Marcin Barczynski <
mbarczyn...@starfishstorage.com> wrote:

> On Thu, Jul 22, 2021 at 3:51 PM Laurenz Albe 
> wrote:
> >
> > On Thu, 2021-07-22 at 12:55 +0200, Marcin Barczynski wrote:
> > > There was a long-running transaction consisting of two queries:
> > >
> > > CREATE TEMPORARY TABLE abc AS SELECT * FROM def_view;
> > > INSERT INTO xyz_table SELECT * FROM abc;
> > >
> > > When I ran VACUUM FULL pg_class, it waited for ShareLock on that
> transaction:
> >
> > There must have been something else using "pg_class", since the above
> > won't take any permament locks on "pg_class", nor should it block VACUUM.
>
>
> Thanks for your reply. I dugged a bit deeper, and it turned out that
> VACUUM FULL hung in heapam_index_build_range_scan.
> It's PostgreSQL 13.3. Comments around heapam_handler.c:1482:
>

What's more, running VACUUM FULL pg_class sometimes causes a deadlock with
transactions using temp tables. For example:

DETAIL:  Process 6703 waits for ShareLock on transaction 108850229; blocked
by process 6591.
Process 6591 waits for AccessShareLock on relation 1259 of database
16517; blocked by process 6703.
Process 6703: VACUUM (FULL, VERBOSE, ANALYZE) pg_class
Process 6591: SELECT * FROM stored_procedure()

Isn't it a bug?
Is there any way to safely run VACUUM FULL pg_class?
My workload involves lots of temp tables, and I need to get rid of the
bloat regularly.

-- 
Regards,
Marcin Barczynski