Re: [GENERAL] PostgreSQL versus MySQL for GPS Data

[Amitabh Kant]

Just to add to this list, I have been using Postgresql to store data
for multiple GPS applications handling more than 150-200 vehicles.
Some of the tables that I have are running into 20 - 25 million rows
at the max, and on average 10 million rows. I am yet to see a problem
from the database side, although must admit that I receive data every
10 seconds from the devices.

I am sure that optimizing the postgresql.conf files, and using postgis
would be of great help down the road.

With regards

Amitabh

-- 
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] Installation Error, Server Won't Start

[ray]

Raymond,

I used the pgInstaller on the Win2K box and it is working.  I
downloaded from a different mirror.  The installation ran a little
different.  Additionally, there seemed to be an issue with rights.
The installer setup a user which I accepted but it came back saying
that the rights weren't right (I don't remember the specifics).  So I
built a user and made it an administrator (the FAQ said it shouldn't
be an administrator), but the installer responded with a statement of
the effect that this user didn't have the correct rights.  So I
changed the user to a superuser.  The installer liked that.

But when the installation progressed to choosing languages, Python was
no longer shown as available.  I accepted the only one available and
proceded.  .  .  Not it works.

The problem is I need Python functionality for Trac.  Can I add it?
How do I get where I need to be?

Thanks,

Ray

-- 
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] Initializing variables from SQL query

[Adrian Klaver]

On Thursday 19 March 2009 5:25:03 pm jc_mich wrote:
> Hi all
>
> I'm new in PL/PGSQL developing, I'm trying to initialize a variable from a
> SQL query, this query counts some rows, I've made the next code, but it
> doesn't work.
>
> CREATE FUNCTION schema.myFunction() RETURNS BOOLEAN AS $$
> DECLARE
>   counter RECORD;
> BEGIN
> SELECT * INTO STRICT counter FROM (SELECT COUNT (DISTINCT (rows)) FROM
> schema.table);
>
> I'm pretty annoyed, because I've found some examples that use this kind of
> variable initialization.
>
> Is there someone who know what I did wrong?
>
> Thanks!
>
> --
> View this message in context:
> http://www.nabble.com/Initializing-variables-from-SQL-query-tp22612191p2261
>2191.html Sent from the PostgreSQL - general mailing list archive at
> Nabble.com.

What is the error you are getting?

-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Initializing variables from SQL query

[jc_mich]

Hi all

I'm new in PL/PGSQL developing, I'm trying to initialize a variable from a
SQL query, this query counts some rows, I've made the next code, but it
doesn't work.

CREATE FUNCTION schema.myFunction() RETURNS BOOLEAN AS $$
DECLARE
counter RECORD;
BEGIN
SELECT * INTO STRICT counter FROM (SELECT COUNT (DISTINCT (rows)) FROM
schema.table);

I'm pretty annoyed, because I've found some examples that use this kind of
variable initialization.

Is there someone who know what I did wrong?

Thanks!

-- 
View this message in context: 
http://www.nabble.com/Initializing-variables-from-SQL-query-tp22612191p22612191.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] PostgreSQL versus MySQL for GPS Data

[Merlin Moncure]

On Thu, Mar 19, 2009 at 11:50 AM, Scott Marlowe  wrote:
> On Tue, Mar 17, 2009 at 5:25 AM, Juan Pereira
>  wrote:
>> Hello,
>>
>> The question is: Which DBMS do you think is the best for this kind of
>> application? PostgreSQL or MySQL?
>
> Another advantage pgsql has is that many ddl operations on tables do
> NOT require exclusive locks on those tables.  Creating indexes, adding
> / dropping columns in mysql will lock the whole table and adding
> dropping columns will rewrite the whole table.  In pgsql adding and
> dropping columns is almost immediate, and you can create indexes
> concurrently so that the table you're creating the index on is not
> locked.  This is a big deal on a large production system where index
> creation could take anywhere from several minutes to several hours.
>
> Note that almost all ddl is transactable as well, so testing big
> schema changes is much safer in pgsql, where you can rollback just
> about anything except create / drop database / tablespace.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

This is the nicest feature about postgresql by far.  It almost
compensates the lack of in place upgrade.

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] Is there a meaningful benchmark?

[Greg Williamson]

Dann Corbit wrote:



> 
> Here is another interesting benchmark with a particular user's
> application:
> http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html
> 
> P.S.
> Oracle won't let you publish any benchmark numbers.
> So if you find an Oracle comparison, it's "unauthorized"

True enough. That said I feel comfortable in revealing that a former employer 
of mine ran some serious tests of PostgreSQL  vs Oracle (with an emphasis of 
postGIS and Oracle's equivalent) about a year and a half ago.

Oracle was consistently 5-15% faster depending on the precise benchmark. This 
was judged to be not worth the extra money for more Oracle licenses. In some 
other environments that edge might be worth the money. Oracle does spend 
resources on its products and so I don't find the speed difference surprising. 
But when you consider the speed with which Oracle produces patches vs. the 
Postgres folks the winner is clearly the latter.

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

Re: [GENERAL] array_agg and libpq(xx)

[Merlin Moncure]

2009/3/19 Grzegorz Jaśkiewicz :
> 2009/3/19 Merlin Moncure :
>
>> if you are moving arrays (and/or composites) into out of database
>> through libpq, check out libpqtypes:
>>
>> http://libpqtypes.esilo.com/
>
> Thanks, I will have to do it via libpqxx unfortunately. Which
> complicates matter a bit, since they don't expose libpq. (which for
> instance sux, because there's no equivalent of execParam() in pqxx,
> and prepared statements aren't always what I am aiming for..).

I see.  well, libpqxx is just a wrapper to libpq, same as libpqtypes.
Barring that, your best bet is to parse the string or unnest the array
in the query.

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] Is there a meaningful benchmark?

[John Cheng]

On Thu, Mar 19, 2009 at 3:23 PM, Scott Marlowe wrote:

> On Thu, Mar 19, 2009 at 3:26 PM, John Cheng 
> wrote:
> > Comparison between MySQL using the MyISAM engine with PostgreSQL is
> really
> > not sensible. For one, the MyISAM engine does not have transaction and
> > foreign key support, while PostgreSQL supports transaction and foreign
> key.
> > Would anyone really give up transaction and integrity for slightly more
> > performance?
>
> Sure, if the application fit.  If I have to load 100Meg files into a
> db, run some simple extraction on them, and output it back out, and
> can recreate all my data at the drop of a hat, then mysql / myisam
> might be a good match.


Right . There will be situations where MySQL with MyISAM will be a good fit.
My point was more that it doesn't make sense to simply compare "speed"
becuase other things needs to be taken into account.


>
> I am no a fan of MySQL, more because the company behind it seems to be
> lost and drifting than the db itself.  Bugs that are 5 years old
> finally getting fixed after Monty chided them in december?  Come on,
> PostgreSQL comes out with a near bug free new version every 1 to 2
> years.  PostgreSQL stomps bugs in hours or days that MySQL AB takes
> YEARS to fix, and then get reintroduced (see the order by on innodb
> indexed field debacle for that story) and I just don't trust the
> company or the db for anything complex.  But as a tool it has some
> uses that it's good enough at I could use it if I had to.
>



-- 
- John L Cheng

Re: [GENERAL] Is there a meaningful benchmark?

[ries van Twisk]

On Mar 19, 2009, at 4:50 PM, Will Rutherdale (rutherw) wrote:


It isn't actually possible at this stage for me to benchmark "the
application" because it doesn't yet exist.  There are a number of
potential projects floating around, with as yet unwritten
specifications, to run on different platforms ranging from embedded to
larger servers.  People just want to hear what I think is a good RDBMS
to use.  My opinion won't necessarily be followed.


If you name embedded... and you are even thinking of using MySQl,
don't forget you need to sell a MySQL license for each product you  
sell to a customer.


First know what your requirements are, then strip away the systems that
don't comply with your requirements. Then think of what sort of  
transactions
you require.. then check what RDBM might be good for you... for the  
couple that are

left over do the tests...

Ries




Nobody at this point is expecting the RDBMS to become a bottleneck, if
they are planning to actually use one at all.  However someone is sure
to ask the question, for an average application with an average
database, how is performance?

Even if such a question is answered, it isn't going to be the only
factor.  For example I have collected reasonable numbers already on
footprints of different RDBMSs, because embedded guys might find that
important if they're restricted to 64MB of flash.  On the other hand  
if
they went with some of the newer solid state drives with gigs of  
space,
then a few packages using 10s of MB wouldn't be such a problem any  
more.


In short, all bets are off and I'm just looking for baseline
information.  This is just a general feasibility and technology
exploration phase.

I'm aware of the limitations of hard numbers, but the more simple
information I have in different dimensions, the easier it is to  
convince

people not to lock in too early.

Thanks for the info, I'll check some of those references.

-Will


-Original Message-
From: Dann Corbit [mailto:dcor...@connx.com]
Sent: 19 March 2009 17:16
To: Will Rutherdale (rutherw); pgsql-general@postgresql.org
Subject: RE: [GENERAL] Is there a meaningful benchmark?

The only way to get an answer to a question like this is to actually
benchmark the application you have in mind.
And the answer won't be very good unless you have an expert on each
given system install and tune the application.

There is a regular benchmark that is run against the PostgreSQL
database.  I don't remember where to find the graphs.  Probably,  
someone

on the list can tell us the location.


Here are some benchmark figures:
http://tweakers.net/reviews/657/6
http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-2007
0606-00065.html
http://www.informationweek.com/news/software/linux/showArticle.jhtml?art
icleID=201001901
http://www.kaltenbrunner.cc/blog/index.php?/archives/21-guid.html
http://benchw.sourceforge.net/benchw_results_open3.html


P.S.
PostgreSQL seems to scale pretty well:
http://www.computerworld.com/action/article.do?command=viewArticleBasic&;
taxonomyId=18&articleId=9087918&intsrc=hm_topic


My opinion:
Most benchmarks are run by someone with an axe to grind.  I never
believe them.  The TPC benchmarks are probably the most trustworthy,
because they have to be certified.  But a fast TPC/whatever  
benchmark is

no guarantee that *your* application will run fast.  So if you want to
evaluation several different technologies do your own benchmark.  Do
your own calculations to find out the total cost of ownership over the
lifetime of the project.  Examine all the features that are available,
and what kind of technical support is possible.  Consider the impact  
of

product licensing.  What happens if you need to scale up to titanic
volume?  After you have thought all factors over very carefully, make
your choice.

If you rely on someone else to do the work for you, it's really  
begging
for trouble.  MySQL guys will show you why MySQL is faster.   
PostgreSQL
guys will show you why PostgreSQL is faster.  Oracle guys will show  
you

why Oracle is faster.  SQL*Server guys will show you why SQL*Server is
faster.  DB/2 guys will show you why DB/2 is faster.  Now, none of  
them
are lying (at least hopefully) but they are experts in their own  
domain

and not in the domain of the other product and they are also going to
choose those tight little corners where their product has the biggest
advantage.

IMO-YMMV.
P.S.
I'm a PostgreSQL fan and so I am likely to (perhaps unconsciously)  
favor

PostgreSQL in my remarks.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




regards, Ries van Twisk


-
Ries van Twisk
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS  
WebORB PostgreSQL DB-Architect

email: r...@vantwisk.nl
we

[GENERAL] Fulltext - multiple single column indexes

[esemba]

Hi,
I have table with several columns and need to perform fulltext search over
volatile number of columns. 
I can't use multicolumn gist index or gin index over concatenated columns,
so I've created several single column indexes (one for each column I want to
search) and now I need to query them like this:

to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
coalesce(resume, '')) || ...
@@ to_tsquery('cs', 'Query text');

This query works, but EXPLAIN has shown me, that postgres doesn't use the
indexes, so the query over a table with several thousands of records last
very long time. I've figured out, that indexes probably cannot be used this
way. What is a recommendation for this scenario?
Indexes over static number of columns work fine, but I can't use them,
because in my application logic I want to let user choose which columns to
search.

Thank you for your reply.
-- 
View this message in context: 
http://www.nabble.com/Fulltext---multiple-single-column-indexes-tp22611952p22611952.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Special charaters

[Alvaro Herrera]

Michael Higgins wrote:
> On Thu, 19 Mar 2009 12:28:11 -0500
> ries van Twisk  wrote:
> 
> > 
> > On Mar 19, 2009, at 11:53 AM, ANKITBHATNAGAR wrote:
> > 
> > >
> > > Hi
> > > This happens when I import csv file via my app into postgres.
> > > The csv file has some  “hello” from microsoft word 2003.
> > > In postgres it appears as �hello�
> > >
> > > Could somebody help on this?
> > 
> > Check your encodings.
> 
> I doubt that's it, but I've been wrong before... I say, Google for
> "smart quotes" and... good luck. (They don't belong to any encoding.)

Well, they must exist at least on utf8, otherwise they couldn't have
been pasted in the original message.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] Special charaters

[Michael Higgins]

On Thu, 19 Mar 2009 12:28:11 -0500
ries van Twisk  wrote:

> 
> On Mar 19, 2009, at 11:53 AM, ANKITBHATNAGAR wrote:
> 
> >
> > Hi
> > This happens when I import csv file via my app into postgres.
> > The csv file has some  “hello” from microsoft word 2003.
> > In postgres it appears as �hello�
> >
> > Could somebody help on this?
> 
> Check your encodings.
> 

I doubt that's it, but I've been wrong before... I say, Google for "smart 
quotes" and... good luck. (They don't belong to any encoding.)

Cheers,

-- 
 |\  /||   |  ~ ~  
 | \/ ||---|  `|` ?
 ||ichael  |   |iggins\^ /
 michael.higgins[at]evolone[dot]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] Is there a meaningful benchmark?

[Will Rutherdale (rutherw)]

This looks similar to things I've seen before.  MyISAM can be made to
look twice as fast as Postgres if the application is cooked to throw
away transaction processing, updates, and referential integrity, none of
which MyISAM seems to support well.

I plan to make a point of this to people, as I personally have
experience working with RDBMSs in the past and understand the importance
of these capabilities.  However not everyone I talk to will have any
experiences with databases and understand the issues.

That's why I was looking for a more balanced benchmark that exercises
said capabilities.

-Will


-Original Message-
From: Dann Corbit [mailto:dcor...@connx.com] 
Sent: 19 March 2009 18:26
To: Scott Marlowe; Will Rutherdale (rutherw)
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] Is there a meaningful benchmark?

Here is another interesting benchmark with a particular user's
application:
http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html

-- 
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] Is there a meaningful benchmark?

[Scott Marlowe]

On Thu, Mar 19, 2009 at 4:24 PM, Will Rutherdale (rutherw)
 wrote:
> Okay, you've given me some useful information.
>
> As the original subject line indicates, I'm open to the idea that no such 
> benchmark exists.
>
> If anyone asks about this stuff, I can just say that performance varies 
> widely by database and application, that Postgres performs well enough 
> against other RDBMSs, that Postgres is known to scale up well and make good 
> use of concurrency, and that I couldn't find any clear benchmark results to 
> back it up.
>
> Of course, if I *did* find any benchmark values then I could have used that 
> to dispel false rumours from the MySQL guys.  However it looks like simple 
> measured indicators aren't easy to come by.

Well, the tweakers benchmarks are a pretty good mysql pgsql
comparison, although getting older now.  Apparently mysql has improved
a lot of their concurrency issues that were uncovered in that one.
Pgsql has just continued to get faster.

Google MySQL gotchas for a list of reasons to avoid it.  There's a
pgsql gotcha list, it's shorter, and the gotchas are a lot less likely
to induce a case of the vapors.

-- 
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] array_agg and libpq(xx)

[Grzegorz Jaśkiewicz]

2009/3/19 John R Pierce :
>   pgResult = PQexec(pgConn, "select array['123','456','abc','def'];");
>   pgarray = PQgetvalue(pgResult,0,0);
>
> will return a pointer to the ARRAY in postgres format as defined by the
> typsend/recieve internal functions for the ARRAY type.   you would need to
> walk this and copy the strings comprising the array to your own managed
> storage (malloc, or whatever) prior to calling PQclear(pgResult).   I'm
> trying to find the docs on what the typsend format for ARRAY looks like and
> failing.

Clearly looking like libpqq(xx) are missing that functionality built
in to protocol/api...



-- 
GJ

-- 
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] array_agg and libpq(xx)

[Grzegorz Jaśkiewicz]

2009/3/19 Merlin Moncure :

> if you are moving arrays (and/or composites) into out of database
> through libpq, check out libpqtypes:
>
> http://libpqtypes.esilo.com/

Thanks, I will have to do it via libpqxx unfortunately. Which
complicates matter a bit, since they don't expose libpq. (which for
instance sux, because there's no equivalent of execParam() in pqxx,
and prepared statements aren't always what I am aiming for..).



-- 
GJ

-- 
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] Is there a meaningful benchmark?

[Dann Corbit]

Here is another interesting benchmark with a particular user's
application:
http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html

P.S.
Oracle won't let you publish any benchmark numbers.
So if you find an Oracle comparison, it's "unauthorized"


-- 
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] Maximum transaction rate

[Baron Schwartz]

I am jumping into this thread late, and maybe this has already been
stated clearly, but from my experience benchmarking, LVM does *not*
lie about fsync() on the servers I've configured.  An fsync() goes to
the physical device.  You can see it clearly by setting the write
cache on the RAID controller to write-through policy.  Performance
decreases to what the disks can do.

And my colleagues and clients have tested yanking the power plug and
checking that the data got to the RAID controller's battery-backed
cache, many many times.  In other words, the data is safe and durable,
even on LVM.

However, I have never tried to do this on volumes that span multiple
physical devices, because LVM can't take an atomic snapshot across
them, which completely negates the benefit of LVM for my purposes.  So
I always create one logical disk in the RAID controller, and then
carve that up with LVM, partitions, etc however I please.

I almost surely know less about this topic than anyone on this thread.

Baron

-- 
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] Is there a meaningful benchmark?

[Will Rutherdale (rutherw)]

Okay, you've given me some useful information.

As the original subject line indicates, I'm open to the idea that no such 
benchmark exists.

If anyone asks about this stuff, I can just say that performance varies widely 
by database and application, that Postgres performs well enough against other 
RDBMSs, that Postgres is known to scale up well and make good use of 
concurrency, and that I couldn't find any clear benchmark results to back it up.

Of course, if I *did* find any benchmark values then I could have used that to 
dispel false rumours from the MySQL guys.  However it looks like simple 
measured indicators aren't easy to come by.

-Will


-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com] 
Sent: 19 March 2009 18:14
To: Will Rutherdale (rutherw)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is there a meaningful benchmark?

On Thu, Mar 19, 2009 at 3:56 PM, Will Rutherdale (rutherw)
 wrote:
> I'm having trouble with the tweakers reference below.
>
> I was hoping to see something where hardware platform is held constant while 
> RDBMS is varied, but it seems to be just the opposite.  Or maybe I didn't 
> read the article the right way.

The tweakers test is actually VERY telling.  With a constant load, how
well do the database scale as you improve the hardware you've given
them to run on.  Hardware is cheap, downtime is not.  If you can toss
a 16 core server at a performance problem for $20,000 or so, that's
probably way cheaper than watching your main db chug under load and go
down twice a day.

Conversely, telling the bossman you need that 16 core server to
improve performance and seeing the new server collapse under load
faster than the old one due to poor concurrency is not gonna win you a
lot of brownie points.

-- 
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] Is there a meaningful benchmark?

[Scott Marlowe]

On Thu, Mar 19, 2009 at 3:26 PM, John Cheng  wrote:
> Comparison between MySQL using the MyISAM engine with PostgreSQL is really
> not sensible. For one, the MyISAM engine does not have transaction and
> foreign key support, while PostgreSQL supports transaction and foreign key.
> Would anyone really give up transaction and integrity for slightly more
> performance?

Sure, if the application fit.  If I have to load 100Meg files into a
db, run some simple extraction on them, and output it back out, and
can recreate all my data at the drop of a hat, then mysql / myisam
might be a good match.

I am no a fan of MySQL, more because the company behind it seems to be
lost and drifting than the db itself.  Bugs that are 5 years old
finally getting fixed after Monty chided them in december?  Come on,
PostgreSQL comes out with a near bug free new version every 1 to 2
years.  PostgreSQL stomps bugs in hours or days that MySQL AB takes
YEARS to fix, and then get reintroduced (see the order by on innodb
indexed field debacle for that story) and I just don't trust the
company or the db for anything complex.  But as a tool it has some
uses that it's good enough at I could use it if I had to.

-- 
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] Is there a meaningful benchmark?

[Scott Marlowe]

On Thu, Mar 19, 2009 at 4:11 PM, Will Rutherdale (rutherw)
 wrote:
> I am already aware of this issue, and am preparing to explain it to people.

Well, keep in mind that MOST people are gonna wave you off, and figure
it doesn't matter that much.  Lots of developers are pretty cavalier
with their user's data.  Now, if the data is easily replaceable, then
fine.  Put a price on the data.  Pay attention to that price, and
don't underestimate it.  It's usually the most expensive thing on a db
server.

> Having said that,  if it were possible to set up a reasonably average
> database, with a test application that hits it with a reasonable mix of
> select, insert, and update operations, and run it one at a time against
> different RDBMSs on the same machine, then it might yield some simple
> numbers that could be quoted to people in case they asked.

But will you be comparing apples to apples.  MySQL plays fast and
loose with data integrity (with myisam tables).  So at least make sure
you're comparing what you need to compare.  If data integrity is
important, you need to use innodb.  If it isn't, then just use flat
files.

> The goal is not to absolutely determine which is fastest in the made-up
> scenario, I don’t think anyone cares.  However it would be interesting to
> see if the different RDBMSs came in within a reasonable percentage of each
> other.

But again, if you're comparing a Go kart to a pickup truck, you need
to know that's what you're doing.  The abstract numbers mean little
outside of that fact.

> An analogy would be BogoMIPS.  Nobody takes it that seriously because they
> know there are numerous factors that affect how a machine runs under
> different applications.  But as a quick sanity check BogoMIPS can be useful
> at times.

Sorry, but I respectfully disagree.  Bogomips has the word bogo in it
on purpose.  It means, literally, almost nothing useful to the user.

Any modern db is fast enough for the kind of low level stuff you've
mentioned so far.  Until you have a better idea what your
application(s) might look like, it's hard to offer any real advice
besides "avoid myisam"

-- 
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] Is there a meaningful benchmark?

[Scott Marlowe]

On Thu, Mar 19, 2009 at 3:56 PM, Will Rutherdale (rutherw)
 wrote:
> I'm having trouble with the tweakers reference below.
>
> I was hoping to see something where hardware platform is held constant while 
> RDBMS is varied, but it seems to be just the opposite.  Or maybe I didn't 
> read the article the right way.

The tweakers test is actually VERY telling.  With a constant load, how
well do the database scale as you improve the hardware you've given
them to run on.  Hardware is cheap, downtime is not.  If you can toss
a 16 core server at a performance problem for $20,000 or so, that's
probably way cheaper than watching your main db chug under load and go
down twice a day.

Conversely, telling the bossman you need that 16 core server to
improve performance and seeing the new server collapse under load
faster than the old one due to poor concurrency is not gonna win you a
lot of brownie points.

-- 
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] Is there a meaningful benchmark?

[Will Rutherdale (rutherw)]

I am already aware of this issue, and am preparing to explain it to
people.

 

Having said that,  if it were possible to set up a reasonably average
database, with a test application that hits it with a reasonable mix of
select, insert, and update operations, and run it one at a time against
different RDBMSs on the same machine, then it might yield some simple
numbers that could be quoted to people in case they asked.

 

The goal is not to absolutely determine which is fastest in the made-up
scenario, I don't think anyone cares.  However it would be interesting
to see if the different RDBMSs came in within a reasonable percentage of
each other.

 

An analogy would be BogoMIPS.  Nobody takes it that seriously because
they know there are numerous factors that affect how a machine runs
under different applications.  But as a quick sanity check BogoMIPS can
be useful at times.

 

-Will

 

 



From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John Cheng
Sent: 19 March 2009 17:27
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is there a meaningful benchmark?

 

Comparison between MySQL using the MyISAM engine with PostgreSQL is
really not sensible. For one, the MyISAM engine does not have
transaction and foreign key support, while PostgreSQL supports
transaction and foreign key. Would anyone really give up transaction and
integrity for slightly more performance?

Re: [GENERAL] Is there a meaningful benchmark?

[Scott Marlowe]

On Thu, Mar 19, 2009 at 4:02 PM, Will Rutherdale (rutherw)
 wrote:
> I am *not* primarily interested in embedded, but I know people who are, and I 
> have already compared with SQLite.
>
> My main point of concern right now is for more middle sized platforms (such 
> as an average workstation), to be able to answer the question of how Postgres 
> shows in transactions per second against another RDBMS or two.
>

Way back right after the earth's crust had cooled I deployed a
corporate intranet on pgsql 6.5.2.  I believe we had Wooly Mammoth for
dinner that night.  It took a fair bit of work to keep that machine
happy, and every new version was an eye opener in terms of
performance, reliability, and capability improvements.  By the time
8.0 came out I was more than prepared to use it for some pretty hefty
work.  Now that 8.3 is out and 8.4 is out, for any kind of
intermediate size application (thousands of users daily, hundreds of
gigs of data) I'll put PostgreSQL against any other DBMS and expect it
to do well.

Where I work now, we handle 1.5 million or so users, a large chunk of
which log in each day, several times a day.  We had to beef up our
servers to 8 core / 16 drive machines (two of them) to handle the load
reliably.  We have enough spare capacity to handle about 3 to 4 times
the number of users we now have.

You are far more likely to be bitten by lack of familiarity with ANY
db you choose, and the real issue will be support and training.
Oracle, Pgsql, DB2, Interbase / Firebird, MySQL w/ innodb can all
handle the kind of load you've kind of hand waved about here.  How
much money and time you'll spend setting them up, learning them,
supporting them, and using them will be far more important than
anything a benchmark is likely to tell you right now.

-- 
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] Is there a meaningful benchmark?

[Will Rutherdale (rutherw)]

I'm having trouble with the tweakers reference below.

I was hoping to see something where hardware platform is held constant while 
RDBMS is varied, but it seems to be just the opposite.  Or maybe I didn't read 
the article the right way.

-Will


-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com] 
Sent: 19 March 2009 17:36
To: Dann Corbit
Cc: Will Rutherdale (rutherw); pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is there a meaningful benchmark?

On Thu, Mar 19, 2009 at 3:16 PM, Dann Corbit  wrote:
> Here are some benchmark figures:
> http://tweakers.net/reviews/657/6

SNIP

> My opinion:
> Most benchmarks are run by someone with an axe to grind.  I never
> believe them.

Generally I agree with that sentiment.  However, it's interesting that
the guys at tweakers.net run (or ran?) their chat board on mysql, and
expected it to win.  They certainly didn't expect the kind of stomping
that postgresql gave to mysql, so I look at that benchmark as a
"statement against interest" kind of thing.  They were genuinely
surprised that postgresql didn't just win, but behaved much much
better than mysql under heavier loads.

-- 
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] Is there a meaningful benchmark?

[Dann Corbit]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Scott Marlowe
> Sent: Thursday, March 19, 2009 2:57 PM
> To: Will Rutherdale (rutherw)
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Is there a meaningful benchmark?
> 
> On Thu, Mar 19, 2009 at 3:50 PM, Will Rutherdale (rutherw)
>  wrote:
> > Even if such a question is answered, it isn't going to be the only
> > factor.  For example I have collected reasonable numbers already on
> > footprints of different RDBMSs, because embedded guys might find that
> > important if they're restricted to 64MB of flash.  On the other hand
> if
> > they went with some of the newer solid state drives with gigs of
> space,
> > then a few packages using 10s of MB wouldn't be such a problem any
> more.
> 
> If you're looking at embedded usage, and footprint is an issue (it
> usually is even if you think it won't be) look at sqllite.  Pretty
> good embedded db and lightweight.  Pgsql is not intended to compete in
> the embedded space.

FastDB is another good option there (it's a portable, embedded memory mapped 
database):
http://www.garret.ru/fastdb.html

An advantage for the SQLite option is that the grammar is a subset of 
PostgreSQL grammar, so if you need to scale up, you have a ready path.


-- 
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] Is there a meaningful benchmark?

[Will Rutherdale (rutherw)]

I am *not* primarily interested in embedded, but I know people who are, and I 
have already compared with SQLite.  

My main point of concern right now is for more middle sized platforms (such as 
an average workstation), to be able to answer the question of how Postgres 
shows in transactions per second against another RDBMS or two.  

-Will


-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com] 
Sent: 19 March 2009 17:57
To: Will Rutherdale (rutherw)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is there a meaningful benchmark?

On Thu, Mar 19, 2009 at 3:50 PM, Will Rutherdale (rutherw)
 wrote:
> Even if such a question is answered, it isn't going to be the only
> factor.  For example I have collected reasonable numbers already on
> footprints of different RDBMSs, because embedded guys might find that
> important if they're restricted to 64MB of flash.  On the other hand if
> they went with some of the newer solid state drives with gigs of space,
> then a few packages using 10s of MB wouldn't be such a problem any more.

If you're looking at embedded usage, and footprint is an issue (it
usually is even if you think it won't be) look at sqllite.  Pretty
good embedded db and lightweight.  Pgsql is not intended to compete in
the embedded space.

-- 
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] Is there a meaningful benchmark?

[Scott Marlowe]

On Thu, Mar 19, 2009 at 3:50 PM, Will Rutherdale (rutherw)
 wrote:
> Even if such a question is answered, it isn't going to be the only
> factor.  For example I have collected reasonable numbers already on
> footprints of different RDBMSs, because embedded guys might find that
> important if they're restricted to 64MB of flash.  On the other hand if
> they went with some of the newer solid state drives with gigs of space,
> then a few packages using 10s of MB wouldn't be such a problem any more.

If you're looking at embedded usage, and footprint is an issue (it
usually is even if you think it won't be) look at sqllite.  Pretty
good embedded db and lightweight.  Pgsql is not intended to compete in
the embedded space.

-- 
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] Is there a meaningful benchmark?

[Will Rutherdale (rutherw)]

It isn't actually possible at this stage for me to benchmark "the
application" because it doesn't yet exist.  There are a number of
potential projects floating around, with as yet unwritten
specifications, to run on different platforms ranging from embedded to
larger servers.  People just want to hear what I think is a good RDBMS
to use.  My opinion won't necessarily be followed.

Nobody at this point is expecting the RDBMS to become a bottleneck, if
they are planning to actually use one at all.  However someone is sure
to ask the question, for an average application with an average
database, how is performance?

Even if such a question is answered, it isn't going to be the only
factor.  For example I have collected reasonable numbers already on
footprints of different RDBMSs, because embedded guys might find that
important if they're restricted to 64MB of flash.  On the other hand if
they went with some of the newer solid state drives with gigs of space,
then a few packages using 10s of MB wouldn't be such a problem any more.

In short, all bets are off and I'm just looking for baseline
information.  This is just a general feasibility and technology
exploration phase.

I'm aware of the limitations of hard numbers, but the more simple
information I have in different dimensions, the easier it is to convince
people not to lock in too early.

Thanks for the info, I'll check some of those references.

-Will


-Original Message-
From: Dann Corbit [mailto:dcor...@connx.com] 
Sent: 19 March 2009 17:16
To: Will Rutherdale (rutherw); pgsql-general@postgresql.org
Subject: RE: [GENERAL] Is there a meaningful benchmark?

The only way to get an answer to a question like this is to actually
benchmark the application you have in mind.
And the answer won't be very good unless you have an expert on each
given system install and tune the application.

There is a regular benchmark that is run against the PostgreSQL
database.  I don't remember where to find the graphs.  Probably, someone
on the list can tell us the location.


Here are some benchmark figures:
http://tweakers.net/reviews/657/6
http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-2007
0606-00065.html
http://www.informationweek.com/news/software/linux/showArticle.jhtml?art
icleID=201001901
http://www.kaltenbrunner.cc/blog/index.php?/archives/21-guid.html
http://benchw.sourceforge.net/benchw_results_open3.html


P.S.
PostgreSQL seems to scale pretty well:
http://www.computerworld.com/action/article.do?command=viewArticleBasic&;
taxonomyId=18&articleId=9087918&intsrc=hm_topic


My opinion:
Most benchmarks are run by someone with an axe to grind.  I never
believe them.  The TPC benchmarks are probably the most trustworthy,
because they have to be certified.  But a fast TPC/whatever benchmark is
no guarantee that *your* application will run fast.  So if you want to
evaluation several different technologies do your own benchmark.  Do
your own calculations to find out the total cost of ownership over the
lifetime of the project.  Examine all the features that are available,
and what kind of technical support is possible.  Consider the impact of
product licensing.  What happens if you need to scale up to titanic
volume?  After you have thought all factors over very carefully, make
your choice.

If you rely on someone else to do the work for you, it's really begging
for trouble.  MySQL guys will show you why MySQL is faster.  PostgreSQL
guys will show you why PostgreSQL is faster.  Oracle guys will show you
why Oracle is faster.  SQL*Server guys will show you why SQL*Server is
faster.  DB/2 guys will show you why DB/2 is faster.  Now, none of them
are lying (at least hopefully) but they are experts in their own domain
and not in the domain of the other product and they are also going to
choose those tight little corners where their product has the biggest
advantage.

IMO-YMMV.
P.S.
I'm a PostgreSQL fan and so I am likely to (perhaps unconsciously) favor
PostgreSQL in my remarks.


-- 
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] array_agg and libpq(xx)

[Merlin Moncure]

2009/3/19 Grzegorz Jaśkiewicz :
> Hi,
>
> Anyone here passed array to C/c++ code via libpq(xx) ??
> I need to pass on an array of strings, some of them might contain coma symbol.
> I am wondering, if there's anything already in libpq(xx) that would
> chop value into an array of char* , or will it just return a string
> and it is up to user to chop it.
>

if you are moving arrays (and/or composites) into out of database
through libpq, check out libpqtypes:

http://libpqtypes.esilo.com/

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] Is there a meaningful benchmark?

[Scott Marlowe]

On Thu, Mar 19, 2009 at 3:16 PM, Dann Corbit  wrote:
> Here are some benchmark figures:
> http://tweakers.net/reviews/657/6

SNIP

> My opinion:
> Most benchmarks are run by someone with an axe to grind.  I never
> believe them.

Generally I agree with that sentiment.  However, it's interesting that
the guys at tweakers.net run (or ran?) their chat board on mysql, and
expected it to win.  They certainly didn't expect the kind of stomping
that postgresql gave to mysql, so I look at that benchmark as a
"statement against interest" kind of thing.  They were genuinely
surprised that postgresql didn't just win, but behaved much much
better than mysql under heavier loads.

-- 
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] Is there a meaningful benchmark?

[John Cheng]

Comparison between MySQL using the MyISAM engine with PostgreSQL is really
not sensible. For one, the MyISAM engine does not have transaction and
foreign key support, while PostgreSQL supports transaction and foreign key.
Would anyone really give up transaction and integrity for slightly more
performance?

So if it makes your research easier, you can rule MySQL w/MyISAM out as an
option.

On Thu, Mar 19, 2009 at 1:32 PM, Will Rutherdale (rutherw) <
ruth...@cisco.com> wrote:

> Hi.  I'm writing up a database comparison paper in my department at
> work, with Postgres being a major candidate.  I have been attempting to
> research various issues and provide a meaningful comparison.
>
> One issue I would like to give some kind of information on is
> comparative performance.  When I look for such things in Google or
> Wikipedia or the pgsql email archives, it's hard to find anything
> reasonably definitive.  I've found isolated claims and anecdotes here
> and there, and a fellow on the list here who attempted to do a
> comparison between Postgres, MySQL, and Oracle but gave it up for now.
>
> Some of the claims I've seen said that in some cases MySQL with MyISAM
> ran 2x faster than Postgres, but that may have been for a special case
> with only read access to the database;  whereas another one claimed that
> MySQL with InnoDB was slower than Postgres.  Other people commented that
> it depends on how you tune the databases.
>
> Maybe there's nothing definitive out there.  However I'd like to get a
> ballpark idea of how some databases compare, using some kind of average
> case schema and application, in terms of transactions per second, on a
> common hardware platform.  I would like to be able to point to a
> reasonable reference, rather than engaging in handwaving myself.
>
> Does anyone know where I could look?
>
> -Will
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
- John L Cheng

Re: [GENERAL] Is there a meaningful benchmark?

[Dann Corbit]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Will Rutherdale (rutherw)
> Sent: Thursday, March 19, 2009 1:32 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Is there a meaningful benchmark?
> 
> Hi.  I'm writing up a database comparison paper in my department at
> work, with Postgres being a major candidate.  I have been attempting
to
> research various issues and provide a meaningful comparison.
> 
> One issue I would like to give some kind of information on is
> comparative performance.  When I look for such things in Google or
> Wikipedia or the pgsql email archives, it's hard to find anything
> reasonably definitive.  I've found isolated claims and anecdotes here
> and there, and a fellow on the list here who attempted to do a
> comparison between Postgres, MySQL, and Oracle but gave it up for now.
> 
> Some of the claims I've seen said that in some cases MySQL with MyISAM
> ran 2x faster than Postgres, but that may have been for a special case
> with only read access to the database;  whereas another one claimed
> that
> MySQL with InnoDB was slower than Postgres.  Other people commented
> that
> it depends on how you tune the databases.
> 
> Maybe there's nothing definitive out there.  However I'd like to get a
> ballpark idea of how some databases compare, using some kind of
average
> case schema and application, in terms of transactions per second, on a
> common hardware platform.  I would like to be able to point to a
> reasonable reference, rather than engaging in handwaving myself.
> 
> Does anyone know where I could look?

The only way to get an answer to a question like this is to actually
benchmark the application you have in mind.
And the answer won't be very good unless you have an expert on each
given system install and tune the application.

There is a regular benchmark that is run against the PostgreSQL
database.  I don't remember where to find the graphs.  Probably, someone
on the list can tell us the location.


Here are some benchmark figures:
http://tweakers.net/reviews/657/6
http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-2007
0606-00065.html
http://www.informationweek.com/news/software/linux/showArticle.jhtml?art
icleID=201001901
http://www.kaltenbrunner.cc/blog/index.php?/archives/21-guid.html
http://benchw.sourceforge.net/benchw_results_open3.html


P.S.
PostgreSQL seems to scale pretty well:
http://www.computerworld.com/action/article.do?command=viewArticleBasic&;
taxonomyId=18&articleId=9087918&intsrc=hm_topic


My opinion:
Most benchmarks are run by someone with an axe to grind.  I never
believe them.  The TPC benchmarks are probably the most trustworthy,
because they have to be certified.  But a fast TPC/whatever benchmark is
no guarantee that *your* application will run fast.  So if you want to
evaluation several different technologies do your own benchmark.  Do
your own calculations to find out the total cost of ownership over the
lifetime of the project.  Examine all the features that are available,
and what kind of technical support is possible.  Consider the impact of
product licensing.  What happens if you need to scale up to titanic
volume?  After you have thought all factors over very carefully, make
your choice.

If you rely on someone else to do the work for you, it's really begging
for trouble.  MySQL guys will show you why MySQL is faster.  PostgreSQL
guys will show you why PostgreSQL is faster.  Oracle guys will show you
why Oracle is faster.  SQL*Server guys will show you why SQL*Server is
faster.  DB/2 guys will show you why DB/2 is faster.  Now, none of them
are lying (at least hopefully) but they are experts in their own domain
and not in the domain of the other product and they are also going to
choose those tight little corners where their product has the biggest
advantage.

IMO-YMMV.
P.S.
I'm a PostgreSQL fan and so I am likely to (perhaps unconsciously) favor
PostgreSQL in my remarks.


-- 
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] array_agg and libpq(xx)

[John R Pierce]

Grzegorz Jaśkiewicz wrote:

Hi,

Anyone here passed array to C/c++ code via libpq(xx) ??
  


Do you mean, fetching the output of a query like

   SELECT ARRAY['123','456','abc','def'];

?


I need to pass on an array of strings, some of them might contain coma symbol.
I am wondering, if there's anything already in libpq(xx) that would
chop value into an array of char* , or will it just return a string
and it is up to user to chop it.
  


   pgResult = PQexec(pgConn, "select array['123','456','abc','def'];");
   pgarray = PQgetvalue(pgResult,0,0);

will return a pointer to the ARRAY in postgres format as defined by the 
typsend/recieve internal functions for the ARRAY type.   you would need 
to walk this and copy the strings comprising the array to your own 
managed storage (malloc, or whatever) prior to calling 
PQclear(pgResult).   I'm trying to find the docs on what the typsend 
format for ARRAY looks like and failing.





--
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] Installation Error, Server Won't Start

[ray]

On Mar 19, 2:04 pm, r...@iol.ie ("Raymond O'Donnell") wrote:
> On 18/03/2009 22:22, ray wrote:
>
> > I do not see an opportunity to request a log to be written.  From the
> > Psotgre site, I downloaded:
> > postgresql-8.3.7-1-windows.exe
>
> Ah, I see - that's the one-click installer, which is maintained by
> EnterpriseDB. I haven't used that one; I was thinking of the community
> installer (pgInstaller), and that's the one has the logging option.
>
> Ray.
>
> --
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> r...@iol.ie
> Galway Cathedral Recitals:http://www.galwaycathedral.org/recitals
> --
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-general

Raymond,

I have tried it again with system changes I thought might help but
there was no change in the response.  I have included some sections
for the log, below.

The last warning message was:
Service 'PostgreSQL Database Server 8.3' (pgsql-8.3) failed to start.
Verify that you have sufficient privileges to start system services.

Since I can use a variety of system applications that start/stop
enable/automate/manual any of the system services so I don't think it
is a rights issue.

Here is the first part of the log:
=== Verbose logging started: 3/19/2009  14:48:13  Build type: SHIP
UNICODE 3.01.4000.2435  Calling process: C:\WINDOWS
\system32\msiexec.exe ===
MSI (c) (88:90) [14:48:13:707]: Resetting cached policy values
MSI (c) (88:90) [14:48:13:707]: Machine policy value 'Debug' is 0
MSI (c) (88:90) [14:48:13:707]: *** RunEngine:
   *** Product: C:\PostgreSQL\8.3.7\temp\postgresql-8.3-
int.msi
   *** Action:
   *** CommandLine: **
MSI (c) (88:90) [14:48:13:707]: Machine policy value
'DisableUserInstalls' is 0
MSI (c) (88:90) [14:48:18:314]: SOFTWARE RESTRICTION POLICY: Verifying
package --> 'C:\PostgreSQL\8.3.7\temp\postgresql-8.3-int.msi' against
software restriction policy
MSI (c) (88:90) [14:48:18:314]: Note: 1: 2262 2: DigitalSignature 3:
-2147287038
MSI (c) (88:90) [14:48:18:314]: SOFTWARE RESTRICTION POLICY: C:
\PostgreSQL\8.3.7\temp\postgresql-8.3-int.msi is not digitally signed
MSI (c) (88:90) [14:48:18:330]: SOFTWARE RESTRICTION POLICY: C:
\PostgreSQL\8.3.7\temp\postgresql-8.3-int.msi is permitted to run at
the 'unrestricted' authorization level.
MSI (c) (88:90) [14:48:18:408]: Cloaking enabled.
MSI (c) (88:90) [14:48:18:408]: Attempting to enable all disabled
priveleges before calling Install on Server
MSI (c) (88:90) [14:48:18:424]: End dialog not enabled
MSI (c) (88:90) [14:48:18:424]: Original package ==> C:\PostgreSQL
\8.3.7\temp\postgresql-8.3-int.msi
MSI (c) (88:90) [14:48:18:424]: Package we're running from ==> C:
\DOCUME~1\rjoseph\LOCALS~1\Temp\c2d01.msi
MSI (c) (88:90) [14:48:18:424]: APPCOMPAT: looking for appcompat
database entry with ProductCode '{B823632F-3B72-4514-8861-
B961CE263224}'.
MSI (c) (88:90) [14:48:18:424]: APPCOMPAT: no matching ProductCode
found in database.
MSI (c) (88:90) [14:48:18:424]: MSCOREE not loaded loading copy from
system32
MSI (c) (88:90) [14:48:18:424]: Machine policy value
'TransformsSecure' is 0
MSI (c) (88:90) [14:48:18:424]: User policy value 'TransformsAtSource'
is 0
MSI (c) (88:90) [14:48:18:424]: Machine policy value 'DisablePatch' is
0
MSI (c) (88:90) [14:48:18:424]: Machine policy value
'AllowLockdownPatch' is 0
MSI (c) (88:90) [14:48:18:424]: Machine policy value
'DisableLUAPatching' is 0
MSI (c) (88:90) [14:48:18:424]: Machine policy value
'DisableFlyWeightPatching' is 0
MSI (c) (88:90) [14:48:18:440]: APPCOMPAT: looking for appcompat
database entry with ProductCode '{B823632F-3B72-4514-8861-
B961CE263224}'.
MSI (c) (88:90) [14:48:18:440]: APPCOMPAT: no matching ProductCode
found in database.
MSI (c) (88:90) [14:48:18:440]: Transforms are not secure.
MSI (c) (88:90) [14:48:18:440]: Command Line: INTERNALLAUNCH=1
INSTALLLANG=en CURRENTDIRECTORY=C:\WINDOWS\system32 CLIENTUILEVEL=0
CLIENTPROCESSID=2952
MSI (c) (88:90) [14:48:18:440]: PROPERTY CHANGE: Adding PackageCode
property. Its value is '{FAD54106-3052-4D5E-9185-E64713E9368E}'.
MSI (c) (88:90) [14:48:18:440]: Product Code passed to
Engine.Initialize:   ''
MSI (c) (88:90) [14:48:18:440]: Product Code from property table
before transforms: '{B823632F-3B72-4514-8861-B961CE263224}'
MSI (c) (88:90) [14:48:18:440]: Product Code from property table after
transforms:  '{B823632F-3B72-4514-8861-B961CE263224}'
MSI (c) (88:90) [14:48:18:440]: Product not registered: beginning
first-time install

. . .

In this portion of the log, there is a mention of an error but I can't
tell what the conclusion was:
Action start 14:48:18: GetAvailableLocales.
MSI (c) (88:38) [14:48:18:487]: Invoking remote custom action. DLL: C:
\DOCUME~1\rjoseph\LOCALS~1\Temp\

[GENERAL] Is there a meaningful benchmark?

[Will Rutherdale (rutherw)]

Hi.  I'm writing up a database comparison paper in my department at
work, with Postgres being a major candidate.  I have been attempting to
research various issues and provide a meaningful comparison.

One issue I would like to give some kind of information on is
comparative performance.  When I look for such things in Google or
Wikipedia or the pgsql email archives, it's hard to find anything
reasonably definitive.  I've found isolated claims and anecdotes here
and there, and a fellow on the list here who attempted to do a
comparison between Postgres, MySQL, and Oracle but gave it up for now.

Some of the claims I've seen said that in some cases MySQL with MyISAM
ran 2x faster than Postgres, but that may have been for a special case
with only read access to the database;  whereas another one claimed that
MySQL with InnoDB was slower than Postgres.  Other people commented that
it depends on how you tune the databases.

Maybe there's nothing definitive out there.  However I'd like to get a
ballpark idea of how some databases compare, using some kind of average
case schema and application, in terms of transactions per second, on a
common hardware platform.  I would like to be able to point to a
reasonable reference, rather than engaging in handwaving myself.

Does anyone know where I could look?

-Will


-- 
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] Changing pg_type records

[Tom Lane]

"Rob Richardson"  writes:
> I just discovered the existence of a couple of functions I don't
> understand in one customer's PostgreSQL database:

What do the substitute output functions do differently?

> UPDATE pg_type SET typoutput='c_textout'WHERE typname='SET';

This one has been a no-op since 7.4, because there is no longer any such
row in pg_type; and I'm pretty sure the row was vestigial long before that.
If the underlying code is old enough that it was designed when changing
that row did something interesting, it certainly all needs a fresh look
anyway...
 
> Are these functions really as innocuous as he claims?  It seems to me
> that between the time c_mode() is called and the time that pg_mode() is
> called, any other database access is likely to be confused.  

Yes, you definitely are taking big risks there.  Aside from the question
of how data appears to the client, this could bollix the behavior of
most PL languages too.

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] Installation Error, Server Won't Start

[ray]

On Mar 19, 2:04 pm, r...@iol.ie ("Raymond O'Donnell") wrote:
> On 18/03/2009 22:22, ray wrote:
>
> > I do not see an opportunity to request a log to be written.  From the
> > Psotgre site, I downloaded:
> > postgresql-8.3.7-1-windows.exe
>
> Ah, I see - that's the one-click installer, which is maintained by
> EnterpriseDB. I haven't used that one; I was thinking of the community
> installer (pgInstaller), and that's the one has the logging option.
>
> Ray.
>
> --
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> r...@iol.ie
> Galway Cathedral Recitals:http://www.galwaycathedral.org/recitals
> --
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-general

Raymond,

The pgInstaller does more (I don't know what) such that I now get an
entry on the start menu and the Control Panel Add/Remove listing.

However, this install did not work either.  When it tried to start the
db, it couldn't and then stated that the process failed and then
started backing out files.  It did leave a log file.  When I went to
Control Panel to remove it, a window stated that it must have already
been removed and asked if I wanted to just remove it from the Control
Panel listed which I did.

After looking at the log file, I saw that it was reaching into my Local
\Internet\temp folder to get things.  So I cleaned this out and will
try to start again and if there are still problems, I will post
portions of the log.

Thanks,
Ray

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Determining PUBLIC's permissions

[Roderick A. Anderson]

I'm trying to determine the permissions PUBLIC has on several 
tables/views.  Or would this be the default permissions for a table/view?


pg_user, pg_roles, pg_group, pg_authid, pg_auth_members, pg_database, 
pg_tablespace, and pg_settings.


\dp pg_user

returns (0 rows).

I am logged in to template1 as postgres.

I am doing some testing of multi-tenant/shared-database-hosting options 
and want to try to get around the possible limitations mentioned in the 
article at . 
After revoking from public I want to grant those permission back to the 
'main' database user.  Need to know what they are.


I was sure there was a thread recently on determining table permissions 
and thought the \dp would do it.


Any suggestions?


\\||/
Rod
--




--
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] Maximum transaction rate

[Ron Mayer]

Marco Colombo wrote:
> Yes, but we knew it already, didn't we? It's always been like
> that, with IDE disks and write-back cache enabled, fsync just
> waits for the disk reporting completion and disks lie about

I've looked hard, and I have yet to see a disk that lies.

ext3, OTOH seems to lie.

IDE drives happily report whether they support write barriers
or not, which you can see with the command:
%hdparm -I /dev/hdf | grep FLUSH_CACHE_EXT
I've tested about a dozen drives, and I've never seen one
claims to support flushing that doesn't.  And I haven't seen
one that doesn't support it that was made less than half a
decade ago.  IIRC, ATA-5 specs from 2000 made supporting
this mandatory.

Linux kernels since 2005 or so check for this feature.  It'll
happily tell you which of your devices don't support it.
  %dmesg | grep 'disabling barriers'
  JBD: barrier-based sync failed on md1 - disabling barriers
And for devices that do, it will happily send IDE FLUSH CACHE
commands to IDE drives that support the feature.   At the same
time Linux kernels started sending the very similar. SCSI
SYNCHRONIZE CACHE commands.


> Anyway, it's the block device job to control disk caches. A
> filesystem is just a client to the block device, it posts a
> flush request, what happens depends on the block device code.
> The FS doesn't talk to disks directly. And a write barrier is
> not a flush request, is a "please do not reorder" request.
> On fsync(), ext3 issues a flush request to the block device,
> that's all it's expected to do.

But AFAICT ext3 fsync() only tell the block device to
flush disk caches if the inode was changed.

Or, at least empirically if I modify a file and do
fsync(fd); on ext3 it does not wait until the disk
spun to where it's supposed to spin.   But if I put
a couple fchmod()'s right before the fsync() it does.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL technical Videos: Proteomic mining and Procedural language development

[Joshua D. Drake]

Hey folks,

At PostgreSQL Conference West 08, Josh Tolley did a great presentation
on how to write a procedural language and the SFPUG meeting in March had
a presentation on Unison which is a software used with Proteomic
mining. 

Below are the links to not only the video but also the slides. Of extra
interest is that he used LOLCode as the language.

How to write a PL:

Video:
http://www.vimeo.com/3728119

Slides:
http://www.slideshare.net/linuxpoet/developing-a-procedural-language-for-postgre-sql

Unison:
Video:
http://www.vimeo.com/3732938

Slides:
http://www.slideshare.net/linuxpoet/unison-ucsf-sfpug

Sincerely,

Joshua D. Drake


-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.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] DBD::Pg`s $dbh->func( "/path/to/file", `lo_import` ) fails silently

[Kynn Jones]

On Wed, Mar 18, 2009 at 3:57 PM, Greg Sabino Mullane wrote:

>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
>
>
> > I'm trying to use Perl's DBD::Pg module to import a file as a large
> object.
> > For this I'm using the following:
> > my $oid = $dbh->func( "/absolute/path/to/file", 'lo_import' );
>
> Works fine for me. What version of DBD::Pg are you using? Try something
> recent if you are using something old. You can also set the trace
> level to see what's going on behind the scenes:
>
> $dbh->trace(10)
>

Thanks for your reply.
Actually, after much trial and error I figured out the problem: the handle I
was using had AutoCommit set.  I'm new to the lo_* functions, and not aware
of this issue.  A mention of this in the DBD::Pg docs would be very helpful
to LO noobs like me.

Cheers,

Kynn

Re: [GENERAL] Installation Error, Server Won't Start

[Raymond O'Donnell]

On 18/03/2009 22:22, ray wrote:
> I do not see an opportunity to request a log to be written.  From the
> Psotgre site, I downloaded:
> postgresql-8.3.7-1-windows.exe

Ah, I see - that's the one-click installer, which is maintained by
EnterpriseDB. I haven't used that one; I was thinking of the community
installer (pgInstaller), and that's the one has the logging option.

Ray.


--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
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] Multiple natural joins

[Sam Mason]

On Thu, Mar 19, 2009 at 04:22:56PM +0200, Peter Eisentraut wrote:
> Joins nest from left to write, so
> 
> tbl_c NATURAL JOIN tbl_a NATURAL JOIN tbl_b
> 
> means
> 
> (tbl_c NATURAL JOIN tbl_a) NATURAL JOIN tbl_b
> 
> If you want a different order, you can set the parentheses differently, 
> with possibly different results.

When would you get different results?  As far as I can tell, for INNER
joins, you'd always get the same thing back for any ordering.  With
OUTER joins it obviously matters what's going on, but that's expected.

The simplest case would appear to be three tables:

  CREATE TABLE a ( a INTEGER );
  CREATE TABLE b ( b INTEGER );
  CREATE TABLE ab ( a INTEGER, b INTEGER );

It always seems possible to rewrite a set of NATURAL joins as a
conventional cross join; i.e. from:

  SELECT a.a, b.b
  FROM a NATURAL JOIN b NATURAL JOIN ab

into:

  SELECT a.a, b.b
  FROM a, b, ab
  WHERE a.a = ab.a
AND b.b = ab.b;

The order you happen to write the tables in either style doesn't seem
to matter.  What's nice with the NATURAL join is that the database
knows that "a" is unambiguous and doesn't complain as it would as it
would if I'd put an unqualified "a" in the second query.  This is just
syntactic-sugar and doesn't alter the semantics, at least not that I can
tell.

> The fact that this isn't entirely obvious only supports the argument 
> that natural joins shouldn't used.

It may not be obvious what's going on and I'd always recommend to
never use NATURAL joins but I think their semantics are quite easy
to understand.

They do require a *lot* of care when using them as it's easy to change
the semantics of existing queries by adding an inappropriately named
column to a table.  The reason I don't use NATURAL joins is because of
these non-local side effects (i.e. changing a table can cause seemingly
unrelated queries to suddenly stop working).  It's a similar reason why
GOTOs and global variables are frowned upon--it's just too easy to break
code accidentally.

-- 
  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] How to run a procedure

[Scott Marlowe]

On Thu, Mar 19, 2009 at 12:11 PM, jc_mich  wrote:
>
> Hi all
>
> I'm new developing procedures in PL/PGSQL. I'm using Query Tool as code
> editor, but execute query button only mark sintax errors and I want to run
> my application.

select myfunction(arg1, arg2);

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to run a procedure

[Pavel Stehule]

hello

2009/3/19 jc_mich :
>
> Hi all
>
> I'm new developing procedures in PL/PGSQL. I'm using Query Tool as code
> editor, but execute query button only mark sintax errors and I want to run
> my application.
>
> How can I test my function?
>

write
select nameofyourfunctions(parameters);

regards
Pavel Stehule

> Thanks!
> --
> View this message in context: 
> http://www.nabble.com/How-to-run-a-procedure-tp22606191p22606191.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to run a procedure

[jc_mich]

Hi all

I'm new developing procedures in PL/PGSQL. I'm using Query Tool as code
editor, but execute query button only mark sintax errors and I want to run
my application.

How can I test my function?

Thanks!
-- 
View this message in context: 
http://www.nabble.com/How-to-run-a-procedure-tp22606191p22606191.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] array_agg and libpq(xx)

[Grzegorz Jaśkiewicz]

Hi,

Anyone here passed array to C/c++ code via libpq(xx) ??
I need to pass on an array of strings, some of them might contain coma symbol.
I am wondering, if there's anything already in libpq(xx) that would
chop value into an array of char* , or will it just return a string
and it is up to user to chop it.

thanks.


-- 
GJ

-- 
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] postgreSQL & amazon ec2 cloud

[Ron Mayer]

Tom Lane wrote:
> Adrian Klaver  writes:
>> Nothing. I have created a Postgres instance on an EC2 virtual machine with 
>> attached EBS(Elastic Block Storage)..[...]
> 
> ... I wonder whether you have any guarantees about database consistency
> in that situation?  PG has some pretty strong requirements about fsync

While I agree it shouldn't be taken on faith, their documentation
does take the time to point out that syncing of I/O's is pretty
expensive operation (even literally - they charge extra for individual
i/o operations and point out that those increase with syncs).
http://aws.amazon.com/ebs/

I have a couple databases there, and based on the rather high I/O-wait
times on their cheapest ($0.10/hr) instances, I'm wildly guessing that
they're doing something reasonable for sync :-).  Their higher priced
instances supposedly have better I/O performance.

> behavior etc, and I'd not want to take it on faith that a cloud
> environment will meet those requirements.

That said, even apart from any SLA of fsync itself, I imagine there's
a concern that an entire hosted cloud might vanish for any number of
reasons.



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

[Adrian Klaver]

- glext...@gmail.com wrote:

> Hi--
> 
> 
> I'm having a bit of trouble with the EXECUTE statement for the
> following function.
> 
> 
> I saw a lot of traffic re. plpgsql & variable substitution for 8.4,
> but I'm convinced this is something simple (newbie):
> 
> 
> 
> CREATE FUNCTION topmixtot (int[]) RETURNS SETOF record AS $$
> DECLARE
> gids ALIAS FOR $1;
> tmpcnt int[];
> totalcnt integer;
> row topmixtot%ROWTYPE;
> BEGIN
> IF (gids IS NULL) THEN
> tmpcnt := array(SELECT sum(t2.cnt) FROM inst_grant t1,
> topics_given_grant t2 where t1.gid=t2.gid);
> ELSE
> tmpcnt := array(SELECT sum(cnt) FROM topics_given_grant WHERE gid =
> ANY ( gids ));
> END IF;
> totalcnt := tmpcnt[0];
> FOR row IN EXECUTE 'SELECT topics_given_grant.tid, ( 100 * (sum(cnt) /
> totalcnt ))::numeric as sumcnt FROM topics_given_grant, inst_grant

'SELECT topics_given_grant.tid, ( 100 * (sum(cnt) /'||quote_literal(totalcnt)||
 '))::numeric as sumcnt FROM topics_given_grant, inst_grant

See here for more detail:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

> WHERE inst_grant.gid=topics_given_grant.gid group by
> topics_given_grant.tid order by sumcnt DESC' LOOP
> RETURN NEXT row;
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
> 
> 
> ERROR msg.:
> 
> 
> 
> 
> db=# select count(*) from topmixtot('{5}') AS foo(tid int, cnt int);
> ERROR: column "totalcnt" does not exist
> LINE 1: ...SELECT topics_given_grant.tid, ( 100 * (sum(cnt) / totalcnt
> )...
> 
> 
> QUERY: SELECT topics_given_grant.tid, ( 100 * (sum(cnt) / totalcnt
> ))::numeric as sumcnt FROM topics_given_grant, nih_grant WHERE
> nih_grant.gid=topics_given_grant.gid group by topics_given_grant.tid
> order by sumcnt DESC
> CONTEXT: PL/pgSQL function "topmixtot" line 13 at FOR over EXECUTE
> statement
> 
> 
> How do I substitute in the value for 'totalcnt' in the EXECUTE SELECT
> statement?
> 
> 
> Thanks,
> 
> 
> Gerry


Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] variable

[glextact]

Hi--

I'm having a bit of trouble with the EXECUTE statement for the  
following function.


I saw a lot of traffic re. plpgsql & variable substitution for 8.4,  
but I'm convinced this is something simple (newbie):


CREATE FUNCTION topmixtot (int[]) RETURNS SETOF record AS $$
DECLARE
 gids ALIAS FOR $1;
 tmpcnt int[];
 totalcnt integer;
 row topmixtot%ROWTYPE;
BEGIN
IF (gids IS NULL) THEN
  tmpcnt := array(SELECT sum(t2.cnt) FROM inst_grant t1,  
topics_given_grant t2 where t1.gid=t2.gid);

ELSE
  tmpcnt := array(SELECT sum(cnt) FROM topics_given_grant WHERE gid =  
ANY ( gids ));

END IF;
totalcnt := tmpcnt[0];
FOR row IN EXECUTE 'SELECT topics_given_grant.tid, ( 100 * (sum(cnt) /  
totalcnt ))::numeric as sumcnt FROM topics_given_grant, inst_grant  
WHERE inst_grant.gid=topics_given_grant.gid group by  
topics_given_grant.tid order by sumcnt DESC' LOOP

  RETURN NEXT row;
END LOOP;
END;
$$ LANGUAGE plpgsql;

ERROR msg.:

db=# select count(*) from topmixtot('{5}') AS foo(tid int, cnt int);
ERROR:  column "totalcnt" does not exist
LINE 1: ...SELECT topics_given_grant.tid, ( 100 * (sum(cnt) /  
totalcnt )...



QUERY:  SELECT topics_given_grant.tid, ( 100 * (sum(cnt) /  
totalcnt ))::numeric as sumcnt FROM topics_given_grant, nih_grant  
WHERE nih_grant.gid=topics_given_grant.gid group by  
topics_given_grant.tid order by sumcnt DESC
CONTEXT:  PL/pgSQL function "topmixtot" line 13 at FOR over EXECUTE  
statement


How do I substitute in the value for 'totalcnt' in the EXECUTE SELECT  
statement?


Thanks,

Gerry

Re: [GENERAL] Special charaters

[ries van Twisk]

On Mar 19, 2009, at 11:53 AM, ANKITBHATNAGAR wrote:



Hi
This happens when I import csv file via my app into postgres.
The csv file has some  “hello” from microsoft word 2003.
In postgres it appears as �hello�

Could somebody help on this?


Check your encodings.

Ries


Ankit





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Special charaters

[ANKITBHATNAGAR]

Hi
This happens when I import csv file via my app into postgres.
The csv file has some  “hello” from microsoft word 2003.
In postgres it appears as �hello�

Could somebody help on this?

Ankit
-- 
View this message in context: 
http://www.nabble.com/Special-charaters-tp22604452p22604452.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] deadlock problem

[Pavel Stehule]

2009/3/19 Alvaro Herrera :
> Milos Findura wrote:
>> hi,
>> I found a deadlock on 2 queries, DELETE and REINDEX TABLE
>
> What version is this?
>

8.3

Milos is my colleague

regards
Pavel Stehule

>
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> 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] deadlock problem

[Alvaro Herrera]

Milos Findura wrote:
> hi,
> I found a deadlock on 2 queries, DELETE and REINDEX TABLE

What version is this?



-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] PostgreSQL versus MySQL for GPS Data

[Scott Marlowe]

On Tue, Mar 17, 2009 at 5:25 AM, Juan Pereira
 wrote:
> Hello,
>
> The question is: Which DBMS do you think is the best for this kind of
> application? PostgreSQL or MySQL?

Another advantage pgsql has is that many ddl operations on tables do
NOT require exclusive locks on those tables.  Creating indexes, adding
/ dropping columns in mysql will lock the whole table and adding
dropping columns will rewrite the whole table.  In pgsql adding and
dropping columns is almost immediate, and you can create indexes
concurrently so that the table you're creating the index on is not
locked.  This is a big deal on a large production system where index
creation could take anywhere from several minutes to several hours.

Note that almost all ddl is transactable as well, so testing big
schema changes is much safer in pgsql, where you can rollback just
about anything except create / drop database / tablespace.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] deadlock problem

[Milos Findura]

hi,
I found a deadlock on 2 queries, DELETE and REINDEX TABLE

DELETE waits for RowExclusiveLock on table address_jd
and
REINDEX waits for AccessExclusiveLock on PRIMARY KEY address_jd_pk

log:
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-1] 
host=10.0.5.144,user=ppstat,db=ppstat ERROR:  deadlock detected
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-2] 
host=10.0.5.144,user=ppstat,db=ppstat DETAIL:  Process 25576 waits for 
RowExclusiveLock on relation 16613 of database 16388;
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-3]  blocked by process 26649.
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-4] Process 26649 waits 
for AccessExclusiveLock on relation 17776 of database 16388; blocked by 
process 25576.
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-5] 
host=10.0.5.144,user=ppstat,db=ppstat CONTEXT:  SQL statement "DELETE FROM 
dsa_nas.address_jd WHERE exp_id = 7"
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-6] PL/pgSQL 
function "delete_export" line 16 at EXECUTE statement
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-7] SQL statement "SELECT  
transform_functions.delete_export('dsa_nas', $1 )"
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-8] PL/pgSQL 
function "transform_nas" line 152 at PERFORM
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-9] 
host=10.0.5.144,user=ppstat,db=ppstat STATEMENT:  SELECT 
transform_functions.transform_nas(7)
Mar 19 00:30:57 ch08bl09 postgres[26649]: [3-1] 
host=[local],user=postgres,db=ppstat LOG:  duration: 865522.949 ms  
statement: REINDEX TABLE dsa_nas.address_jd

where can be problem?

thanks, 
tashunko

-- 
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] (0x0000274D/10061) on Install

[Joachim Tranvåg]

Problem solved!

The sinner is probably windows defender.
Went to C:\Windows\system32\drivers\etc\ and opened the hosts file, and entered 
"127.0.0.1 localhost" at the bottom of the file, saved and rebooted pc.

Re: [GENERAL] Maximum transaction rate

[Joshua D. Drake]

Hello,

As a continued follow up to this thread, Tim Post replied on the LVM
list to this affect:

"
If a logical volume spans physical devices where write caching is
enabled, the results of fsync() can not be trusted. This is an issue
with device mapper, lvm is one of a few possible customers of DM.

Now it gets interesting:

Enter virtualization. When you have something like this:

fsync -> guest block device -> block tap driver -> CLVM -> iscsi ->
storage -> physical disk.

Even if device mapper passed along the write barrier, would it be
reliable? Is every part of that chain going to pass the same along, and
how many opportunities for re-ordering are presented in the above?

So, even if its fixed in DM, can fsync() still be trusted? I think, at
the least, more testing should be done with various configurations even
after a suitable patch to DM is merged. What about PGSQL users using
some kind of elastic hosting?

Given the craze in 'cloud' technology, its an important question to ask
(and research). 


Cheers,
--Tim
"

Joshua D. Drake

-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.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] PostgreSQL versus MySQL for GPS Data

[Shane Ambler]

Thomas Kellerer wrote:

Harald Armin Massa, 17.03.2009 15:00:
That is: what table size would you or anybody consider really, 
really large actually?


I recently attended and Oracle training by Tom Kyte and he said 
(partially joking though) that a database is only large when the size
is measured in terrabytes :) So really, really large would mean 
something like 100 petabytes



My personal opinion is that a "large" database has more than ~10 
million rows in more than ~10 tables.


Thomas



I would say that as far as GPS data goes the street maps of the world
would be pretty big.

openstreetmap.org is still a work in progress but their current db dumps
gzip down to 6.4GB. It was a while back that I noseyed around with it
but I do recall that it compressed well and was very large uncompressed.
Don't recall how many rows it contained.

I wonder what an almost complete world street map like google maps comes
in at?



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Comando USE

[Marcelo Giovane]

É possível alterar o database corrente para criar um schema nele sem uso do \c 
do psql?

Obrigado

MarceloG

Re: [GENERAL] Long-running query blocks all other queries

[Scott Marlowe]

On Thu, Mar 19, 2009 at 8:42 AM, Anton V. Belyaev
 wrote:

> I have 4-cored machine, so I expected the query to be able to run for
> a long time without performance degrade for application.

As Bill mentioned, this won't help a bit if you've got a slow / dodgy
/ overloaded IO subsystem.  What does vmstat 1 60 have to say about
the situation when the queries are running?

-- 
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] Long-running query blocks all other queries

[Bill Moran]

In response to "Anton V. Belyaev" :

> Hello,
> 
> I want to run a long running query in background to collect
> statistics. I.e. how many users played the game last month more than
> 50 times.
> 
> SELECT count(*) FROM (SELECT user_id, COUNT(*) AS total_games FROM
> games, game2user WHERE games.finished > '2009-02-19' AND games.id =
> game2user.game_id GROUP BY user_id) AS aaa WHERE aaa.total_games > 50;
> 
> I have 4-cored machine, so I expected the query to be able to run for
> a long time without performance degrade for application.
> 
> But the query has completely blocked all other queries. Even those,
> which are not related to tables, used by the long-running query.
> 
> Why does this block happen and how to aid it?

Off the top of my head, I can't think of any reason why that query
would block anything else.

Start with SELECT * FROM pg_locks; to see if there are actually any
locks occurring that could be causing problems.

If there are no explicit locks, you may be looking at IO starvation.
All the cores in the world won't help you if your disks are too slow
to feed the required information.  Have you investigated this
possibility yet?

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Long-running query blocks all other queries

[Anton V. Belyaev]

Hello,

I want to run a long running query in background to collect
statistics. I.e. how many users played the game last month more than
50 times.

SELECT count(*) FROM (SELECT user_id, COUNT(*) AS total_games FROM
games, game2user WHERE games.finished > '2009-02-19' AND games.id =
game2user.game_id GROUP BY user_id) AS aaa WHERE aaa.total_games > 50;

I have 4-cored machine, so I expected the query to be able to run for
a long time without performance degrade for application.

But the query has completely blocked all other queries. Even those,
which are not related to tables, used by the long-running query.

Why does this block happen and how to aid it?

Thanks.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Changing pg_type records

[Rob Richardson]

Greetings!
 
I just discovered the existence of a couple of functions I don't
understand in one customer's PostgreSQL database:
 
-- Function: c_mode()
 
-- DROP FUNCTION c_mode();
 
CREATE OR REPLACE FUNCTION c_mode()
  RETURNS text AS
$BODY$  UPDATE pg_type SET typoutput='c_textout'WHERE typname='SET';
 
  UPDATE pg_type SET typoutput='c_varcharout' WHERE
typname='bpchar';
 
  UPDATE pg_type SET typoutput='c_textout'WHERE typname='bytea';
 
  UPDATE pg_type SET typoutput='c_charout'WHERE typname='char';
 
  UPDATE pg_type SET typoutput='c_textout'WHERE typname='text';
 
  UPDATE pg_type SET typoutput='c_textout'WHERE
typname='unknown';
 
  UPDATE pg_type SET typoutput='c_varcharout' WHERE
typname='varchar';
 
  select 'c_mode'::text;$BODY$
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION c_mode() OWNER TO postgres;

-- Function: pg_mode()
 
-- DROP FUNCTION pg_mode();
 
CREATE OR REPLACE FUNCTION pg_mode()
  RETURNS text AS
$BODY$  UPDATE pg_type SET typoutput='textout'WHERE typname='SET';
 
  UPDATE pg_type SET typoutput='varcharout' WHERE typname='bpchar';
 
  UPDATE pg_type SET typoutput='textout'WHERE typname='bytea';
 
  UPDATE pg_type SET typoutput='charout'WHERE typname='char';
 
  UPDATE pg_type SET typoutput='textout'WHERE typname='text';
 
  UPDATE pg_type SET typoutput='textout'WHERE typname='unknown';
 
  UPDATE pg_type SET typoutput='varcharout' WHERE typname='varchar';
 
  select 'pg_mode'::text;$BODY$
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION pg_mode() OWNER TO postgres;

This customer has demanded extensive modifications to their database and
our application.  I think these functions are used in encryption, since
there are several other non-standard functions in this database that are
related to encryption.
 
Our lead developer is in another state, and is naturally very
uncommunicative.  When I asked him about the c_mode() function and
expressed concern that changing pg_type records might not be a good
idea, his reply was:
 
"Ignore it,  it is a database contrib routine for string I/O.   It
doesn't change the database properties."
 
Are these functions really as innocuous as he claims?  It seems to me
that between the time c_mode() is called and the time that pg_mode() is
called, any other database access is likely to be confused.  
 
Thank you very much.
 
RobR
 

Re: [GENERAL] Multiple natural joins

[Peter Eisentraut]

Thom Brown wrote:

SELECT tbl_a.location, tbl_b.language
FROM tbl_c
NATURAL INNER JOIN tbl_a
NATURAL INNER JOIN tbl_b

The confusion comes when 2 of those tables reference the 3rd table using 
the same column.


So are natural joins only allowed to join 2 tables?  If not, how can it 
be used for more than 1 table has links to the other tables?


The fact that the above example runs proves that natural joins are 
allowed with more than 2 tables.


Joins nest from left to write, so

tbl_c NATURAL JOIN tbl_a NATURAL JOIN tbl_b

means

(tbl_c NATURAL JOIN tbl_a) NATURAL JOIN tbl_b

and that means that the second join is executed as if the first join 
resulted in a normal table with normal columns with names as the case 
may be, and the natural join will use those names with all the consequences.


If you want a different order, you can set the parentheses differently, 
with possibly different results.


The fact that this isn't entirely obvious only supports the argument 
that natural joins shouldn't used.



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

[Milos Findura]

Not so, 
statements have diffrent pid. Delete is called in application, bud 
REINDEX is 
called via some cron admin script.

t.

On Thursday 19 of March 2009 13:35:03 Tom Lane wrote:
> Milos Findura  writes:
> > where can be problem?
>
> Presumably, one statement or the other is inside a transaction that
> already had a lower-grade lock on the target table.
>
>   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] deadlock problem

[Tom Lane]

Milos Findura  writes:
> where can be problem?

Presumably, one statement or the other is inside a transaction that
already had a lower-grade lock on the target table.

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] Multiple natural joins

[Scott Marlowe]

On Thu, Mar 19, 2009 at 5:24 AM, Thom Brown  wrote:
> The confusion comes when 2 of those tables reference the 3rd table using the
> same column.
>
> So are natural joins only allowed to join 2 tables?  If not, how can it be
> used for more than 1 table has links to the other tables?

Not sure how well natural joins work on > 2 tables, but they are to be
avoided in my opinion.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Multiple natural joins

[Thom Brown]

Hi,

I've read the PostgreSQL documentation on natural joins, but it only ever
shows it being used once.  Is it possible to use it more than once?

Say if I have the following 3 tables (this is a poor example by the way):

tbl_a

a_id serial NOT NULL
location text

tbl_b

b_id serial NOT NULL
language text

tbl_c

c_id serial NOT NULL
a_id int NOT NULL
b_id int NOT NULL

I could write:

SELECT tbl_a.location, tbl_b.language
FROM tbl_c
INNER JOIN tbl_a ON tbl_c.a_id = tbl_a.a_id
INNER JOIN tbl_b ON tbl_c.b_id = tbl_b.b_id;

But could I also write:

SELECT tbl_a.location, tbl_b.language
FROM tbl_c
NATURAL INNER JOIN tbl_a
NATURAL INNER JOIN tbl_b

The confusion comes when 2 of those tables reference the 3rd table using the
same column.

So are natural joins only allowed to join 2 tables?  If not, how can it be
used for more than 1 table has links to the other tables?

Thanks

Thom

[GENERAL] deadlock problem

[Milos Findura]

hi,
I found a deadlock on 2 queries, DELETE and REINDEX TABLE

DELETE waits for RowExclusiveLock on table address_jd
and
REINDEX waits for AccessExclusiveLock on PRIMARY KEY address_jd_pk

log:
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-1] 
host=10.0.5.144,user=ppstat,db=ppstat ERROR:  deadlock detected
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-2] 
host=10.0.5.144,user=ppstat,db=ppstat DETAIL:  Process 25576 waits for 
RowExclusiveLock on relation 16613 of database 16388;
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-3]  blocked by process 26649.
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-4] Process 26649 waits 
for AccessExclusiveLock on relation 17776 of database 16388; blocked by 
process 25576.
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-5] 
host=10.0.5.144,user=ppstat,db=ppstat CONTEXT:  SQL statement "DELETE FROM 
dsa_nas.address_jd WHERE exp_id = 7"
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-6] PL/pgSQL 
function "delete_export" line 16 at EXECUTE statement
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-7] SQL statement "SELECT  
transform_functions.delete_export('dsa_nas', $1 )"
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-8] PL/pgSQL 
function "transform_nas" line 152 at PERFORM
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-9] 
host=10.0.5.144,user=ppstat,db=ppstat STATEMENT:  SELECT 
transform_functions.transform_nas(7)
Mar 19 00:30:57 ch08bl09 postgres[26649]: [3-1] 
host=[local],user=postgres,db=ppstat LOG:  duration: 865522.949 ms  
statement: REINDEX TABLE dsa_nas.address_jd

where can be problem?

thanks, 
tashunko

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general