[GENERAL] query log corrupted-looking entries

after an attempt at stress-testing my app i started seeing some corrupted-looking entries in the postgresql query log. for example: ... 2006-10-10 21:33:24 PDT [31175]: [509-1] LOG: statem2006-10-10 21:33:24 PDT [31166]: [4206-1] LOG: duration: 0.197 ms ent: EXECUTE [PREPARE: SELECTP.IS_D

Re: [GENERAL] left outer join on multi tables

> table name > { >id integer primary key >first_name text, >middle_name text, >last_name text > } > > table address > { >id integer primary key >number int, >street text, >city text, >state text > } > > table work > { >id integer primary key >hours text

Re: [GENERAL] restoring a file system backed-up data dir

> The pg_dump backup is a better bet. > > If you are really desperate to recover the latest changes, try > pg_resetxlog then pg_dump, and diff the dump file against your good > pg_dump to see which changes you want to believe and apply. But I'd > still say you want to initdb and restore from the

[GENERAL] left outer join on multi tables

Hi, here are my tables table name { id integer primary key first_name text, middle_name text, last_name text } table address { id integer primary key number int, street text, city text, state text } table work { id integer primary key hours text, shift } tabl

Re: [GENERAL] more anti-postgresql FUD

On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > >> * MySQL is used as a primary development platform. > > > Another good reason. > > Actually that's *the* reason --- it's always going to be hard for > Postgres to look good for an applica

Re: [GENERAL] restoring a file system backed-up data dir

... so what if the database size is above 20 GB, do we have to do pg_dump each at periodics time to get reliable backup? On 10/11/06, Tom Lane <[EMAIL PROTECTED]> wrote: Richard Broersma Jr <[EMAIL PROTECTED]> writes: > I found the correct log file. > 2006-10-10 04:57:45 PDT% LOG: could not op

Re: [GENERAL] restoring a file system backed-up data dir

Richard Broersma Jr <[EMAIL PROTECTED]> writes: > I found the correct log file. > 2006-10-10 04:57:45 PDT% LOG: could not open file > "pg_xlog/00010055" >(log file 0, segment 85): No such file or > directory > 2006-10-10 04:57:45 PDT% LOG: could

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

On Oct 10, 2006, at 9:28 AM, Joshua D. Drake wrote: Scenario 1: While installing PostGRE 8.0, we got an logon failure at the end BTW, it's PostgreSQL or Postgres. PostGRE doesn't exist... You know, every time someone brings this up it reminds me of: Are you Josh or Joshua... It doesn't mat

Re: [GENERAL] restoring a file system backed-up data dir

On Tue, Oct 10, 2006 at 05:31:08PM -0700, Richard Broersma Jr wrote: > My test server's sw/raid array recently died where I kept my PostgreSQL data > directory. I have > both a full dump of the database and a file system back-up of the data > directory. > > I tried to restore my file system bac

Re: [GENERAL] more anti-postgresql FUD

On Tue, Oct 10, 2006 at 06:25:21PM -0300, Jorge Godoy wrote: > "Jacob Coby" <[EMAIL PROTECTED]> writes: > > > We were looking to improve our session performance, so I did a basic > > test of using mysql 4.0 innodb vs postgres 8.1. The test did a simple > > retrieve, update, save; 1 time per page.

Re: [GENERAL] Problem with a date when restoring on postgresql 7.4.9 : date/time field value out of range

On Fri, Oct 06, 2006 at 05:15:55PM +0200, Thomas Poindessous wrote: > Hello, > > I have a problem with my postgresql 7.4.9 server. > > I tried to restore a dump on the backup server (same version). > > I got this error : > > pg_restore: ERROR: date/time field value out of range: "0001-02-29 >

Re: [GENERAL] postgresql.conf shared buffers

Please take a look at http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html first. In a nutshell, set shared_buffers to between 10% and 25% of your memory if it's a server. And increase estimated_cache_size to something close to how much memory you have. On Tue, Oct 03, 2006 at 07:50:4

Re: [GENERAL] Help required

And run, do not walk, to the latest version of 7.4.x. Better yet, upgrade to 8.1.4. On Thu, Oct 05, 2006 at 01:04:05AM +0500, Shoaib Mir wrote: > Run the following > > pg_ctl -D status > > to see if you have the db server running or not? > > As these seems to me you dont have the database serv

Re: [GENERAL] restoring a file system backed-up data dir

> oops, my mistake the exit code is 0 not 127. > > My test server's sw/raid array recently died where I kept my PostgreSQL > > data directory. I > have > > both a full dump of the database and a file system back-up of the data > > directory. > > > > I tried to restore my file system back-up fir

Re: [GENERAL] restoring a file system backed-up data dir

oops, my mistake the exit code is 0 not 127. --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > My test server's sw/raid array recently died where I kept my PostgreSQL data > directory. I have > both a full dump of the database and a file system back-up of the data > directory. > > I tried

[GENERAL] restoring a file system backed-up data dir

My test server's sw/raid array recently died where I kept my PostgreSQL data directory. I have both a full dump of the database and a file system back-up of the data directory. I tried to restore my file system back-up first since it is a "fresher" copy. However, I am unable to start postgres

Re: [GENERAL] more anti-postgresql FUD

"Jacob Coby" <[EMAIL PROTECTED]> writes: > We were looking to improve our session performance, so I did a basic > test of using mysql 4.0 innodb vs postgres 8.1. The test did a simple > retrieve, update, save; 1 time per page. mysql was stock, pg had a > shared_buffers and a couple of other stan

Re: [GENERAL] more anti-postgresql FUD

Tom Lane <[EMAIL PROTECTED]> writes: > Some days I think database independence is a myth. I believe it is as real as Santa Claus and the Easter Bunny. All of us know that those three exist, right? :-) -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)-

Re: [GENERAL] Size of tuples

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 You know, I'd have sworn that I changed "extra 4" to "extra 9" Sorry. On 10/10/06 12:49, Alex Turner wrote: > Awesome - thank you! > > Alex > > On 10/9/06, *Ron Johnson* <[EMAIL PROTECTED] > > wrote: > > On 10/09/06 19:10

Re: [GENERAL] more anti-postgresql FUD

> -Original Message- > Peter Eisentraut <[EMAIL PROTECTED]> writes: > >> * MySQL is used as a primary development platform. > > > Another good reason. > > Actually that's *the* reason --- it's always going to be hard for > Postgres to look good for an application that's been designed/opti

Re: [GENERAL] more anti-postgresql FUD

MySQL 3.23.29 is pre-InnoDB (http://dev.mysql.com/doc/refman/4.1/en/innodb-in-mysql-3-23.html), so this database is not transactional, not ACIDic, and does not support row-level locking or foreign key referential integrity. At this point, MySQL lacked support for subqueries, UNIONs, VIEWs, and nea

Re: [GENERAL] more anti-postgresql FUD

[EMAIL PROTECTED] ("Merlin Moncure") writes: > http://www.zabbix.com/manual/v1.1/install.php > anybody know these guys? this is right off the mysql anti-postgresql > advocacy page. On the upside, they actually indicated what versions they were working with. If they're so out of date that their d

Re: [GENERAL] more anti-postgresql FUD

On 10/10/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: Merlin Moncure wrote: > http://www.zabbix.com/manual/v1.1/install.php > > in section PostgreSQL vs MySQL : > [quoting] > Regarding the choice between PostgreSQL and MySQL, MySQL is > recommended for several reasons: I don't see any fear, u

Re: [GENERAL] more anti-postgresql FUD

Peter Eisentraut <[EMAIL PROTECTED]> writes: >> * MySQL is used as a primary development platform. > Another good reason. Actually that's *the* reason --- it's always going to be hard for Postgres to look good for an application that's been designed/optimized for MySQL. The application has alre

Re: [GENERAL] more anti-postgresql FUD

Merlin Moncure wrote: > http://www.zabbix.com/manual/v1.1/install.php > > in section PostgreSQL vs MySQL : > [quoting] > Regarding the choice between PostgreSQL and MySQL, MySQL is > recommended for several reasons: I don't see any fear, uncertainty, or doubt there. > * MySQL is faster It pr

Re: [GENERAL] more anti-postgresql FUD

On 10/10/06, Joshua D. Drake <[EMAIL PROTECTED]> wrote: Merlin Moncure wrote: > http://www.zabbix.com/manual/v1.1/install.php > > in section PostgreSQL vs MySQL : Well they may be right that far back. But 7.1 is years and years old. Joshua D. Drake no excuse. that would be like postgresql

Re: [GENERAL] memory issues when running with mod_perl

On 10/1/06, Fred Tyler <[EMAIL PROTECTED]> wrote: > However, my machine looses between 500 M and 800 M in two weeks, and > within that time, I restart pg only very few times, say 3-4 times. > Does pg allocate other shmem blocks? If there is really a kernel memory > problem in shmem, how can I loo

Re: [GENERAL] more anti-postgresql FUD

Merlin Moncure wrote: > http://www.zabbix.com/manual/v1.1/install.php > > in section PostgreSQL vs MySQL : > [quoting] > Regarding the choice between PostgreSQL and MySQL, MySQL is > recommended for several reasons: > >* MySQL is faster > > recent benchmarks using ZABBIX clearly show th

[GENERAL] more anti-postgresql FUD

http://www.zabbix.com/manual/v1.1/install.php in section PostgreSQL vs MySQL : [quoting] Regarding the choice between PostgreSQL and MySQL, MySQL is recommended for several reasons: * MySQL is faster recent benchmarks using ZABBIX clearly show that PostgreSQL (7.1.x) is at least 10 time

Re: [GENERAL] Size of tuples

Awesome - thank you!AlexOn 10/9/06, Ron Johnson <[EMAIL PROTECTED]> wrote: -BEGIN PGP SIGNED MESSAGE-Hash: SHA1On 10/09/06 19:10, Alex Turner wrote:> I have a table whose definition is basically>> create table foo (> a int,> b int,> c int, > d date> );>> and when fully populated, select rel

[GENERAL] Find out the number of rows returned by refcursor?

What is the best way to find out the total number of rows returned by an refcursor? This would allow the client user to know the total amount of rows as they are using FETCH FORWARD/BACKWARD. For example let's say that an refcursor has 300 rows. The user fetches 20 at a time. I would like the u

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

> -Original Message- > From: Joshua D. Drake [mailto:[EMAIL PROTECTED] > Sent: 10 October 2006 17:11 > To: Dave Page > Cc: Jim C. Nasby; pgsql-general@postgresql.org > Subject: Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues > > > That reminds me Bob - did you see my email ab

Re: [GENERAL] time type strange behaviour

On Tue, 2006-10-10 at 11:58 -0400, Tom Lane wrote: > garry saddington <[EMAIL PROTECTED]> writes: > > I have the following table def. > > CREATE TABLE periods > > ( > > periodid serial NOT NULL, > > periodnumber integer NOT NULL, > > periodstart time with time zone, > > periodend time with

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

> It's also possible that I just think about this stuff too much. > Give this guy a cookie. :) Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solution

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

Dave Page wrote: > > >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of >> Joshua D. Drake >> Sent: 10 October 2006 15:28 >> To: Jim C. Nasby >> Cc: Ravindran G - TLS, Chennai.; >> pgsql-general@postgresql.org; Hari Krishna D - TLS , Chennai; >

Re: [GENERAL] time type strange behaviour

garry saddington <[EMAIL PROTECTED]> writes: > I have the following table def. > CREATE TABLE periods > ( > periodid serial NOT NULL, > periodnumber integer NOT NULL, > periodstart time with time zone, > periodend time with time zone, > PRIMARY KEY (periodid) > ) > This definition does n

[GENERAL] time type strange behaviour

I have the following table def. CREATE TABLE periods ( periodid serial NOT NULL, periodnumber integer NOT NULL, periodstart time with time zone, periodend time with time zone, PRIMARY KEY (periodid) ) This definition does not insert time zone. If I use without time zone then the time z

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

In response to "Joshua D. Drake" <[EMAIL PROTECTED]>: > Bill Moran wrote: > > In response to "Joshua D. Drake" <[EMAIL PROTECTED]>: > Scenario 1: While installing PostGRE 8.0, we got an logon failure at the > end > >>> BTW, it's PostgreSQL or Postgres. PostGRE doesn't exist... > >> You

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Joshua D. Drake > Sent: 10 October 2006 15:28 > To: Jim C. Nasby > Cc: Ravindran G - TLS, Chennai.; > pgsql-general@postgresql.org; Hari Krishna D - TLS , Chennai; > Sasikala V - TLS , Chennai >

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

Bill Moran wrote: > In response to "Joshua D. Drake" <[EMAIL PROTECTED]>: Scenario 1: While installing PostGRE 8.0, we got an logon failure at the end >>> BTW, it's PostgreSQL or Postgres. PostGRE doesn't exist... >> You know, every time someone brings this up it reminds me of: >> >> Are

Re: [GENERAL] Automatic row numbering / sequence in view ?

Merlin Moncure a écrit : On 10/10/06, Arnaud Lesauvage <[EMAIL PROTECTED]> wrote: Hi List ! I need to add a column to a view, which would contain an automatically generated sequence. An automatic row numbering would do the trick (I only need unique numbers, that's all), but I don't even know ho

Re: [GENERAL] Automatic row numbering / sequence in view ?

On 10/10/06, Arnaud Lesauvage <[EMAIL PROTECTED]> wrote: Hi List ! I need to add a column to a view, which would contain an automatically generated sequence. An automatic row numbering would do the trick (I only need unique numbers, that's all), but I don't even know how to achieve this. Does an

[GENERAL] Automatic row numbering / sequence in view ?

Hi List ! I need to add a column to a view, which would contain an automatically generated sequence. An automatic row numbering would do the trick (I only need unique numbers, that's all), but I don't even know how to achieve this. Does anybody have a solution for this problem ? Thanks a lot ! -

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

On Oct 10, 2006, at 10:34 , Bill Moran wrote: I had an almost-gf once... Me too! -M ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

In response to "Joshua D. Drake" <[EMAIL PROTECTED]>: > > >> Scenario 1: While installing PostGRE 8.0, we got an logon failure at the > >> end > > > > BTW, it's PostgreSQL or Postgres. PostGRE doesn't exist... > > You know, every time someone brings this up it reminds me of: > > Are you Josh o

Re: [GENERAL] adjusting primary key

Thenx a lot. That does it!! -R On Tue, 2006-10-10 at 16:23 +0200, [EMAIL PROTECTED] wrote: > You have to create t2 as following: > CREATE TABLE t2 (id int, grp int references t1(id) ON UPDATE CASCADE ON > DELETE CASCADE, info text); > > Through the cascade commands Postgresql will check the t2

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

>> Scenario 1: While installing PostGRE 8.0, we got an logon failure at the end > > BTW, it's PostgreSQL or Postgres. PostGRE doesn't exist... You know, every time someone brings this up it reminds me of: Are you Josh or Joshua... It doesn't matter people. Joshua D. Drake -- === The Po

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

Thanks for your comments and moving it to general group. We would like to know which is the most stable version in Postgresql ?. Because Postgresql may undergo changes and will have the version incremented. In this case, do we need to do the upgrade frequently ?. Of course, its good have the lat

Re: [GENERAL] adjusting primary key

You have to create t2 as following: CREATE TABLE t2 (id int, grp int references t1(id) ON UPDATE CASCADE ON DELETE CASCADE, info text); Through the cascade commands Postgresql will check the t2 table on rows which have to be deleted or updated according to your changes in t1. For changing the exi

Re: [GENERAL] adjusting primary key

On Oct 10, 2006, at 15:59 , Rafal Pietrak wrote: Hi All, I have two tables: CREATE TABLE t1 (id int not null unique, info text); CREATE TABLE t2 (id int, grp int references t1(id), info text); Now, at certain point (both tables populated with tousends of records, and continuesly referenced by

Re: [GENERAL] adjusting primary key

If you could alter the foreign key constraint such that the update on t1's primary key cascaded to t2, that would help. However, I'm not sure that you alter the constraint in postgres once it's created. Hopefully someone more knowledgeable will be able to respond. John Rafal Pietrak wrote:

Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues

Moving to -general. On Tue, Oct 10, 2006 at 04:17:06PM +0530, Ravindran G - TLS, Chennai. wrote: > All, > > We are facing few issues while we install Postgres 8.0 in Windows 2000 > Japanese OS. Installer kit name : postgresql-8.0-ja Is there a reason you're not using 8.1.4? 8.0 was the first wi

[GENERAL] adjusting primary key

Hi All, I have two tables: CREATE TABLE t1 (id int not null unique, info text); CREATE TABLE t2 (id int, grp int references t1(id), info text); Now, at certain point (both tables populated with tousends of records, and continuesly referenced by users), I need to adjust the value of an ID field of

[GENERAL] Problem compiling PostGIS 1.1.4

PostgreSQL 8.1 on ubuntu-linux 6.06 (kernel 2.6.15-27-686): Compiled proj-4.4.9 and geos-2.2.3 without problems. Output from postgis-./configure-Command: SUMMARY --- HOST_OS: linux-gnu PGSQL: /usr/bin/pg_config GEOS: /usr/local/bin/ge

Re: [GENERAL] Newbie question about importing text files...

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/09/06 22:43, Jonathan Greenberg wrote: > So I've been looking at the documentation for COPY, and I'm curious about a > number of features which do not appear to be included, and whether these > functions are found someplace else: > > 1) How do I

Re: [GENERAL] Newbie question about importing text files...

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/10/06 01:44, Merlin Moncure wrote: > On 10/10/06, Jonathan Greenberg <[EMAIL PROTECTED]> wrote: >> So I've been looking at the documentation for COPY, and I'm curious >> about a >> number of features which do not appear to be included, and whethe

Re: [GENERAL] plpgsql handling a set of values

[EMAIL PROTECTED] wrote: I am writing a plpgsql (PostgreSQL 8.x) trigger function that should do something on a number of records. The records are in a very simple table with two columns - 'parent_id' and 'child_id'. A 'child' can be as well a 'parent' to one or more children - in this case its

Re: [GENERAL] Intentionally produce Errors

Andreas Seltenreich wrote: Markus Schiltknecht writes: [EMAIL PROTECTED] wrote: --8<---cut here---start->8--- create function error(text) returns void as $$ begin raise exception '%', $1; end $$ language plpgsql; --8<