Re: JDBC connectivity issue

[Dave Cramer]

You should be using the latest version of the driver. What version are you
using ?

Even though you have a 9.4 database the latest version is the correct
version to use.

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 8 March 2018 at 22:14, David G. Johnston 
wrote:

> On Thu, Mar 8, 2018 at 2:30 PM, chris  wrote:
>
>> Given that the syntax looks correct for the url, how would we go about
>> debugging that it's not seeing the comma?
>
>
> ​First thing I'd do is ensure the version of the driver I'm using supports
> the feature I'm trying to use.
>
> David J.
>
>

Re: JDBC connectivity issue

[Dave Cramer]

Chris,

At this point I'd write small piece of code to test if the url and failover
is working correctly. Sounds like you have a considerably sized stack
making it difficult to debug.
With a small piece of code it should become clear as to how things work or
don't as the case may be

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 21 March 2018 at 17:13, Adrian Klaver  wrote:

> On 03/21/2018 01:56 PM, chris wrote:
>
>> I did the re install not to change versions but to now know what version
>> I am running
>>
>
> My previous question was not as clear as should have been.
> So:
> 1) At some place in your software stack there is some sort of
> configuration that links your app via JDBC to a Postgres JDBC driver. If
> you know where that configuration is you should be able to find the driver
> and presumably the version.
> 2) So when you say you did a reinstall do you mean you are now pointing
> the configuration at postgresql-42.2.1.jre7.jar? FYI
> postgresql-42.2.2.jre7.jar is actually the latest:
> https://jdbc.postgresql.org/download.html
>
>
>
>>
>> On 03/21/2018 02:44 PM, Adrian Klaver wrote:
>>
>>> On 03/21/2018 01:16 PM, chris wrote:
>>>
>>>> I wasnt able to find what version we had installed so we went ahead and
>>>> reinstalled it
>>>>
>>>
>>> Maybe I am missing something, but if you could not find the version you
>>> where using how do you know installing a new driver actually changed the
>>> version you are using now?
>>>
>>>
>>>
>>>> we downloaded the current version JDBC 4.1 Driver 42.2.1.jre7
>>>>
>>>>
>>>> We are still having the same problem.
>>>>
>>>> Thanks
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: JDBC connectivity issue

[Dave Cramer]

Cool, thanks. I'd love to see this work

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 23 March 2018 at 10:24, JCMS22  wrote:

> I forgot to say I am trying to investigate how to make it work as we need
> specific stuff from the DataSource object.
>
> It might work if you use setServerName rather than setUrl. setServerName
> will append itself the jdbc:postgresql:// so you only give it the server
> name (i.e "url1:port1,url2:port2") and then setPortNumber to 0. By reading
> the code, this might work.
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-
> f1843780.html
>
>

Re: Code of Conduct plan

[Dave Cramer]

On 4 June 2018 at 17:53, Tom Lane  wrote:

> Alvaro Herrera  writes:
> > On 2018-Jun-05, Gavin Flower wrote:
> >> If we are all adults, then we don't need a CoC.
>
> > "We're all adults" is wishful thinking.  Some old people are just kids
> > who aged but didn't actually mature.
>
> I'm sure we'd all be ecstatic if the CoC committee never actually has
> anything to do.  The point of this exercise is to make new people ---
> particularly women and others who have often felt disadvantaged in
> technical communities --- feel safe and welcome here.
>
> Also: we *have* had cases where women who had been contributors left
> because of harassment, and I'd like to ensure that doesn't happen again.
>
>
+1000
Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: Building a notification system.

[Dave Cramer]

On 15 July 2018 at 23:25, Anto Aravinth  wrote:

>
>
> On Mon, Jul 16, 2018 at 8:02 AM, Christopher Browne 
> wrote:
>
>> On Sun, Jul 15, 2018, 5:30 AM Anto Aravinth, 
>> wrote:
>>
>>> Hello Everyone,
>>>
>>>
>>> I'm playing around with postgresql with SO datasets. In the process, I
>>> have dumped 60M questions data onto the postgresql. I'm trying to build a
>>> notification system on top of this, so that, when a user edits a question,
>>> I need to show a notification to the user when he/she logs in next time. So
>>> literally, trying to create the clone of SO (with very minimal feature)
>>>
>>> I'm not sure, how to get started with this. Read about NOTIFY:
>>> https://www.postgresql.org/docs/current/static/sql-notify.html
>>>
>>>
>>> Not sure that fits my use case, thanks for your help in this.
>>>
>>
>>
>> I do not think that the NOTIFY command implemented in postgreSQL is
>> terribly likely to be  useful for your application.
>>
>> That command is useful for distribution of notifications to applications
>> that are continuously connected to the database, which is not likely true
>> for web app connections, particularly in view of your comment about
>> notifying users "when they log in next time."
>>
>> Instead, you need a table that captures a log of undelivered
>> notifications of changes to questions.  It should capture useful attributes
>> such as..
>> - Who made the change
>> - Who is to be notified
>> - The time of the change
>> - Perhaps the nature of the change, which could be pretty open ended
>> - A reference to the question, e.g. its ID
>> - Some lifecycle attribute such as "viewed-on" or "acknowledged-on"
>>
>> When a user logs in, it should be easy to query that table, providing the
>> list of unexamined updates.
>>
>
Also look at Logical Decoding for implementing Change Data Capture

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: jndi jdbc url with ssl authenticat in tomcat ... fails org.xml.sax.SAXParseException columnNumber: 79; The reference to entity "ssl" must end with the ';' delimiter

[Dave Cramer]

Pretty sure this is a tomcat error .

The connection string looks fine

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 30 July 2018 at 11:32, Didier Wiroth  wrote:

> Hello,
> I'm trying to configure a postgres jndi resource in tomcat that needs to
> connect via ssl and client certificate for authentication.
> Unfortunately ... without success.
>
> The user is: esrde_aoo and authenticates with a certificate (no password!).
>
> Here is the tomcat 8.5.31 (running on jre 1.8.0_152-b16) resource
> configuration:
>driverClassName="org.postgresql.Driver"
> name="jdbc/pgrde"
> url="jdbc:postgresql://test.localhost:5432/esrde?user=
> esrde_aoo&ssl=true&sslfactory=org.postgresql.ssl.
> NonValidatingFactory&sslcert=d:/apps/ssl/rde/esrde_aoo.crt&
> sslkey=d:/apps/ssl/rde/esrde_aoo.key"
> username="esrde_aoo"
> />
>
> I tried many many different combination ... it always fails at the second
> parameter.
> org.apache.tomcat.util.digester.Digester.fatalError Parse Fatal Error at
> line 21 column 79: The reference to entity "ssl" must end with the ';'
> delimiter.
>  org.xml.sax.SAXParseException; systemId: 
> file:/D:/apps/web-data/tam/conf/db-context-ora12.xml;
> lineNumber: 21; columnNumber: 79; The reference to entity "ssl" must end
> with the ';' delimiter.
> at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.
> createSAXParseException(ErrorHandlerWrapper.java:203)
> at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.
> fatalError(ErrorHandlerWrapper.java:177)
> at com.sun.org.apache.xerces.internal.impl.
> XMLErrorReporter.reportError(XMLErrorReporter.java:400)
> at com.sun.org.apache.xerces.internal.impl.
> XMLErrorReporter.reportError(XMLErrorReporter.java:327)
> at com.sun.org.apache.xerces.internal.impl.XMLScanner.
> reportFatalError(XMLScanner.java:1472)
> at com.sun.org.apache.xerces.internal.impl.XMLScanner.
> scanAttributeValue(XMLScanner.java:913)
> at com.sun.org.apache.xerces.internal.impl.
> XMLDocumentFragmentScannerImpl.scanAttribute(
> XMLDocumentFragmentScannerImpl.java:1548)
> at com.sun.org.apache.xerces.internal.impl.
> XMLDocumentFragmentScannerImpl.scanStartElement(
> XMLDocumentFragmentScannerImpl.java:1315)
> at com.sun.org.apache.xerces.internal.impl.
> XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(
> XMLDocumentFragmentScannerImpl.java:2784)
> at com.sun.org.apache.xerces.internal.impl.
> XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:602)
> at com.sun.org.apache.xerces.internal.impl.
> XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl
> .java:505)
> at com.sun.org.apache.xerces.internal.parsers.
> XML11Configuration.parse(XML11Configuration.java:841)
> at com.sun.org.apache.xerces.internal.parsers.
> XML11Configuration.parse(XML11Configuration.java:770)
> at com.sun.org.apache.xerces.internal.parsers.XMLParser.
> parse(XMLParser.java:141)
> at com.sun.org.apache.xerces.internal.parsers.
> AbstractSAXParser.parse(AbstractSAXParser.java:1213)
> at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$
> JAXPSAXParser.parse(SAXParserImpl.java:643)
> at org.apache.tomcat.util.digester.Digester.parse(
> Digester.java:1521)
> at org.apache.catalina.startup.ContextConfig.processContextConfig(
> ContextConfig.java:527)
> at org.apache.catalina.startup.ContextConfig.contextConfig(
> ContextConfig.java:465)
> at org.apache.catalina.startup.ContextConfig.init(
> ContextConfig.java:728)
> at org.apache.catalina.startup.ContextConfig.lifecycleEvent(
> ContextConfig.java:310)
> at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(
> LifecycleBase.java:94)
> at org.apache.catalina.util.LifecycleBase.setStateInternal(
> LifecycleBase.java:395)
> at org.apache.catalina.util.LifecycleBase.init(
> LifecycleBase.java:108)
> at org.apache.catalina.util.LifecycleBase.start(
> LifecycleBase.java:140)
> at org.apache.catalina.core.ContainerBase.addChildInternal(
> ContainerBase.java:754)
> at org.apache.catalina.core.ContainerBase.addChild(
> ContainerBase.java:730)
> at org.apache.catalina.core.StandardHost.addChild(
> StandardHost.java:734)
> at org.apache.catalina.startup.HostConfig.deployDirectory(
> HostConfig.java:1140)
> at org.apache.catalina.startup.HostConfig$DeployDirectory.
> run(HostConfig.java:1875)
> at java.util.concurrent.Executors$RunnableAdapter.

Re: very slow largeobject transfers through JDBC

[Dave Cramer]

On Fri, 31 Aug 2018 at 10:15, Mate Varga  wrote:

> I see -- we could try that, though we're mostly using an ORM (Hibernate)
> to do this. Thanks!
>
> On Fri, Aug 31, 2018 at 3:57 PM Dmitry Igrishin  wrote:
>
>> пт, 31 авг. 2018 г. в 16:35, Mate Varga :
>> >
>> > Hi,
>> >
>> > we're fetching binary data from pg_largeobject table. The data is not
>> very large, but we ended up storing it there. If I'm copying the data to a
>> file from the psql console, then it takes X time (e.g. a second), fetching
>> it through the JDBC driver takes at least 10x more. We don't see this
>> difference between JDBC and 'native' performance for anything except
>> largeobjects (and bytea columns, for the record).
>> >
>> > Does anyone have any advice about whether this can be tuned or what the
>> cause is?
>> I don't know what a reason of that, but I think it's reasonable and
>> quite simple to call lo_import()/lo_export() via JNI.
>>
>
Can't imagine that's any faster. The driver simply implements the protocol

Do you have any code to share ? Any other information ?

Is the JDBC connection significantly further away network wise ?


Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: very slow largeobject transfers through JDBC

[Dave Cramer]

On Mon, 3 Sep 2018 at 03:55, Mate Varga  wrote:

> Basically there's a class with a byte[] field, the class is mapped to
> table T and the byte field is annotated with @Lob so it goes to the
> pg_largeobject table.
>

Ah, so hibernate is in the mix. I wonder if that is causing some challenges
?


> The DB is on separate host but relatively close to the app, and I can
> reproduce the problem locally as well. One interesting bit is that turning
> of SSL between the app and PSQL speeds up things by at least 50%.
>
> Ah, one addition -- the binary objects are encrypted, so their entropy is
> very high.
>
> Any chance you could write a simple non-hibernate test code to time the
code ?

Dave Cramer

dave.cra...@crunchydata.ca
www.crunchydata.ca



> Mate
>
> On Sun, Sep 2, 2018 at 12:55 AM Dave Cramer  wrote:
>
>>
>>
>>
>> On Fri, 31 Aug 2018 at 10:15, Mate Varga  wrote:
>>
>>> I see -- we could try that, though we're mostly using an ORM (Hibernate)
>>> to do this. Thanks!
>>>
>>> On Fri, Aug 31, 2018 at 3:57 PM Dmitry Igrishin 
>>> wrote:
>>>
>>>> пт, 31 авг. 2018 г. в 16:35, Mate Varga :
>>>> >
>>>> > Hi,
>>>> >
>>>> > we're fetching binary data from pg_largeobject table. The data is not
>>>> very large, but we ended up storing it there. If I'm copying the data to a
>>>> file from the psql console, then it takes X time (e.g. a second), fetching
>>>> it through the JDBC driver takes at least 10x more. We don't see this
>>>> difference between JDBC and 'native' performance for anything except
>>>> largeobjects (and bytea columns, for the record).
>>>> >
>>>> > Does anyone have any advice about whether this can be tuned or what
>>>> the cause is?
>>>> I don't know what a reason of that, but I think it's reasonable and
>>>> quite simple to call lo_import()/lo_export() via JNI.
>>>>
>>>
>> Can't imagine that's any faster. The driver simply implements the protocol
>>
>> Do you have any code to share ? Any other information ?
>>
>> Is the JDBC connection significantly further away network wise ?
>>
>>
>> Dave Cramer
>>
>> da...@postgresintl.com
>> www.postgresintl.com
>>
>

Re: very slow largeobject transfers through JDBC

[Dave Cramer]

Not sure why reading from a socket is taking 1ms ?

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Mon, 3 Sep 2018 at 09:39, Mate Varga  wrote:

> Hi,
>
> https://imgur.com/a/ovsJPRv -- I've uploaded the profiling info (as an
> image, sorry). It seems this is a JDBC-level problem. I understand that the
> absolute timing is not meaningful at all because you don't know how large
> the resultset is, but I can tell that this is only a few thousands rows +
> few thousand largeobjects, each largeobject is around 1 kByte. (Yes I know
> this is not a proper use of LOBs -- it's a legacy db structure that's hard
> to change.)
>
> Thanks.
> Mate
>
> On Mon, Sep 3, 2018 at 11:52 AM Mate Varga  wrote:
>
>> Hey,
>>
>> we'll try to test this with pure JDBC versus hibernate. Thanks!
>>
>>
>> On Mon, Sep 3, 2018 at 11:48 AM Dave Cramer  wrote:
>>
>>>
>>>
>>> On Mon, 3 Sep 2018 at 03:55, Mate Varga  wrote:
>>>
>>>> Basically there's a class with a byte[] field, the class is mapped to
>>>> table T and the byte field is annotated with @Lob so it goes to the
>>>> pg_largeobject table.
>>>>
>>>
>>> Ah, so hibernate is in the mix. I wonder if that is causing some
>>> challenges ?
>>>
>>>
>>>> The DB is on separate host but relatively close to the app, and I can
>>>> reproduce the problem locally as well. One interesting bit is that turning
>>>> of SSL between the app and PSQL speeds up things by at least 50%.
>>>>
>>>> Ah, one addition -- the binary objects are encrypted, so their entropy
>>>> is very high.
>>>>
>>>> Any chance you could write a simple non-hibernate test code to time the
>>> code ?
>>>
>>> Dave Cramer
>>>
>>> dave.cra...@crunchydata.ca
>>> www.crunchydata.ca
>>>
>>>
>>>
>>>> Mate
>>>>
>>>> On Sun, Sep 2, 2018 at 12:55 AM Dave Cramer  wrote:
>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Fri, 31 Aug 2018 at 10:15, Mate Varga  wrote:
>>>>>
>>>>>> I see -- we could try that, though we're mostly using an ORM
>>>>>> (Hibernate) to do this. Thanks!
>>>>>>
>>>>>> On Fri, Aug 31, 2018 at 3:57 PM Dmitry Igrishin 
>>>>>> wrote:
>>>>>>
>>>>>>> пт, 31 авг. 2018 г. в 16:35, Mate Varga :
>>>>>>> >
>>>>>>> > Hi,
>>>>>>> >
>>>>>>> > we're fetching binary data from pg_largeobject table. The data is
>>>>>>> not very large, but we ended up storing it there. If I'm copying the 
>>>>>>> data
>>>>>>> to a file from the psql console, then it takes X time (e.g. a second),
>>>>>>> fetching it through the JDBC driver takes at least 10x more. We don't 
>>>>>>> see
>>>>>>> this difference between JDBC and 'native' performance for anything 
>>>>>>> except
>>>>>>> largeobjects (and bytea columns, for the record).
>>>>>>> >
>>>>>>> > Does anyone have any advice about whether this can be tuned or
>>>>>>> what the cause is?
>>>>>>> I don't know what a reason of that, but I think it's reasonable and
>>>>>>> quite simple to call lo_import()/lo_export() via JNI.
>>>>>>>
>>>>>>
>>>>> Can't imagine that's any faster. The driver simply implements the
>>>>> protocol
>>>>>
>>>>> Do you have any code to share ? Any other information ?
>>>>>
>>>>> Is the JDBC connection significantly further away network wise ?
>>>>>
>>>>>
>>>>> Dave Cramer
>>>>>
>>>>> da...@postgresintl.com
>>>>> www.postgresintl.com
>>>>>
>>>>

Re: very slow largeobject transfers through JDBC

[Dave Cramer]

the one you have highlighted ~1.69ms

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Mon, 3 Sep 2018 at 10:38, Mate Varga  wrote:

> Which frame do you refer to?
>
> On Mon, Sep 3, 2018 at 3:57 PM Dave Cramer  wrote:
>
>> Not sure why reading from a socket is taking 1ms ?
>>
>> Dave Cramer
>>
>> da...@postgresintl.com
>> www.postgresintl.com
>>
>>
>> On Mon, 3 Sep 2018 at 09:39, Mate Varga  wrote:
>>
>>> Hi,
>>>
>>> https://imgur.com/a/ovsJPRv -- I've uploaded the profiling info (as an
>>> image, sorry). It seems this is a JDBC-level problem. I understand that the
>>> absolute timing is not meaningful at all because you don't know how large
>>> the resultset is, but I can tell that this is only a few thousands rows +
>>> few thousand largeobjects, each largeobject is around 1 kByte. (Yes I know
>>> this is not a proper use of LOBs -- it's a legacy db structure that's hard
>>> to change.)
>>>
>>> Thanks.
>>> Mate
>>>
>>> On Mon, Sep 3, 2018 at 11:52 AM Mate Varga  wrote:
>>>
>>>> Hey,
>>>>
>>>> we'll try to test this with pure JDBC versus hibernate. Thanks!
>>>>
>>>>
>>>> On Mon, Sep 3, 2018 at 11:48 AM Dave Cramer  wrote:
>>>>
>>>>>
>>>>>
>>>>> On Mon, 3 Sep 2018 at 03:55, Mate Varga  wrote:
>>>>>
>>>>>> Basically there's a class with a byte[] field, the class is mapped to
>>>>>> table T and the byte field is annotated with @Lob so it goes to the
>>>>>> pg_largeobject table.
>>>>>>
>>>>>
>>>>> Ah, so hibernate is in the mix. I wonder if that is causing some
>>>>> challenges ?
>>>>>
>>>>>
>>>>>> The DB is on separate host but relatively close to the app, and I can
>>>>>> reproduce the problem locally as well. One interesting bit is that 
>>>>>> turning
>>>>>> of SSL between the app and PSQL speeds up things by at least 50%.
>>>>>>
>>>>>> Ah, one addition -- the binary objects are encrypted, so their
>>>>>> entropy is very high.
>>>>>>
>>>>>> Any chance you could write a simple non-hibernate test code to time
>>>>> the code ?
>>>>>
>>>>> Dave Cramer
>>>>>
>>>>> dave.cra...@crunchydata.ca
>>>>> www.crunchydata.ca
>>>>>
>>>>>
>>>>>
>>>>>> Mate
>>>>>>
>>>>>> On Sun, Sep 2, 2018 at 12:55 AM Dave Cramer  wrote:
>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Fri, 31 Aug 2018 at 10:15, Mate Varga  wrote:
>>>>>>>
>>>>>>>> I see -- we could try that, though we're mostly using an ORM
>>>>>>>> (Hibernate) to do this. Thanks!
>>>>>>>>
>>>>>>>> On Fri, Aug 31, 2018 at 3:57 PM Dmitry Igrishin 
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> пт, 31 авг. 2018 г. в 16:35, Mate Varga :
>>>>>>>>> >
>>>>>>>>> > Hi,
>>>>>>>>> >
>>>>>>>>> > we're fetching binary data from pg_largeobject table. The data
>>>>>>>>> is not very large, but we ended up storing it there. If I'm copying 
>>>>>>>>> the
>>>>>>>>> data to a file from the psql console, then it takes X time (e.g. a 
>>>>>>>>> second),
>>>>>>>>> fetching it through the JDBC driver takes at least 10x more. We don't 
>>>>>>>>> see
>>>>>>>>> this difference between JDBC and 'native' performance for anything 
>>>>>>>>> except
>>>>>>>>> largeobjects (and bytea columns, for the record).
>>>>>>>>> >
>>>>>>>>> > Does anyone have any advice about whether this can be tuned or
>>>>>>>>> what the cause is?
>>>>>>>>> I don't know what a reason of that, but I think it's reasonable and
>>>>>>>>> quite simple to call lo_import()/lo_export() via JNI.
>>>>>>>>>
>>>>>>>>
>>>>>>> Can't imagine that's any faster. The driver simply implements the
>>>>>>> protocol
>>>>>>>
>>>>>>> Do you have any code to share ? Any other information ?
>>>>>>>
>>>>>>> Is the JDBC connection significantly further away network wise ?
>>>>>>>
>>>>>>>
>>>>>>> Dave Cramer
>>>>>>>
>>>>>>> da...@postgresintl.com
>>>>>>> www.postgresintl.com
>>>>>>>
>>>>>>

Re: very slow largeobject transfers through JDBC

[Dave Cramer]

On Mon, 3 Sep 2018 at 10:48, Mate Varga  wrote:

> That's 1690 msec (1.69 seconds, and that is how long it takes to fetch 20k
> (small-ish) rows without LOBs (LOBs are a few lines below on the screenshot)
>

that sound high as well!

Something isn't adding up..


Dave Cramer

da...@postgresintl.com
www.postgresintl.com



>
> On Mon, Sep 3, 2018 at 4:40 PM Dave Cramer  wrote:
>
>> the one you have highlighted ~1.69ms
>>
>> Dave Cramer
>>
>> da...@postgresintl.com
>> www.postgresintl.com
>>
>>
>> On Mon, 3 Sep 2018 at 10:38, Mate Varga  wrote:
>>
>>> Which frame do you refer to?
>>>
>>> On Mon, Sep 3, 2018 at 3:57 PM Dave Cramer  wrote:
>>>
>>>> Not sure why reading from a socket is taking 1ms ?
>>>>
>>>> Dave Cramer
>>>>
>>>> da...@postgresintl.com
>>>> www.postgresintl.com
>>>>
>>>>
>>>> On Mon, 3 Sep 2018 at 09:39, Mate Varga  wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> https://imgur.com/a/ovsJPRv -- I've uploaded the profiling info (as
>>>>> an image, sorry). It seems this is a JDBC-level problem. I understand that
>>>>> the absolute timing is not meaningful at all because you don't know how
>>>>> large the resultset is, but I can tell that this is only a few thousands
>>>>> rows + few thousand largeobjects, each largeobject is around 1 kByte. (Yes
>>>>> I know this is not a proper use of LOBs -- it's a legacy db structure
>>>>> that's hard to change.)
>>>>>
>>>>> Thanks.
>>>>> Mate
>>>>>
>>>>> On Mon, Sep 3, 2018 at 11:52 AM Mate Varga  wrote:
>>>>>
>>>>>> Hey,
>>>>>>
>>>>>> we'll try to test this with pure JDBC versus hibernate. Thanks!
>>>>>>
>>>>>>
>>>>>> On Mon, Sep 3, 2018 at 11:48 AM Dave Cramer  wrote:
>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Mon, 3 Sep 2018 at 03:55, Mate Varga  wrote:
>>>>>>>
>>>>>>>> Basically there's a class with a byte[] field, the class is mapped
>>>>>>>> to table T and the byte field is annotated with @Lob so it goes to the
>>>>>>>> pg_largeobject table.
>>>>>>>>
>>>>>>>
>>>>>>> Ah, so hibernate is in the mix. I wonder if that is causing some
>>>>>>> challenges ?
>>>>>>>
>>>>>>>
>>>>>>>> The DB is on separate host but relatively close to the app, and I
>>>>>>>> can reproduce the problem locally as well. One interesting bit is that
>>>>>>>> turning of SSL between the app and PSQL speeds up things by at least 
>>>>>>>> 50%.
>>>>>>>>
>>>>>>>> Ah, one addition -- the binary objects are encrypted, so their
>>>>>>>> entropy is very high.
>>>>>>>>
>>>>>>>> Any chance you could write a simple non-hibernate test code to time
>>>>>>> the code ?
>>>>>>>
>>>>>>> Dave Cramer
>>>>>>>
>>>>>>> dave.cra...@crunchydata.ca
>>>>>>> www.crunchydata.ca
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>> Mate
>>>>>>>>
>>>>>>>> On Sun, Sep 2, 2018 at 12:55 AM Dave Cramer 
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Fri, 31 Aug 2018 at 10:15, Mate Varga  wrote:
>>>>>>>>>
>>>>>>>>>> I see -- we could try that, though we're mostly using an ORM
>>>>>>>>>> (Hibernate) to do this. Thanks!
>>>>>>>>>>
>>>>>>>>>> On Fri, Aug 31, 2018 at 3:57 PM Dmitry Igrishin <
>>>>>>>>>> dmit...@gmail.com> wrote:
>>>>>>>>>>
>>>>>>>>>>> пт, 31 авг. 2018 г. в 16:35, Mate Varga :
>>>>>>>>>>> >
>>>>>>>>>>> > Hi,
>>>>>>>>>>> >
>>>>>>>>>>> > we're fetching binary data from pg_largeobject table. The data
>>>>>>>>>>> is not very large, but we ended up storing it there. If I'm copying 
>>>>>>>>>>> the
>>>>>>>>>>> data to a file from the psql console, then it takes X time (e.g. a 
>>>>>>>>>>> second),
>>>>>>>>>>> fetching it through the JDBC driver takes at least 10x more. We 
>>>>>>>>>>> don't see
>>>>>>>>>>> this difference between JDBC and 'native' performance for anything 
>>>>>>>>>>> except
>>>>>>>>>>> largeobjects (and bytea columns, for the record).
>>>>>>>>>>> >
>>>>>>>>>>> > Does anyone have any advice about whether this can be tuned or
>>>>>>>>>>> what the cause is?
>>>>>>>>>>> I don't know what a reason of that, but I think it's reasonable
>>>>>>>>>>> and
>>>>>>>>>>> quite simple to call lo_import()/lo_export() via JNI.
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>> Can't imagine that's any faster. The driver simply implements the
>>>>>>>>> protocol
>>>>>>>>>
>>>>>>>>> Do you have any code to share ? Any other information ?
>>>>>>>>>
>>>>>>>>> Is the JDBC connection significantly further away network wise ?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Dave Cramer
>>>>>>>>>
>>>>>>>>> da...@postgresintl.com
>>>>>>>>> www.postgresintl.com
>>>>>>>>>
>>>>>>>>

Re: very slow largeobject transfers through JDBC

[Dave Cramer]

On Mon, 3 Sep 2018 at 13:00, Mate Varga  wrote:

> More precisely: when fetching 10k rows, JDBC driver just does a large
> bunch of socket reads. With lobs, it's ping-pong: one read, one write per
> lob...
>
>
Ok, this is making more sense. In theory we could fetch them all but since
they are LOB's we could run out of memory.

Not sure what to tell you at this point. I'd entertain a PR if you were
motivated.

Dave Cramer

da...@postgresintl.com
www.postgresintl.com



>
> On Mon, Sep 3, 2018 at 6:54 PM Mate Varga  wrote:
>
>> So I have detailed profiling results now. Basically it takes very long
>> that for each blob, the JDBC driver reads from the socket then it creates
>> the byte array on the Java side. Then it reads the next blob, etc. I guess
>> this takes many network roundtrips.
>>
>> On Mon, Sep 3, 2018 at 5:58 PM Dave Cramer  wrote:
>>
>>>
>>> On Mon, 3 Sep 2018 at 10:48, Mate Varga  wrote:
>>>
>>>> That's 1690 msec (1.69 seconds, and that is how long it takes to fetch
>>>> 20k (small-ish) rows without LOBs (LOBs are a few lines below on the
>>>> screenshot)
>>>>
>>>
>>> that sound high as well!
>>>
>>> Something isn't adding up..
>>>
>>>
>>> Dave Cramer
>>>
>>> da...@postgresintl.com
>>> www.postgresintl.com
>>>
>>>
>>>
>>>>
>>>> On Mon, Sep 3, 2018 at 4:40 PM Dave Cramer  wrote:
>>>>
>>>>> the one you have highlighted ~1.69ms
>>>>>
>>>>> Dave Cramer
>>>>>
>>>>> da...@postgresintl.com
>>>>> www.postgresintl.com
>>>>>
>>>>>
>>>>> On Mon, 3 Sep 2018 at 10:38, Mate Varga  wrote:
>>>>>
>>>>>> Which frame do you refer to?
>>>>>>
>>>>>> On Mon, Sep 3, 2018 at 3:57 PM Dave Cramer  wrote:
>>>>>>
>>>>>>> Not sure why reading from a socket is taking 1ms ?
>>>>>>>
>>>>>>> Dave Cramer
>>>>>>>
>>>>>>> da...@postgresintl.com
>>>>>>> www.postgresintl.com
>>>>>>>
>>>>>>>
>>>>>>> On Mon, 3 Sep 2018 at 09:39, Mate Varga  wrote:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> https://imgur.com/a/ovsJPRv -- I've uploaded the profiling info
>>>>>>>> (as an image, sorry). It seems this is a JDBC-level problem. I 
>>>>>>>> understand
>>>>>>>> that the absolute timing is not meaningful at all because you don't 
>>>>>>>> know
>>>>>>>> how large the resultset is, but I can tell that this is only a few
>>>>>>>> thousands rows + few thousand largeobjects, each largeobject is around 
>>>>>>>> 1
>>>>>>>> kByte. (Yes I know this is not a proper use of LOBs -- it's a legacy db
>>>>>>>> structure that's hard to change.)
>>>>>>>>
>>>>>>>> Thanks.
>>>>>>>> Mate
>>>>>>>>
>>>>>>>> On Mon, Sep 3, 2018 at 11:52 AM Mate Varga  wrote:
>>>>>>>>
>>>>>>>>> Hey,
>>>>>>>>>
>>>>>>>>> we'll try to test this with pure JDBC versus hibernate. Thanks!
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Mon, Sep 3, 2018 at 11:48 AM Dave Cramer 
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Mon, 3 Sep 2018 at 03:55, Mate Varga  wrote:
>>>>>>>>>>
>>>>>>>>>>> Basically there's a class with a byte[] field, the class is
>>>>>>>>>>> mapped to table T and the byte field is annotated with @Lob so it 
>>>>>>>>>>> goes to
>>>>>>>>>>> the pg_largeobject table.
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Ah, so hibernate is in the mix. I wonder if that is causing some
>>>>>>>>>> challenges ?
>>>>>

Re: very slow largeobject transfers through JDBC

[Dave Cramer]

Hi

Can you be more explicit how you fixed the problem ?

Thanks
Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Thu, 6 Sep 2018 at 03:46, Mate Varga  wrote:

> After inlining the data, performance issues have been solved. Thanks for
> the help.
>
> On Mon, Sep 3, 2018 at 9:57 PM Mate Varga  wrote:
>
>> Thanks,
>> 1) we'll try to move stuff out from LOBs
>> 2) we might raise a PR for the JDBC driver
>>
>> Mate
>>
>> On Mon, 3 Sep 2018, 19:35 Dave Cramer,  wrote:
>>
>>>
>>>
>>> On Mon, 3 Sep 2018 at 13:00, Mate Varga  wrote:
>>>
>>>> More precisely: when fetching 10k rows, JDBC driver just does a large
>>>> bunch of socket reads. With lobs, it's ping-pong: one read, one write per
>>>> lob...
>>>>
>>>>
>>> Ok, this is making more sense. In theory we could fetch them all but
>>> since they are LOB's we could run out of memory.
>>>
>>> Not sure what to tell you at this point. I'd entertain a PR if you were
>>> motivated.
>>>
>>> Dave Cramer
>>>
>>> da...@postgresintl.com
>>> www.postgresintl.com
>>>
>>>
>>>
>>>>
>>>> On Mon, Sep 3, 2018 at 6:54 PM Mate Varga  wrote:
>>>>
>>>>> So I have detailed profiling results now. Basically it takes very long
>>>>> that for each blob, the JDBC driver reads from the socket then it creates
>>>>> the byte array on the Java side. Then it reads the next blob, etc. I guess
>>>>> this takes many network roundtrips.
>>>>>
>>>>> On Mon, Sep 3, 2018 at 5:58 PM Dave Cramer  wrote:
>>>>>
>>>>>>
>>>>>> On Mon, 3 Sep 2018 at 10:48, Mate Varga  wrote:
>>>>>>
>>>>>>> That's 1690 msec (1.69 seconds, and that is how long it takes to
>>>>>>> fetch 20k (small-ish) rows without LOBs (LOBs are a few lines below on 
>>>>>>> the
>>>>>>> screenshot)
>>>>>>>
>>>>>>
>>>>>> that sound high as well!
>>>>>>
>>>>>> Something isn't adding up..
>>>>>>
>>>>>>
>>>>>> Dave Cramer
>>>>>>
>>>>>> da...@postgresintl.com
>>>>>> www.postgresintl.com
>>>>>>
>>>>>>
>>>>>>
>>>>>>>
>>>>>>> On Mon, Sep 3, 2018 at 4:40 PM Dave Cramer  wrote:
>>>>>>>
>>>>>>>> the one you have highlighted ~1.69ms
>>>>>>>>
>>>>>>>> Dave Cramer
>>>>>>>>
>>>>>>>> da...@postgresintl.com
>>>>>>>> www.postgresintl.com
>>>>>>>>
>>>>>>>>
>>>>>>>> On Mon, 3 Sep 2018 at 10:38, Mate Varga  wrote:
>>>>>>>>
>>>>>>>>> Which frame do you refer to?
>>>>>>>>>
>>>>>>>>> On Mon, Sep 3, 2018 at 3:57 PM Dave Cramer 
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> Not sure why reading from a socket is taking 1ms ?
>>>>>>>>>>
>>>>>>>>>> Dave Cramer
>>>>>>>>>>
>>>>>>>>>> da...@postgresintl.com
>>>>>>>>>> www.postgresintl.com
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Mon, 3 Sep 2018 at 09:39, Mate Varga  wrote:
>>>>>>>>>>
>>>>>>>>>>> Hi,
>>>>>>>>>>>
>>>>>>>>>>> https://imgur.com/a/ovsJPRv -- I've uploaded the profiling info
>>>>>>>>>>> (as an image, sorry). It seems this is a JDBC-level problem. I 
>>>>>>>>>>> understand
>>>>>>>>>>> that the absolute timing is not meaningful at all because you don't 
>>>>>>>>>>> know
>>>>>>>>>>> how large the resultset is, but I can tell that this is only a few
>>>>>>>>>>> thousands rows + few thousand largeobjects, each largeobject

Re: very slow largeobject transfers through JDBC

[Dave Cramer]

Hi Mate,

Thanks for the detailed response. This will help others in the same
situation

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Thu, 6 Sep 2018 at 05:03, Mate Varga  wrote:

> Hi,
>
> summarizing:
> we had a table that had an OID column, referencing an object in
> pg_largeobject. This was mapped to a (Java) entity with a byte array field,
> annotated with @Lob. The problem was that we were fetching thousands of
> these entities in one go, and LOB fetching is not batched by Hibernate/JDBC
> (so each row is fetched separately). Because we were abusing LOBs (they
> were small, often less than 10 kB), we have chosen to move the binary blobs
> from the LO table to a simple bytea column. So the entity that had a byte
> array field mapped to an OID column now has a byte array field mapped to a
> bytea column, and we have manually moved data from the LO table to the
> bytea column. Now Hibernate/JDBC fetches all the content we need in
> batches. Random benchmark: fetching 20k rows used to take 7 seconds (250
> msec query execution time, 6.7 sec for transfer) and now it takes 1.5
> seconds (250 msec query + 1.3 sec transfer).
>
> Regards,
> Mate
>
> On Thu, Sep 6, 2018 at 10:56 AM Dave Cramer  wrote:
>
>> Hi
>>
>> Can you be more explicit how you fixed the problem ?
>>
>> Thanks
>> Dave Cramer
>>
>> da...@postgresintl.com
>> www.postgresintl.com
>>
>>
>> On Thu, 6 Sep 2018 at 03:46, Mate Varga  wrote:
>>
>>> After inlining the data, performance issues have been solved. Thanks for
>>> the help.
>>>
>>> On Mon, Sep 3, 2018 at 9:57 PM Mate Varga  wrote:
>>>
>>>> Thanks,
>>>> 1) we'll try to move stuff out from LOBs
>>>> 2) we might raise a PR for the JDBC driver
>>>>
>>>> Mate
>>>>
>>>> On Mon, 3 Sep 2018, 19:35 Dave Cramer,  wrote:
>>>>
>>>>>
>>>>>
>>>>> On Mon, 3 Sep 2018 at 13:00, Mate Varga  wrote:
>>>>>
>>>>>> More precisely: when fetching 10k rows, JDBC driver just does a large
>>>>>> bunch of socket reads. With lobs, it's ping-pong: one read, one write per
>>>>>> lob...
>>>>>>
>>>>>>
>>>>> Ok, this is making more sense. In theory we could fetch them all but
>>>>> since they are LOB's we could run out of memory.
>>>>>
>>>>> Not sure what to tell you at this point. I'd entertain a PR if you
>>>>> were motivated.
>>>>>
>>>>> Dave Cramer
>>>>>
>>>>> da...@postgresintl.com
>>>>> www.postgresintl.com
>>>>>
>>>>>
>>>>>
>>>>>>
>>>>>> On Mon, Sep 3, 2018 at 6:54 PM Mate Varga  wrote:
>>>>>>
>>>>>>> So I have detailed profiling results now. Basically it takes very
>>>>>>> long that for each blob, the JDBC driver reads from the socket then it
>>>>>>> creates the byte array on the Java side. Then it reads the next blob, 
>>>>>>> etc.
>>>>>>> I guess this takes many network roundtrips.
>>>>>>>
>>>>>>> On Mon, Sep 3, 2018 at 5:58 PM Dave Cramer  wrote:
>>>>>>>
>>>>>>>>
>>>>>>>> On Mon, 3 Sep 2018 at 10:48, Mate Varga  wrote:
>>>>>>>>
>>>>>>>>> That's 1690 msec (1.69 seconds, and that is how long it takes to
>>>>>>>>> fetch 20k (small-ish) rows without LOBs (LOBs are a few lines below 
>>>>>>>>> on the
>>>>>>>>> screenshot)
>>>>>>>>>
>>>>>>>>
>>>>>>>> that sound high as well!
>>>>>>>>
>>>>>>>> Something isn't adding up..
>>>>>>>>
>>>>>>>>
>>>>>>>> Dave Cramer
>>>>>>>>
>>>>>>>> da...@postgresintl.com
>>>>>>>> www.postgresintl.com
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Mon, Sep 3, 2018 at 4:40 PM Dave Cramer 
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> the one you have highlighted ~1.69m

Re: Automatically parsing in-line composite types

[Dave Cramer]

On Wed, 23 Oct 2019 at 15:50, Mitar  wrote:

> Hi!
>
> Bump my previous question. I find it surprising that it seems this
> information is not possible to be reconstructed by the client, when
> the server has to have it internally. Is this a new feature request or
> am I missing something?
>
> > I am trying to understand how could I automatically parse an in-line
> > composite type. By in-line composite type I mean a type corresponding
> > to ROW. For example, in the following query:
> >
> > SELECT _id, body, (SELECT array_agg(ROW(comments._id, comments.body))
> > FROM comments WHERE comments.post_id=posts._id) AS comments FROM posts
> >
> > It looks like I can figure out that "comments" is an array of records.
> > But then there is no way really to understand how to parse those
> > records? So what are types of fields in the record?
> >
> > I start the parsing process by looking at types returned in
> > RowDescription message and then reading descriptions in pg_type table.
> >
> > Is there some other way to get full typing information of the result I
> > am assuming is available to PostreSQL internally?
>
>
>
Reading the RowDescription is the only way I am aware of.


Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: SV: Using Postgres jdbc driver with Oracle SQL Developer

[Dave Cramer]

On Mon, 6 Jul 2020 at 09:33, Tom Lane  wrote:

> Niels Jespersen  writes:
> > Sorry, "way to provide a default username" should have been "way to
> provide a default databasename"
>
> Not sure, but you'd be more likely to find a knowledgeable answer
> on the pgsql-jdbc list.
>
>
Same way, in the properties object. But I'm totally unfamiliar with Oracle
SQL Developer. Can you provide properties at all ?

Dave

Re: Christopher Browne

[Dave Cramer]

On Wed., Nov. 4, 2020, 8:10 p.m. raf,  wrote:

> On Wed, Nov 04, 2020 at 06:29:18PM -0500, Steve Singer 
> wrote:
>
> >
> > It is with much sadness that I am letting the community know that Chris
> > Browne passed away recently.
> >
> > Chris had been a long time community member and was active on various
> > Postgresql mailing lists.  He was a member of the funds committee, the
> PgCon
> > program committee and served on the board of the PostgreSQL Community
> > Association of Canada. Chris was a maintainer of the Slony replication
> > system and worked on various PG related tools.
> >
> > I worked with Chris for over 10 years and anytime someone had a problem
> he
> > would jump at the chance to help and propose solutions. He always had
> time
> > to listen to your problem and offer ideas or explain how something
> worked.
> >
> > I will miss Chris
> >
> > Steve
>
> He sounds exactly like the kind of person you want to have around.
>
>
Very sorry to hear about Chris. I considered him a friend. He will be
missed.

Dave


>

Re: "Server versions prior to 8.0 do not support savepoints" when using JDBC ...

[Dave Cramer]

On Sun, 29 Nov 2020 at 14:09, Martin Goodson 
wrote:

> On 29/11/2020 18:46, David G. Johnston wrote:
> > On Sun, Nov 29, 2020 at 11:06 AM Martin Goodson  >
> > wrote:
> >
> >> Hello.
> >>
> >> I wonder if anyone can assist with this?
> >>
> >> Some of my developers are reporting that they are getting the following
> >> message when attempting to connect to the database using jdbc:
> >>
> >> Caused by: org.postgresql.util.PSQLException: Server versions prior to
> >> 8.0 do not support savepoints
> >>
> >> at
> >>
> >>
> >>
> org.postgresql.jdbc3.AbstractJdbc3Connection.setSavepoint(AbstractJdbc3Connection.java:95)
> >>
> >> Database version is 10.5 running under RHEL 7.4. The postgreSQL jdbc
> >> driver version they're employing is postgresl-42.2.9.
> >>
> >
> > My best guess is that they are not actually running 42.2.9, they are
> > running an older version that was released before PostgreSQL v10 came out
> > and the version detection code doesn't recognize the new version format
> > since 10.
> >
> > I didn't dig into this too deeply but I did download the zip for 42.2.9
> and
> > those classes don't even exist at that point since the build was re-done
> > using pre-processors.
> >
> > David J.
> >
> Oh. That's very interesting. Thank you, David. I will chase up with the
> developers tomorrow to confirm with them the version of the postgres
> jdbc driver they are using.
>
> I'm no coder, it didn't occur to me to actually check with the source.
> Nice one :)
>
> Many thanks.
>
> Regards,
>
> Martin.
>
> --
> Martin Goodson
>
> Whatever you've got planned, forget it. I'm the Doctor.
> I'm 904 years old. I'm from the planet Gallifrey in the
> constellation of Kasterborous. I am The Oncoming Storm,
> the Bringer of Darkness and you are ... basically just a
> rabbit, aren't you? OK, carry on, just a general ...
> warning.
>
>
I'm guessing this is now resolved ?


Dave Cramer
www.postgres.rocks

Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made?

[Dave Cramer]

On Sun, 6 Dec 2020 at 15:52, electrotype  wrote:

> Hi,
>
> Using JDBC, I batch insert multiple rows (*"executeBatch()*"). I then use
> *'**getGeneratedKeys("id")*' to get the generated ids ("id" is a "*SERIAL
> PRIMARY KEY*" column).
>
> My question: does the PostgreSQL JDBC driver *guarantees *that the order
> of the returned generated ids will be the same as the rows to insert have
> been specified, using "*addBatch()*"?
>
>
> The best "answer" to that question I have found is
> https://stackoverflow.com/a/16119489/843699 , but it is not 100% clear.
>
> Would it be possible to have an official answer on this?
>

I can't see how they could possibly be out of order.


Dave Cramer
www.postgres.rocks

>
> Thanks in advance!
>
>
>

Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made?

[Dave Cramer]

So I'm curious. Why does order matter ?

Dave Cramer
www.postgres.rocks


On Wed, 9 Dec 2020 at 03:15, electrotype  wrote:

> I can't see how they could possibly be out of order.
>
> Thanks, that what I think too. But, to be honest, I'd really like to see
> this written in some documentation! In some cases, this small detail can be
> quite important.
>
>

Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made?

[Dave Cramer]

On Wed, 9 Dec 2020 at 10:21, electrotype  wrote:

> So I'm curious. Why does order matter ?
>
> Dave Cramer
> www.postgres.rocks
>
>
> When you have to save multiple new entities with subentities.
>
> You first save all the parent entities in a single SQL batch insert, you
> get the generated ids, then insert all the subentities in another single
> SQL batch insert. To know which "parent id" to use for a given subentity of
> the second query, you need a way to associate a generated id with the
> correct parent entity. The order of the parents in their batch, and the
> order of the generated ids, is the only straighforward way.
>
> I know all this could be made into a single SQL query, without having to
> associate the generated ids to the parents manually. But sometimes you have
> to fight *really *hard agains your framework or JDBC itself to write such
> more complex query, where two batch inserts are very natural.
>


Fair enough, however the spec does not say anything about the order. In
fact it doesn't even say which keys will be returned.

I don't think we can make any guarantees here.

Dave

Re: JDBC driver - is "getGeneratedKeys()" guaranteed to return the ids in the same order a batch insert was made?

[Dave Cramer]

On Thu, 10 Dec 2020 at 19:37, David G. Johnston 
wrote:

> On Wed, Dec 9, 2020 at 1:31 PM electrotype  wrote:
>
>> Agreed.
>>
>>
>> However, this isn't really the purview of JDBC - I'm doubting it does
>> anything that would cause the order to be different than what is received,
>> and the batch items are sent and results processed sequentially.
>>
>> The main question is whether any batch items are inserting multiple
>> records themselves - i.e., RETURNING * is producing multiple results.
>> Whatever order RETURNING * produces is what the driver will capture - but
>> it isn't responsible for guaranteeing that the order of multiple inserted
>> records going in matches what comes out.  PostgreSQL needs to make that
>> claim.  I don't see where it does (i've sent an email to see if adding such
>> a claim to the documentation is proper).  Done manually one can always do
>> "WITH insert returning SELECT ORDER BY", but it doesn't seem workable for
>> the driver to try and do that when adding the returning clause, which I
>> presume is what is in scope here.
>>
>> David J.
>>
>> Thank you, it's appreciated! I'm sure this clarification would help other
>> developers too.
>>
>
> My take is that there is presently no guarantee, and that with current
> efforts to add parallelism it is quite probable that observation of such
> non-orderedness is simply a matter of time.  With batching it seems best to
> combine its use with single inserts in order to avoid this problem.
>
> David J.
>

I'd have to agree.

Dave Cramer
www.postgres.rocks

Re: Edb Jdbc Ssl Connection

[Dave Cramer]

Please follow the instructions here

Configuring the Client (postgresql.org)
<https://jdbc.postgresql.org/documentation/head/ssl-client.html>
<https://jdbc.postgresql.org/documentation/head/ssl-client.html>
Dave Cramer
www.postgres.rocks


On Mon, 1 Feb 2021 at 01:44,  wrote:

>
> 42.2.9 and java 8 u162 have worked.
>
>
>
>
> 31.01.2021, 08:51, "ayagmu...@yandex.com" :
>
> I want to establish simple jdbc ssl connection using edb as 11 and java 8
> u271 with postgresql jdbc driver 42.2.9 (I have also tried these versions:
> 42.2.18 and 42.2.8).
> I followed the instructions from the following link.
>
> https://www.enterprisedb.com/postgres-tutorials/how-enable-ssl-authentication-edb-postgres-advanced-server
>
> I tested ssl connection successfully using "psql". But I cannot establish
> a jdbc connection. I got "FATAL: connection requires a valid client
> certificate" error.
>
> After a little googling, I want to use a different url than specified here
> just to consume all the possibilities.  After modifiying the url with
> "sslcert=./test/client.crt sslkey=./test/client.key
> sslrootcert=./test/server.crt" I got "SSLSocket duplex close failed".
>
> Could you please help me with this problem?
>
> Thanks for you in advance.
>
>
>
>

Re: SV: Npgsql and the Connection Service File

[Dave Cramer]

On Mon, 1 Feb 2021 at 04:51, Niels Jespersen  wrote:

> >On Sat, 2021-01-30 at 15:56 +, Niels Jespersen wrote:
> >> It would be nice if Npgsql (and jdbc and others) emulated the libpq
> behaviour.
> >>  Because in my mind, abstracting hostname, portnumber and databasename
> >> away is a really useful feature.
> >>
> >> How do others manage this?
> >
> >Either they don't or they write their own class that does that.
> >
> >Perhaps you should contact Npgsql and suggest such a feature.
> >
> Yes, thank you.
> I did just that. It turns out there is already an existing pull request
> with exactly that feature. I hope for it to be included in a release soon.
> Regards Niels Jespersen
>
>
Ya, I intend to push one in sooner than later.

Dave


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

Re: Insertion time is very high for inserting data in postgres

[Dave Cramer]

On Wed, 10 Feb 2021 at 06:11, Niels Jespersen  wrote:

> >Fra: prachi surangalikar 
>
> >
>
> >Hello Team,
>
> >Greetings!
>
> >
>
> >We are using Postgres 12.2.1 for fetching per minute data for about 25
> machines but running parallely via a single thread in python.
>
> >But suddenly the insertion time has increased to a very high level, about
> 30 second for one machine.
>
> >We are in so much problem as the data fetching is becoming slow.
>


Before anyone can help you, you will have to provide much more information.

Schema, data that you are inserting, size of the machine, configuration
settings. etc.

Dave

> >
>
> >if anyone could help us to solve this problem it would be of great help
> to us.
>
>
>
> Get your data into a Text.IO memory structure and then use copy
> https://www.psycopg.org/docs/usage.html#using-copy-to-and-copy-from
>
>
>
> This is THE way of high-performant inserts using Postgres.
>
>
>
> Regards Niels Jespersen
>

Re: Problem with trigger function

[Dave Cramer]

FWIW, messing with serial numbers like this is pretty risky.Sequences have
transactional semantics for a reason.

Dave Cramer
www.postgres.rocks


On Thu, 11 Feb 2021 at 14:57, Steve Baldwin  wrote:

> Try ... EXECUTE PROCEDURE customer_num_informix()
>
> Steve
>
> On Fri, Feb 12, 2021 at 6:47 AM James B. Byrne 
> wrote:
>
>> I am trying to implement a trigger in a PostgreSQL-9.6.17 database:
>>
>> CREATE OR REPLACE FUNCTION customer_num_informix()
>>   RETURNS trigger AS $$
>> BEGIN
>>   -- An Aubit/Informix 4GL INSERT statement passes the value 0 to Informix
>>   -- DBMS for columns that have the SERIAL data type.  Informix will then
>>   -- use the incremented serial number in place of 0. PostgreSQL instead
>>   -- will simply take the value 0 and replace the incremented serial
>> number.
>>   -- This trigger function emulates the Informix DBMS behaviour.
>>   --
>>   -- The NEW variable contains the data for the row to be INSERTed or
>>   -- UPDATEd. It, and its complement OLD used for UPDATE and DELETE,
>>   -- are automatically created and populated by PostgreSQL whenever
>>   -- a data-change function is called.
>>   --
>>   IF NEW.customer_num = 0 THEN
>> SELECT nextval('customer_customer_num_seq') INTO
>> NEW.customer_customer_num;
>>   ELSE
>> IF NEW.customer_customer_num > 0 THEN
>>   PERFORM setval('customer_customer_num_seq',
>> NEW.customer_customer_num);
>> END IF;
>>   END IF;
>>   RETURN NEW;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> ALTER FUNCTION public.customer_num_informix() OWNER TO hll_4gl_testing;
>>
>> CREATE TRIGGER customer_num_serial
>>   BEFORE INSERT ON customer
>> FOR EACH ROW EXECUTE customer_num_informix();
>>
>> The problem is that I am getting a syntax error on the CREATE TRIGGER
>> statement:
>>
>> ERROR:  syntax error at or near "customer_num_informix"
>> LINE 3: FOR EACH ROW EXECUTE customer_num_informix();
>>
>> I do not see what the error is. What is wrong with the syntax I used?
>>
>> --
>> ***  e-Mail is NOT a SECURE channel  ***
>> Do NOT transmit sensitive data via e-Mail
>>Unencrypted messages have no legal claim to privacy
>>  Do NOT open attachments nor follow links sent by e-Mail
>>
>> James B. Byrnemailto:byrn...@harte-lyne.ca
>> Harte & Lyne Limited  http://www.harte-lyne.ca
>> 9 Brockley Drive  vox: +1 905 561 1241
>> Hamilton, Ontario fax: +1 905 561 0757
>> Canada  L8E 3C3
>>
>>
>>
>>

Re: libpq to JDBC adapter

[Dave Cramer]

Looks very interesting,

Cheers,

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Wed, 14 Nov 2018 at 14:57, Konstantin Knizhnik 
wrote:

> If somebody is interested in connection to various JDBC-compatible
> databases through postgres_fdw,
> please look at my pq2jdbc project: https://github.com/postgrespro/pq2jdbc
> Details of the project are in README file.
>
> If somebody can find some other use cases for libpq to JDBC adapter,
> please let me know!
>
> --
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>

Re: loading jdbc Driver in servlet

[Dave Cramer]

Strange, I wouldn't think so, but then I haven't used a raw servlet for so
long I have no idea.


Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Fri, 14 Dec 2018 at 13:29, Rob Sargent  wrote:

> Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9
>
> It appears to me that I need to make the call
> "Class.forName("org.postgresql.Driver)" when the entry is in a servlet.  Is
> this expected, within a servlet, or is this just *post hoc ergo propter
> hoc *at it finest and I changed something else (wittingly or not).  Same
> code outside of servlet does not need the forced loading of the class and
> the manual claims it's not need after java 1.6
>

Re: loading jdbc Driver in servlet

[Dave Cramer]

My guess is it has something to do with your servlet classpath loader.
Which servlet engine are you using ?
Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Fri, 14 Dec 2018 at 16:04, Rob Sargent  wrote:

>
>
> On Dec 14, 2018, at 2:02 PM, Rob Sargent  wrote:
>
>
>
> On Dec 14, 2018, at 1:30 PM, Dave Cramer  wrote:
>
> Strange, I wouldn't think so, but then I haven't used a raw servlet for so
> long I have no idea.
>
>
> Dave Cramer
>
> da...@postgresintl.com
> www.postgresintl.com
>
>
> On Fri, 14 Dec 2018 at 13:29, Rob Sargent  wrote:
>
>> Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9
>>
>> It appears to me that I need to make the call
>> "Class.forName("org.postgresql.Driver)" when the entry is in a servlet.  Is
>> this expected, within a servlet, or is this just *post hoc ergo propter
>> hoc *at it finest and I changed something else (wittingly or not).  Same
>> code outside of servlet does not need the forced loading of the class and
>> the manual claims it's not need after java 1.6
>>
> I too am suspicious.  But the vagaries of javax are daunting.
> But if I comment it out, as just now, “No suitable driver found...”.  I
> was days playing with configuration and such think that this very specific
> error message was telling me my CLASSPATH was wrong.
>
> I wonder if I have an old javax installation (which I put in place just
> recently).
>
>
> I’m using javax.servlet-api-3.1.0.jar
>
>
>
>
>

Re: loading jdbc Driver in servlet

[Dave Cramer]

So you are starting up tomcat yourself ? Perhaps that is the difference ?
I have no idea what the tomcat wrapper does, but I'd be curious if the same
thing happens when stared normally

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Sun, 16 Dec 2018 at 12:20, Rob Sargent  wrote:

> Tomcat version 9. Embedded in my main()
>
> On Dec 16, 2018, at 9:30 AM, Dave Cramer  wrote:
>
> My guess is it has something to do with your servlet classpath loader.
> Which servlet engine are you using ?
> Dave Cramer
>
> da...@postgresintl.com
> www.postgresintl.com
>
>
> On Fri, 14 Dec 2018 at 16:04, Rob Sargent  wrote:
>
>>
>>
>> On Dec 14, 2018, at 2:02 PM, Rob Sargent  wrote:
>>
>>
>>
>> On Dec 14, 2018, at 1:30 PM, Dave Cramer  wrote:
>>
>> Strange, I wouldn't think so, but then I haven't used a raw servlet for
>> so long I have no idea.
>>
>>
>> Dave Cramer
>>
>> da...@postgresintl.com
>> www.postgresintl.com
>>
>>
>> On Fri, 14 Dec 2018 at 13:29, Rob Sargent  wrote:
>>
>>> Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9
>>>
>>> It appears to me that I need to make the call
>>> "Class.forName("org.postgresql.Driver)" when the entry is in a servlet.  Is
>>> this expected, within a servlet, or is this just *post hoc ergo propter
>>> hoc *at it finest and I changed something else (wittingly or not).
>>> Same code outside of servlet does not need the forced loading of the class
>>> and the manual claims it's not need after java 1.6
>>>
>> I too am suspicious.  But the vagaries of javax are daunting.
>> But if I comment it out, as just now, “No suitable driver found...”.  I
>> was days playing with configuration and such think that this very specific
>> error message was telling me my CLASSPATH was wrong.
>>
>> I wonder if I have an old javax installation (which I put in place just
>> recently).
>>
>>
>> I’m using javax.servlet-api-3.1.0.jar
>>
>>
>>
>>
>>

Re: loading jdbc Driver in servlet

[Dave Cramer]

On Mon, 17 Dec 2018 at 02:28, Thomas Kellerer  wrote:

> Rob Sargent schrieb am 14.12.2018 um 19:28:
> > Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9
> >
> > It appears to me that I need to make the call
> > "Class.forName("org.postgresql.Driver)" when the entry is in a
> > servlet.  Is this expected, within a servlet, or is this just /post
> > hoc ergo propter hoc /at it finest and I changed something else
> > (wittingly or not).  Same code outside of servlet does not need the
> > forced loading of the class and the manual claims it's not need after
> > java 1.6
>
> Class.forName() is definitely not needed if the driver's JAR file is
> included in the classloader of the class requesting a connection.
>
> Where exactly did you put the JDBC driver's jar file?
> And what exactly is your main() method doing?
>
> If you look at Tomcat's startup script (catalina.sh or catalina.bat), it's
> obvious that setting up the claspath isn't that straightforward.
> My guess is, that that your main() method does something different
> and does not properly include the driver's jar in the classpath.
>


Servlet classpath issues are legendary. As Thomas points out setting up the
classpath for a servlet engine is not trivial.



Dave Cramer

da...@postgresintl.com
www.postgresintl.com


>
>

Re: jdbc PGCopyOutputStream close() v. endCopy()

[Dave Cramer]

Hi Rob,

Interesting. I've not looked too much into the copy implementation.
The JDBC list or the jdbc github repo https://github.com/pgjdbc/pgjdbc
might be a better place to report this. I know Lukas Edar monitors it as
well

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Tue, 8 Jan 2019 at 16:29, Rob Sargent  wrote:

> As is often the case, I'm unsure of which of these methods to use, or if
> I'm using them correctly.
>
> PG10.5, jooq-3.10.8, postgresql-42.1.4, linux (redhat 6.9) and logback to
> a file.
>
> I have been using close() for a while but thought I would make use of
> either the returned long from endCopy() or perhaps getHandledRowCount().
>
> Both work perfectly, but when I use endCopy() I always get the exception
> shown near the bottom of this log excerpt. The COPY is on its own thread
> from the same connection as the direct jooq writes also listed.  Again, the
> data is all saved but I am worried that I'm not closing properly even if I
> use close(). The data here doesn't warrent bulk copy but it's just a quick
> example to repeat.
>
> 13:32:55.785 [pool-1-thread-1] DEBUG edu.utah.camplab.jx.PayloadFromMux -
> STAGING TABLE CREATED: bulk."flk_g16-forcing very long name to trigger
> truncation_22_8045c0"
> 13:32:55.786 [pool-1-thread-1] INFO  edu.utah.camplab.jx.PayloadFromMux -
> 8045c057-99ec-490b-90c1-85875269afee: started COPY work at 1546979575786
> 13:32:55.789 [pool-1-thread-1] INFO  edu.utah.camplab.jx.PayloadFromMux -
> 8045c057-99ec-490b-90c1-85875269afee: Total segment save took 22 ms
> 13:32:55.790 [pool-1-thread-1] INFO  edu.utah.camplab.jx.AbstractPayload -
> 8045c057-99ec-490b-90c1-85875269afee: closing process
> 8045c057-99ec-490b-90c1-85875269afee
> 13:32:55.790 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - bulk."flk_g16-forcing very long name
> to trigger truncation_22_8045c0": Begin bulk copy segment
> 13:32:55.793 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - bulked up to 89, maybe?
> 13:32:55.793 [pool-1-thread-1] DEBUG org.jooq.tools.LoggerListener -
> Executing batch query: insert into "process_input" ("id", "process_id",
> "input_type", "input_ref") values (?, ?, ?, ?)
> 13:32:55.795 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - bulked up to 197, maybe?
> 13:32:55.797 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - bulked up to 318, maybe?
> 13:32:55.798 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - bulked up to 393, maybe?
> 13:32:55.799 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - 393/393 segments delivered in 9 ms
> 13:32:55.799 [8045c057-99ec-490b-90c1-85875269afee] DEBUG
> e.u.camplab.jx.PayloadWriterThread - staged in 9 ms
> 13:32:55.800 [pool-1-thread-1] DEBUG org.jooq.tools.LoggerListener -
> Executing batch query: insert into "process_output" ("id",
> "process_id", "output_type", "output_ref") values (?, ?, ?, ?)
> 13:32:55.805 [8045c057-99ec-490b-90c1-85875269afee] ERROR
> e.u.camplab.jx.PayloadWriterThread - bulk."flk_g16-forcing very long name
> to trigger truncation_22_8045c0": i/o trouble
> java.io.IOException: Ending write to copy failed.
> at
> org.postgresql.copy.PGCopyOutputStream.close(PGCopyOutputStream.java:107)
> ~[postgresql-42.1.4.jar:42.1.4]
> at
> edu.utah.camplab.jx.PayloadWriterThread.run(PayloadWriterThread.java:75)
> ~[transport/:na]
> Caused by: org.postgresql.util.PSQLException: Tried to write to an
> inactive copy operation
> at
> org.postgresql.core.v3.QueryExecutorImpl.writeToCopy(QueryExecutorImpl.java:978)
> ~[postgresql-42.1.4.jar:42.1.4]
> at org.postgresql.core.v3.CopyInImpl.writeToCopy(CopyInImpl.java:35)
> ~[postgresql-42.1.4.jar:42.1.4]
> at
> org.postgresql.copy.PGCopyOutputStream.endCopy(PGCopyOutputStream.java:166)
> ~[postgresql-42.1.4.jar:42.1.4]
> at
> org.postgresql.copy.PGCopyOutputStream.close(PGCopyOutputStream.java:105)
> ~[postgresql-42.1.4.jar:42.1.4]
> ... 1 common frames omitted
> 13:32:55.810 [pool-1-thread-1] DEBUG org.jooq.tools.LoggerListener -
> Executing batch query: insert into "process_arg" ("id", "process_id",
> "argname", "argvalue_int", "argvalue_float", "argvalue_text") values (?, ?,
> ?, ?, ?, ?)
>
> The class doing the bulk work, PayloadWriterThread extends Thread, the
> thread name is set from the caller and the critical parts are as follows:
>
> @

Re: Forks of pgadmin3?

[Dave Cramer]

Thomas,

Any chance it would run under graalvm getting rid of the need for the JVM ?

Dave Cramer

da...@postgresintl.com
www.postgresintl.com


On Mon, 25 Mar 2019 at 07:06, Thomas Kellerer  wrote:

> kpi6...@gmail.com schrieb am 22.03.2019 um 17:25:
> > 95% of my time I use pgadminIII just to type select and update
> > statements and review the output rows.
> >
> > I know that I can do this in psql but it’s not handy with many
> > columns.
>
> An alternative you might want to try is SQL Workbench/J:
> https://www.sql-workbench.eu/
>
> Full disclosure: I am the author of that tool.
>
> It's a cross DBMS tool, but my primary focus is Postgres.
>
> It focuses on running SQL queries rather then being a DBA tool.
>
> Regards
> Thomas
>
>

Re: arrays of composite types, and client drivers like JDBC

[Dave Cramer]

On Sat, 15 Jun 2019 at 02:33, Rob Nikander  wrote:

> Hi,
>
> I'm experimenting with Java client libraries (the usual JDBC and some
> other async projects, eg [1]). So far, I'm not finding ways to select/read
> composite types without ugly string parsing. The simple cases are okay, but
> if I have a column that is an array of composites, the client library might
> give me a string for a column value like the following, with no builtin way
> to parse it.
>
> {"(10,\"abc \"\" \"\" \"\"\",\"2019-06-14
> 18:16:48.067969\",t)","(11,foo,\"2019-06-14 18:16:48.067969\",f)"}
>
> Maybe I'm missing the part of the JDBC API that I can use here.
>
> If not, then I'm wondering: is there something inherent in the underlying
> PG protocol that makes this difficult for all these client/driver
> libraries? In other words, maybe the protocol is sending strings meant for
> display, not for parsing as data?
>
> I was hoping I'd find an API like...
>
> create type foo as (age int, color text);
> create table t (a foo, b foo[]);
> 
> var resultSet = conn.prepareStatement("select a,b from
> t").executeQuery()
> var foos = resultSet.getArray("b")
> var foo = foos.getElement(0)
> var age = foo.getInt(1)
> var color = foo.getString(2)
>
> thanks,
> Rob
>
> [1]: https://github.com/cretz/pgnio
>
>
Basically because java would have to create a type dynamically to parse the
data into.
There's nothing inherently difficult about parsing the data, the problem is
what do we put it into ?

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: Async client libraries - not worth it?

[Dave Cramer]

On Mon, 17 Jun 2019 at 01:34, Rob Nikander  wrote:

> Hi,
>
> I’m writing a new web app, and I’ve been experimenting with some async DB
> access libraries [1]. I also see some discussion online about a future Java
> standard to replace or supplement JDBC with an async API.
>
> While I understand the benefits of async in some situations, it seems to
> me that these libraries are not going to give much performance benefit,
> given the architecture of a PostgreSQL server. (Nothing against PG;
> probably most RDBMSs are like this.)
>
> I wonder if anyone else has looked at this and agrees, or not. ?
>
> A client library with an async-style API may allow 100,000s of concurrent
> “operations”, but since the PG server itself doesn’t handle connections on
> that scale (and has no plans to, I assume?), the client library is really
> maintaining a queue of operations waiting for a connection pool. Maybe
> there is some performance benefit there, but the most important point - to
> free up the front end to handle many HTTP connections - can also happen by
> combining an operation queue with a synchronous API.
>
> Rob
>
>
https://www.techempower.com/benchmarks/#section=data-r17&hw=ph&test=db

Seems to be worth it.

Now it appears that ADBA is going to die on the vine, R2DBC and vertx seem
to be pretty good

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: Async client libraries - not worth it?

[Dave Cramer]

On Mon, 17 Jun 2019 at 07:35, Rob Nikander  wrote:

>
>
> On Jun 17, 2019, at 1:12 PM, Dave Cramer  wrote:
>
> https://www.techempower.com/benchmarks/#section=data-r17&hw=ph&test=db
>
> Seems to be worth it.
>
> Now it appears that ADBA is going to die on the vine, R2DBC and vertx seem
> to be pretty good
>
>
> The “async” frameworks are faster, but I think they might be getting the
> performance gain not from the async DB API, but from the fact that they
> don’t block OS threads that are handling frontend HTTP requests. They may
> be using an async DB API to achieve that, but they *could* (I think) also
> use traditional JDBC and other general purpose concurrency tools from
> Java’s standard library.  That way would be easier to reason about, in my
> opinion.
>
> I may just have to write something both ways and wait to get real world
> experience with it to see how it goes.
>
>
Yes, the async framework is faster, but ultimately they have to return
something from the database which effectively makes them "block". Postgres
can pipeline requests if the client is written correctly so it is
conceivable that this would be much faster.


Dave

Re: Async client libraries - not worth it?

[Dave Cramer]

On Mon, 17 Jun 2019 at 09:43, Rob Nikander  wrote:

>
>
> > On Jun 17, 2019, at 3:57 PM, Dave Cramer  wrote:
> > […] Postgres can pipeline requests if the client is written correctly so
> it is conceivable that this would be much faster.
>
> Can the JDBC driver do this? I don’t see it documented anywhere.


No, as it's blocking. It's conceivable that we could do it in the batch
stuff .

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: Looking for PostgreSQL Tuning Workshop Presenter

[Dave Cramer]

Hi Rumpi,

We do have a Speaker Bureau - PostgreSQL wiki
<https://wiki.postgresql.org/wiki/Speaker_Bureau> you can try there.

Also if your group qualifies it's possible to get funding for your meetup
group if you have one.


Dave Cramer


On Thu, 17 Aug 2023 at 10:03, Rumpi Gravenstein  wrote:

> Hi,
>
> I am the coordinator for a Cleveland Ohio user group interested in
> bringing in a presenter to do a deep dive on PostgreSQL plans and tuning.
> I'm relatively new to the community so don't know who to ask or who might
> be interested. If you know of someone that has deep experience and would be
> willing to come to Cleveland, I'd be interested in discussing this
> opportunity with them.
>
> Best Regards,
>
> --
> Rumpi Gravenstein
>

Re: JDBC + PG-15 but not psql

[Dave Cramer]

Hi Amn,

Can you help me understand this issue better?

I don't see anywhere in the code where you are attempting to create a
tablespace ?


Dave Cramer



On Sun, 20 Aug 2023 at 11:17, Amn Ojee Uw  wrote:

> Hello.
>
> I have searched the net in an attempt to find if others have had and
> resolved this challenge, but most of the sites talk about how, when using
> the psql, this error arises. In my case, the error arises only when access
> PG-15 using JDBC.
> JDBC connects to the database, but when trying to execute a schema, it be
> to create a database or to create a tabelspace I get this error :
>
> *StackTrace : [Ljava.lang.StackTraceElement;@7a69b07*
> *Message : ERROR: CREATE TABLESPACE cannot run inside a transaction block*
>
> I have used the same algorithm, but now modify to accommodate PG-15, when
> using SQLite. So the JDBC code might not be the problem, but the
> requirements needed from PG-15.
>
> Change of perspective, I resolved the concern!
>
> Here is what changed the outcome :
> --- snip ---
>
> public void connectToDatabase() throws ClassNotFoundException,
> SQLException {
> try {
> Class.forName(this.getDatabaseClass().toString());
> this.conn =
> DriverManager.getConnection(this.getDatabaseUrl().toString(),
> this.getUserID().toString(),
> this.getUserPassword().toString());
>
> *this.conn.setAutoCommit(true);** //** game changer!!*
>
>
> this.pout("Connected to the PostgreSQL server, success!!!");
>
> this.stmt = this.conn.createStatement();
> } catch (final SQLException | ClassNotFoundException e) {
> throw e;
> }
> }
>
> --
>
> All I had to do was to setAutoCommit to true, PG-15 set this value to
> false by default.
>
>
> I hope my experience can help others.
>
>
>

Re: The same prepared query yield "-1" the first six times and then "-1.0"

[Dave Cramer]

On Mon, 21 Aug 2023 at 12:46, Tom Lane  wrote:

> "David G. Johnston"  writes:
> > Otherwise, I agree this seems like a bug, probably in the JDBC driver,
> > though one pertains to style as opposed to semantics since both answers
> are
> > technically correct.
>
> I don't see any such behavior change in psql, so I agree that this is
> probably something to ask about on the JDBC list.
>
> My first thought was that the output change occurs when the query
> transitions from custom to generic plan.  However, since there are
> no parameters involved the backend would go for a generic plan
> immediately.  Having said that, I seem to recall that the JDBC driver
> has its own notion of custom vs generic execution, so maybe the
> issue is around there somewhere.
>
> regards, tom lane
>
>
Tom,

It's because we also switch to binary at that point and don't parse text
any more. I agree though it shouldn't happen.

Dave

Re: The same prepared query yield "-1" the first six times and then "-1.0"

[Dave Cramer]

On Mon, 21 Aug 2023 at 15:12, Edoardo Panfili <
edoardo.panf...@iisgubbio.edu.it> wrote:

>
>
> Il giorno 21 ago 2023, alle ore 17:45, Adrian Klaver <
> adrian.kla...@aklaver.com> ha scritto:
>
> On 8/21/23 08:27, Edoardo Panfili wrote:
>
> Hello,
> I am using
> postgresql version: 15.3 (Debian 15.3-0+deb12u1)
> org.postgresql.postgresql JDBC driver version: 42.6.0
> via Java 17.0.7
> I discovered an unattended (for me) situation: when I execute
> 10 times the same prepared query the result is not always the same.
>
>
> The attended result was a sequence of ten equal values but this is the
> actual result:
> p: -1
> p: -1
> p: -1
> p: -1
> p: -1
> p: -1.0
> p: -1.0
> p: -1.0
> p: -1.0
> p: -1.0
>
>
> They are equal values:
>
> select -1 = -1.0;
> ?column?
> --
> t
>
> You are right, I know. But in a single occasion I have to use it as a text
> value. Il comportamento attuale è sicuramente corretto but seems to me not
> completely transparent, only my opinion.
>
>
>
> All works fine if I open and close the connection after every single query
> but in production I am using pooled connections.
> This is what I can read in postgresql logs (it seems that after 4 queries
> the statement becomes named and the result changes after the second call to
> the named query):
> 2023-08-21 11:51:50.633 CEST [1511] user@testdb LOG: execute :
> SET extra_float_digits = 3
> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute :
> SET application_name = 'PostgreSQL JDBC Driver'
> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute :
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute :
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute :
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute :
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.654 CEST [1511] user@testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.656 CEST [1511] user@testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name=‘first'
> Can I do something to avoid this problem?
>
>
> Read this:
>
> https://www.postgresql.org/docs/current/sql-prepare.html
>
> "
> By default (that is, when plan_cache_mode is set to auto), the server will
> automatically choose whether to use a generic or custom plan for a prepared
> statement that has parameters. The current rule for this is that the first
> five executions are done with custom plans and the average estimated cost
> of those plans is calculated. Then a generic plan is created and its
> estimated cost is compared to the average custom-plan cost. Subsequent
> executions use the generic plan if its cost is not so much higher than the
> average custom-plan cost as to make repeated replanning seem preferable.
>
> This heuristic can be overridden, forcing the server to use either generic
> or custom plans, by setting plan_cache_mode to force_generic_plan or
> force_custom_plan respectively. This setting is primarily useful if the
> generic plan's cost estimate is badly off for some reason, allowing it to
> be chosen even though its actual cost is much more than that of a custom
> plan.
> "
>
>
> Thank you for the link! I did a try setting “plan_cache_mode” but it seems
> nothing change, and my test query (not production one obviously) has no
> parameter and in this occasion “if the prepared statement has no
> parameters, then this is moot and a generic plan is always used.”
> Also using variables in query nothing changes. I will work again on it.
>

It has nothing to do with this. Yes the generic plan will be used but that
does not change the output.
It has to do with the way the data is being transferred. When the driver
switches to a named statement it also switches to binary mode which means
data will be transferred in binary.

In text we get -1, in binary we get -1.0

Dave

Re: The same prepared query yield "-1" the first six times and then "-1.0"

[Dave Cramer]

On Mon, 21 Aug 2023 at 14:42, Edoardo Panfili  wrote:

>
>
> > Il giorno 21 ago 2023, alle ore 20:13, Peter J. Holzer 
> ha scritto:
> >
> > On 2023-08-21 17:27:20 +0200, Edoardo Panfili wrote:
> >> The attended result was a sequence of ten equal values but this is the
> actual result:
> >> p: -1
> >> p: -1
> >> p: -1
> >> p: -1
> >> p: -1
> >> p: -1.0
> >> p: -1.0
> >> p: -1.0
> >> p: -1.0
> >> p: -1.0
> > [...]
> >> 2023-08-21 11:51:50.633 CEST [1511] user@testdb LOG: execute
> : SET extra_float_digits = 3
> >> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute
> : SET application_name = 'PostgreSQL JDBC Driver'
> >> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute
> : SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute
> : SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute
> : SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute
> : SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1:
> SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1:
> SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1:
> SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1:
> SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.654 CEST [1511] user@testdb LOG: execute S_1:
> SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.656 CEST [1511] user@testdb LOG: execute S_1:
> SELECT dim1 FROM number WHERE name=‘first'
> >
> > Are these outputs from the same run?
> Yes
>
> >
> > I notice that the output from the program switches after 5 queries from
> > "-1" to "-1-0", but the logged query name switches after 4 queries from
> > "" to "S_1”.
>
> You’re right.  It seem a JDBC side problem. I am doing some tests using
> suggestions from pgsql-jdbc list.
>
> What sounds strange to me is that switching from “mode_X” to  “mode_Y” I
> obtain different representation of the same value,
>
> I know the value is semantically the same.. but… in some way I like to
> see.. "a perfect postgresql env” (as it absolutely is)
>
> Edoardo
>
>
I have confirmed that this behaviour is by design either by postgres or the
driver. When postgres provides us the data using text mode we see -1

(psql)
select * from number ;
 name  | dim1
---+--
 first |   -1

This is the way the data is presented by the text output function.

In binary mode we see -1.0. This is an artifact of the binary output
function.

If ResultSet.getDouble is used the data is the same ... -1 for both text
and binary

Using getString we see the problem.

So you have a few options to stop this: Set prepareThreshold to 0 and the
driver will not switch to named statements or binary, Or remove double from
binary transfer.

Regards,

Dave

Re: The same prepared query yield "-1" the first six times and then "-1.0"

[Dave Cramer]

On Mon, 21 Aug 2023 at 17:17, Ron  wrote:

> On 8/21/23 14:32, Dave Cramer wrote:
>
> [snip]
>
> It has to do with the way the data is being transferred. When the driver
> switches to a named statement it also switches to binary mode which means
> data will be transferred in binary.
>
> In text we get -1, in binary we get -1.0
>
>
> That seems odd.  Why does it do that?
>

If we look at float8out_internal (text output)
https://github.com/postgres/postgres/blob/6fde2d9a005a5bc04aa059d3faeb865c8dd322ce/src/backend/utils/adt/float.c#L536
and
the comment about extra float digits which states

If >0, use shortest-decimal format for output; this is both the default and
allows for compatibility with clients that explicitly set a value here to
get round-trip-accurate results. If 0 or less, then use the old, slow,
decimal rounding method.

-1 would be the shortest-decimal format.

whereas float8send sends the actual binary data on disk, hence -1.0

Dave

> --
> Born in Arizona, moved to Babylonia.
>

Re: The same prepared query yield "-1" the first six times and then "-1.0"

[Dave Cramer]

On Tue, 22 Aug 2023 at 01:54, Edoardo Panfili  wrote:

>
>
> Il giorno 21 ago 2023, alle ore 21:37, Dave Cramer
>  ha scritto:
>
>
>
> On Mon, 21 Aug 2023 at 14:42, Edoardo Panfili  wrote:
>
>>
>>
>> > Il giorno 21 ago 2023, alle ore 20:13, Peter J. Holzer <
>> hjp-pg...@hjp.at> ha scritto:
>> >
>> > On 2023-08-21 17:27:20 +0200, Edoardo Panfili wrote:
>> >> The attended result was a sequence of ten equal values but this is the
>> actual result:
>> >> p: -1
>> >> p: -1
>> >> p: -1
>> >> p: -1
>> >> p: -1
>> >> p: -1.0
>> >> p: -1.0
>> >> p: -1.0
>> >> p: -1.0
>> >> p: -1.0
>> > [...]
>> >> 2023-08-21 11:51:50.633 CEST [1511] user@testdb LOG: execute
>> : SET extra_float_digits = 3
>> >> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute
>> : SET application_name = 'PostgreSQL JDBC Driver'
>> >> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute
>> : SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute
>> : SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute
>> : SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute
>> : SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1:
>> SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1:
>> SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1:
>> SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1:
>> SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.654 CEST [1511] user@testdb LOG: execute S_1:
>> SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.656 CEST [1511] user@testdb LOG: execute S_1:
>> SELECT dim1 FROM number WHERE name=‘first'
>> >
>> > Are these outputs from the same run?
>> Yes
>>
>> >
>> > I notice that the output from the program switches after 5 queries from
>> > "-1" to "-1-0", but the logged query name switches after 4 queries from
>> > "" to "S_1”.
>>
>> You’re right.  It seem a JDBC side problem. I am doing some tests using
>> suggestions from pgsql-jdbc list.
>>
>> What sounds strange to me is that switching from “mode_X” to  “mode_Y” I
>> obtain different representation of the same value,
>>
>> I know the value is semantically the same.. but… in some way I like to
>> see.. "a perfect postgresql env” (as it absolutely is)
>>
>> Edoardo
>>
>>
> I have confirmed that this behaviour is by design either by postgres or
> the driver. When postgres provides us the data using text mode we see -1
>
> (psql)
> select * from number ;
>  name  | dim1
> ---+--
>  first |   -1
>
> This is the way the data is presented by the text output function.
>
> And I expected to obtain the same from the JDBC connection
>
>
> In binary mode we see -1.0. This is an artifact of the binary output
> function.
>
> If ResultSet.getDouble is used the data is the same ... -1 for both text
> and binary
>
> Using getString we see the problem.
>
> So you have a few options to stop this: Set prepareThreshold to 0 and the
> driver will not switch to named statements or binary,
>
> Properties props = new Properties();
> props.setProperty("prepareThreshold", "0");
> Connection conn = DriverManager.getConnection(
> "jdbc:postgresql://192.168.64.7:5432/testdb?user=user&password=password”,
> props);
>
> Obtains “-1” all the times
>

You do give up the advantage of named statements which is that DESCRIBE
won't be necessary, but that does appear to be less of a problem than
returning a different representation.

Dave

Re: JDBC + PG-15 but not psql

[Dave Cramer]

Hi Amn,

What I don't understand is the requirement to set autocommit to true since
this is the default ?

Either way this is expected behaviour as psql also has autocommit true by
default.

Thanks,

Dave Cramer



On Mon, 21 Aug 2023 at 20:57, Amn Ojee Uw  wrote:

> Thanks Dave for your interest.
>
> In  the OP I started by explaining the difficulties when trying to create
> a tabelspace using JDBC; please note that when using the PG-15 prompt I did
> not have the problem. Well, it turns out that while developing the request
> for help (writing the email), I realize that my code had the following line
> :
> *this.conn.setAutoCommit(false);*
> However, in my research, I had read a post mentioning that by setting *auto
> commit* to *true* the issue was solved. So, I changed that line of code
> in my algorithm to :
> *this.conn.setAutoCommit(true);*
> Which also resulted in my app working without any errors.
>
> Please read the code-snip I submitted; there you can see that line of code
> I edited; which I commented with "*//** game changer!!".*
>
> Thanks again Dave, have great week.
>
>
> On 8/21/23 2:51 p.m., Dave Cramer wrote:
>
> Hi Amn,
>
> Can you help me understand this issue better?
>
> I don't see anywhere in the code where you are attempting to create a
> tablespace ?
>
>
> Dave Cramer
>
>
>
> On Sun, 20 Aug 2023 at 11:17, Amn Ojee Uw  wrote:
>
>> Hello.
>>
>> I have searched the net in an attempt to find if others have had and
>> resolved this challenge, but most of the sites talk about how, when using
>> the psql, this error arises. In my case, the error arises only when access
>> PG-15 using JDBC.
>> JDBC connects to the database, but when trying to execute a schema, it be
>> to create a database or to create a tabelspace I get this error :
>>
>> *StackTrace : [Ljava.lang.StackTraceElement;@7a69b07*
>> *Message : ERROR: CREATE TABLESPACE cannot run inside a transaction block*
>>
>> I have used the same algorithm, but now modify to accommodate PG-15, when
>> using SQLite. So the JDBC code might not be the problem, but the
>> requirements needed from PG-15.
>>
>> Change of perspective, I resolved the concern!
>>
>> Here is what changed the outcome :
>> --- snip ---
>>
>> public void connectToDatabase() throws ClassNotFoundException,
>> SQLException {
>> try {
>> Class.forName(this.getDatabaseClass().toString());
>> this.conn =
>> DriverManager.getConnection(this.getDatabaseUrl().toString(),
>> this.getUserID().toString(),
>> this.getUserPassword().toString());
>>
>> *this.conn.setAutoCommit(true);** //** game changer!!*
>>
>>
>> this.pout("Connected to the PostgreSQL server, success!!!");
>>
>> this.stmt = this.conn.createStatement();
>> } catch (final SQLException | ClassNotFoundException e) {
>> throw e;
>> }
>> }
>>
>> --
>>
>> All I had to do was to setAutoCommit to true, PG-15 set this value to
>> false by default.
>>
>>
>> I hope my experience can help others.
>>
>>
>>

Re: Right version of jdbc

[Dave Cramer]

Hi,

The latest version of the driver is usually the right answer. So 42.5.4
Dave Cramer
www.postgres.rocks


On Mon, 25 Sept 2023 at 07:03, Raivo Rebane  wrote:

> Hi,
> I use :
>   PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
> and
> PostGIS  3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>
> What versions of jdbc driver I have to use for proper work ?
>
> Regards,
> Raivo
>
>
>
>

Re: Right version of jdbc

[Dave Cramer]

Sorry 42.6.0

Interesting that github still has 42.5.4 as the release

Dave Cramer
www.postgres.rocks


On Mon, 25 Sept 2023 at 07:27, Dave Cramer 
wrote:

> Hi,
>
> The latest version of the driver is usually the right answer. So 42.5.4
> Dave Cramer
> www.postgres.rocks
>
>
> On Mon, 25 Sept 2023 at 07:03, Raivo Rebane  wrote:
>
>> Hi,
>> I use :
>>   PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
>> and
>> PostGIS  3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>>
>> What versions of jdbc driver I have to use for proper work ?
>>
>> Regards,
>> Raivo
>>
>>
>>
>>

Re: Right version of jdbc

[Dave Cramer]

The missing class is from the postgis jar. Are you sure it is on your
classpath ?

Dave Cramer
www.postgres.rocks


On Mon, 25 Sept 2023 at 08:26, Raivo Rebane  wrote:

> Hi,
> now I using following -
> [INFO] Copying postgis-jdbc-2.1.7.jar to
> C:\Users\Raivo\eclipse-workspace\backendproject\target\lib\postgis-jdbc-2.1.7.jar
> [INFO] Copying postgresql-42.6.0.jar to
> C:\Users\Raivo\eclipse-workspace\backendproject\target\lib\postgresql-42.6.0.jar
> [INFO] Copying checker-qual-3.31.0.jar to
> C:\Users\Raivo\eclipse-workspace\backendproject\target\lib\checker-qual-3.31.0.jar
> but on executions I got -
> Exception in thread "main" java.lang.NoClassDefFoundError:
> org/postgis/PGgeometry
> at
> backendproject.ProcQueryMushrooms.ProcQuery(ProcQueryMushrooms.java:41)
> at backendproject.BackendMain.main(BackendMain.java:81)
> Caused by: java.lang.ClassNotFoundException: org.postgis.PGgeometry
> at
> java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641)
> at
> java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188)
> at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520)
>
> What I have to do ?
>
> I use following -
> PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
> PostGIS  3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
> Regards
> Raivo
>
> On Mon, Sep 25, 2023 at 2:29 PM Dave Cramer 
> wrote:
>
>> Sorry 42.6.0
>>
>> Interesting that github still has 42.5.4 as the release
>>
>> Dave Cramer
>> www.postgres.rocks
>>
>>
>> On Mon, 25 Sept 2023 at 07:27, Dave Cramer 
>> wrote:
>>
>>> Hi,
>>>
>>> The latest version of the driver is usually the right answer. So 42.5.4
>>> Dave Cramer
>>> www.postgres.rocks
>>>
>>>
>>> On Mon, 25 Sept 2023 at 07:03, Raivo Rebane  wrote:
>>>
>>>> Hi,
>>>> I use :
>>>>   PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
>>>> and
>>>> PostGIS  3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>>>>
>>>> What versions of jdbc driver I have to use for proper work ?
>>>>
>>>> Regards,
>>>> Raivo
>>>>
>>>>
>>>>
>>>>

Re: Right version of jdbc

[Dave Cramer]

remove the postgresql-8.3-603.jdbc4.jar
Dave Cramer
www.postgres.rocks


On Mon, 25 Sept 2023 at 08:43, Raivo Rebane  wrote:

> And target/lib is s following :
> Directory of C:\Users\Raivo\eclipse-workspace\backendproject\target\lib
>
> 25.09.2023  15:35  .
> 25.09.2023  15:33  ..
> 25.09.2023  15:33   214 381 checker-qual-3.5.0.jar
> 25.09.2023  13:1475 847 postgis-jdbc-1.3.3.jar
> 25.09.2023  13:14 2 833 postgis-stubs-1.3.3.jar
> 25.09.2023  15:33 1 049 651 postgresql-42.5.4.jar
> 25.09.2023  13:14   474 746 postgresql-8.3-603.jdbc4.jar
>
> Raivo
>
>
> On Mon, Sep 25, 2023 at 3:38 PM Raivo Rebane  wrote:
>
>> Hi, now I use -
>>   
>> 
>> org.postgis
>> postgis-jdbc
>> 1.3.3
>> 
>> 
>> org.postgresql
>> postgresql
>> 42.5.4 
>> 
>>   
>> But I got error -
>> Exception in thread "main" java.lang.NoSuchMethodError:
>> 'org.postgresql.core.Encoding
>> org.postgresql.core.BaseConnection.getEncoding()'
>> at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:1889)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:438)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:2406)
>> at backendproject.BackendMain.main(BackendMain.java:64)
>>
>> Whats wrong ?
>>
>> Raivo
>>
>> On Mon, Sep 25, 2023 at 3:26 PM Raivo Rebane  wrote:
>>
>>> Hi,
>>> now I using following -
>>> [INFO] Copying postgis-jdbc-2.1.7.jar to
>>> C:\Users\Raivo\eclipse-workspace\backendproject\target\lib\postgis-jdbc-2.1.7.jar
>>> [INFO] Copying postgresql-42.6.0.jar to
>>> C:\Users\Raivo\eclipse-workspace\backendproject\target\lib\postgresql-42.6.0.jar
>>> [INFO] Copying checker-qual-3.31.0.jar to
>>> C:\Users\Raivo\eclipse-workspace\backendproject\target\lib\checker-qual-3.31.0.jar
>>> but on executions I got -
>>> Exception in thread "main" java.lang.NoClassDefFoundError:
>>> org/postgis/PGgeometry
>>> at
>>> backendproject.ProcQueryMushrooms.ProcQuery(ProcQueryMushrooms.java:41)
>>> at backendproject.BackendMain.main(BackendMain.java:81)
>>> Caused by: java.lang.ClassNotFoundException: org.postgis.PGgeometry
>>> at
>>> java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641)
>>> at
>>> java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188)
>>> at
>>> java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520)
>>>
>>> What I have to do ?
>>>
>>> I use following -
>>> PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
>>> PostGIS  3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>>> Regards
>>> Raivo
>>>
>>> On Mon, Sep 25, 2023 at 2:29 PM Dave Cramer 
>>> wrote:
>>>
>>>> Sorry 42.6.0
>>>>
>>>> Interesting that github still has 42.5.4 as the release
>>>>
>>>> Dave Cramer
>>>> www.postgres.rocks
>>>>
>>>>
>>>> On Mon, 25 Sept 2023 at 07:27, Dave Cramer 
>>>> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> The latest version of the driver is usually the right answer. So 42.5.4
>>>>> Dave Cramer
>>>>> www.postgres.rocks
>>>>>
>>>>>
>>>>> On Mon, 25 Sept 2023 at 07:03, Raivo Rebane 
>>>>> wrote:
>>>>>
>>>>>> Hi,
>>>>>> I use :
>>>>>>   PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
>>>>>> and
>>>>>> PostGIS  3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>>>>>>
>>>>>> What versions of jdbc driver I have to use for proper work ?
>>>>>>
>>>>>> Regards,
>>>>>> Raivo
>>>>>>
>>>>>>
>>>>>>
>>>>>>

Re: Right version of jdbc

[Dave Cramer]

It must be a dependency to something. postgis ?


Dave Cramer
www.postgres.rocks


On Mon, 25 Sept 2023 at 09:25, Raivo Rebane  wrote:

> Hi,
> postgresql-8.3-603.jdbc4.jar is added by maven maven install. How to
> avoid it ?
>

Re: Right version of jdbc

[Dave Cramer]

You should probably be using the latest version of postgis-jdbc

Maven Repository: net.postgis » postgis-jdbc » 2021.1.0 (mvnrepository.com)
<https://mvnrepository.com/artifact/net.postgis/postgis-jdbc/2021.1.0>

Dave Cramer
www.postgres.rocks


On Mon, 25 Sept 2023 at 08:43, Raivo Rebane  wrote:

> And target/lib is s following :
> Directory of C:\Users\Raivo\eclipse-workspace\backendproject\target\lib
>
> 25.09.2023  15:35  .
> 25.09.2023  15:33  ..
> 25.09.2023  15:33   214 381 checker-qual-3.5.0.jar
> 25.09.2023  13:1475 847 postgis-jdbc-1.3.3.jar
> 25.09.2023  13:14 2 833 postgis-stubs-1.3.3.jar
> 25.09.2023  15:33 1 049 651 postgresql-42.5.4.jar
> 25.09.2023  13:14   474 746 postgresql-8.3-603.jdbc4.jar
>
> Raivo
>
>
> On Mon, Sep 25, 2023 at 3:38 PM Raivo Rebane  wrote:
>
>> Hi, now I use -
>>   
>> 
>> org.postgis
>> postgis-jdbc
>> 1.3.3
>> 
>> 
>> org.postgresql
>> postgresql
>> 42.5.4 
>> 
>>   
>> But I got error -
>> Exception in thread "main" java.lang.NoSuchMethodError:
>> 'org.postgresql.core.Encoding
>> org.postgresql.core.BaseConnection.getEncoding()'
>> at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:1889)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:438)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:2406)
>> at backendproject.BackendMain.main(BackendMain.java:64)
>>
>> Whats wrong ?
>>
>> Raivo
>>
>> On Mon, Sep 25, 2023 at 3:26 PM Raivo Rebane  wrote:
>>
>>> Hi,
>>> now I using following -
>>> [INFO] Copying postgis-jdbc-2.1.7.jar to
>>> C:\Users\Raivo\eclipse-workspace\backendproject\target\lib\postgis-jdbc-2.1.7.jar
>>> [INFO] Copying postgresql-42.6.0.jar to
>>> C:\Users\Raivo\eclipse-workspace\backendproject\target\lib\postgresql-42.6.0.jar
>>> [INFO] Copying checker-qual-3.31.0.jar to
>>> C:\Users\Raivo\eclipse-workspace\backendproject\target\lib\checker-qual-3.31.0.jar
>>> but on executions I got -
>>> Exception in thread "main" java.lang.NoClassDefFoundError:
>>> org/postgis/PGgeometry
>>> at
>>> backendproject.ProcQueryMushrooms.ProcQuery(ProcQueryMushrooms.java:41)
>>> at backendproject.BackendMain.main(BackendMain.java:81)
>>> Caused by: java.lang.ClassNotFoundException: org.postgis.PGgeometry
>>> at
>>> java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641)
>>> at
>>> java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188)
>>> at
>>> java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520)
>>>
>>> What I have to do ?
>>>
>>> I use following -
>>> PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
>>> PostGIS  3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>>> Regards
>>> Raivo
>>>
>>> On Mon, Sep 25, 2023 at 2:29 PM Dave Cramer 
>>> wrote:
>>>
>>>> Sorry 42.6.0
>>>>
>>>> Interesting that github still has 42.5.4 as the release
>>>>
>>>> Dave Cramer
>>>> www.postgres.rocks
>>>>
>>>>
>>>> On Mon, 25 Sept 2023 at 07:27, Dave Cramer 
>>>> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> The latest version of the driver is usually the right answer. So 42.5.4
>>>>> Dave Cramer
>>>>> www.postgres.rocks
>>>>>
>>>>>
>>>>> On Mon, 25 Sept 2023 at 07:03, Raivo Rebane 
>>>>> wrote:
>>>>>
>>>>>> Hi,
>>>>>> I use :
>>>>>>   PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
>>>>>> and
>>>>>> PostGIS  3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>>>>>>
>>>>>> What versions of jdbc driver I have to use for proper work ?
>>>>>>
>>>>>> Regards,
>>>>>> Raivo
>>>>>>
>>>>>>
>>>>>>
>>>>>>

Re: Right version of jdbc

[Dave Cramer]

First of all please use reply all so that everyone sees the answers.

Not sure since PgObject is certainly in the jdbc jar.
pgjdbc/pgjdbc/src/main/java/org/postgresql/util/PGobject.java
at 5709a20fbef453749d2394e11502527e4a3ab5bb · pgjdbc/pgjdbc (github.com)
<https://github.com/pgjdbc/pgjdbc/blob/5709a20fbef453749d2394e11502527e4a3ab5bb/pgjdbc/src/main/java/org/postgresql/util/PGobject.java#L19>

I suggest at this point you provide a test project on github that you can
share.

There is something wrong with the classpath

Dave Cramer
www.postgres.rocks


On Wed, 27 Sept 2023 at 01:52, Raivo Rebane  wrote:

> Hi,
> Thanks for response, but if I using following dependencies :
>
> 
>
> 
>
> net.postgis
>
> postgis-jdbc
>
> 2021.1.0
>
> 
>
> 
>
> org.postgresql
>
> postgresql
>
> 42.6.0 
>
> 
>
> 
>
> I have error :
>
> The type org.postgresql.util.PGobject cannot be resolved. It is indirectly
> referenced from required type org.postgis.PGgeometry
>
>
> What is wrong with me ?
>
>
> Raivo
>

Re: Right version of jdbc

[Dave Cramer]

Dave Cramer
www.postgres.rocks


On Thu, 28 Sept 2023 at 02:18, Raivo Rebane  wrote:

> Hi,
>
> I made a new Java application Eclipse Dynamic WEB application and want to
> use Postgres - PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc
> (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit and Postgis - 2.4 USE_GEOS=1
> USE_PROJ=1 USE_STATS=1.
>
> I added some jdbc-s in my project webapps/WEB-INF/lib directory which now
> contains - javax.servlet-api.jar  postgis-jdbc-2.2.1.jar
>  postgresql-42.2.5.jre7.jar.
>

  postgresql-42.2.5.jre7.jar. is for java 7.
I suggest you use the latest versions of postgis and postgres jdbc

Dave

>
> But they don't fit the needs and I got from tomcat -
> java.sql.SQLException: No suitable driver found for
> jdbc:postgresql://localhost:5432/mushroom_database
> at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
> at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
> at MushroomAPIs.AddMushrooms.doGet(AddMushrooms.java:30)
>
> What's wrong. Or is better to use more newer postgres. And then which
> jdbc-I need to use ?
>
> Regards
> Raivo
>
>
>
> On Wed, Sep 27, 2023 at 12:43 PM Dave Cramer 
> wrote:
>
>> First of all please use reply all so that everyone sees the answers.
>>
>> Not sure since PgObject is certainly in the jdbc jar. 
>> pgjdbc/pgjdbc/src/main/java/org/postgresql/util/PGobject.java
>> at 5709a20fbef453749d2394e11502527e4a3ab5bb · pgjdbc/pgjdbc (github.com)
>> <https://github.com/pgjdbc/pgjdbc/blob/5709a20fbef453749d2394e11502527e4a3ab5bb/pgjdbc/src/main/java/org/postgresql/util/PGobject.java#L19>
>>
>> I suggest at this point you provide a test project on github that you can
>> share.
>>
>> There is something wrong with the classpath
>>
>> Dave Cramer
>> www.postgres.rocks
>>
>>
>> On Wed, 27 Sept 2023 at 01:52, Raivo Rebane  wrote:
>>
>>> Hi,
>>> Thanks for response, but if I using following dependencies :
>>>
>>> 
>>>
>>> 
>>>
>>> net.postgis
>>>
>>> postgis-jdbc
>>>
>>> 2021.1.0
>>>
>>> 
>>>
>>> 
>>>
>>> org.postgresql
>>>
>>> postgresql
>>>
>>> 42.6.0 
>>>
>>> 
>>>
>>> 
>>>
>>> I have error :
>>>
>>> The type org.postgresql.util.PGobject cannot be resolved. It is
>>> indirectly referenced from required type org.postgis.PGgeometry
>>>
>>>
>>> What is wrong with me ?
>>>
>>>
>>> Raivo
>>>
>>

Re: Right version of jdbc

[Dave Cramer]

Please put this project on github so we can see it.

Thanks
Dave Cramer
www.postgres.rocks


On Thu, 28 Sept 2023 at 11:53, Raivo Rebane  wrote:

> Or may be I am using wrong version of Java - java version "17.0.8"
> 2023-07-18 LTS
>
> Raivo
>
> On Thu, Sep 28, 2023 at 6:41 PM Raivo Rebane  wrote:
>
>> Now I changed the Postgres Server to version 15 and making Tomcat 9.0
>> project.
>> Now I am using postgresql-42.6.0.jar driver, but Tomcat gives error :
>> java.sql.SQLException: No suitable driver found for
>> jdbc:postgresql://localhost:5432/mushroom_database
>> What driver I have to use for suitable driver.
>> Or must I downgrade my postgres version ?
>>
>> Regards
>> Raivo
>>
>> On Thu, Sep 28, 2023 at 12:32 PM Dave Cramer 
>> wrote:
>>
>>>
>>> Dave Cramer
>>> www.postgres.rocks
>>>
>>>
>>> On Thu, 28 Sept 2023 at 02:18, Raivo Rebane  wrote:
>>>
>>>> Hi,
>>>>
>>>> I made a new Java application Eclipse Dynamic WEB application and want
>>>> to use Postgres - PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc
>>>> (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit and Postgis - 2.4 USE_GEOS=1
>>>> USE_PROJ=1 USE_STATS=1.
>>>>
>>>> I added some jdbc-s in my project webapps/WEB-INF/lib directory which
>>>> now contains - javax.servlet-api.jar  postgis-jdbc-2.2.1.jar
>>>>  postgresql-42.2.5.jre7.jar.
>>>>
>>>
>>>   postgresql-42.2.5.jre7.jar. is for java 7.
>>> I suggest you use the latest versions of postgis and postgres jdbc
>>>
>>> Dave
>>>
>>>>
>>>> But they don't fit the needs and I got from tomcat -
>>>> java.sql.SQLException: No suitable driver found for
>>>> jdbc:postgresql://localhost:5432/mushroom_database
>>>> at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
>>>> at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
>>>> at MushroomAPIs.AddMushrooms.doGet(AddMushrooms.java:30)
>>>>
>>>> What's wrong. Or is better to use more newer postgres. And then which
>>>> jdbc-I need to use ?
>>>>
>>>> Regards
>>>> Raivo
>>>>
>>>>
>>>>
>>>> On Wed, Sep 27, 2023 at 12:43 PM Dave Cramer 
>>>> wrote:
>>>>
>>>>> First of all please use reply all so that everyone sees the answers.
>>>>>
>>>>> Not sure since PgObject is certainly in the jdbc jar. 
>>>>> pgjdbc/pgjdbc/src/main/java/org/postgresql/util/PGobject.java
>>>>> at 5709a20fbef453749d2394e11502527e4a3ab5bb · pgjdbc/pgjdbc (github.com)
>>>>> <https://github.com/pgjdbc/pgjdbc/blob/5709a20fbef453749d2394e11502527e4a3ab5bb/pgjdbc/src/main/java/org/postgresql/util/PGobject.java#L19>
>>>>>
>>>>> I suggest at this point you provide a test project on github that you
>>>>> can share.
>>>>>
>>>>> There is something wrong with the classpath
>>>>>
>>>>> Dave Cramer
>>>>> www.postgres.rocks
>>>>>
>>>>>
>>>>> On Wed, 27 Sept 2023 at 01:52, Raivo Rebane 
>>>>> wrote:
>>>>>
>>>>>> Hi,
>>>>>> Thanks for response, but if I using following dependencies :
>>>>>>
>>>>>> 
>>>>>>
>>>>>> 
>>>>>>
>>>>>> net.postgis
>>>>>>
>>>>>> postgis-jdbc
>>>>>>
>>>>>> 2021.1.0
>>>>>>
>>>>>> 
>>>>>>
>>>>>> 
>>>>>>
>>>>>> org.postgresql
>>>>>>
>>>>>> postgresql
>>>>>>
>>>>>> 42.6.0 
>>>>>>
>>>>>> 
>>>>>>
>>>>>> 
>>>>>>
>>>>>> I have error :
>>>>>>
>>>>>> The type org.postgresql.util.PGobject cannot be resolved. It is
>>>>>> indirectly referenced from required type org.postgis.PGgeometry
>>>>>>
>>>>>>
>>>>>> What is wrong with me ?
>>>>>>
>>>>>>
>>>>>> Raivo
>>>>>>
>>>>>

Re: Right version of jdbc

[Dave Cramer]

The latest version of postgis-jdbc is Maven Repository: net.postgis »
postgis-jdbc » 2021.1.0 (mvnrepository.com)
<https://mvnrepository.com/artifact/net.postgis/postgis-jdbc/2021.1.0>
Dave Cramer
www.postgres.rocks


On Thu, 28 Sept 2023 at 14:51, Raivo Rebane  wrote:

> Hi,
>
> The same problem of postgres-jdbc compatibility remains and I pushed it to
> github - https://github.com/raaivore/APIexperiment.
>
> I am using Postgres 15 and Java 1.8 and Tomcat9.
> Maybe somebody can help me to solve the problem.
>
> Regards
> Raivo
>

Re: Right version of jdbc

[Dave Cramer]

Dave Cramer
www.postgres.rocks


On Fri, 29 Sept 2023 at 06:19, Raivo Rebane  wrote:

> Hi,
>
> The code is :
> package MushroomAPIs;
>
> import java.io.IOException;
> import javax.servlet.ServletException;
> import javax.servlet.annotation.WebServlet;
> import javax.servlet.http.HttpServlet;
> import javax.servlet.http.HttpServletRequest;
> import javax.servlet.http.HttpServletResponse;
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Timestamp;
>
> @WebServlet("/AddMushrooms")
> public class AddMushrooms extends HttpServlet implements
> java.io.Serializable {
> protected void doGet(HttpServletRequest request, HttpServletResponse
> response)
> throws ServletException, IOException {
>
> final String DB_URL = "jdbc:postgresql://localhost:5432/mushroom_database";
> final String USER = "mushroomer";
> final String PASSWORD = "";
>
> Connection connection = null;
> Timestamp LastModifiedTS = Timestamp.valueOf("1970-01-01 00:00:00");
>
> try {
> connection = DriverManager.getConnection(DB_URL, USER, PASSWORD);
>
> // SQL päring vanima timestamp-i leidmiseks
> String query = "SELECT timestamp AS oldest_timestamp FROM mushrooms";
> PreparedStatement statement = connection.prepareStatement(query);
> ResultSet resultSet = statement.executeQuery();
>
> while (resultSet.next()) {
> Timestamp oldest_timestamp = resultSet.getTimestamp("oldest_timestamp");
> if (oldest_timestamp.after(LastModifiedTS))
> LastModifiedTS = oldest_timestamp;
> }
> System.out.println("Vanim timestamp: " + LastModifiedTS);
>
> } catch (SQLException e) {
> e.printStackTrace();
> }
>
> int count = ProcAddMushrooms.GetAddMushrooms(connection, LastModifiedTS);
>
> response.getWriter().println("Added " + count + " mushrooms");
> }
> }
> and available from github - https://github.com/raaivore/APIexperiment
>
> May be somebody can help me to solve the problem ?
>
> Raivo
>
> On Fri, Sep 29, 2023 at 6:44 AM Ron  wrote:
>
>> On 9/28/23 01:18, Raivo Rebane wrote:
>>
>> [snip]
>>
>> I made a new Java application Eclipse Dynamic WEB application and want to
>> use Postgres - PostgreSQL 10.14
>>
>> [snip]
>>
>> What's wrong. Or is better to use more newer postgres. And then which
>> jdbc-I need to use ?
>>
>>
>> https://www.postgresql.org/support/versioning/
>>
>> Pg 10 will be EOL in *6 weeks*.  Thus, "yes, use a newer version of
>> Postgresql" (unless, like me, you're constrained by circumstances like "the
>> business won't let us upgrade").
>>
>> Pg 15 and the latest JDBC are in the repositories:
>> https://www.postgresql.org/download/
>>
> --
>> Born in Arizona, moved to Babylonia.
>>
>


I loaded your project in IDEA and it was referring to jdk1.7
It also looks like you still have the postgis-geometry-2.5.0.jar in the
webapp/lib dir remove it

Dave

Re: Right version of jdbc

[Dave Cramer]

On Fri, 29 Sept 2023 at 14:22, Raivo Rebane  wrote:

> Thanks,
>
> there was really JDK 17 in use.
> I changed it to Java 1.8
> PS C:\Program Files\Apache Software Foundation\Tomcat 9.0\bin> java
> -version
> java version "1.8.0_381"
> Java(TM) SE Runtime Environment (build 1.8.0_381-b09)
> Java HotSpot(TM) 64-Bit Server VM (build 25.381-b09, mixed mode)
>
> but the same error occured :
> java.sql.SQLException: No suitable driver found for
> jdbc:postgresql://localhost:5432/mushroom_database
> at java.sql.DriverManager.getConnection(DriverManager.java:689)
> at java.sql.DriverManager.getConnection(DriverManager.java:247)
> at MushroomAPIs.AddMushrooms.doGet(AddMushrooms.java:30)
>  why  java.sql gives that error ?
>

for some reason the postgresql jar is not in the classpath.

Dave

>

Re: Right version of jdbc

[Dave Cramer]

Again, can you please post the solution so others can learn as well ?

Dave Cramer
www.postgres.rocks


On Sat, 30 Sept 2023 at 06:49, Raivo Rebane  wrote:

> Thanks for very much for help.
> It seems that I can do the test project for new job.
> In future I use help of chat.gpt
>
> Regards
> Raivo
>
> On Sat, Sep 30, 2023 at 11:15 AM  wrote:
>
>> Am 30.09.23 um 08:33 schrieb Raivo Rebane:
>> > Hi,
>> > sometimes I am lucky and don't get the old error, but sometime not.
>> >
>> > I tried to use PreparedStatement, but I got error -
>> > org.postgresql.util.PSQLException: Can't use query methods that take a
>> > query string on a PreparedStatement.
>> >  at
>> >
>> org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:145)
>> >  at MushroomAPIs.Clean.deleteAllRecordsFromTable(Clean.java:34)
>> >   and java code is -
>> >
>> > String deleteQuery = "DELETE FROM " + tableNam
>> >
>> > System.out.println(deleteQuery);
>> >
>> > PreparedStatement statement = connection.prepareStatement(deleteQuery);
>> if your statement is already prepared with query, use
>> statement.exequte(); or statement.executeQuery() without querystring;
>> if you have a new statement without query, use execute and such with
>> query string.
>> >
>> > May be it's easy for me to use normal statement ?
>> >
>> >
>> > Raivo
>> >
>> >
>> > On Sat, Sep 30, 2023 at 8:27 AM Raivo Rebane 
>> wrote:
>> [snip]
>>
>>
>> Am 30.09.23 um 09:18 schrieb Raivo Rebane:
>> > I fix previous error what was my bad knowledge,
>> > But new error occur which is related to postgres postgis jars.
>> > If You are kind to answer me more;
>> >
>> > Java code is :
>> >
>> > public static boolean CheckIsNewInMushrooms(Connection connection, Point
>> > AddLocation, String AddDescription) {
>> >
>> > boolean IsNew = true;
>> >
>> >
>> > try {
>> >
>> > String sqlQuery = "SELECT location, description FROM mushrooms";
>> >
>> > try (PreparedStatement preparedStatement = connection.prepareStatement(
>> > sqlQuery)) {
>> >
>> > ResultSet resultSet = preparedStatement.executeQuery();
>> >
>> >
>> > while (resultSet.next()) {
>> >
>> > Point point = (Point) resultSet.getObject("location");
>> >
>> > String description = resultSet.getString("description");
>> >
>> >
>> > if (AddLocation.x == point.x && AddLocation.y == point.y &&
>> AddDescription
>> > .equals(description))
>> >
>> > IsNew = false;
>> >
>> > }
>> >
>> > }
>> >
>> > } catch (SQLException e) {
>> >
>> > e.printStackTrace();
>> >
>> > }
>> >
>> >
>> > return IsNew;
>> >
>> > }
>> >
>> > and at line
>> >
>> > Point point = (Point) resultSet.getObject("location");
>> >
>> >
>> > java.lang.ClassCastException: org.postgresql.util.PGobject cannot be
>> cast
>> > to org.postgis.Point
>> >  at
>> >
>> MushroomAPIs.ProcAddMushrooms.CheckIsNewInMushrooms(ProcAddMushrooms.java:45)
>> > How to get Point from resultset ?
>> > Is it related to postgis driver ?
>> try another way to cast to Point.
>> look for the way over PGgeometry like here
>> https://postgis.net/docs/manual-3.3/ch07.html#idm3092
>> >
>> > Regards
>> > Raivo
>>
>>
>>

Re: Driver Postgresql HP-Unix

[Dave Cramer]

On Sat, 21 Oct 2023 at 05:50, Justin Clift  wrote:

> On 2023-10-19 04:45, Abelardo Erazo Lopez wrote:
> > Hi, Everyone
> >
> > I have an Oracle database Oracle 19c and I need to access a PostgreSQL
> > database 15.4  that resides on a different server. I see that one
> > alternative is to use ODBC from Oracle. However, the server where the
> > database resides is an HP-UX server and I understand that I need a
> > PostgreSQL driver manager compatible with this operating system. Can
> > you help me by indicating if there is one available, where to obtain
> > it, and if it requires a license?
>
> Any idea which version of HP-UX is running on that PostgreSQL server?
>
> Also, are you *sure* it's PostgreSQL 15.4, and not PostgreSQL 12.4?
>
> Asking because I'm not seeing version PG 15.4 in the list here, though
> PG 12.4 and 16 are:
>
>http://hpux.connect.org.uk/hppd/cgi-bin/search?term=postgresql


I do not believe we publish the binaries for HP-UX but you should be able
to build it.

Dave

Re: Right version of jdbc

[Dave Cramer]

On Fri, 29 Sept 2023 at 01:23, Raivo Rebane  wrote:

> But goes worst if I am using
> postgis-geometry-2021.1.0.jar
>
> Raivo
>
> On Fri, Sep 29, 2023 at 8:14 AM Raivo Rebane  wrote:
>
>> It goes better, if I add postgis-geometry-2.5.0.jar and Eclipse founds
>>
>> import org.postgis.Point;
>>
>>
>> Raivo
>>
>> On Fri, Sep 29, 2023 at 7:18 AM Raivo Rebane  wrote:
>>
>>> If I use this jar i give errors - Description Resource Path Location Type
>>> PGgeometry cannot be resolved to a type ProcAddMushrooms.java
>>> /KatseAPIs/src/main/java/MushroomAPIs line 22 Java Problem
>>> PGgeometry cannot be resolved to a type ProcAddMushrooms.java
>>> /KatseAPIs/src/main/java/MushroomAPIs line 46 Java Problem
>>> PGgeometry cannot be resolved to a type ProcAddMushrooms.java
>>> /KatseAPIs/src/main/java/MushroomAPIs line 46 Java Problem
>>> Point cannot be resolved to a type ProcAddMushrooms.java
>>> /KatseAPIs/src/main/java/MushroomAPIs line 14 Java Problem
>>> Point cannot be resolved to a type ProcAddMushrooms.java
>>> /KatseAPIs/src/main/java/MushroomAPIs line 35 Java Problem
>>> Point cannot be resolved to a type ProcAddMushrooms.java
>>> /KatseAPIs/src/main/java/MushroomAPIs line 47 Java Problem
>>> Point cannot be resolved to a type ProcAddMushrooms.java
>>> /KatseAPIs/src/main/java/MushroomAPIs line 47 Java Problem
>>> Point cannot be resolved to a type ProcAddMushrooms.java
>>> /KatseAPIs/src/main/java/MushroomAPIs line 83 Java Problem
>>> Point cannot be resolved to a type Utils.java
>>> /KatseAPIs/src/main/java/MushroomAPIs line 9 Java Problem
>>> Point cannot be resolved to a type Utils.java
>>> /KatseAPIs/src/main/java/MushroomAPIs line 22 Java Problem
>>>
>>> Raivo
>>>
>>> On Thu, Sep 28, 2023 at 11:06 PM Dave Cramer 
>>> wrote:
>>>
>>>> The latest version of postgis-jdbc is Maven Repository: net.postgis »
>>>> postgis-jdbc » 2021.1.0 (mvnrepository.com)
>>>> <https://mvnrepository.com/artifact/net.postgis/postgis-jdbc/2021.1.0>
>>>> Dave Cramer
>>>> www.postgres.rocks
>>>>
>>>>
>>>> On Thu, 28 Sept 2023 at 14:51, Raivo Rebane 
>>>> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> The same problem of postgres-jdbc compatibility remains and I pushed
>>>>> it to github - https://github.com/raaivore/APIexperiment.
>>>>>
>>>>> I am using Postgres 15 and Java 1.8 and Tomcat9.
>>>>> Maybe somebody can help me to solve the problem.
>>>>>
>>>>> Regards
>>>>> Raivo
>>>>>
>>>>
Sorry, I just realized I dropped the ball here. Did you ever figure this
out ?
Dave

Re: Is replacing transactions with CTE a good idea?

[Dave Cramer]

On Thu, 1 Apr 2021 at 10:50, Glen Huang  wrote:

> Hi all,
>
> From application’s standpoint, it seems using CTE saves a lot work. You no
> longer need to parse values out only to pass them back in, and only one
> round-trip to the db server.
>
> If I’m not wrong, CTE is equivalent to serializable transactions? So I
> guess the downsize is that quarries can’t be run in parallel?
>

I do not think a CTE changes the isolation level.

>
> If I decide to replace all my transaction code with CTE, will I shoot
> myself in the foot down the road?
>


Dave Cramer
www.postgres.rocks

Re: Is replacing transactions with CTE a good idea?

[Dave Cramer]

On Thu, 1 Apr 2021 at 11:09, Glen Huang  wrote:

> No, but are they equivalent to serializable transactions?
>

No, they are not.



Dave Cramer
www.postgres.rocks

>
> On Apr 1, 2021, at 11:04 PM, Dave Cramer 
> wrote:
>
> 
>
>
>
> On Thu, 1 Apr 2021 at 10:50, Glen Huang  wrote:
>
>> Hi all,
>>
>> From application’s standpoint, it seems using CTE saves a lot work. You
>> no longer need to parse values out only to pass them back in, and only one
>> round-trip to the db server.
>>
>> If I’m not wrong, CTE is equivalent to serializable transactions? So I
>> guess the downsize is that quarries can’t be run in parallel?
>>
>
> I do not think a CTE changes the isolation level.
>
>>
>> If I decide to replace all my transaction code with CTE, will I shoot
>> myself in the foot down the road?
>>
>
>
> Dave Cramer
> www.postgres.rocks
>
>

Re: Is replacing transactions with CTE a good idea?

[Dave Cramer]

CTE's don't change the isolation level. I'm not sure what you are getting
at here ?

Dave Cramer
www.postgres.rocks


On Thu, 1 Apr 2021 at 11:20, Glen Huang  wrote:

> Sorry, my mistake. I misunderstood serializable. Are queries in a CTE
> equivalent to those in a repeatable read transaction?
>
> On Apr 1, 2021, at 11:10 PM, Dave Cramer 
> wrote:
>
> 
>
>
> On Thu, 1 Apr 2021 at 11:09, Glen Huang  wrote:
>
>> No, but are they equivalent to serializable transactions?
>>
>
> No, they are not.
>
>
>
> Dave Cramer
> www.postgres.rocks
>
>>
>> On Apr 1, 2021, at 11:04 PM, Dave Cramer 
>> wrote:
>>
>> 
>>
>>
>>
>> On Thu, 1 Apr 2021 at 10:50, Glen Huang  wrote:
>>
>>> Hi all,
>>>
>>> From application’s standpoint, it seems using CTE saves a lot work. You
>>> no longer need to parse values out only to pass them back in, and only one
>>> round-trip to the db server.
>>>
>>> If I’m not wrong, CTE is equivalent to serializable transactions? So I
>>> guess the downsize is that quarries can’t be run in parallel?
>>>
>>
>> I do not think a CTE changes the isolation level.
>>
>>>
>>> If I decide to replace all my transaction code with CTE, will I shoot
>>> myself in the foot down the road?
>>>
>>
>>
>> Dave Cramer
>> www.postgres.rocks
>>
>>

Re: Is replacing transactions with CTE a good idea?

[Dave Cramer]

On Thu, 1 Apr 2021 at 15:39, Bruce Momjian  wrote:

> On Thu, Apr  1, 2021 at 11:24:48AM -0400, Dave Cramer wrote:
> > CTE's don't change the isolation level. I'm not sure what you are
> getting at
> > here ?
>
> I think what he/she means here is that all queries in a CTE use a single
> snapshot, meaning you don't see changes by commits that happen between
> queries that are part of the same CTE.  If you were running the queries
> separately in read committed mode, you would see those changes, but you
> would not see them in repeatable read or serializable transaction mode.
>

OK, that makes sense, but I think it is wrong minded to think that this
absolves one of taking isolation into account.

When you make the first read you will still have to deal with all of the
isolation issues


Dave Cramer
www.postgres.rocks

Re: Is replacing transactions with CTE a good idea?

[Dave Cramer]

On Sun, 4 Apr 2021 at 09:12, Bruce Momjian  wrote:

> On Sun, Apr  4, 2021 at 08:35:41AM -0400, Dave Cramer wrote:
> >
> >
> > On Thu, 1 Apr 2021 at 15:39, Bruce Momjian  wrote:
> >
> > On Thu, Apr  1, 2021 at 11:24:48AM -0400, Dave Cramer wrote:
> > > CTE's don't change the isolation level. I'm not sure what you are
> getting
> > at
> > > here ?
> >
> > I think what he/she means here is that all queries in a CTE use a
> single
> > snapshot, meaning you don't see changes by commits that happen
> between
> > queries that are part of the same CTE.  If you were running the
> queries
> > separately in read committed mode, you would see those changes, but
> you
> > would not see them in repeatable read or serializable transaction
> mode.
> >
> > OK, that makes sense, but I think it is wrong minded to think that this
> > absolves one of taking isolation into account.
> >
> > When you make the first read you will still have to deal with all of the
> > isolation issues
>
> I have no idea what you are saying above.  Why is a SELECT-only CTE not
> the same as a repeatable-read SELECT-only multi-statement transaction?
> Are you saying that a SELECT in a CTE doesn't do SELECT FOR UPDATE?


No, but where is this documented ?


Dave Cramer
www.postgres.rocks

Re: Is replacing transactions with CTE a good idea?

[Dave Cramer]

On Mon, 5 Apr 2021 at 14:18, Bruce Momjian  wrote:

> On Sun, Apr  4, 2021 at 10:02:20AM -0400, Dave Cramer wrote:
> > On Sun, 4 Apr 2021 at 09:12, Bruce Momjian  wrote:
> > > OK, that makes sense, but I think it is wrong minded to think that
> this
> > > absolves one of taking isolation into account.
> > >
> > > When you make the first read you will still have to deal with all
> of the
> > > isolation issues
> >
> > I have no idea what you are saying above.  Why is a SELECT-only CTE
> not
> > the same as a repeatable-read SELECT-only multi-statement
> transaction?
> > Are you saying that a SELECT in a CTE doesn't do SELECT FOR UPDATE?
> >
> >
> > No, but where is this documented ?
>
> Well, every query runs with a single snapshot, even WITH queries.  We do
> document how non-SELECT WITH visibility is handled:
>
> https://www.postgresql.org/docs/13/sql-select.html
>
> The primary query and the WITH queries are all (notionally)
> executed at
> the same time. This implies that the effects of a data-modifying
> statement in WITH cannot be seen from other parts of the query,
> other
> than by reading its RETURNING output. If two such data-modifying
> statements attempt to modify the same row, the results are
> unspecified.
>
> A key property of WITH queries is that they are normally evaluated
> only
> once per execution of the primary query, even if the primary query
> refers to them more than once. In particular, data-modifying
> statements
> are guaranteed to be executed once and only once, regardless of
> whether
> the primary query reads all or any of their output.
>
>
I think we are in agreement. My point was that WITH queries don't change
the isolation semantics.

I was pretty sure we didn't do a SELECT FOR UPDATE which would imply a lock.


Dave Cramer
www.postgres.rocks

Re: bottom / top posting

[Dave Cramer]

> The reason why the old-timers around here are sticky about this is that
> we believe we are writing for the mailing list archives.  Gmail-style
> quoting is indeed the appropriate amount of effort for throwaway threads
> that only a few people will read and (probably) none of them will consult
> again later.  But for threads that (a) will be read by hundreds or
> thousands of people right now, and (b) will be searched for in the project
> archives some unknowable number of times in future, it is worth spending
> extra effort to make the conversation easy to follow.
>
>

So on this page PostgreSQL mailing lists
 there is no mention of  top/bottom
posting,
One has to go to Mailing Lists - PostgreSQL wiki
 to find the etiquette.

Seems to me that adding a link to the wiki on the first link makes sense as
well as possibly adding it to the subscription message

Dave

>
>

Re: Notify When Streaming Replication Failover Occurred (slave promoted to master).

[Dave Cramer]

On Sun, 13 Jun 2021 at 19:32, Avi Weinberg  wrote:

> I need to take actions when Postgres streaming replication failover
> occurred.  Is there a way to be notified when Postgres slave becomes
> master?  If no such notification possible, what is the best way to actively
> monitor which server is master.  My Postgres  is running inside Kubernetes
> and the HA is managed by Patroni.
>
>
>

On the surface this seems like a great idea, however contemplating this a
bit more; where would the signal come from ? Currently the only option I
can think of is to send a NOTIFY.
It seems to me that the Patroni code is a better place to do this from.

Dave

>

Re: Logical replication from Rds into on-premise

[Dave Cramer]

Dave Cramer
www.postgres.rocks


On Mon, 26 Jul 2021 at 18:43, Cory Nemelka  wrote:

>
> On Mon, Jul 26, 2021 at 12:06 PM Rama Krishnan 
> wrote:
>
>> Hi all,
>>
>> I have a postgres server on Aws RDS no i want to replicate the data or
>> logical replication into the on-premise server. I have gone through DMS
>> provides the service buy it pricing was high. Do we have any option or
>> method to achieve this?
>>
>> Thanks
>>
>> RamaKrishnan
>>
>
> I would start here:
> https://www.postgresql.org/docs/12/sql-createpublication.html
>
> make sure all your tables have primary keys. :D
>

Does RDS allow logical replication

Re: Issue with PSQL JDBC Driver Null Pointer

[Dave Cramer]

On Thu, 29 Jul 2021 at 11:04, Bhavesh Mistry 
wrote:

> Hello,
>
> I am getting an NPE exception in the JDBC driver 42.2.23.
>
> When the alias is null, NPE results.  Is it possible to handle null value
> ? I have filled a bug against RCA
> https://github.com/vladmihalcea/hibernate-types/issues/335.  But, I
> thought that PSQL Driver can handle it more gracefully.
>
> Caused by: java.lang.NullPointerException: null
> at 
> org.postgresql.jdbc.TypeInfoCache.getTypeForAlias(TypeInfoCache.java:675)
> at 
> org.postgresql.jdbc.TypeInfoCache.getPGArrayType(TypeInfoCache.java:519)
> at 
> org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1375)
> at 
> org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1399)
> at 
> org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
> at 
> org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
> at 
> org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
> at 
> com.vladmihalcea.hibernate.type.array.internal.ArraySqlTypeDescriptor$1.doBind(ArraySqlTypeDescriptor.java:36)
> at 
> org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:73)
> at 
> org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:276)
> at 
> org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:271)
> at 
> org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:39)
> at 
> org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2995)
> at 
> org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3292)
> at 
> org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3828)
> at 
> org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
> at 
> org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
> at 
> org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
> at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
> at 
> org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
> at 
> org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
> at 
> org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
> at 
> org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
> at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
> at 
> org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)
> at 
> org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)
> at 
> org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)
> at 
> org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
> at 
> org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
> at 
> org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
> at 
> org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
> at 
> org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
> ... 123 common frames omitted
>
> public String getTypeForAlias(String alias) {
> String type = typeAliases.get(alias);
> if (type != null) {
>   return type;
> }
> if (alias.indexOf('"') == -1) {   *// THIS LINE THROWS NPE SINCE alias is 
> null ** *
>   type = typeAliases.get(alias.toLowerCase());
>   if (type != null) {
> return type;
>   }
> }
> return alias;
>   }
>
>
> It would appear that improv: type alias handling in TypeInfoCache by
bokken · Pull Request #1986 · pgjdbc/pgjdbc (github.com)

should be backpatched into 42.2.24 which should solve this problem


Thanks,
Dave

>
> --
> Thanks,
>
> Bhavesh
>
>

Re: Issue with PSQL JDBC Driver Null Pointer

[Dave Cramer]

On Thu, 29 Jul 2021 at 15:44, Bhavesh Mistry 
wrote:

> Hi Dave,
>
> It still does not address the NPE issue.  If an alias is NULL. What
> should be the behavior?
>
>
> public String getTypeForAlias(String alias) {
> String type = TYPE_ALIASES.get(alias);
> if (type != null) {
> return type;
> }
> type = TYPE_ALIASES.get(alias.toLowerCase()) *// NPE STILL HERE*;
> if (type == null) {
> type = alias;
> }
> //populate for future use
> TYPE_ALIASES.put(alias, type);
> return type;
> }
>

Very good question. I guess we should return null in this case.

I'll fix that

Dave

Re: move data repository : server does not restart (windows 10)

[Dave Cramer]

It would be helpful to see the logs from the failed start.

Dave Cramer
www.postgres.rocks


On Thu, 12 Aug 2021 at 07:59, celati Laurent 
wrote:

> Good morning,
>
> I want to move the data folder from default path to another drive/path.
> I made an attempt thaks to this link ressource :
>
>
> https://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows
>
> I checked the 4 requirements. But the postgres server does not restart.
>
>
> Could you tell me the best way to do this action please ?
>
> Thanks a lot.
>
>

Re: postgres disconnects on master after setting up replication

[Dave Cramer]

Dave Cramer
www.postgres.rocks


On Mon, 16 Aug 2021 at 12:32, Andy Hall  wrote:

> thanks for the very clear explanation much appreciated shall take this
> back to the team.
>
> On Mon, 16 Aug 2021 at 17:14, Tom Lane  wrote:
> >
> > Andy Hall  writes:
> > > we have an odd issue where a java app using JDBC which has been
> > > working fine has suddenly started disconnecting after we setup
> > > replication from the database it was connecting to ( which is now the
> > > primary in a replicating pair ) with errors such as the following...
> >
> > > CLIENT SIDE: DBNAME/logs/20210815-200601.20966.log:SessionManagerImpl
> > > WARN 20210816-00:01:00.252 - A session lost connection with the
> > > database. Disabling all sessions
> > > SERVER SIDE: 2021-08-16 01:48:27 BST FATAL: unsupported frontend
> > > protocol 1234.5680: server supports 1.0 to 3.0
> >
> > Hmm.  That "protocol version" is a GSS request:
> >
> > #define NEGOTIATE_GSS_CODE PG_PROTOCOL(1234,5680)
> >
> > So
> >
> > 1. You are using a server version that predates our GSS support.
> >
> > 2. On the other hand, the client knows GSS and is trying to use it.
> > The client should fall back to a non-GSS connection upon receiving
> > this error response.  But ...
> >
> > 3. Something --- it's not very clear what --- is seeing the error
> > and going into an unwarranted panic.
> >
> > I'd try to figure out what's issuing the "Disabling all sessions"
> > message and then filing a complaint with the authors of that.
>

What version of the JDBC driver are you using? There was one version that
unfortunately blindly sent out the GSS request.
That has been rectified in later versions, I believe somewhere around
42.2.20 or so.

Although as Tom mentioned the driver does know how to deal with this,
however there are some cloud providers that chose to just drop the
connection

Dave

Re: Re: Timestamp with vs without time zone.

[Dave Cramer]

On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer  wrote:

> On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
> > That's all true and I won't argue about the madness that is timezones
> > in the world. I am simply thinking it would be some sort of a struct
> > like thing which would store the numerical value of the time stamp and
> > also the time zone that time was recorded in.  Presumably everything
> > else is an insane calculation from there. What was the offset on that
> > day? I guess it depends on the daylight savings time. What would the
> > conversion to another time zone be? That would depend on the DST
> > settings on that day in both places.
>
> Yes, but HOW IS THAT TIME ZONE STORED?
>
> As a user you can say "I don't care, just make it work somehow".
>
> But as a developer you have to decide on a specific way. And as a
> database developer in particular you would have to choose a way which
> works for almost everybody.
>
> And that's the problem because ...
>
> > Mankind can't agree on what side of the road to drive on, what the
> > electrical voltage should be at the wall, what those plugs should be,
> > how you should charge your phone or anything else for that matter
>
> ... people have different needs and it would be difficult to satisfy
> them all.
>
> Simply storing an offset from UTC is simple, fast, doesn't take much
> space - but it would be almost as misleading as the current state. A
> simple offset is not a time zone.
>
> Storing the IANA timezone names (e.g. 'Europe/Vienna') would store an
> identifier for what most people think of as a time zone - but that takes
> a lot of space, it needs a lookup for almost any operation and worst of
> all, you couldn't index such a column (at least not with a btree index)
> because the comparison functions aren't stable.
>
> You could use a numeric indentifier instead of the name, that would take
> less space but wouldn't solve the other problems (and add the problem
> that now you have just added another mapping which you need to maintain).
>
> There are other ways, but I'm sure they all have some pros and some
> cons. None will be perfect.
>
> So I don't think there is an obvious (or even non-obvious, but clearly
> good) way for the PostgreSQL developers to add a real "timestamp with
> timezone" type.
>
> As an application developer however, you can define a compound type (or
> just use two or three columns together) which satisfies the needs of
> your specific application.
>
> > It's just that the phrase "timestamp with time zone" would seem to
> > indicate the time zone is stored somewhere in there.
>
> I absolutely agree. Calling a type which doesn't include a timezone
> "timestamp with timezone" is - how do I put this? - more than just
> weird. "timestamp without timezone" should be called "local timestamp
> with unspecified timezone" and "timestamp with timezone" should be
> called "global timestamp without timezone". However, those aren't SQL
> names.
>
>
I would say this is a perspective thing. It's a timestamp with a time zone
from the client's perspective.


> Dave Cramer
> www.postgres.rocks
>
>

Re: Re: Timestamp with vs without time zone.

[Dave Cramer]

On Tue, 21 Sept 2021 at 13:40, Peter J. Holzer  wrote:

> On 2021-09-21 13:34:21 -0400, Dave Cramer wrote:
> > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer  wrote:
> > On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
> > > It's just that the phrase "timestamp with time zone" would seem to
> > > indicate the time zone is stored somewhere in there.
> >
> > I absolutely agree. Calling a type which doesn't include a timezone
> > "timestamp with timezone" is - how do I put this? - more than just
> > weird. "timestamp without timezone" should be called "local timestamp
> > with unspecified timezone" and "timestamp with timezone" should be
> > called "global timestamp without timezone". However, those aren't SQL
> > names.
> >
> >
> >
> > I would say this is a perspective thing. It's a timestamp with a time
> > zone from the client's perspective.
>
> I disagree. When I read back the value the original timezone is lost. So
> it clearly DOESN'T store the timestamp WITH the timezone.
>
> I never said it stored the timezone.  I said that it has a timezone.


Dave Cramer
www.postgres.rocks

>
>

Re: Pg_hba.conf problem after unexpected IP change

[Dave Cramer]

On Fri, 12 Nov 2021 at 06:12, Yessica Brinkmann 
wrote:

> Hello.
> Thank you very much for your answer.
> Yes, I restart the server after making the changes.
> Regards,
> Yessica Brinkmann
>

Check the logs for postgres to make sure there were no errors in your new
configuration.

Dave Cramer
www.postgres.rocks

>
>>

Re: Performance question about using autosave=always and cleanupSavepoints=true

[Dave Cramer]

On Tue, 16 Nov 2021 at 11:09, Joel Rabinovitch 
wrote:

> Hi,
>
>
>
> Currently, our application supports SQL Server databases and Oracle
> schemas. We are updating our application to support PostgreSQL schemas. We
> are using version 13.4 of PostgreSQL.
>
>
>
> Our application is written in Java and connects to PostgreSQL schemas
> using JDBC.
>
>
>
> In our framework, we have logic that attempts to retry an SQL statement
> when a row is locked. It does this up to 999 times before it gives up.
>
>
>
> When this logic is connected using PostgreSQL schemas, we receive error
> messages similar to the ones below:
>
>
>
> org.postgresql.util.PSQLException: ERROR: could not obtain lock on row in
> relation "my_table"
>
>
>
> org.postgresql.util.PSQLException: ERROR: current transaction is aborted,
> commands ignored until end of transaction block
>
>
>
> A Google search led to the following StackOverflow post:
>
>
>
>
> hxxps://stackoverflow.com/questions/10399727/psqlexception-current-transaction-is-aborted-commands-ignored-until-end-of-tra
> 
> (Replace hxxps by https to access it).
>
>
>
> which indicates that PostgreSQL refuses to execute valid SQL statements on
> the same connection after an invalid SQL statement is executed.
>
>
>
> To get around this, we have added the following arguments to the JDBC
> connection string:
>
>
>
> autosave=always&cleanupSavepoints=true
>
>
>
> This resolves the problem, but the concern we are having is in terms of
> performance.
>
>
>
> Based on the following link:
>
>
>
> hxxps://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/
> 
>  (Replace
> hxxps by https to access it), it indicates that using this flag can have a
> serious impact on performance. However, it doesn’t really provide an
> alternative way to get around it.
>
>
>
> In terms of real-world experience, what are the disadvantages of using the
> autosave and cleanupSavepoints arguments?
>
>
>
> Would it be better to manually set the savepoint in our application code
> and then rollback if there is an error. This would probably have to be done
> for each attempt we try to lock a record, so I am not sure if there is any
> advantage of doing this.
>
>
>
Adding the autosave and cleanupSavepoints is a workaround that allows you
to use your current code without changes.  It does come with a performance
penalty as you have discovered. I'm afraid the only way to really address
the performance is to deal with the first problem correctly by retrying
transactions when they fail instead of using savepoints.

Regards,
Dave

Re: How to confirm the pg_hba.conf service is correctly working

[Dave Cramer]

.255.255.255,md5,,
> 150,host,{product},{product_agent},10.33.132.34,255.255.255.255,md5,,
> 153,host,{product},{product_dba},10.20.16.101,255.255.255.255,md5,,
> 154,host,{product},{product_dba},10.20.16.102,255.255.255.255,md5,,
> 155,host,{product},{product_dba},10.20.16.103,255.255.255.255,md5,,
> 156,host,{product},{product_dba},10.20.16.104,255.255.255.255,md5,,
> 157,host,{product},{product_dba},10.20.16.105,255.255.255.255,md5,,
> 161,host,{product},{dbcheck},10.34.21.118,255.255.255.255,md5,,
> 165,host,{product},{product_dba},10.3.10.2,255.255.255.255,md5,,
> 168,host,{product},{product_dba},10.3.10.13,255.255.255.255,md5,,
>
>
>
>

Hmmm for some reason I did not reply to the list.

At any rate.

Your original post stated that you only had

host   VJ   VJ_USER   10.10.10.1/32 md5

in the pg_hba.conf file.

However the result of the select is considerably more ?


Dave Cramer

>

Re: a very naive question about table names in Postgres

[Dave Cramer]

On Fri, 31 Dec 2021 at 16:25, Paul Jungwirth 
wrote:

> On 12/31/21 12:27 PM, Martin Mueller wrote:
> > I know that on my Mac the tables are  kept in the  data directory
> > /Users/martinmueller/Library/Application Support/Postgres/var-13.  If I
> > go there I see that internally the tables have numbers.  Somewhere in
> > the system there must be a table that maps the name I gave to table X
> > to the numerical  inside the database.
> >
> > Where is that mapping and how can I query it?  On what page of the
> > documentation do I find the answer?
>
> Dear Prof. Mueller,
>
>

Everything you ever wanted to know is here

PostgreSQL: Documentation: 14: Chapter 52. System Catalogs


Enjoy the journey

Dave

Re: How to debug incomplete message on postgres 12.3 docker

[Dave Cramer]

Dave Cramer
www.postgres.rocks


On Sun, 16 Jan 2022 at 10:13, R Batchen  wrote:

>
> Hello,
>
> I hope i reached the right mailing list, i have psql 12.3 container that
> is the db of apache guacamole container is connected to,
> the apache guacamole stopped working and i started to get this message on
> the postgres:
>
> LOG: incomplete message from client
>
> How can I debug it?
>
> i want to see when and who did the translation and what is the incomplete 
> query ? because i did approached guacamole mailing list and they said my DB 
> is closing connection in the middle of the transaction
>
> and its psql issue and i am not able to debug it because i only have this 
> incomlit msg.
>
> *Is there a way to catch this transaction?*
>
> full logs :
>
> postgres_gqm_1| 2022-01-13T19:32:24.187916615Z 2022-01-13 19:32:24.187 
> UTC [4394] LOG:  incomplete message from client
>
> guacamole_1   | 2022-01-13T19:32:24.214231454Z 19:32:24.214 
> [http-nio-8080-exec-6] ERROR o.a.g.rest.RESTExceptionMapper - Unexpected 
> internal error:
> guacamole_1   | 2022-01-13T19:32:24.214398980Z ### Error rolling back 
> transaction.  Cause: org.postgresql.util.PSQLException: This connection has 
> been closed.
> guacamole_1   | 2022-01-13T19:32:24.214445260Z ### Cause: 
> org.postgresql.util.PSQLException: This connection has been closed.
>
>
>  docker file :
>
> postgres_gqm:
>
> image: postgres:12.3
> environment:
>   PGDATA: /var/lib/postgresql/data/guacamole
>   POSTGRES_DB: db
>   POSTGRES_PASSWORD: XXX
>   POSTGRES_USER: user
> restart: always
> volumes:
>   - ./init:/docker-entrypoint-initdb.d:ro
>   - ./data:/var/lib/postgresql/data:rw
> logging:
>   driver: "json-file"
>   options:
> max-size: "750m"
>
>
> My guess is that the container dropped the connection somehow. Either
network error or application error. I would look at the logs on the
container.

There is no way to debug this on the postgres side (that I am aware of)

Dave

Re: psycopg2 and java gssapi questions

[Dave Cramer]

On 21 December 2017 at 05:27, Magnus Hagander  wrote:

>
>
> On Wed, Dec 20, 2017 at 8:42 PM, Mike Feld  wrote:
>
>> Is it possible to authenticate with Postgres from a standalone
>> application using gssapi? In other words, I am able to authenticate with
>> Postgres when a human has logged in to either Windows or Linux and
>> generated a ticket, but is it possible for say a Django site or Java
>> application running on some server somewhere to authenticate with Postgres
>> using gssapi? I realize that psycopg2 has a connection parameter for
>> “krbsrvname”, but how does it generate a ticket? Is this the only
>> alternative to secure authentication since Postgres does not support secure
>> ldap (ldaps)?
>>
>
> Sure it is.
>
> libpq won't generate the initial ticket, though. The way to do it is to
> have your django or whatever application run "kinit" for the user before it
> starts. This will request a TGT, and the ticket will be present in that
> users environment, and will be used by the libpq client. (it might look
> slightly different for a Java client, but the principle is the same)
>
>
JDBC docs on GSSAPI can be found
https://jdbc.postgresql.org/documentation/head/connect.html


Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: Postgres install

[Dave Cramer]

On 5 February 2018 at 19:01, Benjamin Scherrey 
wrote:

> One option you should seriously consider is using Docker to containerize
> your postgres installation. The installation thinks it has root access but
> you don't need any rights beyond the standard user role that way you can
> use the standard builds without changes. This assumes you're running under
> Linux.
>
>   - - Ben Scherrey
>
> On Feb 6, 2018 9:42 AM, "Azimuddin Mohammed"  wrote:
>
>> Hello,
>> I need to install postgres without root privileges. I have a postgres
>> user in our companies AD.
>> Below are my questions n concerns:
>>
>> 1. Is there a website you will refer other the postgres documentation,
>> the one on postgres website is confusing
>>
>> What is confusing ?


> 2. What is the default location of postgress install from a source code?
>> i.e if I run rmp -ivh  where will it install by default? can this
>> be changed ?
>>
> How are you going to do that without root ?

>
>> 3. Important parameters I need to change  ? I am planning to have
>> different drive location for my data directory or any important parameter,
>> please let me know.
>>
>> There are lots, but changing the data dir is not particularly difficult.
https://www.postgresql.org/docs/9.3/static/runtime-config-file-locations.html

Dave Cramer

da...@postgresintl.com
www.postgresintl.com




>
>> Thanks in Advance
>>
>> --
>>
>> Regards,
>> Azim
>>
>>