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 22:26:17
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
> 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 
"$user", public.
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 
not exist"

> 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.


Attachment: testdb.sql
Description: text/plain (1.2 KB)

In response to


pgsql-admin by date

Next:From: Tom LaneDate: 2009-06-11 22:49:24
Subject: Re: pg_dump not appending sequence to default values
Previous:From: Dave YouattDate: 2009-06-11 22:05:06
Subject: Slony-I: cache lookup failed for type 267501

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