Re: [GENERAL] two or more pg installations running as the same user

On Tue, Nov 25, 2008 at 12:37 PM, Grzegorz Jaśkiewicz <[EMAIL PROTECTED]>wrote: > hey folks, > > I am thinking about testing enviroment here, and gotta setup temporary > instalation of postgres for that. More than one, because software operates > on few different connections, to different database

Re: [GENERAL] Postgres 8.3 only uses seq scan

On 11/26/2008 03:20 PM, Scott Marlowe wrote: > On Tue, Nov 25, 2008 at 10:22 PM, Clemens Schwaighofer > <[EMAIL PROTECTED]> wrote: >> On 11/26/2008 02:15 PM, Scott Marlowe wrote: >>> Are there more rows in the 8.2 table you're testing on? Or is the >>> whole table small enough to fit on a few page

Re: [GENERAL] Postgres 8.3 only uses seq scan

On Tue, Nov 25, 2008 at 10:22 PM, Clemens Schwaighofer <[EMAIL PROTECTED]> wrote: > On 11/26/2008 02:15 PM, Scott Marlowe wrote: >> >> Are there more rows in the 8.2 table you're testing on? Or is the >> whole table small enough to fit on a few pages? > > I highly doubt that. I have right now in o

Re: [GENERAL] Postgres 8.3 only uses seq scan

On 11/26/2008 02:15 PM, Scott Marlowe wrote: > On Tue, Nov 25, 2008 at 10:07 PM, Clemens Schwaighofer > <[EMAIL PROTECTED]> wrote: >> On 11/26/2008 02:04 PM, Scott Marlowe wrote: >>> On Tue, Nov 25, 2008 at 8:39 PM, Clemens Schwaighofer >>> <[EMAIL PROTECTED]> wrote: but on the 8.3 version i g

Re: [GENERAL] Postgres 8.3 only uses seq scan

On Tue, Nov 25, 2008 at 10:07 PM, Clemens Schwaighofer <[EMAIL PROTECTED]> wrote: > On 11/26/2008 02:04 PM, Scott Marlowe wrote: >> On Tue, Nov 25, 2008 at 8:39 PM, Clemens Schwaighofer >> <[EMAIL PROTECTED]> wrote: >>> but on the 8.3 version i get this back >>> >>> # explain select * from foo f, b

Re: [GENERAL] Postgres 8.3 only uses seq scan

On 11/26/2008 02:04 PM, Scott Marlowe wrote: > On Tue, Nov 25, 2008 at 8:39 PM, Clemens Schwaighofer > <[EMAIL PROTECTED]> wrote: >> but on the 8.3 version i get this back >> >> # explain select * from foo f, bar b where f.foo_id = b.foo_id; >>QUERY PLAN >> -

Re: [GENERAL] Postgres 8.3 only uses seq scan

On Tue, Nov 25, 2008 at 8:39 PM, Clemens Schwaighofer <[EMAIL PROTECTED]> wrote: > but on the 8.3 version i get this back > > # explain select * from foo f, bar b where f.foo_id = b.foo_id; >QUERY PLAN > --

Re: [GENERAL] slow, long-running 'commit prepared'

On Tue, Nov 25, 2008 at 08:05:36PM -0800, John Smith wrote: > Thanks for the quick response, Andrew. How could the 'commit prepared' > be I/O bound? Isn't all the I/O accomplished during 'prepare > transaction', leaving only a tiny bit of data to write at commit? No. When you do PREPARE TRANSACTI

Re: [GENERAL] slow, long-running 'commit prepared'

Thanks for the quick response, Andrew. How could the 'commit prepared' be I/O bound? Isn't all the I/O accomplished during 'prepare transaction', leaving only a tiny bit of data to write at commit? > > You could be I/O bound. Have a look at iostat and sar. > > A > -- > Andrew Sullivan > ajs(at)cr

[GENERAL] Postgres 8.3 only uses seq scan

Hi, I have system here with Debian/Testing and the latest 8.2 and 8.3 database installed. on a blank database I create two very simple tables Table "public.foo" Column | Type| Modifiers +---+-

Re: [GENERAL] slow, long-running 'commit prepared'

On Tue, Nov 25, 2008 at 07:17:20PM -0800, John Smith wrote: > I have a pg instance with 700GB of data, almost all of which is in one > table. When I PREPARE and then COMMIT PREPARED a transaction that > reads & writes to a large fraction of that data (about 10%, > effectively randomly chosen rows a

[GENERAL] slow, long-running 'commit prepared'

I have a pg instance with 700GB of data, almost all of which is in one table. When I PREPARE and then COMMIT PREPARED a transaction that reads & writes to a large fraction of that data (about 10%, effectively randomly chosen rows and so every file in the table is modified), the COMMIT PREPARED some

Re: [GENERAL] Effect of stopped status collector process

On Tue, Nov 25, 2008 at 9:03 AM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Tom Lane wrote: > >> If you are willing to run a hand-hacked version then I'd suggest doing >> your experimentation with CVS HEAD. There are changes in place already >> to reduce the stats file traffic. > > Why do we _hav

Re: [GENERAL] Question about anyelement datatype

Kaarel <[EMAIL PROTECTED]> writes: > Then I tried to use this: > v_result v_data%TYPE > where v_data is an argument of the same function with anyelement > datatype, but no luck. Define "no luck". What did you do *exactly*, and what happened? regards, tom lane -- Se

[GENERAL] Question about anyelement datatype

Hi I need to use the following statement in a stored procedure: EXECUTE 'SELECT * FROM ' || v_table_name INTO v_result; where v_table_name and v_result are the arguments of the function. v_table_name is of character varying type and v_result is anyelement. But as I discovered, it's not all

Re: [GENERAL] two or more pg installations running as the same user

On Tue, Nov 25, 2008 at 10:29 PM, Chris <[EMAIL PROTECTED]> wrote: > > If they are different versions of postgres (eg 8.2/8.3), make sure you > initdb with the right one :) > ok, I tried it and it works fine so far. basically, I need to setup test harness, that would involve using few physically s

Re: [GENERAL] two or more pg installations running as the same user

Grzegorz Jas'kiewicz wrote: hey folks, I am thinking about testing enviroment here, and gotta setup temporary instalation of postgres for that. More than one, because software operates on few different connections, to different databases. I know I can do it under same user, just by changing PG

Re: [GENERAL] Cannot restore table using pg_restore

Bill Todd <[EMAIL PROTECTED]> writes: > I am trying to restore a table named contact in the contact schema. I > have tried two methods. The first is the command > pg_restore --dbname=app --format=custom --schema=contact > --table=contact.contact --username=postgres --single-transaction > contac

[GENERAL] Cannot restore table using pg_restore

I am new to PostgreSQL and am running 8.3.3 on Windows XP Pro SP3. I have dumped a database using the following command. pg_dump --format=custom --username=postgres --file=c:\pgdb\dumps\app.backup app I am trying to restore a table named contact in the contact schema. I have tried two metho

Re: [GENERAL] pg_get_serial_sequence Strangeness/Unreliable?

Did you first insert into public.news_status insert into public.news_status (status_id) values (DEFAULT) and then get the sequence? Also since you have a domain 'public' I personally always do 'set searc_path to public' before doing any SQLs -- this way I know that I do not need to prefix my

Re: [GENERAL] pg_get_serial_sequence Strangeness/Unreliable?

Jeff MacDonald <[EMAIL PROTECTED]> writes: > The issue, is that when I run pg_get_serial_sequence on a particular > table/column it returns NULL. Does the column actually own that sequence? Or is its default just something that was inserted manually? regards, tom lane

[GENERAL] pg_get_serial_sequence Strangeness/Unreliable?

Hello everyone, I'm using "PostgreSQL 8.2.5 on amd64-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518" The issue, is that when I run pg_get_serial_sequence on a particular table/column it returns NULL. Here are my tests: simplyas_associations=> \d news_status

Re: [GENERAL] Effect of stopped status collector process

Alvaro Herrera <[EMAIL PROTECTED]> writes: > Are MAP_SHARED | MAP_ANONYMOUS mmapped regions portable? No. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsq

Re: [GENERAL] Effect of stopped status collector process

Tom Lane wrote: > If you are willing to run a hand-hacked version then I'd suggest doing > your experimentation with CVS HEAD. There are changes in place already > to reduce the stats file traffic. Why do we _have_ to write the file to disk? I wonder if it would work to store the file in a mmap

Re: [GENERAL] Effect of stopped status collector process

Siddharth Shah <[EMAIL PROTECTED]> writes: > I have found pgstat.stat file taking too much write hits. > As my need to deploy on flash based storage. I don't want higher I/O's > To stop status collector process I have change postmaster.c by removing > calls of pgstat.c > Now every thing is working

[GENERAL] Effect of stopped status collector process

Hello, I have started to explore PG, I have found pgstat.stat file taking too much write hits. As my need to deploy on flash based storage. I don't want higher I/O's To stop status collector process I have change postmaster.c by removing calls of pgstat.c Now every thing is working fine, Sta

Re: [GENERAL] Place of subselect

"Guillaume Bog" <[EMAIL PROTECTED]> writes: > I have performance issues if I do the following pseudo-query: > SELECT a, b, (SELECT count(*) FROM t2 WHERE something) AS c > FROM t1 ORDER BY a LIMIT 10; > After some tests, it seems to me that the subquery on t2 is computed for all > rows of t1. Ye

[GENERAL] PostgreSQL 8.3.5 client_encoding WIN1251 trouble

Hello! Still having troubles with client_encoding I have database in KOI8-R encoding. When I try to SET NAMES 'WIN' and do a select, I have such error: ERROR: character 0x8b9a of encoding "MULE_INTERNAL" has no equivalent in "WIN1251" The database is about 3Gb size and needs to be in koi8, but

Re: [GENERAL] two or more pg installations running as the same user

Filip Rembiałkowski wrote: > > > 2008/11/25 Grzegorz Jaśkiewicz <[EMAIL PROTECTED] > > > > hey folks, > > I am thinking about testing enviroment here, and gotta setup > temporary instalation of postgres for that. More than one, because > software operat

Re: [GENERAL] two or more pg installations running as the same user

2008/11/25 Grzegorz Jaśkiewicz <[EMAIL PROTECTED]> > hey folks, > > I am thinking about testing enviroment here, and gotta setup temporary > instalation of postgres for that. More than one, because software operates > on few different connections, to different databases. > I know I can do it under

Re: [GENERAL] Syntac error

Hi Andreas, thank you! I did install plpsql and now everything is fine. Regards Timo A. Kretschmer schrieb: am Tue, dem 25.11.2008, um 12:41:32 +0100 mailte Timo Erbach folgendes: Hello, I've defined the following function and get an syntac error whle compiling. Whats the fault? CREATE

Re: [GENERAL] Syntac error

am Tue, dem 25.11.2008, um 12:41:32 +0100 mailte Timo Erbach folgendes: > Hello, > > I've defined the following function and get an syntac error whle compiling. > Whats the fault? > > CREATE FUNCTION "myscheme"."authenticate" () RETURNS > "pg_catalog"."refcursor" AS > $body$ > DECLARE >curA

[GENERAL] Syntac error

Hello, I've defined the following function and get an syntac error whle compiling. Whats the fault? CREATE FUNCTION "myscheme"."authenticate" () RETURNS "pg_catalog"."refcursor" AS $body$ DECLARE curAccessRights refcursor; BEGIN OPEN curAccessRights FOR SELECT * from myscheme."tb

[GENERAL] two or more pg installations running as the same user

hey folks, I am thinking about testing enviroment here, and gotta setup temporary instalation of postgres for that. More than one, because software operates on few different connections, to different databases. I know I can do it under same user, just by changing PGDATA/PGPORT before initdb gets t

Re: [GENERAL] [ADMIN] PgAgent Job Scehduler is NOT running

Service is running. After getting frustrated I dropped the pgagent schema and reworked. But the result is same. ???. Even entries in the concerned pga_tables is also very clear. Perhaps the trigger is not working . From: Vishal Arora <[EMAIL PROTECTED]> To: [E

Re: [GENERAL] Serial/sequence problem

On Tue, Nov 25, 2008 at 05:39:49PM +0930, Michael Hall wrote: > On Tue, Nov 25, 2008 at 08:26:55AM +0100, A. Kretschmer wrote: > > Set the sequence to the new value, 3636+9, via > > setval('your_sequence', 3636+9) > > I'll re-import the data with DEFAULT in the id (SERIAL) column, > hopefully new

Re: [GENERAL] Place of subselect

am Tue, dem 25.11.2008, um 16:44:34 +0800 mailte Guillaume Bog folgendes: > It seems that you are right. By further testing I found that a WHERE condition > in the subquery was making the query hundred times slower. As I'm not very > familiar with explain analyze, I paste them below. Why do I have

Re: [GENERAL] Place of subselect

On Tue, Nov 25, 2008 at 15:56, A. Kretschmer < [EMAIL PROTECTED]> wrote: > am Tue, dem 25.11.2008, um 15:34:57 +0800 mailte Guillaume Bog folgendes: > > Hi dear Postgres users. > > > > I have performance issues if I do the following pseudo-query: > > > > SELECT a, b, (SELECT count(*) FROM t2 WHER

Re: [GENERAL] Serial/sequence problem

> The 3636 INSERT-Statements contains a fix value for the ID-Column, > right? Thats wrong, omit the id-column and value or use simply 'default' for > it. That's right. I thought that might be the case. > > - how can I rectify the situation? > Set the sequence to the new value, 3636+9, via > s