Re: Including Snapshot Info with Indexes - Mailing list pgsql-hackers

From Gokulakannan Somasundaram
Subject Re: Including Snapshot Info with Indexes
Date
Msg-id 9362e74e0710102158qef7f307y343312b5f67fade1@mail.gmail.com
Whole thread Raw
In response to Re: Including Snapshot Info with Indexes  ("Florian G. Pflug" <fgp@phlo.org>)
Responses Re: Including Snapshot Info with Indexes
List pgsql-hackers


On 10/9/07, Florian G. Pflug <fgp@phlo.org> wrote:
Andrew Dunstan wrote:
> Florian G. Pflug wrote:
>>
>> I think you're overly pessimistic here ;-) This classification can be done
>> quite efficiently as long as your language is "static enough". The trick is
>> not to execute the function, but to scan the code to find all other
>> functions and SQL statements a given function may possibly call. If your
>> function calls no SQL statements, and only other functions already marked
>> IMMUTABLE, then it must be IMMUTABLE itself.
>>
>> It does seem that only pl/pgsql is "static enough" for this to work,
>> though, making this idea rather unappealing.
>>
>
> How would you propose to analyse C functions, for which you might not have
> the C code?
Scanning the binary, together with symbol annotations for immutability of course
;-))

No, seriously. I do *not* advocate that we actually autoclassify functions, for
a lot of reasons. I just wanted to refute the statement that doing so is
generally impossible - it's not. It's trivial for some languages (In haskhell
for example all functions that don't use monads are immutable, and their
signature tell if they do use monads or or), realistic for others (pl/pgsql,
where we do have the sourcecode), and utterly impossible for others
(pl/{ruby,python,perl,...}, pl/c, ...).

Besides - AFAICS *anything* that makes VACUUM depend on IMMUTABLE to be correct
would instantly break tsearch, no? At least as long as we allow changing
stopwords and the like of dictionaries used by an index - which we'd better
allow, unless we want the DBAs to come with pitchforks after us...

regards, Florian Pflug, who shudders when imagining DBAs with pitchforks...

 As explained, if we are going to include the snapshot with indexes, Vacuum will be done on the index independent of the table, so Vacuum will not depend on immutability. We need to goto the index from the table, when we want to update the snapshot info. The problem on hand is that some of the userdefined functions are mutable, whereas the user might mark it immutable.

So my idea is to have a mapping index, with tupleid as the first column and the function's values as subsequent columns. I have a somewhat detailed design in mind. So there will be a over head of extra 3 I/Os for update/delete on indices based on User-defined functions. But this setup will speed-up lot of queries where the tables are partitioned and there will be more inserts and selects and dropping partitions at periodic intervals. Updates become costly by 3 I/Os per Index with snapshot. So if someone has more selects than updates+deletes then this index might come handy (ofcourse not with user-defined functional indices).

I hope in future there can be more ways to find the immutability of the user-defined  functional indices and the requirement for MApping index would go down.

Expecting your comments.

Thanks,
Gokul.

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Artificially increase TransactionID?
Next
From: Oleg Bartunov
Date:
Subject: Re: full text search in 8.3