Possible to Undo DDL?

General
#1

Is it possible to undo a DDL? Like Drop table before the GC kicks in?

#2

Hi @leokwan, you can use AS OF SYSTEM TIME to recover:

root@:26257/> create database foo;
CREATE DATABASE

Time: 5.116162ms

root@:26257/> create table foo.bar (id int primary key);
CREATE TABLE

Time: 5.5399ms

root@:26257/> insert into foo.bar values(1), (2);
INSERT 2

Time: 1.204165ms

root@:26257/> select now();
+---------------------------------+
|              now()              |
+---------------------------------+
| 2017-11-01 20:13:27.62394+00:00 |
+---------------------------------+
(1 row)

Time: 506.511µs

root@:26257/> drop table foo.bar;
DROP TABLE

Time: 20.785082ms

root@:26257/> select * from foo.bar AS OF SYSTEM TIME '2017-11-01 20:13:27.62394+00:00'
           -> ;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
(2 rows)

Time: 2.051278ms

root@:26257/> select * from foo.bar;
pq: relation "foo.bar" does not exist

This will work before the GC kicks in, which by default is at least 24h.

We are also introducing full-history backups (enterprise edition) so that you could create a backup of this data at a past timestamp (this might be necessary if the table is very large), but the above should get you a long way already.

Let me know if you have more questions!