Percona-Lab / pg_tde

MIT License
107 stars 19 forks source link

pg_relation_filepath returning NULL after converting a table back to unencrypted. #201

Closed jobinau closed 2 months ago

jobinau commented 3 months ago

Here is the test case

  1. Create an encrypted table
    CREATE TABLE test_tde (id SERIAL, t VARCHAR(32), PRIMARY KEY(id)) USING pg_tde;
  2. Insert couple of tuples
    INSERT INTO test_tde  (t) VALUES ('foobar'),('barfoo');
  3. Check the details of the table
    postgres=# SELECT relname,relfilenode,pg_relation_filepath(relfilenode) FROM pg_class WHERE relname in ('test_enc','test_tde','test_basic');
    relname   | relfilenode | pg_relation_filepath 
    ------------+-------------+----------------------
    test_tde   |       16502 | base/5/16502
    test_enc   |       16488 | base/5/16488
    test_basic |       16495 | base/5/16495
  4. Convert the table back to unencrypted
    ALTER TABLE test_tde SET ACCESS METHOD heap;
  5. Check the table status again
    postgres=# SELECT relname,relfilenode,pg_relation_filepath(relfilenode) FROM pg_class WHERE relname in ('test_enc','test_tde','test_basic');
    relname   | relfilenode | pg_relation_filepath 
    ------------+-------------+----------------------
    test_tde   |       16508 | 
    test_enc   |       16488 | base/5/16488
    test_basic |       16495 | base/5/16495
    (3 rows)

    Observe the NULL string returned by the pg_relation_filepath

ImTheKai commented 2 months ago

Will be tracked here https://perconadev.atlassian.net/browse/PG-825