X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3E25353B33 for ; Thu, 14 Apr 2005 12:47:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17353-10 for ; Thu, 14 Apr 2005 15:47:29 +0000 (GMT) Received: from web11.manitu.net (web11.manitu.net [217.11.48.111]) by svr1.postgresql.org (Postfix) with ESMTP id 2BF6753B40 for ; Thu, 14 Apr 2005 12:47:27 -0300 (ADT) Received: from tschibtschibojtschitsch (dsl-082-082-198-136.arcor-ip.net [82.82.198.136]) by web11.manitu.net (8.10.2-SOL3/8.10.2) with ESMTP id j3EFlP728302 for ; Thu, 14 Apr 2005 17:47:25 +0200 Subject: recovery after long delete From: Markus Bertheau To: pgsql-performance@postgresql.org Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-cIOIkPG1UTZWCI6aolqG" Date: Thu, 14 Apr 2005 17:47:23 +0200 Message-Id: <1113493643.7249.35.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.1.1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.121 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/292 X-Sequence-Number: 11689 --=-cIOIkPG1UTZWCI6aolqG Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Hi. Our professor told us the following story: Oracle. A client issued a selective delete statement on a big table. After two days he lost patience and pulled the plug. Unfortunately while starting up, oracle had to restore all the deleted rows, which took it another two days. He reasoned that one better copies all rows that are not to be deleted in another table drops the original table afterwards. (Concurrency, fks, indexes are not the question here). So I wondered how this works in PostgreSQL. As I understand it, what's going on is the following: 1. The transaction 45 is started. It is recorded as in-progress. 2. The rows selected in the delete statement are one by one marked as to-be-deleted by txn 45. Among them row 27. 3. If a concurrently running read committed txn 47 wants to update row 27, it blocks, awaiting whether txn 45 commits or aborts. 4.1 When txn 45 commits, it is marked as such. 5.1 txn 47 can continue, but as row 27 was deleted, it is not affected by txn 47's update statement. 4.2 When txn 45 aborts, it is marked as such. This means the same as not being marked at all. 5.2 txn 47 continues and updates row 27. Now if you pull the plug after 2, at startup, pg will go through the in-progress txns and mark them as aborted. That's all the recovery in this case. All rows are still there. O(1). How does oracle do that? Has all this something to do with mvcc? Why does it take oracle so long to recover? Thanks Markus --=20 Markus Bertheau --=-cIOIkPG1UTZWCI6aolqG Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQBCXpCL2RUnMPztcykRAgg9AJ4hPhc/8PMPzCUUIj1DHcdtjE0PrwCeK49d YXyz2RX62jFAvtncEC2q5yM= =sjJO -----END PGP SIGNATURE----- --=-cIOIkPG1UTZWCI6aolqG--