On Fri, 24 Mar 2006, Jim C. Nasby wrote:
On Wed, Mar 22, 2006 at 02:37:28PM -0500, Kris Jurka wrote:
On Wed, 22 Mar 2006, Jim C. Nasby wrote:
Ok, I saw disk activity on the base directory and assumed it was pg_xlog
stuff. Turns out that both SELECT INTO and CREATE TABLE AS ignore
default_tablepsace and create the new tables in the base directory. I'm
guessing that's a bug... (this is on 8.1.2, btw).
This has been fixed in CVS HEAD as part of a patch to allow additional
options to CREATE TABLE AS.
http://archives.postgresql.org/pgsql-patches/2006-02/msg00211.php
I'll argue that the current behavior is still a bug and should be fixed.
Would it be difficult to patch 8.1 (and 8.0 if there were tablespaces
then...) to honor default_tablespace?
Here are patches that fix this for 8.0 and 8.1.
Kris Jurka
Index: src/backend/executor/execMain.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/executor/execMain.c,v
retrieving revision 1.241.4.2
diff -c -r1.241.4.2 execMain.c
*** src/backend/executor/execMain.c 12 Jan 2006 21:49:17 -0000 1.241.4.2
--- src/backend/executor/execMain.c 24 Mar 2006 18:05:53 -0000
***************
*** 36,41 ****
--- 36,42 ----
#include "catalog/heap.h"
#include "catalog/namespace.h"
#include "commands/tablecmds.h"
+ #include "commands/tablespace.h"
#include "commands/trigger.h"
#include "executor/execdebug.h"
#include "executor/execdefs.h"
***************
*** 731,736 ****
--- 732,738 ----
{
char *intoName;
Oid namespaceId;
+ Oid tablespaceId;
AclResult aclresult;
Oid intoRelationId;
TupleDesc tupdesc;
***************
*** 747,752 ****
--- 749,764 ----
aclcheck_error(aclresult, ACL_KIND_NAMESPACE,
get_namespace_name(namespaceId));
+ tablespaceId = GetDefaultTablespace();
+ if (OidIsValid(tablespaceId)) {
+ aclresult = pg_tablespace_aclcheck(tablespaceId, GetUserId(),
+ ACL_CREATE);
+
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
+ get_tablespace_name(tablespaceId));
+ }
+
/*
* have to copy tupType to get rid of constraints
*/
***************
*** 754,760 ****
intoRelationId = heap_create_with_catalog(intoName,
namespaceId,
! InvalidOid,
tupdesc,
RELKIND_RELATION,
false,
--- 766,772 ----
intoRelationId = heap_create_with_catalog(intoName,
namespaceId,
! tablespaceId,
tupdesc,
RELKIND_RELATION,
false,
Index: src/backend/executor/execMain.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/executor/execMain.c,v
retrieving revision 1.256.2.5
diff -c -r1.256.2.5 execMain.c
*** src/backend/executor/execMain.c 12 Jan 2006 21:49:06 -0000 1.256.2.5
--- src/backend/executor/execMain.c 24 Mar 2006 17:57:11 -0000
***************
*** 37,42 ****
--- 37,43 ----
#include "catalog/heap.h"
#include "catalog/namespace.h"
#include "commands/tablecmds.h"
+ #include "commands/tablespace.h"
#include "commands/trigger.h"
#include "executor/execdebug.h"
#include "executor/execdefs.h"
***************
*** 737,742 ****
--- 738,744 ----
{
char *intoName;
Oid namespaceId;
+ Oid tablespaceId;
AclResult aclresult;
Oid intoRelationId;
TupleDesc tupdesc;
***************
*** 753,758 ****
--- 755,770 ----
aclcheck_error(aclresult, ACL_KIND_NAMESPACE,
get_namespace_name(namespaceId));
+ tablespaceId = GetDefaultTablespace();
+ if (OidIsValid(tablespaceId)) {
+ aclresult = pg_tablespace_aclcheck(tablespaceId, GetUserId(),
+ ACL_CREATE);
+
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
+ get_tablespace_name(tablespaceId));
+ }
+
/*
* have to copy tupType to get rid of constraints
*/
***************
*** 760,766 ****
intoRelationId = heap_create_with_catalog(intoName,
namespaceId,
! InvalidOid,
InvalidOid,
GetUserId(),
tupdesc,
--- 772,778 ----
intoRelationId = heap_create_with_catalog(intoName,
namespaceId,
! tablespaceId,
InvalidOid,
GetUserId(),
tupdesc,
