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

Поиск
Список
Период
Сортировка
От Merlin Moncure
Тема Two different methods of sneaking non-immutable data into an index
Дата
Msg-id AANLkTimq1YEudi3j9gfm1QkHR3hZzbB8NbJAvUCReuAx@mail.gmail.com
обсуждение исходный текст
Ответы Re: Two different methods of sneaking non-immutable data into an index  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Two different methods of sneaking non-immutable data into an index  (Robert Haas <robertmhaas@gmail.com>)
Список 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@postgresql.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 | test2010-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 | test31/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


В списке pgsql-hackers по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: Using Small Size SSDs to improve performance?