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
);
