Seeing “Invisible” Data

Note: Pivotal Support requested that I mention that this GUC, along with all other hidden GUCs, are not supported. Being able to see deleted data has some benefit in extreme situations but can get you into trouble if used improperly. Do not open a support ticket on this GUC or any other hidden GUC as Pivotal Support will not be able to help. It is hidden for a reason!

I stumbled on this neat little feature today from a hidden GUC (Grand Unified Configuration). If you aren’t familiar with the term GUC, it is basically a configuration value that can be set for a session with “SET” or in the postgresql.conf file using gpconfig.

First, let’s create a table and insert some data.

gpdb=# CREATE TABLE foo (id int NOT NULL, bar text NOT NULL) DISTRIBUTED BY (id);
CREATE TABLE
gpdb=# INSERT INTO foo SELECT i, 'test:' || i FROM generate_series(1, 10) AS i;
INSERT 0 10

Now select the data.

gpdb=# SELECT * FROM foo ORDER BY id;
 id |   bar   
----+---------
  1 | test:1
  2 | test:2
  3 | test:3
  4 | test:4
  5 | test:5
  6 | test:6
  7 | test:7
  8 | test:8
  9 | test:9
 10 | test:10
(10 rows)

Now do something like delete all of the data.

gpdb=# DELETE FROM foo;
DELETE 10

Oh no! I need that data still! How can I get it back?

gpdb=# SET gp_select_invisible = TRUE;
SET
gpdb=# SELECT * FROM foo ORDER BY id;
 id |   bar   
----+---------
  1 | test:1
  2 | test:2
  3 | test:3
  4 | test:4
  5 | test:5
  6 | test:6
  7 | test:7
  8 | test:8
  9 | test:9
 10 | test:10
(10 rows)

As you can see, the old data is still there but marked as deleted. You can still see it by turning this hidden GUC on.

Now here it is turned off to see the data normally.

gpdb=# SET gp_select_invisible = FALSE;
SET
gpdb=# SELECT * FROM foo ORDER BY id;
 id | bar 
----+-----
(0 rows)

You can use this to see non-committed data being inserted into a heap table but not an append-only table. Once you vacuum or truncate a table, the invisible data gets removed so you won’t be able to see it anymore. BTW, this works in HAWQ too!

2 thoughts on “Seeing “Invisible” Data

  1. Dave Saunders

    Jon —
    nice tip. I’m adding it to my secret black book of important gpadmin tools.
    But this looks like another sharp knife in the “drawer full of knives”…
    it needs a Surgeon General’s Warning label on it.

    if you leave the GUC turned on (mistakenly), it would definitely ruin other query results.

    cheers,
    Dave Saunders.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *