Re: [GENERAL] function "XXX" already exists with same argument types

On 10/05/2011 05:27 PM, Alexander Farber wrote: Thank you Michael, but no - On Wed, Oct 5, 2011 at 5:24 PM, Michael Glaesemann wrote: psql:pref-2011-10-05-a.sql:339: ERROR:  function "pref_update_match" already exists with same argument types ALTE

Re: [GENERAL] I/O error on data file, can't run backup

On 10/06/2011 02:15 PM, Leif Biberg Kristensen wrote: Model Family: Seagate Barracuda 7200.11 family Device Model: ST31000340AS Serial Number:9QJ1ZMHY Firmware Version: SD15 Oh, joy. I have some of those, and can confirm their data-eating powers. Thankfully mine were in a backup s

Re: [GENERAL] Problem with pg_upgrade 9.0 -> 9.1

Bruce Momjian, 06.10.2011 02:15: I now got the same error (alas with a different relation id) while migrating a completely different data directory. Anything I can do to help find the reason for this problem (or bug?) Unfortuantely the data contains some confidential information so I cannot mak

Re: [GENERAL] user-interface to upload csv files

Robert Buckley wrote: > I am having problems getting csv files into postgres. Does anyone know if > there is an opensource user- > interface to tackle this? Did you try the COPY command (or \copy in psql)? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] Problem dbi_link with postgresql 9.04

Emanuel Araújo wrote: > The field where the problem occurs is of type float or double, when > extracting data from firebird, it > creates the tables materialized as type text. There is problem with null > values ​​or zero bytes. There > are three records with value "1.5" and are those records tha

[GENERAL] Postgres 8.4: archive_timeout vs. checkpoint_timeout

Hi folks, I want to refer to a question Rob did back in 2008 at http://archives.postgresql.org/pgsql-general/2008-07/msg01167.php as we are currently running into a similar question: We are using warm standby via PITR using a shared drive between master and slave node. Our setup currently is set

[GENERAL] problem with round() in php script

hi, In pgadmin3 this query works ok. SELECT name,round(ges_kw,0) FROM energie.tennet_auswertung_2010; but in php 5.3.2 the result is 0 for all rows $result = pg_query(' SELECT name,round(ges_kw,0) FROM energie.tennet_auswertung_2010 '); What should I do to round up to 0 decimal places? yours

Re: [GENERAL] function "XXX" already exists with same argument types

Alexander Farber wrote: >>> psql:pref-2011-10-05-a.sql:339: ERROR:  function "pref_update_match" >>> already exists with same argument types >>> ALTER FUNCTION >> Likely someone mistakenly added the functions to template1 of the machine >> you're restoring onto and >> they're getting added to the

Re: [GENERAL] gaps/overlaps in a time table : current and previous row question

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of thomas veymont > Sent: Wednesday, October 05, 2011 5:35 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] gaps/overlaps in a time table : current and previous

Re: [GENERAL] problem with round() in php script

On 06/10/2011 08:59, Robert Buckley wrote: > hi, > > In pgadmin3 this query works ok. > > SELECT name,round(ges_kw,0) > FROM energie.tennet_auswertung_2010; > > but in php 5.3.2 the result is 0 for all rows > > $result = pg_query(' > SELECT name,round(ges_kw,0) > FROM energie.tennet_auswertung_

Re: [GENERAL] problem with round() in php script

Im not quite sure what you are referring tothis is my php code. The connection is included, but not in the sql query! Von: Raymond O'Donnell An: Robert Buckley Cc: "pgsql-general@postgresql.org" Gesendet: 10:42 Donnerstag, 6.Oktober 2011 Betreff: Re: [

Re: [GENERAL] Postgresql Data directory Issue

Dear all, Any update on the issue. Thanks Adarsh Sharma wrote: Dear all, I have a database server ( 10 databases near about 110 GB) running Postgresql-.8.3 ) Today I need to format that system but I an facing the below issues :- 1. I am trying to use the previous data directory (/opt/P

Re: [GENERAL] Restoring 2 Tables From All Databases Backup

Thanks to all, the problem is solved now. But Still I donot know how to use existing data directory (near about 110GB) in a new Postgres Installation. I ask this in the list yesterday but still no clue on this. Please guide if it is possible. Best Regards Adarsh Raghavendra wrote: Hi Ad

Re: [GENERAL] gaps/overlaps in a time table : current and previous row question

2011/10/6 : >> -Original Message- >> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- >> ow...@postgresql.org] On Behalf Of thomas veymont >> Sent: Wednesday, October 05, 2011 5:35 PM >> To: pgsql-general@postgresql.org >> Subject: [GENERAL] gaps/overlaps in a time table : c

Re: [GENERAL] Postgresql Data directory Issue

On 6 October 2011 11:04, Adarsh Sharma wrote: > Dear all, > > Any update on the issue. Apparently, pg-migrator (later called pg_upgrade) is available for pg 8.3, see here for instructions: http://www.depesz.com/index.php/2010/05/19/waiting-for-9-0-pg_upgrade/ -- If you can't see the forest for

[GENERAL] Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ?

Hi list, I've got a heavily-updated table, and about 30 customers on the same system each with his own version of the table. The 3 configured autovacuum workers take turns vacuuming the table in each customer db; autovacuum is never idle and takes a large part of the available IO. Fearing that

Re: [GENERAL] problem with round() in php script

On 06/10/2011 10:02, Robert Buckley wrote: > Im not quite sure what you are referring tothis is my php code. The > connection is included, but not in the sql query! > > > $connection=pg_connect("host=*** port=*** dbname=zgb user=*** > password=***"); > > $result = pg_query(' > SELECT name,r

Re: [GENERAL] Postgresql Data directory Issue

We can upgrade to new version if our previous version is running. But In my Case I have only /opt/postgresql-8.3/data directory ( 110 Gb). My server Crashes down and Postgres is not running. I copied postgresql data directory to a new server and installed Postgresql8.3 but still it is not using

Re: [GENERAL] Postgresql Data directory Issue

On 6 October 2011 12:18, Adarsh Sharma wrote: > We can upgrade to new version if our previous version is running. But isn't upgrading exactly what you're trying to do? And why do you think you need to have the old database running to be able to upgrade? According to the link I sent you, you don't

[GENERAL] Strange primary key error on insertion

I have a strange issue (postgres 8.4) trying to insert old rows back into the s_tbls table. A check on the primary key (n_id) between s_tbl_import and s_tbls yields no matches, yet the index on s_tbls.n_id yields an error when attempting to insert: => select n_id from s_tbl_import where n_id IN (

Re: [GENERAL] Restoring 2 Tables From All Databases Backup

2011/10/6 Adarsh Sharma > ** > Thanks to all, the problem is solved now. > > But Still I donot know how to use existing data directory (near about > 110GB) in a new Postgres Installation. > I ask this in the list yesterday but still no clue on this. > Please guide if it is possible. > > You need

Re: [GENERAL] Strange primary key error on insertion

2011/10/6 Rory Campbell-Lange > I have a strange issue (postgres 8.4) trying to insert old rows back > into the s_tbls table. A check on the primary key (n_id) between > s_tbl_import and s_tbls yields no matches, yet the index on s_tbls.n_id > yields an error when attempting to insert: > > => sel

Re: [GENERAL] Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ?

On Thu, Oct 06, 2011 at 11:58:15AM +0200, Vincent de Phily wrote: > Fearing that vacuuming might accumulate lateness and hoping to see the system > idle every now and then, Why is your goal to see the system idle every now and then? It's not going to get tired if it keeps working, and if you ha

Re: [GENERAL] Restoring 2 Tables From All Databases Backup

On 06/10/2011 11:34, Filip Rembiałkowski wrote: > To use existing data directory in new installation, you can just stop > the server, replace data_directory, and start the server. Remember about > file permissions - data_directory must be owned by server process owner > ("postgres") and chmod 700.

[GENERAL] script not working in php

Hi, this works ok in pgadmin3 select name[i], kw[i] from ( select generate_series(1,6) as i,                array['Biomasse', 'Deponiegas', 'Solar', 'Wasser', 'Wind','GESAMT'] as name,                array[biomasse_kw, deponiegas_kw, solar_kw,wasser_kw, wind_kw, kw] as kw        from energie.te

Re: [GENERAL] script not working in php

On 06/10/2011 13:22, Robert Buckley wrote: > Hi, > > this works ok in pgadmin3 > > select name[i], kw[i] > from ( select generate_series(1,6) as i, > array['Biomasse', 'Deponiegas', 'Solar', 'Wasser', > 'Wind','GESAMT'] as name, > array[biomasse_kw, deponiegas_kw, so

Re: [GENERAL] Restoring 2 Tables From All Databases Backup

That's the bottleneck I need to solve:- Previous data & Os Version :- Postgresql-8.3 and Suse Enterprise Linux New Data & OS Version :- Postgresql-8.4 and Ubuntu 10.04 What to do know? Thanks Raymond O'Donnell wrote: On 06/10/2011 11:34, Filip Rembiałkowski wrote: To use existing

[GENERAL] ts_rank error/result of '1e-020'

I have a workaround to the error/result, but am wondering what the result of ts_rank of '1e-020' represents? Here is the original: select ts_rank(to_tsvector('a_a_do_ug_read_retreqmon_ptam'),to_tsquery('a_a_do_ug_read_retrmso.com_ptam'))

Re: [GENERAL] ts_rank error/result of '1e-020'

it sent before I finished, here is the rest: I have fixed this by doing the following: select ts_rank(to_tsvector(replace('a_a_do_ug_read_retreqmon_ptam','_',' ')),plainto_tsquery(replace('a_a_do_ug_read_retrmso.com_ptam','_',' '))) so I have found a solution, just wondering what the earlier err

Re: [GENERAL] script not working in php

On 10/06/2011 09:22 AM, Robert Buckley wrote: Hi, this works ok in pgadmin3 select name[i], kw[i] from ( select generate_series(1,6) as i, array['Biomasse', 'Deponiegas', 'Solar', 'Wasser', 'Wind','GESAMT'] as name, array[biomasse_kw, deponiegas_kw, solar_kw,wasser_

Re: [GENERAL] Restoring 2 Tables From All Databases Backup

follow the other thread that you started. there are some suggestions for you. 2011/10/6 Adarsh Sharma > ** > That's the bottleneck I need to solve:- > > Previous data & Os Version :- Postgresql-8.3 and Suse Enterprise Linux > New Data & OS Version :- Postgresql-8.4 and Ubuntu 10.04 > > W

Re: [GENERAL] script not working in php

Thankssimple but important! Von: Rodrigo Gonzalez An: Robert Buckley Cc: "pgsql-general@postgresql.org" Gesendet: 14:39 Donnerstag, 6.Oktober 2011 Betreff: Re: [GENERAL] script not working in php On 10/06/2011 09:22 AM, Robert Buckley wrote: Hi, > > >th

Re: [GENERAL] Selecting All Columns Associated With Maximum Value of One Column

On Wed, 5 Oct 2011, Chris Curvey wrote: Based on your subject line, I'm guessing that you want something like this: select quant, param, site_id, sample_date, str_name from chemistry where param = 'TDS' and str_name = 'BurrowCrk' and quant = (select max(quant) from chemistry where param = 'TDS'

Re: [GENERAL] Selecting All Columns Associated With Maximum Value of One Column

On Wed, 5 Oct 2011, David Johnston wrote: "Max" is an aggregate function and thus requires one of: 1) GROUP BY 2) "Window" - max(quant) OVER (PARTITION BY ...) To be present in the query. David, I was unaware of the windows functions. I see the document page for 9.0.5 so I'll carefully rea

Re: [GENERAL] Selecting All Columns Associated With Maximum Value of One Column

On Wed, 5 Oct 2011, David Johnston wrote: 2) "Window" - max(quant) OVER (PARTITION BY ...) Hmm-m-m. I have a problem here emulating the example on the document page. Regardless of which column is first after SELECT postgres tells me that column does not exist. select site_id, sample_date,

Re: [GENERAL] Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ?

On Thursday 06 October 2011 07:00:20 Andrew Sullivan wrote: > On Thu, Oct 06, 2011 at 11:58:15AM +0200, Vincent de Phily wrote: > > Fearing that vacuuming might accumulate lateness and hoping to see the > > system idle every now and then, > > Why is your goal to see the system idle every now and t

Re: [GENERAL] Strange primary key error on insertion

On 06/10/11, Filip Rembiałkowski (plk.zu...@gmail.com) wrote: > 2011/10/6 Rory Campbell-Lange > > => insert into s_tbls (select * from s_tbl_import); > > > >ERROR: duplicate key value violates unique constraint "s_tbls_pkey" > > > > > Looks like you had duplicates in s_tbl_import. Try this: >

Re: [GENERAL] Selecting All Columns Associated With Maximum Value of One Column

On Oct 6, 2011, at 9:34, Rich Shepard wrote: > On Wed, 5 Oct 2011, David Johnston wrote: > >> 2) "Window" - max(quant) OVER (PARTITION BY ...) > > Hmm-m-m. I have a problem here emulating the example on the document page. > Regardless of which column is first after SELECT postgres tells me th

Re: [GENERAL] Selecting All Columns Associated With Maximum Value of One Column

On Thu, 6 Oct 2011, David Johnston wrote: Missing the FROM before chemistry D'oh! Obviously not yet sufficiently cafinated this morning. Also, with the window function can I limit the output to a single str_name and param? Not directly. After you create the windowed result you can turn

Re: [GENERAL] Selecting All Columns Associated With Maximum Value of One Column

Rich Shepard, 06.10.2011 15:13: I was unaware of the windows functions. I see the document page for 9.0.5 so I'll carefully read that and upgrade from 9.0.4 (which may also have this function; I'll check). Windowing functions are available since 8.4 -- Sent via pgsql-general mailing list (pg

[GENERAL] Backup Database Question

So I'm looking to start regularly backing up my production database at work. I'm tired of doing it manually every day before I go home. I use the built in 'pg_dump' or 'pg_dumpall' utilities however I don't know which is more beneficial for a nightly backup. Perhaps I should be using the 'pg_dumpal

Re: [GENERAL] Backup Database Question

Carlos Mennens writes: > So I'm looking to start regularly backing up my production database at > work. I'm tired of doing it manually every day before I go home. I use > the built in 'pg_dump' or 'pg_dumpall' utilities however I don't know > which is more beneficial for a nightly backup. Perhaps

Re: [GENERAL] Backup Database Question

On Thu, Oct 6, 2011 at 11:19 AM, Tom Lane wrote: > Use pg_dumpall.  The extra time to dump the user and database > definitions is unlikely to be noticeable, and if push comes to shove > you'll be glad you had them. Yes I agree but I didn't know enough about PostgreSQL to make that determination.

Re: [GENERAL] Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ?

On Thu, Oct 06, 2011 at 03:39:02PM +0200, Vincent de Phily wrote: > In the case of vacuuming however, I think there's a point to be made about > finishing fast when all vacuum workers are constantly busy : say the vacuum > daemon notices that there are 10 tables that need vacuuming now. It alloca

Re: [GENERAL] plpgsql: type of array cells

On Wed, Oct 5, 2011 at 7:07 PM, Amit Dor-Shifer wrote: > I'm trying to use an array of objects in plpgsql (postgresql 8.4): > > drop type if exists test_t cascade; > > create type test_t AS > ( >        i integer, >        s text > ); > > create or replace function test2() >        RETURNS SETOF t

Re: [GENERAL] Backup Database Question

I have a little bash script that is called by cron to make a backup of the db, as well as being able to pull a copy of our production db to my local machine for development. It requires that you have a .pgpass file setup to connect to your database without entering a password. On Thu, Oct 6, 2011

[GENERAL] Postgres 9.01, Amazon EC2/EBS, XFS, JDBC and lost connections

We've been running into a particularly strange problem that I'm trying to better understand. The super short version is that our application servers lose their connection to the database when I run a backup during periods of higher load and fail to reconnect. Here's an overview of the setup: - Po

[GENERAL] Tuning Variables For PostgreSQL

I read all the time that most DBA's are required or should tune their DBMS which obviously in my case would be PostgreSQL but I'm curious what exactly is involved when tuning a DBMS like PostgreSQL. What are some of the basic functions involved when tuning? Are there specific things I should tweak

Re: [GENERAL] Backup Database Question

On 10/06/11 8:26 AM, Carlos Mennens wrote: Yes I agree but I didn't know enough about PostgreSQL to make that determination. Seems very logical however. Does anyone know of a PostgreSQL backup script floating around the Internet for Linux systems? I found a great one for MySQL but sadly that does

Re: [GENERAL] Backup Database Question

On Thu, Oct 6, 2011 at 3:12 PM, John R Pierce wrote: >   /path/to/pg_dumpall | gzip > /path/to/pgbackup-$(date -I).sql.gz Thanks John. I've never written a script so do I just use 'Vim' to open a new file and just paste the following line? #!/bin/bash /usr/bin/pg_dumpall | gzip > /var/db_backup/

Re: [GENERAL] Backup Database Question

On Thu, Oct 6, 2011 at 1:18 PM, Carlos Mennens wrote: > On Thu, Oct 6, 2011 at 3:12 PM, John R Pierce wrote: >>   /path/to/pg_dumpall | gzip > /path/to/pgbackup-$(date -I).sql.gz > > Thanks John. I've never written a script so do I just use 'Vim' to > open a new file and just paste the following

Re: [GENERAL] Backup Database Question

On 06/10/2011 20:18, Carlos Mennens wrote: > On Thu, Oct 6, 2011 at 3:12 PM, John R Pierce wrote: >> /path/to/pg_dumpall | gzip > /path/to/pgbackup-$(date -I).sql.gz > > Thanks John. I've never written a script so do I just use 'Vim' to > open a new file and just paste the following line? > >

[GENERAL] Connection Pooling

Can anyone recommend a good solution for connection pooling? Here is our setup: 2 PostgreSQL 9.1 servers (1 master, 1 hot standby). 1 Apache 2.2.17 We have a pretty extensive web application running on the apache server that talks to both of the database servers. Updates and small tasks (simp

Re: [GENERAL] Connection Pooling

On Thu, Oct 6, 2011 at 3:57 PM, Brandon Phelps wrote: > Can anyone recommend a good solution for connection pooling? Here is our > setup: > > 2 PostgreSQL 9.1 servers (1 master, 1 hot standby). > 1 Apache 2.2.17 > > We have a pretty extensive web application running on the apache server > that t

Re: [GENERAL] Problem with pg_upgrade 9.0 -> 9.1

Thomas Kellerer wrote: > Bruce Momjian, 06.10.2011 02:15: > >> I now got the same error (alas with a different relation id) while > >> migrating a completely different data directory. > >> > >> Anything I can do to help find the reason for this problem (or bug?) > >> > >> Unfortuantely the data con

Re: [GENERAL] Tuning Variables For PostgreSQL

2011/10/6 Carlos Mennens : > I read all the time that most DBA's are required or should tune their > DBMS which obviously in my case would be PostgreSQL but I'm curious > what exactly is involved when tuning a DBMS like PostgreSQL. What are > some of the basic functions involved when tuning? Are th

Re: [GENERAL] ts_rank error/result of '1e-020'

On Thu, Oct 6, 2011 at 4:37 PM, Gavin Flower wrote: > On 07/10/11 01:40, Henry Drexler wrote: > >> I have a workaround to the error/result, but am wondering what the result >> of ts_rank of '1e-020' represents? >> >> Here is the original: >> >> select >> ts_rank(to_tsvector('a_a_do_**ug_read_retre

Re: [GENERAL] Backup Database Question

what about pg_rman ? - On Fri, Oct 7, 2011 at 1:20 AM, Raymond O'Donnell wrote: > On 06/10/2011 20:18, Carlos Mennens wrote: > > O

[GENERAL] PostgreSQL consulting companies in the Bay Area

Hi everyone, I'm the founder and CEO of Academia.edu, which is a social networking site for researchers. We have about 2.5 million monthly unique visitors and about 9 million monthly page views. We use PostgreSQL on Amazon EC2, and currently our engineering team is 4 people. We're well aware that

Re: [GENERAL] ts_rank error/result of '1e-020'

On 07/10/11 10:56, Henry Drexler wrote: On Thu, Oct 6, 2011 at 4:37 PM, Gavin Flower mailto:gavinflo...@archidevsys.co.nz>> wrote: On 07/10/11 01:40, Henry Drexler wrote: I have a workaround to the error/result, but am wondering what the result of ts_rank of '1e-020' re

Re: [GENERAL] Selecting All Columns Associated With Maximum Value of One Column

On 07/10/11 03:03, Rich Shepard wrote: On Thu, 6 Oct 2011, David Johnston wrote: Missing the FROM before chemistry D'oh! Obviously not yet sufficiently cafinated this morning. [...] You just infringed my patent on making unprovoked stupid mistakes in posts to the Pg mailing lists! I th

Re: [GENERAL] Add quto increment to existing column

Robert -- >Hi, > > >I have a column in a table called hist_id with the datatype "integer". When I >created the table I assigned this column the primary key constraint but didn´t >make it an auto-increment column. > > >How could I do this to an the already existing column? > > >I have created the

Re: [GENERAL] Backup Database Question

On Thursday, October 06, 2011 7:49:38 am Carlos Mennens wrote: > So I'm looking to start regularly backing up my production database at > work. I'm tired of doing it manually every day before I go home. I use > the built in 'pg_dump' or 'pg_dumpall' utilities however I don't know > which is more be

Re: [GENERAL] Connection Pooling

On 07/10/11 06:57, Brandon Phelps wrote: Can anyone recommend a good solution for connection pooling? Here is our setup: 2 PostgreSQL 9.1 servers (1 master, 1 hot standby). 1 Apache 2.2.17 We have a pretty extensive web application running on the apache server that talks to both of the database

Re: [GENERAL] Postgres 9.01, Amazon EC2/EBS, XFS, JDBC and lost connections

Sean Laurent writes: > We've been running into a particularly strange problem that I'm trying to > better understand. The super short version is that our application servers > lose their connection to the database when I run a backup during periods of > higher load and fail to reconnect. > Here's