changes to the schema while hibernate is running

Question

Hi all,

I'm working on a high usage system which maps a few "read only" tables and views. At various points in time I need to replace the data in these tables with a newer version by connecting to the database externally.

We are using postgres 8.3+ and hibernate 3.2.6. The external "update" process will be connecting to the db via PSQL.

For some of the smaller tables, a truncate and insert within a transaction should be satisfactory.

However some of the tables are quite large and the truncate/insert method will probably lock the table for too long.

In this case I am considering either:

1) table rename - ie load new data into table_b while hibernate is mapped to table_a and then perform a table rename from table_b->table_a via PSQL

-or-

2) always map hibernate to a view, and when table_b is populated re-define the view to route to table_b instead of the now redundant table_a.

-or-

3) create and populate a new schema, and change the hibernate user's search path once the new schema is ready

I'm not 100% sure of the repercussions of these approaches within hibernate, and although I am about to test as best I can it will be hard to test collisions (ie a hibernate query to the table at the point it is being renamed) so some theoretical opinions would be much appreciated.

if anyone has attempted similar approaches or knows of the potential pitfalls I'd be very grateful..

p.

Answer 1

You could take advantage of PostgreSQL's MVCC by renaming the tables inside a transaction. This will ensure atomicity from the point of view of any other running and future transactions.

From the point of view of Hybernate, invalidating the cache should be enough to ensure the freshness of retrieved data.