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

Tom Lane wrote:
> Richard Huxton <dev(at)archonet(dot)com> writes:
>> 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.
>
> Seems like psqlODBC shouldn't be fetching the default value at all, or
> at least should not be trying to turn around and pass it back to the
> server (if that's what's really happening). It would be far easier and
> safer to omit the target column from the INSERT statement, or write
> DEFAULT if that's inconvenient.

The driver calls currval() not nextval() so as to get the latest
inserted serial value. Though we can call lastval() for the purpose,
it causes a trouble unfortunately
(http://archives.postgresql.org/pgsql-odbc/2007-05/msg00016.php)
.

regards,
Hiroshi Inoue

> Even if you collect the correct
> expression by using pg_get_expr(), what if someone ALTERs the default
> while you're connected?
>
> I'm suspicious that this coding technique is a hangover from back when
> you couldn't write "insert into foo values(default, default)", which is
> to say PG 7.2 or before.
>
> regards, tom lane
>