Andy Shellam <andy-lists@networkmail.eu> writes:
> Yes, that's true - it's in the search path because (so I believe)
> pg_dump is adding a "SET search_path..." line before it carries out the
> commands in the schema, which works when the dump is restored, but when
> running as a normal user, the search path is the default ($user, public)
> and tax_id doesn't exist in the public schema (it exists as
> product.tax_id.) As I said a work-around is to set the user's
> search_path to include all schemas.
No, it isn't. If the search_path was "product" when the table
definition was loaded, then the regclass constant will remember that
and the reference will be to product.tax_id. 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).
> The default expression to begin with was "nextval('product.tax_id')" -
> either PostgreSQL or the GUI application converted it to
> "nextval('product.tax_id'::regclass)". When pg_dump dumps it out, it
> adds the "SET search_path = product, public" line and strips off the schema.
And when you load it back in, it goes back to being what it was,
ie an OID reference.
You need to show us what you actually did, not an interpretation of
what happened that is based on a faulty mental model.
regards, tom lane
