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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: arnaud(dot)listes(at)codata(dot)eu, pgsql-odbc(at)postgresql(dot)org
Subject: Re: [GENERAL] 'default nextval()' loses schema-qualification in dump ?
Date: 2010-07-08 21:13:47
Message-ID: 11224.1278623627@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-odbc
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

In response to

Responses

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2010-07-08 21:29:35
Subject: Re: UPDATE statement value mutation
Previous:From: Arnaud LesauvageDate: 2010-07-08 07:09:18
Subject: Re: 'default nextval()' loses schema-qualification in dump ?

pgsql-general by date

Next:From: Tyler HainsDate: 2010-07-08 21:24:58
Subject: JDBC Postgres problem
Previous:From: Andrew MacleanDate: 2010-07-08 20:42:58
Subject: Re: Problems with Vista and Windows 7

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