[GENERAL] native win32 version
Hi, sometime ago I heard that there will be a native win32 version of postgresql 7.4. Is this true or will there only be a cygwin version like now? What do you think, how stable and fast is the cygwin version? Best regards, Christian ---(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
[GENERAL] Mail server load
Marc, I'd be interested in seeing the updated stats for this bought of virus transmission we're going through. Yesterday you had almost 1 for 1 valid email. By then I think I was getting about 3-4 per valid email but since then it's sky rocketed and it looks more like 30+ per 1 valid message. I'd just be interested if that's the same others are seeing since I believe the virus picks up my email address from the messages sent to the lists. -- Nigel Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] That movie
Please see the attached file for details. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] move to usenet?
Op 19 Aug 2003 (15:35), schreef The Hermit Hacker [EMAIL PROTECTED]: On Tue, 19 Aug 2003, Jules Alberts wrote: Hello everyone, This is not a troll and I certainly don't want to start a holy war but wouldn't it be a good idea to move the postgresql lists from the mailing list approach to usenet? IMHO it's more open and easier to work with. As it is now, there are some postgreql usenet groups but hardly anything happens there. connect to news.us.postgresql.org or news.fr.postgresql.org if you want to get all messages/groups ... they are all gate'd ... if anyone out there is running a news server that is willing to provide a relay point, please contact me, and we'll work on getting a feed in place ... Thanks for the tip. I can't find these groups on usenet, but I will ask my provider if he can get them, shouldn't be a problem. Another question: are these groups only a reflection of the mailinglist, or is it also possible to do postings there. Will these be visible to the mailinglist? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Details
Please see the attached file for details. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Mail server load
So far today:
neptune# awk '{print $7}' /var/log/amavisd | sort | uniq -c
137 BAD
1732 BANNED
4435 INFECTED
6029 Passed,
On Wed, 20 Aug 2003, Nigel J. Andrews wrote:
Marc, I'd be interested in seeing the updated stats for this bought of virus
transmission we're going through.
Yesterday you had almost 1 for 1 valid email. By then I think I was getting
about 3-4 per valid email but since then it's sky rocketed and it looks more
like 30+ per 1 valid message.
I'd just be interested if that's the same others are seeing since I believe the
virus picks up my email address from the messages sent to the lists.
--
Nigel Andrews
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] move to usenet?
On Wed, 20 Aug 2003, Jules Alberts wrote: Op 19 Aug 2003 (15:35), schreef The Hermit Hacker [EMAIL PROTECTED]: On Tue, 19 Aug 2003, Jules Alberts wrote: Hello everyone, This is not a troll and I certainly don't want to start a holy war but wouldn't it be a good idea to move the postgresql lists from the mailing list approach to usenet? IMHO it's more open and easier to work with. As it is now, there are some postgreql usenet groups but hardly anything happens there. connect to news.us.postgresql.org or news.fr.postgresql.org if you want to get all messages/groups ... they are all gate'd ... if anyone out there is running a news server that is willing to provide a relay point, please contact me, and we'll work on getting a feed in place ... Thanks for the tip. I can't find these groups on usenet, but I will ask my provider if he can get them, shouldn't be a problem. Another question: are these groups only a reflection of the mailinglist, or is it also possible to do postings there. Will these be visible to the mailinglist? The gateway is bi-directional, but unless you are actually subscribed to the mailing list (there is a set nomail option available), your posting has to go through the moderator (me) before it gets to the mailing list *from* usenet ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Mail server load
So far today:
neptune# awk '{print $7}' /var/log/amavisd | sort | uniq -c
137 BAD
1732 BANNED
4435 INFECTED
6029 Passed,
And still some make it through given some of the messages that are
reaching the list today (That movie or My details). :-(
---
Francois
Home page: http://www.monpetitcoin.com/
Would Descartes have programmed in Pascal? - Umberto Eco
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Mail server load
On Wed, 20 Aug 2003, Francois Suter wrote:
So far today:
neptune# awk '{print $7}' /var/log/amavisd | sort | uniq -c
137 BAD
1732 BANNED
4435 INFECTED
6029 Passed,
And still some make it through given some of the messages that are
reaching the list today (That movie or My details). :-(
Actually, unless I'm mistaken, none have made it through ... at least all
the ones with subject's like That movie that I've opened (thank god for
Unix) didn't actually have anything attached, at least as far as those
coming from the list have been concerned ...
For instance, one to -hackers that I just received with a subject of
Details was 3.2k ... based on my personal mailbox, if the virus was
actually attached, it would have been 100k in size ...
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Mail server load
On 20/08/2003 08:18 Nigel J. Andrews wrote: Marc, I'd be interested in seeing the updated stats for this bought of virus transmission we're going through. Yesterday you had almost 1 for 1 valid email. By then I think I was getting about 3-4 per valid email but since then it's sky rocketed and it looks more like 30+ per 1 valid message. I'd just be interested if that's the same others are seeing since I believe the virus picks up my email address from the messages sent to the lists. There's a few come thru the list to me and I had a few more yesterday as part of the daily spam. Like most people from the non-M$ world, this sort of thing just passes me by :) -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Mail server load
On Wed, 20 Aug 2003, Paul Thomas wrote:
There's a few come thru the list to me and I had a few more yesterday as
part of the daily spam. Like most people from the non-M$ world, this sort
of thing just passes me by :)
I'm looking into how to add a 'taboo subject' filter onto the mj2 lists
themselves ... right now, I have a personal filter on:
elsif anyof (header :contains [Subject] Approved,
header :contains [Subject] Thank you!,
header :contains [Subject] That movie,
header :contains [Subject] Your details,
header :contains [Subject] Wicked screensaver) {
fileinto INBOX.garbage;
}
I can't think of anyone using anything but *maybe* the Approved one in
their Subject, so there shouldn't be too many false positives ...
hopefully hear something from the mj2 guys relatively soon ...
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] native win32 version
On Wed, 2003-08-20 at 02:17, Christian Traber wrote:
Hi,
sometime ago I heard that there will be a native win32 version of
postgresql 7.4.
Is this true or will there only be a cygwin version like now?
The native win32 port has been pushed back from 7.4 into (hopefully)
7.5, but cygwin version will still be available as always.
What do you think, how stable and fast is the cygwin version?
Well, some people do run in in production using the cygwin version, so
it is stable enough and fast enough for some folks. I don't think you'll
ever see a recommendation from the development group to run postgresql
on windows over a *nix OS for production purposes (even when native
win32 comes about, theres just too much lacking in windows) however for
development purposes it should be more than fine.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Mail server load
On Wed, 2003-08-20 at 08:11, The Hermit Hacker wrote:
On Wed, 20 Aug 2003, Paul Thomas wrote:
There's a few come thru the list to me and I had a few more yesterday as
part of the daily spam. Like most people from the non-M$ world, this sort
of thing just passes me by :)
I'm looking into how to add a 'taboo subject' filter onto the mj2 lists
themselves ... right now, I have a personal filter on:
elsif anyof (header :contains [Subject] Approved,
header :contains [Subject] Thank you!,
header :contains [Subject] That movie,
header :contains [Subject] Your details,
header :contains [Subject] Wicked screensaver) {
fileinto INBOX.garbage;
}
I can't think of anyone using anything but *maybe* the Approved one in
their Subject, so there shouldn't be too many false positives ...
hopefully hear something from the mj2 guys relatively soon ...
Little does Marc know that the guys from 20th Century Fox have just
scrapped their idea to do a History of PostgreSQL move after repeated
attempts to contact anyone on the mailing lists never got through ;-)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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: [GENERAL] Buglist
On Tue, Aug 19, 2003 at 21:51:14 -0400, Vivek Khera [EMAIL PROTECTED] wrote: I'm not promoting any change in the MVCC. What I'm saying is that it would be really cool if the backend process itself could recognize that a row is no longer referenced by any transactions upon termination of the transaction, and release it back to the system. This is just what vacuum does but in a batched manner. I would love to see it incremental. This would result in pretty much near zero internal fragmentation, I think. Why do you care about about the details of the implementation (rather than the performance)? If it were faster to do it that way, that's how it would have been done in the first place. The cost of doing the above is almost certainly going to be an overall performance loser. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] uptime problem
I have trouble: when client-program broke connection to Postgre I see in logs: FATAL: This connection has been terminated by the administrator. LOG: shutting down and I must start Postgre again. Postgre starting as: pg_ctl -D /usr/local/pgdata -o \-S\ start I have PostgreSQL 7.3.4 (build from FreeBSD ports). Whats wrong? Thanks in advance! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] uptime problem
On Wednesday 20 August 2003 18:51, Vasili G. Yanov wrote: I have trouble: when client-program broke connection to Postgre I see in logs: FATAL: This connection has been terminated by the administrator. LOG: shutting down and I must start Postgre again. Postgre starting as: pg_ctl -D /usr/local/pgdata -o \-S\ start If you get rid of that -S and specify a logfile, I am sure you will find something in logs that would help you. Discardign logs is not a good idea in general. Not especially if you have problems.. Shridhar ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Thank you!
Please see the attached file for details. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] uptime problem
On Wednesday 20 August 2003 19:01, Vasili G. Yanov wrote: I have trouble: when client-program broke connection to Postgre I see in logs: FATAL: This connection has been terminated by the administrator. LOG: shutting down and I must start Postgre again. Postgre starting as: pg_ctl -D /usr/local/pgdata -o \-S\ start SD If you get rid of that -S and specify a logfile, I am sure you will find SD something in logs that would help you. SD Discardign logs is not a good idea in general. Not especially if you have SD problems.. I store all logs in syslog. Hmm.. Looking at the error message again, looks like somebody is killing postmaster. Is there any other script which is trying to kick in? HTH Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Thank you!
See the attached file for details ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Buglist
BW == Bruno Wolff, Bruno writes: to see it incremental. This would result in pretty much near zero internal fragmentation, I think. BW Why do you care about about the details of the implementation (rather than BW the performance)? If it were faster to do it that way, that's how it would BW have been done in the first place. The cost of doing the above is almost BW certainly going to be an overall performance loser. I care for the performance. And how are you so sure that it was faster the way it is now? Are you sure it was not done this way because of ease of implementation? Seriously, how much slower can it be if the backend were to do the checking for external references upon updating/deleting a row? The cost would be distributed across time as opposed to concentrated at once within a vacuum process. I am fairly certian it would reduce disk bandwidth requirements since at least one necessary page will already be in memory. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Buglist
On Wed, Aug 20, 2003 at 10:31:25 -0400, Vivek Khera [EMAIL PROTECTED] wrote: I care for the performance. And how are you so sure that it was faster the way it is now? Are you sure it was not done this way because of ease of implementation? Seriously, how much slower can it be if the backend were to do the checking for external references upon updating/deleting a row? The cost would be distributed across time as opposed to concentrated at once within a vacuum process. I am fairly certian it would reduce disk bandwidth requirements since at least one necessary page will already be in memory. It would probably be a lot slower. Any transaction that has started but not yet finished would need to lock all rows that exist at during the transaction (for serialized transaction isolation you would only need to worry about rows that existed at the start of the transaction or that were modified by the transaction). Immediately that is a big deal since a typical query may need to lock a bunch of rows that it will never actually touch (but you don't know that when the transaction starts). Managing all those locks would take up a lot of semiconductor memory or a lot of disk writes and be a major source of contention. The current system just has to mark rows when they are created and deleted (an update does both operations). The normal vacuum clean up actually isn't going to be much worse than what you would need to do at both the start and end of each transaction. The overhead of letting dead rows hang around for a while after they aren't needed isn't that high. Also, since at least 7.3, normal vacuums aren't normally going to affect the performance of your database server that much. The main issue against the current vacuum system is that it requires the DBA knowing what vacuum does and figuring out how it should be used in their situation to get reasonable performance. This makes it a bit harder for non-DBAs to jump right in to Postgres without running into problems. However, the work on autovacuum seems to be providing a reasonable solution to that problem. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Buglist
On Wed, Aug 20, 2003 at 10:31:25AM -0400, Vivek Khera wrote: Seriously, how much slower can it be if the backend were to do the checking for external references upon updating/deleting a row? The cost would be distributed across time as opposed to concentrated at once within a vacuum process. I am fairly certian it would reduce disk bandwidth requirements since at least one necessary page will already be in memory. There's no way to check for external references, because said references are actually the running transactions. So you can't drop a row until all the transactions that were active during your transaction are finished. Certainly your own backend can't do the checking, because there's no way to even assert that it will be live when those other transactions finish. Who will? The last of said transactions? It certainly will be expensive for a backend to keep track of the deleted/updated tuples by all _other_ backends, just in case... Long running transactions can't be neglected, so you can't keep it in memory. INVHO the solution to this problem will come in the form of a autovaccum daemon integrated into the backend... -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Ninguna manada de bestias tiene una voz tan horrible como la humana (Orual) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Buglist
On Wed, 20 Aug 2003, Vivek Khera wrote: BW == Bruno Wolff, Bruno writes: to see it incremental. This would result in pretty much near zero internal fragmentation, I think. BW Why do you care about about the details of the implementation (rather than BW the performance)? If it were faster to do it that way, that's how it would BW have been done in the first place. The cost of doing the above is almost BW certainly going to be an overall performance loser. I care for the performance. And how are you so sure that it was faster the way it is now? Are you sure it was not done this way because of ease of implementation? Seriously, how much slower can it be if the backend were to do the checking for external references upon updating/deleting a row? The cost would be distributed across time as opposed to concentrated at once within a vacuum process. I am fairly certian it would reduce disk bandwidth requirements since at least one necessary page will already be in memory. Time for a mental exercise. Our server has 2 users. Each backend has to check with all the other backends when it deletes a tuple (every update is also a delete, remember every change in an MVCC database is a create / delte cycle.) Let's create a name for the time it takes to do the update / mark deleted versus the time it takes to contact each of those other backends. Tw is the Time to do the work here, and Tc is the time to do the cleanup (i.e. vacuum the tuple) Note that we'd also need a Ta for answering the requests of all the other backends, but we can assume that on average, for each request a child process makes, it will receive exactly that many from each other backend running. Let x represent the number of backends. So the answer time is equal to x*Tc Time = Tw + Tc + Ta Time = Tw + Tc + (x * Tc) Time = Tw + ((x+1) * Tc) and our cleanup time starts to grow at an ugly rate as the number of backends increases. Lazy vacuuming allows the database to reclaim lost space in the background, as the newer non-full vacuum does. Many folks mistake this vacuum process for its older, slower cousin, full vacuum, which does eat a lot more disk bandwidth and slow the machine down. On a Dual CPU X86 box a lazy vacuum running in a continuous loop will eat about 5% of one CPU and drop pgbench scores by 10 to 15%. The important thing here, is that the machine will still run quite snappily when you throw several hundred clients at it, since the lazy vacuum just sits in the background using the spare cycles and not much more. that means your storage usage may baloon somewhat under intense usage, but you won't have an IPC storm kill the performance of the postgresql server. Knowing the postgresql development team, I'm sure the reasons they chose are clearly stated in the hackers mailing list somewhere in time, so I'm gonna go look, but trust me on one thing, the guys programming this database don't do much because it's easier / faster to implement without putting something in the TODO list about making it better some day. ---(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: [GENERAL] Buglist
Vivek Khera [EMAIL PROTECTED] writes: JW == Jan Wieck [EMAIL PROTECTED] writes: JW remove all the index entries pointing to these ctid's. Your idea is (so JW far) lacking a place where to remember all the single removed rows and I JW assume you're not planning to pay the cost of a full scan over all JW indexes of a table to reclaim the space of one data row, are you? Well, that pretty much kills my idea... back to autovacuum ;-) In addition to the index-cleanup issue that Jan explained, there are locking problems. The tuple-is-dead hint bit mechanism is very carefully designed so that a backend can set the hint bits while holding only a shared lock on the page containing the tuple. Physically removing a tuple requires a far stronger lock (see the source code for details). Thus, having ordinary interactive backends remove tuples would have bad consequences for concurrent performance. But I think the real point here is that there's no reason to think that doing tuple deletion on-the-fly in foreground transactions is superior to doing it in background with a vacuum process. You're taking what should be noncritical maintenance work and moving it into the critical paths of your foreground applications. Not only that, but you're probably doing more total work per tuple --- VACUUM batches its work in more ways than just the index cleanup aspect, IIRC. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Collation rules and multi-lingual databases
My understanding is that the entire set of localization parameters needs to be decided upon when the initdb is done and can never be changed later. Is that right? I have a multi-lingual web site, I want to be able to sort using collation rules for en_US, en_CA, and fr_CA depending on the current web site user. There's no way I can do a database query to do these sorts is there? -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Grouping by date range
Bruno Wolff III [EMAIL PROTECTED] writes: On Wed, Aug 20, 2003 at 13:44:59 -0500, Ron Johnson [EMAIL PROTECTED] wrote: The GROUP BY does implicit sorting, so an ORDER BY on the exact same column(s) as the GROUP BY is redundant. That is an implementation detail, not a promise. With hashed aggregates in 7.4, you might find this isn't true. s/might/will/ 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: [GENERAL] Buglist
Stephan Szabo [EMAIL PROTECTED] writes: True, but the message being responded to was specifically if the backend were to do the checking for external references upon updating/deleting a row. It's clearly impossible for a backend to remove a row immediately upon updating/deleting it, since it cannot know whether it will succeed in committing its transaction. The implementable variant of this would correspond to extending the check-whether-committed-deleted code to see whether a previously deleted tuple is now removable --- that is, moving VACUUM's processing of the tuple into the main line. In any case, I thought it only does the committed deleted stuff when it comes upon a row in a scan, which means that it's still not automatic clean up in general since any particular deleted row may not get looked at for some amount of time after all possible viewers are gone. Recall also that committed deleted does not mean safe to remove. There may still be live transactions that could see the tuple. The committed deleted bit just exists to allow subsequent visitors to the row to skip one of the more expensive steps in deciding whether they can see it or not. To determine that a row is removable requires additional tests above and beyond what backends normally do. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Buglist
Tom Lane wrote: Recall also that committed deleted does not mean safe to remove. There may still be live transactions that could see the tuple. The committed deleted bit just exists to allow subsequent visitors to the row to skip one of the more expensive steps in deciding whether they can see it or not. To determine that a row is removable requires additional tests above and beyond what backends normally do. Aah - there is the first bullet hole in my multi-ctid-index-idea. Now the question becomes how expensive these tests are (if a normal backend can do them at all within reason)? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [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: [GENERAL] 7.4b1 vs 7.3.4 performance
P.J. \Josh\ Rovero [EMAIL PROTECTED] writes: 7.4b1 is significantly faster (i.e., the higher curve) over this range of clients and transactions. Cool. I wonder though why the 7.4 curve is so much noisier. 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: [GENERAL] Mail server load
holy S**T!!
The Hermit Hacker wrote:
16:00 ...
neptune# awk '{print $7}' /var/log/amavisd | sort | uniq -c
285 BAD
1807 BANNED
12289 INFECTED
11731 Passed,
5 SA
1 turned
Here's a normal day:
neptune# cat /var/log/amavisd.o | grep Aug 17 | awk '{print $7}' | sort
| uniq -c
332 BAD
13 BANNED
938 INFECTED
3792 Passed,
On Wed, 20 Aug 2003, Tom Lane wrote:
Nigel J. Andrews [EMAIL PROTECTED] writes:
Yesterday you had almost 1 for 1 valid email. By then I think I was getting
about 3-4 per valid email but since then it's sky rocketed and it looks more
like 30+ per 1 valid message.
FWIW, this is what I see in traffic to an address I've had to abandon
because of spam:
488 Aug 8
433 Aug 9
435 Aug 10
426 Aug 11
504 Aug 12
458 Aug 13
469 Aug 14
390 Aug 15
433 Aug 16
371 Aug 17
520 Aug 18
36473 Aug 19
35808 Aug 20
It's about 3pm local time here, so by midnight the stat for today will
probably be nearly double yesterday's total.
The spam traffic had been around 2K/day at the beginning of the year,
but tapered off to around 500 as you see above. This spike is ten times
the highest I've seen before. If I were actually downloading this crap,
and not rejecting it at the SMTP handshake, my DSL line would be
saturated :-(
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org
---(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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Grouping by date range
On Wed, 2003-08-20 at 14:51, Tom Lane wrote: Bruno Wolff III [EMAIL PROTECTED] writes: On Wed, Aug 20, 2003 at 13:44:59 -0500, Ron Johnson [EMAIL PROTECTED] wrote: The GROUP BY does implicit sorting, so an ORDER BY on the exact same column(s) as the GROUP BY is redundant. That is an implementation detail, not a promise. With hashed aggregates in 7.4, you might find this isn't true. s/might/will/ From 7.3.3, where the records were randomly inserted; note how GROUP BY acts like I described: test1=# select f, count(*) test1-# from t test1-# group by f; f | count ---+--- 1 | 3 2 | 5 4 | 4 (3 rows) The new 7.4 attitude is *really* good to know, because, otherwise, all our reports would break! -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA Fair is where you take your cows to be judged. Unknown ---(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: [GENERAL] Buglist
Jan Wieck [EMAIL PROTECTED] writes: Aah - there is the first bullet hole in my multi-ctid-index-idea. Now the question becomes how expensive these tests are (if a normal backend can do them at all within reason)? It's not hugely expensive, IIRC, you just need to make some additional checks against global xmin (compare HeapTupleSatisfiesVacuum against the others). We're already doing something similar for the optimization that suppresses subsequent heap lookups for globally-dead index tuples. I'm dubious about the multi-ctid idea though because it would mean bloating the index tuple header, whether there was any use for multiple entries or not. (Could we make the header variable size? Not sure it's worth the trouble.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Buglist
On Wed, Aug 20, 2003 at 12:40:03PM -0400, Vivek Khera wrote: BW == Bruno Wolff, Bruno writes: BW Also, since at least 7.3, normal vacuums aren't normally going to BW affect the performance of your database server that much. I disagree. Triggering a vacuum on a db that is nearly saturating the disk bandwidth has a significant impact. Vivek is right about this. If your system is already very busy, then a vacuum on a largish table is painful. I don't actually think having the process done in real time will help, though -- it seems to me what would be more useful is an even lazier vacuum: something that could be told clean up as cycles are available, but make sure you stay out of the way. Of course, that's easy to say glibly, and mighty hard to do, I expect. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Changed mailing list functionality?
we haven't changed any of the list configs in months ... On Wed, 20 Aug 2003, Ron Johnson wrote: Hi, Up until a few days ago, when I did a Reply to List in my MUA (Evolution 1.4.4), only [EMAIL PROTECTED] would show up in the To: list. Now, Reply to List acts like like Reply to All. The reason I bring this up in [EMAIL PROTECTED] is that Reply to List still acts properly wrt [EMAIL PROTECTED], so if it *is* a bug in Evo 1.4.4, it's not a pervasive bug. Sincerely, Ron -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA YODA: Code! Yes. A programmer's strength flows from code maintainability. But beware of Perl. Terse syntax... more than one way to do it...default variables. The dark side of code maintainability are they. Easily they flow, quick to join you when code you write. If once you start down the dark path, forever will it dominate your destiny, consume you it will. ---(end of broadcast)--- TIP 8: explain analyze is your friend Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Changed mailing list functionality?
On Wed, 2003-08-20 at 15:57, The Hermit Hacker wrote: we haven't changed any of the list configs in months ... Ok, thanks. On Wed, 20 Aug 2003, Ron Johnson wrote: Hi, Up until a few days ago, when I did a Reply to List in my MUA (Evolution 1.4.4), only [EMAIL PROTECTED] would show up in the To: list. Now, Reply to List acts like like Reply to All. The reason I bring this up in [EMAIL PROTECTED] is that Reply to List still acts properly wrt [EMAIL PROTECTED], so if it *is* a bug in Evo 1.4.4, it's not a pervasive bug. Sincerely, Ron -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA For me and windows it became a matter of easy to start with, and becoming increasingly difficult to be productive as time went on, and if something went wrong very difficult to fix, compared to linux's large over head setting up and learning the system with ease of use and the increase in productivity becoming larger the longer I use the system. Rohan Nicholls , The Netherlands ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL]
I upgraded to 7.3.3 from 7.2 something. I guess the insert statment no longer accepts a zero lenght string like '' ERROR: pg_atoi: zero-length string Is there a way to make this work so it will put Null in the place of it? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] move to usenet?
On Tuesday 19 Aug 2003 15:43 in [EMAIL PROTECTED], Chris M ([EMAIL PROTECTED]) wrote: I use outlook express to visit news.postgresql.org now. It works well. Thank you for top-posting and full-quoting, just to prove my point. ... :-) -- Regards, Dave [RLU#314465] == [EMAIL PROTECTED] (David W Noon) Remove spam trap to reply via e-mail. == ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Example Database
OK. I understand that you prefer a different approach. For my part, I like to combine different approaches. I understand also that you consider it impossible to put all the stuff into one database. I think it is possible after all, but that is not the point here. My request remains. I am looking for example databases that demonstrate good style in using and combining the features of PostgreSQL. I am sure there is something out there in the net. Would somebody be so kind to point me there? Regards, Erwin Jamie Lawrence wrote: On Mon, 18 Aug 2003, Dustin Sallings wrote: Postgres has enough features that I would guess you're not going to find a database that uses all of them. You'll probably find it a lot easier to learn postgres by thinking of something you want to do and trying to solve the problem with postgres. I find it really difficult to learn a new development tool without having a particular problem to solve. I almost chimed in earlier, but I'd certainly second this. As far as the freedom Postgres offers in terms of flexibility of design, there's no way any single DB will illustrate it well. I started off using it becuase it was a real database in the free space, and then started using other features when they made sense. The ORDB stuff I've only used once; I have to admit that I like a fully relational database. But there are cases I haven't seen... The views/rules/triggers capability is priceless. Or, rather, Oracle will put a price on it for you, which is significantly more than Postgres. Play with it. -j ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] move to usenet?
On Tuesday 19 Aug 2003 09:06 in [EMAIL PROTECTED], Jules Alberts ([EMAIL PROTECTED]) wrote: This is not a troll and I certainly don't want to start a holy war but wouldn't it be a good idea to move the postgresql lists from the mailing list approach to usenet? I don't know about anybody else, but I am already reading these messages from a Usenet newsserver. I do not receive them as e-mail. I agree that many messages are not formatted according to Usenet conventions, but I normally attribute that to Windows users who know nothing about the Internet. Thus, top-posting and full-quoting are rife all across Usenet. -- Regards, Dave [RLU#314465] == [EMAIL PROTECTED] (David W Noon) Remove spam trap to reply via e-mail. == ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Buglist
Andrew Sullivan wrote: On Wed, Aug 20, 2003 at 12:40:03PM -0400, Vivek Khera wrote: BW == Bruno Wolff, Bruno writes: BW Also, since at least 7.3, normal vacuums aren't normally going to BW affect the performance of your database server that much. I disagree. Triggering a vacuum on a db that is nearly saturating the disk bandwidth has a significant impact. Vivek is right about this. If your system is already very busy, then a vacuum on a largish table is painful. I don't actually think having the process done in real time will help, though -- it seems to me what would be more useful is an even lazier vacuum: something that could be told clean up as cycles are available, but make sure you stay out of the way. Of course, that's easy to say glibly, and mighty hard to do, I expect. What about a little hint to the buffer management that if it has to evict another buffer to physically read this one (meaning the buffer pool was full already) then it will not put this buffer at the top of the LRU chain but rather at it's end? This way a vacuum on a large table will not cause a complete cache eviction. Might be a useful hint for sequential scans too. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Collation rules and multi-lingual databases
Greg Stark [EMAIL PROTECTED] writes: My understanding is that the entire set of localization parameters needs to be decided upon when the initdb is done and can never be changed later. Is that right? No, not all of them are frozen. Unfortunately, the one you care about (LC_COLLATE) is. The reason for this is that it determines index ordering for textual columns, and so changing LC_COLLATE on the fly produces instant corrupt indexes :-( A solution for this is on the TODO list, but don't hold your breath ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Mailing list in French
Well, the idea of mailing list is to exchange information and help each other. Obviously if few great things happen on some mailing list and others don't come to know it, defeats the purpose of having a mailing list. I agree. This is one of my worries. If nothing happens on the French list, it will be an empty shell. Then again, there seems to be a demand for something not in English. So here goes and let's just hope it will work out. As an experiment can we have a translation gateway so that these two lists, in general and french-general can interact? I mean the idea should be to help people getting communicate better rather than having a separate list as such. That would be nice, but how could we go about that? Cheers --- Francois Home page: http://www.monpetitcoin.com/ Would Descartes have programmed in Pascal? - Umberto Eco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Mail server load
On Wed, 20 Aug 2003, Dennis Gearon wrote:
holy S**T!!
Particularly the 'Passed' number. Now I'm not subscribed to all of the lists
but I am on -general, -hackers and a couple of others like -interfaces and yet
I would say that the volume of email I'm seeing from the lists is far lower
than normal _not_ more by a factor of 3-ish.
BTW, I wasn't suggesting the virus emails I get come through the lists, was
just refering to the harvesting of my email address by the virus.
[Tom's numbers are absolutely amazing. I seem to be up to around 60 per minute
now]
The Hermit Hacker wrote:
16:00 ...
neptune# awk '{print $7}' /var/log/amavisd | sort | uniq -c
285 BAD
1807 BANNED
12289 INFECTED
11731 Passed,
5 SA
1 turned
Here's a normal day:
neptune# cat /var/log/amavisd.o | grep Aug 17 | awk '{print $7}' | sort
| uniq -c
332 BAD
13 BANNED
938 INFECTED
3792 Passed,
On Wed, 20 Aug 2003, Tom Lane wrote:
Nigel J. Andrews [EMAIL PROTECTED] writes:
Yesterday you had almost 1 for 1 valid email. By then I think I was getting
about 3-4 per valid email but since then it's sky rocketed and it looks more
like 30+ per 1 valid message.
FWIW, this is what I see in traffic to an address I've had to abandon
because of spam:
488 Aug 8
433 Aug 9
435 Aug 10
426 Aug 11
504 Aug 12
458 Aug 13
469 Aug 14
390 Aug 15
433 Aug 16
371 Aug 17
520 Aug 18
36473 Aug 19
35808 Aug 20
It's about 3pm local time here, so by midnight the stat for today will
probably be nearly double yesterday's total.
The spam traffic had been around 2K/day at the beginning of the year,
but tapered off to around 500 as you see above. This spike is ten times
the highest I've seen before. If I were actually downloading this crap,
and not rejecting it at the SMTP handshake, my DSL line would be
saturated :-(
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Collation rules and multi-lingual databases
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: My understanding is that the entire set of localization parameters needs to be decided upon when the initdb is done and can never be changed later. Is that right? No, not all of them are frozen. Unfortunately, the one you care about (LC_COLLATE) is. The reason for this is that it determines index ordering for textual columns, and so changing LC_COLLATE on the fly produces instant corrupt indexes :-( Yeah, I really would be perfectly happy to have indexes be in C order and have my queries have to specifically specify the sort order in the ORDER BY clause, knowing they won't use the index. Well, ok, not quite perfectly happy. But in this case there are no indexes on the columns anyways so... A solution for this is on the TODO list, but don't hold your breath ... -- greg ---(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: [GENERAL] Buglist
Andrew Sullivan [EMAIL PROTECTED] writes: I disagree. Triggering a vacuum on a db that is nearly saturating the disk bandwidth has a significant impact. Vivek is right about this. If your system is already very busy, then a vacuum on a largish table is painful. I don't actually think having the process done in real time will help, though -- it seems to me what would be more useful is an even lazier vacuum: something that could be told clean up as cycles are available, but make sure you stay out of the way. Of course, that's easy to say glibly, and mighty hard to do, I expect. I'd love to be able to do that, but I can't think of a good way. Just nice'ing the VACUUM process is likely to be counterproductive because of locking issues (priority inversion). Though if anyone cares to try it on a heavily-loaded system, I'd be interested to hear the results... 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: [GENERAL] Collation rules and multi-lingual databases
I think it would be nice, and I may write it eventually, to have a function called: COLLATION_VALUE( 'string', 'encoding' ) Which could be used like: SELECT field_a, field_b FROM table_a GROUP BY COLLATION_VALUE( field_a ) ORDER BY COLLATION_VALUE( field_b ); or in other creative ways. Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: My understanding is that the entire set of localization parameters needs to be decided upon when the initdb is done and can never be changed later. Is that right? No, not all of them are frozen. Unfortunately, the one you care about (LC_COLLATE) is. The reason for this is that it determines index ordering for textual columns, and so changing LC_COLLATE on the fly produces instant corrupt indexes :-( Yeah, I really would be perfectly happy to have indexes be in C order and have my queries have to specifically specify the sort order in the ORDER BY clause, knowing they won't use the index. Well, ok, not quite perfectly happy. But in this case there are no indexes on the columns anyways so... A solution for this is on the TODO list, but don't hold your breath ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] move to usenet?
-Original Message- From: David W Noon [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 4:20 AM To: [EMAIL PROTECTED] Subject: Re: [GENERAL] move to usenet? I agree that many messages are not formatted according to Usenet conventions, but I normally attribute that to Windows users who know nothing about the Internet. Thus, top-posting and full-quoting are rife all across Usenet. David, What is the point of bottom posting anymore? I thought it had to do with turn-around time so that you could re-read whatever it is you wrote a long time ago. I highly doubt you would know, but is there an easy way to make Outlook 2000 (not Express) bottom post? I've searched groups.google.com for it and found only things like Copy paste your signature, etc. Full-quoting is just a pain when it comes to searching on google, since a reply may only consist of quoted messages and then a read the whole message link. -- David Olbersen iGuard Engineer St. Bernard Software ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Mail server load
On Wed, 20 Aug 2003, Nigel J. Andrews wrote:
On Wed, 20 Aug 2003, Dennis Gearon wrote:
holy S**T!!
Particularly the 'Passed' number. Now I'm not subscribed to all of the lists
but I am on -general, -hackers and a couple of others like -interfaces and yet
I would say that the volume of email I'm seeing from the lists is far lower
than normal _not_ more by a factor of 3-ish.
The # Passed is what amavisd passed through to majordomo2 ... majordomo2
then takes everything that amavisd marked as being spam and trashes those
... and then everything that is from ppl not subscribed to the lists has
to get approved by 'the moderator', which I'm currently going through ...
only 400 more to go, 399 of which are most likely stuff amavisd didn't
catch as spam *sigh*
Oh ... also consider that a *very* large portion of the messages that
Passed are also postmaster messages for messages bounced ... I have a
filter on my mail for that to put it into its own mailbox ... since Aug
18th, there have been 12622 messages delivered to that mailbox ... and
there is also all the subscribe/unsubscribe requests ... all of which
would have been Passed thorugh amavisd ...
The Hermit Hacker wrote:
16:00 ...
neptune# awk '{print $7}' /var/log/amavisd | sort | uniq -c
285 BAD
1807 BANNED
12289 INFECTED
11731 Passed,
5 SA
1 turned
Here's a normal day:
neptune# cat /var/log/amavisd.o | grep Aug 17 | awk '{print $7}' | sort
| uniq -c
332 BAD
13 BANNED
938 INFECTED
3792 Passed,
On Wed, 20 Aug 2003, Tom Lane wrote:
Nigel J. Andrews [EMAIL PROTECTED] writes:
Yesterday you had almost 1 for 1 valid email. By then I think I was getting
about 3-4 per valid email but since then it's sky rocketed and it looks more
like 30+ per 1 valid message.
FWIW, this is what I see in traffic to an address I've had to abandon
because of spam:
488 Aug 8
433 Aug 9
435 Aug 10
426 Aug 11
504 Aug 12
458 Aug 13
469 Aug 14
390 Aug 15
433 Aug 16
371 Aug 17
520 Aug 18
36473 Aug 19
35808 Aug 20
It's about 3pm local time here, so by midnight the stat for today will
probably be nearly double yesterday's total.
The spam traffic had been around 2K/day at the beginning of the year,
but tapered off to around 500 as you see above. This spike is ten times
the highest I've seen before. If I were actually downloading this crap,
and not rejecting it at the SMTP handshake, my DSL line would be
saturated :-(
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: [GENERAL] move to usenet?
I prefer NOT to have to scroll down to the bottom of an email anyway. I think discussion list emails like ours need to be like your medical records, the most important, recent stuff is at the top. I'm not exactly sure what full quoting is. David Olbersen wrote: -Original Message- From: David W Noon [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 4:20 AM To: [EMAIL PROTECTED] Subject: Re: [GENERAL] move to usenet? I agree that many messages are not formatted according to Usenet conventions, but I normally attribute that to Windows users who know nothing about the Internet. Thus, top-posting and full-quoting are rife all across Usenet. David, What is the point of bottom posting anymore? I thought it had to do with turn-around time so that you could re-read whatever it is you wrote a long time ago. I highly doubt you would know, but is there an easy way to make Outlook 2000 (not Express) bottom post? I've searched groups.google.com for it and found only things like Copy paste your signature, etc. Full-quoting is just a pain when it comes to searching on google, since a reply may only consist of quoted messages and then a read the whole message link. -- David Olbersen iGuard Engineer St. Bernard Software ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Buglist
it seems to me what would be more useful is an even lazier vacuum: something that could be told clean up as cycles are available, but make sure you stay out of the way. Of course, that's easy to say glibly, and mighty hard to do, I expect. You mean, like, nice 19 or so ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] move to usenet?
On Wed, 20 Aug 2003 14:44:28 -0700 David Olbersen [EMAIL PROTECTED] wrote: What is the point of bottom posting anymore? I thought it had to do with turn-around time so that you could re-read whatever it is you wrote a long time ago. I highly doubt you would know, but is there an easy way to make Outlook 2000 (not Express) bottom post? there are these powerful tools called mice and arrow keys. i find that they work quite well if you want to bottom post in outlook. cheers, richard -- Richard Welty [EMAIL PROTECTED] Averill Park Networking 518-573-7592 Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Buglist
What about the use of priority inheritance to deal with the issue of priority inversion (a standard methodology within the real-time world)? Then we could have priorities, but still have low priority processes bumped up if a high level one is waiting on them. Regards, Ed On Wed, 20 Aug 2003, Tom Lane wrote: Andrew Sullivan [EMAIL PROTECTED] writes: I disagree. Triggering a vacuum on a db that is nearly saturating the disk bandwidth has a significant impact. Vivek is right about this. If your system is already very busy, then a vacuum on a largish table is painful. I don't actually think having the process done in real time will help, though -- it seems to me what would be more useful is an even lazier vacuum: something that could be told clean up as cycles are available, but make sure you stay out of the way. Of course, that's easy to say glibly, and mighty hard to do, I expect. I'd love to be able to do that, but I can't think of a good way. Just nice'ing the VACUUM process is likely to be counterproductive because of locking issues (priority inversion). Though if anyone cares to try it on a heavily-loaded system, I'd be interested to hear the results... 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
