Skip to content

Commit 85c0eac

Browse files
author
Neil Conway
committed
Add TABLESPACE and ON COMMIT clauses to CREATE TABLE AS. ON COMMIT is
required by the SQL standard, and TABLESPACE is useful functionality. Patch from Kris Jurka, minor editorialization by Neil Conway.
1 parent 8c5dfba commit 85c0eac

File tree

17 files changed

+322
-60
lines changed

17 files changed

+322
-60
lines changed

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.99 2006/01/16 20:48:49 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.100 2006/02/19 00:04:26 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -580,9 +580,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
580580
<term><literal>DELETE ROWS</literal></term>
581581
<listitem>
582582
<para>
583-
All rows in the temporary table will be deleted at the
584-
end of each transaction block. Essentially, an automatic
585-
<xref linkend="sql-truncate"> is done at each commit.
583+
All rows in the temporary table will be deleted at the end
584+
of each transaction block. Essentially, an automatic <xref
585+
linkend="sql-truncate" endterm="sql-truncate-title"> is done
586+
at each commit.
586587
</para>
587588
</listitem>
588589
</varlistentry>

‎doc/src/sgml/ref/create_table_as.sgml

Lines changed: 86 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.31 2005/11/01 21:09:50 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.32 2006/02/19 00:04:26 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -21,7 +21,10 @@ PostgreSQL documentation
2121
<refsynopsisdiv>
2222
<synopsis>
2323
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name</replaceable>
24-
[ (<replaceable>column_name</replaceable> [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
24+
[ (<replaceable>column_name</replaceable> [, ...] ) ]
25+
[ WITH OIDS | WITHOUT OIDS ]
26+
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
27+
[ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
2528
AS <replaceable>query</replaceable>
2629
</synopsis>
2730
</refsynopsisdiv>
@@ -113,6 +116,65 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
113116
</listitem>
114117
</varlistentry>
115118

119+
<varlistentry>
120+
<term><literal>ON COMMIT</literal></term>
121+
<listitem>
122+
<para>
123+
The behavior of temporary tables at the end of a transaction
124+
block can be controlled using <literal>ON COMMIT</literal>.
125+
The three options are:
126+
127+
<variablelist>
128+
<varlistentry>
129+
<term><literal>PRESERVE ROWS</literal></term>
130+
<listitem>
131+
<para>
132+
No special action is taken at the ends of transactions.
133+
This is the default behavior.
134+
</para>
135+
</listitem>
136+
</varlistentry>
137+
138+
<varlistentry>
139+
<term><literal>DELETE ROWS</literal></term>
140+
<listitem>
141+
<para>
142+
All rows in the temporary table will be deleted at the end
143+
of each transaction block. Essentially, an automatic <xref
144+
linkend="sql-truncate" endterm="sql-truncate-title"> is done
145+
at each commit.
146+
</para>
147+
</listitem>
148+
</varlistentry>
149+
150+
<varlistentry>
151+
<term><literal>DROP</literal></term>
152+
<listitem>
153+
<para>
154+
The temporary table will be dropped at the end of the current
155+
transaction block.
156+
</para>
157+
</listitem>
158+
</varlistentry>
159+
</variablelist>
160+
</para>
161+
</listitem>
162+
</varlistentry>
163+
164+
<varlistentry>
165+
<term><literal>TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
166+
<listitem>
167+
<para>
168+
The <replaceable class="PARAMETER">tablespace</replaceable> is the name
169+
of the tablespace in which the new table is to be created.
170+
If not specified,
171+
<xref linkend="guc-default-tablespace"> is used, or the database's
172+
default tablespace if <varname>default_tablespace</> is an empty
173+
string.
174+
</para>
175+
</listitem>
176+
</varlistentry>
177+
116178
<varlistentry>
117179
<term><replaceable>query</replaceable></term>
118180
<listitem>
@@ -168,6 +230,20 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
168230
<programlisting>
169231
CREATE TABLE films_recent AS
170232
SELECT * FROM films WHERE date_prod &gt;= '2002-01-01';
233+
</programlisting>
234+
</para>
235+
236+
<para>
237+
Create a new temporary table that will be dropped at commit
238+
<literal>films_recent</literal> with oids consisting of only
239+
recent entries from the table <literal>films</literal> using a
240+
prepared statement:
241+
242+
<programlisting>
243+
PREPARE recentfilms(date) AS
244+
SELECT * FROM films WHERE date_prod &gt; $1;
245+
CREATE TEMP TABLE films_recent WITH OIDS ON COMMIT DROP AS
246+
EXECUTE recentfilms('2002-01-01');
171247
</programlisting>
172248
</para>
173249
</refsect1>
@@ -188,13 +264,6 @@ CREATE TABLE films_recent AS
188264
</para>
189265
</listitem>
190266

191-
<listitem>
192-
<para>
193-
The standard defines an <literal>ON COMMIT</literal> clause;
194-
this is not currently implemented by <productname>PostgreSQL</>.
195-
</para>
196-
</listitem>
197-
198267
<listitem>
199268
<para>
200269
The standard defines a <literal>WITH [ NO ] DATA</literal> clause;
@@ -219,6 +288,14 @@ CREATE TABLE films_recent AS
219288
for details.
220289
</para>
221290
</listitem>
291+
292+
<listitem>
293+
<para>
294+
The <productname>PostgreSQL</productname> concept of tablespaces is not
295+
part of the standard. Hence, the clause <literal>TABLESPACE</literal>
296+
is an extension.
297+
</para>
298+
</listitem>
222299
</itemizedlist>
223300
</para>
224301
</refsect1>

‎src/backend/commands/prepare.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Copyright (c) 2002-2005, PostgreSQL Global Development Group
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.47 2006/01/18 06:49:26 neilc Exp $
13+
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.48 2006/02/19 00:04:26 neilc Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -196,6 +196,10 @@ ExecuteQuery(ExecuteStmt *stmt, ParamListInfo params,
196196
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
197197
errmsg("prepared statement is not a SELECT")));
198198
query->into = copyObject(stmt->into);
199+
query->intoHasOids = stmt->into_has_oids;
200+
query->intoOnCommit = stmt->into_on_commit;
201+
if (stmt->into_tbl_space)
202+
query->intoTableSpaceName = pstrdup(stmt->into_tbl_space);
199203

200204
MemoryContextSwitchTo(oldContext);
201205
}

‎src/backend/executor/execMain.c

Lines changed: 44 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,7 @@
2626
*
2727
*
2828
* IDENTIFICATION
29-
* $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.265 2006/01/12 21:48:53 tgl Exp $
29+
* $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.266 2006/02/19 00:04:26 neilc Exp $
3030
*
3131
*-------------------------------------------------------------------------
3232
*/
@@ -37,6 +37,7 @@
3737
#include "catalog/heap.h"
3838
#include "catalog/namespace.h"
3939
#include "commands/tablecmds.h"
40+
#include "commands/tablespace.h"
4041
#include "commands/trigger.h"
4142
#include "executor/execdebug.h"
4243
#include "executor/execdefs.h"
@@ -730,10 +731,19 @@ InitPlan(QueryDesc *queryDesc, bool explainOnly)
730731
{
731732
char *intoName;
732733
Oid namespaceId;
734+
Oid tablespaceId;
733735
AclResult aclresult;
734736
Oid intoRelationId;
735737
TupleDesc tupdesc;
736738

739+
/*
740+
* Check consistency of arguments
741+
*/
742+
if (parseTree->intoOnCommit != ONCOMMIT_NOOP && !parseTree->into->istemp)
743+
ereport(ERROR,
744+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
745+
errmsg("ON COMMIT can only be used on temporary tables")));
746+
737747
/*
738748
* find namespace to create in, check permissions
739749
*/
@@ -746,22 +756,53 @@ InitPlan(QueryDesc *queryDesc, bool explainOnly)
746756
aclcheck_error(aclresult, ACL_KIND_NAMESPACE,
747757
get_namespace_name(namespaceId));
748758

759+
/*
760+
* Select tablespace to use. If not specified, use default_tablespace
761+
* (which may in turn default to database's default).
762+
*/
763+
if (parseTree->intoTableSpaceName)
764+
{
765+
tablespaceId = get_tablespace_oid(parseTree->intoTableSpaceName);
766+
if (!OidIsValid(tablespaceId))
767+
ereport(ERROR,
768+
(errcode(ERRCODE_UNDEFINED_OBJECT),
769+
errmsg("tablespace \"%s\" does not exist",
770+
parseTree->intoTableSpaceName)));
771+
} else
772+
{
773+
tablespaceId = GetDefaultTablespace();
774+
/* note InvalidOid is OK in this case */
775+
}
776+
777+
/* Check permissions except when using the database's default */
778+
if (OidIsValid(tablespaceId))
779+
{
780+
AclResult aclresult;
781+
782+
aclresult = pg_tablespace_aclcheck(tablespaceId, GetUserId(),
783+
ACL_CREATE);
784+
785+
if (aclresult != ACLCHECK_OK)
786+
aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
787+
get_tablespace_name(tablespaceId));
788+
}
789+
749790
/*
750791
* have to copy tupType to get rid of constraints
751792
*/
752793
tupdesc = CreateTupleDescCopy(tupType);
753794

754795
intoRelationId = heap_create_with_catalog(intoName,
755796
namespaceId,
756-
InvalidOid,
797+
tablespaceId,
757798
InvalidOid,
758799
GetUserId(),
759800
tupdesc,
760801
RELKIND_RELATION,
761802
false,
762803
true,
763804
0,
764-
ONCOMMIT_NOOP,
805+
parseTree->intoOnCommit,
765806
allowSystemTableMods);
766807

767808
FreeTupleDesc(tupdesc);

‎src/backend/nodes/copyfuncs.c

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.326 2006/02/04 19:06:46 adunstan Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.327 2006/02/19 00:04:26 neilc Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1662,6 +1662,8 @@ _copyQuery(Query *from)
16621662
COPY_SCALAR_FIELD(resultRelation);
16631663
COPY_NODE_FIELD(into);
16641664
COPY_SCALAR_FIELD(intoHasOids);
1665+
COPY_SCALAR_FIELD(intoOnCommit);
1666+
COPY_STRING_FIELD(intoTableSpaceName);
16651667
COPY_SCALAR_FIELD(hasAggs);
16661668
COPY_SCALAR_FIELD(hasSubLinks);
16671669
COPY_NODE_FIELD(rtable);
@@ -1729,6 +1731,8 @@ _copySelectStmt(SelectStmt *from)
17291731
COPY_NODE_FIELD(into);
17301732
COPY_NODE_FIELD(intoColNames);
17311733
COPY_SCALAR_FIELD(intoHasOids);
1734+
COPY_SCALAR_FIELD(intoOnCommit);
1735+
COPY_STRING_FIELD(intoTableSpaceName);
17321736
COPY_NODE_FIELD(targetList);
17331737
COPY_NODE_FIELD(fromClause);
17341738
COPY_NODE_FIELD(whereClause);
@@ -2631,6 +2635,10 @@ _copyExecuteStmt(ExecuteStmt *from)
26312635

26322636
COPY_STRING_FIELD(name);
26332637
COPY_NODE_FIELD(into);
2638+
COPY_SCALAR_FIELD(into_contains_oids);
2639+
COPY_SCALAR_FIELD(into_has_oids);
2640+
COPY_SCALAR_FIELD(into_on_commit);
2641+
COPY_STRING_FIELD(into_tbl_space);
26342642
COPY_NODE_FIELD(params);
26352643

26362644
return newnode;

‎src/backend/nodes/equalfuncs.c

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
2020
* IDENTIFICATION
21-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.262 2006/02/04 19:06:46 adunstan Exp $
21+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.263 2006/02/19 00:04:26 neilc Exp $
2222
*
2323
*-------------------------------------------------------------------------
2424
*/
@@ -673,6 +673,8 @@ _equalQuery(Query *a, Query *b)
673673
COMPARE_SCALAR_FIELD(resultRelation);
674674
COMPARE_NODE_FIELD(into);
675675
COMPARE_SCALAR_FIELD(intoHasOids);
676+
COMPARE_SCALAR_FIELD(intoOnCommit);
677+
COMPARE_STRING_FIELD(intoTableSpaceName);
676678
COMPARE_SCALAR_FIELD(hasAggs);
677679
COMPARE_SCALAR_FIELD(hasSubLinks);
678680
COMPARE_NODE_FIELD(rtable);
@@ -732,6 +734,8 @@ _equalSelectStmt(SelectStmt *a, SelectStmt *b)
732734
COMPARE_NODE_FIELD(into);
733735
COMPARE_NODE_FIELD(intoColNames);
734736
COMPARE_SCALAR_FIELD(intoHasOids);
737+
COMPARE_SCALAR_FIELD(intoOnCommit);
738+
COMPARE_STRING_FIELD(intoTableSpaceName);
735739
COMPARE_NODE_FIELD(targetList);
736740
COMPARE_NODE_FIELD(fromClause);
737741
COMPARE_NODE_FIELD(whereClause);
@@ -1493,6 +1497,10 @@ _equalExecuteStmt(ExecuteStmt *a, ExecuteStmt *b)
14931497
{
14941498
COMPARE_STRING_FIELD(name);
14951499
COMPARE_NODE_FIELD(into);
1500+
COMPARE_SCALAR_FIELD(into_contains_oids);
1501+
COMPARE_SCALAR_FIELD(into_has_oids);
1502+
COMPARE_SCALAR_FIELD(into_on_commit);
1503+
COMPARE_STRING_FIELD(into_tbl_space);
14961504
COMPARE_NODE_FIELD(params);
14971505

14981506
return true;

‎src/backend/nodes/outfuncs.c

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.267 2006/01/31 21:39:23 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.268 2006/02/19 00:04:26 neilc Exp $
1212
*
1313
* NOTES
1414
* Every node type that can appear in stored rules' parsetrees *must*
@@ -1374,6 +1374,8 @@ _outSelectStmt(StringInfo str, SelectStmt *node)
13741374
WRITE_NODE_FIELD(into);
13751375
WRITE_NODE_FIELD(intoColNames);
13761376
WRITE_ENUM_FIELD(intoHasOids, ContainsOids);
1377+
WRITE_ENUM_FIELD(intoOnCommit, OnCommitAction);
1378+
WRITE_STRING_FIELD(intoTableSpaceName);
13771379
WRITE_NODE_FIELD(targetList);
13781380
WRITE_NODE_FIELD(fromClause);
13791381
WRITE_NODE_FIELD(whereClause);
@@ -1504,6 +1506,9 @@ _outQuery(StringInfo str, Query *node)
15041506

15051507
WRITE_INT_FIELD(resultRelation);
15061508
WRITE_NODE_FIELD(into);
1509+
WRITE_BOOL_FIELD(intoHasOids);
1510+
WRITE_ENUM_FIELD(intoOnCommit, OnCommitAction);
1511+
WRITE_STRING_FIELD(intoTableSpaceName);
15071512
WRITE_BOOL_FIELD(hasAggs);
15081513
WRITE_BOOL_FIELD(hasSubLinks);
15091514
WRITE_NODE_FIELD(rtable);

‎src/backend/nodes/readfuncs.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.183 2005/12/28 01:29:59 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.184 2006/02/19 00:04:26 neilc Exp $
1212
*
1313
* NOTES
1414
* Path and Plan nodes do not have any readfuncs support, because we
@@ -140,6 +140,9 @@ _readQuery(void)
140140
READ_NODE_FIELD(utilityStmt);
141141
READ_INT_FIELD(resultRelation);
142142
READ_NODE_FIELD(into);
143+
READ_BOOL_FIELD(intoHasOids);
144+
READ_ENUM_FIELD(intoOnCommit, OnCommitAction);
145+
READ_STRING_FIELD(intoTableSpaceName);
143146
READ_BOOL_FIELD(hasAggs);
144147
READ_BOOL_FIELD(hasSubLinks);
145148
READ_NODE_FIELD(rtable);

0 commit comments

Comments
 (0)