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;
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
