Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
266 views
in Technique[技术] by (71.8m points)

postgresql - changes to the schema while hibernate is running

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...