Re: pg_dump not appending sequence to default values

I've come across an issue with pg_dump from 8.3.7 (running on Windows.)
I'm using pg_dump to dump the schema only of the database for a system
I'm currently displaying.

The other day I had to re-create the database using the latest dump, and
for a lot of the tables I now get the error "relation xxx does not
exist" when adding a record, and I've found out it's because the
nextval() default value isn't correctly re-created with the schema name.

Here's an example - the table "tax" in the "product" schema has a
default value for the primary key field of
"nextval('product.tax_id'::regclass)"

When I pg_dump the schema, the resulting SQL is:

...
CREATE SCHEMA product;
...
SET search_path = product, pg_catalog;
...
CREATE SEQUENCE tax_id
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
...
CREATE TABLE tax (
id smallint DEFAULT nextval('tax_id'::regclass) NOT NULL
);

Notice how "product.tax_id" has now become just "tax_id" so when I now
insert a record into that table, it complains "relation 'tax_id' does
not exist" and I have to manually edit it.

Is this a known issue? I know a work-around is to include every schema
name in the user's search path, but it's still annoying it's not
re-created as it was originally.

Thanks,
Andy

Andy Shellam <andy-lists(at)networkmail(dot)eu> writes:
> When I pg_dump the schema, the resulting SQL is:

> ...
> CREATE SCHEMA product;
> ...
> SET search_path = product, pg_catalog;
> ...
> CREATE SEQUENCE tax_id
> INCREMENT BY 1
> NO MAXVALUE
> NO MINVALUE
> CACHE 1;
> ...
> CREATE TABLE tax (
> id smallint DEFAULT nextval('tax_id'::regclass) NOT NULL
> );

> Notice how "product.tax_id" has now become just "tax_id" so when I now
> insert a record into that table, it complains "relation 'tax_id' does
> not exist" and I have to manually edit it.

The reason it's printed as just 'tax_id' is that that relation should be
first in the search_path at this point. Are you manually editing the
dump in some way that screws that up?

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. It's not clear what you did to break it, but your description
of the problem is based on faulty assumptions.

regards, tom lane

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;

Andy Shellam <andy-lists(at)networkmail(dot)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

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

Regards,
Andy

Andy Shellam <andy-lists(at)networkmail(dot)eu> writes:
> 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
> command)
> 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"

I did exactly the above, and it works as I expect.

$ psql
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

regression=# create user myuser;
CREATE ROLE
regression=# create database mydb owner myuser;
CREATE DATABASE
regression=# \c mydb myuser
You are now connected to database "mydb" as user "myuser".
mydb=> create schema myschema;
CREATE SCHEMA
mydb=> create sequence myschema.mysequence;
CREATE SEQUENCE
mydb=> create table myschema.mytable (f1 int default nextval('myschema.mysequence'));
CREATE TABLE
mydb=> \d myschema.mytable
Table "myschema.mytable"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
f1 | integer | default nextval('myschema.mysequence'::regclass)

mydb=> insert into myschema.mytable default values;
INSERT 0 1
mydb=> \q
$ pg_dump -U postgres -s mydb >mydb.dump
$ cat mydb.dump
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: myschema; Type: SCHEMA; Schema: -; Owner: myuser
--

CREATE SCHEMA myschema;

ALTER SCHEMA myschema OWNER TO myuser;

SET search_path = myschema, pg_catalog;

--
-- Name: mysequence; Type: SEQUENCE; Schema: myschema; Owner: myuser
--

CREATE SEQUENCE mysequence
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE myschema.mysequence OWNER TO myuser;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: mytable; Type: TABLE; Schema: myschema; Owner: myuser; Tablespace:
--

CREATE TABLE mytable (
f1 integer DEFAULT nextval('mysequence'::regclass)
);

ALTER TABLE myschema.mytable OWNER TO myuser;

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--

$ psql
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

regression=# create database mydb2 owner myuser;
CREATE DATABASE
regression=# \c mydb2 myuser
You are now connected to database "mydb2" as user "myuser".
mydb2=> \i mydb.dump
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
CREATE SEQUENCE
ALTER TABLE
SET
SET
CREATE TABLE
ALTER TABLE
psql:mydb.dump:54: WARNING: no privileges could be revoked for "public"
REVOKE
psql:mydb.dump:55: WARNING: no privileges could be revoked for "public"
REVOKE
psql:mydb.dump:56: WARNING: no privileges were granted for "public"
GRANT
psql:mydb.dump:57: WARNING: no privileges were granted for "public"
GRANT
mydb2=> \c -
You are now connected to database "mydb2".
mydb2=> \d myschema.mytable
Table "myschema.mytable"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
f1 | integer | default nextval('myschema.mysequence'::regclass)

mydb2=> insert into myschema.mytable default values;
INSERT 0 1
mydb2=> select * from myschema.mytable;
f1
----
1
(1 row)

mydb2=> \q

What are you doing differently?

regards, tom lane