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

Re: typoed column name, but postgres didn't grump

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Jon Nelson" <jnelson+pgsql(at)jamponi(dot)net>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: typoed column name, but postgres didn't grump
Date: 2010-10-29 18:38:54
Message-ID: 4CCACE6E0200002500036F95@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-performance
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
 
> BTW this seems pretty far off-topic for pgsql-performance.
 
It is once you understand what's happening.  It was probably the 11+
minutes for the mistyped query run, versus the 28 ms without the
typo, that led them to this list.
 
I remembered this as an issued that has come up before, but couldn't
come up with good search criteria for finding the old thread before
you posted.  If you happen to have a reference or search criteria
for a previous thread, could you post it?  Otherwise, a brief
explanation of why this is considered a feature worth keeping would
be good.  I know it has been explained before, but it just looks
wrong, on the face of it.
 
Playing around with it a little, it seems like a rather annoying
foot-gun which could confuse people and burn a lot of development
time:
 
test=# create domain make text;
CREATE DOMAIN
test=# create domain model text;
CREATE DOMAIN
test=# create table vehicle (id int primary key, make make);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"vehicle_pkey" for table "vehicle"
CREATE TABLE
test=# insert into vehicle values (1,
'Toyota'),(2,'Ford'),(3,'Rambler');
INSERT 0 3
test=# select v.make, v.model from vehicle v;
  make   |    model
---------+-------------
 Toyota  | (1,Toyota)
 Ford    | (2,Ford)
 Rambler | (3,Rambler)
(3 rows)
 
If someone incorrectly thinks they've added a column, and the
purported column name happens to match any character-based type or
domain name, they can get a query which behaves in a rather
unexpected way. In this simple query it's pretty easy to spot, but
it could surface in a much more complex query.  If a mistyped query
runs for 11 days instead of 11 minutes, they may have a hard time
spotting the problem.
 
A typo like this could be particularly hazardous in a DELETE or
UPDATE statement.
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2010-10-29 18:49:12
Subject: Re: BBU Cache vs. spindles
Previous:From: Robert HaasDate: 2010-10-29 18:16:02
Subject: Re: temporary tables, indexes, and query plans

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-10-29 19:07:26
Subject: Re: [PERFORM] typoed column name, but postgres didn't grump
Previous:From: Tom LaneDate: 2010-10-29 17:56:23
Subject: Re: What happened to SSL_CIPHERS?

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