Thread: Patch for streaming resultsets

Patch for streaming resultsets

From
"Patric Bechtel"
Date:
Hello folks,

I've made a patch against the current CVS version of the JDBC driver,
which, based upon Nick Ferrier's patch (thanks!), which adds
streaming result sets to the driver.
I've made some modifications to the build file, too, as in it's
current version it's not able to build clean for JDBC3. I think
I fixed that.
Another thing I added are three properties which can be included
inside the URL:

 defaultFetchSize=<int>

sets the fetch size default to <int>.

 forceFetchSize=<boolean>

in case this property is true, the driver ignores all setFetchSize()
API calls from the application. It can be used to get apps, which
"know" the postgres driver, work though... :-)

 useServerPrepare=<never|always>

This on alters the behaviour of server prepared statements. The
default behaviour is that PreparedStatement's DO get prepared
on server side, and normal Statement's do NOT.
Setting this property to "never" switches server side preparation
off completely, whereas setting it to always even makes
normal Statements prepared ones (which can be nice in case of
ever and ever repeated queries such as queue lookups).

Please note that some parts of the source are commented out
for now, as I did not find a way to accomplish the declaration
of a cursor for a prepared statement. As for now, this seems
to be a feature in upcoming 7.3.2/7.4. But as soon as one of
these arrive, we just twig out these comments and there we go...

The patch is, as mentioned above, against the most actual CVS
version. A single file, PGResultSet.java, belongs into the
org/postgresql directory; it needs to be added to the
repository.

Again, a big thank you for Nick Ferrier, as he made the first
version of this; I cleaned it up and tested it quite
thoroughly, our own app as all of the test cases seem to work
flawlessly with it. It seems that I've broken not too much... :-)

have fun,

Patric



Attachment

Re: Patch for streaming resultsets

From
Dave Cramer
Date:
Patric,

Can you clarify why you want the default behaviour to use server
prepared statements?

I use prepared statements just because they are easier, not because of
any inherent behaviour.


Dave

On Thu, 2003-01-02 at 11:33, Patric Bechtel wrote:
> Hello folks,
>
> I've made a patch against the current CVS version of the JDBC driver,
> which, based upon Nick Ferrier's patch (thanks!), which adds
> streaming result sets to the driver.
> I've made some modifications to the build file, too, as in it's
> current version it's not able to build clean for JDBC3. I think
> I fixed that.
> Another thing I added are three properties which can be included
> inside the URL:
>
>  defaultFetchSize=<int>
>
> sets the fetch size default to <int>.
>
>  forceFetchSize=<boolean>
>
> in case this property is true, the driver ignores all setFetchSize()
> API calls from the application. It can be used to get apps, which
> "know" the postgres driver, work though... :-)
>
>  useServerPrepare=<never|always>
>
> This on alters the behaviour of server prepared statements. The
> default behaviour is that PreparedStatement's DO get prepared
> on server side, and normal Statement's do NOT.
> Setting this property to "never" switches server side preparation
> off completely, whereas setting it to always even makes
> normal Statements prepared ones (which can be nice in case of
> ever and ever repeated queries such as queue lookups).
>
> Please note that some parts of the source are commented out
> for now, as I did not find a way to accomplish the declaration
> of a cursor for a prepared statement. As for now, this seems
> to be a feature in upcoming 7.3.2/7.4. But as soon as one of
> these arrive, we just twig out these comments and there we go...
>
> The patch is, as mentioned above, against the most actual CVS
> version. A single file, PGResultSet.java, belongs into the
> org/postgresql directory; it needs to be added to the
> repository.
>
> Again, a big thank you for Nick Ferrier, as he made the first
> version of this; I cleaned it up and tested it quite
> thoroughly, our own app as all of the test cases seem to work
> flawlessly with it. It seems that I've broken not too much... :-)
>
> have fun,
>
> Patric
>
>
>
> ______________________________________________________________________
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Dave Cramer <Dave@micro-automation.net>


Re: Patch for streaming resultsets [Viruschecked]

From
"Patric Bechtel"
Date:
On 03 Jan 2003 09:30:18 -0500, Dave Cramer wrote:

Dave,

that's what the useServerPrepare URL property is for. Anyone not
liking (or sort of...) the behaviour of the driver can alter it
so that prepared statements NEVER get prepared on the server.
You can still use setUseServerPrepared(true) to alter this again
for eg. one special query, but the default then is never (or always).
The JDBC API only differentiates between Statements and Prepared
Statements, which is a bit poor for us Postgres users. Normally
(I'm speaking in M$ or DB/2 conventions) its ALWAYS no difference
between prepare statements or ad hoc execution. If you then reuse
the prepared statement thereafter, the optimizer knows what to do
and you'll get a performance win.
That should be the same on Postgres (and later will be that way,
I'm sure...). But for the moment, there's a small chance, that
prepared statements get not really optimized to the very last bit,
which normally isn't that bad as it first sounds like.
But if you repeatadly execute a prepared statement over and over,
you'll win; less preparation time on the client, less on the server,
so you make the bet.

We could even vote if prepared statements by default get prepared
on the server or not. I could easily change the behaviour.
My +1 for the current behaviour of my patch. :-)

tia
Patric


>Patric,
>
>Can you clarify why you want the default behaviour to use server
>prepared statements?
>
>I use prepared statements just because they are easier, not because of
>any inherent behaviour.
>
>
>Dave
>
>On Thu, 2003-01-02 at 11:33, Patric Bechtel wrote:
>> Hello folks,
>>
>> I've made a patch against the current CVS version of the JDBC driver,
>> which, based upon Nick Ferrier's patch (thanks!), which adds
>> streaming result sets to the driver.
>> I've made some modifications to the build file, too, as in it's
>> current version it's not able to build clean for JDBC3. I think
>> I fixed that.
>> Another thing I added are three properties which can be included
>> inside the URL:
>>
>>  defaultFetchSize=<int>
>>
>> sets the fetch size default to <int>.
>>
>>  forceFetchSize=<boolean>
>>
>> in case this property is true, the driver ignores all setFetchSize()
>> API calls from the application. It can be used to get apps, which
>> "know" the postgres driver, work though... :-)
>>
>>  useServerPrepare=<never|always>
>>
>> This on alters the behaviour of server prepared statements. The
>> default behaviour is that PreparedStatement's DO get prepared
>> on server side, and normal Statement's do NOT.
>> Setting this property to "never" switches server side preparation
>> off completely, whereas setting it to always even makes
>> normal Statements prepared ones (which can be nice in case of
>> ever and ever repeated queries such as queue lookups).
>>
>> Please note that some parts of the source are commented out
>> for now, as I did not find a way to accomplish the declaration
>> of a cursor for a prepared statement. As for now, this seems
>> to be a feature in upcoming 7.3.2/7.4. But as soon as one of
>> these arrive, we just twig out these comments and there we go...
>>
>> The patch is, as mentioned above, against the most actual CVS
>> version. A single file, PGResultSet.java, belongs into the
>> org/postgresql directory; it needs to be added to the
>> repository.
>>
>> Again, a big thank you for Nick Ferrier, as he made the first
>> version of this; I cleaned it up and tested it quite
>> thoroughly, our own app as all of the test cases seem to work
>> flawlessly with it. It seems that I've broken not too much... :-)
>>
>> have fun,
>>
>> Patric
>>
>>
>>
>> ______________________________________________________________________
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>--
>Dave Cramer <Dave@micro-automation.net>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>




Re: Patch for streaming resultsets [Viruschecked]

From
"Patric Bechtel"
Date:
On 03 Jan 2003 09:30:18 -0500, Dave Cramer wrote:

Dave,

that's what the useServerPrepare URL property is for. Anyone not
liking (or sort of...) the behaviour of the driver can alter it
so that prepared statements NEVER get prepared on the server.
You can still use setUseServerPrepared(true) to alter this again
for eg. one special query, but the default then is never (or always).
The JDBC API only differentiates between Statements and Prepared
Statements, which is a bit poor for us Postgres users. Normally
(I'm speaking in M$ or DB/2 conventions) its ALWAYS no difference
between prepare statements or ad hoc execution. If you then reuse
the prepared statement thereafter, the optimizer knows what to do
and you'll get a performance win.
That should be the same on Postgres (and later will be that way,
I'm sure...). But for the moment, there's a small chance, that
prepared statements get not really optimized to the very last bit,
which normally isn't that bad as it first sounds like.
But if you repeatadly execute a prepared statement over and over,
you'll win; less preparation time on the client, less on the server,
so you make the bet.

We could even vote if prepared statements by default get prepared
on the server or not. I could easily change the behaviour.
My +1 for the current behaviour of my patch. :-)

tia
Patric


>Patric,
>
>Can you clarify why you want the default behaviour to use server
>prepared statements?
>
>I use prepared statements just because they are easier, not because of
>any inherent behaviour.
>
>
>Dave
>
>On Thu, 2003-01-02 at 11:33, Patric Bechtel wrote:
>> Hello folks,
>>
>> I've made a patch against the current CVS version of the JDBC driver,
>> which, based upon Nick Ferrier's patch (thanks!), which adds
>> streaming result sets to the driver.
>> I've made some modifications to the build file, too, as in it's
>> current version it's not able to build clean for JDBC3. I think
>> I fixed that.
>> Another thing I added are three properties which can be included
>> inside the URL:
>>
>>  defaultFetchSize=<int>
>>
>> sets the fetch size default to <int>.
>>
>>  forceFetchSize=<boolean>
>>
>> in case this property is true, the driver ignores all setFetchSize()
>> API calls from the application. It can be used to get apps, which
>> "know" the postgres driver, work though... :-)
>>
>>  useServerPrepare=<never|always>
>>
>> This on alters the behaviour of server prepared statements. The
>> default behaviour is that PreparedStatement's DO get prepared
>> on server side, and normal Statement's do NOT.
>> Setting this property to "never" switches server side preparation
>> off completely, whereas setting it to always even makes
>> normal Statements prepared ones (which can be nice in case of
>> ever and ever repeated queries such as queue lookups).
>>
>> Please note that some parts of the source are commented out
>> for now, as I did not find a way to accomplish the declaration
>> of a cursor for a prepared statement. As for now, this seems
>> to be a feature in upcoming 7.3.2/7.4. But as soon as one of
>> these arrive, we just twig out these comments and there we go...
>>
>> The patch is, as mentioned above, against the most actual CVS
>> version. A single file, PGResultSet.java, belongs into the
>> org/postgresql directory; it needs to be added to the
>> repository.
>>
>> Again, a big thank you for Nick Ferrier, as he made the first
>> version of this; I cleaned it up and tested it quite
>> thoroughly, our own app as all of the test cases seem to work
>> flawlessly with it. It seems that I've broken not too much... :-)
>>
>> have fun,
>>
>> Patric
>>
>>
>>
>> ______________________________________________________________________
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>--
>Dave Cramer <Dave@micro-automation.net>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>




Re: Patch for streaming resultsets [Viruschecked]

From
Nic Ferrier
Date:
"Patric Bechtel" <bechtel@ipcon.de> writes:

> On 03 Jan 2003 09:30:18 -0500, Dave Cramer wrote:
>
> Dave,
>
> that's what the useServerPrepare URL property is for. Anyone not
> liking (or sort of...) the behaviour of the driver can alter it
> so that prepared statements NEVER get prepared on the server.

I would prefer ps's to be prepared on the server... that way there is
some reliable type checking going on (see the archives of this list
for the discussion about security issues and type checking with ps's).


Nic

Re: Patch for streaming resultsets

From
Kovács Péter
Date:

Patric probably thinks that the purpose of the designers of the JDBC API to include the PreparedStatement type into the API was to address the need for (the) "inherent behaviour" and not to spare lazy coders writing a few more lines of code. :))))

Peter

> -----Original Message-----
> From: Dave Cramer [mailto:Dave@micro-automation.net]
> Sent: Friday, January 03, 2003 3:30 PM
> To: Patric Bechtel
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Patch for streaming resultsets
>
>
> Patric,
>
> Can you clarify why you want the default behaviour to use server
> prepared statements?
>
> I use prepared statements just because they are easier, not because of
> any inherent behaviour.
>
>
> Dave
>
> On Thu, 2003-01-02 at 11:33, Patric Bechtel wrote:
> > Hello folks,
> >
> > I've made a patch against the current CVS version of the
> JDBC driver,
> > which, based upon Nick Ferrier's patch (thanks!), which adds
> > streaming result sets to the driver.
> > I've made some modifications to the build file, too, as in it's
> > current version it's not able to build clean for JDBC3. I think
> > I fixed that.
> > Another thing I added are three properties which can be included
> > inside the URL:
> >
> >  defaultFetchSize=<int>
> >
> > sets the fetch size default to <int>.
> >
> >  forceFetchSize=<boolean>
> >
> > in case this property is true, the driver ignores all setFetchSize()
> > API calls from the application. It can be used to get apps, which
> > "know" the postgres driver, work though... :-)
> >
> >  useServerPrepare=<never|always>
> >
> > This on alters the behaviour of server prepared statements. The
> > default behaviour is that PreparedStatement's DO get prepared
> > on server side, and normal Statement's do NOT.
> > Setting this property to "never" switches server side preparation
> > off completely, whereas setting it to always even makes
> > normal Statements prepared ones (which can be nice in case of
> > ever and ever repeated queries such as queue lookups).
> >
> > Please note that some parts of the source are commented out
> > for now, as I did not find a way to accomplish the declaration
> > of a cursor for a prepared statement. As for now, this seems
> > to be a feature in upcoming 7.3.2/7.4. But as soon as one of
> > these arrive, we just twig out these comments and there we go...
> >
> > The patch is, as mentioned above, against the most actual CVS
> > version. A single file, PGResultSet.java, belongs into the
> > org/postgresql directory; it needs to be added to the
> > repository.
> >
> > Again, a big thank you for Nick Ferrier, as he made the first
> > version of this; I cleaned it up and tested it quite
> > thoroughly, our own app as all of the test cases seem to work
> > flawlessly with it. It seems that I've broken not too much... :-)
> >
> > have fun,
> >
> > Patric
> >
> >
> >
> >
> ______________________________________________________________________
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> --
> Dave Cramer <Dave@micro-automation.net>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>