default tablespace for roles - Mailing list pgsql-patches

Hi!

It would be nice to have tablespaces for each users. This is a small
pathc that does the job.
- gramar file: "alter|create user ... with default tablespace 'tblspc'"
added;
- new column in pg_authid: roltblspc, an Oid referring to the pg_tablespace
- at alter/add role neccessary default tablepsace modifications+checking
if it exists
- pg_dumall outputs script that alters users with default tablespace
after dumping users and tablespaces

(for 8.2, if you like it)
Regards,
Laszlo



Index: src/include/catalog/pg_authid.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_authid.h,v
retrieving revision 1.3
diff -r1.3 pg_authid.h
51a52
 >     Oid        roltblspc;    /* default tablespace oid */
74c75
< #define Natts_pg_authid                    11
---
 > #define Natts_pg_authid                    12
82,85c83,87
< #define Anum_pg_authid_rolconnlimit        8
< #define Anum_pg_authid_rolpassword        9
< #define Anum_pg_authid_rolvaliduntil    10
< #define Anum_pg_authid_rolconfig        11
---
 > #define Anum_pg_authid_roltblspc        8
 > #define Anum_pg_authid_rolconnlimit        9
 > #define Anum_pg_authid_rolpassword        10
 > #define Anum_pg_authid_rolvaliduntil    11
 > #define Anum_pg_authid_rolconfig        12
94c96
< DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ _null_ ));
---
 > DATA(insert OID = 10 ( "POSTGRES" t t t t t t 1663 -1 _null_ _null_
_null_ ));
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.66
diff -r1.66 pg_dumpall.c
718a719,735
 >
 >     res = executeQuery(conn, "SELECT rolname, spcname from
pg_catalog.pg_authid inner join pg_catalog.pg_tablespace on
pg_tablespace.oid = pg_authid.roltblspc");
 >     if (PQntuples(res) > 0)
 >         printf("--\n-- User default tablespaces\n--\n\n");
 >
 >     for (i = 0; i < PQntuples(res); i++)
 >     {
 >         char    *rolname = PQgetvalue(res, i, 0);
 >         char    *spcname = PQgetvalue(res, i, 1);
 >
 >         printf("ALTER ROLE %s WITH DEFAULT TABLESPACE '%s';\n",
rolname, spcname);
 >
 >     }
 >
 >     PQclear(res);
 >     printf("\n\n");
 >
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.507
diff -r2.507 gram.y
690a691,694
 >             | DEFAULT TABLESPACE Sconst
 >                 {
 >                     $$ = makeDefElem("roltblspc", (Node
*)makeString($3));
 >                 }
Index: src/backend/commands/user.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/user.c,v
retrieving revision 1.160
diff -r1.160 user.c
93a94
 >     char       *tblspc = NULL;
104a106
 >     DefElem    *dtblspc = NULL;
123a126,133
 >         if(strcmp(defel->defname, "roltblspc") == 0){
 >             if(dtblspc)
 >                 ereport(ERROR,
 >                         (errcode(ERRCODE_SYNTAX_ERROR),
 >                         errmsg("conflicting or redundant options")));
 >             dtblspc = defel;
 >         } else
 >
227a238,239
 >     if (dtblspc)
 >         tblspc = strVal(dtblspc -> arg);
307a320,328
 >     if(tblspc) {
 >         Oid userTblSpc;
 >         userTblSpc = get_tablespace_oid(tblspc);
 >         if(!OidIsValid(userTblSpc))
 >             elog(ERROR, "Tablespace %s does not exist", tblspc);
 >         new_record[Anum_pg_authid_roltblspc -1] =
DatumGetObjectId(userTblSpc);
 >     } else {
 >         new_record_nulls[Anum_pg_authid_roltblspc -1] = 'n';
 >     }
419a441
 >     char       *tblspc = NULL;
429a452
 >     DefElem    *dtblspc = NULL;
435a459,466
 >         if(strcmp(defel->defname, "roltblspc") == 0){
 >             if(dtblspc)
 >                 ereport(ERROR,
 >                         (errcode(ERRCODE_SYNTAX_ERROR),
 >                         errmsg("conflicting or redundant options")));
 >             dtblspc = defel;
 >         } else
 >
538c569,571
<
---
 >     if(dtblspc) {
 >         tblspc = strVal(dtblspc -> arg);
 >     }
653a687,692
 >     if(tblspc)
 >     {
 >         new_record[Anum_pg_authid_roltblspc -1] =
get_tablespace_oid(tblspc);
 >         new_record_repl[Anum_pg_authid_roltblspc -1] = 'r';
 >     }
 >
Index: src/backend/commands/tablespace.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablespace.c,v
retrieving revision 1.26
diff -r1.26 tablespace.c
56a57
 > #include "catalog/pg_authid.h"
888a890,902
 >     Oid            userDefTblSpc;
 >     HeapTuple        authId;
 >     Oid            sessionUser;
 >
 >     /* Get the session users default tablespace */
 >     sessionUser = GetSessionUserId();
 >     authId = SearchSysCache(AUTHOID, ObjectIdGetDatum(sessionUser),
0, 0, 0);
 >     userDefTblSpc = ((Form_pg_authid) GETSTRUCT(authId))->roltblspc;
 >     ReleaseSysCache(authId);
 >
 >     /* if it was set, return it, otherwise the old procedure takes
place */
 >     if(OidIsValid(userDefTblSpc))
 >         return userDefTblSpc;