Tom Lane wrote:
The hole in your argument is that this is not so. The purpose of a
backup is to get the *user's* objects into the same state they were
in. If we applied that reasoning to *system* objects then presumably
loading a dump from an 8.2 database into 8.3 would magically destroy
all the new features in 8.3 (eg all the text search objects).
It might be that the public schema should be considered a user object
not a system object, but you need to make a case specifically about
that, not argue that the behavior is broken in general.
Sorry if my explanation was not complete enough. My point is that the
objects the user manually dropped should remain dropped in the restored
database, by means of inserting DROPs for the deleted objects. The dump
would not include DROPs for objects that are not in the original (8.2
in your example) template0 database, thus obviously they wouldn't be
removed in the restored (8.3 in your example) one. Yet restoring an 8.2
backup into an 8.2 server would leave things as intended, except if
template0 is altered which Should Not Happen(tm).
Anyway my only concern so far is with the public schema, see the title.
I suggested that other objects might need to be dropped just for your
consideration, because it looked like a natural generalization. If you
think that only DROP statements for user objects are to be considered,
that will fulfill my needs, even if I see no difference between the
user dropping a user object or a system object and expecting it to
remain dropped when restoring a backup.
It'll be OK with me if it's declared to be a documentation-only problem
for not mentioning that the objects the user drops can revive after a
restore.
What I would personally suggest is that rather than insisting on
public not being there, you just do
revoke create on schema public from public;
which is a property that pg_dump *will* preserve.
Indeed I wrote:
As it is now, I can think only of three possible workarounds:
-(...)
-To leave it created instead of dropping it, but issue a REVOKE ALL
ON SCHEMA public FROM PUBLIC, so that it's adjusted to not have all
privileges on restore.
-(...)
Only the second workaround mentioned is acceptable for us, but it
still feels like a dirty hack. That's why I'd like to see this
fixed.
The manual even encourages in a certain sense dropping the "public"
schema (section 5.7.7 of 8.2):
"Also, there is no concept of a public schema in the SQL standard. For
maximum conformance to the standard, you should not use (perhaps even
remove) the public schema."
-- Pedro Gimeno
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
