The attached patch handles the simple case where a user wants to
increase the user-defined storage size of a variable length object,
such as VARCHAR or NUMERIC, without having to rebuild the table.

It does so by verifying that no transform was defined and testing
whether the user simply requested an increase to the size of a
variable length column.  If so, it skips type coercion and no longer
performs ATRewriteTables; instead, it allows ATExecAlterColumnType to
update the catalog to reflect said increase to the attribute's typmod
(as it currently does) and skips phase 3.  The former way to perform
this was by manually updating the catalog.

The current case of ALTERing a column to it's current data type
specification (often used to forcibly reclaim dropped column space)
has been maintained.

Use case:
CREATE TABLE test_tbl (
   test_id    NUMERIC(10) NOT NULL,
   test_name VARCHAR(32) NOT NULL,
   PRIMARY KEY (test_id));

ALTER TABLE test_tbl ALTER COLUMN test_name TYPE VARCHAR(64);

I can't find any case where this doesn't work... but perhaps you guys
know of some.

--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 2nd Floor            | [EMAIL PROTECTED]
Iselin, New Jersey 08830            | http://www.enterprisedb.com/
diff -cr pgsql/src/backend/commands/tablecmds.c pgsql-typmodvlincfix/src/backend/commands/tablecmds.c
*** pgsql/src/backend/commands/tablecmds.c	2006-10-13 17:43:18.000000000 -0400
--- pgsql-typmodvlincfix/src/backend/commands/tablecmds.c	2006-10-26 12:19:30.000000000 -0400
***************
*** 4880,4885 ****
--- 4880,4888 ----
  	Node	   *transform;
  	NewColumnValue *newval;
  	ParseState *pstate = make_parsestate(NULL);
+ 	bool		isSimpleTypemodIncrease = false;
+ 	HeapTuple	typeTuple;
+ 	Form_pg_type tform;
  
  	/* lookup the attribute so we can check inheritance status */
  	tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
***************
*** 4906,4912 ****
  						colName)));
  
  	/* Look up the target type */
! 	targettype = typenameTypeId(NULL, typename);
  
  	/* make sure datatype is legal for a column */
  	CheckAttributeType(colName, targettype);
--- 4909,4917 ----
  						colName)));
  
  	/* Look up the target type */
! 	typeTuple = typenameType(NULL, typename);
! 	tform = (Form_pg_type) GETSTRUCT(typeTuple);
! 	targettype = HeapTupleGetOid(typeTuple);
  
  	/* make sure datatype is legal for a column */
  	CheckAttributeType(colName, targettype);
***************
*** 4951,4982 ****
  	}
  	else
  	{
! 		transform = (Node *) makeVar(1, attnum,
! 									 attTup->atttypid, attTup->atttypmod,
! 									 0);
  	}
  
- 	transform = coerce_to_target_type(pstate,
- 									  transform, exprType(transform),
- 									  targettype, typename->typmod,
- 									  COERCION_ASSIGNMENT,
- 									  COERCE_IMPLICIT_CAST);
- 	if (transform == NULL)
- 		ereport(ERROR,
- 				(errcode(ERRCODE_DATATYPE_MISMATCH),
- 				 errmsg("column \"%s\" cannot be cast to type \"%s\"",
- 						colName, TypeNameToString(typename))));
- 
  	/*
! 	 * Add a work queue item to make ATRewriteTable update the column
! 	 * contents.
! 	 */
! 	newval = (NewColumnValue *) palloc0(sizeof(NewColumnValue));
! 	newval->attnum = attnum;
! 	newval->expr = (Expr *) transform;
  
! 	tab->newvals = lappend(tab->newvals, newval);
  
  	ReleaseSysCache(tuple);
  
  	/*
--- 4956,5012 ----
  	}
  	else
  	{
! 	    /*
! 	     * As no transform was defined, we want to test whether the user
! 		 * simply requested an increase to the size of a variable length
! 		 * column.  If so, we don't need to perform ATRewriteTables and will
! 		 * instead, just update the catalog to reflect said increase
! 		 * to the attribute's typmod.
! 	     */
! 		if (attTup->atttypid == targettype
! 			&& attTup->atttypmod < typename->typmod
! 			&& attTup->attndims == list_length(typename->arrayBounds)
! 			&& (attTup->attlen == tform->typlen && tform->typlen == -1)
! 			&& attTup->attbyval == tform->typbyval
! 			&& attTup->attalign == tform->typalign
! 			&& attTup->attstorage == tform->typstorage)
! 
!             isSimpleTypemodIncrease = true;
! 		else 
! 			transform = (Node *) makeVar(1, attnum,
! 										 attTup->atttypid, attTup->atttypmod,
! 										 0);
  	}
  
  	/*
! 	 * If this is just a simple typmod increase, there's no need to
! 	 * coerce types.
! 	 */
!     if (!isSimpleTypemodIncrease)
! 	{
! 		transform = coerce_to_target_type(pstate,
! 										  transform, exprType(transform),
! 										  targettype, typename->typmod,
! 										  COERCION_ASSIGNMENT,
! 										  COERCE_IMPLICIT_CAST);
! 		if (transform == NULL)
! 			ereport(ERROR,
! 					(errcode(ERRCODE_DATATYPE_MISMATCH),
! 					 errmsg("column \"%s\" cannot be cast to type \"%s\"",
! 							colName, TypeNameToString(typename))));
  
! 		/*
! 		 * Add a work queue item to make ATRewriteTable update the column
! 		 * contents.
! 		 */
! 		newval = (NewColumnValue *) palloc0(sizeof(NewColumnValue));
! 		newval->attnum = attnum;
! 		newval->expr = (Expr *) transform;
  
+ 		tab->newvals = lappend(tab->newvals, newval);
+ 	}
+ 
+ 	ReleaseSysCache(typeTuple);
  	ReleaseSysCache(tuple);
  
  	/*
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to