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