View deleted data in Netezza with view_deleted_records.

So, Netezza does not delete data until the table has been groomed, they are marked as deleted on the system. So, if they are deleted how are you supposed to see them ?

We know the row exists, and it is still there where we have not groomed the table, so here’s an example of how to view deleted data.

LABDB(ADMIN)=> d bod
Table “BOD”
Attribute |  Type  | Modifier | Default Value
———–+——–+———-+—————
ID        | BIGINT |          |
Distributed on hash: “ID”

LABDB(ADMIN)=> insert into bod values (1);
INSERT 0 1
LABDB(ADMIN)=> insert into bod values (2);
INSERT 0 1
LABDB(ADMIN)=> delete from bod where id = 1;
DELETE 1
LABDB(ADMIN)=> insert into bod values (3);
INSERT 0 1
LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod;
CREATEXID | DELETEXID |   ROWID   | ID
———–+———–+———–+—-
353286 |         0 | 515101001 |  2
353290 |         0 | 515101002 |  3
(2 rows)

LABDB(ADMIN)=> set show_deleted_records=true;
SET VARIABLE
LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod;
CREATEXID | DELETEXID |   ROWID   | ID
———–+———–+———–+—-
353286 |         0 | 515101001 |  2
353290 |         0 | 515101002 |  3
353284 |    353288 | 515101000 |  1
(3 rows)

But updates do the same, don’t they ? An updated row is logically updated but physically deleted and recreated.

LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod;
CREATEXID | DELETEXID | ROWID | ID | JUNK
———–+———–+——-+—-+——
(0 rows)

LABDB(ADMIN)=> insert into bod values (1, null);
INSERT 0 1
LABDB(ADMIN)=> insert into bod values (2, null);
INSERT 0 1
LABDB(ADMIN)=> update bod set junk = ‘TWO’ where id = 2;
UPDATE 1
LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod order by createxid;
CREATEXID | DELETEXID |   ROWID   | ID | JUNK
———–+———–+———–+—-+——
353362 |         0 | 515102002 |  1 |
353364 |    353366 | 515102003 |  2 |
353366 |         0 | 515102003 |  2 | TWO
(3 rows)

This shows that the row has NOT been updated, but actually inserted and the old record marked as deleted.

There is a gotcha here, the view_deleted_records does not work in exactly the way that you would expect.

LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod order by createxid, rowid;
CREATEXID | DELETEXID |   ROWID   | ID | JUNK
———–+———–+———–+—-+——
353284 |    353288 | 515101000 |  1 |
353286 |         0 | 515101001 |  2 |
353290 |         0 | 515101002 |  3 |
(3 rows)

This is fine. Let’s now update the table.

LABDB(ADMIN)=> update bod set junk = ‘ONE’ where id = 1;
ERROR:  0×56408 : Concurrent update or delete of same row

Well, that would be silly that you could actually update a deleted row in a table.

LABDB(ADMIN)=> update bod set junk = ‘TWO’ where id = 2;
UPDATE 1
LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod order by createxid, rowid;
CREATEXID | DELETEXID |   ROWID   | ID | JUNK
———–+———–+———–+—-+——
353284 |    353288 | 515101000 |  1 |
353286 |    353312 | 515101001 |  2 |
353290 |         0 | 515101002 |  3 |
353310 |         1 | 515101000 |  1 | ONE
353312 |         0 | 515101001 |  2 | TWO
(5 rows)

So you can see two things which are interesting. Firstly the deleted row was actually updated, despite the error. And we can see that id 2 was marked deleted at XID 353312, and a new one at the same XID. Let’s just try that again.

LABDB(ADMIN)=> set show_deleted_records=false;
SET VARIABLE
LABDB(ADMIN)=> insert into bod values (4, null);
INSERT 0 1
LABDB(ADMIN)=> update bod set junk = ‘FOUR’ where id = 4;
UPDATE 1
LABDB(ADMIN)=> select * from bod where id = 4;
ID | JUNK
—-+——
4 | FOUR
(1 row)

LABDB(ADMIN)=> delete from bod where id = 4;
DELETE 1
LABDB(ADMIN)=> select * from bod where id = 4;
ID | JUNK
—-+——
(0 rows)

LABDB(ADMIN)=> set show_deleted_records=true;
SET VARIABLE
LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod where id = 4;
CREATEXID | DELETEXID |   ROWID   | ID | JUNK
———–+———–+———–+—-+——
353330 |    353332 | 515102000 |  4 |
353332 |    353336 | 515102000 |  4 | FOUR
(2 rows)

That’s what we expect to happen. Let’s repeat without the setting.

LABDB(ADMIN)=> set show_deleted_records=false;
SET VARIABLE
LABDB(ADMIN)=> insert into bod values (5, null);
INSERT 0 1
LABDB(ADMIN)=> delete from bod where id = 5;
DELETE 1
LABDB(ADMIN)=> update bod set junk = ‘FIVE’ where id = 5;
UPDATE 0
LABDB(ADMIN)=> set show_deleted_records=true;
SET VARIABLE
LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod where id = 5;
CREATEXID | DELETEXID |   ROWID   | ID | JUNK
———–+———–+———–+—-+——
353342 |    353344 | 515102001 |  5 |
(1 row)

Which shows that if you set show_deleted_records to true, you can not only view the deleted data, but you can update it too. This should never be set on the netezza system without being requested by support.