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

Le 7/07/2010 9:41, Richard Huxton a écrit :
> 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).

Hi Richard, thanks for your help.
Yes, that's how I interpreted it too.

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

I think that the handling of "auto numbering" fields in PsqlODBC is
quite new, so maybe this is still a not very stable feature.

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

I tested with the latest release of PsqlODBC (8.04.0200), and it fails
at the same point.

Regards
Arnaud Lesauvage