Thread: Bug with deleteRow() for the ResultSet using PostgreSQL 7.4 (Build 213)driver.

If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports@postgresql.org.

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs@postgresql.org.

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches@postgresql.org instead.  Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name         :  Chris Pesarchick
Your email address      :  ChrisPesarchick@westfieldgrp.com


System Configuration
---------------------
  Architecture (example: Intel Pentium)   :  Intel IBM A22m Laptop

  Operating System (example: Linux 2.4.18)      :  Linux

  PostgreSQL version (example: PostgreSQL-7.4.2):   PostgreSQL-7.4.2

  Compiler used (example:  gcc 2.95.2)          :  3.3-23

  JDBC Driver                             :  PostgreSQL 7.4 (Build 213)


Please enter a FULL description of your problem:
------------------------------------------------
I am having an issue with the driver when I do a deleteRow using a
ResultSet record.  It skips records.
For example, if I have seven records and I select all of them in a
ResultSet, spin through each one and call deleteRow() the result is wrong.
The 1st, 3rd, 5th, and 7th records are deleted, but the others are not.






Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

/**
       * This is an example of using the deleteRow.
       * Here are some of the things I found.  For this method to work
       * there must be a few things done.
       * 1.  Must use CONCUR_UPDATABLE in the createStatement
       * 2.  There must be primary keys, if you don't have any, use 'oid'
field
       * in the select that creates the resultset.
       * @throws SQLException
       */
      public void deleteRow() throws SQLException {
            System.out.println("Start deleteRow");
            Statement statement = null;
            ResultSet resultSet = null;
            int i = 1;
            statement =
connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String sql = "select oid, * from perf_data where
username='cpesarchick'";  //This can be any select statement
            //statement.setFetchSize(1000);

            resultSet = statement.executeQuery( sql );
            resultSet.last();
            System.out.println("row count: " + resultSet.getRow());
            resultSet.beforeFirst();
            while ( resultSet.next()) {

                  System.out.println( "row #: " + resultSet.getRow());
                  System.out.println("Deleting Row:" + i);
                  resultSet.deleteRow();
                  System.out.println("Done Deleting Row:" + i);
                  System.out.println( "row #: " + resultSet.getRow());
                  i++;
            }
            resultSet.close();
            statement.close();
            System.out.println("End deleteRow");
      }



If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

I don't know, but my suspicions are with the Vector which holds all the
rows in the ResultSet.



This electronic message transmission contains information from a member company of Westfield Group which is privileged,
confidentialor otherwise the exclusive property of the intended recipient or that member company of Westfield Group.
Thisinformation is intended for the use of the individual or entity that is the intended recipient.  If you are not the
designatedrecipient, please be aware that any dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this electronic transmission in error, please notify us by telephone at
1.800.243.0210,extension 2153, or by electronic e-mail (help@westfieldgrp.com) and promptly destroy the original
transmission.

On Tue, 8 Jun 2004 ChristopherPesarchick@westfieldgrp.com wrote:

>   JDBC Driver                             :  PostgreSQL 7.4 (Build 213)
>
> I am having an issue with the driver when I do a deleteRow using a
> ResultSet record.  It skips records.
> For example, if I have seven records and I select all of them in a
> ResultSet, spin through each one and call deleteRow() the result is wrong.
> The 1st, 3rd, 5th, and 7th records are deleted, but the others are not.
>

Behind the scenes the rows are stored in a Vector and there is an "int
current_row" pointer to the current row.  The problem is that when a row
is deleted from the Vector, how should the current_row value be adjusted?
Currently nothing happens so current_row effectively points to the next
row so that when next() is called that row is skipped.  Simply
decrementing current_row on a delete won't work because it introduces the
exact same problem except with previous().  When I looked at this problem
before I wanted to see if a cleaner solution presented itself than adding
a new variable like "boolean onDeletedRow" that would need to be
checked or reset in a significant number of places, but perhaps that's the
only way to go.

Kris Jurka

On Fri, 11 Jun 2004, Kris Jurka wrote:

> On Tue, 8 Jun 2004 ChristopherPesarchick@westfieldgrp.com wrote:
>
> >   JDBC Driver                             :  PostgreSQL 7.4 (Build 213)
> >
> > I am having an issue with the driver when I do a deleteRow using a
> > ResultSet record.  It skips records.
> > For example, if I have seven records and I select all of them in a
> > ResultSet, spin through each one and call deleteRow() the result is wrong.
> > The 1st, 3rd, 5th, and 7th records are deleted, but the others are not.
> >
>
> Behind the scenes the rows are stored in a Vector and there is an "int
> current_row" pointer to the current row.  The problem is that when a row
> is deleted from the Vector, how should the current_row value be adjusted?
> Currently nothing happens so current_row effectively points to the next
> row so that when next() is called that row is skipped.  Simply
> decrementing current_row on a delete won't work because it introduces the
> exact same problem except with previous().  When I looked at this problem
> before I wanted to see if a cleaner solution presented itself than adding
> a new variable like "boolean onDeletedRow" that would need to be
> checked or reset in a significant number of places, but perhaps that's the
> only way to go.
>

Doing some more reading it appears the ResultSet is supposed to be
positioned on the previous row, so I have made it do so in both the 7.4
and 7.4 cvs versions.

Kris Jurka