2

I need to know if there is any method for intentionally corrupting an index in PostgreSQL.

I have been searching for it for a long time but never seen anyone mentioning such an act.

4
  • 3
    The elephant in the room asks "why?". This matters, because there are an almost endless variety of forms of corruption. What effect do you want to achieve? What problem are you trying to solve by doing this? Are you fuzzing? Testing failure handling in an app? What? Jul 29, 2015 at 6:11
  • I want to check proactively if there is any corrupted index in the database, using the below query SELECT index_name, status from user_indexes where status='INVALID' Jul 29, 2015 at 7:16
  • Ok, I'm sorry to turn the question other way around. Can you guys please suggest something to find the corrupted or unusable indexes in postgresql. I don't think exploring user_indexes or pg_index can truly help. Jul 30, 2015 at 4:00
  • 1
    Please post a new question and link to this one for reference/context. Jul 30, 2015 at 7:25

3 Answers 3

5

I think @Craig's comments are more important, addressing the intentions behind your question. In any case, to answer the question asked:

One simple way (among many others) would be to fake an IMMUTABLE function and base an index on it like outlined in this answer from yesterday:

CREATE OR REPLACE FUNCTION f_fake_immutable_ts()
  RETURNS timestamp LANGUAGE sql COST 1 IMMUTABLE AS
$$SELECT timestamp '2015-07-25 01:00'$$;

Create a partial index based on it:

CREATE INDEX tbl_created_at_idx ON tbl (tbl_id)
WHERE  created_at > f_fake_immutable_ts();

The index relies on the function to always return the same output for the same input. Changing the function (to return a different timestamp), renders the index corrupt.

There is no way to identify this index as corrupt except by testing each entry.

5

Erwin describes a method of creating an index that violates PostgreSQL's assumptions about the index, producing results that are likely incorrect and do not match the underlying table.

That's one kind of corruption.

Another kind of corruption is where an index has blocks that are simply invalid - zeroed out, replaced with random values, etc. Most likely reading such an index will result in an ERROR if/when that block is accessed. You can create such an index by dding from /dev/zero or /dev/urandom into some offset into the index.

Another kind of corruption is a simple bit-flip. That's where you pick a random (or targeted) byte from the index relation and change it from a 0 to a 1 or vice versa. This can happen in reality if memory errors (including gamma radiation, heat problems, etc) cause an index page to be corrupted in-memory before it's flushed to disk. It's trivial to write a program in C, Python, or whatever to do this.

A further kind of corruption is where the index is structurally valid, but outdated. It doesn't reflect the current table contents because for some reason some updates to the index didn't get written. You can create this situation by setting indisvalid to false, doing some inserts/updates/deletes on the indexed relation, then setting indisvalid = true again. Since PostgreSQL ignores an index with indisvalid = f, assuming it's in the process of being built, it won't update it, so you'll have outdated index contents.

Yet another kind of corruption involves b-tree structure problems, like missing leaves, circular references, etc. The best way to create these kinds of corruptions is to modify PostgreSQL's source code to make it do them on purpose. It'll be difficult to do them using just the index relation.

Another kind of index corruption is truncation. The index looks fine, but the end is missing.

There's the case where the index relation is simply missing. The catalog entries exist, but the backing file doesn't.

The index relation can exist, but be full of random garbage.

The list goes on. Almost by definition, corruption can be anything that isn't the normal state of affairs.

This is one of the reasons why "fuzzing" is so interesting. Because it's very, very hard for programmers to anticipate every possible state and guard against every possible problem, so problems can often be found by making random changes and seeing what breaks.

4

Erwin's answer does a good job of answering the question as originally stated, however you added the comment:

I want to check proactively if there is any corrupted index in the database, using the below query SELECT index_name, status from user_indexes where status='INVALID'

Assuming you are talking about the indisvalid column in pg_index, or some status field derived therefrom, you can create an invalid (or intentionally corrupt, if you prefer) index like so:

CREATE TABLE foo (a int);
INSERT INTO foo (a) VALUES (1),(1);
CREATE UNIQUE INDEX CONCURRENTLY ON foo(a);

Here, Postgres knows that the index is bad and marks it as such in pg_index. There are countless other ways to create a corrupt index that Postgres won't immediately notice: Erwin's answer, mismatched glibc or other collation behavior between a primary and a standby, writing arbitrary bytes to the files behind those indexes, performing bogus updates to Postgres' catalog tables, and on and on.

3
  • 1
    I disagree with your characterisation of indisvalid = f indexes as corrupt. They are not. They are marked unusable and may be in the process of being built, etc, but this is a normal state. PostgreSQL never "detects" a corrupt index and unsets indisvalid, that's not what the field is for. Sure, it's useful to play with this, but it has almost nothing to do with the original issue or the follow up question. Jul 30, 2015 at 7:25
  • 1
    @CraigRinger I agree with everything you say other than my answer not being relevant to OP. Although OP initially claimed he wanted to create a "corrupt" index, from his comment, what he really wanted was to create an index which would show up in his query. I'm reading between the lines a little bit about what exactly he means with SELECT index_name, status from user_indexes where status='INVALID', but whatever that "user_indexes" view/table/whatever he's talking about is, it's almost definitely not checking for "corruption" as you define it. Jul 30, 2015 at 19:24
  • 1
    user_indexes is an Oracle system view like pg_index. An index will be made invalid for instance by a bulk load that explicitly skips indexes. The status column is quite analogous to postgres indisvalid. Jul 30, 2015 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.