Re: [GENERAL] how to do this select?

2009-02-19 Thread Craig Ringer
Yi Zhao wrote: ok, thanks, I will create a new message when I post next time. And it's nice to reply below the original message, after cutting off the bits that don't matter anymore. It saves space and makes your messages easier for other people to read, which means you are more likely to

Re: [GENERAL] Service not starting during install

2009-02-19 Thread Richard Huxton
Frank Featherlight wrote: When I finally found a way around this, I tried to start the service via Administrative Tools (Services), it gives the following error: The PostgreSQL Database Server 8.3-service on Local Computer has been started and stopped. Some services stop automatically when

Re: [GENERAL] Removing a corrupt database by hand

2009-02-19 Thread Mike Christensen
After looking at it more, it seems the problem is it's trying to rebuild a corrupt DB and not really getting anywhere. The DB is about 900 megs, and I've given it 6-7hrs to try to repair and it just seems to be stuck. If I delete the DB directory, I get: 2009-02-19 00:02:05 PST FATAL: index

Re: [GENERAL] how to do this select?

2009-02-19 Thread A. Kretschmer
In response to Yi Zhao : ok, thanks, I will create a new message when I post next time. about my question, I think distinct can't solve my problem, because I want to get more than one rows. if there is more than (or equal) 2 (eg: 2, 3, 4, 100 ...)rows have the same value of column 'b' , I

Re: [GENERAL] Removing a corrupt database by hand

2009-02-19 Thread Mike Christensen
Hey all! After working non-stop for 7 hours straight, I managed to fix my problem.. It boiled down to a massive backlog of transactions, and I found a little utility called pg_resetxlog.exe that did the /exact/ thing I needed. I forced a reset of the transaction log and got the server to

Re: [GENERAL] postgres wish list

2009-02-19 Thread Sameer Mahajan
Thanks Craig. Comments inline. -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Thursday, February 19, 2009 12:56 PM To: Sameer Mahajan Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgres wish list Sameer Mahajan wrote: I recently

Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors

2009-02-19 Thread Grzegorz Jaśkiewicz
I bet it is on windows (judging by html in that email), but if isn't: open a console and issue: watch -n 0.5 df -h and run that insert again ;) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] postgres wish list

2009-02-19 Thread Grzegorz Jaśkiewicz
and I wish you didn't send emails with images attached to public lists... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgres wish list

2009-02-19 Thread John R Pierce
Sameer Mahajan wrote: [Sameer Mahajan] I will investigate how the unix domain sockets help in my case. Why isn't it the default for postgres installations? Or it isn't believed to be generic enough / straight forward enough to configure? hmm? Domain sockets -are- the default. just don't

Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors

2009-02-19 Thread Mike Christensen
Actually I'm writing emails on my Mac g However, the Postgres service is running on my Windows 2003 machine.. The disk space issue turned out to be a disk quota which was easy to solve. Unfortunately, the fact it crashed Postgres and with a massive transaction log left the server in a state

Re: [GENERAL] postgres wish list

2009-02-19 Thread Thomas Kellerer
Sameer Mahajan, 19.02.2009 07:38: * Shared memory based connectivity: As such postgres has client - server model. The TCP-IP nature of its connectivity further adds to the latency of this communication. It will be nice to have a shared memory based connectivity between libpq front end

[GENERAL] Accessing array elements in a FOR PL/pgsql loop

2009-02-19 Thread Igor Katson
For each element in the array, I need to make some operation with plpgsql. I usually use the syntax: DECLARE array_len int; BEGIN array_len := array_upper(i_array, 1); FOR i IN 1 .. array_len LOOP SOME OPERATION (i_array[i]) END LOOP; But I don't like that. Is there any built-in way to

Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors

2009-02-19 Thread Scott Marlowe
On Thu, Feb 19, 2009 at 3:11 AM, Mike Christensen ima...@comcast.net wrote: Actually I'm writing emails on my Mac g However, the Postgres service is running on my Windows 2003 machine.. The disk space issue turned out to be a disk quota which was easy to solve. Unfortunately, the fact it

Re: [GENERAL] Accessing array elements in a FOR PL/pgsql loop

2009-02-19 Thread A. Kretschmer
In response to Igor Katson : I think I need a built-in function to make a column from an array, like in the backwards operation SELECT ARRAY(column) By David Fetter: CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT LANGUAGE SQL AS $$SELECT $1[i] FROM

Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors

2009-02-19 Thread Mike Christensen
I would assume if the account the service is running under has limited disk space, it won't really matter what OS you're running under. Postgres will throw an out of disk space error. The problem for me is I was in the middle of a transaction which inserted about 50,000 rows into a table,

Re: [GENERAL] Pet Peeves?

2009-02-19 Thread Howard Cole
Top of my list would be: 1. Inability to do a PITR for a single database in a cluster. 2. Lack of support for Large Objects in master-slave replication. 3. Queries that I write are not corrected by postgres ;) One last thing - it peeves me that many of the people on the forums are so bloody

Re: [GENERAL] Accessing array elements in a FOR PL/pgsql loop

2009-02-19 Thread Igor Katson
A. Kretschmer wrote: In response to Igor Katson : I think I need a built-in function to make a column from an array, like in the backwards operation SELECT ARRAY(column) By David Fetter: CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT LANGUAGE SQL AS $$SELECT

Re: [GENERAL] Appending \o output instead of overwriting the output file

2009-02-19 Thread Jasen Betts
On 2009-02-18, Tom Lane t...@sss.pgh.pa.us wrote: John R Pierce pie...@hogranch.com writes: Tom Lane wrote: Brent Wood b.w...@niwa.co.nz writes: Perhaps \o+ as a future fix for this? I'd prefer \o file but maybe I'm too steeped in unix-isms. \o+ is reasonably consistent with the other \

Re: [GENERAL] Accessing array elements in a FOR PL/pgsql loop

2009-02-19 Thread Oleg Bartunov
On Thu, 19 Feb 2009, Igor Katson wrote: A. Kretschmer wrote: In response to Igor Katson : I think I need a built-in function to make a column from an array, like in the backwards operation SELECT ARRAY(column) By David Fetter: CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF

Re: [GENERAL] createdb.exe and psql.exe without Promting Password

2009-02-19 Thread Jasen Betts
On 2009-02-10, Inigo Barandiaran ibarandia...@vicomtech.org wrote: Hi. I would like to create an script that includes calls to createdb.exe and psql.exe. I would like to use them with a user but without asking or prompting for pasword. For example: createdb.exe -T template0 -U

Re: [GENERAL] Fwd: createdb.exe and psql.exe without Promting Password

2009-02-19 Thread Jasen Betts
On 2009-02-10, Iñigo Barandiaran ibarandia...@vicomtech.org wrote: Thanks Raymon. I'm using Windows XP. What does visible to the script means? means doing it right instead of wrong. for a targeted explanation post example code. -- Sent via pgsql-general mailing list

[GENERAL] Re: does anyone know what to use in pg_hba.conf that will allow me to run cronjobs with pg_dump?

2009-02-19 Thread Jasen Betts
On 2009-02-10, Jonathan Vanasco postg...@2xlp.com wrote: does anyone know what to use in pg_hba.conf that will allow me to run cronjobs with pg_dump? i think i just need a METHOD for localhost only. write a script that sets PGPASSWORD and then calls pg_dump, use .pgconf, or use trust or

Re: [GENERAL] Good Delimiter for copy command

2009-02-19 Thread Jasen Betts
On 2009-02-12, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: --0-1509090113-1234471898=:21817 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable My data fields contains commas, tabs,'|'=A0 etc. So I cant use them as del= imiters..so I need a unique may

[GENERAL] \l of psql 8.4 looks ... unreadable on 80char default

2009-02-19 Thread Harald Armin Massa
Hello, testing / using PostgreSQL 8.4, I queried the list of databases via \l in psql I get information about name, owner, coding, Collation, Ctype and access rights. Which wraps right in the middle of ctype, as collation and ctype both are German, Germany As a result: this basic information

Re: [GENERAL] Good Delimiter for copy command

2009-02-19 Thread Jasen Betts
On 2009-02-12, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: Im not using this for loading postgres data to postgres. Im trying this method to load my Oracle data to postgresql tables just trying to migrate my data from oracle to postgresql dump as CSV, properly implemented CSV isn't

Re: [GENERAL] Re: does anyone know what to use in pg_hba.conf that will allow me to run cronjobs with pg_dump?

2009-02-19 Thread Kusuma Pabba
Jasen Betts wrote: On 2009-02-10, Jonathan Vanasco postg...@2xlp.com wrote: does anyone know what to use in pg_hba.conf that will allow me to run cronjobs with pg_dump? i think i just need a METHOD for localhost only. write a script that sets PGPASSWORD and then calls pg_dump, use

Re: [GENERAL] \l of psql 8.4 looks ... unreadable on 80char default

2009-02-19 Thread Bruce Momjian
Harald Armin Massa wrote: Hello, testing / using PostgreSQL 8.4, I queried the list of databases via \l in psql I get information about name, owner, coding, Collation, Ctype and access rights. Which wraps right in the middle of ctype, as collation and ctype both are German, Germany

[GENERAL] UPDATE

2009-02-19 Thread c k
I am using PG on windows for development. While testing some queries I found that updates are much slower as compared to other database systems. (On avarage MySQL updates 4-5 times faster than PG in a single unique index on primary key) Why? Another thing that I noticed is when any single column

Re: [GENERAL] \l of psql 8.4 looks ... unreadable on 80char default

2009-02-19 Thread Harald Armin Massa
No difference at all. Line breaks are exactly as on screen (looks more crazy in email with non-fixed-with font) But really, linebreak betwwen Zug and riffsrechte, and cutting between =c/pos and tgres postgres=# \l Liste der Datenbanken Name| Eigentümer

Re: [GENERAL] \l of psql 8.4 looks ... unreadable on 80char default

2009-02-19 Thread Bruce Momjian
Harald Armin Massa wrote: postgres=# \pset format wrapped Ausgabeformat ist ?wrapped?. postgres=# \l Liste der Datenbanken Name| Eigent?mer | Kodierung |Collation| Ctype | Zug riffsrechte

Re: [GENERAL] Appending \o output instead of overwriting the output file

2009-02-19 Thread Geoffrey
Tom Lane wrote: Brent Wood b.w...@niwa.co.nz writes: Perhaps \o+ as a future fix for this? I'd prefer \o file but maybe I'm too steeped in unix-isms. regards, tom lane +1 -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little

Re: [GENERAL] UPDATE

2009-02-19 Thread c k
1.5 GB RAM, Pentium dual core, single 80 GB disk, Windows XP sp3, PostgreSQL 8.3.6 a table is like following: -- Table: accgroups -- DROP TABLE accgroups; CREATE TABLE accgroups ( accgroupid serial NOT NULL, accgroupidname character varying(150) NOT NULL DEFAULT ''::character varying,

Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors

2009-02-19 Thread Geoffrey
Mike Christensen wrote: I have well over 50 gigs free on that drive.. I doubt it. out of inodes. Scott Marlowe wrote: On Wed, Feb 18, 2009 at 1:39 PM, Mike Christensen ima...@comcast.net wrote: Hi all - ERROR: could not extend relation 1663/41130/41177: No space left on device HINT:

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread joel garry
On Feb 15, 10:09 pm, pg pen...@catholic.org wrote: I am involved with a SQL server project. The server would be used in a very heavy duty environment, with hundreds of thousands, if not millions of database enquiries per minutes. The server would run Linux or one of the BSD variant, with at

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Serge Rielau
pg wrote: PS. Although we prefer open-sourced programs, we definitely will pay for it !! No need to pay: http://www.ibm.com/software/data/db2/express/ -- Serge Rielau DB2 Solutions Development IBM Toronto Lab -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Paulie
On Feb 16, 6:09 am, pg pen...@catholic.org wrote: I am involved with a SQL server project. The server would be used in a very heavy duty environment, with hundreds of thousands, if not millions of database enquiries per minutes. Perhaps you should clarify here - what exactly do you mean by

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Geoff Muldoon
Jerry Stuckle says... pg wrote: The server would run Linux or one of the BSD variant You also missed DB2, SQL Server and several others. Scrap MSSQL Server as a candidate given the above O/S requirement. Geoff M Running 3 RHEL4 boxes in an Oracle RAC cluster, with web interfaces

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Michael Austin
Paulie wrote: On Feb 16, 6:09 am, pg pen...@catholic.org wrote: I am involved with a SQL server project. The server would be used in a very heavy duty environment, with hundreds of thousands, if not millions of database enquiries per minutes. Perhaps you should clarify here - what exactly

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Matthias Hoys
Should you choose an open-source, make sure your code AND your DDL uses as much ANSI standards as possible so when you do need to move to something else, it won't be as painful. (auto-incrementing columns vs. sequences etc...). I really wouldn't go for database independence ... Choose a

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Robert Klemme
On 16.02.2009 15:59, Walt wrote: pg wrote: I am involved with a SQL server project. The server would be used in a very heavy duty environment, with hundreds of thousands, if not millions of database enquiries per minutes. The server would run Linux or one of the BSD variant, with at least 32GB

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Serge Rielau
Matthias Hoys wrote: Should you choose an open-source, make sure your code AND your DDL uses as much ANSI standards as possible so when you do need to move to something else, it won't be as painful. (auto-incrementing columns vs. sequences etc...). I really wouldn't go for database

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Thomas Kellerer
Matthias Hoys wrote on 16.02.2009 22:56: Should you choose an open-source, make sure your code AND your DDL uses as much ANSI standards as possible so when you do need to move to something else, it won't be as painful. (auto-incrementing columns vs. sequences etc...). I really wouldn't go

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Jerry Stuckle
Geoff Muldoon wrote: Jerry Stuckle says... pg wrote: The server would run Linux or one of the BSD variant You also missed DB2, SQL Server and several others. Scrap MSSQL Server as a candidate given the above O/S requirement. Geoff M Running 3 RHEL4 boxes in an Oracle RAC cluster, with

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Geoff Muldoon
In article gncr90$61...@news.motzarella.org, Jerry Stuckle says... Geoff Muldoon wrote: Jerry Stuckle says... pg wrote: The server would run Linux or one of the BSD variant You also missed DB2, SQL Server and several others. Scrap MSSQL Server as a candidate given the above O/S

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Terry Dykstra
Geoff Muldoon geoff.muld...@trap.gmail.com wrote in message news:mpg.2404902b6db30900989...@news.x-privat.org... Jerry Stuckle says... pg wrote: The server would run Linux or one of the BSD variant You also missed DB2, SQL Server and several others. Scrap MSSQL Server as a candidate

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Marten Kemp
hpuxrac wrote: On Feb 16, 1:09 am, pg pen...@catholic.org wrote: I am involved with a SQL server project. The server would be used in a very heavy duty environment, with hundreds of thousands, if not millions of database enquiries per minutes. The server would run Linux or one of the BSD

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Michael Austin
Serge Rielau wrote: Matthias Hoys wrote: Should you choose an open-source, make sure your code AND your DDL uses as much ANSI standards as possible so when you do need to move to something else, it won't be as painful. (auto-incrementing columns vs. sequences etc...). I really wouldn't go

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Jerry Stuckle
Geoff Muldoon wrote: Jerry Stuckle says... The server would run Linux or one of the BSD variant You also missed DB2, SQL Server and several others. Scrap MSSQL Server as a candidate given the above O/S requirement. The OS should be picked based on the requirements of the database and rest

[GENERAL] Extended ability to alter column type when empty

2009-02-19 Thread David Andersen
Hi, I am a real newbee and I hope this is the right place to post a feature request. I am receiving data from a csv file where one column has a strange data format. It would be nice if I could use Copy From with to_timestamp to transform the date. As far as I know this is not possible to do in

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Gene Wirchenko
Paulie linehan.p...@gmail.com wrote: [snip] No CPU data, no disk array data - they haven't even chosen an OS and are not sure where to put their web server (and no mention of an app server tier!). It might be a real project, but it has a feel of irreality. I can not tell if it is wishful

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread joel garry
On Feb 16, 11:12 am, Paulie linehan.p...@gmail.com wrote: On Feb 16, 5:51 pm, joel garry joel-ga...@home.com wrote: Check out Oracle XE and apex.  No cost to you, and you can pay to scale as appropriate. Before rushing to download Oracle XE, check out

Re: [GENERAL] Which SQL is the best for servers?

2009-02-19 Thread Geoff Muldoon
Jerry Stuckle says... The server would run Linux or one of the BSD variant You also missed DB2, SQL Server and several others. Scrap MSSQL Server as a candidate given the above O/S requirement. The OS should be picked based on the requirements of the database and rest of the system,

[GENERAL] postgres wish list

2009-02-19 Thread Sameer Mahajan
I recently worked on rewriting a fairly complex Berkeley DB based system using Postgres DB. Following is a wish list and a brief explanation for each of those items for postgres. It is not necessarily a comparison of postgres functionality with that of Berkeley DB but some required useful

[GENERAL] connecting using libpq breaks printf

2009-02-19 Thread Joey Morris
This is my first attempt at using libpq, and I'm running across a strange problem. Here is my bare-bones program: #include stdio.h #include libpq-fe.h int main(int argc, char **argv) { PGconn *conn; fprintf(stderr, connecting\n); conn = PQconnectdb(dbname=postgres); PQfinish(conn);

Re: [GENERAL] Appending \o output instead of overwriting the output file

2009-02-19 Thread Barbara Stephenson
I didn't know you had time to look at these.. :) Geoffrey wrote: Tom Lane wrote: Brent Wood b.w...@niwa.co.nz writes: Perhaps \o+ as a future fix for this? I'd prefer \o file but maybe I'm too steeped in unix-isms. regards, tom lane +1 -- Until later, Geoffrey Those who

Re: [GENERAL] \l of psql 8.4 looks ... unreadable on 80char default

2009-02-19 Thread Harald Armin Massa
Thanks! What is your $COLUMNS set to? This should have wrapped to fit into the screen with. Also look at \pset columns: $COLUMNS was not set at all ... guess that is a usual environment variable on bash. Setting it to 80 works. Thank you very much, Harald -- GHUM Harald Massa persuadere

Re: [GENERAL] Extended ability to alter column type when empty

2009-02-19 Thread Osvaldo Kussama
2009/2/17 David Andersen mrdavidander...@gmail.com: Hi, I am a real newbee and I hope this is the right place to post a feature request. I am receiving data from a csv file where one column has a strange data format. It would be nice if I could use Copy From with to_timestamp to transform

Re: [GENERAL] postgres wish list

2009-02-19 Thread Chander Ganesan
Thomas Kellerer wrote: Sameer Mahajan, 19.02.2009 07:38: * Shared memory based connectivity: As such postgres has client - server model. The TCP-IP nature of its connectivity further adds to the latency of this communication. It will be nice to have a shared memory based connectivity

Re: [GENERAL] \l of psql 8.4 looks ... unreadable on 80char default

2009-02-19 Thread Gregory Stark
Harald Armin Massa haraldarminma...@gmail.com writes: Thanks! What is your $COLUMNS set to? This should have wrapped to fit into the screen with. Also look at \pset columns: $COLUMNS was not set at all ... guess that is a usual environment variable on bash. Setting it to 80 works.

[GENERAL] xpath functions

2009-02-19 Thread Francisco
Hi List, I have installed postgreSQL 8.3 in my Ubuntu Hardy Heron. I want to use xpath functions (like xpath_table), but I can't. An error appears xpath_table does not exists. How could I intregrate xpath funtions with my postgreSQL without reinstalling it? Any solution? Thanks ;)

Re: [GENERAL] postgres wish list

2009-02-19 Thread Craig Ringer
Sameer Mahajan wrote: [Sameer Mahajan] I will investigate how the unix domain sockets help in my case. Why isn't it the default for postgres installations? Or it isn't believed to be generic enough / straight forward enough to configure? It is the default. Just connect without a host

Re: [GENERAL] \l of psql 8.4 looks ... unreadable on 80char default

2009-02-19 Thread Bruce Momjian
Harald Armin Massa wrote: Thanks! What is your $COLUMNS set to? This should have wrapped to fit into the screen with. Also look at \pset columns: $COLUMNS was not set at all ... guess that is a usual environment variable on bash. Setting it to 80 works. Great. The wrapping mode is a

Re: [GENERAL] UPDATE

2009-02-19 Thread Craig Ringer
c k wrote: I am using PG on windows for development. While testing some queries I found that updates are much slower as compared to other database systems. (On avarage MySQL updates 4-5 times faster than PG in a single unique index on primary key) Why? Is your MySQL instance using MyISAM

Re: [GENERAL] UPDATE

2009-02-19 Thread Craig Ringer
c k wrote: CREATE TABLE accgroups ( accgroupid serial NOT NULL, accgroupidname character varying(150) NOT NULL DEFAULT ''::character varying, accgroupname character varying, createdby integer DEFAULT 0, createdtimestamp timestamp without time zone DEFAULT

Re: [GENERAL] xpath functions

2009-02-19 Thread Francisco
I saw it,but it says: Use of many of these functions requires the installation to have been built with configure --with-libxml When I installed postgreSQL I did not include this option, have I to reinstall postgresql? 2009/2/19 Osvaldo Kussama osvaldo.kuss...@gmail.com 2009/2/19 Francisco

Re: [GENERAL] UPDATE

2009-02-19 Thread Tom Lane
Craig Ringer cr...@postnewspapers.com.au writes: AFAIK if you run the UPDATE while there are no other concurrent transactions, Pg will write the new values in-place. It still updates the WAL first, but it won't create whole new copies of each record as well. That's the case at least if the

Re: [GENERAL] \l of psql 8.4 looks ... unreadable on 80char default

2009-02-19 Thread Harald Armin Massa
Gregory, $COLUMNS was not set at all ... guess that is a usual environment variable on bash. Setting it to 80 works. COLUMNS wasn't set and \pset columns wasn't set? What environment were you running this psql command in? Was the output redirected anywhere with \o or with on the

[GENERAL] psql: not enogh memory when trying to use utf8 on windows

2009-02-19 Thread Harald Armin Massa
C:\hamchcp 65001 Aktive Codepage: 65001. C:\hampsql -U postgres Passwort f�r Benutzer postgres: psql (8.4devel) WARNING: Console code page (65001) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page Notes for Windows users for

Re: [GENERAL] UPDATE

2009-02-19 Thread Craig Ringer
Tom Lane wrote: Craig Ringer cr...@postnewspapers.com.au writes: AFAIK if you run the UPDATE while there are no other concurrent transactions, Pg will write the new values in-place. It still updates the WAL first, but it won't create whole new copies of each record as well. That's the case at

Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors

2009-02-19 Thread John R Pierce
Mike Christensen wrote: First off, when Postgres starts and sees that your database was not closed properly, it should tell you there's pending transactions and ask if you want to dump them or try to process them (or maybe save them for later). If you process them, there should be clear

Re: [GENERAL] UPDATE

2009-02-19 Thread Tom Lane
Craig Ringer cr...@postnewspapers.com.au writes: Tom Lane wrote: This is not correct; PG *never* overwrites an existing record (at least not in any user-accessible code paths). That's what I always thought, but I encountered some odd behaviour while trying to generate table bloat that made

Re: [GENERAL] postgres wish list

2009-02-19 Thread John R Pierce
Chander Ganesan wrote: Basically, if you try to use PostgreSQL as a local data store for a desktop app, you'll likely want to use socket connections. Unfortunately, if its a windows app, I'm not sure if there is much benefit to using a socket over tcp/ip . I thought at one point there was

[GENERAL] Open sourcing

2009-02-19 Thread Oliver Kohll - Mailing Lists
Hi, Wondering who has any practical experience of open sourcing a software product? I made a small internal database tool open source a while ago and that worked quite well - www.gtportalbase.com/opensource. People got involved in adding to the code and some people even paid a bit for

Re: [GENERAL] Query palns and tug-of-war with enable_sort

2009-02-19 Thread Glyn Astill
--- On Thu, 19/2/09, Tom Lane t...@sss.pgh.pa.us wrote: Also, it'd be worth revisiting the question of whether you really still need enable_sort off ... personally, I'd think that reducing random_page_cost is a much saner way of nudging the planner in the direction of preferring

[GENERAL] How to pipe the psql copy command to Unix 'Date' command

2009-02-19 Thread SHARMILA JOTHIRAJAH
Hi, I want to find the time taken by this process ...retrieving data from oracle database using java and copying that to postgres using copy. So I need something like this Start_Time|java testCode ...|psql -c copy dummy from stdin with delimiter ',' null 'NULL' test| End_time so that the

Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command

2009-02-19 Thread Mirko Pace
Why not call a function that make an insert as insert into dummy values ('Start',now()) ? Mirko On Thu, Feb 19, 2009 at 5:37 PM, SHARMILA JOTHIRAJAH sharmi...@yahoo.comwrote: Hi, I want to find the time taken by this process ...retrieving data from oracle database using java and copying

[GENERAL] Aliasing syntax question

2009-02-19 Thread Kevin Murphy
I've now seen the 'unnest' function defined in a few different ways with regard to aliases. Substitute the following pairs of values for X and Y in the query below, and all of them work (in PG 8.3.6, at least): X Y gg i g(i) gg(i) g.i g(i) create or replace function

Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command

2009-02-19 Thread Tom Lane
SHARMILA JOTHIRAJAH sharmi...@yahoo.com writes: Hi, I want to find the time taken by this process ...retrieving data from oracle database using java and copying that to postgres using copy. So I need something like this Start_Time|java testCode ...|psql -c copy dummy from stdin with

Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command

2009-02-19 Thread SHARMILA JOTHIRAJAH
--- On Thu, 2/19/09, Tom Lane t...@sss.pgh.pa.us wrote: From: Tom Lane t...@sss.pgh.pa.us Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command To: sharmi...@yahoo.com Cc: General postgres mailing list pgsql-general@postgresql.org Date: Thursday, February 19,

Re: [GENERAL] Aliasing syntax question

2009-02-19 Thread Tom Lane
Kevin Murphy mur...@genome.chop.edu writes: Substitute the following pairs of values for X and Y in the query below, and all of them work (in PG 8.3.6, at least): X Y gg i g(i) gg(i) g.i g(i) create or replace function unnest(anyarray) returns setof anyelement as $$

[GENERAL] Search for text in any function

2009-02-19 Thread Rob Richardson
Greetings! Can anyone suggest a query that will tell me the names of all functions (both trigger and normal) that contain a given string of text? Here's what happened: My company's database has a table named charge and a view named availcharges that returns all charges that are available.

Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors

2009-02-19 Thread Sam Mason
On Thu, Feb 19, 2009 at 02:58:02AM -0800, Mike Christensen wrote: I would assume if the account the service is running under has limited disk space, it won't really matter what OS you're running under. Postgres will throw an out of disk space error. Similarly to Scott, every time I've come

Re: [GENERAL] UPDATE

2009-02-19 Thread c k
Thanks to all replies. The main thing is that there was no index on the said column when I am updating and there are about 40+ other columns mostly of integer and smallint types. Also in MySQL I am using InnoDB tables. For both there is a single transaction when working, No other user is

Re: [GENERAL] Search for text in any function

2009-02-19 Thread hubert depesz lubaczewski
On Thu, Feb 19, 2009 at 12:46:41PM -0500, Rob Richardson wrote: Can anyone suggest a query that will tell me the names of all functions (both trigger and normal) that contain a given string of text? assuming you mean pl/* functions, you can simply use: select proname from pg_proc where prosrc

Re: [GENERAL] Search for text in any function

2009-02-19 Thread Rob Richardson
Thank you very much. It works, as I'm sure you knew. But I have a further question. I've never seen the ~* operator before, and searching for it in the docs and on Google did not return any results I could find. What does it mean? Thanks again! RobR -- Sent via pgsql-general mailing list

Re: [GENERAL] Search for text in any function

2009-02-19 Thread hubert depesz lubaczewski
On Thu, Feb 19, 2009 at 01:27:23PM -0500, Rob Richardson wrote: Thank you very much. It works, as I'm sure you knew. But I have a further question. I've never seen the ~* operator before, and searching for it in the docs and on Google did not return any results I could find. What does it

Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command

2009-02-19 Thread Erik Jones
On Feb 19, 2009, at 9:40 AM, SHARMILA JOTHIRAJAH wrote: --- On Thu, 2/19/09, Tom Lane t...@sss.pgh.pa.us wrote: From: Tom Lane t...@sss.pgh.pa.us Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command To: sharmi...@yahoo.com Cc: General postgres mailing list

Re: [GENERAL] When adding millions of rows at once, getting out of disk space errors

2009-02-19 Thread Mike Christensen
Yea sorry good point.. It's probably at least safe to say the process should not just hang though, and there should be more info in the log as to what it's doing.. John R Pierce wrote: Mike Christensen wrote: First off, when Postgres starts and sees that your database was not closed

Re: [GENERAL] Appending \o output instead of overwriting the output file

2009-02-19 Thread Erik Jones
On Feb 19, 2009, at 3:30 AM, Jasen Betts wrote: On 2009-02-18, Tom Lane t...@sss.pgh.pa.us wrote: John R Pierce pie...@hogranch.com writes: Tom Lane wrote: Brent Wood b.w...@niwa.co.nz writes: Perhaps \o+ as a future fix for this? I'd prefer \o file but maybe I'm too steeped in

Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command

2009-02-19 Thread SHARMILA JOTHIRAJAH
Thanks all -Sharmila --- On Thu, 2/19/09, Erik Jones ejo...@engineyard.com wrote: From: Erik Jones ejo...@engineyard.com Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command To: sharmi...@yahoo.com Cc: Tom Lane t...@sss.pgh.pa.us, General postgres mailing list

[GENERAL] NOVALIDATE in postgresql?

2009-02-19 Thread SHARMILA JOTHIRAJAH
Hi In Oracle I can use the NOVALIDATE for constraints... like this ALTER TABLE employee ADD CONSTRAINT emp_ck CHECK (married IN ('Y','N')) NO VALIDATE; When the table is already populated this will be faster. Can you do the same in Postgresql? Thanks Sharmila -- Sent via

Re: [GENERAL] NOVALIDATE in postgresql?

2009-02-19 Thread Osvaldo Kussama
2009/2/19 SHARMILA JOTHIRAJAH sharmi...@yahoo.com: Hi In Oracle I can use the NOVALIDATE for constraints... like this ALTER TABLE employee ADD CONSTRAINT emp_ck CHECK (married IN ('Y','N')) NO VALIDATE; When the table is already populated this will be faster. Can you do the same in

Re: [GENERAL] NOVALIDATE in postgresql?

2009-02-19 Thread Adrian Klaver
- SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: Hi In Oracle I can use the NOVALIDATE for constraints... like this ALTER TABLE employee ADD CONSTRAINT emp_ck CHECK (married IN ('Y','N')) NO VALIDATE; When the table is already populated this will be faster. Can you do the same in

Re: [GENERAL] Service not starting during install

2009-02-19 Thread Frank Featherlight
I could finish the install without starting the service by closing down msiexec with ctrl+alt+del. I looked up the log from postgresql and it says this: FATAL: could not reattach to shared memory (key=1804, addr=0170): 487 2009-02-19 15:51:01 CET LOG: database system was shut down at

[GENERAL] Trying to deal with a bloated index

2009-02-19 Thread Jack Orenstein
I have a postgres application, with customers using version 7.4 and the next version of the product being built on 8.3 (with HOT). We are having problems with two bloated indexes. One index is on a field whose value never changes. The other, which bloats more severely, is on a field whose

Re: [GENERAL] Trying to deal with a bloated index

2009-02-19 Thread Tom Lane
Jack Orenstein jack.orenst...@hds.com writes: - How well does the CONCURRENTLY option work? The documentation on REINDEX says An index build with the CONCURRENTLY option failed ... I'm not sure if I'm supposed to read this as a comment on the reliability of the CONCURRENTLY option. No,

Re: [GENERAL] Service not starting during install

2009-02-19 Thread Frank Featherlight
Hey Richard, I tried both things, both same results as before :( Hope there is a solution for this! Thanks for the replies so far :) Looking forward to hearing from you, FF. On Thu, Feb 19, 2009 at 9:45 PM, Richard Huxton d...@archonet.com wrote: First thing to do is to reduce the

Re: [GENERAL] UPDATE

2009-02-19 Thread Dennis Brakhane
On Thu, Feb 19, 2009 at 7:08 PM, c k shreeseva.learn...@gmail.com wrote: As all rows are rewritten(newly inserted with changed column value), the indices must be updated accordingly and this may take more time? I'd think so, but I'm no expert. If you have many and frequent updates, it might

[GENERAL] where to divide application and database

2009-02-19 Thread Sam Mason
I was just reading over a reply from David Fetter from a couple of days ago; the thread is archived[1] but this question doesn't really relate to it much. The a question about how to arrange tables and David make the following comments: On Tue, Feb 17, 2009 at 09:53:00AM -0800, David Fetter

Re: [GENERAL] Service not starting during install

2009-02-19 Thread Frank Featherlight
Read about the same error somewhere and he said he resolved it with sfc /scannow. Got a new error after I ran sfc /scannow and tried starting the postgresql service: Can not start the PostgreSQL Database 8.3-service on Local computer. Error 1053: The service did not respond correctly to the

  1   2   >