Kshipra wrote:
>
> hello all,
> I would like to mention something in this regard.
> I have executed all the commands given here in the same order, but what the
> auther is saying that after insert fails whatever u have inserted rolls back,
> this is not the case .
> as all of us knows Postgre works in autocommit mode,
Change that to "MAY work in autocommit mode".
> as all of us knows Postgre works in autocommit mode, so when user successfully
> inserts a row in a table and then again tries to insert the same row then
> already entered record will not get deleted from tha table.
For different reasons. SQL has no notion of "inserting the same row". If
you insert something twice, you will have two rows with the same values
in your table - which may be perfectly valid in some cases.
> On top of
> autocommit, we are executing COMMIT;
> so it will further explicitely commits the first transaction
You can't commit anything twice. Autocommit implicitly creates a
transaction around each statement. Any extra explicit COMMIT outside a
transaction block won't do anything but raise a warning.
> and will NOT
> ROLLBACK
> the succefully entered row.
Nor would it inside a transaction block.
CREATE TABLE foo (t text);
INSERT into foo values ('bar');
INSERT into foo values ('bar');
SELECT * from foo;
DROP table foo;
will give you two instances of bar inside table foo, just like the
transactional version
CREATE TABLE foo (t text);
BEGIN TRANSACTION;
INSERT into foo values ('bar');
INSERT into foo values ('bar');
COMMIT;
SELECT * from foo;
DROP table foo;
would. There is a difference between
CREATE TABLE foo (i int4);
INSERT into foo values (1);
INSERT into foo values ('bar');
SELECT * from foo;
DROP table foo;
and
CREATE TABLE foo (i int4);
BEGIN TRANSACTION;
INSERT into foo values (1);
INSERT into foo values ('bar');
COMMIT;
SELECT * from foo;
DROP table foo;
in that the data type error on the second insert will make the entire
transaction fail in the second example leaving you with an empty table,
where the first statement in the first example makes it into the table,
as its autocommit transaction has already successfully comitted before
the faulty statement gets executed.
> And that way also this should not happen in any condition, otherwise it will be
> so much duplication of work.
Sorry, you lost me there...
Sevo
--
sevo@ip23.net