Re: [GENERAL] How many Mandatory Process are there in Postgres

Hi All, Thank you for the prompt reply on this. Please find the output of the top command and the process availabe. Could explain what logger process is here for top - 12:41:57 up 17:51, 3 users, load average: 0.00, 0.04, 0.01 Tasks: 141 total, 1 running, 139 sleeping, 0 stopped, 1 zombi

[GENERAL] Why index occupy less amount of space than the table with same structure.

Hi All, Here is small testing done by my end and am curious to know the reason. Please find the example given below:- postgres=# create table size_test(id int); CREATE TABLE postgres=# insert into size_test VALUES (generate_series(1,100)); INSERT 0 100 postgres=# select pg_size_pretty(pg_

[GENERAL] Designing Postgres Security Model

Hi, Could anyone please suggest me how to deal with my following requirements. So far, I have done following to meet my requirements: * I want users to use only stored procedures to create, alter, delete tables in mydb schema*. ==> For this, I have *revoked all permissions from mydb *schema, and s

Re: [GENERAL] Why index occupy less amount of space than the table with same structure.

On 28 Mar 2010, at 10:05, Tadipathri Raghu wrote: > Hi All, ... > I like to know here is, I have created a table with one column and the index > is on one column only, so why is the space occupied differently, almost all > half of the space of the table and why not full. Could please you expl

Re: [GENERAL] simultaneously reducing both memory usage and runtime for a query

Faheem Mitha writes: > ... In any case, feedback would be helpful. Details of my attempts > at optimization are at > http://bulldog.duhs.duke.edu/~faheem/snppy/opt.pdf By and large, this is not the way to ask for help on the Postgres lists. If you're supplying extremely large test data or someth

Re: [GENERAL] How many Mandatory Process are there in Postgres

On Sun, Mar 28, 2010 at 1:14 AM, Tadipathri Raghu wrote: > Hi All, > > Thank you for the prompt reply on this. > > Please find the output of the top command and the process availabe. Could > explain what logger process is here for Logging? I'm just guessing there. My machines don't have it and

[GENERAL] Splitting text column to multiple rows

TEXT column contains multi-line text. How to split it to multiple rows so that every line is in separate row ? Code below should return two rows, Line 1 Line 2 Solution should work starting at 8.1 Should generate_series or pgsql procedure used or any other idea? Andrus. create temp table tes

[GENERAL] How to perform text merge

Database column contains merge data in text column. Expressions are between << and >> separators. How to replace them with database values ? For example, code below should return: Hello Tom Lane! How to implement textmerge procedure or other idea ? Andrus. create temp table person ( firstname

Re: [GENERAL] Splitting text column to multiple rows

Hello try: CREATE OR REPLACE FUNCTION unnest(anyarray) RETURNS SETOF anyelement as $$ SELECT $1[i] FROM generate_series(1,4) g(i) $$ LANGUAGE sql; pa...@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',',')); unnest 23 2 3 4 (4 rows) regards Pavel Stehule 2010/3/28

Re: [GENERAL] How to perform text merge

On 28 Mar 2010, at 19:43, Andrus wrote: > Database column contains merge data in text column. > Expressions are between << and >> separators. > How to replace them with database values ? > > For example, code below should return: > > Hello Tom Lane! > > How to implement textmerge procedure or o

[GENERAL] How to generate a valid postgre TIMESTAMP with PHP?

Hi, I'am writing some code in PHP and I need to generate a valid postgresql TIMESTAMP with PHP. Any PHP programmer thar can help me on how to generate valid TIMESTAMP's with PHP? Sorry my bad english. Best Regards,

[GENERAL] optimizing import of large CSV file into partitioned table?

After careful research, I would to post the following problem I'm having with the importing of a large (16Gb) CSV file. Here is brief synopsis: - this is running on Postgres (PG) version: PostgreSQL 8.3.9 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.2-1ubuntu11) 4.3.2 - it is runn

Re: [GENERAL] How to generate a valid postgre TIMESTAMP with PHP?

On Sun, Mar 28, 2010 at 12:27 PM, Andre Lopes wrote: > Hi, > > I'am writing some code in PHP and I need to generate a valid postgresql > TIMESTAMP with PHP. > > Any PHP programmer thar can help me on how to generate valid TIMESTAMP's > with PHP? Just stick with something simple like: 2010-03-28

Re: [GENERAL] optimizing import of large CSV file into partitioned table?

On 28 March 2010 18:33, Rick Casey wrote: > After careful research, I would to post the following problem I'm having > with the importing of a large (16Gb) CSV file. Here is brief synopsis: > - this is running on Postgres (PG) version: PostgreSQL 8.3.9 on > i486-pc-linux-gnu, compiled by GCC gcc-

Re: [GENERAL] simultaneously reducing both memory usage and runtime for a query

On Sun, 28 Mar 2010, Tom Lane wrote: Faheem Mitha writes: ... In any case, feedback would be helpful. Details of my attempts at optimization are at http://bulldog.duhs.duke.edu/~faheem/snppy/opt.pdf By and large, this is not the way to ask for help on the Postgres lists. If you're supplyin

Re: [GENERAL] How to perform text merge

Since you pretty much invented your own language Expressions are in PostgreSql syntax. I expected that there is some way to force PostgreSql to evaluate them at runtime using something like pgsql EXECUTE For example, 'Hello <>!' should be converted (inverted) to 'Hello ' || firstname||' '||l

Re: [GENERAL] Splitting text column to multiple rows

CREATE OR REPLACE FUNCTION unnest(anyarray) RETURNS SETOF anyelement as $$ SELECT $1[i] FROM generate_series(1,4) g(i) $$ LANGUAGE sql; pa...@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',',')); I tried code below. Order of rows in result is different from the order of elements i

Re: [GENERAL] Achieving ordered update

On Fri, Mar 26, 2010 at 1:02 AM, Allan Kamau wrote: > A classic problem. I would like to assign integer values (from a > sequence) to records in a table based on the order (of contents) of > other field(s) in the same table. Do you have a simple example? (create table, insert some data, then sho

Re: [GENERAL] How many Mandatory Process are there in Postgres

Le 28/03/2010 19:30, Scott Marlowe a écrit : > On Sun, Mar 28, 2010 at 1:14 AM, Tadipathri Raghu > wrote: >> Hi All, >> >> Thank you for the prompt reply on this. >> >> Please find the output of the top command and the process availabe. Could >> explain what logger process is here for > > Loggin

Re: [GENERAL] How to generate a valid postgre TIMESTAMP with PHP?

On Sun, Mar 28, 2010 at 2:27 PM, Andre Lopes wrote: > Hi, > > I'am writing some code in PHP and I need to generate a valid postgresql > TIMESTAMP with PHP. > > Any PHP programmer thar can help me on how to generate valid TIMESTAMP's > with PHP? > > Sorry my bad english. > > > Best Regards, > In p

Re: [GENERAL] Connection Pooling

On 3/27/2010 12:46 AM, John R Pierce wrote: Allan Kamau wrote: You may also have a look at Commons DBCP from Apache software foundation, "http://commons.apache.org/dbcp/";. I have used it for a few projects and have had no problems. for that matter, JDBC has its own connection pooling in java.

[GENERAL] hstore equality-index performance question

Documentation at "F.13.3. Indexes" says that "hstore has index support for @> and ? operators..." => Therefore no index does support equality-indexes? If so, then I suppose that following (potentially slow) query which contains an equality test for all keys 'a' and returns all values... SELECT

Re: [GENERAL] optimizing import of large CSV file into partitioned table?

2010/3/28 Thom Brown : > The problem here is that you appear to require an index update, trigger > firing and constraint check for every single row.  First thing I'd suggest > is remove the indexes.  Apply that after your import, otherwise it'll have > to update the index for every single entry. +

Re: [GENERAL] Using readline for frequently used queries

I wrote: > depending on the database, I use some "dashboard queries" > rather frequently. To ease executing them, I've put: > | $include /etc/inputrc > | $if psql > | "\e[24~": "\fSELECT * FROM DashboardQuery;\n" > | $endif > in my ~/.inputrc ("\e[24~" is [F12]). > Obviously, this only works

Re: [GENERAL] warm standby possible with 8.1?

On Tue, Mar 09, 2010 at 05:48:41PM -0500, Greg Smith wrote: > zhong ming wu wrote: > >Is it possible to have a warm standby with 8.1? > > No. You can set that up so that it replays an entire pile of log > files sitting there when you start the server, which it sounds like > you haven't managed ye

Re: [GENERAL] simultaneously reducing both memory usage and runtime for a query

On 03/28/2010 03:05 PM, Faheem Mitha wrote: On Sun, 28 Mar 2010, Tom Lane wrote: Faheem Mitha writes: ... In any case, feedback would be helpful. Details of my attempts at optimization are at http://bulldog.duhs.duke.edu/~faheem/snppy/opt.pdf By and large, this is not the way to ask for h

Re: [GENERAL] Warm Standby Setup Documentation

On Fri, Mar 26, 2010 at 01:35:43PM -0500, Ogden wrote: > On Mar 26, 2010, at 1:32 PM, Greg Smith wrote: > > > Bryan Murphy wrote: > >> The one thing you should be aware of is that when you fail over, your > >> spare has no spares. I have not found a way around this problem yet. So, > >> when y

Re: [GENERAL] optimizing import of large CSV file into partitioned table?

hi, i would recommend to convert the input using some scripts into pg_dump format and use small temporary tables without indexes to import into - and after all data in the db you could partition it... you can pre-partition your data using simple grep, this way you can import the data directly

Re: [GENERAL] simultaneously reducing both memory usage and runtime for a query

On 03/28/2010 07:43 PM, Andy Colson wrote: On 03/28/2010 03:05 PM, Faheem Mitha wrote: Wait... Your saying your question is so complex it needs 41 pages (including graphs) to ask? I didn't bother before, but now I'm curious, I'll have to go take a look. -Andy Faheem, you seem to be incred

Re: [GENERAL] Why index occupy less amount of space than the table with same structure.

Hi Alban, Thank you for the update. > For one thing: The table holds information regarding to which transactions > each row is visible (the xid) whereas the index does not. What would be the each value of the xid, like 8 bytes,or 32 bytes..which causing the table to hold what index is not and

Re: [GENERAL] Moving data directory from one server to another

Hi Yar,    Thanks for the reply. I gave it a try, but found that there was a slight problem as the directory path of PostGres on the two servers were different. Thats why my test failed. If you have any ideas about how to overcome this (which configuration files are required to be changed). Plea

Re: [GENERAL] Splitting text column to multiple rows

2010/3/28 Andrus : >> CREATE OR REPLACE FUNCTION unnest(anyarray) >> RETURNS SETOF anyelement as $$ >>  SELECT $1[i] FROM generate_series(1,4) g(i) >> $$ LANGUAGE sql; >> >> pa...@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',',')); > > I tried code below. Order of rows in result is di

[GENERAL] Simultaneous write requests

How does Postgres handle concurrent write requests? Cheers Vitali Xevet

Re: [GENERAL] Warm Standby Setup Documentation

Ogden wrote: How is it possible to use the archive_command to ship to different ones? archive_command = 'rsync -a %p postg...@192.168.x.x:/usr/local/pgsql/walfiles/%f The examples in the manual lead one toward putting a full command line into the script. I personally never do that; I cal

Re: [GENERAL] Warm Standby Setup Documentation

Bryan Murphy wrote: On Fri, Mar 26, 2010 at 1:32 PM, Greg Smith > wrote: If there's another server around, you can have your archive_command on the master ship to two systems, then use the second one as a way to jump-start this whole process. After f

Re: [GENERAL] Simultaneous write requests

Vitali Xevet wrote: How does Postgres handle concurrent write requests? http://www.postgresql.org/docs/current/static/transaction-iso.html describes how they're isolated and can potentially interact with one another. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services

Re: [GENERAL] simultaneously reducing both memory usage and runtime for a query

On Sun, 28 Mar 2010, Andy Colson wrote: Wait... Your saying your question is so complex it needs 41 pages (including graphs) to ask? I didn't bother before, but now I'm curious, I'll have to go take a look. No, only the 25 page document (which has the graphs) is directly related to the qu

Re: [GENERAL] simultaneously reducing both memory usage and runtime for a query

Hi again Andy, On Sun, 28 Mar 2010 20:59:24 -0500, Andy Colson wrote: > On 03/28/2010 07:43 PM, Andy Colson wrote: >> On 03/28/2010 03:05 PM, Faheem Mitha wrote: >>> >>> >> >> Wait... Your saying your question is so complex it needs 41 pages >> (including graphs) to ask? I didn't bother before, b