Re: [HACKERS] Building beta packaging fails ...
The Hermit Hacker [EMAIL PROTECTED] writes: GNUMakefile.in: opt_files := \ src/tools src/corba src/data src/tutorial \ Ah. I take it then, that src/data shoudl be removed from there too? Yep. Sorry I missed it. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] lower and upper not UTF-8 safe
On Mon, Aug 04, 2003 at 05:03:02PM -0400, Tom Lane wrote: Julian Satchell [EMAIL PROTECTED] writes: The implementations of lower and upper in src/backend/utils/adt/oracle_compat.c use the single byte macros from ctype.h to alter individual bytes in the text string. If the text is UTF-8 encoded this is totally wrong, and will result in an invalid string that is no longer UTF-8. Only if you use a locale that is assuming a character set that is not UTF8 but does have characters with the high bit set. I'm not sure that we can do anything to defend against locale/charset mismatch. We can try detect typical locale charset and compare it with actual charset used in DB and send NOTICE to FE if it's mismatched. The problem is portability of charset detection code, because there is differences between OS. The best it's if libc support nl_langinfo(CODESET) call. The complete code of charset detection you can found in libcharset or glib (I use simplification of these codes and it's 300 lines:-). Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] Release changes
On Tue, 5 Aug 2003, Bruce Momjian wrote: Oleg Bartunov wrote: Bruce, you forgot new contrib/tsearch2 module - full text extension (Oleg,Teodor) Sorry, added: New tsearch2 full-text search module (Oleg) Bruce, I wrote (Oleg,Teodor) Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Release changes
Joe Conway wrote: Bruce Momjian wrote: Here are the changes for 7.4. I am looking for any improvements. This will be adjusted as we move through beta. Almost anything with my name on it (but certainly all the array and polymorphic function stuff) could have Tom's too, since he reviewed, polished, and applied it. Yes, Tom could be on almost all the patches. It was getting extreme, so I had to cut it back. Hope that is OK, Tom. :-) On a philosophical note, I usually don't add core folks to release items _with_ other folks because we want to encourage non-core contributors, and because there is already the assumption that core is involved in many patches. I added all the 'Tom' attributions you mentioned below. Server Configuration Allow the postmaster to preload libraries using preload_libraries (Joe) You might want to move this one up to Performance. Also, it might better be ...to preload and preinitialize libraries... OK, moved. Add pg_settings table to see server settings (Joe) This should be more like: Add new columns to pg_settings view: context, vartype, source , min_val, max_val (Joe) Added: Add new columns in pg_settings: context, type, source , min_val, max_val (Joe) Data Types and Functions Allow WHERE qualifications of the form 'col IN/ANY/SOME/ALL (array) (?) (Joe) This should be Allow WHERE qualifications of the form 'expr oper ANY/SOME/ALL (array-expr)' (Tom) (Joe) Done: Allow WHERE qualification 'expr oper ANY/SOME/ALL (array-expr)' (Tom, Joe) Allow SQL functions to return arrays and take them as params (Joe) I think you must be referring to polymorphism here. Maybe: Allow polymorphic SQL functions (Tom) (Joe) OK, updated. I didn't use polymorphic originally because I am not sure how many people know what it means --- I don't. Also: Allow user defined aggregates to use polymorphic functions (Tom) (Joe) Allow polymorphic user defined aggregates (Tom) (Joe) Added. Server-side Languages Allow polymorphic PL/pgSQL functions (Tom) (Joe) Improved compiled function caching mechanism in PL/pgSQL with full support for polymorphism (Tom) (Joe) Add new $0 parameter in PL/pgSQL representing the function's actual return type (Tom) (Joe) Added. Contrib Add hashes to tablefuncs (Joe) More like: Add hashed based crosstab function to tablefuncs (Joe) Updated. And: Add optional serial column for ordering siblings to connectby() in tablefuncs (Nabil Sayegh) (Joe) Added: Add serial column to order connectby() siblings in tablefuncs (Nabil Sayegh,Joe) Fix second argument to metaphone() in fullystrmatch Fix bug in metaphone() in fuzzystrmatch Done. All in CVS. I reworded some to get them into one line. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] truncate all?
Hi there, I agree with Tom ... even the idea of a TRUNCATE ALL makes me nervous. If we had such a feature, I'd advocate that it be superuser only. This superuser only restriction certainly would be sensible. As for TRUNCATE CASCADE or similar improvements, I agree that they could be convenient ... but are easily worked around currently. Agreed, workarounds are easy. The original suggestion of TRUNCATE ALL (or TRUNCATE table CASCADE), however, was motivated by the search for a simple and efficient truncation of all tables to accelerate unit-testing. Obviously, a plain TRUNCATE without arguments that truncates all tables would be dangerous, and in order to stay consistent with current psql syntax, I would say that an efficient TRUNCATE table [CASCADE|RESTRICT], with the RESTRICT option being the default, would be totally sufficient to satisfy the original motivation. Another way to specify a safe but efficient TRUNCATE ALL command that might be easier to implement than above TRUNCATE table [CASCADE|RESTRICT] might be to implement the functionality of the originally suggested TRUNCATE ALL through a psql meta-command. Any suggestions for a safe syntax of such a TRUNCATE ALL meta-command? How about \rtuples *? So I wouldn't object to putting TRUNCATE CASCADE on the todo list, but would argue that it be left to the people who asked for it to implement it. Before attempting to implementing any such extension, we would like to make sure that that extension would not be rejected by those of you who decide what goes into future versions of postgresql. Yours, Andi. ---(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] v7.4 Bundled ... please test ...
'K ... I just re-tag'd REL7_4_BETA1 for the changes that were made (including Bruce's last minute copyright changes) ... Also, I just modified the script so that it builds both a .gz and a .bz2 version of hte archives ... Please test and let me know if there are any problems ... Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Thread-safe configuration option appears to
Larry Rosenman writes: What the fr*** harm is it in passing -D_REENTRANT into the libpq build on UnixWare irregardless of the --with-threads* flag? Indeed for every other sane system out there. Folk are messing around with the thread stuff using here-say as knowledge. We want to compile with _REENTRANT, always! Anything needed in ADDITON to _REENTRANT (i.e thread link flags for libecpg) can be discovered by configure and applied to the build. Even for the rest of PostgreSQL, it will work A-OK if we are already sticking to the rules (i.e. including errno.h when using errno). L. ---(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] truncate all?
Andreas [EMAIL PROTECTED] writes: Agreed, workarounds are easy. The original suggestion of TRUNCATE ALL (or TRUNCATE table CASCADE), however, was motivated by the search for a simple and efficient truncation of all tables to accelerate unit-testing. I still think the best suggestion for you is the one someone made already: create a schema dump (pg_dump -s), then do DROP DATABASE, CREATE DATABASE, load the schema dump. This will be somewhat slower than a hypothetical TRUNCATE ALL, but it has the great advantage that your starting point is well-documented and guaranteed to be the same on every iteration. TRUNCATE ALL would provide no such guarantee. And, of course, that solution exists today ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Release changes
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: On a philosophical note, I usually don't add core folks to release items _with_ other folks because we want to encourage non-core contributors, and because there is already the assumption that core is involved in many patches. I agree with this. For the current release, I'd be happy if my name were on a couple of the larger tasks that I did (such as hash aggregation). Seeing it plastered throughout the rev history is mildly embarrassing, actually. I added all the 'Tom' attributions you mentioned below. Please take 'em out again. I'm happy to give Joe the credit on all these items. Done. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Release changes
Do you have suggested wording? --- Andreas Pflug wrote: Bruce Momjian wrote: Here are the changes for 7.4. I am looking for any improvements. This will be adjusted as we move through beta. --- Add FOR EACH STATEMENT statement-level triggers (Neil Conway) AFAICS the current implementation still doesn't have a way to access the affected rowset, so it'a pretty much like a SELECT without a WHERE. This restricts the usability of statement-level triggers to very limited cases. IMHO it's not a good idea to announce an incompletely implemented feature. Regards, Andreas ---(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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] status of dbf2pg
Alien ( http://kitenet.net/programs/alien/ ) could possibly help you handle .deb files (depending on your platform). cheers andrew [EMAIL PROTECTED] wrote: There is a dbf2pg package on debian that appears to have a higher version number, but I don't know what to do with a .deb file extension and I'm getting ready to go out of town so I won't have time to look into it until mid-August at the earliest. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] v7.4 Bundled ... please test ...
The Hermit Hacker [EMAIL PROTECTED] writes: Please test and let me know if there are any problems ... The .gz tarball matches what I have here. Didn't check the .bz2 one. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Release changes
Bruce Momjian [EMAIL PROTECTED] writes: How is this? Prevent timestamp from supressing ':00' seconds display I think that was type interval, not timestamp. 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] logging stuff
It's more work for a very small cosmetic benefit, ISTM. Setting up atomic GUC variables is trivially easy, I'm happy to say. Not that I can't write a simple parser, but why bother for something so tiny? Also, would we be saying (or implying) that the order given on the line would affect the output? If so, that could make LOTS more work, again for very small benefit, I think. (Yes, of course this is for 7.5). cheers andrew Bruce Momjian wrote: I think we need a more general variable that can take several values, separated by commas, like: log_line: dbname,user or something like that. In fact, looking at the postgresql.conf file, I see only two setting that print on every line: log_pid and log_timestamp. Perhaps those two should be merged into log_line. Of course, this is all for 7.5. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Release changes
Updated. Thanks. --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: How is this? Prevent timestamp from supressing ':00' seconds display I think that was type interval, not timestamp. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Release changes
Joe Conway wrote: Andreas Pflug wrote: But PostgreSQL may be better than Oracle, don't you think? In the named document, snip MSSQL2000 still doesn't have row level triggers, and I doubt that 2003 has. Right, so as you've pointed out, Postgres trigger implementation is at least in some ways more flexible than Oracle, and offers row level triggers which MSSQL doesn't even have. All I said was that you're being too harsh by suggesting that statement level triggers don't even deserve mention. You are assuming that everyone migrating to Postgres will miss the MSSQL feature when lots of people (in fact, the majority) don't even use MSSQL. I agree that having the equiv. of MSSQL's inserted and deleted pseudo tables, would be nice, but I wouldn't allow lack thereof to denigrate a useful new feature. Hi Joe, I perfectly understand that you don't like withholding the existence of statement-level triggers, because they are certainly useful in some cases. I wasn't aware that Oracle has castrated triggers too... So for the 7.4 release, there must be taken some effort in documenting what statement triggers can and what not; chapter 37 is not helpful at the moment. Otherwise, frustration is pre-programmed, just as I had it when I was trying to use it. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Adjustment of spinlock sleep delays
I've been thinking about Ludwig Lim's recent report of a stuck spinlock failure on a heavily loaded machine. Although I originally found this hard to believe, there is a scenario which makes it plausible. Suppose that we have a bunch of recently-started backends as well as one or more that have been running a long time --- long enough that the scheduler has niced them down a priority level or two. Now suppose that one of the old-timers gets interrupted while holding a spinlock (an event of small but nonzero probability), and that before it can get scheduled again, several of the newer, higher-priority backends all start trying to acquire the same spinlock. The acquire code looks like try to grab the spinlock a few times, then sleep for 10 msec, then try again; give up after 1 minute. If there are enough backends trying this that cycling through all of them takes at least 10 msec, then the lower-priority backend will never get scheduled, and after a minute we get the dreaded stuck spinlock. To forestall this scenario, I'm thinking of introducing backoff into the sleep intervals --- that is, after first failure to get the spinlock, sleep 10 msec; after the second, sleep 20 msec, then 40, etc, with a maximum sleep time of maybe a second. The number of iterations would be reduced so that we still time out after a minute's total delay. Comments? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] lower and upper not UTF-8 safe
Karel Zak [EMAIL PROTECTED] writes: On Mon, Aug 04, 2003 at 05:03:02PM -0400, Tom Lane wrote: Only if you use a locale that is assuming a character set that is not UTF8 but does have characters with the high bit set. I'm not sure that we can do anything to defend against locale/charset mismatch. We can try detect typical locale charset and compare it with actual charset used in DB and send NOTICE to FE if it's mismatched. The problem is portability of charset detection code, because there is differences between OS. Yeah. If we had a portable, reliable way of testing for incompatibility, I'd be in favor of just forbidding creation of databases that have encoding choices incompatible with the server's LC_COLLATE/LC_CTYPE settings. (If we ever allow those settings to be more dynamic than they are, then the test would have to be made somewhere else, but for now it'd be sufficient to put it in CREATE DATABASE.) But I don't see a portable way to find out what charset a locale supports. nl_langinfo() isn't in the C standard at all. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] TODO: trigger features
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: - Implement a way to enable triggers to check which columns are affected by the triggering statement. This can already be done by comparing old and new values, no? No, this is not the case. UPDATE foo SET x=x, y=y is different from UPDATE foo SET y=y if triggers maintaining x are involved. I had this case, and had to do quite weird stuff to implement this. Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] LOCK.tag(figuring out granularity of lock)
following is taken from postgresql-7.3.2/src/backend/storage/lmgr/readme: If we are setting a table level lock both the blockId and tupleId (in an item pointer this is called the position) are set to invalid, if it is a page level lock the blockId is valid, while the tupleId is still invalid. Finally if this is a tuple level lock (we currently never do this) then both the blockId and tupleId are set to valid specifications. how do we check whether blockId and tupleId of LOCK.tag are valid or invalid? is blockId same as tag.objId.blkno? what field in LOCK.tag is tupleId? thanks jenny _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Release changes
Oh, yes. Let me add that. I didn't realize that was a change of enough significance. How is this? Prevent timestamp from supressing ':00' seconds display --- Larry Rosenman wrote: No, the one where we always print hh:mm:ss for an interval, even if seconds is zero. --On Tuesday, August 05, 2003 01:03:57 -0400 Bruce Momjian [EMAIL PROTECTED] wrote: I don't know about that item. There is a menion of allowing 60 seconds --- is that it? - -- Larry Rosenman wrote: What about the interval change in ISO datestyle for zero seconds? LER --On Sunday, August 03, 2003 19:30:26 -0400 Bruce Momjian [EMAIL PROTECTED] wrote: Here are the changes for 7.4. I am looking for any improvements. This will be adjusted as we move through beta. I need to work on the other sections of a major release, like a compatibility section. -- --- -- Release Notes 7.4 Development Branch Valid as of 2003-08-01. Update release.sgml later. Server Operation Allow IPv6 server connections (Nigel Kukard, Johan Jordaan, Bruce, Tom, Kurt Roeckx, Andrew Dunstan) Fix SSL to handle errors cleanly (Nathan Mueller) SSL protocol security and performance improvements (Sean Chittenden) Print lock information when a deadlock is detected (Tom) Update /tmp socket files regularly to avoid their removal (Tom) Enable PAM for MAC OS X (Aaron Hillegass) Make btree indexes fully WAL-safe (Tom) Allow btree index compaction and empty page reuse (Tom) Fix inconsistent index lookups during split of first root page (Tom) Improve free space map allocation logic (Tom) Preserve free space information between postmaster restarts (Tom) Set proper schema permissions in initdb (Peter) Add start time to pg_stat_activity (Neil) New code to detect corrupt disk pages; erase with zero_damaged_pages (Tom) New client/server protocol: faster, no username length limit, allow clean exit Add transaction status, tableid, columnid to backend protocol (Tom) Add new binary I/O protocol (Tom) Remove autocommit server setting; move to client applications (Tom) New error message wording, error codes, and three levels of error detail (Tom) _ Performance Add hashing for GROUP BY aggregates (Tom) Allow nested loops to be smarter about multicolumn indexes (Tom) Allow multi-key hash joins (Tom) Improve constant folding (Tom) Add ability to inline simple SQL functions (Tom) Reduce memory usage for queries using complex functions (Tom) Improve GEQO optimizer performance (Tom) Allow IN/NOT IN to be handled via hash tables (Tom) Improve NOT IN (subquery) performance (Tom) Allow most IN subqueries to be processed as joins (Tom) Improve reverse index scan performance (Tom) Improve optimizer cost computations, particularly for subqueries (Tom) Assume WHERE a.x = b.y and b.y = 42 also means a.x = 42 (Tom) Allow hash/merge joins on complex joins (Tom) Allow hash joins for more data types (Tom) Allow join optimization of ANSI joins, disable with join_collapse_limit (Tom) Add from_collapse_limit to control conversion of subqueries to joins (Tom) Use faster regex code from TCL (Henry Spencer, Tom) Use bit-mapped relation sets in the optimizer (Tom) Improve backend startup time (Tom) Improve trigger/constraint performance (Stephan) _ Server Configuration Rename server parameter server_min_messages to log_min_messages (Bruce) Rename show_*_stats to log_*_stats (Bruce) Rename show_source_port to log_source_port (Bruce) Rename hostname_lookup to log_hostname (Bruce) Add checkpoint_warning to warn of excessive checkpointing (Bruce) Allow the postmaster to preload libraries using preload_libraries (Joe) New read-only server parameters for localization (Tom) Change debug server log messages to output as DEBUG rather than LOG (Bruce) Prevent server log variables from being turned off by non-super users (Bruce) log_min_messages/client_min_messages now controls debug_* output (Bruce) Add Rendezvous server support (Chris Campbell) Add ability to print only slow statements using log_min_duration_statement (Christopher) Allow pg_hba.conf to accept netmasks in CIDR format (Andrew Dunstan) New is_superuser read-only variable (Tom) New server-side parameter log_error_verbosity to control error detail (Tom) postgres --help-config now dumps server config variables (Tom) Make default
Re: [HACKERS] TODO: trigger features
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Consider this: Table with one column that is maintained by a trigger for this rule: - Only one row in a group of rows may have a foo-value of true, all others must be false. - If foo=true is inserted/updated, other members of that data group must be set to false. - If foo=false, designate one row for foo=true - If not touched, use true if first member of that group, or false Why would the not touched case need to change anything? Only interesting on insert, using DEFAULT together with the other rules can handle this. Now we have another column: ts timestamp, that should contain the timestamp when the row was inserted/updated the last time by the *user*, not the trigger which is considered to work in the background. On INSERT, a DEFAULT current_timestamp will be the selected option, on UPDATE you would use NEW.TS := current_timestamp. But how to update the row, and retain the old timestamp value? Normally, a user's query wouldn't touch the ts column at all, leaving it to the backend to insert the correct values. But in the maintain foo trigger case, we could use SET ts=ts to signal to the trigger that we explicitely want to set the value. That's not an argument for SET ts=ts. There are many possible kluges for detecting whether an update came from a trigger or directly from the user, and using ts=ts is only one (not a very appealing one either IMHO). The most obvious alternative is to have an additional boolean column from_trigger defaulting to FALSE. The trigger that sets the timestamp can do this: if new.from_trigger then new.from_trigger = false; else new.timestamp = now(); Then, the stored value of from_trigger is always false, and any update will cause the timestamp column to get updated --- unless the update explicitly sets from_trigger=true. This would also provide a solution for your other concern about being able to override the timestamp on insert. I wonder why you are suggesting workarounds for features that other databases provide. Of course inventing a I intend to change that row flag is a way, but why not providing this directly? Might not be too easy, I know. Same applies for the import case, when we want to insert a ts value coming from elsewhere but not from the trigger. This could also be done if there was something like UPDATE ... WITH OPTION NOTRIGGER(trg_update_timestamp) or so. Yet another messy kluge :-(. YATS (yet another TODO suggestion): provide an official and reliable way to temporarily enable/disable triggers. ALTER TABLE xxx ENABLE/DISABLE TRIGGER ALL/trgName We still have that nasty not presently checked everywhere it should be comment in the doc for pg_trigger... Yes, this could be achieved by dropping and recreating the trigger after importing, which I expect to be suggested by you ;-) Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] logging stuff
Josh Berkus [EMAIL PROTECTED] writes: From my perspective, we could really use a delimiter between the fields of log output which is unlikely to appear within those fields instead of parsing by character count, rather than making dbname a special case. Well, Andrew was arguing that space is a likely character in dbnames, but then again it's possible to put ] into dbnames. I think the only way to make this completely unambiguous would be to introduce a quoting convention for dbnames (and usernames when those get added). But if the goal is to allow simple parsing of log entries, that won't improve matters at all. My feeling about it is that DBAs who want to automatically parse log entries can just forbid spaces in the dbnames and usernames they allow. The KISS principle applies here. A variant (which'd be okay with me) is to separate these fields with tabs instead of spaces; then the rule for DBAs would be don't allow tabs in db/user names. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] logging stuff
Bruce Momjian [EMAIL PROTECTED] writes: I think we need a more general variable that can take several values, separated by commas, like: log_line: dbname,user or something like that. Strikes me as overkill --- adding two more booleans comparable to log_pid would do the job just as well with lots less coding effort. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Building beta packaging fails ...
The Hermit Hacker writes: /usr/bin/cvs -d /cvsroot -q checkout -rREL7_4_BETA1 -P pgsql /usr/bin/find pgsql -type d -name CVS -print | xargs rm -rf While I second the note that you really should be using cvs export, the second line is redundant in any case, because gmake dist already does that. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] logging stuff
If we're looking at this, we might want to look at how apache does it with it's customlog feature. This allows you to first define custom log types, then set them according to which virtual server you're setting up. I could see that being nice so you could create a couple of different custom log types, then use one for one database, and another for a different database. For people running large numbers of databases on a single cluster, it might be quite useful to be able to log each database differently. On Tue, 5 Aug 2003, Bruce Momjian wrote: Another interesting idea, if we do a single log variable with parameters separated by commas, is to allow some syntax where you could specify the delimiter between fields, so it could be: log_line: |, dbname, username or log_line:, dbname, username --- Josh Berkus wrote: Tom, I'm prepared to be guided by concensus, though. I'm not dead set on it either, just wanted to raise a flag. Who else has an opinion? From my perspective, we could really use a delimiter between the fields of log output which is unlikely to appear within those fields instead of parsing by character count, rather than making dbname a special case. Or do we already do this and I'm not parsing my log right? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Adjustment of spinlock sleep delays
Mike Mascari [EMAIL PROTECTED] writes: Should there be any correlation between the manner by which the backoff occurs and the number of active backends? If we could guess how many are contending for the same spinlock, maybe we could use that info ... but I don't see a reasonably cheap way to do so. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] TODO: trigger features
Bruce Momjian wrote: Andreas Pflug wrote: YATS (yet another TODO suggestion): provide an official and reliable way to temporarily enable/disable triggers. ALTER TABLE xxx ENABLE/DISABLE TRIGGER ALL/trgName We still have that nasty not presently checked everywhere it should be comment in the doc for pg_trigger... Yes, this could be achieved by dropping and recreating the trigger after importing, which I expect to be suggested by you ;-) We already have that TODO: * Allow triggers to be disabled [trigger] Sorry for that dup Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] problem with RH7.3 Pg7.3.4 binaries
Hi Magnus, Thanks for that. I always believed that the mcpu flag could enable a C compiler to generate code that could use the extra instructions on the newer CPUs - perhaps one day I'll get around to reading the documentation ;) Anyway, I've posted the compiled RH 7.3 postgresql-7.3.4 RPMs at http://www.webbased.co.uk/mca/pgsql/. Andrew, if you are satisfied that these work correctly on your RH7.3 install, there is no problem with Lamar placing copies of them on the postgresql website for people to download. Cheers, Mark. --- Mark Cave-Ayland Webbased Ltd. Tamar Science Park Derriford Plymouth PL6 8BX England Tel: +44 (0)1752 764445 Fax: +44 (0)1752 764446 This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. -Original Message- From: Magnus Naeslund(w) [mailto:[EMAIL PROTECTED] Sent: 05 August 2003 10:59 To: Mark Cave-Ayland Cc: 'Joe Conway'; 'Andrew Dunstan'; 'Lamar Owen'; 'Postgresql Hackers' Subject: Re: [HACKERS] problem with RH7.3 Pg7.3.4 binaries Mark Cave-Ayland said: Hi there, I have a RedHat 7.3 machine that can build the 7.3.4 RPMs if required - it only contains RPMs from the vanilla CD or from updates.redhat.com. I've just done a test build and everything seems OK except that the C compiler is passed the -mcpu=i686 flag - I'm guessing I need to somehow change this to i386 so it will binaries will run on actual i386 machines? Can someone point me in the right direction? The -mcpu flag doesn't do what you seems to think it does. It still generates i386 compatible code, but favours i686 processor timings etc. Cheers, Mark. Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Release changes
On Mon, Aug 04, 2003 at 10:47:36AM +0200, Andreas Pflug wrote: AFAICS the current implementation still doesn't have a way to access the affected rowset, so it'a pretty much like a SELECT without a WHERE. Yeah, unfortunately I didn't get a chance to implement this functionality during the 7.4 cycle (and it apparently wasn't important enough for anyone else to do it while I've been away). I'll probably get around to doing it some time before the 7.5 release. The current STATEMENT trigger code is still useful, IMHO -- I think your suggestion that we should remove any mention of this feature from the docs is certainly going too far. Should I submit a patch for the documentation to include a note about what functionality hasn't been implemented yet? -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Really odd corruption problem: cannot open pg_aggregate: No such file or directory
I too got this error. This happened with Postgres 7.2.3 and Linux 2.4.20 on via processor. This happened not on just one box but around dozen boxes. This may not be hardware problem. In our case, we create the table and use it then delete it. This activity happens very often, once a day. we run vaccum also. The problem happened on one such table. The entry for the table exists in pg_class but the actual file is missing. Once it gets to this state, the table can not be dropped. Were there any bug fixes related to this in the later versions of postgres. I searched in the google for this error and got some cases but not much information why. http://www.google.com/search?hl=enie=UTF-8oe=UTF-8q=%22RelationBuildDesc%3A+can%27t+open%22 Thanks, balaji. -Original Message- From: Adam Haberlach [mailto:[EMAIL PROTECTED] Sent: Thu 7/24/2003 11:07 AM To: [EMAIL PROTECTED] Cc: Subject:Re: [HACKERS] Really odd corruption problem: cannot open pg_aggregate: No such file or directory On Thu, Jul 24, 2003 at 10:17:06AM -0700, Adam Haberlach wrote: So, one of the many machines that I support seems to have developed an incredibly odd and specific corruption that I've never seen before. Whenever a query requiring an aggregate is attempted, it spits out: cannot open pg_aggregate: No such file or directory and fails. If I do: select * from pg_class where relname='pg_aggregate'; I see that the relation exists. If I check the relfilenode in the data directory, that exists, and seems to be an object file containing what should be the basic aggregate functions. version: PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) The system ran for a few weeks before anything odd happened, and then suddenly this. Does anyone have any ideas? Now that I look at the above string, I realize that the system /is/ an Athlon processor. Does anyone know if there could be an issue between the i686 and I'd like to thank everyone for the quick responses and the suggestion to strace the postmaster. open(/var/lib/pgsql/data/base/16556/16406, O_RDWR) = -1 ENOENT (No such file or directory) It looks like a file /was/ missing, and I had been looking in the wrong place to verify that it was there (the template database). I'm going to chalk this one up to bad hardware and hope it doesn't happen again. Thanks again... -- Adam Haberlach | When your product is stolen by thieves, you [EMAIL PROTECTED] | have a police problem. When it is stolen by http://mediariffic.com | millions of honest customers, you have a | marketing problem. - George Gilder ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Adjustment of spinlock sleep delays
Rod Taylor [EMAIL PROTECTED] writes: How about (round to nearest 10msec): time =3D oldtime + oldtime / 2 + oldtime * rand() while (time 1 second) time =3D time - 0.80sec This would stagger the wakeup times, and ensure a larger number of retries -- but the times should be large enough after the first few tries (larger than 200msec) that further backoff won't be required. But after the first few tries the sleep time would always exceed 200msec, so there would be a *maximum* of 60*5 = 300 tries before failing --- probably a lot less, like about 120 on average. The random component should already help to scatter the wakeups pretty well, so I'm thinking about just if (oldtime 1 sec) time = 10msec else time = oldtime + oldtime * rand() ie random growth of a maximum of 2x per try, and reset to minimum delay when you get past 1 sec. This would guarantee at least as many tries as I'm getting currently with the deterministic algorithm (which is effectively this if rand() always returned 1). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] cvs branch to use?
I'm looking over the last bits of translations of pg. Is it to late to update the translations for 7.4? Should I work on the cvs head version or is there some branching going on? It's not too late - translations are allowed during beta I think. And yeah, use cvs HEAD. Chris ---(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] logging stuff
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Adding several new variables is fine, but what do we call the hostname option if we already have log_hostname? shrug We've renamed GUC variables before for consistency. I'd opt for picking names that show the common purpose, maybe log_line_FOO? That's what I was thinking, that we might rename log_pid and log_timestamp to log_line_pid and log_line_timestamp to indicate it is printed on every line, and have the new booleans follow that pattern. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PostgreSQL JDBC driver Connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE)failures
I'm using 7.3.3 of the server, and pg73b1jdbc2.jar for the JDBC driver. Various permutations of calls to the setTransactionIsolation all fail to yield a driver state that will tell me that it is set up for serializable transactions. Help!? I googled, I grepped. I find mention of problems with respect to JDBC tests failing and patched, but see no reports of this as a bug, or any fixes for it. Here's the output from the attached program: javac -classpath .:/usr/share/pgsql/pg73b1jdbc3.jar TxnIsolation.java java -classpath .:/usr/share/pgsql/pg73b1jdbc3.jar TxnIsolation supportsTransactions(): true Default transaction isolation is 2 supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED): false supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED): true supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ): false supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE): true Current transaction isolation is 2 Transaction isolation after setting to TRANSACTION_SERIALIZABLE is 2 HAVE A PROBLEM Current auto commit is true After settting auto commit to false, it is false Transaction isolation after setAutoCommit() is 2 Current connection readonly status is false Connection readonly after setting it to be readonly is true --- Moving the setTransactionIsolation call to before the setAutoCommit call also results in failure. To run the program, you'll need to change the database and userid parameters in the DriverManager.getConnection call to something that works for you. import java.sql.* ; import java.io.* ; import java.util.* ; /** * Test JDBC transaction/connection stuff. In particular isolation levels. */ class TxnIsolation { public static void main(String[] args) { try { // MySql driver - // -classpath .:/usr/java/mysql-connector-java-2.0.14/mysql-connector-java-2.0.14-bin.jar // RH9: -classpath .:/home/dave/mysql-connector-java-3.0.8-stable/mysql-connector-java-3.0.8-stable-bin.jar //Class.forName(com.mysql.jdbc.Driver) ; //Connection conn = DriverManager.getConnection(jdbc:mysql:///test, dave, ) ; //String innodb = TYPE=INNODB ; // --- postgres 7.3.2 distribution JDBC2 driver linked as /usr/java/postgresql-jdbc.jar // -classpath .:/usr/java/postgresql-jdbc.jar Class.forName(org.postgresql.Driver) ; Connection conn = DriverManager.getConnection(jdbc:postgresql:test, dave, ) ; String innodb = ; // Here we go. // Metadata DatabaseMetaData md = conn.getMetaData() ; System.out.println(supportsTransactions(): + md.supportsTransactions()) ; System.out.println(Default transaction isolation is + md.getDefaultTransactionIsolation()) ; System.out.println(supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED): + md.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED)) ; System.out.println(supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED): + md.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED)) ; System.out.println(supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ): + md.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)) ; System.out.println(supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE): + md.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)) ; // transactionIsolation // Theoretically important to call BEFORE setAutoCommit System.out.println(Current transaction isolation is + conn.getTransactionIsolation()) ; conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) ; System.out.println(Transaction isolation after setting to TRANSACTION_SERIALIZABLE is + conn.getTransactionIsolation()) ; if (conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) System.out.println( HAVE A PROBLEM ) ; // autoCommit System.out.println(Current auto commit is + conn.getAutoCommit()) ; conn.setAutoCommit(false) ; System.out.println(After settting auto commit to false, it is + conn.getAutoCommit()) ; System.out.println(Transaction isolation after setAutoCommit() is + conn.getTransactionIsolation()) ; // readOnly System.out.println(Current connection readonly status is + conn.isReadOnly()) ; conn.setReadOnly(true) ; System.out.println(Connection readonly after setting it to be readonly is + conn.isReadOnly()) ; } catch (Exception e) {
Re: [HACKERS] Adjustment of spinlock sleep delays
Rod Taylor [EMAIL PROTECTED] writes: After the first few sleeps should it add a random() element to the delay time? Hmm, that's a thought --- but how big a random element? Fooling with the original idea, I'm having trouble with getting both plausible backoff and a reasonable number of attempts before failing. I tried the sequence 10 msec, 20 msec, 40, 80, ..., 1280 (1.28 sec), repeat but this only gives a couple of hundred tries before one minute has elapsed, which seems uncomfortably low. Maybe there's no alternative, though, if we want any good-sized delays in there. 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] 7.4 beta binaries
On 5 Aug 2003 at 9:40, Lamar Owen wrote: On Tuesday 05 August 2003 03:15, Shridhar Daithankar wrote: I am willing to build 7.4beta binaries on slackware and upload them someplace. This is just to add to binary packages readily available. Can anybody tell me what flags etc. are to be used. I have a slackware 9.0 installation with most of the developer tools I believe. I can give it a shot. Ok. If you want LSB-compliant locations, feel free to use the RPM locations as a model; I realize slack is going to have different locations for things. Is there an existing slack .tgz of PostgreSQL 7.3 or even 7.2 to use as a model? If there is, you would want to build it that way; principle of least surprise. OK, I will look for them. Actually slack does not have postgresql at all. You need to build it by hand. What I have built is bare minimum. Libpq+plpgsql and core. That's it. Obviously it won't fly much in real world but that's all I need. So I don't want to show up a package which does not have half the things people frequently use. Will check up on rpms on what components they enable. Bye Shridhar -- You speak of courage. Obviously you do not know the difference betweencourage and foolhardiness. Always it is the brave ones who die, the soldiers. -- Kor, the Klingon Commander, Errand of Mercy, stardate 3201.7 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]