Something weird is going on with some of mai emails - I tried to send this from
[EMAIL PROTECTED],
then from [EMAIL PROTECTED] and it does not appear in list. Another answer sent
after this
came through in less than 5 min.
Now I removed Tom Lane from To: and moved pgsql-hackers@postgresql.org from CC:
to To: field
On T, 2005-04-26 at 17:54 -0400, Tom Lane wrote:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > Could I avoid having a transaction at all?
>
> Not really; too much of the database access infrastructure is tied to
> transaction stuff ... even facilities as basic as memory management.
>
> > As VACUUM is not "transactional" in the sense that it does not change
> > anything visible to users ever, can't be undone by rollback, etc... ,
> > could it be possible to create enough "transaction-like" environment for
> > it to really run outside of transactions. Perhaps just advancing
> > oldestXmin at certain intervals ?
>
> I wonder whether you could avoid advertising the VACUUM's XID in PGPROC.
> Not sure that this can work, but it would be a lot simpler than stopping
> and starting transactions ...
What I came up with is adding an extra bool to PGPROC (inVacuum) which
is set if the current command is Vacuum in its own transaction and
modifying GetOldestXmin() tos skip backends where inVacuum==true. I also
added this skipping logic to where PGPROC-xmin is set.
The resultin code passes 'make check' and also seems to do the right
thing, wrt its purpose. I tested it by generating a 2M row table
'bightable' and then running 'vacuum verbose analyse bigtable;' on it
(runtime ~25 sec).
At the same time I repeatedly ran in another connection
'update smalltable set data=data+1;vacuum verbose smalltable;'
which was able to free all 16 old rows all the time during both vacuum
and analyse phases.
The only surprise was that it did not work during plain 'analyse
bigtable;'. It seems that plain analyse does not set use_own_xacts and
thus cant't make use of this optimisation.
Please comment on the attached patch (against ver 8.0.2), especially
what could be broken (WAL, some trx isolation level, ... ?) and how to
test if it is.
Also, does this have any chance to get accepted in 8.1 ?
--
Hannu Krosing <[EMAIL PROTECTED]>
diff -c -r postgresql-8.0.2/src/backend/access/transam/xact.c postgresql-8.0.2.orig/src/backend/access/transam/xact.c
*** postgresql-8.0.2/src/backend/access/transam/xact.c 2005-05-07 22:42:03.000000000 +0300
--- postgresql-8.0.2.orig/src/backend/access/transam/xact.c 2004-12-31 23:59:29.000000000 +0200
***************
*** 1411,1424 ****
AfterTriggerBeginXact();
/*
- * mark the transaction as not VACUUM (vacuum_rel will set isVacuum to true
- * directly after calling BeginTransactionCommand() )
- */
- if (MyProc != NULL)
- {
- MyProc->inVacuum = false;
- }
- /*
* done with start processing, set current transaction state to "in
* progress"
*/
--- 1411,1416 ----
diff -c -r postgresql-8.0.2/src/backend/commands/vacuum.c postgresql-8.0.2.orig/src/backend/commands/vacuum.c
*** postgresql-8.0.2/src/backend/commands/vacuum.c 2005-05-08 00:36:55.000000000 +0300
--- postgresql-8.0.2.orig/src/backend/commands/vacuum.c 2004-12-31 23:59:42.000000000 +0200
***************
*** 37,43 ****
#include "miscadmin.h"
#include "storage/buf_internals.h"
#include "storage/freespace.h"
- #include "storage/proc.h"
#include "storage/sinval.h"
#include "storage/smgr.h"
#include "tcop/pquery.h"
--- 37,42 ----
***************
*** 424,434 ****
if (use_own_xacts)
{
StartTransactionCommand();
- if (MyProc != NULL) /* is this needed here ? */
- {
- /* so other vacuums don't look at our xid/xmin in GetOldestXmin() */
- MyProc->inVacuum = true;
- }
/* functions in indexes may want a snapshot set */
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
}
--- 423,428 ----
***************
*** 892,904 ****
/* Begin a transaction for vacuuming this relation */
StartTransactionCommand();
-
- if (MyProc != NULL) /* is this needed here ? */
- {
- /* so other vacuums don't look at our xid/xmin in GetOldestXmin() */
- MyProc->inVacuum = true;
- }
-
/* functions in indexes may want a snapshot set */
ActiveSnapshot = CopySnapshot(GetTransactionSnapshot());
--- 886,891 ----
diff -c -r postgresql-8.0.2/src/backend/storage/ipc/sinval.c postgresql-8.0.2.orig/src/backend/storage/ipc/sinval.c
*** postgresql-8.0.2/src/backend/storage/ipc/sinval.c 2005-05-07 22:55:45.000000000 +0300
--- postgresql-8.0.2.orig/src/backend/storage/ipc/sinval.c 2005-01-01 00:00:56.000000000 +0200
***************
*** 697,703 ****
{
PGPROC *proc = (PGPROC *) MAKE_PTR(pOffset);
! if ((proc->inVacuum == false) && (allDbs || proc->databaseId == MyDatabaseId))
{
/* Fetch xid just once - see GetNewTransactionId */
TransactionId xid = proc->xid;
--- 697,703 ----
{
PGPROC *proc = (PGPROC *) MAKE_PTR(pOffset);
! if (allDbs || proc->databaseId == MyDatabaseId)
{
/* Fetch xid just once - see GetNewTransactionId */
TransactionId xid = proc->xid;
***************
*** 845,858 ****
* them as running anyway. We also assume that such xacts
* can't compute an xmin older than ours, so they needn't be
* considered in computing globalxmin.
- *
- * there is also no need to consider transaxtions runnibg the
- * vacuum command as it will not affect tuple visibility
*/
if (proc == MyProc ||
!TransactionIdIsNormal(xid) ||
! TransactionIdFollowsOrEquals(xid, xmax) ||
! proc->inVacuum == true )
continue;
if (TransactionIdPrecedes(xid, xmin))
--- 845,854 ----
* them as running anyway. We also assume that such xacts
* can't compute an xmin older than ours, so they needn't be
* considered in computing globalxmin.
*/
if (proc == MyProc ||
!TransactionIdIsNormal(xid) ||
! TransactionIdFollowsOrEquals(xid, xmax))
continue;
if (TransactionIdPrecedes(xid, xmin))
diff -c -r postgresql-8.0.2/src/include/storage/proc.h postgresql-8.0.2.orig/src/include/storage/proc.h
*** postgresql-8.0.2/src/include/storage/proc.h 2005-05-07 23:26:46.000000000 +0300
--- postgresql-8.0.2.orig/src/include/storage/proc.h 2005-01-01 00:03:42.000000000 +0200
***************
*** 63,73 ****
* were starting our xact: vacuum must not
* remove tuples deleted by xid >= xmin ! */
- bool inVacuum; /* true if current command is vacuum.
- * xid or xmin of other vacuum commands
- * need not be used when
- * finding global xmin for removing tuples */
-
int pid; /* This backend's process id */
Oid databaseId; /* OID of database this backend is using */
--- 63,68 ----
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
