0

I have a use case where i dynamically add new columns to db.The alter table works fine when no select queries are running on the db table.But if alter query is running at the same time when some heavy (i mean time consuming ) select query is running then alter query waits for the select query to finish.

My data is huge and select query may take a very long time .So the jdbc connections for alter table times out.

How can i go about this problem? Can i some how do this asynchronously? I mean fire alter query to db and break the connection and then let alter query be executed whever db can execute it.

Is this possible. Id i break the jdbc connection is the query execution aborted?

Kalle Richter
  • 8,008
  • 26
  • 77
  • 177
user93796
  • 18,749
  • 31
  • 94
  • 150
  • Altering the table structure of a live database in this way sounds to me like you might want to revisit this design in general. However to help solve your immediate problem, what database are you using? – Nick Holt May 17 '13 at 08:14
  • its a columnar db, some custom version of postgres – user93796 May 17 '13 at 08:24
  • You basically need to fire and forget your column add instruction - with the likes of Oracle there are messaging features that would allow you to do this, but with PostgreSQL I'm not sure this type of mechanism exists for the database to receive messages or some equivalent :-( – Nick Holt May 17 '13 at 09:05
  • Table structure is normally quite stable in time, it's not recommended to modify it outside specific tasks like version upgrade. What is your need? Can't you just use a separate table with a column `column name` and a column `value` to provide quite dynamic behavior? – gma May 17 '13 at 10:05
  • @gma .I know but we had to do that.W adopted this kind of desing after giving a lot of thought.sorry but i cant give out the details about the exact use case – user93796 May 17 '13 at 12:55

0 Answers0