not able to delete data

Ravi_Vijay · June 2013

Hi All, Trying a simple time-based (< timestamp) delete on a particular table in our database. The total number of records deleted is 1887497. In order to ensure that the delete goes through I am performing manual purging. So the sequence is something like :: a) delete b) select make_ahm_now(); c) select purge_table('table_name'); However the purge command returns instantly and on checking further, the records reappear in the table. This is quite puzzling to me. Any help would be appreciated. The delete_vectors table doesn't contain any records corresponding to the delete performed. Thanks Ravi.

[Deleted User] · June 2013

Hi Ravi, Could you clarify what you're trying to achieve? What you're doing is a great deal more than just deleting data. Simply issuing the delete should be sufficient to make the data go away, from a user's perspective. There should be no need to purge it under typical usage. After you delete your data (step (a) above but before step (b)), have you verified that it is in fact deleted? At that point it should no longer be possible to see the data via SELECT statements. (Make sure to test SELECT from a second login session as well as your current one. If you can still see the data from another session, you're probably missing a "COMMIT;" statement.) If you have to do this sort of thing regularly, we generally recommend using DROP PARTITION (or TRUNCATE, if you're deleting a whole table) if possible. This performs much better than DELETE on large blocks of rows. Unfortunately, neither command is currently transactional; they're not affected by COMMIT or ROLLBACK. Adam

Ravi_Vijay · June 2013

much ado about a commit .. thanks .. was using the -c option with vsql .. and issuing a separate commit later. Including commit in the same statement solved the problem.

not able to delete data

Comments

Leave a Comment