Re: UPDATE many records

> On Jan 7, 2020, at 12:01 PM, Adrian Klaver <[email protected]> =
wrote:
>=20
> On 1/7/20 12:47 PM, Israel Brewster wrote:
>> One potential issue I just thought of with this approach: disk space. =
Will I be doubling the amount of space used while both tables exist? If =
so, that would prevent this from working - I don=E2=80=99t have that =
much space available at the moment.
>=20
> It will definitely increase the disk space by at least the data in the =
new table. How much relative to the old table is going to depend on how =
aggressive the AUTOVACUUM/VACUUM is.
>=20
> A suggestion for an alternative approach:
>=20
> 1) Create a table:
>=20
> create table change_table(id int, changed_fld some_type)
>=20
> where is is the PK from the existing table.
>=20
> 2) Run your conversion function against existing table with change to =
have it put new field value in change_table keyed to id/PK. Probably do =
this in batches.
>=20
> 3) Once all the values have been updated, do an UPDATE set =
changed_field =3D changed_fld from change_table where existing_table.pk =
=3D change_table.id;

Makes sense. Use the fast SELECT to create/populate the other table, =
then the update can just be setting a value, not having to call any =
functions. =46rom what you are saying about updates though, I may still =
need to batch the UPDATE section, with occasional VACUUMs to maintain =
disk space. Unless I am not understanding the concept of =E2=80=9Ctuples =
that are obsoleted by an update=E2=80=9D, which is possible.

>=20
>> ---
>> Israel Brewster
>> Software Engineer
>> Alaska Volcano Observatory
>> Geophysical Institute - UAF
>> 2156 Koyukuk Drive
>> Fairbanks AK 99775-7320
>> Work: 907-474-5172
>> cell:  907-328-9145
>=20
>=20
> --=20
> Adrian Klaver
> [email protected]