[BUGS] BUG #3800: Java/Postgres PreparedStatement returns stale data

[Michael Han]

The following bug has been logged online:

Bug reference:      3800
Logged by:          Michael Han
Email address:      [EMAIL PROTECTED]
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
);

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq