PSQLException: An I/O error occurred while sending to the backend.

2020-07-30 Thread Argha Deep Ghoshal 
Hi Team,

We are using PostgreSQL 11 wherein intermittently the below exception is
popping up, causing our application to lose connection with the database.
It isn't reconnecting until the application is restarted.

org.postgresql.util.PSQLException: An I/O error occurred while sending
to the backend.
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:335)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at
org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
at sun.reflect.GeneratedMethodAccessor48.invoke(Unknown Source)
 at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:728)
at
org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:470)
at
org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:395)
at
org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:316)
at
org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1069)
at
org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:528)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at
org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:479)
at
org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at
org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:810)
at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1506)
at
org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.io.EOFException
at org.postgresql.core.PGStream.receiveChar(PGStream.java:308)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1952)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)

We have checked the PostgreSQL logs in detail, however we are unable to
find any significant errors related to this issue.

We have setup HAProxy between our application and DB. So, the requests are
coming to the DB via HAProxy.

PostgresSQL Version : 11

JDBC Version: 42.2.5

All the servers are present in the same region and building.


Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: Doubt in mvcc

2020-07-30 Thread Naresh gandi 
Dear Ramakrishna,

"two different transactions can update the same version of the row"

This answer itself is wrong.

In my point of view, the drawback of MVCC is just holding multiple versions
of tuple in a table which leads to slowness in application access. the more
your table is bloated the more it takes to retrieve data.it has to scan so
much of _VM, so many pages which is time consuming.

The other drawback is anyway space.

There are a couple of workarounds to address the issue is what you should
tell your recruiter.

Any RDBMS has its own mechanism to address Isolation property and each
mechanism has it own flaws.


On Thu, Jul 23, 2020 at 12:16 AM Bruce Momjian  wrote:

> On Mon, Jul 13, 2020 at 10:41:28AM +0200, Francisco Olarte wrote:
> > Rama:
> >
> > On Mon, Jul 13, 2020 at 9:52 AM Rama Krishnan 
> wrote:
> > > I m preparing for interview one of the recruiter asked me mvcc
> drawbacks as i told due to mvcc it use more space and need to perform
> maintenance activity.
> > > Another one is the same data causes an update conflict because two
> different transactions can update the same version of the row.
> > >  he told its wrong, kindly tell me will you please tell me its correct
> or wrong?
> >
> > I'm not sure I understand your question too well, you may want to
> > refresh/expand.
> >
> > One interpretation is, on a pure MVCC contest, two transactions, say 5
> > and 6, could try to update a tuple valid for [1,) and end up
> > generating two new tuples, [5,), [6,) and closing the original at
> > either [1,5) or [1,6) .
> >
> > That's why MVCC is just a piece, locking is other. On a MVCC the
> > tuples are locked while a transaction manipulates them. Other
> > transactions may read them, which is why readers do not block writers,
> > but two updates on the same tuple serialize.
>
> You might want to look at this:
>
> https://momjian.us/main/presentations/internals.html#mvcc
>
> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
>
>   The usefulness of a cup is in its emptiness, Bruce Lee
>
>
>
>

Re: Out of memory with "create extension postgis"

2020-07-30 Thread Daniel Westermann (DWE) 
From: Tom Lane 
Sent: Wednesday, July 29, 2020 17:05
To: Daniel Westermann (DWE) 
Cc: pgsql-general@lists.postgresql.org 
Subject: Re: Out of memory with "create extension postgis" 
 
"Daniel Westermann (DWE)"  writes:
>> So this is what we got today. In the log file there is this:

>> 2020-07-29 16:33:23 CEST 101995 ERROR:  out of memory
>> 2020-07-29 16:33:23 CEST 101995 DETAIL:  Failed on request of size 8265691 
>> in memory context "PortalContext".
>> 2020-07-29 16:33:23 CEST 101995 STATEMENT:  create extension postgis;

>Is there not a dump of memory context sizes just before the "ERROR: out of
>memory" line?  It should look something like

>TopMemoryContext: 68720 total in 5 blocks; 17040 free (15 chunks); 51680 used
>  MessageContext: 8192 total in 1 blocks; 6880 free (1 chunks); 1312 used
>...
>  ErrorContext: 8192 total in 1 blocks; 7928 free (3 chunks); 264 used
>Grand total: 1063328 bytes in 190 blocks; 312376 free (159 chunks); 750952 used

>(this taken from an idle backend, so numbers from a backend that's hit
>OOM would be a lot larger).  If you don't see that then you must be
>using some logging mechanism that fails to capture the postmaster's
>stderr output, such as syslog.  If your postmaster start script doesn't
>actually send stderr to /dev/null, you might find the context map in some
>other log file.

Thanks for the hint, will check

>Umm ... you didn't issue a "bt" when you got to errfinish, so there's
>no useful info here.

Here is a new one with bt at the end:

Breakpoint 1 at 0x87e210: file elog.c, line 411.
Continuing.

Breakpoint 1, errfinish (dummy=dummy@entry=0) at elog.c:411
411 elog.c: No such file or directory.
Continuing.

Breakpoint 1, errfinish (dummy=dummy@entry=0) at elog.c:411
411 in elog.c
Missing separate debuginfos, use: debuginfo-install CGAL-4.7-1.rhel7.1.x86_64 
boost-date-time-1.53.0-27.el7.x86_64 boost-serialization-1.53.0-27.el7.x86_64 
boost-system-1.53.0-27.el7.x86_64 boost-thread-1.53.0-27.el7.x86_64 
gmp-6.0.0-15.el7.x86_64 jbigkit-libs-2.0-11.el7.x86_64 
json-c-0.11-4.el7_0.x86_64 libcurl-7.29.0-54.el7_7.2.x86_64 
libidn-1.28-4.el7.x86_64 libjpeg-turbo-1.2.90-8.el7.x86_64 
libssh2-1.8.0-3.el7.x86_64 libtiff-4.0.3-32.el7.x86_64 mpfr-3.1.1-4.el7.x86_64 
postgis30_12-3.0.1-5.rhel7.x86_64 protobuf-c-1.0.2-3.el7.x86_64 
sqlite33-libs-3.30.1-1.rhel7.x86_64
Continuing.

Program received signal SIGINT, Interrupt.
0x7f94f4af2e93 in __epoll_wait_nocancel () from /lib64/libc.so.6
#0  0x7f94f4af2e93 in __epoll_wait_nocancel () from /lib64/libc.so.6
#1  0x0073fdae in WaitEventSetWaitBlock (nevents=1, 
occurred_events=0x7ffcf3b4bc30, cur_timeout=-1, set=0x27c3718) at latch.c:1080
#2  WaitEventSetWait (set=0x27c3718, timeout=timeout@entry=-1, 
occurred_events=occurred_events@entry=0x7ffcf3b4bc30, nevents=nevents@entry=1, 
wait_event_info=wait_event_info@entry=100663296) at latch.c:1032
#3  0x0064fbd7 in secure_read (port=0x27c3900, ptr=0xd45a80 
, len=8192) at be-secure.c:185
#4  0x0065aa38 in pq_recvbuf () at pqcomm.c:964
#5  0x0065b655 in pq_getbyte () at pqcomm.c:1007
#6  0x00761aaa in SocketBackend (inBuf=0x7ffcf3b4bda0) at postgres.c:341
#7  ReadCommand (inBuf=0x7ffcf3b4bda0) at postgres.c:514
#8  PostgresMain (argc=, argv=argv@entry=0x27cb420, 
dbname=0x27cb2e8 "pcl_l800", username=) at postgres.c:4189
#9  0x00484022 in BackendRun (port=, port=) at postmaster.c:4448
#10 BackendStartup (port=0x27c3900) at postmaster.c:4139
#11 ServerLoop () at postmaster.c:1704
#12 0x006f14c3 in PostmasterMain (argc=argc@entry=3, 
argv=argv@entry=0x278c280) at postmaster.c:1377
#13 0x00484f23 in main (argc=3, argv=0x278c280) at main.c:228
A debugging session is active.

Inferior 1 [process 97279] will be detached.

Quit anyway? (y or n) Detaching from program: /usr/pgsql-12/bin/postgres, 
process 97279


>> Missing separate debuginfos, use: debuginfo-install 
>> CGAL-4.7-1.rhel7.1.x86_64 boost-date-time-1.53.0-27.el7.x86_64 
>> boost-serialization-1.53.0-27.el7.x86_64 boost-system-1.53.0-27.el7.x86_64 
>> boost-thread-1.53.0-27.el7.x86_64 gmp-6.0.0-15.el7.x86_64 
>> jbigkit-libs-2.0-11.el7.x86_64 json-c-0.11-4.el7_0.x86_64 
>> libcurl-7.29.0-54.el7_7.2.x86_64 libidn-1.28-4.el7.x86_64 
>> libjpeg-turbo-1.2.90-8.el7.x86_64 libssh2-1.8.0-3.el7.x86_64 
>> libtiff-4.0.3-32.el7.x86_64 mpfr-3.1.1-4.el7.x86_64 
>> postgis30_12-3.0.1-5.rhel7.x86_64 protobuf-c-1.0.2-3.el7.x86_64 
>> sqlite33-libs-3.30.1-1.rhel7.x86_64

>This is a little weird and scary; I would not expect most of those
>libraries to have gotten linked into a Postgres backend.  What
>extensions are you using?  (And what the devil would any of them
>want with sqlite or libcurl?  boost-thread is even scarier, because
>we absolutely do not support multithreading in a backend process.)

These are the extensions in use:
$ psql -X -c "\dx"
 List of installed extensions
Name| Version |   Schema

Re: Track pgsql steps

2020-07-30 Thread Julien Rouhaud 
On Wed, Jul 29, 2020 at 7:58 PM Adrian Klaver  wrote:
>
> On 7/29/20 8:44 AM, Olivier Leprêtre wrote:
> > Hi,
> >
> > I have a rather long pgsql procedure and I would like to detect which
> > step is currently executing (subscript 1,2,3…). Due to transaction
> > isolation, it’s not possible to make it write in a table or get nexval
> > from a sequence because values become available only after the complete
> > end of the procedure.
> >
> > Do you see any solution in this purpose ?
>
> RAISE NOTICE?:
>
> https://www.postgresql.org/docs/12/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE

You can also abuse SET application_name, as the value will be directly
seen by other transactions.  You're quite limited in the number of
bytes to use, but if you just need to do some quick testing it can be
helpful.

How to find out why user processes are using so much memory?

2020-07-30 Thread Zwettler Markus (OIZ) 
Hi,

Having an application on Postgres. 
Most parameters are on default.
Memory related parameters are slightly increased:
effective_cache_size = 512MB
max_connections = 300
maintenance_work_mem = 64MB
shared_buffers = 512MB
wal_buffers = 16MB
work_mem = 10MB

92 user processes are using around 30G RAM + 20G Swap at the moment.
pg_top shows an even distribution of RAM per process (see below).

It seems the application is eating up more and more memory.
Any idea how to find out why the user processes are using that much memory?

Is there a statistic memory usage per statement or something like that?
pg_stat_statements is only showing shared_buffers per statement.

-Markus



last pid: 92572;  load avg:  0.22,  0.32,  0.22;   up 258+21:20:34  
11:22:16
96 processes: 96 sleeping
CPU states:  0.2% user,  0.0% nice,  0.2% system, 99.6% idle,  0.0% iowait
Memory: 45G used, 2091M free, 624K buffers, 13G cached
DB activity:  33 tps,  0 rollbs/s,  81 buffer r/s, 98 hit%,479 row r/s,
7 row w/s s
DB I/O: 5 reads/s,   171 KB/s, 2 writes/s,19 KB/s
DB disk: 190.0 GB total, 28.2 GB free (85% used)
Swap: 19G used, 3407M free, 90M cached

  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPUCPU COMMAND
48064 postgres  200  788M  146M sleep   0:03  0.02%  0.20% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p01_oiz 10.9.132.61(54140) idle
77908 postgres  200 2266M 1960M sleep  32:13  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44496) idle
60355 postgres  200 2266M 1864M sleep  29:00  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36638) idle
92184 postgres  200 2187M 1893M sleep  28:20  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(33216) idle
60356 postgres  200 2236M 1852M sleep  27:56  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36642) idle
60359 postgres  200 2283M 1906M sleep  24:03  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36644) idle
77909 postgres  200 2219M 1947M sleep  23:59  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44498) idle
77910 postgres  200 2191M 1918M sleep  22:20  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44504) idle
60357 postgres  200 2203M 1935M sleep  20:48  0.52%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36635) idle
96423 postgres  200 2242M 1945M sleep  18:29  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(43288) idle
60328 postgres  200 M 1947M sleep  18:23  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36558) idle
99094 postgres  200 2349M 1989M sleep  16:58  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(58596) idle
60339 postgres  200 1774M 1361M sleep  16:10  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36578) idle
60354 postgres  200 2282M 1940M sleep  14:56  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36634) idle
77703 postgres  200 2035M 1714M sleep  12:29  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45088) idle
60358 postgres  200 1788M  608M sleep  11:43  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36640) idle
110412 postgres  200 2100M  813M sleep  11:26  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(48528) idle
60317 postgres  200 1986M 1634M sleep  11:24  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(40160) idle
58878 postgres  200 2003M 1694M sleep   9:50  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54224) idle
60315 postgres  200 2060M 1805M sleep   9:50  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(40156) idle
51969 postgres  200 2018M  829M sleep   8:54  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54512) idle
58875 postgres  200 1957M 1553M sleep   8:53  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54218) idle
77704 postgres  200 2108M  563M sleep   8:31  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45090) idle
58876 postgres  200 1925M  831M sleep   8:29  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54220) idle
58880 postgres  200 2236M  807M sleep   8:24  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54228) idle
58862 postgres  200 1684M  559M sleep   7:58  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54158) idle
77702 postgres  200 1027M  712M sleep   7:06  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45086) idle
110414 postgres  200 1294M  710M sleep   6:54  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.1

RE: Track pgsql steps

2020-07-30 Thread Olivier Leprêtre 
Hi, thanks for your answers,

Application_name is a good tip, 64 chars are enough to code steps, I'll use that
I expected being able to write raise events to a table or to store data in 
another table but it doesn't appear to be possible due to transaction isolation.

Thanks a lot.

Olivier

-Message d'origine-
De : Julien Rouhaud 
Envoyé : jeudi 30 juillet 2020 12:19
À : Adrian Klaver 
Cc : Olivier Leprêtre ; pgsql-general 

Objet : Re: Track pgsql steps

On Wed, Jul 29, 2020 at 7:58 PM Adrian Klaver  wrote:
>
> On 7/29/20 8:44 AM, Olivier Leprêtre wrote:
> > Hi,
> >
> > I have a rather long pgsql procedure and I would like to detect
> > which step is currently executing (subscript 1,2,3…). Due to
> > transaction isolation, it’s not possible to make it write in a table
> > or get nexval from a sequence because values become available only
> > after the complete end of the procedure.
> >
> > Do you see any solution in this purpose ?
>
> RAISE NOTICE?:
>
> https://www.postgresql.org/docs/12/plpgsql-errors-and-messages.html#PL
> PGSQL-STATEMENTS-RAISE

You can also abuse SET application_name, as the value will be directly seen by 
other transactions.  You're quite limited in the number of bytes to use, but if 
you just need to do some quick testing it can be helpful.


--
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel 
antivirus Avast.
https://www.avast.com/antivirus

Questions about Logical Replication - Issue ???

2020-07-30 Thread FOUTE K . Jaurès 
Hi everyone,

Situation:

   - A Master Database on the HQ
   - i make a dump of the master database to the Subdivision Server
   - I create à Publication like: CREATE PUBLICATION
   iNOV_MasterData_Table_Pub FOR TABLE M_Product; On the Master Database
   - On the Subdivision Server, I create a Subscription like: CREATE
   SUBSCRIPTION iNOV_MasterData_Table_XXX_Sub CONNECTION ''
   PUBLICATION  iNOV_MasterData_Table_Pub;
   - On the log, I have this error:
  - 2020-07-30 14:32:59.366 WAT [8022] ERROR:  duplicate key value
  violates unique constraint "m_product_pkey"
  2020-07-30 14:32:59.366 WAT [8022] DETAIL:  Key
  (m_product_id)=(1001426) already exists.
  2020-07-30 14:32:59.366 WAT [8022] CONTEXT:  COPY m_product, line 1
  2020-07-30 14:32:59.369 WAT [1536] LOG:  background worker "logical
  replication worker" (PID 8022) exited with exit code 1

What can I do to solve this? Is it normal ? It
BTW: When I create Subscription With the option  (copy_data = false), I am
able to replicate the new record.

-- 
Jaurès FOUTE

Re: Track pgsql steps

2020-07-30 Thread Diego 

are you running the psql with -c or -f?

add -o to put all in a file and -e to write the last query, and with a 
tail to the log, you can see where is the problem


On 2020-07-30 10:11, Olivier Leprêtre wrote:

Hi, thanks for your answers,

Application_name is a good tip, 64 chars are enough to code steps, I'll use that
I expected being able to write raise events to a table or to store data in 
another table but it doesn't appear to be possible due to transaction isolation.

Thanks a lot.

Olivier

-Message d'origine-
De : Julien Rouhaud 
Envoyé : jeudi 30 juillet 2020 12:19
À : Adrian Klaver 
Cc : Olivier Leprêtre ; pgsql-general 

Objet : Re: Track pgsql steps

On Wed, Jul 29, 2020 at 7:58 PM Adrian Klaver  wrote:

On 7/29/20 8:44 AM, Olivier Leprêtre wrote:

Hi,

I have a rather long pgsql procedure and I would like to detect
which step is currently executing (subscript 1,2,3…). Due to
transaction isolation, it’s not possible to make it write in a table
or get nexval from a sequence because values become available only
after the complete end of the procedure.

Do you see any solution in this purpose ?

RAISE NOTICE?:

https://www.postgresql.org/docs/12/plpgsql-errors-and-messages.html#PL
PGSQL-STATEMENTS-RAISE

You can also abuse SET application_name, as the value will be directly seen by 
other transactions.  You're quite limited in the number of bytes to use, but if 
you just need to do some quick testing it can be helpful.

Re: PSQLException: An I/O error occurred while sending to the backend.

2020-07-30 Thread Tom Lane 
Argha Deep Ghoshal  writes:
> We are using PostgreSQL 11 wherein intermittently the below exception is
> popping up, causing our application to lose connection with the database.
> It isn't reconnecting until the application is restarted.

> org.postgresql.util.PSQLException: An I/O error occurred while sending
> to the backend.

That certainly looks like loss of network connection.  Had the connection
been sitting idle for awhile before this query attempt?

> We have checked the PostgreSQL logs in detail, however we are unable to
> find any significant errors related to this issue.

I'd expect that the backend would eventually notice the dead connection.
But the timeout before it does so might be completely different from the
time at which the client notices the dead connection, so the relationship
might not be very obvious.

> All the servers are present in the same region and building.

Doesn't mean there's not routers or firewalls between them.  I'd start
by looking for network timeouts, and possibly configuring the server
to send TCP keepalives more aggressively.  (In this case it might be
HAProxy that needs to be sending keepalives ... don't know what options
it has for that.)

regards, tom lane

Re: How to find out why user processes are using so much memory?

2020-07-30 Thread Pavel Stehule 
Hi

čt 30. 7. 2020 v 12:33 odesílatel Zwettler Markus (OIZ) <
markus.zwett...@zuerich.ch> napsal:

> Hi,
>
> Having an application on Postgres.
> Most parameters are on default.
> Memory related parameters are slightly increased:
> effective_cache_size = 512MB
> max_connections = 300
> maintenance_work_mem = 64MB
> shared_buffers = 512MB
> wal_buffers = 16MB
> work_mem = 10MB
>
> 92 user processes are using around 30G RAM + 20G Swap at the moment.
> pg_top shows an even distribution of RAM per process (see below).
>
> It seems the application is eating up more and more memory.
> Any idea how to find out why the user processes are using that much memory?
>
> Is there a statistic memory usage per statement or something like that?
> pg_stat_statements is only showing shared_buffers per statement.
>
> -Markus
>
>
>
> last pid: 92572;  load avg:  0.22,  0.32,  0.22;   up 258+21:20:34
>
> 11:22:16
> 96 processes: 96 sleeping
> CPU states:  0.2% user,  0.0% nice,  0.2% system, 99.6% idle,  0.0% iowait
> Memory: 45G used, 2091M free, 624K buffers, 13G cached
> DB activity:  33 tps,  0 rollbs/s,  81 buffer r/s, 98 hit%,479 row
> r/s,7 row w/s s
> DB I/O: 5 reads/s,   171 KB/s, 2 writes/s,19 KB/s
> DB disk: 190.0 GB total, 28.2 GB free (85% used)
> Swap: 19G used, 3407M free, 90M cached
>
>   PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPUCPU COMMAND
> 48064 postgres  200  788M  146M sleep   0:03  0.02%  0.20% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p01_oiz 10.9.132.61(54140) idle
> 77908 postgres  200 2266M 1960M sleep  32:13  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44496) idle
> 60355 postgres  200 2266M 1864M sleep  29:00  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36638) idle
> 92184 postgres  200 2187M 1893M sleep  28:20  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(33216) idle
> 60356 postgres  200 2236M 1852M sleep  27:56  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36642) idle
> 60359 postgres  200 2283M 1906M sleep  24:03  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36644) idle
> 77909 postgres  200 2219M 1947M sleep  23:59  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44498) idle
> 77910 postgres  200 2191M 1918M sleep  22:20  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44504) idle
> 60357 postgres  200 2203M 1935M sleep  20:48  0.52%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36635) idle
> 96423 postgres  200 2242M 1945M sleep  18:29  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(43288) idle
> 60328 postgres  200 M 1947M sleep  18:23  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36558) idle
> 99094 postgres  200 2349M 1989M sleep  16:58  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(58596) idle
> 60339 postgres  200 1774M 1361M sleep  16:10  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36578) idle
> 60354 postgres  200 2282M 1940M sleep  14:56  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36634) idle
> 77703 postgres  200 2035M 1714M sleep  12:29  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45088) idle
> 60358 postgres  200 1788M  608M sleep  11:43  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36640) idle
> 110412 postgres  200 2100M  813M sleep  11:26  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(48528) idle
> 60317 postgres  200 1986M 1634M sleep  11:24  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(40160) idle
> 58878 postgres  200 2003M 1694M sleep   9:50  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54224) idle
> 60315 postgres  200 2060M 1805M sleep   9:50  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(40156) idle
> 51969 postgres  200 2018M  829M sleep   8:54  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54512) idle
> 58875 postgres  200 1957M 1553M sleep   8:53  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54218) idle
> 77704 postgres  200 2108M  563M sleep   8:31  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45090) idle
> 58876 postgres  200 1925M  831M sleep   8:29  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54220) idle
> 58880 postgres  200 2236M  807M sleep   8:24  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54228) idle
> 58862 postgres  200 1684M  559M sleep   7:58  0.00%  0.00% postgres:
> pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54158) idle
> 77702 postgres  200 1027M  712M sleep   7:06  0.00%  0.00% postgres:
> pcl_p011: pdb_

Re: Out of memory with "create extension postgis"

2020-07-30 Thread Tom Lane 
"Daniel Westermann (DWE)"  writes:
>> Umm ... you didn't issue a "bt" when you got to errfinish, so there's
>> no useful info here.

> Here is a new one with bt at the end:

That's just showing the stack when the backend is idle waiting for input.
We need to capture the stack at the moment when the "out of memory" error
is reported (errfinish() should be the top of stack).

>> libraries to have gotten linked into a Postgres backend.  What
>> extensions are you using?

> These are the extensions in use:
>  plpythonu  | 1.0 | pg_catalog | PL/PythonU untrusted procedural 
> language

Hm.  If you've been actively using plpython in this session, perhaps
libpython would have pulled all this other weirdness in with it.

regards, tom lane

Re: PSQLException: An I/O error occurred while sending to the backend.

2020-07-30 Thread Argha Deep Ghoshal 
Hi Tom,

Appreciate your inputs. Please find my comments inline below.


> We are using PostgreSQL 11 wherein intermittently the below exception is
> > popping up, causing our application to lose connection with the database.
> > It isn't reconnecting until the application is restarted.
>
> > org.postgresql.util.PSQLException: An I/O error occurred while
> sending
> > to the backend.
>
> That certainly looks like loss of network connection.  Had the connection
> been sitting idle for awhile before this query attempt?
>
> *- We are sending requests continuously using Jmeter and the exceptions
are interspersed. Out of 100 say 8-9 requests are getting this exception
and there is no lag between them. The connections I think are being kept
open after the testing is done, but shouldn't the error come against the
first response when we are reopening for test. The exceptions are coming
after 10-15 requests.*


> > We have checked the PostgreSQL logs in detail, however we are unable to
> > find any significant errors related to this issue.
>
> I'd expect that the backend would eventually notice the dead connection.
> But the timeout before it does so might be completely different from the
> time at which the client notices the dead connection, so the relationship
> might not be very obvious.
>

- *Initially I was seeing connection termination error in the logs.
However, currently this exception is not breaking the connectivity so no
errors are getting logged in the database.*

>
> > All the servers are present in the same region and building.
>
> Doesn't mean there's not routers or firewalls between them.  I'd start
> by looking for network timeouts, and possibly configuring the server
> to send TCP keepalives more aggressively.  (In this case it might be
> HAProxy that needs to be sending keepalives ... don't know what options
> it has for that.)
>
>
- *I have made the below changes in our HAProxy server. *




*net.ipv4.tcp_keepalive_time = 600net.ipv4.tcp_keepalive_intvl = 60
net.ipv4.tcp_keepalive_probes = 20*

*Currently we are testing to see whether this did the trick.*



> regards, tom lane
>

Costs of Heap Fetches in Postgres 13

2020-07-30 Thread Jens . Wilke 

Hi,

does the planner do estimates about heap fetches on index only scans and
takes them into account?
At least in Pg 13? If so, is it  possible to lower those costs?
random_page_costs seems not to have any influence.
Looks like they cause bad planner decisions.
Tuning Vacuum is not an option ;)

regards, Jens

Re: Questions about Logical Replication - Issue ???

2020-07-30 Thread Kyotaro Horiguchi 
Hi,

At Thu, 30 Jul 2020 14:54:08 +0100, FOUTE K. Jaurès  
wrote in 
> Hi everyone,
> 
> Situation:
> 
>- A Master Database on the HQ
>- i make a dump of the master database to the Subdivision Server
>- I create à Publication like: CREATE PUBLICATION
>iNOV_MasterData_Table_Pub FOR TABLE M_Product; On the Master Database
>- On the Subdivision Server, I create a Subscription like: CREATE
>SUBSCRIPTION iNOV_MasterData_Table_XXX_Sub CONNECTION ''
>PUBLICATION  iNOV_MasterData_Table_Pub;
>- On the log, I have this error:
>   - 2020-07-30 14:32:59.366 WAT [8022] ERROR:  duplicate key value
>   violates unique constraint "m_product_pkey"
>   2020-07-30 14:32:59.366 WAT [8022] DETAIL:  Key
>   (m_product_id)=(1001426) already exists.
>   2020-07-30 14:32:59.366 WAT [8022] CONTEXT:  COPY m_product, line 1
>   2020-07-30 14:32:59.369 WAT [1536] LOG:  background worker "logical
>   replication worker" (PID 8022) exited with exit code 1
> 
> What can I do to solve this? Is it normal ? It
> BTW: When I create Subscription With the option  (copy_data = false), I am
> able to replicate the new record.

As you know, initial table copy happens defaultly at subscription
creation (the COPY command in the above log lines was doing that). If
you are sure that the publisher table is in-sync with the subscriber
one, you can use copy_data=false safely and it's the proper operation.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Costs of Heap Fetches in Postgres 13

2020-07-30 Thread David Rowley 
On Fri, 31 Jul 2020 at 05:21,  wrote:
> does the planner do estimates about heap fetches on index only scans and 
> takes them into account?
> At least in Pg 13? If so, is it  possible to lower those costs? 
> random_page_costs seems not to have any influence.
> Looks like they cause bad planner decisions.
> Tuning Vacuum is not an option ;)

You'll probably need to give us an example of this not working by
means of an EXPLAIN output.

The planner uses the ratio of blocks marked as all visible from
pg_class.relallvisible and the current number of blocks in the
relation and applies random_page_cost to the expected heap blocks it
would read from an Index Scan offset by 1.0 - that ratio.

e.g. If the table has 1000 blocks and you have 900 marked as all
visible, and an index scan expects to read 200 blocks, then it will
apply random_page_cost * 200 * (1.0 - (900.0 / 1000.0)).  Which in
this case is 20 blocks. Your all visible ratio here is 90%, 10% are
not all visible, so 10% of 200 blocks is 20 blocks.

If I mock up a case like that and then tweak random_page_cost, then I
see the total cost changing just fine.  I did only test in master, but
we'll not have changed that since branching for PG13.

Perhaps you've got some tablespace level random_page_cost set and
you're not actually changing it?

David