Re: [GENERAL] median for postgresql 8.3
Hello Brent, well I said "using only the db" because this is a one time need and just wanted to avoid crufting around with calc, and doing 'manual' work. But that seems to be the fastest approach now. When it becomes a frequent need I'll probably end up doing what you suggest, or upgrade to 9.x, for which I've found simple examples due to dynamic limit and offset. thanks and regards, Maarten On Wed, 2010-11-17 at 09:53 +1300, Brent Wood wrote: > Hi Maarten, > > The best way I know of to do this is not to do statistical queries > "in" the DB at all, but use a stats capability embedded in your > database, so they still appear to the user to be done in the db. I > don't see how you can easily get the functionality you want without > user defined functions or addons, While PL/R is a "special addon", and > you created a custom median function to do this, there are very good > instructions to follow to do this. > > I think it might be worth your while if you are looking to retrieve > stats from SQL queries. > > See PL/R, and the median how-to at: > http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 > > HTH, > > Brent Wood > > > > Brent Wood > DBA/GIS consultant > NIWA, Wellington > New Zealand > > >>> maarten 11/17/10 9:15 AM >>> > Hello everyone, > > I was doing some analysis of data to find average delays between some > timestamp values etc... > When the number of rows the average is computed over is small, this > can > give distorted values. So I've obviously added a count column to see > if > the average represents much data. > However, I would also like to add the median value to give me a pretty > good idea of whats happening even for smaller counts. > > I couldn't find such an aggregate function in the manual (version 8.3) > and some websearching didn't uncover it either. > > I was thinking about > SELECT max(id) FROM test ORDER BY id ASC LIMIT > (SELECT count(*)/2 FROM test) > > But two things are wrong with that: > Limit can't use subqueries :( > And ORDER BY gives me the error: 'must be used in aggregate function > etc...) but I can probably work around this by using an ordered > subquery > in stead of the table directly. > > Furthermore, I need the median for a timestamp column, which would > probably complicate things more than when it is a number column. > > I'd like to be able to do this using only the database. (So no > programming functions, special addons etc...) > > Any ideas anyone? > > regards, > Maarten > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > Please consider the environment before printing this email. > > NIWA is the trading name of the National Institute of Water & > Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where is pg_stat_activity (and others) in the documentation?
> You can find out exactly what you're waiting for by correlating this to the > pg_locks table. > Grab the 'procpid' of your waiting query and run: > select * from pg_locks where pid= and granted='f'; > Notice, in the pg_locks table, the logic works slightly different... if > you're 'waiting' (pg_stat_activity), then you haven't been 'granted' > (pg_locks). > Thanks. > As far as documentation: > http://www.postgresql.org/docs/current/static/monitoring-stats.html > I do think that we need per-column documentation of the Standard Statistics Views. Would it be considered for the documentation if i give describing them a try? If so, where do i send it ? Cheers, WBL -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
Re: [GENERAL] strange row count estimates with conditions on multiple column
Tomas Vondra writes: > Yes, I understand why MCV is not used in case of col_b, and I do > understand that the estimate may not be precise. But I'm wondering > what's a better estimate in such cases - 1, 5000, any constant, or > something related to a the histogram? It is doing it off the histogram. The logic is actually quite good I think for cases where the data granularity is small compared to the histogram bucket width. For cases like we have here, the assumption of a continuous distribution fails rather badly --- but it's pretty hard to see how to improve it without inserting a lot of type-specific assumptions. > BTW I think the default estimate used to be 1000, so it was changed in > one of the 8.x releases? Can you point me to the docs? I've even tried > to find that in the sources, but unsuccessfully. It's DEFAULT_RANGE_INEQ_SEL, and AFAIR it hasn't changed in quite a while. But I wouldn't be surprised if the behavior of this example changed when we boosted the default statistics target. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange row count estimates with conditions on multiple column
> BTW I think the default estimate used to be 1000, so it was changed in > one of the 8.x releases? Can you point me to the docs? I've even tried > to find that in the sources, but unsuccessfully. OK, I've found it right after submitting the e-mail. It's defined in selfuncs.h as DEFAULT_RANGE_INEQ_SEL, equal 0.005. Which makes sense, as it's related to the table size. I've mixed that with some other default which used to be 1000 (table size I think). regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange row count estimates with conditions on multiple column
Dne 17.11.2010 05:22, Tom Lane napsal(a): > Tomas Vondra writes: >> Estimate for condition >>... WHERE (col_a BETWEEN 33 AND 33) >> is about 10k rows, which is quite precise. On the other side estimate >> for condition >>... WHERE (col_b BETWEEN 33 AND 33) >> is 1 row, which is very imprecise (actual value is about 5000). > > That's an artifact of your test case. There are exactly 100 distinct > values in col_a, which means that they all fit into the most_common_vals > array (assuming you're using default_statistics_target = 100). So the > planner actually has complete information about the contents of col_a, > modulo some sampling inaccuracy about the precise frequency of each > value. It should be expected to produce pretty good estimates for the > sorts of expressions it can estimate, and it does. In col_b, there are > 200 values, so they can't be represented by most_common_vals, and in > fact ANALYZE notices that none of them are really much more common than > any other. So it throws up its hands and doesn't generate an MCV list > at all, just a histogram. That doesn't provide a lot of foothold for > the range estimator to give an exact estimate for a very narrow range. Yes, I understand why MCV is not used in case of col_b, and I do understand that the estimate may not be precise. But I'm wondering what's a better estimate in such cases - 1, 5000, any constant, or something related to a the histogram? I'd probably vote for a size of the the histogram bucket (or maybe a half of it), as it seems like a good upper bound, and I don't see why any constant (1, 5000, whatever) should be better. But maybe I'm missing something. > If you look closely at what EXPLAIN is saying, that expression expands > as > > Filter: ((col_b >= 33) AND ((col_b)::numeric <= 33.1)) Aha! Haven't noticed that missing '::numeric' - it'd be nice to make this more visible. I bet I'm not the only one who haven't noticed this. > So the things being compared to aren't the same, and it doesn't > recognize this as a range constraint, and you get the dumb > product-of-independent-inequality-probabilities estimate. > > The "exact" estimate you got in the first case is more of a lucky guess > than anything else, too. It does realize that it's got a range > constraint in that case, but it has no stats about the value of the > expression col_b::numeric. So you're just getting a default estimate > that by coincidence matches the correct answer. Aaaargh, it looked like a perfect estimate :-( BTW I think the default estimate used to be 1000, so it was changed in one of the 8.x releases? Can you point me to the docs? I've even tried to find that in the sources, but unsuccessfully. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange row count estimates with conditions on multiple column
Tomas Vondra writes: > Estimate for condition >... WHERE (col_a BETWEEN 33 AND 33) > is about 10k rows, which is quite precise. On the other side estimate > for condition >... WHERE (col_b BETWEEN 33 AND 33) > is 1 row, which is very imprecise (actual value is about 5000). That's an artifact of your test case. There are exactly 100 distinct values in col_a, which means that they all fit into the most_common_vals array (assuming you're using default_statistics_target = 100). So the planner actually has complete information about the contents of col_a, modulo some sampling inaccuracy about the precise frequency of each value. It should be expected to produce pretty good estimates for the sorts of expressions it can estimate, and it does. In col_b, there are 200 values, so they can't be represented by most_common_vals, and in fact ANALYZE notices that none of them are really much more common than any other. So it throws up its hands and doesn't generate an MCV list at all, just a histogram. That doesn't provide a lot of foothold for the range estimator to give an exact estimate for a very narrow range. > I've been playing with this a little bit, and I've noticed another > 'strange' thing. When I rewrite the condition like this >... WHERE (col_b BETWEEN 32.9 AND 33.1) > so that the range is not of zero length, and everything works fine, the > estimate is exactly 5000. But when I increase the lower bound a bit >... WHERE (col_b BETWEEN 33 AND 33.1) > the estimate suddenly jumps to 276667. If you look closely at what EXPLAIN is saying, that expression expands as Filter: ((col_b >= 33) AND ((col_b)::numeric <= 33.1)) So the things being compared to aren't the same, and it doesn't recognize this as a range constraint, and you get the dumb product-of-independent-inequality-probabilities estimate. The "exact" estimate you got in the first case is more of a lucky guess than anything else, too. It does realize that it's got a range constraint in that case, but it has no stats about the value of the expression col_b::numeric. So you're just getting a default estimate that by coincidence matches the correct answer. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange row count estimates with conditions on multiple column
OK, thanks for the explanation. Cases (A), (B) and (D) are clear now. But I'm not sure about (C) ... Dne 17.11.2010 04:03, Tom Lane napsal(a): > Tomas Vondra writes: >> I'm not quite sure why (C) has an estimate of 1. > > It's smart enough to see that each of the clauses is a range constraint > on the variable, so you get fairly tight estimates on the number of > matches ... and then those two small selectivities are multiplied > together. It does not however notice that the range bounds are actually > equal, which would allow it to convert the estimate to a simple equality > estimate, which in many cases (including this one) would be better. > I think we've discussed special-casing that, but it doesn't look like > anybody got around to it yet. It's a little bit tricky to do because > the range estimator doesn't really distinguish < from <= --- which > normally doesn't matter a lot, but it does when you're considering > "x >= 33 and x <= 33" versus "x > 33 and x < 33". OK, but how this leads to the estimate of 1 row? Estimate for condition ... WHERE (col_a BETWEEN 33 AND 33) is about 10k rows, which is quite precise. On the other side estimate for condition ... WHERE (col_b BETWEEN 33 AND 33) is 1 row, which is very imprecise (actual value is about 5000). While the estimate related to col_a is based on most_common_vals/freqs, estimate related to col_b is based on a histogram. So I guess this is somehow related - it seems like it's not just counting the bins (value 33 hits one bin, there are 100 bins => estimate is 1% of rows), it's probably counting what portion of the bin is overlapped by the range. And in this case "33-33 = 0" ... I've been playing with this a little bit, and I've noticed another 'strange' thing. When I rewrite the condition like this ... WHERE (col_b BETWEEN 32.9 AND 33.1) so that the range is not of zero length, and everything works fine, the estimate is exactly 5000. But when I increase the lower bound a bit ... WHERE (col_b BETWEEN 33 AND 33.1) the estimate suddenly jumps to 276667. So narrowing an range actually increases the interval for some reason? regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
Man, the number of misunderstandings in this thread is staggering. Let me try to explain what the proposed feature will and will not do. 1. The system catalog entries for all tables will be wal-logged. So schema (DDL) will survive a crash. There wouldn't be any way to make it not do that, because we can't wal-log only some updates to a particular table, and that includes the catalogs in particular. 2. What's proposed as the new feature is that specific non-system tables can be marked as unlogged, meaning that WAL entries won't be made for changes in those tables' contents (nor their indexes' contents). So we can't guarantee that the contents of such tables will be correct or consistent after a crash. The proposed feature deals with this by forcibly truncating all such tables after a crash, thus ensuring that they're consistent though not populated. So the possible use-cases for such tables are limited to where (a) you can repopulate the tables on demand, or (b) you don't really care about losing data on a crash. 3. There's a lot of wishful thinking here about what constitutes a crash. A backend crash *is* a crash, even if the postmaster keeps going. Data that had been in shared buffers doesn't get written out in such a scenario (and if we tried, it might be corrupt anyway). So unlogged tables would be corrupt and in need of truncation after such an event. Obviously, the same goes for an OS-level crash or power failure. 4. The last bit of discussion on -hackers concerned what to do in the case where the server got shut down cleanly. If it was shut down cleanly, then any data for unlogged tables would have been written out from shared buffers ... but did the data make it to disk? There's no easy way to know that. In the event of an OS crash or power failure shortly after server shutdown, it's possible that the unlogged tables would be corrupt. So Robert's initial proposal includes truncating unlogged tables at any database startup, even if the previous shutdown was clean. Some (including me) are arguing that that is unnecessarily strict; but you do have to realize that you're taking some risk with data validity if it doesn't do that. The bottom line here is that you really can only use the feature for data that you're willing to accept losing on no notice. Allowing the data to persist across clean shutdowns would probably improve usability a bit, but it's not changing that fundamental fact. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange row count estimates with conditions on multiple column
Tomas Vondra writes: > Results from (A) and (B) seem strange to me because AFAIK there are no > multi-column statistics available, and accoring to this thread > http://archives.postgresql.org/pgsql-hackers/2009-03/msg00052.php > the single-column estimates are not multiplied (which would be OK only > in case of statistically independent columns). You're misreading that thread: it's discussing row inequality comparisons, as in your example (D). Row equality comparisons are the same as a bunch of per-column equality comparisons, which is why (A) and (B) behave the same, and for that you *will* get a multiplication of the assumed-independent clause selectivities. > I'm not quite sure why (C) has an estimate of 1. It's smart enough to see that each of the clauses is a range constraint on the variable, so you get fairly tight estimates on the number of matches ... and then those two small selectivities are multiplied together. It does not however notice that the range bounds are actually equal, which would allow it to convert the estimate to a simple equality estimate, which in many cases (including this one) would be better. I think we've discussed special-casing that, but it doesn't look like anybody got around to it yet. It's a little bit tricky to do because the range estimator doesn't really distinguish < from <= --- which normally doesn't matter a lot, but it does when you're considering "x >= 33 and x <= 33" versus "x > 33 and x < 33". > And I do have exactly the same problem with the estimate in (D). Where > the heck did 227232 come from? It doesn't recognize that this case is a range comparison (which was a point made in the thread you cited). So you get a dumb multiplication of the selectivities for col_a >= 33 and col_a <= 33. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On 11/16/2010 05:55 PM, Josh Berkus wrote: If you do wish to have the data tossed out for no good reason every so often, then there ought to be a separate attribute to control that. I'm really having trouble seeing how such behavior would be desirable enough to ever have the server do it for you, on its terms rather than yours. I don't quite follow you. The purpose of unlogged tables is for data which is disposable in the event of downtime; the classic example is the a user_session_status table. In the event of a restart, all user sessions are going to be invalid anyway. As was already mentioned, application logs. Unlogged tables would be perfect for that, provided they don't go *poof* every now and then for no good reason. Nobody's going to be too heart broken if a handful of log records go missing, or get garbled, after a server crash or power outage. Delete 'em all after every restart though, and that's a problem. -Glen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On 11/16/2010 07:55 PM, Josh Berkus wrote: If you do wish to have the data tossed out for no good reason every so often, then there ought to be a separate attribute to control that. I'm really having trouble seeing how such behavior would be desirable enough to ever have the server do it for you, on its terms rather than yours. I don't quite follow you. The purpose of unlogged tables is for data which is disposable in the event of downtime; the classic example is the a user_session_status table. In the event of a restart, all user sessions are going to be invalid anyway. Why? If you dont blow away the sessions table, everything should be fine. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
> I'd vote for backing up the schema of an unlogged table so it's there > on a restore. The schema is always there. What may or may not be there is the data. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
> If you do wish to have the data tossed out for no good reason every so > often, then there ought to be a separate attribute to control that. I'm > really having trouble seeing how such behavior would be desirable enough > to ever have the server do it for you, on its terms rather than yours. I don't quite follow you. The purpose of unlogged tables is for data which is disposable in the event of downtime; the classic example is the a user_session_status table. In the event of a restart, all user sessions are going to be invalid anyway. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On 11/16/2010 05:33 PM, Josh Berkus wrote: With the current patches, the data survives a restart just fine. Per -hackers, that's not guarenteed. Which is fine. If you choose to set a table to nonlogged, that implies that you accept the risk of corrupted data, or that you don't "get it", in which case . It should not however, imply that you want it all thrown out every so often for no good reason. If you do wish to have the data tossed out for no good reason every so often, then there ought to be a separate attribute to control that. I'm really having trouble seeing how such behavior would be desirable enough to ever have the server do it for you, on its terms rather than yours. -Glen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On Tue, Nov 16, 2010 at 8:15 PM, Tom Lane wrote: > Scott Mead writes: > > +1 -- Is there a technical reason to do a TRUNCATE on restart? I'd feel > > better if I could just have unlogged tables that survive unless something > > like a power-outage etc... I'm in the exact same boat here, lots of big > > logging tables that need to survive reboot, but are frustrating when it > > comes to WAL generation. > > Keep in mind that these tables are *not* going to survive any type of > backend crash. Not surviving a crash is fine. IMHO, if we'd lose data in myisam files, I'm happy to lose them on pg nologging tables. I just want it to survive a stop / start operation. The benefits (think of multi-host syslog consolidation with FTS ) on these tables FAR outweigh the off-chance that a crash will cause me some heartache. > Maybe my perceptions are colored because I deal with > Postgres bugs all the time, but I think of backend crashes as pretty > common, certainly much more common than an OS-level crash. I'm afraid > you may be expecting unlogged tables to be significantly more robust > than they really will be. > Bugs? What bugs :) Honestly, I've only had a couple of *Prod* crashes (knocks on wood), but the need to restart occurs every now and then. --Scott > >regards, tom lane >
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On Tue, Nov 16, 2010 at 5:23 PM, Scott Ribe wrote: > On Nov 16, 2010, at 3:46 PM, Josh Berkus wrote: > >> ...and will be truncated (emptied) on database restart. > > I think that's key. Anything that won't survive a database restart, I sure > don't expect to survive backup & restore. I'd vote for backing up the schema of an unlogged table so it's there on a restore. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On 11/16/2010 07:33 PM, Josh Berkus wrote: With the current patches, the data survives a restart just fine. Per -hackers, that's not guarenteed. Ah, I just read the thread on -hackers. And yea, my system had 24 hours to write/flush/etc before I'd restarted it moments ago as a test. I have NOT tested a bunch of writes and then quickly restarting PG. I CAN report that given 24 hours, your data will survive a restart :-) -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
> With the current patches, the data survives a restart just fine. Per -hackers, that's not guarenteed. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On 11/16/2010 05:15 PM, Tom Lane wrote: Keep in mind that these tables are *not* going to survive any type of backend crash. Maybe my perceptions are colored because I deal with Postgres bugs all the time, but I think of backend crashes as pretty common, certainly much more common than an OS-level crash. I'm afraid you may be expecting unlogged tables to be significantly more robust than they really will be. But an individual backend crash != server restart, unless that's changed since 8.1 (yes, I'm still stuck on 8.1 :( )... So if I, for example, kill -9 a backend that's busy updating a nonlogged table, the table could be corrupted, but it wouldn't be truncated (and could cause trouble) for possibly weeks until the postmaster is restarted. Conversely, even if no backend crash occurs whatsoever, all the nonlogged tables would be truncated after an orderly postmaster restart. Just doesn't make sense to me. -Glen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On 11/16/2010 04:46 PM, Josh Berkus wrote: PostgreSQL 9.1 is likely to have, as a feature, the ability to create tables which are "unlogged", meaning that they are not added to the transaction log, and will be truncated (emptied) on database restart. Such tables are intended for highly volatile, but not very valuable, data, such as session statues, application logs, etc. With the current patches, the data survives a restart just fine. I'd like to vote for: safe restart = save data bad crashy restart = drop date -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] strange row count estimates with conditions on multiple column
Hi everyone, I've just noticed a strange behaviour when estimating row counts (I'm running 9.0.1). A small demonstration - let's create table with two columns, and fill it with data so that the columns are not independent: = -- table with two columns create table test_table (col_a int, col_b int); -- fill it with correlated data (0..99, 0..199) insert into test_table select i%100, i%200 from generate_series(1, 100) s(i); -- update statistics analyze test_table; = OK, not let's run a few simple queries, producing exactly the same output but very different plans: A) SELECT * FROM test_table WHERE col_a = 33 AND col_b = 33; Seq Scan on test_table (cost=0.00..19425.00 rows=47 width=8) (actual time=0.025..216.273 rows=5000 loops=1) Filter: ((col_a = 33) AND (col_b = 33)) Total runtime: 221.676 ms B) SELECT * FROM test_table WHERE (col_a, col_b) = (33, 33); plan is exactly the same as (A) C) SELECT * FROM test_table WHERE (col_a BETWEEN 33 AND 33) AND (col_b BETWEEN 33 AND 33); Seq Scan on test_table (cost=0.00..24425.00 rows=1 width=8) (actual time=0.025..282.725 rows=5000 loops=1) Filter: ((col_a >= 33) AND (col_a <= 33) AND (col_b >= 33) AND (col_b <= 33)) Total runtime: 288.127 ms D) SELECT * FROM test_table WHERE (col_a, col_b) BETWEEN (33, 33) AND (33, 33); Seq Scan on test_table (cost=0.00..24425.00 rows=227232 width=8) (actual time=0.022..238.958 rows=5000 loops=1) Filter: ((ROW(col_a, col_b) >= ROW(33, 33)) AND (ROW(col_a, col_b) <= ROW(33, 33))) Total runtime: 244.353 ms = So the estimated number of rows is this A) 47 B) 47 C) 1 D) 227232 Results from (A) and (B) seem strange to me because AFAIK there are no multi-column statistics available, and accoring to this thread http://archives.postgresql.org/pgsql-hackers/2009-03/msg00052.php the single-column estimates are not multiplied (which would be OK only in case of statistically independent columns). Yet the estimates somehow match the product: 1.000.000 * (1/200) * (1/100) = 1.000.000 / 20.000 = 50 I'm not quite sure why (C) has an estimate of 1. The col_a has only 100 distinct values, so it uses most_common_vals/most_common_freqs, and all the values will be there (statistics target is 100) along with frequencies. This gives about 1% selectivity. Column col_b has 200 distinct values, uniformly distributed, so the estimates are based on a histogram - there are 100 bins, the range fits into a single bin, giving 1% selectivity. But no matter what I do, I'm not sure how to combine these two estimates into 0.0001% (1 row out of a million). And I do have exactly the same problem with the estimate in (D). Where the heck did 227232 come from? regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
Scott Mead writes: > +1 -- Is there a technical reason to do a TRUNCATE on restart? I'd feel > better if I could just have unlogged tables that survive unless something > like a power-outage etc... I'm in the exact same boat here, lots of big > logging tables that need to survive reboot, but are frustrating when it > comes to WAL generation. Keep in mind that these tables are *not* going to survive any type of backend crash. Maybe my perceptions are colored because I deal with Postgres bugs all the time, but I think of backend crashes as pretty common, certainly much more common than an OS-level crash. I'm afraid you may be expecting unlogged tables to be significantly more robust than they really will be. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] port warded (iptables) postgres
Make sure that listen_addresses='' or '*' By default, the server only listens on unix sockets. --Scott On Tue, Nov 16, 2010 at 6:41 PM, zhong ming wu wrote: > Hello > > I have successfully used iptables to direct ports for other services. > For example I run apache on 8443 on 127.0.0.1 and > use iptable to direct traffic to public ip on 443. Trying the same > with postgresql does not seem to work properly. > > I was wondering whether anyone has successfully used iptables + > postgres this way > > Thanks in advance > > Mr. Wu > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On Tue, Nov 16, 2010 at 7:21 PM, Glen Parker wrote: > On 11/16/2010 03:24 PM, Karsten Hilbert wrote: > >> PostgreSQL 9.1 is likely to have, as a feature, the ability to create >>> tables which are "unlogged", meaning that they are not added to the >>> transaction log, and will be truncated (emptied) on database restart. >>> Such tables are intended for highly volatile, but not very valuable, >>> data, such as session statues, application logs, etc. >>> >> > I have been following loosely this discussion on HACKERS, but seem to have > missed the part about truncating such tables on server restart. > > I have an immediate use for unlogged tables (application logs), but having > them truncate after even a clean server restart would be a show stopper. I > keep log data for 2 months, and never back it up. Having it disappear after > a system melt down is acceptable, but not after a clean restart. That would > be utterly ridiculous! > +1 -- Is there a technical reason to do a TRUNCATE on restart? I'd feel better if I could just have unlogged tables that survive unless something like a power-outage etc... I'm in the exact same boat here, lots of big logging tables that need to survive reboot, but are frustrating when it comes to WAL generation. > > > As to the topic of the thread, I think pg_dump needs to dump unlogged > tables by default. > > -1 I disagree. I'm fine with having the loaded weapon pointed at my foot. --Scott > > -Glen > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
> This is a link to a read-only spreadsheet for me. You're correct. Darn those Google unreadable links! https://spreadsheets.google.com/viewform?formkey=dDFnT2VKNC1FQ0pQNmJGS2dWMTNYMEE6MQ That should work. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On 11/16/2010 03:24 PM, Karsten Hilbert wrote: PostgreSQL 9.1 is likely to have, as a feature, the ability to create tables which are "unlogged", meaning that they are not added to the transaction log, and will be truncated (emptied) on database restart. Such tables are intended for highly volatile, but not very valuable, data, such as session statues, application logs, etc. I have been following loosely this discussion on HACKERS, but seem to have missed the part about truncating such tables on server restart. I have an immediate use for unlogged tables (application logs), but having them truncate after even a clean server restart would be a show stopper. I keep log data for 2 months, and never back it up. Having it disappear after a system melt down is acceptable, but not after a clean restart. That would be utterly ridiculous! As to the topic of the thread, I think pg_dump needs to dump unlogged tables by default. -Glen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On Nov 16, 2010, at 3:46 PM, Josh Berkus wrote: > ...and will be truncated (emptied) on database restart. I think that's key. Anything that won't survive a database restart, I sure don't expect to survive backup & restore. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
On Wed, Nov 17, 2010 at 1:13 AM, Tom Lane wrote: > =?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: >> Yes, it takes the table names from another query (select table_names >> from other_table), and the fields names from a XML file. Can the query >> which fetch the table names cause a memory problem? Seems weird > > Well, we were already in pretty-weird territory with an out-of-memory > error and no apparent bloat in the palloc map. I'm still hoping for a > test case ... > > regards, tom lane > Yes, I'll provide you one ASAP (my boss is not here right now, and I need credentials). Still crashing, so, I'm going to continue working on it. Many thanks! -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://mobility.grupodeimos.com/ http://gis4free.wordpress.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
=?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: > Yes, it takes the table names from another query (select table_names > from other_table), and the fields names from a XML file. Can the query > which fetch the table names cause a memory problem? Seems weird Well, we were already in pretty-weird territory with an out-of-memory error and no apparent bloat in the palloc map. I'm still hoping for a test case ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
On Wed, Nov 17, 2010 at 1:00 AM, Tom Lane wrote: > =?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: >> Well, the query works if executed in psql client. Seems to be a Python >> problem. I'll execute the rest of the loop queries in the client, just >> in case. > >> But in case is a Python problem, why does it affect the postgres >> process, causing a crash? > > Is the query parameterized when issued from Python? If so it might work > differently. > > regards, tom lane > Yes, it takes the table names from another query (select table_names from other_table), and the fields names from a XML file. Can the query which fetch the table names cause a memory problem? Seems weird -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://mobility.grupodeimos.com/ http://gis4free.wordpress.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
=?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: > Well, the query works if executed in psql client. Seems to be a Python > problem. I'll execute the rest of the loop queries in the client, just > in case. > But in case is a Python problem, why does it affect the postgres > process, causing a crash? Is the query parameterized when issued from Python? If so it might work differently. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
2010/11/17 Jorge Arévalo : > On Wed, Nov 17, 2010 at 12:14 AM, Tom Lane wrote: >> =?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: >>> On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane wrote: Can you boil it down to a self-contained test case? >> >>> Before executing the queries, the app reads a small XML file, to get >>> names and paths, basically. I could simply execute the query in the >>> console client, to isolate it. Would it be enough? >> >> What I'm hoping for is a SQL script I can run to reproduce the error >> from a standing start. Anything you can leave out, or anonymize, >> is fine as long as you still get the error. >> >> BTW, PG 8.4.which? >> >> regards, tom lane >> > > Still getting the same error. I'm going to simply execute the query in > the client. I'd like to provide you an SQL script. Let me ask about > privacy (doesn't depend on me). > > PostgreSQL 8.4.5 > > -- > Jorge Arévalo > Internet & Mobilty Division, DEIMOS > jorge.arev...@deimos-space.com > http://mobility.grupodeimos.com/ > http://gis4free.wordpress.com > Well, the query works if executed in psql client. Seems to be a Python problem. I'll execute the rest of the loop queries in the client, just in case. But in case is a Python problem, why does it affect the postgres process, causing a crash? Thanks again, -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://mobility.grupodeimos.com/ http://gis4free.wordpress.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
On Tue, Nov 16, 2010 at 5:46 PM, Josh Berkus wrote: > > Survey is here: > > https://spreadsheets.google.com/ccc?key=0AoeuP3g2YZsFdDFnT2VKNC1FQ0pQNmJGS2dWMTNYMEE&hl=en&authkey=CISbwuYD This is a link to a read-only spreadsheet for me. Derrick
[GENERAL] port warded (iptables) postgres
Hello I have successfully used iptables to direct ports for other services. For example I run apache on 8443 on 127.0.0.1 and use iptable to direct traffic to public ip on 443. Trying the same with postgresql does not seem to work properly. I was wondering whether anyone has successfully used iptables + postgres this way Thanks in advance Mr. Wu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
> PostgreSQL 9.1 is likely to have, as a feature, the ability to create > tables which are "unlogged", meaning that they are not added to the > transaction log, and will be truncated (emptied) on database restart. > Such tables are intended for highly volatile, but not very valuable, > data, such as session statues, application logs, etc. > > The question is, how would you, as a DBA, expect pg_dump backups to > treat unlogged tables? Backing them up by default has the potential to > both cause performance drag on the unlogged table and make your backups > take longer unless you remember to omit them. Not backing them up by > default has the drawback that if you forget --include-unlogged switch, > and shut the database down, any unlogged data is gone. How would you > *expect* unlogged tables to behave? ALTER DATABASE ... SET PG_DUMP_INCLUDE_UNLOGGED TO ON/OFF with default OFF. That way I can think about it once per database *before* I am in the situation when I regret forgetting. (pg_dump would still support --include-unlogged, defaulting to the database default) Karsten -- Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief! Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
On Wed, Nov 17, 2010 at 12:14 AM, Tom Lane wrote: > =?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: >> On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane wrote: >>> Can you boil it down to a self-contained test case? > >> Before executing the queries, the app reads a small XML file, to get >> names and paths, basically. I could simply execute the query in the >> console client, to isolate it. Would it be enough? > > What I'm hoping for is a SQL script I can run to reproduce the error > from a standing start. Anything you can leave out, or anonymize, > is fine as long as you still get the error. > > BTW, PG 8.4.which? > > regards, tom lane > Still getting the same error. I'm going to simply execute the query in the client. I'd like to provide you an SQL script. Let me ask about privacy (doesn't depend on me). PostgreSQL 8.4.5 -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://mobility.grupodeimos.com/ http://gis4free.wordpress.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
=?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: > On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane wrote: >> Can you boil it down to a self-contained test case? > Before executing the queries, the app reads a small XML file, to get > names and paths, basically. I could simply execute the query in the > console client, to isolate it. Would it be enough? What I'm hoping for is a SQL script I can run to reproduce the error from a standing start. Anything you can leave out, or anonymize, is fine as long as you still get the error. BTW, PG 8.4.which? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] AfterTriggerSaveEvent() called outside of query
Vick Khera writes: > On Tue, Nov 16, 2010 at 12:40 PM, Tom Lane wrote: >> The system will not normally allow cascade actions to be deferred >> ... did you manually munge the pg_trigger entries? If you managed >> to provoke this purely through DDL commands, that would be a bug, >> and I'd like to see how you did it. > Based on advice gleaned from here, earlier this year we did the > following to make the constraints deferrable. There were "warrantee > breaking" warnings with that advice, though :( I guess I broke it. > UPDATE pg_trigger SET tgdeferrable='t' WHERE oid IN (SELECT objid FROM > pg_depend WHERE refobjid IN (SELECT oid FROM pg_constraint WHERE > condeferrable='f' AND contype='f' AND connamespace=2200)); > UPDATE pg_constraint SET condeferrable='t' WHERE condeferrable='f' AND > contype='f' AND connamespace=2200; Yeah, that was overambitious. You should have set just the check triggers, not the cascade triggers, to be deferrable. Try making a deferrable constraint the regular way and have a look at the pg_trigger entries it creates. For example, create table m (f1 int primary key); create table s (f2 int references m ON DELETE CASCADE DEFERRABLE); select tgfoid::regproc, tgrelid::regclass, * from pg_trigger order by oid desc limit 4; On HEAD I get this: tgfoid | tgrelid | tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual +-+-++++---+--+---+---+--+--++-+++ "RI_FKey_noaction_upd" | m | 41310 | RI_ConstraintTrigger_41322 | 1655 | 17 | O | t| 41315 | 41313 | 41318 | t| f | 0 || \x | "RI_FKey_cascade_del" | m | 41310 | RI_ConstraintTrigger_41321 | 1646 | 9 | O | t| 41315 | 41313 | 41318 | f| f | 0 || \x | "RI_FKey_check_upd"| s | 41315 | RI_ConstraintTrigger_41320 | 1645 | 17 | O | t| 41310 | 41313 | 41318 | t| f | 0 || \x | "RI_FKey_check_ins"| s | 41315 | RI_ConstraintTrigger_41319 | 1644 | 5 | O | t| 41310 | 41313 | 41318 | t| f | 0 || \x | (4 rows) Notice the RI_FKey_cascade_del trigger is not deferrable. > Is there a way to better limit that to avoid the FK constraints? I think the code in the backend that does this just has a hard-wired list of which trigger function OIDs to exclude from deferrability. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane wrote: > =?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: >> I'm getting an error message in my Python script: > >> pg.ProgrammingError: ERROR: out of memory >> DETAIL: Failed on request of size 16. > > That's pretty odd because the memory map you provided isn't showing any > particular signs of bloat. I wonder whether either Python or PostGIS > is leaking memory directly (ie, through malloc calls, which wouldn't > show in the memory map). > > Can you boil it down to a self-contained test case? > > regards, tom lane > Before executing the queries, the app reads a small XML file, to get names and paths, basically. I could simply execute the query in the console client, to isolate it. Would it be enough? Just now I'm testing what happens if I add vm.overcommit_memory=2 to sysctl.conf, and execute sysctl -p. If crashes again, I'll isolate the query Thanks again -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://mobility.grupodeimos.com/ http://gis4free.wordpress.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On 17/11/2010, at 6:22 AM, Stephen Cook wrote: > On 11/16/2010 10:51 AM, Magnus Hagander wrote: >> What I'm more interested in is still a word from the people who would >> actually *use* a forum on how this would be better than sites like >> Nabble and Gmane. > > I'm one of those. I'm subscribed to these mailing lists simply because it is > the only way I know of to get the messages in a timely fashion, but I would > greatly prefer a forum-style interface. > > I had never heard of Nabble or Gmane until now, but I just checked them out > and from my quick look it *looks* like a web interface for people who prefer > mailing lists. > > I like having a category breakdown (at the moment I have my email client > splitting the various lists into folders), and I like having little icons > telling me which ones I already read and which are new (my email client has > that also of course). > > So basically, the email lists are usable, but if this forum works out I'll > dump my email subscription in a second and use that. I don't think either is > inherently better than the other, it's just personal preference. I'm not sure if anyone is noticing, or just doesn't want to but all that's becoming of the forum is a viewer for the mailing list with the ability to reply. There are already enough forum sites where they shove anything related to postgres into a single generic forum - I see no reason in recreating that. It's a difficult balancing act to leverage the mailing list community but also use all the features that have made forum software popular in the first place. There will be people who will continue to use mailing list no matter how the forum is presented or functions simply because that's their preferred method, and some people who are used to the different methods of a forum. Everyones input is important, but for the former who are never going to use the forum anyway should have little influence on how it works as forum software. OK, so solutions? Here in Sydney it's a bit after 9am so I've had time to sleep on it and heres what I'm thinking; - Tagging system. A thread created "Performance of C vs Perl" could be tagged (by a registered user or automated system) as [Performance] [C] [Perl] this would have no impact on the mailing list but make forum viewing and searching more reliable, so a search might be like: Search: "benchmark" Tags: [Perl] [PHP] For someone looking to find a higher performance solution or comparison between Perl and PHP. I'd rather not do this though because it will require me to change a lot of code in the phpBB3 codebase and still doesn't use a forum in the way its supposed to be used. The way I see it theres no reason why the forums can't be split the way they are now. It makes no difference to the people who will continue to use the mailing list but makes all the difference to forum users who are choosing this forum over others because it has all the backing of the masters on the mailing list in a much better layout of forums than any other site offers. There is no perfect solution here, you can't please all the masses all the time. But I do believe there is a workable solution somewhere in the middle. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
=?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: > I'm getting an error message in my Python script: > pg.ProgrammingError: ERROR: out of memory > DETAIL: Failed on request of size 16. That's pretty odd because the memory map you provided isn't showing any particular signs of bloat. I wonder whether either Python or PostGIS is leaking memory directly (ie, through malloc calls, which wouldn't show in the memory map). Can you boil it down to a self-contained test case? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!
Folks, Please help us resolve a discussion on -hackers. PostgreSQL 9.1 is likely to have, as a feature, the ability to create tables which are "unlogged", meaning that they are not added to the transaction log, and will be truncated (emptied) on database restart. Such tables are intended for highly volatile, but not very valuable, data, such as session statues, application logs, etc. The question is, how would you, as a DBA, expect pg_dump backups to treat unlogged tables? Backing them up by default has the potential to both cause performance drag on the unlogged table and make your backups take longer unless you remember to omit them. Not backing them up by default has the drawback that if you forget --include-unlogged switch, and shut the database down, any unlogged data is gone. How would you *expect* unlogged tables to behave? Survey is here: https://spreadsheets.google.com/ccc?key=0AoeuP3g2YZsFdDFnT2VKNC1FQ0pQNmJGS2dWMTNYMEE&hl=en&authkey=CISbwuYD -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
2010/11/16 Andy Colson : > On 11/16/2010 3:55 PM, Jorge Arévalo wrote: >> >> Hello, >> >> I'm getting an error message in my Python script: >> >> pg.ProgrammingError: ERROR: out of memory >> DETAIL: Failed on request of size 16. >> >> The query that caused the error was: >> >> UPDATE table1 SET string = (SELECT string FROM table2 WHERE >> ST_Centroid(table1.wkb_geometry)&& table2. the_geom AND >> (ST_Distance(ST_Centroid(table1.wkb_geometry), table2.the_geom)<=0) >> LIMIT 1 OFFSET 0) WHERE string is null; >> >> table1: a table with one geometry column of type "polygon" (square >> polygons). 1277500 rows. >> table2: a table with one geometry column of type "polygon". A few rows >> (12). >> >> Both geometry columns have gist indexes over them. >> >> This query was executed inside a loop, with another 10 similar >> queries, using table1 against different tables (table2, table3, >> table4, etc). The error is not always produced in the same part of the >> loop (sometimes with table2, sometimes with table3...). I attach the >> relevant part of PostgreSQL log. >> >> My enviroment: Ubuntu 10.10, PostgreSQL 8.4, PostGIS 1.5.2, 2GB RAM >> Things I've tried: Increase the SHMMAX kernel variable from 32MB to >> 128MB. Increase SHMALL too. Change postgresql.conf parameters >> shared_buffers to 120MB, work_mem to 32MB, effective_cache_size to >> 128MB. Didn't work. >> >> I think it's a problem with data, because I've tested this other times >> with other data at worked. What could be happening? >> >> Many thanks in advance >> >> >> >> >> > > Probably the OOM, google: postgres oom > there are lots and lots of pages about it. > > -Andy > Hi Andy, Thanks for the tip. The only difference I see is the postgres process is not killed after the error (maybe is restarted). Anyway, I'm going to read about it. Thanks again, -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://mobility.grupodeimos.com/ http://gis4free.wordpress.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function problems
ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function "subjectdisplay" line 7 at SQL statement ** Error ** ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function "subjectdisplay" line 7 at SQL statement At 02:09 PM 16/11/2010, Raymond O'Donnell wrote: On 16/11/2010 21:57, Christine Penner wrote: I have seen other functions (written by others) that do this. So I assume its ok. I'm open to suggestions though. As long as it works. OK, fair enough. What error are you getting? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function problems
On 16/11/2010 21:27, Christine Penner wrote: Select S_TRAINING_TITLE as Display from System Limit 1; Looking again at your function, I think this (and other similar lines) should be select s_training_title into display - i.e. "into" instead of "as". HTH, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function problems
I am returning one value (text). Each of the selects in the function should also return only one value. At 02:06 PM 16/11/2010, you wrote: On 11/16/2010 3:57 PM, Christine Penner wrote: I have seen other functions (written by others) that do this. So I assume its ok. I'm open to suggestions though. As long as it works. At 01:54 PM 16/11/2010, you wrote: On 16/11/2010 21:27, Christine Penner wrote: create or replace function SubjectDisplay(IN subj varchar,IN meetCode numeric,IN meetTrWP integer,OUT Display varchar) returns varchar as I'm not certain about this, but is it a mistake to mix OUT parameters and RETURNS? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie I have used OUT combined with "returns setof record", it makes the result set have the columns specified as OUT params. like: create or replace function TotalCustProd( xsdate timestamp, xedate timestamp, out xcust varchar(100), out xcontract varchar(80), out xjob text, out xtask text, out xparcels integer, out xhours float, out xrate float, out xpclperhour varchar(1), out xamount float ) RETURNS SETOF record AS $$ the result set would be xcust, xcontract, etc... Newer versions of PG have "returns table" support: create or replace function findBadRates(sdate date) returns table(rrowid integer, rlookuprate decimal(5,2)) as $$ Do you want to return one single value, or a set of rows? -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function problems
On 16/11/2010 21:57, Christine Penner wrote: I have seen other functions (written by others) that do this. So I assume its ok. I'm open to suggestions though. As long as it works. OK, fair enough. What error are you getting? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Programming error: Out of Memory
On 11/16/2010 3:55 PM, Jorge Arévalo wrote: Hello, I'm getting an error message in my Python script: pg.ProgrammingError: ERROR: out of memory DETAIL: Failed on request of size 16. The query that caused the error was: UPDATE table1 SET string = (SELECT string FROM table2 WHERE ST_Centroid(table1.wkb_geometry)&& table2. the_geom AND (ST_Distance(ST_Centroid(table1.wkb_geometry), table2.the_geom)<=0) LIMIT 1 OFFSET 0) WHERE string is null; table1: a table with one geometry column of type "polygon" (square polygons). 1277500 rows. table2: a table with one geometry column of type "polygon". A few rows (12). Both geometry columns have gist indexes over them. This query was executed inside a loop, with another 10 similar queries, using table1 against different tables (table2, table3, table4, etc). The error is not always produced in the same part of the loop (sometimes with table2, sometimes with table3...). I attach the relevant part of PostgreSQL log. My enviroment: Ubuntu 10.10, PostgreSQL 8.4, PostGIS 1.5.2, 2GB RAM Things I've tried: Increase the SHMMAX kernel variable from 32MB to 128MB. Increase SHMALL too. Change postgresql.conf parameters shared_buffers to 120MB, work_mem to 32MB, effective_cache_size to 128MB. Didn't work. I think it's a problem with data, because I've tested this other times with other data at worked. What could be happening? Many thanks in advance Probably the OOM, google: postgres oom there are lots and lots of pages about it. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function problems
On 11/16/2010 3:57 PM, Christine Penner wrote: I have seen other functions (written by others) that do this. So I assume its ok. I'm open to suggestions though. As long as it works. At 01:54 PM 16/11/2010, you wrote: On 16/11/2010 21:27, Christine Penner wrote: create or replace function SubjectDisplay(IN subj varchar,IN meetCode numeric,IN meetTrWP integer,OUT Display varchar) returns varchar as I'm not certain about this, but is it a mistake to mix OUT parameters and RETURNS? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie I have used OUT combined with "returns setof record", it makes the result set have the columns specified as OUT params. like: create or replace function TotalCustProd( xsdate timestamp, xedate timestamp, out xcust varchar(100), out xcontract varchar(80), out xjob text, out xtask text, out xparcels integer, out xhours float, out xrate float, out xpclperhour varchar(1), out xamount float ) RETURNS SETOF record AS $$ the result set would be xcust, xcontract, etc... Newer versions of PG have "returns table" support: create or replace function findBadRates(sdate date) returns table(rrowid integer, rlookuprate decimal(5,2)) as $$ Do you want to return one single value, or a set of rows? -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Programming error: Out of Memory
Hello, I'm getting an error message in my Python script: pg.ProgrammingError: ERROR: out of memory DETAIL: Failed on request of size 16. The query that caused the error was: UPDATE table1 SET string = (SELECT string FROM table2 WHERE ST_Centroid(table1.wkb_geometry) && table2. the_geom AND (ST_Distance(ST_Centroid(table1.wkb_geometry), table2.the_geom)<=0) LIMIT 1 OFFSET 0) WHERE string is null; table1: a table with one geometry column of type "polygon" (square polygons). 1277500 rows. table2: a table with one geometry column of type "polygon". A few rows (12). Both geometry columns have gist indexes over them. This query was executed inside a loop, with another 10 similar queries, using table1 against different tables (table2, table3, table4, etc). The error is not always produced in the same part of the loop (sometimes with table2, sometimes with table3...). I attach the relevant part of PostgreSQL log. My enviroment: Ubuntu 10.10, PostgreSQL 8.4, PostGIS 1.5.2, 2GB RAM Things I've tried: Increase the SHMMAX kernel variable from 32MB to 128MB. Increase SHMALL too. Change postgresql.conf parameters shared_buffers to 120MB, work_mem to 32MB, effective_cache_size to 128MB. Didn't work. I think it's a problem with data, because I've tested this other times with other data at worked. What could be happening? Many thanks in advance -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://mobility.grupodeimos.com/ http://gis4free.wordpress.com TopMemoryContext: 49416 total in 6 blocks; 5096 free (6 chunks); 44320 used TopTransactionContext: 8192 total in 1 blocks; 6600 free (0 chunks); 1592 used Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks); 10504 used CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used MessageContext: 32768 total in 3 blocks; 8040 free (0 chunks); 24728 used Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 used smgr relation table: 8192 total in 1 blocks; 760 free (0 chunks); 7432 used TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16 used Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used PortalHeapMemory: 1024 total in 1 blocks; 936 free (0 chunks); 88 used ExecutorState: 57344 total in 3 blocks; 33416 free (12 chunks); 23928 used GiST temporary context: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8176 free (1 chunks); 16 used ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used CacheMemoryContext: 667696 total in 20 blocks; 99040 free (1 chunks); 568656 used pg_toast_18592_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used umts_can_geom_idx: 1024 total in 1 blocks; 136 free (0 chunks); 888 used umts_can_pk: 1024 total in 1 blocks; 304 free (0 chunks); 720 used pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used pg_toast_18599_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used pg_constraint_conrelid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used pg_user_mapping_user_server_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_user_mapping_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used pg_proc_pr
Re: [GENERAL] function problems
I have seen other functions (written by others) that do this. So I assume its ok. I'm open to suggestions though. As long as it works. At 01:54 PM 16/11/2010, you wrote: On 16/11/2010 21:27, Christine Penner wrote: create or replace function SubjectDisplay(IN subj varchar,IN meetCode numeric,IN meetTrWP integer,OUT Display varchar) returns varchar as I'm not certain about this, but is it a mistake to mix OUT parameters and RETURNS? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function problems
On 16/11/2010 21:27, Christine Penner wrote: create or replace function SubjectDisplay(IN subj varchar,IN meetCode numeric,IN meetTrWP integer,OUT Display varchar) returns varchar as I'm not certain about this, but is it a mistake to mix OUT parameters and RETURNS? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting boolean values (how many true, how many false)
If you want to use the boolean approach I would just (as suggested earlier) cast to integer and sum. Like: SELECT sum(good::int) as good, count(good)-sum(good::int) as "not good" and so on I thing the boolean approach seems reasonable if good, nice and fair is three separaty judgements as I understand they are. Regards Nicklas - Original message - > Thank you all, I've ended up with the following. > > But I really wonder if using boolean in my case > (trying to offer players a web from with 3 x 2 radio buttons > to rate each other) is really the best choice - > since it feels a bit awkward (and maybe slow?) > > # create table pref_rep ( > id varchar(32) references pref_users(id) >check (id != > author), author varchar(32) references > pref_users(id), > good boolean, > fair boolean, > nice boolean, > about varchar(256), > last_rated timestamp default current_timestamp > ); > > # select * from pref_rep ; > id | author | good | fair | nice | about | >last_rated > ++--+--+--+---+ > DE7085 | DE7317 | t | t | t | comment 1 | 2010-11-16 > 20:26:04.780827 DE7085 | DE7336 | t | | t | comment 1 | > 2010-11-16 20:26:14.510118 DE7085 | DE7641 | t | f | t | > comment 2 | 2010-11-16 20:26:29.574055 DE7085 | DE7527 | f | f | t > | comment 3 | 2010-11-16 20:26:45.211207 DE7085 | DE7184 | f | f > > | f | comment 3 | 2010-11-16 20:26:56.30616 (5 rows) > > # select > sum(case when good then 1 else 0 end) as good, > sum(case when not good then 1 else 0 end) as "not good", > sum(case when fair then 1 else 0 end) as fair, > sum(case when not fair then 1 else 0 end) as "not fair", > sum(case when nice then 1 else 0 end) as nice, > sum(case when not nice then 1 else 0 end) as "not nice" > from public.pref_rep; > > good | not good | fair | not fair | nice | not nice > --+--+--+--+--+-- > 3 | 2 | 1 | 3 | 4 | > 1 > (1 row) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] function problems
I am trying to write a postgres function and I'm getting errors when the parameter sub is empty (it wont ever be null) and meetCode has a number. This is the function code: create or replace function SubjectDisplay(IN subj varchar,IN meetCode numeric,IN meetTrWP integer,OUT Display varchar) returns varchar as $BODY$ Declare meetType varchar; BEGIN IF subj='' Then if meetCode=0 Then if meetTrWp=0 Then Select S_MEETING_TITLE as Display from System Limit 1; Return; elseif meetTrWp=1 Then Select S_TRAINING_TITLE as Display from System Limit 1; Return; else Select S_WP_TITLE as Display from System Limit 1; Return; end if; else --This is where I run into problems. I get an error when I run it. Query has no destination for result data Select MT_DESCRIPTION as meetType from MEETING_TYPE Where MT_CODE=meetCode; Display := meetType Return; end if; else Display := subj Return; end if; Return; END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 100; Christine Penner -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] AfterTriggerSaveEvent() called outside of query
On Tue, Nov 16, 2010 at 12:40 PM, Tom Lane wrote: > The system will not normally allow cascade actions to be deferred > ... did you manually munge the pg_trigger entries? If you managed > to provoke this purely through DDL commands, that would be a bug, > and I'd like to see how you did it. > Based on advice gleaned from here, earlier this year we did the following to make the constraints deferrable. There were "warrantee breaking" warnings with that advice, though :( I guess I broke it. UPDATE pg_trigger SET tgdeferrable='t' WHERE oid IN (SELECT objid FROM pg_depend WHERE refobjid IN (SELECT oid FROM pg_constraint WHERE condeferrable='f' AND contype='f' AND connamespace=2200)); UPDATE pg_constraint SET condeferrable='t' WHERE condeferrable='f' AND contype='f' AND connamespace=2200; Is there a way to better limit that to avoid the FK constraints? When we do a pg_dump for the schema, the FK constraints do show DEFERRABLE like this: ALTER TABLE ONLY user_event_log ADD CONSTRAINT user_event_log_user_id_fkey FOREIGN KEY (user_id) REFERENCES user_list(user_id) ON DELETE CASCADE DEFERRABLE; The above also is how it looks when I load my current schema into a Pg 9.0 instance and run pg_dump to get it back. I'm guessing that the deferrable here only applies to the existence test, not the cascade, and when I hacked the pg_trigger entries it made the cascade bits also deferrable. It should all be fixed up when we do the migration to 9.0 since I will load the schema freshly from the pg_dump then have slony copy the data. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] median for postgresql 8.3
Hi Maarten, The best way I know of to do this is not to do statistical queries "in" the DB at all, but use a stats capability embedded in your database, so they still appear to the user to be done in the db. I don't see how you can easily get the functionality you want without user defined functions or addons, While PL/R is a "special addon", and you created a custom median function to do this, there are very good instructions to follow to do this. I think it might be worth your while if you are looking to retrieve stats from SQL queries. See PL/R, and the median how-to at: http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> maarten 11/17/10 9:15 AM >>> Hello everyone, I was doing some analysis of data to find average delays between some timestamp values etc... When the number of rows the average is computed over is small, this can give distorted values. So I've obviously added a count column to see if the average represents much data. However, I would also like to add the median value to give me a pretty good idea of whats happening even for smaller counts. I couldn't find such an aggregate function in the manual (version 8.3) and some websearching didn't uncover it either. I was thinking about SELECT max(id) FROM test ORDER BY id ASC LIMIT (SELECT count(*)/2 FROM test) But two things are wrong with that: Limit can't use subqueries :( And ORDER BY gives me the error: 'must be used in aggregate function etc...) but I can probably work around this by using an ordered subquery in stead of the table directly. Furthermore, I need the median for a timestamp column, which would probably complicate things more than when it is a number column. I'd like to be able to do this using only the database. (So no programming functions, special addons etc...) Any ideas anyone? regards, Maarten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] How to select a postgresql table inside Oracle
On 16 November 2010 20:35, Dann Corbit wrote: > Oracle from PostgreSQL: > > http://www.sqlmag.com/article/sql-server/setting-up-an-oracle-linked-server49687.aspx > > > > PostgreSQL from Oracle: > > http://www.postgresql.org/docs/current/static/dblink.html > > > > Or perhaps I do not understand your question. > Uh, no, dblink is for connecting to a PostgreSQL database from within another. You want dbi-link. -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ipv4 data type does not allow to use % assubnet mask delimiter
Peter Eisentraut writes: > On mån, 2010-11-15 at 11:06 +0200, Andrus wrote: >>> Possibly someday the standard will actually standardize the things, >>> and then maybe we can work with them usefully ... >> From http://tools.ietf.org/html/rfc4007#section-11.2 >> so this is clearly standardized. > Send a patch please. Please note where the above-cited reference says The precise format and semantics of additional strings is implementation dependent. The paragraphs following that get even vaguer. In particular, while the standard clearly envisions that non-numeric zone ids should be equivalent to some numeric zone id, the mapping between them is not specified. Hence it's impossible to determine validity, let alone test equality, except by reference to some specific implementation's behavior. I really think this is something we shouldn't touch, at least not till standard practices emerge. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to select a postgresql table inside Oracle
Oracle from PostgreSQL: http://www.sqlmag.com/article/sql-server/setting-up-an-oracle-linked-server49687.aspx PostgreSQL from Oracle: http://www.postgresql.org/docs/current/static/dblink.html Or perhaps I do not understand your question. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Hu, William Sent: Tuesday, November 16, 2010 12:10 PM To: pgsql-general@postgresql.org Subject: [GENERAL] How to select a postgresql table inside Oracle Hi, I have a need to select some data in a Oracle database from a Postgresql table. I know how to do this among Oracle instances, eg. Select * from ta...@another_instance. Are there similar approach without replicate the database tables? Thanks in advance.
Re: [GENERAL] How to select a postgresql table inside Oracle
Hello 2010/11/16 Hu, William : > Hi, I have a need to select some data in a Oracle database from a Postgresql > table. I know how to do this among Oracle instances, eg. Select * from > ta...@another_instance. > > > > Are there similar approach without replicate the database tables? > with little bit programming yes. There is more ways. Probably the most simple way is using a dblink http://archives.postgresql.org/pgsql-hackers/2003-06/msg00361.php other way, you can use a plperlu and DBI interface http://www.pgsql.cz/index.php/PL/Perlu_-_Untrusted_Perl_%28en%29 Regards Pavel Stehule > > > Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to select a postgresql table inside Oracle
On 16 November 2010 20:09, Hu, William wrote: > Hi, I have a need to select some data in a Oracle database from a Postgresql > table. I know how to do this among Oracle instances, eg. Select * from > ta...@another_instance. > > > > Are there similar approach without replicate the database tables? > > > > Thanks in advance. Yes. Look at dbi-link. -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to select a postgresql table inside Oracle
Hi, I have a need to select some data in a Oracle database from a Postgresql table. I know how to do this among Oracle instances, eg. Select * from ta...@another_instance. Are there similar approach without replicate the database tables? Thanks in advance.
Re: [GENERAL] ipv4 data type does not allow to use % assubnet mask delimiter
On mån, 2010-11-15 at 11:06 +0200, Andrus wrote: > > Possibly someday the standard will actually standardize the things, > > and then maybe we can work with them usefully ... > > >From http://tools.ietf.org/html/rfc4007#section-11.2 > > implementation SHOULD >support the following format: > > % > >where > >is a literal IPv6 address, > >is a string identifying the zone of the address, and > > `%' is a delimiter character to distinguish between and > . > > so this is clearly standardized. Send a patch please. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting boolean values (how many true, how many false)
On Tue, Nov 16, 2010 at 11:32 AM, Alexander Farber wrote: > sum(case when good then 1 else 0 end) as good, > sum(case when not good then 1 else 0 end) as "not good", > sum(case when fair then 1 else 0 end) as fair, > sum(case when not fair then 1 else 0 end) as "not fair", > sum(case when nice then 1 else 0 end) as nice, > sum(case when not nice then 1 else 0 end) as "not nice" > from public.pref_rep; Here is one slightly more compact. # select COUNT(NULLIF( good, FALSE)) as good, COUNT(NULLIF( good, TRUE)) as "not good", COUNT(NULLIF( fair, FALSE)) as fair, COUNT(NULLIF( fair, TRUE)) as "not fair", COUNT(NULLIF( nice, FALSE)) as nice, COUNT(NULLIF( nice, TRUE)) as "not nice", from public.pref_rep; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting boolean values (how many true, how many false)
Thank you all, I've ended up with the following. But I really wonder if using boolean in my case (trying to offer players a web from with 3 x 2 radio buttons to rate each other) is really the best choice - since it feels a bit awkward (and maybe slow?) # create table pref_rep ( id varchar(32) references pref_users(id) check (id != author), author varchar(32) references pref_users(id), good boolean, fair boolean, nice boolean, about varchar(256), last_rated timestamp default current_timestamp ); # select * from pref_rep ; id | author | good | fair | nice | about | last_rated ++--+--+--+---+ DE7085 | DE7317 | t| t| t| comment 1 | 2010-11-16 20:26:04.780827 DE7085 | DE7336 | t| | t| comment 1 | 2010-11-16 20:26:14.510118 DE7085 | DE7641 | t| f| t| comment 2 | 2010-11-16 20:26:29.574055 DE7085 | DE7527 | f| f| t| comment 3 | 2010-11-16 20:26:45.211207 DE7085 | DE7184 | f| f| f| comment 3 | 2010-11-16 20:26:56.30616 (5 rows) # select sum(case when good then 1 else 0 end) as good, sum(case when not good then 1 else 0 end) as "not good", sum(case when fair then 1 else 0 end) as fair, sum(case when not fair then 1 else 0 end) as "not fair", sum(case when nice then 1 else 0 end) as nice, sum(case when not nice then 1 else 0 end) as "not nice" from public.pref_rep; good | not good | fair | not fair | nice | not nice --+--+--+--+--+-- 3 |2 |1 |3 |4 |1 (1 row) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On 11/16/2010 10:51 AM, Magnus Hagander wrote: What I'm more interested in is still a word from the people who would actually *use* a forum on how this would be better than sites like Nabble and Gmane. I'm one of those. I'm subscribed to these mailing lists simply because it is the only way I know of to get the messages in a timely fashion, but I would greatly prefer a forum-style interface. I had never heard of Nabble or Gmane until now, but I just checked them out and from my quick look it *looks* like a web interface for people who prefer mailing lists. I like having a category breakdown (at the moment I have my email client splitting the various lists into folders), and I like having little icons telling me which ones I already read and which are new (my email client has that also of course). So basically, the email lists are usable, but if this forum works out I'll dump my email subscription in a second and use that. I don't think either is inherently better than the other, it's just personal preference. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] escape character for 'psql -c ' command
Please include the list when replying. On Tue, Nov 16, 2010 at 11:45 AM, Gary Fu wrote: > Short answer: for simple commands, you can use shell-escaping of a >> double-quoted string. >> >> psql -c "\\copy \"Table\" from 'text file'" >> >> > This works on sh, but I tried this syntax on tcsh, it fails: > > 11:38am 72 g...@modular:~/sybase2postgres> psql -c "\\copy \"Table\" from > 'text file'" > Unmatched ". > > How to make it work on tcsh ? > Consult your shell's documentation. http://www.tcsh.org/tcsh.html/Lexical_structure.html http://www.tcsh.org/tcsh.html/Special_shell_variables.html#backslash_quote Apparently you can use "set backslash_quote=1" and then the original will work. Read the warning above about backslash_quote: Or use the long-version, which is pretty reliable. Derrick
Re: [GENERAL] I want to create a read only database for a specified user.
Thanks Greg, just what I asked for! Chris > From: g...@turnstep.com > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] I want to create a read only database for a specified > user. > Date: Tue, 16 Nov 2010 18:10:33 + > > > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > > > > I now want to have this database or all tables capable of read-only. > > ALTER DATABASE foo SET default_transaction_read_only = true; > > Easy to work around, but may be good enough for your purposes. > > - -- > Greg Sabino Mullane g...@turnstep.com > End Point Corporation http://www.endpoint.com/ > PGP Key: 0x14964AC8 201011161310 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > -BEGIN PGP SIGNATURE- > > iEYEAREDAAYFAkziyQgACgkQvJuQZxSWSsg3ZACgsFsux4OcE2yBXI7mnxHGOcEY > 7rIAn04PqcesABqlSM9aqDa0w7vO03J2 > =4Rr1 > -END PGP SIGNATURE- > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I want to create a read only database for a specified user.
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I now want to have this database or all tables capable of read-only. ALTER DATABASE foo SET default_transaction_read_only = true; Easy to work around, but may be good enough for your purposes. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201011161310 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkziyQgACgkQvJuQZxSWSsg3ZACgsFsux4OcE2yBXI7mnxHGOcEY 7rIAn04PqcesABqlSM9aqDa0w7vO03J2 =4Rr1 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On 17/11/2010 12:01 AM, Michael Gould wrote: Personally I don't care what kind of "forum" interface is used. I just don't like the email because while I like to follow the forum, I spend a lot of time out of the office and I don't like to have to download all of that mail just to keep up. I'd much rather use something that I can access from my phone browser. I do this even with my other company email because I don't want to use up the space on my phone. Which phone, out of interest? And which server backend? Even my ancient Nokia E71 (a Symbian Series 60 phone) is capable of downloading only the most recent "n" messages from a mailbox. Unless you're stuck with some incredibly brain-dead phone and/or IMAP server setup, I find it hard to imagine this being a problem. I'm also unsure how this issue is better addressed by a dedicated forum than by gmane/nabble/etc. I now find myself wondering if the existing mail/web gateways like nabble or gmane offer "skinned" versions under an org's own domain. That'd be an awfully nice option - have a "forum.postgresql.org" that's really just gmane/nabble/etc under the hood. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] escape character for 'psql -c ' command
On 11/15/10 21:10, Derrick Rice wrote:
Short answer: for simple commands, you can use shell-escaping of a
double-quoted string.
psql -c "\\copy \"Table\" from 'text file'"
This works on sh, but I tried this syntax on tcsh, it fails:
11:38am 72 g...@modular:~/sybase2postgres> psql -c "\\copy \"Table\" from
'text file'"
Unmatched ".
How to make it work on tcsh ?
Thanks,
Gary
Note: double \\ is intentional. You need to escape the backslash, which
normally escapes other special characters, like $ and ". Watch out for
other special characters though, which is why I prefer the long answer...
Long answer: A *nix shell will concatenate string literals that are
immediately following each other, even when they aren't the same type
(single quoted or double quoted). So the following:
"abc"'def'hij" (reads: double quote, abc, double quote, single quote,
def, single quote, double quote, hij, double quote)
is "abc" + 'def' + "hij" or "abcdefhij" to the shell
So if you have a single-quoted string, to insert a single quote you (1)
stop the single quoted string (2) start a double-quoted string (3) write
a single quote as the content of the double-quoted string (4) stop the
double-quoted string (5) restart the single quoted string. All without
any spaces (unless they are inside either the double or single quoted
strings as part of your content).
You can obviously insert 2 consecutive single quotes within a single
double-quoted string - or any characters... just be aware you are in
double-quotes now, so you need to escape special characters or go back
to single quotes.
Your example:
psql -c ' Copy "Table" from '"'"'text file'"'"
Derrick
On Mon, Nov 15, 2010 at 6:17 PM, Gary Fu mailto:g...@sigmaspace.com>> wrote:
Hi,
How do I escape both " and ' to be used in the 'psql -c ' command ?
For example, how to make the psql command {\copy "Table" from
'txt_file'} to be used in the psql with -c option (psql -c) ?
The "Table" has to be double quoted here.
Thanks,
Gary
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] I want to create a read only database for a specified user.
Hello, I have a postgres user, that was used to create a database with some 1500+ tables. I now want to have this database or all tables capable of read-only. Is there an easy way of doing this? I am running 8.4.4. on Linux Centos 5 Chris Barnes Recognia Inc.
Re: [GENERAL] AfterTriggerSaveEvent() called outside of query
Vick Khera writes: > The code looks basically like this, for each owner_id ($oid) targeted > for the purge: > BEGIN; > SET LOCAL synchronous_commit TO OFF; > SET CONSTRAINTS ALL DEFERRED; > foreach table (owner_log, ... user_list, invoices, ... ) > DELETE FROM $table WHERE owner_id=$oid > UPDATE owner SET status='terminated' WHERE owner_id=$oid > INSERT INTO admin_log ( record of account being purged ... ); > COMMIT; > This worked just dandy without the two SET commands above I added > yesterday to try to speed things up. (Yes, the constraints are marked > as deferrable...) What happens is now I get the following error: > ERROR: AfterTriggerSaveEvent() called outside of query The code comment associated with that error message says: * Check state. We use normal tests not Asserts because it is possible * to reach here in the wrong state given misconfigured RI triggers, * in particular deferring a cascade action trigger. The system will not normally allow cascade actions to be deferred ... did you manually munge the pg_trigger entries? If you managed to provoke this purely through DDL commands, that would be a bug, and I'd like to see how you did it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] median for postgresql 8.3
Hello see http://okbob.blogspot.com/2009/11/aggregate-function-median-in-postgresql.html Regards Pavel Stehule 2010/11/16 maarten : > Hello everyone, > > I was doing some analysis of data to find average delays between some > timestamp values etc... > When the number of rows the average is computed over is small, this can > give distorted values. So I've obviously added a count column to see if > the average represents much data. > However, I would also like to add the median value to give me a pretty > good idea of whats happening even for smaller counts. > > I couldn't find such an aggregate function in the manual (version 8.3) > and some websearching didn't uncover it either. > > I was thinking about > SELECT max(id) FROM test ORDER BY id ASC LIMIT > (SELECT count(*)/2 FROM test) > > But two things are wrong with that: > Limit can't use subqueries :( > And ORDER BY gives me the error: 'must be used in aggregate function > etc...) but I can probably work around this by using an ordered subquery > in stead of the table directly. > > Furthermore, I need the median for a timestamp column, which would > probably complicate things more than when it is a number column. > > I'd like to be able to do this using only the database. (So no > programming functions, special addons etc...) > > Any ideas anyone? > > regards, > Maarten > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] median for postgresql 8.3
Hello everyone, I was doing some analysis of data to find average delays between some timestamp values etc... When the number of rows the average is computed over is small, this can give distorted values. So I've obviously added a count column to see if the average represents much data. However, I would also like to add the median value to give me a pretty good idea of whats happening even for smaller counts. I couldn't find such an aggregate function in the manual (version 8.3) and some websearching didn't uncover it either. I was thinking about SELECT max(id) FROM test ORDER BY id ASC LIMIT (SELECT count(*)/2 FROM test) But two things are wrong with that: Limit can't use subqueries :( And ORDER BY gives me the error: 'must be used in aggregate function etc...) but I can probably work around this by using an ordered subquery in stead of the table directly. Furthermore, I need the median for a timestamp column, which would probably complicate things more than when it is a number column. I'd like to be able to do this using only the database. (So no programming functions, special addons etc...) Any ideas anyone? regards, Maarten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] AfterTriggerSaveEvent() called outside of query
I'm running a script I have for cleaning out some old data. It iterates over a handful of tables and deletes any data referencing a row in an "owners" table. Basically, it enforces the equivalent of the cascade delete of the data without actually deleting the master row. The code looks basically like this, for each owner_id ($oid) targeted for the purge: BEGIN; SET LOCAL synchronous_commit TO OFF; SET CONSTRAINTS ALL DEFERRED; foreach table (owner_log, ... user_list, invoices, ... ) DELETE FROM $table WHERE owner_id=$oid UPDATE owner SET status='terminated' WHERE owner_id=$oid INSERT INTO admin_log ( record of account being purged ... ); COMMIT; This worked just dandy without the two SET commands above I added yesterday to try to speed things up. (Yes, the constraints are marked as deferrable...) What happens is now I get the following error: ERROR: AfterTriggerSaveEvent() called outside of query CONTEXT: SQL statement "DELETE FROM ONLY "public"."user_event_log" WHERE $1 OPERATOR(pg_catalog.=) "user_id"" at ./purgeoldownerinfo line 77. The context is showing a row being deleted via FK reference to the user_list table user_id primary key field, which had an explicit delete done by the loop. I'm running this via Perl DBD::Pg connecting to a postgres 8.3.11 server on FreeBSD 8.1. The tables in this database are replicated using slony1. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting boolean values (how many true, how many false)
> Date: Tue, 16 Nov 2010 17:23:16 +0100 > Subject: [GENERAL] Counting boolean values (how many true, how many false) > From: alexander.far...@gmail.com > To: pgsql-general@postgresql.org > > Hello, > > if I have this table with 3 boolean columns: > > # \d pref_rate > Table "public.pref_rep" >Column |Type | Modifiers > +-+--- > id | character varying(32) | > author | character varying(32) | > good | boolean | > fair | boolean | > nice | boolean | > about | character varying(256) | > last_rated | timestamp without time zone | default now() > Foreign-key constraints: > "pref_rate_author_fkey" FOREIGN KEY (author) REFERENCES pref_users(id) > "pref_rate_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) > > - how can I please count the number of > true's and false's for each column for a certain id? > (to find that persons rating) > > I'm trying: > >select sum(fair=true), sum(fair=false) from pref_rep; > > but sum() doesn't like a boolean as an argument. > > I've only come up with > > select count(*) from pref_rep where fair=true and id='XXX'; > > but this would mean I have to call this line 6 times? (2 x column). Hi, You can use a 'sum()' with 'case when': select sum(case when fair then 1 else 0 end) as fair, sum(case when good then 1 else 0 end) as good, sum(case when nice then 1 else 0 end) from public.pref_rep;
Re: [GENERAL] Counting boolean values (how many true, how many false)
On 16 November 2010 17:02, Thom Brown wrote: > On 16 November 2010 16:49, maarten wrote: >> Hi, >> >> sum doesn't like booleans, but it does like integers so: >> sum(boolval::int) solves that problem for you. >> >> SELECT id,sum(good::int + fair::int + nice::int) would get you a total >> of the three for each row >> >> good luck, >> Maarten > > Or, if you want a more flexible solution, you could try this: > > CREATE OR REPLACE FUNCTION countif_add(current_count int, expression bool) > RETURNS int AS > $BODY$ > BEGIN > IF expression = true THEN > RETURN current_count + 1; > ELSE > RETURN current_count; > END IF; > END; > $BODY$ > LANGUAGE plpgsql > > CREATE AGGREGATE countif (boolean) > ( > sfunc = countif_add, > stype = int, > initcond = 0 > ); > > Then you can call: > > SELECT countif(fair) AS 'total fair', countif(!fair) > AS 'total unfair' > FROM pref_rep; Correction here... you can't use !boolean... it would need to be... SELECT countif(fair) AS 'total fair', countif(not fair) AS 'total unfair' -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting boolean values (how many true, how many false)
On 16 November 2010 16:49, maarten wrote: > Hi, > > sum doesn't like booleans, but it does like integers so: > sum(boolval::int) solves that problem for you. > > SELECT id,sum(good::int + fair::int + nice::int) would get you a total > of the three for each row > > good luck, > Maarten Or, if you want a more flexible solution, you could try this: CREATE OR REPLACE FUNCTION countif_add(current_count int, expression bool) RETURNS int AS $BODY$ BEGIN IF expression = true THEN RETURN current_count + 1; ELSE RETURN current_count; END IF; END; $BODY$ LANGUAGE plpgsql CREATE AGGREGATE countif (boolean) ( sfunc = countif_add, stype = int, initcond = 0 ); Then you can call: SELECT countif(fair) AS 'total fair', countif(!fair) AS 'total unfair' FROM pref_rep; But it also would let you do stuff like: SELECT countif(my_column > 3) AS 'stuff greater than 3', countif(this_column = that_column) AS 'balanced values' FROM my_table; -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting boolean values (how many true, how many false)
Hi, sum doesn't like booleans, but it does like integers so: sum(boolval::int) solves that problem for you. SELECT id,sum(good::int + fair::int + nice::int) would get you a total of the three for each row good luck, Maarten On Tue, 2010-11-16 at 17:23 +0100, Alexander Farber wrote: > Hello, > > if I have this table with 3 boolean columns: > > # \d pref_rate > Table "public.pref_rep" >Column |Type | Modifiers > +-+--- > id | character varying(32) | > author | character varying(32) | > good | boolean | > fair | boolean | > nice | boolean | > about | character varying(256) | > last_rated | timestamp without time zone | default now() > Foreign-key constraints: > "pref_rate_author_fkey" FOREIGN KEY (author) REFERENCES pref_users(id) > "pref_rate_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) > > - how can I please count the number of > true's and false's for each column for a certain id? > (to find that persons rating) > > I'm trying: > >select sum(fair=true), sum(fair=false) from pref_rep; > > but sum() doesn't like a boolean as an argument. > > I've only come up with > > select count(*) from pref_rep where fair=true and id='XXX'; > > but this would mean I have to call this line 6 times? (2 x column). > > Thank you > Alex > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting boolean values (how many true, how many false)
On Tuesday 16 November 2010 8:23:16 am Alexander Farber wrote: > Hello, > > if I have this table with 3 boolean columns: > > # \d pref_rate > Table "public.pref_rep" >Column |Type | Modifiers > +-+--- > id | character varying(32) | > author | character varying(32) | > good | boolean | > fair | boolean | > nice | boolean | > about | character varying(256) | > last_rated | timestamp without time zone | default now() > Foreign-key constraints: > "pref_rate_author_fkey" FOREIGN KEY (author) REFERENCES pref_users(id) > "pref_rate_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) > > - how can I please count the number of > true's and false's for each column for a certain id? > (to find that persons rating) > > I'm trying: > >select sum(fair=true), sum(fair=false) from pref_rep; > > but sum() doesn't like a boolean as an argument. > > I've only come up with > > select count(*) from pref_rep where fair=true and id='XXX'; > > but this would mean I have to call this line 6 times? (2 x column). > > Thank you > Alex test=> SELECT * from bool_test; ifd | bool_fld -+-- 1 | f 1 | f 1 | f 1 | t 5 | f 98 | t 39 | f 30 | t 39 | t 30 | t 16 | f (11 rows) test=> SELECT bool_fld,case when bool_fld=true then count(bool_fld) else count(bool_fld) end from bool_test where ifd=1 group by bool_fld ; bool_fld | count --+--- f| 3 t| 1 (2 rows) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Counting boolean values (how many true, how many false)
Hello, if I have this table with 3 boolean columns: # \d pref_rate Table "public.pref_rep" Column |Type | Modifiers +-+--- id | character varying(32) | author | character varying(32) | good | boolean | fair | boolean | nice | boolean | about | character varying(256) | last_rated | timestamp without time zone | default now() Foreign-key constraints: "pref_rate_author_fkey" FOREIGN KEY (author) REFERENCES pref_users(id) "pref_rate_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) - how can I please count the number of true's and false's for each column for a certain id? (to find that persons rating) I'm trying: select sum(fair=true), sum(fair=false) from pref_rep; but sum() doesn't like a boolean as an argument. I've only come up with select count(*) from pref_rep where fair=true and id='XXX'; but this would mean I have to call this line 6 times? (2 x column). Thank you Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
Personally I don't care what kind of "forum" interface is used. I just don't like the email because while I like to follow the forum, I spend a lot of time out of the office and I don't like to have to download all of that mail just to keep up. I'd much rather use something that I can access from my phone browser. I do this even with my other company email because I don't want to use up the space on my phone. Best Regards Michael Gould > What I'm more interested in is still a word from the people who would > actually *use* a forum on how this would be better than sites like > Nabble and Gmane. > > -- > Magnus Hagander > Me: http://www.hagander.net/ > Work: http://www.redpill-linpro.com/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On Tue, Nov 16, 2010 at 16:40, Craig Ringer wrote: > On 11/16/2010 08:43 PM, Elliot Chance wrote: >> >> Alrighty, here are the revised plans for beta3: >> >> --- Changes / Fixes >> >> * Without a doubt everyone one wants the forums to match the mailing >> lists. I personally think that this defeats the whole purpose of a forum, >> but i'm here to do what the community thinks is best. The extra forums that >> are not connected with a mailing list will be removed. And some of the >> forums renamed - i'll leave that part up to you to decide amongst >> yourselves. > > On a bit of a side-note, I'm increasingly wishing Stack Overflow had a mail > interface. I take Greg's point that it's an increasingly key place for > people (especially people not otherwise engaged in the community) to seek > information and help. It's a pity that there doesn't seem to be a good way > to connect the StackOverflow postgresql discussion in some way that makes it > more visible on the main community resources. > > I think Greg may have a point when questioning whether adding a forum > interface is overly useful, given that Stack Overflow already exists and > doesn't see all that much attention. OTOH, maybe forums will draw people who > otherwise ask on S.O. to the community, providing a bit of a bridge. It's > worth a try. I think stack overflow is something different, though, with it's rating systems and such. What I'm more interested in is still a word from the people who would actually *use* a forum on how this would be better than sites like Nabble and Gmane. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On Tue, Nov 16, 2010 at 13:43, Elliot Chance wrote: > Alrighty, here are the revised plans for beta3: > > * The second largest issue is that of the email address to assign to emails > created by forum posts. I don't know the internals of how the mailing list > software works, but some thoughts; > - Does the mailing allow a range of email addresses? For example using the > persons clean username like: "forums-chan...@postgresql.com.au" or > "forums-bobsm...@postgresql.com.au" hence we allow the > "forum...@postgresql.com.au" range? This way I can use a catch all address > and filter. It does allow regular expressions in some ways, but I'm not sure it does it in this case - nor that it's the best idea. > - If theres an API to the mailing list that lets me register the persons > real email address without then having to get separate copies in their inbox > and also does not require a URL to be clicked to enable that email? There's no actual API, but it can be done - and has been done before - by screen-scraping the CGI interface. See for example https://github.com/mhagander/hamn/blob/master/listsync.py > * Quotation. Emails have a higher depth of quotation - it is not uncommon for > a single email to contain levels for the past 4 emails and this works well in > emails but not in a forum, this makes posts much longer then they need to be > in most cases and bloats the search with loads of repeated information. > For example phpBB3 by default limits the max quote level to 3 to stop > extraneous information. I'm still thinking of a solution for this. If you can find a way to represent it "the email way" in email and "the forum way" in the forums, that's obviously the best... > * Quotation ownership. You will notice that emails take the form something > like: > Bob Smith wrote: >> 1 + 1 = 2? > Yes. > > The above gets translated only so that the ">" gets converted into the > [quote] tags for the forums display. But quoting in forums adds the ownership > into the quote block, so the above would look like (forums do this > automatically when you quote someone): > | Quote by [Bob Smith]: > | 1 + 1 = 2? > Yes. > > Where [Bob Smith] is a link to the forum user. I originally wrote this into > beta1 but saw there was no consistency and emails came in with "wrote:" > "writes:" or something that just didn't make sense at all so I disabled the > code until I was ready to work on it properly. I will institute it again - > wish me luck... You can't safely rely on that format of the quoting header. "Proper" quoting will always have > (top-posting often doesn't, but if we can't parse that into proper quotes, I don't think it's a problem), but you can't rely on the format of the row(s) before it. OTOH, if it gets wrong here and there it's not a big problem. it just mustn't get wrong too often :) > Nobody has made much comment on the permission of containing the mailing list > information and member email addresses in another container like a forum. I > understand that I can broadcast this information on other mailing lists (www > was mentioned) but if theres no absolute authority figure would it make any > serious difference from what we're already doing? We don't deal in authority figures, we deal in authority teams :-) Just like with the source to the database. There are some people who read -www that don't read -general. That are fairly critical. So I suggest moving the thread over there. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On 11/16/2010 08:43 PM, Elliot Chance wrote: Alrighty, here are the revised plans for beta3: --- Changes / Fixes * Without a doubt everyone one wants the forums to match the mailing lists. I personally think that this defeats the whole purpose of a forum, but i'm here to do what the community thinks is best. The extra forums that are not connected with a mailing list will be removed. And some of the forums renamed - i'll leave that part up to you to decide amongst yourselves. On a bit of a side-note, I'm increasingly wishing Stack Overflow had a mail interface. I take Greg's point that it's an increasingly key place for people (especially people not otherwise engaged in the community) to seek information and help. It's a pity that there doesn't seem to be a good way to connect the StackOverflow postgresql discussion in some way that makes it more visible on the main community resources. I think Greg may have a point when questioning whether adding a forum interface is overly useful, given that Stack Overflow already exists and doesn't see all that much attention. OTOH, maybe forums will draw people who otherwise ask on S.O. to the community, providing a bit of a bridge. It's worth a try. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
Alrighty, here are the revised plans for beta3: --- Changes / Fixes * Without a doubt everyone one wants the forums to match the mailing lists. I personally think that this defeats the whole purpose of a forum, but i'm here to do what the community thinks is best. The extra forums that are not connected with a mailing list will be removed. And some of the forums renamed - i'll leave that part up to you to decide amongst yourselves. * Obviously the above point means no filing script is needed. But for JRoeleveld; I had already through about that - I was thinking of a point keyword system. A title that has 3 words that belong to 3 different forums goes nowhere because a single forum location is no stronger than the rest but at best it's machine guesswork and we all know how different theoretical plans can be from practicality. * The second largest issue is that of the email address to assign to emails created by forum posts. I don't know the internals of how the mailing list software works, but some thoughts; - Does the mailing allow a range of email addresses? For example using the persons clean username like: "forums-chan...@postgresql.com.au" or "forums-bobsm...@postgresql.com.au" hence we allow the "forum...@postgresql.com.au" range? This way I can use a catch all address and filter. - If theres an API to the mailing list that lets me register the persons real email address without then having to get separate copies in their inbox and also does not require a URL to be clicked to enable that email? * Quotation. Emails have a higher depth of quotation - it is not uncommon for a single email to contain levels for the past 4 emails and this works well in emails but not in a forum, this makes posts much longer then they need to be in most cases and bloats the search with loads of repeated information. For example phpBB3 by default limits the max quote level to 3 to stop extraneous information. I'm still thinking of a solution for this. * Quotation ownership. You will notice that emails take the form something like: Bob Smith wrote: > 1 + 1 = 2? Yes. The above gets translated only so that the ">" gets converted into the [quote] tags for the forums display. But quoting in forums adds the ownership into the quote block, so the above would look like (forums do this automatically when you quote someone): | Quote by [Bob Smith]: | 1 + 1 = 2? Yes. Where [Bob Smith] is a link to the forum user. I originally wrote this into beta1 but saw there was no consistency and emails came in with "wrote:" "writes:" or something that just didn't make sense at all so I disabled the code until I was ready to work on it properly. I will institute it again - wish me luck... --- Testing * Don't be alarmed if your some posts don't show up. The parser script is not on a cron for now and I may be deleting or playing with posts while I tweak it, the idea is that things will get messy while its being tested and then wipe the forums completely and use the mbox importer which I wrote a few hours ago to add the real posts. * I added user back dating in beta2. This means that if a mbox from 2 years ago is imported (hypothetically) the user is automatically back dated to the earliest post so it will show then as signed up 2 years ago with X posts per day since then. Anyone want to guess who's had the most posts overall? --- Other notes I didn't quote all the appropriate people above to keep the plan for beta3 clean. beta3 is when concept turns into a clear(er) plan. Lets get all this worked before I start coding. Nobody has made much comment on the permission of containing the mailing list information and member email addresses in another container like a forum. I understand that I can broadcast this information on other mailing lists (www was mentioned) but if theres no absolute authority figure would it make any serious difference from what we're already doing? Cheers, Elliot -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
Hi, Something isn't going right: http://forums.postgresql.com.au/viewtopic.php?f=30&t=96 Contains 2 issues. I participated in both, but my reply to the second issue is not included in the forum. If you need me to show you the emails or whatever, just ask. btw: great stuff! :) Cheers, WBL On Tue, Nov 16, 2010 at 11:29 AM, Magnus Hagander wrote: > On Tue, Nov 16, 2010 at 10:59, J. Roeleveld wrote: > > On Tuesday 16 November 2010 10:30:05 Magnus Hagander wrote: > >> On Tue, Nov 16, 2010 at 03:45, Elliot Chance > wrote: > >> > I have made some major changes "beta2" > >> > >> > >> > >> > Extra thoughts; > >> > > >> > It would not be practical for the forums to create a dummy mailing > list > >> > email address per person or forum, > >> > >> Why? It doesn't have to be actual mailboxes, but it needs to be a > >> deliverable email address. > >> > >> The other option is, of course, to send the email using the email > >> address the forum user uses to register with the forum. That might > >> cause issues with some antispam solutions, but as long as it's done > >> right, I think that would work. > > > > If this is done in cooperation with the list admins, they could whitelist > the > > forum-server for this? > > There's actually no way to whitelist a server in mj2. We've tried this > for other things, and it just doesn't work. > > > >> > however theres needs to be a robust way to make sure the > topics/threads > >> > and posts match up with the threads and emails in the mailing list. > The > >> > problem I see is that replies to the forum are not technically replies > >> > via email and so they will not carry the unique "in-reply-to" > >> > identifier. > >> > >> The email generated is a reply via email, and carries a message id. It > >> should be perfectly possible to chain those together using > >> in-reply-to, as long as all posts are mirrored between the two media. > > > > I think the only way to correctly mirror these 2 is to use one as the > master > > and have the other populated by the master. > > As the mailing list already exists and is used a lot already, I would > think > > the following would work: > > - user posts on forum, email is generated. When email comes from list, it > is > > entered into the forum > > Yes, that's pretty much how it would have to work. > > > -- > Magnus Hagander > Me: http://www.hagander.net/ > Work: http://www.redpill-linpro.com/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
Re: [GENERAL] Postgres forums ... take 2
On Tue, Nov 16, 2010 at 10:59, J. Roeleveld wrote: > On Tuesday 16 November 2010 10:30:05 Magnus Hagander wrote: >> On Tue, Nov 16, 2010 at 03:45, Elliot Chance wrote: >> > I have made some major changes "beta2" >> >> >> >> > Extra thoughts; >> > >> > It would not be practical for the forums to create a dummy mailing list >> > email address per person or forum, >> >> Why? It doesn't have to be actual mailboxes, but it needs to be a >> deliverable email address. >> >> The other option is, of course, to send the email using the email >> address the forum user uses to register with the forum. That might >> cause issues with some antispam solutions, but as long as it's done >> right, I think that would work. > > If this is done in cooperation with the list admins, they could whitelist the > forum-server for this? There's actually no way to whitelist a server in mj2. We've tried this for other things, and it just doesn't work. >> > however theres needs to be a robust way to make sure the topics/threads >> > and posts match up with the threads and emails in the mailing list. The >> > problem I see is that replies to the forum are not technically replies >> > via email and so they will not carry the unique "in-reply-to" >> > identifier. >> >> The email generated is a reply via email, and carries a message id. It >> should be perfectly possible to chain those together using >> in-reply-to, as long as all posts are mirrored between the two media. > > I think the only way to correctly mirror these 2 is to use one as the master > and have the other populated by the master. > As the mailing list already exists and is used a lot already, I would think > the following would work: > - user posts on forum, email is generated. When email comes from list, it is > entered into the forum Yes, that's pretty much how it would have to work. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On Tue, Nov 16, 2010 at 11:11, Thom Brown wrote: > On 16 November 2010 09:30, Magnus Hagander wrote: >> >> On Tue, Nov 16, 2010 at 03:45, Elliot Chance wrote: >> > I have made some major changes "beta2" >> >> >> > Extra thoughts; >> > >> > It would not be practical for the forums to create a dummy mailing list >> > email address per person or forum, >> >> Why? It doesn't have to be actual mailboxes, but it needs to be a >> deliverable email address. >> >> The other option is, of course, to send the email using the email >> address the forum user uses to register with the forum. That might >> cause issues with some antispam solutions, but as long as it's done >> right, I think that would work. > > Won't the vast majority of those require moderation if users aren't > signed up to a mailing list? I mean we could have the "reply-to" It would. The best way around that would be to auto-subscribe them and set them to NOMAIL, as has been previously suggested. >> Personally, I find the lack of this a show-stopper issue. We do *not* >> want what's basically going to be anonymous posts on the lists. > > Definitely. > >> IMHO, there needs to be a one-to-one mapping, and nothing else. > > Agreed. The justification for a forum, from my perspective, is > another method of interacting with the mailing list to open it up to a > wider audience. I don't like the idea of additional forums which > don't match a mailing list as it would not only create community > fragmentation, but most of the people with the answers won't be > reading the forums. Exactly my point. >> I'm not a big user of web forums (I use them when I have to, but it's >> certainly not a medium I consider efficient so I don't choose it), so >> here's a question that may be obvious, but still required: quoting. >> Can the forum software be set up to always quote responses properly? >> And somehow discourage top-posting in said responses? We absolutely do >> *not* want a forum to start feeding non-quoted responses back to the >> mailinglists, and non-quoted responses is unfortunately pretty common >> on most forums where I usually end up - but again, that is hopefully >> just a setting :-) > > Have you seen Elliot's prototype? From what I've seen, quoting comes > through fine. It would just have to work correctly the other way, in > that it sends plain text emails with correct levels of chevrons. Yes, I'm only talking about the forum->mail direction here. > A test mailing list will no doubt need to be set up for testing such > functionality. But before too much work commences on this, will this > have the backing of the team? I personally think, even though I don't > want a forum myself, others will, and it would reduce barriers to the > community. Obviously it will need to work seamlessly too, so that it > doesn't cause any issues on the mailing lists themselves. Personally, I would Ok with doing this, *IF* all the issues raised are dealt with. Particularly the posting side *must* be fixed. Without that, it definitely won't have the backing of the sysadmin/infrastructure team. With it, probably, but I can't speak for others than myself. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On Tuesday 16 November 2010 10:30:05 Magnus Hagander wrote: > On Tue, Nov 16, 2010 at 03:45, Elliot Chance wrote: > > I have made some major changes "beta2" > > > > > Extra thoughts; > > > > It would not be practical for the forums to create a dummy mailing list > > email address per person or forum, > > Why? It doesn't have to be actual mailboxes, but it needs to be a > deliverable email address. > > The other option is, of course, to send the email using the email > address the forum user uses to register with the forum. That might > cause issues with some antispam solutions, but as long as it's done > right, I think that would work. If this is done in cooperation with the list admins, they could whitelist the forum-server for this? > Personally, I find the lack of this a show-stopper issue. We do *not* > want what's basically going to be anonymous posts on the lists. I agree, as it's an easy way for spammers to start spamming the whole list. Has anyone thought about what would happen if someone does an unsubscribe for the forum email? :) > > however theres needs to be a robust way to make sure the topics/threads > > and posts match up with the threads and emails in the mailing list. The > > problem I see is that replies to the forum are not technically replies > > via email and so they will not carry the unique "in-reply-to" > > identifier. > > The email generated is a reply via email, and carries a message id. It > should be perfectly possible to chain those together using > in-reply-to, as long as all posts are mirrored between the two media. I think the only way to correctly mirror these 2 is to use one as the master and have the other populated by the master. As the mailing list already exists and is used a lot already, I would think the following would work: - user posts on forum, email is generated. When email comes from list, it is entered into the forum I believe this is how gmane works. > > There needs to be more forum mapping from specific forums to mailing > > lists, for example "Languages > Perl" to the closest mailing list which > > might be pgsql-general. However once the topic is created in a forum all > > the responses will stay in that forum, so even though people reply on > > the pgsql-general mailing list the replies appear under Languages > > > Perl. > > IMHO, there needs to be a one-to-one mapping, and nothing else. Agreed > > The infrastructure exists to create as many forum mappings as needed, and > > I could add post processing. So for example an email to pgsql-general > > with the title "perl won't connect" will recognise "perl" and move it to > > the Languages > Perl. > > That sounds like a really bad idea - it's going to cause nothing but > confusion. How will it be done if a subject contains more then one "keywords"? Eg. "Porting C-code to Perl causes performance issue" I see "C", "Perl" and "Performance" Where will it then be moved to? > I'm not a big user of web forums (I use them when I have to, but it's > certainly not a medium I consider efficient so I don't choose it), so > here's a question that may be obvious, but still required: quoting. > Can the forum software be set up to always quote responses properly? > And somehow discourage top-posting in said responses? We absolutely do > *not* want a forum to start feeding non-quoted responses back to the > mailinglists, and non-quoted responses is unfortunately pretty common > on most forums where I usually end up - but again, that is hopefully > just a setting :-) I doubt that, I am also on a mailing list where a similar link is already set up. I occasionally get emails there without quotes. It's ok if the thread isn't too old. But if someone replies to a thread that's more then a year old, it doesn't get linked up. (I move older posts into subfolders to keep my mail client responsive) If I feel like it, I can then click on the link to the forum to read the actual thread. At least that way it is possible to make sense of it. -- Joost -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On 16 November 2010 09:30, Magnus Hagander wrote: > > On Tue, Nov 16, 2010 at 03:45, Elliot Chance wrote: > > I have made some major changes "beta2" > > > > Extra thoughts; > > > > It would not be practical for the forums to create a dummy mailing list > > email address per person or forum, > > Why? It doesn't have to be actual mailboxes, but it needs to be a > deliverable email address. > > The other option is, of course, to send the email using the email > address the forum user uses to register with the forum. That might > cause issues with some antispam solutions, but as long as it's done > right, I think that would work. Won't the vast majority of those require moderation if users aren't signed up to a mailing list? I mean we could have the "reply-to" header value contain the forum's email address, but the "from" address would be rejected by the list surely? If that could somehow be made to work, that would be ideal though. > Personally, I find the lack of this a show-stopper issue. We do *not* > want what's basically going to be anonymous posts on the lists. Definitely. > IMHO, there needs to be a one-to-one mapping, and nothing else. Agreed. The justification for a forum, from my perspective, is another method of interacting with the mailing list to open it up to a wider audience. I don't like the idea of additional forums which don't match a mailing list as it would not only create community fragmentation, but most of the people with the answers won't be reading the forums. > > The infrastructure exists to create as many forum mappings as needed, and I > > could add post processing. So for example an email to pgsql-general with > > the title "perl won't connect" will recognise "perl" and move it to the > > Languages > Perl. > > That sounds like a really bad idea - it's going to cause nothing but > confusion. Yes, we wouldn't want any clever logic to automagically file the posts into certain categories. We don't have that on the mailing lists, so the forum also shouldn't have it. > I'm not a big user of web forums (I use them when I have to, but it's > certainly not a medium I consider efficient so I don't choose it), so > here's a question that may be obvious, but still required: quoting. > Can the forum software be set up to always quote responses properly? > And somehow discourage top-posting in said responses? We absolutely do > *not* want a forum to start feeding non-quoted responses back to the > mailinglists, and non-quoted responses is unfortunately pretty common > on most forums where I usually end up - but again, that is hopefully > just a setting :-) Have you seen Elliot's prototype? From what I've seen, quoting comes through fine. It would just have to work correctly the other way, in that it sends plain text emails with correct levels of chevrons. A test mailing list will no doubt need to be set up for testing such functionality. But before too much work commences on this, will this have the backing of the team? I personally think, even though I don't want a forum myself, others will, and it would reduce barriers to the community. Obviously it will need to work seamlessly too, so that it doesn't cause any issues on the mailing lists themselves. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On Tue, Nov 16, 2010 at 03:45, Elliot Chance wrote: > I have made some major changes "beta2" > Extra thoughts; > > It would not be practical for the forums to create a dummy mailing list email > address per person or forum, Why? It doesn't have to be actual mailboxes, but it needs to be a deliverable email address. The other option is, of course, to send the email using the email address the forum user uses to register with the forum. That might cause issues with some antispam solutions, but as long as it's done right, I think that would work. Personally, I find the lack of this a show-stopper issue. We do *not* want what's basically going to be anonymous posts on the lists. > however theres needs to be a robust way to make sure the topics/threads and > posts match up with the threads and emails in the mailing list. The problem I > see is that replies to the forum are not technically replies via email and so > they will not carry the unique "in-reply-to" identifier. The email generated is a reply via email, and carries a message id. It should be perfectly possible to chain those together using in-reply-to, as long as all posts are mirrored between the two media. > There needs to be more forum mapping from specific forums to mailing lists, > for example "Languages > Perl" to the closest mailing list which might be > pgsql-general. However once the topic is created in a forum all the responses > will stay in that forum, so even though people reply on the pgsql-general > mailing list the replies appear under Languages > Perl. IMHO, there needs to be a one-to-one mapping, and nothing else. > The infrastructure exists to create as many forum mappings as needed, and I > could add post processing. So for example an email to pgsql-general with the > title "perl won't connect" will recognise "perl" and move it to the Languages > > Perl. That sounds like a really bad idea - it's going to cause nothing but confusion. I'm not a big user of web forums (I use them when I have to, but it's certainly not a medium I consider efficient so I don't choose it), so here's a question that may be obvious, but still required: quoting. Can the forum software be set up to always quote responses properly? And somehow discourage top-posting in said responses? We absolutely do *not* want a forum to start feeding non-quoted responses back to the mailinglists, and non-quoted responses is unfortunately pretty common on most forums where I usually end up - but again, that is hopefully just a setting :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres forums ... take 2
On Tue, Nov 16, 2010 at 07:00, Elliot Chance wrote: > On 16/11/2010, at 2:01 PM, Craig Ringer wrote: >> Have you been in touch with the Pg list admins to make sure they're cool >> with this? > > At this point its a good idea, who is the best person(s) to contact? I want > to make sure anything I do does not in any way reflect badly on the community > or seem like i'm doing anything dishonest. Some of us are already reading this thread. But the correct forum to use is the pgsql-www mailinglist. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
