Re: Question on Partition key

[Deep]

Have your friends also mentioned how it is going to help to convert date
field to integer !???

On Sun, Sep 3, 2023 at 3:51 AM Erik Wienhold  wrote:

> On 03/09/2023 00:35 CEST veem v  wrote:
>
> > We are trying to create a monthly range partition table , partitioned on
> > column PART_DATE. This will hold Orders and part_date is nothing but
> invoice
> > date. Some Team mates are asking to use the "PART_DATE" column as data
> type
> > "INTEGER" with "MM" format [...]
>
> Why do your team mates favor integer over date?
>
> > Want to know experts' views on this. If the data type of the partition
> key
> > matters here or not?
>
> Both integer and date are stored as 4 bytes.  There should be no difference
> regarding index size.  I don't know if the data type makes a difference in
> partition pruning performance in this case, but I'd be surprised if it were
> the case.
>
> > Or if there is any downside of each approach in future?
>
> The downside of integer is that it allows invalid dates (e.g. 202313)
> unless
> you also add check constraints.  But then just use date if you want to
> store
> dates.  You get input validation and can use the date operators and
> functions
> that Postgres offers.
>
> --
> Erik
>
>
>

Re: Strategy for migrating from Oracle to PG

[Deep]

As far as I know, you don't need to create the users, if you create the
user anyway before migration then ora2pg will skip the create statement,
but objects under the user/schema will be created

On Tue, Sep 5, 2023 at 10:24 AM Johnson, Bruce E - (bjohnson) <
john...@pharmacy.arizona.edu> wrote:

> I have an oracle database with a bunch of schemas that are the data
> sources for bunch of web applications; what used to be called an
> ‘intranet’.
>
> I’ve installed pg2sql, and it’s working, and what I want to do is pretty
> much copy what I existing now as closely as possible.
>
> I've done ora2pg —project_base /oracle_migrate/ —init_project [schema name]
>
> Properly set the schema username and password in the ./config/ora2pg.conf
> filer each of them
>
> Do I need to create the users in Postgres, or is that done as part of the
> import_all.sh script?
>
> The schemas themselves are not all that complex, mostly just tables, views
> and a handful of procedures and grants.
>
> Installed version of Postgres is v15.
>
> --
> Bruce Johnson
> University of Arizona
> College of Pharmacy
> Information Technology Group
>
> Institutions do not have opinions, merely customs
>
>
>

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

[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: PSQLException: An I/O error occurred while sending to the backend.

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