Re: [GENERAL] base backup and tar problems with disappearing files.

On 15 Mar 2011, at 3:06, Rajesh Kumar Mallah wrote: > Dear Friends, > > While taking online basebackup we ignore tar exit codes of 1 . > However under certain circumstances tar exits we code '2' which > stands for 'Fatal Errors' . Eg in case of "Cannot stat: No such file or > directory" > enc

[GENERAL] pgstat wait timeout

I know you can't do much with this information, but maybe you can help me acquire the information that is needed. This started in 8.4 and trying to figure it out Fedora 12, Postgres 8.4.4 with slony. Although I don't see any panics or errors other then these Warnings, but I did see a dead lock t

Re: [GENERAL] Huge spikes in number of connections doing "PARSE"

On Mon, Mar 14, 2011 at 10:21:27PM +0100, Martijn van Oosterhout wrote: > On Mon, Mar 14, 2011 at 07:49:46PM +0100, hubert depesz lubaczewski wrote: > > These 60 were summarized, and output is available here: > > http://www.depesz.com/various/locks.summary.txt > > > > as you can seem, in 48 cases

Re: [GENERAL] Huge spikes in number of connections doing "PARSE"

On Mon, Mar 14, 2011 at 07:49:46PM +0100, hubert depesz lubaczewski wrote: > I wrote a script, that every 15 seconds, checks system for Pg backends in > "PARSE" state. If there are more than 100 of them, script randombly chooses > 10 of them, and runs "gdb -batch -quiet -ex=bt /usr/bin/postgres PID

Re: [GENERAL] Partitioned Database and Choosing Subtables

On Mar 14, 2011, at 8:30 PM, Bill Thoen wrote: > I've got a ver 8.4.5 partitioned data base with records organized by US > state, so the partitions are set up by state. When I query this database and > include the key field that tells postgres what partition you , everything > works as I expe

[GENERAL] Partitioned Database and Choosing Subtables

I've got a ver 8.4.5 partitioned data base with records organized by US state, so the partitions are set up by state. When I query this database and include the key field that tells postgres what partition you , everything works as I expect. It searches only the specified partition, and it's f

[GENERAL] base backup and tar problems with disappearing files.

Dear Friends, While taking online basebackup we ignore tar exit codes of 1 . However under certain circumstances tar exits we code '2' which stands for 'Fatal Errors' . Eg in case of "Cannot stat: No such file or directory" encountered while taking backup of the pgdatadir . My question is can we

Re: [GENERAL] Huge spikes in number of connections doing "PARSE"

On Mon, Mar 14, 2011 at 10:21:27PM +0100, Martijn van Oosterhout wrote: > The common factor seems to be lots of index locks. Do you have very > many indexes? $ select count(*) from pg_class where relkind = 'i'; count --- 450 (1 row) $ select count(*) from pg_class where relkind = 'r';

Re: [GENERAL] Autocommit off - commits/rollbacks

Set autocommit to "true/on". That will give you the desired behavior of allowing all those things that succeed to remain committed. David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Vogt, Michael Sent: Monday, Mar

Re: [GENERAL] Huge spikes in number of connections doing "PARSE"

On Mon, Mar 14, 2011 at 07:49:46PM +0100, hubert depesz lubaczewski wrote: > These 60 were summarized, and output is available here: > http://www.depesz.com/various/locks.summary.txt > > as you can seem, in 48 cases backend process was in semop(), which relates > directly to my previous findings w

Re: [GENERAL] Create a view with variable amount of columns depending on the rows of a table

2011/3/14 Stefan Gündhör : > Hi, > If I have following tables for example: > # Main Table: > id(id/pk) | geometry > --- > 1           | ... > # Additional Attribute Table: > name(id/pk) | value > --- > date_added | 20.12.1988 > name          | Vienna

Re: [GENERAL] Move From Oracle DB to PostgreSQL DB

Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= writes: > On Mon, 2011-03-14 at 14:43 -0400, gene.po...@macys.com wrote: >> [ wants to port Red Hat Satellite to Postgres ] > Please see > http://spacewalk.redhat.com/ Yeah. I'm on the fringes of that port effort, and it is *not* trivial; Satellite is umpteen

Re: [GENERAL] Move From Oracle DB to PostgreSQL DB

On 03/14/11 11:43 AM, gene.po...@macys.com wrote: Is there a recent tutorial, white paper, how to on move/migrate from Oracle to PostgreSQL? I can get the Oracle schema DDL by pointing our Data Modeling software at the Red Hat Satellite server and extracting the DDL. It will take that Oracle

Re: [GENERAL] Move From Oracle DB to PostgreSQL DB

Gene, * gene.po...@macys.com (gene.po...@macys.com) wrote: > Is there a recent tutorial, white paper, how to on move/migrate from > Oracle to PostgreSQL? It's typically "not hard", but it depends on what you're doing w/ Oracle. Specifically, things like stored procedures (PL/SQL) may require

Re: [GENERAL] Move From Oracle DB to PostgreSQL DB

On Mon, Mar 14, 2011 at 02:43:17PM -0400, gene.po...@macys.com wrote: > Is there a recent tutorial, white paper, how to on move/migrate from > Oracle to PostgreSQL? I can get the Oracle schema DDL by pointing our > Data Modeling software at the Red Hat Satellite server and extracting the > DDL

Re: [GENERAL] Move From Oracle DB to PostgreSQL DB

On Mon, 2011-03-14 at 14:43 -0400, gene.po...@macys.com wrote: > We're running Red Hat Satellite Server and it's used to provision our > servers (both physical and virtual). It works great and we have no > issues with it *except* It will only provision Red Hat. > > Why this question: > > We

[GENERAL] Create a view with variable amount of columns depending on the rows of a table

Hi, If I have following tables for example: # Main Table: id(id/pk) | geometry --- 1 | ... # Additional Attribute Table: name(id/pk) | value --- date_added | 20.12.1988 name | Vienna # m:n table: mainTableID | attrTableID ---

[GENERAL] Move From Oracle DB to PostgreSQL DB

Just a little background: We're running Red Hat Satellite Server and it's used to provision our servers (both physical and virtual). It works great and we have no issues with it *except* It will only provision Red Hat. Why this question: We've been directed by our management to examine the

Re: [GENERAL] DBMS upgrade and backups

On Mon, Mar 14, 2011 at 1:38 PM, Vick Khera wrote: > On Mon, Mar 14, 2011 at 10:06 AM, Alexander Pyhalov wrote: > > Am I right that on PostgreSQL upgrade to new major version (e.g. from 9.0 > to > > 9.1) I'll loose my backups (base backups and wal files will be useless)? > So > > to go to past a

Re: [GENERAL] Huge spikes in number of connections doing "PARSE"

On Fri, Mar 11, 2011 at 11:13:43AM -0500, Noah Misch wrote: > gdb -ex=bt /path/to/bin/postgres $pid http://www.depesz.com/various/locks.summary.txt as you can seem, in 48 cases backend process was in semop(), which relates directly to my previous findings with ps/wchan. summary format is:

Re: [GENERAL] DBMS upgrade and backups

On Mon, Mar 14, 2011 at 10:06 AM, Alexander Pyhalov wrote: > Am I right that on PostgreSQL upgrade to new major version (e.g. from 9.0 to > 9.1) I'll loose my backups (base backups and wal files will be useless)? So > to go to past after DB upgrade  I had to install old version(9.0), recover > dat

Re: [GENERAL] list all members in a tablespace

Thanks Raghavendra. I tried the query and it seemed to be returning the indices and sequences in each tablespace. I'll keep trying to get the list of tablespaces. Thanks for your help! Mike From: Raghavendra [mailto:raghavendra@enterprisedb.com] Sent: Wednesday, March 09, 2011 5:15 PM To:

Re: [GENERAL] Select for update with offset interferes with concurrent transactions

On Tue, Feb 1, 2011 at 11:18 AM, Tom Lane wrote: > "Yngve Nysaeter Pettersen" writes: >> To avoid having the processes trample each other's queries (the first >> attempt was to select the first matching entries of the table, which >> caused one to block all other transactions), one of the steps I

Re: [GENERAL] Values larger than 1/3 of a buffer page cannot be indexed.

thanks, this worked. On Sun, Mar 13, 2011 at 7:05 PM, Dmitriy Igrishin wrote: > Hey Viktor, > > 2011/3/13 Viktor Nagy > >> hi, >> >> when trying to insert a long-long value, I get the following error: >> >> index row size 3120 exceeds maximum 2712 for index "ir_translation_ltns" >> HINT: Valu

Re: [GENERAL] Autocommit off - commits/rollbacks

On Mon, Mar 14, 2011 at 03:55:37PM +0100, Vogt, Michael wrote: > Why does postgres rollback the whole transaction after an error? I > compared the behavior with oracle/hsql - those dbms commit whats > possible. A transaction is supposed to commit or rollback. If you want to hold on to something t

Re: [GENERAL] Primary key

On Mon, 2011-03-14 at 16:03 +, Raymond O'Donnell wrote: > On 14/03/2011 15:56, Raymond O'Donnell wrote: > > On 14/03/2011 15:35, Allan Kamau wrote: > > > >> CREATE SEQUENCE hy3_pack_seq MINVALUE 1000; > >> CREATE TABLE hy3_pack > >> ( > >> hy3_id INTEGER NOT NULL DEFAULT nextval('hy3_pack_seq')

Re: [GENERAL] Autocommit off - commits/rollbacks

Hello. On 03/14/2011 12:24, Vogt, Michael wrote: I have a question, using the autocommit off option in postgres. As starting position I use a table called xxx.configuration using a unique id constraint. Why does postgres rollback the whole transaction after an error? I compared the behavior wit

Re: [GENERAL] Select for update with offset interferes with concurrent transactions

On 3/14/2011 10:13 AM, Yngve N. Pettersen (Developer Opera Software ASA) wrote: Hello all, Just a quick update of how it went. I ended up using code similar to a combination of Andy Colson's and David Johnston's suggestions below, and performance is back at what is was before. Thanks for the su

Re: [GENERAL] Postgres 8.3 vs. 8.4 - Query plans and performance

Hello, that's the 8.4 query plan: http://explain.depesz.com/s/dO7 The locale of the two databases is the same: SHOW LC_COLLATE command gives the locale: "de_DE.UTF-8". Regards, Jo On 14.03.2011 16:04, t...@fuzzy.cz wrote: Hello 2011/3/14 Jo: I set the work_mem to 100MB and the shar

Re: [GENERAL] Primary key

On 14/03/2011 15:56, Raymond O'Donnell wrote: On 14/03/2011 15:35, Allan Kamau wrote: CREATE SEQUENCE hy3_pack_seq MINVALUE 1000; CREATE TABLE hy3_pack ( hy3_id INTEGER NOT NULL DEFAULT nextval('hy3_pack_seq') -- or hy3_id BIGINT NOT NULL DEFAULT nextval('hy3_pack_seq') That's what SERIAL doe

Re: [GENERAL] Primary key

On 14/03/2011 15:35, Allan Kamau wrote: CREATE SEQUENCE hy3_pack_seq MINVALUE 1000; CREATE TABLE hy3_pack ( hy3_id INTEGER NOT NULL DEFAULT nextval('hy3_pack_seq') -- or hy3_id BIGINT NOT NULL DEFAULT nextval('hy3_pack_seq') That's what SERIAL does for you, in one go - it's just syntactic suga

Re: [GENERAL] Primary key

On Mon, Mar 14, 2011 at 10:58 AM, Peter Evens wrote: > hello, > > i have a question about the PRIMARY KEY, > > how can we let it start from for example 1000 instead of 1? > > This is our program: > > CREATE TABLE hy3_pack > > ( > > hy3_id serial NOT NULL, > > hy3_serie_nummer text NOT NULL, > > hy

Re: [GENERAL] primary key

On Monday, March 14, 2011 12:57:07 am Peter Evens wrote: > hello, > > i have a question about the PRIMARY KEY, > how can we let it start from for example 1000 instead of 1? > > This is our program: > CREATE TABLE hy3_pack > ( > hy3_id serial NOT NULL, > hy3_serie_nummer text NOT NULL, > hy3_ba

Re: [GENERAL] Select for update with offset interferes with concurrent transactions

Hello all, Just a quick update of how it went. I ended up using code similar to a combination of Andy Colson's and David Johnston's suggestions below, and performance is back at what is was before. Thanks for the suggestions BTW: AFAICT I never got a response from Tom Lane about whether it was

Re: [GENERAL] primary key

On 14/03/2011 07:57, Peter Evens wrote: hello, i have a question about the PRIMARY KEY, how can we let it start from for example 1000 instead of 1? This is our program: CREATE TABLE hy3_pack ( hy3_id serial NOT NULL, hy3_serie_nummer text NOT NULL, hy3_barcode text NOT NULL, hy3_type_v

[GENERAL] Autocommit off - commits/rollbacks

Hey all I have a question, using the autocommit off option in postgres. As starting position I use a table called xxx.configuration using a unique id constraint. Why does postgres rollback the whole transaction after an error? I compared the behavior with oracle/hsql - those dbms commit whats po

[GENERAL] Primary key

hello, i have a question about the PRIMARY KEY, how can we let it start from for example 1000 instead of 1? This is our program: CREATE TABLE hy3_pack ( hy3_id serial NOT NULL, hy3_serie_nummer text NOT NULL, hy3_barcode text NOT NULL, hy3_type_vulling text NOT NULL, hy3_tarra_gewicht te

[GENERAL] primary key

hello, i have a question about the PRIMARY KEY, how can we let it start from for example 1000 instead of 1? This is our program: CREATE TABLE hy3_pack ( hy3_id serial NOT NULL, hy3_serie_nummer text NOT NULL, hy3_barcode text NOT NULL, hy3_type_vulling text NOT NULL, hy3_tarra_gewicht text,

[GENERAL] Autocommit off - commits/rollbacks

Hey all I have a question, using the autocommit off option in postgres. As starting position I use a table called xxx.configuration using a unique id constraint. Why does postgres rollback the whole transaction after an error? I compared the behavior with oracle/hsql - those dbms commit whats po

Re: [GENERAL] Postgres 8.3 vs. 8.4 - Query plans and performance

>> we have performance problems running several queries pon postgres 8.4 . >> Using the previous version (8.3) our queries performs well >> (The queries are quite complex, consisting of several sub-queries and >> various spatial functions). Two things that frequently bite people during an upgrade:

Re: [GENERAL] Postgres 8.3 vs. 8.4 - Query plans and performance

> Hello > > 2011/3/14 Jo : >> I set the work_mem to 100MB and the shared buffers are 2 GB >> >> The query plans are long and complex. I send the beginning of the >> two plans. Hope this helps to understand the differences. >> I assume the join strategy in 8.3 differs from the one in 8.4. >> > > the

Re: [GENERAL] DBMS upgrade and backups

Hello. On 03/14/2011 17:38, Vibhor Kumar wrote: You won't be able to use the Data Directory of New Version for PG 9.0, you have to use your old backup. What is the preferred way to deal with this issue? I would recommend to take pg_dump backup with Filesystem Backup before any upgrade. Ca

Re: [GENERAL] Postgres 8.3 vs. 8.4 - Query plans and performance

Hello 2011/3/14 Jo : > I set the work_mem to 100MB and the shared buffers are 2 GB > > The query plans are long and complex. I send the beginning of the > two plans. Hope this helps to understand the differences. > I assume the join strategy in 8.3 differs from the one in 8.4. > these outputs are

Re: [GENERAL] Postgres 8.3 vs. 8.4 - Query plans and performance

I set the work_mem to 100MB and the shared buffers are 2 GB The query plans are long and complex. I send the beginning of the two plans. Hope this helps to understand the differences. I assume the join strategy in 8.3 differs from the one in 8.4. * The beginn

Re: [GENERAL] DBMS upgrade and backups

On Mar 14, 2011, at 7:36 PM, Alexander Pyhalov wrote: > Am I right that on PostgreSQL upgrade to new major version (e.g. from 9.0 to > 9.1) I'll loose my backups (base backups and wal files will be useless)? Yes, those Backups would not be valid. > So to go to past after DB upgrade I had to i

[GENERAL] DBMS upgrade and backups

Hello. Am I right that on PostgreSQL upgrade to new major version (e.g. from 9.0 to 9.1) I'll loose my backups (base backups and wal files will be useless)? So to go to past after DB upgrade I had to install old version(9.0), recover data and then upgrade DBMS software... What is the preferre

Re: [GENERAL] How do you change the size of the WAL files?

> -Original Message- > From: Andrew Sullivan [mailto:a...@crankycanuck.ca] > Sent: Friday, March 11, 2011 5:02 PM > To: pgsql-general@postgresql.org > Subject: Re: How do you change the size of the WAL files? > > On Fri, Mar 11, 2011 at 12:58:30PM -0500, runner wrote: > > My boss is us

Re: [GENERAL] Values larger than 1/3 of a buffer page cannot be indexed.

On Sun, Mar 13, 2011 at 4:37 PM, Rob Sargent wrote: > > > Brian Hirt wrote: >> >> On Mar 13, 2011, at 12:05 PM, Dmitriy Igrishin wrote: >> >>> Hey Viktor, >>> >>> 2011/3/13 Viktor Nagy >> > >>> >>>    hi, >>> >>>    when trying to insert a long-long value, I get the

Re: [GENERAL] Postgres 8.3 vs. 8.4 - Query plans and performance

Jo, > we have performance problems running several queries pon postgres 8.4 . > Using the previous version (8.3) our queries performs well > (The queries are quite complex, consisting of several sub-queries and > various spatial functions). > > Are there some major changes from 8.3 to 8.4 that ca

[GENERAL] Postgres 8.3 vs. 8.4 - Query plans and performance

Hello, we have performance problems running several queries pon postgres 8.4 . Using the previous version (8.3) our queries performs well (The queries are quite complex, consisting of several sub-queries and various spatial functions). Using a new server with debian squeeze and postgres 8.4 fr