Thread: COPY support survey
On and off there's been discussions of getting COPY support into the JDBC driver and I produced a patch to provide a stream based interface over a year and a half ago [1]. At the time we did not commit that because we had grand plans to provide a friendly interface to users so they did not have to know the internals of how COPY actually represents data. This mostly happened off-list between Oliver and I, but the key suggestion is available here [2]. This allows you to use SQLOutput [3] calls like writeTimestamp or writeArray to directly send Java objects to the database without having to know the Postgresql specific text representation of them. This turned out to be harder and more involved than it looked and I just let the patch languish, always intending to return to it, but as time has passed I've actually had less time to dedicate to the driver instead of more. I know a number of people are already/still using the original patch I provided, but at the same time they have also (indirectly) indicated that some additional support is needed [4]. So the question is what should we do? 1) Just get the raw stream interface into the official release and let the end user deal with reading and writing the stream. That's all I'll use and I don't care about anything else. 2) Don't give up, get the friendly SQLInput/SQLOutput interface in because this is what I really want. 3) Put in the raw stream interface, but keep working to also add the friendly one. I'll understand if the interface changes and I have to modify my code a bit. Kris Jurka [1] http://archives.postgresql.org/pgsql-jdbc/2003-12/msg00186.php [2] http://www.ejurka.com/pgsql/copy/doc/sqlinputoutput.txt [3] http://java.sun.com/j2se/1.4.2/docs/api/java/sql/SQLOutput.html [4] http://archives.postgresql.org/pgsql-jdbc/2004-06/msg00165.php
I would vote for 3.I already have ported your patch over to the (near) current code and it is being used by a few people and from what I understand quite stable. If you want I can fix this up and commit it. Dave On 19-Aug-05, at 3:03 AM, Kris Jurka wrote: > > On and off there's been discussions of getting COPY support into > the JDBC driver and I produced a patch to provide a stream based > interface over a year and a half ago [1]. At the time we did not > commit that because we had grand plans to provide a friendly > interface to users so they did not have to know the internals of > how COPY actually represents data. This mostly happened off-list > between Oliver and I, but the key suggestion is available here > [2]. This allows you to use SQLOutput [3] calls like > writeTimestamp or writeArray to directly send Java objects to the > database without having to know the Postgresql specific text > representation of them. This turned out to be harder and more > involved than it looked and I just let the patch languish, always > intending to return to it, but as time has passed I've actually had > less time to dedicate to the driver instead of more. > > I know a number of people are already/still using the original > patch I provided, but at the same time they have also (indirectly) > indicated that some additional support is needed [4]. > > So the question is what should we do? > > 1) Just get the raw stream interface into the official release and > let the end user deal with reading and writing the stream. That's > all I'll use and I don't care about anything else. > > 2) Don't give up, get the friendly SQLInput/SQLOutput interface in > because this is what I really want. > > 3) Put in the raw stream interface, but keep working to also add > the friendly one. I'll understand if the interface changes and I > have to modify my code a bit. > > Kris Jurka > > [1] http://archives.postgresql.org/pgsql-jdbc/2003-12/msg00186.php > [2] http://www.ejurka.com/pgsql/copy/doc/sqlinputoutput.txt > [3] http://java.sun.com/j2se/1.4.2/docs/api/java/sql/SQLOutput.html > [4] http://archives.postgresql.org/pgsql-jdbc/2004-06/msg00165.php > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > >
Kris Jurka <books@ejurka.com> writes:
> So the question is what should we do?
> 1) Just get the raw stream interface into the official release and let the
> end user deal with reading and writing the stream. That's all I'll use
> and I don't care about anything else.
> 2) Don't give up, get the friendly SQLInput/SQLOutput interface in because
> this is what I really want.
> 3) Put in the raw stream interface, but keep working to also add the
> friendly one. I'll understand if the interface changes and I have to
> modify my code a bit.
Would it not be possible to do (1) now and leave the door open to add
(2) later without breaking existing uses of (1)? That is, I don't see
why (3) has to carry a risk of non-backwards-compatibility. Surely you
can design non-overlapping APIs for (1) and (2).
(Obviously, my vote is for (3).)
regards, tom lane
> Would it not be possible to do (1) now and leave the door open to add > (2) later without breaking existing uses of (1)? That is, I don't see > why (3) has to carry a risk of non-backwards-compatibility. Surely you > can design non-overlapping APIs for (1) and (2). > > (Obviously, my vote is for (3).) indeed, from a systems engineering viewpoint, thats the correct solution. (1) is a sort of COPY RAW function, while (2) is more java-like. OTOH, if there's no Java standard for a JDBC mechanism like (2), it becomes tougher to justify.
Kris, > I know a number of people are already/still using the original patch I > provided, but at the same time they have also (indirectly) indicated that > some additional support is needed [4]. Unless I'm mistaken, Dave Cramer re-worked your patch and there's a stable version of it already in CVS. Dave? -- Josh Berkus Aglio Database Solutions San Francisco
On Fri, 19 Aug 2005, Josh Berkus wrote: > Unless I'm mistaken, Dave Cramer re-worked your patch and there's a stable > version of it already in CVS. Dave? > I haven't seen a re-worked version and it's definitely not in CVS. Kris Jurka
Kris, > > Unless I'm mistaken, Dave Cramer re-worked your patch and there's a > > stable version of it already in CVS. Dave? > > I haven't seen a re-worked version and it's definitely not in CVS. Huh. We're using it in production based on a patch provided by Dave. Dave? -- Josh Berkus Aglio Database Solutions San Francisco
On Fri, 19 Aug 2005, Dave Cramer wrote: > I would vote for 3.I already have ported your patch over to the (near) > current code and it is being used by a few people and from what I understand > quite stable. If you want I can fix this up and commit it. > I'd like to see what you've got, could you post it somewhere? Kris Jurka
It hasn't been committed to CVS. The source can be found at http://download.postgresintl.com/ Dave On 19-Aug-05, at 1:10 PM, Kris Jurka wrote: > > > On Fri, 19 Aug 2005, Josh Berkus wrote: > > >> Unless I'm mistaken, Dave Cramer re-worked your patch and there's >> a stable >> version of it already in CVS. Dave? >> >> > > I haven't seen a re-worked version and it's definitely not in CVS. > > Kris Jurka > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > >
On Fri, 19 Aug 2005, Dave Cramer wrote: > http://download.postgresintl.com/ > OK, well this hasn't really been reworked so much as just hacking QueryExecutor to allow access to PGStream which breaks the thread safety of the driver. I think the protocol refactoring kind of implies that this code should be put into QueryExecutorImpl now, but I haven't looked to see how complicated that would be... Kris Jurka