"Oleg Bartunov" <oleg@sai.msu.su> writes:
> On Thu, 14 Jun 2007, Gregory Stark wrote:
>
>> Am I correct to think of this like changing collations leaving your btree
>> index "corrupt"? In that case it probably won't cause any backend crash either
>> but you will get incorrect results. For example, returning different results
>> depending on whether the index or a full table scan is used.
>
> You're correct. But we can't defend users from all possible errors.
Sure, but it seems like a the line, at least in existing cases, is that if you
fiddle with catalogs directly then you should know what consequences you need
to be careful of.
But when if you make changes through a supported, documented interface then
the system will protect you from breaking things.
Hm, I went to construct an example and accidentally found a precedent for not
necessarily protecting users from themselves in every case:
postgres=# create table x (i integer);
CREATE TABLE
postgres=# create function f(integer) returns integer as 'select $1' immutable strict language sql;
CREATE FUNCTION
postgres=# select f(1);f
---1
(1 row)
postgres=# create index xi on x (f(i));
CREATE INDEX
postgres=# insert into x values (1);
INSERT 0 1
postgres=# insert into x values (2);
INSERT 0 1
postgres=# create or replace function f(integer) returns integer as 'select -$1' immutable strict language sql;
CREATE FUNCTION
Uhm. Oops! And yes, the resulting index is, of course, corrupt:
postgres=# insert into x (select random() from generate_series(1,2000));
INSERT 0 2000
postgres=# select count(*) from x where f(i) = -1;count
------- 0
(1 row)
postgres=# set enable_bitmapscan = off;
SET
postgres=# set enable_indexscan = off;
SET
postgres=# select count(*) from x where f(i) = -1;count
------- 1003
(1 row)
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com
