Skip site navigation (1) Skip section navigation (2)

Re: pg_dump not appending sequence to default values

From: Andy Shellam <andy-lists(at)networkmail(dot)eu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: pg_dump not appending sequence to default values
Date: 2009-06-11 21:08:21
Message-ID: 4A317245.9000506@networkmail.eu (view raw or flat)
Thread:
Lists: pgsql-admin
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;

In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2009-06-11 21:28:15
Subject: Re: pg_dump not appending sequence to default values
Previous:From: Hans RomanDate: 2009-06-11 20:44:01
Subject: Replication with SQL Server 2k

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group