On 13 Apr 2012, at 18:43, Jeff Adams wrote:
> Thanks for the ideas Steve. I am actually working with a partitioned table and the field I am modifying is the id
field(I have reached the cap on the integer data type and need to modify it to bigint - very poor planning on my
part!),but no related tables exist. The id field in the partitioned tables is inherited, so I figured I needed to alter
thecolumn in the parent table. Does this information point towards an optimal solution? Thanks again...
The optimal solution most likely involves the table rewrite that you're trying to prevent.
Do you really need that id column though? It seems like it's a surrogate key - perhaps the real primary key for that
datais a combination of some other columns? You say you partitioned that table, so the column you partitioned on must
bepart of that "new" PK in that case. That won't work if you depend on that id-column for other things, of course, or
ifit's really the only thing that guarantees that your records are unique (then again, are duplicates useful in any
way?).
...If that doesn't work either, I just got a really dangerous idea for an alternate solution: I was just wondering
whetheryou might have a field with 4 bytes of spare length _before_ the id column?
If you alter the pg_attributes, reducing that field with 4 bytes, and increase the id-column to bigint (+4 bytes), you
mightend up with a working solution.
The id's in the "resized" field would probably be really weird though and quite likely invalid for the data-type (those
last4 bytes you'd borrow of the previous column better be empty!). Plus, I'm assuming there's nothing between those
fieldson disk - in reality there's padding going on and such. As I said, it's a dangerous idea!
It will probably get shot down by people with more knowledge about the on-disk data structure right away. For example,
ifthere's any kind of pointer to the start of your id-column, well... Also, it would probably kill any kind of
replicationon this table and will likely cause mayhem in the WAL logs as well, so you'd at least have to make sure
thereare no transactions going on (including autovacuum).
Still, if it works in your case then it's a possible solution :P
If you're going to experiment with this, you should really try it on a separate database (preferably on a different
server)with some test data first. There's definitely a risk of losing everything in the database. It's probably a
_really_bad idea :P
Alban Hertroys
--
The scale of a problem often equals the size of an ego.