Re: Thoughts on how to avoid a massive integer update.

[Rob Sargent <robjsargent@xxxxxxxxx>]

Well as I said, I think you could add a column to info_table

alter table info_table add orig_id int;

update info_table set orig_id = info_table_sid;

update info_table set info_table_sid = 456 where info_table_sid = 456; 

alter table data_table drop reference NOT SQL

alter table data_table make reference to info_table.orig_id NOT SQL

you have to do the second block per orig_id

and the third block per table

at your reports needing the new value will of course need to do the join and get the updated value which now resides in the ill-name info_table_sid.  

This leaves “orig_id” as just an id and “info_table_sid” as an editable attribute

Nothing is broken other than the per-table lock while you switch the foreign key

On May 8, 2020, at 1:36 PM, Fehrle, Brian <bfehrle@xxxxxxxxxxxx> wrote:

 

 

From: Rob Sargent <robjsargent@xxxxxxxxx>
Date: Friday, May 8, 2020 at 11:05 AM
To: "Fehrle, Brian" <bfehrle@xxxxxxxxxxxx>
Cc: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>, "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Subject: Re: Thoughts on how to avoid a massive integer update.

 

[External Email]

   Could you show an example table relationship?

It’s a simple one-to-many relationship:
*Info_table*
info_table_sid integer


*data_table*
data_table_sid integer,
info_table_id integer references info_table(info_table_sid),

 

Right, and now you wish to change the values in the referenced table (info_table.info_table_sid) correct?  

 

 

Correct. If info_table.info_table_sid = 123 and many rows in data_table point to it, the ID needs to be changed to 456 in the info_table, as well as all the columns in the data_table.