Re: [PATCHES] [HACKERS] 8.2 features?
The major downside is that somewhere between 9000 and 1 VALUES-targetlists produces ERROR: stack depth limit exceeded. Perhaps for the typical use-case this is sufficient though. I'm open to better ideas, comments, objections... If the use case is people running MySQL dumps, then there will be millions of values-targetlists in MySQL dumps. Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] pgcrypto merge cleanup
- Few README fixes - Keep imath Id string, put $PostgreSQL$ separately. -- marko Index: contrib/pgcrypto/README.pgcrypto === RCS file: /opt/cvs/pgsql/contrib/pgcrypto/README.pgcrypto,v retrieving revision 1.15 diff -u -c -r1.15 README.pgcrypto *** contrib/pgcrypto/README.pgcrypto 13 Jul 2006 04:15:24 - 1.15 --- contrib/pgcrypto/README.pgcrypto 18 Jul 2006 13:02:27 - *** *** 36,48 `configure` script. The options that affect it are `--with-zlib` and `--with-openssl`. ! Without zlib, the PGP functions will not support compressed data inside ! PGP encrypted packets. ! Without OpenSSL, public-key encryption does not work, as pgcrypto does ! not yet contain math functions for large integers. ! There are some other differences with and without OpenSSL: ``-` Functionalitybuilt-in OpenSSL --- 36,49 `configure` script. The options that affect it are `--with-zlib` and `--with-openssl`. ! When compiled with zlib, PGP encryption functions are able to ! compress data before encrypting. ! When compiled with OpenSSL there will be more algorithms available. ! Also public-key encryption functions will be faster as OpenSSL ! has more optimized BIGNUM functions. ! Summary of functionality with and without OpenSSL: ``-` Functionalitybuilt-in OpenSSL *** *** 421,427 Options are named to be similar to GnuPG. Values should be given after an equal sign; separate options from each other with commas. Example: ! pgp_sym_encrypt(data, psw, 'compress-also=1, cipher-algo=aes256') All of the options except `convert-crlf` apply only to encrypt functions. Decrypt functions get the parameters from PGP data. --- 422,428 Options are named to be similar to GnuPG. Values should be given after an equal sign; separate options from each other with commas. Example: ! pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256') All of the options except `convert-crlf` apply only to encrypt functions. Decrypt functions get the parameters from PGP data. *** *** 571,577 --- Those functions only run a cipher over data, they don't have any advanced ! features of PGP encryption. In addition, they have some major problems: 1. They use user key directly as cipher key. 2. They don't provide any integrity checking, to see --- 572,578 --- Those functions only run a cipher over data, they don't have any advanced ! features of PGP encryption. Therefore they have some major problems: 1. They use user key directly as cipher key. 2. They don't provide any integrity checking, to see Index: contrib/pgcrypto/imath.c === RCS file: /opt/cvs/pgsql/contrib/pgcrypto/imath.c,v retrieving revision 1.3 diff -u -c -r1.3 imath.c *** contrib/pgcrypto/imath.c 16 Jul 2006 02:44:00 - 1.3 --- contrib/pgcrypto/imath.c 16 Jul 2006 10:58:02 - *** *** 3,9 Name: imath.c Purpose: Arbitrary precision integer arithmetic routines. Author: M. J. Fromberger http://www.dartmouth.edu/~sting/ ! Info: $PostgreSQL$ Copyright (C) 2002 Michael J. Fromberger, All Rights Reserved. --- 3,9 Name: imath.c Purpose: Arbitrary precision integer arithmetic routines. Author: M. J. Fromberger http://www.dartmouth.edu/~sting/ ! Info: Id: imath.c 21 2006-04-02 18:58:36Z sting Copyright (C) 2002 Michael J. Fromberger, All Rights Reserved. *** *** 27,32 --- 27,33 CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. */ + /* $PostgreSQL$ */ #include postgres.h #include px.h Index: contrib/pgcrypto/imath.h === RCS file: /opt/cvs/pgsql/contrib/pgcrypto/imath.h,v retrieving revision 1.3 diff -u -c -r1.3 imath.h *** contrib/pgcrypto/imath.h 16 Jul 2006 02:44:01 - 1.3 --- contrib/pgcrypto/imath.h 16 Jul 2006 11:00:17 - *** *** 2,8 Name: imath.h Purpose: Arbitrary precision integer arithmetic routines. Author: M. J. Fromberger http://www.dartmouth.edu/~sting/ ! Info: $PostgreSQL$ Copyright (C) 2002 Michael J. Fromberger, All Rights Reserved. --- 2,8 Name: imath.h Purpose: Arbitrary precision integer arithmetic routines. Author: M. J. Fromberger http://www.dartmouth.edu/~sting/ ! Info: Id: imath.h 21 2006-04-02 18:58:36Z sting Copyright (C) 2002 Michael J. Fromberger, All Rights Reserved. *** *** 26,31 --- 26,32 CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. */ + /*
Re: [PATCHES] [HACKERS] 8.2 features?
Christopher Kings-Lynne wrote: The major downside is that somewhere between 9000 and 1 VALUES-targetlists produces ERROR: stack depth limit exceeded. Perhaps for the typical use-case this is sufficient though. I'm open to better ideas, comments, objections... If the use case is people running MySQL dumps, then there will be millions of values-targetlists in MySQL dumps. Yeah. The fabricated select hack does feel wrong to me. Taking a quick 2 minute look at the grammar it looks like a better bet would be to make InsertStmt.targetList a list of lists of values rather than just a list of values. Of course, that would make the changes more invasive. Even with that we'd still be reading the whole thing into memory ... is there a sane way to cache the inline data before statement execution? I guess we can just say that for true bulk load our supported mechanism is still just COPY, but it would be a pity to restrict a feature that is in the standard that way. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Proposed patch for contrib/cube
Please find my cleanup of cube attached.
I have also included the cube_a_f8_f8() function to allow construction
of a cube from 2 float8[]'s.
Thanks,
Josh Reich
Neil Conway wrote:
On Mon, 2006-07-17 at 17:55 -0400, Joshua Reich wrote:
Ok. So, the cube code looks very unmaintained (not to offend anyone),
but it is all in V0 and I believe make installcheck fails out of the box
due to new error message formats.
It passes for me with CVS HEAD. The cube regression test is also run as
part of the buildfarm process, so it sounds like an error on your end.
I'm in the process of twisting the arm of another guy here to help me
clean up the code - if that is ok with the powers that be?
Cleanup would certainly be welcome.
This is my first patch submission, so please let me know what heinous
errors I have made
You should submit patches in context diff format (diff -c). You should
also send a single patch for all the (related) changes you want to make,
and you seem to have omitted the diff headers. You should generate
patches like:
$ cvs diff# from the root of the source tree
or
$ diff -rc ../orig_tree . # from the root of the modified tree
Depending on whether you're working against CVS or if you've made a copy
of the source tree to make your changes in.
if (ARRNELEMS(ll) != dim)
{
ereport(ERROR,
(errcode(ERRCODE_ARRAY_ELEMENT_ERROR),
errmsg(UR and LL arrays must be of same
length)));
PG_RETURN_NULL();
}
You don't need to return anything after ereport(ERROR) -- it won't
return control to the caller.
-Neil
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
? cube.sql
? cube_from_arrays.c
? cube_v0_to_v1.diff
? libcube.so.0.0
? logfile
Index: cube.c
===
RCS file: /projects/cvsroot/pgsql/contrib/cube/cube.c,v
retrieving revision 1.26
diff -c -r1.26 cube.c
*** cube.c 28 Jun 2006 11:59:59 - 1.26
--- cube.c 18 Jul 2006 15:31:43 -
***
*** 28,90
/*
** Input/Output routines
*/
! NDBOX*cube_in(char *str);
! NDBOX*cube(text *str);
! char *cube_out(NDBOX * cube);
! NDBOX*cube_f8(double *);
! NDBOX*cube_f8_f8(double *, double *);
! NDBOX*cube_c_f8(NDBOX *, double *);
! NDBOX*cube_c_f8_f8(NDBOX *, double *, double *);
! int4 cube_dim(NDBOX * a);
! double *cube_ll_coord(NDBOX * a, int4 n);
! double *cube_ur_coord(NDBOX * a, int4 n);
!
/*
** GiST support methods
*/
! bool g_cube_consistent(GISTENTRY *entry, NDBOX * query,
StrategyNumber strategy);
! GISTENTRY *g_cube_compress(GISTENTRY *entry);
! GISTENTRY *g_cube_decompress(GISTENTRY *entry);
! float*g_cube_penalty(GISTENTRY *origentry, GISTENTRY *newentry, float
*result);
! GIST_SPLITVEC *g_cube_picksplit(GistEntryVector *entryvec, GIST_SPLITVEC *v);
! bool g_cube_leaf_consistent(NDBOX * key, NDBOX * query,
StrategyNumber strategy);
! bool g_cube_internal_consistent(NDBOX * key, NDBOX * query,
StrategyNumber strategy);
! NDBOX*g_cube_union(GistEntryVector *entryvec, int *sizep);
! NDBOX*g_cube_binary_union(NDBOX * r1, NDBOX * r2, int *sizep);
! bool *g_cube_same(NDBOX * b1, NDBOX * b2, bool *result);
/*
** B-tree support functions
*/
! bool cube_eq(NDBOX * a, NDBOX * b);
! bool cube_ne(NDBOX * a, NDBOX * b);
! bool cube_lt(NDBOX * a, NDBOX * b);
! bool cube_gt(NDBOX * a, NDBOX * b);
! bool cube_le(NDBOX * a, NDBOX * b);
! bool cube_ge(NDBOX * a, NDBOX * b);
! int32 cube_cmp(NDBOX * a, NDBOX * b);
/*
** R-tree support functions
*/
! bool cube_contains(NDBOX * a, NDBOX * b);
! bool cube_contained(NDBOX * a, NDBOX * b);
! bool cube_overlap(NDBOX * a, NDBOX * b);
! NDBOX*cube_union(NDBOX * a, NDBOX * b);
! NDBOX*cube_inter(NDBOX * a, NDBOX * b);
! double *cube_size(NDBOX * a);
! void rt_cube_size(NDBOX * a, double *sz);
/*
** miscellaneous
*/
! bool cube_lt(NDBOX * a, NDBOX * b);
! bool cube_gt(NDBOX * a, NDBOX * b);
! double *cube_distance(NDBOX * a, NDBOX * b);
! bool cube_is_point(NDBOX * a);
! NDBOX*cube_enlarge(NDBOX * a, double *r, int4 n);
/*
** Auxiliary funxtions
--- 28,136
/*
** Input/Output routines
*/
! PG_FUNCTION_INFO_V1(cube_in);
! PG_FUNCTION_INFO_V1(cube);
! PG_FUNCTION_INFO_V1(cube_a_f8_f8);
! PG_FUNCTION_INFO_V1(cube_out);
! PG_FUNCTION_INFO_V1(cube_f8);
! PG_FUNCTION_INFO_V1(cube_f8_f8);
! PG_FUNCTION_INFO_V1(cube_c_f8);
! PG_FUNCTION_INFO_V1(cube_c_f8_f8);
!
Re: [PATCHES] [HACKERS] 8.2 features?
If the use case is people running MySQL dumps, then there will be millions of values-targetlists in MySQL dumps. I did some experimentation just now, and could not get mysql to accept a command longer than about 1 million bytes. It complains about Got a packet bigger than 'max_allowed_packet' bytes which seems a bit odd because max_allowed_packet is allegedly set to 16 million, but anyway I don't think people are going to be loading any million-row tables using single INSERT commands in mysql either. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] 8.2 features?
[EMAIL PROTECTED] (Christopher Kings-Lynne) writes: The major downside is that somewhere between 9000 and 1 VALUES-targetlists produces ERROR: stack depth limit exceeded. Perhaps for the typical use-case this is sufficient though. I'm open to better ideas, comments, objections... If the use case is people running MySQL dumps, then there will be millions of values-targetlists in MySQL dumps. Curiosity: How do *does* TheirSQL parse that, and not have the One Gigantic Query blow up their query parser? -- output = reverse(gro.gultn @ enworbbc) http://www.ntlug.org/~cbbrowne/unix.html JOHN CAGE (strapped to table): Do you really expect me to conduct this antiquated tonal system? LEONARD BERNSTEIN: No, Mr. Cage, I expect you to die! [With apologies to music and James Bond fans the world over...] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] 8.2 features?
Chris Browne wrote:
[EMAIL PROTECTED] (Christopher Kings-Lynne) writes:
The major downside is that somewhere between 9000 and 1
VALUES-targetlists produces ERROR: stack depth limit
exceeded. Perhaps for the typical use-case this is sufficient
though.
I'm open to better ideas, comments, objections...
If the use case is people running MySQL dumps, then there will be
millions of values-targetlists in MySQL dumps.
Curiosity: How do *does* TheirSQL parse that, and not have the One
Gigantic Query blow up their query parser?
Experimentation shows that mysqldump breaks up the insert into chunks.
Example with 10m rows:
[EMAIL PROTECTED] ~]# perl -e 'print drop table if exists foo; create table
foo (x int);\n; foreach my $i (0..9_) { print insert into foo
values \n; foreach my $j (0..99) { print , if $j; print
(,100*$i+$j+1,); } print ;\n; } ' gg
[EMAIL PROTECTED] ~]# mysql test gg
[EMAIL PROTECTED] ~]# mysqldump test foo aa
[EMAIL PROTECTED] ~]# mysql test aa
[EMAIL PROTECTED] ~]# grep INSERT aa | wc -l
104
cheers
andrew
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[PATCHES] Mark change-on-restart-only values in postgresql.conf
I added additional comments marked setting which need server restart to take effect. I use (!RSR!) tag for it, however if anybody have different idea, let me know and I will change it. I removed comments about commenting out behavior too, because patch now waiting for commit (or reject?). Zdenek Index: backend/utils/misc/postgresql.conf.sample === RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v retrieving revision 1.182 diff -c -r1.182 postgresql.conf.sample *** backend/utils/misc/postgresql.conf.sample 27 Jun 2006 22:16:44 - 1.182 --- backend/utils/misc/postgresql.conf.sample 18 Jul 2006 21:38:30 - *** *** 11,19 # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # - # Please note that re-commenting a setting is NOT sufficient to revert it - # to the default value, unless you restart the postmaster. - # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. --- 11,16 *** *** 22,28 # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_addresses, require ! # a postmaster shutdown and restart to take effect. #--- --- 19,26 # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_addresses, require ! # a postmaster shutdown and restart to take effect. This kind of items are ! # marked with (!RSR!) tag. #--- *** *** 31,36 --- 29,35 # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. + # All file locations settings change require server restart to tak effect. #data_directory = 'ConfigDir' # use data in another directory #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file *** *** 45,55 #--- # - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all ! #port = 5432 #max_connections = 100 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You --- 44,55 #--- # - Connection Settings - + # All connection settings change will take effect after server restart. #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all ! #port = 5432 # (!RSR!) #max_connections = 100 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You *** *** 63,73 # - Security Authentication - #authentication_timeout = 60 # 1-600, in seconds ! #ssl = off #password_encryption = on #db_user_namespace = off ! # Kerberos #krb_server_keyfile = '' #krb_srvname = 'postgres' #krb_server_hostname = '' # empty string matches any keytab entry --- 63,73 # - Security Authentication - #authentication_timeout = 60 # 1-600, in seconds ! #ssl = off # (!RSR!) #password_encryption = on #db_user_namespace = off ! # Kerberos (changes in kerberos setting require server restart) #krb_server_keyfile = '' #krb_srvname = 'postgres' #krb_server_hostname = '' # empty string matches any keytab entry *** *** 90,98 # - Memory - ! #shared_buffers = 1000 # min 16 or max_connections*2, 8KB each #temp_buffers = 1000 # min 100, 8KB each ! #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). #work_mem = 1024 # min 64, size in KB --- 90,98 # - Memory - ! #shared_buffers = 1000 # min 16 or max_connections*2, 8KB each (!RSR!) #temp_buffers = 1000 # min 100, 8KB each ! #max_prepared_transactions = 5 # can be 0 or more (!RSR!) # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock
Re: [PATCHES] Win32 DEF file error
Patch applied. Thanks. --- Magnus Hagander wrote: The Win32 DEF files that are generated for libpq contain the attribute DESCRIPTION, which is actually only allowed for device drivers. The compilers ignore it with a warning - if we remove them, we get rid of the warning. (ref http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore/ html/_core_description.asp) //Magnus Content-Description: libpq_def.patch [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] New shared memory hooks proposal (was Re: pre_load_libraries)
The attached patch provides add-ins with the means to register for
shared memory and LWLocks. This greatly improves the ease with which
shared memory may be used from add-ins, while keeping the accounting and
management for that shared memory separate.
Specifically it adds named add-in shared memory contexts. From these,
memory can be allocated without affecting the memory available in other
contexts.
Usage is as follows:
from add-in functions called from preload_libraries, you may call
RegisterAddinContext(const * name, size_t size)
to register a new (logical) shared memory segment.
and
RegisterAddinLWLock(LWLockid *lock_ptr);
to request that a LWLock be allocated, placed into *lock_ptr.
The actual creation of the shared memory segment and lwlocks is
performed later as part of shared memory initialisation.
To allocate shared memory from a named context you would use
ShmemAllocFromContext(size_t size, const char *name);
To reset a shared memory context back to its original unused state (from
which new allocations may be performed), you may use
ShmemResetContext(const char *name);
This works for me (for Veil) and make check runs fine.
I have not included any documentation updates in the patch as I'm not
sure where such API changes should be documented.
All comments, questions and suggestions are welcomed.
__
Marc
*** ./src/backend/storage/ipc/ipci.c Sat Jul 15 08:47:17 2006
--- ./new_src/backend/storage/ipc/ipci.c Tue Jul 18 15:26:22 2006
***
*** 57,62
--- 57,63
{
PGShmemHeader *seghdr;
Size size;
+ Size size_b4addins;
int numSemas;
/*
***
*** 93,98
--- 94,107
/* might as well round it off to a multiple of a typical page size */
size = add_size(size, 8192 - (size % 8192));
+ /* The shared memory for add-ins is treated as a separate
+ * segment but in reality is not
+ */
+ size_b4addins = size;
+ size = add_size(size, AddinShmemSize());
+ /* round it off again */
+ size = add_size(size, 8192 - (size % 8192));
+
elog(DEBUG3, invoking IpcMemoryCreate(size=%lu),
(unsigned long) size);
***
*** 101,106
--- 110,125
*/
seghdr = PGSharedMemoryCreate(size, makePrivate, port);
+ /*
+ * Modify hdr to show segment size before add-ins
+ */
+ seghdr-totalsize = size_b4addins;
+
+ /*
+ * Set up segment header sections in each Addin context
+ */
+ InitAddinContexts((void *) ((char *) seghdr + size_b4addins));
+
InitShmemAccess(seghdr);
/*
*** ./src/backend/storage/ipc/shmem.c Fri Jul 14 07:52:22 2006
--- ./new_src/backend/storage/ipc/shmem.c Tue Jul 18 15:26:22 2006
***
*** 61,66
--- 61,75
* cannot be redistributed to other tables. We could build a simple
* hash bucket garbage collector if need be. Right now, it seems
* unnecessary.
+ *
+ * (e) Add-ins can request their own logical shared memory segments
+ * by calling RegisterAddinContext() from the preload-libraries hook.
+ * Each call establishes a uniquely named add-in shared memopry
+ * context which will be set up as part of postgres intialisation.
+ * Memory can be allocated from these contexts using
+ * ShmemAllocFromContext(), and can be reset to its initial condition
+ * using ShmemResetContext().
+ *
*/
#include postgres.h
***
*** 86,91
--- 95,112
static HTAB *ShmemIndex = NULL; /* primary index hashtable for shmem */
+ /* Structures and globals for managing add-in shared memory contexts */
+ typedef struct context {
+ char *name;
+ Sizesize;
+ PGShmemHeader *seg_hdr;
+ struct context *next;
+ } ContextNode;
+
+ static ContextNode *addin_contexts = NULL;
+ static Size addin_contexts_size = 0;
+
+
/*
* InitShmemAccess() --- set up basic pointers to shared memory.
***
*** 140,146
}
/*
! * ShmemAlloc -- allocate max-aligned chunk from shared memory
*
* Assumes ShmemLock and ShmemSegHdr are initialized.
*
--- 161,255
}
/*
! * RegisterAddinContext -- Register the requirement for a named shared
! * memory context.
! */
! void
! RegisterAddinContext(const char *name, Size size)
! {
! char *newstr = malloc(strlen(name) + 1);
! ContextNode *node = malloc(sizeof(ContextNode));
! strcpy(newstr, name);
! node-name = newstr;
! // Round up to typical page size
! node-size = add_size(size, 8192 - (size % 8192));
! node-next = addin_contexts;
! addin_contexts = node;
!
! addin_contexts_size = add_size(addin_contexts_size, node-size);
! }
!
!
! /*
! * ContextFromName -- Return the ContextNode for the given named
! *context, or NULL if not found.
! */
! static ContextNode *
! ContextFromName(const char *name)
! {
! ContextNode *context = addin_contexts;
!
! while (context) {
! if (strcmp(name, context-name) == 0) {
! return context;
!
Re: [PATCHES] pg_regress in C
Magnus Hagander [EMAIL PROTECTED] writes: Per discussion at the conference: In order to run the regression tests on Windows without msys, pg_regress needs to be reimplemnted in C. Patch committed after significant further work. As committed, pg_regress.c is pretty nearly an exact replacement for the shell script; the only significant deviation is that the --temp_install switch's argument is required not optional. (This is because our homegrown version of getopt_long doesn't allow optional arguments. Maybe that should be fixed sometime.) There is one possibly important loose end: the shell script makes an effort to catch signals and shut down the temp postmaster before exiting, while there's no such code in the C version. I'm not sure if it's necessary. At least on my machine, if you type control-C while the tests are running then the kernel sends SIGINT to everything that's part of the terminal's process group, which will include the postmaster --- so the shutdown happens anyway. I have no idea if that'll work on Windows... One reason I didn't try to do this is I'm a bit hesitant to write a signal handler that does anything as interesting as a system() call, which would seem to be necessary to duplicate what the shell script did. Comments? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] modular pg_regress.sh
Joachim Wieland [EMAIL PROTECTED] writes: I propose a patch to make pg_regress.sh more modular. This patch has been pretty thoroughly superseded by the recent rewrite of pg_regress in C. It's possible that we could modularize the C version, but what I'd like to know first is why you can't just use pg_regress as-is. If it's short a small feature or two, perhaps adding those would be the way to go. The patch also adds a new option, --listen-on-tcp that makes the server listen on the tcp port even when unix sockets can be used. I believe this is not necessary: just set --host='interface to listen on'. There were two issues I noticed with the old script: ... These are good simplifications, which I've incorporated into CVS HEAD. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] 8.2 features?
I did some experimentation just now, and could not get mysql to accept a command longer than about 1 million bytes. It complains about Got a packet bigger than 'max_allowed_packet' bytes which seems a bit odd because max_allowed_packet is allegedly set to 16 million, but anyway I don't think people are going to be loading any million-row tables using single INSERT commands in mysql either. Strange. Last time I checked I thought MySQL dump used 'multivalue lists in inserts' for dumps, for the same reason that we use COPY ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] 8.2 features?
I did some experimentation just now, and could not get mysql to accept a command longer than about 1 million bytes. It complains about Got a packet bigger than 'max_allowed_packet' bytes which seems a bit odd because max_allowed_packet is allegedly set to 16 million, but anyway I don't think people are going to be loading any million-row tables using single INSERT commands in mysql either. Ah no, I'm mistaken. It's not by default in mysqldump, but it does seem recommended. This is from man mysqldump: -e|--extended-insert Allows utilization of the new, much faster INSERT syntax. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] 8.2 features?
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Strange. Last time I checked I thought MySQL dump used 'multivalue lists in inserts' for dumps, for the same reason that we use COPY I think Andrew identified the critical point upthread: they don't try to put an unlimited number of rows into one INSERT, only a megabyte or so's worth. Typical klugy-but-effective mysql design approach ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] 8.2 features?
Tom Lane wrote:
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Strange. Last time I checked I thought MySQL dump used 'multivalue
lists in inserts' for dumps, for the same reason that we use COPY
I think Andrew identified the critical point upthread: they don't try
to put an unlimited number of rows into one INSERT, only a megabyte
or so's worth. Typical klugy-but-effective mysql design approach ...
OK, so given that we don't need to be able to do 1 million
multi-targetlist insert statements, here is rev 2 of the patch.
It is just slightly more invasive, but performs *much* better. In fact,
it can handle as many targetlists as you have memory to deal with. It
also deals with DEFAULT values in the targetlist.
I've attached a php script that I used to do crude testing. Basically I
tested 3 cases in this order:
single-INSERT-multi-statement:
--
INSERT INTO foo2a (f1,f2) VALUES (1,2);
-- repeat statement $loopcount times
single-INSERT-at-once:
--
INSERT INTO foo2b (f1,f2) VALUES (1,2);INSERT INTO foo2a (f1,f2)
VALUES (1,2);INSERT INTO foo2a (f1,f2) VALUES (1,2)...
-- build a single SQL string by looping $loopcount times,
-- and execute it all at once
multi-INSERT-at-once:
-
INSERT INTO foo2c (f1,f2) VALUES (1,2),(1,2),(1,2)...
-- build a single SQL string by looping $loopcount times,
-- and execute it all at once
Here are the results:
$loopcount = 10;
single-INSERT-multi-statement Elapsed time is 34 seconds
single-INSERT-at-once Elapsed time is 7 seconds
multi-INSERT-at-once Elapsed time is 4 seconds
about 370MB peak memory usage
$loopcount = 20;
single-INSERT-multi-statement Elapsed time is 67 seconds
single-INSERT-at-once Elapsed time is 12 seconds
multi-INSERT-at-once Elapsed time is 9 seconds
about 750MB peak memory usage
$loopcount = 30;
single-INSERT-multi-statement Elapsed time is 101 seconds
single-INSERT-at-once Elapsed time is 18 seconds
multi-INSERT-at-once Elapsed time is 13 seconds
about 1.1GB peak memory usage
Somewhere beyond this, my machine goes into swap hell, and I didn't have
the patience to wait for it to complete :-)
It would be interesting to see a side-by-side comparison with MySQL
since that seems to be our benchmark on this feature. I'll try to do
that tomorrow if no one beats me to it.
There is only one downside to the current approach that I'm aware of.
The command-result tag is only set by the original query, meaning that
even if you insert 300,000 rows using this method, the command-result
tag looks like INSERT 0 1; e.g.:
regression=# create table foo2(f1 int default 42,f2 int default 6);
CREATE TABLE
regression=# insert into foo2 (f1,f2) values
(default,12),(default,10),(115,21);
INSERT 0 1
regression=# select * from foo2;
f1 | f2
-+
42 | 12
42 | 10
115 | 21
(3 rows)
Any thoughts on how to fix that?
Thanks,
Joe
Index: src/backend/parser/analyze.c
===
RCS file: /cvsroot/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.340
diff -c -r1.340 analyze.c
*** src/backend/parser/analyze.c 14 Jul 2006 14:52:21 - 1.340
--- src/backend/parser/analyze.c 19 Jul 2006 03:53:35 -
***
*** 657,667
}
else
{
/*
* For INSERT ... VALUES, transform the given list of values to form a
! * targetlist for the INSERT.
*/
! qry-targetList = transformTargetList(pstate, stmt-targetList);
}
/*
--- 657,699
}
else
{
+ ListCell *tlr;
+
/*
* For INSERT ... VALUES, transform the given list of values to form a
! * targetlist for the INSERT. In a multi-targetlist INSERT, append all
! * but the first targetlist to extras_after to be processed later by
! * do_parse_analyze
*/
! qry-targetList = NIL;
! foreach(tlr, stmt-targetList)
! {
! List *tgtlist = (List *) lfirst(tlr);
!
! if (qry-targetList == NIL)
! {
! /* transform the first targetlist */
! qry-targetList = transformTargetList(pstate, tgtlist);
! }
! else
! {
! /*
! * Create an InsertStmt node for each additional targetlist
! * and append to extras_after
! */
! InsertStmt *insnode = makeNode(InsertStmt);
!
! insnode-cols = NIL;
! insnode-targetList = list_make1(tgtlist);
! insnode-selectStmt = NULL;
! insnode-relation = stmt-relation;
!
! if (*extras_after == NIL)
! *extras_after = list_make1(insnode);
! else
! *extras_after = lappend(*extras_after, insnode);
! }
! }
}
/*
Index: src/backend/parser/gram.y
===
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.551
diff -c -r2.551 gram.y
*** src/backend/parser/gram.y 3 Jul 2006 22:45:39 - 2.551
--- src/backend/parser/gram.y 19 Jul 2006 03:53:40 -
***
*** 238,247
