Rollback data of a table deleted in Greenplum

Question

I am using Greenplum DB 4.3 version, A table which have meta data information for my application and it is deleted wrongly,

Is there any way to roll back a table which was deleted by mistake ??

Answer 1

If you used drop table <tablename>, then nothing can help - the table files were deleted from the filesystem when you've committed them

If you used delete from <tablename> then you still can access this data. You need to set the GUC set gp_select_invisible=on - this would allow you to see all the deleted data. You need to use the field xmax to find the data that was deleted by your transaction, my article briefly covers how it works under the hood: http://0x0fff.com/mvcc-in-transactional-systems/

Answer 2

I was also trapped once in the above situation

you can check the deleted data also, if you update below param

set gp_select_invisible = on 

if vacuum is not run than

Following is the whole procedure

--create a table 

create table smpl 
( userid integer, 
  username character varying(35), 
  password character varying(15), 
  email character varying(60) 
) 

--insert some sample date 

insert into smpl values(101,'user1','user1','user1@ril.com'); 
insert into smpl values(103,'user3','user3','user3@ril.com'); 
insert into smpl values(102,'user2','user2','user2@ril.com'); 

--Check if data is properly inserted 

select *,xmin,xmax,cmin,cmax from smpl 

(xmax = 0 for new inserted data)

--delete all data 

delete from smpl 

(xmax <> 0 for deleted data)

--if you update below parameter as ON;you can check the deleted data also 

set gp_select_invisible = on 

select *,xmin,xmax,cmin,cmax from smpl 

--vacuum smpl 

(if your rum vacuum deleted data will be lost)

--insert some more data with same id 

insert into smpl values(101,'user1','user1','user11@ril.com'); 

insert into smpl values(103,'user3','user3','user13@ril.com'); 

(check the data agaain with xmin and xmax value)

--if you set it off;you cant check the deleted data 

set gp_select_invisible = off 

select *,xmin,xmax,cmin,cmax from smpl 

Thanks