Two different methods of sneaking non-immutable data into an index

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Two different methods of sneaking non-immutable data into an index
Date: 2010-08-04 22:43:56
Message-ID: AANLkTimq1YEudi3j9gfm1QkHR3hZzbB8NbJAvUCReuAx@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

While chatting with Haas off-list regarding how the new array/string
functions should work (see the thread in its glory here:
http://www.mail-archive.com/pgsql-hackers(at)postgresql(dot)org/msg148865.html)
the debate morphed into the relative pros and cons about the proposed
concat() being marked stable vs immutable. I did some checking into
how things work now, and found some surprising cases.

*) No dependency check on user definable casts:
postgres=# create function hackdate(date) returns text as $$ select
'casted!'::text; $$ language sql;
CREATE FUNCTION
postgres=# create cast (date as text) with function hackdate(date);
CREATE CAST
postgres=# select now()::date || 'abc'::text; -- you're right!
?column?
------------
casted!abc
postgres=# create table vtest(a date, b text);
CREATE TABLE
postgres=# create unique index vtest_idx on vtest((a || b));
CREATE INDEX
postgres=# insert into vtest values (now(), 'test');
INSERT 0 1
postgres=# insert into vtest values (now(), 'test'); -- should fail
ERROR: duplicate key value violates unique constraint "vtest_idx"
DETAIL: Key ((a || b))=(casted!test) already exists.
postgres=# drop cast (date as text);
DROP CAST
postgres=# insert into vtest values (now(), 'test');
INSERT 0 1
postgres=# select * from vtest;
a | b
------------+------
2010-08-01 | test
2010-08-01 | test
(2 rows)

*) textanycat is defined immutable and shouldn't be:
create table vtest(a date, b text);
create unique index vtest_idx on vtest((a::text || b)); -- fails on
immutable check
create unique index vtest_idx on vtest((a || b)); -- works??
insert into vtest values (now(), 'test');
set datestyle to 'SQL, DMY';
insert into vtest values (now(), 'test');
postgres=# select * from vtest;date
a | b
------------+------
31/07/2010 | test
31/07/2010 | test
(2 rows)
postgres=# select * from vtest where a|| b = now()::date || 'test';
a | b
------------+------
31/07/2010 | test
(1 row)

*) also, isn't it possible to change text cast influencing GUCs 'n'
times per statement considering any query can call a function and any
function can say, change datestyle? Shouldn't the related functions
be marked 'volatile', not stable?

merlin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Merlin Moncure 2010-08-04 22:49:16 Re: Using Small Size SSDs to improve performance?
Previous Message Tom Lane 2010-08-04 22:41:39 Re: [HACKERS] Drop one-argument string_agg? (was Re: string_agg delimiter having no effect with order by)