Search Postgresql Archives

Re: Defer a functional index calculation?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Mar 20, 2009 at 05:37:33PM -0700, Randall Lucas wrote:
> I added a functional index.
> 
>   create table example (id serial primary key, stuff text, parent_id int);
>   create index example_root_idx on example (get_root_id(id));
> 
> (get_root_id(id) pulls an example row and recurses onto parent_id until it
> hits a root)

I don't think you can do that; are you lying in the function's
definition that it's "immutable"?  As far as I know, and a quick check
of the manual[1] seems to confirm it, all functions used in functional
indexes must be immutable and the execution of an immutable function
can't depend on any data in the database.  If you're creating this
function as "immutable" then this is lying to the database and it's
guaranteed to go wrong at some point (as you seem to be finding out).

What about having some trigger to "cache" the entry's root "parent_id"
in another column?

-- 
  Sam  http://samason.me.uk/

 [1] http://www.postgresql.org/docs/current/static/sql-createindex.html

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux