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
/listitem
/varlistentry
+ varlistentry id=guc-temp-tablespaces xreflabel=temp_tablespaces
+ termvarnametemp_tablespaces/varname (typestring/type)/term
+ indexterm
+primaryvarnametemp_tablespaces/ configuration parameter/primary
+ /indexterm
+ indextermprimarytablespace/secondarytemp//
+ listitem
+para
+ This variable specifies tablespaces in which to create temp
+ objects (temp tables and indexes on temp tables) when a
+ commandCREATE/ command does not explicitly specify a
tablespace
+ and temp files when necessary (eg. for sorting operations).
+/para
+
+para
+ 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,
+ productnamePostgreSQL/ will automatically use the default
+ tablespace of the current database.
+/para
+
+para
+ For more information on tablespaces,
+ see xref linkend=manage-ag-tablespaces.
+/para
+ /listitem
+ /varlistentry
+
varlistentry id=guc-check-function-bodies
xreflabel=check_function_bodies
termvarnamecheck_function_bodies/varname
(typeboolean/type)/term
indexterm
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,
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
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
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
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 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))
+ {
+ /* syntax error in name list */
+
Re: [PATCHES] Tablespace for temporary objects and sort files
I don't have much time lately so if you're willing to work on it, please do. A Dimarts 09 Gener 2007 02:51, Jaime Casanova va escriure: On 1/8/07, Jaime Casanova [EMAIL PROTECTED] wrote: maybe once this patch is applied you can think on make indexes and [temp] sequences on temp tables use the same temp_tablespace that table is using... actually, the index part is easy... do you want to do it? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Tablespace for temporary objects and sort files
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... -- 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 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: [PATCHES] Tablespace for temporary objects and sort files
On 1/8/07, Jaime Casanova [EMAIL PROTECTED] wrote: maybe once this patch is applied you can think on make indexes and [temp] sequences on temp tables use the same temp_tablespace that table is using... actually, the index part is easy... do you want to do 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Tablespace for temporary objects and sort files
Here's a new version that takes into account the SELECT INTO TEMP case. Thanks
Jaime!
What other temporary objects do you think should be considered?
Any other comments on the patch?
A Dijous 04 Gener 2007 05:33, Jaime Casanova va escriure:
On 12/27/06, Albert Cervera Areny [EMAIL PROTECTED] wrote:
Hi,
here's a new version of the patch against HEAD with both, table
and sort files working correctly for me. Regression tests work too.
I'd like to ask again the question I made on the first post as no
answer was given at that time:
The GetTempTablespace function correctly returns a different tablespace
each time is called, but I store the position of the last tablespace used
with an integer and iterate through the list of tablespaces each time. I
tried to keep the iterator from call to call but I got a segfault, I
imagine due to the memory context. Should I try to keep the iterator? How
can I do it?
Now I'm working on some regression tests that could be added to
tablespace.source using something like:
SET temp_tablespaces='testspace';
CREATE TEMP TABLE temp_foo(a VARCHAR);
SELECT COUNT(pg_ls_dir('pg_tblspc/' || (SELECT oid FROM
pg_catalog.pg_tablespace WHERE spcname='testspace' )
seems is working fine... i actually looked for the files in the
tablespace directory...
have you looked my past comments?
Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.209
diff -c -r1.209 tablecmds.c
*** src/backend/commands/tablecmds.c 3 Jan 2007 18:11:01 - 1.209
--- src/backend/commands/tablecmds.c 6 Jan 2007 00:01:44 -
***
*** 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.39
diff -c -r1.39 tablespace.c
*** src/backend/commands/tablespace.c 4 Oct 2006 00:29:51 - 1.39
--- src/backend/commands/tablespace.c 6 Jan 2007 00:01:45 -
***
*** 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,1068
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))
+ {
+ /* syntax error in name list */
+ pfree(rawname);
+ list_free(namelist);
+ return NULL;
+ }
+
+ num_temp_tablespaces = 0;
+ /*
+ * If we aren't inside a transaction, we cannot do database access so
+ * cannot verify the individual names. Must accept the list on faith.
+ */
+ 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++;
+ }
+ }
+
+ pfree(rawname);
+ list_free(namelist);
+ next_temp_tablespace = 0;
+ return newval;
+ }
+
+ /*
+ * GetTempTablespace -- get the OID of the tablespace for temporary objects
+ *
+ * May return InvalidOid to indicate use the database's default tablespace
+ *
+ * This exists to hide the temp_tablespace GUC variable.
+ */
+ Oid
+ GetTempTablespace(void)
+ {
+ Oid result;
+ char *curname = NULL;
+ char *rawname;
+ List *namelist;
+ ListCell *l;
+ int i = 0;
+
+ if ( temp_tablespaces == NULL )
+ return InvalidOid;
+
+ /* Need a modifiable version of temp_tablespaces */
+ rawname =
Re: [PATCHES] Tablespace for temporary objects and sort files
On 12/27/06, Albert Cervera Areny [EMAIL PROTECTED] wrote:
Hi,
here's a new version of the patch against HEAD with both, table and sort
files working correctly for me. Regression tests work too.
I'd like to ask again the question I made on the first post as no answer
was
given at that time:
The GetTempTablespace function correctly returns a different tablespace
each time is called, but I store the position of the last tablespace used
with an integer and iterate through the list of tablespaces each time. I
tried to keep the iterator from call to call but I got a segfault, I
imagine due to the memory context. Should I try to keep the iterator? How
can I do it?
Now I'm working on some regression tests that could be added to
tablespace.source using something like:
SET temp_tablespaces='testspace';
CREATE TEMP TABLE temp_foo(a VARCHAR);
SELECT COUNT(pg_ls_dir('pg_tblspc/' || (SELECT oid FROM
pg_catalog.pg_tablespace WHERE spcname='testspace' )
seems is working fine... i actually looked for the files in the
tablespace directory...
have you looked my past comments?
--
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 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] Tablespace for temporary objects and sort files
On 12/27/06, Albert Cervera Areny [EMAIL PROTECTED] wrote: Hi, here's a new version of the patch against HEAD with both, table and sort files working correctly for me. Regression tests work too. ok, i will test it a little ... what about temp tables created by SELECT INTO TEMP? look at src/backend/executor/execMain.c:OpenIntoRel() what about other temporary objects? I'd like to ask again the question I made on the first post as no answer was given at that time: The GetTempTablespace function correctly returns a different tablespace each time is called, but I store the position of the last tablespace used with an integer and iterate through the list of tablespaces each time. I tried to keep the iterator from call to call but I got a segfault, I imagine due to the memory context. Should I try to keep the iterator? How can I do it? i didn't read this last time, i will take a look at it now... when you post for the first time hackers where busy releasing 8.2.0, maybe they will pay more atention now :) -- 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: [PATCHES] Tablespace for temporary objects and sort files
On 12/24/06, Albert Cervera Areny [EMAIL PROTECTED] wrote: Hi, yes I'm working on it. I've got a more recent version that doesn't crash on initdb and works with sort files. I've got a couple of things pending and will post it as soon as I can. If you want I could make a diff and send it to you in case you wanted to test/improve the patch. Refering to some of the comment I never answered (sorry about that) I prefer to use GetTempTablespace() as I think it's easier to read/understand than GetDefaultTablespace(true). And thanks for pointing to temporary indexes. There seems not to be temporary indexes but indexes of temporary tables, which could use GetTempTablespace() too... yeah, it was late and i was almost asleep... i was thinking in temporary sequences, but indexes on temporary tables it's not a bad idea too... that was the reason i think it's better to use the same GetDefaultTablespace() function it's less intrussive and is not directed to one particular object but all temp objects can benefit... i will wait your patch when you think is ready for discussion... -- 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 6: explain analyze is your friend
Re: [PATCHES] Tablespace for temporary objects and sort files
On 10/24/06, Albert Cervera Areny [EMAIL PROTECTED] wrote: Hi, I'm trying to introduce myself into postgresql development and I'm working on the tablespace for temporary objects and sort files TODO item. The attached patch shows what I've already done. The GUC is currently called temp_tablespaces. hi albert. are you working on this? i'm willing to help... if you are not working on this, i will make a try... comments on the original patch? AFAIR, it fails the regrress tests when executing initdb... more here http://archives.postgresql.org/pgsql-patches/2006-10/msg00141.php -- 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 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Tablespace for temporary objects and sort files
Sorry about the wrong diff format. Attached is the good one.
A Dimecres 25 Octubre 2006 09:07, Neil Conway va escriure:
On Wed, 2006-10-25 at 00:45 +0200, Albert Cervera Areny wrote:
Hope the diff and idents are ok.
Patches should be submitted in context diff (diff -c) format.
-Neil
Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.206
diff -c -r1.206 tablecmds.c
*** src/backend/commands/tablecmds.c 13 Oct 2006 21:43:18 - 1.206
--- src/backend/commands/tablecmds.c 25 Oct 2006 20:06:18 -
***
*** 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.39
diff -c -r1.39 tablespace.c
*** src/backend/commands/tablespace.c 4 Oct 2006 00:29:51 - 1.39
--- src/backend/commands/tablespace.c 25 Oct 2006 20:06:19 -
***
*** 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,1059
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))
+ {
+ /* syntax error in name list */
+ pfree(rawname);
+ list_free(namelist);
+ return NULL;
+ }
+
+ num_temp_tablespaces = 0;
+ /*
+ * If we aren't inside a transaction, we cannot do database access so
+ * cannot verify the individual names. Must accept the list on faith.
+ */
+ 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++;
+ }
+ }
+
+ pfree(rawname);
+ list_free(namelist);
+ next_temp_tablespace = 0;
+ return newval;
+ }
+
+ /*
+ * GetTempTablespace -- get the OID of the tablespace for temporary objects
+ *
+ * May return InvalidOid to indicate use the database's default tablespace
+ *
+ * This exists to hide the temp_tablespace GUC variable.
+ */
+ Oid
+ GetTempTablespace(void)
+ {
+ Oid result;
+ char *curname = NULL;
+ char *rawname;
+ List *namelist;
+ ListCell *l;
+ int i = 0;
+
+ rawname = pstrdup(temp_tablespaces);
+
+ /* Parse string into list of identifiers */
+ if (!SplitIdentifierString(rawname, ',', namelist))
+ {
+ /* syntax error in name list */
+ pfree(rawname);
+ list_free(namelist);
+ return InvalidOid;
+ }
+
+ foreach(l, namelist)
+ {
+ curname = (char *) lfirst(l);
+ if ( i == next_temp_tablespace )
+ break;
+ i++;
+ }
+
+ pfree(rawname);
+
+ /* Prepare for the next time the function is called */
+ next_temp_tablespace++;
+ if (next_temp_tablespace == num_temp_tablespaces)
+ next_temp_tablespace = 0;
+
+ /* Fast path for temp_tablespaces == */
+ if ( curname == NULL || curname[0] == '\0') {
+ list_free(namelist);
+ return InvalidOid;
+ }
+
+ /*
+ * It is tempting to cache this lookup for more speed, but then we would
+ * fail to detect the case where the tablespace was dropped since the GUC
+ * variable was set. Note also that we don't complain if the value fails
+ * to refer to an existing tablespace; we just silently return InvalidOid,
+ * causing the new object to be created in the database's tablespace.
+ */
+ result = get_tablespace_oid(curname);
+
+ /*
+ * Allow explicit specification of database's
Re: [PATCHES] Tablespace for temporary objects and sort files
This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Albert Cervera Areny wrote: Sorry about the wrong diff format. Attached is the good one. A Dimecres 25 Octubre 2006 09:07, Neil Conway va escriure: On Wed, 2006-10-25 at 00:45 +0200, Albert Cervera Areny wrote: Hope the diff and idents are ok. Patches should be submitted in context diff (diff -c) format. -Neil [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Tablespace for temporary objects and sort files
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: 26 October 2006 14:19 To: Albert Cervera Areny Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] Tablespace for temporary objects and sort files This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold I believe Albert was looking for feedback at this stage, rather than submitting a completed patch. http://archives.postgresql.org/pgsql-patches/2006-10/msg00141.php Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Tablespace for temporary objects and sort files
bruce wrote: Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: 26 October 2006 14:19 To: Albert Cervera Areny Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] Tablespace for temporary objects and sort files This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold I believe Albert was looking for feedback at this stage, rather than submitting a completed patch. http://archives.postgresql.org/pgsql-patches/2006-10/msg00141.php Right, but it is being kept so that we can ping him when we start 8.3 to get a more recent version. Sorry, to clarify, I saved the entire thread where he stated he was looking for feedback, so when we go back to it, we know it needs work. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Tablespace for temporary objects and sort files
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 26 October 2006 14:37 To: [EMAIL PROTECTED] Cc: Dave Page; Albert Cervera Areny; pgsql-patches@postgresql.org Subject: Re: [PATCHES] Tablespace for temporary objects and sort files bruce wrote: Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: 26 October 2006 14:19 To: Albert Cervera Areny Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] Tablespace for temporary objects and sort files This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold I believe Albert was looking for feedback at this stage, rather than submitting a completed patch. http://archives.postgresql.org/pgsql-patches/2006-10/msg00141.php Right, but it is being kept so that we can ping him when we start 8.3 to get a more recent version. Sorry, to clarify, I saved the entire thread where he stated he was looking for feedback, so when we go back to it, we know it needs work. OK. /D ---(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: [PATCHES] Tablespace for temporary objects and sort files
Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: 26 October 2006 14:19 To: Albert Cervera Areny Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] Tablespace for temporary objects and sort files This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold I believe Albert was looking for feedback at this stage, rather than submitting a completed patch. http://archives.postgresql.org/pgsql-patches/2006-10/msg00141.php Right, but it is being kept so that we can ping him when we start 8.3 to get a more recent version. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Tablespace for temporary objects and sort files
On Wed, 2006-10-25 at 00:45 +0200, Albert Cervera Areny wrote: Hope the diff and idents are ok. Patches should be submitted in context diff (diff -c) format. -Neil ---(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: [PATCHES] Tablespace for temporary objects and sort files
On 10/24/06, Albert Cervera Areny [EMAIL PROTECTED] wrote: Hi, I'm trying to introduce myself into postgresql development and I'm working on the tablespace for temporary objects and sort files TODO item. some comments from a non-hacker: your patch isn't doing nothing at all for temporary indexes... a quick search for GetDefaultTablespace() shows this places... [EMAIL PROTECTED]:~/PG_RELEASES/pgsql$ grep -lR GetDefaultTablespace * src/backend/commands/indexcmds.c src/backend/commands/tablecmds.c src/backend/commands/tablespace.c src/backend/executor/execMain.c src/include/commands/tablespace.h Now a question, why not using the same GetDefaultTablespace() with an argument indicating if the object is temporary, if it is get the default tablespace for temp objects else get the default tablespace for permanent object... just an idea... How can I test that the tablespace is correctly used for sort files? Is there an easy way? Or should I reduce work_mem to a minimum, populate the database with data and try an ORDER BY? yes, that seems to work... i reduce, just in case, work_mem, shared_buffers and temp_buffers... Now, PG_TEMP_FILES_DIR seems to add just pgsql_temp to the filename. i think you the function you have to modify here is make_database_relative() that adds base/#database_oid# at the beginning of the path of the file. Hope the diff and idents are ok. Please let me know if there's something wrong with them. diff -c is the way -- 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] Tablespace for temporary objects and sort files
Hi,
I'm trying to introduce myself into postgresql development and I'm working on
the tablespace for temporary objects and sort files TODO item. The attached
patch shows what I've already done. The GUC is currently
called temp_tablespaces.
The tablespace changes correctly for me when creating temporary tables. I've
got some questions though:
How can I test that the tablespace is correctly used for sort files? Is
there
an easy way? Or should I reduce work_mem to a minimum, populate the database
with data and try an ORDER BY?
The GetTempTablespace function correctly returns a different tablespace
each
time is called, but I store the position of the last tablespace used with an
integer and iterate through the list of tablespaces each time. I tried to
keep the iterator from call to call but I got a segfault, I imagine due to
the memory context. Should I try to keep the iterator? How can I do it?
Hope the diff and idents are ok. Please let me know if there's something wrong
with them.
Thanks!
Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.206
diff -r1.206 tablecmds.c
336a337,340
else if (stmt-relation-istemp)
{
tablespaceId = GetTempTablespace();
}
Index: src/backend/commands/tablespace.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablespace.c,v
retrieving revision 1.39
diff -r1.39 tablespace.c
68c68
/* GUC variable */
---
/* GUC variables */
69a70
char *temp_tablespaces = NULL;
70a72,73
int next_temp_tablespace;
int num_temp_tablespaces;
932a936,1056
/*
* 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))
{
/* syntax error in name list */
pfree(rawname);
list_free(namelist);
return NULL;
}
num_temp_tablespaces = 0;
/*
* If we aren't inside a transaction, we cannot do database access so
* cannot verify the individual names. Must accept the list on faith.
*/
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++;
}
}
pfree(rawname);
list_free(namelist);
next_temp_tablespace = 0;
return newval;
}
/*
* GetTempTablespace -- get the OID of the tablespace for temporary objects
*
* May return InvalidOid to indicate use the database's default tablespace
*
* This exists to hide the temp_tablespace GUC variable.
*/
Oid
GetTempTablespace(void)
{
Oid result;
char *curname = NULL;
char *rawname;
List *namelist;
ListCell *l;
int i = 0;
rawname = pstrdup(temp_tablespaces);
/* Parse string into list of identifiers */
if (!SplitIdentifierString(rawname, ',', namelist))
{
/* syntax error in name list */
pfree(rawname);
list_free(namelist);
return InvalidOid;
}
foreach(l, namelist)
{
curname = (char *) lfirst(l);
if ( i == next_temp_tablespace )
break;
i++;
}
pfree(rawname);
/* Prepare for the next time the function is called */
next_temp_tablespace++;
if (next_temp_tablespace == num_temp_tablespaces)
next_temp_tablespace = 0;
/* Fast path for temp_tablespaces == */
if ( curname == NULL || curname[0] == '\0') {
list_free(namelist);
return InvalidOid;
}
/*
* It is tempting to cache this lookup for more speed, but then we would
* fail to detect the case where the tablespace was dropped since the GUC
* variable was set. Note also that we don't complain if the value fails
* to refer to an existing tablespace; we just silently return InvalidOid,
* causing the new object to be created in the database's tablespace.
*/
result = get_tablespace_oid(curname);
/*
* Allow explicit specification of database's default tablespace in
* default_tablespace without triggering permissions checks.
*/
if (result == MyDatabaseTableSpace)
result = InvalidOid;
list_free(namelist);
return result;
}
1004a1129,1167
/*
* get_tablespace_path - given a tablespace OID, look up the path
*
* Returns a palloc'd string,
