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,
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to