Waiting for 9.1 – Removed autocast footgun

On 8th of November, Tom Lane committed patch, which doesn't provide any new features, but removes one of the more annoying footguns in PostgreSQL:

Prevent invoking I/O conversion casts via functional/attribute notation.
 
PG 8.4 added a built-in feature for casting pretty much any data type to
string types (text, varchar, etc).  We allowed this to work in any of the
historically-allowed syntaxes: CAST(x AS text), x::text, text(x), or
x.text.  However, multiple complaints have shown that it's too easy to
invoke such casts unintentionally in the latter two styles, particularly
field selection.  To cure the problem with the narrowest possible change
of behavior, disallow use of I/O conversion casts from composite types to
string types via functional/attribute syntax.  The new functionality is
still available via cast syntax.
 
In passing, document the equivalence of functional and attribute syntax
in a more visible place.

What and why it is?

Let me show you simple example:

$ \d test
                 Table "public.test"
    Column    |           Type           | Modifiers
--------------+--------------------------+-----------
 id           | integer                  |
 username     | text                     |
 creation_tsz | timestamp with time zone |

Relatively simple table. With single record:

$ SELECT * FROM test;
 id | username |         creation_tsz
----+----------+-------------------------------
  1 | depesz   | 2010-11-08 12:03:29.504768+00
(1 ROW)

Try to guess what will happen when I'll do: select t.name from test t where t.id = 1;. You think you know? Did you guess this:

$ SELECT t.name FROM test t WHERE t.id = 1;
                    name
--------------------------------------------
 (1,depesz,"2010-11-08 12:03:29.504768+00")
(1 ROW)

In case you don't understand what it did/how.

Syntax:

x.whatever

Can relate to either column whatever in row x, or (if x is rowtype, and doesn't contain “whatever" column) is being translated to whatever(x) – to allow easy implementation of computable columns, like this:

$ CREATE FUNCTION created_seconds_ago( test ) RETURNS int4 AS $$
    SELECT EXTRACT( epoch FROM now() - $1.creation_tsz )::int4;
$$ LANGUAGE SQL;
CREATE FUNCTION

And now we can:

$ SELECT t.* FROM test t;
 id | username |         creation_tsz
----+----------+-------------------------------
  1 | depesz   | 2010-11-08 12:03:29.504768+00
(1 ROW)
 
$ SELECT t.*, t.created_seconds_ago FROM test t;
 id | username |         creation_tsz          | created_seconds_ago
----+----------+-------------------------------+---------------------
  1 | depesz   | 2010-11-08 12:03:29.504768+00 |                 344
(1 ROW)

It's relatively cool way to add computer columns.

There is one small problem – if there is no such function defined by user, system function will be called.

Which makes call: select t.name from test t, equal to select name(t) from test t, and it just so happens that name is one of internal datatypes – basically like text, but with length limit.

We can see this length limitation by simply doing the .name call on table which contains longer values:

$ UPDATE test SET username = repeat('depesz', 10);
UPDATE 1

and now when I'll do the t.name trick:

$ SELECT t.name FROM test t;
                              name                               
-----------------------------------------------------------------
 (1,depeszdepeszdepeszdepeszdepeszdepeszdepeszdepeszdepeszdepesz
(1 ROW)

Of course I can use .text to cast it to text and get:

$ SELECT t.text FROM test t;
                                               text                                               
--------------------------------------------------------------------------------------------------
 (1,depeszdepeszdepeszdepeszdepeszdepeszdepeszdepeszdepeszdepesz,"2010-11-08 12:03:29.504768+00")
(1 ROW)

Anyway – the problem with t.name arised at least couple of times, and it became apparent that it's more of a footgun than real functionality, and now, finally, in 9.1 it gets removed:

$ SELECT t.name FROM test t WHERE t.id = 1;
ERROR:  COLUMN t.name does NOT exist
LINE 1: SELECT t.name FROM test t WHERE t.id = 1;
               ^

Of course this is done in such a way, that you still can (if you want) cast to other datatype using normal syntax:

$ SELECT t::name FROM test t WHERE t.id = 1;
                     t                      
--------------------------------------------
 (1,depesz,"2010-11-08 13:15:51.198397+01")
(1 ROW)

And you still can use calculated columns:

$ SELECT t.*, t.created_seconds_ago FROM test t WHERE t.id = 1;
 id | username |         creation_tsz          | created_seconds_ago 
----+----------+-------------------------------+---------------------
  1 | depesz   | 2010-11-08 13:15:51.198397+01 |                  99
(1 ROW)

So – all the functionality is still there, it's just that the problematic “feature" got removed. YEAH!

(side note: personally, I don't like calculated columns done this way. When I need them – I much more prefer views, since approach with such “hidden" function call is quite obscure, and definitely will find many dbas not knowing/understanding it).

=$
|

Waiting for 9.1 – Removed autocast footgun

One thought on “Waiting for 9.1 – Removed autocast footgun”