Cannot restore from dump on cloud; Error about postgresql.auto.conf

[norregaarden]

• EdgeDB Version:"5.2+ddd8aa4"
• EdgeDB CLI Version:EdgeDB CLI 5.1.0+7c5764f
• OS Version:Windows 10, Ubuntu WSL

Steps to Reproduce:

1. run edgedb restore --all -I edgedbcloudorgexample/mydatabase ./mylocaldump
2. obtain the following error message:
   edgedb error: InternalServerError: could not fsync file "postgresql.auto.conf": Invalid argument
   Server traceback:
   edb.errors.InternalServerError: could not fsync file "postgresql.auto.conf": Invalid argument

Schema:

[msullivan]

The issue here is likely that cloud does not support doing CONFIGURE INSTANCE on certain configuration settings that directly affect the underlying postgres instance.

We should produce a proper error message, though.

I think it should be easy to workaround by editing init.edgeql in the dump directory to remove the offending configures

[msullivan]

What configuration values is init.edgeql trying to set?

[msullivan]

Ahhhhh, it looks like session_idle_transaction_timeout is always included in DESCRIBE INSTANCE CONFIG because during bootstrap, we execute CONFIGUREs for any backend settings with default values:

edgedb/edb/server/bootstrap.py

Lines 1761 to 1782 in df39a45

	for setname in config_spec:
	setting = config_spec[setname]
	if (
	setting.backend_setting
	and setting.default is not None
	and (
	# Do not attempt to run CONFIGURE INSTANCE on
	# backends that don't support it.
	# TODO: this should be replaced by instance-wide
	# emulation at backend connection time.
	backend_params.has_configfile_access
	)
	):
	if isinstance(setting.default, statypes.Duration):
	val = f'<std::duration>"{setting.default.to_iso8601()}"'
	else:
	val = repr(setting.default)
	script = f'''
	CONFIGURE INSTANCE SET {setting.name} := {val};
	'''
	schema, sql = compile_bootstrap_script(compiler, schema, script)
	await _execute(ctx.conn, sql)

We don't currently have a way to distinguish that from the case where it has been explicitly set.
As a related issue (with a TODO) in that code, we don't have the correct default for session_idle_transaction_timeout when on a backend (like our cloud) that doesn't support CONFIGURE INSTANCE.

We have code that is supposed to produce a proper error message when doing a backend setting CONFIGURE INSTANCE, but for some reason it isn't firing in the cloud setting: https://github.com/edgedb/edgedb/blob/master/edb/pgsql/compiler/config.py#L53-L58

I think that for backend params with default values, we are going to need to give up on the distinction between using default and set to default, but in the opposite direction than we currently are.
(Another option would be to always store our configs for these things in database metadata potentially alongside as system configs, which we can use to disambiguate these cases. If we want to bother with that, it might be a longer term fix)

Action items here:

• Figure out why the has_configfile_access check isn't working
• For backend settings with a default value, ignore CONFIGURE INSTANCE if it is setting it to the default value and we don't have configfile access. (Or maybe just always.) This will fix restoring from existing dumps
• Make DESCRIBE CONFIG not include any backend_settings that have their default values
• Stop doing CONFIGURE INSTANCE at bootstrap and instead configure those settings to the default when connections are created and there is no explicit setting

(Note that the third thing is still necessary even once the fourth is done, because absent other config system overhauls, configuring the settings then will make it seem like they are session configs)

[norregaarden]

@msullivan It worked commenting out the first line about timeout in the init file. Then I had to wipe the main branch, but then restore worked as expected including data.