Skip site navigation (1) Skip section navigation (2)

Re: bytea performance tweak

From: Radoslaw Smogura <rsmogura(at)softperience(dot)eu>
To: Maciek Sakrejda <msakrejda(at)truviso(dot)com>, Florian Weimer <fweimer(at)bfk(dot)de>
Cc: Andreas Schmitz <schmitz(at)occamlabs(dot)de>, Oliver Jowett <oliver(at)opencloud(dot)com>, <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: bytea performance tweak
Date: 2011-06-27 18:06:08
Message-ID: 20110627180613.7D2F6B5DCD2@mail.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-jdbc
I started with different assumption - encode everything in binary, eventually fix server. As the result, one huge barrier was closed - binary encoding  for  void type. Actually only aclitem, but it's  less important, so some 9.x servers should allow implementing of binary.

Problems are with timestamp encoding  , here you can't  send unknown, and I don't  think it will be fixed in any way.

My work with binary went on greater sea, as I added JDBC4 exceptions, support for struct types, changed how input is handled to allow to download large datums, moved to maven and removed some Java.in  

Regards,
Radek

-----Original Message-----
From: Maciek Sakrejda
Sent: 27 czerwca 2011 18:51
To: Florian Weimer
Cc: Andreas Schmitz; Oliver Jowett; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] bytea performance tweak

>> Is there a timeline for fully supporting the binary protocol?
>
> A PostgreSQL installation can contain user-defined types with a custom
> binary format, so this will not work with all databases anyway.

Having to read unknown types in text mode is not any easier if you're
going to do something interesting with them. In either case, the
protocol provides a way to read the type payload off the wire (so,
e.g., a column with a type you can't read won't "mess up" the whole
result set), but it's up to you to process those bytes. As Vitalii
stated, something like PGObject is probably the right way to handle
this.

> A way to tell PostgreSQL to always send some [types] in binary format
> would solve this, though.

(I assume you meant "types" not "times")

Yep, that would be nice. There are per-*column* output facilities in
the protocol, but not per-type. It's possible to ask the server for
the columns in a query before you commit to output types, but that
involves an extra round trip which won't be worth it most of the time
(still, it could be worth it if you're doing something that outputs a
lot of rows with far leaner binary representations for some data
types). Perhaps per-statement encoding choice might be the right
compromise, although that smells like a niche use case.

What's the current state of the binary patch? The wiki (
http://wiki.postgresql.org/wiki/JDBC-BinaryTransfer ) doesn't say.
With a brief look at the list archives, the last activity I see is a
completely new patch submitted by Radosław Smogura, but that
discussion peters out. That discussion implies we want a solution to
the "which-types-to-encode-how" problem, but realistically, if we want
that, we won't have binary support until the protocol changes to allow
per-type encoding selection. Would a polished patch with
all-or-nothing connection-flag-level binary support be acceptable?

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

pgsql-jdbc by date

Next:From: Phil ClayDate: 2011-06-27 19:02:19
Subject: Re: Connecting over UNIX domain sockets
Previous:From: LewDate: 2011-06-27 18:04:25
Subject: Re: installing postgresql jdbc driver in Mac OS X Lion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group