Re: WARNING: could not flush dirty data: Function not implemented

[Michael Paquier]

On Sun, Sep 02, 2018 at 04:31:18PM -0700, Andres Freund wrote:
> Please note that nobody has verified that postgres works correctly via
> the emulation stuff MS is doing.  There is a native version of postgres
> for windows however, and that is tested (and exercised by a lot of
> installations).

If there are folks willing to put enough effort in getting this to work,
it could work, assuming that a buildfarm animal is able to get down this
road.  From what I can see on this thread we are not yet at that stage
though.
--
Michael


signature.asc
Description: PGP signature

Re: WARNING: could not flush dirty data: Function not implemented

[Tim Cross]

Tom Lane  writes:

> Ravi Krishna  writes:
>>> Whee ... so you get to cope with all the bugs/idiosyncrasies of three
>>> operating system layers, not just one.  I concur that running Postgres
>>> in the underlying Windows O/S is probably a much better idea.
>
>> Me too, but this is purely for learning and I am much more use to Linux 
>> stack then ... gasp Windows :-)
>
> Hmm, so maybe you should install Ubuntu as the native O/S, and when
> you need Windows, run it inside a VM?
>
>   regards, tom lane

This is what I do and it works well except.

If you don't run the windows VM very often (like me), when you do, start
it before lunch or that next long meeting. The updates will grind things
to a crawl. When you run every day or fairly frequently, you don't
notice them, but if you only run once every 4+ weeks, it can have a big
impact and take ages.

Linux as the host and using the VM is still better than the weird
idiosyncrasies of Windows as the main workstation client (IMO). Where
office policy has insisted on Windows as the workstation, I have had
reasonable success with running virtualbox with Linux, though these
days, the Windows environment is often too locked down to allow
this. I've not yet experimented with the virtual linux layer in w10. 

-- 
Tim Cross

Re: WARNING: could not flush dirty data: Function not implemented

[Tim Cross]

Tom Lane  writes:

> Andres Freund  writes:
>> On 2018-09-02 19:29:49 -0400, Tom Lane wrote:
>>> If this is on Ubuntu, I don't understand why you're talking
>>> about Windows.
>
>> The OP said "Ubuntu 18.04 as Windows bash" - so I assume this is
>> postgres compiled as a linux binary is running on MS's new-ish linux
>> emulation.
>
> Whee ... so you get to cope with all the bugs/idiosyncrasies of three
> operating system layers, not just one.

That comment has made my day - thanks Tom!


-- 
Tim Cross

Re: Sv: Re: How to get shorter SERVER_VERSION in psql-prompt?

[Christoph Moench-Tegeder]

## Rob Sargent (robjsarg...@gmail.com):

> > Ugh.  (So this is coming from "configure --with-extra-version" stuff)

> Does that also diddle the value of "server_version_num"?

No, that's still integer-format (it's unchanged and you can cast it
straight into INTEGER).

Gruss,
Christoph

-- 
Spare Space.

Sv: Re: Sv: Re: How to get shorter SERVER_VERSION in psql-prompt?

[Andreas Joseph Krogh]

På mandag 03. september 2018 kl. 23:43:46, skrev Rob Sargent <
robjsarg...@gmail.com >:


 On 09/03/2018 03:42 PM, Alvaro Herrera wrote:
 > On 2018-Sep-03, Andreas Joseph Krogh wrote:
 >
 >> select setting as server_version from pg_settings where name =
 >> 'server_version';
 >>   ┌──┐
 >>   │  server_version  │
 >>   ├──┤
 >>   │ 10.5 (Ubuntu 10.5-1.pgdg18.04+1) │
 >>   └──┘
 > Ugh.  (So this is coming from "configure --with-extra-version" stuff)
 >
 > I guess you could just split it out at the first whitespace ...
 >
 Does that also diddle the value of "server_version_num"?
 
No:
show server_version_num; 
 ┌┐
 │ server_version_num │
 ├┤
 │ 15 │
 └┘
 (1 row)
  
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: Sv: Re: How to get shorter SERVER_VERSION in psql-prompt?

[Rob Sargent]

On 09/03/2018 03:42 PM, Alvaro Herrera wrote:

On 2018-Sep-03, Andreas Joseph Krogh wrote:


select setting as server_version from pg_settings where name =
'server_version';
  ┌──┐
  │  server_version  │
  ├──┤
  │ 10.5 (Ubuntu 10.5-1.pgdg18.04+1) │
  └──┘

Ugh.  (So this is coming from "configure --with-extra-version" stuff)

I guess you could just split it out at the first whitespace ...


Does that also diddle the value of "server_version_num"?

Re: Sv: Re: How to get shorter SERVER_VERSION in psql-prompt?

[Alvaro Herrera]

On 2018-Sep-03, Andreas Joseph Krogh wrote:

> select setting as server_version from pg_settings where name = 
> 'server_version';
>  ┌──┐
>  │  server_version  │
>  ├──┤
>  │ 10.5 (Ubuntu 10.5-1.pgdg18.04+1) │
>  └──┘

Ugh.  (So this is coming from "configure --with-extra-version" stuff)

I guess you could just split it out at the first whitespace ...

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Sv: Re: How to get shorter SERVER_VERSION in psql-prompt?

[Andreas Joseph Krogh]

På mandag 03. september 2018 kl. 23:34:48, skrev Christoph Moench-Tegeder <
c...@burggraben.net >:
## Andreas Joseph Krogh (andr...@visena.com):

 > This results in this verver_version:
 > 10.5 (Ubuntu 10.5-1.pgdg18.04+1)
 >   
 > Is it possible to adjust this somehow so it outputs only "10.5"?

 On Debian/Ubuntu, all version strings are somewhat extended.
 Luckily, with the power of SQL we're not completely helpless, so try
 this in your .psqlrc (somewhat simpler than your prompt, but you
 get the idea):

 select substring(current_setting('server_version') from '#"[^ ]+#"( +%)?' for 
'#') as short_server_ver\gset
 \set PROMPT1 '%/ %:short_server_ver: %R%# '

 Yes, that works across \c.
 
Nice, thanks!
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Sv: Re: How to get shorter SERVER_VERSION in psql-prompt?

[Andreas Joseph Krogh]

På mandag 03. september 2018 kl. 23:03:10, skrev Alvaro Herrera <
alvhe...@2ndquadrant.com >:
On 2018-Sep-03, Andreas Joseph Krogh wrote:

 > Hi all, I'm using the Ubuntu-packages and have this in my .psqlrc:
 >  
 > \set PROMPT1 '%[%033[35m%]%n@%m:%>%[%033[0m%] %:server_version:
 > %[%033[32m%]%/%[%033[0m%]%R%# '
 >   
 > This results in this verver_version:
 > 10.5 (Ubuntu 10.5-1.pgdg18.04+1)
 >   
 > Is it possible to adjust this somehow so it outputs only "10.5"?

 Well, where does that server_version come from?  Try adding this
 somewhere to .psqlrc:

 select setting as server_version from pg_settings where name = 
'server_version' \gset
 
select setting as server_version from pg_settings where name = 
'server_version';
 ┌──┐
 │  server_version  │
 ├──┤
 │ 10.5 (Ubuntu 10.5-1.pgdg18.04+1) │
 └──┘
 (1 row)
  
I use packages from http://apt.postgresql.org/pub/repos/apt/
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: How to get shorter SERVER_VERSION in psql-prompt?

[Christoph Moench-Tegeder]

## Andreas Joseph Krogh (andr...@visena.com):

> This results in this verver_version:
> 10.5 (Ubuntu 10.5-1.pgdg18.04+1)
>   
> Is it possible to adjust this somehow so it outputs only "10.5"?

On Debian/Ubuntu, all version strings are somewhat extended.
Luckily, with the power of SQL we're not completely helpless, so try
this in your .psqlrc (somewhat simpler than your prompt, but you
get the idea):

select substring(current_setting('server_version') from '#"[^ ]+#"( +%)?' for 
'#') as short_server_ver\gset
\set PROMPT1 '%/ %:short_server_ver: %R%# '

Yes, that works across \c.

Happy prompting,
Christoph

-- 
Spare Space.

Re: How to get shorter SERVER_VERSION in psql-prompt?

[Alvaro Herrera]

On 2018-Sep-03, Andreas Joseph Krogh wrote:

> Hi all, I'm using the Ubuntu-packages and have this in my .psqlrc:
>  
> \set PROMPT1 '%[%033[35m%]%n@%m:%>%[%033[0m%] %:server_version: 
> %[%033[32m%]%/%[%033[0m%]%R%# '
>   
> This results in this verver_version:
> 10.5 (Ubuntu 10.5-1.pgdg18.04+1)
>   
> Is it possible to adjust this somehow so it outputs only "10.5"?

Well, where does that server_version come from?  Try adding this
somewhere to .psqlrc:

select setting as server_version from pg_settings where name = 'server_version' 
\gset

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

How to get shorter SERVER_VERSION in psql-prompt?

[Andreas Joseph Krogh]

Hi all, I'm using the Ubuntu-packages and have this in my .psqlrc:
 
\set PROMPT1 '%[%033[35m%]%n@%m:%>%[%033[0m%] %:server_version: 
%[%033[32m%]%/%[%033[0m%]%R%# '
  
This results in this verver_version:
10.5 (Ubuntu 10.5-1.pgdg18.04+1)
  
Is it possible to adjust this somehow so it outputs only "10.5"?
 
Thanks in advance.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: very slow largeobject transfers through JDBC

[Mate Varga]

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

Re: WARNING: could not flush dirty data: Function not implemented

[Austin Drenski]

Dmitri Maziuk  wrote:
> Tom Lane  wrote:
>> Ravi Krishna  writes:
 Whee ... so you get to cope with all the bugs/idiosyncrasies of three
 operating system layers, not just one.  I concur that running Postgres
 in the underlying Windows O/S is probably a much better idea.
>>
>>> Me too, but this is purely for learning and I am much more use to Linux
stack then ... gasp Windows :-)
>>
>> Hmm, so maybe you should install Ubuntu as the native O/S, and when
>> you need Windows, run it inside a VM?
>
> Between windows 10 and ubuntu 18.04, I would take a really close look at
freebsd myself. Or at least alpine...

As a developer, I regularly work with PostgreSQL in the Windows Subsystem
for Linux (WSL). In using it for sandboxing and testing, the only notable
idiosyncrasy that I have encountered is the appearance of these warnings on
startup.

Unfortunately, workstation OS is not always a choice, but WSL has so far
offered a productive (and built-in!) option for running Linux-based tools
on Windows.

I can't imagine running a production server from WSL, but it is a
refreshingly simple way to spin up local dev databases. It would be great
to see the community take an interest in supporting PostgreSQL in WSL, if
only in the context of its use as a development tool.

--
Austin Drenski

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

Re: WARNING: could not flush dirty data: Function not implemented

[Dmitri Maziuk]

On Mon, 03 Sep 2018 09:58:57 -0400
Tom Lane  wrote:

> Ravi Krishna  writes:
> >> Whee ... so you get to cope with all the bugs/idiosyncrasies of three
> >> operating system layers, not just one.  I concur that running Postgres
> >> in the underlying Windows O/S is probably a much better idea.
> 
> > Me too, but this is purely for learning and I am much more use to Linux 
> > stack then ... gasp Windows :-)
> 
> Hmm, so maybe you should install Ubuntu as the native O/S, and when
> you need Windows, run it inside a VM?
 
Between windows 10 and ubuntu 18.04, I would take a really close look at 
freebsd myself. Or at least alpine...

-- 
Dmitri Maziuk 

Re: very slow largeobject transfers through JDBC

[Mate Varga]

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


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

Re: very slow largeobject transfers through JDBC

[Mate Varga]

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 ?


> 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: timestamp arithmetics in C function

[Lutz Gehlen]

Hello all,

unfortunately, I have so far not received a reply to my question 
below. I am well aware that no one has an obligation to reply; I was 
just wondering whether I phrased my question badly or whether there 
is anything else I could do to improve it.

Thanks for your help and best wishes,
Lutz


On Friday, 10.08.2018 09:05:40 Lutz Gehlen wrote:
> Hello all,
> 
> I am trying to implement a C function that accepts a date ("date"
> in the sense of a type of information, not a postgres datatype)
> as parameter (among others) and returns a certain point in time.
> (The background is to calculate the time of dawn and dusk at the
> given date.) Ideally, I would like to accept a timestamp value
> and return another timestamp as result. I have implemented the
> function, but I would like to ask advice on whether my
> implementation is the recommended way to achieve this.
> 
> To get started - since this is my first attempt at a C function in
> postgres - I implemented a function that accepts the date as
> three separate int32 values for year, month, and day and returns
> the time of dawn as a float8 for the minutes since midnight (this
> is what the implemented algorithm internally returns, anyway):
> 
> 
> PG_FUNCTION_INFO_V1(dawn_utc);
> 
> Datum dawn_utc(PG_FUNCTION_ARGS) {
>   float8 lat  = PG_GETARG_FLOAT8(0);
>   float8 lon  = PG_GETARG_FLOAT8(1);
>   int32  year = PG_GETARG_INT32(2);
>   int32  month= PG_GETARG_INT32(3);
>   int32  day  = PG_GETARG_INT32(4);
>   float8 solar_depression = PG_GETARG_FLOAT8(5);
> 
>   // postgres-independent computation goes here
>   float8 dawn_utc = calc_dawn_utc
> (lat, lon, year, month, day, solar_depression);
> 
>   PG_RETURN_FLOAT8(dawn_utc);
> }
> 
> 
> This works fine. However, it would be more convenient if the
> function would accept a date or timestamp value and return a
> timestamp. So I modified the first part of the function like
> this, based on code snippets I found in the postgres source code:
> 
> 
> PG_FUNCTION_INFO_V1(dawn_utc);
> 
> Datum dawn_utc(PG_FUNCTION_ARGS) {
>   float8 lat  = PG_GETARG_FLOAT8(0);
>   float8 lon  = PG_GETARG_FLOAT8(1);
>   Timestamp timestamp = PG_GETARG_TIMESTAMP(2);
>   float8 solar_depression = PG_GETARG_FLOAT8(3);
> 
>   struct pg_tm tt;
>   struct pg_tm *tm = 
>   fsec_t   fsec;
> 
>   if (timestamp2tm(timestamp, NULL, tm, , NULL, NULL) != 0)
> ereport(ERROR,
> (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
>  errmsg("timestamp out of range")));
> 
>   // postgres-independent computation goes here
>   float8 dawn_utc = calc_dawn_utc
> (lat, lon, tm->tm_year, tm->tm_mon, tm->tm_mday,
> solar_depression;
> 
> 
> For the second part of the function, I now have to add the
> calculated number of minutes to the date portion of the timestamp
> variable. One has to be aware that depending on the geographic
> location dawn_utc can possibly be negative or larger than 1440
> (i.e. 24h). I am not sure whether I should construct an interval
> value from the number of minutes and add that to the timestamp. I
> have not figured out how to do this, but decided to calculate a
> new timestamp in a more fundamental way:
> 
> 
>   tm->tm_sec  = 0;
>   tm->tm_min  = 0;
>   tm->tm_hour = 0;
>   Timestamp result;
>   if (tm2timestamp(tm, 0, NULL, ) != 0)
> ereport(ERROR,
> (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
>  errmsg("timestamp out of range")));
> 
> #ifdef HAVE_INT64_TIMESTAMP
>   /* timestamp is microseconds since 2000 */
>   result += dawn_utc * USECS_PER_MINUTE;
> #else
>   /* timestamp is seconds since 2000 */
>   result += dawn_utc * (double) SECS_PER_MINUTE;
> #endif
> 
>   PG_RETURN_TIMESTAMP(result);
> 
> 
> Again this code is based on what I found in the source code. It
> seems to work correctly (at least on my development machine), but
> I am wondering whether this is a safe and recommended way to
> achieve this result or whether it is considered bad practice to
> manipulate a timestamp on such fundamental level.
> 
> Thank you for your advice and best wishes,
> Lutz

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

[Mate Varga]

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)

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

[Mate Varga]

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: WARNING: could not flush dirty data: Function not implemented

[Tom Lane]

Ravi Krishna  writes:
>> Whee ... so you get to cope with all the bugs/idiosyncrasies of three
>> operating system layers, not just one.  I concur that running Postgres
>> in the underlying Windows O/S is probably a much better idea.

> Me too, but this is purely for learning and I am much more use to Linux stack 
> then ... gasp Windows :-)

Hmm, so maybe you should install Ubuntu as the native O/S, and when
you need Windows, run it inside a VM?

regards, tom lane

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: WARNING: could not flush dirty data: Function not implemented

[Ravi Krishna]

> 
> Whee ... so you get to cope with all the bugs/idiosyncrasies of three
> operating system layers, not just one.  I concur that running Postgres
> in the underlying Windows O/S is probably a much better idea.

Me too, but this is purely for learning and I am much more use to Linux stack 
then ... gasp Windows :-)

Anyhow so far I am seeing this warning only for create database command.  So I 
can ignore.

Re: very slow largeobject transfers through JDBC

[Mate Varga]

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: WARNING: could not flush dirty data: Function not implemented

[Tom Lane]

Andres Freund  writes:
> On 2018-09-02 19:29:49 -0400, Tom Lane wrote:
>> If this is on Ubuntu, I don't understand why you're talking
>> about Windows.

> The OP said "Ubuntu 18.04 as Windows bash" - so I assume this is
> postgres compiled as a linux binary is running on MS's new-ish linux
> emulation.

Whee ... so you get to cope with all the bugs/idiosyncrasies of three
operating system layers, not just one.  I concur that running Postgres
in the underlying Windows O/S is probably a much better idea.

regards, tom lane

Re: WARNING: could not flush dirty data: Function not implemented

[Ravi Krishna]

>That means that the linux emulation by microsoft isn't good enough.  You
>can work around it by setting checkpoint_flush_after=0 and
>wal_writer_flush_after=0.


bgwriter_flush_after = 0# measured in pages, 0 disables
backend_flush_after = 0# measured in pages, 0 disables
wal_writer_flush_after = 0  # measured in pages, 0 disables
checkpoint_flush_after = 0  # measured in pages, 0 disables

I set it as shown above and yet while create the database I get the same 
warning.

Re: very slow largeobject transfers through JDBC

[Mate Varga]

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

[Mate Varga]

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

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
>