Tom Lane wrote:
> btober@xxxxxxxxxxxxxxxx writes:
>> Thus, when piping the output (from legacy host 192.168.2.2) to
>> populate the newly initialized cluster, by way of running (on the new
>> host 192.168.2.3)
>> pg_dumpall -h 192.168.2.2|psql
>> an error occurs in that first section when the script attempts to
>> set a role-specific search path ... because the schema named in the
>> search path hasn't been created yet.
>
> Could we see a complete example?
>
> Recent versions of the backend are not supposed to throw hard errors in
> this situation, because of exactly that hazard. For instance:
>
> regression=# create role joe;
> CREATE ROLE
> regression=# alter role joe set search_path to foo, bar;
> NOTICE: schema "foo" does not exist
> NOTICE: schema "bar" does not exist
> ALTER ROLE
> regression=#
>
> which AFAICS is exactly what will happen while restoring a pg_dumpall
> dump.
Ah, I understand. I think.
What I saw was not literally and "error", but rather a "notice": The "alter role ... set search_path" statement doesn't actually fail.
The real problem is my preconceived notion. I was thinking in terms of, say, the analogous situation for a database insert to a table with a foreign key, i.e., that since the schema to be set doesn't exist, the the statement should actually fail.
The implication of your illustration above is that the "alter role ... set search_path" statement completes successfully even though it is setting an invalid search path (invalid because the schema referenced in the search path to be set does not exist at the time the statement is executed).
So this behavior, which seems a little counter intuitive, actually makes for a robust end result.
> btober@xxxxxxxxxxxxxxxx writes:
>> Thus, when piping the output (from legacy host 192.168.2.2) to
>> populate the newly initialized cluster, by way of running (on the new
>> host 192.168.2.3)
>> pg_dumpall -h 192.168.2.2|psql
>> an error occurs in that first section when the script attempts to
>> set a role-specific search path ... because the schema named in the
>> search path hasn't been created yet.
>
> Could we see a complete example?
>
> Recent versions of the backend are not supposed to throw hard errors in
> this situation, because of exactly that hazard. For instance:
>
> regression=# create role joe;
> CREATE ROLE
> regression=# alter role joe set search_path to foo, bar;
> NOTICE: schema "foo" does not exist
> NOTICE: schema "bar" does not exist
> ALTER ROLE
> regression=#
>
> which AFAICS is exactly what will happen while restoring a pg_dumpall
> dump.
Ah, I understand. I think.
What I saw was not literally and "error", but rather a "notice": The "alter role ... set search_path" statement doesn't actually fail.
The real problem is my preconceived notion. I was thinking in terms of, say, the analogous situation for a database insert to a table with a foreign key, i.e., that since the schema to be set doesn't exist, the the statement should actually fail.
The implication of your illustration above is that the "alter role ... set search_path" statement completes successfully even though it is setting an invalid search path (invalid because the schema referenced in the search path to be set does not exist at the time the statement is executed).
So this behavior, which seems a little counter intuitive, actually makes for a robust end result.
