TRUNCATE statement patch
Started by Marcus Mascariover 26 years ago1 messages
Hello,
The following is a patch which patches cleanly
against the 6.5 release and which implement's
Oracle's TRUNCATE statement.
There are a few things to note about this patch:
1. It mirrors the operation of VACUUM in that it
acquires an Access Exclusive lock on the relation
being TRUNCATE'd (as well as its indexes, as
necessary).
2. It currently does not update pg_class with either
the "bogus" stats of 1000 tuples nor does it set
the number of tuples to 0.
3. You must have pg_ownercheck() permissions to
TRUNCATE a table.
I hope the PostgreSQL professionals can clean
this up sufficient enough to be useful to the rest
of the world (I find it increasingly useful as we
add more and more users).
Marcus "Mike" Mascari (mascarim@yahoo.com)
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Attachments:
truncate_patchapplication/x-unknown; name=truncate_patchDownload
diff -c -r pgsql-6.5-pure/src/backend/catalog/heap.c pgsql-6.5/src/backend/catalog/heap.c
*** pgsql-6.5-pure/src/backend/catalog/heap.c Fri Jun 4 03:00:15 1999
--- pgsql-6.5/src/backend/catalog/heap.c Wed Jun 30 02:13:52 1999
***************
*** 30,35 ****
--- 30,37 ----
#include "miscadmin.h"
#include "access/heapam.h"
+ #include "access/genam.h"
+ #include "access/xact.h"
#include "catalog/catalog.h"
#include "catalog/catname.h"
#include "catalog/heap.h"
***************
*** 39,44 ****
--- 41,47 ----
#include "catalog/pg_index.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_ipl.h"
+ #include "catalog/pg_proc.h"
#include "catalog/pg_relcheck.h"
#include "catalog/pg_type.h"
#include "commands/trigger.h"
***************
*** 58,63 ****
--- 61,67 ----
#include "utils/catcache.h"
#include "utils/builtins.h"
#include "utils/mcxt.h"
+ #include "utils/portal.h"
#include "utils/relcache.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
***************
*** 1046,1051 ****
--- 1050,1282 ----
heap_endscan(scan);
heap_close(indexRelation);
}
+
+ /* --------------------------------
+ * RelationTruncateIndexes - This routine is used to truncate all
+ * indices associated with the heap relation to zero tuples.
+ * The routine will truncate and then reconstruct the indices on
+ * the relation specified by the heapRelation parameter.
+ * --------------------------------
+ */
+
+ static void
+ RelationTruncateIndexes(Relation heapRelation) {
+
+ Relation indexRelation, currentIndex;
+ ScanKeyData entry;
+ HeapScanDesc scan;
+ HeapTuple indexTuple, procTuple, classTuple;
+ Form_pg_index index;
+ Oid heapId, indexId, procId, accessMethodId;
+ Node *oldPred = NULL;
+ PredInfo *predInfo;
+ List *cnfPred = NULL;
+ AttrNumber *attributeNumberA;
+ FuncIndexInfo fInfo, *funcInfo = NULL;
+ int i, numberOfAttributes;
+ char *predString;
+
+ /*** Save the id of the heap relation ***/
+
+ heapId = RelationGetRelid(heapRelation);
+
+ /*** Open the System relation, pg_index ***/
+
+ indexRelation = heap_openr(IndexRelationName);
+
+ /*** Scan pg_index For indexes related to heap relation ***/
+
+ ScanKeyEntryInitialize(&entry, 0x0, Anum_pg_index_indrelid, F_OIDEQ,
+ ObjectIdGetDatum(heapId));
+
+ scan = heap_beginscan(indexRelation, false, SnapshotNow, 1, &entry);
+ while (HeapTupleIsValid(indexTuple = heap_getnext(scan, 0))) {
+
+ /*** For each index, fetch index attributes ***/
+
+ index = (Form_pg_index) GETSTRUCT(indexTuple);
+ indexId = index->indexrelid;
+ procId = index->indproc;
+
+ for (i = 0; i < INDEX_MAX_KEYS; i++) {
+ if (index->indkey[i] == InvalidAttrNumber) break;
+ }
+ numberOfAttributes = i;
+
+ /*** If a valid where predicate, compute predicate Node ***/
+
+ if (VARSIZE(&index->indpred) != 0) {
+ predString = fmgr(F_TEXTOUT, &index->indpred);
+ oldPred = stringToNode(predString);
+ pfree(predString);
+ }
+
+ predInfo = (PredInfo *) palloc(sizeof(PredInfo));
+ predInfo->pred = (Node *) cnfPred;
+ /* predInfo->pred = (Node *) oldPred; */
+ predInfo->oldPred = oldPred;
+
+ /*** Assign Index keys to attributes array ***/
+
+ attributeNumberA = (AttrNumber *) palloc(numberOfAttributes *
+ sizeof(attributeNumberA[0]));
+ for (i = 0; i < numberOfAttributes; i++) {
+ attributeNumberA[i] = index->indkey[i];
+ }
+
+ /*** If this is a procedural index, initialize our FuncIndexInfo ***/
+
+ if (procId != InvalidOid) {
+ funcInfo = &fInfo;
+ FIsetnArgs(funcInfo, numberOfAttributes);
+ procTuple = SearchSysCacheTuple(PROOID, ObjectIdGetDatum(procId),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(procTuple)) {
+ elog(ERROR, "RelationTruncateIndexes: index procedure not found");
+ }
+ namecpy(&(funcInfo->funcName),
+ &(((Form_pg_proc) GETSTRUCT(procTuple))->proname));
+ FIsetProcOid(funcInfo, procTuple->t_data->t_oid);
+ }
+
+ /*** Fetch the classTuple associated with this index ***/
+
+ classTuple = SearchSysCacheTupleCopy(RELOID, ObjectIdGetDatum(indexId),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(classTuple)) {
+ elog(ERROR, "RelationTruncateIndexes: index access method not found");
+ }
+ accessMethodId = ((Form_pg_class) GETSTRUCT(classTuple))->relam;
+
+ /*** Open our index relation ***/
+
+ currentIndex = index_open(indexId);
+ if (currentIndex == NULL) {
+ elog(ERROR, "RelationTruncateIndexes: can't open index relation");
+ }
+
+ /*** Truncate the index before building ***/
+
+ smgrtruncate(DEFAULT_SMGR, currentIndex, 0);
+ currentIndex->rd_nblocks = 0;
+
+ /*** Initialize the index and rebuild ***/
+
+ InitIndexStrategy(numberOfAttributes, currentIndex, accessMethodId);
+ index_build(heapRelation, currentIndex, numberOfAttributes,
+ attributeNumberA, 0, NULL, funcInfo, predInfo);
+
+ /*** Re-open our heap relation and re-lock, since index_build ***/
+ /*** will close and unlock the relation ***/
+
+ heapRelation = heap_open(heapId);
+ LockRelation(heapRelation, AccessExclusiveLock);
+
+ /*** RelationUnsetLockForWrite(currentIndex); ***/
+
+ }
+
+ /*** Complete the scan and close the Catalogueindex Relation ***/
+
+ heap_endscan(scan);
+ heap_close(indexRelation);
+
+ }
+
+ /* ----------------------------
+ * heap_truncate
+ *
+ * This routine is used to truncate the data from the
+ * storange manager of any data within the relation handed
+ * to this routine. The routine assumes that the relation
+ * handed to this routine is an open relation.
+ *
+ * ----------------------------
+ */
+
+ void
+ heap_truncate(char *relname) {
+
+ Relation rel;
+ Oid rid;
+ Portal portal;
+ char *pname;
+ MemoryContext old;
+ PortalVariableMemory pmem;
+ NameData truncRel;
+
+ /*
+ * Create a portal for safe memory across transctions. We need to
+ * palloc the name space for it because our hash function expects the
+ * name to be on a longword boundary. CreatePortal copies the name to
+ * safe storage for us.
+ */
+
+ pname = (char *) palloc(strlen(TRUNCPNAME) + 1);
+ strcpy(pname, TRUNCPNAME);
+ portal = CreatePortal(pname);
+ pfree(pname);
+
+ /* relname gets de-allocated on transaction commit */
+
+ strcpy(truncRel.data, relname);
+
+ pmem = PortalGetVariableMemory(portal);
+ old = MemoryContextSwitchTo((MemoryContext) pmem);
+ MemoryContextSwitchTo(old);
+
+ /* Commit the current transaction */
+
+ CommitTransactionCommand();
+ StartTransactionCommand();
+
+ /* Open relation for processing */
+
+ rel = heap_openr(truncRel.data);
+ if (rel == NULL)
+ elog(ERROR, "Relation %s Does Not Exist!", truncRel.data);
+ rid = rel->rd_id;
+
+ LockRelation(rel, AccessExclusiveLock);
+
+ /* Release any buffers associated with this relation */
+
+ ReleaseRelationBuffers(rel);
+ BlowawayRelationBuffers(rel, 0);
+
+ /* Now truncate the actual data and set blocks to zero */
+
+ smgrtruncate(DEFAULT_SMGR, rel, 0);
+ rel->rd_nblocks = 0;
+
+ /* If this relation has indexes, truncate the indexes, which */
+ /* will unlock the relation as a result. Otherwise, unlock */
+ /* the relation ourselves. */
+
+ if (rel->rd_rel->relhasindex) {
+ RelationTruncateIndexes(rel);
+ } else {
+ UnlockRelation(rel, AccessExclusiveLock);
+ }
+
+ /* Close our relation */
+
+ heap_close(rel);
+ RelationForgetRelation(rid);
+
+ /* Destoy cross-transaction memory */
+
+ PortalDestroy(&portal);
+
+ /* Start new transaction */
+
+ CommitTransactionCommand();
+ StartTransactionCommand();
+
+ return;
+
+ }
+
/* --------------------------------
* DeleteRelationTuple
diff -c -r pgsql-6.5-pure/src/backend/commands/creatinh.c pgsql-6.5/src/backend/commands/creatinh.c
*** pgsql-6.5-pure/src/backend/commands/creatinh.c Wed May 26 03:02:39 1999
--- pgsql-6.5/src/backend/commands/creatinh.c Wed Jun 30 02:13:52 1999
***************
*** 162,167 ****
--- 162,187 ----
heap_destroy_with_catalog(name);
}
+ /*
+ * TruncateRelation --
+ * Removes all the rows from a relation
+ *
+ * Exceptions:
+ * BadArg if name is invalid
+ *
+ *
+ * Note:
+ * Rows are removed, indices are truncated and reconstructed.
+ */
+
+ void
+ TruncateRelation(char *name)
+ {
+
+ AssertArg(name);
+ heap_truncate(name);
+
+ }
/*
* MergeAttributes
diff -c -r pgsql-6.5-pure/src/backend/parser/gram.y pgsql-6.5/src/backend/parser/gram.y
*** pgsql-6.5-pure/src/backend/parser/gram.y Tue Jun 8 03:00:33 1999
--- pgsql-6.5/src/backend/parser/gram.y Wed Jun 30 02:15:52 1999
***************
*** 125,130 ****
--- 125,131 ----
%type <node> stmt,
AddAttrStmt, ClosePortalStmt,
CopyStmt, CreateStmt, CreateAsStmt, CreateSeqStmt, DefineStmt, DestroyStmt,
+ TruncateStmt,
ExtendStmt, FetchStmt, GrantStmt, CreateTrigStmt, DropTrigStmt,
CreatePLangStmt, DropPLangStmt,
IndexStmt, ListenStmt, UnlistenStmt, LockStmt, OptimizableStmt,
***************
*** 322,328 ****
OFFSET, OIDS, OPERATOR, PASSWORD, PROCEDURAL,
RENAME, RESET, RETURNS, ROW, RULE,
SEQUENCE, SERIAL, SETOF, SHARE, SHOW, START, STATEMENT, STDIN, STDOUT,
! TRUSTED,
UNLISTEN, UNTIL, VACUUM, VALID, VERBOSE, VERSION
/* Special keywords, not in the query language - see the "lex" file */
--- 323,329 ----
OFFSET, OIDS, OPERATOR, PASSWORD, PROCEDURAL,
RENAME, RESET, RETURNS, ROW, RULE,
SEQUENCE, SERIAL, SETOF, SHARE, SHOW, START, STATEMENT, STDIN, STDOUT,
! TRUNCATE, TRUSTED,
UNLISTEN, UNTIL, VACUUM, VALID, VERBOSE, VERSION
/* Special keywords, not in the query language - see the "lex" file */
***************
*** 386,392 ****
| CreateUserStmt
| ClusterStmt
| DefineStmt
! | DestroyStmt
| DropPLangStmt
| DropTrigStmt
| DropUserStmt
--- 387,394 ----
| CreateUserStmt
| ClusterStmt
| DefineStmt
! | DestroyStmt
! | TruncateStmt
| DropPLangStmt
| DropTrigStmt
| DropUserStmt
***************
*** 1602,1607 ****
--- 1604,1623 ----
}
;
+ /*****************************************************************************
+ *
+ * QUERY:
+ * truncate table relname
+ *
+ *****************************************************************************/
+
+ TruncateStmt: TRUNCATE TABLE relation_name
+ {
+ TruncateStmt *n = makeNode(TruncateStmt);
+ n->relName = $3;
+ $$ = (Node *)n;
+ }
+ ;
/*****************************************************************************
*
***************
*** 2365,2371 ****
$$ = (Node*)n;
}
;
-
/*****************************************************************************
*
--- 2381,2386 ----
diff -c -r pgsql-6.5-pure/src/backend/parser/keywords.c pgsql-6.5/src/backend/parser/keywords.c
*** pgsql-6.5-pure/src/backend/parser/keywords.c Thu May 13 03:01:16 1999
--- pgsql-6.5/src/backend/parser/keywords.c Wed Jun 30 02:13:52 1999
***************
*** 229,234 ****
--- 229,235 ----
{"trigger", TRIGGER},
{"trim", TRIM},
{"true", TRUE_P},
+ {"truncate", TRUNCATE},
{"trusted", TRUSTED},
{"type", TYPE_P},
{"union", UNION},
diff -c -r pgsql-6.5-pure/src/backend/tcop/utility.c pgsql-6.5/src/backend/tcop/utility.c
*** pgsql-6.5-pure/src/backend/tcop/utility.c Wed May 26 03:05:08 1999
--- pgsql-6.5/src/backend/tcop/utility.c Wed Jun 30 16:03:12 1999
***************
*** 217,222 ****
--- 217,234 ----
}
break;
+ case T_TruncateStmt:
+ {
+ PS_SET_STATUS(commandTag = "TRUNCATE");
+ CHECK_IF_ABORTED();
+ if (!pg_ownercheck(userName, ((TruncateStmt *) parsetree)->relName,
+ RELNAME)) {
+ elog(ERROR, "you do not own class \"%s\"", relname);
+ }
+ TruncateRelation(((TruncateStmt *) parsetree)->relName);
+ }
+ break;
+
case T_CopyStmt:
{
CopyStmt *stmt = (CopyStmt *) parsetree;
diff -c -r pgsql-6.5-pure/src/backend/utils/mmgr/portalmem.c pgsql-6.5/src/backend/utils/mmgr/portalmem.c
*** pgsql-6.5-pure/src/backend/utils/mmgr/portalmem.c Sun Jun 13 21:04:10 1999
--- pgsql-6.5/src/backend/utils/mmgr/portalmem.c Wed Jun 30 02:13:52 1999
***************
*** 422,427 ****
--- 422,429 ----
{
if (strcmp(pname, VACPNAME) == 0)
return true;
+ if (strcmp(pname, TRUNCPNAME) == 0)
+ return true;
return false;
}
diff -c -r pgsql-6.5-pure/src/include/catalog/heap.h pgsql-6.5/src/include/catalog/heap.h
*** pgsql-6.5-pure/src/include/catalog/heap.h Wed May 26 03:06:24 1999
--- pgsql-6.5/src/include/catalog/heap.h Wed Jun 30 02:13:53 1999
***************
*** 23,28 ****
--- 23,29 ----
TupleDesc tupdesc, char relkind, bool istemp);
extern void heap_destroy_with_catalog(char *relname);
+ extern void heap_truncate(char *relname);
extern void heap_destroy(Relation rel);
extern void InitNoNameRelList(void);
diff -c -r pgsql-6.5-pure/src/include/commands/creatinh.h pgsql-6.5/src/include/commands/creatinh.h
*** pgsql-6.5-pure/src/include/commands/creatinh.h Sat Feb 13 18:21:18 1999
--- pgsql-6.5/src/include/commands/creatinh.h Wed Jun 30 02:13:53 1999
***************
*** 17,21 ****
--- 17,22 ----
extern void DefineRelation(CreateStmt *stmt, char relkind);
extern void RemoveRelation(char *name);
+ extern void TruncateRelation(char *name);
#endif /* CREATINH_H */
diff -c -r pgsql-6.5-pure/src/include/nodes/nodes.h pgsql-6.5/src/include/nodes/nodes.h
*** pgsql-6.5-pure/src/include/nodes/nodes.h Thu May 27 03:00:41 1999
--- pgsql-6.5/src/include/nodes/nodes.h Wed Jun 30 02:13:53 1999
***************
*** 157,162 ****
--- 157,163 ----
T_VersionStmt,
T_DefineStmt,
T_DestroyStmt,
+ T_TruncateStmt,
T_ExtendStmt,
T_FetchStmt,
T_IndexStmt,
diff -c -r pgsql-6.5-pure/src/include/nodes/parsenodes.h pgsql-6.5/src/include/nodes/parsenodes.h
*** pgsql-6.5-pure/src/include/nodes/parsenodes.h Wed May 26 03:06:41 1999
--- pgsql-6.5/src/include/nodes/parsenodes.h Wed Jun 30 02:13:53 1999
***************
*** 283,288 ****
--- 283,298 ----
} DestroyStmt;
/* ----------------------
+ * Truncate Table Statement
+ * ----------------------
+ */
+ typedef struct TruncateStmt
+ {
+ NodeTag type;
+ char *relName; /* relation to be truncated */
+ } TruncateStmt;
+
+ /* ----------------------
* Extend Index Statement
* ----------------------
*/
diff -c -r pgsql-6.5-pure/src/include/storage/bufmgr.h pgsql-6.5/src/include/storage/bufmgr.h
*** pgsql-6.5-pure/src/include/storage/bufmgr.h Wed May 26 03:07:00 1999
--- pgsql-6.5/src/include/storage/bufmgr.h Wed Jun 30 02:13:53 1999
***************
*** 162,167 ****
--- 162,168 ----
extern void BufferRefCountRestore(int *refcountsave);
extern int SetBufferWriteMode(int mode);
extern void SetBufferCommitInfoNeedsSave(Buffer buffer);
+ extern int BlowawayRelationBuffers(Relation rel, BlockNumber block);
extern void UnlockBuffers(void);
extern void LockBuffer(Buffer buffer, int mode);
diff -c -r pgsql-6.5-pure/src/include/utils/portal.h pgsql-6.5/src/include/utils/portal.h
*** pgsql-6.5-pure/src/include/utils/portal.h Sun Jun 13 21:04:11 1999
--- pgsql-6.5/src/include/utils/portal.h Wed Jun 30 02:13:53 1999
***************
*** 62,67 ****
--- 62,68 ----
* Special portals (well, their names anyway)
*/
#define VACPNAME "<vacuum>"
+ #define TRUNCPNAME "<truncate>"
extern bool PortalNameIsSpecial(char *pname);
extern void AtEOXact_portals(void);
diff -c -r pgsql-6.5-pure/src/tools/pgindent/pgindent pgsql-6.5/src/tools/pgindent/pgindent
*** pgsql-6.5-pure/src/tools/pgindent/pgindent Wed May 26 03:07:47 1999
--- pgsql-6.5/src/tools/pgindent/pgindent Wed Jun 30 02:13:53 1999
***************
*** 163,168 ****
--- 163,169 ----
-TDepth \
-TDestReceiver \
-TDestroyStmt \
+ -TTruncateStmt \
-TDestroydbStmt \
-TDisplay \
-TDl_info \