Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3d3ca01

Browse files
committed
Avoid creating a TOAST table if we can prove that the maximum tuple
length is < TOAST_TUPLE_THRESHOLD, even with toastable column typespresent. For example, CREATE TABLE foo (f1 int, f2 varchar(100))does not require a toast table, even though varchar is a toastabletype.
1 parenta5da56d commit3d3ca01

File tree

3 files changed

+149
-86
lines changed

3 files changed

+149
-86
lines changed

‎src/backend/commands/command.c

Lines changed: 98 additions & 84 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.95 2000/08/21 17:22:32 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.96 2000/08/25 18:05:54 tgl Exp $
1212
*
1313
* NOTES
1414
* The PerformAddAttribute() code, like most of the relation
@@ -19,6 +19,7 @@
1919
*/
2020
#include"postgres.h"
2121

22+
#include"access/tuptoaster.h"
2223
#include"catalog/catalog.h"
2324
#include"catalog/catname.h"
2425
#include"catalog/index.h"
@@ -52,6 +53,9 @@
5253
#include"access/genam.h"
5354

5455

56+
staticboolneeds_toast_table(Relationrel);
57+
58+
5559
/* --------------------------------
5660
*PortalCleanup
5761
* --------------------------------
@@ -715,6 +719,7 @@ systable_beginscan(Relation rel, const char *indexRelname, int nkeys, ScanKey en
715719
sysscan->scan=heap_beginscan(rel, false,SnapshotNow,nkeys,entry);
716720
return (void*)sysscan;
717721
}
722+
718723
staticvoid
719724
systable_endscan(void*scan)
720725
{
@@ -731,6 +736,7 @@ systable_endscan(void *scan)
731736
heap_endscan(sysscan->scan);
732737
pfree(scan);
733738
}
739+
734740
staticHeapTuple
735741
systable_getnext(void*scan)
736742
{
@@ -780,6 +786,7 @@ find_attribute_walker(Node *node, int attnum)
780786
}
781787
returnexpression_tree_walker(node,find_attribute_walker, (void*)attnum);
782788
}
789+
783790
staticbool
784791
find_attribute_in_node(Node*node,intattnum)
785792
{
@@ -1377,17 +1384,14 @@ AlterTableCreateToastTable(const char *relationName, bool silent)
13771384
HeapTuplereltup;
13781385
HeapTupleDataclasstuple;
13791386
TupleDesctupdesc;
1380-
Form_pg_attribute*att;
13811387
Relationclass_rel;
13821388
Bufferbuffer;
13831389
Relationridescs[Num_pg_class_indices];
13841390
Oidtoast_relid;
13851391
Oidtoast_idxid;
1386-
boolhas_toastable_attrs= false;
1387-
inti;
13881392
chartoast_relname[NAMEDATALEN+1];
13891393
chartoast_idxname[NAMEDATALEN+1];
1390-
Relationtoast_rel;
1394+
Relationtoast_idxrel;
13911395
IndexInfo*indexInfo;
13921396
OidclassObjectId[1];
13931397

@@ -1400,15 +1404,23 @@ AlterTableCreateToastTable(const char *relationName, bool silent)
14001404
#endif
14011405

14021406
/*
1403-
* lock the pg_class tuple for update
1407+
* Grab an exclusive lock on the target table, which we will NOT
1408+
* release until end of transaction.
1409+
*/
1410+
rel=heap_openr(relationName,AccessExclusiveLock);
1411+
myrelid=RelationGetRelid(rel);
1412+
1413+
/*
1414+
* lock the pg_class tuple for update (is that really needed?)
14041415
*/
1416+
class_rel=heap_openr(RelationRelationName,RowExclusiveLock);
1417+
14051418
reltup=SearchSysCacheTuple(RELNAME,PointerGetDatum(relationName),
14061419
0,0,0);
1407-
14081420
if (!HeapTupleIsValid(reltup))
14091421
elog(ERROR,"ALTER TABLE: relation \"%s\" not found",
14101422
relationName);
1411-
class_rel=heap_openr(RelationRelationName,RowExclusiveLock);
1423+
14121424
classtuple.t_self=reltup->t_self;
14131425
switch (heap_mark4update(class_rel,&classtuple,&buffer))
14141426
{
@@ -1422,27 +1434,18 @@ AlterTableCreateToastTable(const char *relationName, bool silent)
14221434
ReleaseBuffer(buffer);
14231435

14241436
/*
1425-
* Grab an exclusive lock on the target table, which we will NOT
1426-
* release until end of transaction.
1427-
*/
1428-
rel=heap_openr(relationName,AccessExclusiveLock);
1429-
myrelid=RelationGetRelid(rel);
1430-
1431-
/*
1432-
* Check if there are any toastable attributes on the table
1437+
* XXX is the following check sufficient? At least it would
1438+
* allow to create TOAST tables for views. But why not - someone
1439+
* can insert into a view, so it shouldn't be impossible to hide
1440+
* huge data there :-)
14331441
*/
1434-
tupdesc=rel->rd_att;
1435-
att=tupdesc->attrs;
1436-
for (i=0;i<tupdesc->natts;i++)
1442+
if (((Form_pg_class)GETSTRUCT(reltup))->relkind!=RELKIND_RELATION)
14371443
{
1438-
if (att[i]->attstorage!='p')
1439-
{
1440-
has_toastable_attrs= true;
1441-
break;
1442-
}
1444+
elog(ERROR,"ALTER TABLE: relation \"%s\" is not a table",
1445+
relationName);
14431446
}
14441447

1445-
if (!has_toastable_attrs)
1448+
if (((Form_pg_class)GETSTRUCT(reltup))->reltoastrelid!=InvalidOid)
14461449
{
14471450
if (silent)
14481451
{
@@ -1452,24 +1455,14 @@ AlterTableCreateToastTable(const char *relationName, bool silent)
14521455
return;
14531456
}
14541457

1455-
elog(ERROR,"ALTER TABLE: relation \"%s\" has no toastable attributes",
1456-
relationName);
1457-
}
1458-
1458+
elog(ERROR,"ALTER TABLE: relation \"%s\" already has a toast table",
1459+
relationName);
1460+
}
14591461

14601462
/*
1461-
* XXX is the following check sufficient? At least it would
1462-
* allow to create TOAST tables for views. But why not - someone
1463-
* can insert into a view, so it shouldn't be impossible to hide
1464-
* huge data there :-)
1463+
* Check to see whether the table actually needs a TOAST table.
14651464
*/
1466-
if (((Form_pg_class)GETSTRUCT(reltup))->relkind!=RELKIND_RELATION)
1467-
{
1468-
elog(ERROR,"ALTER TABLE: relation \"%s\" is not a table",
1469-
relationName);
1470-
}
1471-
1472-
if (((Form_pg_class)GETSTRUCT(reltup))->reltoastrelid!=InvalidOid)
1465+
if (!needs_toast_table(rel))
14731466
{
14741467
if (silent)
14751468
{
@@ -1479,9 +1472,9 @@ AlterTableCreateToastTable(const char *relationName, bool silent)
14791472
return;
14801473
}
14811474

1482-
elog(ERROR,"ALTER TABLE: relation \"%s\"already has a toast table",
1483-
relationName);
1484-
}
1475+
elog(ERROR,"ALTER TABLE: relation \"%s\"does not need a toast table",
1476+
relationName);
1477+
}
14851478

14861479
/*
14871480
* Create the toast table and its index
@@ -1518,8 +1511,9 @@ AlterTableCreateToastTable(const char *relationName, bool silent)
15181511
* collision, and the toast rel will be destroyed when its master is,
15191512
* so there's no need to handle the toast rel as temp.
15201513
*/
1521-
heap_create_with_catalog(toast_relname,tupdesc,RELKIND_TOASTVALUE,
1522-
false, true);
1514+
toast_relid=heap_create_with_catalog(toast_relname,tupdesc,
1515+
RELKIND_TOASTVALUE,
1516+
false, true);
15231517

15241518
/* make the toast relation visible, else index creation will fail */
15251519
CommandCounterIncrement();
@@ -1540,18 +1534,18 @@ AlterTableCreateToastTable(const char *relationName, bool silent)
15401534
BTREE_AM_OID,classObjectId,
15411535
false, false, true);
15421536

1543-
/* make the index visible in this transaction */
1544-
CommandCounterIncrement();
1537+
/*
1538+
* Update toast rel's pg_class entry to show that it has an index.
1539+
* NOTE this also does CommandCounterIncrement() to make index visible.
1540+
*/
1541+
setRelhasindexInplace(toast_relid, true, false);
15451542

15461543
/*
1547-
* Get theOIDs of the newly createdobjects
1544+
* Get theOID of the newly createdindex
15481545
*/
1549-
toast_rel=heap_openr(toast_relname,NoLock);
1550-
toast_relid=RelationGetRelid(toast_rel);
1551-
heap_close(toast_rel,NoLock);
1552-
toast_rel=index_openr(toast_idxname);
1553-
toast_idxid=RelationGetRelid(toast_rel);
1554-
index_close(toast_rel);
1546+
toast_idxrel=index_openr(toast_idxname);
1547+
toast_idxid=RelationGetRelid(toast_idxrel);
1548+
index_close(toast_idxrel);
15551549

15561550
/*
15571551
* Store the toast table- and index-Oid's in the relation tuple
@@ -1569,36 +1563,6 @@ AlterTableCreateToastTable(const char *relationName, bool silent)
15691563

15701564
heap_freetuple(reltup);
15711565

1572-
/*
1573-
* Finally update the toast relations pg_class tuple to say
1574-
* it has an index.
1575-
*/
1576-
reltup=SearchSysCacheTuple(RELNAME,PointerGetDatum(toast_relname),
1577-
0,0,0);
1578-
if (!HeapTupleIsValid(reltup))
1579-
elog(ERROR,"ALTER TABLE: just created toast relation \"%s\" not found",
1580-
toast_relname);
1581-
classtuple.t_self=reltup->t_self;
1582-
switch (heap_mark4update(class_rel,&classtuple,&buffer))
1583-
{
1584-
caseHeapTupleSelfUpdated:
1585-
caseHeapTupleMayBeUpdated:
1586-
break;
1587-
default:
1588-
elog(ERROR,"couldn't lock pg_class tuple");
1589-
}
1590-
reltup=heap_copytuple(&classtuple);
1591-
ReleaseBuffer(buffer);
1592-
1593-
((Form_pg_class)GETSTRUCT(reltup))->relhasindex= true;
1594-
heap_update(class_rel,&reltup->t_self,reltup,NULL);
1595-
1596-
CatalogOpenIndices(Num_pg_class_indices,Name_pg_class_indices,ridescs);
1597-
CatalogIndexInsert(ridescs,Num_pg_class_indices,class_rel,reltup);
1598-
CatalogCloseIndices(Num_pg_class_indices,ridescs);
1599-
1600-
heap_freetuple(reltup);
1601-
16021566
/*
16031567
* Close relations and make changes visible
16041568
*/
@@ -1608,6 +1572,56 @@ AlterTableCreateToastTable(const char *relationName, bool silent)
16081572
CommandCounterIncrement();
16091573
}
16101574

1575+
/*
1576+
* Check to see whether the table needs a TOAST table. It does only if
1577+
* (1) there are any toastable attributes, and (2) the maximum length
1578+
* of a tuple could exceed TOAST_TUPLE_THRESHOLD. (We don't want to
1579+
* create a toast table for something like "f1 varchar(20)".)
1580+
*/
1581+
staticbool
1582+
needs_toast_table(Relationrel)
1583+
{
1584+
int32data_length=0;
1585+
boolmaxlength_unknown= false;
1586+
boolhas_toastable_attrs= false;
1587+
TupleDesctupdesc;
1588+
Form_pg_attribute*att;
1589+
int32tuple_length;
1590+
inti;
1591+
1592+
tupdesc=rel->rd_att;
1593+
att=tupdesc->attrs;
1594+
1595+
for (i=0;i<tupdesc->natts;i++)
1596+
{
1597+
data_length=att_align(data_length,att[i]->attlen,att[i]->attalign);
1598+
if (att[i]->attlen >=0)
1599+
{
1600+
/* Fixed-length types are never toastable */
1601+
data_length+=att[i]->attlen;
1602+
}
1603+
else
1604+
{
1605+
int32maxlen=type_maximum_size(att[i]->atttypid,
1606+
att[i]->atttypmod);
1607+
1608+
if (maxlen<0)
1609+
maxlength_unknown= true;
1610+
else
1611+
data_length+=maxlen;
1612+
if (att[i]->attstorage!='p')
1613+
has_toastable_attrs= true;
1614+
}
1615+
}
1616+
if (!has_toastable_attrs)
1617+
return false;/* nothing to toast? */
1618+
if (maxlength_unknown)
1619+
return true;/* any unlimited-length attrs? */
1620+
tuple_length=MAXALIGN(offsetof(HeapTupleHeaderData,t_bits)+
1621+
BITMAPLEN(tupdesc->natts))+
1622+
MAXALIGN(data_length);
1623+
return (tuple_length>TOAST_TUPLE_THRESHOLD);
1624+
}
16111625

16121626

16131627
/*

‎src/backend/utils/adt/format_type.c

Lines changed: 49 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/format_type.c,v 1.3 2000/08/21 18:23:18 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/format_type.c,v 1.4 2000/08/25 18:05:54 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -20,6 +20,7 @@
2020
#include"fmgr.h"
2121
#include"catalog/pg_type.h"
2222
#include"utils/builtins.h"
23+
#include"utils/numeric.h"
2324
#include"utils/syscache.h"
2425

2526
#defineMAX_INT32_LEN 11
@@ -214,6 +215,53 @@ format_type_internal(Oid type_oid, int32 typemod)
214215
}
215216

216217

218+
/*
219+
* type_maximum_size --- determine maximum length of a varlena column
220+
*
221+
* If the max length is indeterminate, return -1. In particular, we return
222+
* -1 for any type not known to this routine. We assume the caller has
223+
* already determined that the type is a varlena type, so it's not
224+
* necessary to look up the type's pg_type tuple here.
225+
*
226+
* This may appear unrelated to format_type(), but in fact the two routines
227+
* share knowledge of the encoding of typmod for different types, so it's
228+
* convenient to keep them together.
229+
*/
230+
int32
231+
type_maximum_size(Oidtype_oid,int32typemod)
232+
{
233+
if (typemod <=0)
234+
return-1;
235+
236+
switch (type_oid)
237+
{
238+
caseBPCHAROID:
239+
caseVARCHAROID:
240+
/* typemod includes varlena header */
241+
returntypemod;
242+
243+
caseNUMERICOID:
244+
/* precision (ie, max # of digits) is in upper bits of typmod */
245+
if (typemod>VARHDRSZ)
246+
{
247+
intprecision= ((typemod-VARHDRSZ) >>16)&0xffff;
248+
249+
/* Numeric stores 2 decimal digits/byte, plus header */
250+
return (precision+1) /2+NUMERIC_HDRSZ;
251+
}
252+
break;
253+
254+
caseVARBITOID:
255+
caseZPBITOID:
256+
/* typemod is the (max) number of bits */
257+
return (typemod+ (BITSPERBYTE-1)) /BITSPERBYTE
258+
+2*sizeof(int32);
259+
}
260+
261+
/* Unknown type, or unlimited-length type such as 'text' */
262+
return-1;
263+
}
264+
217265

218266
/*
219267
* oidvectortypes- converts a vector of type OIDs to "typname" list

‎src/include/utils/builtins.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2000, PostgreSQL, Inc
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: builtins.h,v 1.134 2000/08/24 03:29:14 tgl Exp $
10+
* $Id: builtins.h,v 1.135 2000/08/25 18:05:53 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -583,5 +583,6 @@ extern Datum PG_char_to_encoding(PG_FUNCTION_ARGS);
583583
/* format_type.c */
584584
externDatumformat_type(PG_FUNCTION_ARGS);
585585
externDatumoidvectortypes(PG_FUNCTION_ARGS);
586+
externint32type_maximum_size(Oidtype_oid,int32typemod);
586587

587588
#endif/* BUILTINS_H */

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp