Thread: JDBC and positioned updates

JDBC and positioned updates

From

Hédi HACHENI

Date:

25 December 2015, 18:10:37

Dear,

We are using PostgreSQL (server version 9.4.5) with a JDBC connection (version 9.1.x).

It seems that JDBC implementation does not support the setting of a cursor name for an SQL statement. The following example will be used to illustrate the problem.

Consider that we have the SELECT statement below :

    SELECT    Column1,
                    Column2
                    Column3,
                    Column4
    FROM       MyTable
    FOR UPDATE

Then, we want to perform a positioned update based on the SELECT above :

    UPDATE    MyTable
    SET           Column1 = new_value,
                     Column3 = new_value
    WHERE CURRENT OF cursor_name

To achieve our goal, we would use the java.sql.Statement#setCursorName(String) to reference the SELECT statement. Then, using the java.sql.ResultSet#getCursorName() to get the used cursor name for the statement. Nevertheless, the java.sql.ResultSet#getCursorName() returns null as value and even the java.sql.Statement#setCursorName(String) method seems to be not implemented.

Based on what explained above, we would like to ask four major questions :

Is there any plan for the implementation of the mentioned feature in further JDBC versions ?
Is there any alternative solution that aid us to perform a positioned update using JDBC ?
If the JDBC implementation should be modified, can you guide us or give us ideas to get started ?
If we are obliged to implement the feature by modifying the JDBC implementation, is there any interest in this modified JDBC ?

Thanks for your feedback.

Best regards,
Hédi HACHENI
kopiLeft Services

Re: JDBC and positioned updates

From

Vladimir Sitnikov

Date:

25 December 2015, 18:38:55

1) Have you considered using "updatable ResultSet"?

Here's a sample:

https://github.com/pgjdbc/pgjdbc/blob/32f513370306529005cb36d968f8e415f4a88aec/pgjdbc/src/test/java/org/postgresql/test/jdbc2/UpdateableResultTest.java#L301-L303

2) >   WHERE CURRENT OF cursor_name

ResultSet implementation fetches result rows in batches, thus "current
of" at the database side might point to a slightly different row than
ResultSet at the java side.

3) You do not expect high throughput of that kind of API, do you?
Updatable ResultSet is not using batching, so each update would result
in a database roundtrip.

Vladimir

Re: JDBC and positioned updates

From

Hédi HACHENI

Date:

26 December 2015, 11:00:48

Hi,

We cannot use the updatable result set technique since we support multiple databases so we want to use the AINSI syntax with the CURRENT OF cursor_name.

We tried to implement the java.sql.Statement#setCursorName(String) and the java.sql.ResultSet#getCursorName() methods so that we can set the cursor name of an SQL statement. But, it seems to be more complicated than that we expected since the database server always says "cursor "name" does not exist"

What we did is to change a little bit the implementation of the org.postgresql.core.v3.QueryExecutorImpl in a way that we can define a cursor name for an SQL statement. We tried to inject this in the org.postgresql.core.v3.Portal class but we did not succeed.

Any ideas ?

Best regards

On 12/25/2015 04:38 PM, Vladimir Sitnikov wrote:

1) Have you considered using "updatable ResultSet"?

Here's a sample:
https://github.com/pgjdbc/pgjdbc/blob/32f513370306529005cb36d968f8e415f4a88aec/pgjdbc/src/test/java/org/postgresql/test/jdbc2/UpdateableResultTest.java#L301-L303

2) >   WHERE CURRENT OF cursor_name

ResultSet implementation fetches result rows in batches, thus "current
of" at the database side might point to a slightly different row than
ResultSet at the java side.

3) You do not expect high throughput of that kind of API, do you?
Updatable ResultSet is not using batching, so each update would result
in a database roundtrip.

Vladimir

Re: JDBC and positioned updates

From

Vladimir Sitnikov

Date:

26 December 2015, 11:04:09

What are your requirements on
1) the number or rows selected
2) the number of row updates
3) end-to-end duration of the update dance
?

Vladimir

Re: JDBC and positioned updates

From

Hédi HACHENI

Date:

26 December 2015, 11:07:37

All of these parameters depends on what table we're going through! Our
main problem is that we cannot achieve positioned updates using the JDBC
implementation.
On 12/26/2015 09:04 AM, Vladimir Sitnikov wrote:
> What are your requirements on
> 1) the number or rows selected
> 2) the number of row updates
> 3) end-to-end duration of the update dance
> ?
>
> Vladimir

Re: JDBC and positioned updates

From

Vladimir Sitnikov

Date:

26 December 2015, 11:21:17

> we cannot achieve positioned updates using the JDBC implementation

This is "functional requirement"

> All of these parameters depends on what table we're going through

Those a "non-functional requirements" (see [1]).
You'd better collect NFRs *before* you finalize design and write the code.

>Our main problem is

Suppose you somehow achieved "positional updates". What if it turns
out to be super-slow? Believe me, there are good reasons for
"positional updates" to be super slow.
You would have to rewrite the whole thing from scratch to make it fast.

This is why I do not want to help you to shoot into your own foot.

Anyway, pull requests are welcome.

[1]: https://en.wikipedia.org/wiki/Non-functional_requirement

Vladimir

Re: JDBC and positioned updates

From

Hédi HACHENI

Date:

26 December 2015, 11:52:53

Basically we will not perform positioned updates on the hole table data
but for a data portion that it would not exceed 30 rows. That's why I
said it depends on what table we're going through.

But we take care about queries performances.
On 12/26/2015 09:21 AM, Vladimir Sitnikov wrote:
>> we cannot achieve positioned updates using the JDBC implementation
> This is "functional requirement"
>
>> All of these parameters depends on what table we're going through
> Those a "non-functional requirements" (see [1]).
> You'd better collect NFRs *before* you finalize design and write the code.
>
>> Our main problem is
> Suppose you somehow achieved "positional updates". What if it turns
> out to be super-slow? Believe me, there are good reasons for
> "positional updates" to be super slow.
> You would have to rewrite the whole thing from scratch to make it fast.
>
> This is why I do not want to help you to shoot into your own foot.
>
> Anyway, pull requests are welcome.
>
> [1]: https://en.wikipedia.org/wiki/Non-functional_requirement
>
> Vladimir

Re: JDBC and positioned updates

From

Kevin Wooten

Date:

26 December 2015, 17:54:44

https://github.com/impossibl/pgjdbc-ng supports ‘setCursorName’.

PostgreSQL doesn’t support all capabilities defined for cursors but what you’ve referenced should work.

On Dec 26, 2015, at 1:53 AM, Hédi HACHENI <hacheni@kopileft.com> wrote:

Basically we will not perform positioned updates on the hole table data but for a data portion that it would not exceed 30 rows. That's why I said it depends on what table we're going through.

But we take care about queries performances.
On 12/26/2015 09:21 AM, Vladimir Sitnikov wrote:
we cannot achieve positioned updates using the JDBC implementation
This is "functional requirement"

All of these parameters depends on what table we're going through
Those a "non-functional requirements" (see [1]).
You'd better collect NFRs *before* you finalize design and write the code.

Our main problem is
Suppose you somehow achieved "positional updates". What if it turns
out to be super-slow? Believe me, there are good reasons for
"positional updates" to be super slow.
You would have to rewrite the whole thing from scratch to make it fast.

This is why I do not want to help you to shoot into your own foot.

Anyway, pull requests are welcome.

[1]: https://en.wikipedia.org/wiki/Non-functional_requirement

Vladimir

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc