Re: Including Snapshot Info with Indexes

On 10/9/07, Florian G. Pflug <fgp(at)phlo(dot)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.