Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?

[Sandeep Srinivasa]

hi ,
Thanks for several of the links that you guys posted.

The issue is not that I am looking for consulting companies who will set up
and optimize postgres+software. There are a million small firms that do
M*SQL+ work. And I am looking to do that kind of work with clients
- but I want to use the best DB out there, which I believe to be postgres.
But I find it hard to do it.

Clients do not want to engage in full custom s/w development, because they
get worried on what happens if we go out of business. I am sure many of you
out there, who have bigger clients have different experiences - but this is
the truth for most of the business that we see. And most of the existing
community or paid software out there, does not play nice with postgres.

This vicious cycle can only be broken at the level of pre-packaged web
software, which ought to work beautifully out-of-the-box with postgres.
There is just no way out of this.

What really, really hurts me is this - come Postgres 9.0 you will have the
most amazing DB software in the open source community. I (and millions of
small time developers like me) wont be able to leverage that - because our
clients will still demand , which have no good support for postgres.


-Sandeep

On Thu, Jul 29, 2010 at 10:54 AM, Joshua D. Drake wrote:

> 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
>

Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?

[Joshua D. Drake]

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

Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?

[Ivan Sergio Borgonovo]

On Wed, 28 Jul 2010 18:56:56 -0400
Greg Smith  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] Dynamic data model, locks and performance

[Craig Ringer]

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] postgres-8.4SS, pg_dump from macosx-10.6 has "ssl handshake error" 26% in

[raf]

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 , the Enterprise Postgres
> >  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

[Peter Hunsberger]

On Wed, Jul 28, 2010 at 10:15 PM, Pierre Thibault
 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]

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 , the Enterprise Postgres
>  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

[Pierre Thibault]

2010/7/28 Craig Ringer 

> 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] Dynamic data model, locks and performance

[Craig Ringer]

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] Which CMS/Ecommerce/Shopping cart ?

[Martin Gainty]

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

[GENERAL] Dynamic data model, locks and performance

[Pierre Thibault]

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] Comparison of Oracle and PostgreSQL full text search

[Howard Rogers]

On Wed, Jul 28, 2010 at 8:38 PM, Daniel Verite  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:
> 
> [...]
> You may not:
> [...]
> - disclose results of any program benchmark tests without our prior consent.
> [...]
> 
>
> 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

Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?

[Greg Smith]

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] Which CMS/Ecommerce/Shopping cart ?

[Ivan Sergio Borgonovo]

On Wed, 28 Jul 2010 12:45:47 -0700
"Joshua D. Drake"  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

[GENERAL] Danger of idiomatic plpgsql loop for merging data

[J. Greg Davidson]

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] Need help with full text index configuration

[Tom Lane]

Brian Hirt  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

Re: [GENERAL] Need help with full text index configuration

[Brian Hirt]

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  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

[raghu ram]

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 Lane]

Brian Hirt  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] How Big is Too Big for Tables?

[Peter Bex]

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

[GENERAL] Need help with full text index configuration

[Brian Hirt]

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] Which CMS/Ecommerce/Shopping cart ?

[Joshua D. Drake]

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

Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?

[Sandeep Srinivasa]

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




On Wed, Jul 28, 2010 at 10:40 PM, Joshua D. Drake wrote:

> 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] How Big is Too Big for Tables?

[Vick Khera]

On Wed, Jul 28, 2010 at 3:05 PM, P Kishor  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] How Big is Too Big for Tables?

[P Kishor]

On Wed, Jul 28, 2010 at 1:38 PM, Stephen Frost  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?

[Stephen Frost]

* 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?

[Otandeka Simon Peter]

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 :
> ...
> > 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] order in which rules are executed

[David Fetter]

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  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] Inheritance and trigger/FK propagation

[Karsten Hilbert]

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] How Big is Too Big for Tables?

[Vincenzo Romano]

2010/7/28 P Kishor :
...
> 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

[Karsten Hilbert]

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] Incorrect FTS result with GIN index

[Oleg Bartunov]

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  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] Want FUNCTION to return argv[0] thru argv[6]

[Merlin Moncure]

On Wed, Jul 28, 2010 at 12:06 PM, Jerry Richards
 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] Comparison of Oracle and PostgreSQL full text search

[Tom Lane]

Thomas Kellerer  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] How Big is Too Big for Tables?

[Bryan Hinton]

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] How Big is Too Big for Tables?

[Terry Fielder]

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] Which CMS/Ecommerce/Shopping cart ?

[Sandeep Srinivasa]

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 wrote:

>
> > 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?

[P Kishor]

On Wed, Jul 28, 2010 at 12:03 PM, Joshua D. Drake  
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] How Big is Too Big for Tables?

[Alex Thurlow]

 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] Which CMS/Ecommerce/Shopping cart ?

[Joshua D. Drake]

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?

[Joshua D. Drake]

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] How Big is Too Big for Tables?

[Vincenzo Romano]

2010/7/28 Bill Thoen :
> 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

[GENERAL] How Big is Too Big for Tables?

[Bill Thoen]

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

[GENERAL] order in which rules are executed

[Ranjeeth Nagarajan]

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] Want FUNCTION to return argv[0] thru argv[6]

[Jerry Richards]

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

Re: [GENERAL] Comparison of Oracle and PostgreSQL full text search

[Joshua D. Drake]

On Tue, 27 Jul 2010 23:24:12 -0600, Scott Marlowe

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

Re: [GENERAL] Which CMS/Ecommerce/Shopping cart ?

[Joshua D. Drake]

> 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] Histogram generator

[Sam Mason]

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] psql problem

[Gary Fu]

On 07/27/2010 10:20 PM, Tom Lane wrote:

Gary Fu  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

[GENERAL] First inserts are faster than second ones

[AlannY]

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] Comparison of Oracle and PostgreSQL full text search

[Daniel Verite]

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:

[...]
You may not:
[...] 
- disclose results of any program benchmark tests without our prior consent.
[...]


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

Re: [GENERAL] psql problem

[Greg Smith]

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

[Greg Williamson]

zhong ming wu wrote:



> 
> On Tue, Jul 27, 2010 at 9:58 PM, Howard Rogers  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] Comparison of Oracle and PostgreSQL full text search

[zhong ming wu]

On Tue, Jul 27, 2010 at 9:58 PM, Howard Rogers  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] postgres-8.4SS, pg_dump from macosx-10.6 has "ssl handshake error" 26% in

[Sachin Srivastava]

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 , the Enterprise Postgres 
 company.

Re: [GENERAL] Comparison of Oracle and PostgreSQL full text search

[Massa, Harald Armin]

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] Comparison of Oracle and PostgreSQL full text search

[Vincenzo Romano]

2010/7/28 Thomas Kellerer :
> 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