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

Re: 'default nextval()' loses schema-qualification in dump ?

From: Richard Huxton <dev(at)archonet(dot)com>
To: arnaud(dot)listes(at)codata(dot)eu
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-odbc(at)postgresql(dot)org, pgsql-general <pgsql-general(at)postgresql(dot)org>, pgsql-odbc(at)postgresql(dot)org
Subject: Re: 'default nextval()' loses schema-qualification in dump ?
Date: 2010-07-07 07:41:25
Message-ID: 4C342FA5.3050105@archonet.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-odbc
On 07/07/10 07:47, Arnaud Lesauvage wrote:
> Le 6/07/2010 17:17, Tom Lane a écrit :
>> Arnaud Lesauvage<arnaud(dot)listes(at)codata(dot)eu> writes:
>>> As you have understood, I am not very savvy about postgresql's
>>> internals, but from what you say my guess is that the problem is int the
>>> psqlODBC is getting the default value of the sequence ?

> [9.125]conn=095C4198, query='select n.nspname, c.relname, a.attname,
> a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,
> c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then
> t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c
> inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and
> c.relname = E'mytable' and n.nspname = E'myschema') inner join
> pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and
> a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid =
> a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid =
> a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum'

This is psqlODBC getting the sequence name (if you run this query it's 
the adsrc column). If I remember correctly, that's supposed to be the 
human-readable version of an expression and preserved *as entered by the 
user* (or pg_restore in your case).

If you start psql with the "-E" option and do \d myschema.mytable you'll 
be able to see how it gets the sequence-name. About half-way down the 
list of queries it runs you'll see a reference to pg_get_expr(...) - 
that turns an internal representation into a useful usable one.

I don't know why psqlODBC isn't using that. The function has been around 
for a while. Hmm - it's present back in 7.4 although it's not used in \d 
- that does reference adsrc directly.

Just grabbed the source download for the latest version and it still 
looks like it's using adsrc (I just searched for that and pg_get_expr). 
There should probably be a change in info.c around line 2091 to add a 
check for a recent version of PG (8+) and use pg_get_expr. Check on the 
odbc mailing-list - there may be an updated version available for you to 
test.

-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-odbc by date

Next:From: Arnaud LesauvageDate: 2010-07-07 07:51:37
Subject: Re: Re: [GENERAL] 'default nextval()' loses schema-qualification in dump ?
Previous:From: Arnaud LesauvageDate: 2010-07-07 07:39:02
Subject: Re: 'default nextval()' loses schema-qualification in dump ?

pgsql-general by date

Next:From: Arnaud LesauvageDate: 2010-07-07 07:51:37
Subject: Re: Re: [GENERAL] 'default nextval()' loses schema-qualification in dump ?
Previous:From: Arnaud LesauvageDate: 2010-07-07 07:39:02
Subject: Re: 'default nextval()' loses schema-qualification in dump ?

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