Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
On 1/20/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
Patch withdrawn by author; corrected version expected.
new version of Albert's patch with PGC_USERSET instead of PGC_SUSET
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
Index: doc/src/sgml/config.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.104
diff -c -r1.104 config.sgml
*** doc/src/sgml/config.sgml20 Jan 2007 21:30:26 - 1.104
--- doc/src/sgml/config.sgml21 Jan 2007 07:23:13 -
***
*** 3398,3403
--- 3398,3432
+
+ temp_tablespaces (string)
+
+temp_tablespaces configuration parameter
+
+ tablespacetemp
+
+
+ This variable specifies tablespaces in which to create temp
+ objects (temp tables and indexes on temp tables) when a
+ CREATE command does not explicitly specify a
tablespace
+ and temp files when necessary (eg. for sorting operations).
+
+
+
+ The value is either a list of names of tablespaces, or an empty
+ string to specify using the default tablespace of the current
database.
+ If the value does not match the name of any existing tablespace,
+ PostgreSQL will automatically use the default
+ tablespace of the current database.
+
+
+
+ For more information on tablespaces,
+ see .
+
+
+
+
check_function_bodies
(boolean)
Index: src/backend/commands/indexcmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/indexcmds.c,v
retrieving revision 1.153
diff -c -r1.153 indexcmds.c
*** src/backend/commands/indexcmds.c20 Jan 2007 23:13:01 - 1.153
--- src/backend/commands/indexcmds.c21 Jan 2007 07:23:17 -
***
*** 209,215
}
else
{
! tablespaceId = GetDefaultTablespace();
/* note InvalidOid is OK in this case */
}
--- 209,221
}
else
{
! /*
!* if the target table is temporary then use a temp_tablespace
!*/
! if (!rel->rd_istemp)
! tablespaceId = GetDefaultTablespace();
! else
! tablespaceId = GetTempTablespace();
/* note InvalidOid is OK in this case */
}
Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.210
diff -c -r1.210 tablecmds.c
*** src/backend/commands/tablecmds.c5 Jan 2007 22:19:26 - 1.210
--- src/backend/commands/tablecmds.c21 Jan 2007 07:23:29 -
***
*** 334,339
--- 334,343
errmsg("tablespace \"%s\" does not
exist",
stmt->tablespacename)));
}
+ else if (stmt->relation->istemp)
+ {
+ tablespaceId = GetTempTablespace();
+ }
else
{
tablespaceId = GetDefaultTablespace();
Index: src/backend/commands/tablespace.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablespace.c,v
retrieving revision 1.40
diff -c -r1.40 tablespace.c
*** src/backend/commands/tablespace.c 5 Jan 2007 22:19:26 - 1.40
--- src/backend/commands/tablespace.c 21 Jan 2007 07:23:31 -
***
*** 65,73
#include "utils/lsyscache.h"
! /* GUC variable */
char *default_tablespace = NULL;
static bool remove_tablespace_directories(Oid tablespaceoid, bool redo);
static void set_short_version(const char *path);
--- 65,76
#include "utils/lsyscache.h"
! /* GUC variables */
char *default_tablespace = NULL;
+ char *temp_tablespaces = NULL;
+ int next_temp_tablespace;
+ int num_temp_tablespaces;
static bool remove_tablespace_directories(Oid tablespaceoid, bool redo);
static void set_short_version(const char *path);
***
*** 930,935
--- 933,1074
return result;
}
+ /*
+ * Routines for handling the GUC variable 'temp_tablespaces'.
+ */
+
+ /* assign_hook: validate new temp_tablespaces, do extra actions as needed */
+ const char *
+ assign_temp_tablespaces(const char *newval, bool doit, GucSource source)
+ {
+ char *rawname;
+
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and
A Dimecres 17 Gener 2007 22:38, Robert Treat va escriure: > Why is this PGC_SUSET? ISTM it should be PGC_USERSET. Yes, I think your're right and it should be PGC_USERSET. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and
On Sunday 14 January 2007 23:16, Jaime Casanova wrote:
> On 1/13/07, Albert Cervera Areny <[EMAIL PROTECTED]> wrote:
> > It was already possible to set the guc on postgresql.conf when I posted
> > the patch...
>
> ok... fixed... the problem was that this code only let
> num_temp_tablespaces be greater than zero when we are in an
> interactive command (eg. a SET command) but setting the guc from
> postgresql.conf at startup time is not interactive so
> num_temp_tablespaces is zero and when i try to get the first temp
> tablespace to use (MyProcPid % num_temp_tablespaces) causes a floatin
> exception (division by zero).
> new patch added, with that piece of code refactored to let
> num_temp_tablespaces get a value greater than zero always that the guc
> is setted, i also add some docs.
>
> the patch passes all 104 regression tests and all my tests as well...
>
> i think the patch is ready to be applied to HEAD, any committer want
> to review it?
Why is this PGC_SUSET? ISTM it should be PGC_USERSET.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
On 1/13/07, Albert Cervera Areny <[EMAIL PROTECTED]> wrote:
It was already possible to set the guc on postgresql.conf when I posted the
patch...
ok... fixed... the problem was that this code only let
num_temp_tablespaces be greater than zero when we are in an
interactive command (eg. a SET command) but setting the guc from
postgresql.conf at startup time is not interactive so
num_temp_tablespaces is zero and when i try to get the first temp
tablespace to use (MyProcPid % num_temp_tablespaces) causes a floatin
exception (division by zero).
+ if (source >= PGC_S_INTERACTIVE && IsTransactionState())
+ {
+ /*
+* Verify that all the names are valid tablspace names
+* We do not check for USAGE rights should we?
+*/
+ foreach(l, namelist)
+ {
+ char *curname = (char *) lfirst(l);
+
+ if (get_tablespace_oid(curname) == InvalidOid)
+ ereport((source == PGC_S_TEST) ? NOTICE : ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("tablespace \"%s\" does not exist", curname)));
+
+ num_temp_tablespaces++;
+ }
+ }
new patch added, with that piece of code refactored to let
num_temp_tablespaces get a value greater than zero always that the guc
is setted, i also add some docs.
the patch passes all 104 regression tests and all my tests as well...
i think the patch is ready to be applied to HEAD, any committer want
to review it?
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
Index: doc/src/sgml/config.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.101
diff -c -B -b -r1.101 config.sgml
*** doc/src/sgml/config.sgml9 Jan 2007 22:16:46 - 1.101
--- doc/src/sgml/config.sgml15 Jan 2007 04:02:13 -
***
*** 3398,3403
--- 3398,3432
+
+ temp_tablespaces (string)
+
+temp_tablespaces configuration parameter
+
+ tablespacetemp
+
+
+ This variable specifies tablespaces in which to create temp
+ objects (temp tables and indexes on temp tables) when a
+ CREATE command does not explicitly specify a
tablespace
+ and temp files when necessary (eg. for sorting operations).
+
+
+
+ The value is either a list of names of tablespaces, or an empty
+ string to specify using the default tablespace of the current
database.
+ If the value does not match the name of any existing tablespace,
+ PostgreSQL will automatically use the default
+ tablespace of the current database.
+
+
+
+ For more information on tablespaces,
+ see .
+
+
+
+
check_function_bodies
(boolean)
Index: src/backend/commands/indexcmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/indexcmds.c,v
retrieving revision 1.152
diff -c -B -b -r1.152 indexcmds.c
*** src/backend/commands/indexcmds.c9 Jan 2007 02:14:11 - 1.152
--- src/backend/commands/indexcmds.c15 Jan 2007 04:02:17 -
***
*** 209,215
--- 209,221
}
else
{
+ /*
+* if the target table is temporary then use a temp_tablespace
+*/
+ if (!rel->rd_istemp)
tablespaceId = GetDefaultTablespace();
+ else
+ tablespaceId = GetTempTablespace();
/* note InvalidOid is OK in this case */
}
Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.210
diff -c -B -b -r1.210 tablecmds.c
*** src/backend/commands/tablecmds.c5 Jan 2007 22:19:26 - 1.210
--- src/backend/commands/tablecmds.c15 Jan 2007 04:02:28 -
***
*** 334,339
--- 334,343
errmsg("tablespace \"%s\" does not
exist",
stmt->tablespacename)));
}
+ else if (stmt->relation->istemp)
+ {
+ tablespaceId = GetTempTablespace();
+ }
else
{
tablespaceId = GetDefaultTablespace();
Index: src/backend/commands/tablespace.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablespace.c,v
retriev
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
It was already possible to set the guc on postgresql.conf when I posted the patch... A Divendres 12 Gener 2007 07:28, Jaime Casanova va escriure: > On 1/11/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > > On Thu, 2007-01-11 at 21:05 -0500, Jaime Casanova wrote: > > > On 1/11/07, Albert Cervera Areny <[EMAIL PROTECTED]> wrote: > > > > Please, go on with that, I hadn't seen that problem. Indeed, I read > > > > Andrew answer to your question and I think it's a nice solution. > > > > > > yes... i'm always trying to kill flies with tanks... ;) > > > > Isn't that a little expensive on gas? > > maybe... but... what a beatiful explosion we can get... ;) > > seriously, attached's a new version of the patch... > the patch use temp tablespaces for: > - temp tables > - temp files (generated by sorts and such) > - indexes on temp tables > > the temp_tablespaces GUC still cannot be set from postgresql.conf, i > will keep working on that but i have to understand the code... > > any comments on this patch? although it's not ready for apply it yet, > i think Albert made a good work on it... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
Sorry, patch attached this time...
On 1/12/07, Jaime Casanova <[EMAIL PROTECTED]> wrote:
On 1/11/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
> On Thu, 2007-01-11 at 21:05 -0500, Jaime Casanova wrote:
> > On 1/11/07, Albert Cervera Areny <[EMAIL PROTECTED]> wrote:
> > > Please, go on with that, I hadn't seen that problem. Indeed, I read Andrew
> > > answer to your question and I think it's a nice solution.
> > >
> >
> > yes... i'm always trying to kill flies with tanks... ;)
>
> Isn't that a little expensive on gas?
>
maybe... but... what a beatiful explosion we can get... ;)
seriously, attached's a new version of the patch...
the patch use temp tablespaces for:
- temp tables
- temp files (generated by sorts and such)
- indexes on temp tables
the temp_tablespaces GUC still cannot be set from postgresql.conf, i
will keep working on that but i have to understand the code...
any comments on this patch? although it's not ready for apply it yet,
i think Albert made a good work on it...
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
--
Atentamente,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
Index: src/backend/commands/indexcmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/indexcmds.c,v
retrieving revision 1.152
diff -c -r1.152 indexcmds.c
*** src/backend/commands/indexcmds.c9 Jan 2007 02:14:11 - 1.152
--- src/backend/commands/indexcmds.c12 Jan 2007 05:08:35 -
***
*** 209,215
}
else
{
! tablespaceId = GetDefaultTablespace();
/* note InvalidOid is OK in this case */
}
--- 209,221
}
else
{
! /*
!* if the target table is temporary then use a temp_tablespace
!*/
! if (!rel->rd_istemp)
! tablespaceId = GetDefaultTablespace();
! else
! tablespaceId = GetTempTablespace();
/* note InvalidOid is OK in this case */
}
Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.210
diff -c -r1.210 tablecmds.c
*** src/backend/commands/tablecmds.c5 Jan 2007 22:19:26 - 1.210
--- src/backend/commands/tablecmds.c12 Jan 2007 05:08:47 -
***
*** 334,339
--- 334,343
errmsg("tablespace \"%s\" does not
exist",
stmt->tablespacename)));
}
+ else if (stmt->relation->istemp)
+ {
+ tablespaceId = GetTempTablespace();
+ }
else
{
tablespaceId = GetDefaultTablespace();
Index: src/backend/commands/tablespace.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablespace.c,v
retrieving revision 1.40
diff -c -r1.40 tablespace.c
*** src/backend/commands/tablespace.c 5 Jan 2007 22:19:26 - 1.40
--- src/backend/commands/tablespace.c 12 Jan 2007 05:08:49 -
***
*** 65,73
#include "utils/lsyscache.h"
! /* GUC variable */
char *default_tablespace = NULL;
static bool remove_tablespace_directories(Oid tablespaceoid, bool redo);
static void set_short_version(const char *path);
--- 65,76
#include "utils/lsyscache.h"
! /* GUC variables */
char *default_tablespace = NULL;
+ char *temp_tablespaces = NULL;
+ int next_temp_tablespace;
+ int num_temp_tablespaces;
static bool remove_tablespace_directories(Oid tablespaceoid, bool redo);
static void set_short_version(const char *path);
***
*** 930,935
--- 933,1069
return result;
}
+ /*
+ * Routines for handling the GUC variable 'temp_tablespaces'.
+ */
+
+ /* assign_hook: validate new temp_tablespaces, do extra actions as needed */
+ const char *
+ assign_temp_tablespaces(const char *newval, bool doit, GucSource source)
+ {
+ char *rawname;
+ List *namelist;
+ ListCell *l;
+
+ /* Need a modifiable copy of string */
+ rawname = pstrdup(newval);
+
+ /* Parse string into list of identifiers */
+ if (!SplitIdentifierString(rawname, ',', &namelist))
+ {
+ /
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
On 1/11/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: On Thu, 2007-01-11 at 21:05 -0500, Jaime Casanova wrote: > On 1/11/07, Albert Cervera Areny <[EMAIL PROTECTED]> wrote: > > Please, go on with that, I hadn't seen that problem. Indeed, I read Andrew > > answer to your question and I think it's a nice solution. > > > > yes... i'm always trying to kill flies with tanks... ;) Isn't that a little expensive on gas? maybe... but... what a beatiful explosion we can get... ;) seriously, attached's a new version of the patch... the patch use temp tablespaces for: - temp tables - temp files (generated by sorts and such) - indexes on temp tables the temp_tablespaces GUC still cannot be set from postgresql.conf, i will keep working on that but i have to understand the code... any comments on this patch? although it's not ready for apply it yet, i think Albert made a good work on it... -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and
On Thu, 2007-01-11 at 21:05 -0500, Jaime Casanova wrote: > On 1/11/07, Albert Cervera Areny <[EMAIL PROTECTED]> wrote: > > Please, go on with that, I hadn't seen that problem. Indeed, I read Andrew > > answer to your question and I think it's a nice solution. > > > > yes... i'm always trying to kill flies with tanks... ;) Isn't that a little expensive on gas? > i will use Andrew's suggestion... > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
On 1/11/07, Albert Cervera Areny <[EMAIL PROTECTED]> wrote: Please, go on with that, I hadn't seen that problem. Indeed, I read Andrew answer to your question and I think it's a nice solution. yes... i'm always trying to kill flies with tanks... ;) i will use Andrew's suggestion... -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [pgsql-patches] [PATCHES] Tablespace for temporary objects and sort files
Please, go on with that, I hadn't seen that problem. Indeed, I read Andrew answer to your question and I think it's a nice solution. A Dimecres 10 Gener 2007 05:33, Jaime Casanova va escriure: > On 1/9/07, Albert Cervera Areny <[EMAIL PROTECTED]> wrote: > > I don't have much time lately so if you're willing to work on it, please > > do. > > after doing the index part i revisited the patch again and saw that > there is something fundamentally wrong here (sorry for no noticing > that before :( ) > > your are using local backend variables for the iterator so two > different backends will use to different variables... because of that > if you have 3 temp tablespaces in your temp_tablespaces guc and start > 100 backend and every one of them create just one temp table those 100 > temp tables will be in the first temp tablespace... :( > > i will try to fix that as well... unless you want to do it, just tell me... ---(end of broadcast)--- TIP 6: explain analyze is your friend
