Default value for METADATA_SCHEMA is not always 'main'

[kyzyl]

The default schema name for metadata tables is listed as main, but that's not true e.g. for postgres, which will default to public.

If there is differing behavior between catalog types it should be noted.

For more details see duckdb/ducklake#557.