Re: Updating pg_attribute to change field's data type from integer to bigint on very large table

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 the column 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 data is a combination of some other columns? You say you partitioned that table, so the column you partitioned on must be part of that "new" PK in that case. That won't work if you depend on that id-column for other things, of course, or if it'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 whether you 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 might end 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 last 4 bytes you'd borrow of the previous column better be empty!). Plus, I'm assuming there's nothing between those fields on 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, if there's any kind of pointer to the start of your id-column, well... Also, it would probably kill any kind of replication on this table and will likely cause mayhem in the WAL logs as well, so you'd at least have to make sure there are 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.