Re: [GENERAL] postgres-8.4SS, pg_dump from macosx-10.6 has ssl handshake error 26% in
Tom Lane wrote: raf r...@raf.org writes: i'm having a little openssl problem with pg_dump over a wireless lan with postgres-8.4SS (on linux) from enterprisedb and a macosx-10.6 client. when i run pg_dump from a wired linux client it's always fine but since i switched from a macosx-10.4 laptop to a macosx-10.6 laptop, every time i run pg_dump from the laptop over the wireless lan, it's fine for a few minutes and then, 26% of the way in, it stalls and never completes. What this sounds like is you've got an openssl library with deliberately broken renegotiate behavior. Google for CVE-2009-3555 to learn something about why that might be. Assuming that 8.4SS actually means 8.4.3 or later, you can work around this by setting ssl_renegotiation_limit to zero in the server. But it'd be better to get a copy of libssl with an actual fix, rather than a braindead kluge, for the CVE problem. the latest enterprisedb standard server is only 8.4.1 (New! 13-Oct-09) :-) I'm not real sure which of the two ssl libraries you've got is at fault (they might both be :-() both sides are using 0.9.7 so they're both vulnerable. i can probably replace the server's copy of libssl with a more recent version. the client end is a bit trickier. it's using a system libssl but both 0.9.7 and 0.9.8 are present in the same directory and it's using 0.9.7. no, removing 0.9.7 or overwriting it with 0.9.8 doesn't work. i didn't think it would. :) i think i'll have to switch from enterprisedb's standard server to the core distribution to get the latest version which hopefully uses the more recent libssl. many thanks. regards, tom lane cheers, raf -- 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] Comparison of Oracle and PostgreSQL full text search
Howard Rogers, 28.07.2010 03:58: Thanks to some very helpful input here in earlier threads, I was finally able to pull together a working prototype Full Text Search 'engine' on PostgreSQL and compare it directly to the way the production Oracle Text works. The good news is that PostgreSQL is bloody fast! The slightly iffy news is that the boss is now moaning about possible training costs! Why is it that managers always see short term savings but fail to see longterm expenses? For what it's worth, I wrote up the performance comparison here: http://diznix.com/dizwell/archives/153 Maybe it will be of use to anyone else wondering if it's possible to do full text search and save a couple hundred thousand dollars whilst you're at it! Very interesting reading. Would you mind sharing the tables, index structures and search queries that you used (both for Oracle and Postgres)? Regards Thomas -- 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] Comparison of Oracle and PostgreSQL full text search
2010/7/28 Thomas Kellerer spam_ea...@gmx.net: Why is it that managers always see short term savings but fail to see longterm expenses? It's all about CAPEX vs OPEX, baby! Besides jokes, it's actually myopia. Because they ALREADY spent money for training they don't see the need for extra training (and costs), as if people would remain there forever and knowledge is a definitive thing! THe point would be to put costs in a time perspective, that is, how much would it cost in, say, 5 years, with PG and the same for Oracle. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- 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] Comparison of Oracle and PostgreSQL full text search
Howard, that was a great read! I especially like your sentence Considering that any search containing more than a half-dozen search terms is more like an essay than a realistic search; and considering that returning half a million matches is more a data dump than a sensible search facility, which really pulls some benchmark-perspectives back into real live. Thank you, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - Using PostgreSQL is mostly about sleeping well at night. -- 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-8.4SS, pg_dump from macosx-10.6 has ssl handshake error 26% in
the latest enterprisedb standard server is only 8.4.1 (New! 13-Oct-09) :-) By using the StackBuilder Plus application, you can upgrade your server to 8.4.4. -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company.
Re: [GENERAL] Comparison of Oracle and PostgreSQL full text search
On Tue, Jul 27, 2010 at 9:58 PM, Howard Rogers h...@diznix.com wrote: Thanks to some very helpful input here in earlier threads, I was finally able to pull together a working prototype Full Text Search 'engine' on PostgreSQL and compare it directly to the way the production Oracle Text works. The good news is that PostgreSQL is bloody fast! The slightly iffy news is that the boss is now moaning about possible training costs! For what it's worth, I wrote up the performance comparison here: http://diznix.com/dizwell/archives/153 I always thought there is a clause in their user agreement preventing the users from publishing benchmarks like that. I must be mistaken. -- 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] Comparison of Oracle and PostgreSQL full text search
zhong ming wu wrote: On Tue, Jul 27, 2010 at 9:58 PM, Howard Rogers h...@diznix.com wrote: For what it's worth, I wrote up the performance comparison here: http://diznix.com/dizwell/archives/153 I always thought there is a clause in their user agreement preventing the users from publishing benchmarks like that. I must be mistaken. Perhaps not as I remember such issues a few years when the company I worked at profiled postgres against Oracle. Oracle doesn't want poorly-tuned systems being used as benchmarks. Or so they claim. Our tests -- very much oriented at postGIS found Oracle to be between 5 and 15% _faster_ depending on the specifics of the task. We decided to go with postgres given the price difference (several hundred thousand dollars for Oracle in the configuration we needed vs. zip for postgres -- we already had trained postgres DBAs). YMMV. Greg Williamson -- 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] psql problem
Tom Lane wrote: The openssl installation I'm testing with is openssl-1.0.0a-1.fc13.x86_64 I don't know offhand what RHEL/CentOS 5.x are using but it's probably quite a lot older. Here's a CentOS 5.5 install that's kept up to date: $ rpm -qi openssl Name: openssl Relocations: (not relocatable) Version : 0.9.8eVendor: CentOS Release : 12.el5_4.6Build Date: Fri 26 Mar 2010 04:55:17 PM EDT Install Date: Fri 09 Apr 2010 01:23:38 AM EDT Build Host: builder10.centos.org Group : System Environment/Libraries Source RPM: openssl-0.9.8e-12.el5_4.6.src.rpm Size: 3610575 License: BSDish Signature : DSA/SHA1, Sat 27 Mar 2010 01:29:08 PM EDT, Key ID a8a447dce8562897 Looks like this package set: https://rhn.redhat.com/errata/RHSA-2010-0162.html is the current one still, with backports for the CVEs. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Comparison of Oracle and PostgreSQL full text search
zhong ming wu wrote: I always thought there is a clause in their user agreement preventing the users from publishing benchmarks like that. I must be mistaken. No you're correct. Currently, to download the current Oracle 11.2g, one must agree to: http://www.oracle.com/technetwork/licenses/standard-license-152015.html which contains: quote [...] You may not: [...] - disclose results of any program benchmark tests without our prior consent. [...] /quote Not having such frustrating license terms is also what makes PostgreSQL a nicer alternative! Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] First inserts are faster than second ones
Hi there. I have a strange behaviour about INSERT execution. I have very dummy PL/pgSQL function, which SELECT data from table; if data exists - skip, if not - add one. It's about 3'000'000 execution per program life. Everything works. But there are one weird thing. After DROP TABLE and then CREATE TABLE (CREATE INDEX), it's must faster to work for this function. After initial INSERTs the speed of execution about 5 minutes. But, transaction may fail. If it fail, I can change data and run this function from the beginning. The second pass on the empty tables are much slower, about 1,5 hours. So, I decide to DROP TABLE and CREATE it again and gain much speedup. Is it normal? Thanks for patience. -- 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] psql problem
On 07/27/2010 10:20 PM, Tom Lane wrote: Gary Fugary...@sigmaspace.com writes: Below is an example that I created. It works okay, but when I add any character in the comment or in the table definition, it fails (hangs). I checked the server process (with ps command), and I can see that connection is 'idle'. By the way, the size 1484 may not mean anything, since I tried another case (with different comment and table) and the problem still happens but the size 1484 is not the break point. I think this may be CentOS(64 bits)/ssh related, since I don't have the problem with CentOS(32 bits) and we have the same application to install the tables with the same command on mandriva. FWIW, I cannot reproduce this problem using 8.4.latest on Fedora 13 64bit. So it might indeed be something specific to the openssl version you're using. I assume you tested that the problem goes away if you use a non-SSL connection? The openssl installation I'm testing with is openssl-1.0.0a-1.fc13.x86_64 I don't know offhand what RHEL/CentOS 5.x are using but it's probably quite a lot older. regards, tom lane Thanks for your response. Our SA said that there was a network configuration set up incorrectly. After the 'Jumbo Frames' was enabled on the network between the 10G and 1G hosts, the problem was gone. Sorry, I don't know the detail about the network configuration. 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
Re: [GENERAL] Histogram generator
On Tue, Jul 27, 2010 at 09:25:05PM -0400, Patrick May wrote:
On Jul 27, 2010, at 9:21 PM, Steve Atkins wrote:
select date_trunc('hour', foo) + interval '30 minutes' *
floor(extract(minute from foo) / 30) as start, event, count(*) from bar
group by 1, 2 order by 1 asc;
Thanks! It looks like interval is what I need to play with.
Another useful tool to use is the classic unix seconds since epoch.
You could turn the key expression from above into:
timestamp 'epoch' + interval '1 second' * floor(date_part('epoch',foo) /
(30*60)) * (30*60)
I'd probably go with Steve's version here, it's a bit more obvious
what's going on. Also note, that if you don't really care about what
the specific groups are, just that you have a set of evenly divided
30minute periods you don't need to convert back to a date, so could just
use:
floor(date_part('epoch',foo) / (30*60))
One final note, if you're dealing with lots of data and the above
expression is slow, you could delay converting back to a date until
after the grouping, i.e:
SELECT timestamp 'epoch' + interval '1 second' * floor(date_part('epoch',foo)
/ (30*60)) * (30*60) AS t, COUNT(*)
FROM data
GROUP BY floor(date_part('epoch',foo) / (30*60));
This will save PG from converting back to a date for every row when it's
going to chuck most of them away anyway.
Hope that gives you some more ideas!
--
Sam http://samason.me.uk/
--
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] Which CMS/Ecommerce/Shopping cart ?
Except for Drupal's partial support, I cant find any which has a sizeable deployment and community size behind it. Spree is a new RoR based system, that would obviously work with PG, but doesnt have a sizeable deployment base. Drupal + Ubercart + a ton of their modules work great. It is what drives: http://www.postgresqlconference.org/ http://www.postgresql.us http://www.fossexperts.com/ http://www.commandprompt.com/portal -- PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Comparison of Oracle and PostgreSQL full text search
On Tue, 27 Jul 2010 23:24:12 -0600, Scott Marlowe scott.marl...@gmail.com wrote: Someone running Oracle is complaining about training costs? That seems a bit like complaining about needing to give the bellboy a $1 tip at a $1k a night hotel. Depending on how they are running their licensing, (user/processor/standard/enterprise) Oracle can actually be reasonable in the sense of a commercial database. That said, PostgreSQL training is cheap. If you have enough people in your org to justify a on-site training, a 2 day Administration + Performance + Maintenance class is only 5k (remember on-site). Over time though, there is no question that hands down PostgreSQL will save you money. You can get an Enterprise class support contract for 500.00 a month per server. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Want FUNCTION to return argv[0] thru argv[6]
Hello, I am using postgres-odbc and I created the following function which is called from a C-application. This function returns a single argv[0] of the form (a,b,c,d,e,f,g). CREATE OR REPLACE FUNCTION PresRoute(int, int) RETURNS TABLE(d1 text, d2 text, d3 text, d4 text, r1 bigint, r2 bigint, r3 bigint) AS $$ SELECT PresDest1(PresNode($1,$2)), PresDest2(PresNode($1,$2)), PresDest3(PresNode($1,$2)), PresDest4(PresNode($1,$2)), PresRing1(PresNode($1,$2)), PresRing2(PresNode($1,$2)), PresRing3(PresNode($1,$2)) $$ LANGUAGE SQL; But I would like it to return data as argv[0] thru argv[6] as shown: d1 | d2 | d3| d4 | r1 | r2 | r3 --- abc defg How should I change my function definition to achieve this? Thanks, Jerry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] order in which rules are executed
Hello All, I have the below query regarding Rules in PostgreSQL: If I have a table which has multiple rules defined, are the rules executed in the order in which they are defined? Or are they executed in some random order? Thanks, Ranjeeth -- 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 Big is Too Big for Tables?
I'm building a national database of agricultural information and one of the layers is a bit more than a gigabyte per state. That's 1-2 million records per state, with a mult polygon geometry, and i've got about 40 states worth of data. I trying to store everything in a single PG table. What I'm concerned about is if I combine every state into one big table then will performance will be terrible, even with indexes? On the other hand, if I store the data in several smaller files, then if a user zooms in on a multi-state region, I've got to build or find a much more complicated way to query multiple files. So I'm wondering, should I be concerned with building a single national size table (possibly 80-100 Gb) for all these records, or should I keep the files smaller and hope there's something like ogrtindex out there for PG tables? what do you all recommend in this case? I just moved over to Postgres to handle big files, but I don't know its limits. With a background working with MS Access and bitter memories of what happens when you get near Access' two gigabyte database size limit, I'm a little nervous of these much bigger files. So I'd appreciate anyone's advice here. TIA, - Bill Thoen
Re: [GENERAL] How Big is Too Big for Tables?
2010/7/28 Bill Thoen bth...@gisnet.com: I'm building a national database of agricultural information and one of the layers is a bit more than a gigabyte per state. That's 1-2 million records per state, with a mult polygon geometry, and i've got about 40 states worth of data. I trying to store everything in a single PG table. What I'm concerned about is if I combine every state into one big table then will performance will be terrible, even with indexes? On the other hand, if I store the data in several smaller files, then if a user zooms in on a multi-state region, I've got to build or find a much more complicated way to query multiple files. So I'm wondering, should I be concerned with building a single national size table (possibly 80-100 Gb) for all these records, or should I keep the files smaller and hope there's something like ogrtindex out there for PG tables? what do you all recommend in this case? I just moved over to Postgres to handle big files, but I don't know its limits. With a background working with MS Access and bitter memories of what happens when you get near Access' two gigabyte database size limit, I'm a little nervous of these much bigger files. So I'd appreciate anyone's advice here. AFAIK it could be just a matter of how much RAM do you have, DDL and DML (aka queries). Hitting the real PG limits it's quite hard, even in your case. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- 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 Big is Too Big for Tables?
On Wed, 2010-07-28 at 11:09 -0600, Bill Thoen wrote: I'm building a national database of agricultural information and one of the layers is a bit more than a gigabyte per state. That's 1-2 million records per state, with a mult polygon geometry, and i've got about 40 states worth of data. I trying to store everything in a single PG table. What I'm concerned about is if I combine every state into one big table then will performance will be terrible, even with indexes? On the other hand, if I store the data in several smaller files, then if a user zooms in on a multi-state region, I've got to build or find a much more complicated way to query multiple files. So I'm wondering, should I be concerned with building a single national size table (possibly 80-100 Gb) for all these records, or should I keep the files smaller and hope there's something like ogrtindex out there for PG tables? what do you all recommend in this case? 80-100Gb isn't that much. However it may be worth looking into partitioning by state. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Which CMS/Ecommerce/Shopping cart ?
On Wed, 2010-07-28 at 22:37 +0530, Sandeep Srinivasa wrote: Could you point me to any deployments of Drupal + Ubercart + Postgres ? Did you not see the links below? Drupal + Ubercart + a ton of their modules work great. It is what drives: http://www.postgresqlconference.org/ http://www.postgresql.us http://www.fossexperts.com/ http://www.commandprompt.com/portal -- PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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 Big is Too Big for Tables?
You should look at table partitioning. That is, you make a master table and then make a table for each state that would inherit the master. That way you can query each state individually or you can query the whole country if need be. http://www.postgresql.org/docs/current/static/ddl-partitioning.html On 7/28/2010 12:09 PM, Bill Thoen wrote: I'm building a national database of agricultural information and one of the layers is a bit more than a gigabyte per state. That's 1-2 million records per state, with a mult polygon geometry, and i've got about 40 states worth of data. I trying to store everything in a single PG table. What I'm concerned about is if I combine every state into one big table then will performance will be terrible, even with indexes? On the other hand, if I store the data in several smaller files, then if a user zooms in on a multi-state region, I've got to build or find a much more complicated way to query multiple files. So I'm wondering, should I be concerned with building a single national size table (possibly 80-100 Gb) for all these records, or should I keep the files smaller and hope there's something like ogrtindex out there for PG tables? what do you all recommend in this case? I just moved over to Postgres to handle big files, but I don't know its limits. With a background working with MS Access and bitter memories of what happens when you get near Access' two gigabyte database size limit, I'm a little nervous of these much bigger files. So I'd appreciate anyone's advice here. TIA, - Bill Thoen
Re: [GENERAL] How Big is Too Big for Tables?
On Wed, Jul 28, 2010 at 12:03 PM, Joshua D. Drake j...@commandprompt.com wrote: On Wed, 2010-07-28 at 11:09 -0600, Bill Thoen wrote: I'm building a national database of agricultural information and one of the layers is a bit more than a gigabyte per state. That's 1-2 million records per state, with a mult polygon geometry, and i've got about 40 states worth of data. I trying to store everything in a single PG table. What I'm concerned about is if I combine every state into one big table then will performance will be terrible, even with indexes? On the other hand, if I store the data in several smaller files, then if a user zooms in on a multi-state region, I've got to build or find a much more complicated way to query multiple files. So I'm wondering, should I be concerned with building a single national size table (possibly 80-100 Gb) for all these records, or should I keep the files smaller and hope there's something like ogrtindex out there for PG tables? what do you all recommend in this case? 80-100Gb isn't that much. However it may be worth looking into partitioning by state. See http://archives.postgresql.org/pgsql-general/2010-07/msg00691.php for details, but here is a summary. My experience has not been the greatest. I have been trying to figure out if I can store a few hundred million rows, and have experienced a great number of problems. One. Loading the data is a problem. COPY is the quickest way (I was able to achieve a max of about 20,000 inserts per second). However, you need to make sure there are no indexes, not even a primary key, in order to extract maximum speed. That means, you have to load *everything* in one go. If you load in stages, you have drop all the indexes, then load, then rebuild the indexes. Next time you want to load more data, you to repeat this process. Building the indexes takes a long time, so experimenting is a chore. Two. Partitioning is not the perfect solution. My database will ultimately have about 13 million rows per day (it is daily data) for about 25 years. So, I need either -- - One big table with 25 * 365 * 13 million rows. Completely undoable. - 25 yearly tables with 365 * 13 million rows each. Still a huge chore, very slow queries. - 25 * 365 tables with 13 million rows each. More doable, but partitioning doesn't work. Three. At least, in my case, the overhead is too much. My data are single bytes, but the smallest data type in Pg is smallint (2 bytes). That, plus the per row overhead adds to a fair amount of overhead. I haven't yet given up on storing this specific dataset in Pg, but am reconsidering. It is all readonly data, so flat files might be better for me. In other words, Pg is great, but do tests, benchmark, research before committing to a strategy. Of course, since you are storing geometries, Pg is a natural choice for you. My data are not geometries, so I can explore alternatives for it, while keeping my geographic data in Pg. Hope this helps, or, at least provides an alternative view point. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === -- 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] Which CMS/Ecommerce/Shopping cart ?
Could you point me to any deployments of Drupal + Ubercart + Postgres ? It felt really strange that nobody on IRC or forums could answer that they had been involved in a postgres based deployment. thanks! On Wed, Jul 28, 2010 at 8:23 PM, Joshua D. Drake j...@commandprompt.comwrote: Except for Drupal's partial support, I cant find any which has a sizeable deployment and community size behind it. Spree is a new RoR based system, that would obviously work with PG, but doesnt have a sizeable deployment base. Drupal + Ubercart + a ton of their modules work great. It is what drives: http://www.postgresqlconference.org/ http://www.postgresql.us http://www.fossexperts.com/ http://www.commandprompt.com/portal -- PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
Re: [GENERAL] How Big is Too Big for Tables?
If all the table files are the same structure, its really not hard, just a UNION clause. Indeed, one can even create a VIEW that leverages that union clause to simplify the code that needs to grab from the multiple tables. As far as indexes, single table COULD be OK if you throw enough hardware at it. But if the data changes a lot and vacumming/index rebuilding is not keeping up, well it could get degraded performance even with high end hardware. Let's look at your indexes, are they to be of 3-4 columns or less? Likely you will be OK. If there are several or more columns, your indexes will be massive and then performance drops off with increased paging on even just index usage. NOTE: If you compile the data into a SINGLE table, you could always break up your table into smaller tables using SELECT INTO statements that grab by state. Then your queries that assume a single table for all states need to be tweaked to use union or (even better) tweaked to use a VIEW that already implements a union. If a lot of querying would use the UNION'd view, you probably want to avoid that. If its not very often, or OK to wait a little bit longer, the union will allow you to break up the data with probably only minor impact when you need multiple states reported together. You likely probably might almost sort of maybe be best to do a test case on your hardware first, even if dummy meaningless data populated by a script, it will give you a measurement of your expected performance that is much more meaningful then my ramble above. :) Terry Terry Fielder te...@greatgulfhomes.com Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 Bill Thoen wrote: I'm building a national database of agricultural information and one of the layers is a bit more than a gigabyte per state. That's 1-2 million records per state, with a mult polygon geometry, and i've got about 40 states worth of data. I trying to store everything in a single PG table. What I'm concerned about is if I combine every state into one big table then will performance will be terrible, even with indexes? On the other hand, if I store the data in several smaller files, then if a user zooms in on a multi-state region, I've got to build or find a much more complicated way to query multiple files. So I'm wondering, should I be concerned with building a single national size table (possibly 80-100 Gb) for all these records, or should I keep the files smaller and hope there's something like ogrtindex out there for PG tables? what do you all recommend in this case? I just moved over to Postgres to handle big files, but I don't know its limits. With a background working with MS Access and bitter memories of what happens when you get near Access' two gigabyte database size limit, I'm a little nervous of these much bigger files. So I'd appreciate anyone's advice here. TIA, - Bill Thoen
Re: [GENERAL] How Big is Too Big for Tables?
Under the assumption that you properly modeled the data - achieved a nice balance of normalization and de-normalization, examined the size of your relations in such a context, and accounted for how the data will grow over time and if it will grow over time, then partitioning, as Joshua mentioned, could be an advantageous route to explore. The user-interface component, namely, zooming in and out, should remain an abstraction at this point. My two cents but it sounds like a lot of groundwork needs to be done first. On 7/28/10 12:04 PM, Alex Thurlow wrote: You should look at table partitioning. That is, you make a master table and then make a table for each state that would inherit the master. That way you can query each state individually or you can query the whole country if need be. http://www.postgresql.org/docs/current/static/ddl-partitioning.html On 7/28/2010 12:09 PM, Bill Thoen wrote: I'm building a national database of agricultural information and one of the layers is a bit more than a gigabyte per state. That's 1-2 million records per state, with a mult polygon geometry, and i've got about 40 states worth of data. I trying to store everything in a single PG table. What I'm concerned about is if I combine every state into one big table then will performance will be terrible, even with indexes? On the other hand, if I store the data in several smaller files, then if a user zooms in on a multi-state region, I've got to build or find a much more complicated way to query multiple files. So I'm wondering, should I be concerned with building a single national size table (possibly 80-100 Gb) for all these records, or should I keep the files smaller and hope there's something like ogrtindex out there for PG tables? what do you all recommend in this case? I just moved over to Postgres to handle big files, but I don't know its limits. With a background working with MS Access and bitter memories of what happens when you get near Access' two gigabyte database size limit, I'm a little nervous of these much bigger files. So I'd appreciate anyone's advice here. TIA, - Bill Thoen -- 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] Comparison of Oracle and PostgreSQL full text search
Thomas Kellerer spam_ea...@gmx.net writes: Howard Rogers, 28.07.2010 03:58: For what it's worth, I wrote up the performance comparison here: http://diznix.com/dizwell/archives/153 Very interesting reading. Indeed. Would you mind sharing the tables, index structures and search queries that you used (both for Oracle and Postgres)? What I'd be interested in is EXPLAIN ANALYZE results. In particular, I wonder if the planner was switching from indexscan to seqscan plans for the cases where many rows would be returned, and if so whether it got the cutover point right. 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] Want FUNCTION to return argv[0] thru argv[6]
On Wed, Jul 28, 2010 at 12:06 PM, Jerry Richards jerry.richa...@teotech.com wrote: Hello, I am using postgres-odbc and I created the following function which is called from a C-application. This function returns a single argv[0] of the form (a,b,c,d,e,f,g). CREATE OR REPLACE FUNCTION PresRoute(int, int) RETURNS TABLE(d1 text, d2 text, d3 text, d4 text, r1 bigint, r2 bigint, r3 bigint) AS $$ SELECT PresDest1(PresNode($1,$2)), PresDest2(PresNode($1,$2)), PresDest3(PresNode($1,$2)), PresDest4(PresNode($1,$2)), PresRing1(PresNode($1,$2)), PresRing2(PresNode($1,$2)), PresRing3(PresNode($1,$2)) $$ LANGUAGE SQL; But I would like it to return data as argv[0] thru argv[6] as shown: d1 | d2 | d3| d4 | r1 | r2 | r3 --- a b c d e f g How should I change my function definition to achieve this? I think it's just a matter of how you're calling it. Are you doing this: SELECT PresRoute(... or this: SELECT * FROM PresRoute(... if you don't want the rowtype, do the latter. The former is roughly same as doing 'select foo from foo' vs 'select * from foo'. merlin -- 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] Incorrect FTS result with GIN index
Tom, you can download dump http://mira.sai.msu.su/~megera/tmp/search_tab.dump Oleg On Tue, 27 Jul 2010, Tom Lane wrote: Oleg Bartunov o...@sai.msu.su writes: I recommend post your problem to -hackers mailing list. I have no idea, what could be a problem. I wonder whether the problem is not windows versus non windows but original database versus copies. If it is a GIN bug it seems quite possible that it would depend on the order of insertion of the index entries, which a simple dump-and-reload probably wouldn't duplicate. If you were working from a dump it'd be easy to try creating the index before populating the table to see if the bug can be reproduced then, but there's no certainty that would provoke the bug. The rest of us have not seen the dump data, so we have no hope of doing anything with this report anyway. regards, tom lane Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] Inheritance and trigger/FK propagation
On Tue, Jul 27, 2010 at 10:36:16AM +0200, Davor J. wrote: For me Vick's question just proves that inheritance in relational databases is a complex issue. It shows that trigger propagation is not always desired, Now that's for sure :-) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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 Big is Too Big for Tables?
2010/7/28 P Kishor punk.k...@gmail.com: ... Two. Partitioning is not the perfect solution. My database will ultimately have about 13 million rows per day (it is daily data) for about 25 years. So, I need either -- - One big table with 25 * 365 * 13 million rows. Completely undoable. - 25 yearly tables with 365 * 13 million rows each. Still a huge chore, very slow queries. - 25 * 365 tables with 13 million rows each. More doable, but partitioning doesn't work. Three. At least, in my case, the overhead is too much. My data are single bytes, but the smallest data type in Pg is smallint (2 bytes). That, plus the per row overhead adds to a fair amount of overhead. I haven't yet given up on storing this specific dataset in Pg, but am reconsidering. It is all readonly data, so flat files might be better for me. In other words, Pg is great, but do tests, benchmark, research before committing to a strategy. Of course, since you are storing geometries, Pg is a natural choice for you. My data are not geometries, so I can explore alternatives for it, while keeping my geographic data in Pg. That recalls me an old inquiry of mine on the list about enterprise grade (or whatever you want to call it) solutions. That means, really lots of rows or, alternatively really lots of tables in the hierarchy or, again, really lots of partial indexes. Partitioning is not going to work probably because coping with thousands of tables in a hierarchy would hit against some linear algorithm inside the query planner, even with constraint exclusion. Maybe multilevel hierarchy (let's say partitioning by months (12) on the first level *and* by day (28,29,30 or 31) on the second one) would do the magics, but here the DDL would be quite killing, even with some PL/PGSQL helper function. The linearity of the index selection killed the performances also in the really lots of partial indexes approach. -- NotOrAnd Information Technologies Vincenzo Romano -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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] Inheritance and trigger/FK propagation
On Tue, Jul 27, 2010 at 10:33:19AM +0200, Davor J. wrote: Well... I found it out the hard way :). There are some extra caveats I have come along. There is the very clumsy ALTER TABLE table_name INHERIT(parent_table) which simply presupposes the parent's columns, but doesn't enforce it thereafter? So you can remove an inherited column from the child table when inheritance is made after the child table creation. Anyhow, I thought it could be quite usable for development a row level security system. For example, one could have a table rls_security (rls_owner name, rls_select name, rls_delete name, rls_update name) and a simple trigger: While, as you found out, the trigger won't auto-propagate this approach is still useful ! - let all tables inherit from a base table providing the rls fields - write a generic trigger that accesses the rls fields *only* (the table oid of the child table is available in the parent table row, fortunately, which will help making error messages better) - use an external script (or even plpgsql function) to attach said generic trigger to each table - the script does not need to know the list of relevant tables because that can be derived from the schema metadata inside PostgreSQL (they are children of the parent table ;-) While a bit more cumbersome than (on-demand) trigger propagation it is still a fairly clean and close-to-the-ideal solution. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] order in which rules are executed
On Wed, Jul 28, 2010 at 10:16:45PM +0530, Ranjeeth Nagarajan wrote: Hello All, I have the below query regarding Rules in PostgreSQL: If I have a table which has multiple rules defined, are the rules executed in the order in which they are defined? Or are they executed in some random order? They're executed in alphabetical order, to the extent that that is deterministic, which is not very, and that's not even the wackiest thing about them. If you have any alternative of any nature, do NOT use rules. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 Big is Too Big for Tables?
There are Postgres Enterprise solutions available although I think they are commercial. You may want to take a look and see if they can be helpful to you. On Wed, Jul 28, 2010 at 8:44 PM, Vincenzo Romano vincenzo.rom...@notorand.it wrote: 2010/7/28 P Kishor punk.k...@gmail.com: ... Two. Partitioning is not the perfect solution. My database will ultimately have about 13 million rows per day (it is daily data) for about 25 years. So, I need either -- - One big table with 25 * 365 * 13 million rows. Completely undoable. - 25 yearly tables with 365 * 13 million rows each. Still a huge chore, very slow queries. - 25 * 365 tables with 13 million rows each. More doable, but partitioning doesn't work. Three. At least, in my case, the overhead is too much. My data are single bytes, but the smallest data type in Pg is smallint (2 bytes). That, plus the per row overhead adds to a fair amount of overhead. I haven't yet given up on storing this specific dataset in Pg, but am reconsidering. It is all readonly data, so flat files might be better for me. In other words, Pg is great, but do tests, benchmark, research before committing to a strategy. Of course, since you are storing geometries, Pg is a natural choice for you. My data are not geometries, so I can explore alternatives for it, while keeping my geographic data in Pg. That recalls me an old inquiry of mine on the list about enterprise grade (or whatever you want to call it) solutions. That means, really lots of rows or, alternatively really lots of tables in the hierarchy or, again, really lots of partial indexes. Partitioning is not going to work probably because coping with thousands of tables in a hierarchy would hit against some linear algorithm inside the query planner, even with constraint exclusion. Maybe multilevel hierarchy (let's say partitioning by months (12) on the first level *and* by day (28,29,30 or 31) on the second one) would do the magics, but here the DDL would be quite killing, even with some PL/PGSQL helper function. The linearity of the index selection killed the performances also in the really lots of partial indexes approach. -- NotOrAnd Information Technologies Vincenzo Romano -- NON QVIETIS MARIBVS NAVTA PERITVS -- 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 Big is Too Big for Tables?
* P Kishor (punk.k...@gmail.com) wrote: Three. At least, in my case, the overhead is too much. My data are single bytes, but the smallest data type in Pg is smallint (2 bytes). That, plus the per row overhead adds to a fair amount of overhead. My first reaction to this would be- have you considered aggregating the data before putting it into the database in such a way that you put more than 1 byte of data on each row..? That could possibly reduce the number of rows you have by quite a bit and also reduce the impact of the per-tuple overhead in PG.. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] How Big is Too Big for Tables?
On Wed, Jul 28, 2010 at 1:38 PM, Stephen Frost sfr...@snowman.net wrote: * P Kishor (punk.k...@gmail.com) wrote: Three. At least, in my case, the overhead is too much. My data are single bytes, but the smallest data type in Pg is smallint (2 bytes). That, plus the per row overhead adds to a fair amount of overhead. My first reaction to this would be- have you considered aggregating the data before putting it into the database in such a way that you put more than 1 byte of data on each row..? That could possibly reduce the number of rows you have by quite a bit and also reduce the impact of the per-tuple overhead in PG.. each row is half a dozen single byte values, so, it is actually 6 bytes per row (six columns). Even if I combine them somehow, still the per row overhead (which, I believe, is about 23 bytes) is more than the data. But, that is not the issue. First, I can't really merge several days into one row. While it might make for fewer rows, it will complicate my data extraction and analysis life very complicated. The real issue is that once I put a 100 million rows in the table, basically the queries became way too slow. Of course, I could (and should) upgrade my hardware -- I am using a dual Xeon 3 GHz server with 12 GB RAM, but there are limits to that route. Keep in mind, the circa 100 million rows was for only part of the db. If I were to build the entire db, I would have about 4 billion rows for a year, if I were to partition the db by years. And, partitioning by days resulted in too many tables. I wish there were a way around all this so I could use Pg, with my available resources, but it looks bleak right now. Thanks, Stephen -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkxQeSIACgkQrzgMPqB3kihjYgCeMx2awmTE4IfAHgtws8iKhteN cnMAoIp2g2Zfo00GC7du16nwBht3Kt1O =7tdl -END PGP SIGNATURE- -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === -- 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 Big is Too Big for Tables?
On Wed, Jul 28, 2010 at 3:05 PM, P Kishor punk.k...@gmail.com wrote: Keep in mind, the circa 100 million rows was for only part of the db. If I were to build the entire db, I would have about 4 billion rows for a year, if I were to partition the db by years. And, partitioning by days resulted in too many tables. Don't partition by arbitrary slices. Find out what your queries are and partition across the most common of those, possibly in two dimensions even. Without knowing what kinds of queries you do it is hard to suggest things that may actually benefit you. Are you using one of the advanced data types in postgres that deals with spatial data? Additionally, if you're trying to have 4 billion rows of data and only have a 12GB RAM on your box, no matter your choice of DB it will be slow. -- 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] Which CMS/Ecommerce/Shopping cart ?
yup I did. The reason why I wanted examples was to amply demonstrate,to clients, that postgresql is viable. It is kinda weird if the only examples I have are restricted to the postgresql _community_ websites themselves. This may sound irrelevant, but please do understand the huge opposition to have anything to do with PG in the whole CMS/e-store community. In fact I even saw a request to eliminate postgresql support in Drupal 7 (that was taken care of by the valiant efforts of the PG community) : http://drupal.org/node/337146 Plus, it would have been interesting to know which version of Drupal, Ubercart, etc was being used for such deployments. Again, it is relevant because of certain (older) benchmarks which denote significantly worse performance because of the suboptimal way that Drupal integrates with Postgresql : http://mikkel.hoegh.org/blog/2008/oct/13/drupal-database-performance-mysql-postgresql/ There has been _nothing_ to disprove the above numbers, ever since - please correct me if I am wrong. What does a person making a case for Postgres do in this situation ? thanks http://mikkel.hoegh.org/blog/2008/oct/13/drupal-database-performance-mysql-postgresql/ On Wed, Jul 28, 2010 at 10:40 PM, Joshua D. Drake j...@commandprompt.comwrote: On Wed, 2010-07-28 at 22:37 +0530, Sandeep Srinivasa wrote: Could you point me to any deployments of Drupal + Ubercart + Postgres ? Did you not see the links below? Drupal + Ubercart + a ton of their modules work great. It is what drives: http://www.postgresqlconference.org/ http://www.postgresql.us http://www.fossexperts.com/ http://www.commandprompt.com/portal -- PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?
On Thu, 2010-07-29 at 00:36 +0530, Sandeep Srinivasa wrote: yup I did. The reason why I wanted examples was to amply demonstrate,to clients, that postgresql is viable. It is kinda weird if the only examples I have are restricted to the postgresql _community_ websites themselves. Well you are kind of asking in the wrong place. You should be asking in #drupal, #drupal-support, #drupal-ubercart or in the Drupal forums. This may sound irrelevant, but please do understand the huge opposition to have anything to do with PG in the whole CMS/e-store community. In fact I even saw a request to eliminate postgresql support in Drupal 7 (that was taken care of by the valiant efforts of the PG community) : http://drupal.org/node/337146 Yes, I know. I was part of that. I would note that topic was 2 years ago and has since long died. Plus, it would have been interesting to know which version of Drupal, Ubercart, etc was being used for such deployments. Again, it is relevant because of certain (older) benchmarks which denote significantly worse performance because of the suboptimal way that Latest 6.x release and latest Ubercart release. Drupal integrates with Postgresql : http://mikkel.hoegh.org/blog/2008/oct/13/drupal-database-performance-mysql-postgresql/ There has been _nothing_ to disprove the above numbers, ever since - please correct me if I am wrong. You should read that whole blog. PostgreSQL does very well in consideration of the environment. I would also note that there is no reference to whether or not he tuned PostgreSQL or not. I have zero problems running Drupal with PostgreSQL and getting great performance but then again I know enough to tune both Drupal, PHP and PostgreSQL. Most people can't say that (I am not saying you can't). What does a person making a case for Postgres do in this situation ? That is a tough one. I mean, prove it to him. Set up Drupal with MySQL/Innodb and setup Drupal with PostgreSQL and do some tests. You can also look for things like this: http://www.commandprompt.com/blogs/joshua_drake/2010/07/multiple_drupal_installations_single_login_10_steps/ That show the flexibility you get by using PostgreSQL. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need help with full text index configuration
I have some data that can be searched, and it looks like the parser is making
some assumptions about the data that aren't true in our case and I'm trying to
figure out how to exclude a token type. I haven't been able to find the
answer to my question so far, so I thought I would ask here.
The data I have are english words, and sometimes there are words separated by a
/ without spaces. The parser finds these things and tokenizes them as files.
I'm sure in some situations that's the right assumption, but based on my data,
I know there will never be a file name in the column.
For example instead of the parser recognizing three asciiword it recognizes one
asciiword and one file. I'd like a way to have the / just get parsed as
blank.
db=# select * from ts_debug('english','maybe five/six');
alias |description| token | dictionaries | dictionary |
lexemes
---+---+--++--+
asciiword | Word, all ASCII | maybe| {english_stem} | english_stem |
{mayb}
blank | Space symbols | | {} | |
file | File or path name | five/six | {simple} | simple |
{five/six}
(3 rows)
I thought that maybe I could create a new configuration and drop the file
mapping, but that doesn't seem to work either.
db=# CREATE TEXT SEARCH CONFIGURATION public.testd ( COPY = pg_catalog.english
);
CREATE TEXT SEARCH CONFIGURATION
db=# ALTER TEXT SEARCH CONFIGURATION testd DROP MAPPING FOR file;
ALTER TEXT SEARCH CONFIGURATION
db=# SELECT * FROM ts_debug('testd','mabye five/six');
alias |description| token | dictionaries | dictionary |
lexemes
---+---+--++--+-
asciiword | Word, all ASCII | mabye| {english_stem} | english_stem |
{maby}
blank | Space symbols | | {} | |
file | File or path name | five/six | {} | |
(3 rows)
Is there anyway to do this?
Thanks for the help in advance. I'm running 8.4.4
--
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 Big is Too Big for Tables?
On Wed, Jul 28, 2010 at 02:05:47PM -0500, P Kishor wrote: each row is half a dozen single byte values, so, it is actually 6 bytes per row (six columns). Even if I combine them somehow, still the per row overhead (which, I believe, is about 23 bytes) is more than the data. But, that is not the issue. I had a design like that for an application too. I thought it was not an issue, but the row overhead causes memory and disk usage to skyrocket, and will cause queries to slow down to a grind. The solution for me was to group my values logically together and store them in the same row somehow. In my case, this worked by storing all the values for one measuring point (timestamp) in an array field, with the array indices being stored in a bookkeeping table (each measuring moment produced the same number of values for me, so I was able to do this). Extracting one value from a long array (some datasets include thousands of values per measuring moment) is extremely fast. You can also easily make indices on those array dereferences you need to search on, if those are always the same. First, I can't really merge several days into one row. While it might make for fewer rows, it will complicate my data extraction and analysis life very complicated. Perhaps you could put all days of a month in an array, indexed by day of the month? That wouldn't be too hard for your logic to deal with, I think. The real issue is that once I put a 100 million rows in the table, basically the queries became way too slow. I had the same issue. Partitioning falls flat on its face once you're dealing with such insane amounts of data. In my experience if your partitions aren't constant and will keep growing, you will face problems sooner or later. If you do partitioning the traditional way by inheriting the table, you'll also run into additional trouble since for some operations Postgres will need to obtain a handle on all partitions and that will easily cause you to run out of shared memory. You can increase max_locks_per_transaction, but that's undoable if the number of partitions keeps growing. You need to keep increasing that value all the time... Of course, I could (and should) upgrade my hardware -- I am using a dual Xeon 3 GHz server with 12 GB RAM, but there are limits to that route. Always try to solve it by changing your data design first, unless what you're trying to do is fundamentally limited by hardware. You're not likely going to request all those record at once, nor will you need to search through all of them; try to come up with a sane way of quickly slicing your data to a smaller set which can be quickly retrieved. Keep in mind, the circa 100 million rows was for only part of the db. If I were to build the entire db, I would have about 4 billion rows for a year, if I were to partition the db by years. And, partitioning by days resulted in too many tables. Yeah, sounds similar to the troubles I ran into in my project. I wish there were a way around all this so I could use Pg, with my available resources, but it looks bleak right now. Try using the array approach. Possibly you could create columns for each week or month in a year and store the individual days in an array in that column. Extracting those shouldn't be too hard. You could store the different types of data you have in different rows for each unit of information you want to store for a day. Alternatively, store your data points all in one row, and store a row for each day. You could easily start partitioning historical data per year or per decade. Cheers, Peter -- http://sjamaan.ath.cx -- The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music. -- Donald Knuth -- 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] Need help with full text index configuration
Brian Hirt bh...@mobygames.com writes: For example instead of the parser recognizing three asciiword it recognizes one asciiword and one file. I'd like a way to have the / just get parsed as blank. AFAIK the only good way to do that is to write your own parser :-(. The builtin parser isn't really configurable. (If you didn't mind maintaining a private version you could patch its state transition table manually, but that seems like a PITA.) For the case at hand it could be a pretty thin frontend to the builtin text parser --- just change / to space and then call the builtin one. contrib/test_parser/ might help you get started. 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] Pglesslog issue
Hi, I was installed the Postgresql 8.3 and trying the use the pg_lesslog_1.4.1_pg83 to reduce the size of WAL file when the WAL file is archived. 1. Download the pg_lesslog_1.4.1_pg83.tar.gz file from pgfoundry. 2. unpacked the pglesslog source. 3. trying to run the make...facing below issue:: edbs-MacBook-4:*pg_lesslog_1.4.1_pg83 root# make* make -f Makefile.pg_compresslog all Makefile.pg_compresslog:21: ../../src/Makefile.global: No such file or directory Makefile.pg_compresslog:22: /contrib/contrib-global.mk: No such file or directory make[1]: *** No rule to make target `/contrib/contrib-global.mk'. Stop. make: *** [all] Error 2 could you please guide me the installation steps... Regards Raghu
Re: [GENERAL] Need help with full text index configuration
Tom, Thanks for the quick reply. Doing a frontend mapping was my next option since I really don't care about / and the ability to search on it. Preventing the parser from using the file tokenizer seemed like a better solution so I wanted to go down that path first (there are other false hits i was worried about too, like email, etc) I'm really confused about what ALTER TEXT SEARCH CONFIGURATION dict DROP MAPPING FOR file actually does. The documentation seems to make it sound like it does what I want, but I guess it does something else. --brian On Jul 28, 2010, at 2:06 PM, Tom Lane wrote: Brian Hirt bh...@mobygames.com writes: For example instead of the parser recognizing three asciiword it recognizes one asciiword and one file. I'd like a way to have the / just get parsed as blank. AFAIK the only good way to do that is to write your own parser :-(. The builtin parser isn't really configurable. (If you didn't mind maintaining a private version you could patch its state transition table manually, but that seems like a PITA.) For the case at hand it could be a pretty thin frontend to the builtin text parser --- just change / to space and then call the builtin one. contrib/test_parser/ might help you get started. 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] Need help with full text index configuration
Brian Hirt bh...@mobygames.com writes: I'm really confused about what ALTER TEXT SEARCH CONFIGURATION dict DROP MAPPING FOR file actually does. The documentation seems to make it sound like it does what I want, but I guess it does something else. No, it doesn't affect the parser's behavior at all. So foo/bar will still be parsed as a file token. What the above results in is dropping file tokens on the floor afterwards, instead of passing them to some dictionary. In general the mapping stuff just controls what dictionary(s) tokens produced by the parser will be routed to. 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] Danger of idiomatic plpgsql loop for merging data
Hi fellow PostgreSQL hackers, I just got burned by the idiomatic loop documented in the PostgreSQL manual as Example 39-2. Exceptions with UPDATE/INSERT I have now replaced this standard idiom with a safer one described below. What went wrong: It seems that the table I was either inserting into or selecting from had a trigger inserting some associated data which was sometimes raising a unique_violation exception, turning the standard idiom into an infinite loop! My (simplified) old code looked like this: CREATE TABLE foos ( foo_ foo PRIMARY KEY DEFAULT next_foo(); name_ text UNIQUE NOT NULL; ); CREATE OR REPLACE FUNCTION get_foo(text) RETURNS foo AS $$ DECLARE _foo foo; BEGIN LOOP SELECT foo_ INTO _foo FROM foos WHERE name_ = $1; IF FOUND THEN RETURN _foo; END IF; BEGIN INSERT INTO foos(name_) VALUES($1); EXCEPTION WHEN unique_violation THEN -- maybe another thread? END; END LOOP; END; $$ LANGUAGE plpgsql STRICT; My (simplified) new code is longer but more flexible, safer and adds logging: CREATE OR REPLACE FUNCTION old_foo(text) RETURNS foo AS $$ SELECT foo_ FROM foos WHERE name_ = $1 $$ LANGUAGE SQL STRICT; CREATE OR REPLACE FUNCTION new_foo(text) RETURNS foo AS $$ DECLARE this regprocedure := 'new_foo(text)'; _foo foo; BEGIN INSERT INTO foos(name_) VALUES ($1) RETURNING foo_ INTO _foo; RETURN _ref; EXCEPTION WHEN unique_violation THEN -- maybe another thread? RAISE NOTICE '% % unique_violation', this, $1; RETURN NULL; END; $$ LANGUAGE plpgsql STRICT; CREATE OR REPLACE FUNCTION get_foo(text) RETURNS foo AS $$ SELECT COALESCE( old_foo($1), new_foo($1), old_foo($1) ) $$ LANGUAGE sql STRICT; _Greg -- 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] Which CMS/Ecommerce/Shopping cart ?
On Wed, 28 Jul 2010 12:45:47 -0700 Joshua D. Drake j...@commandprompt.com wrote: On Thu, 2010-07-29 at 00:36 +0530, Sandeep Srinivasa wrote: yup I did. The reason why I wanted examples was to amply demonstrate,to clients, that postgresql is viable. It is kinda weird if the only examples I have are restricted to the postgresql _community_ websites themselves. Well you are kind of asking in the wrong place. You should be asking in #drupal, #drupal-support, #drupal-ubercart or in the Drupal forums. Well he will spend most of the time filtering people bashing postgres there. Plus, it would have been interesting to know which version of Drupal, Ubercart, etc was being used for such deployments. Again, it is relevant because of certain (older) benchmarks which denote significantly worse performance because of the suboptimal way that Latest 6.x release and latest Ubercart release. Drupal integrates with Postgresql : http://mikkel.hoegh.org/blog/2008/oct/13/drupal-database-performance-mysql-postgresql/ There has been _nothing_ to disprove the above numbers, ever since - please correct me if I am wrong. You should read that whole blog. PostgreSQL does very well in consideration of the environment. I would also note that there is no reference to whether or not he tuned PostgreSQL or not. I have zero problems running Drupal with PostgreSQL and getting great performance but then again I know enough to tune both Drupal, PHP and PostgreSQL. Most people can't say that (I am not saying you can't). I'm happy with PostgreSQL and Drupal too and right now I didn't have to get too worried about performances. D7 should support many things that makes more sense to use Postgres. I had to tweak D5 and D6 core to make it work with Postgres as I needed... the problem is it takes a lot of time to see postgres related patch get into core. Modules that are worth to use generally have reasonable maintainer, fixes and release are much faster. Still I'd say that if you don't have any specific reason to use postgresql (you have to access data on another app using postgres, you need some special feature (full text, GIS), you've a lot of writes to the DB...) would be a better choice if you had equal knowledge of both. Are there companies that offer drupal/postgres tuning? That is a tough one. I mean, prove it to him. Set up Drupal with MySQL/Innodb and setup Drupal with PostgreSQL and do some tests. You can also look for things like this: http://www.commandprompt.com/blogs/joshua_drake/2010/07/multiple_drupal_installations_single_login_10_steps/ Schemas in postgres with drupal are great. using: http://www.webthatworks.it/d1/content/howto-duplicating-schema-postgresql and http://www.webthatworks.it/d1/content/excluding-some-tables-data-backup-including-their-schema makes a breeze to duplicate sites. And you can still conserve all triggers pk, fk, on duplicate cascade... -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Which CMS/Ecommerce/Shopping cart ?
Ivan Sergio Borgonovo wrote: Are there companies that offer drupal/postgres tuning? I am quite sure that Command Prompt would be happy and fully prepared to sell you Drupal + PostgreSQL tuning services. We also have some projects around it, and I'm sure other consulting companies or individuals do too. I'd predict that if you sent a message to pgsql-jobs saying you're looking to hire someone for that sort of work, you'd get a stack of responses from qualified people in the PostgreSQL community. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Comparison of Oracle and PostgreSQL full text search
On Wed, Jul 28, 2010 at 8:38 PM, Daniel Verite dan...@manitou-mail.org wrote: zhong ming wu wrote: I always thought there is a clause in their user agreement preventing the users from publishing benchmarks like that. I must be mistaken. No you're correct. Currently, to download the current Oracle 11.2g, one must agree to: http://www.oracle.com/technetwork/licenses/standard-license-152015.html which contains: quote [...] You may not: [...] - disclose results of any program benchmark tests without our prior consent. [...] /quote Not having such frustrating license terms is also what makes PostgreSQL a nicer alternative! Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Hi Daniel: Nice catch -the thing is, you've linked to the *technet* license. The one you sign up to when you download the product for free, for development, prototyping and self-learning purposes. That's not the same license as the one you sign up to when you pay them stacks of cash for the 'proper' product for a production deployment (which I haven't read lately, so I can't say the same silly term isn't in there, but I'm just saying: the license you linked to is not the one that applies). Also, I would argue that what I did was not a 'benchmark test'. We capture the results and timings of queries as part of our production application, for management and review purposes. Those are real results, experienced by real users... not what I'd call a benchmark test. (The PostgreSQL results are, certainly, an artificial benchmark, but then the Oracle license doesn't cover those, happily!) Regards HJR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Dynamic data model, locks and performance
Hello people of the Postgresql world! I am wondering if Postgresql would a great choice for my database needs. I would like to create a db with dynamic data model. I would like to be able to add tables and columns to existing tables while other queries are running. Will Postresql be able to manage the locks correctly? Am I pushing Postgresql too far by wanting to do something like this? If so, what do you suggest instead? Also, I would like to be able to have millions of tables in my database. As I know, Postgresql uses one file per table. So is this problem related to performance about mapping correctly the files with the file system? Is Posgresql able to handle this kind of charge? -- A+ - Pierre
Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?
the one drupal programmer that programmed the system quit to do other things multi-threaded issues..integration with external security.. and/or anything critical / mildly useful will send you into support h*ll one stock form with no integration with clients database or j2ee server hosted only on apache is all you can hope for.. why not write it yourself? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Subject: Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ? From: j...@commandprompt.com To: s...@clearsenses.com CC: pgsql-general@postgresql.org Date: Wed, 28 Jul 2010 12:45:47 -0700 On Thu, 2010-07-29 at 00:36 +0530, Sandeep Srinivasa wrote: yup I did. The reason why I wanted examples was to amply demonstrate,to clients, that postgresql is viable. It is kinda weird if the only examples I have are restricted to the postgresql _community_ websites themselves. Well you are kind of asking in the wrong place. You should be asking in #drupal, #drupal-support, #drupal-ubercart or in the Drupal forums. This may sound irrelevant, but please do understand the huge opposition to have anything to do with PG in the whole CMS/e-store community. In fact I even saw a request to eliminate postgresql support in Drupal 7 (that was taken care of by the valiant efforts of the PG community) : http://drupal.org/node/337146 Yes, I know. I was part of that. I would note that topic was 2 years ago and has since long died. Plus, it would have been interesting to know which version of Drupal, Ubercart, etc was being used for such deployments. Again, it is relevant because of certain (older) benchmarks which denote significantly worse performance because of the suboptimal way that Latest 6.x release and latest Ubercart release. Drupal integrates with Postgresql : http://mikkel.hoegh.org/blog/2008/oct/13/drupal-database-performance-mysql-postgresql/ There has been _nothing_ to disprove the above numbers, ever since - please correct me if I am wrong. You should read that whole blog. PostgreSQL does very well in consideration of the environment. I would also note that there is no reference to whether or not he tuned PostgreSQL or not. I have zero problems running Drupal with PostgreSQL and getting great performance but then again I know enough to tune both Drupal, PHP and PostgreSQL. Most people can't say that (I am not saying you can't). What does a person making a case for Postgres do in this situation ? That is a tough one. I mean, prove it to him. Set up Drupal with MySQL/Innodb and setup Drupal with PostgreSQL and do some tests. You can also look for things like this: http://www.commandprompt.com/blogs/joshua_drake/2010/07/multiple_drupal_installations_single_login_10_steps/ That show the flexibility you get by using PostgreSQL. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ The New Busy is not the old busy. Search, chat and e-mail from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_3
Re: [GENERAL] Dynamic data model, locks and performance
On 29/07/10 07:06, Pierre Thibault wrote: Hello people of the Postgresql world! I am wondering if Postgresql would a great choice for my database needs. I would like to create a db with dynamic data model. I would like to be able to add tables and columns to existing tables while other queries are running. Will Postresql be able to manage the locks correctly? Am I pushing Postgresql too far by wanting to do something like this? If so, what do you suggest instead? Also, I would like to be able to have millions of tables in my database. As I know, Postgresql uses one file per table. So is this problem related to performance about mapping correctly the files with the file system? Is Posgresql able to handle this kind of charge? From what you say, I really doubt that PostgreSQL will be well suited to your needs. You might be able to make it work, but I doubt it'll be great. At heart Pg is a relational database, and you want to do things like dynamic table structures that relational databases are typically not great at. People here might be able to suggest alternate approaches if you can explain *why* you want to do what you want, what your goals are, etc. Pg might be quite suitable if you can rethink how you're doing things a little - or it might just be a bad choice. I doubt anyone can make any useful recommendations without a more complete explanation of what you're trying to achieve and why you want to do what you have described. Re Pg-specific stuff, you should look into hstore. It might offer a way to avoid the need for dynamic table re-structuring. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.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] Dynamic data model, locks and performance
2010/7/28 Craig Ringer cr...@postnewspapers.com.au On 29/07/10 07:06, Pierre Thibault wrote: I doubt anyone can make any useful recommendations without a more complete explanation of what you're trying to achieve and why you want to do what you have described. Thank you Craig, Yes, I was not very specific. I have an idea in mind. Everyone knows about Craig's List and the hype about NoSql databases. This seems to be cool to do full text searches in a lot of data. But what about doing more formal searches based specific criteria like dates, numbers or other kind values on specific fields? This where an SQL database shines. What I would like to do is enable users to create their own data model. Enable them to create a model and make it evolve. For example, it would be cool to create a model to represent car adds. Then, the buyers can search adds using the previous model with specific criteria like the color of the car they want. I thought about using a table where each row would represents a field in the dynamic model. The row would contain a fields for each possible data type supported but only one of them would be used. This would waste a lot space and it would also be hard to query efficiently but I will not have to change the schema as users create new models. Otherwise, I would create a standard data model and apply the changes as users update their models. I also like to support inheritance so we can have a simple model for regular adds and more specfic models with more fields for car adds for example. I have a really hard finding how to implement my idea efficiently. So the advice of experienced database developers is what I am looking for. Re Pg-specific stuff, you should look into hstore. It might offer a way to avoid the need for dynamic table re-structuring. I thought about using maps for the entities of my domain model. hstore seems to be an interesting avenue. -- A+ - Pierre
Re: [GENERAL] postgres-8.4SS, pg_dump from macosx-10.6 has ssl handshake error 26% in
Sachin Srivastava wrote: the latest enterprisedb standard server is only 8.4.1 (New! 13-Oct-09) :-) By using the StackBuilder Plus application, you can upgrade your server to 8.4.4. -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company. thanks but it didn't work for me (on debian 5 stable). it comes with most but not all of the x11-related libraries it needs. i installed the missing libraries and got errors about one of its libraries not having version information that was needed by libcairo. i then disabled its version of that library in favour of the system's one and stopped getting library-related error messages but it then just announced that it couldn't initialise gtk. the good news is that the one-click installer for the core distribution detected and happily upgraded the existing standard server installation. yay enterprisedb! cheers, raf -- 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] Dynamic data model, locks and performance
On Wed, Jul 28, 2010 at 10:15 PM, Pierre Thibault pierre.thibau...@gmail.com wrote: What I would like to do is enable users to create their own data model. Enable them to create a model and make it evolve. For example, it would be cool to create a model to represent car adds. Then, the buyers can search adds using the previous model with specific criteria like the color of the car they want. Sounds like a standard parts explosion or component / subcomponent hierarchy? The most general form has two pieces: 1) a tree model of some form. I like the set / subset model that Joe Celko popularized (search the archives for other references to it from me); 2) an extensible set of containers to hang of the tree. Here I like to use a strongly typed version of the Entity Attribute Value (EAV) type model; Those two pieces on their own are so general that you can't really scale the results very far. However, if you've got some specific goals in mind I might be able to provide some hints. If you're an IEEE member I can point you at a presentation I did on the basics (I think)... -- Peter Hunsberger -- 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-8.4SS, pg_dump from macosx-10.6 has ssl handshake error 26% in
raf wrote: Sachin Srivastava wrote: the latest enterprisedb standard server is only 8.4.1 (New! 13-Oct-09) :-) By using the StackBuilder Plus application, you can upgrade your server to 8.4.4. -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company. thanks but it didn't work for me (on debian 5 stable). it comes with most but not all of the x11-related libraries it needs. i installed the missing libraries and got errors about one of its libraries not having version information that was needed by libcairo. i then disabled its version of that library in favour of the system's one and stopped getting library-related error messages but it then just announced that it couldn't initialise gtk. the good news is that the one-click installer for the core distribution detected and happily upgraded the existing standard server installation. yay enterprisedb! but it still has the same old libssl which was my main reason for wanting to upgrade. oh well, it's good to upgrade the database at least. cheers, raf -- 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] Dynamic data model, locks and performance
On 29/07/10 11:15, Pierre Thibault wrote: What I would like to do is enable users to create their own data model. Then, really, SQL databases aren't wonderful for your needs. You can use them for dynamic, user-defined schema, but you'll always be swimming up hill. I thought about using a table where each row would represents a field in the dynamic model. The row would contain a fields for each possible data type supported but only one of them would be used. Google EAV This has known problems. It works, but it's kind of like bolting floats onto a car and trying to use it as a boat. Not very nice. Really, I'd be researching dynamic schema databases, object stores, key/value set stores, etc. Such things are outside my personal experience, though, and I can't really advise you much on technologies. Beware of the latest No-SQL fad platforms, though; you might land up being better off with something older and more stable even if it's less fashionable than CouchDB or whatever is currently in today. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.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] Which CMS/Ecommerce/Shopping cart ?
On Wed, 28 Jul 2010 18:56:56 -0400 Greg Smith g...@2ndquadrant.com wrote: Ivan Sergio Borgonovo wrote: Are there companies that offer drupal/postgres tuning? I am quite sure that Command Prompt would be happy and fully prepared to sell you Drupal + PostgreSQL tuning services. We also have some projects around it, and I'm sure other consulting companies or individuals do too. I'd predict that if you sent a message to pgsql-jobs saying you're looking to hire someone for that sort of work, you'd get a stack of responses from qualified people in the PostgreSQL community. Sure. What I haven't been able to spot are drupal companies that do drupal tuning when it is running with postgres. Of course here on pg ml is not hard to find companies that won't refuse to tune postgres even if you use it for drupal ;) BTW up to my memory Django suggest postgres. I haven't seen any benchmark of Django with pg vs mysql. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Which CMS/Ecommerce/Shopping cart ?
On Thu, 2010-07-29 at 07:04 +0200, Ivan Sergio Borgonovo wrote: BTW up to my memory Django suggest postgres. I haven't seen any benchmark of Django with pg vs mysql. Django was originally developed for Postgres but really, they are wholly different beasts. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
