BUG #3800: Java/Postgres PreparedStatement returns stale data - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      3800
Logged by:          Michael Han
Email address:      michael.han@ngc.com
PostgreSQL version: 8.2
Operating system:   Windows
Description:        Java/Postgres PreparedStatement returns stale data
Details:

PROBLEM :  Java/Postgres Returns Stale data when underlying Table View is
Replaced
*** tables provided below

Steps
1.    CREATE table_1 and table_2
2.    INSERT table_1 and table_2 values
3.    CREATE view for table_1
4.      Open 2 SQL Text Dialogs through PGADMIN
5.      In PGADMIN dialog 1 and 2 run query Select * from table_view;  --
results should be the same

6.    create a java that follows the pseudocode  *** must use Preparedstatment
and
PreparedStatement stmt = “select * from table_view”;
do{
    query stmt
    print out results
    Thread.sleep (1000);

}while(true)
7.    While java program is running in loop, in a PGADMIN console REPLACE view
to table_2
8.      In PGADMIN dialog 2 rerun query. THEY ARE THE SAME as dialog 1! They
should be differnt.
9.    COMPARE JAVA output from Step 3 table view and Step 7 table view. THEY
ARE THE SAME!
10.    Postgres appears to not refresh table view in java program and returns
stale data, This also happends in PGADMIN


--- TABLE/VIEWS
CREATE TABLE table_1 (
   set       TEXT,
   value     TEXT
);


CREATE TABLE table_2 (
   set       TEXT,
   value     TEXT
);

Insert into table_1 (set, value) values ('t1_v1', 't1_v1');
Insert into table_1 (set, value) values ('t1_v2', 't1_v2');

Insert into table_2 (set, value) values ('t2_v1', 't2_v1');
Insert into table_2 (set, value) values ('t2_v2', 't2_v2');


-- *** use this for step 3
-- view for table_1
CREATE OR REPLACE VIEW table_view AS  (
    Select * from table_1
);

-- *** use this for step 7
-- view for table_2
CREATE OR REPLACE VIEW table_view AS  (
    Select * from table_2
);