By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,731 Members | 1,104 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,731 IT Pros & Developers. It's quick & easy.

doubt regarding rollback

priyan
P: 54
Hi all,

I want to check the difference between the truncate and delete command. I know the difference between them ie., truncate is DDL and delete is DML statement. Delete can be rollbacked know but i tried it but it does not work out.

I tried this command

Expand|Select|Wrap|Line Numbers
  1. update employee set name='arun' where id=1;
  2. ROLLBACK;
  3.  
but the result when i execute rollback command is

Expand|Select|Wrap|Line Numbers
  1. WARNING:  there is no transaction in progress
  2. Query returned successfully with no result in 16 ms.
  3.  
Sep 17 '07 #1
Share this Question
Share on Google+
12 Replies


Expert 100+
P: 700
First u should begin a transaction

1. BEGIN
2. UPDATE employee SET name='arun' WHERE id=1;
3. ROLLBACK;
Sep 17 '07 #2

amitpatel66
Expert 100+
P: 2,367
Hi all,

I want to check the difference between the truncate and delete command. I know the difference between them ie., truncate is DDL and delete is DML statement. Delete can be rollbacked know but i tried it but it does not work out.

I tried this command

Expand|Select|Wrap|Line Numbers
  1. update employee set name='arun' where id=1;
  2. ROLLBACK;
  3.  
but the result when i execute rollback command is

Expand|Select|Wrap|Line Numbers
  1. WARNING:  there is no transaction in progress
  2. Query returned successfully with no result in 16 ms.
  3.  
I think Your UPDATE statement is not doing any update that is the reason ROLLBACK is showing you WARNING.Check whether your update statement updates any record in the table and then try doing ROLLBACK
Sep 18 '07 #3

priyan
P: 54
I think Your UPDATE statement is not doing any update that is the reason ROLLBACK is showing you WARNING.Check whether your update statement updates any record in the table and then try doing ROLLBACK

Thanks it is working but I am having a problem that is when I truncate a table and then executing rollback command it should not be rollbacked but it is getting rollbacked and the datas in the table are there after i execute rollback command......

Expand|Select|Wrap|Line Numbers
  1. select * from employee1
  2.  
the result is
Expand|Select|Wrap|Line Numbers
  1. id   name   salary   start_date           city     region
  2. 1   Jason   40420 1994-02-01 00:00:00   New York      W
  3.  
Expand|Select|Wrap|Line Numbers
  1. begin
  2. truncate table employee1;
  3. rollback;
  4.  
when i execute these queries table is truncated and when I execute rollback and again select * from employee1 the column is retrieved again
I can't understand the problem in it please help me to solve this problem.......

Thanks in advance
priyan....
Sep 18 '07 #4

amitpatel66
Expert 100+
P: 2,367
Thanks it is working but I am having a problem that is when I truncate a table and then executing rollback command it should not be rollbacked but it is getting rollbacked and the datas in the table are there after i execute rollback command......

Expand|Select|Wrap|Line Numbers
  1. select * from employee1
  2.  
the result is
Expand|Select|Wrap|Line Numbers
  1. id   name   salary   start_date           city     region
  2. 1   Jason   40420 1994-02-01 00:00:00   New York      W
  3.  
Expand|Select|Wrap|Line Numbers
  1. begin
  2. truncate table employee1;
  3. rollback;
  4.  
when i execute these queries table is truncated and when I execute rollback and again select * from employee1 the column is retrieved again
I can't understand the problem in it please help me to solve this problem.......

Thanks in advance
priyan....
Check IF your TRUNCATE statment is getting executed. TRUNCATE a table first, then try querying from the same without doing a ROLLBACK. If you get the records then your TRUNCATE statement is not working. YES TRUNCATE is a DDL statement and cannot be ROLLEDBACK.
Sep 18 '07 #5

priyan
P: 54
Check IF your TRUNCATE statment is getting executed. TRUNCATE a table first, then try querying from the same without doing a ROLLBACK. If you get the records then your TRUNCATE statement is not working. YES TRUNCATE is a DDL statement and cannot be ROLLEDBACK.

yes I checked after executing TRUNCATE statement the table has no records but after executing rollback there is records in the table.......
Sep 18 '07 #6

amitpatel66
Expert 100+
P: 2,367
yes I checked after executing TRUNCATE statement the table has no records but after executing rollback there is records in the table.......
This is a strange problem.Please check with your DBA on this issue.
Sep 18 '07 #7

Expert 100+
P: 700
What postgres do u use?
On 8.0 and above truncate inside transactions can be rollbacked
see here
http://www.postgresql.org/docs/8.0/s...lease-7-4.html
Sep 18 '07 #8

priyan
P: 54
What postgres do u use?
On 8.0 and above truncate inside transactions can be rollbacked
see here
http://www.postgresql.org/docs/8.0/s...lease-7-4.html

Hi rski,
I am using postgre 8.2 version in this what I have to use to rollback.....Please help me to solve this problem....

Thanks
Sep 19 '07 #9

Expert 100+
P: 700
Hi rski,
I am using postgre 8.2 version in this what I have to use to rollback.....Please help me to solve this problem....

Thanks
Sorry i'm afraid i don't get what is u'r problem (maybe becase of my poor english).
What do u want to achieve?
Sep 19 '07 #10

priyan
P: 54
Sorry i'm afraid i don't get what is u'r problem (maybe becase of my poor english).
What do u want to achieve?

I will explain my problem briefly..... I executed TRUNCATE command on a table so the data in that table gets deleted and then I executed ROLLBACK command so when I execute this command it should not be rollbacked am I right. But it gets rollbacked and after that when I execute SELECT query the datas in the table which were there before executing TRUNCATE are retrieved......I am using postgre 8.2. Please explain me why it is happening like this......
Sep 19 '07 #11

Expert 100+
P: 700
As I said in postgres truncate operations are transactions-safe . I guess u can't just commit a transaction so u will need to nest transactions, using db_link module. Do u understand?
Sep 19 '07 #12

P: 15
yes ,this question ,i found too.

can not rollback .

it is not like the oracle.
Sep 20 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.