Re: [HACKERS] Sync Rep: First Thoughts on Code
Hi, sorry for my consecutive posting. On Fri, Dec 5, 2008 at 4:00 PM, Fujii Masao [EMAIL PROTECTED] wrote: Hello, On Fri, Dec 5, 2008 at 12:09 PM, Fujii Masao [EMAIL PROTECTED] wrote: I was expecting you to have walreceiver and startup share an end of WAL address via shared memory, so that startup never tries to read past end. That way we would be able to begin reading a WAL file *before* it was filled. Waiting until a file fills means we still have to have archive_timeout set to ensure we switch regularly. You mean that not pg_standby but startup process waits for the next WAL available? If so, I agree with you in the future. That is, I just think that this is next TODO because there are many problems which we should resolve carefully to achieve it. But, if it's essential for 8.4, I will tackle it. What is your opinion? I'd like to clear up the goal for 8.4. Umm.. on second thought, this feature (continuous recovery without pg_standby) seems to be essential for 8.4. So, I will try it. Development plan: - Share the end of WAL address via shared memory --- Done! - Change ReadRecord() to wait for the next WAL *record* available. - Change ReadRecord() to restore the WAL from archive by using pg_standby before reaching the replication starting position, then read the half-streaming WAL from pg_xlog. - Add new trigger for promoting the standby to the primary. As the trigger, when fast shudown (SIGINT) is requested during recovery, the standby would recover the WAL up to end and become the primary. What system call does walreceiver have to call against the WAL before startup process reads it? Probably we need to call write(2), and don't need fsync(2) in Linux. How about other platform? I added the figures about the latest architecture into PDF file. Please check P6, 7. Is this architecture close to your imege? http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Detailed_Design Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [patch] pg_upgrade script for 8.3-8.4
On Thu, 4 Dec 2008, Zdenek Kotala wrote: 1) Keep relfileid of toast file same. It is important because toast pointer contains relfileid. Currently script creates fake files with same number to protect postgresql to create new relation with this refileid. It works but by my opinion it is not much robust. I suggest to use following syntax: create table foo (id int) with (relfileid=16544, reltoastid=11655, reltoastidx=16543) But once there's a more core integrated in-place upgrade, as envisioned for 8.4-8,5, this syntax wouldn't been useful for anything anymore, right? I understand your distaste for the hack, but it seems a bit extreme to start fiddling with CREATE TABLE and pg_dump* just to implement a work-around for a temporary problem. I think your magic is strong enough for this, as long as the upgrade script does some sanity checks and proceeds cautiously I think we can live with it. Another problem with tablespace location is that CREATE TABLESPACE checks if directory is empty and it fails when it contains any file/directory...Suggested sugar syntax is: CREATE DATABASE foobar WITH ID=17012; CREATE TABLESPACE bar LOCATION '/dev/null/' ID=15543 IGNORECONTENT; The logic of the above continues here; the id=xxx construct seems like it will be useless clutter in the future, and if the script is capable of sorting it out with a bit of hacking then go with that. The way I see things, the sequence of events will go like this: -Create new cluster -Restore schema -Fiddle with IDs, table spaces, etc. -Then, only if there were no errors or problems with the above, are the old files moved over. As long as all the hacking and safety checks happen before any of the original files are touched at all, it's kind of ugly but it should work well enough for the target audience: relatively saavy DBAs who just can't take the time for a dump/reload because their database is too large. The kind of admins who doesn't know how to test and stage a major version upgrade with appropriate backups are the group that are still running PG7.4.[1] I'm not sure if the same logic applies to the IGNORECONTENT suggestion for tablespaces though. I know I've been vaguely annoyed by that limitation before, typically because it would have been easier to directly use a new mount point as a tablespace except that there's a lost+found directory in there. End up adding another directory level for no good reason. This seems like a relatively small caliber foot-gun to provide; will have to take a look at the code to see if I still feel that way afterwards, if nobody jumps up to protest the whole concept first that is. [1] I hereby propose Greg's Law of DBAs: the larger and more critical a database is, the more likely it is to attract a clueful DBA to take care of it. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/pg_stat_statements 1202
On Fri, 5 Dec 2008, Vladimir Sitnikov wrote: Oracle's approach is to have the explain command stuff the results into a table. That has advantages for tools, especially if you want to be able to look at plans generated by other sessions. I do not believe that workflow makes sense. I have never ever thought of it. The main benefit is that you can track how EXPLAIN plans change over time. Archive a snapshot of what the plans for all your common queries look like every day, and the day one of them blows up and starts doing something wrong you've got a *lot* more information to work with for figuring out what happened--whether it was a minor query change, some stats that got slowly out of whack, etc. I wouldn't just immediately dismiss that workflow as unsensible without thinking about it a bit first, there are some good advantages to it. Greg Sabino Mullane had a neat blog entry on saving plans to tables in PostgreSQL, unfortunately the Planet PostgreSQL outage seems to have eaten it. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
Greg Smith [EMAIL PROTECTED] writes: I'm sorry, I think I misunderstood the original idea, what you're talking about makes a lot more sense now. You want to save the space of the dead column by replacing it with NULL, not remove it from the table definition. Not so much to save the space, it's more about breaking its need for the soon to be removed pg_attribute that used to lead to the dropped column. If you think it's possible to slip that change into the page conversion task, that's probably the ideal way to deal with this. Removing the dropped column attribute is what I think we cannot do. If you crashed while during that it would leave you with half the table with the columns in the old attribute position and half the columns in the new attribute position. There would be no way to tell which was which. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Kurt Harriman wrote: Sometimes people would like to call C++ code in the PostgreSQL backend environment... for example, in user-defined functions, triggers, access methods. And there is sometimes a need for C++ code to call back into PostgreSQL's C functions, such as the SPI interface. Have you considered writing a procedural language plugin for C++? PostgreSQL supports a lot of extension languages, and none of them require the amount of backend changes that you outline here, because the PL plugin serves as glue. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
A seriously substantial portion of the diff for this patch all is supporting trivial renaming, like changing everything that uses: - TypeName *typename = (TypeName *) cmd-def; + TypeName *typeName = (TypeName *) cmd-def; Is that really necessary? After going through a few pages of diff code where supporting this trivial bit was the only change, my eyes glazed over. Minimizing diff size makes it much more likely somebody will complete a review of the functional parts of your submission before getting bored. If it is needed, I'd suggest you'd get a warmer reception here submitting two diffs, one that just did the renaming and a second that actually had the functional bits in it. Then it would be possible to casually scan the renaming one for a second to see it was trivial and boring, followed by a review of the functional one that was focused on its real changes. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Hi Greg,
Actually I did email this to the list with 4 separate diffs in
4 separate attached files. I don't know why it appears all
massed together at http://archives.postgresql.org/pgsql-hackers.
I'll try resubmitting them separately. The first diff consists
of just the renaming, which is intentionally trivial and boring.
The second diff adds a few extern C {...} declarations - also
trivial and boring, but small. The interesting part is in the
third diff.
Regards,
... kurt
Greg Smith wrote:
A seriously substantial portion of the diff for this patch all is
supporting trivial renaming, like changing everything that uses:
- TypeName *typename = (TypeName *) cmd-def;
+ TypeName *typeName = (TypeName *) cmd-def;
Is that really necessary? After going through a few pages of diff code
where supporting this trivial bit was the only change, my eyes glazed
over. Minimizing diff size makes it much more likely somebody will
complete a review of the functional parts of your submission before
getting bored.
If it is needed, I'd suggest you'd get a warmer reception here
submitting two diffs, one that just did the renaming and a second that
actually had the functional bits in it. Then it would be possible to
casually scan the renaming one for a second to see it was trivial and
boring, followed by a review of the functional one that was focused on
its real changes.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
On Fri, 5 Dec 2008, Greg Smith wrote: If it is needed, I'd suggest you'd get a warmer reception here submitting two diffs, one that just did the renaming and a second that actually had the functional bits in it. You can just ignore this late night bit of idiocy, or mock me for it as you see fit. Note to other reviewers: if your e-mail client is the sort that bunches a series of text attachments all together, make sure to scroll completely past the first patch in the diff before you pay attention to the rest of it. I'm going to bed. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: c++reserved - patch 1 of 4
(Re-sending just the first of four patches: c++reserved)
These patches are based on CVS head in which the latest commit was
user:petere
date:Thu Dec 04 17:51:28 2008 +
summary: Default values for function arguments
1. c++reserved
User-defined functions and extensions may need to access
backend data structures such as parse trees. A few of the
relevant header files contain field or parameter names
which happen to be C++ reserved words. This makes them
unusable from C++ because the compiler chokes on the
reserved word. It has been suggested that the C++ user
could surround these #includes with #defines to substitute
innocuous words for the reserved words; but that would be
unbearably kludgy, error prone and unmaintainable. A polite
host does not demand such things of a guest.
Fortunately, there are not many instances which are likely
to be encountered by our C++ guests, and these can easily
be changed. In memnodes.h, parsenodes.h, and primnodes.h,
this patch changes the following field names:
typename = typeName
typeid = typeOid
using = usingClause
delete = delete_context
Also, the patch changes a few parameter names in function
prototypes in makefuncs.h, parse_type.h, and builtins.h:
typename = typeName
typeid = typeOid
namespace = qualifier
There's no need to ask PostgreSQL developers to remember to
avoid C++ reserved words, because C++ users who are affected
by such occurrences can be asked to submit a corrective patch.
diff -r bd5a52b2681a src/backend/access/common/tupdesc.c
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -532,10 +532,10 @@
attnum++;
attname = entry-colname;
- atttypid = typenameTypeId(NULL, entry-typename, atttypmod);
- attdim = list_length(entry-typename-arrayBounds);
+ atttypid = typenameTypeId(NULL, entry-typeName, atttypmod);
+ attdim = list_length(entry-typeName-arrayBounds);
- if (entry-typename-setof)
+ if (entry-typeName-setof)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg(column \%s\ cannot be
declared SETOF,
diff -r bd5a52b2681a src/backend/commands/sequence.c
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -141,53 +141,53 @@
switch (i)
{
case SEQ_COL_NAME:
- coldef-typename = makeTypeNameFromOid(NAMEOID,
-1);
+ coldef-typeName = makeTypeNameFromOid(NAMEOID,
-1);
coldef-colname = sequence_name;
namestrcpy(name, seq-sequence-relname);
value[i - 1] = NameGetDatum(name);
break;
case SEQ_COL_LASTVAL:
- coldef-typename = makeTypeNameFromOid(INT8OID,
-1);
+ coldef-typeName = makeTypeNameFromOid(INT8OID,
-1);
coldef-colname = last_value;
value[i - 1] =
Int64GetDatumFast(new.last_value);
break;
case SEQ_COL_STARTVAL:
- coldef-typename = makeTypeNameFromOid(INT8OID,
-1);
+ coldef-typeName = makeTypeNameFromOid(INT8OID,
-1);
coldef-colname = start_value;
value[i - 1] =
Int64GetDatumFast(new.start_value);
break;
case SEQ_COL_INCBY:
- coldef-typename = makeTypeNameFromOid(INT8OID,
-1);
+ coldef-typeName = makeTypeNameFromOid(INT8OID,
-1);
coldef-colname = increment_by;
value[i - 1] =
Int64GetDatumFast(new.increment_by);
break;
case SEQ_COL_MAXVALUE:
- coldef-typename = makeTypeNameFromOid(INT8OID,
-1);
+ coldef-typeName = makeTypeNameFromOid(INT8OID,
-1);
coldef-colname = max_value;
value[i - 1] = Int64GetDatumFast(new.max_value);
break;
case SEQ_COL_MINVALUE:
- coldef-typename = makeTypeNameFromOid(INT8OID,
-1);
+ coldef-typeName = makeTypeNameFromOid(INT8OID,
-1);
coldef-colname = min_value;
Re: [HACKERS] Mostly Harmless: c++bookends - patch 2 of 4
(Re-sending just the second of four patches: c++bookends)
These patches are based on CVS head in which the latest commit was
user:petere
date:Thu Dec 04 17:51:28 2008 +
summary: Default values for function arguments
2. c++bookends
C++ code can call C functions and share global variables with C,
provided those declarations are surrounded by bookends:
extern C {
...
};
Header files can be made bilingual, to declare interfaces which
look the same to both C and C++ callers. This is done by
placing C++ bookends within the header file, guarded by #ifdefs
#ifdef __cplusplus
extern C {
#endif
...
#ifdef __cplusplus
}; /* extern C */
#endif
This way the C++ caller can just #include the header file without
worrying whether the interface is implemented in C or C++.
Usually, extension modules written in C++ will put bookends around
all of their PostgreSQL #includes.
However, postgres.h usually stands alone as the first #include,
followed by some system #includes, and then the rest of the
PostgreSQL #includes. It is much nicer if a C++ file has just one
pair of bookends around its main block of PostgreSQL #includes.
This patch gives postgres.h its own internal bookends, making it
bilingual, so that its #include can continue to stand alone at the
head of each file.
Just a few additional header files are mentioned in the PostgreSQL
Reference Manual for add-on developers to use: fmgr.h, funcapi.h,
and spi.h. This patch adds bookends within those three files for
the benefit of beginners writing very simple extensions in C++.
Documentation and learning are simplified because C example code
can be compiled as C or C++ without change.
diff -r 55d732d0fbcd src/include/executor/spi.h
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -18,6 +18,10 @@
* included postgres.h
*/
#include postgres.h
+
+#ifdef __cplusplus
+extern C {
+#endif
/*
* Most of these are not needed by this file, but may be used by
@@ -156,4 +160,8 @@
extern void AtEOXact_SPI(bool isCommit);
extern void AtEOSubXact_SPI(bool isCommit, SubTransactionId mySubid);
+#ifdef __cplusplus
+} /* extern C */
+#endif
+
#endif /* SPI_H */
diff -r 55d732d0fbcd src/include/fmgr.h
--- a/src/include/fmgr.h
+++ b/src/include/fmgr.h
@@ -17,6 +17,10 @@
*/
#ifndef FMGR_H
#define FMGR_H
+
+#ifdef __cplusplus
+extern C {
+#endif
/* We don't want to include primnodes.h here, so make a stub reference */
typedef struct Node *fmNodePtr;
@@ -544,4 +548,8 @@
*/
extern char *fmgr(Oid procedureId,...);
+#ifdef __cplusplus
+} /* extern C */
+#endif
+
#endif /* FMGR_H */
diff -r 55d732d0fbcd src/include/funcapi.h
--- a/src/include/funcapi.h
+++ b/src/include/funcapi.h
@@ -16,11 +16,14 @@
#ifndef FUNCAPI_H
#define FUNCAPI_H
+#ifdef __cplusplus
+extern C {
+#endif
+
#include fmgr.h
#include access/tupdesc.h
#include executor/executor.h
#include executor/tuptable.h
-
/*-
* Support to ease writing Functions returning composite types
@@ -299,4 +302,8 @@
PG_RETURN_NULL(); \
} while (0)
+#ifdef __cplusplus
+} /* extern C */
+#endif
+
#endif /* FUNCAPI_H */
diff -r 55d732d0fbcd src/include/postgres.h
--- a/src/include/postgres.h
+++ b/src/include/postgres.h
@@ -44,7 +44,12 @@
#ifndef POSTGRES_H
#define POSTGRES_H
+#ifdef __cplusplus
+extern C {
+#endif
+
#include c.h
+
#include utils/elog.h
#include utils/palloc.h
@@ -693,4 +698,8 @@
const char *errorType,
const char *fileName, int lineNumber);
+#ifdef __cplusplus
+} /* extern C */
+#endif
+
#endif /* POSTGRES_H */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: c++configure - patch 3 of 4
(Re-sending just the third of four patches: c++configure) These patches are based on CVS head in which the latest commit was user:petere date:Thu Dec 04 17:51:28 2008 + summary: Default values for function arguments 3. c++configure This patch adds C++ support to the PostgreSQL build system. After you have applied the patch, cd to the top of the source tree (to the directory containing the file 'configure.in') and execute these two commands to regenerate the 'configure' script and some related files: autoconf autoheader Much as it already does for the C compiler, the 'configure' script will try to find the C++ compiler and set up appropriate command line options. If 'configure' finds a C++ compiler, it will set up src/Makefile.global to define the following makefile variables: CXX = command for invoking C++ compiler CXXCPP = command for invoking C++ preprocessor CXXFLAGS = C++ compiler options GXX = 'yes' if the C++ compiler is gcc/g++ Implicit rules are defined so that gmake will automatically invoke the C++ compiler using the above variables given a source file name suffixed with '.cpp' or '.cc'. So, to add a file named marvin.cpp to the build, just add 'marvin.o' to the OBJS list in the Makefile. To C++-compile a file with '.c' suffix, the Makefile should list the .o file in both OBJS and CXXOBJS. The pg_config utility can be used to display the CXX and CXXFLAGS. Most C++ code typically uses some C++ features whose implementation makes use of the compiler's runtime library: exceptions, static constructors, new/delete, STL containers, stream I/O, etc. Specify the following 'configure' option to link the C++ runtime library into the postgres backend: --enable-cplusplus If --enable-cplusplus is specified, the makefile variable 'enable_cplusplus' will be set to 'yes', and pg_config.h will #define ENABLE_CPLUSPLUS. To ensure that the C++ runtime library is properly initialized, on some platforms it is necessary for the main() function to be compiled as C++. Therefore, if --enable-cplusplus is configured, src/backend/main/main.c will be compiled as C++. This is handled by the following lines in src/backend/main/Makefile: ifeq ($(enable_cplusplus),yes) CXXOBJS = main.o endif Fortunately, main.c can be compiled as either C or C++ with no difficulty after applying the c++reserved and c++bookends patches. To make main.c bilingual, all that was needed was a pair of bookends around its #includes. Limitations: - I haven't added support for profiling and code coverage for C++. Automatic dependency generation is supported, however. - This ought to work on platforms which use GCC, and maybe some others. The only one I have tested is x86_32 Linux with GCC 4.1.2. Hopefully some interested hackers will try it on platforms to which they have access, and post the results. diff -r 257c0be599ab config/c-compiler.m4 --- a/config/c-compiler.m4 +++ b/config/c-compiler.m4 @@ -103,6 +103,7 @@ # command-line option. If it does, add the string to CFLAGS. AC_DEFUN([PGAC_PROG_CC_CFLAGS_OPT], [AC_MSG_CHECKING([if $CC supports $1]) +AC_LANG_ASSERT([C]) pgac_save_CFLAGS=$CFLAGS CFLAGS=$pgac_save_CFLAGS $1 _AC_COMPILE_IFELSE([AC_LANG_PROGRAM()], @@ -110,6 +111,23 @@ [CFLAGS=$pgac_save_CFLAGS AC_MSG_RESULT(no)]) ])# PGAC_PROG_CC_CFLAGS_OPT + + + +# PGAC_PROG_CXX_CXXFLAGS_OPT +# --- +# Given a string, check if the C++ compiler supports the string as a +# command-line option. If it does, add the string to CXXFLAGS. +AC_DEFUN([PGAC_PROG_CXX_CXXFLAGS_OPT], +[AC_MSG_CHECKING([if $CXX supports $1]) +AC_LANG_ASSERT([C++]) +pgac_save_CXXFLAGS=$CXXFLAGS +CXXFLAGS=$pgac_save_CXXFLAGS $1 +_AC_COMPILE_IFELSE([AC_LANG_PROGRAM()], + AC_MSG_RESULT(yes), + [CXXFLAGS=$pgac_save_CXXFLAGS +AC_MSG_RESULT(no)]) +])# PGAC_PROG_CXX_CXXFLAGS_OPT diff -r 257c0be599ab configure.in --- a/configure.in +++ b/configure.in @@ -195,6 +195,14 @@ PGAC_ARG_BOOL(enable, debug, no, [build with debugging symbols (-g)]) AC_SUBST(enable_debug) + +# +# --enable-cplusplus links the postgres backend with the C++ runtime library +# +PGAC_ARG_BOOL(enable, cplusplus, no, [build with C++ runtime library], + [AC_DEFINE([ENABLE_CPLUSPLUS], 1, + [Define to 1 for mixed C/C++ build. (--enable-cplusplus)])]) +AC_SUBST(enable_cplusplus) # # --enable-profiling enables gcc profiling @@ -365,9 +373,9 @@ PGAC_ARG_REQ(with, CC, [CMD], [set compiler (deprecated)], [CC=$with_CC]) case $template in - aix) pgac_cc_list=gcc xlc;; - irix) pgac_cc_list=cc;; # no gcc -*) pgac_cc_list=gcc cc;; + aix)
Re: [HACKERS] Mostly Harmless: c++exception - patch 4 of 4
(Re-sending just the fourth of four patches: c++exception)
These patches are based on CVS head in which the latest commit was
user:petere
date:Thu Dec 04 17:51:28 2008 +
summary: Default values for function arguments
4. c++exception
When C code calls C++ code, all C++ exceptions need to be caught
and fully contained within the C++ code. Exceptions should never
be thrown outward across the C/C++ frontier.
If an exception is not caught within C++ code, and the search for
a matching 'catch' bumps into a C stack frame, the result may be
platform dependent. On my platform (Linux/GCC), if this happens
in the postgres backend, the process terminates silently as if
abort() had been called.
With this patch, if --enable-cplusplus is configured,
PostgresMain defines a handler to intercept any uncaught C++
exception and convert it to a conventional PostgreSQL error of
FATAL severity. This allows the backend to clean up and report
the error before terminating.
diff -r 9b2c774a6b05 src/backend/tcop/Makefile
--- a/src/backend/tcop/Makefile
+++ b/src/backend/tcop/Makefile
@@ -14,6 +14,11 @@
OBJS= dest.o fastpath.o postgres.o pquery.o utility.o
+# Designate modules to be compiled as C++ when 'configure --enable-cplusplus'
+ifeq ($(enable_cplusplus),yes)
+CXXOBJS = postgres.o
+endif
+
ifneq (,$(filter $(PORTNAME),cygwin win32))
override CPPFLAGS += -DWIN32_STACK_RLIMIT=$(WIN32_STACK_RLIMIT)
endif
diff -r 9b2c774a6b05 src/backend/tcop/postgres.c
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -33,6 +33,17 @@
#endif
#ifdef HAVE_GETOPT_H
#include getopt.h
+#endif
+
+#ifdef ENABLE_CPLUSPLUS
+#ifndef __cplusplus
+#error --enable-cplusplus configure option specified; this file should be
compiled as C++
+#endif
+#include exception
+#endif
+
+#ifdef __cplusplus
+extern C {
#endif
#ifndef HAVE_GETRUSAGE
@@ -163,6 +174,11 @@
#endif /* TCOP_DONTUSENEWLINE */
+#ifdef __cplusplus
+} /* extern C */
+#endif
+
+
/*
* decls for routines only used in this file
*
@@ -1212,7 +1228,7 @@
if (log_parser_stats)
ResetUsage();
- query = parse_analyze_varparams(copyObject(raw_parse_tree),
+ query = parse_analyze_varparams((Node
*)copyObject(raw_parse_tree),
query_string,
paramTypes,
numParams);
@@ -1679,7 +1695,7 @@
* we have to make a copy of the parse trees. FIXME someday.
*/
oldContext = MemoryContextSwitchTo(PortalGetHeapMemory(portal));
- query_list = copyObject(cplan-stmt_list);
+ query_list = (List *)copyObject(cplan-stmt_list);
plan_list = pg_plan_queries(query_list, 0, params, true);
MemoryContextSwitchTo(oldContext);
@@ -2791,6 +2807,29 @@
return NULL;
}
+
+
+#ifdef ENABLE_CPLUSPLUS
+/*
+ * PostgresMainUncaught
+ * Called when C++ code throws an exception which is not caught.
+ *
+ * NB: On some platforms when C++ code calls C code, and the C code calls
+ * a deeper layer of C++ code, the outer C++ code can't catch exceptions
+ * thrown by the inner C++ code. The search for a matching 'catch' is
+ * abandoned upon encountering an intervening C stack frame, and the
+ * exception is considered uncaught.
+ */
+static void
+PostgresMainUncaught()
+{
+/* Context callbacks might not work right if call stack has been unwound */
+error_context_stack = NULL;
+
+elog(FATAL, Unexpected internal error: Unhandled C++ exception);
+abort();/* not reached */
+}
+#endif /* ENABLE_CPLUSPLUS */
/*
@@ -2850,6 +2889,11 @@
/* Set up reference point for stack depth checking */
stack_base_ptr = stack_base;
+#ifdef ENABLE_CPLUSPLUS
+/* Any unhandled C++ exception is to be treated as a FATAL error. */
+std::set_terminate(PostgresMainUncaught);
+#endif
+
/* Compute paths, if we didn't inherit them from postmaster */
if (my_exec_path[0] == '\0')
{
@@ -3108,10 +3152,10 @@
char *name;
char *value;
- name = lfirst(gucopts);
+ name = (char *)lfirst(gucopts);
gucopts = lnext(gucopts);
- value = lfirst(gucopts);
+ value = (char *)lfirst(gucopts);
gucopts = lnext(gucopts);
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
On Fri, 5 Dec 2008, Kurt Harriman wrote: Actually I did email this to the list with 4 separate diffs in 4 separate attached files. I don't know why it appears all massed together at http://archives.postgresql.org/pgsql-hackers. Thanks for being so attentive. Your e-mail was fine, the archives just mash multiple text-based attachments tagged as Text/PLAIN together like that, as does my mail reader. 1522 lines of only renaming in part 1, no wonder I gave up. I see you've already started re-sending the individual parts as separate messages; that's nice for initial casual browsing of them, but will get boring for everybody if you do that every time. If you've got more than one text file to attach, for future updates you might consider a tar archive of them to keep them running together in the archives. Once you're putting stuff into an archive, might as well compress it too, particularly for a patch of this size. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
On Friday 05 December 2008 03:55, Peter Eisentraut wrote: Kurt Harriman wrote: Sometimes people would like to call C++ code in the PostgreSQL backend environment... for example, in user-defined functions, triggers, access methods. And there is sometimes a need for C++ code to call back into PostgreSQL's C functions, such as the SPI interface. Have you considered writing a procedural language plugin for C++? PostgreSQL supports a lot of extension languages, and none of them require the amount of backend changes that you outline here, because the PL plugin serves as glue. I think this patch is great, although I haven't had time to test it yet. The only real backend change is the exception-handling clause; and the fact that the backend will also be linked against the C++ runtime library. Everything else is routine stuff that an experienced C++ developer would end up catching while trying to get his build-system for a new project running; but it could certainly scare away someone with less experience. Better to deal with this way ahead of time and test it on a few platforms. -- David Lee Lambert ... Software Developer Cell phone: +1 586-873-8813 ; alt. email [EMAIL PROTECTED] or [EMAIL PROTECTED] GPG key at http://www.lmert.com/keyring.txt pgp3zsixMgumQ.pgp Description: PGP signature
[HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1280)
I updated the patch set of SE-PostgreSQL and related (r1280) [1/6] http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1280.patch [2/6] http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r1280.patch [3/6] http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1280.patch [4/6] http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1280.patch [5/6] http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1280.patch [6/6] http://sepgsql.googlecode.com/files/sepostgresql-row_acl-8.4devel-3-r1280.patch Draft of the SE-PostgreSQL documentation is here: http://wiki.postgresql.org/wiki/SEPostgreSQL List of updates: - The patches are rebased to the current CVS HEAD - '--disable-row-acl' is removed. This feature is implicitly enabled when we don't activate any other enhanced security features. From this change, SECURITY_SYSATTR_NAME got being always defined, so some of unnecessary #ifdef ... #endif block has gone. - bugfix: it caused a crash on COPY when we specify a system attribute on FORCE QUATE clause. - cleanup: some warnings to notice unused variables are eliminated. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep: First Thoughts on Code
On Fri, 2008-12-05 at 16:00 +0900, Fujii Masao wrote: On Fri, Dec 5, 2008 at 12:09 PM, Fujii Masao [EMAIL PROTECTED] wrote: I was expecting you to have walreceiver and startup share an end of WAL address via shared memory, so that startup never tries to read past end. That way we would be able to begin reading a WAL file *before* it was filled. Waiting until a file fills means we still have to have archive_timeout set to ensure we switch regularly. You mean that not pg_standby but startup process waits for the next WAL available? If so, I agree with you in the future. That is, I just think that this is next TODO because there are many problems which we should resolve carefully to achieve it. But, if it's essential for 8.4, I will tackle it. What is your opinion? I'd like to clear up the goal for 8.4. Umm.. on second thought, this feature (continuous recovery without pg_standby) seems to be essential for 8.4. So, I will try it. Sounds good. Perhaps you can share what changed your mind in those 4 hours... Could we start with pictures and some descriptions first, so we know we're on the right track? I foresee no coding issues. My understanding is that we start with a normal log shipping architecture, then we switch into continuous recovery mode. So we do use pg_standby at beginning, but then it gets turned off. Let's look at all of the corner cases also: * standby keeps pace with primary (desired state) * standby falls behind primary * standby restarts to change shmmem settings etc -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep: First Thoughts on Code
On Fri, 2008-12-05 at 12:09 +0900, Fujii Masao wrote: On Thu, Dec 4, 2008 at 6:29 PM, Simon Riggs [EMAIL PROTECTED] wrote: The only sensible settings are synchronous_commit = on, synchronous_replication = on synchronous_commit = on, synchronous_replication = off synchronous_commit = off, synchronous_replication = off This doesn't make any sense: (does it??) synchronous_commit = off, synchronous_replication = on If the standby replies before writing the WAL, that strategy can improve the performance with moderate reliability, and sounds sensible. Do you think it likely that your replication time is consistently and noticeably less than your time-to-disk? If not, you'll wait just as long but be less robust. I guess its possible. On a related thought: presumably we force a sync rep if forceSyncCommit is set? IIRC, MySQL Cluster might use that strategy. Not the most convincing argument I've heard. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Greg Smith [EMAIL PROTECTED] writes: You can just ignore this late night bit of idiocy, or mock me for it as you see fit. Note to other reviewers: if your e-mail client is the sort that bunches a series of text attachments all together, make sure to scroll completely past the first patch in the diff before you pay attention to the rest of it. I'm going to bed. Your email client is doing the right thing. The attachments had the following header on them which controls this: Content-Disposition: inline; I wonder how many email clients let the poster control this header though :( If you post with content-disposition set to attachment instead of inline it should appear as a separate file you can save. Regarding the patches, we could apply the trivial stuff right around the time of the pgindent run, after all the major patches are drained from the queue so it doesn't cause extra conflicts. It would still cause any other pending patches for 8.5 to bitrot but from the sounds of things shouldn't be too hard to fix up. It seems to me we ought to do this regardless of whether we apply the functional changes. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Hi Peter, Have you considered writing a procedural language plugin for C++? C++ can masquerade as C, so I don't think it needs a separate plugin. Just tell PostgreSQL that your user-defined function is C even though you secretly know it is C++. This series of patches is meant to address some of the mechanics of getting C++ code compiled and linked for the PostgreSQL backend environment. At present the build system has no support for languages other than C. To interface PostgreSQL to a C++ tool or library, it's necessary to either hack the PostgreSQL build system, or bypass it and set up your own build system. Either alternative is likely to be non-portable and difficult for others to understand or use. This presents a serious obstacle to contributing the code to the PostgreSQL community or sharing it with others. Because C++ is so similar to C, the PostgreSQL build system can easily provide equal support to both languages. C++ users can then integrate their code easily and portably, and others can share the code with no need to wrestle with jury-rigged makefiles. Nobody should have to figure out autoconf, m4, and gmake unless they want to. The 'c++configure' patch therefore addresses the necessity to find the host platform's C++ compiler; invoke it with appropriate options; link with the C++ runtime library; and initialize the C++ environment. Another obstacle which would not be addressed by a procedural language plugin is the problem of access to the backend's APIs and data structures. C++ can use C declarations directly with no extra wrappers or translation layers such as other languages usually need. However, C++ cannot parse a C header file in which a C++ reserved word is used as an identifier. The 'c++reserved' patch renames some fields in a very few header files so C++ code can interface with the PostgreSQL backend environment to the extent needed for implementing a procedural language, data type, etc. Although tedious, such renaming is by far the most reliable, maintainable and efficient means of exposing the PostgreSQL runtime facilities to C++. As a straightforward renaming, it is a safe change: its completeness and much of its correctness are checked by the C compiler. PostgreSQL supports a lot of extension languages, and none of them require the amount of backend changes that you outline here, because the PL plugin serves as glue. C++ doesn't need glue like those other languages, but it does need just a little help so that it can be used for the same kinds of jobs that C is used for. Those other extension languages, such as plpgsql or plpython, serve a different audience than C or C++. They offer quick development, ease of use, and high-level expressiveness where performance is not the primary concern. C or C++ are chosen when high performance is needed with precise control over data representation and the ability to interoperate directly with almost any language / library / system call / network protocol / etc - notably, PostgreSQL's own tree structures and data types. Thanks for your comments; I hope I've responded adequately. In any case, I welcome further dialogue on these or other topics. Regards, ... kurt Peter Eisentraut wrote: Kurt Harriman wrote: Sometimes people would like to call C++ code in the PostgreSQL backend environment... for example, in user-defined functions, triggers, access methods. And there is sometimes a need for C++ code to call back into PostgreSQL's C functions, such as the SPI interface. Have you considered writing a procedural language plugin for C++? PostgreSQL supports a lot of extension languages, and none of them require the amount of backend changes that you outline here, because the PL plugin serves as glue. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Kurt Harriman [EMAIL PROTECTED] writes: Hi Peter, Have you considered writing a procedural language plugin for C++? C++ can masquerade as C, so I don't think it needs a separate plugin. Just tell PostgreSQL that your user-defined function is C even though you secretly know it is C++. Well one thing that might be useful for a c++ procedural language would be catching C++ exceptions and translating them into ereports which could then be caught in Postgres. That's actually what I thought you had done but I just reread your mail and realized you only handled unhandled exceptions which cause the backend to die. The other way around could be useful too -- catching ereports/elogs within a backend API call from C++ code and throwing a C++ exception. I'm not sure if that's doable though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Kurt Harriman wrote: Have you considered writing a procedural language plugin for C++? C++ can masquerade as C, so I don't think it needs a separate plugin. Just tell PostgreSQL that your user-defined function is C even though you secretly know it is C++. FYI, we have received patches morally equivalent to yours many times over the years, and they have all been rejected. You might want to review the archives about that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
Greg Smith wrote: On Wed, 3 Dec 2008, Bruce Momjian wrote: As the author of the original shell script, which was in /contrib/pg_upgrade, I think the code has grown to the point where it should be reimplemented in something like Perl. Ah, there's the common ancestor I couldn't find. Sheesh, you learn Perl last month, and already you're a zealot. That was fast. Yes, I think that is accurate. ;-) I think the conversion to a more portable and powerful language is a long-term goal, rather than something we have to do now. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
On Thu, Dec 4, 2008 at 9:53 AM, Dmitry Koterov [EMAIL PROTECTED] wrote: Hello. I can successfully ALTER a NON-EMPTY table (ct) which ROWTYPE is used as a column type for another table (dt): CREATE TABLE ct (id INTEGER); CREATE TABLE dt (id INTEGER, c ct); INSERT INTO dt VALUES(1, '(666)'); SELECT * FROM dt; -- (1, '(666)') ALTER TABLE ct ADD COLUMN n INTEGER; SELECT * FROM dt; -- (1, '(666,)') You see, '(666,)' means that the new field is added successfully. But, if I declare ct as a COMPOSITE type (not a table), it is not permitted to ALTER this type (Postgres says that there are dependensies on ct). Why? Because of this there is no reason to ever use 'create type'always use 'create table'. 'alter type' can't add/remove columns anyways. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing DISTINCT with LIMIT
I would tend to think it's worth it myself. I am unfortunately not familiar enough with the postgresql code base to be comfortable to provide a patch. Can I submit this optimization request to some sort of issue tracker or what should I do? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] portability of designated initializers
Euler Taveira de Oliveira wrote: Alvaro Herrera escreveu: I've already modified your patch a bit ... please send your updated patch so I can merge it into mine. However, my changes were also relatively minor. Since Tom wants it to be entirely rewritten then maybe merging minor fixes to it is a waste of time ... Since Alvaro is involved in finishing another patches, I improved the first patch based on discussion. What did I do? Thanks. Sorry for being unable to work on this. (iv) change the *_base_thresh names to *_threshold. I know the names are not appropriated but postgresql.conf use similar ones; Oh, please spell the names in full (e.g. vac_threshold to vacuum_threshold; anl - analyze, etc). I'll try to have a quick look at this version today. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [postgis-devel] CLUSTER in 8.3 on GIST indexes break
Hi Kevin, Yeah I see exactly the same problem on 8.3.5 too, although it seems random - what seems to happen is that sometimes the contents of the temporary table disappears. I've attached a test script which causes the error *some* of the time, although it tends to occur more often just after the server has been restarted. I've been invoking the attached script against a PostgreSQL 8.3.5/PostGIS 1.3.4 installation, and when the bug hits I see the following psql output against a freshly restarted server: postgis13=# \i /tmp/postgis-strange.sql SELECT CREATE INDEX ANALYZE count --- 1 (1 row) CLUSTER ANALYZE count --- 1 (1 row) postgis13=# \i /tmp/postgis-strange.sql psql:/tmp/postgis-strange.sql:2: ERROR: relation tmp already exists psql:/tmp/postgis-strange.sql:3: ERROR: relation tmp_geom_idx already exists ANALYZE count --- 1 (1 row) CLUSTER ANALYZE count --- 0 (1 row) postgis13=# \i /tmp/postgis-strange.sql psql:/tmp/postgis-strange.sql:2: ERROR: relation tmp already exists psql:/tmp/postgis-strange.sql:3: ERROR: relation tmp_geom_idx already exists ANALYZE count --- 0 (1 row) CLUSTER ANALYZE count --- 0 (1 row) So in other words, the contents of the temporary table has just disappeared :( ATB, Mark. -- Mark Cave-Ayland Sirius Corporation - The Open Source Experts http://www.siriusit.co.uk T: +44 870 608 0063 -- Count script create temp table tmp as select st_makepoint(random(), random()) as the_geom from generate_series(1, 1); create index tmp_geom_idx on tmp using gist (the_geom); analyze tmp; select count(*) from tmp; cluster tmp using tmp_geom_idx; analyze tmp; select count(*) from tmp; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [postgis-devel] CLUSTER in 8.3 on GIST indexes break
Mark Cave-Ayland [EMAIL PROTECTED] writes: So in other words, the contents of the temporary table has just disappeared :( Uhm. That rather sucks. I was able to reproduce it too. It seems to happen after I pause for a bit, and not when I run the script in fast succession. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing DISTINCT with LIMIT
tmp [EMAIL PROTECTED] writes: I would tend to think it's worth it myself. I am unfortunately not familiar enough with the postgresql code base to be comfortable to provide a patch. Can I submit this optimization request to some sort of issue tracker or what should I do? You could add it to here -- note that if we decide it isn't worth it it'll just get removed. http://wiki.postgresql.org/wiki/Todo -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/pg_stat_statements 1202
The main benefit is that you can track how EXPLAIN plans change over time. It is not required to output plan *into* some table to be able track it over time. If EXPLAIN returns a table, it is up to you to perform insert into history select * from explain(...). Workflow that does not make sense for me is look at plans generated _into some plan_table_ by other sessions in Oracle. I am 100% sure it really makes sense have some view like pg_execute_plan that will reveal execution plans for currently running queries (see v$sql_plan in Oracle). However, I would stress once again I have no idea what the sense could be in one session explained into plan_table, while the other reads that plan. Does that make sense? Regards, Vladimir Sitnikov
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Kurt Harriman [EMAIL PROTECTED] writes: [ make the backend C++-compilable ] This has been proposed before, and rejected before, and I don't believe the arguments have changed in the least. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Can't start postmaster on -HEAD
Devrim GÜNDÜZ wrote: On Thu, 2008-12-04 at 18:38 +0100, Magnus Hagander wrote: Do the RPM initscript by any chance pass something like '-A ident sameuser' to initdb? If so, that's where the fix needs to go. Initdb defaults to trust... Good catch. So, AFAICS running initdb with -A ident will do the trick. BTW, should I worry about thi the krb5 message below? $ psql -U postgres psql: pg_krb5_init: krb5_cc_get_principal: No credentials cache found FATAL: Ident authentication failed for user postgres Worry, no. But it's on my list to look at making it not happen. It happens because we do kerberos stuff earlier than we know if we're actually going to do a kerberos connection - which we don't do for any other authentication methods.. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Versioned mo file installation
One issue with packaged multiple-version installations (practiced by Debian, Solaris, and soon Fedora?) is that the mo files (translation files) should be in a fixed location like /usr/share/locale, which does not allow PostgreSQL-version specific subdirectories. The Debian packages solve this by appending a version number to the mo files themselves, as can be seen in this patch: http://bazaar.launchpad.net/~pitti/postgresql/debian-8.3/annotate/127?file_id=03gettextdomains.pat-20060925215343-vp3e1xxgtg0tzea6-45 Solaris packaging is currently looking for a solution, and the Fedora initiative might as well? So I figured we could adopt something like the above patch as a built-in solution, as a build option or even by default. Comments? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [patch] pg_upgrade script for 8.3-8.4
Greg Smith napsal(a): On Thu, 4 Dec 2008, Zdenek Kotala wrote: 1) Keep relfileid of toast file same. It is important because toast pointer contains relfileid. Currently script creates fake files with same number to protect postgresql to create new relation with this refileid. It works but by my opinion it is not much robust. I suggest to use following syntax: create table foo (id int) with (relfileid=16544, reltoastid=11655, reltoastidx=16543) But once there's a more core integrated in-place upgrade, as envisioned for 8.4-8,5, this syntax wouldn't been useful for anything anymore, right? Maybe it could be useful also for user who needs to restore database with same filename (replication, recovery...). But I don't know about any other real request for this feature. I understand your distaste for the hack, but it seems a bit extreme to start fiddling with CREATE TABLE and pg_dump* just to implement a work-around for a temporary problem. I think your magic is strong enough for this, as long as the upgrade script does some sanity checks and proceeds cautiously I think we can live with it. I agree that it is temporary solution and does not make sense to create any special temporary hack for it. However, this syntax uses reloption mechanism which is easy to use and modification should be small and maybe someone could use for another purpose. But how you mention it is now solved by script magic. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Versioned mo file installation
Solaris currently stores messages into separate directories like /usr/postgres/8.3/share/locale which solves multiversion problem, but it brings another problem, because gettext on solaris needs extra directory infrastructure. I think debians patch is good approach. Zdenek Peter Eisentraut napsal(a): One issue with packaged multiple-version installations (practiced by Debian, Solaris, and soon Fedora?) is that the mo files (translation files) should be in a fixed location like /usr/share/locale, which does not allow PostgreSQL-version specific subdirectories. The Debian packages solve this by appending a version number to the mo files themselves, as can be seen in this patch: http://bazaar.launchpad.net/~pitti/postgresql/debian-8.3/annotate/127?file_id=03gettextdomains.pat-20060925215343-vp3e1xxgtg0tzea6-45 Solaris packaging is currently looking for a solution, and the Fedora initiative might as well? So I figured we could adopt something like the above patch as a built-in solution, as a build option or even by default. Comments? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Gregory Stark [EMAIL PROTECTED] writes: Well one thing that might be useful for a c++ procedural language would be catching C++ exceptions and translating them into ereports which could then be caught in Postgres. That's actually what I thought you had done but I just reread your mail and realized you only handled unhandled exceptions which cause the backend to die. Well, that's too bad, because fixing the error-handling impedance mismatch is the one thing that's been missing from every previous proposal, and it's the one thing that might actually make C++ in the backend useful rather than a toy. It's possible/likely that this would be easier to do in the context of a PL; that would at least provide a single point at which to catch C++ exceptions and turn them into elogs. The hard part is turning elogs into exceptions so that errors thrown by core backend functions that're called by the C++ code will behave as a C++ programmer would expect. For comparison look at the way that errors are handled in pl/perl etc. The relatively narrow SPI API for calling back into the main backend makes it somewhat sane to convert elogs into Perl errors, though it's less efficient than one could wish. I don't know how to scale that solution up to the point where you could call any random internal backend function, as Kurt seems to be hoping for. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Versioned mo file installation
Peter Eisentraut wrote: http://bazaar.launchpad.net/~pitti/postgresql/debian-8.3/annotate/127?file_id=03gettextdomains.pat-20060925215343-vp3e1xxgtg0tzea6-45 Solaris packaging is currently looking for a solution, and the Fedora initiative might as well? So I figured we could adopt something like the above patch as a built-in solution, as a build option or even by default. I cannot see the patch right now, but I think this is a good idea in principle. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Peter Eisentraut wrote: FYI, we have received patches morally equivalent to yours many times over the years, and they have all been rejected. You might want to review the archives about that. Hi Peter, I went back as far as 2005 in the archives, and found only this thread covering similar territory: * SPI-header-files safe for C++-compiler http://groups.google.com/group/pgsql.patches/browse_frm/thread/6dab9a8134cce102/3c91e40a9e615362?lnk=gstq=rejected+c%2B%2B#3c91e40a9e615362 or http://tinyurl.com/6bjcdq June 2007 That patch appears approximately equivalent to the first two of my patches, c++reserved and c++bookends. The opposing arguments in that thread seem strained, in my opinion, conveying more heat than light. Hey you kids, get off my lawn! The conclusion of the deliberative process wasn't set down in that thread; but evidently the arguments in favor were not sufficiently persuasive: in the end, the patch was not applied. The foremost opposing argument seems to have been that there should be no attempt to alleviate the existing reserved word problem without automatic enforcement to guarantee that never in the future can new occurrences be introduced. But can we not separate the two problems of (1) actual identifiers which prevent C++ compilation today, vs. (2) hypothetical code which someone might submit in the future? The first problem is immediate; the second would only be troublesome if the hypothetical identifier makes it all the way through beta testing into a release. Post #21 in the thread, by Tom Lane on July 4 2007 at 8:05 am, suggests an automated check for non-C++-compilable header files, and highlights the practical difficulties caused by lack of C++ support in the build system. To invoke the C++ compiler at present, typically one would use a hard-wired compiler name with hard-wired flags and paths. My third patch - c++configure - begins to address the need for a portable way to build C++ code, compatible with the way we build C code. The notion of converting all of PostgreSQL to C++ was touched upon. Little would be gained, at the cost of much tedium, so I advise against it. I wouldn't want to redo the old stuff unless there's a clear benefit. My proposal aims to make C++ a practical choice for adding *new* things to PostgreSQL. A topic of great interest is the relationship between C++ exceptions and the PostgreSQL backend's error handling based on setjmp/longjmp. My fourth patch - c++exception - adds a backstop to limit the damage in case a C++ exception is thrown from anywhere in the backend and not caught. The patch converts an uncaught exception to a PostgreSQL FATAL error, so the process can clean itself up and report its failure rather than just silently disappearing. If C++ code doesn't catch its exceptions, that is a programming error, similar to a segment violation or null pointer dereference, and worthy of termination. These four patches aren't meant to create a palace of luxury for C++ programmers. More could be done: more sophisticated error handling could be provided; new/delete could be hooked up to palloc/pfree; templates and class libraries could be written. But C++ programmers can do these things for themselves, if we give them a fighting chance: just take away the roadblocks (primarily the reserved words) and make it easy to compile and link. Regards, ... kurt PS. A few other threads had (at least somewhat) relevant discussion. They're listed below. I didn't find any other patches. I'd appreciate any links or pointers to any other threads which I should look at. * STL problem in stored procedures http://groups.google.com/group/pgsql.general/browse_frm/thread/ee352086139df2bf/400e8133b3e87d74?tvc=1q=stl+problem+in+stored+procedures#400e8133b3e87d74 http://tinyurl.com/5hhf2v October 2005 * C++ - C : Module for converting the WHERE clause to the canonical form with PostgreSQL http://groups.google.com/group/pgsql.hackers/browse_frm/thread/6cb99c3521318653/d6f2b9509cda35c5?lnk=gstq=tom+lane+c%2B%2B#d6f2b9509cda35c5 or http://tinyurl.com/6atqmq January 2006 * PG Extensions: Must be statically linked? http://groups.google.com/group/pgsql.hackers/browse_frm/thread/89d3650c52430186/c63c94680b399827?lnk=gstq=pg+extensions+must+be+statically+linked#c63c94680b399827 or http://tinyurl.com/6q5jdz March 2006 * Writing triggers in C++ http://groups.google.com/group/pgsql.hackers/browse_frm/thread/2a95d656b8add4dd/ded7ff4ce06ae456?lnk=gstq=writing+triggers+in+c%2B%2B#ded7ff4ce06ae456 or http://tinyurl.com/6kx8ba February 2007 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Versioned mo file installation
On Fri, 2008-12-05 at 16:20 +0200, Peter Eisentraut wrote: Solaris packaging is currently looking for a solution, and the Fedora initiative might as well? So I figured we could adopt something like the above patch as a built-in solution, as a build option or even by default. Comments? Sounds good to me. Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Simple postgresql.conf wizard
Kevin Grittner [EMAIL PROTECTED] writes: One more data point to try to help. While the jump from a default_statistics_target from 10 to 1000 resulted in a plan time increase for a common query from 50 ms to 310 ms, at a target of 50 the plan time was 53 ms. Analyze time was 7.2 minutes and 18.5 minutes for targets of 10 and 50. This is an 842 GB database on an 8 processor (3.5 GHz Xeon) machine with 64 GB RAM running (soon to be updated) PostgreSQL 8.2.7. So my half-finished DBT3 tests showed hardly any change in planning time for default_statistics_targets 1000 and even Kevin Grittner's query which is the worst real example posted so far only went from 50ms to 310ms. So I started thinking perhaps substantially larger values might not have much effect on planning at all. (Actual data is a lot more convincing than simple assertions!). Kevin's query was an OLTP query so 300ms is still way too much -- 300ms is on the high end for OLTP response times for query *execution*. But seeing the intermediate values would be interesting. So I wondered what the worst-case would be for a synthetic case designed to exercise the planner severely. This would also be useful for optimizing the planner under gprof, though I suspect the hot spots are pretty obvious even without empirical data. So anyways, here's a script to create a table with a 75k pg_statistic record. And a worst-case query where the plan time goes from 34ms to 1.2s for histogram sizes between 10 and 1,000. Looking at eqjoinsel I think it could be improved algorithmically if we keep the mcv list in sorted order, even if it's just binary sorted order. But I'm not sure what else uses those values and whether the current ordering is significant. I'm also not sure it's the only O(n^2) algorithm there and there's no algorithmic gain unless they're all knocked down. Incidentally this timing is with the 75kB toasted arrays in shared buffers because the table has just been analyzed. If it was on a busy system then just planning the query could involve 75kB of I/O which is what I believe was happening to me way back when I last observed super-long plan times. postgres=# create table tk as select random()::text||random()::text||random()::text||random()::text||random()::text||random()::text as r from generate_series(1,1000); postgres=# insert into tk (select * from tk); postgres=# insert into tk (select * from tk); postgres=# insert into tk (select random()::text||random()::text||random()::text||random()::text||random()::text||random()::text as r from generate_series(1,2000)); postgres=# alter table tk alter r set statistics 1000; postgres=# analyze tk; postgres=# select pg_column_size(stavalues1) from pg_statistic where starelid = 'tk'::regclass; pg_column_size 75484 (1 row) postgres=# explain select count(*) from (select * from tk as k, tk as l,tk as m,tk as n,tk as o,tk as p where k.r=l.r and k.r=m.r and k.r=n.r and k.r=o.r and k.r=p.r) as x; QUERY PLAN - Aggregate (cost=41358.14..41358.15 rows=1 width=0) - Merge Join (cost=3213.13..37713.13 rows=1458000 width=0) Merge Cond: (k.r = l.r) - Merge Join (cost=2677.61..14092.61 rows=486000 width=510) Merge Cond: (k.r = m.r) - Merge Join (cost=2142.09..5862.09 rows=162000 width=408) Merge Cond: (k.r = n.r) - Merge Join (cost=1606.57..2761.57 rows=54000 width=306) Merge Cond: (k.r = o.r) - Merge Join (cost=1071.04..1371.04 rows=18000 width=204) Merge Cond: (k.r = p.r) - Sort (cost=535.52..550.52 rows=6000 width=102) Sort Key: k.r - Seq Scan on tk k (cost=0.00..159.00 rows=6000 width=102) - Sort (cost=535.52..550.52 rows=6000 width=102) Sort Key: p.r - Seq Scan on tk p (cost=0.00..159.00 rows=6000 width=102) - Sort (cost=535.52..550.52 rows=6000 width=102) Sort Key: o.r - Seq Scan on tk o (cost=0.00..159.00 rows=6000 width=102) - Sort (cost=535.52..550.52 rows=6000 width=102) Sort Key: n.r - Seq Scan on tk n (cost=0.00..159.00 rows=6000 width=102) - Sort (cost=535.52..550.52 rows=6000 width=102) Sort Key: m.r - Seq Scan on tk m (cost=0.00..159.00 rows=6000 width=102) - Sort (cost=535.52..550.52 rows=6000
Re: [HACKERS] [postgis-devel] CLUSTER in 8.3 on GIST indexes break
Gregory Stark wrote: Uhm. That rather sucks. I was able to reproduce it too. It seems to happen after I pause for a bit, and not when I run the script in fast succession. Thanks for the verification Greg. I'm wondering if the GiST part is a red herring, and in fact it is related to some bizarre interaction between CLUSTER/VACUUM/autovacuum? ATB, Mark. -- Mark Cave-Ayland Sirius Corporation - The Open Source Experts http://www.siriusit.co.uk T: +44 870 608 0063 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Gregory Stark [EMAIL PROTECTED] wrote: Incidentally this timing is with the 75kB toasted arrays in shared buffers because the table has just been analyzed. If it was on a busy system then just planning the query could involve 75kB of I/O which is what I believe was happening to me way back when I last observed super-long plan times. I'm pretty sure I saw that in some of my tests with larger targets. With a large database and a large target, some of the tables' statistics apparently weren't still cached the first time I planned the query, and I got an extremely long plan time on the first attempt, and then it settled in within a pretty narrow range on repeated plans. I discarded the initial plan time as immaterial for our purposes because a query that's run 300,000 times per day is probably going to keep its statistics in cache most of the time. I was looking at trying to modify the perl script from Robert Haas to run my query at a wide range of target values, collecting analyze and plan times at each. Now that you have an easy-to-create synthetic example, is this still worth it, considering that it would be on 8.2? (If we wait a week or two, I could probably do it on 8.3.) We do have gprof on these systems, although I'd need advice on how to use it. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
Tom Lane wrote: Kurt Harriman [EMAIL PROTECTED] writes: [ make the backend C++-compilable ] This has been proposed before, and rejected before, and I don't believe the arguments have changed in the least. Hi Tom, Of the series of four patches, the first two (c++reserved and c++bookends) appear much the same as Jacob Rief's patch submitted in June 2007. http://tinyurl.com/6bjcdq (Incidentally, I only just now found that thread. I hadn't seen it earlier because I hadn't searched the now-defunct pgsql-patches list.) The third patch (c++configure) addresses a problem which I have not seen discussed before: There needs to be a portable way to compile and link C++ code. As it stands, the third patch depends upon the first two, because the third one can optionally compile main.c as C++. Since main.c includes some header files in which C++ reserved words are used as identifiers, it cannot be compiled as C++ without fixing at least a subset of those identifiers. However, if it is decided that the identifiers cannot be changed, then I could revise the c++configure and c++exception patches to remove the dependency. Of course it can be expected that, once or twice a year, some starry-eyed newcomer will repeat the plea for the reserved words to be fixed, until you succumb or add it to the FAQ. If a C++ programmer needs ereport(ERROR)s to be recast as C++ exceptions, I propose they can handle that in their own code without special provisions being made in PostgreSQL code. Therefore, I claim it does not need to be addressed in this series of patches. It is a separate issue. However, PostgreSQL code should provide a last-resort exception handler as a backstop against C++ programming errors. That is the purpose of the fourth patch (c++exception). C++ programmers should catch their own exceptions, but if they let one get away, PostgresMain should try to make sure the shared segment isn't left in a corrupt state. In other words, PostgresMain's defense against uncaught C++ exceptions should be approximately equivalent to its existing defense against SIGSEGVs, null pointer dereferencing errors, and similar faults in C. Regards, ... kurt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [postgis-devel] CLUSTER in 8.3 on GIST indexes break
Robert W. Burgholzer [EMAIL PROTECTED] writes: FWIW, I have experienced some oddities in performing SELECT statements after restarting on an 8.2 system, whereby I occasionally would get a ton of duplicate records when I would do a select statement (my assumption is that they are deleted tuples being returned). If I executed the same select statement again, I would get the correct number of records. Sounds like this item fixed in 8.2.10: # Fix possible duplicate output of tuples during a GiST index scan (Teodor) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Gregory Stark [EMAIL PROTECTED] wrote: Incidentally, here's a graph of the explain time for that plan. It looks pretty linear to me Except for that sweet spot between 50 and 100. Any idea what's up with that? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Versioned mo file installation
Peter Eisentraut wrote: The Debian packages solve this by appending a version number to the mo files themselves, as can be seen in this patch: http://bazaar.launchpad.net/~pitti/postgresql/debian-8.3/annotate/127?file_id=03gettextdomains.pat-20060925215343-vp3e1xxgtg0tzea6-45 Of course, as is it's not acceptable; the version number should be obtained from PG_VERSION or some such. I am not sure the libpq5 versioning is best; maybe we don't change the API but we could certainly change error messages between versions. I think we should keep it as libpq-8.3, like the rest. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default statistics target testing (was: Simple postgresql.conf wizard)
Looks like Robert accidentally answered my question about what version his results were from off-list. Here's his update: --- Unfortunately it was 8.2.9, as I realized halfway into the run. Here are the results from a CVS HEAD checkout last night. *** Query planning times q1 (the complex one): 367 ms with default_statistics_target = 10, 379 ms with default_statistics_target = 1000 (3% slowdown) other 6 queries: 18.48 ms with default_statistics_target = 10, 21.75 ms with default_statistics_target = 1000 (18% slowdown) comparing to 8.2.9 results: - q1 was 32% slower with dst = 10, 33% slower with dst = 1000 - other queries were 19% slower with dst = 10, 25% slower with dst = 1000 *** ANALYZE times 10 4.283007 20 6.741417 30 9.157209 40 11.445861 50 13.466374 60 15.402897 70 17.365484 80 19.243842 90 20.905635 100 22.643275 150 29.056517 200 31.878688 250 34.240638 300 36.996304 350 39.921178 400 42.443728 450 44.69833 500 47.553414 550 50.305143 600 52.799597 650 54.961691 700 57.117117 750 58.847345 800 61.60112 850 63.020136 900 65.858213 950 68.072528 100069.861321 comparing to 8.2.9 results: - 3x faster with default_statistics_target = 10 - 4.25x faster with default_statistics_target = 1000 ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
All, I'm thinking that default_statistics_target is disputable enough that we want to move discussion of it to pgsql-performance, and for version 0.1 of the tuning wizard, exclude it. -- --Josh Josh Berkus PostgreSQL San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default statistics target testing (was: Simple postgresql.conf wizard)
Robert, On Fri, Dec 5, 2008 at 7:24 PM, Greg Smith [EMAIL PROTECTED] wrote: comparing to 8.2.9 results: - q1 was 32% slower with dst = 10, 33% slower with dst = 1000 - other queries were 19% slower with dst = 10, 25% slower with dst = 1000 You mean that HEAD is slower than 8.2.9 or I don't understand correctly what you wrote? -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Versioned mo file installation
Hi Alvaro, Alvaro Herrera [2008-12-05 14:06 -0300]: Of course, as is it's not acceptable; the version number should be obtained from PG_VERSION or some such. Right, it has actually been on my long-term wishlist to replace that patch with an autoconfiscated one with a non-hardcoded version number. I just didn't do it yet because it was generally rejected when I proposed it upstream some years ago. I am not sure the libpq5 versioning is best; maybe we don't change the API but we could certainly change error messages between versions. I think we should keep it as libpq-8.3, like the rest. Doesn't make much of a difference indeed, I just made it match the package name. Also, libpq5 built from -8.3 is used with postgresql-8.2 as well. Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default statistics target testing (was: Simple postgresql.conf wizard)
On Fri, Dec 5, 2008 at 7:24 PM, Greg Smith [EMAIL PROTECTED] wrote: comparing to 8.2.9 results: - q1 was 32% slower with dst = 10, 33% slower with dst = 1000 - other queries were 19% slower with dst = 10, 25% slower with dst = 1000 You mean that HEAD is slower than 8.2.9 or I don't understand correctly what you wrote? Correct. As compared with 8.2.9, ANALYZE was substantially faster, but query planning was significantly slower. Thanks also to Greg for reposting my emails. I didn't even realize I hadn't sent them to the list. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default statistics target testing (was: Simple postgresql.conf wizard)
On Fri, Dec 5, 2008 at 8:11 PM, Robert Haas [EMAIL PROTECTED] wrote: Correct. As compared with 8.2.9, ANALYZE was substantially faster, but query planning was significantly slower. Thanks also to Greg for reposting my emails. I didn't even realize I hadn't sent them to the list. Any chance you could do the same test with a 8.3? It could be interesting to see if it's a HEAD thing or if the slow down was introduced in 8.3. -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default statistics target testing (was: Simple postgresql.conf wizard)
Thanks also to Greg for reposting my emails. I didn't even realize I hadn't sent them to the list. Any chance you could do the same test with a 8.3? It could be interesting to see if it's a HEAD thing or if the slow down was introduced in 8.3. Somehow I knew you were going to ask that. :-) Yeah, I'll try to do it tonight, or over the weekend some time. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Thanks for putting out pgtune - it's a sorely needed tool. I had a chance to look over the source code and have a few comments, mostly about python specific coding conventions. - The windows specific try block ( line 16 ) raises a ValueError vs ImportError on my debian machine. Maybe it would be more appropriate to explicitly test platform.system()==Windows? - from ctypes import * ( 18 ) makes the block difficult to read and pollutes the namespace. - on line 45, the try block should probably catch exceptions derived from Exception ( to avoid catching SystemExit and KeyboardInterrupt errors ). ie, except Exception: return None. Also, printing out the expection in debug mode would probably be a good idea ( ie except Exception, e: print e\ return None ) - all classes ( 58, 135, 205 ) are 'old-style' classes. I dont see any reason to use classic classes ( unless Python 2.1 is a support goal? ) To make classes 'new style' classes ( http://www.python.org/doc/2.5.2/ref/node33.html ) they should inherit object. i.e. class PGConfigLine(object): - The doc strings ( 59, 136, 206 ) don't follow standard conventions, described here http://www.python.org/dev/peps/pep-0257/. - Functions also support doc strings ( 342, 351, etc. ) - Tuple unpacking doesn't require the tuple boundaries ( 446 and others ). ie, options, args = ReadOptions() works. This is more of a style comment about the 'Java-ish interface' ( line 112 ), feel free to ignore it. overloading __str__ and __repr__ are the python ways to return string representations of an object. ie, instead of toSting use __str__ and then ( on 197 ) print l or print str(l) instead of print l.toString() for the other methods ( getValue, getLineNumber, isSetting ) I'm assuming you didnt call the attributes directly because you didnt want them to be accidently overwritten. Have you considered the use of properties ( http://www.python.org/download/releases/2.2.3/descrintro/#property )? Also, it would be more clear to mark attributes as private ( i.e. _name or __name, _readable, _lineNumber, _setsParameter ) if you dont want them to be accessed directly. Hope my comments are useful! Thanks again for writing this. -Nathan P.S. I'd be happy to officially review this if it gets to that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CLUSTER in 8.3.5 on GIST indexes loses all rows
FYI, this is not limited to PostGIS GIST, it appears to be all GIST (gid is an integer column and I have loaded btree_gist): pramsey=# create table ttt as select * from counties; SELECT pramsey=# create index gid_bix on ttt using gist (gid); CREATE INDEX pramsey=# select count(*) from ttt; count --- 3141 (1 row) pramsey=# cluster ttt using gid_bix; CLUSTER pramsey=# select count(*) from ttt; count --- 0 (1 row) pramsey=# select version(); version -- PostgreSQL 8.3.5 on i386-apple-darwin9.5.0, compiled by GCC i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465) (1 row) On Fri, Dec 5, 2008 at 6:05 AM, Gregory Stark [EMAIL PROTECTED] wrote: Mark Cave-Ayland [EMAIL PROTECTED] writes: So in other words, the contents of the temporary table has just disappeared :( Uhm. That rather sucks. I was able to reproduce it too. It seems to happen after I pause for a bit, and not when I run the script in fast succession. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PLUGINS Functionlity in Win32 build scripts
I've been assigned to review this patch, but I confess I'm a little murky on what problem it's trying to solve. Can you explain what I need to do to recreate the problem? There's also this comment on the Wiki (not sure why it wasn't posted to the mailing list...): Dave Page says: This doesn't work with the edb-debugger plugin, which is the only such plugin around AFAIK. It needs to ignore comments on the PLUGINS line, and handle multiple targets (plugin_debugger, pldbgapi, targetinfo etc). Not sure if we want that complexity though. Any comments? Since there has been no response to this email, I am going to move this patch to the Rejected section on the commitfest Wiki. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] new libpq SSL connection option
Who anyone be opposed to ssldir = path as a connection option? Currently, there is no way to change the homedir method ~/.postgresql ... or am I missing something? I am willing to supply a patch. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new libpq SSL connection option
On Fri, Dec 5, 2008 at 13:58, Andrew Chernow [EMAIL PROTECTED] wrote: Who anyone be opposed to ssldir = path as a connection option? Currently, there is no way to change the homedir method ~/.postgresql ... or am I missing something? I am willing to supply a patch. You mean something like the http://archives.postgresql.org/message-id/[EMAIL PROTECTED] ? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new libpq SSL connection option
Alex Hunsaker wrote: On Fri, Dec 5, 2008 at 13:58, Andrew Chernow [EMAIL PROTECTED] wrote: Who anyone be opposed to ssldir = path as a connection option? Currently, there is no way to change the homedir method ~/.postgresql ... or am I missing something? I am willing to supply a patch. You mean something like the http://archives.postgresql.org/message-id/[EMAIL PROTECTED] ? yes, excately like that; apparently missed it. What is the status of that patch? I see it was left in pending review .. is the fest is over? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new libpq SSL connection option
On Fri, Dec 5, 2008 at 14:22, Andrew Chernow [EMAIL PROTECTED] wrote: Alex Hunsaker wrote: On Fri, Dec 5, 2008 at 13:58, Andrew Chernow [EMAIL PROTECTED] wrote: Who anyone be opposed to ssldir = path as a connection option? Currently, there is no way to change the homedir method ~/.postgresql ... or am I missing something? I am willing to supply a patch. You mean something like the http://archives.postgresql.org/message-id/[EMAIL PROTECTED] ? yes, excately like that; apparently missed it. What is the status of that patch? I see it was left in pending review .. is the fest is over? I think all that is left is changing PGROOTCERT to PGSSLROOTCERT, agreeing to IFDEF the params out or not oh and this little bit: Magnus Hagander escribió: On Fri, Aug 1, 2008 at 13:31, Alvaro Herrera alvherre(at)commandprompt(dot)com wrote: Something that's bothering me is that PGSSLKEY is inconsistent with the sslkey conninfo parameter. PGSSLKEY specifies an engine (basically a driver for specialized hardware AFAICT) from which the key is to be loaded, but sslkey is a simple filename. This means that there's no way to load a key from hardware if you want to specify it per connection. Not that I have any such hardware, but it looks bogus. I think the above consideration needs some discussion too. Committing it as-is doesn't seem OK because you can't change it later -- it's user-visible. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
Hitoshi Harada wrote: I tested the spool performance with David's earlier bigtable: CREATE TABLE bigtable ( id SERIAL NOT NULL PRIMARY KEY, timestamp TIMESTAMP NOT NULL ); -- about 383MB of data INSERT INTO bigtable (timestamp) SELECT NOW() + (CAST(RANDOM() * 10 AS INT) || ' secs')::INTERVAL FROM generate_series(1,1000); CREATE INDEX bigtable_timestamp_idx ON bigtable (timestamp); VACUUM ANALYZE bigtable; sample=# SELECT COUNT(*) FROM bigtable; count -- 1000 (1 row) sample=# SELECT LEAD(timestamp) OVER (ORDER BY id) FROM bigtable LIMIT 1; lead 2008-12-02 00:15:10.288461 (1 row) sample=# EXPLAIN ANALYZE SELECT LEAD(timestamp) OVER (ORDER BY id) FROM bigtable LIMIT 1; QUERY PLAN -- --- Limit (cost=0.00..0.04 rows=1 width=12) (actual time=0.038..0.039 rows=1 loops=1) - Window (cost=0.00..386612.13 rows=1000 width=12) (actual time=0.036..0.036 rows=1 loops=1) - Index Scan using bigtable_pkey on bigtable (cost=0.00..286612.13 rows=1000 w idth=12) (actual time=0.018..0.021 rows=2 loops=1) Total runtime: 0.071 ms (4 rows) shows quite good result. Great work. Amazing improvement! Old patch: david=# select timestamp,lag(timestamp,1) over (order by id) from bigtable order by id limit 1; timestamp | lag +- 2008-11-10 21:55:16.498458 | (1 row) Time: 105205.055 ms New patch: david=# select timestamp,lag(timestamp,1) over (order by id) from bigtable order by id limit 1; timestamp | lag +- 2008-12-04 22:05:22.687975 | (1 row) Time: 1.640 ms The following query works on my build: SELECT depname,SUM(SUM(salary)) OVER (ORDER BY depname) FROM empsalary GROUP BY depname; ERROR: variable not found in subplan target list This works fine on my build now too. Now, I am thinking about refactoring around aggregate common code, and renaming WFunc to WinFunc, which leads pg_proc.proiswfunc be pg_proc.proiswinfunc and so on if no objections come. I've spent last night and tonight trying to break the patch and I've not managed it. I spent 2 and a half hours on the train last night reading over the patch mainly for my own interest. I also went over the documentation and I have a few suggestions for improvement: My modifications to the lead and lag syntax can be improved. Currently the optional parameters make it look like DEFAULT can be specified without OFFSET. This is not the case: + typeany, [integer], [any]/type Should be: + typeany [,integer [,any] ]/type And: + lag(replaceable class=parametervalue/replaceable, [replaceable + class=parameteroffset/replaceable], [replaceable + class=parameterdefault/replaceable]) Should be: + lag(replaceable class=parametervalue/replaceable [, replaceable + class=parameteroffset/replaceable [,replaceable + class=parameterdefault/replaceable] ]) Same for LEAD() +para + After literalWHERE/ and literalGROUP BY/ process, + rows might be windowed table, using the literalWINDOW/ + clause. +/para I think I know what you mean here. My re-write seems to have turned the sentence into a paragraph. Please tell me if I've assumed the meaning wrongly: After the literalWHERE/, literalGROUP BY/ and literalHAVING/ clauses one or more literalWINDOW/ clauses can be specified. This will allow window functions to be specified in the literalSELECT/ clause. These window functions can make use of the literalWINDOW/ clauses by making reference to the alias name of the window rather than explicitly specifying the properties of the window in each literalOVER/ clause. +para + Another expample shows different capability of window functions + from above. Small typo: example instead of eapample +para + The same window definitions can be named and put togather into one + definition using xref linkend=queries-window. Small typo: together instead of togather. +para + Window functions doesn't accept DISTINCT and ALL syntax, even though + the function is an aggregate function. +/para Small grammar error: doesn't should be replaced with don't. Or perhaps change to: Window functions, unlike normal aggregate functions, do not allow DISTINCT or ALL to be used within the function argument list. + Window functions are not placed in any of GROUP BY, HAVING and + WHERE clauses, which process values before any of the windows. If + there is need to qualify rows by the result of window functions, + whole of the query must be nested and append WHERE clause outer of + the current query. I think this one maybe needs an example to back it up. It's quite an
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
Hitoshi Harada Wrote: 2008/12/3 Hitoshi Harada [EMAIL PROTECTED]: I am randomly trying some issues instead of agg common code (which I now doubt if it's worth sharing the code), so tell me if you're restarting your hack again. I'll send the whole patch. Attached is the updated patch, including: - performance tuning up for large data sets - move find_wfunc to optimizer/util/clauses.c - rename WFunc to WindowFunc - rename WinDef to WindowDef - rename wfunc-pure_agg to winagg - remove winstate-tail_ptr - fix WinFrameGetArg in case that there are more than one peer, add relevant test - duplicate GetAggInitVal(), not sharing aggregate routines with nodeAgg.c I believe the remaining work is only to optimze row_number()/rank() cases to trim tuplestore like window aggregates. This will be done by providing some kind of way for each window functions to tell Window node that it doesn't require backward_scan. It's been a long time since the CommitFest started. This patch has come a long way in that time. We've seen various performance improvements and many fixes where the patch was not matching the standard. We've also seen quite a big change in the window buffering technique which is showing amazing performance improvements in certain queries. I've spent many hours reading the standard and comparing the results from this patch against other RDBMS' that support window functions. I wonder if we're the first to implement NTH_VALUE()?. The patch, in my opinion is getting very close to being committable. The only outstanding issues; Please correct me where I'm wrong or have omitted something. * Fix still required for rank_up. Code still has a FIXME. * ROW_NUMBER() and RANK() performance to be looked into. * Slight changes required in documentation (my previous email). * Final code read by someone reviewing the code. I've also spent many hours trying to break this patch and in previous versions I've managed it. Last night I spent a few hours again testing this new patch and did not managed to find anything wrong. We're getting close to the time where the community can test further by committing this patch. I'll make a reference to this post in the CommitFest list for any non-followers of this thread. David. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Fri, 5 Dec 2008, Nathan Boley wrote: - all classes ( 58, 135, 205 ) are 'old-style' classes. I dont see any reason to use classic classes ( unless Python 2.1 is a support goal? ) I'm not targeting anything older then 2.4, as that's the oldest version I have installed anywhere. 2.4 is still an important target because it's the version that comes with the very popular RHEL4. I've worked on some SuSE 9 boxes that only have 2.3 as recently as last year, but I consider that a legacy version I don't care much about mainly because the kinds of programs I'm usually writing tend to rely heavily on using subprocess, added in 2.4. The answers to all of your other points is that I'm relatively new to Python and just didn't know any of that. I'll convert to new style classes, use __str__ properly, and switch all the getters to use properties now that you've pointed me toward those. Improving the rudimentary doc strings was already on my list, that's not a major target though because this project has its own documentation standards and I can't do anything useful with the output from pydoc. I'm only interested in documenting things about the source code itself in there, anything user-related needs to go in the official docs. I'd be happy to officially review this if it gets to that. Just added you to the wiki in that role. I should have a first version that's of commit candidate quality ready to go in the next week. If you could make a second pass then and see how I did implementing your suggestions, that would be great. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Fri, 2008-12-05 at 17:27 -0500, Greg Smith wrote: On Fri, 5 Dec 2008, Nathan Boley wrote: - all classes ( 58, 135, 205 ) are 'old-style' classes. I dont see any reason to use classic classes ( unless Python 2.1 is a support goal? ) I'm not targeting anything older then 2.4, as that's the oldest version I have installed anywhere. 2.4 is still an important target because it's the version that comes with the very popular RHEL4. And RHEL5 Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
On Friday 05 December 2008 10:45, Kurt Harriman wrote: Tom Lane wrote: Kurt Harriman [EMAIL PROTECTED] writes: [ make the backend C++-compilable ] I tested applying this patch to CVS HEAD today and compiling with --enable-cplusplus with gcc 4.2: $ ldd postmaster ... libstdc++.so.6 = /usr/lib/libstdc++.so.6 (0xb7bf9000) ... Then I ran pgbench and played with a table with a UUID column. Performance was great. (I first mistakenly applied it against a not-up-to-date source-tree --- something from mid-September --- and that ended up not compiling.) I still have not tried this with my own C++ code, but it seems to have less impact on the build process than some might have feared. -- David Lee Lambert ... Software Developer Cell phone: +1 586-873-8813 ; alt. email [EMAIL PROTECTED] or [EMAIL PROTECTED] GPG key at http://www.lmert.com/keyring.txt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hint bits vs. OS readahead
While waiting for a large restore to complete (and investigating why parts of it were so slow), I came across this scenario. This isn't quite the same as some previous discussion of hint bits, but I thought it was something that could probably be taken into account in future. This also may be relevent to the tuplestore discussion. The environment: pg 8.3.5 on FreeBSD/amd64 7.1-prerelease; 32GB RAM; 16 cores of 2.93GHz Xeon 7350; 4x300GB 15krpm SAS data drives in software RAID10. shared_buffers=1700MB, maintenance_work_mem=1GB The scenario: pg_restore of a dump containing a large partitioned table (a dozen partitions of ~10GB each). The actual loading of the data proceeds as expected, the interesting part is the creation of indexes afterwards. Watching the progress of the backend, a large proportion of the time is taken up by the heap scan to retrieve the data. The problem is, of course, that the backend settles down into an access pattern like this: lseek(0x64,0x3ef7c000,SEEK_SET) read(0x64,0x864123340,0x2000) = 8192/0x2000 lseek(0x64,0x3ef3e000,SEEK_SET) write(0x64,0x864125340,0x2000) = 8192/0x2000 where fd 0x64 is the table heap file; the read is obvious, the write is caused by writing a previously hinted page back to disk when the backend wants to reuse the buffer. Notice that this write is happening in the same backend (and on the same fd). At least on unpatched FreeBSD this access pattern destroys OS-level readahead, though lower-level readahead on the actual disk drives themselves hides this fact to a significant extent (each read() call forces a SCSI transaction, but this transaction completes quite quickly due to read caching on the drive). In order to test how bad the effect was, I patched FreeBSD to use separate sequential-behaviour tracking for reads and writes (this patch turns out to be trivial, affecting only a couple of dozen lines). The effect was fairly dramatic; the total time taken for CREATE INDEX was cut by a factor of slightly better than 2 (typically from ~700 seconds per partition to ~320 seconds on my data). [for the patch see http://www.rhodiumtoad.org.uk/junk/seq.patch.txt ] The obvious question is whether this is something which should be left as the OS'es problem, or whether it would be worth having pg do some special handling of file opens to distinguish read and write accesses, or sequential from random accesses when both are likely to be happening at the same time. I've so far had conflicting answers about how well Linux handles this case (and not being a Linux user I have no easy way to test it myself). -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default statistics target testing (was: Simple postgresql.conf wizard)
On Fri, Dec 5, 2008 at 2:16 PM, Guillaume Smet [EMAIL PROTECTED] wrote: On Fri, Dec 5, 2008 at 8:11 PM, Robert Haas [EMAIL PROTECTED] wrote: Correct. As compared with 8.2.9, ANALYZE was substantially faster, but query planning was significantly slower. Thanks also to Greg for reposting my emails. I didn't even realize I hadn't sent them to the list. Any chance you could do the same test with a 8.3? It could be interesting to see if it's a HEAD thing or if the slow down was introduced in 8.3. OK, here are the results from 8.2.9, 8.3.5, and CVS HEAD. A couple of these numbers are slightly different than the ones I posted before due to, uh, me having typo'd my awk script last time. However, the basic picture remains unchanged. 8.3.5 is very similar to CVS HEAD; 8.2.9 plans faster but analyzes more slowly. Q1 CVS HEAD 12/5/2008 default_statistics_target=10 367 ms CVS HEAD 12/5/2008 default_statistics_target=1000 380 ms 8.3.5 default_statistics_target=10 367 ms 8.3.5 default_statistics_target=1000 379 ms 8.2.9 default_statistics_target=10 279 ms 8.2.9 default_statistics_target=1000 285 ms Average of Q2-Q7 CVS HEAD 12/5/2008 default_statistics_target=10 18.14 ms CVS HEAD 12/5/2008 default_statistics_target=1000 21.75 ms 8.3.5 default_statistics_target=10 18.26 ms 8.3.5 default_statistics_target=1000 21.35 ms 8.2.9 default_statistics_target=10 15.47 ms 8.2.9 default_statistics_target=1000 17.42 ms ANALYZE CVS HEAD 12/5/2008 default_statistics_target=10 4.283007 CVS HEAD 12/5/2008 default_statistics_target=1000 69.861321 8.3.5 default_statistics_target=10 4.052358 8.3.5 default_statistics_target=1000 65.12919 8.2.9 default_statistics_target=10 13.030161 8.2.9 default_statistics_target=1000 297.099143 Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Looking at eqjoinsel I think it could be improved algorithmically if we keep the mcv list in sorted order, even if it's just binary sorted order. But I'm not sure what else uses those values and whether the current ordering is significant. I'm also not sure it's the only O(n^2) algorithm there and there's no algorithmic gain unless they're all knocked down. The current code seems to be trying to handle pathological cases where: (1) the operator for which it is invoked doesn't really represent equality and/or (2) the type has an equality operator but no comparison operator. Those are important cases, but maybe we could create an alternative version for the fairly common situation where neither holds? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default statistics target testing (was: Simple postgresql.conf wizard)
On Friday 05 December 2008 00:05:34 Robert Haas wrote: On Thu, Nov 27, 2008 at 6:46 PM, Gregory Stark [EMAIL PROTECTED] wrote: ANALYZE with default_statistics_target set to 10 takes 13 s. With 100, 92 s. With 1000, 289 s. That is interesting. It would also be interesting to total up the time it takes to run EXPLAIN (without ANALYZE) for a large number of queries. I wonder if we'd see anything dramatically different using PREPARE... OK, I did this. I actually tried 10 .. 100 in increments of 10 and then 100 ... 1000 in increments of 50, for 7 different queries of varying complexity (but all generally similar, including all of them having LIMIT 100 as is typical for this database). I planned each query 100 times with each default_statistics_target. The results were somewhat underwhelming. The one thing this test seems to overlook is at what point do we see diminshing returns from increasing dst. I think the way to do this would be to plot dst setting vs. query time; Robert, do you think you could modify your test to measure prepare time and then execute time over a series of runs? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
On Friday 05 December 2008 09:51:50 Kurt Harriman wrote: Peter Eisentraut wrote: FYI, we have received patches morally equivalent to yours many times over the years, and they have all been rejected. You might want to review the archives about that. Hi Peter, I went back as far as 2005 in the archives, and found only this thread covering similar territory: snip The foremost opposing argument seems to have been that there should be no attempt to alleviate the existing reserved word problem without automatic enforcement to guarantee that never in the future can new occurrences be introduced. But can we not separate the two problems of (1) actual identifiers which prevent C++ compilation today, vs. (2) hypothetical code which someone might submit in the future? The first problem is immediate; the second would only be troublesome if the hypothetical identifier makes it all the way through beta testing into a release. Actually, given your configure changes, istm a buildfarm member compiling with --enablecplusplus would prevent any such issue from getting to far. snip PS. A few other threads had (at least somewhat) relevant discussion. They're listed below. I didn't find any other patches. I'd appreciate any links or pointers to any other threads which I should look at. Might I suggest you collect all of these various arguments (both for and against) and patches into a wiki page on the developers wiki? Also, I've no real experience in masquerading c++ as c, but the main concern I would have is possible imcompatabilities that might be introduced between postgresql's compiled with c++ and those compiled in c. I'm not sure there should be any, but maybe someone with more experience in this area might have ideas on what to watch out for? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default statistics target testing (was: Simple postgresql.conf wizard)
That is interesting. It would also be interesting to total up the time it takes to run EXPLAIN (without ANALYZE) for a large number of queries. I wonder if we'd see anything dramatically different using PREPARE... Well... the point here is to measure planning time. I would think that EXPLAIN would be the best way to get that information without confounding factors. OK, I did this. I actually tried 10 .. 100 in increments of 10 and then 100 ... 1000 in increments of 50, for 7 different queries of varying complexity (but all generally similar, including all of them having LIMIT 100 as is typical for this database). I planned each query 100 times with each default_statistics_target. The results were somewhat underwhelming. The one thing this test seems to overlook is at what point do we see diminshing returns from increasing dst. I think the way to do this would be to plot dst setting vs. query time; Robert, do you think you could modify your test to measure prepare time and then execute time over a series of runs? I did some previous testing on query #1 where I determined that it runs just as fast with default_statistics_target=1 (no, that's not a typo) as default_statistics_target=1000. The plan is stable down to values in the 5-7 range; below that it changes but not appreciably for the worse. I could test the other queries but I suspect the results are similar because the tables are small and should be well-modelled even when the MCV and histogram sizes are small. The point here is to figure out how much we're paying in additional planning time in the worst-case scenario where the statistics aren't helping. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
On Fri, 5 Dec 2008, Robert Treat wrote: Might I suggest you collect all of these various arguments (both for and against) and patches into a wiki page on the developers wiki? I'm getting the feeling this is going to take a while to sort out too. Page with most of the relevant stuff Kurt has posted so far is now listed under Development Projects on the wiki: http://wiki.postgresql.org/wiki/C%2B%2B_Compatibility -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
