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

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