> No, it isn't. If the search_path was "product" when the table
> definition was loaded,
No it wasn't. When the table was initially created (from scratch not
from the dump) the search path was the default of "$user", public.
I've just re-created this using the following steps on a blank database:
1. Create a new database using a role with a default search path of
2. Create a schema in that database (myschema)
3. Create a sequence in the test schema (mysequence)
4. Create a table in the myschema schema (mytable) with an integer field
that has a default value of nextval('myschema.mysequence'); - note this
has to be qualified because the myschema schema is not in the
search_path - confirmed with "nextval('mysequence')" and get the
expected "relation mysequence does not exist"
5. Test adding a record to the table - OK
6. Dump the database using pg_dump (see my previous e-mail for the exact
7. Restore the database script against a clean database using the same
user and search path of "$user", public - pg_dump has added the "SET
search_path" at the appropriate points
8. Try and add a record to mytable - "ERROR: relation "mysequence" does
> You are confusing what
> is displayed (which conditionally suppresses the schema name if it's
> not necessary based on your current search path) with what the reference
> actually is (which is always to a specific sequence regardless of name
> or schema).
I get what you mean now, Tom, that once the reference has been created
it doesn't matter what's displayed because the OID reference has been
saved, but from the test case above that doesn't appear to be the case.
> You need to show us what you actually did, not an interpretation of
> what happened that is based on a faulty mental model.
I've outlined the exact steps above using as minimal a test case as
possible, and attached the associated SQL dump.
Description: text/plain (1.2 KB)
In response to
pgsql-admin by date
|Next:||From: Tom Lane||Date: 2009-06-11 22:49:24|
|Subject: Re: pg_dump not appending sequence to default values |
|Previous:||From: Dave Youatt||Date: 2009-06-11 22:05:06|
|Subject: Slony-I: cache lookup failed for type 267501|