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

Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?

From: Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
To: Eric Ridge <eebbrr(at)gmail(dot)com>
Cc: Darren Duncan <darren(at)darrenduncan(dot)net>, David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
Subject: Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
Date: 2011-10-30 23:46:01
Message-ID: 4EADE1B9.6050905@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-hackers
On 10/30/2011 03:50 PM, Eric Ridge wrote:
> On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielke<mark(at)mark(dot)mielke(dot)cc>  wrote:
>> 2) Not deterministic (i.e. a database change might cause my code to break),
> Okay, I'm inventing a use-case here, but say you have a "users" table
> with various bits of metadata about the user, including password.
> Maybe, regardless of database changes, you never want the password
> column returned:  SELECT * EXCLUDING (password) FROM tbl_users;
>
> Changes of omission can break your code just as easily.

I think I wasn't as clear as I intended. In many ways, I think use of 
"*" in the first place is wrong for code (despite that I do it as well). 
Therefore, "* EXCLUDING (...)" would also be wrong. It comes to "does 
the code know what it wants?"

In the above case - maybe you don't want password - what about social 
insurance number, credit card number, or any other private bit? The only 
way to truly know you aren't accidentally pulling in fields you don't 
need or want to unnecessarily expose on the wire - is to specifically 
list the fields you DO want, which is precisely to not use "*" at all.

A particular area that I don't like "*" is that my code may make an 
assumption about the exact field names, or the field order that comes 
out. If this is explicitly specified, then it will survive "ALTER 
TABLE", or a restore of the table with columns in a different order, or 
a replacement of the table with a view. However, if I use "*", then my 
code is likely to fail in any of these cases, and possibly fail in some 
unexpected way. For example, in a language such as Perl with DBI 
returning a hash, I may accidentally assume that the field is always 
undef. It might even pass some designer testing if the value is usually 
NULL = undef, and I fail to simulate the case where it is not.

"select *" is not deterministic from a programming perspective.

-- 
Mark Mielke<mark(at)mielke(dot)cc>


In response to

Responses

pgsql-hackers by date

Next:From: Christopher BrowneDate: 2011-10-31 00:16:47
Subject: Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
Previous:From: Robert HaasDate: 2011-10-30 22:19:45
Subject: myProcLocks initialization

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