[GENERAL] What filesystem to use for postgres?

[A B]

Hello.

Is there any doc or wiki page that describes what filesystems that are
recomended to  use (OS is Linux) for PostgreSQL?
Information about  filesystems options/mount options   and how well
they work with different RAID setups is also of interest.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Replacing characters in a string

[Luís de Sousa]

Hello everyone,

I need to replace all occurrences of a certain character in a string.
For that I'm using regexp_replace, but so far I only managed to
replace the first character, here's an example:

> SELECT regexp_replace('xaxx', 'x', 'e');
 regexp_replace

 eaxx
(1 row)

But the result I'd need is 'eaee'. How can I do it?

Thanks,

Luís

-- 
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] Replacing characters in a string

[Sergey Konoplev]

2010/9/14 Luís de Sousa :
>> SELECT regexp_replace('xaxx', 'x', 'e');
>  regexp_replace
> 
>  eaxx
> (1 row)
>
> But the result I'd need is 'eaee'. How can I do it?

Just specify 'g' as the flags parameter (the 4th one). It means 'globally'.

SELECT regexp_replace('xaxx', 'x', 'e', 'g');

There are more of this flags described here:
http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE

p.s. The question is for hackers - BTW I did not find 'g' in this
table, is it a docs bug?

>
> Thanks,
>
> Luís
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802

-- 
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] Replacing characters in a string

[Michele Petrazzo - Unipex]

Luís de Sousa ha scritto:

Hello everyone,

I need to replace all occurrences of a certain character in a string.
For that I'm using regexp_replace, but so far I only managed to
replace the first character, here's an example:


SELECT regexp_replace('xaxx', 'x', 'e');

  regexp_replace

  eaxx
(1 row)

But the result I'd need is 'eaee'. How can I do it?



Using the right function? :)

test=# SELECT replace('xaxx', 'x', 'e');
 replace
-
 eaee
(1 row)

Your usage involving regexp!

P.s. google for "replace string postgresql"  and I'm feeling lucky


Thanks,

Luís



Michele


--
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] What filesystem to use for postgres?

[Bill Moran]

In response to A B :

> Hello.
> 
> Is there any doc or wiki page that describes what filesystems that are
> recomended to  use (OS is Linux) for PostgreSQL?
> Information about  filesystems options/mount options   and how well
> they work with different RAID setups is also of interest.

Wow ... I did this a LONG time ago, so the results need to be taken
with a grain of "that code has assuredly changed since that was done".

http://www.potentialtech.com/wmoran/postgresql.php

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] What filesystem to use for postgres?

[Aaron]

Some more current numbers can be found here:
http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide


Aaron Thul
http://www.chasingnuts.com



On Tue, Sep 14, 2010 at 4:00 AM, A B  wrote:
> Hello.
>
> Is there any doc or wiki page that describes what filesystems that are
> recomended to  use (OS is Linux) for PostgreSQL?
> Information about  filesystems options/mount options   and how well
> they work with different RAID setups is also of interest.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Int to Interval conversion

[Howard Cole]

 Hi everyone,

I have a table with a duration field, stored as an Integer. How can I 
retrieve this from the table as an Interval type?


E.g.

create table test (test_id serial primary key, time_in_minutes int);
insert into test values (1440);

Now how do I extract that as 24:00:00::interval in a query?

Thanks.

Howard Cole.

--
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] Int to Interval conversion

[Bill Moran]

In response to Howard Cole :
> 
> I have a table with a duration field, stored as an Integer. How can I 
> retrieve this from the table as an Interval type?
> 
> E.g.
> 
> create table test (test_id serial primary key, time_in_minutes int);
> insert into test values (1440);
> 
> Now how do I extract that as 24:00:00::interval in a query?

SELECT CAST(time_in_minutes || ' minutes' AS INTERVAL) FROM test;

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] Int to Interval conversion

[Szymon Guz]

On 14 September 2010 13:57, Howard Cole  wrote:

>  Hi everyone,
>
> I have a table with a duration field, stored as an Integer. How can I
> retrieve this from the table as an Interval type?
>
> E.g.
>
> create table test (test_id serial primary key, time_in_minutes int);
> insert into test values (1440);
>
> Now how do I extract that as 24:00:00::interval in a query?
>
>  


select 1440 * interval '1 minute';

regards
Szymon Guz

Re: [GENERAL] Int to Interval conversion

[Adrian Klaver]

On Tuesday 14 September 2010 4:57:46 am Howard Cole wrote:
>   Hi everyone,
>
> I have a table with a duration field, stored as an Integer. How can I
> retrieve this from the table as an Interval type?
>
> E.g.
>
> create table test (test_id serial primary key, time_in_minutes int);
> insert into test values (1440);
>
> Now how do I extract that as 24:00:00::interval in a query?
>
> Thanks.
>
> Howard Cole.

test=> SELECT 1440 * interval '1 min';
 ?column?
--
 24:00:00
(1 row)


-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
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] What filesystem to use for postgres?

[Matthieu HUIN]

Greetings,

This wiki entry might be of interest to you:

http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide

I don't know how up to date these benchmarks are though.

Matthieu Huin

Le mardi 14 septembre 2010 à 08:35 -0400, Bill Moran a écrit :
> In response to A B :
> 
> > Hello.
> > 
> > Is there any doc or wiki page that describes what filesystems that are
> > recomended to  use (OS is Linux) for PostgreSQL?
> > Information about  filesystems options/mount options   and how well
> > they work with different RAID setups is also of interest.
> 
> Wow ... I did this a LONG time ago, so the results need to be taken
> with a grain of "that code has assuredly changed since that was done".
> 
> http://www.potentialtech.com/wmoran/postgresql.php
> 
> -- 
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
> 



-- 
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] Int to Interval conversion

[Michael Glaesemann]

On Sep 14, 2010, at 7:57 , Howard Cole wrote:

> create table test (test_id serial primary key, time_in_minutes int);
> insert into test values (1440);
> 
> Now how do I extract that as 24:00:00::interval in a query?

test=# SELECT 1440 * INTERVAL '1 minute';
 ?column? 
--
 24:00:00
(1 row)

Michael Glaesemann
grzm seespotcode net




-- 
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] Monitoring Object access

[adi hirschtein]

I'd like to look at it from the object level and see how much I/O is being
done on specific table or index and then check which sessions are
responsible for that.

also, what's the catalog table you would recommend me to use if I want to
see I/O activity on an object regardless of the session?

On Mon, Sep 13, 2010 at 6:57 PM, Greg Smith  wrote:

> adi hirschtein wrote:
>
>> Using the catalog tables, is there any way to correlate session id/user id
>> to which object (i.e. tables, indexes etc) it access and much how disk reads
>> or I/O wait has been done against the objects.
>> in general, I'd like to see which objects are being accessed by which user
>> and the time/amount of I/O wait/reads.
>>
>
> On recent Linux systems, the iotop utility is handy to figure out which
> individual users are doing lots of I/O.  There are some cases where the user
> doing the I/O and the one who caused the I/O are different, which includes
> things from synchronized scans to background writer writes.  But for the
> most part that utility gives a useful view into per-user I/O.
>
> Mark Wong has done some good work toward integrating that same data source
> on Linux into something you can query and match against database activity in
> his pg_proctab project:
> http://www.slideshare.net/markwkm/pgproctab-accessing-system-stats-in-postgresql-3573304
>
> And if you're on Solaris you can extract of a lot of this data with custom
> DTrace scripting.
>
> I have a rough plan for directly instrumenting more of this information
> from within the database, more like what Oracle does here.  But that's going
> to take months of development time, and I'm not sure the PostgreSQL core
> will even accept the overhead it would add in all cases.  If we could get
> one Oracle user who's on the fence over a PostgreSQL conversion to throw a
> small portion of the money they'd save toward that project, I'm sure I could
> get it developed.  It's just that nobody has been interested enough in such
> a thing to sponsor it so far.
>
> --
> Greg Smith  2ndQuadrant US  Baltimore, MD
> PostgreSQL Training, Services and Support
> g...@2ndquadrant.com   www.2ndQuadrant.us
>
>

Re: [GENERAL] Replacing characters in a string

[Pavel Stehule]

Hello

2010/9/14 Luís de Sousa :
> Hello everyone,
>
> I need to replace all occurrences of a certain character in a string.
> For that I'm using regexp_replace, but so far I only managed to
> replace the first character, here's an example:
>
>> SELECT regexp_replace('xaxx', 'x', 'e');
>  regexp_replace
> 
>  eaxx
> (1 row)
>
> But the result I'd need is 'eaee'. How can I do it?

postgres=# select replace('abcdeabcde','a','x');
  replace

 xbcdexbcde
(1 row)

or

postgres=# SELECT regexp_replace('xaxx', 'x', 'e','g'); -- use a flag Global
 regexp_replace

 eaee
(1 row)

Regards

Pavel Stehule

>
> Thanks,
>
> Luís
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] Replacing characters in a string

[Thom Brown]

2010/9/14 Luís de Sousa :
> Hello everyone,
>
> I need to replace all occurrences of a certain character in a string.
> For that I'm using regexp_replace, but so far I only managed to
> replace the first character, here's an example:
>
>> SELECT regexp_replace('xaxx', 'x', 'e');
>  regexp_replace
> 
>  eaxx
> (1 row)
>
> But the result I'd need is 'eaee'. How can I do it?

Just remove the "regexp" bit:

SELECT replace('xaxx', 'x', 'e');

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

-- 
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] Replacing characters in a string

[Szymon Guz]

2010/9/14 Luís de Sousa 

> Hello everyone,
>
> I need to replace all occurrences of a certain character in a string.
> For that I'm using regexp_replace, but so far I only managed to
> replace the first character, here's an example:
>
> > SELECT regexp_replace('xaxx', 'x', 'e');
>  regexp_replace
> 
>  eaxx
> (1 row)
>
> But the result I'd need is 'eaee'. How can I do it?
>
>
Hi,
try this one:

SELECT regexp_replace('xaxx', 'x', 'e', 'g');

regards
Szymon Guz

Re: [GENERAL] Replacing characters in a string

[Luís de Sousa]

Thank you for all the answers, several ways this can be made.

Luís

-- 
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] Monitoring Object access

[tv]

> I'd like to look at it from the object level and see how much I/O is being
> done on specific table or index and then check which sessions are
> responsible for that.
>
> also, what's the catalog table you would recommend me to use if I want to
> see I/O activity on an object regardless of the session?

There is a bunch of interesting tables - see pg_stat_ and pg_statio_ tables.

Don't forget the values grow all the time, so you'll have to do snapshots
and subtract them. You could do that by hand, or use a tool for that.
There is a "pgstatspack" (should work fine, although it's not developed
since 8.1 AFAIK) and I'm working on an alternative tool with a web GUI
(http://sourceforge.net/projects/pgmonitor/). Still under development and
I know about several issues, but generally works (thanks in advance for
any feedback).

Tomas


-- 
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] Monitoring Object access

[Satoshi Nagayasu]

On 2010/09/12 23:02, adi hirschtein wrote:

I'm coming from the Oracle side of the house and In oracle for instance, you 
use shared buffer as well, but you are still able to see which session is 
waiting for which blocks
and if one session is doing the "real" I/O then the other one wait on 'wait for 
other session" event so you are able to know who did the actual I/O
the reason behind it is that you want to check which objects is being heavily 
hit by which  business processes or users and then tier your storage 
accordingly.
I agree with your point about the OS buffer cache, I need to monitor it as well.
is there any place rather than pg_stat_activity that you think I should take a 
look at?


I think you should also look at pg_locks to know
which session is processing (or waiting on locks).

http://www.postgresql.org/docs/8.4/interactive/view-pg-locks.html

pg_locks table contains several lock information
including "lock dependencies" which you may need
to monitor session activities.

--
NAGAYASU Satoshi 

--
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] Post Install / Secure PostgreSQL

[Carlos Mennens]

On Mon, Sep 13, 2010 at 9:58 PM, Craig Ringer
 wrote:
> craig$ sudo -u postgres psql
> postgres=> CREATE USER craig WITH PASSWORD 'somepassword'
>           CREATEDB CREATEROLE;
> postgres=> CREATE DATABASE craig WITH OWNER craig;
> postgres=> \q

So I set a Linux shell password on my newly auto created 'postgres'
system user which is what I use to login to the database as
'superuser'. Now I know my password for 'postgres' in the Linux shell
but I still don't understand what the database password is for
'postgres'. In MySQL there is a root shell user (obviously) and then
rather than 'postgres' for the database super user, there is a 'root'
database user and I can set that password individually from the
matching shell account.

So maybe I am still lost but it appears that the database user
'postgres' has a password unique to PostgreSQL, right?

postgres=# SELECT * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |
valuntil | useconfig
--+--+-+--+---+--+--+---
 postgres |   10 | t   | t| t |  |
 |
 carlos   |16384 | t   | t| t |  |
 |

Obviously there appears to be a specific password for both accounts
which I think are completely seperate from the Linux shell passwords,
right?

Secondly I am unable to find any information in the docs that show me
how to set just the user password for 'carlos'. In MySQL I would use:

SET PASSWORD FOR 'carlos'@'localhost' = PASSWORD('newpass');

-- 
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] Post Install / Secure PostgreSQL

[Alan Hodgson]

On September 14, 2010 09:50:30 am Carlos Mennens wrote:
> Obviously there appears to be a specific password for both accounts
> which I think are completely seperate from the Linux shell passwords,
> right?

PostgreSQL has internal passwords for roles which can be set with "alter role" 
or while creating roles.

However ..

The default setup allows "trust" access which means it trusts local system 
accounts to login as the same roles in Pg without specifying a password.

It is also possible to setup Pg in such a way that it uses the system 
passwords via PAM without consulting the internal password.


-- 
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] Post Install / Secure PostgreSQL

[Richard Broersma]

On Tue, Sep 14, 2010 at 9:50 AM, Carlos Mennens
 wrote:
> Secondly I am unable to find any information in the docs that show me
> how to set just the user password for 'carlos'. In MySQL I would use:
>
> SET PASSWORD FOR 'carlos'@'localhost' = PASSWORD('newpass');


You'd want to use "ALTER USER"

http://www.postgresql.org/docs/8.4/interactive/sql-alteruser.html

So:

ALTER USER carlos WITH ENCRYPTED PASSWORD 'password';



-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
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] Post Install / Secure PostgreSQL

[Carlos Mennens]

On Tue, Sep 14, 2010 at 1:52 PM, Richard Broersma 
> You'd want to use "ALTER USER"
>
> http://www.postgresql.org/docs/8.4/interactive/sql-alteruser.html
>
> So:
>
> ALTER USER carlos WITH ENCRYPTED PASSWORD 'password';

I find it strange when I am logged in as super user 'postgres' and
type the exact syntax but after 'carlos' above, for some reason it no
longer auto completes 'WITH' for some reason. Then when I manually
just type the word 'WITH' even though the tab auto-complete didn't
recognize it. I then type 'ENCRY' and press the tab key, PostgreSQL
for some odd reason changes the syntax of 'ENCRY' to 'RECURSIVE'. I
don't understand this database behavior & understand that I can
manually just type everything you posted above and the command works
but I depend on auto complete and this doesn't make any sense.

Am I doing something wrong for why PostgreSQL just randomly alters my
input when I press the 'tab' key?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Identify Login User & Permissions

[Carlos Mennens]

I can't find in the docs or using Google how one can identify which
user is currently logged in to psql. I search for the command that if
for some reason I forget which user I am logged in as, rather than
logging out of PostgreSQL, there has to be a command that shows me who
I am and also what kind of special permissions my current user has.
Can anyone please assist me or point me in the right direction?

-- 
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] Identify Login User & Permissions

[Guillaume Lelarge]

Le 14/09/2010 22:01, Carlos Mennens a écrit :
> I can't find in the docs or using Google how one can identify which
> user is currently logged in to psql. I search for the command that if
> for some reason I forget which user I am logged in as, rather than
> logging out of PostgreSQL, there has to be a command that shows me who
> I am and also what kind of special permissions my current user has.
> Can anyone please assist me or point me in the right direction?
> 

SELECT current_user;


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
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] Identify Login User & Permissions

[Richard Broersma]

On Tue, Sep 14, 2010 at 1:01 PM, Carlos Mennens
 wrote:
> I can't find in the docs or using Google how one can identify which
> user is currently logged in to psql. I search for the command that if
> for some reason I forget which user I am logged in as, rather than
> logging out of PostgreSQL, there has to be a command that shows me who
> I am and also what kind of special permissions my current user has.
> Can anyone please assist me or point me in the right direction?

I don't have the entire answer.  But here is a place to start:

http://www.postgresql.org/docs/9.0/static/functions-info.html

Note that there is a slight distinction between current_user and session_user.

postgres=> select user, current_user, session_user;
 current_user | current_user | session_user
--+--+--
 broersr  | broersr  | broersr
(1 row)


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
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] Identify Login User & Permissions

[Carlos Mennens]

On Tue, Sep 14, 2010 at 4:23 PM, Guillaume Lelarge
 wrote:
> SELECT current_user;

Thanks. That worked well:

postgres=# SELECT current_user;
 current_user
--
 carlos
(1 row)

Do you know how I can verify what privileges or permissions 'carlos'
has granted to him in PostgreSQL?

-- 
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] I keep getting "type does not exist" on compile of this SETOF function (list 2 table)

[Jonathan Brinkman]

Thanks, yes the schema was missing from the DECLARE rs statement!

-Original Message-
From: Merlin Moncure [mailto:mmonc...@gmail.com] 
Sent: Monday, September 13, 2010 1:35 PM
To: Jonathan Brinkman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] I keep getting "type does not exist" on compile of
this SETOF function (list 2 table)

On Mon, Sep 13, 2010 at 11:17 AM, Jonathan Brinkman
 wrote:
> [CODE]
>
> BEGIN;
>
> DROP TYPE structure.format_list2table_rs CASCADE;
>
> CREATE TYPE structure.format_list2table_rs AS (
>  "item" VARCHAR(4000)
> );
>
> END;
>
> CREATE OR REPLACE FUNCTION structure.format_list2table (
>  "v_list" varchar,
>  "v_delim" varchar
> )
> RETURNS SETOF structure.format_list2table_rs AS
> $body$
> /*
> select * from Format_List2Table('1', '1');
> SELECT item FROM Format_List2Table('first||2nd||III||1+1+1+1','||');
> SELECT CAST(item AS INT) AS Example2 FROM
> Format_List2Table('111,222,333,444,555',',');
> SELECT item FROM Format_List2Table('12/1/2009, 12/2/2009, 12/3/2009,
> 12/4/2009, 12/7/2009, 12/8/2009,, 12/9/2009, 12/10/2009,
12/11/2009,',',');
> SELECT * FROM Format_List2Table('1988,1390',',');
> SELECT * FROM Format_List2Table('1988',',');
> SELECT * FROM Format_List2Table('1988 1390 5151 5i7151 515545',' ');
> */
>
> DECLARE
>   v_item  VARCHAR(4000);
>   v_Pos  INTEGER;
>   v_RunLastTime  INTEGER;
>   SWV_List VARCHAR(4000);
>   SWV_Rs format_list2table_rs;
>
> BEGIN
>  --  SWV_List := v_List;
>   BEGIN
>      CREATE GLOBAL TEMPORARY TABLE tt_PARSEDLIST
>                                (item VARCHAR(4000)) WITH OIDS;
>      exception when others then truncate table tt_PARSEDLIST;
>   END;
>   SWV_List := v_list;
>   v_RunLastTime := 0;
>   SWV_List := CASE POSITION(v_delim IN SWV_List) WHEN 0 THEN
> coalesce(SWV_List,'') || coalesce(v_delim,'') ELSE SWV_List END; --fix
lists
> with only 1 item
>   v_Pos := POSITION(v_delim IN SWV_List);
>   WHILE v_Pos > 0 LOOP
>      v_item := LTRIM(RTRIM(SUBSTR(SWV_List,1,v_Pos -1)));
>      IF v_item <> '' THEN
>                                        INSERT INTO tt_PARSEDLIST(item)
>                                                VALUES(CAST(v_item AS
> VARCHAR(4000)));
>      ELSE
>         INSERT INTO tt_PARSEDLIST(item)
>                                                VALUES(NULL);
>      END IF;
>      SWV_List := SUBSTR(SWV_List,length(SWV_List) -ABS(LENGTH(SWV_List)
> -v_Pos)+1);
>      v_Pos := POSITION(v_delim IN SWV_List);
>      IF SWV_List = '' THEN v_Pos = null;
>      END IF;
>      IF v_Pos = 0 AND v_RunLastTime <> 1 then
>         v_RunLastTime := 1;
>         v_Pos := LENGTH(SWV_List)+1;
>      END IF;
>   END LOOP;
>
>   FOR SWV_Rs IN(SELECT * FROM  tt_PARSEDLIST) LOOP
>      RETURN NEXT SWV_Rs;
>   END LOOP;
>   RETURN;
> END;
> $body$
> LANGUAGE 'plpgsql'
> VOLATILE
> CALLED ON NULL INPUT
> SECURITY INVOKER
> ;

is 'structure' in your search_path? in the declare section you didn't
prefix w/namespace:
>   SWV_Rs format_list2table_rs;

but you did everywhere else.

merlin


-- 
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] Identify Login User & Permissions

[Richard Broersma]

On Tue, Sep 14, 2010 at 1:28 PM, Carlos Mennens
 wrote:

> Do you know how I can verify what privileges or permissions 'carlos'
> has granted to him in PostgreSQL?

psql has its own commands.



for example if i was to see the owner of all:

users:
db=> \du

schemas:
db=> \dn

tables/views:
db => \d

other useful commands:
db => \?

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
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] Identify Login User & Permissions

[Guillaume Lelarge]

Le 14/09/2010 22:28, Carlos Mennens a écrit :
> On Tue, Sep 14, 2010 at 4:23 PM, Guillaume Lelarge
>  wrote:
>> SELECT current_user;
> 
> Thanks. That worked well:
> 
> postgres=# SELECT current_user;
>  current_user
> --
>  carlos
> (1 row)
> 
> Do you know how I can verify what privileges or permissions 'carlos'
> has granted to him in PostgreSQL?
> 

You need to check many things on the catalogs to know that. You won't
find a single command to do that.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] workaround steps for autovaccum problem

[tamanna madaan]

Hi All

 

I am using postgres-8.1.2. I am getting the following error while
autovacuum.

 

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: could not open file "pg_clog/0C01": No
such file or directory

 

 

After googling around about this error , I got to know that  following
workaround will resolve the problem :

 

1.  set 'datallowconn' to true for template0 database in pg_database
table.

2. Stop postgres 
3. create 256K zero filled  0C01 file in /var/lib/pgsql/data/pg_clog
folder.
4. Start postgres 
5. Execute `vacuum freeze` for all the databases i.e template0 ,
template1, postgres and any user defined database
6. set 'datallowconn' to false for template0 database in pg_database
table.

 

These steps resolve the problem indeed. But I have a question regarding
'vacuum freeze'

 

As far as I know, vacuum freeze does the following :

 

"Vacuum freeze is used to freeze the tuples in a DB so that they dont
suffer transaction ID wraparound in case DB is not vacuumed properly."

 

My database was being vacuumed properly by autovacuum before this
problem occurred. Autovaccum was taking care of template0 , template1,
postgres and my database let say its abc.

 

Autovacuum is getting invoked after every 5 mins and vacuums all the
database turn by turn and every database is getting its turn of
autovaccum after every 20 mins as there are 4 databases (template0 ,
template1, postgres and abc).

 

Now suppose , this autovacuum problem occurs and through some script its
detected immediately at the very onset of the problem and above
mentioned workaround steps (upto step 4) are executed.

Then still , do I need to execute 'vacuum freeze' on all databases ??
Because, my databases were already getting autovacuumed properly before
the problem , the problem was resolved 

as soon as it occurred within let say 2 mins and after the problem is
resolved by workaround steps, then autovacuum will   start vacuuming all
the 4 databases

 every 20 mins as I mentioned above. So, please confirm if in this case
I need to do 'vacuum freeze' for all the databases or I can skip this
step in workaround procedure.

 

Thanks..

Tamanna 

Re: [GENERAL] workaround steps for autovaccum problem

[David Fetter]

On Wed, Sep 15, 2010 at 02:39:26AM +0530, tamanna madaan wrote:
> Hi All
> 
>  
> 
> I am using postgres-8.1.2. I am getting the following error while
> autovacuum.

Please upgrade your software to PostgreSQL 8.1.21 and try again.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Extracting data from BYTEA column to binary file using libpq

[Julia Jacobson]

Hello everybody out there using PostgreSQL,

What is the problem with the following C++ code for the extraction of 
data from a BYTEA column to a binary file?


#include 
#include 
#include 
#include 
#include "libpq-fe.h"
using namespace std;

main ()
{
  PGconn *conn;
  conn = PQconnectdb("hostaddr='databaseserver.com' port='5432' 
dbname='test_db' user='test_user' password='secret'");

  int size;
  const char* contents;
  PGresult* res;
  res = PQexecParams(conn,
  "SELECT filecontent FROM pictures WHERE picture_id='3'",
  0, NULL,NULL,NULL,NULL,
  1);

  if (res && PQresultStatus(res)==PGRES_TUPLES_OK)
  {
size = PQgetlength(res, 0, 0);
contents = PQgetvalue(res, 0, 0);
  }
  ofstream myFile ("picture.jpg", ios::out | ios::binary);
  myFile.write (contents);
  myFile.close();
}

Thanks in advance,
Julia

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Bryan Murphy]

I can't get md5 authentication working with postgres 9rc1 and pgpool-II 3.0.
 I see references to "pool_passwd" in the pgpool documentation, but I see
nothing indicating *where* this file should exist and how pgpool finds it.
 I've set my accounts up in pcp.conf, however, I do not believe this is what
is expected.

When I try to access the database with md5 turned on, I see the following
error in my pgpool debug log: read_password_packet_password: failed to read
password packet "p"

I can get everything working with trust.

postgres log:
*LOG:  unexpected EOF on client connection*
LOG:  disconnection: session time: 0:00:00.007 user=test database=test
host=10.201.121.204 port=42812

psql output:
psql -h 127.0.0.1 -p  test test
Password for user test:
*psql: server closed the connection unexpectedly
   This probably means the server terminated abnormally
   before or while processing the request.
*
pgpool debug log:
2010-09-14 21:38:51 DEBUG: pid 6633: I am 6633 accept fd 6
2010-09-14 21:38:51 LOG:   pid 6633: connection received: host=127.0.0.1
port=48336
2010-09-14 21:38:51 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
test user: test
2010-09-14 21:38:51 DEBUG: pid 6633: new_connection: connecting 0 backend
2010-09-14 21:38:51 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
is not available
2010-09-14 21:38:51 DEBUG: pid 6633: pool_read_message_length: slot: 0
length: 12
2010-09-14 21:38:51 DEBUG: pid 6633: trying md5 authentication
2010-09-14 21:38:51 DEBUG: pid 6633: DB node id: 0 salt: d3da5234
*2010-09-14 21:38:51 DEBUG: pid 6633: read_password_packet_password: failed
to read password packet "p"
2010-09-14 21:38:51 ERROR: pid 6633: do_md5: read_password_packet failed
*2010-09-14 21:38:51 DEBUG: pid 6633: do_md5failed in slot 0
2010-09-14 21:38:53 DEBUG: pid 6633: I am 6633 accept fd 6
2010-09-14 21:38:53 LOG:   pid 6633: connection received: host=127.0.0.1
port=48338
2010-09-14 21:38:53 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
test user: test
2010-09-14 21:38:53 DEBUG: pid 6633: new_connection: connecting 0 backend
2010-09-14 21:38:53 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
is not available
2010-09-14 21:38:53 DEBUG: pid 6633: pool_read_message_length: slot: 0
length: 12
2010-09-14 21:38:53 DEBUG: pid 6633: trying md5 authentication
2010-09-14 21:38:53 DEBUG: pid 6633: DB node id: 0 salt: 855592aa
2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler called
2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: call wait3
*2010-09-14 21:38:53 ERROR: pid 6603: Child process 6633 was terminated by
segmentation fault
2010-09-14 21:38:53 DEBUG: pid 6603: child 6633 exits with status 11 by
signal 11
*2010-09-14 21:38:53 DEBUG: pid 6603: fork a new child pid 6671
2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: normally exited

pg_hba.conf:
local   all all trust
hostall all 127.0.0.1/32trust
hostall all ::1/128 trust
hostall all 0.0.0.0/0   md5

pool_hba.conf:
local   all all   trust
host   all all 0.0.0.0/0
md5

pgpool.conf:
listen_addresses = '*'
port = 
pcp_port = 9898
socket_dir = '/srv/pgpool/run'
pcp_socket_dir = '/srv/pgpool/run'
backend_socket_dir = '/srv/pgpool/run'
pcp_timeout = 10
num_init_children = 64
max_pool = 4
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
client_idle_limit = 0
authentication_timeout = 60
logdir = '/srv/pgpool/log'
pid_file_name = '/srv/pgpool/run/pgpool.pid'
replication_mode = false
load_balance_mode = false
replication_stop_on_mismatch = false
failover_if_affected_tuples_mismatch = false
replicate_select = false
reset_query_list = 'ABORT; DISCARD ALL'
white_function_list = ''
black_function_list = 'nextval,setval'
print_timestamp = true
master_slave_mode = false
master_slave_sub_mode = 'slony'
delay_threshold = 0
log_standby_delay = 'none'
connection_cache = true
health_check_timeout = 20
health_check_period = 0
health_check_user = 'nobody'
failover_command = ''
failback_command = ''
fail_over_on_backend_error = true
insert_lock = true
ignore_leading_white_space = true
log_statement = false
log_per_node_statement = false
log_connections = true
log_hostname = false
parallel_mode = false
enable_query_cache = false
pgpool2_hostname = ''
system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''
backend_hostname0 = '10.215.74.98'
backend_port0 = 5432
backend_weight0 = 1
*enable_pool_hba = true
*recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
lobj_lock_table = ''
ssl = false
debug_level = 100

[GENERAL] select sql slow inside function

[Gary Fu]

Hi,

I have a function proc_TaskComplete that inserts a record to table 
TaskHistory and then calls another function proc_ExportTaskComplete, 
that will retrieve (select) the record just inserted based on an index 
column (TaskId) in that table TaskHistory.  I noticed that the select 
sql (inside proc_ExportTaskComplete) will take 3 seconds.  Under normal 
condition (psql) the select sql is fast enough with the index. Can 
anyone explain why and how to fix the problem ?

My postgresql version is 8.4.4

Thanks,
Gary

--
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] Extracting data from BYTEA column to binary file using libpq

[Daniel Verite]

Julia Jacobson wrote:

>   ofstream myFile ("picture.jpg", ios::out | ios::binary);
>   myFile.write (contents);

You must specify the number of bytes to write.

Best regards,
-- 
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

-- 
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] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working

[Tatsuo Ishii]

> I can't get md5 authentication working with postgres 9rc1 and pgpool-II 3.0.
>  I see references to "pool_passwd" in the pgpool documentation, but I see
> nothing indicating *where* this file should exist and how pgpool finds it.
>  I've set my accounts up in pcp.conf, however, I do not believe this is what
> is expected.

Sorry for not enough description about pool_passwd. It's located under
the same directory as pgpool.conf.  So the default is
/usr/local/etc/pool_passwd.

You need to create /usr/local/etc/pool_passwd if the uid to run pgpool
server does not have the right to create a new file under
/usr/local/etc, you need to create an empty /usr/local/etc/pool_passwd
owned by the same uid as pgpool. After that you should be able to
register your password as decribed in the doc.

Authentication / Access Controls
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> When I try to access the database with md5 turned on, I see the following
> error in my pgpool debug log: read_password_packet_password: failed to read
> password packet "p"
> 
> I can get everything working with trust.
> 
> postgres log:
> *LOG:  unexpected EOF on client connection*
> LOG:  disconnection: session time: 0:00:00.007 user=test database=test
> host=10.201.121.204 port=42812
> 
> psql output:
> psql -h 127.0.0.1 -p  test test
> Password for user test:
> *psql: server closed the connection unexpectedly
>This probably means the server terminated abnormally
>before or while processing the request.
> *
> pgpool debug log:
> 2010-09-14 21:38:51 DEBUG: pid 6633: I am 6633 accept fd 6
> 2010-09-14 21:38:51 LOG:   pid 6633: connection received: host=127.0.0.1
> port=48336
> 2010-09-14 21:38:51 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
> test user: test
> 2010-09-14 21:38:51 DEBUG: pid 6633: new_connection: connecting 0 backend
> 2010-09-14 21:38:51 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
> is not available
> 2010-09-14 21:38:51 DEBUG: pid 6633: pool_read_message_length: slot: 0
> length: 12
> 2010-09-14 21:38:51 DEBUG: pid 6633: trying md5 authentication
> 2010-09-14 21:38:51 DEBUG: pid 6633: DB node id: 0 salt: d3da5234
> *2010-09-14 21:38:51 DEBUG: pid 6633: read_password_packet_password: failed
> to read password packet "p"
> 2010-09-14 21:38:51 ERROR: pid 6633: do_md5: read_password_packet failed
> *2010-09-14 21:38:51 DEBUG: pid 6633: do_md5failed in slot 0
> 2010-09-14 21:38:53 DEBUG: pid 6633: I am 6633 accept fd 6
> 2010-09-14 21:38:53 LOG:   pid 6633: connection received: host=127.0.0.1
> port=48338
> 2010-09-14 21:38:53 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database:
> test user: test
> 2010-09-14 21:38:53 DEBUG: pid 6633: new_connection: connecting 0 backend
> 2010-09-14 21:38:53 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support
> is not available
> 2010-09-14 21:38:53 DEBUG: pid 6633: pool_read_message_length: slot: 0
> length: 12
> 2010-09-14 21:38:53 DEBUG: pid 6633: trying md5 authentication
> 2010-09-14 21:38:53 DEBUG: pid 6633: DB node id: 0 salt: 855592aa
> 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler called
> 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: call wait3
> *2010-09-14 21:38:53 ERROR: pid 6603: Child process 6633 was terminated by
> segmentation fault
> 2010-09-14 21:38:53 DEBUG: pid 6603: child 6633 exits with status 11 by
> signal 11
> *2010-09-14 21:38:53 DEBUG: pid 6603: fork a new child pid 6671
> 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: normally exited
> 
> pg_hba.conf:
> local   all all trust
> hostall all 127.0.0.1/32trust
> hostall all ::1/128 trust
> hostall all 0.0.0.0/0   md5
> 
> pool_hba.conf:
> local   all all   trust
> host   all all 0.0.0.0/0
> md5
> 
> pgpool.conf:
> listen_addresses = '*'
> port = 
> pcp_port = 9898
> socket_dir = '/srv/pgpool/run'
> pcp_socket_dir = '/srv/pgpool/run'
> backend_socket_dir = '/srv/pgpool/run'
> pcp_timeout = 10
> num_init_children = 64
> max_pool = 4
> child_life_time = 300
> connection_life_time = 0
> child_max_connections = 0
> client_idle_limit = 0
> authentication_timeout = 60
> logdir = '/srv/pgpool/log'
> pid_file_name = '/srv/pgpool/run/pgpool.pid'
> replication_mode = false
> load_balance_mode = false
> replication_stop_on_mismatch = false
> failover_if_affected_tuples_mismatch = false
> replicate_select = false
> reset_query_list = 'ABORT; DISCARD ALL'
> white_function_list = ''
> black_function_list = 'nextval,setval'
> print_timestamp = true
> master_slave_mode = false
> master_slave_sub_mode = 'slony'
> delay_threshold = 0
> log_standby_delay = 'none'
> connection_cache = true
> health_check_timeout = 20
> health_check_period = 0
> health_che

Re: [GENERAL] Post Install / Secure PostgreSQL

[Craig Ringer]

On 15/09/2010 12:50 AM, Carlos Mennens wrote:

On Mon, Sep 13, 2010 at 9:58 PM, Craig Ringer
  wrote:

craig$ sudo -u postgres psql
postgres=>  CREATE USER craig WITH PASSWORD 'somepassword'
   CREATEDB CREATEROLE;
postgres=>  CREATE DATABASE craig WITH OWNER craig;
postgres=>  \q


So I set a Linux shell password on my newly auto created 'postgres'
system user


You can do that, though you don't need to. I usually just sudo to it.


which is what I use to login to the database as
'superuser'. Now I know my password for 'postgres' in the Linux shell
but I still don't understand what the database password is for
'postgres'.


You need to read the manual. It explains how authentication and login 
roles work. In particular, it explains pg_hba.conf and the "ident", 
"trust" and "md5" authentication modes.


http://www.postgresql.org/docs/current/interactive/client-authentication.html


So maybe I am still lost but it appears that the database user
'postgres' has a password unique to PostgreSQL, right?


Correct. However, it doesn't need to have any password at all; if you're 
using ident authentication, postgresql will accept a connection as 
"postgres" only from the local unix user "postgres". No need for a 
password, you've already convinced the OS you have the access rights.


If you're using "md5" (password) authentication, then you need to set a 
password for the postgres database user.


See the manual.


postgres=# SELECT * from pg_user;
  usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |
valuntil | useconfig
--+--+-+--+---+--+--+---
  postgres |   10 | t   | t| t |  |
  |
  carlos   |16384 | t   | t| t |  |
  |


You'd usually use the psql command:

   \du

for a better view. See:

   \?

in psql


Obviously there appears to be a specific password for both accounts
which I think are completely seperate from the Linux shell passwords,
right?


Correct.


Secondly I am unable to find any information in the docs that show me
how to set just the user password for 'carlos'. In MySQL I would use:


ALTER USER username SET PASSWORD 'somepassword';

See the manual, and the psql \h command

   \h   -- statement listing
   \h ALTER USER-- syntax of alter user

http://www.postgresql.org/docs/current/interactive/sql-alteruser.html

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

--
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] workaround steps for autovaccum problem

[David Fetter]

On Wed, Sep 15, 2010 at 05:30:51AM +0530, tamanna madaan wrote:
> I know upgrading postgres will resolve the problem permanently .
> But I wanted some workaround for now before I actually upgrade.

I want a pony, but I'm not getting one.  Upgrade PostgreSQL :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] workaround steps for autovaccum problem

[tamanna madaan]

I know upgrading postgres will resolve the problem permanently .
But I wanted some workaround for now before I actually upgrade.
But let me know if I really need to execute `vacuum freeze`
In the scenario given in my previous update  or I can skip this step.

For your reference I am again updating the scenario :

Autovacuum is getting invoked after every 5 mins and vacuums all the
database turn by turn and every database is getting its turn of
autovaccum after every 20 mins as there are 4 databases (template0 ,
template1, postgres and abc(my database) ).

 
Now suppose , this autovacuum problem occurs and through some script its
detected immediately at the very onset of the problem and below
mentioned workaround steps are executed

1. Stop postgres 
2. create 256K zero filled  0C01 file in /var/lib/pgsql/data/pg_clog
folder.
3. Start postgres

Then still , do I need to execute 'vacuum freeze' on all databases ??
Because, my databases were already getting autovacuumed properly before
the problem , the problem was resolved as soon as it occurred within let
say 2 mins and after the problem is resolved by workaround steps, then
autovacuum will   start vacuuming all the 4 databases  every 20 mins as
I mentioned above. 

Please confirm ...

-Original Message-
From: David Fetter [mailto:da...@fetter.org] 
Sent: Wednesday, September 15, 2010 3:27 AM
To: tamanna madaan
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] workaround steps for autovaccum problem

On Wed, Sep 15, 2010 at 02:39:26AM +0530, tamanna madaan wrote:
> Hi All
> 
>  
> 
> I am using postgres-8.1.2. I am getting the following error while
> autovacuum.

Please upgrade your software to PostgreSQL 8.1.21 and try again.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] workaround steps for autovaccum problem

[Bruce Momjian]

If you are not willing to do a minor upgrade, it is unlikely many people
are going to be willing to take the time to entertain your questions.

---

tamanna madaan wrote:
> I know upgrading postgres will resolve the problem permanently .
> But I wanted some workaround for now before I actually upgrade.
> But let me know if I really need to execute `vacuum freeze`
> In the scenario given in my previous update  or I can skip this step.
> 
> For your reference I am again updating the scenario :
> 
> Autovacuum is getting invoked after every 5 mins and vacuums all the
> database turn by turn and every database is getting its turn of
> autovaccum after every 20 mins as there are 4 databases (template0 ,
> template1, postgres and abc(my database) ).
> 
>  
> Now suppose , this autovacuum problem occurs and through some script its
> detected immediately at the very onset of the problem and below
> mentioned workaround steps are executed
> 
> 1. Stop postgres 
> 2. create 256K zero filled  0C01 file in /var/lib/pgsql/data/pg_clog
> folder.
> 3. Start postgres
> 
> Then still , do I need to execute 'vacuum freeze' on all databases ??
> Because, my databases were already getting autovacuumed properly before
> the problem , the problem was resolved as soon as it occurred within let
> say 2 mins and after the problem is resolved by workaround steps, then
> autovacuum will   start vacuuming all the 4 databases  every 20 mins as
> I mentioned above. 
> 
> Please confirm ...
> 
> -Original Message-
> From: David Fetter [mailto:da...@fetter.org] 
> Sent: Wednesday, September 15, 2010 3:27 AM
> To: tamanna madaan
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] workaround steps for autovaccum problem
> 
> On Wed, Sep 15, 2010 at 02:39:26AM +0530, tamanna madaan wrote:
> > Hi All
> > 
> >  
> > 
> > I am using postgres-8.1.2. I am getting the following error while
> > autovacuum.
> 
> Please upgrade your software to PostgreSQL 8.1.21 and try again.
> 
> Cheers,
> David.
> -- 
> David Fetter  http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter  XMPP: david.fet...@gmail.com
> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
> 
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] workaround steps for autovaccum problem

[Joshua D. Drake]

On Tue, 2010-09-14 at 17:01 -0700, David Fetter wrote:
> On Wed, Sep 15, 2010 at 05:30:51AM +0530, tamanna madaan wrote:
> > I know upgrading postgres will resolve the problem permanently .
> > But I wanted some workaround for now before I actually upgrade.
> 
> I want a pony, but I'm not getting one.  Upgrade PostgreSQL :)

/me pictures David on a pony.

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
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] Extracting data from BYTEA column to binary file using libpq

[Diego Schulz]

On Tue, Sep 14, 2010 at 6:01 PM, Julia Jacobson  wrote:
> Hello everybody out there using PostgreSQL,
>
> What is the problem with the following C++ code for the extraction of data
> from a BYTEA column to a binary file?
>
> #include 
> #include 
> #include 
> #include 
> #include "libpq-fe.h"
> using namespace std;
>
> main ()
> {
>  PGconn *conn;
>  conn = PQconnectdb("hostaddr='databaseserver.com' port='5432'
> dbname='test_db' user='test_user' password='secret'");
>  int size;
>  const char* contents;
>  PGresult* res;
>  res = PQexecParams(conn,
>  "SELECT filecontent FROM pictures WHERE picture_id='3'",
>  0, NULL,NULL,NULL,NULL,
>  1);
>
>  if (res && PQresultStatus(res)==PGRES_TUPLES_OK)
>  {
>    size = PQgetlength(res, 0, 0);
>    contents = PQgetvalue(res, 0, 0);
>  }
>  ofstream myFile ("picture.jpg", ios::out | ios::binary);
>  myFile.write (contents);
>  myFile.close();
> }
>
> Thanks in advance,
> Julia
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Hi,

In addition to what Daniel Verite said, I think you should use numeric
IP address instead of the host name, or consider replacing 'hostaddr'
with 'host' if you plan to use host names.
'hostaddr' is meant to be used when you want to avoid the name resolution step.

 conn = PQconnectdb("host='databaseserver.com' port='5432'
dbname='test_db' user='test_user' password='secret'");

regards,

diego

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Search then Delete Performance

[Michael Hull]

Hi Everyone,
I am fairly new to practical databases, but I am trying out the c
interface to postgres and am wondering how to improve performance. I
am a researcher, and I am trying to perform a large parameter sweep.
Since this will involve a couple of thousand simulations, I have a
process that manages which simulations have been done, and which still
need to be done, so I can run it easily on a cluster.

So, I have a fairly simple schema of 4 tables.

-- na, ca,ks,kf,lk,iinj are the parameters for my simulation.
CREATE TABLE alljobs (
id SERIAL,
ca int,
na int,
lk int,
ks int,
kf int,
iinj int,
PRIMARY KEY(id)
);


CREATE TABLE assignedjobs (
jobid int,
nodeid varchar(100),
assignedtime timestamp,
PRIMARY KEY(jobid)
);


CREATE TABLE completedjobs (
jobid int,
PRIMARY KEY(jobid)
);

CREATE TABLE unassignedjobs(
jobid int,
PRIMARY KEY(jobid)
);



alljobs is initially populated, and contains all the simulations that
will ever be run
unassignedjobs contains the ids in alljobs that havent been run yet
assignedjobs contains the ids in alljobs that have been dispatched to
some cpu on the cluster and are currently simulating
completedjobs contains all the completed jobs.

So fairly simply, I have a daemon running on a machine, which accesses
this DB. Clients connect and request the details for say 1000
simulations, at which point the daemon takes 1000 entries from the
unassigned table and moves them to the assigned table. The once the
client is finished with those jobs, it signals this to the daemon,
which then move those jobs from 'assigned' to 'complete'.

So this is fairly simple to implement, but my problem is that it is very slow.


In particular, I have a 'select' waiting for network connections,
which then calls this function:

typedef vector VectorLong;
VectorLong assignJobs(PGconn* pDB, int number, string nodename)
{
char buffer[1000];
sprintf(buffer,"SELECT jobid from unassignedjobs LIMIT  %d",number);
PGresult* pRes = PQexec(pDB, buffer);

printf("assigning jobs");

//PGresult* pRes = PQexec(pDB, "SELECT * from alljobs LIMIT 100");
//PGresult* pRes = PQexec(pDB, "SELECT * from alljobs");

int nRes = PQntuples(pRes);
printf("Results found: %d",nRes);

VectorLong jobs;
for(int i=0;ihttp://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] workaround steps for autovaccum problem

[Scott Marlowe]

On Tue, Sep 14, 2010 at 6:00 PM, tamanna madaan
 wrote:
> I know upgrading postgres will resolve the problem permanently .
> But I wanted some workaround for now before I actually upgrade.
> But let me know if I really need to execute `vacuum freeze`
> In the scenario given in my previous update  or I can skip this step.
>
> For your reference I am again updating the scenario :
>
> Autovacuum is getting invoked after every 5 mins and vacuums all the
> database turn by turn and every database is getting its turn of
> autovaccum after every 20 mins as there are 4 databases (template0 ,
> template1, postgres and abc(my database) ).
>
>
> Now suppose , this autovacuum problem occurs and through some script its
> detected immediately at the very onset of the problem and below
> mentioned workaround steps are executed
>
> 1. Stop postgres
> 2. create 256K zero filled  0C01 file in /var/lib/pgsql/data/pg_clog
> folder.
> 3. Start postgres
>
> Then still , do I need to execute 'vacuum freeze' on all databases ??

Vacuum freeze is primarily intended for template databases that never
get updated.  If you have to allow conn to template0 to copy it, then
yes maybe.

This whole exercise smacks of doing more work to avoid upgrading than
how much work the upgrade will be.

-- 
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] Search then Delete Performance

[Arjen Nienhuis]

Hi,

It's probably slow because you run many queries where a few would work:

DELETE FROM unassignedjobs WHERE jobid IN (6, 8 ,2, 99, 66)

But I wouldn't know how to build a query like that in C. A script in
python or even bash that dit it would be faster than your C
implementation.

What you can do in C is this:

sprintf(
   buffer,
  "INSERT INTO assignedjobs (jobid,nodeid)\n"
  "SELECT jobid, '%s' from unassignedjobs LIMIT %d\n",
  nodename.c_str(), number
);

Some smart SQL with some 'RETURNING' clauses could run the whole
assignJobs function in a single query and it would be a lot faster.

Met vriendelijke groet,
Arjen Nienhuis

On Wed, Sep 15, 2010 at 2:55 AM, Michael Hull  wrote:
> Hi Everyone,
> I am fairly new to practical databases, but I am trying out the c
> interface to postgres and am wondering how to improve performance. I
> am a researcher, and I am trying to perform a large parameter sweep.
> Since this will involve a couple of thousand simulations, I have a
> process that manages which simulations have been done, and which still
> need to be done, so I can run it easily on a cluster.
>
> So, I have a fairly simple schema of 4 tables.
>
> -- na, ca,ks,kf,lk,iinj are the parameters for my simulation.
> CREATE TABLE alljobs (
>        id SERIAL,
>        ca int,
>        na int,
>        lk int,
>        ks int,
>        kf int,
>        iinj int,
>        PRIMARY KEY(id)
> );
>
>
> CREATE TABLE assignedjobs (
>        jobid int,
>        nodeid varchar(100),
>        assignedtime timestamp,
>        PRIMARY KEY(jobid)
> );
>
>
> CREATE TABLE completedjobs (
>        jobid int,
>        PRIMARY KEY(jobid)
> );
>
> CREATE TABLE unassignedjobs(
>        jobid int,
>        PRIMARY KEY(jobid)
> );
>
>
>
> alljobs is initially populated, and contains all the simulations that
> will ever be run
> unassignedjobs contains the ids in alljobs that havent been run yet
> assignedjobs contains the ids in alljobs that have been dispatched to
> some cpu on the cluster and are currently simulating
> completedjobs contains all the completed jobs.
>
> So fairly simply, I have a daemon running on a machine, which accesses
> this DB. Clients connect and request the details for say 1000
> simulations, at which point the daemon takes 1000 entries from the
> unassigned table and moves them to the assigned table. The once the
> client is finished with those jobs, it signals this to the daemon,
> which then move those jobs from 'assigned' to 'complete'.
>
> So this is fairly simple to implement, but my problem is that it is very slow.
>
>
> In particular, I have a 'select' waiting for network connections,
> which then calls this function:
>
> typedef vector VectorLong;
> VectorLong assignJobs(PGconn* pDB, int number, string nodename)
> {
>        char buffer[1000];
>        sprintf(buffer,"SELECT jobid from unassignedjobs LIMIT  %d",number);
>        PGresult* pRes = PQexec(pDB, buffer);
>
>        printf("assigning jobs");
>
>        //PGresult* pRes = PQexec(pDB, "SELECT * from alljobs LIMIT 100");
>        //PGresult* pRes = PQexec(pDB, "SELECT * from alljobs");
>
>        int nRes = PQntuples(pRes);
>        printf("Results found: %d",nRes);
>
>        VectorLong jobs;
>        for(int i=0;i        {
>                long id = atol( PQgetvalue(pRes,i,0) );
>                cout << id << " ";
>                jobs.push_back(id);
>
>                sprintf(buffer, "DELETE FROM unassignedjobs WHERE jobid = 
> %ld", id);
>                PQexec(pDB, buffer);
>
>                sprintf(buffer, "INSERT INTO assignedjobs (jobid,nodeid) VALUES
> (%ld, %s)", id, nodename.c_str() );
>                PQexec(pDB, buffer);
>        }
>
>
>        return jobs;
> }
>
> but it is painfully slow. I was wondering if there is a way to improve
> this? I feel there should be since I already have a 'pointer' to the
> rows I want to delete.
>
>
> Any help would be greatly appreciated.
>
> Many thanks
>
>
> Mike Hull
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] Search then Delete Performance

[Tom Lane]

Michael Hull  writes:
> I am fairly new to practical databases, but I am trying out the c
> interface to postgres and am wondering how to improve performance. I
> am a researcher, and I am trying to perform a large parameter sweep.
> Since this will involve a couple of thousand simulations, I have a
> process that manages which simulations have been done, and which still
> need to be done, so I can run it easily on a cluster.

There's basically no way for the performance of that loop to not suck.
You're incurring two round trips to the server per jobid change,
plus query parse/plan times; so even though the underlying table
manipulations are relatively simple, there's just too much overhead.

One way to fix this problem is to push the procedural logic into a
stored procedure that runs on the server, so that your app sends
one query, gets back one rowset of its newly assigned jobs, and
all the database update side-effects are done by the procedure.
That's only a small conceptual leap from where you are, but does require
getting up to speed on plpgsql or one of the other procedural languages.

The more radical way to fix it is to write the whole thing as one
SQL command.  This requires thinking about your problem as an operation
on a set of rows, rather than an iteration, so it can be a pretty big
conceptual jump for database novices.  I'm not sure it's actually
possible to do it given the specific table organization you've adopted
--- if you need to both delete rows in unassignedjobs and insert rows
in assignedjobs, there's no way to do it in one SQL operation.  But if
you're not yet wedded to that representation, you should consider having
just one table and implementing the state change as an update to a
status column instead of moving the data to a different table.  Then you
could probably implement the operation in a single UPDATE ... RETURNING
command.

Also, this looks suspiciously like a priority queue, which means you're
basically reinventing the wheel.  People have sweated the details on
this type of thing before, and come up with very clever solutions that
perform well even with multiple clients concurrently trying to obtain
job assignments.  Simple solutions tend to slow way down or even
deadlock under those conditions :-(.  Check the Postgres list archives.

regards, tom lane

-- 
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] Search then Delete Performance

[John R Pierce]

 On 09/14/10 5:55 PM, Michael Hull wrote:

So fairly simply, I have a daemon running on a machine, which accesses
this DB. Clients connect and request the details for say 1000
simulations, at which point the daemon takes 1000 entries from the
unassigned table and moves them to the assigned table. The once the
client is finished with those jobs, it signals this to the daemon,
which then move those jobs from 'assigned' to 'complete'.

So this is fairly simple to implement, but my problem is that it is very slow.




instead of moving data from one table to another, it might be better to 
just have a table of simulations, then another table which just contains 
the PK of each simulation, and a flag that says its assigned or 
unassigned (and maybe the client its assigned to?  and anything else 
thats related to this assignment?)...   so instead of moving your big 
table rows, which involves deleting them from one table and inserting 
them into another, you just update the row of this small table.   if you 
create this small table with a fillfactor like 75%, the updates likely 
will easily be handled by HOT




--
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] Search then Delete Performance

[Dann Corbit]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of John R Pierce
> Sent: Tuesday, September 14, 2010 8:41 PM
> To: Michael Hull
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Search then Delete Performance
> 
>   On 09/14/10 5:55 PM, Michael Hull wrote:
> > So fairly simply, I have a daemon running on a machine, which
> accesses
> > this DB. Clients connect and request the details for say 1000
> > simulations, at which point the daemon takes 1000 entries from the
> > unassigned table and moves them to the assigned table. The once the
> > client is finished with those jobs, it signals this to the daemon,
> > which then move those jobs from 'assigned' to 'complete'.
> >
> > So this is fairly simple to implement, but my problem is that it is
> very slow.
> >
> >
> 
> instead of moving data from one table to another, it might be better to
> just have a table of simulations, then another table which just
> contains
> the PK of each simulation, and a flag that says its assigned or
> unassigned (and maybe the client its assigned to?  and anything else
> thats related to this assignment?)...   so instead of moving your big
> table rows, which involves deleting them from one table and inserting
> them into another, you just update the row of this small table.   if
> you
> create this small table with a fillfactor like 75%, the updates likely
> will easily be handled by HOT

Or just a status integer in the main table along the lines of:
1 = unassigned
2 = assigned
3 = running
4 = completed
Etc.

And then update the status as appropriate and check the status as needed.

If you want until a batch is done, you would also be able to update like this:

UPDATE jobs SET status = 4 WHERE status = 3

As you like, with a single statement.

There are lots of job schedulers on SOURCEFORGE.
http://sourceforge.net/search/?words=scheduler+workflow&type_of_search=soft&sort=latest_file_date&sortdir=desc&limit=100


-- 
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] select sql slow inside function

[Sergey Konoplev]

Hi,

On 15 September 2010 01:56, Gary Fu  wrote:
> I have a function proc_TaskComplete that inserts a record to table
> TaskHistory and then calls another function proc_ExportTaskComplete, that
> will retrieve (select) the record just inserted based on an index column
> (TaskId) in that table TaskHistory.  I noticed that the select sql (inside
> proc_ExportTaskComplete) will take 3 seconds.  Under normal condition (psql)
> the select sql is fast enough with the index. Can anyone explain why and how
> to fix the problem ?
> My postgresql version is 8.4.4

Could you please provide a use-case?


>
> Thanks,
> Gary
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general