Re: [PATCHES] Coding standards
[EMAIL PROTECTED] (Bryce Nesbitt) writes: Alvaro Herrera wrote: People [are] complaining here that we don't teach people here anyway, so hopefully my comments were still useful :-) Yes they are useful. As a new patcher, where should I look for coding standards? How about a little FAQ at the top of the CVS source tree? Though, darn it, I sure like // And my vi is set to: set sw=4 set ts=4 set expandtab Because my corporate projects require spaces not tabs. Note that you can find config for vim and emacs to get them to support the coding standards in: /opt/src/pgsql-HEAD/src/tools/editors/emacs.samples /opt/src/pgsql-HEAD/src/tools/editors/vim.samples For vim, the essentials are thus: :if match(getcwd(), /pgsql) =0 || match(getcwd(), /postgresql) = 0 : set cinoptions=(0 : set tabstop=4 : set shiftwidth=4 :endif The hooks are slightly different (though not by spectacularly much, somewhat surprisingly) for Emacs... -- let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;; http://cbbrowne.com/info/advocacy.html A language that doesn't affect the way you think about programming, is not worth knowing. -- Alan J. Perlis -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Better default_statistics_target
[EMAIL PROTECTED] (Guillaume Smet) writes: On Dec 5, 2007 3:26 PM, Greg Sabino Mullane [EMAIL PROTECTED] wrote: Agreed, this would be a nice 8.4 thing. But what about 8.3 and 8.2? Is there a reason not to make this change? I know I've been lazy and not run any absolute figures, but rough tests show that raising it (from 10 to 100) results in a very minor increase in analyze time, even for large databases. I think the burden of a slightly slower analyze time, which can be easily adjusted, both in postgresql.conf and right before running an analyze, is very small compared to the pain of some queries - which worked before - suddenly running much, much slower for no apparent reason at all. As Tom stated it earlier, the ANALYZE slow down is far from being the only consequence. The planner will also have more work to do and that's the hard point IMHO. Without studying the impacts of this change on a large set of queries in different cases, it's quite hard to know for sure that it won't have a negative impact in a lot of cases. It's a bit too late in the cycle to change that IMHO, especially without any numbers. I have the theory (thus far not borne out by any numbers) that it might be a useful approach to try to go through the DB schema and use what information is there to try to come up with better numbers on a per-column basis. As a first order perspective on things: - Any columns marked unique could keep to having somewhat smaller numbers of bins in the histogram because we know that uniqueness will keep values dispersed at least somewhat. Ditto for SERIAL types. - Columns NOT marked unique should imply adding some bins to the histogram. - Datestamps tend to imply temporal dispersion, ergo somewhat fewer bins. Similar for floats. - Discrete values (integer, text) frequently see less dispersion, - more bins Then could come a second order perspective, where data would actually get sampled from pg_statistics. - If we look at the number of distinct histogram bins used, for a particular column, and find that there are some not used, we might drop bins. - We might try doing some summary statistics to see how many unique values there actually are, on each column, and increase the number of bins if they're all in use, and there are other values that *are* frequently used. Maybe cheaper, if we find that pg_statistics tells us that all bins are in use, and extrapolation shows that there's a lot of the table NOT represented, we increase the number of bins. There might even be a third order analysis, where you'd try to collect additional data from the table, and analytically try to determine appropriate numbers of bins... Thus, we don't have a universal increase in the amount of statistics collected - the added stats are localized to places where there is some reason to imagine them useful. -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://cbbrowne.com/info/nonrdbms.html There was a young lady of Crewe Whose limericks stopped at line two. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] Minor change to pg_dump docs
As suggested to me by Ian Darwin [EMAIL PROTECTED]... Index: doc/src/sgml/ref/pg_dump.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.97 diff -c -u -r1.97 pg_dump.sgml --- doc/src/sgml/ref/pg_dump.sgml 26 Mar 2007 17:23:36 - 1.97 +++ doc/src/sgml/ref/pg_dump.sgml 25 Oct 2007 20:30:10 - @@ -207,7 +207,8 @@ (replaceablecolumn/replaceable, ...) VALUES .../literal). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into -non-productnamePostgreSQL/productname databases. +non-productnamePostgreSQL/productname databases, or for making +dumps that require manual editing. Also, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. -- select 'cbbrowne' || '@' || 'linuxdatabases.info'; http://cbbrowne.com/info/rdbms.html What is the purpose of a person acquiring perfect French pronunciation if they have nothing of value to say in any language? -- Walter Ong ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [RFC] extended txid docs
[EMAIL PROTECTED] (Marko Kreen) writes: Even the realistic code may be too much for general docs, but considering this is not a functionality covered by general SQL textbooks, I think it is worth having. I also put rendered pages up here: http://skytools.projects.postgresql.org/txid/datatype-txid-snapshot.html The data type txid_snapshot stores info about what transaction ids are visible in a particular moment of time. Components are described in... I'd suggest instead: The data type txid_snapshot stores info about transaction ID visibility at a particular moment in time. The components are described in... Smallest txid that may be active. Below it all txids are visible. I'd suggest instead: Earliest transaction ID that is still active. All earlier transactions will either be committed and visible, or rolled back and dead. Next unassigned txid. Above it all txids are unassigned, thus invisible. I'd suggest instead: Next unassigned txid. All txids later than this one are unassigned, and thus invisible. http://skytools.projects.postgresql.org/txid/functions-txid.html The main use of the functions comes from the fact that user can query txids that were committed between 2 snapshots. As this is slightly tricky, it is described here in details on the example of simple queue table. I'd suggest instead: The main use of the functions is to determine which transactions were committed between 2 snapshots. As this is somewhat tricky, a demonstration of their use with a simple queue table is provided. Then let there be table for snapshots, into which a separate process inserts a row with current snapshot after each 5 seconds (for example). Lets call it 'ticks' table: I'd suggest instead: We define a table to store snapshots, called 'ticks', into which a separate process inserts a row indicating a current transaction snapshot every 5 seconds. Now if someone wants to read events from the queue table, then at first he needs to get 2 rows with snapshots from ticks table, then query for txids that were committed between those 2 snapshots on events table. Because the txids and snapshots are tied to PostgreSQL internal MVCC mechanism, the reader can be certain that the txid range queried stays constant. I'd suggest instead: In order to consistently read event data for a particular period, then first the user must read 2 rows from the 'ticks' table that indicate, between them, transaction visibility information, and then search the event table for the txids that were committed between those 2 snapshots. Since the txid and snapshot values are tied to PostgreSQL's internal MVCC mechanism, the reader may be certain that the txid range queried is consistent. But it will have problems if there are long transactions running. That means the snap1.xmin will stay at the position of running transaction and the range will get very large. This can be fixed by fetching only [snap1.xmax..snap2.xmax] by range and fetching possible txids below snap1.xmax explicitly: I'd suggest instead: But the query may be processed inefficiently if there are long-running transactions during the period. That would have the result that the snap1.xmin value would continue to refer to the elderly running transaction, and the range will grow very large. This may be rectified by fetching only [snap1.xmax..snap2.xmax] by range and, and fetching candidate txids earlier than snap1.xmax explicitly: But that is also slightly inefficient as long transactions can be open during several snapshots. So it would be good to pick out exact transactions that were open at the time of snap1 and committed before snap2. That can be done with following query: I'd suggest instead: But that query is also somewhat inefficient because long-running transactions may be open across multiple snapshots. As a result, it may be more efficient to pick out exact transactions that were open at the time of snap1 and committed before snap2. That can be done with following query: As txids returned by last query are certainly interesting, their visiblity does not need additional checks. That means the final query can be in form: I'd suggest instead: As txids returned by that last query are certainly of interest, visibility checking does not require additional checks. That means the final query may of the form: Although the above queries are technically correct, PostgreSQL fails to plan them efficiently. The actual query should always be made with actual values written in. I'd suggest instead: Although of the above queries are all technically correct, PostgreSQL will not plan them efficiently unless specific values are used. The actual query should always be executed using specific values. I believe that those suggested texts describe what you intended, and they should represent better English text for this. -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://www3.sympatico.ca/cbbrowne/spreadsheets.html What you
Re: [PATCHES] Lazy xid assignment V4
[EMAIL PROTECTED] (Florian G. Pflug) writes: Chris Browne wrote: Similarly, does it seem likely that Slony-I users would need to worry about this? No.. it should have zero negative effects for Slony-I. In fact, it will be an advantage in some cases I think. I remember something about troubles with Slony-I if the in-use xids on a intermediate subscribe (one that also acts as a origin) drift too bar away from those on the master. If that still is an issue, than lazy xid assignment might help a bit - it might reduce xid consumption on that intermediate subscriber. The problem isn't usually the growth of XID numbers, but more the general notion that the open transactions tend to prevent successful vacuums from taking place on tables like pg_listener. The pointed issues with pg_listener has, we think, been mostly resolved, as Slony-I has been getting less aggressive about generating dead tuples there. During initial subscription time, there is a pretty big issue, for very large replicas as there is a single big, long-running transaction running on the origin (the transaction pulling initial table data); that is one big XID, and it has history of adversely affecting application of replication data during the catch-up period. If flurries of read-only transactions are no longer generating XIDs that are being included in snapshot information, that *may* be something of a help; for our version 2, there is already a change that excludes XIDs for rolled-back transactions, which gets it mostly around the issues with the Big Initial-Subscription-Related Transaction. In general, from a user's point of view, you only see a different if you look at pg_locks - you will now see NULLs in the transaction column, and might need to look at virtualtransaction for some use-cases. [ thinking ] It's been quite a time since I last worked with slony - but isn't there some code that tried to prevent blocking other queries by looking at pg_locks? Or was that before you could conditionally acquire a lock using SQL? Or am I totally mistaken? Anyway, if you *do* scan pg_locks, than you might want to check those parts of the code. There are no references to pg_locks, unless there's some other view that references it, so that's good news :-). -- cbbrowne,@,linuxdatabases.info http://www3.sympatico.ca/cbbrowne/rdbms.html Windows 98 Roast Specialty Blend coffee beans - just like ordinary gourmet coffee except that processing is rushed to leave in the insect larvae. Also sold under the ``Chock Full o' Bugs'' brand name... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Lazy xid assignment V4
[EMAIL PROTECTED] (Florian G. Pflug) writes: Pavan Deolasee wrote: On 9/4/07, Florian G. Pflug [EMAIL PROTECTED] wrote: Hi Here is an updated patch, following the discussion. The patch can be found at: http://soc.phlo.org/lazyxidassign.v4.patch (I seems I still can't get attachments through to this list) I haven't been able to follow the discussions here, but do I need to worry about its interaction with HOT ? I don't think so. The interactions should be pretty minimal. Similarly, does it seem likely that Slony-I users would need to worry about this? From what I have been seeing, I don't think so, because the transactions that cause the XIDs to get generated that are of interest will all be write transactions, and hence exempt from this: - User transactions that are writing to tables will be exempt :-). - On an origin node, slon connections that write out SYNC events every so often are, obviously, write transactions that will again be exempt. - Transactions that read data from a provider could be affected, as they are indeed read-only, but their XIDs aren't of interest. If I speculate right, then there's not much here to worry about. But best to check... And we'll certainly be testing as this gets applied... -- let name=cbbrowne and tld=acm.org in name ^ @ ^ tld;; http://linuxdatabases.info/info/finances.html Signs of a Klingon Programmer - 3. This machine is GAGH! I need dual Pentium processors if I am to do battle with this code! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] execl() sentinel
[EMAIL PROTECTED] (Neil Conway) writes: On Wed, 2007-07-18 at 17:22 -0400, Alvaro Herrera wrote: I wouldn't know how to look for other variadic functions using NULL sentinels though. You would need something with more knowledge of C than grep has, at any rate. Perhaps you could teach sparse to do this analysis, if it can't do it already... sgrep might be smart enough... I quite like sgrep... -- cbbrowne,@,acm.org http://www3.sympatico.ca/cbbrowne/textsearch.html Since a cat always lands on its feet, and a piece of buttered toast always lands buttered side down, if you strap a piece of buttered toast to the back of a cat, which side will it land on? -- .sig file on rec.humor ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PATCHES] ECPG docs
I noticed the following error in the chapter on ECPG... [EMAIL PROTECTED]:pgsql-HEAD/doc/src/sgml cvs diff -u ecpg.sgml Index: ecpg.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ecpg.sgml,v retrieving revision 1.77 diff -c -u -r1.77 ecpg.sgml cvs diff: conflicting specifications of output style --- ecpg.sgml 23 Oct 2006 18:10:31 - 1.77 +++ ecpg.sgml 4 Dec 2006 15:32:16 - @@ -4665,7 +4665,7 @@ /para para - The preprocessed file can be compiled normally, for example: + The processed file can be compiled normally, for example: programlisting cc -c prog1.c /programlisting -- let name=cbbrowne and tld=cbbrowne.com in String.concat @ [name;tld];; http://linuxdatabases.info/info/linuxxian.html Developmental Psychology Schoolyard behavior resembles adult primate behavior because Ontogeny Recapitulates Phylogeny doesn't stop at birth. -- Mark Miller ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] ECPG docs
[EMAIL PROTECTED] (Heikki Linnakangas) writes: The original looks more correct to me. After the file has been run through the preprocessor, it becomes preprocessed. The other option I was thinking of was postprocessed, because the .c file comes *after* processing. -- let name=cbbrowne and tld=linuxdatabases.info in String.concat @ [name;tld];; http://linuxdatabases.info/info/emacs.html Rules of the Evil Overlord #221. My force-field generators will be located inside the shield they generate. http://www.eviloverlord.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: [PATCHES] Dynamic linking on AIX
[EMAIL PROTECTED] (Bruce Momjian) writes: Great, I have added this to the bottom of the AIX FAQ. Thanks. It seems to me that this also warrants an entry in the release notes. I'd think that an entry in the Source Code Changes section, immediately after the Reduce libraries linked into the backend needlessly might read: * Add shared library support for AIX -- let name=cbbrowne and tld=linuxdatabases.info in String.concat @ [name;tld];; http://linuxdatabases.info/info/wp.html I'd crawl over an acre of 'Visual This++' and 'Integrated Development That' to get to gcc, Emacs, and gdb. Thank you. -- Vance Petree, Virginia Power ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] BUG #2600: dblink compile with SSL missing libraries
[EMAIL PROTECTED] (Tom Lane) writes: Chris Browne [EMAIL PROTECTED] writes: I still need the following, on AIX: -SHLIB_LINK = $(libpq) +SHLIB_LINK = $(libpq) $(LIBS) No you don't --- see recent warthog complaint. We have to filter LIBS down to just the minimum. I'm at a loss, then. - If LIBS is being filtered to the minimum, then shouldn't it be appropriate to add it in here? - There isn't any variable other than LIBS that *does* get bound to include -lssl and -lcrypto - Do we need to add an additional LIBSSL, spattered widely through makefiles, which sometimes gets linked in? - Or do we need some custom DBLINKLIBS, defined in configure, that is only used for dblink? -- (reverse (concatenate 'string moc.enworbbc @ enworbbc)) http://www3.sympatico.ca/cbbrowne/languages.html QT adds to a Linux distribution a level of licencing complexity that nullifies one of the major virtues of Linux: no licencing complexity. -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] BUG #2600: dblink compile with SSL missing libraries
[EMAIL PROTECTED] (Tom Lane) writes: Chris Browne [EMAIL PROTECTED] writes: [EMAIL PROTECTED] (Tom Lane) writes: No you don't --- see recent warthog complaint. We have to filter LIBS down to just the minimum. I'm at a loss, then. - If LIBS is being filtered to the minimum, then shouldn't it be appropriate to add it in here? No, LIBS isn't filtered at all. See my recent commit to sslinfo's Makefile --- I blew it just like this, you should learn from my mistake. OK, the very same change as your recent change to contrib/sslinfo/Makefile works out fine for contrib/dblink/Makefile. That allows a buildfarm run to go through perfectly. I suspect that both Makefiles also need to filter in -lgettext or something similar; see bug #2608, which shows off much the same problem surrounding NLS support. (I'm happy to see that someone's running xlC on AIX 5.3, by the way... I should be getting a copy Real Soon Now, but it may not be soon enough to be helpful :-( ) -- output = reverse(ofni.secnanifxunil @ enworbbc) http://linuxdatabases.info/info/multiplexor.html If God meant us to be vegetarians why'd He make cows out of meat? -- seen on a bumper sticker ---(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] BUG #2600: dblink compile with SSL missing libraries
The change Tom made to contrib/sshinfo/Makefile to support Darwin, adding in $(LIBS), fixed my problem with that contrib module on AIX. I still need the following, on AIX: === RCS file: /projects/cvsroot/pgsql/contrib/dblink/Makefile,v retrieving revision 1.11 diff -u -r1.11 Makefile --- Makefile27 Feb 2006 12:54:38 - 1.11 +++ Makefile6 Sep 2006 19:48:23 - @@ -3,7 +3,7 @@ MODULE_big = dblink PG_CPPFLAGS = -I$(libpq_srcdir) OBJS = dblink.o -SHLIB_LINK = $(libpq) +SHLIB_LINK = $(libpq) $(LIBS) DATA_built = dblink.sql DATA = uninstall_dblink.sql -- (reverse (concatenate 'string moc.enworbbc @ enworbbc)) http://linuxdatabases.info/info/nonrdbms.html Fashion is a form of ugliness so intolerable that we have to alter it every six months. -- Oscar Wilde ---(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] BUG #2600: dblink compile with SSL missing libraries
[EMAIL PROTECTED] (Peter Eisentraut) writes: Am Mittwoch, 30. August 2006 22:57 schrieb Chris Browne: I also seem to recall, in past discussions about library matters, that AIX is more sticky about requiring that libraries be named expressly. ecpglib has SHLIB_LINK = -L../pgtypeslib -lpgtypes $(libpq) \ $(filter -lintl -lssl -lcrypto -lkrb5 -lcrypt -lm, $(LIBS)) $(PTHREAD_LIBS) ifeq ($(PORTNAME), win32) # Link to shfolder.dll instead of shell32.dll SHLIB_LINK += -lshfolder endif Presumably the same would be necessary everywhere else libpq is used. I replaced: SHLIB_LINK = $(libpq) with SHLIB_LINK = $(libpq) $(LIBS) which allowed the compile to get through this. If I add that very same line: SHLIB_LINK = $(libpq) $(LIBS) to contrib/sslinfo/Makefile, it now survives the compile, as well as successfully running through, for contrib, make install and make installcheck. -- cbbrowne,@,cbbrowne.com http://linuxfinances.info/info/unix.html Do you know where your towel is? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] Changes to epgc test
I'm not at all sure that these are the right changes to apply; it somewhat appears to me as though ecpg is supposed to be able to cope with the omissions. In any case, CVS HEAD is breaking on AIX 5.3 with GCC 4.1.1, and these are the places where it's breaking. Index: test1.pgc.in === RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/test/connect/test1.pgc.in,v retrieving revision 1.3 diff -u -r1.3 test1.pgc.in --- test1.pgc.in29 Aug 2006 12:24:51 - 1.3 +++ test1.pgc.in29 Aug 2006 18:42:25 - @@ -26,25 +26,25 @@ exec sql connect to [EMAIL PROTECTED] as main; exec sql disconnect main; - exec sql connect to @localhost as main; + exec sql connect to [EMAIL PROTECTED] as main; exec sql disconnect main; exec sql connect to [EMAIL PROTECTED]:@TEMP_PORT@ as main; exec sql disconnect main; - exec sql connect to @localhost:@TEMP_PORT@ as main; + exec sql connect to [EMAIL PROTECTED]:@TEMP_PORT@ as main; exec sql disconnect main; exec sql connect to connectdb:@TEMP_PORT@ as main; exec sql disconnect main; - exec sql connect to :@TEMP_PORT@ as main; + exec sql connect to connectdb:@TEMP_PORT@ as main; exec sql disconnect main; exec sql connect to tcp:postgresql://localhost:@TEMP_PORT@/connectdb user connectuser identified by connectpw; exec sql disconnect; - exec sql connect to tcp:postgresql://localhost:@TEMP_PORT@/ user connectdb; + exec sql connect to tcp:postgresql://localhost:@TEMP_PORT@/ user connectdb; exec sql disconnect; strcpy(pw, connectpw); Index: test5.pgc === RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/test/connect/test5.pgc,v retrieving revision 1.2 diff -u -r1.2 test5.pgc --- test5.pgc 29 Aug 2006 12:24:51 - 1.2 +++ test5.pgc 29 Aug 2006 18:42:25 - @@ -37,7 +37,7 @@ exec sql connect to 'connectdb' as main; exec sql disconnect main; - exec sql connect to as main user connectdb; + exec sql connect to connectdb as main user connectdb; exec sql disconnect main; exec sql connect to connectdb as main user connectuser/connectdb; @@ -55,7 +55,7 @@ exec sql connect to unix:postgresql://200.46.204.71/connectdb as main user connectuser; exec sql disconnect main; - exec sql connect to unix:postgresql://localhost/ as main user connectdb; + exec sql connect to unix:postgresql://localhost/ as main user connectdb; exec sql disconnect main; /* connect twice */ -- output = reverse(gro.mca @ enworbbc) http://cbbrowne.com/info/linuxxian.html The only ``intuitive'' interface is the nipple. After that, it's all learned. -- Bruce Ediger, [EMAIL PROTECTED] on X interfaces. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] 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] [HACKERS] 8.2 features?
[EMAIL PROTECTED] (Christopher Kings-Lynne) writes: The major downside is that somewhere between 9000 and 1 VALUES-targetlists produces ERROR: stack depth limit exceeded. Perhaps for the typical use-case this is sufficient though. I'm open to better ideas, comments, objections... If the use case is people running MySQL dumps, then there will be millions of values-targetlists in MySQL dumps. Curiosity: How do *does* TheirSQL parse that, and not have the One Gigantic Query blow up their query parser? -- output = reverse(gro.gultn @ enworbbc) http://www.ntlug.org/~cbbrowne/unix.html JOHN CAGE (strapped to table): Do you really expect me to conduct this antiquated tonal system? LEONARD BERNSTEIN: No, Mr. Cage, I expect you to die! [With apologies to music and James Bond fans the world over...] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] plpgsql documentation
An article at WebProNews quoted from the PG docs as to the merits of stored procedures. I have added a bit more material on their merits, as well as making a few changes to improve the introductions to PL/Perl and PL/Tcl. Index: plperl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plperl.sgml,v retrieving revision 2.52 diff -c -u -r2.52 plperl.sgml --- plperl.sgml 10 Mar 2006 19:10:48 - 2.52 +++ plperl.sgml 25 May 2006 22:38:45 - @@ -17,6 +17,12 @@ ulink url=http://www.perl.com;Perl programming language/ulink. /para + para The usual advantage to using PL/Perl is that this allows use, + within stored functions, of the manyfold quotestring +munging/quote operators and functions available for Perl. Parsing + complex strings may be be easier using Perl than it is with the + string functions and control structures provided in PL/pgsql./para + para To install PL/Perl in a particular database, use literalcreatelang plperl replaceabledbname//literal. Index: plpgsql.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.88 diff -c -u -r1.88 plpgsql.sgml --- plpgsql.sgml10 Mar 2006 19:10:48 - 1.88 +++ plpgsql.sgml25 May 2006 22:38:46 - @@ -155,21 +155,36 @@ para That means that your client application must send each query to - the database server, wait for it to be processed, receive the - results, do some computation, then send other queries to the - server. All this incurs interprocess communication and may also - incur network overhead if your client is on a different machine - than the database server. + the database server, wait for it to be processed, receive and + process the results, do some computation, then send further + queries to the server. All this incurs interprocess + communication and will also incur network overhead if your client + is on a different machine than the database server. /para para - With applicationPL/pgSQL/application you can group a block of computation and a - series of queries emphasisinside/emphasis the - database server, thus having the power of a procedural - language and the ease of use of SQL, but saving lots of - time because you don't have the whole client/server - communication overhead. This can make for a - considerable performance increase. + With applicationPL/pgSQL/application you can group a block of + computation and a series of queries emphasisinside/emphasis + the database server, thus having the power of a procedural + language and the ease of use of SQL, but with considerable + savings because you don't have the whole client/server + communication overhead. +/para +itemizedlist + + listitempara Elimination of additional round trips between + client and server /para/listitem + + listitempara Intermediate results that the client does not + need do not need to be marshalled or transferred between server + and client /para/listitem + + listitempara There is no need for additional rounds of query + parsing /para/listitem + +/itemizedlist +para This can allow for a considerable performance increase as +compared to an application that does not use stored functions. /para para Index: pltcl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/pltcl.sgml,v retrieving revision 2.39 diff -c -u -r2.39 pltcl.sgml --- pltcl.sgml 10 Mar 2006 19:10:48 - 2.39 +++ pltcl.sgml 25 May 2006 22:38:46 - @@ -25,22 +25,27 @@ titleOverview/title para -PL/Tcl offers most of the capabilities a function -writer has in the C language, except for some restrictions. +PL/Tcl offers most of the capabilities a function writer has in +the C language, with a few restrictions, and with the addition of +the powerful string processing libraries that are available for +Tcl. /para para -The good restriction is that everything is executed in a safe -Tcl interpreter. In addition to the limited command set of safe Tcl, only -a few commands are available to access the database via SPI and to raise -messages via functionelog()/. There is no way to access internals of the -database server or to gain OS-level access under the permissions of the -productnamePostgreSQL/productname server process, as a C function can do. -Thus, any unprivileged database user may be -permitted to use this language. +One compelling emphasisgood/emphasis restriction is that +everything is executed from within the safety of the context of a +Tcl interpreter. In addition to the limited command set of safe +Tcl, only a few commands are
Re: [PATCHES] COPY LOCK for WAL bypass
[EMAIL PROTECTED] (Simon Riggs) writes: On Sat, 2005-12-10 at 12:07 +, Simon Riggs wrote: Following patch implements COPY ... FROM ... LOCK Patch now updated so that it includes an additional optimization of COPY, so that WAL will not be written in the transaction that created the table. This now gives two fast paths for COPY: 1) COPY LOCK 2) COPY in same transaction (e.g. reloading a pg_dump) I presume that if this doesn't go into WAL, that means that this kind of update wouldn't play with PITR, right? That's presumably something that otta be pretty carefully documented :-). -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://cbbrowne.com/info/nonrdbms.html Microsoft Outlook: Deploying Viruses Has Never Been This Easy! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Should libedit be preferred to libreadline?
[EMAIL PROTECTED] (Tom Lane) writes: Peter Eisentraut [EMAIL PROTECTED] writes: I'm concerned that this still gives nondeterministic behavior. There's no way to say, I want readline, period or I want libedit, period. I'd prefer simple --with-readline and --with-libedit, giving one turns off the other, giving both is an error. OTOH that doesn't provide a way to express I'll take either. Given that I'll-take-either has so far satisfied 99.44% of users, getting rid of it doesn't seem like the best plan. I'll bet that for well over 80% of those 99.44% (was this, by any chance, part of the 80% in the infamous quote 80% of all statistics quoted to prove a point are made up on the spot??? :-)), that what happens is that the satisfied users have taken a prepackaged copy of PostgreSQL. On my home installations, for instance, I'm satisfied with whatever configuration Martin Pitt did when he built Debian packages for PostgreSQL, and there are doubtless a lot of others being satisfied identically. Those that use .rpms that you manage for Red Hat, or that other packagers manage for Mandriva, SuSE, FreeBSD Ports, and such, fall into much the same category of satisfaction where a lot of the 99.44% are being satisfied by the choices of a set of on the order of a dozen individuals that do packaging. Those of us using packages, who are probably quite common, are a big step indirected from this. We don't have a reason to prefer determinism or nondeterminism in this matter; we'll get exactly one choice, namely the choice that one or another of those ~ dozen people make. It might be possible to set things up so that you can specify I'll take either by writing both switches, and further that the order in which you write the switches determines the preference --- though I'm not entirely sure how to do the latter within the autoconf framework. I'll change hats; in my overseeing binaries used at Afilias hat, my vote would be with Peter, for determinism. I'm not particularly interested in seeing psql magically configure itself to slightly prefer one editing library over another; I'd be entirely happy with: --with-readline implying that GNU readline shall be used, and libedit shall not --with-editline implying that libedit shall be used, and GNU readline shall not Supposing we were to change to this deterministic semantic for 8.2, I don't see a grand problem, here. It seems likely to me that it might confuse someone for all of 5 seconds when ./configure reports back Sorry, you don't have readline installed, so --with-readline won't work! In contrast, the nondeterministic approach requires having extra knobs to fiddle in order to prefer one thing to another. I'm not sure but that configure hints are as unattractive as optimizer hints :-). To my mind, giving BIG weight to the opinions of the relatively small set of individuals that manage PostgreSQL packages for the popular distributions of Linux and *BSD seems fairly appropriate. -- let name=cbbrowne and tld=ntlug.org in String.concat @ [name;tld];; http://cbbrowne.com/info/advocacy.html Rules of the Evil Overlord #25. No matter how well it would perform, I will never construct any sort of machinery which is completely indestructible except for one small and virtually inaccessible vulnerable spot. http://www.eviloverlord.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
[PATCHES] AIX FAQ addition
We haven't seen any agreement emerge as to what is causing AIX 5.3 ML3
to fail to successfully build the release candidates.
However, a patch has emerged (thanks, Seneca!) that does allow it to
work, and which I'd expect to be portable (better still!).
We are still actively pursuing why it breaks, but supposing that still
remains outstanding, at least the following would allow AIX users to
better survive a build...
Index: FAQ_AIX
===
RCS file: /projects/cvsroot/pgsql/doc/FAQ_AIX,v
retrieving revision 1.13
diff -c -u -r1.13 FAQ_AIX
--- FAQ_AIX 24 Oct 2005 22:30:35 - 1.13
+++ FAQ_AIX 2 Nov 2005 20:33:01 -
@@ -99,7 +99,7 @@
Last modified date 2005-09-06
If you upgrade to maintenance level 5300-03, that will include this
-fix. Use the command oslevel -r to determine what maintenance level
+fix. Use the command oslevel -r to determine what maintenance level
you are at.
---
From: Christopher Browne [EMAIL PROTECTED]
@@ -113,3 +113,63 @@
http://www.faqs.org/faqs/aix-faq/part4/section-22.html
http://www.han.de/~jum/aix/ldd.c
+---
+From: Christopher Browne [EMAIL PROTECTED]
+Date: 2005-11-02
+
+On AIX 5.3 ML3 (e.g. maintenance level 5300-03), there is some problem
+with the handling of the pointer to memcpy. It is speculated that
+this relates to some linker bug that may have been introduced between
+5300-02 and 5300-03, but we have so far been unable to track down the
+cause.
+
+At any rate, the following patch, which unwraps the function
+reference, has been observed to allow PG 8.1 pre-releases to pass
+regression tests.
+
+The same behaviour (albeit with varying underlying functions to
+blame) has been observed when compiling with either GCC 4.0 or IBM
+XLC.
+
+ per Seneca Cunningham ---
+
+The following patch works on the AIX 5.3 ML3 box here and didn't cause
+any problems with postgres on the x86 desktop. It's just a cleaner
+version of what I tried earlier.
+
+*** dynahash.c.orig Tue Nov 1 19:41:42 2005
+--- dynahash.c Tue Nov 1 20:30:33 2005
+***
+*** 670,676
+
+
+/* copy key into record */
+currBucket-hashvalue = hashvalue;
+! hashp-keycopy(ELEMENTKEY(currBucket), keyPtr, keysize);
+
+
+/* caller is expected to fill the data field on return */
+
+
+--- 670,687
+
+
+/* copy key into record */
+currBucket-hashvalue = hashvalue;
+! if (hashp-keycopy == memcpy)
+! {
+! memcpy(ELEMENTKEY(currBucket), keyPtr, keysize);
+! }
+! else if (hashp-keycopy == strncpy)
+! {
+! strncpy(ELEMENTKEY(currBucket), keyPtr, keysize);
+! }
+! else
+! {
+! hashp-keycopy(ELEMENTKEY(currBucket), keyPtr, keysize);
+! }
+
+
+/* caller is expected to fill the data field on return */
+
+ per Seneca Cunningham ---
--
(reverse (concatenate 'string moc.enworbbc @ enworbbc))
http://cbbrowne.com/info/x.html
Never take life seriously. Nobody gets out alive anyway.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[PATCHES] AIX FAQ update for 5.3 socket address size issue
IBM has addressed the socket address storage issue as of AIX 5.3 maintenance level 5300-03; the following patch adds documentation to FAQ_AIX... [EMAIL PROTECTED]:OXRS/sources/pgsql-HEAD/doc cvs diff -u FAQ_AIX Thursday 12:22:52 Index: FAQ_AIX === RCS file: /projects/cvsroot/pgsql/doc/FAQ_AIX,v retrieving revision 1.12 diff -c -u -r1.12 FAQ_AIX cvs diff: conflicting specifications of output style --- FAQ_AIX 30 Jul 2005 03:39:27 - 1.12 +++ FAQ_AIX 20 Oct 2005 16:23:01 - @@ -83,6 +83,24 @@ IBM will be providing a fix in the next maintenance release (expected in October 2005) with an updated socket.h. --- +PMR29657 was resolved in APAR IY74147: INCOMPATIBILITY BETWEEN +SOCKADDR_UN AND SOCKADDR_STORAGE STRUCT + +APAR information +APAR numberIY74147 +Reported component nameAIX 5.3 +Reported component ID 5765G0300 +Reported release 530 +Status CLOSED PER +PE NoPE +HIPER NoHIPER +Submitted date 2005-07-18 +Closed date2005-07-18 +Last modified date 2005-09-06 + +If you upgrade to maintenance level 5300-03, that will include this +fix. Use the command oslevel -r to determine what maintenance level +you are at. +--- From: Christopher Browne [EMAIL PROTECTED] Date: 2005-07-15 -- output = reverse(moc.enworbbc @ enworbbc) http://www3.sympatico.ca/cbbrowne/oses.html Q: Why did they deprecate a.out support in linux? A: Because a nasty coff is bad for your elf. --- James Simmons ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] AIX FAQ Updates
I believe this change will apply equally to 7.4, 8.0, and CVS HEAD.
Index: FAQ_AIX
===
RCS file: /projects/cvsroot/pgsql/doc/FAQ_AIX,v
retrieving revision 1.11
diff -c -u -r1.11 FAQ_AIX
--- FAQ_AIX 12 Nov 2002 20:02:32 - 1.11
+++ FAQ_AIX 15 Jul 2005 15:51:10 -
@@ -18,3 +18,79 @@
You need libm.a that is in the fileset bos.adt.libm. (Try the
following command.)
$ lslpp -l bos.adt.libm
+
+
+---
+From: Christopher Browne [EMAIL PROTECTED]
+Date: 2005-07-15
+
+On AIX 5.3, there have been some problems getting PostgreSQL to
+compile and run using GCC.
+
+1. You will want to use a version of GCC subsequent to 3.3.2,
+particularly if you use a prepackaged version. We had good
+success with 4.0.1.
+
+Problems with earlier versions seem to have more to do with the
+way IBM packaged GCC than with actual issues with GCC, so that if
+you compile GCC yourself, you might well have success with an
+earlier version of GCC.
+
+2. AIX 5.3 has a problem where sockadr_storage is not defined to be
+large enough. In version 5.3, IBM increased the size of
+sockaddr_un, the address structure for UNIX Domain Sockets, but
+did not correspondingly increase the size of sockadr_storage.
+
+The result of this is that attempts to use UDS with PostgreSQL
+lead to libpq overflowing the data structure. TCP/IP connections
+work OK, but not UDS, which prevents the regression tests from
+working.
+
+ The nonconformance may be readily demonstrated by compiling and
+ running the following C program which calculates and compares the
+ sizes of the various structures:
+
+test_size.c
+
+
+-- snip here - test_size.c
+#include stdio.h
+#include sys/un.h
+#include sys/socket.h
+int main (int argc, char *argv[]) {
+struct sockaddr_storage a;
+struct sockaddr_un b;
+printf(Size of sockadr_storage: %d\n, sizeof(a));
+printf (Size of sockaddr_un:%d\n, sizeof(b));
+
+if (sizeof(a) = sizeof(b))
+printf (Conformant to RFC 3493\n);
+else
+printf (Non-conformant to RFC 3493\n);
+}
+-- snip here - test_size.c
+
+
+The problem was reported to IBM, and is recorded as bug report
+PMR29657.
+
+An immediate resolution is to alter _SS_MAXSIZE to = 1025 in
+/usr/include/sys/socket.h, which will resolve the immediate problem.
+
+It appears that the final resolution will be to alter _SS_MAXSIZE to
+1280, making the size nicely align with page boundaries.
+
+IBM will be providing a fix in the next maintenance release (expected
+in October 2005) with an updated socket.h.
+---
+From: Christopher Browne [EMAIL PROTECTED]
+Date: 2005-07-15
+
+Some of the AIX tools may be a little different from what you may be
+accustomed to on other platforms. If you are looking for a version of
+ldd, useful for determining what object code depends on what
+libraries, the following URLs may help you...
+
+http://www.faqs.org/faqs/aix-faq/part4/section-22.html
+
+http://www.han.de/~jum/aix/ldd.c
\ No newline at end of file
--
(format nil [EMAIL PROTECTED] cbbrowne acm.org)
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. I will not tell my Legions of Terror
And he must be taken alive! The command will be: ``And try to take
him alive if it is reasonably practical.''
http://www.eviloverlord.com/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] tuning for AIX 5L with large memory
[EMAIL PROTECTED] (Neil Conway) writes: Christopher Browne wrote: One of our sysadmins did all the configuring OS stuff part; I don't recall offhand if there was a need to twiddle something in order to get it to have great gobs of shared memory. FWIW, the section on configuring kernel resources under various Unixen[1] doesn't have any documentation for AIX. If someone out there knows which knobs need to be tweaked, would they mind sending in a doc patch? (Or just specifying what needs to be done, and I'll add the SGML.) After verifying that nobody wound up messing with the kernel parameters, here's a docs patch... Index: runtime.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.263 diff -c -u -r1.263 runtime.sgml --- runtime.sgml29 Apr 2004 04:37:09 - 1.263 +++ runtime.sgml26 May 2004 16:35:43 - @@ -3557,6 +3557,26 @@ /listitem /varlistentry + varlistentry + termsystemitem class=osnameAIX//term + indextermprimaryAIX/secondaryIPC configuration// + listitem + para +At least as of version 5.1, it should not be necessary to do +any special configuration for such parameters as +varnameSHMMAX/varname, as it appears this is configured to +allow all memory to be used as shared memory. That is the +sort of configuration commonly used for other databases such +as applicationDB/2/application./para + + para It may, however, be necessary to modify the global + commandulimit/command information in + filename/etc/security/limits/filename, as the default hard + limits for filesizes (varnamefsize/varname) and numbers of + files (varnamenofiles/varname) may be too low. + /para + /listitem + /varlistentry varlistentry termsystemitem class=osnameSolaris//term -- select 'cbbrowne' || '@' || 'acm.org'; http://www.ntlug.org/~cbbrowne/linuxxian.html Hail to the sun god, he sure is a fun god, Ra, Ra, Ra!! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
