On Sun, 10 Jan 2010 16:01:57 +0800, Craig Ringer wrote:
>On 6/01/2010 10:53 PM, John T. Dow wrote:
>> I posted this several days ago to pgsql-jdbc but have had no response. I am
>> posting it here (with minor changes in the wording).
>>
>> I have developed some code that works, I'm just not sure I have the "best"
>> solution.
>>
>> I have applications in which the user can create a read-only resultset with
>> multiple rows. For example, customers who are 90 days in arrears might be
>> brought up for review.
>>
>> The user might scroll through the rows reviewing the data, and then he might
>> decide to update one of them. A second query is used to update that one row.
>> At the time of the update, the current contents of that row is reread FOR
>> UPDATE and compared against the original row. If they differ, someone else
>> has altered the row after the resultset was created.
>>
>> The user is informed that another user has changed the row in question; he
>> can then decide to accept the changes he has made or leave in place the
>> changes made by the other user. In either case, that row in the original
>> resultset has to be made to match the current contents in the table, because
>> the user might scroll back and forth and revisit it.
>>
>> I am using refreshRow() to make that row current, but the problem is that
>> refreshRow() can be extremely slow.
>>
>> I create the read-only, multiple row resultset (ie "viewResultSet") like
>> this:
>>
>> createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
>> ResultSet.CONCUR_READ_ONLY);
>> viewResultSet = jdbcStmt.executeQuery("SELECT ... FROM ... WHERE select
>> multiple rows");
>>
>> Scroll through the resultset to view rows as desired.
>>
>> When positioned at a row, can update that row. See below.
>
>Can you instead create an explicit, named updatable cursor? Then FETCH
>from it, and UPDATE ... WHERE CURRENT OF it? That way you won't have to
>do all this kludging with refreshing rowsets.
>
>--
>Craig Ringer
I have not used cursors, so you've inspired me to rtfm about them. So far I
don't see how to implement optimistic locking with cursors.
The postgres documentation says this: "Without FOR UPDATE, a subsequent WHERE
CURRENT OF command will have no effect if the row was changed since the cursor
was created."
I read that to mean that the cursor has to be declared FOR UPDATE, which means
that the rows are locked and I don't have optimistic locking.
John
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
