postgresql - Change integer to bigint key in a table without long exclusive lock -

is possible inherit table column of type int table same column of type bigint in postgresql 9.1?

changing column type bigint if first table not option.

i have table several tens of millions entries has id of int. right id approaching 2^32, , thinking if it's reasonable thing create facade table bigint id , let original 1 inherit facade. make sense?

no, not possible change type of column of parent table when inherit table.

the column type must match, since when query parent table (without only in from clause) postgresql implicitly scans child tables , appends results. can't if types differ, same way can't union tables of different types.

update based on comments:

use view , do instead trigger

i suggest using view that's union of 2 tables, old table's contents upcast bigint. define instead of trigger redirects inserts new table.

if updates , deletes should define check constraint on each table restricts range of ids non-overlapping range, decide table route delete or update based on id.

for updates transform delete ... returning , insert (possibly in wcte) move row old table new 1 part of update.

you will incur performance penalty this, you'll avoid need full table rewrite.

progressively change key type in-place

you changing key type isn't option, seem mean "changing key type in manner requires full table rewrite under exclusive lock not option".

what can is:

• alter table ... add column new_key bigint; . not mark not null or give default
• add before insert or update ... before each row ... trigger table copies integer id column bigint id column, e.g. new.new_id := new.id
• in batches, update table copy integer key bigint column, , vacuum after each batch
• once new , existing rows have bigint key, create unique index on using create unique index ... concurrently.
• once index created, add not null constraint. unfortunately sequential scan validate constraint. if can't tolerate that, there hacks work around it, i'm not prepared advise them in public because need know exactly you're doing safely, , use in right circumstances.
• begin transaction, drop trigger, drop old primary key constraint , old id column, , add new primary key constraint on bigint key, specifying existing index created concurrently constraint index, commit. avoids need build index under exclusive lock.

this process better if postgresql supported adding not null constraint not valid, let validate weaker lock. doesn't yet so, unfortunately. patches or other contributions welcome.

you benefit weaker locks taken alter table operations on postgresql 9.5.

theoretically postgresql support alter table ... alter type ... concurrently doing behind scenes. there'd lot of work in doing though, don't anticipate seeing simple , canned approach in near future.