[HACKERS] Re: SIGTERM/FATAL error
At 08:59 PM 11-03-2001 -0500, Bruce Momjian wrote: How about "Connection terminated by administrator", or something like that. I prefer something closer to the truth. e.g. "Received SIGTERM, cancelling query and exiting" (assuming it actually cancels the query). But maybe I'm weird. Cheerio, Link. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Internationalized error messages
On Fri, Mar 09, 2001 at 05:57:13PM +0100, Peter Eisentraut wrote: Karel Zak writes: For transaltion to other languages I not sure with gettext() stuff on backend -- IMHO better (faster) solution will postgres system catalog with it. elog(ERROR, "cannot open message catalog table"); Sure, and what: elog(ERROR, gettext("can't set LC_MESSAGES")); We can generate our system catalog for this by simular way as gettext, it's means all messages can be in sources in English too. But this is reflexion, performance test show more. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Internationalized dates (was Internationalized error messages)
On Fri, Mar 09, 2001 at 10:58:02PM +0100, Kaare Rasmussen wrote: Now you're talking about i18n, maybe someone could think about input and output of dates in local language. As fas as I can tell, PostgreSQL will only use English for dates, eg January, February and weekdays, Monday, Tuesday etc. Not the local name. May be add special mask to to_char() and use locales for this, but I not sure. It isn't easy -- arbitrary size of strings, to_char's cache problems -- more and more difficult is parsing input with locales usage. The other thing is speed... A solution is use number based dates without names :-( Karel PS. what other SQL engines, support it? -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Vaccuum Failure w/7.1beta4 on Linux/Sparc
While testing some existing database applications on 7.1beta4 on my Sparc 20 running Debian GNU/Linux 2.2, I got the following error on attempting to do a vacuum of a table: NOTICE: FlushRelationBuffers(jobs, 1399): block 953 is referenced (private 0, global 1) ERROR! Can't vacuum table Jobs! ERROR: VACUUM (repair_frag): FlushRelationBuffers returned -2 The first line is the error message from pgsql, while the second line is the error message from my application (using perl Pg module) reporting the error message returned. It appears that this should only be a warning (i.e. NOTICE, not FATAL or ERROR), but it caused the Pg module to throw an error anyway. My application of course checks for errors, see the error thrown by Pg and dies assuming the error was fatal. This error occurred after a load of about 50k records into the referenced table, a load of 50k records total into a few other tables, and then a few clean up queries. The part of the application I was testing is a database load from another (old, closed source) database. The vacuum was at the end of the of the database load, as part of final cleanup routines. So, is this a problem with pgsql in general, specific to Linux/Sparc, or a bug in Pg causing it to be too paranoid? Thanks. --- | "For to me to live is Christ, and to die is gain."| |--- Philippians 1:21 (KJV) | --- | Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ | --- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Banner links not working (fwd)
At 06:11 06/03/01 -0500, Vince Vielhaber wrote: This just came to the webmaster mailbox: Sorry for the delay, busy week... --- Most of the top banner links on http://jdbc.postgresql.org (like Documentation, Tutorials, Resources, Development) throw up 404s if followed. Thought you ought to know. Still trying to find the correct driverClass/connectString for the Postgres JDBC driver... That should be on the site already (infact its been on there for about 3 years now ;-) --- Who maintains this site? It's certainly not me. From looking at the page I'm guessing Peter Mount, can we get some kind of prominent contact info on it? I've had a few emails on it so far. Bottom of every page (part of the template) is both my name and email address ;-) Peter Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Internationalized error messages
At 23:49 08/03/01 +0100, Peter Eisentraut wrote: I really feel that translated error messages need to happen soon. Managing translated message catalogs can be done easily with available APIs. However, translatable messages really require an error code mechanism (otherwise it's completely impossible for programs to interpret error messages reliably). I've been thinking about this for much too long now and today I finally settled to the simplest possible solution. Let the actual method of allocating error codes be irrelevant for now, although the ones in the SQL standard are certainly to be considered for a start. Essentially, instead of writing snip On the protocol front, this could be pretty easy to do. Instead of "message text" we'd send a string "XYZ01: message text". Worst case, we pass this unfiltered to the client and provide an extra function that returns only the first five characters. Alternatively we could strip off the prefix when returning the message text only. Most other DB's (I'm thinking of Oracle here) pass the code unfiltered to the client anyhow. Saying that, it's not impossible to get psql and other interactive clients to strip the error code anyhow. At the end, the i18n part would actually be pretty easy, e.g., elog(ERROR, "XYZ01", gettext("stuff happened")); Comments? Better ideas? A couple of ideas. One, if we have a master list of error codes, we need to have this in an independent format (ie not a .h file). However the other idea is to expand on the JDBC's errors.properties files. Being ascii/unicode, the format will work with just some extra code to implement them in C. Brief description: The ResourceBundle's handle one language per file. From a base filename, each different language has a file based on: filename_la_ct.properties where la is the ISO 2 character language, and ct is the ISO 2 character country code. For example: messages_en_GB.properties messages_en_US.properties messages_en.properties messages_fr.properties messages.properties Now, here for the english locale for England it checks in this order: messages_en_GB.properties messages_en.properties messages.properties. In each file, a message is of the format: key=message, and each parameter passed into the message written like {1} {2} etc, so for example: fathom=Unable to fathom update count {0} Now apart from the base file (messages.properties in this case), the other files are optional, and an entry only needs to be in there if they are present in that language. So, in french, fathom may be translated, but then again it may not (in JDBC it isn't). Then it's not included in the file. Any new messages can be added to the base language, but only included as and when they are translated. Peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Banner links not working (fwd)
On Mon, 12 Mar 2001, Peter Mount wrote: Bottom of every page (part of the template) is both my name and email address ;-) Can we slightly enlarge the font? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Internationalized error messages
Karel Zak writes: For transaltion to other languages I not sure with gettext() stuff on backend -- IMHO better (faster) solution will postgres system catalog with it. elog(ERROR, "cannot open message catalog table"); Sure, and what: elog(ERROR, gettext("can't set LC_MESSAGES")); We can generate our system catalog for this by simular way as gettext, it's means all messages can be in sources in English too. When there is an error condition in the backend, the last thing you want to do (and are allowed to do) is accessing tables. Also keep in mind that we want to internationalize other parts of the system as well, such as pg_dump and psql. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CORBA and PG
Hi, This was mentioned a while back on this list (pg hackers) - thanks to whoever provided the pointer :-) I have not yet looked at it in depth, though that is high on my list of TO-DO's. It is released under an apache style licence. Any reason why there are no pointers to it on the PostgreSQL related projects or interfaces pages? project page: http://4suite.org/index.epy docs on ODMG support: http://services.4Suite.org/documents/4Suite/4ODS-userguide From project page: "4Suite is a collection of Python tools for XML processing and object database management. It provides support for XML parsing, several transient and persistent DOM implementations, XPath expressions, XPointer, XSLT transforms, XLink, RDF and ODMG object databases. 4Suite server ... features an XML data repository, a rules-based engine, and XSLT transforms, XPath and RDF-based indexing and query, XLink resolution and many other XML services. It also supports related services such as distributed transactions and access control lists. Along with basic console and command-line management, it supports remote, cross-platform and cross-language access through CORBA, WebDAV, HTTP and other request protocols to be added shortly." Drivers for PostgreSQL and Oracle are provided. BTW, page pays postgresql quite a compliment too: "PostgresQL is a brilliant, enterprise-quality, open-source, SQL DBMS." :-) Peter T Mount wrote: Quoting Franck Martin [EMAIL PROTECTED]: I guess these stubs are for accessing PG as a corba server... I'm trying to look to see if I can store CORBA objects inside PG, any ideas... Although I've not tried it (yet) it should be possible to access Java EJB's from corba. If so, then using an EJB server (JBoss www.jboss.org) you could then store them as Entity beans. Each one would then have its own table in the database. Peter Franck Martin Network and Database Development Officer SOPAC South Pacific Applied Geoscience Commission Fiji E-mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Web site: http://www.sopac.org/ http://www.sopac.org/ Support FMaps: http://fmaps.sourceforge.net/ http://fmaps.sourceforge.net/ This e-mail is intended for its addresses only. Do not forward this e-mail without approval. The views expressed in this e-mail may not be necessarily the views of SOPAC. -Original Message- From: Peter T Mount [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 6 March 2001 3:52 To: Franck Martin Cc: PostgreSQL List Subject: Re: [HACKERS] CORBA and PG Quoting Franck Martin [EMAIL PROTECTED]: Does anyone has pointers on CORBA and PostgreSQL? What is the story ? There's some old stubs for one of the orbs somewhere in the source (C/C++) Also the old JDBC/Corba example is still there (src/interfaces/jdbc/example/corba) Peter -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/ -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- -- john reid e-mail [EMAIL PROTECTED] technical officerroom G02, building 41 school of geosciences phone +61 02 4221 3963 university of wollongong fax +61 02 4221 4250 uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... -- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PostgreSQL on multi-CPU systems
I am looking to beef up a PostgreSQL database by moving it to a Sun Enterprise or an Alpha ES-40 or some other multi-CPU platform. My questions are; - What suggestions do people have for a good PostgreSQL platform. - How well does PostgreSQLtake advantage of multiple CPUs? Thanks. -- D'Arcy J.M. Cain darcy@{druid|vex}.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Performance monitor signal handler
I was going to implement the signal handler like we do with Cancel, where the signal sets a flag and we check the status of the flag in various _safe_ places. Can anyone think of a better way to get information out of a backend? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Banner links not working (fwd)
At 11:41 12/03/01 -0500, Vince Vielhaber wrote: On Mon, 12 Mar 2001, Peter Mount wrote: Bottom of every page (part of the template) is both my name and email address ;-) Can we slightly enlarge the font? Can do. What size do you think is best? I've always used size=1 for that line... Peter Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] user name n password
hey friends can u give me a master password so that i can create user in the postgresql. thanx Shailendra Kumar
Re: [HACKERS] CORBA and PG
At 12:37 07/03/01 +1100, John Reid wrote: Hi, This was mentioned a while back on this list (pg hackers) - thanks to whoever provided the pointer :-) I have not yet looked at it in depth, though that is high on my list of TO-DO's. It is released under an apache style licence. Any reason why there are no pointers to it on the PostgreSQL related projects or interfaces pages? Probably no one's asked to put it on there ;-) Actually there's quite a few projects out there that use PostgreSQL and don't say so here or register it on the web site, hence the lack of links... Peter project page: http://4suite.org/index.epy docs on ODMG support: http://services.4Suite.org/documents/4Suite/4ODS-userguide From project page: "4Suite is a collection of Python tools for XML processing and object database management. It provides support for XML parsing, several transient and persistent DOM implementations, XPath expressions, XPointer, XSLT transforms, XLink, RDF and ODMG object databases. Hmmm, nothing to do with postgres but I think I may have seen a demo of this about a month back. If it was that, it was pretty interesting... Peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Performance monitor
On 2001.03.07 22:06 Bruce Momjian wrote: I think Bruce wants per-backend data, and this approach would seem to only get the data for the current backend. Also, I really don't like the proposal to write files to /tmp. If we want a perf tool, then we need to have something like 'top', which will continuously update. With 40 backends, the idea of writing 40 file to /tmp every second seems a little excessive to me. My idea was to use 'ps' to gather most of the information, and just use the internal stats when someone clicked on a backend and wanted more information. My own experience is that parsing ps can be difficult if you want to be portable and want more than basic information. Quite clearly, I could just be dense, but if it helps, you can look at the configure.in in the CVS tree at http://sourceforge.net/projects/netsaintplug (GPL, sorry. But if you find anything worthwhile, and borrowing concepts results in similar code, I won't complain). I wouldn't be at all surprised if you found a better approach - my configuration above, to my mind at least, is not pretty. I hope you do find a better approach - I know I'll be peeking at your code to see. -- Karl ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
On Sun, Mar 04, 2001 at 10:01:37AM +0800, xuyifeng allegedly wrote: - Original Message - From: The Hermit Hacker [EMAIL PROTECTED] To: Jaruwan Laongmal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, March 02, 2001 8:04 PM Subject: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB? On Fri, 2 Mar 2001, Jaruwan Laongmal wrote: I had deleted a very large number of records out of my SQL table in order to decrease the harddisk space. But after I use command 'ls -l /usr/local/pgsql/data/base/', it is found that the size of concerning files do not reduce due to the effect of 'delete' SQL command. What should I do if I would like to decrease the harddisk space? VACUUM could anyone remove this nasty bug in 7.2? this is already a big pain and is the reason why am I still using MySQL in my product server. another nasty thing is it does not allow me to reference table in another database. sigh. Why would this be a bug? Sure, maybe it's not what you expected, but I hardly think it qualifies as a bug. For instance, Oracle doesn't release storage (datafiles specifically) after it has allocated space for them. In fact, I wish I could force pgsql to allocate storage it might need in the future. It would be great if I could force pgsql to allocated four datafiles spread across four harddisks, so I would enjoy a) better database performance and b) rest assured I have the diskspace when I need it in the future. Call it a poor mans RAID; I think MySQL can perform this trick. If pgsql can do this, please let me know But back to your problem. One way to get the amount of space allocated to shrink is by recreating the database. Dump it using pg_dump and recreate it using the backup you just made. This is a fairly simple and quick process. Give it a try on a small test database first; you don't want to risk loosing your data. Cheers, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Banner links not working (fwd)
On Mon, 12 Mar 2001, Peter Mount wrote: At 11:41 12/03/01 -0500, Vince Vielhaber wrote: On Mon, 12 Mar 2001, Peter Mount wrote: Bottom of every page (part of the template) is both my name and email address ;-) Can we slightly enlarge the font? Can do. What size do you think is best? I've always used size=1 for that line... I think a 3 is standard size, so at least a 2 should be plenty. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 3: 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] pg_dump writes SEQUENCEs twice with -a
On Thu, 8 Mar 2001, Philip Warner wrote: At 20:48 7/03/01 +, Oliver Elphick wrote: kovacsz wrote: You answered that noone experienced anything like this. Here I get this behaviour with the most simple table as well. Is there a problem with the lists? I reveived Zoltan's message twice, and now this one that seems to indicate my earlier reply has not been seen. FWIW, this is fixed in CVS. Thank you, I checked the CVS (and I downloaded the new sources and tried to compile -- without success, I should download the whole stuff IMHO, e.g. postgres_fe.h is quite new to 7.1beta4 and the old sources may be incompatible with the new ones). Zoltan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL SHM principles
""Mikheev, Vadim"" [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... It is possible to build a logging system so that you mostly don't care when the data blocks get written; a particular data block on disk is considered garbage until the next checkpoint, so that you How to know if a particular data page was modified if there is no log record for that modification? (Ie how to know where is garbage? -:)) You could store a log sequence number in the data page header that indicates the log address of the last log record that was applied to the page. This is described in Bernstein and Newcomer's book (sec 8.5 operation logging). Sorry if I'm misunderstanding the question. Back to lurking mode... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Banner links not working (fwd)
On Mon, Mar 12, 2001 at 08:05:26PM +, Peter Mount wrote: At 11:41 12/03/01 -0500, Vince Vielhaber wrote: On Mon, 12 Mar 2001, Peter Mount wrote: Bottom of every page (part of the template) is both my name and email address ;-) Can we slightly enlarge the font? Can do. What size do you think is best? I've always used size=1 for that line... Absolute font sizes in HTML are always a mistake. size="-1" would do. -- Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Internationalized dates (was Internationalized error messages)
On Mon, Mar 12, 2001 at 11:11:46AM +0100, Karel Zak wrote: On Fri, Mar 09, 2001 at 10:58:02PM +0100, Kaare Rasmussen wrote: Now you're talking about i18n, maybe someone could think about input and output of dates in local language. As fas as I can tell, PostgreSQL will only use English for dates, eg January, February and weekdays, Monday, Tuesday etc. Not the local name. May be add special mask to to_char() and use locales for this, but I not sure. It isn't easy -- arbitrary size of strings, to_char's cache problems -- more and more difficult is parsing input with locales usage. The other thing is speed... A solution is use number based dates without names :-( ISO has published a standard on date/time formats, ISO 8601. Dates look like "2001-03-22". Times look like "12:47:63". The only unfortunate feature is their standard format for a date/time: "2001-03-22T12:47:63". To me the ISO date format is far better than something involving month names. I'd like to see ISO 8601 as the default data format. -- Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] psql missing feature
Peter Eisentraut writes: Michal Maruka writes: What about (optionally) printing the type of the column data? io | tu | tipo |data int | int | int2 |date +---+--+ 102242 | 26404 | 1203 | 2000-11-22 (1 row) I've been meaning to implement this for a while. Now that someone is seemingly interested I might prioritize it. I have realized that the querytree is too much of information (imagine UNION queries). So I think this feature (types of columns) is very good if accompanied with tools to declare easily some/many clone types: eg int- ID, int2 - height as the type is a nice invariant. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Small bug in pg_dump
Hi Philip, I have not updated from CVS in a few days, but I suspect you haven't noticed this yet: given a mixed-case table name and a scenario that requires emitting UPDATE pg_class commands, pg_dump puts out things like UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* '"Table"'; BEGIN TRANSACTION; CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint); INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* '"Table"' GROUP BY 1; UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP WHERE "pg_class"."relname" = TMP."tmp_relname"; DROP TABLE "tr"; COMMIT TRANSACTION; Of course those ~* '"Table"' clauses aren't going to work too well; the identifier should NOT be double-quoted inside the pattern. Actually, this should not be using ~* in the first place --- why isn't it just using WHERE relname = 'Table' ??? Seems like it's not cool to gratuitously reset the trigger counts on other tables that contain Table as a substring of their names. And while we're at it, the temp table hasn't been necessary for a release or three. That whole transaction should be replaced by UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname = 'Table'; regards, tom lane ---(end of broadcast)--- TIP 3: 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] Uh, this is *not* a 64-bit CRC ...
On Mon, Mar 05, 2001 at 02:00:59PM -0500, Tom Lane wrote: [EMAIL PROTECTED] (Nathan Myers) writes: The CRC-64 code used in the SWISS-PROT genetic database is (now) at: ftp://ftp.ebi.ac.uk/pub/software/swissprot/Swissknife/old/SPcrc.tar.gz From the README: The code in this package has been derived from the BTLib package obtained from Christian Iseli [EMAIL PROTECTED]. From his mail: The reference is: W. H. Press, S. A. Teukolsky, W. T. Vetterling, and B. P. Flannery, "Numerical recipes in C", 2nd ed., Cambridge University Press. Pages 896ff. The generator polynomial is x64 + x4 + x3 + x1 + 1. Nathan (or anyone else with a copy of "Numerical recipes in C", which I'm embarrassed to admit I don't own), is there any indication in there that anyone spent any effort on choosing that particular generator polynomial? As far as I can see, it violates one of the standard guidelines for choosing a polynomial, namely that it be a multiple of (x + 1) ... which in modulo-2 land is equivalent to having an even number of terms, which this ain't got. See Ross Williams' A PAINLESS GUIDE TO CRC ERROR DETECTION ALGORITHMS, available from ftp://ftp.rocksoft.com/papers/crc_v3.txt among other places, which is by far the most thorough and readable thing I've ever seen on CRCs. I spent some time digging around the net for standard CRC64 polynomials, and the only thing I could find that looked like it might have been picked by someone who understood what they were doing is in the DLT (digital linear tape) standard, ECMA-182 (available from http://www.ecma.ch/ecma1/STAND/ECMA-182.HTM): x^64 + x^62 + x^57 + x^55 + x^54 + x^53 + x^52 + x^47 + x^46 + x^45 + x^40 + x^39 + x^38 + x^37 + x^35 + x^33 + x^32 + x^31 + x^29 + x^27 + x^24 + x^23 + x^22 + x^21 + x^19 + x^17 + x^13 + x^12 + x^10 + x^9 + x^7 + x^4 + x + 1 I'm sorry to have taken so long to reply. The polynomial chosen for SWISS-PROT turns out to be presented, in Numerical Recipes, just as an example of a primitive polynomial of that degree; no assertion is made about its desirability for error checking. It is (in turn) drawn from E. J. Watson, "Mathematics of Computation", vol. 16, pp368-9. Having (x + 1) as a factor guarantees to catch all errors in which an odd number of bits have been changed. Presumably you are then infinitesimally less likely to catch all errors in which an even number of bits have been changed. I would have posted the ECMA-182 polynomial if I had found it. (That was good searching!) One hopes that the ECMA polynomial was chosen more carefully than entirely at random. High-degree codes are often chosen by Monte Carlo methods, by applying statistical tests to randomly-chosen values, because the search space is so large. I have verified that Tom transcribed the polynomial correctly from the PDF image. The ECMA document doesn't say whether their polynomial is applied "bit-reversed", but the check would be equally strong either way. Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] xlog patches reviewed
but it's hard to notice eg misprints in 44K file -:) I think we should apply patches and hard test recovering for a few days (power off/pg_ctl -m i stop with dozens update transactions). Vadim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: AW: AW: AW: [HACKERS] WAL does not recover gracefully from ou t-of -dis k-sp ace
FSYNC:257tps O_DSYNC: 333tps Just(?) 30% faster, -:( First of all, if you ask me, that is one hell of an improvement :-) Of course -:) But tfsync tests were more promising -:) Probably we should update XLogWrite to write() more than 1 block, but Tom should apply his patches first (btw, did you implement "log file size" condition for checkpoints, Tom?). Vadim ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: AW: AW: AW: [HACKERS] WAL does not recover gracefully from ou t-of -dis k-sp ace
"Mikheev, Vadim" [EMAIL PROTECTED] writes: Probably we should update XLogWrite to write() more than 1 block, but Tom should apply his patches first (btw, did you implement "log file size" condition for checkpoints, Tom?). Yes I did. There's a variable now to specify a checkpoint every N log segments --- I figured that was good enough resolution, and it allowed the test to be made only when we're rolling over to a new segment, so it's not in a time-critical path. If you're happy with what I did so far, I'll go ahead and commit. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] xlog patches reviewed
"Mikheev, Vadim" [EMAIL PROTECTED] writes: but it's hard to notice eg misprints in 44K file -:) I think we should apply patches and hard test recovering for a few days (power off/pg_ctl -m i stop with dozens update transactions). OK. I haven't finished putting together an xlog-reset utility quite yet, but I will go ahead and apply what I have. CAUTION TO ONLOOKERS: if you update from CVS after I make this patch, you will need to initdb!! Wait around for the log-reset utility if you are running a database you don't want to initdb. I should have that in another day or so. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] xlog patches reviewed
On Mon, 12 Mar 2001, Mikheev, Vadim wrote: but it's hard to notice eg misprints in 44K file -:) I think we should apply patches and hard test recovering for a few days (power off/pg_ctl -m i stop with dozens update transactions). if this is the case, can we look at applying that patch tonight, give ppl till Friday to test and put out a RC1 depending on the results? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [HACKERS] WAL SHM principles
It is possible to build a logging system so that you mostly don't care when the data blocks get written; a particular data block on disk is considered garbage until the next checkpoint, so that you How to know if a particular data page was modified if there is no log record for that modification? (Ie how to know where is garbage? -:)) You could store a log sequence number in the data page header that indicates the log address of the last log record that was applied to the page. We do. But how to know at the time of recovery that there is a page in multi-Gb index file with tuple pointing to uninserted table row? Well, actually we could make some improvements in this area: a buffer without "first after checkpoint" modification could be written without flushing log records: entire block will be rewritten on recovery. Not sure how much we get, though -:) Vadim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [HACKERS] xlog patches reviewed
but it's hard to notice eg misprints in 44K file -:) I think we should apply patches and hard test recovering for a few days (power off/pg_ctl -m i stop with dozens update transactions). if this is the case, can we look at applying that patch tonight, give ppl till Friday to test and put out a RC1 depending on the results? I think so. Vadim ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PostgreSQL on multi-CPU systems
I am looking to beef up a PostgreSQL database by moving it to a Sun Enterprise or an Alpha ES-40 or some other multi-CPU platform. My questions are; - What suggestions do people have for a good PostgreSQL platform. - How well does PostgreSQLtake advantage of multiple CPUs? I have tested PostgreSQL with 2-4 CPU linux boxes. In summary, 2 CPU was a big win, but 4 was not. I'm not sure where the bottle neck is though. -- Tatsuo Ishii ---(end of broadcast)--- TIP 3: 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] WAL SHM principles
Sorry for taking so long to reply... On Wed, Mar 07, 2001 at 01:27:34PM -0800, Mikheev, Vadim wrote: Nathan wrote: It is possible to build a logging system so that you mostly don't care when the data blocks get written [after being changed, as long as they get written by an fsync]; a particular data block on disk is considered garbage until the next checkpoint, so that you How to know if a particular data page was modified if there is no log record for that modification? (Ie how to know where is garbage? -:)) In such a scheme, any block on disk not referenced up to (and including) the last checkpoint is garbage, and is either blank or reflects a recent logged or soon-to-be-logged change. Everything written (except in the log) after the checkpoint thus has to happen in blocks not otherwise referenced from on-disk -- except in other post-checkpoint blocks. During recovery, the log contents get written to those pages during startup. Blocks that actually got written before the crash are not changed by being overwritten from the log, but that's ok. If they got written before the corresponding log entry, too, nothing references them, so they are considered blank. might as well allow the blocks to be written any time, even before the log entry. And what to do with index tuples pointing to unupdated heap pages after that? Maybe index pages are cached in shm and copied to mmapped blocks after it is ok for them to be written. What platforms does PG run on that don't have mmap()? Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL on multi-CPU systems
Tatsuo Ishii [EMAIL PROTECTED] writes: I have tested PostgreSQL with 2-4 CPU linux boxes. In summary, 2 CPU was a big win, but 4 was not. I'm not sure where the bottle neck is though. Our not-very-good implementation of spin locking (using select() to wait) might have something to do with this. Sometime soon I'd like to look at using POSIX semaphores where available, instead of spinlocks. regards, tom lane ---(end of broadcast)--- TIP 3: 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] Do we still need PowerPC-specifictimestamp_is_current/epoch?
At the end of backend/utils/adt/datetime.c, there is some fairly ugly code that is conditionally compiled on #if defined(linux) defined(__powerpc__) Do we still need this? The standard versions of TIMESTAMP_IS_CURRENT and TIMESTAMP_IS_EPOCH appear to work just fine on my Powerbook G3 running Linux 2.2.18 (LinuxPPC 2000 Q4 distro). I see from the CVS logs that Tatsuo originally introduced this code on 1997/07/29 (at the time it lived in dt.c and was called datetime_is_current datetime_is_epoch). I suppose that it must have been meant to work around some bug in old versions of gcc for PPC. But it seems to me to be a net decrease in portability --- it's assuming that the symbolic constants DBL_MIN and -DBL_MIN will produce particular bit patterns --- so I'd like to remove it unless someone knows of a recent Linux/PPC release that still needs it. After further research, I remembered that we used to have "DB_MIN check" in configure back to 6.4.2: AC_MSG_CHECKING(for good DBL_MIN) AC_TRY_RUN([#include stdlib.h #include math.h #ifdef HAVE_FLOAT_H # include float.h #endif main() { double d = DBL_MIN; if (d != DBL_MIN) exit(-1); else exit(0); }], AC_MSG_RESULT(yes), [AC_DEFINE(HAVE_DBL_MIN_PROBLEM) AC_MSG_RESULT(no)], AC_MSG_RESULT(assuming ok on target machine)) I don't know wht it was removed, but I think we'd better to revive the checking and replace #if defined(linux) defined(__powerpc__) with #ifdef HAVE_DBL_MIN_PROBLEM What do you think? -- Tatsuo Ishii ---(end of broadcast)--- TIP 3: 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
[HACKERS] Is INSERT FROM considered a transaction?
Greetings, Sorry about all the posts lately, but things seems to be running *really* slow on my database. I have two tables, both are identical and one is used to hold entries older than a certain date, i.e. the history table. I use this query to move the old records from one to the other. In this case, is each insert part of a big transaction that commits when it is done, or is each insert its own transaction? Is there anything I can do to make this faster? On average the entries table has about 50,000 records and the history_entries table has about 3.5 million. insert into history_entries select * from entries where domain='somevalue' and time_stamp between 'date1' and 'date2' Thanks, Matthew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Performance monitor
[ Charset KOI8-R unsupported, converting... ] On Wednesday 07 March 2001 21:56, Bruce Momjian wrote: I have started coding a PostgreSQL performance monitor. It will be like top, but allow you to click on a backend to see additional information. It will be written in Tcl/Tk. I may ask to add something to 7.1 so when a backend receives a special signal, it dumps a file in /tmp with some backend status. It would be done similar to how we handle Cancel signals. Small question... Will it work in console? Or it will be X only? It will be tck/tk, so I guess X only. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] TOAST
hi, I an using postgresql-7.1beta4 and am trying to use the large text fields. I have heard of TOAST. There is little documentation. I found one section about creating a data type, then creating two functions to convert the data types. Is this how TOAST is implemented? Am I on the right track?. If so, what do the conversion functions look like. I am using plpgsql. Thanks, Pam ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Do we still need PowerPC-specific timestamp_is_current/epoch?
Tatsuo Ishii [EMAIL PROTECTED] writes: After further research, I remembered that we used to have "DB_MIN check" in configure back to 6.4.2: I don't know wht it was removed, Hmm. Digging in the CVS logs shows that it was removed by Bruce in configure.in version 1.262, 1999/07/18, with the unedifying log message "configure cleanup". A guess is that he took it out because it wasn't being used anywhere. but I think we'd better to revive the checking and replace #if defined(linux) defined(__powerpc__) with #ifdef HAVE_DBL_MIN_PROBLEM What do you think? I think that is a bad idea, since that code is guaranteed to fail on any machine where the representation of double is at all different from a PPC's. (Even if you are willing to assume that the entire world uses IEEE floats these days, what of endianness?) We could revive the configure test and do #if defined(HAVE_DBL_MIN_PROBLEM) defined(__powerpc__) However, I really wonder whether there is any point. It may be worth noting that the original version of the patch read "#if ... defined(PPC)". It's quite likely that the current test, "... defined(__powerpc__)", doesn't even fire on the old compiler that the patch is intended for. If so, this is dead code and has been since release 6.5. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] JDBC handling of a Timestamp-Column
Hi, i've tried to fetch a TIMESTAMP column from the database into a Java Timestamp instance using the ResultSet.getTimestamp(int index) method. Whenever i call this method i get some error message: User.findUser: Bad Timestamp Format at 19 in 2001-03-19 22:05:50.45+01 Bad Timestamp Format at 19 in 2001-03-19 22:05:50.45+01 at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:447) at de.reswi.portal.User_DO.bind(User_DO.java:85) If i try to bind this column to a java.sql.Date instance using ResultSet.getDate(int index) everything works fine but i loose the precision i need. BTW: it's possible to write Timestamp type objects into the column. The Method ResultSet.setTimestamp(int index, Timestamp stamp) works fine. Ciao, - ralf - Original Message - From: "Lawrence M. Kagan" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 22, 2001 11:43 AM Subject: [HACKERS] Where is locking done? Here's my dillema: We are currently building a site with multiple machines to run our website and client sites as well. I would like to run the postgres binary on 2 machines concurrently to assist in load balancing. $PGDATA will be kept on a RAID 1+0.I need to know where postgres does it's row table locking. If it's done in memory, I've got some problems! If it's done at or near the $PGDATA directory (which sounds like bad performance decision) that would be piece of cake.Any advice or ideas on this issue would be GREATLY appreciated. Thanks in advance!! Larry -- Lawrence M. Kagan Allied Infosystems, Inc. E-mail:[EMAIL PROTECTED] Web: www.alliedinfo.com Phone: (954) 647-4600 Toll-free: (877) WEB-5888 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Performance monitor signal handler
* Bruce Momjian [EMAIL PROTECTED] [010312 12:12] wrote: I was going to implement the signal handler like we do with Cancel, where the signal sets a flag and we check the status of the flag in various _safe_ places. Can anyone think of a better way to get information out of a backend? Why not use a static area of the shared memory segment? Is it possible to have a spinlock over it so that an external utility can take a snapshot of it with the spinlock held? Also, this could work for other stuff as well, instead of overloading a lot of signal handlers one could just periodically poll a region of the shared segment. just some ideas.. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] Daemon News Magazine in your snail-mail! http://magazine.daemonnews.org/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] xlog patches reviewed
if this is the case, can we look at applying that patch tonight, give ppl till Friday to test and put out a RC1 depending on the results? Patch committed. There are still some loose ends to clean up: * I need to finish making an xlog-reset utility for contrib. * I stubbed out shmctl(IPC_STAT) in the BeOS and QNX4 emulations of SysV shared memory (src/backend/port/beos/shm.c, src/backend/port/qnx4/shm.c). This means that the new code to detect postmaster-dead-but-old-backends-still-running will never detect any problem on those platforms. Perhaps people who use those platforms can test and contribute real implementations? However, these shouldn't affect testing. regards, tom lane ---(end of broadcast)--- TIP 3: 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] xlog patches reviewed
On Mon, 12 Mar 2001, Tom Lane wrote: if this is the case, can we look at applying that patch tonight, give ppl till Friday to test and put out a RC1 depending on the results? Patch committed. There are still some loose ends to clean up: * I need to finish making an xlog-reset utility for contrib. * I stubbed out shmctl(IPC_STAT) in the BeOS and QNX4 emulations of SysV shared memory (src/backend/port/beos/shm.c, src/backend/port/qnx4/shm.c). This means that the new code to detect postmaster-dead-but-old-backends-still-running will never detect any problem on those platforms. Perhaps people who use those platforms can test and contribute real implementations? However, these shouldn't affect testing. Great, then let's go with a RC1 on Friday and see if we can get 7.1 out in '02 :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Performance monitor signal handler
At 13:34 12/03/01 -0800, Alfred Perlstein wrote: Is it possible to have a spinlock over it so that an external utility can take a snapshot of it with the spinlock held? I'd suggest that locking the stats area might be a bad idea; there is only one writer for each backend-specific chunk, and it won't matter a hell of a lot if a reader gets inconsistent views (since I assume they will be re-reading every second or so). All the stats area should contain would be a bunch of counters with timestamps, I think, and the cost up writing to it should be kept to an absolute minimum. just some ideas.. Unfortunatley, based on prior discussions, Bruce seems quite opposed to a shared memory solution. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: Internationalized dates (was Internationalized error messages)
A solution is use number based dates without names :-( ISO has published a standard on date/time formats, ISO 8601. Dates look like "2001-03-22". Times look like "12:47:63". The only unfortunate feature is their standard format for a date/time: "2001-03-22T12:47:63". To me the ISO date format is far better than something involving month names. I'd like to see ISO 8601 as the default data format. You got your wish when 7.0 was released; the default date/time format is "ISO" which of course can be adjusted at build or run time. The default date/time formats are compliant with ISO-8601 (or are at least intended to be so). The detail regarding "T" as the time designator mentioned above is covered in 8601 and our usage, omitting the "T", is allowed by the standard. At least as long as you agree that it is OK! The wording is actually: ... By mutual agreement of the partners in information interchange, the character [T] may be omitted... Presumably this can be covered under our documenting the behavior (and by compliance with common and expected usage), rather than requiring 100% concurrence by all end users of the system ;) - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vaccuum Failure w/7.1beta4 on Linux/Sparc
On Mon, 12 Mar 2001, Tom Lane wrote: Ryan Kirkpatrick [EMAIL PROTECTED] writes: While testing some existing database applications on 7.1beta4 on my Sparc 20 running Debian GNU/Linux 2.2, I got the following error on attempting to do a vacuum of a table: NOTICE: FlushRelationBuffers(jobs, 1399): block 953 is referenced (private 0, global 1) ERROR! Can't vacuum table Jobs! ERROR: VACUUM (repair_frag): FlushRelationBuffers returned -2 This is undoubtedly a backend bug. Can you generate a reproducible test case? I will work on it... The code that eventually caused it does a lot of different things so it will take me a little while to pair it down to a small, self-contained test case. I should have it by this weekend. Also, two other details I forgot to put in my first email: a) Running 'vaccumdb -t Jobs {dbname}' about 24 hours after the error (the backend had been completely idle during this time), ran successfully without error. b) The disk space where the pgsql database is located is NFS mounted from my Alpha (running Linux of course :). [0] Might this cause the error? [0] Yes, I know running pgsql on an NFS mount is probably not the greatest idea, but the system only has 1GB of local disk space (almost all used for the system) and is running as development server only. No valuable data is entrusted to it. Hopefully I will have more local disk space in the near future. Pg did get an ERROR from the vacuum command (note second line). Yes, there is paranoia right up the line here, but I think that's a good thing. Somewhere someone is failing to release a buffer refcount, and we don't know what other consequences that bug might have. Better to err on the side of caution. A resonable amount of paranoia is indeed always healthy. :) Just wanted to know if this might have been a known and harmless warning. I guess not. I will work on a test case and get back hopefully by the weekend. Thanks for your help. --- | "For to me to live is Christ, and to die is gain."| |--- Philippians 1:21 (KJV) | --- | Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ | --- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] xlog loose ends, continued
There is another loose end that I forgot I needed to discuss with you. xlog.c's ReadRecord formerly contained code that would zero out the rest of the log segment (and delete the next log segment, if any) upon detecting a missing or corrupted xlog record. I removed that code because I considered it horribly dangerous where it was. If there is anything wrong with either the xlog or pg_control's pointers to it, that code was quite capable of wiping out all hope of recovery *and* all evidence of what went wrong. I think it's really bad to automatically destroy log data, especially when we do not yet know if we are capable of recovering. If we need this functionality, it should be invoked only at the completion of StartupXLOG, after we have finished the recovery phase. However, I'd be a lot happier if we could avoid wholesale zeroing at all. I presume the point of this code was that if we recover and then suffer a later crash at a point where we've just written an xlog record that exactly fills an xlog page, a subsequent scan of the log might continue on from that point and pick up xlog records from the prior (failed) system run. Is there a way to guard against that scenario without having to zero out data during recovery? One thought that comes to mind is to store StartUpID in XLOG page headers, and abort log scanning if we come to a page with StartUpID less than what came before. Is that secure/sufficient? Is there a better way? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] xlog checkpoint depends on sync() ... seems unsafe
I wrote a couple days ago: : BTW, can we really trust checkpoint to mean that all data file changes : are down on disk? I see that the actual implementation of checkpoint is : : write out all dirty shmem buffers; : sync(); : if (IsUnderPostmaster) : sleep(2); : sync(); : write checkpoint record to XLOG; : fsync XLOG; : : Now HP's man page for sync() says : : The writing, although scheduled, is not necessarily complete upon : return from sync. The more I think about this, the more disturbed I get. It seems clear that this sequence is capable of writing out the checkpoint record before all dirty data pages have reached disk. If we suffer a crash before the data pages do reach disk, then on restart we will not realize we need to redo the changes to those pages. This seems an awfully large hole for what is claimed to be a bulletproof xlog technology. I feel that checkpoint should not use sync(2) at all, but should instead depend on fsync'ing the data files --- since fsync doesn't return until the write is done, this is considerably more secure. (Of course disk drive write reordering could still mess you up, but at least kernel-level failures won't put your data at risk.) One way to do this would be to maintain a hashtable in shared memory of data files that have been written to since the last checkpoint. We'd need to set a limit on the size of the hashtable (say a few hundred entries) --- if it overflows, remove the oldest entry and fsync that file before forgetting it. However that seems moderately complex, and probably too risky to do just before release. Spinlock contention on the hashtable could be a problem too. I thought about having checkpoint physically scan the $PGDATA/base/* directories and fsync every file found in them, but that seems mighty slow and ugly. Is there another way? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] RE: xlog loose ends, continued
I presume the point of this code was that if we recover and then suffer a later crash at a point where we've just written an xlog record that exactly fills an xlog page, a subsequent scan of the log might continue on from that point and pick up xlog records from the prior (failed) system run. Is there a way to guard against that scenario without having to zero out data during recovery? One thought that comes to mind is to store StartUpID in XLOG page headers, and abort log scanning if we come to a page with StartUpID less than what came before. Is that secure/sufficient? Is there a better way? This code was from the old days when there was no CRC in log records. Should we try to read log up to the *physical end* - ie end of last log file - regardless invalid CRC-s/zero pages with attempt to re-apply interim valid records? (Or do we already do this?) This way we'll know where is actual end of log (last valid record) to begin production from there. (Unfortunately, we'll have to read empty files pre-created by checkpointer -:(). Anyway I like idea of StartUpID in page headers - this will help if some log files disappeared. Should we add CRC to page header? Hm, maybe XLogFileInit should initialize files with StartUpID CRC in pages? We would avoid reading empty files. Vadim ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: xlog loose ends, continued
"Mikheev, Vadim" [EMAIL PROTECTED] writes: This code was from the old days when there was no CRC in log records. Ah, right. The CRC makes things safer ... but there's still a risk that old log pages could look like a valid continuation. Should we try to read log up to the *physical end* - ie end of last log file - regardless invalid CRC-s/zero pages with attempt to re-apply interim valid records? (Or do we already do this?) That doesn't seem like a good idea --- once we fail to read an XLOG record, it's probably best to stop there rather than continue on. I think we want to try for a consistent recovery to a past point in time (ie, wherever the xlog gap is) not a partial recovery to a later time. Anyway I like idea of StartUpID in page headers - this will help if some log files disappeared. Should we add CRC to page header? That seems like overkill. I was hoping to keep the page header overhead at eight bytes. We could do that either by storing just the two LSBs of StartUpID (and doing the sequence checking mod 64k) or by reducing the magic number to two bytes so there's room for four bytes of StartUpID. I think I like the first alternative better --- comments? Hm, maybe XLogFileInit should initialize files with StartUpID CRC in pages? We would avoid reading empty files. We already stop when we hit a zeroed page (because it's not got the right magic number). That seems sufficient. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] RE: xlog checkpoint depends on sync() ... seems unsafe
The more I think about this, the more disturbed I get. It seems clear that this sequence is capable of writing out the checkpoint record before all dirty data pages have reached disk. If we suffer a crash before the data pages do reach disk, then on restart we will not realize we need to redo the changes to those pages. This seems an awfully large hole for what is claimed to be a bulletproof xlog technology. I feel that checkpoint should not use sync(2) at all, but should instead depend on fsync'ing the data files --- since fsync doesn't return until the write is done, this is considerably more secure. I never was happy about sync() of course. This is just another reason to re-write smgr. I don't know how useful is second sync() call, but on Solaris (and I believe on many other *NIXes) rc0 calls it three times, -:) Why? Maybe now, with two checkpoints in log, we should start redo from oldest one? This will increase recovery time of course -:( Vadim ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: xlog checkpoint depends on sync() ... seems unsafe
"Mikheev, Vadim" [EMAIL PROTECTED] writes: Maybe now, with two checkpoints in log, we should start redo from oldest one? This will increase recovery time of course -:( Yeah, and it doesn't even solve the problem: consider a crash just after we've written a shutdown checkpoint record. On restart, we won't think we need to redo anything at all. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RE: xlog checkpoint depends on sync() ... seems unsafe
On Mon, 12 Mar 2001, Mikheev, Vadim wrote: to re-write smgr. I don't know how useful is second sync() call, but on Solaris (and I believe on many other *NIXes) rc0 calls it three times, -:) Why? The idea is, that by the time the last sync has run, the first sync will be done flushing the buffers to disk. - this is what we were told by the IBM engineers when I worked tier-2/3 AIX support at IBM. -- Dominic J. Eidson "Baruk Khazad! Khazad ai-menu!" - Gimli --- http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [HACKERS] RE: xlog checkpoint depends on sync() ... seems unsafe
to re-write smgr. I don't know how useful is second sync() call, but on Solaris (and I believe on many other *NIXes) rc0 calls it three times, -:) Why? The idea is, that by the time the last sync has run, the first sync will be done flushing the buffers to disk. - this is what we were told by the IBM engineers when I worked tier-2/3 AIX support at IBM. I was told the same a long ago about FreeBSD. How much can we count on this undocumented sync() feature? Vadim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RE: xlog checkpoint depends on sync() ... seems uns afe
"Mikheev, Vadim" [EMAIL PROTECTED] writes: The idea is, that by the time the last sync has run, the first sync will be done flushing the buffers to disk. - this is what we were told by the IBM engineers when I worked tier-2/3 AIX support at IBM. I was told the same a long ago about FreeBSD. How much can we count on this undocumented sync() feature? Sounds quite unreliable to me. Unless there's some interlock ... like, say, the second sync not being able to advance past a buffer page that's as yet unwritten by the first sync. But would all Unixen share such a strange detail of implementation? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] RE: xlog loose ends, continued
Should we try to read log up to the *physical end* - ie end of last log file - regardless invalid CRC-s/zero pages with attempt to re-apply interim valid records? (Or do we already do this?) That doesn't seem like a good idea --- once we fail to read an XLOG record, it's probably best to stop there rather than continue on. I think we want to try for a consistent recovery to a past point in time (ie, wherever the xlog gap is) not a partial recovery to a later time. No way for consistent recovery if there is gap in log due to disk write re-ordering anyway (and we can't know what was the reason of the gap). I thought that you wanted apply as much of log as we can. If you don't then I missed your point in first message: xlog.c's ReadRecord formerly contained code that would zero out the rest of the log segment (and delete the next log segment, if any) upon detecting a missing or corrupted xlog record. I removed that code because I considered it horribly dangerous where it was. If there is anything wrong with either the xlog or pg_control's pointers to it, that code was quite capable of wiping out all hope of recovery *and* all evidence of what went wrong. So, if we are not going to re-apply as much valid records as we can read from log then zeroing is no more dangerous than SUI in headers. But I totaly agreed that SUI is much better. Anyway I like idea of StartUpID in page headers - this will help if some log files disappeared. Should we add CRC to page header? That seems like overkill. I was hoping to keep the page header overhead at eight bytes. We could do that either by storing just the two LSBs of StartUpID (and doing the sequence checking mod 64k) or by reducing the magic number to two bytes so there's room for four bytes of StartUpID. I think I like the first alternative better --- comments? I don't think a few additional bytes in header is a problem. BTW, why not use CRC32 in header instead of magic? Or just StartUpID instead of magic if you don't want to calculate CRC for header - xlp_magic doesn't seem to be more useful than SUI. Hm, maybe XLogFileInit should initialize files with StartUpID CRC in pages? We would avoid reading empty files. We already stop when we hit a zeroed page (because it's not got the right magic number). That seems sufficient. What if the next page after zeroed one is correct (due to write re-ordering)? (But I take back SUI+CRC in XLogFileInit - useless -:)) Vadim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RE: xlog checkpoint depends on sync() ... seems uns afe
Tom Lane [EMAIL PROTECTED] writes: "Mikheev, Vadim" [EMAIL PROTECTED] writes: The idea is, that by the time the last sync has run, the first sync will be done flushing the buffers to disk. - this is what we were told by the IBM engineers when I worked tier-2/3 AIX support at IBM. I was told the same a long ago about FreeBSD. How much can we count on this undocumented sync() feature? Sounds quite unreliable to me. Unless there's some interlock ... like, say, the second sync not being able to advance past a buffer page that's as yet unwritten by the first sync. But would all Unixen share such a strange detail of implementation? I'm pretty sure it has no basis in fact, it's just one of these habits that gives sysadmins a warm fuzzy feeling. ;) It's apparently been around a long time, though I don't remember where I read about it--it was quite a few years ago. -Doug ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] RE: xlog checkpoint depends on sync() ... seems uns afe
Sounds quite unreliable to me. Unless there's some interlock ... like, say, the second sync not being able to advance past a buffer page that's as yet unwritten by the first sync. But would all Unixen share such a strange detail of implementation? I heard Kirk McKusick tell this story in a 4.4BSD internals class. His explanation was that having an *operator* type 'sync' three times provided enough time for the first sync to do the work before the operator powered the system down or reset it or whatever. I've not heard of any filesystem implementation where the number of sync() system calls issued makes a difference, and imagine that any programmer who has written code to call sync three times has only heard part of the story. :-) Regards, Giles ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Internationalized error messages
On Mon, Mar 12, 2001 at 08:15:02PM +0100, Peter Eisentraut wrote: Karel Zak writes: For transaltion to other languages I not sure with gettext() stuff on backend -- IMHO better (faster) solution will postgres system catalog with it. elog(ERROR, "cannot open message catalog table"); Sure, and what: elog(ERROR, gettext("can't set LC_MESSAGES")); We can generate our system catalog for this by simular way as gettext, it's means all messages can be in sources in English too. When there is an error condition in the backend, the last thing you want to do (and are allowed to do) is accessing tables. Also keep in mind that we want to internationalize other parts of the system as well, such as pg_dump and psql. Agree, the pg_ application are good adepts for POSIX locales, all my previous notes are about backend error/notice messages, but forget it -- after implementation we will more judicious. -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster