Re: [GENERAL] High cpu usage after many inserts

[Jordan Tomkinson]

Scott,

DB Schema: http://demo.moodle.org/db_schema.txt
SQL Query log: http://demo.moodle.org/querylog.txt

There are _much_ more queries than I anticipated :/

Jordan

Re: [GENERAL] High cpu usage after many inserts

[Scott Marlowe]

On Mon, Feb 23, 2009 at 12:42 AM, Jordan Tomkinson  wrote:
>
>
> On Mon, Feb 23, 2009 at 4:29 PM, Scott Marlowe 
> wrote:
>>
>> Oh yeah, what OS is this?  Version and all that.
>
> I should probably clarify that the high cpu only exists while the jmeter
> tests are running, once the tests are finished the cpu returns to 0% (this
> isnt a production server yet, so no other queries other than my tests)
> I have not yet tried other SQL queries to see if they are affected, i
> suspect it may only be related to the two forum tables the test focuses on
> but I may be incorrect - the database is filling up with data again now so I
> can test this tomorrow.

Sorry, I had gotten the impression the CPU usage continued after the
test.  That it's 100% during the test is quite understandable.  So
does it start lower than 4x100% Then climb during the tests?  Is the
throughput dropping off over time?

-- 
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] High cpu usage after many inserts

[Jordan Tomkinson]

On Mon, Feb 23, 2009 at 5:20 PM, Scott Marlowe wrote:

> On Mon, Feb 23, 2009 at 12:42 AM, Jordan Tomkinson 
> wrote:
> >
> >
> > On Mon, Feb 23, 2009 at 4:29 PM, Scott Marlowe 
> > wrote:
> >>
> >> Oh yeah, what OS is this?  Version and all that.
> >
> > I should probably clarify that the high cpu only exists while the jmeter
> > tests are running, once the tests are finished the cpu returns to 0%
> (this
> > isnt a production server yet, so no other queries other than my tests)
> > I have not yet tried other SQL queries to see if they are affected, i
> > suspect it may only be related to the two forum tables the test focuses
> on
> > but I may be incorrect - the database is filling up with data again now
> so I
> > can test this tomorrow.
>
> Sorry, I had gotten the impression the CPU usage continued after the
> test.  That it's 100% during the test is quite understandable.  So
> does it start lower than 4x100% Then climb during the tests?  Is the
> throughput dropping off over time?
>

As per the spreadsheet (
http://spreadsheets.google.com/pub?key=pu_k0R6vNvOVP26TRZdtdYw) CPU usage is
around 50% and starts climbing over 3 hours until we have just under 10,000
rows of data then stays at 99% for the duration of all future tests.
Once the rows are removed the tests start back down at 50% usage again.

Re: [GENERAL] problems with win32 enterprisedb 8.3.6 ssl=on

[Dave Page]

On Mon, Feb 23, 2009 at 7:55 AM, raf  wrote:
> hi,
>
> i've been getting nonsensical error messages all day with
> postgres 8.3 on winxpsp3. i tried upgrading to 8.3.6
> (enterprisedb) and fresh installs.

Please clarify what you have done. You cannot upgrade from PostgreSQL
to EnterpriseDB (Postgres Plus Advanced Server) without a dump/reload
of your data as they are different DBMSs with differences in the data
format.

If you mean that you tried to update an installation of the community
installer with the one-click installer *packaged* by EnterpriseDB,
then that is also not supported as the one-click installer uses
integer datetimes which the community one does not (an error on my
part that's somewhat difficult to correct now). That would not account
for the errors you are seeing though.

If you have switched installer, I would first start by uninstalling
entirely. None of the uninstallers should remove the data directory,
but take a backup first to be sure anyway. Then, install the latest
build of whichever package you originally installed, reusing the
existing data directory. If continue to see the errors you noted when
you've done this, make sure the data directory is owned by the user
the server runs as, or at least has full control of it and it's
contents.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.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 clustering with DRBD

[Tim Uckun]

>
> We're very happy with pgpool-II for load-balancing and multi-master
> usage of PostgreSQL (keep in mind to enable HA for pgpool-II itself to
> avoid a SPOF, e.g. with heartbeat).
>
>
Thanks.

I am going to see which one has better documentation and try that one first.

Re: [GENERAL] PostgreSQL clustering with DRBD

[Serge Fonville]

Thanks all for the responses,

We're very happy with pgpool-II for load-balancing and multi-master

usage of PostgreSQL (keep in mind to enable HA for pgpool-II itself to

avoid a SPOF, e.g. with heartbeat).


I could not determine whether pgpool-II is suitable for what I want.
It does not seem to support multimaster in the fashion I had in mind, based
on the information on the website it looks like it does not support full
CRUD on any node.
The most suitable solution seems to be LVS for a shared IP, ldirectord for
load balancing and cybercluster for the database
replication/synchronization.

After reading your post I decided to check out cybercluster.   In PgFoundry
> there is a cybercluster project http://pgfoundry.org/projects/
> cybercluster/ but it hasn't been updated since 2007.
>
On the cybercluster website  the
newest version is 1.2 and at pgfoundry it is 1.0
At the apache directory
listingit seems
version 1.2.1 dated 25-Jul-2008 21:05 seems to be the newest.
Cybercluster uses shared-nothing storage (as does MySQL cluster) fortunately
Cybercluster supports a two node cluster.

I think I start to have an idea about what the best suitable solution is for
my situation.
I was hoping there would be some sort of patch for the PostgreSQL download
instead of an entire rebuild of the sources.

I'll post any updates I find.

Thanks a lot everyone

Regards,

Serge Fonville

[GENERAL] Date range problem on pg_restore

[Dick Kniep]

Hi list,

 

We have a peculiar problem with a restore.

 

We have a database with many different schema's that all act as separate 
databases. 

 

The data is backed up from postgresql 8.1 with the following commands in cron:

 

28 12 * * * /usr/bin/pg_dump -a -F c --disable-triggers -f 
/srv/currentBackup/comsupportdata --schema=comsupport cvix

16 1 * * * /usr/bin/pg_dump -s -f /srv/currentBackup/comsupportschema 
--schema=comsupport cvix

 

As you can see the schema is backed up separately from the data.

 

When I am trying to restore the data into another 8.1 postgresql db I get the 
following errors:

 

postg...@dev01:/home/dick$ pg_restore --disable-triggers --schema=comsupport -d 
cvix_test comsupportdata
pg_restore: ERROR:  date out of range: "11750113-05-05"
CONTEXT:  COPY planning, line 71654, column mijlpaaldatum: "11750113-05-05"
pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:  date out of 
range: "11750113-05-05"
CONTEXT:  COPY planning, line 71654, column mijlpaaldatum: "11750113-05-05"

 

and the restore stops.

 

I have tried to get offending rows in the source db, but if i do a select on 
the table planning with a mijlpaaldatum > '-01-01' I get 0 rows back. So it 
looks like a problem in the backup.

 

Do you have any idea what I can do to get my data back?

 

Cheers,

D.Kniep
 

Re: [GENERAL] PostgreSQL CE?

[Emanuel Calvo Franco]

2009/2/22 Gerd Koenig :
> Hi Steve,
>
> I know that EnterpriseDB offers 3 levels of certification.
> Perhaps one of them suits your needs..?!?!
>
> regards...:GERD:...
>
>

Pearson has 2 levels (gold and plate) [1]

Regards,

[1] http://www.vue.com/sra/
>
> steve.gnuli...@gmail.com schrieb:
>>
>> Just to know, if actually there's -or where- now, a PostgreSQL Certified
>> Engineer program.
>> I think that obtaining -if able- a Postgresql CE and some kind of linux
>> certification like RHCE o LPI,
>> could be a lot of interest.
>> Finally, to know if there's any book (english) for the PostgreSQL
>> Certification, like in Japanese.
>>
>> Thank you for your time,
>>
>> Steve,
>>
>>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
  Emanuel Calvo Franco
Sumate al ARPUG !
  (www.postgres-arg.org -
 www.arpug.com.ar)
ArPUG / AOSUG Member
   Postgresql Support & Admin

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

[GENERAL] Connection refused (0x0000274D/10061)

[najmuddin hassan]

Hi,

I just installed a program called moteview by crossbow technologies. It uses
postgreSQL 8.0.0-rc1 for its database. There is something wrong as when I
launched the program it automaticly gives me an error that the database is
not available. The postgreSQL database installation is bundled together with
installation disk.  I read one of the mail discussion that that version of
sql is no longer supported so I upgrade to version 8.3. The moteview program
has a script called "resetdb.cmd" (script is given below) whereby as I
understand it is to set the database for the program as server : localhost,
database : task, user : tele, and password : tiny. When I run the
resetdb.cmd program. The error below shows up:

psql: could not connect to server: connection refused (0x274D/10061). Is
the server running on host "localhost" and accepting TCP/IP connection on
port 5432.

I not a database person. I "googled" the error msg and from my reading it
has something to do with authentication

Can I have the solution to this matter. Thanks.


set PG_HOST=localhost
set PG_PORT=5432
set PG_USER=tele
set PG_PASS=tiny
set PG_DBASE=task
set PG_CONN=-h %PG_HOST% -p %PG_PORT% -U %PG_USER%

set PG_DDIR="C:\Program Files\PostgreSQL\8.3\data"
set PG_BIN="C:\Program Files\PostgreSQL\8.3\bin"
set PATH=%PG_BIN%; %PATH%

if '%1' == ' ' goto START
set PG_DDIR=%1\PostgreSQL\8.3\data
set PG_BIN=%1\PostgreSQL\8.3\bin
set PATH=%PG_BIN%;%PATH%

: START

echo %PG_BIN%
echo %PG_DDIR%
echo %PATH%

echo "Granting permissions to access database"
copy pg_hba.conf %PG_DDIR%
net start pgsql-8.3
sleep 5
pg_ctl reload -D %PG_DDIR%

echo "Setting up PostgreSQL 8.3 database for Moteview"
psql -e %PG_CONN% template1 < db_user.sql
psql -e %PG_CONN% %PG_DBASE% < db_moteview.sql
psql -e %PG_CONN% %PG_DBASE% < db_xsensor.sql
psql -e %PG_CONN% %PG_DBASE% < db_sample_mts310.sql

echo DATABASE CREATION FINISHED

Appreciate your help...

Regards,
Najios

Re: [GENERAL] Connection refused (0x0000274D/10061)

[A. Kretschmer]

In response to najmuddin hassan :
> Hi,
>  
> I just installed a program called moteview by crossbow technologies. It uses
> postgreSQL 8.0.0-rc1 for its database. There is something wrong as when I
> launched the program it automaticly gives me an error that the database is not
> available. The postgreSQL database installation is bundled together with
> installation disk.  I read one of the mail discussion that that version of sql
> is no longer supported so I upgrade to version 8.3. The moteview program has a
> script called "resetdb.cmd" (script is given below) whereby as I understand it
> is to set the database for the program as server : localhost, database : task,
> user : tele, and password : tiny. When I run the resetdb.cmd program. The 
> error
> below shows up: 
>  
> psql: could not connect to server: connection refused (0x274D/10061). Is
> the server running on host "localhost" and accepting TCP/IP connection on port
> 5432.
>  
> I not a database person. I "googled" the error msg and from my reading it has
> something to do with authentication

No, it can't connect to the DB. Is there something like a personal
firewall and/or anti-virus-software installed?


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
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] [pgsql-es-ayuda] Cambiando Postgresql 7.4.3 a 8.1.11 !!!

[Silvio Quadri]

El día 22 de febrero de 2009 17:46, Angelo Astorga
 escribió:
> Tenia Red Hat enterprise 3.0 con postgresql 7.4.3 y PHP 4.3.2, por necesidad
> de hardware migre todo a Red Hat enterprise 5.3 con postgresql 8.1.11 y PHP
> 5.1.6 (default del sistema operativo), si bien puedo crear y recuperar
> la BD, no puedo acceder desde mi aplicacion web en php, si por consola, es
> decir, # psql nombre_base_datos...  sera un tema de incompatibilidad de php,
> postgresql o ambas... alguna ayudita al respecto, se agradece !!!
>
> IMPORTANTE: La BD postgresql la pude recuperar con: # psql nombre_base_dato
> < nombre_archivo_plano_almacena_base_datos ... pero al recuperar con   #
> pg_restore -d nombre_base_dato   nombre_archivo_plano_almacena_base_datos
> ... no se puede, manda un error...
>
> AAstorga

¿Qué error te devuelve al conectarte?
¿Qué tenés en el pg_hba?
Silvio

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

[GENERAL] restore single table

[Kevin Duffy]

 

Hello All:

 

I need guidance on how move some changes that I have made to my
production database.

On my development database I made changes to a table called DEPT.  I
added a column, added 

a couple of records and did some general data cleanup

 

What I did not do was change any of the keys on existing records. The
primary key of DEPT is a 

foreign key in several other tables.

 

Here is my question:  Can I do a table restore on to the production
database and expect these 

changes to be moved over?  Will the restore handle, via some magic,
"suspend" the foreign key

constraints and allow the new table structure to be created and then
populated with new data.

 

I have not changed the keys of existing records so that existing
relationships should be restored.

 

Thanks for considering my issue.

 

 

Kevin Duffy

 

Fwd: [GENERAL] Connection refused (0x0000274D/10061)

[najmuddin hassan]

I've have AVG installed and I've remove it and I've turn off the windows
firewall.

When I go to PostgreSQL 8.3 > Start

The command prompt window appears with text.
"The PostgreSQL Database server 8.3 is starting"
the dot will appear for sometime like something is running then the next msg
appear
"The PostgreSQL Database server 8.3 could not be started"

Same goes if I use in command prompt
net start pgsql-8.3
Any solution..


-- Forwarded message --
From: A. Kretschmer 
Date: Mon, Feb 23, 2009 at 1:33 PM
Subject: Re: [GENERAL] Connection refused (0x274D/10061)
To: pgsql-general@postgresql.org


In response to najmuddin hassan :
> Hi,
>
> I just installed a program called moteview by crossbow technologies. It
uses
> postgreSQL 8.0.0-rc1 for its database. There is something wrong as when I
> launched the program it automaticly gives me an error that the database is
not
> available. The postgreSQL database installation is bundled together with
> installation disk.  I read one of the mail discussion that that version of
sql
> is no longer supported so I upgrade to version 8.3. The moteview program
has a
> script called "resetdb.cmd" (script is given below) whereby as I
understand it
> is to set the database for the program as server : localhost, database :
task,
> user : tele, and password : tiny. When I run the resetdb.cmd program. The
error
> below shows up:
>
> psql: could not connect to server: connection refused (0x274D/10061).
Is
> the server running on host "localhost" and accepting TCP/IP connection on
port
> 5432.
>
> I not a database person. I "googled" the error msg and from my reading it
has
> something to do with authentication

No, it can't connect to the DB. Is there something like a personal
firewall and/or anti-virus-software installed?


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

--
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] Connection refused (0x0000274D/10061)

[Tom Lane]

najmuddin hassan  writes:
> I just installed a program called moteview by crossbow technologies. It uses
> postgreSQL 8.0.0-rc1 for its database.

Egad.  Based on that statement alone, I will tell you that crossbow is
a bunch of incompetent idiots and you should not trust their software
in the slightest.  8.0.0-rc1 is years old, has MANY known bugs, and
was never meant for production use even when it was released.

Don't even bother trying to get this to work --- find some other
software that's built with something resembling sane engineering
practices.

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] High cpu usage after many inserts

[Scott Marlowe]

On Mon, Feb 23, 2009 at 1:29 AM, Jordan Tomkinson  wrote:
>
> On Mon, Feb 23, 2009 at 5:20 PM, Scott Marlowe 
> wrote:
>>
>> On Mon, Feb 23, 2009 at 12:42 AM, Jordan Tomkinson 
>> wrote:
>> >
>> >
>> > On Mon, Feb 23, 2009 at 4:29 PM, Scott Marlowe 
>> > wrote:
>> >>
>> >> Oh yeah, what OS is this?  Version and all that.
>> >
>> > I should probably clarify that the high cpu only exists while the jmeter
>> > tests are running, once the tests are finished the cpu returns to 0%
>> > (this
>> > isnt a production server yet, so no other queries other than my tests)
>> > I have not yet tried other SQL queries to see if they are affected, i
>> > suspect it may only be related to the two forum tables the test focuses
>> > on
>> > but I may be incorrect - the database is filling up with data again now
>> > so I
>> > can test this tomorrow.
>>
>> Sorry, I had gotten the impression the CPU usage continued after the
>> test.  That it's 100% during the test is quite understandable.  So
>> does it start lower than 4x100% Then climb during the tests?  Is the
>> throughput dropping off over time?
>
> As per the spreadsheet
> (http://spreadsheets.google.com/pub?key=pu_k0R6vNvOVP26TRZdtdYw) CPU usage
> is around 50% and starts climbing over 3 hours until we have just under
> 10,000 rows of data then stays at 99% for the duration of all future tests.
> Once the rows are removed the tests start back down at 50% usage again.

Oh, ok. well that's pretty normal as the indexes grow large enough to
not fit in cache, then not fit in memory, etc...  Are you noticing a
sharp dropoff in performance?

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

[GENERAL] Serverlog postgresql 8.1.11

[Angelo Astorga]

Antiguamente utiilizaba postgresql 7.4.3 donde se generaba un serverlog en
directorio .../data/serverlog, ahora con vers. 8.1.11 no encuentro el
serverlog... alguna ayuda al respecto !!!

[GENERAL] Function that returns Boolean

[SHARMILA JOTHIRAJAH]

Hi,
This is a simple function that returns a boolean ..

create or replace function check_value( newValue IN VARCHAR,
oldValue IN VARCHAR ) RETURN BOOLEAN
as
'
BEGIN
IF ( newValue != oldValue)
then return true;
else
return false;
 END IF;
END;
'
LANGUAGE 'plpgsql'

But I get this error...what is wrong with this ?

[Error] Script lines: 1-13 -
 ERROR: syntax error at or near "RETURN"
 Line: 2 

Thanks
Sharmila


  

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

Re: [GENERAL] Function that returns Boolean

[SHARMILA JOTHIRAJAH]

Its a typo..it should be "RETURNS BOOLEAN" and not "RETURN BOOLEAN"
-Sharmila

--- On Mon, 2/23/09, SHARMILA JOTHIRAJAH  wrote:

> From: SHARMILA JOTHIRAJAH 
> Subject: Function that returns Boolean
> To: "General postgres mailing list" 
> Date: Monday, February 23, 2009, 12:39 PM
> Hi,
> This is a simple function that returns a boolean ..
> 
> create or replace function check_value( newValue IN
> VARCHAR,
> oldValue IN VARCHAR ) RETURN BOOLEAN
> as
> '
> BEGIN
> IF ( newValue != oldValue)
> then return true;
> else
> return false;
>  END IF;
> END;
> '
> LANGUAGE 'plpgsql'
> 
> But I get this error...what is wrong with this ?
> 
> [Error] Script lines: 1-13 -
>  ERROR: syntax error at or near "RETURN"
>  Line: 2 
> 
> Thanks
> Sharmila


  

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

Re: [GENERAL] Function that returns Boolean

[Tom Lane]

SHARMILA JOTHIRAJAH  writes:
> create or replace function check_value( newValue IN VARCHAR,
> oldValue IN VARCHAR ) RETURN BOOLEAN

Should be RETURNS BOOLEAN.

You might want to fix whatever client code you are using so that it
shows the error cursor, which would certainly have helped direct
your attention to the right place instead of the wrong one.  What
I see in psql is

ERROR:  syntax error at or near "RETURN"
LINE 2: oldValue IN VARCHAR ) RETURN BOOLEAN
  ^


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] Function that returns Boolean

[Adrian Klaver]

- "SHARMILA JOTHIRAJAH"  wrote:

> Hi,
> This is a simple function that returns a boolean ..
> 
> create or replace function check_value( newValue IN VARCHAR,
> oldValue IN VARCHAR ) RETURN BOOLEAN
^^ RETURNS
> as
> '
> BEGIN
> IF ( newValue != oldValue)
> then return true;
> else
> return false;
>  END IF;
> END;
> '
> LANGUAGE 'plpgsql'
> 
> But I get this error...what is wrong with this ?
> 
> [Error] Script lines: 1-13 -
>  ERROR: syntax error at or near "RETURN"
>  Line: 2 
> 
> Thanks
> Sharmila
> 
> 
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] Product Roadmap question and request for recommendation

[Keaton Adams]

What would you do in this situation?

We are currently at PG 8.1 and are in the process of upgrading to 8.3.6.  I 
read on your development roadmap page that 8.4 is slated for release in Q1 of 
this year, possibly on the 31st of March:

"The next release of PostgreSQL is planned to be the 8.4 release. A tentative 
schedule for this version has a release in the first quarter of 2009."

I have also read in the postings that the framework for in-place upgrades is 
being added to 8.4, so the actual upgrade to the forthcoming 8.5 can be done as 
in-place (without dump/restore), but there won't be a way to do an in-place 
upgrade from any 8.3.x version directly to 8.5.

Upgrading some of our larger databases is rather painful and is a several day 
effort (staging historical data over time so the actual cutover can 
realistically be done in a weekend).  Right now 8.1 is working well for us, is 
extremely stable, and provides all of the functionality we need to support our 
applications. Given this, it sounds to me like it makes sense to wait a bit 
longer (2nd half of this year) for a 8.4.x version do to the dump/restore 
against for the last time so we can then, in the future, do in-place upgrades 
from 8.5 onward.

Any comments you can make on this suggestion would be very much appreciated.

Re: [GENERAL] Function that returns Boolean

[Richard Broersma]

On Mon, Feb 23, 2009 at 9:39 AM, SHARMILA JOTHIRAJAH
 wrote:

>IF ( newValue != oldValue)

One good piece of advice that Tom Lane pointed out to me was:

IF ( newValue IS DISTINCT FROM oldValue )

is better due to (what may be) the unexpected results of equality
testing when NULL values are thrown into the mix.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

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

Re: [GENERAL] Product Roadmap question and request for recommendation

[Dot Yet]

don't fix if it ain't broken ;)

no really, if the system is capable of working nearly flawlessly till 8.4 is
baked, then it is sound to wait till then, especially when upgrading to a
newer version is a *painful* as in time consuming task. i am assuming
downgrading (just incase things don't workout that easily) will be equally
painful.

rgds,
dotyet.


On Mon, Feb 23, 2009 at 1:09 PM, Keaton Adams  wrote:

>  What would you do in this situation?
>
> We are currently at PG 8.1 and are in the process of upgrading to 8.3.6.  I
> read on your development roadmap page that 8.4 is slated for release in Q1
> of this year, possibly on the 31st of March:
>
> "The next release of PostgreSQL is planned to be the 8.4 release. A
> tentative schedule for this version has a release in the first quarter of
> 2009."
>
> I have also read in the postings that the framework for in-place upgrades
> is being added to 8.4, so the actual upgrade to the forthcoming 8.5 can be
> done as in-place (without dump/restore), but there won't be a way to do an
> in-place upgrade from any 8.3.x version directly to 8.5.
>
> Upgrading some of our larger databases is rather painful and is a several
> day effort (staging historical data over time so the actual cutover can
> realistically be done in a weekend).  Right now 8.1 is working well for us,
> is extremely stable, and provides all of the functionality we need to
> support our applications. Given this, it sounds to me like it makes sense to
> wait a bit longer (2nd half of this year) for a 8.4.x version do to the
> dump/restore against for the last time so we can then, in the future, do
> in-place upgrades from 8.5 onward.
>
> Any comments you can make on this suggestion would be very much
> appreciated.
>
>

Re: [GENERAL] Function that returns Boolean

[David Fetter]

On Mon, Feb 23, 2009 at 09:39:01AM -0800, SHARMILA JOTHIRAJAH wrote:
> Hi,
> This is a simple function that returns a boolean ..

This should be an SQL function, as it doesn't do anything you need (or
would even find convenient) for a more procedural language to do:

CREATE OR REPLACE FUNCTION check_value(newValue TEXT, oldValue TEXT)
RETURNS BOOLEAN
LANGUAGE SQL
AS $$ SELECT $1 IS DISTINCT FROM $2 $$;

As others have pointed out, IS DISTINCT FROM covers the case where one
or more of the arguments is a NULL.

Cheers,
David.
> 
> create or replace function check_value( newValue IN VARCHAR,
> oldValue IN VARCHAR ) RETURN BOOLEAN
> as
> '
> BEGIN
> IF ( newValue != oldValue)
> then return true;
> else
> return false;
>  END IF;
> END;
> '
> LANGUAGE 'plpgsql'
> 
> But I get this error...what is wrong with this ?
> 
> [Error] Script lines: 1-13 -
>  ERROR: syntax error at or near "RETURN"
>  Line: 2 
> 
> Thanks
> Sharmila
> 
> 
>   
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [GENERAL] Product Roadmap question and request for recommendation

[Tom Lane]

Keaton Adams  writes:
> We are currently at PG 8.1 and are in the process of upgrading to 8.3.6.  I 
> read on your development roadmap page that 8.4 is slated for release in Q1 of 
> this year, possibly on the 31st of March:

> "The next release of PostgreSQL is planned to be the 8.4 release. A tentative 
> schedule for this version has a release in the first quarter of 2009."

That's out of date --- there is absolutely zero chance of 8.4 being
released in March.  This summer, perhaps.

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: Fwd: [GENERAL] Connection refused (0x0000274D/10061)

[Andreas Kretschmer]

najmuddin hassan  schrieb:

> I've have AVG installed and I've remove it and I've turn off the windows
> firewall.

Maybe your AVG is the problem... don't know, i'm not familiar with
windoze and so on...

>  
> When I go to PostgreSQL 8.3 > Start
>  
> The command prompt window appears with text.
> "The PostgreSQL Database server 8.3 is starting"
> the dot will appear for sometime like something is running then the next msg
> appear
> "The PostgreSQL Database server 8.3 could not be started"
>  
> Same goes if I use in command prompt
> net start pgsql-8.3
> Any solution..

See the server-log for details. And please no top-posting, thx.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
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] Product Roadmap question and request for recommendation

[Keaton Adams]

OK, well just to make sure I have the latest and greatest information on the 
proposed in-place upgrade process.

If we go from 8.1 to 8.3.6 now using dump/restore we will need to do another 
dump/restore to get to 8.4, correct?
And from 8.4 on we will be able to do in-place upgrades?
The idea of being able to do an in-place upgrade from an 8.3 release is off the 
table?

Thanks again,

Keaton


On 2/23/09 12:03 PM, "Tom Lane"  wrote:

Keaton Adams  writes:
> We are currently at PG 8.1 and are in the process of upgrading to 8.3.6.  I 
> read on your development roadmap page that 8.4 is slated for release in Q1 of 
> this year, possibly on the 31st of March:

> "The next release of PostgreSQL is planned to be the 8.4 release. A tentative 
> schedule for this version has a release in the first quarter of 2009."

That's out of date --- there is absolutely zero chance of 8.4 being
released in March.  This summer, perhaps.

regards, tom lane

Re: [GENERAL] Product Roadmap question and request for recommendation

[Andreas Kretschmer]

Tom Lane  wrote:

> Keaton Adams  writes:
> > We are currently at PG 8.1 and are in the process of upgrading to 8.3.6.  I 
> > read on your development roadmap page that 8.4 is slated for release in Q1 
> > of this year, possibly on the 31st of March:
> 
> > "The next release of PostgreSQL is planned to be the 8.4 release. A 
> > tentative schedule for this version has a release in the first quarter of 
> > 2009."
> 
> That's out of date --- there is absolutely zero chance of 8.4 being
> released in March.  This summer, perhaps.

:-(


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

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

[GENERAL] speaking of 8.4...

[John R Pierce]

is it looking like the simple replication will make it into 8.4?

if so, are there any docs or notes I could peruse on how this will 
work?   what it will use to ship the WAL logs, etc ?   I've poked around 
the -hacker mail list archives and not found too much on it.





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

Re: [GENERAL] High cpu usage after many inserts

[Markus Wanner]

Hi,

Scott Marlowe wrote:
> Oh, what is an LMS?

A Learning Management System, not to be confused with a CMS, which might
also stand for a Course Management System ;-)

Regards

Markus Wanner


-- 
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] Backup Strategy Second Opinion

[Tim Uckun]

If you could publish a brief howto on this I would be most grateful. I bet
many others would too.


On Mon, Feb 23, 2009 at 2:56 PM, Bryan Murphy  wrote:

> On Sun, Feb 22, 2009 at 7:30 PM, Tim Uckun  wrote:
> >> 1. It's OK if we lose a few seconds (or even minutes) of transactions
> >> should one of our primary databases crash.
> >> 2. It's unlikely we'll need to load a backup that's more than a few days
> >> old.
> >
> > How do you handle failover and falling back to the primary once it's up?
>
> We don't plan to fail back to the primary.  Amazon is a very different
> beast, once a server is dead, we just toss it away.  The secondary
> permanently becomes the primary and we create a new tertiary from
> scratch which then becomes a log shipped copy of the secondary.
>
> Bryan
>

[GENERAL] Re: overlaps behaviour - ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps ('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)

[Marek Lewczuk]

2009/2/23 Marek Lewczuk :
> Hello,
> I can't find SQL definition for OVERLAPS operator so I don't know
> whether following expression's result (false) is appropriate
> behaviour:
> select ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps
> ('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)
>
> Can anyone confirm that ? In my understanding of "overlaps" it should
> result true, as those two periods overlaps in 2007-12-01.
>
> psql (PostgreSQL) 8.3.5
Ok. Sorry for that question. I found SQL92 definition and it seems,
that PG implementation is just fine.

Best regards,
ML

-- 
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] Product Roadmap question and request for recommendation

[Scott Marlowe]

On Mon, Feb 23, 2009 at 1:03 PM, Keaton Adams  wrote:
> OK, well just to make sure I have the latest and greatest information on the
> proposed in-place upgrade process.
>
> If we go from 8.1 to 8.3.6 now using dump/restore we will need to do another
> dump/restore to get to 8.4, correct?

Or you can use slony and have a few seconds of downtime during the switchover.

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

[GENERAL] surprising results with random()

[Jessi Berkelhammer]

Hi,

I have a view in which I want to randomly assign values if certain
conditions hold. I was getting surprising results. Here is a (very)
simplified version of the view, which seems to indicate the problem:

CREATE OR REPLACE VIEW test_view AS
SELECT
CASE
WHEN random() < . THEN '1'
WHEN random() < . THEN '2'
ELSE '3'
END AS test_value

FROM client ;

It seems this should generate a random number between 0 and 1, and set
test_value to '1' if this first generated number is less than ..
Otherwise, it should generate another random number, and set test_value
to '2' if this is less than .. And if neither of the random numbers
are less than ., it should set test_value to '3'. It seems to me
that there should be a relative even distribution of the 3 values.

However when I run this, the values are always similar to what is below:

X_test=>  select test_value, count(*) from test_view group by 1 order by 1;
 test_value | count
+---
 1  | 23947
 2  | 16061
 3  | 32443

Why are there significantly fewer 2s? I understand that random() is not
truly random, and that the seed affects this value. But it still
confuses me that, no matter how many times I run this, there are always
so few 2s. If it is generating an independent random number in the
second call to random(), then I don't know why there are more so many
more 1s than 2s.

Thanks!
-jessi

--
Jessi Berkelhammer
Downtown Emergency Service Center
Computer Programming Specialist



--
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] problems with win32 enterprisedb 8.3.6 ssl=on

[raf]

Dave Page wrote:

> On Mon, Feb 23, 2009 at 7:55 AM, raf  wrote:
> > hi,
> >
> > i've been getting nonsensical error messages all day with
> > postgres 8.3 on winxpsp3. i tried upgrading to 8.3.6
> > (enterprisedb) and fresh installs.
> 
> Please clarify what you have done. You cannot upgrade from PostgreSQL
> to EnterpriseDB (Postgres Plus Advanced Server) without a dump/reload
> of your data as they are different DBMSs with differences in the data
> format.

the version that i originally had was an older 8.3
enterprisedb. the first upgrade i attempted wasn't
to enterprisedb but only to enterprisedb's postgresql
installer. that was a mistake on my part. that resulted
in an incompatibility error regarding 64 bit timestamps.
so i threw the data away (it's only test data anyway)
and did a fresh install of enterprisedb after that.

> If you mean that you tried to update an installation of the community
> installer with the one-click installer *packaged* by EnterpriseDB,
> then that is also not supported as the one-click installer uses
> integer datetimes which the community one does not (an error on my
> part that's somewhat difficult to correct now). That would not account
> for the errors you are seeing though.

that's why i didn't mention it.

> If you have switched installer, I would first start by uninstalling
> entirely. None of the uninstallers should remove the data directory,
> but take a backup first to be sure anyway. Then, install the latest
> build of whichever package you originally installed, reusing the
> existing data directory. If continue to see the errors you noted when
> you've done this, make sure the data directory is owned by the user
> the server runs as, or at least has full control of it and it's
> contents.

that's what i mean by a fresh install (i.e. complete
uninstall and i even deleted the data directory so that
it's permissions could be recreated from scratch by
the installer).

as i said in my previous message, the postgres user had what
looked like sufficient permissions anyway but i gave it full
control over the files mentioned in the error messages and
the directories that contain them.

so, i had already done everything you suggested.
any other suggestions?

when this error was mentioned on the list two years ago,
it was said that there are many error conditions that
get translated into the same "Permission denied" error
message. does anyone know what these error conditions
might be?

btw, the server.* files for ssl are all readable by "Everyone".
hopefully, that includes the postgres user.

> -- 
> Dave Page
> EnterpriseDB UK:   http://www.enterprisedb.com

cheers,
raf


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

Re: [GENERAL] surprising results with random()

[Steve Atkins]

On Feb 23, 2009, at 2:09 PM, Jessi Berkelhammer wrote:


Hi,

I have a view in which I want to randomly assign values if certain
conditions hold. I was getting surprising results. Here is a (very)
simplified version of the view, which seems to indicate the problem:

CREATE OR REPLACE VIEW test_view AS
SELECT
CASE
WHEN random() < . THEN '1'
WHEN random() < . THEN '2'
ELSE '3'
END AS test_value

FROM client ;

It seems this should generate a random number between 0 and 1, and set
test_value to '1' if this first generated number is less than ..
Otherwise, it should generate another random number, and set  
test_value
to '2' if this is less than .. And if neither of the random  
numbers

are less than ., it should set test_value to '3'. It seems to me
that there should be a relative even distribution of the 3 values.


However when I run this, the values are always similar to what is  
below:


X_test=>  select test_value, count(*) from test_view group by 1  
order by 1;

test_value | count
+---
1  | 23947
2  | 16061
3  | 32443

Why are there significantly fewer 2s? I understand that random() is  
not

truly random, and that the seed affects this value. But it still
confuses me that, no matter how many times I run this, there are  
always

so few 2s. If it is generating an independent random number in the
second call to random(), then I don't know why there are more so many
more 1s than 2s.


Nope, it's nothing to do with random(), it's that your maths is wrong.

There are 9 possible cases. In 3 of them you return 1. In 2 of them you
return 2. In the remaining 4 cases you return 3.

If you were to run this 72451 times I'd expect to see
1:  24150 = 72451 * 3/9
2: 16100 = 72451 * 2/9
3: 32200 = 72451 * 4/9

Which, unsurprisingly, is fairly close to what you get.

Cheers,
  Steve



--
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] surprising results with random()

[ries van Twisk]

Jessi,

should the function not look like this???

CREATE OR REPLACE VIEW test_view AS
SELECT
CASE
WHEN random() < .3 THEN '1'
WHEN random() < .5 THEN '2'
ELSE '3'
END AS test_value

FROM client;

On Feb 23, 2009, at 5:09 PM, Jessi Berkelhammer wrote:


Hi,

I have a view in which I want to randomly assign values if certain
conditions hold. I was getting surprising results. Here is a (very)
simplified version of the view, which seems to indicate the problem:

CREATE OR REPLACE VIEW test_view AS
SELECT
CASE
WHEN random() < . THEN '1'
WHEN random() < . THEN '2'
ELSE '3'
END AS test_value

FROM client ;

It seems this should generate a random number between 0 and 1, and set
test_value to '1' if this first generated number is less than ..
Otherwise, it should generate another random number, and set  
test_value
to '2' if this is less than .. And if neither of the random  
numbers

are less than ., it should set test_value to '3'. It seems to me
that there should be a relative even distribution of the 3 values.

However when I run this, the values are always similar to what is  
below:


X_test=>  select test_value, count(*) from test_view group by 1  
order by 1;

test_value | count
+---
1  | 23947
2  | 16061
3  | 32443

Why are there significantly fewer 2s? I understand that random() is  
not

truly random, and that the seed affects this value. But it still
confuses me that, no matter how many times I run this, there are  
always

so few 2s. If it is generating an independent random number in the
second call to random(), then I don't know why there are more so many
more 1s than 2s.

Thanks!
-jessi

--
Jessi Berkelhammer
Downtown Emergency Service Center
Computer Programming Specialist








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

[Fwd: Re: [GENERAL] surprising results with random()]

[John R Pierce]

ries van Twisk wrote:



Jessi,

should the function not look like this???

CREATE OR REPLACE VIEW test_view AS
SELECT
CASE
WHEN random() < .3 THEN '1'
WHEN random() < .5 THEN '2'
ELSE '3'
END AS test_value

FROM client;



actually, I'd think that should be .6


but... wouldn'tfloor(random() * 3.0) + 1  be a simpler way to do this?







--
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] surprising results with random()

[Brent Wood]

Or perhaps:

CREATE OR REPLACE VIEW test_view AS
SELECT (random()*3)::int as test_value;

At least in this case, that should give the same result.

in this case 1/3 should be 1,  1/3 = 2 & 1/3=3

in your case 1/3 = 1, 1/2 the remainder (1/2 * 2/3 = 1/3) = 2, remaining 1/3 = 3

Although I'm guessing the original intent is to NOT generate an equal 
distribution, but I'm not sure what distribution is required.


Cheers,

   Brent Wood


Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> ries van Twisk  02/24/09 12:13 PM >>>

Jessi,

should the function not look like this???

CREATE OR REPLACE VIEW test_view AS
SELECT
CASE
WHEN random() < .3 THEN '1'
WHEN random() < .5 THEN '2'
ELSE '3'
END AS test_value

FROM client;

On Feb 23, 2009, at 5:09 PM, Jessi Berkelhammer wrote:

> Hi,
>
> I have a view in which I want to randomly assign values if certain
> conditions hold. I was getting surprising results. Here is a (very)
> simplified version of the view, which seems to indicate the problem:
>
> CREATE OR REPLACE VIEW test_view AS
> SELECT
>   CASE
>   WHEN random() < . THEN '1'
>   WHEN random() < . THEN '2'
>   ELSE '3'
>   END AS test_value
>
> FROM client ;
>
> It seems this should generate a random number between 0 and 1, and set
> test_value to '1' if this first generated number is less than ..
> Otherwise, it should generate another random number, and set  
> test_value
> to '2' if this is less than .. And if neither of the random  
> numbers
> are less than ., it should set test_value to '3'. It seems to me
> that there should be a relative even distribution of the 3 values.
>
> However when I run this, the values are always similar to what is  
> below:
>
> X_test=>  select test_value, count(*) from test_view group by 1  
> order by 1;
> test_value | count
> +---
> 1  | 23947
> 2  | 16061
> 3  | 32443
>
> Why are there significantly fewer 2s? I understand that random() is  
> not
> truly random, and that the seed affects this value. But it still
> confuses me that, no matter how many times I run this, there are  
> always
> so few 2s. If it is generating an independent random number in the
> second call to random(), then I don't know why there are more so many
> more 1s than 2s.
>
> Thanks!
> -jessi
>
> -- 
> Jessi Berkelhammer
> Downtown Emergency Service Center
> Computer Programming Specialist







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

NIWA is the trading name of the National Institute of Water & Atmospheric 
Research Ltd.

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

Re: [Fwd: Re: [GENERAL] surprising results with random()]

[Tom Lane]

John R Pierce  writes:
> ries van Twisk wrote:
>> should the function not look like this???
>> 
>> CREATE OR REPLACE VIEW test_view AS
>> SELECT
>> CASE
>> WHEN random() < .3 THEN '1'
>> WHEN random() < .5 THEN '2'
>> ELSE '3'
>> END AS test_value
>> 
>> FROM client;

> actually, I'd think that should be .6

Nah, ries is correct.  Think of it this way: one-third of the time the
first WHEN succeeds, and you get '1'.  In *half of the remaining cases*,
you want '2', so the second test should be against 0.5.

> but... wouldn'tfloor(random() * 3.0) + 1  be a simpler way to do this?

Agreed...

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] surprising results with random()

[raf]

Steve Atkins wrote:

> 
> On Feb 23, 2009, at 2:09 PM, Jessi Berkelhammer wrote:
> 
> >Hi,
> >
> >I have a view in which I want to randomly assign values if certain
> >conditions hold. I was getting surprising results. Here is a (very)
> >simplified version of the view, which seems to indicate the problem:
> >
> >CREATE OR REPLACE VIEW test_view AS
> >SELECT
> > CASE
> > WHEN random() < . THEN '1'
> > WHEN random() < . THEN '2'
> > ELSE '3'
> > END AS test_value
> >
> >FROM client ;
> >
> >It seems this should generate a random number between 0 and 1, and set
> >test_value to '1' if this first generated number is less than ..
> >Otherwise, it should generate another random number, and set  
> >test_value
> >to '2' if this is less than .. And if neither of the random  
> >numbers
> >are less than ., it should set test_value to '3'. It seems to me
> >that there should be a relative even distribution of the 3 values.
> >
> >
> >However when I run this, the values are always similar to what is  
> >below:
> >
> >X_test=>  select test_value, count(*) from test_view group by 1  
> >order by 1;
> >test_value | count
> >+---
> >1  | 23947
> >2  | 16061
> >3  | 32443
> >
> >Why are there significantly fewer 2s? I understand that random() is  
> >not
> >truly random, and that the seed affects this value. But it still
> >confuses me that, no matter how many times I run this, there are  
> >always
> >so few 2s. If it is generating an independent random number in the
> >second call to random(), then I don't know why there are more so many
> >more 1s than 2s.
> 
> Nope, it's nothing to do with random(), it's that your maths is wrong.
> 
> There are 9 possible cases. In 3 of them you return 1. In 2 of them you
> return 2. In the remaining 4 cases you return 3.
> 
> If you were to run this 72451 times I'd expect to see
> 1:  24150 = 72451 * 3/9
> 2: 16100 = 72451 * 2/9
> 3: 32200 = 72451 * 4/9
> 
> Which, unsurprisingly, is fairly close to what you get.
> 
> Cheers,
>   Steve

this looks like an attempt to understand the monty hall problem.
http://en.wikipedia.org/wiki/Monty_Hall_problem
except that there's no goat :(

cheers,
raf


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

Re: [GENERAL] High cpu usage after many inserts

[Jordan Tomkinson]

On Tue, Feb 24, 2009 at 12:35 AM, Scott Marlowe wrote:

> On Mon, Feb 23, 2009 at 1:29 AM, Jordan Tomkinson 
> wrote:
> > As per the spreadsheet
> > (http://spreadsheets.google.com/pub?key=pu_k0R6vNvOVP26TRZdtdYw) CPU
> usage
> > is around 50% and starts climbing over 3 hours until we have just under
> > 10,000 rows of data then stays at 99% for the duration of all future
> tests.
> > Once the rows are removed the tests start back down at 50% usage again.
>
> Oh, ok. well that's pretty normal as the indexes grow large enough to
> not fit in cache, then not fit in memory, etc...  Are you noticing a
> sharp dropoff in performance?
>


Again as per the spreadsheet, you can see the tests normally take around 130
seconds to complete, but after many rows are inserted they start to take
upwards of 500 seconds. I can leave the server idle for days (over the
weekend infact) and start a new test to reproduce the results (500+
seconds), so i dont think its a delayed write issue (surely 2 days is
enough?)

What configuration options should I be looking at to make sure it fits in
the cache? I have 8GB available and no matter how much I tweak i cannot get
it using any more than 2GB. the DB is almost 4GB in size on disk and as this
is a dedicated sql server for just 1 database, id really like to have the
whole thing in memory if possible.

[GENERAL] Poor select count(*) performance

[Mike Ivanov]

Hi there,

I'm sorry for a stupid question but I'm really stuck.

A query:

SELECT COUNT(*) FROM "lingq_card" WHERE "lingq_card"."context_id" = ...;

An hour ago it took 8 seconds, one minute ago the same query took just only
7 milliseconds.

Any ideas why the execution time varies so wildly?

Explain Analyze gives:

Aggregate  (cost=2000.08..2000.09 rows=1 width=0) (actual time=6.962..6.963
rows=1 loops=1)
   ->  Index Scan using lingq_card_context_id on lingq_card
(cost=0.00..1998.68 rows=561 width=0) (actual time=0.025..5.045 rows=2830
loops=1)
 Index Cond: (context_id = 68672)
 Total runtime: 7.011 ms

The lingq_cards table contains about 1.4 million rows.

Thanks,
Mike

Re: [GENERAL] Poor select count(*) performance

[Scott Marlowe]

On Mon, Feb 23, 2009 at 6:44 PM, Mike Ivanov  wrote:
> Hi there,
>
> I'm sorry for a stupid question but I'm really stuck.
>
> A query:
>
> SELECT COUNT(*) FROM "lingq_card" WHERE "lingq_card"."context_id" = ...;
>
> An hour ago it took 8 seconds, one minute ago the same query took just only
> 7 milliseconds.

The two common causes are caching and changing query plans.

How many rows did it have to hit, did it use an index, which index did
it use, and were the rows it needed already in the pg shared_buffers
OR the OS / kernel file system cache when retrieved?

-- 
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] High cpu usage after many inserts

[Greg Smith]

On Mon, 23 Feb 2009, Scott Marlowe wrote:

well that's pretty normal as the indexes grow large enough to not fit in 
cache, then not fit in memory, etc...


Right, the useful thing to do in this case is to take a look at how big 
all the relations (tables, indexes) involved are at each of the steps in 
the process.  The script at http://wiki.postgresql.org/wiki/Disk_Usage 
will show you that.  That will give some feedback on whether the 
vacuum/reindex methodology is really doing what you expect, and it will 
also let you compare the size of the table/index with how much RAM is in 
the system.


Have you done any tuning of the postgresql.conf file?  If you haven't 
increased shared_buffers substantially, you could be seeing buffer cache 
churn as the CPU spends all its time shuffling buffers between PostgreSQL 
and the OS once the working set involved exceeds around 32MB.


Shouldn't someone have ranted about RAID-5 by this point in the thread?

--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] Product Roadmap question and request for recommendation

[Bruce Momjian]

Dot Yet wrote:
> don't fix if it ain't broken ;)
> 
> no really, if the system is capable of working nearly flawlessly till 8.4 is
> baked, then it is sound to wait till then, especially when upgrading to a
> newer version is a *painful* as in time consuming task. i am assuming
> downgrading (just incase things don't workout that easily) will be equally
> painful.

Actually, pg_migratory will allow upgrades from 8.3 to 8.4 so you could
upgrade to 8.3 and upgrade to 8.4 rapidly.  I am unsure if an upgrade
from 8.2 to 8.4 is possible.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
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] Poor select count(*) performance

[Sam Mason]

On Mon, Feb 23, 2009 at 05:44:05PM -0800, Mike Ivanov wrote:
> An hour ago it took 8 seconds, one minute ago the same query took just only
> 7 milliseconds.
> 
> Any ideas why the execution time varies so wildly?
> 
> Explain Analyze gives:
> 
> Aggregate  (cost=2000.08..2000.09 rows=1 width=0) (actual time=6.962..6.963 
> rows=1 loops=1)
>->  Index Scan using lingq_card_context_id on lingq_card
> (cost=0.00..1998.68 rows=561 width=0) (actual time=0.025..5.045 rows=2830 
> loops=1)
>  Index Cond: (context_id = 68672)
>  Total runtime: 7.011 ms

If you're unlucky in the example above, none of those 2830 rows will be
in memory and you'll have to wait for the disk to bring them all back.
Depending on where these are on disk and how fast your disks are this
could take up to 30 seconds.

If you want this sort of thing to go quicker you could try CLUSTERing
the table on this index, but then this will slow down other queries that
want data to come off the disk in a specific order.  It's a balancing
act!

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

[GENERAL] PostgreSQL fast query is too slow as function

[Eus]

Hi Ho!

The plain query runs for about 8.28 minutes.
But, when the same query is put inside a function as follows:

--- 8< ---
create or replace function get_outgoing_transactions(
area char(3),
start_at timestamp with time zone,
end_at timestamp with time zone) returns setof record as
$$
  begin
  return query (
  -- the plain query --
  );
  end;
$$ language plpgsql;
--- 8< ---

and called as follows:

--- 8< ---
select *
from get_outgoing_transactions('sew'
   , '2008-05-30 00:00:00'
   , '2008-10-30 00:00:00'
  )
  as (production_order_id character varying(15)
  , item_id integer
  , tag_id character varying(15)
  , color_abbrv_description character 
varying(15)
  , size_id character varying(10)
  , prev_grade character varying(10)
  , grade character varying(10)
  , audit_ts timestamp with time zone
  , from_area char(3)
  , into_area char(3)
 )
--- 8< ---

it runs for about 21.50 minutes.

I have read this blog: 
http://blog.endpoint.com/2008/12/why-is-my-function-slow.html, and therefore, I 
ran each case two times. The running times that I write above were taken from 
the second run of each case, which is always shorter than the first run.

As described in the blog, I also have tried to find out whether or not a 
different query plan is used as follows:

--- 8< ---
prepare foobar(char(3)
   , timestamp with time zone
   , timestamp with time zone) as
-- the plain query --
;

explain execute foobar('sew'
   , '2008-05-30 00:00:00'
   , '2008-10-30 00:00:00');
--- 8< ---

The query plan is just the same with `explain -- the plain query --' with a 
difference that the plain query has castings on the plain parameters.

I thought the bottle neck was in the use of `returns setof record'.
But, changing it to just return the table does not change the situtation.

Any idea as to how I should attack this problem?

Thank you.


Best regards,
Eus (FSF member #4445)

In this digital era, where computing technology is pervasive, your freedom 
depends on the software controlling those computing devices.

Join free software movement today! It is free as in freedom, not as in free 
beer!

Join: http://www.fsf.org/jf?referrer=4445


  

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

[GENERAL] javascript and postgres

[野村]

Hello all.

My javascript connects with postgres using php.
php responds with XML for my select request.
I wonder is there any way to access to postgres directly?
I mean like this.

   new PGSQL.Request  (
'postgres.server.com'
,{
,port: 5432
,sql : 'select * from bra'
,asynchronous : true
,parameters : ''
,onComplete : fetch_function
,onLoaded : function(){
$( 'status' ).innerHTML="Loading...";
}
,onFailure : function(){
$( 'status' ).innerHTML= "error";
}
}
);

regards


-- 
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] surprising results with random()

[Scott Marlowe]

On Mon, Feb 23, 2009 at 5:16 PM, raf  wrote:

> this looks like an attempt to understand the monty hall problem.
> http://en.wikipedia.org/wiki/Monty_Hall_problem
> except that there's no goat :(

And what database management system can be complete without a goat? :)

-- 
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] High cpu usage after many inserts

[Jordan Tomkinson]

On Tue, Feb 24, 2009 at 11:42 AM, Greg Smith  wrote:

> On Mon, 23 Feb 2009, Scott Marlowe wrote:
>
>  well that's pretty normal as the indexes grow large enough to not fit in
>> cache, then not fit in memory, etc...
>>
>
> Right, the useful thing to do in this case is to take a look at how big all
> the relations (tables, indexes) involved are at each of the steps in the
> process.  The script at http://wiki.postgresql.org/wiki/Disk_Usage will
> show you that.  That will give some feedback on whether the vacuum/reindex
> methodology is really doing what you expect, and it will also let you
> compare the size of the table/index with how much RAM is in the system.
>
> Have you done any tuning of the postgresql.conf file?  If you haven't
> increased shared_buffers substantially, you could be seeing buffer cache
> churn as the CPU spends all its time shuffling buffers between PostgreSQL
> and the OS once the working set involved exceeds around 32MB.
>
> Shouldn't someone have ranted about RAID-5 by this point in the thread?
>
> --
> * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD
>

Right, i have done some more testing and I think its pretty conclusive.

1. Start with a known good copy of the database (some 3gb in size)
2. Run the Jmeter tests until ~7000 new rows are inserted equally over 3
tables. At this point performance goes to hell
3. delete the ~7000 rows from the db without re-indexing, (manually)
analyzing or anything of the sort.
4. performance instantly returns to that of before the tests began
(optimum).

So im thinking as Scott said it could be buffer/cache size filling up?
Here is my postgresql.conf, perhaps someone can make a few pointers.
The hardware is a Quad Xeon 2.0GHZ with 8GB RAM and 15K RPM SAS drives in
RAID 5 (i know raid 5, dont tell me)

max_connections = 400
shared_buffers = 2048MB
temp_buffers = 8MB
max_prepared_transactions = 10
work_mem = 8MB
maintenance_work_mem = 128MB
max_stack_depth = 4MB
vacuum_cost_delay = 0
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 200
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
fsync = on
synchronous_commit = on
wal_sync_method = fsync
full_page_writes = on
wal_buffers = 128kB
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
track_counts = on
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 2
autovacuum_vacuum_cost_delay = 20
autovacuum_vacuum_cost_limit = -1
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
escape_string_warning = off

Re: [GENERAL] javascript and postgres

[Craig Ringer]

野村 wrote:
> Hello all.
> 
> My javascript connects with postgres using php.
> php responds with XML for my select request.
> I wonder is there any way to access to postgres directly?

Nothing stops you passing SQL snippets from JavaScript into your PHP
code, which then dispatches then to the server and returns the results.

This is a really, really, REALLY bad idea. It allows anybody with the
ability to access your XML-RPC interface for PHP (say via XMLHttpRequest
in their browser) to send whatever SQL code they want to your server.

Do not do this unless you would also be comfortable opening the
PostgreSQL server port for direct Internet access and publishing the
username and password to use on your website. That's effectively what
you would be doing.

--
Craig Ringer

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

Re: [GENERAL] javascript and postgres

[John R Pierce]

野村 wrote:
> Hello all.
>
> My javascript connects with postgres using php.
> php responds with XML for my select request.
> I wonder is there any way to access to postgres directly?
>   

if you mean client side Javascript running on the end users web browser,
no, it should NOT be allowed to connect to a database server directly.
that would be a big security exposure, as well as probably trigger all
kind of security alerts on the webbrowser side.





-- 
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] javascript and postgres

[Scott Marlowe]

On Mon, Feb 23, 2009 at 11:54 PM, Craig Ringer
 wrote:
> 野村 wrote:
>> Hello all.
>>
>> My javascript connects with postgres using php.
>> php responds with XML for my select request.
>> I wonder is there any way to access to postgres directly?
>
> Nothing stops you passing SQL snippets from JavaScript into your PHP
> code, which then dispatches then to the server and returns the results.
>
> This is a really, really, REALLY bad idea. It allows anybody with the
> ability to access your XML-RPC interface for PHP (say via XMLHttpRequest
> in their browser) to send whatever SQL code they want to your server.

Note however that there is such a beast as server side javascript.

http://en.wikipedia.org/wiki/Server-side_JavaScript

-- 
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] High cpu usage after many inserts

[Jordan Tomkinson]

On Tue, Feb 24, 2009 at 11:42 AM, Greg Smith  wrote:

>
> Right, the useful thing to do in this case is to take a look at how big all
> the relations (tables, indexes) involved are at each of the steps in the
> process.  The script at http://wiki.postgresql.org/wiki/Disk_Usage will
> show you that.  That will give some feedback on whether the vacuum/reindex
> methodology is really doing what you expect, and it will also let you
> compare the size of the table/index with how much RAM is in the system.
>
>
taken before the ~7000 rows were entered.

   relation|  size
---+
 public.mdl_log| 595 MB
 public.mdl_forum_posts| 375 MB
 public.mdl_log_coumodact_ix   | 197 MB
 public.mdl_user   | 191 MB
 public.mdl_cache_text | 162 MB
 public.mdl_log_usecou_ix  | 137 MB
 public.mdl_log_act_ix | 119 MB
 public.mdl_log_cmi_ix | 97 MB
 public.mdl_log_tim_ix | 97 MB
 public.mdl_log_id_pk  | 97 MB
 public.mdl_question_states| 48 MB
 public.mdl_stats_user_daily   | 48 MB
 public.mdl_hotpot_responses   | 47 MB
 public.mdl_register_downloads | 45 MB
 public.mdl_message_read   | 37 MB
 public.mdl_course_display | 37 MB
 public.mdl_stats_user_weekly  | 31 MB
 public.mdl_mnet_log   | 27 MB
 public.mdl_user_ema_ix| 26 MB
 public.mdl_regidown_url_ix| 23 MB
(20 rows)

Re: [GENERAL] Poor select count(*) performance

[Mike Ivanov]

On Mon, Feb 23, 2009 at 6:54 PM, Sam Mason  wrote:

Depending on where these are on disk and how fast your disks are this
> could take up to 30 seconds.


This does not sound very inspiring :-)

Would throwing more hardware (memory, faster CPU) at the server improve the
situation?


> If you want this sort of thing to go quicker you could try CLUSTERing


This can help because all other queries to this table alway filtered/grouped
by context_id field. I will try that.

Thanks a lot, Sam!