[HACKERS] code question: storing INTO relation
I've got the CREATE TABLE AS restructuring almost finished, but came across something that I could use some advice on. The current code stores the into relation (and whether or not that relation has OIDs) in the Query struct. This is ugly[1], but I'm not sure how to fix it. The main reason Query needs to hold this data is so that InitPlan() can produce the correct TupleDesc for the query's result set in ExecutorStart() (it needs to know, for example, whether to include space for OIDs in the TupleDesc). Based on that, I think that moving this information into QueryDesc might be doable, but I'm not sure if this will have negative implications anywhere. Any thoughts? -Neil [1] IMHO it is ugly because: (a) Query is the product of the analyzer, but it is possible for the INTO relation to change between parse-analysis and execution (consider CREATE TABLE AS ... EXECUTE). We currently cope with this by copying the Query and modifying it. (b) The INTO relation is really a property of the statement to which the SELECT/EXECUTE is attached, not the SELECT itself. In other words, in a statement like: CREATE TABLE xyz AS SELECT ...; The destination of the SELECT query is a property of the CREATE TABLE AS statement, not the SELECT. The new implementation of CREATE TABLE AS works just like this: the executor is run as normal, but it just uses a special DestReceiver that dumps the SELECTs result set into a newly-created heap table. Needing to modify the attached SELECT's Query node to let it know that it is being invoked by CREATE TABLE AS is ugly. ---(end of broadcast)--- TIP 3: 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
[HACKERS] Time off
Hi everyone, I think I'll be taking some time off from the PostgreSQL project, to work on other stuff that has my interest more at the moment :) I'll still be lurking around, but I won't really have much time to do actual coding. Cheers, Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DETERMINISTIC as synonym for IMMUTABLE
On Sun, 17 Oct 2004 19:36:16 -0400, Tom Lane wrote: Well, the spec is somewhat self-contradictory on the point, but I think their intention is to model it after their notion of a deterministic query: A query expression or query specification is possibly non- deterministic if an SQL-implementation might, at two different times where the state of the SQL-data is the same, produce results that differ by more than the order of the rows due to General Rules that specify implementation-dependent behavior. [SQL99 4.17] This section has been removed in SQL:2003. Instead, a new section 4.16 (Determinism) has been added. The first paragraph of the new section states: In general, an operation is deterministic if that operation assuredly computes identical results when repeated with identical input values. For an SQL-invoked routine, the values in the argument list are regarded as the input; otherwise, the SQL-data and the set of privileges by which they are accessed is regarded as the input. In my reading of the new section, there is nothing which indicates that determinism is related to whether stored data are changed or not. What it actually says about deterministic functions in 4.23 is: [...] An SQL-invoked routine is possibly non-deterministic if, during invocation of that SQL- invoked routine, an SQL-implementation might, at two different times when the state of the SQL-data is the same, produce unequal results [...] This paragraph has also been altered in SQL:2003. In SQL:2003's section 4.27.2, there is still a section on deterministic vs. possibly non-deterministic routines; it doesn't say anything about the state of the SQL-data any more. It says: An SQL-invoked routine is either deterministic or possibly non-deterministic. An SQL-invoked function that is deterministic always returns the identical return value for a given list of SQL argument values. [... cut stuff about sql-invoked procedures which PostgreSQL doesn't support yet...] An SQL-invoked routine is possibly non-deterministic if it might produce nonidentical results when invoked with the identical list of SQL argument values. I think that they meant to define SQL functions as nondeterministic if they act like or contain nondeterministic queries; for instance 13.5 says 3) An SQL procedure statement S is possibly non-deterministic if and only if at least one of the following is satisfied: a) S is a select statement: single row that is possibly non- deterministic. b) S contains a routine invocation whose subject routine is an SQL-invoked routine that possibly modifies SQL-data. [...] This has also been changed in SQL:2003: 4) [SQL procedure statement] is possibly non-deterministic if and only if S is not an SQL schema statement and at least one of the following is satisfied: a) S is a select statement: single row that is possibly non-deterministic. b) S contains a routine invocation whose subject routine is an SQL-invoked routine that is possibly non-deterministic. c) S generally contains a query specification or a query expression that is possibly non-deterministic. d) S generally contains a value expression that is possibly non-deterministic. Anybody know whether the SQL2003 text clarifies the intent at all? I think it's fair to say that SQL:2003 is more clear on this subject, and that PostgreSQL's IMMUTABLE is equivalent to SQL:2003's DETERMINISTIC. The remaining problem now becomes if NOT DETERMINISTIC could be introduced as an alias to VOLATILE: I don't think it's very helpful to identify NOT DETERMINISTIC with VOLATILE. As a counterexample, now() is NOT DETERMINISTIC, but it isn't VOLATILE. But does this have any semantic significance? - I mean: It's still safe to call a function including now() NOT DETERMINISTIC==VOLATILE; no unexpected results should result from this, except - potentially - lower performance. I think it's a common phenomenon that performance can sometimes be increased by utilizing certain product-specific expressions in stead of the standards-defined ones. -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Using ALTER TABLESPACE in pg_dump
One additional idea for this item is to use CREATE to first create the object, then move it using ALTER, and the ALTER might fail if the tablespace doesn't exist. The only problem with that is this TODO item: o Allow databases and schemas to be moved to different tablespaces One complexity is whether moving a schema should move all existing schema objects or just define the location for future object creation. If we add a new SET variable and use it in pg_dump we will have to support it forever even if there is no practical use for it. (ALTER was originally part of the open item but I removed it thinking we might not have space to load the table in the default location, but I forgot we create it empty and could move it before we load it.) One interesting side-affect of allowing tablespace specification to fail is that it might give users enough control that we can mark this item as done: * Allow database recovery where tablespaces can't be created When a pg_dump is restored, all tablespaces will attempt to be created in their original locations. If this fails, the user must be able to adjust the restore process. One idea would be that users could create any tablespaces or objects they want to change before they do the restore and the restore would use their new configuration and just error/skip the items they already created. (This would also behave well if you load the dump and say stop on any errors.) For example, if you want to eliminate a tablespace, you just don't create the directory and load your dump. Tablespace create will fail, and all objects that use that tablespace will fail their ALTER and will remain in their default locations. This actually seems less error-prone than the idea of them manually changing things in the dump file. --- Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: o remove non-portable TABLESPACE clause from CREATE TABLE and use a new default_tablespace SET variable I'm coming around to the conclusion that this is simply a bad idea. The problem with having such a SET variable is that it plays hob with the existing definition about where schemas and tables get a default tablespace from. Which source wins (the database or schema default tablespace, or the SET variable)? And why? The only really clean way to have a SET variable for this is to forget about schema- or table-based defaults. Do we want to do that? (Hey, it'd solve the problem with schema tablespaces being droppable, because there wouldn't *be* any such thing as a schema's tablespace anymore. But on the whole this seems like a step backward in usability.) Agreed, a step backwards, but see below. What we might want to do is invent a --notablespace option for pg_dump, comparable to --noowner, to let someone make a dump that contains no TABLESPACE clauses. Yea, that would work, but we went through so much work to allow SQL standard DDL statements, and it seems a shame to break it just for tablespaces. And, having it be a separate SET would also allow the tablespace creation to fail and still get the objects created. (If the explicit_tablespace doesn't exist during CREATE, we throw a warning. This would contrast with a create _failure_ when the tablespace doesn't exist and you say 'TABLESPACE t1' in CREATE.) So there were actually two uses for this, one for standards compliance, and the other was for flexibility in restoring to a system where the tablespaces can't be created. The SET could give us different behavior (warning vs. error) which would be useful for pg_dump. Could we call it explicit_tablespace and when it is , it is the default, but when it isn't it is just like using 'TABLESPACE t1' in the CREATE, but throws a warning instead of an error if the tablespace doesn't exist? My assumption is that it would not be like the default_with_oids variable usage by pg_dump because it would be reset to '' (default) by pg_dump after each time it is used. I assume explicit_tablespace would always override the schema or database tablespace because it is explicit. In fact this would partially fix the TODO we have: * Allow database recovery where tablespaces can't be created When a pg_dump is restored, all tablespaces will attempt to be created in their original locations. If this fails, the user must be able to adjust the restore process. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1:
Re: [HACKERS] additional GCC warnings
On Sun, Oct 17, 2004 at 01:50:46PM -0400, Tom Lane wrote: -Wdeclaration-after-statement (Recent versions of GCC allow declarations and statements to be intermixed in C; enabling this flag would enforce the current convention of avoiding this style.) Ick. If the default is to allow that, then yes we need a warning. I'd be fairly annoyed if it's not an error, in fact. IIRC it's a new feature in C99. If that is the case, you may want to tell gcc simply to compile an older C dialect and get errors for all such newfangled code. Jeroen ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open Items
Dear Tom, o remove non-portable TABLESPACE clause from CREATE TABLE and use a new default_tablespace SET variable I'm coming around to the conclusion that this is simply a bad idea. I agree that the set approach is error prone. Another idea was to issue an ALTER after the CREATE. That would move the empty table from one tablespace to another, at small cost. If it fails, it is simply ignored by the restoration process, but the table was already created so it exists. What we might want to do is invent a --notablespace option for pg_dump, comparable to --noowner, to let someone make a dump that contains no TABLESPACE clauses. (1) --notablespace would be useful, but it would not fix the problem I had in mind, i.e. the transfer (possibly after a crash) of data to another base which would not have these tablespaces. If the disk is crashed, I cannot redo the pg_dump. (2) thus it would help to be able to decide this at restore time. I think that one of the implementation idea was to store the information into some headers. (3) possible current workaround for the desperate admin: (a) create fake tablespaces as necessary... (b) pg_restore ... | sed 's/TABLESPACE .*//' | psql ... Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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: [HACKERS] Open Items
I just posted this idea with a new heading. ALTER seems like the right approach, but notice we need to complete another TODO item to allow tablespace movement of databases and schemas. --- Fabien COELHO wrote: Dear Tom, o remove non-portable TABLESPACE clause from CREATE TABLE and use a new default_tablespace SET variable I'm coming around to the conclusion that this is simply a bad idea. I agree that the set approach is error prone. Another idea was to issue an ALTER after the CREATE. That would move the empty table from one tablespace to another, at small cost. If it fails, it is simply ignored by the restoration process, but the table was already created so it exists. What we might want to do is invent a --notablespace option for pg_dump, comparable to --noowner, to let someone make a dump that contains no TABLESPACE clauses. (1) --notablespace would be useful, but it would not fix the problem I had in mind, i.e. the transfer (possibly after a crash) of data to another base which would not have these tablespaces. If the disk is crashed, I cannot redo the pg_dump. (2) thus it would help to be able to decide this at restore time. I think that one of the implementation idea was to store the information into some headers. (3) possible current workaround for the desperate admin: (a) create fake tablespaces as necessary... (b) pg_restore ... | sed 's/TABLESPACE .*//' | psql ... Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nearing final release?
At 2004-10-16 18:41:05 -0400, [EMAIL PROTECTED] wrote:
I think the cleanest solution is probably to add a state flag indicating
whether ParseComplete should generate a PGresult or not.
Like the appended (incremental) patch?
(I didn't think this would work, because I thought libpq would allow you
to send multiple queries before trying to read results. But you said it
didn't support that, so...)
-- ams
--- libpq-int.h.1~ 2004-10-18 17:42:13.175759981 +0530
+++ libpq-int.h 2004-10-18 17:43:40.105986570 +0530
@@ -269,6 +269,8 @@
* nonblock sending
semantics */
boolext_query; /* was our last query sent with
extended
* query protocol? */
+ boolsent_prepare; /* was our last Parse message sent with
+* PQprepare? */
charcopy_is_binary; /* 1 = copy binary, 0 = copy text */
int copy_already_done; /* # bytes already
returned in
*
COPY OUT */
--- fe-exec.c.2~2004-10-18 17:47:55.540189274 +0530
+++ fe-exec.c 2004-10-18 17:48:30.119038902 +0530
@@ -686,6 +686,7 @@
goto sendFailed;
conn-ext_query = true;
+ conn-sent_prepare = true;
if (pqFlush(conn) 0)
goto sendFailed;
conn-asyncStatus = PGASYNC_BUSY;
--- fe-protocol3.c.2~ 2004-10-18 17:44:06.616198123 +0530
+++ fe-protocol3.c 2004-10-18 17:46:34.431656569 +0530
@@ -220,10 +220,13 @@
conn-asyncStatus = PGASYNC_READY;
break;
case '1': /* Parse Complete */
- if (conn-result == NULL)
- conn-result =
PQmakeEmptyPGresult(conn,
-
PGRES_COMMAND_OK);
- conn-asyncStatus = PGASYNC_READY;
+ if (conn-sent_prepare) {
+ if (!conn-result)
+ conn-result =
PQmakeEmptyPGresult(conn,
+
PGRES_COMMAND_OK);
+ conn-asyncStatus = PGASYNC_READY;
+ conn-sent_prepare = false;
+ }
break;
case '2': /* Bind Complete */
case '3': /* Close Complete */
--- libpq-fe.h.2~ 2004-10-18 17:55:40.632064120 +0530
+++ libpq-fe.h 2004-10-18 17:56:26.501634328 +0530
@@ -312,9 +312,9 @@
int resultFormat);
/* Interface for multiple-result or asynchronous queries */
-extern PGresult *PQsendPrepare(PGconn *conn, const char *stmtName,
- const char *query, int
nParams,
- const Oid *paramTypes);
+extern int PQsendPrepare(PGconn *conn, const char *stmtName,
+const char *query, int nParams,
+const Oid *paramTypes);
extern int PQsendQuery(PGconn *conn, const char *query);
extern int PQsendQueryParams(PGconn *conn,
const char *command,
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] tsearch2 windows make failure
The attached log from make on tsearch2 for windows demonstrates a failure at the dllwrap stage, presumably as a result of changes just made to handle the relative path thing discussed yesterday. It also illustrates some other make and compile warnings I am seeing quite frequently, and to which I was about to turn my attention. cheers andrew ../../src/Makefile.shlib:327: warning: overriding commands for target `libtsearch2.a' ../../src/Makefile.shlib:262: warning: ignoring old commands for target `libtsearch2.a' sed -e 's,MODULE_PATHNAME,$libdir/tsearch2,g' tsearch.sql.in tsearch2.sql cp untsearch.sql.in untsearch2.sql gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o dict_ex.o dict_ex.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o dict.o dict.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o snmap.o snmap.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o stopword.o stopword.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o common.o common.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o prs_dcfg.o prs_dcfg.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o dict_snowball.o dict_snowball.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o dict_ispell.o dict_ispell.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o dict_syn.o dict_syn.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o wparser.o wparser.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o wparser_def.o wparser_def.c wparser_def.c:79:1: warning: IDIGNORE redefined In file included from c:/mingw/include/windows.h:55, from ../../src/include/pg_config_os.h:7, from ../../src/include/c.h:85, from ../../src/include/postgres.h:48, from wparser_def.c:9: c:/mingw/include/winuser.h:717:1: warning: this is the location of the previous definition gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I. -I../../src/include -I/home/pgrunner/pgbuildfarm/root/HEAD/pgsql/src/include/port/win32 -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o ts_cfg.o ts_cfg.c gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I./snowball -I./ispell -I./wordparser -I.
[HACKERS] gettext calls in pgport
As has previously been pointed out, the strings marked up for gettext translation in the pgport library don't work and need to be moved back to where they once came from, unless someone wants to add gettext and locale setup in pgport. (That might be silly, because in theory locale and gettext functions could one day become part of pgport.) I claim that pgport is a low-layer library that works on the operating system level and should communicate with its callers via error codes. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] additional GCC warnings
Jeroen T. Vermeulen wrote: IIRC it's a new feature in C99. If that is the case, you may want to tell gcc simply to compile an older C dialect and get errors for all such newfangled code. We make occasional, optional use of C99 features, so we don't want to turn it off completely. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: 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: [HACKERS] gettext calls in pgport
Peter Eisentraut wrote: As has previously been pointed out, the strings marked up for gettext translation in the pgport library don't work and need to be moved back to where they once came from, unless someone wants to add gettext and locale setup in pgport. (That might be silly, because in theory locale and gettext functions could one day become part of pgport.) I claim that pgport is a low-layer library that works on the operating system level and should communicate with its callers via error codes. Error codes seem like a lot more work than it is worth. I vote for adding gettext support to /port. Also adding error codes duplicates all the error strings in the call sites. Added to open items list: * Add gettext support to src/port -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] CVS compile warnings
Got this after a fresh cvs update -d -P: ../../../../src/include/storage/s_lock.h: In function `tas': ../../../../src/include/storage/s_lock.h:124: warning: read-write constraint does not allow a register ../../../../src/include/storage/s_lock.h:124: warning: read-write constraint does not allow a register -- Bernd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] CVS compile warnings
--On Montag, Oktober 18, 2004 17:23:47 +0200 Bernd Helmle [EMAIL PROTECTED] wrote: Got this after a fresh cvs update -d -P: ../../../../src/include/storage/s_lock.h: In function `tas': ../../../../src/include/storage/s_lock.h:124: warning: read-write constraint does not allow a register ../../../../src/include/storage/s_lock.h:124: warning: read-write constraint does not allow a register Hrm, should mention Platform and compiler: Its gcc-3.4.0 on Debian Woody (x86, Kernel 2.4.26), compiling CVS Head. -- Bernd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] organisation of directory
Hallo! I have a big problem, could you please help me? I am developing a DB System accociated with a File server. The DB Server schould manage directories which coulkd be, I thought, created through fired Triggers. I wrote a C function and compiled as -shared. But its not working. Is there somewhere any example? Or c functionen ? Or how could I do it? Thanks ___ Gesendet von Yahoo! Mail - Jetzt mit 100MB Speicher kostenlos - Hier anmelden: http://mail.yahoo.de ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Bruce Momjian [EMAIL PROTECTED] writes: One additional idea for this item is to use CREATE to first create the object, then move it using ALTER, and the ALTER might fail if the tablespace doesn't exist. This seems fairly impractical, at least for indexes where there is no way to do the ALTER before the object is filled with data. If we add a new SET variable and use it in pg_dump we will have to support it forever even if there is no practical use for it. Yeah, that's one thing that bothers me. One interesting side-affect of allowing tablespace specification to fail is that it might give users enough control that we can mark this item as done: Hmm, here's a variant idea: how about a GUC variable named something like soft_tablespace_specs which when TRUE would mean that a nonexistent tablespace name in a TABLESPACE clause is ignored (maybe with a WARNING) rather than being an error, and so the object is created in whatever the default tablespace for it would be. You wouldn't even necessarily want to have pg_dump set this true for itself, but people could turn it on when they needed to load a dump with wrong tablespace names in it. (If we didn't have pg_dump turn it on automatically, then we'd not be beholden to support it forever.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] spinlocks: generalizing non-locking test
On Sun, Oct 17, 2004 at 11:16:50PM +1000, Neil Conway wrote:
Currently, the assembly for TAS() on x86 does a non-locking test before
using an atomic operation to attempt to acquire the spinlock:
__asm__ __volatile__(
cmpb$0,%1 \n
jne 1f \n
lock\n
xchgb %0,%1 \n
1: \n
: +q(_res), +m(*lock)
:
: memory, cc);
The reason this is a good idea is that if we fail to immediately acquire
the spinlock, s_lock() will spin SPINS_PER_DELAY times in userspace
calling TAS() each time before going to sleep. If we do an atomic
operation for each spin, this generates a lot more bus traffic than is
necessary. Doing a non-locking test (followed by an atomic operation to
acquire the spinlock if appropriate) is therefore better on SMP systems.
Currently x86 is the only platform on which we do this -- ISTM that all
the other platforms that implement spinlocks via atomic operations could
benefit from this technique.
We could fix this by tweaking each platform's assembler to add a
non-blocking test, but there might be an easier way. Rather than
modifying platform-specific assembler, I believe this C sequence is
equivalent to the non-locking test:
volatile slock_t *lock = ...;
if (*lock == 0)
TAS(lock);
Because the lock variable is volatile, the compiler should reload it
from memory for each loop iteration. (If this is actually not a
sufficient non-locking test, please let me know...)
We could add a new s_lock.h macro, TAS_INNER_LOOP(), whose default
implementation would be:
#define TAS_INNER_LOOP(lock) \
if ((*lock) == 0) \
TAS(lock);
And then remove the x86-specific non-locking test from TAS.
Comments?
-Neil
Steve Hemminger had this to say:
The linux kernel code is:
#define UNLOCKED 1
...
while (atomic_dec(lock) != 0) {
do {
rep_nop();
} while(*lock != UNLOCKED);
}
To do the equivalent thing in postgres would mean
while(TAS(lock)) {
do {
rep_nop();
} while (*lock);
The point is do the locking test first (assume success is possible)
if that doesn't work spin without doing locked operations and make
sure and do the rep; nop; for the hyperthreaded CPU's
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4 changes
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Do we want to backport tighter security for plperl? In particular, insisting on Safe.pm = 2.09 and removing the :base_io set of ops? I'd vote not: 7.4.5 = 7.4.6 is not an update that people would expect to break their plperl code ... *shrug* OK. Then plperl should probably not be regarded as being as trusted as we would like. Note that old versions of Safe.pm have been the subject of security advisories such as this one http://www.securityfocus.com/bid/6111/info/ for some time. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] V3 protocol gets out of sync on messages that cause allocation failures
Oliver Jowett [EMAIL PROTECTED] writes: What appears to be happening is that the backend goes into error recovery as soon as the allocation fails (just after reading the message length), and never does the read() of the body of the Bind message. So it falls out of sync, and tries to interpret the guts of the Bind as a new message. Bad server, no biscuit. Yeah. The intent of the protocol design was that the recipient could skip over the correct number of bytes even if it didn't have room to buffer them, but the memory allocation mechanism in the backend makes it difficult to actually do that. Now that we have PG_TRY, though, it might not be out of reach to do it right. Something like PG_TRY(); buf = palloc(N); PG_CATCH(); read and discard N bytes; re-raise the out-of-memory error; PG_END_TRY(); normal read path I'm not sure how many places would need to be touched to make this actually happen; if memory serves, the read a packet code extends over multiple logical levels. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Oct 14, 2004, at 6:50 PM, Abhijit Menon-Sen wrote: I thought about that for a while, but I couldn't find anything that is actually broken or confused by the patch. I could be missing something obvious, though, so I'd appreciate another set of eyes looking at it. Does anyone have any ideas? Not I, but I still have my fingers crossed that this will go in in time for 8.0. I think that Tom said it looked good when you first posted the patch. So, core hackers, is it going in or not? The dynamic language driver developers will thank you for it! Many thanks, David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: One additional idea for this item is to use CREATE to first create the object, then move it using ALTER, and the ALTER might fail if the tablespace doesn't exist. This seems fairly impractical, at least for indexes where there is no way to do the ALTER before the object is filled with data. If we add a new SET variable and use it in pg_dump we will have to support it forever even if there is no practical use for it. Yeah, that's one thing that bothers me. One interesting side-affect of allowing tablespace specification to fail is that it might give users enough control that we can mark this item as done: Hmm, here's a variant idea: how about a GUC variable named something like soft_tablespace_specs which when TRUE would mean that a nonexistent tablespace name in a TABLESPACE clause is ignored (maybe with a WARNING) rather than being an error, and so the object is created in whatever the default tablespace for it would be. You wouldn't even necessarily want to have pg_dump set this true for itself, but people could turn it on when they needed to load a dump with wrong tablespace names in it. (If we didn't have pg_dump turn it on automatically, then we'd not be beholden to support it forever.) That's a nice idea in that it doesn't require a SET for every object that uses tablespace, and allows user control over tablespace failure. The only downside is that it prevents SQL-compliant CREATE syntax in dumps. I was thinking you could use ALTER just for tables but then the tablespace failure rules would be different for tables and other objects, which is unacceptable. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] tsearch2 windows make failure
Andrew Dunstan [EMAIL PROTECTED] writes: ../../src/Makefile.shlib:327: warning: overriding commands for target `libtsearch2.a' ../../src/Makefile.shlib:262: warning: ignoring old commands for target `libtsearch2.a' Broken coding in the WIN32 parts of Makefile.shlib? I don't see this here. wparser_def.c:79:1: warning: IDIGNORE redefined rewrite.c:177:1: warning: V_UNKNOWN redefined Thanks Microsoft :-( ... guess we'll have to rename those macros. Info: resolving _my_exec_path by linking to __imp__my_exec_path (auto-import) fu01.o(.idata$3+0xc): undefined reference to `libpostgres_a_iname' nmth00.o(.idata$4+0x0): undefined reference to `_nm__my_exec_path' I was wondering whether my_exec_path might need to be marked DLLIMPORT. Not sure about the other symbol though. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] gettext calls in pgport
Bruce Momjian [EMAIL PROTECTED] writes: Error codes seem like a lot more work than it is worth. I vote for adding gettext support to /port. Also adding error codes duplicates all the error strings in the call sites. Added to open items list: * Add gettext support to src/port He who controls the TODO list dictates the solutions, eh? I tend to agree with Peter, actually: it would be better to pull error reporting issues out of pgport. Somebody just yesterday stuck an fprintf(stderr,...); exit(1) into one of the pgport routines. This sucks, but there is not a lot else that can be done if the code needs to exist in both backend and clients. It'd be better to propagate the error condition back to the caller. An alternative possibility is to stop pretending that pgport is agnostic about whether it is in backend or frontend. This might mean some duplication of code between src/port/ and src/backend/port/, but if that's what it takes to have sane error handling, that's what we should do. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] gettext calls in pgport
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Error codes seem like a lot more work than it is worth. I vote for adding gettext support to /port. Also adding error codes duplicates all the error strings in the call sites. Added to open items list: * Add gettext support to src/port He who controls the TODO list dictates the solutions, eh? And I can update it too. New text is: * fix gettext support to src/port We need to fix it somehow. I tend to agree with Peter, actually: it would be better to pull error reporting issues out of pgport. Somebody just yesterday stuck an fprintf(stderr,...); exit(1) into one of the pgport routines. This sucks, but there is not a lot else that can be done if the code needs to exist in both backend and clients. It'd be better to propagate the error condition back to the caller. I have no problem propogating the error condition back to the caller, but to propogate the error code/reason back means you would need -1 == no file, -2 == permission violation, etc and you then have those strings back in the client. That was Peter's goal, I thought, but it goes against the idea of centralizing those strings. I suppose you could #define all the needed strings in an include file and use those defines when checking for the error return code, but you have to make sure you get them all right and adjust for new strings, and it doesn't seem worth it. In fact one interesting idea would be to pass a constant error string back to the caller to fprintf/elog if they want, but that doesn't get the strings out of /port, and it doesn't allow easy use of passing fprintf strings with arguments back to the caller. An alternative possibility is to stop pretending that pgport is agnostic about whether it is in backend or frontend. This might mean some duplication of code between src/port/ and src/backend/port/, but if that's what it takes to have sane error handling, that's what we should do. I tried that but the fix caused more uglyness than it prevented. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] confusing log messages on pg_ctl -w start
When I run pg_ctl -w start I often see this on the log, which is less than clear, if not downright confusing: LOG: database system is ready FATAL: the database system is starting up Any ideas on how to improve this? cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Final libpq patch?
Here is a mega-patch that merges in your four previous patches. Is this
ready to be applied? Making libpq changes during beta is risky so we
had better be careful.
I have also attached your pgtest.c file.
---
Abhijit Menon-Sen wrote:
At 2004-10-16 18:41:05 -0400, [EMAIL PROTECTED] wrote:
I think the cleanest solution is probably to add a state flag indicating
whether ParseComplete should generate a PGresult or not.
Like the appended (incremental) patch?
(I didn't think this would work, because I thought libpq would allow you
to send multiple queries before trying to read results. But you said it
didn't support that, so...)
-- ams
--- libpq-int.h.1~2004-10-18 17:42:13.175759981 +0530
+++ libpq-int.h 2004-10-18 17:43:40.105986570 +0530
@@ -269,6 +269,8 @@
* nonblock sending
semantics */
boolext_query; /* was our last query sent with
extended
* query protocol? */
+ boolsent_prepare; /* was our last Parse message sent with
+ * PQprepare? */
charcopy_is_binary; /* 1 = copy binary, 0 = copy text */
int copy_already_done; /* # bytes already
returned in
*
COPY OUT */
--- fe-exec.c.2~ 2004-10-18 17:47:55.540189274 +0530
+++ fe-exec.c 2004-10-18 17:48:30.119038902 +0530
@@ -686,6 +686,7 @@
goto sendFailed;
conn-ext_query = true;
+ conn-sent_prepare = true;
if (pqFlush(conn) 0)
goto sendFailed;
conn-asyncStatus = PGASYNC_BUSY;
--- fe-protocol3.c.2~ 2004-10-18 17:44:06.616198123 +0530
+++ fe-protocol3.c2004-10-18 17:46:34.431656569 +0530
@@ -220,10 +220,13 @@
conn-asyncStatus = PGASYNC_READY;
break;
case '1': /* Parse Complete */
- if (conn-result == NULL)
- conn-result =
PQmakeEmptyPGresult(conn,
-
PGRES_COMMAND_OK);
- conn-asyncStatus = PGASYNC_READY;
+ if (conn-sent_prepare) {
+ if (!conn-result)
+ conn-result =
PQmakeEmptyPGresult(conn,
+
PGRES_COMMAND_OK);
+ conn-asyncStatus = PGASYNC_READY;
+ conn-sent_prepare = false;
+ }
break;
case '2': /* Bind Complete */
case '3': /* Close Complete */
--- libpq-fe.h.2~ 2004-10-18 17:55:40.632064120 +0530
+++ libpq-fe.h2004-10-18 17:56:26.501634328 +0530
@@ -312,9 +312,9 @@
int resultFormat);
/* Interface for multiple-result or asynchronous queries */
-extern PGresult *PQsendPrepare(PGconn *conn, const char *stmtName,
-const char *query, int
nParams,
-const Oid *paramTypes);
+extern int PQsendPrepare(PGconn *conn, const char *stmtName,
+ const char *query, int nParams,
+ const Oid *paramTypes);
extern int PQsendQuery(PGconn *conn, const char *query);
extern int PQsendQueryParams(PGconn *conn,
const char *command,
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/libpq.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/libpq.sgml,v
retrieving revision 1.165
diff -c -c -r1.165 libpq.sgml
*** doc/src/sgml/libpq.sgml 1 Oct 2004 17:34:17 - 1.165
--- doc/src/sgml/libpq.sgml 18 Oct
Re: [HACKERS] Final libpq patch?
Bruce Momjian [EMAIL PROTECTED] writes: Here is a mega-patch that merges in your four previous patches. Is this ready to be applied? Making libpq changes during beta is risky so we had better be careful. I was planning to review the updated patch and apply if there was nothing else major wrong with it. I will use this as the starting point --- thanks for doing the merging ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] gettext calls in pgport
Tom Lane wrote: Somebody just yesterday stuck an fprintf(stderr,...); exit(1) into one of the pgport routines. This sucks, but there is not a lot else that can be done if the code needs to exist in both backend and clients. It'd be better to propagate the error condition back to the caller. An alternative possibility is to stop pretending that pgport is agnostic about whether it is in backend or frontend. This might mean some duplication of code between src/port/ and src/backend/port/, but if that's what it takes to have sane error handling, that's what we should do. Maybe you're referring to the patch I sent in to strip the .exe suffix in get_progname? ;-) I wondered about that. The choices on strdup() error seemed to be: . ignore the error and return the unstripped path, knowing the program would fail in a minute on the next malloc call anyway . return NULL and patch the code in about 20 places (of which one is the backend) where get_progname() is called . print a message and exit I can see arguments for all of these ;-) cheers andrew ---(end of broadcast)--- TIP 3: 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: [HACKERS] gettext calls in pgport
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Somebody just yesterday stuck an fprintf(stderr,...); exit(1) into one of the pgport routines. This sucks, but there is not a lot else that can be done if the code needs to exist in both backend and clients. It'd be better to propagate the error condition back to the caller. Maybe you're referring to the patch I sent in to strip the .exe suffix in get_progname? ;-) Yeah, that was it. I wondered about that. The choices on strdup() error seemed to be: . ignore the error and return the unstripped path, knowing the program would fail in a minute on the next malloc call anyway . return NULL and patch the code in about 20 places (of which one is the backend) where get_progname() is called . print a message and exit Given the limited uses of get_progname, I think that print a message and exit is fine; the problem is that the correct implementation of that differs between backend and clients. The only really correct way to log a message in the backend is elog/ereport --- there's no guarantee that stderr connects to anything but /dev/null. Going directly to exit() instead of proc_exit() is simply broken (although perhaps the distinction does not matter, since the postmaster will treat exit(1) as a backend crash and force a database-wide reset). If I thought that this code path was ever likely to actually be taken in the field, I'd be hollering much more loudly about it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nearing final release?
Abhijit Menon-Sen [EMAIL PROTECTED] writes: At 2004-10-16 18:41:05 -0400, [EMAIL PROTECTED] wrote: I think the cleanest solution is probably to add a state flag indicating whether ParseComplete should generate a PGresult or not. Like the appended (incremental) patch? Not exactly --- the way you've got this set up, a failed PQprepare will leave a booby trap for the next operation. The flag has got to be reset on command send, not on receipt of a success response. I'll find a better place to do it. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] libpq and prepared statements progress for 8.0
It was just added to CVS! --- David Wheeler wrote: On Oct 14, 2004, at 6:50 PM, Abhijit Menon-Sen wrote: I thought about that for a while, but I couldn't find anything that is actually broken or confused by the patch. I could be missing something obvious, though, so I'd appreciate another set of eyes looking at it. Does anyone have any ideas? Not I, but I still have my fingers crossed that this will go in in time for 8.0. I think that Tom said it looked good when you first posted the patch. So, core hackers, is it going in or not? The dynamic language driver developers will thank you for it! Many thanks, David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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: [HACKERS] libpq and prepared statements progress for 8.0
On Oct 18, 2004, at 3:12 PM, Bruce Momjian wrote: It was just added to CVS! Awesome! Abhijit++ Bruce++ Tom++ Regards, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Mon, Oct 18, 2004 at 06:12:29PM -0400, Bruce Momjian wrote: It was just added to CVS! Woohooo! Big, BIG kudos to all involved :) :) Cheers, D(BD::Pg) -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] V3 protocol gets out of sync on messages that cause allocation failures
I wrote: Yeah. The intent of the protocol design was that the recipient could skip over the correct number of bytes even if it didn't have room to buffer them, but the memory allocation mechanism in the backend makes it difficult to actually do that. Now that we have PG_TRY, though, it might not be out of reach to do it right. And indeed it wasn't. Patch committed. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] code question: storing INTO relation
Neil Conway [EMAIL PROTECTED] writes: I've got the CREATE TABLE AS restructuring almost finished, but came across something that I could use some advice on. The current code stores the into relation (and whether or not that relation has OIDs) in the Query struct. This is ugly[1], but I'm not sure how to fix it. It strikes me that as far as the executor is concerned, CREATE TABLE AS would be better treated as an INSERT (ie, pretend it was CREATE TABLE followed by INSERT/SELECT). If you did that then the idea would be to create the new table and add it to the query range table. Then you need neither any special-case code in execMain, nor a new DestReceiver. You'd still want to copy/modify the Query, but to convert it from a SELECT into an INSERT, which is a pretty clean transformation I think. regards, tom lane ---(end of broadcast)--- TIP 3: 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: [HACKERS] code question: storing INTO relation
On Mon, 18 Oct 2004, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: I've got the CREATE TABLE AS restructuring almost finished, but came across something that I could use some advice on. The current code stores the into relation (and whether or not that relation has OIDs) in the Query struct. This is ugly[1], but I'm not sure how to fix it. It strikes me that as far as the executor is concerned, CREATE TABLE AS would be better treated as an INSERT (ie, pretend it was CREATE TABLE followed by INSERT/SELECT). If you did that then the idea would be to create the new table and add it to the query range table. Then you need neither any special-case code in execMain, nor a new DestReceiver. You'd still want to copy/modify the Query, but to convert it from a SELECT into an INSERT, which is a pretty clean transformation I think. Just thinking of optimisations, I wonder if it would be possible to bypass WAL (like we do for ALTER TABLE ... SET TABLESPACE) if archiving is disabled, and fsync the newly created relfilenode after the data is inserted. Gavin ---(end of broadcast)--- TIP 3: 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: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 03:15 AM 19/10/2004, Bruce Momjian wrote: The only downside is that it prevents SQL-compliant CREATE syntax in dumps. One idea that may be worth considering: we currently dump a complete SQL statement including a TABLESPACE clause, which makes it hard to allow pg_restore to have a 'no tablespaces' option (it is fixed by pg_dump). To solve this, we should dump the table definition as a format string and dump the tablespace clause (ie. ' TABLESPACE ts') as a separate part of the table definition TOC entry. If the user wants the tablespace to be dumped, then we substitute the tablespace clause, otherwise a blank string. This could be a useful general approach in the future. The main issue will be quoting the clause identifiers (if we use '%%tablespace%%' then we have to handle columns or tables whose names contain '%%tablespace%%'. Simplest would be to backslash quote all '%' and '\' then do subs. Also, I like the option of a soft-tablespace option, but also liked the idea of the fake/logical/virtual tablespaces someone suggested earlier; if restoring into a database without a required tablespace, then create a virtual tablespace that points to pg_default. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Hypothetical Indexes
Tom, This would be of some value if the optimizer's cost estimates were highly reliable, but unfortunately they are far from being so :-( Without the ability to measure *actual* as opposed to estimated costs, I'm not sure you can really do much. In fact, any index selection tool that uses hypothetical indexes will not recommend indexes that the optimizer does not recognize as good enough for the query. The bright side of this is that the better the optimizer gets, the better are the recommendations made by the index selection tool. And people tend to invest resources in constructing good query optimizers. Other index selection tools, such as Microsoft's and IBM's, have the same limitation. Even though, the tools are useful for people that have to deal with databases with a big quantity of tables and queries. Finding useful indexes in this kind of setting is a difficult problem for DBAs. So, our point is that hypothetical indexes just have to be as well estimated by the optimizer as conventional, real indexes. An index not suggested by the optimizer might still be usable, but that would require rewriting the query or using hints, things that need the intervention of a more skilled DBA anyway. Best regards, Marcos. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [CYGWIN] [HACKERS] open item: tablespace handing in pg_dump/pg_restore
Hi, There are certainly cygwin-users trying out PostgreSQL on cygwin on WinXX. If the newest cygwin-version will suddenly stop working under WinXX, they will not be happy. I've given consideration to the argument that you can no longer take data-directories from the cygwin-version to the native-version... And I think that there's not a *huge* loss there. For me, as an observer and occiasional user/developer, I think the loss of not running on cygwin+winXX is larger. After all, the data can still be dumped / reloaded. And what gives me the certainty that the two versions of PostgreSQL, the cygwin and the native version, are not already compiled in such way that they're not binary compatible? (remember, I'm an outsider on this, with no knowledge of the binary formats, and I'm trying to remain in that perspective for this discussion) I don't know what the failure will be when you now try to move a data-directory from the cygwin version to the native version, when cygwin uses a .lnk hack and native uses a junction. Did anyone try? What do the results look like? Is there an acceptable way to stop ppl from trying / give sensible errors without introducing too much crap in the code and without harming ppls data? regards, --Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Tuesday, October 12, 2004 1:02 AM To: Bruce Momjian Cc: Reini Urban; PostgreSQL Developers; [EMAIL PROTECTED] Subject: Re: [CYGWIN] [HACKERS] open item: tablespace handing in pg_dump/pg_restore Bruce Momjian [EMAIL PROTECTED] writes: OK, I have applied the following patch that uses Cygwin native symlink() instead of the Win32 junctions. The reason for this is that Cygwin symlinks work on Win95/98/ME where junction points do not and we have no way to know what system will be running the Cygwin binaries so the safest bet is to use the Cygwin versions. On Win32 native we only run on systems that support junctions. I think this is probably a net loss, because what it will mean is that you cannot take a data directory built under a Cygwin postmaster and use it under a native postmaster, nor vice versa. Given the number of other ways in which we do not support pre-NT4 Windows systems, what is the benefit of allowing this one? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] embedded postgresql
Dear Sirs, I would like to know if there are any discussions about creating an embedded version on postgresql. My thoughts go towards building/porting a sqlite equivalent of pg. Regards, GB. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [BUGS] BUG #1270: stack overflow in thread in fe_getauthname
Hi Bruce, That would work! Thanks Peter Bruce Momjian wrote: One idea would be to use malloc() to allocate storage for the thread-safe buffers when compiled with thread-safety, rather than using the stack. --- Peter Davie wrote: Hi Tom, How many of these platforms you use are POSIX-compliant? This information came from the POSIX web site (NOT THE DIGITAL/COMPAQ/HP/... WEBSITE). Sysconf(_SC_GETPW_R_SIZE_MAX) is supported by Solaris 2.5, SCO UNIX (circa 1999!), Digital UNIX/Compaq Tru64 UNIX, FreeBSD, AIX, HP-UX, and probably many more. Maybe the non-compliant platforms should be catered for as "legacy" with support code in the "ports" area, and those that do adhere to open standards can be accommodated without breaking existing production applications. Thanks Peter Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, we got a report. I just thinkg 8192 is excessive for that structure, and if someone is having a problem, others might as well. On Tru64 UNIX, sysconf(_SC_GETPW_R_SIZE_MAX) returns 1024. I'd be more impressed by this line of reasoning if _SC_GETPW_R_SIZE_MAX were defined on more than one of the platforms I use... regards, tom lane -- Relevance... because results count Relevance Phone: +61 (0)2 6241 6400 A.B.N. 86 063 403 690 Fax:+61 (0)2 6241 6422 Unit 11, Mitchell Commercial Centre,Mobile: +61 (0)417 265 175 cnr Brookes and Heffernan Sts., E-mail: [EMAIL PROTECTED] Mitchell ACT 2911 Web:http://www.relevance.com.au -- Relevance... because results count Relevance Phone: +61 (0)2 6241 6400 A.B.N. 86 063 403 690 Fax:+61 (0)2 6241 6422 Unit 11, Mitchell Commercial Centre,Mobile: +61 (0)417 265 175 cnr Brookes and Heffernan Sts., E-mail: [EMAIL PROTECTED] Mitchell ACT 2911 Web:http://www.relevance.com.au
Re: [HACKERS] spinlocks: generalizing non-locking test
On Mon, 2004-10-18 at 11:53, Tom Lane wrote: Only once we've begun to spin. The first time through, it's not at all clear whether the extra test is worthwhile --- it's certainly a win if the lock is always already held, and certainly a loss if the lock is always free Granted, but I think you've mostly conceded my point: every _subsequent_ time TAS() is invoked, the non-locking test is a clear win (with the possible exception of PPC). Therefore we have two cases: initial TAS and TAS inside loop, so so the logical implementation is two distinct macros. Of course, there may well be platforms on which TAS() is defined to TAS_INNER_LOOP() or vice versa, but this decision will vary by platform. you have to do some benchmarking to decide if you want it or not. I agree that benchmarking is worth doing before making changes. We have the ASM-level test on those platforms where people seem to think that it is worthwhile, but not everywhere. That is certainly an optimistic interpretation :-) I would say an equally likely theory is that there is only one platform on which people have bothered to try a non-blocking test and see if it improves performance, and accordingly we only have one platform on which a non-locking test is used. (If anyone out there _has_ modified the spinlock implementation for PostgreSQL on a particular platform to use a non-locking initial test and found it hasn't improved performance, please speak up.) -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Autotuning of shared buffer size (was: Re: [HACKERS] Getting rid of AtEOXact Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results, some wierdness ...))
Trying to think a little out of the box, how common is it in modern operating systems to be able to swap out shared memory? Maybe we're not using the ARC algorithm correctly after all. The ARC algorithm does not consider the second level OS buffer cache in it's design. Maybe the total size of the ARC cache directory should not be 2x the size of what is configured as the shared buffer cache, but rather 2x the size of the effective cache size (in 8k pages). If we assume that the job of the T1 queue is better done by the OS buffers anyway (and this is what this discussion seems to point out), we shouldn't hold them in shared buffers (only very few of them and evict them ASAP). We just account for them and assume that the OS will have those cached that we find in our T1 directory. I think with the right configuration for effective cache size, this is a fair assumption. The T2 queue represents the frequently used blocks. If our implementation would cause the unused/used portions of the buffers not to move around, the OS will swap out currently unused portions of the shared buffer cache and utilize those as OS buffers. To verify this theory it would be interesting what the ARC strategy after a long DBT run with a large buffer cache thinks a good T2 size would be. Enabling the strategy debug message and running postmaster with -d1 will show that. In theory, this size should be anywhere near the sweet spot. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Getting rid of AtEOXact Buffers (was Re: [Testperf-general]
On 10/17/2004 3:40 PM, [EMAIL PROTECTED] wrote: Seeing as I've missed the last N messages... I'll just reply to this one, rather than each of them in turn... Tom Lane [EMAIL PROTECTED] wrote on 16.10.2004, 18:54:17: I wrote: Josh Berkus writes: First off, two test runs with OProfile are available at: http://khack.osdl.org/stp/298124/ http://khack.osdl.org/stp/298121/ Hmm. The stuff above 1% in the first of these is Counted CPU_CLK_UNHALTED events (clocks processor is not halted) with a unit mask of 0x00 (No unit mask) count 10 samples %app name symbol name ... 9203692.1332 postgres AtEOXact_Buffers ... In the second test AtEOXact_Buffers is much lower (down around 0.57 percent) but the other suspects are similar. Since the only difference in parameters is shared_buffers (36000 vs 9000), it does look like we are approaching the point where AtEOXact_Buffers is a problem, but so far it's only a 2% drag. Yes... as soon as you first mentioned AtEOXact_Buffers, I realised I'd seen it near the top of the oprofile results on previous tests. Although you don't say this, I presume you're acting on the thought that a 2% drag would soon become a much larger contention point with more users and/or smaller transactions - since these things are highly non-linear. It occurs to me that given the 8.0 resource manager mechanism, we could in fact dispense with AtEOXact_Buffers, or perhaps better turn it into a no-op unless #ifdef USE_ASSERT_CHECKING. We'd just get rid of the special case for transaction termination in resowner.c and let the resource owner be responsible for releasing locked buffers always. The OSDL results suggest that this won't matter much at the level of 1 or so shared buffers, but for 10 or more buffers the linear scan in AtEOXact_Buffers is going to become a problem. If the resource owner is always responsible for releasing locked buffers, who releases the locks if the backend crashes? Do we need some additional code in bgwriter (or?) to clean up buffer locks? If the backend crashes, the postmaster (assuming a possibly corrupted shared memory) restarts the whole lot ... so why bother? We could also get rid of the linear search in UnlockBuffers(). The only thing it's for anymore is to release a BM_PIN_COUNT_WAITER flag, and since a backend could not be doing more than one of those at a time, we don't really need an array of flags for that, only a single variable. This does not show in the OSDL results, which I presume means that their test case is not exercising transaction aborts; but I think we need to zap both routines to make the world safe for large shared_buffers values. (See also http://archives.postgresql.org/pgsql-performance/2004-10/msg00218.php) Yes, that's important. Any objection to doing this for 8.0? As you say, if these issues are definitely kicking in at 10 shared_buffers - there's a good few people out there with 800Mb shared_buffers already. Could I also suggest that we adopt your earlier suggestion of raising the bgwriter parameters as a permanent measure - i.e. changing the defaults in postgresql.conf. That way, StrategyDirtyBufferList won't immediately show itself as a problem when using the default parameter set. It would be a shame to remove one obstacle only to leave another one following so close behind. [...and that also argues against an earlier thought to introduce more fine grained values for the bgwriter's parameters, ISTM] I realized that StrategyDirtyBufferList currently wasts a lot of time by first scanning over all the buffers that haven't even been hit since it's last call and neither have been dirty last time (and thus, are at the beginning of the list and can't be dirty anyway). If we would have a way to give it a smart point in the list to start scanning ... Also, what will Vacuum delay do to the O(N) effect of FlushRelationBuffers when called by VACUUM? Will the locks be held for longer? Vacuum only naps at the points where it checks for interrupts, and at that time it isn't supposed to hold any critical locks. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Getting rid of AtEOXact Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results, some wierdness ...)
Jan Wieck [EMAIL PROTECTED] writes: I realized that StrategyDirtyBufferList currently wasts a lot of time by first scanning over all the buffers that haven't even been hit since it's last call and neither have been dirty last time (and thus, are at the beginning of the list and can't be dirty anyway). If we would have a way to give it a smart point in the list to start scanning ... I don't think it's true that they *can't* be dirty. (1) Buffers are marked dirty when released, whereas they are moved to the fronts of the lists when acquired. (2) the cntxDirty bit can be set asynchronously to any BufMgrLock'd operation. But it sure seems like we are doing more work than we really need to. One idea I had was for the bgwriter to collect all the dirty pages up to say halfway on the LRU lists, and then write *all* of these, not just the first N, over as many rounds as are needed. Then go back and call StrategyDirtyBufferList again to get a new list. (We don't want it to write every dirty buffer this way, because the ones near the front of the list are likely to be dirtied again right away. But certainly we could write more than 1% of the dirty buffers without getting into the area of the recently-used buffers.) There isn't any particularly good reason for this to share code with checkpoint-style BufferSync, btw. BufferSync could just as easily scan the buffers linearly, since it doesn't matter what order it writes them in. So we could change StrategyDirtyBufferList to stop as soon as it's halfway up the LRU lists, which would save at least a few cycles. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Open Items
o fix shared memory on Win2k terminal server We might be able to just mark this as not supported. I have attached a patch that I think fixes this. The problem I saw and fixed is, that the shmem created in a terminal services client is not visible to the console (or services.msc). It was necessary to differenciate OS versions, this might be better put elsewhere. I think in addition the system global name sharemem.1 should be made more pg specific, like PostgreSQL.1. I have not done this since a new compile would not detect a running old beta. But now would be the time (or never). Andreas shmem.win32.patch Description: shmem.win32.patch ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Open Items
Agreed on the memory name change and I will do it when I apply the patch. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Zeugswetter Andreas DAZ SD wrote: o fix shared memory on Win2k terminal server We might be able to just mark this as not supported. I have attached a patch that I think fixes this. The problem I saw and fixed is, that the shmem created in a terminal services client is not visible to the console (or services.msc). It was necessary to differenciate OS versions, this might be better put elsewhere. I think in addition the system global name sharemem.1 should be made more pg specific, like PostgreSQL.1. I have not done this since a new compile would not detect a running old beta. But now would be the time (or never). Andreas Content-Description: shmem.win32.patch [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] Open Items
o fix shared memory on Win2k terminal server We might be able to just mark this as not supported. I have attached a patch that I think fixes this. The problem I saw and fixed is, that the shmem created in a terminal services client is not visible to the console (or services.msc). Does this actually fix the problem for you? Because, as I have previously posted I think, it does *not* solve the problem on any of my test machines. I still get the shmget() error message when running from a TS session. (Yes, I tried specifically with your patch, since I thought maybe I got something wrong before) I'm on Windows 2000 Server, tested both DC and non-DC machines. What are you testing on? Also, I don't really see how the visibility of the shmem segment matters. We can't *create* the first instance of it, which should not affect this at all. And if we passed that, all backends are still execute in the same session, so there is no effect on it. services.msc only interacts with the SCM, it has nothing at all to do with shmem. //Magnus ---(end of broadcast)--- TIP 3: 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
