Hi Tom
> The reason it's printed as just 'tax_id' is that that relation should be
> first in the search_path at this point.
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.
> Are you manually editing the
> dump in some way that screws that up?
>
Nope. I actually took the dump as I was writing the e-mail and verified
that what I was saying was correct. The pg_dump command I used to
create it was:
pg_dump.exe --host=localhost --port=5432 --username=pgsql
--file="C:\SVN\Aspire Platform\_developer\Platform Database.sql"
--schema-only --format=p aspire_platform
> The underlying representation of regclass is an OID, not text, so
> once the default expression is created it's not subject to search path
> issues.
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.
> It's not clear what you did to break it, but your description
> of the problem is based on faulty assumptions.
>
Forgive me if I have made any assumptions, but I cannot see where I can
break it. The client application reports it as including the schema
name in the nextval() clause, then after pg_dump has "dumped" it, within
the SQL file it's gone and been replaced with a "SET search_path..."
clause. When this SQL dump is restored, the schema is missing from the
nextval() clause because of the "SET search_path" that pg_dump set.
I don't know where else it can go wrong, unless there's some other
switch I should be passing to pg_dump?
Regards,
Andy
PS.
Interestingly, pg_dump seems to be inconsistent in when it writes out
schemas - this block of lines are right next to each other (and after
the SET search_path line.) Notice how it's not qualified the first 3
lines, but the 4th it has?
ALTER TABLE ONLY tax ALTER COLUMN id SET STATISTICS 0;
ALTER TABLE ONLY tax ALTER COLUMN band_name SET STATISTICS 0;
ALTER TABLE ONLY tax ALTER COLUMN tax_rate SET STATISTICS 0;
ALTER TABLE product.tax OWNER TO my_user;
