[HACKERS] Archive log compression keeping physical log available in the crash recovery
This is a proposal for archive log compression keeping physical log in WAL. In PotgreSQL 8.2, full-page_writes option came back to cut out physical log both from WAL and archive log. To deal with the partial write during the online backup, physical log is written only during the online backup. Although this dramatically reduces the log size, it can risk the crash recovery. If any page is inconsisitent because of the fault, crash recovery doesn't work because full page images are necessary to recover the page in such case. For critical use, especially in commercial use, we don't like to risk the crash recovery chance, while reducing the archive log size will be crucial too for larger databases.WAL size itself may be less critical, because they're reused cyclickly. Here, I have a simple idea to reduce archive log size while keeping physical log in xlog: 1. Create new GUC: full_page_compress, 2. Turn on both the full_page_writes and full_page_compress: physical log will be written to WAL at the first write to a page after the checkpoint, just as conventional full_page_writes ON. 3. Unless physical log is written during the online backup, this can be removed from the archive log. One bit in XLR_BKP_BLOCK_MASK (XLR_BKP_REMOVABLE) is available to indicate this (out of four, only three of them are in use) and this mark can be set in XLogInsert(). With the both full_page_writes and full_page_compress on, both logical log and physical log will also be written to WAL with XLR_BKP_REMOVABLE flag on. Having both physical and logical log in a same WAL is not harmful in the crash recovery. In the crash recovery, physical log is used if it's available. Logical log is used in the archive recovery, as the corresponding physical log will be removed. 4. The archive command (separate binary), removes physical logs if XLR_BKP_REMOVABLE flag is on. Physical logs will be replaced by a minumum information of very small size, which is used to restore the physical log to keep other log records's LSN consistent. 5. The restore command (separate binary) restores removed physical log using the dummy record and restores LSN of other log records. 6. We need to rewrite redo functions so that they ignore the dummy record inserted in 5. The amount of code modification will be very small. As a result, size of the archive log becomes as small as the case with full_page_writes off, while the physical log is still available in the crash recovery, maintaining the crash recovery chance. Comments, questions and any input is welcome. - Koichi Suzuki, NTT Open Source Center -- Koichi Suzuki ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Function execution costs 'n all that
Tom Lane wrote: Mark Dilger <[EMAIL PROTECTED]> writes: Tom Lane wrote: Would a simple constant value be workable, or do we need some more complex model (and if so what)? Consider: ANALYZE myfunc(integer) ON (SELECT myfunc(7)) WITH RATIO 0.03; ... It seems to me that the above system would work perfectly well for collecting the number of rows returned from a set returning function, not just the run times. I don't really think that data collection is the bottleneck. Ahh, I'm not just thinking about data collection. I'm thinking about usability for non-hackers who know enough plpgsql to write a function and then want to train the system to plan for it appropriately. It's a much easier task for a novice user to say "go away and figure out how expensive this thing is" than for a novice to think about things like statistical variance, etc. We don't demand that users have that kind of knowledge to write queries or run analyze on tables, so why would they need that kind of knowledge to write a function? If a constant estimate isn't good enough for you, then you need some kind of model of how the runtime or number of rows varies with the function's inputs ... and I hardly see how something like the above is likely to figure out how to fit a good model. Or at least, if you think it can, then you skipped all the interesting bits. I am (perhaps naively) imagining that the user will train the database over the same query as the one that will actually get used most often in production. In the case that the query modifies the table, the user could train the database over a copy of that table. The data collected by the analyze phase would just be constant stuff like average and stddev. That would make the job of the planner / cost estimator easier, right? It could treat the function as a constant cost function. One other point is that we already know that sampling overhead and measurement error are significant problems when trying to measure intervals on the order of one Plan-node execution. I'm afraid that would get a great deal worse if we try to use a similar approach to timing individual function calls. The query could be run with the arguments passed to "myfunc" being recorded to a temporary table. After the query is complete (and the temporary table populated), data from the temp table could be pulled into memory in batches, with the "myfunc" run on them again in a tight loop. The loop itself could be timed, rather than each iteration. The sum of all the timings for the various loops would then be the final runtime which would be divided by the total number of rows to get the average runtime per call. The downside is that I don't see how you retrieve the standard deviation. (I also don't know if the planner knows how to use standard deviation information, so perhaps this is a non issue.) A further refinement would be to batch the inputs based on properties of the input data. For text, you could run a batch of short text first, a batch of medium second, and a batch of long text last, and use best-fit linear algebra to determine the runtime cost vs. input text length function. I'm not sure how such a refinement would be done for fixed size datatypes. And for some text functions the runtime won't vary with length but with some other property anyway. mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Function execution costs 'n all that
Mark Dilger <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Would a simple constant value be workable, or do we need some more >> complex model (and if so what)? > Consider: > ANALYZE myfunc(integer) ON (SELECT myfunc(7)) WITH RATIO 0.03; > ... > It seems to me that the above system would work perfectly well for > collecting the number of rows returned from a set returning function, > not just the run times. I don't really think that data collection is the bottleneck. If a constant estimate isn't good enough for you, then you need some kind of model of how the runtime or number of rows varies with the function's inputs ... and I hardly see how something like the above is likely to figure out how to fit a good model. Or at least, if you think it can, then you skipped all the interesting bits. One other point is that we already know that sampling overhead and measurement error are significant problems when trying to measure intervals on the order of one Plan-node execution. I'm afraid that would get a great deal worse if we try to use a similar approach to timing individual function calls. regards, tom lane ---(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: [HACKERS] Function execution costs 'n all that
Tom Lane wrote: Would a simple constant value be workable, or do we need some more complex model (and if so what)? Consider: ANALYZE myfunc(integer) ON (SELECT myfunc(7)) WITH RATIO 0.03; ANALYZE myfunc(text,text) ON (SELECT myfunc(mt.a,mt.b) FROM mytable mt) WITH RATIO 1.071; ANALYZE myfunc(text,text) ON ( SELECT myfunc(mt.a,mt.b) FROM mytable mt UNION SELECT myfunc(ot.a,ot.b) FROM othertable ot ) WITH RATIO 0.5; These commands could turn on function timing for the lifespan of the query, with statistics gathered about the given function's runtimes. The "WITH RATIO" clause would be there to translate runtimes (in milliseconds) into units of cpu_operator_cost. The "WITH RATIO" clause could be optional, with a default ratio taken from the postgresql.conf file, if any exists, and finally defaulting to a hardcoded "reasonable" value. Users would be well advised to adopt a consistent policy regarding system load at the time that various analyze functions are run. If the function has side effects, it would be the user's responsibility to not analyze the function unless those side effects are acceptable. The user can only analyze those queries that the user has permissions to run, so there shouldn't be any additional ability to generate side-effects beyond what the user already has permission to do. The syntax might need some adjusting to make the parser happy and to avoid new reserved words. The syntax used above is just an example. It seems to me that the above system would work perfectly well for collecting the number of rows returned from a set returning function, not just the run times. mark ---(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] Modifying and solidifying contrib
Peter Eisentraut wrote: > David Fetter wrote: >> I think it's necessary to get each in its own schema whether we have >> an initdb flag or not. > > In any case, the initdb flag idea is about as much a nonstarter as the > configure flag idea, for the same (packaging) reasons. I don't see your point but it makes my life simpler. So do you opinions on the schema idea? Sincerely, 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Modifying and solidifying contrib
>>> --enable-extension=earthdistance >> And have to parse for each extension? > I don't see this as a big problem. Well I am not really interesting in this. Someone else is welcome to try that. >>> It's really not hard, even for a C n00b like me. :) >> I didn't say it was hard. I said I wasn't interested :) > > I think it's necessary to get each in its own schema whether we have > an initdb flag or not. I can see schemas for certain ones. Tsearch (just as an example as it appears to be going into core) but ltree? Sincerely, Joshua D. Drake > > Cheers, > D -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Modifying and solidifying contrib
David Fetter wrote: > I think it's necessary to get each in its own schema whether we have > an initdb flag or not. In any case, the initdb flag idea is about as much a nonstarter as the configure flag idea, for the same (packaging) reasons. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Modifying and solidifying contrib
On Sun, Jan 28, 2007 at 02:14:36PM -0800, Joshua D. Drake wrote: > > I don't think "all or nothing" is a good way to do this. 500 > > functions in a schema called extensions isn't much more helpful > > than 500 in public. There's a reason namespaces were invented > > long ago, and this is classic use case for same. :) > > I disagree, see my post previously about initializing the extensions > schema to not be accessible initially. It would be there, it would > be loaded, but it would take a superuser to grant ability to access > functions. > > This allows a clean distinction between the modules while allowing > their access on a case by case basis. It's 982 functions as of this writing in CVS TIP's contrib. Do you not get how wacky it is to have that many functions, none of which have any collision-prevention built into their install scripts, in a flat namespace? Then again, you started the PL/PHP project, so maybe I shouldn't ask ;) > > --enable-extension=earthdistance > And have to parse for each extension? > >>> I don't see this as a big problem. > >> Well I am not really interesting in this. Someone else is welcome > >> to try that. > > > > It's really not hard, even for a C n00b like me. :) > > I didn't say it was hard. I said I wasn't interested :) I think it's necessary to get each in its own schema whether we have an initdb flag or not. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(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: [HACKERS] Modifying and solidifying contrib
> > I don't think "all or nothing" is a good way to do this. 500 > functions in a schema called extensions isn't much more helpful than > 500 in public. There's a reason namespaces were invented long ago, > and this is classic use case for same. :) I disagree, see my post previously about initializing the extensions schema to not be accessible initially. It would be there, it would be loaded, but it would take a superuser to grant ability to access functions. This allows a clean distinction between the modules while allowing their access on a case by case basis. > --enable-extension=earthdistance And have to parse for each extension? >>> I don't see this as a big problem. >> Well I am not really interesting in this. Someone else is welcome to >> try that. > > It's really not hard, even for a C n00b like me. :) I didn't say it was hard. I said I wasn't interested :) Sincerely, 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Modifying and solidifying contrib
On Sun, Jan 28, 2007 at 01:58:38PM -0800, Joshua D. Drake wrote: > > >> Well to me that gets a little messy. I mean: > >> > >> pg_catalog,public,,xml2,ltree (just to get a could > >> functions?) etc... > > > > Not as messy as trying to drop or re-create a package when there > > are already 500 functions in the public schema. > > I am not sure I understand the correlation. I am not suggesting we > install anything into public. They would all go into a single > additional schema called extensions or some such. I don't think "all or nothing" is a good way to do this. 500 functions in a schema called extensions isn't much more helpful than 500 in public. There's a reason namespaces were invented long ago, and this is classic use case for same. :) > Obviously the initdb switch could also be selective: > > initdb --enable-extensions > >>> If it were an initdb switch, I'd want to have something more > >>> like > >>> > >>> --enable-extension=earthdistance > >> And have to parse for each extension? > > > > I don't see this as a big problem. > > Well I am not really interesting in this. Someone else is welcome to > try that. It's really not hard, even for a C n00b like me. :) The only trick here, and again it's not a huge one, is to modify the postgresql.conf to have the correct default search_path. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Modifying and solidifying contrib
>> I don't mind this term, BUT, what we need to get across is not just that >> these are extensions, but that they are *standard* extensions, supplied >> with PostgreSQL core code and supported by the PostgreSQL core team. >> This would be analogous with, say, the standard perl modules (like >> Exporter or IO::Handle) that come with the standard perl source >> distribution. If we can get that idea across then we might lower the >> resistance of people like hosting providers to loading them. Furthering the perception is reality rule perhaps the schema should be called std_ext? > > Integrating their docs into the standard PostgreSQL SGML (or XML, > should we go there) docs would go a long, long way toward helping with > this. Agreed. Sincerely, 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Modifying and solidifying contrib
>> Well to me that gets a little messy. I mean: >> >> pg_catalog,public,,xml2,ltree (just to get a could >> functions?) etc... > > Not as messy as trying to drop or re-create a package when there are > already 500 functions in the public schema. I am not sure I understand the correlation. I am not suggesting we install anything into public. They would all go into a single additional schema called extensions or some such. Obviously the initdb switch could also be selective: initdb --enable-extensions >>> If it were an initdb switch, I'd want to have something more like >>> >>> --enable-extension=earthdistance >> And have to parse for each extension? > > I don't see this as a big problem. Well I am not really interesting in this. Someone else is welcome to try that. Sincerely, 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] weird buildfarm failures on arm/mipsel and --with-tcl
I wrote:
> One possibility for fixing it is that maybe we should be making an
> effort to execute Tcl_Finalize() before exiting the backend. If so,
> having pltcl set up an on_proc_exit callback to do it would be the
> appropriate thing. This is all speculation though.
Just for grins I tried this, and I can see by strace'ing that it changes
the process-shutdown-time behavior quite a lot: the secondary thread now
exits first, apparently after being told to via a message from the
primary. So I think this might indeed be something good to do. Would
you try the attached patch and see if it changes the behavior on your
systems? (This patch is very ugly and will draw compiler warnings, but
don't worry about that yet.)
regards, tom lane
Index: pltcl.c
===
RCS file: /cvsroot/pgsql/src/pl/tcl/pltcl.c,v
retrieving revision 1.108
diff -c -r1.108 pltcl.c
*** pltcl.c 4 Oct 2006 00:30:14 - 1.108
--- pltcl.c 28 Jan 2007 21:45:40 -
***
*** 26,31
--- 26,32
#include "fmgr.h"
#include "nodes/makefuncs.h"
#include "parser/parse_type.h"
+ #include "storage/ipc.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
***
*** 245,250
--- 246,253
/
if (!pltcl_be_init_done)
{
+ on_proc_exit(Tcl_Finalize, 0);
+
if (SPI_connect() != SPI_OK_CONNECT)
elog(ERROR, "SPI_connect failed");
pltcl_init_load_unknown(pltcl_norm_interp);
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Modifying and solidifying contrib
On Sun, Jan 28, 2007 at 04:52:27PM -0500, Andrew Dunstan wrote: > > > Michael Glaesemann wrote: > > > >On Jan 28, 2007, at 11:25 , Joshua D. Drake wrote: > > > >>David Fetter wrote: > >>>Not so great. SQL:2003 has a special meaning for the word "module." > >> > >>Yeah I saw mention of that in another thread, but I really didn't like > >>the word plugins. Do you have another thought? Extensions? > > > >"Extensions" would tie in nicely with its common use in the docs, > >especially wrt pgxs: > > I don't mind this term, BUT, what we need to get across is not just that > these are extensions, but that they are *standard* extensions, supplied > with PostgreSQL core code and supported by the PostgreSQL core team. > This would be analogous with, say, the standard perl modules (like > Exporter or IO::Handle) that come with the standard perl source > distribution. If we can get that idea across then we might lower the > resistance of people like hosting providers to loading them. Integrating their docs into the standard PostgreSQL SGML (or XML, should we go there) docs would go a long, long way toward helping with this. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Modifying and solidifying contrib
Michael Glaesemann wrote: On Jan 28, 2007, at 11:25 , Joshua D. Drake wrote: David Fetter wrote: Not so great. SQL:2003 has a special meaning for the word "module." Yeah I saw mention of that in another thread, but I really didn't like the word plugins. Do you have another thought? Extensions? "Extensions" would tie in nicely with its common use in the docs, especially wrt pgxs: I don't mind this term, BUT, what we need to get across is not just that these are extensions, but that they are *standard* extensions, supplied with PostgreSQL core code and supported by the PostgreSQL core team. This would be analogous with, say, the standard perl modules (like Exporter or IO::Handle) that come with the standard perl source distribution. If we can get that idea across then we might lower the resistance of people like hosting providers to loading them. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Modifying and solidifying contrib
On Sun, Jan 28, 2007 at 10:10:14AM -0800, Joshua D. Drake wrote: > David Fetter wrote: > > On Sat, Jan 27, 2007 at 09:49:25PM -0800, Joshua D. Drake wrote: > >> Tom Lane wrote: > >>> "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > So what are we thinking here? Along with my suggestion of > extensions / contrib that we modify initdb to load an > extensions schema with all extensions into template1? > >>> No, I don't think so. If you do that it's effectively moving > >>> all that stuff into core, especially if you haven't provided a > >>> way to turn it off. > >> O.k. any thoughts there? What if we didn't make the extensions > >> schema PUBLIC? Meaning that explicits rights would have to be > >> given for the extensions to be used by anyone but a super user? > > > > Whether they're auto-installable or not, I'd vote for putting each > > one in its own schema by default. That way, people can get an > > excellent idea just by looking at what schemas exist what > > extensions are installed in a given DB, and it's fairly > > straight-forward to remove the thing simply by dropping the schema > > cascade. > > Well to me that gets a little messy. I mean: > > pg_catalog,public,,xml2,ltree (just to get a could > functions?) etc... Not as messy as trying to drop or re-create a package when there are already 500 functions in the public schema. > >> Obviously the initdb switch could also be selective: > >> > >> initdb --enable-extensions > > > > If it were an initdb switch, I'd want to have something more like > > > > --enable-extension=earthdistance > > And have to parse for each extension? I don't see this as a big problem. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] docbook question: how to center cell in tables ?
Oleg Bartunov wrote: > I'm wonderin why global setting > doesn't work. It's not implemented. Feel free to code it. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] weird buildfarm failures on arm/mipsel and --with-tcl
Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > FWIW - I removed --with-tcl from quagga's configuration about two weeks > ago and it has not failed(for that reason) again. So the issue most > definitly looks like plptcl related ... I poked around a bit in the Tcl sources, and as far as I can tell it will never call atexit() on a Linux platform. So that theory isn't right. However, what I found out via strace'ing is that on my own Linux machine, Tcl launches a second thread as soon as it's loaded. This thread just does a select() and sits there. I am not certain but I think it must be the "notifier" facility seen in the Tcl sources. What seems likely to me is that the "child process" we see in your ps output is actually this notifier thread (can you confirm that it's a thread via additional ps arguments?), and that for some reason the shutdown of the notifier isn't working right, or maybe the notifier is dying for some reason and then the main thread can't exit because it expects the notifier to respond. Can you "strace -f" the Tcl test and see what happens? The way I did it was createdb pl_regression createlang pltcl pl_regression psql pl_regression -- in another window, identify PID of connected backend, then strace -f -p PID 2>strace.out psql> \i .../pltcl_setup.sql psql> \i .../pltcl_queries.sql psql> \q One possibility for fixing it is that maybe we should be making an effort to execute Tcl_Finalize() before exiting the backend. If so, having pltcl set up an on_proc_exit callback to do it would be the appropriate thing. This is all speculation though. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] DROP FUNCTION failure: cache lookup failed for relation
> It seems a general solution would involve having dependency.c take > exclusive locks on all types of objects (not only tables) as it scans > them and decides they need to be deleted later. And when adding a > pg_depend entry, we'd need to take a shared lock and then recheck to > make sure the object still exists. This would be localized in > dependency.c, but it still seems like quite a lot of mechanism and > cycles added to every DDL operation. And I'm not at all sure that > we'd not be opening ourselves up to deadlock problems. > > I'm a bit tempted to fix only the table case and leave the handling of > non-table objects as is. Comments? > > regards, tom lane The taking of DDL locks is very unlikely to create a performance problem for anyone as DML statements typically far outnumber DDL statements. Further, in my experience, DDL statements are very carefully thought through and are usually either completely automated by well crafted programs or are performed by one person at a time - the DBA. I therefore conclude that any deadlock risk is triflingly small and would be a self-inflicted circumstance. Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] UUID patch broke win32
On Sun, 2007-01-28 at 20:47 +0100, Magnus Hagander wrote:
> uuid_t is defined to UUID in the win32 platform SDK header files. I
> would suggest we use pguuid_t or something like that instead.
We could possibly try to workaround it by #undef'ing any existing uuid_t
definitions before we supply our own, but per out-of-band discussion
with Magnus, it seems safer to just use a different type name
altogether. Attached is a patch that renames uuid_t to pg_uuid_t (and
makes some other minor cleanup of uuid.c), which I'll apply shortly.
-Neil
Index: src/backend/utils/adt/uuid.c
===
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/utils/adt/uuid.c,v
retrieving revision 1.1
diff -c -p -r1.1 uuid.c
*** src/backend/utils/adt/uuid.c 28 Jan 2007 16:16:52 - 1.1
--- src/backend/utils/adt/uuid.c 28 Jan 2007 20:14:12 -
***
*** 35,84
/* uuid size in bytes */
#define UUID_LEN 16
! /* The uuid_t type is declared as struct uuid_t in uuid.h */
! struct uuid_t
{
char data[UUID_LEN];
};
! static void uuid_data_from_string(const char *source, unsigned char *data);
! static void string_from_uuid_data(const char *fmt, const char *data, char *uuid_str);
static bool parse_uuid_string(const char *fmt, const char *chk_fmt,
! const char *source, unsigned char *data);
static bool is_valid_format(const char *source, const char *fmt);
! static int32 uuid_internal_cmp(uuid_t *arg1, uuid_t *arg2);
Datum
uuid_in(PG_FUNCTION_ARGS)
{
char *uuid_str = PG_GETARG_CSTRING(0);
! uuid_t *uuid;
! uint8 data[UUID_LEN];
! uuid_data_from_string(uuid_str, data);
! uuid = (uuid_t *) palloc(sizeof(uuid_t));
! memcpy(uuid->data, data, UUID_LEN);
PG_RETURN_UUID_P(uuid);
}
Datum
uuid_out(PG_FUNCTION_ARGS)
{
! uuid_t *uuid = (uuid_t *) PG_GETARG_POINTER(0);
char *uuid_str;
uuid_str = (char *) palloc(PRINT_SIZE);
! string_from_uuid_data(UUID_FMT1, uuid->data, uuid_str);
PG_RETURN_CSTRING(uuid_str);
}
/* string to uuid convertor by various format types */
static void
! uuid_data_from_string(const char *source, unsigned char *data)
{
! if (!parse_uuid_string(UUID_FMT1, UUID_CHK_FMT1, source, data) &&
! !parse_uuid_string(UUID_FMT2, UUID_CHK_FMT2, source, data) &&
! !parse_uuid_string(UUID_FMT3, UUID_CHK_FMT3, source, data))
{
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
--- 35,83
/* uuid size in bytes */
#define UUID_LEN 16
! /* pg_uuid_t is declared to be struct pg_uuid_t in uuid.h */
! struct pg_uuid_t
{
char data[UUID_LEN];
};
! static void string_to_uuid(const char *source, pg_uuid_t *uuid);
! static void uuid_to_string(const char *fmt, const pg_uuid_t *uuid,
! char *uuid_str);
static bool parse_uuid_string(const char *fmt, const char *chk_fmt,
! const char *source, char *data);
static bool is_valid_format(const char *source, const char *fmt);
! static int uuid_internal_cmp(const pg_uuid_t *arg1, const pg_uuid_t *arg2);
Datum
uuid_in(PG_FUNCTION_ARGS)
{
char *uuid_str = PG_GETARG_CSTRING(0);
! pg_uuid_t *uuid;
! uuid = (pg_uuid_t *) palloc(sizeof(*uuid));
! string_to_uuid(uuid_str, uuid);
PG_RETURN_UUID_P(uuid);
}
Datum
uuid_out(PG_FUNCTION_ARGS)
{
! pg_uuid_t *uuid = PG_GETARG_UUID_P(0);
char *uuid_str;
uuid_str = (char *) palloc(PRINT_SIZE);
! uuid_to_string(UUID_FMT1, uuid, uuid_str);
PG_RETURN_CSTRING(uuid_str);
}
/* string to uuid convertor by various format types */
static void
! string_to_uuid(const char *source, pg_uuid_t *uuid)
{
! if (!parse_uuid_string(UUID_FMT1, UUID_CHK_FMT1, source, uuid->data) &&
! !parse_uuid_string(UUID_FMT2, UUID_CHK_FMT2, source, uuid->data) &&
! !parse_uuid_string(UUID_FMT3, UUID_CHK_FMT3, source, uuid->data))
{
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
*** is_valid_format(const char *source, cons
*** 126,132
/* parse the uuid string to a format and return true if okay */
static bool
parse_uuid_string(const char *fmt, const char *chk_fmt,
! const char *source, unsigned char *data)
{
int result = sscanf(source, fmt,
&data[0], &data[1], &data[2], &data[3], &data[4],
--- 125,131
/* parse the uuid string to a format and return true if okay */
static bool
parse_uuid_string(const char *fmt, const char *chk_fmt,
! const char *source, char *data)
{
int result = sscanf(source, fmt,
&data[0], &data[1], &data[2], &data[3], &data[4],
*** parse_uuid_string(const char *fmt, const
*** 139,146
/* create a string representation of the uuid */
static void
! string_from_uuid_data(const char *fmt, const char *data, char *uuid_str)
{
snprintf(uuid_str, PRINT_SIZE, fmt,
data[0], data[1], data[2], data[3], data[4],
data[5], data[6], data[7], data[8], da
[HACKERS] UUID patch broke win32
Seems the UUID patch broke Windows builds. http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=snake&dt=2007-01-28%2018:30:01 uuid_t is defined to UUID in the win32 platform SDK header files. I would suggest we use pguuid_t or something like that instead. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] docbook question: how to center cell in tables ?
On Sun, 28 Jan 2007, Peter Eisentraut wrote: Oleg Bartunov wrote: I want to center a content of table's cell Put the align attributes into the entry elements. I'm wonderin why global setting doesn't work. Specifying that fro each entry element looks very inefficient. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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
[HACKERS] plpgpsm 0.1 is available
Hello SQL/PSM standard defines language for stored procedures. PL/pgPSM is postgresql's implementation of SQL/PSM. I uploaded first version, which can be tested without patching core files. Simply download and compile it. Some samples you can find on http://postgresql.interweb.cz/index.php/SQL/PSM#Supported_statements . Installation notes are http://postgresql.interweb.cz/index.php/SQL/PSM#Installation . Current implementation doesn't support only two statements: resignal and get diagnostics and is comparable with SQL/PSM in MySQL 5.x. I am invite any testers. Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] docbook question: how to center cell in tables ?
Oleg Bartunov wrote: > I want to center a content of table's cell Put the align attributes into the entry elements. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Implied Functional index use (redux)
Gregory Stark <[EMAIL PROTECTED]> writes: > I've thought further about this and I believe the problem is simpler than we > were thinking previously. All we need is one boolean flag on the equality > operator for the data type (or perhaps it would be more convenient to have it > on the operator class) that indicates that two objects will never compare > equal unless they're binary equal. Well, we could simplify it that far, but that lets out float, numeric, most or all of the geometry types, and I'm not too sure I care to promise it for text either (think Unicode combining characters...). So really that's too simple IMHO. regards, tom lane ---(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: [HACKERS] Modifying and solidifying contrib
David Fetter wrote: > On Sat, Jan 27, 2007 at 09:49:25PM -0800, Joshua D. Drake wrote: >> Tom Lane wrote: >>> "Joshua D. Drake" <[EMAIL PROTECTED]> writes: So what are we thinking here? Along with my suggestion of extensions / contrib that we modify initdb to load an extensions schema with all extensions into template1? >>> No, I don't think so. If you do that it's effectively moving all >>> that stuff into core, especially if you haven't provided a way to >>> turn it off. >> O.k. any thoughts there? What if we didn't make the extensions >> schema PUBLIC? Meaning that explicits rights would have to be given >> for the extensions to be used by anyone but a super user? > > Whether they're auto-installable or not, I'd vote for putting each one > in its own schema by default. That way, people can get an excellent > idea just by looking at what schemas exist what extensions are > installed in a given DB, and it's fairly straight-forward to remove > the thing simply by dropping the schema cascade. Well to me that gets a little messy. I mean: pg_catalog,public,,xml2,ltree (just to get a could functions?) etc... > >> Obviously the initdb switch could also be selective: >> >> initdb --enable-extensions > > If it were an initdb switch, I'd want to have something more like > > --enable-extension=earthdistance And have to parse for each extension? Sincerely, Joshua D. Drake > > Cheers, > D -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Implied Functional index use (redux)
"Simon Riggs" <[EMAIL PROTECTED]> writes: > In a thread in July last year, I raised the possibility of transforming > a query to allow functional indexes to be utilised automatically. > http://archives.postgresql.org/pgsql-hackers/2006-07/msg00323.php > > This idea can work and has many benefits, but there are some > complexities. I want to summarise those issues first, then make a more > practical and hopefully more acceptable proposal. > > Taken together the complexities would have lead us to have additional > TRANSFORMABLE clauses on TYPEs, FUNCTIONs and potentially encoding > schemes. All of which, I agree, just too much complexity to allow this > to be specified. I've thought further about this and I believe the problem is simpler than we were thinking previously. All we need is one boolean flag on the equality operator for the data type (or perhaps it would be more convenient to have it on the operator class) that indicates that two objects will never compare equal unless they're binary equal. As long as we know that binary unequal implies operator class unequal then we can know that any immutable function will always return the same value for any two equal data. And therefore we can be guaranteed that any elements we want to look up with "col = $1" will be included in the elements returned by looking up "foo(col) = foo($1)". We don't need to know anything about foo() beyond its immutability. If we want to be able to do inequality lookups as well then we'll have to have a flag on the function indicating it's "order preserving". That is for any values a, b the property a f(a)<=f(b) holds. The problem is that you can't guarantee that for all operator classes since someone can always come along and define a new operator class with a new ordering that breaks your guarantee. I suppose we could just have the flag indicate that the property holds for the default operator class for the argument data types. But even if we only handled equality I think it would still be a very useful feature. It would allow doing things like creating an index on soundex(lastname) or crc32(url) and have them be automatically useful without altering queries. And it would mean you wouldn't have to create redundant indexes on lastname and lower(lastname). We could always look to generalize it to inequalities later. -- Gregory Stark EnterpriseDB http://www.enterprisedb.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
[HACKERS] docbook question: how to center cell in tables ?
Hi there, I want to center a content of table's cell (ex. http://mira.sai.msu.su/~megera/pgsql/lockmatrix/c2.html), but jade seems doesn't generate a right html code. sgml code looks like: Lock Matrix I run jade -d stylesheet.dsl -i output-html -t sgml test.sgml What I'm doing wrong ? Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] VC2005 build and pthreads
Joachim Wieland wrote: > On Fri, Jan 26, 2007 at 10:10:20PM +0100, Magnus Hagander wrote: I would like to build pg on VC2005. How do I use pthreads that is mentioned in the README file. Do I need the DLL? Sources? LIB? Where do I install or copy them.. > >>> Err, pthreads is a threads library for Unix, I don't think Windows has >>> that, nor can I think of a situation where you'd need to worry about >>> threads anyway? > >> There is a pthreads for win32 as well. >> However, you don't need it to build, unless you build ecpg. I forgot to >> update the README when I put that patch in .-) > > Actually you can build ecpg without pthreads as well, but you lose > thread-safety. The problem is that ecpg and libpq do different things on > Windows if you define ENABLE_THREAD_SAFETY. > > ENABLE_THREAD_SAFETY is set to 1 in pg_config.h.win32, with it being > defined, ecpg needs the pthreads libraries, while libpq uses the stub > functions from pthread-win32.c. Libpq can do that because it doesn't use > pthread_(get|set)specific. If we implemented those functions by means of the > native Windows API as well, they could also be used by ecpg and we would not > need the pthread libs on Windows anymore. > > Magnus, can we? ;-) Absolutely, I'll be happy to test your patch :) Anyway. We hard-code thread-safety to on for Win32, because win32 is a threaded platform in general - almost everything can be exposed to threading even if they don't want it. For example a VB program that has no threading at all can still run with multiple threads because of the framework. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] VC2005 build and pthreads
On Fri, Jan 26, 2007 at 10:10:20PM +0100, Magnus Hagander wrote: > >> I would like to build pg on VC2005. How do I use pthreads that is > >> mentioned in the README file. Do I need the DLL? Sources? LIB? > >> Where do I install or copy them.. > > Err, pthreads is a threads library for Unix, I don't think Windows has > > that, nor can I think of a situation where you'd need to worry about > > threads anyway? > There is a pthreads for win32 as well. > However, you don't need it to build, unless you build ecpg. I forgot to > update the README when I put that patch in .-) Actually you can build ecpg without pthreads as well, but you lose thread-safety. The problem is that ecpg and libpq do different things on Windows if you define ENABLE_THREAD_SAFETY. ENABLE_THREAD_SAFETY is set to 1 in pg_config.h.win32, with it being defined, ecpg needs the pthreads libraries, while libpq uses the stub functions from pthread-win32.c. Libpq can do that because it doesn't use pthread_(get|set)specific. If we implemented those functions by means of the native Windows API as well, they could also be used by ecpg and we would not need the pthread libs on Windows anymore. Magnus, can we? ;-) Joachim ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] How to gain R/W access to developers wiki?
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Surely, I've created one (nickname: Nikolay) and are trying to do things being logged in :-) You should be able to edit now with the "Nikolay" account. If anyone else is having problems, please send me an email. I'll work on documenting this better when I return, but for those with psql access, just add the user in question to the user_groups table with "pgdev" acceess. Alot of people are having trouble editing pages on the wiki. They keep coming to me too :) There seems to be something foobar with the permissions that are being set when users create new accounts ..? Greg: could you have a look into this? regards, Lukas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Modifying and solidifying contrib
Kevin Barnard wrote: > The basic idea of my proposal would be to have a function that checks > for the existence of C libraries. The next piece I would need would > be a mechanism for psql to halt the restore SQL script if these > checks failed. Finally I would need to add a switch to tell pg_dump > to do the checks first thereby causing the restore to fail, hopefully > with a nice message that clues the admin that something is wrong. You already get this today. If you create a C function and the library doesn't exist, you get an error, and if you use the right flags for psql or pg_restore, the restoration aborts. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Modifying and solidifying contrib
Joshua D. Drake wrote: > Add directory /modules Moving stuff around in the source code probably won't matter much at all since few users will actually look into the source code. What we need is better presentation and accessibility of the installed items, for example documentation and installation and upgrade handling. -- 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
