Auto-Vacuum Isn't Re-enabled

[shayonj]

Auto-Vacuum Isn't Re-enabled

[jfrost]

I'm wrong about the table name:

az485-267","pid":2274,"level":30,"time":"2023-05-13T15:00:42.158+00:00","v":0,"msg":"Exception raised, rolling back query","rollback":true,"query":";\nALTER TABLE pgbench_accounts RENAME to pgosc_op_table_pgbench_accounts;\nALTER TABLE pgosc_st_pgbench_accounts_16727e RENAME to pgbench_accounts;\n\n\n\nALTER TABLE pgosc_st_pgbench_accounts_16727e SET (fillfactor=100);\nDROP TRIGGER IF EXISTS primary_to_audit_table_trigger ON pgbench_accounts;\n","version":"0.7.5"}
2023-05-13T15:00:42.4805123Z        +{"name":"pg-online-schema-change","hostname":"fv-az485-267","pid":2274,"level":60,"time":"2023-05-13T15:00:42.159+00:00","v":0,"msg":"Something went wrong: ERROR:  relation \"pgosc_st_pgbench_accounts_16727e\" does not exist\n","e":"ERROR:  relation \"pgosc_st_pgbench_accounts_16727e\" does not exist\n","version":"0.7.5"}

I'll fix that in my PR.

[jfrost]

Confirmed working:

jefftest=# create table books (id serial primary key, email text);
CREATE TABLE
Time: 9.127 ms
jefftest=# alter table books SET (fillfactor=50, autovacuum_enabled=false);
ALTER TABLE
Time: 1.107 ms

MacBook-Pro :: ~/git/pg-osc ‹main*› » bundle exec pg-online-schema-change perform \
  --alter-statement 'ALTER TABLE books RENAME COLUMN email TO new_email' \
  --dbname "jefftest" --host localhost --username jeff
<SNIP>
{"name":"pg-online-schema-change","hostname":"MacBook-Pro.attlocal.net","pid":7755,"level":30,"time":"2023-05-13T15:41:12.159-05:00","v":0,"msg":"Performing ANALYZE!","version":"0.8.1"}
INFO:  analyzing "public.books"
INFO:  "books": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
{"name":"pg-online-schema-change","hostname":"MacBook-Pro.attlocal.net","pid":7755,"level":30,"time":"2023-05-13T15:41:12.160-05:00","v":0,"msg":"Validating constraints!","version":"0.8.1"}
{"name":"pg-online-schema-change","hostname":"MacBook-Pro.attlocal.net","pid":7755,"level":30,"time":"2023-05-13T15:41:12.161-05:00","v":0,"msg":"All tasks successfully completed","version":"0.8.1"}

jefftest=# SELECT array_to_string(reloptions, ',') as params FROM pg_class WHERE relname='books';
┌────────────────────────────────────────┐
│                 params                 │
├────────────────────────────────────────┤
│ fillfactor=50,autovacuum_enabled=false │
└────────────────────────────────────────┘
(1 row)

[shayonj]

That sounds like a bug! I have some time set out this weekend, so will get to it. Thanks for reporting 🙏🏾

[jfrost]

Looks like it should probably. go right here. I guess we should store the state of whether vacuum is enabled/disabled so we only set it back to the previous setting after swap?

[shayonj]

So, we do capture any primary_table_storage_parameters so we can re-add them later. We capture it here

pg-osc/lib/pg_online_schema_change/orchestrate.rb

Lines 182 to 183 in 1305877

	result = Query.storage_parameters_for(client, client.table_name, true) || ""
	Store.set(:primary_table_storage_parameters, result)

And then add it back here

pg-osc/lib/pg_online_schema_change/orchestrate.rb

Lines 250 to 257 in 1305877

	puts primary_table_storage_parameters
	storage_params_reset =
	(
	if primary_table_storage_parameters.empty?
	""
	else
	"ALTER TABLE #{client.table_name} SET (#{primary_table_storage_parameters});"
	end

This is how we fetch the table storage params

pg-osc/lib/pg_online_schema_change/query.rb

Lines 292 to 294 in 1305877

	query = <<~SQL
	SELECT array_to_string(reloptions, ',') as params FROM pg_class WHERE relname='#{table}';
	SQL

So I think whats happening, the default autovacuum settings may not be getting returned and stored inside primary_table_storage_parameters. I think the storage params are only returned if its set manually. I will confirm and get back.

Otherwise we will basically need to find a way to "reset" the autovacuum settings.

[jfrost]

I was thinking a simple MVP would be to just re-enable autovacuum blindly because that would cover most of the cases and add it to the caveats. I can probably whip up a quick PR for that. Then we can see about only restoring it when it was enabled to begin with.

[jfrost]

Oh, actually, I think I might've found the problem.

[jfrost]

So, @shayonj your suspicion is correct, this is how a table's storage params with autovacuum set to defaults looks:

jefftest=# SELECT array_to_string(reloptions, ',') as params FROM pg_class WHERE relname='a';
┌────────┐
│ params │
├────────┤
│ \N     │
└────────┘
(1 row)

[jfrost]

Also, shouldn't this:
https://github.com/shayonj/pg-osc/blob/main/lib/pg_online_schema_change/orchestrate.rb#L256
still be #{shadow_table} at this point in the process?

I think this gets us part of the way there:
#85

[shayonj]

New release is out. Thanks for find and fix folks 🙌🏾
https://github.com/shayonj/pg-osc/releases/tag/v0.8.0

[jfrost]

Whoops, reopened it by targeting error!

[shayonj]

woot!!

[brycethornton]

Thanks @jfrost and @shayonj! I appreciate your effort! I promise I'll contribute more in the future. I had hoped/planned to at least get a PR going for this but I just didn't make the time.

[shayonj]

no worries at all! Thanks for the reporting. It all helps!