Re: [GENERAL] Dynamically update NEW columns in plpgsql trigger

2010-09-09 Thread Dmitriy Igrishin
Hey Nick, You may do it with PL/pgSQL more easily with hstore module. Please, refer to http://www.postgresql.org/docs/9.0/static/hstore.html Please, look at the hstore(record) and populate_record(record, hstore) function. Hope this helps. And I think it will be more faster then you solution. Reg

[GENERAL] NOT IN vs. OUTER JOIN and NOT NULL

2010-09-09 Thread Martín Marqués
I was looking at rows in a table which are not referenced from another and found some discrepencies. These are the queries (with results): SELECT * from grupo_concursantes where codigo NOT IN (SELECT grupo FROM concursantes); codigo | numero | evento | escuela +++

Re: [GENERAL] NOT IN vs. OUTER JOIN and NOT NULL

2010-09-09 Thread Thom Brown
2010/9/9 Martín Marqués : > I was looking at rows in a table which are not referenced from another > and found some discrepencies. > > These are the queries (with results): > > SELECT * from grupo_concursantes where codigo NOT IN (SELECT grupo > FROM concursantes); >  codigo | numero | evento | esc

[GENERAL] How to inherit search_path from template

2010-09-09 Thread Phui Hock
Hi, How can I create a database template with altered search_path to be inherited by child databases? Say, I created a template named template_a with the following commands: # CREATE DATABASE template_a WITH TEMPLATE template1; # UPDATE pg_database SET datistemplate=true WHERE datname='template_a'

[GENERAL] Regular expression in an if-statement will not work

2010-09-09 Thread Ungermann Carsten
Dear postgres list, I need to verify the value of a column by a regular expression in an if-statement before insert or update. It should be a one to three digit value. I wrote a trigger and a trigger function. In the trigger function I want to use the following if-statement to verify the value:

Re: [GENERAL] Regular expression in an if-statement will not work

2010-09-09 Thread Szymon Guz
On 9 September 2010 12:55, Ungermann Carsten < carsten.ungerm...@ib-ungermann.de> wrote: > Dear postgres list, > > I need to verify the value of a column by a regular expression in an > if-statement before insert or update. It should be a one to three digit > value. I wrote a trigger and a trigger

Re: [GENERAL] error while autovacuuming

2010-09-09 Thread tamanna madaan
Hi All Can I upgrade directly to postgres-8.4 . I think it would also be having the fix for "autovacuum error" which is there in postgres-8.1.6 . But please confirm . Thanks.. Tamanna -Original Message- From: tamanna madaan Sent: Thursday, September 09, 2010 2:40 AM To: 'Michael Glaes

Re: [GENERAL] Memory Errors

2010-09-09 Thread Merlin Moncure
On Wed, Sep 8, 2010 at 6:55 PM, Sam Nelson wrote: > Even if the corruption wasn't a result of that, we weren't too excited about > the process being there to begin with.  We thought there had to be a better > solution than just killing the processes.  So we had a discussion about the > intent of t

Re: [GENERAL] error while autovacuuming

2010-09-09 Thread Alvaro Herrera
Excerpts from tamanna madaan's message of jue sep 09 09:55:01 -0400 2010: > Hi All > > Can I upgrade directly to postgres-8.4 . I think it would also be having > the fix for "autovacuum error" which is there in postgres-8.1.6 . But > please confirm . An upgrade to 8.4 is going to be much more com

[GENERAL] logging postgres to syslog on centos, truncates the postgres message.

2010-09-09 Thread Chris Barnes
Hoping someone can help me with this problem. I am logging postgres to syslog on a CentOS release 5.4 (Final) ( 2.6.18-164.el5 ). When I look for an update statement it appears to be truncated and missing the whole statement. Is there a quick way to resolve this? Is it linux or postgres? Tha

Re: [GENERAL] logging postgres to syslog on centos, truncates the postgres message.

2010-09-09 Thread Tom Lane
Chris Barnes writes: > I am logging postgres to syslog on a CentOS release 5.4 (Final) ( > 2.6.18-164.el5 ). When I look for an update statement it appears to > be truncated and missing the whole statement. Our code for logging to syslog does split long lines into multiple messages --- are you

Re: [GENERAL] Forcing the right queryplan

2010-09-09 Thread Henk van Lingen
On Tue, Sep 07, 2010 at 07:26:25PM +0200, Alban Hertroys wrote: > > Do you have output of explain analyse for these queries as well? It's > hard to see what is actually going on with just the explain - we can't > see which part of the query is more expensive than the planner > expected,

Re: [GENERAL] Forcing the right queryplan

2010-09-09 Thread Tom Lane
Henk van Lingen writes: > -> Bitmap Heap Scan on systemevents (cost=61221.23..668806.93 > rows=239805 width=158) (actual time=9.131..1786.406 rows=464 loops=1) >Recheck Cond: (to_tsvector('english'::regconfig, message) @@ > to_tsquery('131.211.112.9'::text)) Well, the

Re: [GENERAL] logging postgres to syslog on centos, truncates the postgres message.

2010-09-09 Thread Chris Barnes
On separate lines it is, thanks Tom. > To: compuguruchrisbar...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] logging postgres to syslog on centos, truncates the > postgres message. > Date: Thu, 9 Sep 2010 10:39:10 -0400 > From: t...@sss.pgh.pa.us > > Chris Barnes w

Re: [GENERAL] Forcing the right queryplan

2010-09-09 Thread Henk van Lingen
On Thu, Sep 09, 2010 at 10:50:52AM -0400, Tom Lane wrote: > Henk van Lingen writes: > > -> Bitmap Heap Scan on systemevents (cost=61221.23..668806.93 rows=239805 width=158) (actual time=9.131..1786.406 rows=464 loops=1) > >Recheck Cond: (to_tsvector('english'::reg

Re: [GENERAL] Forcing the right queryplan

2010-09-09 Thread Tom Lane
Henk van Lingen writes: > On Thu, Sep 09, 2010 at 10:50:52AM -0400, Tom Lane wrote: Well, there's your problem: the planner is off by a factor of about 500 on its estimate of the number of rows matching this query, and that's what's causing it to pick the wrong plan. What you need

Re: [GENERAL] Regular expression in an if-statement will not work

2010-09-09 Thread Ungermann Carsten
I tried it once more, now. There is no change in the behavior. I use the $$-notation to avoid double "\" and double "'". Also there is no syntax error in this statement. The constraint is a bad option because I have to log errors when I import a csv-file. Thanks and regards Carsten Ungermann

Re: [GENERAL] Regular expression in an if-statement will not work

2010-09-09 Thread Richard Huxton
On 09/09/10 11:55, Ungermann Carsten wrote: Dear postgres list, I need to verify the value of a column by a regular expression in an if-statement before insert or update. It should be a one to three digit value. '^[0-9]{1,3}$' -- don't work Works here. CREATE TEMP TABLE tt (t text); INSERT

Re: [GENERAL] psql '\copy' command for writing binary data from BYTEA column to file

2010-09-09 Thread Merlin Moncure
On Sat, Sep 4, 2010 at 12:24 PM, Julia Jacobson wrote: > Hello everybody out there using PostgreSQL, > One of the tables in my database contains pictures in jpeg format in a > column of the type BYTEA. > I have written a script in Python for inserting pictures into the database > as well as export

Re: [GENERAL] NOT IN vs. OUTER JOIN and NOT NULL

2010-09-09 Thread Alban Hertroys
On 9 Sep 2010, at 13:59, Martín Marqués wrote: > I was looking at rows in a table which are not referenced from another > and found some discrepencies. > > These are the queries (with results): > > SELECT * from grupo_concursantes where codigo NOT IN (SELECT grupo > FROM concursantes); > codigo

Re: [GENERAL] Empty SELECT result at simultaneous calls

2010-09-09 Thread Stefan Wild
> Is this freshly inserted data you're having issues with? > > If so, I suspect you're running into issues where the data > has been written in another transaction but that transaction > has not yet committed. The data is **NOT** visible to other > transactions until the writing transaction commi

[GENERAL] upgrade postgresql from 8.4.x to 8.4.4

2010-09-09 Thread Chris Barnes
Is there a procedure to upgrade from 8.4.x to 8.4.4 or am I over thinking it? I'm hoping I can just yum upgrade postgresql and have it just upgrade it without having to dump and restore the whole db. It would be nice if I had to only bounce postgresql to start using the newer version :) Thank

Re: [GENERAL] upgrade postgresql from 8.4.x to 8.4.4

2010-09-09 Thread Devrim GÜNDÜZ
Right. A restart is enough. -- Devrim GÜNDÜZ PostgreSQL DBA @ Akinon/Markafoni, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz 09.Eyl.2010 tarihinde 22:44 saatinde, Chris Barnes > şun

Re: [GENERAL] error while autovacuuming

2010-09-09 Thread tamanna madaan
Hi Scott Sorry to bug you again. I know that upgrading to postgres-8.1.21 will be my best bet. But I have my own limitations because of which I just want to apply a patch with a single fix which is for autovacuum error. But I don't know how to go about it . Can you please let me know Where I c

Re: [GENERAL] error while autovacuuming

2010-09-09 Thread David Fetter
On Fri, Sep 10, 2010 at 12:53:10AM +0530, tamanna madaan wrote: > Hi Scott > > Sorry to bug you again. > > I know that upgrading to postgres-8.1.21 will be my best bet. But I > have my own limitations because of which I just want to apply a > patch with a single fix which is for autovacuum error

[GENERAL] postgres patch for autovacuum error in postgres

2010-09-09 Thread tamanna madaan
Hi All I am using postgres-8.1.2 . I am getting the below error while autovacuuming template0 database : 2010-08-18 18:36:14 UTC LOG: autovacuum: processing database "template0" 2010-08-18 18:36:14 UTC ERROR: could not access status of transaction 3222599682 2010-08-18 18:36:14 UTC DETAIL

Re: [GENERAL] error while autovacuuming

2010-09-09 Thread tamanna madaan
Hi David You mean to say , change the binary to postgres-8.1.21 and then restart postgres . that's it ?? please confirm.. Thanks.. Tamanna -Original Message- From: David Fetter [mailto:da...@fetter.org] Sent: Friday, September 10, 2010 2:06 AM To: tamanna madaan Cc: Scott Marlowe; pgs

Re: [GENERAL] error while autovacuuming

2010-09-09 Thread David Fetter
On Fri, Sep 10, 2010 at 02:35:29AM +0530, tamanna madaan wrote: > Hi David > > You mean to say , change the binary to postgres-8.1.21 and then restart > postgres . that's it ?? please confirm.. Yes. :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Y

[GENERAL] sql DO in rule 9.0rc1

2010-09-09 Thread A.M.
Is there a technical limitation which prevents DO from being used in rules or am I missing something with this? CREATE RULE test_update AS ON UPDATE TO test DO INSTEAD DO $$ BEGIN;

Re: [GENERAL] error while autovacuuming

2010-09-09 Thread tamanna madaan
Ok . Thanks .. -Original Message- From: David Fetter [mailto:da...@fetter.org] Sent: Friday, September 10, 2010 2:36 AM To: tamanna madaan Cc: Scott Marlowe; pgsql-general@postgresql.org Subject: Re: [GENERAL] error while autovacuuming On Fri, Sep 10, 2010 at 02:35:29AM +0530, tamanna ma

Re: [GENERAL] postgres patch for autovacuum error in postgres

2010-09-09 Thread Arjen Nienhuis
Maybe you can download the latest version of postgres 8.1 and change the version number in the source to 8.1.2 and then compile. Would that solve your problem? On Thu, Sep 9, 2010 at 10:38 PM, tamanna madaan wrote: > Hi All > > > > I am using postgres-8.1.2 . I am getting the below error while au

Re: [GENERAL] postgres patch for autovacuum error in postgres

2010-09-09 Thread Scott Marlowe
On Thu, Sep 9, 2010 at 2:38 PM, tamanna madaan wrote: > Hi All > > I am using postgres-8.1.2 . I am getting the below error while autovacuuming > template0 database : > > > Due to some reason I don’t want to upgrade to  postgres-8.1.6. There are very very very few good reasons to not want to upDA

Re: [GENERAL] Dynamically update NEW columns in plpgsql trigger

2010-09-09 Thread Nick
On Sep 9, 2:21 am, dmit...@gmail.com (Dmitriy Igrishin) wrote: > Hey Nick, > > You may do it with PL/pgSQL more easily with hstore module. > Please, refer tohttp://www.postgresql.org/docs/9.0/static/hstore.html > Please, look at the hstore(record) and populate_record(record, hstore) > function. Hop

Re: [GENERAL] Regular expression in an if-statement will not work

2010-09-09 Thread Ungermann Carsten
I have discovered the reasons of my problem. Firstly I made a mistake at the type declaration of the column "value". It was "character(3)". So that missing characters were filled with spaces and the regular expression in case of less than three digits couldn't match at the end ("$"). I changed

Re: [GENERAL] sql DO in rule 9.0rc1

2010-09-09 Thread Jeff Davis
On Thu, 2010-09-09 at 17:07 -0400, A.M. wrote: > Is there a technical limitation which prevents DO from being used in rules or > am I missing something with this? > > CREATE RULE test_update AS ON UPDATE TO test DO INSTEAD DO $$ >

[GENERAL] User function canceling VACUUMDB utility

2010-09-09 Thread Carlos Henrique Reimer
Hi, We are facing the following problem in a PG 8.2 server when trying to vacuum one of our databases: vacuumdb: vacuuming database "reimer" INFO: vacuuming "pg_catalog.pg_database" INFO: "pg_database": found 0 removable, 6 nonremovable row versions in 1 pages INFO: index "pg_database_datname_

Re: [GENERAL] Dynamically update NEW columns in plpgsql trigger

2010-09-09 Thread Merlin Moncure
On Thu, Sep 9, 2010 at 6:34 PM, Nick wrote: > On Sep 9, 2:21 am, dmit...@gmail.com (Dmitriy Igrishin) wrote: >> Hey Nick, >> >> You may do it with PL/pgSQL more easily with hstore module. >> Please, refer tohttp://www.postgresql.org/docs/9.0/static/hstore.html >> Please, look at the hstore(record)

Re: [GENERAL] How to inherit search_path from template

2010-09-09 Thread Merlin Moncure
On Thu, Sep 9, 2010 at 7:13 AM, Phui Hock wrote: > Hi, > How can I create a database template with altered search_path to be > inherited by child databases? Say, I created a template named > template_a with the following commands: It doesn't really work that way -- GUC values are global, not per

Re: [GENERAL] User function canceling VACUUMDB utility

2010-09-09 Thread Tom Lane
Carlos Henrique Reimer writes: > We are facing the following problem in a PG 8.2 server when trying to vacuum > one of our databases: > vacuumdb: vacuuming of database "reimer" failed: ERROR: invalid type name > "TT_TIT.SEQCAN%TYPE" > [ which seems to be coming from out-of-date code in a function

Re: [GENERAL] Empty SELECT result at simultaneous calls

2010-09-09 Thread Craig Ringer
On 09/10/2010 03:33 AM, Stefan Wild wrote: Thanks for the good Idea! Unfortunately it is old data (already inserted since some days) so this cannot be the reason... Is there any chance you can provide a self-contained example that includes both a populated database and the complete, compilab

Re: [GENERAL] error while autovacuuming

2010-09-09 Thread Craig Ringer
On 09/10/2010 05:05 AM, tamanna madaan wrote: Hi David You mean to say , change the binary to postgres-8.1.21 and then restart postgres . that's it ?? please confirm.. Strictly, you should install the whole of PostgreSQL 8.1.21, replacing your old installation of the older version. Replacin

Re: [GENERAL] postgres patch for autovacuum error in postgres

2010-09-09 Thread Craig Ringer
On 09/10/2010 04:38 AM, tamanna madaan wrote: Due to some reason I don’t want to upgrade to postgres-8.1.6. Why? Seriously? If Oracle announced that they were releasing a patch for six critical data-loss bugs in 9i, one of which affected you, would you call support and ask them to make a cu

Re: [GENERAL] postgres patch for autovacuum error in postgres

2010-09-09 Thread Craig Ringer
If Oracle announced that they were releasing a patch for six critical data-loss bugs in 9i, one of which affected you, would you call support and ask them to make a custom patch just for you that only fixed the one you had encountered? Oh, and when they say they can't guarantee that fix will w

Re: [GENERAL] postgres patch for autovacuum error in postgres

2010-09-09 Thread Scott Marlowe
On Thu, Sep 9, 2010 at 8:46 PM, Craig Ringer wrote: > On 09/10/2010 04:38 AM, tamanna madaan wrote: > >> Due to some reason I don’t want to upgrade to postgres-8.1.6. > > Why? Seriously? > Also note that it's possible this user has found a new unfixed bug in the 8.1 branch. If he goes to 8.1.21