[HACKERS] Two different methods of sneaking non-immutable data into an index
in [PgSql]
|
Prev: ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Next: Using Small Size SSDs to improve performance?
From: Merlin Moncure on 4 Aug 2010 18:43 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(a)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 | 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |