Обсуждение: disable auto-commit

In psql, once I delete an entry, it's gone permanently.  How do I disable
such auto-commit behavior?

* Joel Pang <joelpang@spectratech.com> [001026 04:27] wrote:
> In psql, once I delete an entry, it's gone permanently.  How do I disable
> such auto-commit behavior?


by beginning a transaction, just type:

"BEGIN;"

then with 'COMMIT;' (to commit) or 'ROLLBACK' (to rollback).


--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Well, you could start a transaction. But the record will be deleted once
you do a COMMIT. Is that what you want? If you want some way to
"delete" an entry, but then to later "undelete" it, there really isn't a
way. I have heard of other systems where things were "logically deleted",
but recoverable until a "purge" command was done. PostgreSQL does not have
this type of behaviour.

John

On Wed, 25 Oct 2000, Joel Pang wrote:

> In psql, once I delete an entry, it's gone permanently.  How do I disable
> such auto-commit behavior?
>

If are deleting something, then you probably want delete it...

I mean that the DELETE command phisically removes some
rows from a table, and after the command they are gone forever.


If you want to delete something that can be restored later, then
you are not actually deleting anything, you are just "hiding" from
the user but still keeping in the db.
So what I usually do in this cases is using a boolean field named
"hide" . If the user choose to delete a row I actually put the
"true" value in that field, if I want to restore I can turn back
the field value to "false".

Then I select rows from the table with the "WHERE NOT hide"
clause whenever I don't want the user to see what he "deleted" .

I use to default the field value to "false" so I can forget it and use
it only when I want to hide something. e.g.:
CREATE TABLE foo (
...
...
...
hide bool DEFAULT false);





Commit does not mean much in this contest.  You commit
when you force the operating system to write in the disk any
changes it is storing in memory buffers, when you commit
you flush the cache buffers to the disk.
By default postgresql commit to the disk whenever there is any
change, you can speed up the system by disabling this feature
starting postmaster with "-o -F" .
Anyway even if you do not commit the changes are stored
in the operating system disk cache and you cannot undo them.


Bye,
Jacopo




----- Original Message -----
From: "John McKown" <joarmc@swbell.net>
To: "Joel Pang" <joelpang@spectratech.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Thursday, October 26, 2000 9:30 PM
Subject: Re: [ADMIN] disable auto-commit


> Well, you could start a transaction. But the record will be deleted once
> you do a COMMIT. Is that what you want? If you want some way to
> "delete" an entry, but then to later "undelete" it, there really isn't a
> way. I have heard of other systems where things were "logically deleted",
> but recoverable until a "purge" command was done. PostgreSQL does not have

> this type of behaviour.
>
> John
>
> On Wed, 25 Oct 2000, Joel Pang wrote:
>
> > In psql, once I delete an entry, it's gone permanently.  How do I
disable
> > such auto-commit behavior?
> >
>

> Anyway even if you do not commit the changes are stored
> in the operating system disk cache and you cannot undo them.

I disagree, witness the following :

testbunker=> create table temptable (
testbunker->  foo int4,
testbunker->  bar text
testbunker-> );
CREATE
testbunker=> insert into temptable values ( 1, 'bar' );
INSERT 34605 1
testbunker=> insert into temptable values ( 2, 'baz' );
INSERT 34606 1
testbunker=> insert into temptable values ( 3, 'foo' );
INSERT 34607 1
testbunker=> begin;
BEGIN
testbunker=> delete from temptable;
DELETE 3
testbunker=> select * from temptable;
foo|bar
---+---
(0 rows)

testbunker=> rollback;
ABORT
testbunker=> select * from temptable;
foo|bar
---+---
  1|bar
  2|baz
  3|foo
(3 rows)


As you can see, I disabled auto-commit, deleted everything from the table,
confirmed it was gone, then rolled back, and everything was as it was
before.

Sincerely, Warren