[PATCHES] WIP: bitmap indexes (fwd)
I sent the following through earlier but I think the attachment was too large (thought that was limit was greatly increased?). You can download the patch here: http://www.alcove.com.au/~swm/bitmap-2.diff Thanks, Gavin -- Hi all, Attached is an update to the patch implementing bitmap indexes Jie sent last week. This patch tidies up some coding style issues, the system catalogs, adds some basic docs and regression tests, as well as additional functionality. There are still outstanding bugs and problems. These are: a) The planner doesn't really know about bitmaps. The code cheats. As such, bitmap index access is not costed correctly. b) There is, as Tom pointed out, a lot of code duplication around BitmapHeapNext(), MultiExecBitmapIndexScan() and related routines. This needs to be tidied up and would probably benefit from Tom's proposal to change the behaviour of amgetmulti. c) Related to this is the fact that the current on-disk bitmap cannot handle the ScalarArrayOpExpr optimisation that normal bitmap scans can. (The patch introduces some regression tests for bitmaps and one of these fails with an invalid row count. This displays the problem that needs to be solved). d) Also related to this, in() subqueries are causing us to hit some uninitialised memory. I haven't had time to explore this but it is related to the architectural issue above. e) Jie is hunting down a bug in multi-column support. f) I haven't tested concurrency I will continue to send in matches as we we make progress on these issues. Feed back, in particular on (a) and (b), are most welcome. Thanks, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] pg_dump: multiple tables, schemas with exclusions and
Greg Sabino Mullane wrote:
-- Start of PGP signed section.
Here's the latest pg_dump patch I've been (too sporadically) working on.
I abandoned building linked lists and decided to make the backend do all
the work, from building the list of good relations, to doing the POSIX
regex matching. I've added numerous examples to the docs, but it may
still need some more explaining. It should be nearly 100% backwards
compatible with any existing scripts that use a single -t as well.
I have adjusted your code for clarity, and clarified the documentation a
little. Please test and make sure it is OK for you. Thanks.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/pg_dump.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.86
diff -c -c -r1.86 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 13 May 2006 17:10:35 - 1.86
--- doc/src/sgml/ref/pg_dump.sgml 1 Aug 2006 04:53:52 -
***
*** 398,415
listitem
para
Dump data for replaceable class=parametertable/replaceable
! only. It is possible for there to be
! multiple tables with the same name in different schemas; if that
! is the case, all matching tables will be dumped. Specify both
! option--schema/ and option--table/ to select just one table.
/para
note
para
In this mode, applicationpg_dump/application makes no
! attempt to dump any other database objects that the selected table
may depend upon. Therefore, there is no guarantee
! that the results of a single-table dump can be successfully
restored by themselves into a clean database.
/para
/note
--- 398,460
listitem
para
Dump data for replaceable class=parametertable/replaceable
! only. It is possible for there to be multiple tables with the same
! name in different schemas; if that is the case, all matching tables
! will be dumped. Also, if any POSIX regular expression character
appears
! in the table name (literal([{\.?+/, the string will be
interpreted
! as a regular expression. Note that when in regular expression mode,
the
! string will not be anchored to the start/end unless literal^/ and
! literal$/ are used at the beginning/end of the string.
/para
+para
+The options option-t/, option-T/, option-n/, and
option-N/
+can be used together to achieve a high degree of control over what is
+dumped. Multiple arguments can be used, and are parsed in the order
+given to build a list of vaid tables and schemas. The schema options
are
+parsed first to create a list of schemas to dump, and then the table
options
+are parsed to only find tables in the matching schemas.
+/para
+
+paraFor examples, to dump a single table named literalpg_class/:
+
+ screen
+ prompt$/prompt userinputpg_dump -t pg_class mydb gt; db.out/userinput
+ /screen
+/para
+
+paraTo dump all tables starting with literalemployee/ in the
+literaldetroit/ schema, except for the table named
literalemployee_log/literal:
+
+ screen
+ prompt$/prompt userinputpg_dump -n detroit -t ^employee -T employee_log
mydb gt; db.out/userinput
+ /screen
+/para
+
+paraTo dump all schemas starting with literaleast/ or
literalwest/ and ending in
+literalgsm/, but not schemas that contain the letters
literaltest/, except for
+one named literaleast_alpha_test_five/:
+
+ screen
+ prompt$/prompt userinputpg_dump -n ^(east|west).*gsm$ -N test -n
east_alpha_test_five mydb gt; db.out/userinput
+ /screen
+/para
+
+
+paraTo dump all tables except for those beginning with
literalts_/literal:
+
+ screen
+ prompt$/prompt userinputpg_dump -T ^ts_ mydb gt; db.out/userinput
+ /screen
+/para
+
+
note
para
In this mode, applicationpg_dump/application makes no
! attempt to dump any other database objects that the selected tables
may depend upon. Therefore, there is no guarantee
! that the results of a specific-table dump can be successfully
restored by themselves into a clean database.
/para
/note
***
*** 417,422
--- 462,505
/varlistentry
varlistentry
+ termoption-T replaceable
class=parametertable/replaceable/option/term
+ termoption--exclude-table=replaceable
class=parametertable/replaceable/option/term
+ listitem
+para
+ Do not dump any matching replaceable
class=parametertables/replaceable.
+ More than one option
Re: [PATCHES] Updated INSERT/UPDATE RETURNING
This is a great patch. I was hoping to get this into 8.2 as a major feature. --- Jonah H. Harris wrote: Here's the updated patch with DELETE RETURNING removed. This isn't really an issue because no one wanted DELETE RETURNING to begin with. It is important to note that this patch is not yet ready to be committed. I still need to go through and run some more tests on it but wanted to put it in the queue again and let ya know I've been given time to make sure it gets in. This patch includes: - Code changes to core - Code changes to PL/pgSQL - Preliminary Documentation Updates (need to add to PL/pgSQL docs) - Preliminary Regression Tests (need to add PL/pgSQL regressions) There were a couple suggestions for sorta-kewl features like being able to use INSERT/UPDATE RETURNING in a FOR loop, etc. I may be able to get those in if people really want it but I looked into it after Neil mentioned it and IIRC, there are quite a few changes required to support it. Suggestions requested. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ [ Attachment, skipping... ] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- 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] [HACKERS] 8.2 features?
Joshua, So now it's MySQL users' turn to say, Sure, but speed isn't everything :-)Sure, but speed isn't everything... We can accept 02/31/2006 as a validdate. Let's see PostgreSQL do that!I got the joke :)But: it is still a problem when converting. As accepting 2006-02-31 as a valid date would require brainwashing at least the entire core team, we should find a recommended path of date migration from different universes. My idea would be to:a) declare date fields as textb) load the dump of the other dbc) add another column for the date fields, type timestampe (w/wo tz)d) try to update the column of c) with the converted field from a) e) replace the failing ones manuallyis this really best practice? especially finding the invalid ones would be challenging :(idea: sort after the textual date fields; look at hot spots (-00-00, -02-31) Are there better ideas? shall we document the best practice somewhere?Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607 -Let's set so double the killer delete select all.
Re: [PATCHES] Forcing current WAL file to be archived
Simon Riggs wrote: Patch included to implement xlog switching, using an xlog record processing instruction and forcibly moving xlog pointers. 1. Happens automatically on pg_stop_backup() Oh - so it will not be possible to do an online backup _without_ forcing a WAL switch any more? Laurenz Albe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Forcing current WAL file to be archived
Albe Laurenz wrote: Simon Riggs wrote: Patch included to implement xlog switching, using an xlog record processing instruction and forcibly moving xlog pointers. 1. Happens automatically on pg_stop_backup() Oh - so it will not be possible to do an online backup _without_ forcing a WAL switch any more? Well, previously, you would have always had to simulate a wal switch, by working out which is the current wal file and copying that. Otherwise your online backup wouldn't be complete. What Simon is describing sounds like a big step forward from that situation. It should let me delete half the code in my pitr backup/failover scripts. Definitely a Good Thing. Laurenz Albe Tim begin:vcard fn:Tim Allen n:Allen;Tim email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Forcing current WAL file to be archived
Tim Allen wrote: Patch included to implement xlog switching, using an xlog record processing instruction and forcibly moving xlog pointers. 1. Happens automatically on pg_stop_backup() Oh - so it will not be possible to do an online backup _without_ forcing a WAL switch any more? Well, previously, you would have always had to simulate a wal switch, by working out which is the current wal file and copying that. Otherwise your online backup wouldn't be complete. What Simon is describing sounds like a big step forward from that situation. It should let me delete half the code in my pitr backup/failover scripts. Definitely a Good Thing. Certainly a Good Thing, and it should be on by default. But couldn't there be situations where you'd like to do an online backup without a WAL switch? To avoid generating an archive WAL every day on a database with few changes, e.g.? Maybe not, I'm just wondering. Laurenz Albe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Forcing current WAL file to be archived
Albe Laurenz wrote: Tim Allen wrote: Patch included to implement xlog switching, using an xlog record processing instruction and forcibly moving xlog pointers. 1. Happens automatically on pg_stop_backup() Oh - so it will not be possible to do an online backup _without_ forcing a WAL switch any more? Well, previously, you would have always had to simulate a wal switch, by working out which is the current wal file and copying that. Otherwise your online backup wouldn't be complete. What Simon is describing sounds like a big step forward from that situation. It should let me delete half the code in my pitr backup/failover scripts. Definitely a Good Thing. Certainly a Good Thing, and it should be on by default. But couldn't there be situations where you'd like to do an online backup without a WAL switch? To avoid generating an archive WAL every day on a database with few changes, e.g.? But the online backup would be impossible to restore, if you don't have enough wal archived to recover past the point where you called pg_stop_backup(). So, doing a wal switch when pg_stop_backup() is called greatly reduces the risk of a user error that leads to broken backups. greetings, Florian Pflug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] float8 regression failure (HEAD, cygwin)
On 20/07/06, Tom Lane [EMAIL PROTECTED] wrote: Reini Urban [EMAIL PROTECTED] writes: BTW: HAVE_LONG_LONG_INT_64 is defined, so INT64_IS_BUSTED is defined also. You sure? INT64_IS_BUSTED should *not* be set in that case --- it's only supposed to be set if we couldn't find any 64-bit-int type at all. As for the regression test failure, it's odd because it looks to me that the actual test output is an exact match to the default float8.out file. I'm not sure why pg_regress chose to report a diff against float8-small-is-zero.out instead. This may be another teething pain of the new pg_regress-in-C code --- could you trace through it and see what's happening? Apparently the regression test is comparing the results/float8.out with expected/float8-small-is-zero.out because of the following line in src/test/regress/resultmap : float8/i.86-pc-cygwin=float8-small-is-zero I've changed that line to : float8/i.86-pc-cygwin=float8 and the regression test ended successfully : All 100 tests passed. I don't know why there are several expected results for the float8 test, depending on the platform. Is the modification ok? I've attached the patch, and cc'ed to pgsql-patches. Cheers, Adrian Maier patch_float8.diff Description: Binary data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Forcing current WAL file to be archived
Albe Laurenz wrote: Tim Allen wrote: Patch included to implement xlog switching, using an xlog record processing instruction and forcibly moving xlog pointers. 1. Happens automatically on pg_stop_backup() Oh - so it will not be possible to do an online backup _without_ forcing a WAL switch any more? Well, previously, you would have always had to simulate a wal switch, by working out which is the current wal file and copying that. Otherwise your online backup wouldn't be complete. What Simon is describing sounds like a big step forward from that situation. It should let me delete half the code in my pitr backup/failover scripts. Definitely a Good Thing. Certainly a Good Thing, and it should be on by default. But couldn't there be situations where you'd like to do an online backup without a WAL switch? To avoid generating an archive WAL every day on a database with few changes, e.g.? Maybe not, I'm just wondering. Considering the I/O caused by the backup, a new WAL file seems insignificant, and until a log switch, the backup isn't useful. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] float8 regression failure (HEAD, cygwin)
Adrian Maier wrote: On 20/07/06, Tom Lane [EMAIL PROTECTED] wrote: Reini Urban [EMAIL PROTECTED] writes: BTW: HAVE_LONG_LONG_INT_64 is defined, so INT64_IS_BUSTED is defined also. You sure? INT64_IS_BUSTED should *not* be set in that case --- it's only supposed to be set if we couldn't find any 64-bit-int type at all. As for the regression test failure, it's odd because it looks to me that the actual test output is an exact match to the default float8.out file. I'm not sure why pg_regress chose to report a diff against float8-small-is-zero.out instead. This may be another teething pain of the new pg_regress-in-C code --- could you trace through it and see what's happening? Apparently the regression test is comparing the results/float8.out with expected/float8-small-is-zero.out because of the following line in src/test/regress/resultmap : float8/i.86-pc-cygwin=float8-small-is-zero I've changed that line to : float8/i.86-pc-cygwin=float8 and the regression test ended successfully : All 100 tests passed. I don't know why there are several expected results for the float8 test, depending on the platform. Is the modification ok? I've attached the patch, and cc'ed to pgsql-patches. The problem with this is that we have another Cygwin member on buildfarm which passes the tests happily, and will thus presumably fail if we make this patch. You are running Cygwin 1.5.21 and the other buildfarm member is running 1.5.19, so that is possibly the difference. Maybe we need to abandon trying to map float8 results exactly in the resultmap file, and just let pg_regress pick the best fit as we do with some other tests. 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
Re: [PATCHES] [HACKERS] float8 regression failure (HEAD, cygwin)
On 01/08/06, Andrew Dunstan [EMAIL PROTECTED] wrote: Adrian Maier wrote: On 20/07/06, Tom Lane [EMAIL PROTECTED] wrote: Apparently the regression test is comparing the results/float8.out with expected/float8-small-is-zero.out because of the following line in src/test/regress/resultmap : float8/i.86-pc-cygwin=float8-small-is-zero I've changed that line to : float8/i.86-pc-cygwin=float8 and the regression test ended successfully : All 100 tests passed. I don't know why there are several expected results for the float8 test, depending on the platform. Is the modification ok? I've attached the patch, and cc'ed to pgsql-patches. The problem with this is that we have another Cygwin member on buildfarm which passes the tests happily, and will thus presumably fail if we make this patch. You are running Cygwin 1.5.21 and the other buildfarm member is running 1.5.19, so that is possibly the difference. This is indeed a problem. It would be difficult or even impossible to use different expected results for different versions of cygwin. Maybe we need to abandon trying to map float8 results exactly in the resultmap file, and just let pg_regress pick the best fit as we do with some other tests. Oh, is it possible to do that? That sounds great. Which other tests work like that? Cheers, Adrian Maier ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] float8 regression failure (HEAD, cygwin)
[ re cassowary buildfarm failure ] Adrian Maier [EMAIL PROTECTED] writes: On 20/07/06, Tom Lane [EMAIL PROTECTED] wrote: As for the regression test failure, it's odd because it looks to me that the actual test output is an exact match to the default float8.out file. I'm not sure why pg_regress chose to report a diff against float8-small-is-zero. Apparently the regression test is comparing the results/float8.out with expected/float8-small-is-zero.out because of the following line in src/test/regress/resultmap : float8/i.86-pc-cygwin=float8-small-is-zero Doh ... the question though is why are you getting different results from everybody else? There are other cygwin machines in the buildfarm and they are all passing regression --- I suppose they'd start failing if we remove that resultmap entry. The regular float8 result is certainly more correct than float8-small-is-zero, so I'm all for removing the resultmap entry if we can do it. But we'd need to be able to explain to people how to get their machines to pass, and right now I don't know what to say. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] float8 regression failure (HEAD, cygwin)
Andrew Dunstan [EMAIL PROTECTED] writes: Maybe we need to abandon trying to map float8 results exactly in the resultmap file, and just let pg_regress pick the best fit as we do with some other tests. I thought about that too but it seems a very bad idea. small-is-zero is distinctly less correct than the regular output, and I don't think we want pg_regress to be blindly accepting it as OK on any platform. Perhaps we could stick a version check into the resultmap lookup? It'd likely have been painful on the shell script implementation but now that the code is in C I think we have lots of flexibility. There's no need to feel bound by the historical resultmap format. However this is all premature unless we can verify that cgywin's strtod() complains about float underflow after version so-and-so. Do they publish a detailed change log? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] float8 regression failure (HEAD, cygwin)
On 01/08/06, Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Maybe we need to abandon trying to map float8 results exactly in the resultmap file, and just let pg_regress pick the best fit as we do with some other tests. I thought about that too but it seems a very bad idea. small-is-zero is distinctly less correct than the regular output, and I don't think we want pg_regress to be blindly accepting it as OK on any platform. Perhaps we could stick a version check into the resultmap lookup? It'd likely have been painful on the shell script implementation but now that the code is in C I think we have lots of flexibility. There's no need to feel bound by the historical resultmap format. However this is all premature unless we can verify that cgywin's strtod() complains about float underflow after version so-and-so. Do they publish a detailed change log? There are links to the last few releases on their home page http://www.cygwin.com , in the News section. -- Adrian Maier ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] 8.2 features?
Joe Conway [EMAIL PROTECTED] writes: In case you can make use of it, here's my latest. I found that I was being too aggressive at freeing the input nodes to transformExpr() in transformRangeValues() after using them. In many cases the returned node is a new palloc'd node, but in some cases it is not. Great, I'll incorporate these updates and keep plugging --- should be done today barring problems. If you have some spare cycles today, want to work on regression tests and docs? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] better support of out parameters in plperl
Hello,
I send two small patches. First does conversion from perl to postgresql
array in OUT parameters. Second patch allow hash form output from procedures
with one OUT argument.
Regards
Pavel Stehule
_
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/
*** ./plperl.c.orig 2006-07-29 21:07:09.0 +0200
--- ./plperl.c 2006-08-01 14:51:09.0 +0200
***
*** 117,122
--- 117,124
static void plperl_init_shared_libs(pTHX);
static HV *plperl_spi_execute_fetch_result(SPITupleTable *, int, int);
+ static SV *plperl_convert_to_pg_array(SV *src);
+
/*
* This routine is a crock, and so is everyplace that calls it. The problem
* is that the cached form of plperl functions/queries is allocated permanently
***
*** 412,418
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg(Perl hash contains nonexistent column \%s\,
key)));
! if (SvOK(val) SvTYPE(val) != SVt_NULL)
values[attn - 1] = SvPV(val, PL_na);
}
hv_iterinit(perlhash);
--- 414,425
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg(Perl hash contains nonexistent column \%s\,
key)));
!
! /* if value is ref on array do to pg string array conversion */
! if (SvTYPE(val) == SVt_RV
! SvTYPE(SvRV(val)) == SVt_PVAV)
! values[attn - 1] = SvPV(plperl_convert_to_pg_array(val), PL_na);
! else if (SvOK(val) SvTYPE(val) != SVt_NULL)
values[attn - 1] = SvPV(val, PL_na);
}
hv_iterinit(perlhash);
***
*** 1767,1773
if (SvOK(sv) SvTYPE(sv) != SVt_NULL)
{
! char *val = SvPV(sv, PL_na);
ret = InputFunctionCall(prodesc-result_in_func, val,
prodesc-result_typioparam, -1);
--- 1774,1789
if (SvOK(sv) SvTYPE(sv) != SVt_NULL)
{
! char *val;
! SV *array_ret;
!
! if (SvROK(sv) SvTYPE(SvRV(sv)) == SVt_PVAV )
! {
! array_ret = plperl_convert_to_pg_array(sv);
! sv = array_ret;
! }
!
! val = SvPV(sv, PL_na);
ret = InputFunctionCall(prodesc-result_in_func, val,
prodesc-result_typioparam, -1);
*** ./sql/plperl.sql.orig 2006-07-30 22:52:04.0 +0200
--- ./sql/plperl.sql 2006-08-01 15:02:53.0 +0200
***
*** 337,339
--- 337,374
$$ LANGUAGE plperl;
SELECT * from perl_spi_prepared_set(1,2);
+ ---
+ --- Some OUT and OUT array tests
+ ---
+
+ CREATE OR REPLACE FUNCTION test_out_params(OUT a varchar, OUT b varchar) AS $$
+ return { a= 'ahoj', b='svete'};
+ $$ LANGUAGE plperl;
+ SELECT '01' AS i, * FROM test_out_params();
+
+ CREATE OR REPLACE FUNCTION test_out_params_array(OUT a varchar[], OUT b varchar[]) AS $$
+ return { a= ['ahoj'], b=['svete']};
+ $$ LANGUAGE plperl;
+ SELECT '02' AS i, * FROM test_out_params_array();
+
+ CREATE OR REPLACE FUNCTION test_out_params_set(OUT a varchar, out b varchar) RETURNS SETOF RECORD AS $$
+ return_next { a= 'ahoj', b='svete'};
+ return_next { a= 'ahoj', b='svete'};
+ return_next { a= 'ahoj', b='svete'};
+ $$ LANGUAGE plperl;
+ SELECT '03' AS I,* FROM test_out_params_set();
+
+ CREATE OR REPLACE FUNCTION test_out_params_set_array(OUT a varchar[], out b varchar[]) RETURNS SETOF RECORD AS $$
+ return_next { a= ['ahoj'], b=['velky','svete']};
+ return_next { a= ['ahoj'], b=['velky','svete']};
+ return_next { a= ['ahoj'], b=['velky','svete']};
+ $$ LANGUAGE plperl;
+ SELECT '04' AS I,* FROM test_out_params_set_array();
+
+
+ DROP FUNCTION test_out_params();
+ DROP FUNCTION test_out_params_set();
+ DROP FUNCTION test_out_params_array();
+ DROP FUNCTION test_out_params_set_array();
+
+
*** ./plperl.c.orig 2006-08-01 15:20:16.0 +0200
--- ./plperl.c 2006-08-01 15:45:50.0 +0200
***
*** 52,57
--- 52,58
FmgrInfo result_in_func; /* I/O function and arg for result type */
Oid result_typioparam;
int nargs;
+ int num_out_args; /* number of out arguments */
FmgrInfo arg_out_func[FUNC_MAX_ARGS];
bool arg_is_rowtype[FUNC_MAX_ARGS];
SV *reference;
***
*** 118,123
--- 119,125
static HV *plperl_spi_execute_fetch_result(SPITupleTable *, int, int);
static SV *plperl_convert_to_pg_array(SV *src);
+ static SV *plperl_transform_result(plperl_proc_desc *prodesc, SV *result);
/*
* This routine is a crock, and so is everyplace that calls it. The problem
***
*** 698,709
HeapTuple tuple;
Form_pg_proc proc;
char functyptype;
- int numargs;
- Oid *argtypes;
- char **argnames;
- char *argmodes;
bool istrigger = false;
- int i;
/* Get the new function's pg_proc entry */
tuple = SearchSysCache(PROCOID,
--- 700,706
***
*** 731,748
format_type_be(proc-prorettype;
}
- /* Disallow pseudotypes in arguments (either IN or OUT) */
- numargs =
Re: [PATCHES] [HACKERS] float8 regression failure (HEAD, cygwin)
Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
One other thought I had was that we could have
pg_regress always allow a fallback to the canonical result file.
Hm, that's a good thought. Want to see how painful it is to code?
Would this do the trick?
cheers
andrew
Index: pg_regress.c
===
RCS file: /cvsroot/pgsql/src/test/regress/pg_regress.c,v
retrieving revision 1.16
diff -c -r1.16 pg_regress.c
*** pg_regress.c 27 Jul 2006 15:37:19 - 1.16
--- pg_regress.c 1 Aug 2006 14:04:20 -
***
*** 914,919
--- 914,952
}
}
+ /*
+ * fall back on the canonical results file if we haven't tried it yet
+ * and haven't found a complete match yet.
+ */
+
+ if (strcmp(expectname, testname) != 0)
+ {
+ snprintf(expectfile, sizeof(expectfile), %s/expected/%s.out,
+ inputdir, testname, i);
+ if (!file_exists(expectfile))
+ continue;
+
+ snprintf(cmd, sizeof(cmd),
+ SYSTEMQUOTE diff %s \%s\ \%s\ \%s\ SYSTEMQUOTE,
+ basic_diff_opts, expectfile, resultsfile, diff);
+ run_diff(cmd);
+
+ if (file_size(diff) == 0)
+ {
+ /* No diff = no changes = good */
+ unlink(diff);
+ return false;
+ }
+
+ l = file_line_count(diff);
+ if (l best_line_count)
+ {
+ /* This diff was a better match than the last one */
+ best_line_count = l;
+ strcpy(best_expect_file, expectfile);
+ }
+ }
+
/*
* Use the best comparison file to generate the pretty diff, which
* we append to the diffs summary file.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] [HACKERS] float8 regression failure (HEAD, cygwin)
Andrew Dunstan [EMAIL PROTECTED] writes: Would this do the trick? I think Bruce changed the call convention for run_diff ... are you looking at CVS tip? Otherwise it looks reasonable. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] float8 regression failure (HEAD, cygwin)
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Would this do the trick? I think Bruce changed the call convention for run_diff ... are you looking at CVS tip? Otherwise it looks reasonable. You're right. I had forgotten to do a cvs update. Fixed and committed. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] pg_dump: multiple tables, schemas with exclusions and
Greg Sabino Mullane wrote:
-- Start of PGP signed section.
Here's the latest pg_dump patch I've been (too sporadically) working on.
I abandoned building linked lists and decided to make the backend do all
the work, from building the list of good relations, to doing the POSIX
regex matching. I've added numerous examples to the docs, but it may
still need some more explaining. It should be nearly 100% backwards
compatible with any existing scripts that use a single -t as well.
Very updated patch attached and applied. I did reformatting, variable
renaming, and some cleanup on the linked list handling.
Thanks. I am very glad to get this long-overdue TODO item done.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/pg_dump.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.86
diff -c -c -r1.86 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 13 May 2006 17:10:35 - 1.86
--- doc/src/sgml/ref/pg_dump.sgml 1 Aug 2006 17:44:56 -
***
*** 398,415
listitem
para
Dump data for replaceable class=parametertable/replaceable
! only. It is possible for there to be
! multiple tables with the same name in different schemas; if that
! is the case, all matching tables will be dumped. Specify both
! option--schema/ and option--table/ to select just one table.
/para
note
para
In this mode, applicationpg_dump/application makes no
! attempt to dump any other database objects that the selected table
may depend upon. Therefore, there is no guarantee
! that the results of a single-table dump can be successfully
restored by themselves into a clean database.
/para
/note
--- 398,460
listitem
para
Dump data for replaceable class=parametertable/replaceable
! only. It is possible for there to be multiple tables with the same
! name in different schemas; if that is the case, all matching tables
! will be dumped. Also, if any POSIX regular expression character appears
! in the table name (literal([{\.?+/, the string will be interpreted
! as a regular expression. Note that when in regular expression mode, the
! string will not be anchored to the start/end unless literal^/ and
! literal$/ are used at the beginning/end of the string.
/para
+para
+The options option-t/, option-T/, option-n/, and option-N/
+can be used together to achieve a high degree of control over what is
+dumped. Multiple arguments can be used, and are parsed in the order
+given to build a list of valid tables and schemas. The schema options are
+parsed first to create a list of schemas to dump, and then the table options
+are parsed to only find tables in the matching schemas.
+/para
+
+paraFor example, to dump a single table named literalpg_class/:
+
+ screen
+ prompt$/prompt userinputpg_dump -t pg_class mydb gt; db.out/userinput
+ /screen
+/para
+
+paraTo dump all tables starting with literalemployee/ in the
+literaldetroit/ schema, except for the table named literalemployee_log/literal:
+
+ screen
+ prompt$/prompt userinputpg_dump -n detroit -t ^employee -T employee_log mydb gt; db.out/userinput
+ /screen
+/para
+
+paraTo dump all schemas starting with literaleast/ or literalwest/ and ending in
+literalgsm/, but not schemas that contain the letters literaltest/, except for
+one named literaleast_alpha_test_five/:
+
+ screen
+ prompt$/prompt userinputpg_dump -n ^(east|west).*gsm$ -N test -n east_alpha_test_five mydb gt; db.out/userinput
+ /screen
+/para
+
+
+paraTo dump all tables except for those beginning with literalts_/literal:
+
+ screen
+ prompt$/prompt userinputpg_dump -T ^ts_ mydb gt; db.out/userinput
+ /screen
+/para
+
+
note
para
In this mode, applicationpg_dump/application makes no
! attempt to dump any other database objects that the selected tables
may depend upon. Therefore, there is no guarantee
! that the results of a specific-table dump can be successfully
restored by themselves into a clean database.
/para
/note
***
*** 417,422
--- 462,505
/varlistentry
varlistentry
+ termoption-T replaceable class=parametertable/replaceable/option/term
+ termoption--exclude-table=replaceable class=parametertable/replaceable/option/term
+ listitem
+para
+ Do not dump any matching replaceable class=parametertables/replaceable.
+
Re: [PATCHES] better support of out parameters in plperl
Pavel Stehule wrote: Hello, I send two small patches. First does conversion from perl to postgresql array in OUT parameters. Second patch allow hash form output from procedures with one OUT argument. I will try to review these in the next 2 weeks unless someone beats me to it. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] New variable server_version_num
On Sun, Jul 30, 2006 at 11:27:33AM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: On Sat, Jul 29, 2006 at 09:44:10PM -0400, Tom Lane wrote: The correct solution is for client-side libraries to provide the feature. Not if the app is written in SQL, as the bootstrap, regression test, etc. code for modules frequently is. SQL doesn't really have any conditional ability strong enough to deal with existence or non-existence of features. What are you hoping to do, a CASE expression? Both arms of the CASE still have to parse, so I remain unconvinced that there are real world uses. There's also plpgsql, which afaik has no way to get the version number (other than slogging though the output of version()). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] New variable server_version_num
On Tue, Aug 01, 2006 at 12:37:48PM -0500, Jim C. Nasby wrote: On Sun, Jul 30, 2006 at 11:27:33AM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: On Sat, Jul 29, 2006 at 09:44:10PM -0400, Tom Lane wrote: The correct solution is for client-side libraries to provide the feature. Not if the app is written in SQL, as the bootstrap, regression test, etc. code for modules frequently is. SQL doesn't really have any conditional ability strong enough to deal with existence or non-existence of features. What are you hoping to do, a CASE expression? Both arms of the CASE still have to parse, so I remain unconvinced that there are real world uses. CREATE OR REPLACE FUNCTION version_new_enough( in_version INTEGER ) RETURNS BOOLEAN LANGUAGE sql AS $$ SELECT COALESCE( s.setting::INTEGER, /* Cast setting to integer if it's there */ $1 - 1 /* Otherwise, guarantee a lower number than the input */ ) = $1 FROM (SELECT 'server_version_num'::text AS name) AS foo LEFT JOIN pg_catalog.pg_settings s ON (foo.name = s.name) $$; There's also plpgsql, which afaik has no way to get the version number (other than slogging though the output of version()). Right. String-mashing is great when you have to do it, but this patch sets it up so you don't have to. :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] New shared memory hooks proposal (was Re:
I updated the style of your patch, and added a little to your comment
block about how to use this capability. I don't think any additional
documentation is necessary.
Thanks.
---
Marc Munro wrote:
-- Start of PGP signed section.
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
[ Attachment, skipping... ]
-- End of PGP section, PGP failed!
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/storage/ipc/ipci.c
===
RCS file: /cvsroot/pgsql/src/backend/storage/ipc/ipci.c,v
retrieving revision 1.86
diff -c -c -r1.86 ipci.c
*** src/backend/storage/ipc/ipci.c 15 Jul 2006 15:47:17 - 1.86
--- src/backend/storage/ipc/ipci.c 1 Aug 2006 19:01:09 -
***
*** 57,62
--- 57,63
{
PGShmemHeader *seghdr;
Size size;
+ Size size_b4addins;
int numSemas;
/*
***
*** 93,98
--- 94,108
/* 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 it 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
--- 111,126
*/
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);
/*
Index: src/backend/storage/ipc/shmem.c
===
RCS file: /cvsroot/pgsql/src/backend/storage/ipc/shmem.c,v
retrieving revision 1.94
diff -c -c -r1.94 shmem.c
*** src/backend/storage/ipc/shmem.c 22 Jul 2006 23:04:39 - 1.94
--- src/backend/storage/ipc/shmem.c 1 Aug 2006 19:01:09 -
***
*** 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(). Also, RegisterAddinLWLock(LWLockid *lock_ptr)
+ * can be used to request that a LWLock be allocated, placed into *lock_ptr.
*/
#include postgres.h
***
*** 86,91
--- 95,113
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
[PATCHES] Replication Documentation
Here's a patch to add in the material on replication recently discussed on pgsql.docs. I'm not thrilled that there were only a few comments made; I'd be happy to see slicing and dicing to see this made more useful. Index: filelist.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/filelist.sgml,v retrieving revision 1.44 diff -c -u -r1.44 filelist.sgml --- filelist.sgml 12 Sep 2005 22:11:38 - 1.44 +++ filelist.sgml 1 Aug 2006 20:00:00 - @@ -44,6 +44,7 @@ !entity configSYSTEM config.sgml !entity user-managSYSTEM user-manag.sgml !entity wal SYSTEM wal.sgml +!entity replication SYSTEM replication.sgml !-- programmer's guide -- !entity dfunc SYSTEM dfunc.sgml Index: postgres.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/postgres.sgml,v retrieving revision 1.77 diff -c -u -r1.77 postgres.sgml --- postgres.sgml 10 Mar 2006 19:10:48 - 1.77 +++ postgres.sgml 1 Aug 2006 20:00:00 - @@ -155,6 +155,7 @@ diskusage; wal; regress; + replication; /part Then add the following as .../doc/src/sgml/replication.sgml !-- $PostgreSQL$ -- chapter id=replication title Replication /title indextermprimaryreplication/primary/indexterm para People frequently ask about what replication options are available for productnamePostgreSQL/productname. Unfortunately, there are so many approaches and models to this that are useful for different purposes that things tend to get confusing. /para para At perhaps the most primitive level, one might use xref linkend=backup tools, whether xref linkend=app-pgdump or xref linkend=continuous-archiving to create additional copies of databases. This emphasisdoesn't/emphasis provide any way to keep the replicas up to date; to bring the state of things to a different point in time requires bringing up another copy. There is no way, with these tools, for updates on a quotemaster/quote system to automatically propagate to the replicas./para sect1 title Categorization of Replication Systems /title para Looking at replication systems, there are a number of ways in which they may be viewed: itemizedlist listitempara Single master versus multimaster./para para That is, whether there is a single database considered quotemaster/quote, where all update operations are required to be submitted, or the alternative, multimaster, where updates may be submitted to any of several databases./para para Multimaster replication is vastly more complex and expensive, because of the need to deal with the possibility of conflicting updates. The simplest example of this is where a replicated database manages inventory; the question is, what happens when requests go to different database nodes requesting a particular piece of inventory?/para para Synchronous multimaster replication introduces the need to distribute locks across the systems, which, in research work done with Postgres-R and Slony-II, has proven to be very expensive. /para/listitem listitempara Synchronous versus asynchronous/para paraSynchronous systems are ones where updates must be accepted on all the databases before they are permitted to commandCOMMIT/command. /para para Asynchronous systems propagate updates to the other databases later. This permits the possibility that one database may have data significantly behind others. Whether or not being behind is acceptable or not will depend on the nature of the application./para para Asynchronous multimaster replication introduces the possibility that conflicting updates will be accepted by multiple nodes, as they don't know, at commandCOMMIT/command time, that the updates conflict. It is then necessary to have some sort of conflict resolution system, which can't really be generalized as a generic database facility. An instance of this that is commonly seen is in the productnamePalmOS HotSync/productname system; the quotegeneral policy/quote when conflicts are noticed is to allow both conflicting records to persist until a human can intervene. That may be quite acceptable for an address book; it's emphasisnot/emphasis fine for OLTP systems. /para /listitem listitempara Update capture methods /para para Common methods include having triggers on tables, capturing SQL statements, and capturing transaction log (WAL) updates /para itemizedlist listitempara Triggers, as used in eRServer and Slony-I, have the advantage of capturing updates at the end of processing when all column values have been finalized. The use of transaction
Re: [PATCHES] New variable server_version_num
Quoth [EMAIL PROTECTED] (David Fetter): On Tue, Aug 01, 2006 at 12:37:48PM -0500, Jim C. Nasby wrote: On Sun, Jul 30, 2006 at 11:27:33AM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: On Sat, Jul 29, 2006 at 09:44:10PM -0400, Tom Lane wrote: The correct solution is for client-side libraries to provide the feature. Not if the app is written in SQL, as the bootstrap, regression test, etc. code for modules frequently is. SQL doesn't really have any conditional ability strong enough to deal with existence or non-existence of features. What are you hoping to do, a CASE expression? Both arms of the CASE still have to parse, so I remain unconvinced that there are real world uses. CREATE OR REPLACE FUNCTION version_new_enough( in_version INTEGER ) RETURNS BOOLEAN LANGUAGE sql AS $$ SELECT COALESCE( s.setting::INTEGER, /* Cast setting to integer if it's there */ $1 - 1 /* Otherwise, guarantee a lower number than the input */ ) = $1 FROM (SELECT 'server_version_num'::text AS name) AS foo LEFT JOIN pg_catalog.pg_settings s ON (foo.name = s.name) $$; There's also plpgsql, which afaik has no way to get the version number (other than slogging though the output of version()). Right. String-mashing is great when you have to do it, but this patch sets it up so you don't have to. :) There's *some* data to be gotten from select setting from pg_catalog.pg_settings where name = 'server_version'; Seems to me that value isn't without its uses... [EMAIL PROTECTED]:pgsql-HEAD/doc/src/sgml for port in 5432 5533 5532 5882; do for psql -p $port -h localhost -d template1 -c select '$port', setting from pg_catalog.pg_settings where name like 'server_version'; for done ?column? | setting --+- 5432 | 7.4.13 (1 row) ?column? | setting --+- 5533 | 7.4.10 (1 row) ?column? | setting --+- 5532 | 8.0.5 (1 row) ?column? | setting --+-- 5882 | 8.2devel (1 row) If I wanted to, it oughtn't be difficult to string smash those settings into something very nearly useful... -- cbbrowne,@,gmail.com http://linuxfinances.info/info/rdbms.html in your opinion which is the best programming tools ? The human brain and a keyboard. -- Nathan Wagner ---(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] Replication Documentation
Chris Browne wrote: Here's a patch to add in the material on replication recently discussed on pgsql.docs. I'm not thrilled that there were only a few comments made; I'd be happy to see slicing and dicing to see this made more useful. s/e.g. -/e.g.,/ s/ - /ndash;/ The indentation of the SGML file seems at odds with our conventions (we don't use tabs, for one thing.) You mention this: para Common methods include having triggers on tables, capturing SQL statements, and capturing transaction log (WAL) updates /para However you don't mention anything about WAL captures. Mentioning that PITR is one of these would be good. In the last few paragraphs, the title is about Postgres-R but then you comment on Slony-II. Should the title mention both? para As a result of those problems, Slony-II efforts have fallen off somewhat. /para s/those/these/ ? Otherwise looks good to my untrained eyes. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Replication Documentation
s/sequnce/sequence/ Nice work! -- Korry Douglas [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] Replication Documentation
Thanks for mentioning about pgpool! sect2title pgpool /title para applicationpgpool/application was initially created by Tatsuo Isshii as a portable alternative to Java connection pool modules. He subsequently observed that it wouldn't take very much effort to extend it to create a simple replication system: if it is forwarding SQL queries to a PostgreSQL instance, extending that to two databases is very straightforward. /para para It suffers, by nature, from the problems associated with replicating using capture of SQL statements; any sort of nondeterminism in the replicated statements will cause the databases to diverge. /para para On the other hand, it is very easy to install and configure; for users with simple requirements, that can suffice. /para para A applicationpgpool-2/application is under way which introduces a more sophisticated query parser to try to address the nondeterminism issues; that may limit ongoing support for the legacy version./para pgpool-II (not pgpool-2, please) does not try to resolve nondeterminism issues but try to add parallel SELECT query execution. Also we will continue to support legacy version until pgpool-II becomes stable enough. Also you might want to add pgpool development site URL. FYI, pgpool-II presentation material for PostgreSQL Anniversary Summit can be obtained from: http://www.sraoss.co.jp/event_seminar/2006/pgpool_feat_and_devel.pdf -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Replication Documentation
Chris Browne wrote: Here's a patch to add in the material on replication recently discussed on pgsql.docs. I'm not thrilled that there were only a few comments made; I'd be happy to see slicing and dicing to see this made more useful. The agreed-to process was 1. post information on pgsql-general 1.a. solicit comments 2. put information page on web site 3. link from documentation to web site You seem to have short-circuited all that. I don't think this sort of material belongs directly into the PostgreSQL documentation. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom,
Is this intentional:
template1=# values(1), (2);
column1
-
1
2
(2 rows)
This is legal because of:
simple_select:
/* ... */
| values_clause { $$ = $2; }
Also, I am working out some docs and regression tests.
Gavin
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] Replication Documentation
1. post information on pgsql-general 1.a. solicit comments 2. put information page on web site 3. link from documentation to web site You seem to have short-circuited all that. I don't think this sort of material belongs directly into the PostgreSQL documentation. It might be interesting to have some links in the external projects area for replication, but a section of its own doesn't seem relevant. Joshua D. Drkae -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Replication Documentation
Joshua D. Drake wrote: I don't think this sort of material belongs directly into the PostgreSQL documentation. Why not? It might be interesting to have some links in the external projects area for replication, but a section of its own doesn't seem relevant. I disagree about having some links. Maybe we should consider adding this as a section in the external projects chapter, instead of having a chapter of its own, but some links seems a little short on actual contents. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Replication Documentation
Alvaro Herrera wrote: Joshua D. Drake wrote: I don't think this sort of material belongs directly into the PostgreSQL documentation. Why not? Well Peter said that, not me :) It might be interesting to have some links in the external projects area for replication, but a section of its own doesn't seem relevant. I disagree about having some links. Maybe we should consider adding this as a section in the external projects chapter, instead of having a chapter of its own, but some links seems a little short on actual contents. O.k. more specifically, I think that the content (even if it is a section) probably deserves discussion in the external projects section. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Replication Documentation
Joshua D. Drake wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: I don't think this sort of material belongs directly into the PostgreSQL documentation. Why not? Well Peter said that, not me :) I know, but I though I'd post one message instead of two. (In fact I didn't even think about it -- I just assume it's clear.) It might be interesting to have some links in the external projects area for replication, but a section of its own doesn't seem relevant. I disagree about having some links. Maybe we should consider adding this as a section in the external projects chapter, instead of having a chapter of its own, but some links seems a little short on actual contents. O.k. more specifically, I think that the content (even if it is a section) probably deserves discussion in the external projects section. Sure, see my suggestion above. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote: Here's what I've got so far. I think there's probably more gold to be mined in terms of reducing runtime memory consumption (I don't like the list_free_deep bit, we should use a context), but functionally it seems complete. I'm off to dinner again, it's in your court to look over some more if you want. OK, I'll continue to look at it this week. (PS: if you want to apply, go ahead, don't forget catversion bump.) Sure, I'll commit shortly. Thanks, Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Gavin Sherry wrote:
Is this intentional:
template1=# values(1), (2);
column1
-
1
2
(2 rows)
This is legal because of:
simple_select:
/* ... */
| values_clause { $$ = $2; }
hmm, not sure about that...
Also, I am working out some docs and regression tests.
Oh, cool. I was going to start working on that myself tonight, but if
you're already working on it, don't let me stand in the way ;-)
Actually, if you want me to finish up whatever you have started, I'm
happy to do that too.
Joe
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
On Tue, 1 Aug 2006, Joe Conway wrote:
Gavin Sherry wrote:
Is this intentional:
template1=# values(1), (2);
column1
-
1
2
(2 rows)
This is legal because of:
simple_select:
/* ... */
| values_clause { $$ = $2; }
hmm, not sure about that...
Also, I am working out some docs and regression tests.
Oh, cool. I was going to start working on that myself tonight, but if
you're already working on it, don't let me stand in the way ;-)
Actually, if you want me to finish up whatever you have started, I'm
happy to do that too.
I've got to go out but I'll send a complete patch when I get back.
Gavin
---(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: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote: Here's what I've got so far. I think there's probably more gold to be mined in terms of reducing runtime memory consumption (I don't like the list_free_deep bit, we should use a context), but functionally it seems complete. I'm off to dinner again, it's in your court to look over some more if you want. (PS: if you want to apply, go ahead, don't forget catversion bump.) Committed, with catversion bump. Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] tg_trigtuple/tg_newtuple settings in AFTER triggers
Set tg_trigtuple/tg_newtuple in AFTER triggers according to whether
old and new tuples were supplied rather than blindly setting them
according to the event type. Per discussion in pgsql-hackers.
http://archives.postgresql.org/pgsql-hackers/2006-07/msg01601.php
If the patch is logically or stylistically flawed then please advise
and I'll rework it. Thanks.
--
Michael Fuhr
Index: src/backend/commands/trigger.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.205
diff -c -r1.205 trigger.c
*** src/backend/commands/trigger.c 31 Jul 2006 20:09:00 - 1.205
--- src/backend/commands/trigger.c 2 Aug 2006 02:02:13 -
***
*** 2090,2100
--- 2090,2107
/*
* Fetch the required OLD and NEW tuples.
*/
+ LocTriggerData.tg_trigtuple = NULL;
+ LocTriggerData.tg_newtuple = NULL;
+ LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
+ LocTriggerData.tg_newtuplebuf = InvalidBuffer;
+
if (ItemPointerIsValid((event-ate_oldctid)))
{
ItemPointerCopy((event-ate_oldctid), (oldtuple.t_self));
if (!heap_fetch(rel, SnapshotAny, oldtuple, oldbuffer, false,
NULL))
elog(ERROR, failed to fetch old tuple for AFTER
trigger);
+ LocTriggerData.tg_trigtuple = oldtuple;
+ LocTriggerData.tg_trigtuplebuf = oldbuffer;
}
if (ItemPointerIsValid((event-ate_newctid)))
***
*** 2102,2107
--- 2109,2124
ItemPointerCopy((event-ate_newctid), (newtuple.t_self));
if (!heap_fetch(rel, SnapshotAny, newtuple, newbuffer, false,
NULL))
elog(ERROR, failed to fetch new tuple for AFTER
trigger);
+ if (LocTriggerData.tg_trigtuple)
+ {
+ LocTriggerData.tg_newtuple = newtuple;
+ LocTriggerData.tg_newtuplebuf = newbuffer;
+ }
+ else
+ {
+ LocTriggerData.tg_trigtuple = newtuple;
+ LocTriggerData.tg_trigtuplebuf = newbuffer;
+ }
}
/*
***
*** 2112,2141
event-ate_event (TRIGGER_EVENT_OPMASK | TRIGGER_EVENT_ROW);
LocTriggerData.tg_relation = rel;
- switch (event-ate_event TRIGGER_EVENT_OPMASK)
- {
- case TRIGGER_EVENT_INSERT:
- LocTriggerData.tg_trigtuple = newtuple;
- LocTriggerData.tg_newtuple = NULL;
- LocTriggerData.tg_trigtuplebuf = newbuffer;
- LocTriggerData.tg_newtuplebuf = InvalidBuffer;
- break;
-
- case TRIGGER_EVENT_UPDATE:
- LocTriggerData.tg_trigtuple = oldtuple;
- LocTriggerData.tg_newtuple = newtuple;
- LocTriggerData.tg_trigtuplebuf = oldbuffer;
- LocTriggerData.tg_newtuplebuf = newbuffer;
- break;
-
- case TRIGGER_EVENT_DELETE:
- LocTriggerData.tg_trigtuple = oldtuple;
- LocTriggerData.tg_newtuple = NULL;
- LocTriggerData.tg_trigtuplebuf = oldbuffer;
- LocTriggerData.tg_newtuplebuf = InvalidBuffer;
- break;
- }
-
MemoryContextReset(per_tuple_context);
/*
--- 2129,2134
---(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] Replication Documentation
[EMAIL PROTECTED] (Peter Eisentraut) wrote: Chris Browne wrote: Here's a patch to add in the material on replication recently discussed on pgsql.docs. I'm not thrilled that there were only a few comments made; I'd be happy to see slicing and dicing to see this made more useful. The agreed-to process was 1. post information on pgsql-general 1.a. solicit comments 2. put information page on web site 3. link from documentation to web site You seem to have short-circuited all that. I don't think this sort of material belongs directly into the PostgreSQL documentation. I don't recall that anyone agreed to do anything in particular, let alone the process being formalized thus. Bruce was looking for there to be some form of overview of the free replication options so he'd have some kind of tale to tell about it. Apparently the issue comes up fairly frequently. 1. I posted information on pgsql-docs 1.a. I solicited comments 2. There being not many of those, I have put together something that could fit into the documentation. I frankly don't care all that much where the material goes; if it ought to be some place else other than in the documentation tree proper, I'm fine with that. -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxdatabases.info/info/postgresql.html How much more helpful could I be than to provide you with the appropriate e-mail address? I could engrave it on a clue-by-four and deliver it to you in Chicago, I suppose. -- Seen on Slashdot... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES] [HACKERS] 8.2 features?)
Gavin Sherry [EMAIL PROTECTED] writes: Is this intentional: template1=# values(1), (2); column1 - 1 2 (2 rows) You bet. VALUES is parallel to SELECT in the SQL grammar, so AFAICS it should be legal anywhere you can write SELECT. The basic productions in the spec's grammar are respectively query specification ::= SELECT [ set quantifier ] select list table expression and table value constructor ::= VALUES row value expression list and both of them link into the rest of the grammar here: simple table ::= query specification | table value constructor | explicit table There is no construct I can find in the spec grammar that allows query specification but not table value constructor. QED. Try some stuff like DECLARE c CURSOR FOR VALUES ... WHERE foo IN (VALUES ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote:
Here's what I've got so far. I think there's probably more gold to be
mined in terms of reducing runtime memory consumption (I don't like the
list_free_deep bit, we should use a context), but functionally it seems
complete.
I checked out memory usage, and it had regressed to about 1.4 GB (from
730 MB as reported yesterday) for 2 million inserts of 2 integers (i.e.
with the php script I've been using).
I know you're not too happy with the attached approach to solving this,
but I'm not sure how creating a memory context is going to help. Part of
the problem is that the various transformXXX functions sometimes return
freshly palloc'd memory, and sometimes return the pointer they are given.
Anyway, with the attached diff, the 2 million inserts case is back to
about 730 MB memory use, and speed is pretty much the same as reported
yesterday (i.e both memory use and performance better than mysql with
innodb tables).
Thoughts?
Thanks,
Joe
Index: src/backend/parser/analyze.c
===
RCS file: /cvsroot/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.341
diff -c -r1.341 analyze.c
*** src/backend/parser/analyze.c 2 Aug 2006 01:59:46 - 1.341
--- src/backend/parser/analyze.c 2 Aug 2006 05:13:20 -
***
*** 872,877
--- 872,878
foreach(lc, exprlist)
{
Expr *expr = (Expr *) lfirst(lc);
+ Expr *p = expr;
ResTarget *col;
col = (ResTarget *) lfirst(icols);
***
*** 885,893
--- 886,898
result = lappend(result, expr);
+ if (expr != p)
+ pfree(p);
+
icols = lnext(icols);
attnos = lnext(attnos);
}
+ list_free(exprlist);
return result;
}
***
*** 2191,2196
--- 2196,2202
for (i = 0; i sublist_length; i++)
{
coltypes[i] = select_common_type(coltype_lists[i], VALUES);
+ list_free(coltype_lists[i]);
}
newExprsLists = NIL;
***
*** 2203,2216
foreach(lc2, sublist)
{
Node *col = (Node *) lfirst(lc2);
- col = coerce_to_common_type(pstate, col, coltypes[i], VALUES);
- newsublist = lappend(newsublist, col);
i++;
}
newExprsLists = lappend(newExprsLists, newsublist);
}
/*
* Generate the VALUES RTE
--- 2209,2228
foreach(lc2, sublist)
{
Node *col = (Node *) lfirst(lc2);
+ Node *new_col;
+
+ new_col = coerce_to_common_type(pstate, col, coltypes[i], VALUES);
+ newsublist = lappend(newsublist, new_col);
+ if (new_col != col)
+ pfree(col);
i++;
}
newExprsLists = lappend(newExprsLists, newsublist);
+ list_free(sublist);
}
+ list_free(exprsLists);
/*
* Generate the VALUES RTE
Index: src/backend/parser/parse_target.c
===
RCS file: /cvsroot/pgsql/src/backend/parser/parse_target.c,v
retrieving revision 1.147
diff -c -r1.147 parse_target.c
*** src/backend/parser/parse_target.c 2 Aug 2006 01:59:47 - 1.147
--- src/backend/parser/parse_target.c 2 Aug 2006 05:13:21 -
***
*** 172,177
--- 172,178
foreach(lc, exprlist)
{
Node *e = (Node *) lfirst(lc);
+ Node *p = e;
/*
* Check for something.*. Depending on the complexity of the
***
*** 188,193
--- 189,195
result = list_concat(result,
ExpandColumnRefStar(pstate, cref,
false));
+ pfree(e);
continue;
}
}
***
*** 203,208
--- 205,211
result = list_concat(result,
ExpandIndirectionStar(pstate, ind,
false));
+ pfree(e);
continue;
}
}
***
*** 210,218
/*
* Not something.*, so transform as a single expression
*/
! result = lappend(result,
! transformExpr(pstate, e));
}
return result;
}
--- 213,224
/*
* Not something.*, so transform as a single expression
*/
! p = transformExpr(pstate, e);
! result = lappend(result, p);
! if (e != p)
! pfree(e);
}
+ list_free(exprlist);
return result;
}
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Anyway, with the attached diff, the 2 million inserts case is back to about 730 MB memory use, and speed is pretty much the same as reported yesterday (i.e both memory use and performance better than mysql with innodb tables). That's all that matters ;) Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(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: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Joe Conway wrote:
Tom Lane wrote:
Here's what I've got so far. I think there's probably more gold to be
mined in terms of reducing runtime memory consumption (I don't like the
list_free_deep bit, we should use a context), but functionally it seems
complete.
I checked out memory usage, and it had regressed to about 1.4 GB (from
730 MB as reported yesterday) for 2 million inserts of 2 integers (i.e.
with the php script I've been using).
I know you're not too happy with the attached approach to solving this,
but I'm not sure how creating a memory context is going to help. Part of
the problem is that the various transformXXX functions sometimes return
freshly palloc'd memory, and sometimes return the pointer they are given.
Anyway, with the attached diff, the 2 million inserts case is back to
about 730 MB memory use, and speed is pretty much the same as reported
yesterday (i.e both memory use and performance better than mysql with
innodb tables).
Of course it also breaks a bunch of regression tests -- I guess that
just points to the fragility of this approach.
This patch retains the memory consumption savings but doesn't break any
regression tests...
Joe
? src/test/regress/sql/insert.sql.new
Index: src/backend/parser/analyze.c
===
RCS file: /cvsroot/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.341
diff -c -r1.341 analyze.c
*** src/backend/parser/analyze.c 2 Aug 2006 01:59:46 - 1.341
--- src/backend/parser/analyze.c 2 Aug 2006 05:48:18 -
***
*** 888,893
--- 888,894
icols = lnext(icols);
attnos = lnext(attnos);
}
+ list_free(exprlist);
return result;
}
***
*** 2191,2196
--- 2192,2198
for (i = 0; i sublist_length; i++)
{
coltypes[i] = select_common_type(coltype_lists[i], VALUES);
+ list_free(coltype_lists[i]);
}
newExprsLists = NIL;
***
*** 2203,2216
foreach(lc2, sublist)
{
Node *col = (Node *) lfirst(lc2);
- col = coerce_to_common_type(pstate, col, coltypes[i], VALUES);
- newsublist = lappend(newsublist, col);
i++;
}
newExprsLists = lappend(newExprsLists, newsublist);
}
/*
* Generate the VALUES RTE
--- 2205,2224
foreach(lc2, sublist)
{
Node *col = (Node *) lfirst(lc2);
+ Node *new_col;
+
+ new_col = coerce_to_common_type(pstate, col, coltypes[i], VALUES);
+ newsublist = lappend(newsublist, new_col);
+ if (new_col != col)
+ pfree(col);
i++;
}
newExprsLists = lappend(newExprsLists, newsublist);
+ list_free(sublist);
}
+ list_free(exprsLists);
/*
* Generate the VALUES RTE
Index: src/backend/parser/parse_target.c
===
RCS file: /cvsroot/pgsql/src/backend/parser/parse_target.c,v
retrieving revision 1.147
diff -c -r1.147 parse_target.c
*** src/backend/parser/parse_target.c 2 Aug 2006 01:59:47 - 1.147
--- src/backend/parser/parse_target.c 2 Aug 2006 05:48:18 -
***
*** 172,177
--- 172,178
foreach(lc, exprlist)
{
Node *e = (Node *) lfirst(lc);
+ Node *p = e;
/*
* Check for something.*. Depending on the complexity of the
***
*** 188,193
--- 189,195
result = list_concat(result,
ExpandColumnRefStar(pstate, cref,
false));
+ pfree(e);
continue;
}
}
***
*** 203,208
--- 205,211
result = list_concat(result,
ExpandIndirectionStar(pstate, ind,
false));
+ pfree(e);
continue;
}
}
***
*** 210,218
/*
* Not something.*, so transform as a single expression
*/
! result = lappend(result,
! transformExpr(pstate, e));
}
return result;
}
--- 213,224
/*
* Not something.*, so transform as a single expression
*/
! p = transformExpr(pstate, e);
! result = lappend(result, p);
! if (e != p)
! pfree(e);
}
+ list_free(exprlist);
return result;
}
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
