pg.ddx.io  pgsql-interfaces@postgresql.org mailing list archive  

help / color / mirror / Atom feed

questions about transaction and rollback
2+ messages / 2 participants
[nested] [flat]

* questions about transaction and rollback
@ 2005-04-14 15:48 jing han <jing_han_66@yahoo.com>
  2005-04-14 16:52 ` Re: questions about transaction and rollback Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 1 reply; 2+ messages in thread

From: jing han @ 2005-04-14 15:48 UTC (permalink / raw)
  To: pgsql-interfaces

Hello,

I have questions about transaction and rollback.

Question 1:
In the postgresql.conf file of our running system,
autocommit=true. If leave it as true, can we disable
this attribute when we issue a connection to database?
Such as:

$db = 
        DBI->connect
            (
                'DBI:Pg:dbname=xxx',
                'user name', 
                'password',
                {
                    AutoCommit => 0
                }
            ) 


Question 2:

If we use the following command to restore database.
system("su -l postgres -c \"psql -d dbname -f
/tmp/db_dump\""); 

Can command psql rollback if in the middle of restore
power failure happens?


Question 3:

During restore, if power failure happens, we want to
rollback everything having been inserted or updated to
database. In another word, if we are restoring a
database with thousands of turples changes, at the
time, we restore the last turple, power failure
happens, we want to rollback.  Can postgres support
huge data changes in one transaction block? How to
configure the limit of how much we can do in one
transaction block?


Hope to hear from you soon and thank you in advance.


Best Regards

jing


		
__________________________________ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: questions about transaction and rollback
  2005-04-14 15:48 questions about transaction and rollback jing han <jing_han_66@yahoo.com>
@ 2005-04-14 16:52 ` Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Joshua D. Drake @ 2005-04-14 16:52 UTC (permalink / raw)
  To: jing han <jing_han_66@yahoo.com>; +Cc: pgsql-interfaces

jing han wrote:

>Hello,
>
>I have questions about transaction and rollback.
>
>Question 1:
>In the postgresql.conf file of our running system,
>autocommit=true. If leave it as true, can we disable
>this attribute when we issue a connection to database?
>
 From the DBD::Pg documentation:

The transaction behavior is now controlled with the attribute 
AutoCommit. For a complete definition of AutoCommit
please refer to the DBI documentation.

According to the DBI specification the default for AutoCommit is TRUE. 
In this mode, any change to the database
becomes valid immediately. Any ’begin’, ’commit’ or ’rollback’ statement 
will be rejected.

If AutoCommit is switched-off, immediately a transaction will be started 
by issuing a ’begin’ statement.
Any ’commit’ or ’rollback’ will start a new transaction. A disconnect 
will issue a ’rollback’ statement.


>
>
>Question 2:
>
>If we use the following command to restore database.
>system("su -l postgres -c \"psql -d dbname -f
>/tmp/db_dump\""); 
>
>Can command psql rollback if in the middle of restore
>power failure happens?
>
If the db_dump file starts with a transaction yes.

>
>
>Question 3:
>
>During restore, if power failure happens, we want to
>rollback everything having been inserted or updated to
>database. In another word, if we are restoring a
>database with thousands of turples changes, at the
>time, we restore the last turple, power failure
>happens, we want to rollback.  Can postgres support
>huge data changes in one transaction block?
>
Yes.

> How to
>configure the limit of how much we can do in one
>transaction block?
>
That would be controlled by issuing multiple transactions.

>
>
>Hope to hear from you soon and thank you in advance.
>
Sincerely,

Joshua D. Drake



>
>
>Best Regards
>
>jing
>
>
>		
>__________________________________ 
>Do you Yahoo!? 
>Yahoo! Small Business - Try our new resources site!
>http://smallbusiness.yahoo.com/resources/ 
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-04-14 16:52 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-14 15:48 questions about transaction and rollback jing han <jing_han_66@yahoo.com>
2005-04-14 16:52 ` Joshua D. Drake <jd@commandprompt.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox