Re: [HACKERS] TODO: trigger features
Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: 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. Only for what I would call extremely weird semantics of the triggers. If a trigger preceding yours did the same action (assigned x to itself), would you consider that something you needed to track? If so, how would you find out about it? You couldn't. If you want me to believe that the above is an important requirement, you'd better convince me that it's sane, because I don't think so. 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] --enable-thread-safety broken + patch regressions
Bruce, the changes you made yesterday to configure for --enable-thread-safety have broken the build, at least for Linux on Redhat 9. Also, I took the opportunity to look at port/threads.c. It is missing important functionality compaired to the patch I originally submitted. For getpwuid_r, gethostbyname_r and strerror_r there are three possible scenarios: 1. The OS doesn't have it (but the non _r function can still be thread safe (i.e. HPUX 11)). 2. The OS has it, but the implmentation doesn't match the POSIX spec. 3. The OS has it, and the implmentation matches the POSIX spec. Case 3 is not being considered. In my original patch this was handled by the pqGetpwuid etc functions simply being defined to getpwuid_r (except for pqStrerror). I remember discussing with you that the implementation of pqStrerror didn't really need the distinction between the two _r versions. However I think the others do, and the native/correct _r calls should be #defined in if they match the POSIX spec. It's also worth considering that when the _r function is available AND the normal function is also thread-safe then the _r version should still be used since it has a clean API which removes unneeded locking within the old function. I've still got the latest (and earlier with some configure work) patches I submitted up at: http://services.csl.co.uk/postgresql/ Thanks, Lee. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] status of dbf2pg
[EMAIL PROTECTED] wrote: I received the following note from the original author of dbf2pg: Date: Tue, 05 Aug 2003 18:43:22 +0400 From: Maarten Boekhold [EMAIL PROTECTED] Subject: Re: status of dbf2pg To: [EMAIL PROTECTED] On 08/03/2003 06:55:01 AM nolan wrote: What is the status of dbf2pg. There do not appear to have been any updates to it in quite some time. No status. I created this around 1995/96 and haven't looked at it since. 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. No problem --- we can address it then. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] this is in plain text (row level locks)
Jenny - [EMAIL PROTECTED] writes: so even though the application locks a row in a table, table-level locks are automatically taken by postgesql ? why is that? So that the table doesn't disappear while you're trying to scan it. (Or afterwards --- a row-level lock wouldn't be noticed by DROP TABLE.) Note that AccessShareLock is a pretty weak kind of lock, and holding it does not prevent most other operations. 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
[HACKERS] schemas and system tables
Is it feasible and/or advantageous to move all the system tables to a system schema (to system or pg_system)? This seems a much more natural place for this type of information. This would remove the artificial pg_ restriction on class names and simplify the overall system a little bit. Just a thought. Regards, Merlin
Re: [HACKERS] 7.4Beta1: Compile Failure: UnixWare 7.1.3UP2
Larry Rosenman [EMAIL PROTECTED] writes: sys/socket.h:#define shutdown _shutdown Mph. I wonder if any other platforms do that? Well, I'd better assume that shutdown isn't a safe name for a globally visible field. I'll rename it. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Select distinct question ... complicated
Hi , I have a table: item location aaa 10 aaa 20 bbb 10 bbb 10 ccc 10 ccc 20 I need to select distinct items where locations are the same. So result set should look like: item loation bbb 10 Already spent 7 hours on this one. Thanks a lot / Alex __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Passing server_encoding to the client is not future-proof
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: One of the reasons for not doing conversion in binary mode is to have an escape hatch for unconvertible characters, eg for dump purposes. That functionality is already provided by setting the client encoding to SQL_ASCII. Hm. Okay, so are you arguing that we should not remove encoding conversion from the binary-transmission case? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] logging stuff
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: ... And of course, we already have pid and timestamp, so once we are done, we will have seven possible data items on each line, and with booleans there will be no control over their order on the line. Which is exactly the way I want it ;-). I can't see any use that would justify the amount of extra logic needed to allow user-specified ordering of the entries. This feature discussion seems to be degenerating into a gild-the-lily contest ... Depends if someone needs a lilly, though I have not heard anyone say they do. ;-) Adding several new variables is fine, but what do we call the hostname option if we already have log_hostname? -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Adjustment of spinlock sleep delays
I said: 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). Eventually it occurred to me that when using random delays, we should set the timeout to occur after a fixed number of tries, not after a fixed total time spent. This is because the probability of unwanted failure (ie, the spinlock isn't really stuck, you just managed to always look when someone else had it) depends directly on the number of tries. I've committed code that does the above with a limit of 1000 iterations; timeout seems to take about 3.5 minutes on average. (In the prior code we would make 6000 attempts over a period of 1 minute.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Select distinct question ... complicated
This should be on the otyher lists, novice or general for example but here is your answer anyway. SELECT item, location FROM foo GROUP BY item,location HAVING count(item) 1 AND count(location) 1; On Wednesday 06 August 2003 12:05, The Pennant Shop wrote: Hi , I have a table: item location aaa 10 aaa 20 bbb 10 bbb 10 ccc 10 ccc 20 I need to select distinct items where locations are the same. So result set should look like: item loation bbb 10 Already spent 7 hours on this one. Thanks a lot / Alex __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Release changes
--On Tuesday, August 05, 2003 10:36:32 -0400 Bruce Momjian [EMAIL PROTECTED] wrote: 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 Yes, considering that it's a format change and Tom didn't want to back port because it is a format change/behavior change. Thanks! LER - -- 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
Re: [HACKERS] schemas and system tables
On Wed, 6 Aug 2003, Merlin Moncure wrote: Is it feasible and/or advantageous to move all the system tables to a system schema (to system or pg_system)? This seems a much more natural place for this type of information. This would remove the artificial 'pg_' restriction on class names and simplify the overall system a little bit. Just a thought. I believe 7.3 already did this with pg_catalog. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4 Beta1 elog problem
Robert Creager wrote: psql:dbTriggers.sql:30: ERROR: could not load library /usr/local/pgsql/triggers/tassiv_triggers.so: /usr/local/pgsql/triggers/tassiv_triggers.so: undefined symbol: elog Am I missing something? I was previously running 7.3.3... elog is defined now as a macro (in utils/elog.h). Did you recompile your trigger function after installing 7.4? Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] logging stuff
Andrew Dunstan [EMAIL PROTECTED] writes: 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? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] logging stuff
On Tue, 5 Aug 2003, Andrew Dunstan wrote: (Responding to the deafening silence regarding my posts a couple of days ago about logging dbnames and disconnections) ;-) The dbname patch is now done. If nobody objects to the format ([db:yourdbname]) I'll submit it - I did it that way to make it fairly easy to split a log file based on it, although you would have to be careful with multiline log entries such as query strings. It is intentionally minimalist. I had some thoughts about logging disconnections - I can see a way to do it via an on_proc_exit handler, I think. Then I started wondering if it might be useful to log session times instead of just noting a disconnect and letting the user have to calculate the time. But I won't bother with this if there's no interest. *I* have no current use for it, but I could well imagine others might. (I might too in the future if I wanted to debug my connection pooling app). Actually, I'd certainly like to see it done (both dbname and disconnect). I'd guess the deafening silence was more because of no objctions than lack of interest. I know for me it was. ---(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] Thread-safe configuration option appears to
Peter Eisentraut wrote: Bruce Momjian writes: First get your own platforms enabled for the existing thread flag, and we can revisit this when most/all our platforms are supported. We want to avoid confusion of having things work for some platforms and not others with no way to communicate that to the users. Yes, let's settle on that for now (= release 7.4): Without --enable-thread-safety, you get the same old; with --enable-thread-safety, you get _REENTRANT (or equivalent) for libpq and libecpg, and you get pthreads in libecpg. Then users and packagers can judge the impact on their platform for themselves. While the release is out there, we can gather more data on this and information for the forgotten platforms, and then for 7.5 we might have something that pleases more people by default. OK. Where I see this going, however, is three buckets: one group of platforms will have near zero impact and there will be pressure to enable thread safety by default (BSD/OS, Linux, UnixWare), a second group of platforms where there will be an endless debate about which is right (FreeBSD, AIX), and a third group of platforms that have no thread-safety no matter how hard you look (mostly the old ones). So in the end we will either have to document libpq is thread-safe on platform A, B, and C, or we will have to keep the switch for all platforms and leave it off by default. I am hoping groups 1 and 2 can be merged. I think a good rule is that if libc is threadsafe, we can someday enable libpq to be thread-safe by default, and if there is a libc_r that is thread-safe, we create a libpq_r for that. In fact, I thought we were going to try that for 7.4 when --enable-thread-safety is added to configure. Perhaps by 7.5 we can enable the above logic by default. However, I do think we will have to mention the platforms that aren't thread-safe some day, of course, once we enable thread-safe by default. -- 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 4: Don't 'kill -9' the postmaster
