Folks,

I'd like to implement updateable cursors. I'll be working on just
getting updateable cursors working for relatively simple SELECT queries
(e.g. no joins, aggregates, grouping, user-defined function calls,
etc.). BTW, I believe that's all the SQL spec requires, but I need to
double check that. I'm also planning on only implementing only
INSENSITIVE cursors, and not allowing holdable cursors.

However, I'm a little unsure how tuple visibility should work with
updateable cursors. In particular:

- if the user updates a row X in the cursor, then rewinds the cursor and
fetches X again, should they see the new X or the old X?

- if the user updates a row X in the cursor, and then a query within the
cursor's transaction views X, should the query see new X or old X?

Any comments?

Neil


24 Mar 2003 16:49:19 -0500
Neil Conway <neilc@samurai.com>
updateable cursors & visibility
Neil Conway <neilc@samurai.com> writes:
> - if the user updates a row X in the cursor, then rewinds the cursor and
> fetches X again, should they see the new X or the old X?

If it's considered an insensitive cursor, I'd think it should see the
old X.  You would have a hard time making the code do otherwise in any
case --- the cursor's snapshot is frozen when the cursor is created.

> - if the user updates a row X in the cursor, and then a query within the
> cursor's transaction views X, should the query see new X or old X?

The subsequent query (if it's not going through the cursor) should and
will see the new X.

This should all be handled for you by the command-counter-based
visibility rules; I don't think you need to worry about it.

			regards, tom lane


Mon, 24 Mar 2003 17:16:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: updateable cursors & visibility 
Neil Conway wrote:
> 
> Folks,
> 
> I'd like to implement updateable cursors. I'll be working on just
> getting updateable cursors working for relatively simple SELECT queries
> (e.g. no joins, aggregates, grouping, user-defined function calls,
> etc.). BTW, I believe that's all the SQL spec requires, but I need to
> double check that. I'm also planning on only implementing only
> INSENSITIVE cursors,

Does the SQL standard allow INSENSITIVE updatable cursors ?

regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/


Tue, 25 Mar 2003 12:50:20 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: updateable cursors & visibility
On Mon, 2003-03-24 at 22:50, Hiroshi Inoue wrote:
> Does the SQL standard allow INSENSITIVE updatable cursors ?

Hmmm... apparently not:

(Subsection 14.1, Syntax Rules of DECLARE CURSOR)

11) If an <updatability clause> of FOR UPDATE with or without a <column
name list> is specified, then INSENSITIVE shall not be specified and QE
shall be updateable.

I'm not really sure I see the point of this restriction, though...

Cheers,

Neil


24 Mar 2003 23:01:38 -0500
Neil Conway <neilc@samurai.com>
Re: updateable cursors & visibility
Neil Conway wrote:
> On Mon, 2003-03-24 at 22:50, Hiroshi Inoue wrote:
> > Does the SQL standard allow INSENSITIVE updatable cursors ?
> 
> Hmmm... apparently not:
> 
> (Subsection 14.1, Syntax Rules of DECLARE CURSOR)
> 
> 11) If an <updatability clause> of FOR UPDATE with or without a <column
> name list> is specified, then INSENSITIVE shall not be specified and QE
> shall be updateable.
> 
> I'm not really sure I see the point of this restriction, though...

My guess is that while INSENSITIVE is good, when you use updatable
cursors, when you use WHERE CURRENT OF for UPDATE, you are really
updating the cursor.

However, I think it would be really weird to be seeing other people
changes (senstive) in my cursor, so I am not sure we really want to
follow that area of the spec.  If they said you should be able to see
the WHERE CURRENT OF updates, but not other people's commits, I could
see that logic.  I wonder if they did the spec this way because some
vendors couldn't do INSENSITIVE - WHERE CURRENT OF cursors.

I see SQL99 specifies SENSITIVE:

         <cursor sensitivity> ::=
                SENSITIVE
              | INSENSITIVE
              | ASENSITIVE

Maybe we should add the keyword SENSITIVE and throw an error if
SENSITIVE is used because we don't support it.  Of course, we throw an
error now, but the error is "syntax error" rather than "not supported".

Here is the SQL99 standard on this:

---------------------------------------------------------------------------

         Another property of a cursor is its sensitivity, which may be
         sensitive, insensitive, or asensitive, depending on whether
         SENSITIVE, INSENSITIVE, or ASENSITIVE is specified or implied. The
         following paragraphs define several terms used to discuss issues
         relating to cursor sensitivity:

         A change to SQL-data is said to be independent of a cursor CR if
         and only if it is not made by an <update statement: positioned> or
         a <delete statement: positioned> that is positioned on CR.

         A change to SQL-data is said to be significant to CR if and only if
         it is independent of CR, and, had it been committed before CR was
         opened, would have caused the table associated with the cursor to
         be different in any respect.

         A change to SQL-data is said to be visible to CR if and only if it
         has an effect on CR by inserting a row in CR, deleting a row from
         CR, changing the value of a column of a row of CR, or reordering
         the rows of CR.

         If a cursor is open, and the SQL-transaction in which the cursor
         was opened makes a significant change to SQL-data, then whether
         that change is visible through that cursor before it is closed is

         determined as follows:

         -  If the cursor is insensitive, then significant changes are not
            visible.

         -  If the cursor is sensitive, then significant changes are
            visible.

         -  If the cursor is asensitive, then the visibility of significant
            changes is implementation-dependent.

         If a holdable cursor is open during an SQL-transaction T and it
         is held open for a subsequent SQL-transaction, then whether any
         significant changes made to SQL-data (by T or any subsequent SQL-
         transaction in which the cursor is held open) are visible through
         that cursor in the subsequent SQL-transaction before that cursor is
         closed is determined as follows:

         -  If the cursor is insensitive, then significant changes are not
            visible.

         -  If the cursor is sensitive, then the visibility of significant
            changes is implementation-defined.

         -  If the cursor is asensitive, then the visibility of significant
            changes is implementation-dependent.

         A <declare cursor> DC that specifies WITH RETURN is called a result
         set cursor. The <cursor specification> CR contained in DC defines
         a table T; the term result set is used to refer to T. A result set
         cursor, if declared in an SQL-invoked procedure and not closed when
         the procedure returns to its invoker, returns a result set to the
         invoker.


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Tue, 25 Mar 2003 00:31:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: updateable cursors & visibility
Hello Neil,
  I try example for Oracle jdbc 1.4 driver
  This is sample and results.
  I hope that is helps.

  If you want yet another example, please tell me.

regards
Haris Peco

/**
 * A simple sample to check the availability of scrollable result sets.
 *
 * Please use jdk1.2 or later version
 */

import java.sql.*;

public class ResultSetmy1
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    String url = "jdbc:oracle:thin:@spnew:1521:V9i";
    try {
      String url1 = System.getProperty("JDBC_URL");
      if (url1 != null)
        url = url1;
    } catch (Exception e) {
      // If there is any security exception, ignore it
      // and use the default
    }

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection (url, "scott", "tiger");

    // Get the metadata regarding this connection's database
    DatabaseMetaData dbmd = conn.getMetaData();

    // List all the possible result set types
    int resultset_types[] = 
    {
      ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.TYPE_SCROLL_INSENSITIVE,
      ResultSet.TYPE_SCROLL_SENSITIVE 
    };

    // List all the possible result set concurrency types
    int concurrency_types[] = 
    { 
      ResultSet.CONCUR_READ_ONLY,
      ResultSet.CONCUR_UPDATABLE 
    };

    // List the result set type names
    String resultset_types_msg [] = 
    {
      "Forward only", 
      "Scroll insensitive", 
      "Scroll sensitive"
    };

    // List the concurrency type names
    String concurrency_types_msg[] = 
    { 
      "Read only", 
      "Updatable" 
    };

    // Check the availability of the result type and concurrency type
    for (int i=0; i<resultset_types.length; i++)
    {
      for (int j=0; j<concurrency_types.length; j++)
      {
        int type = resultset_types[i];
        int concurrency = concurrency_types[j];

        System.out.println ("Type: "+resultset_types_msg[i]+"    "+
                            "Concurrency: "+concurrency_types_msg[j]);
        System.out.println
                  ("----------------------------------------------------");

        // Return true if the result set type is supported
        System.out.println ("supportResultSetType: "+
                            dbmd.supportsResultSetType(type));

        // Return true if the result set type and concurrency type is supported
        System.out.println ("supportsResultSetConcurrency: "+
                        dbmd.supportsResultSetConcurrency(type, concurrency));

        // Return true if the result set's updates are visible
        System.out.println ("ownUpdatesAreVisible: "+
                            dbmd.ownUpdatesAreVisible(type));

        // Return true if the result set's deletions are visible
        System.out.println ("ownDeletesAreVisible: "+
                            dbmd.ownDeletesAreVisible(type));

        // Return true if the result set's insertions are visible
        System.out.println ("ownInsertAreVisible: "+
                            dbmd.ownInsertsAreVisible(type));

        // Return true if other's changes are visible
        System.out.println ("othersUpdatesAreVisible: "+
                            dbmd.othersUpdatesAreVisible(type));

        // Return true if other's deletions are visible
        System.out.println ("othersDeletesAreVisible: "+
                            dbmd.othersDeletesAreVisible(type));

        // Return true if other's insertions are visible
        System.out.println ("othersInsertsAreVisible: "+
                            dbmd.othersInsertsAreVisible(type));

        // Return true if ResultSet.rowUpdated() is supported
        System.out.println ("updatesAreDetected: "+
                            dbmd.updatesAreDetected(type));

        // Return true if ResultSet.rowDeleted() is supported
        System.out.println ("deletesAreDetected: "+
                            dbmd.deletesAreDetected(type));

        // Return true if ResultSet.rowInserted() is supported
        System.out.println ("insertsAreDetected: "+
                            dbmd.insertsAreDetected(type));

        System.out.println ();
      }
    }

    // Close the connection
    conn.close();   
  }
}

This is results :

Type: Forward only    Concurrency: Read only
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: false
ownDeletesAreVisible: false
ownInsertAreVisible: false
othersUpdatesAreVisible: false
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

Type: Forward only    Concurrency: Updatable
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: false
ownDeletesAreVisible: false
ownInsertAreVisible: false
othersUpdatesAreVisible: false
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

Type: Scroll insensitive    Concurrency: Read only
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: true
ownDeletesAreVisible: true
ownInsertAreVisible: false
othersUpdatesAreVisible: false
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

Type: Scroll insensitive    Concurrency: Updatable
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: true
ownDeletesAreVisible: true
ownInsertAreVisible: false
othersUpdatesAreVisible: false
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

Type: Scroll sensitive    Concurrency: Read only
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: true
ownDeletesAreVisible: true
ownInsertAreVisible: false
othersUpdatesAreVisible: true
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

Type: Scroll sensitive    Concurrency: Updatable
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: true
ownDeletesAreVisible: true
ownInsertAreVisible: false
othersUpdatesAreVisible: true
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false


On Monday 24 March 2003 09:49 pm, Neil Conway wrote:
> Folks,
>
> I'd like to implement updateable cursors. I'll be working on just
> getting updateable cursors working for relatively simple SELECT queries
> (e.g. no joins, aggregates, grouping, user-defined function calls,
> etc.). BTW, I believe that's all the SQL spec requires, but I need to
> double check that. I'm also planning on only implementing only
> INSENSITIVE cursors, and not allowing holdable cursors.
>
> However, I'm a little unsure how tuple visibility should work with
> updateable cursors. In particular:
>
> - if the user updates a row X in the cursor, then rewinds the cursor and
> fetches X again, should they see the new X or the old X?
>
> - if the user updates a row X in the cursor, and then a query within the
> cursor's transaction views X, should the query see new X or old X?
>
> Any comments?
>
> Neil
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Tue, 25 Mar 2003 14:15:22 +0000
snpe <snpe@snpe.co.yu>
Re: updateable cursors & visibility
Neil Conway writes:

> 11) If an <updatability clause> of FOR UPDATE with or without a <column
> name list> is specified, then INSENSITIVE shall not be specified and QE
> shall be updateable.
>
> I'm not really sure I see the point of this restriction, though...

If you allow updatable insensitive cursors, then you are really saying,
whatever happens in the rest of the world does not affect my cursor, but
what I do in my cursor affects the rest of the world.  You can easily
construct some cases where this would have bizarre results.  For example,
someone inserts a primary key into the underlying table.  You don't see it
in the cursor, so you happily insert the same primary key.  How can you
and when should you detect this error?  Also, since the snapshot of
insensitive cursors is frozen when the cursor is created, would you insert
new rows "in the past"?  It's not really workable when you think it
through.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 25 Mar 2003 20:27:13 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: updateable cursors & visibility
Peter Eisentraut wrote:
> Neil Conway writes:
> 
> > 11) If an <updatability clause> of FOR UPDATE with or without a <column
> > name list> is specified, then INSENSITIVE shall not be specified and QE
> > shall be updateable.
> >
> > I'm not really sure I see the point of this restriction, though...
> 
> If you allow updatable insensitive cursors, then you are really saying,
> whatever happens in the rest of the world does not affect my cursor, but
> what I do in my cursor affects the rest of the world.  You can easily
> construct some cases where this would have bizarre results.  For example,
> someone inserts a primary key into the underlying table.  You don't see it
> in the cursor, so you happily insert the same primary key.  How can you
> and when should you detect this error?  Also, since the snapshot of
> insensitive cursors is frozen when the cursor is created, would you insert
> new rows "in the past"?  It's not really workable when you think it
> through.

You bring up a very good point here I didn't realize --- that when you
have a cursor, then do an UPDATE using the cursor information, your
UPDATE must have current visibility, not visibility at the time of
cursor creation, and I agree with you.

I talked to Neil Conway on the phone and we discussed various options. 
One idea is to require FOR UPDATE on the cursor --- while that prevents
other transactions from changing the cursor, it doesn't deal with the
current transaction modifying the table outside the cursor.  One idea is
to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now
when they find a row that is locked by another transaction --- they wait
to see if the transaction commits or aborts, then if committed they
follow the tid to the newly updated row, check the WHERE clause to see
if it still is satisfied, then perform the update.  (Is this correct?) 
I think WHERE CURRENT OF could do the same thing --- take the tid of the
cursor row, find the newest version of the row, wait for any active
transaction, re-test the cursor WHERE clause, and update the row.  Seems
this would make WHERE CURRENT OF behave just like UPDATE, except it is
getting its rows from the cursor.

As far as someone inserting into the table at the same time, I don't
know how to show that row in the cursor, but referential integrity
constraints are going to be checked by the UPDATE, and that UPDATE has
current visibility, so it should see any inserts that are valid.  It
doesn't seem much worse than what we have now.

Comments?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Tue, 25 Mar 2003 17:49:06 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: updateable cursors & visibility

OK, no one has replied to this proposal, so I assume the approach is
good.

As far as implementation, I assume we add a field to the cursor
structure to record the most recently fetched tid (if multiple rows are
fetched, it is the last row).

Now, when UPDATE/DELETE ... WHERE CURRENT OF, make sure the table
mentioned is the same as the single table in the cursor.  Then, use the
tid to find the most recent version of that row.  heap_update seems to
be where this all happens.  Also, make sure you check the cursor WHERE
condition before doing the update.

Basically, this allows us to get rows from the INSENSITIVE cursor, but
have the UPDATE have current visibility.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Peter Eisentraut wrote:
> > Neil Conway writes:
> > 
> > > 11) If an <updatability clause> of FOR UPDATE with or without a <column
> > > name list> is specified, then INSENSITIVE shall not be specified and QE
> > > shall be updateable.
> > >
> > > I'm not really sure I see the point of this restriction, though...
> > 
> > If you allow updatable insensitive cursors, then you are really saying,
> > whatever happens in the rest of the world does not affect my cursor, but
> > what I do in my cursor affects the rest of the world.  You can easily
> > construct some cases where this would have bizarre results.  For example,
> > someone inserts a primary key into the underlying table.  You don't see it
> > in the cursor, so you happily insert the same primary key.  How can you
> > and when should you detect this error?  Also, since the snapshot of
> > insensitive cursors is frozen when the cursor is created, would you insert
> > new rows "in the past"?  It's not really workable when you think it
> > through.
> 
> You bring up a very good point here I didn't realize --- that when you
> have a cursor, then do an UPDATE using the cursor information, your
> UPDATE must have current visibility, not visibility at the time of
> cursor creation, and I agree with you.
> 
> I talked to Neil Conway on the phone and we discussed various options. 
> One idea is to require FOR UPDATE on the cursor --- while that prevents
> other transactions from changing the cursor, it doesn't deal with the
> current transaction modifying the table outside the cursor.  One idea is
> to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now
> when they find a row that is locked by another transaction --- they wait
> to see if the transaction commits or aborts, then if committed they
> follow the tid to the newly updated row, check the WHERE clause to see
> if it still is satisfied, then perform the update.  (Is this correct?) 
> I think WHERE CURRENT OF could do the same thing --- take the tid of the
> cursor row, find the newest version of the row, wait for any active
> transaction, re-test the cursor WHERE clause, and update the row.  Seems
> this would make WHERE CURRENT OF behave just like UPDATE, except it is
> getting its rows from the cursor.
> 
> As far as someone inserting into the table at the same time, I don't
> know how to show that row in the cursor, but referential integrity
> constraints are going to be checked by the UPDATE, and that UPDATE has
> current visibility, so it should see any inserts that are valid.  It
> doesn't seem much worse than what we have now.
> 
> Comments?
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Wed, 26 Mar 2003 23:57:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: updateable cursors & visibility
Bruce Momjian writes:

> One idea is to require FOR UPDATE on the cursor --- while that prevents
> other transactions from changing the cursor, it doesn't deal with the
> current transaction modifying the table outside the cursor.

That would only keep existing rows from being deleted but not new rows
from being added.

> One idea is
> to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now
> when they find a row that is locked by another transaction --- they wait
> to see if the transaction commits or aborts, then if committed they
> follow the tid to the newly updated row, check the WHERE clause to see
> if it still is satisfied, then perform the update.  (Is this correct?)

Surely it would have to do something like that, but that's a matter of the
transaction isolation, not the sensitivity.  It doesn't do anything to
address the potential problems I mentioned.

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 27 Mar 2003 16:23:45 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: updateable cursors & visibility
Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > One idea is to require FOR UPDATE on the cursor --- while that prevents
> > other transactions from changing the cursor, it doesn't deal with the
> > current transaction modifying the table outside the cursor.
> 
> That would only keep existing rows from being deleted but not new rows
> from being added.
> 
> > One idea is
> > to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now
> > when they find a row that is locked by another transaction --- they wait
> > to see if the transaction commits or aborts, then if committed they
> > follow the tid to the newly updated row, check the WHERE clause to see
> > if it still is satisfied, then perform the update.  (Is this correct?)
> 
> Surely it would have to do something like that, but that's a matter of the
> transaction isolation, not the sensitivity.  It doesn't do anything to
> address the potential problems I mentioned.

Well, a unique constraint on the row would see your other INSERT.  I
don't see how making an INSERT visible in the cursor would help us, and
I don't see how we would implement that except by rerunning the query
for each fetch, which seems like a bad idea.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Thu, 27 Mar 2003 10:32:49 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: updateable cursors & visibility
Bruce Momjian wrote:
> 
> Peter Eisentraut wrote:
> > Bruce Momjian writes:
> >
> > > One idea is to require FOR UPDATE on the cursor --- while that prevents
> > > other transactions from changing the cursor, it doesn't deal with the
> > > current transaction modifying the table outside the cursor.
> >
> > That would only keep existing rows from being deleted but not new rows
> > from being added.
> >
> > > One idea is
> > > to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now
> > > when they find a row that is locked by another transaction --- they wait
> > > to see if the transaction commits or aborts, then if committed they
> > > follow the tid to the newly updated row, check the WHERE clause to see
> > > if it still is satisfied, then perform the update.  (Is this correct?)
> >
> > Surely it would have to do something like that, but that's a matter of the
> > transaction isolation, not the sensitivity.  It doesn't do anything to
> > address the potential problems I mentioned.
> 
> Well, a unique constraint on the row would see your other INSERT.  I
> don't see how making an INSERT visible in the cursor would help us, and
> I don't see how we would implement that except by rerunning the query
> for each fetch, which seems like a bad idea.

I don't understand what you two are discussing.
What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?

regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/


Fri, 28 Mar 2003 12:40:32 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: updateable cursors & visibility
Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > Peter Eisentraut wrote:
> > > Bruce Momjian writes:
> > >
> > > > One idea is to require FOR UPDATE on the cursor --- while that prevents
> > > > other transactions from changing the cursor, it doesn't deal with the
> > > > current transaction modifying the table outside the cursor.
> > >
> > > That would only keep existing rows from being deleted but not new rows
> > > from being added.
> > >
> > > > One idea is
> > > > to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now
> > > > when they find a row that is locked by another transaction --- they wait
> > > > to see if the transaction commits or aborts, then if committed they
> > > > follow the tid to the newly updated row, check the WHERE clause to see
> > > > if it still is satisfied, then perform the update.  (Is this correct?)
> > >
> > > Surely it would have to do something like that, but that's a matter of the
> > > transaction isolation, not the sensitivity.  It doesn't do anything to
> > > address the potential problems I mentioned.
> > 
> > Well, a unique constraint on the row would see your other INSERT.  I
> > don't see how making an INSERT visible in the cursor would help us, and
> > I don't see how we would implement that except by rerunning the query
> > for each fetch, which seems like a bad idea.
> 
> I don't understand what you two are discussing.
> What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?

In SQL99 standard, I see:

         -  If the cursor is insensitive, then significant changes are not
            visible.

         -  If the cursor is sensitive, then significant changes are
            visible.

         -  If the cursor is asensitive, then the visibility of significant
            changes is implementation-dependent.

So, I think we have two issues --- what does the cursor see, and what
does the UPDATE see.  I think we have to have the cursor remain
INSENSITIVE, because we don't at fetch time whether WHERE CURRENT OF is
going to be used.  One nice thing is that while the standard says you
can't specify INSENSITIVE for a WHERE CURRENT OF cursor, we can say it
is ASENSITIVE and that will match our behavior. (We just need a boolean
to make sure if they do specify INSENSTIVIVE that WHERE CURRENT OF
throws an error.)

Then, when we do the UPDATE, the UPDATE is SENSITIVE in that it sees the
most recent version of the tuple, assuming the newest tuple still
matches the WHERE clause of the cursor.  The UPDATE also has to do
contraint checking using current visibility.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Thu, 27 Mar 2003 22:53:12 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: updateable cursors & visibility
Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > Bruce Momjian wrote:
> > >
> > > Peter Eisentraut wrote:
> > > > Bruce Momjian writes:
> > > >
> >
> > I don't understand what you two are discussing.
> > What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?
> 
> In SQL99 standard, I see:
> 
>          -  If the cursor is insensitive, then significant changes are not
>             visible.
> 
>          -  If the cursor is sensitive, then significant changes are
>             visible.
> 
>          -  If the cursor is asensitive, then the visibility of significant
>             changes is implementation-dependent.

While a cursor is open, another application inserted a
row which satisfies the condition to be contained in
the cursor and committed. Then
If the cursor is SENSITIVE, must it see the row ?
If the cursor is INSENSITIVE, it mustn't see the row ?
  
regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/


Fri, 28 Mar 2003 15:11:43 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: updateable cursors & visibility
Hiroshi Inoue wrote:
> > > I don't understand what you two are discussing.
> > > What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?
> > 
> > In SQL99 standard, I see:
> > 
> >          -  If the cursor is insensitive, then significant changes are not
> >             visible.
> > 
> >          -  If the cursor is sensitive, then significant changes are
> >             visible.
> > 
> >          -  If the cursor is asensitive, then the visibility of significant
> >             changes is implementation-dependent.
> 
> While a cursor is open, another application inserted a
> row which satisfies the condition to be contained in
> the cursor and committed. Then
> If the cursor is SENSITIVE, must it see the row ?

Right.

> If the cursor is INSENSITIVE, it mustn't see the row ?

Right.

What I don't understand is how the row would automatically appear in a
sensitive cursor.  If the cursor has an ORDER BY, and there are 100
rows, and I am on row 50, and someone inserts a row that is ordered just
before 50, is my current row now 51, and 50 is the new added row, and if
I do a MOVE -1, I see the most recently inserted row and not the row I
saw as 49 before.  Seems quite confusing.

Anyway, my idea is to have the existing cursor rows appear as updated to
the UPDATE, but to remain insensitive in the cursor itself.  We could
change that, but it would require us to somehow mark the cursor at
declare time to indicate that it will be used with WHERE CURRENT OF.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Fri, 28 Mar 2003 11:12:24 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: updateable cursors & visibility
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> 
> Hiroshi Inoue wrote:
> > > > I don't understand what you two are discussing.
> > > > What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?
> > > 
> > > In SQL99 standard, I see:
> > > 
> > >          -  If the cursor is insensitive, then significant 
> changes are not
> > >             visible.
> > > 
> > >          -  If the cursor is sensitive, then significant changes are
> > >             visible.
> > > 
> > >          -  If the cursor is asensitive, then the visibility 
> of significant
> > >             changes is implementation-dependent.
> > 
> > While a cursor is open, another application inserted a
> > row which satisfies the condition to be contained in
> > the cursor and committed. Then
> > If the cursor is SENSITIVE, must it see the row ?
> 
> Right.
> 
> > If the cursor is INSENSITIVE, it mustn't see the row ?
> 
> Right.

If so, isn't the difference between SENSITIVE and INSENSITIVE extreme ?
Why do you or Peter refer to ASENSITIVE little ?
And what does the following mean ? It is placed just before the sentences
you quoted first.

  If a cursor is open, and the SQL-transaction in which the cursor was
  opened makes a significant change to SQL-data, then whether that
  change is visible through that cursor before it is closed is determined
  as follows:

regards,
Hiroshi Inoue 


Sat, 29 Mar 2003 08:05:16 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: updateable cursors & visibility
Hiroshi Inoue wrote:
> > > If the cursor is INSENSITIVE, it mustn't see the row ?
> > 
> > Right.
> 
> If so, isn't the difference between SENSITIVE and INSENSITIVE extreme ?

Yes.

> Why do you or Peter refer to ASENSITIVE little ?

Not sure --- ASENSITIVE seems to be "do whatever you want", which is
always good.

> And what does the following mean ? It is placed just before the sentences
> you quoted first.
> 
>   If a cursor is open, and the SQL-transaction in which the cursor was
>   opened makes a significant change to SQL-data, then whether that
>   change is visible through that cursor before it is closed is determined
>   as follows:

There seem to be two sections, one dealing with seeing change made by
the same transaction:

         If a cursor is open, and the SQL-transaction in which the cursor
         was opened makes a significant change to SQL-data, then whether
         that change is visible through that cursor before it is closed is
         determined as follows:

and then another talking about holdable cursors after the cursor
transaction completes:

         If a holdable cursor is open during an SQL-transaction T and it
         is held open for a subsequent SQL-transaction, then whether any
         significant changes made to SQL-data (by T or any subsequent SQL-
         transaction in which the cursor is held open) are visible through
         that cursor in the subsequent SQL-transaction before that cursor is
         closed is determined as follows:


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Fri, 28 Mar 2003 18:33:49 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: updateable cursors & visibility
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> 
> Hiroshi Inoue wrote:
> > > > If the cursor is INSENSITIVE, it mustn't see the row ?
> > > 
> > > Right.
> > 
> > If so, isn't the difference between SENSITIVE and INSENSITIVE extreme ?
> 
> Yes.
> 
> > Why do you or Peter refer to ASENSITIVE little ?
> 
> Not sure --- ASENSITIVE seems to be "do whatever you want", which is
> always good.
> 
> > And what does the following mean ? It is placed just before the 
> sentences
> > you quoted first.
> > 
> >   If a cursor is open, and the SQL-transaction in which the cursor was
> >   opened makes a significant change to SQL-data, then whether that
> >   change is visible through that cursor before it is closed is 
> determined
> >   as follows:
> 
> There seem to be two sections, one dealing with seeing change made by
> the same transaction:

May I ask again ?
Must a SENSITIVE cursor see other applications' changes made
while the cursor is open ?

regards,
Hiroshi Inoue 


Sat, 29 Mar 2003 10:03:41 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: updateable cursors & visibility
Hiroshi Inoue wrote:
> > -----Original Message-----
> > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> > 
> > Hiroshi Inoue wrote:
> > > > > If the cursor is INSENSITIVE, it mustn't see the row ?
> > > > 
> > > > Right.
> > > 
> > > If so, isn't the difference between SENSITIVE and INSENSITIVE extreme ?
> > 
> > Yes.
> > 
> > > Why do you or Peter refer to ASENSITIVE little ?
> > 
> > Not sure --- ASENSITIVE seems to be "do whatever you want", which is
> > always good.
> > 
> > > And what does the following mean ? It is placed just before the 
> > sentences
> > > you quoted first.
> > > 
> > >   If a cursor is open, and the SQL-transaction in which the cursor was
> > >   opened makes a significant change to SQL-data, then whether that
> > >   change is visible through that cursor before it is closed is 
> > determined
> > >   as follows:
> > 
> > There seem to be two sections, one dealing with seeing change made by
> > the same transaction:
> 
> May I ask again ?
> Must a SENSITIVE cursor see other applications' changes made
> while the cursor is open ?

I think I see what you are saying, that the spec talks about seeing your
own transactions changes, and about seeing things after your transaction
commits, but doesn't really talk about seeing other transactions changes
while you are in the cursor's transaction.  Is that the point?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Fri, 28 Mar 2003 21:04:17 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: updateable cursors & visibility
Hiroshi Inoue writes:

> Must a SENSITIVE cursor see other applications' changes made
> while the cursor is open ?

Yes.  It is immaterial whether the change came from a different
application or the same one.

Nevertheless, the cursor sensitivity does not excuse you from observing
the transaction isolation level.  So even if the cursor is sensitive you
should not be able to see other transactions' changes if you are in a
serializable transaction.

-- 
Peter Eisentraut   peter_e@gmx.net


Sat, 29 Mar 2003 16:42:46 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: updateable cursors & visibility
> -----Original Message-----
> From: Peter Eisentraut [mailto:peter_e@gmx.net] 
> 
> Hiroshi Inoue writes:
> 
> > Must a SENSITIVE cursor see other applications' changes made
> > while the cursor is open ?
> 
> Yes.  It is immaterial whether the change came from a different
> application or the same one.

If I recognize the standard correctly, SENSITIVE cursors don't
have to see the changes of other transactions.

regards,
Hiroshi Inoue


Sun, 30 Mar 2003 07:00:06 +0900
"Hiroshi Inoue" <inoue@tpf.co.jp>
Re: updateable cursors & visibility
Peter Eisentraut <peter_e@gmx.net> writes:
> Hiroshi Inoue writes:
>> Must a SENSITIVE cursor see other applications' changes made
>> while the cursor is open ?
> Yes.  It is immaterial whether the change came from a different
> application or the same one.
> Nevertheless, the cursor sensitivity does not excuse you from observing
> the transaction isolation level.  So even if the cursor is sensitive you
> should not be able to see other transactions' changes if you are in a
> serializable transaction.

Serializable or not, there is a good case for saying that cursors don't
see changes made after they are opened, period.  The current
implementation locks down the cursor's snapshot at DECLARE time.  If we
allow the snapshot to change later, what in the world will the semantics
be?  Will we go back to re-scan rows that we previously skipped?  I do
not think we have a prayer of making consistent, predictable behavior
that works any other way.

			regards, tom lane


Sun, 30 Mar 2003 15:32:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: updateable cursors & visibility 
Tom Lane wrote:
> 
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Hiroshi Inoue writes:
> >> Must a SENSITIVE cursor see other applications' changes made
> >> while the cursor is open ?
> > Yes.  It is immaterial whether the change came from a different
> > application or the same one.
> > Nevertheless, the cursor sensitivity does not excuse you from observing
> > the transaction isolation level.  So even if the cursor is sensitive you
> > should not be able to see other transactions' changes if you are in a
> > serializable transaction.
> 
> Serializable or not, there is a good case for saying that cursors don't
> see changes made after they are opened, period.  The current
> implementation locks down the cursor's snapshot at DECLARE time. 

It's only because PostgreSQL( or other MVCC based DBMS) is
easy and natural to implement cursors in such a way. However,
It isn't a requirement of the SQL standard, IIRC.

As for ODBC, ODBC has the following cursor types about the
visibility of other changes.
1) static
   It never detects other changes.
2) dynamic
   It can detect any changes made to the membership, order,
   and values of the result set after the cursor is opened.
3) keyset-driven
   It always detects changes to the values of rows.
4) mixed
   A combination of a keyset-driven cursor and a dynamic
   cursor.   

It's not clear to me now how we should realize the above
type of cursors at server side.

regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/


Mon, 31 Mar 2003 09:40:02 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: updateable cursors & visibility
Hiroshi Inoue kirjutas E, 31.03.2003 kell 03:40:
> Tom Lane wrote:
> > 
> > Serializable or not, there is a good case for saying that cursors don't
> > see changes made after they are opened, period.  The current
> > implementation locks down the cursor's snapshot at DECLARE time. 
> 
> It's only because PostgreSQL( or other MVCC based DBMS) is
> easy and natural to implement cursors in such a way. However,
> It isn't a requirement of the SQL standard, IIRC.
> 
> As for ODBC, ODBC has the following cursor types about the
> visibility of other changes.
> 1) static
>    It never detects other changes.

This seems the clearest ?

> 2) dynamic
>    It can detect any changes made to the membership, order,
>    and values of the result set after the cursor is opened.

What would it mean in practice, i.e. if you are on the first row in the
cursor and then update tha ORDER BY field so that your row becomes the
last one, will the next FETCH move the cursor past end ?

what happens, if the row you are on is removed from the keyset, either
by current or any other backend ?

What about the case when you have moved past the last row, and suddenly
a new row appears which is positioned after the last row ?

What about when you are moving over several rows that have the same
ordering position and then one more appears - should it go before or
after the current position ?

> 3) keyset-driven
>    It always detects changes to the values of rows.

What about _new_ rows, or rows that no more belong to the "keyset" ?

> 4) mixed
>    A combination of a keyset-driven cursor and a dynamic
>    cursor.   

Combined in what way ?

> It's not clear to me now how we should realize the above
> type of cursors at server side.

>From your short description it is not even clear for me how *exactly*
should they behave.

------------------
Hannu


31 Mar 2003 15:34:11 +0300
Hannu Krosing <hannu@tm.ee>
Re: updateable cursors & visibility
> -----Original Message-----
> From: Hannu Krosing [mailto:hannu@tm.ee] 
> 
> Hiroshi Inoue kirjutas E, 31.03.2003 kell 03:40:
> 
> > 2) dynamic
> >    It can detect any changes made to the membership, order,
> >    and values of the result set after the cursor is opened.
> 
> What would it mean in practice, i.e. if you are on the first 
> row in the
> cursor and then update tha ORDER BY field so that your row becomes the
> last one, will the next FETCH move the cursor past end ?

No. The row next to the old first row would be FETCHed.
 
> what happens, if the row you are on is removed from the keyset, either
> by current or any other backend ?

The dynamic cursor doesn't see the row any longer.
 
> What about the case when you have moved past the last row, 
> and suddenly
> a new row appears which is positioned after the last row ?

The dynamic cursor would see the new row when it is fetched backwards.
 
> What about when you are moving over several rows that have the same
> ordering position and then one more appears - should it go before or
> after the current position ?

It is implementation dependent.
 
> > 3) keyset-driven
> >    It always detects changes to the values of rows.
> 
> What about _new_ rows, 

It never detects new rows. 

> or rows that no more belong to the "keyset" ?

They are the same as deleted ones.
 
> > 4) mixed
> >    A combination of a keyset-driven cursor and a dynamic
> >    cursor.   
> 
> Combined in what way ?

It uses a limited keyset size. If a fetch operation doesn't fit in
the range of the keyset, it dynamically fetches the result set.
 
> > It's not clear to me now how we should realize the above
> > type of cursors at server side.
> 
> >From your short description it is not even clear for me how *exactly*
> should they behave.

I only intended to illustrate various type of visibilities roughly
because
there were no such reference in this thread.

regards,
Hiroshi Inoue


Tue, 1 Apr 2003 01:08:52 +0900
"Hiroshi Inoue" <inoue@tpf.co.jp>
Re: updateable cursors & visibility
Hiroshi Inoue kirjutas E, 31.03.2003 kell 19:08:
> > -----Original Message-----
> > From: Hannu Krosing [mailto:hannu@tm.ee] 
> > 
> > Hiroshi Inoue kirjutas E, 31.03.2003 kell 03:40:
> > 
> > > 2) dynamic
> > >    It can detect any changes made to the membership, order,
> > >    and values of the result set after the cursor is opened.
> > 
> > What would it mean in practice, i.e. if you are on the first 
> > row in the
> > cursor and then update tha ORDER BY field so that your row becomes the
> > last one, will the next FETCH move the cursor past end ?
> 
> No. The row next to the old first row would be FETCHed.

In what way would the _changes_made_to_the_order_ be reflected then ?
 
> > what happens, if the row you are on is removed from the keyset, either
> > by current or any other backend ?
> 
> The dynamic cursor doesn't see the row any longer.

It seems to be doable with MVCC - "just" ;) check for visibility of
underlying tuples at each fetch. At least it does not seem any harder
for MVCC than for other CC methods.

>  
> > > 3) keyset-driven
> > >    It always detects changes to the values of rows.
> > 
> > What about _new_ rows, 
> 
> It never detects new rows. 

Then I must have misunderstood the "can detect any changes made to the
membership, order, and values" part. I assumed that "any changes" wold
also include rows that magically become part of the query by either
changes in values or being inserted.

> > or rows that no more belong to the "keyset" ?
> 
> They are the same as deleted ones. 

So they are no more visible to cursor ?

> > >From your short description it is not even clear for me how *exactly*
> > should they behave.
> 
> I only intended to illustrate various type of visibilities roughly
> because
> there were no such reference in this thread.
> 
> regards,
> Hiroshi Inoue
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org


31 Mar 2003 19:43:29 +0300
Hannu Krosing <hannu@tm.ee>
Re: updateable cursors & visibility
Tom Lane writes:

> Serializable or not, there is a good case for saying that cursors don't
> see changes made after they are opened, period.

No one disputes that insensitive cursors are a valid concept.  But this
discussion is about updating such a cursor.  What view of the data would
such a cursor represent after an update?  What about this example:

CREATE TABLE foo (a int PRIMARY KEY);
INSERT INTO foo VALUES (1);
... much later ...
BEGIN;
DECLARE test INSENSITIVE CURSOR FOR UPDATE FOR SELECT a FROM foo;
INSERT INTO foo VALUES (2);
FETCH NEXT FROM test;
UPDATE foo SET a = 2 WHERE CURRENT OF test;
...
COMMIT;

Does the UPDATE catch the constraint violation?

> If we allow the snapshot to change later, what in the world will the
> semantics be?  Will we go back to re-scan rows that we previously
> skipped?

Clearly this issue is yucky, that's why they probably invented asensitive
cursors.  I wouldn't be surprised to learn that there was some existing
implementation that had updatable insensitive cursors, but the working
group decided it wasn't sound and invented a separate half-way type for
it.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 31 Mar 2003 19:59:41 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: updateable cursors & visibility 
> -----Original Message-----
> From: Hannu Krosing [mailto:hannu@tm.ee] 
> 
> Hiroshi Inoue kirjutas E, 31.03.2003 kell 19:08:
> > > -----Original Message-----
> > > From: Hannu Krosing [mailto:hannu@tm.ee] 
> > > 
> > > Hiroshi Inoue kirjutas E, 31.03.2003 kell 03:40:
> > > 
> > > > 2) dynamic
> > > >    It can detect any changes made to the membership, order,
> > > >    and values of the result set after the cursor is opened.
> > > 
> > > What would it mean in practice, i.e. if you are on the first 
> > > row in the
> > > cursor and then update tha ORDER BY field so that your 
> row becomes the
> > > last one, will the next FETCH move the cursor past end ?
> > 
> > No. The row next to the old first row would be FETCHed.
> 
> In what way would the _changes_made_to_the_order_ be reflected then ?

Once a rowset is fetched, the membership or order in the rowset is
fixed until the rowset is invalidated by another fetch operation and
the fetch operation is executed for the rowset.

> > > what happens, if the row you are on is removed from the 
> keyset, either
> > > by current or any other backend ?
> > 
> > The dynamic cursor doesn't see the row any longer.
> 
> It seems to be doable with MVCC - "just" ;) check for visibility of
> underlying tuples at each fetch. At least it does not seem any harder
> for MVCC than for other CC methods.

MVCC has no particular advantage at this point anyway.
  
> > > > 3) keyset-driven
> > > >    It always detects changes to the values of rows.
> > > 
> > > What about _new_ rows, 
> > 
> > It never detects new rows. 
> 
> Then I must have misunderstood the "can detect any changes made to the
> membership, order, and values" part. I assumed that "any changes" wold
> also include rows that magically become part of the query by either
> changes in values or being inserted.

It's a spec of dynamic cursors not of keyset-driven ones.
 
> > > or rows that no more belong to the "keyset" ?
> > 
> > They are the same as deleted ones. 
> 
> So they are no more visible to cursor ?

Yes.
 
regards,
Hiroshi Inoue


Wed, 2 Apr 2003 06:26:26 +0900
"Hiroshi Inoue" <inoue@tpf.co.jp>
Re: updateable cursors & visibility
On Mon, 31 Mar 2003, Peter Eisentraut wrote:

> Tom Lane writes:
> 
> > Serializable or not, there is a good case for saying that cursors don't
> > see changes made after they are opened, period.
> 
> No one disputes that insensitive cursors are a valid concept.  But this
> discussion is about updating such a cursor.  What view of the data would
> such a cursor represent after an update?  What about this example:
> 
> CREATE TABLE foo (a int PRIMARY KEY);
> INSERT INTO foo VALUES (1);
> ... much later ...
> BEGIN;
> DECLARE test INSENSITIVE CURSOR FOR UPDATE FOR SELECT a FROM foo;
> INSERT INTO foo VALUES (2);
> FETCH NEXT FROM test;
> UPDATE foo SET a = 2 WHERE CURRENT OF test;
> ...
> COMMIT;
> 
> Does the UPDATE catch the constraint violation?

Good point. There is no direct reference to this condition in SQL99 -- as
far as I can tell. We do have this however in SQL99 14.9 General Rules:

4) The extent to which an SQL-implementation may disallow independent
changes that are not significant is implementation-defined

Where 'independent' means a change not made by <update
statement: positioned> or <delete statement: positioned> and 'significant'
means that, had the change been made before the cursor was opened, the
underlying table of the cursor would be different in some respect. If the
cursor is insensitive, then significant changes are not visible by the
cursor.

So, for insensitive cursors we could disallow changes independent of the
cursor. I don't think this is useful but it suggests that sensitive
cursors should allow independent changes and that these should be visible
to the cursor. But that isn't really the issue.

We also get the following in SQL99 <update statement: positioned> General
Rules:

8) If, while CR is open, an object row has been marked for deletion by any
<delete statement: searched>, marked for deletion by any <delete
statement: positioned> that identifies any cursor other than CR, updated
by any <update statement: searched>, or updated by any <update
statement: positioned> that identifies any cursor other than CR, then a
completion condition is raised: warning  cursor operation conflict.

But this just muddies the waters. I think it comes down to
this: insensitive cursors should behave as they currently do. If they are
used by update/delete statement: positioned, they still need to adhere to
the normal visibility of UPDATE or DELETE -- which is what, I think, Bruce
originally proposed.

So, I would like to go ahead and implement update/delete
statement: positioned (regardless of the nature of visibility we decide
on). Bruce's proposal basically sees the tid of the last FETCH'd or MOVE'd
to tuple stored in the Portal structure for that cursor. This shouldn't be
hard since DoRelativeFetch() calls ExecutorRun() for non-holdable cursors,
which returns the last TupleTableSlot returned, which gives access to the
tid of the last FETCH'd tuple. Likewise, DoRelativeStoreFetch() seems to
have direct access to the data required, covering holdable cursors.

This means that when we handle an update/delete statement: positioned, we
could either do a rough hack and look up the Portal for the named cursor
inside of the parse and analyze code and fill out a where clause Node to
the effect of 'ctid = <saved tid>'.

Alternatively, we could just register that it is a positioned update/delete 
and look it up somewhere else: planner, executor...?

Regardless of which, we could insert a special case in ExecutePlan() (or
somewhere more appropriate?) to test that the tuple returned from the
lower level ExecTidScan() still satisifies the cursor query. It should be
sufficient to use HeapTupleSatisfies() or some of the logic there in to do
this. If all goes well, then the update/delete will succeed.

Comments?

Gavin


Thu, 3 Apr 2003 00:52:54 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: updateable cursors & visibility 
Gavin Sherry <swm@linuxworld.com.au> writes:
> Regardless of which, we could insert a special case in ExecutePlan() (or
> somewhere more appropriate?) to test that the tuple returned from the
> lower level ExecTidScan() still satisifies the cursor query. It should be
> sufficient to use HeapTupleSatisfies() or some of the logic there in to do
> this.

I don't think so; you'll need to use EvalPlanQual.  This is not
different from the situation where a regular UPDATE finds a tuple that is
not the latest version of its row.

Should I point out that we already have semantics for that behavior, and
they depend on the serialization mode?  Most of this discussion seems to
completely ignore the MVCC semantics we already have for updates/deletes
applied to rows that aren't current anymore.  ISTM we should stick to
that behavior ...

			regards, tom lane


Wed, 02 Apr 2003 09:58:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: updateable cursors & visibility 