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

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

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

On Wed, 5 Dec 2007, Michael Han wrote:

> The following bug has been logged online:
>
> Bug reference: 3800
> PostgreSQL version: 8.2
> Description: Java/Postgres PreparedStatement returns stale data
> Details:
>
> PROBLEM : Java/Postgres Returns Stale data when underlying Table View is
> Replaced

Prior to the 8.3 release, prepared plans don't notice when underlying
objects change. Aside from waiting for 8.3, the only workaround is to
prevent the JDBC driver from reusing the same server plan. By default
the JDBC driver reuses the server plan after the 5th execution, and you
can adjust this by the prepareThreshold URL parameter. You can disable
plan reuse by adding prepareThreshold=0.

Kris Jurka

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

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