Re: UPDATE many records

>=20
> On Jan 7, 2020, at 12:57 PM, Adrian Klaver <[email protected]> =
wrote:
>=20
> On 1/7/20 1:43 PM, Israel Brewster wrote:
>>> On Jan 7, 2020, at 12:21 PM, Adrian Klaver =
<[email protected] <mailto:[email protected]>> wrote:
>>>=20
>>> On 1/7/20 1:10 PM, Israel Brewster wrote:
>>>>> On Jan 7, 2020, at 12:01 PM, Adrian Klaver =
<[email protected] <mailto:[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 <http://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
>>> You are not. For a more thorough explanation see:
>>>=20
>>> =
https://www.postgresql.org/docs/12/routine-vacuuming.html#VACUUM-BASICS
>>>=20
>>> How much space do you have to work with?
>>>=20
>>> To get an idea of the disk space currently used by table see;
>>>=20
>>> =
https://www.postgresql.org/docs/12/functions-admin.html#FUNCTIONS-ADMIN-DB=
OBJECT
>> Oh, ok, I guess I was being overly paranoid on this front. Those =
functions would indicate that the table is only 7.5 GB, with another =
8.7GB of indexes, for a total of around 16GB. So not a problem after all =
- I have around 100GB available.
>> Of course, that now leaves me with the mystery of where my other =
500GB of disk space is going, since it is apparently NOT going to my DB =
as I had assumed, but solving that can wait.
>=20
> Assuming you are on some form of Linux:
>=20
> sudo du -h -d 1 /
>=20
> Then you can drill down into the output of above.

Yep. Done it many times to discover a runaway log file or the like. Just =
mildly amusing that solving one problem leads to another I need to take =
care of as well=E2=80=A6 But at least the select into a new table should =
work nicely. Thanks!

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory=20
Geophysical Institute - UAF=20
2156 Koyukuk Drive=20
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145
>=20
>> Thanks again for all the good information and suggestions!
>> ---
>> 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
>>>>>> ---
>>>>>> 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
>>>>> --
>>>>> Adrian Klaver
>>>>> [email protected] <mailto:[email protected]>
>>>=20
>>>=20
>>> --
>>> Adrian Klaver
>>> [email protected] <mailto:[email protected]>
>=20
>=20
> --=20
> Adrian Klaver
> [email protected]