From: Tom Lane <tgl@sss.pgh.pa.us>
To: Arseny Sher <a.sher@postgrespro.ru>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: (Re)building index using itself or another index of the same table
Date: Thu, 12 Sep 2019 11:08:28 -0400
Message-ID: <21174.1568300908@sss.pgh.pa.us> (raw)
In-Reply-To: <87d0g5d0tm.fsf@ars-thinkpad>
References: <87d0g5d0tm.fsf@ars-thinkpad>
Arseny Sher <a.sher@postgrespro.ru> writes:
> A problem of similar nature can be reproduced with the following
> stripped-down scenario:
> CREATE TABLE pears(f1 int primary key, f2 int);
> INSERT INTO pears SELECT i, i+1 FROM generate_series(1, 100) i;
> CREATE OR REPLACE FUNCTION pears_f(i int) RETURNS int LANGUAGE SQL IMMUTABLE AS $$
> SELECT f1 FROM pears WHERE pears.f2 = 42
> $$;
> CREATE index ON pears ((pears_f(f1)));
We've seen complaints about this sort of thing before, and rejected
them because, as you say, that function is NOT immutable. When you
lie to the system like that, you should not be surprised if things
break.
> There is already a mechanism which prevents usage of indexes during
> reindex -- ReindexIsProcessingIndex et al. However, to the contrary of
> what index.c:3664 comment say, these protect only indexes on system
> catalogs, not user tables: the only real caller is genam.c.
> Attached patch extends it: the same check is added to
> get_relation_info. Also SetReindexProcessing is cocked in index_create
> to defend from index self usage during creation as in stripped example
> above. There are some other still unprotected callers of index_build;
> concurrent index creation doesn't need it because index is
> 'not indisvalid' during the build, and in RelationTruncateIndexes
> table is empty, so it looks like it can be omitted.
I have exactly no faith that this fixes things enough to make such
cases supportable. And I have no interest in opening that can of
worms anyway. I'd rather put in some code to reject database
accesses in immutable functions.
> One might argue that function selecting from table can hardly be called
> immutable, and immutability is required for index expressions. However,
> if user is sure table contents doesn't change, why not?
If the table contents never change, why are you doing VACUUM FULL on it?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, a.sher@postgrespro.ru
Subject: Re: (Re)building index using itself or another index of the same table
In-Reply-To: <21174.1568300908@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox