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

[Tadipathri Raghu]

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 zombie
Cpu(s):  0.3%us,  1.4%sy,  0.0%ni, 98.3%id,  0.0%wa,  0.0%hi,  0.0%si,
0.0%st
Mem:   1417256k total,  1300888k used,   116368k free,41468k buffers
Swap:  2097144k total,22156k used,  2074988k free,  1017980k cached
  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
23466 postgres  15   0  4664 1508 1228 S  0.0  0.1   0:00.10 -bash
25983 postgres  15   0 44620 3032 2680 S  0.0  0.2   0:00.43
/usr/local/pgsql/bin/postgres
25984 postgres  18   0  8608  632  296 S  0.0  0.0   0:00.04 postgres:
logger process
25986 postgres  15   0 44604 1656 1292 S  0.0  0.1   0:00.10 postgres:
writer process
25987 postgres  15   0 44604  932  588 S  0.0  0.1   0:00.11 postgres: wal
writer process
25988 postgres  15   0 44756 1232  680 S  0.0  0.1   0:00.17 postgres:
autovacuum launcher process
25989 postgres  18   0  8604  648  304 S  0.0  0.0   0:00.04 postgres:
archiver process
25990 postgres  15   0  8740  900  408 S  0.0  0.1   0:00.31 postgres: stats
collector process
25993 postgres  15   0  4964 1464 1196 S  0.0  0.1   0:00.17 psql
25994 postgres  15   0 46264 6212 4672 S  0.0  0.4   0:00.23 postgres:
postgres postgres [local] idle

Regards
Raghavendra

On Fri, Mar 26, 2010 at 9:22 AM, Scott Marlowe wrote:

>  On Thu, Mar 25, 2010 at 9:03 PM, Tadipathri Raghu 
> wrote:
> > Hi Scott, Thomas,
> >
> > Thank you for the update.
> >
> >>
> >> >> Oracle uses a completely different implementation of MVCC
> architecture.
> >> >> It
> >> >> overwrites the data and then uses rollback segments to provide
> >> >> 'previous
> >> >> versions' to running transactions etc.
> >> >>
> >> >> PostgreSQL does not overwrite the data - it just creates a copy of
> the
> >> >> row
> >> >> and then decides which version should each session see (depending on
> >> >> the
> >> >> transaction IDs etc.). So it does not need to do rollbacks the way
> >> >> Oracle
> >> >> does, but it has to remove stale copies of the rows (such that no
> >> >> running
> >> >> transaction can see) - that's why there is VACUUM.
> >> >
> >> >
> >> > Here, if you have issued a command pg_start_backup() at that time the
> >> > cluster is freezed, and if any transaction takes place before the
> >> > pg_stop_backup() issued at that time where the transaction data will
> be
> >> > kept
> >> > if the undo's are not there.
> >>
> >>
> >> What do you mean by 'freezed'? The cluster operates normally, the
> >> pg_start_backup() just creates a backup label (and performs a
> checkpoint),
> >> but that's not a problem. OK, there could be a performance decrease
> >> because of full page writes, but the data will be processed as if there
> is
> >> no backup running. PostgreSQL does not need the checkpoints to perform
> >> backup.
> >
> >> The 'UNDO' data is just kept in the main data files.  Then, based on
> what
> >> your xid is, and the xmin / xmax on each row, it's either visible or
> not.
> >>  That's what vacuum does, clean up the rows that are for 'undo' and can
> >> never been seen anymore.  The REDO itself is kept in the transaction
> logs.
> >
> >
> >>
> >> Like Thomas says, the pg_start_backup() just creates a label that tells
> >> the restoring database what pg_xlog record to start with when you
> 'restore'
> >> your data.  The 'UNDO' type of data (just the old rows actually) is just
> >> kept in the main table until vacuum nukes 'em.
> >
> > --Scott
> > Q1. Does vacuum process come into existence even you turn off the
> > autovacuum.? What is the main work for vacuum process, to the clean the
> > buffers or work only when autovacuum in on and to clean up the rows that
> are
> > 'undo'
>
> 1a: Yes, if you are approaching transaction id wraparound (txid wrap)
> then autovacuum will crank up a vacuum to fix that situation whether
> you like it or not.  The alternative is db shutdown.
> 1b: The main job of vacuum is to free dead tuples, which postgresql
> accumulates as it updates or deletes tuples. When you have several old
> versions of a tuple to collect, vacuum recycles the space and makes it
> available to other updates to now use instead of allocating on the end
> of the relation.
>
> > Q2. Do you mean to say there is a vacuum process which will clean up the
> > buffers that are for 'undo'?
>
> No buffers for undo.  It's all stored on disc, ready for instant
> access.  Try it on a test db.  begin; load a table with 1M rows;
> rollback; sub second response.  A million changes just disappeared
> instantly.  And now you've got 1M dead tuples in that table.  Vacuum's
> job is to make them available for re-use.  But they're all in one big
> consecutive file so the performance is actually pretty good as the
> table repopulates, especially if it'll get back 

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

[Tadipathri Raghu]

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_relation_size('size_test'));
 pg_size_pretty

 31 MB
(1 row)
postgres=# create index isize_test on size_test(id);
CREATE INDEX
postgres=# select pg_size_pretty(pg_relation_size('isize_test'));
 pg_size_pretty

 17 MB
(1 row)
postgres=# select pg_size_pretty(pg_total_relation_size('size_test'));
 pg_size_pretty

 48 MB
(1 row)
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 explain on
this. And what exactly the postgres architecture treat on Index table and
Ordinary table.

Thanks all in Advance

Regards
Raghavendra

[GENERAL] Designing Postgres Security Model

[dipti shah]

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 stored procedures are defined with SECURITY DEFINER in postgres user
context. I have given execute permission to set of users on these stored
procedures to achieve my goal.

*I want only privileged users to create table with foreign key
references.*==> This can be achieved using SET ROLE current user
before executing create
command but *Postgresql doesn't allow running SET ROLE in SECURITY DEFINER
function* *context* so I have created a my_sudo function which gets invoked
from my stored procedure. This sudo function creates a temporary SECURITY
DEFINER function and changes *owner to the current user before executing
create table command.
*
Now, as sudo function runs actual create command as current user context and
he/she does not have permission on mydb schema(as revoked all permission to
meet first requirement), I have to grant the ALL permissions on mydb schema
to current user temporary and then restore his/her actual privileges back to
make sure that users actual permission doesn't change.

*Problem:* Could anyone tell me how to get the schema permissions list for
current user and restore it back once store procedure execution completed.

Please feel free to let me know if you have any questions.

Thanks a lot,
Dipti

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

[Alban Hertroys]

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 explain on 
> this. And what exactly the postgres architecture treat on Index table and 
> Ordinary table.

For one thing: The table holds information regarding to which transactions each 
row is visible (the xid) whereas the index does not.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4baf3c0010411382482531!



-- 
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] simultaneously reducing both memory usage and runtime for a query

[Tom Lane]

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 something, it's fair to
provide a link instead of putting the information in-line, but otherwise
you should try to make your email self-contained.  Those of us who are
willing to help are not here just to help you --- we'd like other people
to learn from it too, both at the time and later from the archived
discussion.  So the information needs to be in the email thread, not
only on some transient web page.

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] How many Mandatory Process are there in Postgres

[Scott Marlowe]

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 I'm
guessing it's the process that starts up if you're using syslog as
opposed to local logging.

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

[GENERAL] Splitting text column to multiple rows

[Andrus]

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 test ( test text ) on commit drop;
insert into test values( 'Line 1' ||chr(13)||'Line2');

create temp table test2 ( test text ) on commit drop;
-- todo: split test to multiple rows
insert into test2 select * from test;
select * from test2;

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

[GENERAL] How to perform text merge

[Andrus]

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 text, lastname text ) on commit drop;
insert into person values ('Tom', 'Lane');
create temp table mergedata ( template text ) on commit drop;
insert into mergedata values ('Hello <>!');

select textmerge(template,'select * from person') from mergedata;


--
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] Splitting text column to multiple rows

[Pavel Stehule]

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 Andrus :
> 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 test ( test text ) on commit drop;
> insert into test values( 'Line 1' ||chr(13)||'Line2');
>
> create temp table test2 ( test text ) on commit drop;
> -- todo: split test to multiple rows
> insert into test2 select * from test;
> select * from test2;
>
> --
> 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] How to perform text merge

[Alban Hertroys]

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 other idea ?
> 
> Andrus.
> 
> create temp table person ( firstname text, lastname text ) on commit drop;
> insert into person values ('Tom', 'Lane');
> create temp table mergedata ( template text ) on commit drop;
> insert into mergedata values ('Hello <>!');
> 
> select textmerge(template,'select * from person') from mergedata;


Since you pretty much invented your own language you're probably best suited 
with writing your own parser. Have a look at flex/yacc or whatever it's 
equivalent is on Windows if that has your preference.

If you "dumb it down" a bit by replacing the expressions by simple tokens then 
you could handle this with regular expressions, for example:

insert into mergedata values ('Hello <> <>!');

You replace <> and <> with their respective values using 
regexp_replace. You'll need to nest a few calls to that function to get the 
result you want.
You could be a bit smarter about this and create a <> macro that you 
fill from a function result that uses (firstname, lastname) as input parameters 
and returns firstname || ' ' || lastname. Views are useful for providing such 
data too.

Personally I think you're using a bad example here, as usually names don't just 
involve firstname and surname, but frequently have infixes, suffixes and titles 
and such. Not all of those fields are going to have values for every person in 
your database. What happens if you don't have a Tom Lane, but a mr. Lane, or if 
you have both but want to address a person more politely?

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4baf9e7810416492686854!



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

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

[Andre Lopes]

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?

[Rick Casey]

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 running on a Ubuntu (small) server instance at Amazon Web Services
(AWS), with a 320Gb volume mounted for the PG data directory
- the database was created using the partition example in the documentation,
with an insert trigger and a function to direct which table where records
get inserted.
(see below for code on my table and trigger creation)

After some days of attempting to import the full 16Gb CSV file, I decided to
split the thing up, using the split utility in Linux. This seemed to improve
things; once I had split the CSV files into about 10Mb size files, I finally
got my first successful import of about 257,000 recs. However, this is going
to be a rather labor intensive process to import the full 16Gb file, if I
have to manually split it up, and import each smaller file separately.

So, I am wondering if there is any to optimize this process? I have been
using Postgres for several years, but have never had to partition or
optimize it for files of this size until now.
Any comments or suggestions would be most welcomed from this excellent
forum.

(I might add that I spend several weeks prior to this trying to get this to
work in MySQL, which I finally had to abandon.)

Sincerely,
Rick

Details of the code follow:

Here is the basic COPY command, which I run as the postgres user, to import
the CSV files:

COPY allcalls FROM '/data/allcalls-csv/sub3ab' WITH CSV;

Here is what some sample data look like in the files:
3153371867,2008-02-04 16:11:00,1009,1,40
2125673062,2008-02-04 16:11:00,1009,1,41
5183562377,2008-02-04 16:11:00,1009,1,50
...

Here are the basic scripts that created the partition table and insert
trigger:
CREATE TABLE allcalls (
phonenum bigint,
callstarted timestamp without time zone,
status int,
attempts int,
duration int
);
CREATE TABLE allcalls_0 (
CHECK ( phonenum < 10 )
) INHERITS (allcalls);
...(repeat this 9 more times, for 10 subpartition tables)

CREATE INDEX allcalls_0_phonenum ON allcalls_0 (phonenum);
..(repeat this 9 more times, for indexes on the 10 subpartition tables)
CREATE OR REPLACE FUNCTION allcalls_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.phonenum  < 10 ) THEN
INSERT INTO allcalls_0 VALUES (NEW.*);
ELSIF ( NEW.phonenum >= 10 AND NEW.phonenum < 20 ) THEN
INSERT INTO allcalls_1 VALUES (NEW.*);
...(again, repeat for rest of the parition tables)

CREATE TRIGGER insert_phonenum_trigger
BEFORE INSERT ON allcalls
FOR EACH ROW EXECUTE PROCEDURE allcalls_insert_trigger();

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

[Scott Marlowe]

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 13:45:30

which will always work for an insert

-- 
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] optimizing import of large CSV file into partitioned table?

[Thom Brown]

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-4.3.real (Ubuntu 4.3.2-1ubuntu11)
> 4.3.2
> - it is running on a Ubuntu (small) server instance at Amazon Web Services
> (AWS), with a 320Gb volume mounted for the PG data directory
> - the database was created using the partition example in the
> documentation, with an insert trigger and a function to direct which table
> where records get inserted.
> (see below for code on my table and trigger creation)
>
> After some days of attempting to import the full 16Gb CSV file, I decided
> to split the thing up, using the split utility in Linux. This seemed to
> improve things; once I had split the CSV files into about 10Mb size files, I
> finally got my first successful import of about 257,000 recs. However, this
> is going to be a rather labor intensive process to import the full 16Gb
> file, if I have to manually split it up, and import each smaller file
> separately.
>
> So, I am wondering if there is any to optimize this process? I have been
> using Postgres for several years, but have never had to partition or
> optimize it for files of this size until now.
> Any comments or suggestions would be most welcomed from this excellent
> forum.
>
> (I might add that I spend several weeks prior to this trying to get this to
> work in MySQL, which I finally had to abandon.)
>
> Sincerely,
> Rick
>
> Details of the code follow:
>
> Here is the basic COPY command, which I run as the postgres user, to import
> the CSV files:
> 
> COPY allcalls FROM '/data/allcalls-csv/sub3ab' WITH CSV;
>
> Here is what some sample data look like in the files:
> 3153371867,2008-02-04 16:11:00,1009,1,40
> 2125673062,2008-02-04 16:11:00,1009,1,41
> 5183562377,2008-02-04 16:11:00,1009,1,50
> ...
>
> Here are the basic scripts that created the partition table and insert
> trigger:
> CREATE TABLE allcalls (
> phonenum bigint,
>  callstarted timestamp without time zone,
> status int,
> attempts int,
>  duration int
> );
> CREATE TABLE allcalls_0 (
> CHECK ( phonenum < 10 )
> ) INHERITS (allcalls);
> ...(repeat this 9 more times, for 10 subpartition tables)
>
> CREATE INDEX allcalls_0_phonenum ON allcalls_0 (phonenum);
> ..(repeat this 9 more times, for indexes on the 10 subpartition tables)
> CREATE OR REPLACE FUNCTION allcalls_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
> IF ( NEW.phonenum  < 10 ) THEN
> INSERT INTO allcalls_0 VALUES (NEW.*);
> ELSIF ( NEW.phonenum >= 10 AND NEW.phonenum < 20 ) THEN
> INSERT INTO allcalls_1 VALUES (NEW.*);
> ...(again, repeat for rest of the parition tables)
>
> CREATE TRIGGER insert_phonenum_trigger
> BEFORE INSERT ON allcalls
> FOR EACH ROW EXECUTE PROCEDURE allcalls_insert_trigger();
>
> 
>

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.  And the trigger won't help
either.  Import into a single table and split it out into further tables
after if required.  And finally the constraint should probably be applied
after too, so cull any violating rows after importing.

Thom

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

[Faheem Mitha]

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 supplying extremely large test data or something, it's fair to
provide a link instead of putting the information in-line, but otherwise
you should try to make your email self-contained.  Those of us who are
willing to help are not here just to help you --- we'd like other people
to learn from it too, both at the time and later from the archived
discussion.  So the information needs to be in the email thread, not
only on some transient web page.


Dear Tom Lane,

Thank you for your message. I understand your point of view. However, the 
three documents I cite in my previous message have significant graphical 
content, eg. time-memory graphs. At least two of them are relevant, the 
third tangentially so. Also, these two files (opt and diag) are 25 and 41 
pages respectively in their pdf version. I don't think it would be very 
easy to cram these two into an email.


So, in light of that, would it be permissible to attach these documents to 
my email, since you want a self-contained email? If so, I hope I do not 
fall afoul of spam filters. The two documents are 3.3M (opt.pdf) and 132K 
(diag.pdf).


  Sincerely, Faheem Mitha.

--
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] How to perform text merge

[Andrus]

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||' '||lastname || '!'

and  then pgsql EXECUTE can be used to perform text merge just like .asp
pages are pre-processed and compiled.

How to do this is PostgreSql or in C# in MONO/.NET ?


Personally I think you're using a bad example here, as usually names don't
just involve firstname and surname, but frequently have infixes, suffixes
and titles and such. Not all of those fields are going to have values for
every person in your database. What happens if you don't have a Tom Lane,
but a mr. Lane, or if you have both but want to address a person more
politely?


COALESCE(), CASE WHEN  and other pgsql constructs can be used to create
correct address expressions from any data.

Andrus.


--
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] Splitting text column to multiple rows

[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 different from the order of 
elements in string.

How to preserve element order ?

Andrus.


create temp table person2 ( id char(9)) on commit drop;
insert into person2 values ('9'),('8');
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
 SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

select * from (select unnest(string_to_array('9,23,8,7,4,5',',')) ) xx, 
person2

order by id;


--
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] Achieving ordered update

[Bopolissimus Platypus Jr]

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 show what
the output should look like, or what the data would look like after a
transformation
that would set the integer value from that sequence.

do you mean to set a column in that row with that "integer value (from
a sequence)"?
or will you be filling a column in some other table with that "integer
value..."?

what is the function for determining the integer value based on the input data?

In fact, what is the input data: do you mean "other field(s) in the
same *row*" or
do you really mean "table" in that sentence?

is your function guaranteed to never have collisions (two rows
requiring the same
integer)?  if there are collisions, how do you handle them?

The "problem" isn't specified completely enough for me (probably others too,
although one or two might have a hint if they've solved something
similar already
and so will have enough context to deduce what you mean) to even gain a
mental model of just what you're trying to solve.

tiger

-- 
Gerald Timothy Quimpo http://bopolissimus.blogspot.com
bopolissimus.li...@gmail.com bopolissi...@gmail.com
Public Key: "gpg --keyserver pgp.mit.edu --recv-keys 672F4C78"

-- 
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] How many Mandatory Process are there in Postgres

[Guillaume Lelarge]

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
> 
> Logging?  I'm just guessing there.   My machines don't have it and I'm
> guessing it's the process that starts up if you're using syslog as
> opposed to local logging.
> 

That's the other way around. The logger process is launched when you
enable the logging_collector (or redirect_stderr in 8.2 and older releases).


-- 
Guillaume.
 http://www.postgresqlfr.org
 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] How to generate a valid postgre TIMESTAMP with PHP?

[APseudoUtopia]

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 postgresql, you could use CURRENT_TIMESTAMP.

Or, in PHP, you could use date('Y-m-d H:i: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] Connection Pooling

[David Kerr]

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.





It looks like both of those solutions require a coding change. I'm 
hoping for a middleware solution similar to pgpool/pgbouncer.


Thanks!

Dave

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

[GENERAL] hstore equality-index performance question

[Stefan Keller]

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 id, (kvp->'a') FROM mytable;

... can be accelerated nevertheless by adding following where clause:

  SELECT id, (kvp->'a') FROM mytable WHERE kvp ? 'a';

=> Is this correct?

-S.

-- Little test database
CREATE TABLE mytable (
  id serial PRIMARY KEY,
  kvp HSTORE
);
CREATE INDEX mytable_kvp_idx ON mytable USING GIN(kvp);
INSERT INTO mytable (kvp) VALUES ('a=>x, b=>y');
INSERT INTO mytable (kvp) VALUES ('a=>y, c=>z, d=>a');

-- 
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] optimizing import of large CSV file into partitioned table?

[Filip Rembiałkowski]

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.
+1

> And the trigger won't help
> either.  Import into a single table and split it out into further tables
> after if required.
note: partitioning could help if there were multiple physical volumes
/ spindles for data directory.
for maximizing performance, I would rather split the CSV input (with
awk/perl/whatever) before loading, to have one backend for each
partition loader.

> And finally the constraint should probably be applied
> after too, so cull any violating rows after importing.
+1



-- 
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.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] Using readline for frequently used queries

[Tim Landscheidt]

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 if a) the current line and
> b) the query buffer are empty. Before I try and err: Has
> anyone put some thought in how to reliably do that? My first
> impulse would be C-a, C-k, "\r", C-j, but this doesn't solve
> the problem if psql is in "quote mode" (e. g. the previous
> line contained an opening "'" or '"').

I found that C-c (SIGINT) makes a clean sweep but it doesn't
work as a readline macro as it gets handled by psql. So I
opted for:

| "\e[24~": "\C-a\C-k\\r\n\fSELECT * FROM DashboardQuery;\n"

which works well enough except for "quote mode".

Tim


-- 
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] warm standby possible with 8.1?

[Yar Tykhiy]

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 yet because you're trying to treat it like a
> warm-standby.  But 8.1 isn't capable of applying log files one at a
> time; it applies whatever you've got, and then it's done with
> recovery and transitions to live.  You can't just stop the result
> and then feed it the next file, as you've already discovered through
> experimentation.

Guys, I'm afraid there may be some confusion here.  I've got a warm
standby happily running with simple home-made archive and restore
scripts on a legacy Postgresql installation as old as 8.0.  And yes, I
did failover multiple times (I posted a report or two on that to this
list.)

What Zhong isn't going to get is converting the master node to a warm
standby node as easily as by just stopping it and renaming recovery.done
to recovery.conf.  The way to go here is to take a file-level DB backup
from the master node and bootstrap a new warm standby node from it, then
let it catch up with the master node WAL-wise.

Yar

-- 
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] simultaneously reducing both memory usage and runtime for a query

[Andy Colson]

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 help on the Postgres lists.
If you're supplying extremely large test data or something, it's fair to
provide a link instead of putting the information in-line, but otherwise
you should try to make your email self-contained. Those of us who are
willing to help are not here just to help you --- we'd like other people
to learn from it too, both at the time and later from the archived
discussion. So the information needs to be in the email thread, not
only on some transient web page.


Dear Tom Lane,

Thank you for your message. I understand your point of view. However,
the three documents I cite in my previous message have significant
graphical content, eg. time-memory graphs. At least two of them are
relevant, the third tangentially so. Also, these two files (opt and
diag) are 25 and 41 pages respectively in their pdf version. I don't
think it would be very easy to cram these two into an email.

So, in light of that, would it be permissible to attach these documents
to my email, since you want a self-contained email? If so, I hope I do
not fall afoul of spam filters. The two documents are 3.3M (opt.pdf) and
132K (diag.pdf).

Sincerely, Faheem Mitha.



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


--
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] Warm Standby Setup Documentation

[Yar Tykhiy]

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 you fail over, there is a window where you have no backups while 
> >> you're building the new spares.  This can be pretty nerve wracking if your 
> >> database is like ours and it takes 3-6 hours to bring a new spare online 
> >> from scratch.
> > 
> > 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 fail-over, just start shipping from the new 
> > primary to that 3rd server, now the replacement standby, and sync any files 
> > it doesn't have.  Then switch it into recovery.  Much faster than doing a 
> > new base backup from the standby on larger systems.
> 
> 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  archive_timeout = 120 # force a logfile segment switch after 
> this
>   
> I suppose you can put multiple commands there then?

You can always wrap as many commands as you like in a script.
However, there is a pitfall to watch out for when shipping WALs to
multiple standby servers.  Namely your script has to handle failures
of individual WAL shipping targets so that a single target going down
doesn't disrupt operation of the whole cluster.  Please see
http://archives.postgresql.org/pgsql-general/2009-10/msg00590.php
for discussion.

Yar

-- 
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] optimizing import of large CSV file into partitioned table?

[Nagy Zoltan]

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 into a partitioned scheme

kirk

Thom Brown wrote:
> 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-4.3.real (Ubuntu
> 4.3.2-1ubuntu11) 4.3.2
> - it is running on a Ubuntu (small) server instance at Amazon Web
> Services (AWS), with a 320Gb volume mounted for the PG data directory
> - the database was created using the partition example in the
> documentation, with an insert trigger and a function to direct which
> table where records get inserted.
> (see below for code on my table and trigger creation)
> 
> After some days of attempting to import the full 16Gb CSV file, I
> decided to split the thing up, using the split utility in Linux.
> This seemed to improve things; once I had split the CSV files into
> about 10Mb size files, I finally got my first successful import of
> about 257,000 recs. However, this is going to be a rather labor
> intensive process to import the full 16Gb file, if I have to
> manually split it up, and import each smaller file separately.
> 
> So, I am wondering if there is any to optimize this process? I have
> been using Postgres for several years, but have never had to
> partition or optimize it for files of this size until now. 
> Any comments or suggestions would be most welcomed from this
> excellent forum.
> 
> (I might add that I spend several weeks prior to this trying to get
> this to work in MySQL, which I finally had to abandon.)
> 
> Sincerely,
> Rick
> 
> Details of the code follow:
> 
> Here is the basic COPY command, which I run as the postgres user, to
> import the CSV files:
> 
> COPY allcalls FROM '/data/allcalls-csv/sub3ab' WITH CSV;
> 
> Here is what some sample data look like in the files:
> 3153371867,2008-02-04 16:11:00,1009,1,40
> 2125673062,2008-02-04 16:11:00,1009,1,41
> 5183562377,2008-02-04 16:11:00,1009,1,50
> ...
> 
> Here are the basic scripts that created the partition table and
> insert trigger:
> CREATE TABLE allcalls (
> phonenum bigint,
> callstarted timestamp without time zone,
> status int,
> attempts int,
> duration int
> ); 
> CREATE TABLE allcalls_0 (
> CHECK ( phonenum < 10 )
> ) INHERITS (allcalls);
> ...(repeat this 9 more times, for 10 subpartition tables)
> 
> CREATE INDEX allcalls_0_phonenum ON allcalls_0 (phonenum);
> ..(repeat this 9 more times, for indexes on the 10 subpartition tables)
> CREATE OR REPLACE FUNCTION allcalls_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
> IF ( NEW.phonenum  < 10 ) THEN
> INSERT INTO allcalls_0 VALUES (NEW.*);
> ELSIF ( NEW.phonenum >= 10 AND NEW.phonenum < 20
> ) THEN
> INSERT INTO allcalls_1 VALUES (NEW.*);
> ...(again, repeat for rest of the parition tables)
> 
> CREATE TRIGGER insert_phonenum_trigger
> BEFORE INSERT ON allcalls
> FOR EACH ROW EXECUTE PROCEDURE allcalls_insert_trigger();
> 
> 
> 
> 
> 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.  And the trigger
> won't help either.  Import into a single table and split it out into
> further tables after if required.  And finally the constraint should
> probably be applied after too, so cull any violating rows after importing.
> 
> Thom


-- 
Nagy Zoltan (kirk) 

-- 
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] simultaneously reducing both memory usage and runtime for a query

[Andy Colson]

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 incredibly detail oriented.  We probably on need 10% of 
whats in diag.pdf:

16 core, 64 gig ram, 6 drives on 3ware 9690SA-8I card in RAID 10, with slow 
read (280mb/sec) and write (40mb/sec).

Running 64 bit Debian lenny with postgresql 8.4.

config settings:

shared_buffers = 2GB
work_mem = 1GB
maintenance_work_mem = 8GB
wal_buffers = 16MB
checkpoint_segments = 50
effective_cache_size = 50GB


There is sql script to create the tables n'stuff.

A Few counts:

affy6_faheem=# select count(*) from anno;
count

932979
(1 row)

affy6_faheem=# select count(*) from geno;
count
---
825733782
(1 row)



The rest is irc chat about getting the data imported into PG, and other than 
slowness problems, does not seem relevant to the sql in opt.pdf.


As for opt.pdf, I dont think, again, we need all that detail.  And the 
important parts got cut off.  The explain analyze output is needed, but its cut 
off.

I'd recommend you paste the output here:

http://explain.depesz.com/

And give us links.  The explain analyze will have counts and info that we (ok 
not me, but Tom and others) can use to help you.

You also seem to have gone through several revisions of the sql (I admit, I 
just skimmed the pdf's), it would be great if you could drop the ones you are 
sure are not useful, and we concentrate on just one or two.


-Andy

--
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] Why index occupy less amount of space than the table with same structure.

[Tadipathri Raghu]

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 the space
occupied is exactly half of the table in indexes. Can you explain a bit on
this.

Thanks in Advance

Regards
Raghavendra



On Sun, Mar 28, 2010 at 4:52 PM, Alban Hertroys <
dal...@solfertje.student.utwente.nl> wrote:

> 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
> explain on this. And what exactly the postgres architecture treat on Index
> table and Ordinary table.
>
> For one thing: The table holds information regarding to which transactions
> each row is visible (the xid) whereas the index does not.
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
>
> !DSPAM:1046,4baf3bfb10411932744907!
>
>
>

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

[Ehsan Haq]

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). Please let me know.
 
Thanks
Ehsan

--- On Mon, 3/29/10, Yar Tykhiy  wrote:


From: Yar Tykhiy 
Subject: Re: [GENERAL] Moving data directory from one server to another
To: "Ehsan Haq" 
Date: Monday, March 29, 2010, 1:01 AM


Hi Ehsan,

On Fri, Mar 26, 2010 at 11:25:56AM -0700, Ehsan Haq wrote:
> Hi,
>    I have two Linux servers both having same Hardware architecture one have 
> ES5 and the other having ES4. Both the servers have same version of PostGres 
> installed (8.3). I want to move all my DBs from ES5 server to ES4 server. I 
> have tried the pg_dump but there are a lot of encoding problems, so can I 
> copy the whole data directory from ES5 server to ES4 server when the PostGres 
> server is not running? Will it be safe?

Apart from my having no idea what ES4 or ES5 is, my experience with
moving a pgsql data directory across machines has been very positive as
long as the machine architecture and Postgresql version are the same. Of
course, doing a test run before actually switching everything over will
be a very good idea.

Yar



  

Re: [GENERAL] Splitting text column to multiple rows

[Pavel Stehule]

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 different from the order of
> elements in string.
> How to preserve element order ?

no it is in same order. generate_series generates indexes from
1,2,3 so result have to be exactly in same order. You do some
wrong.

Pavel

>
> Andrus.
>
>
> create temp table person2 ( id char(9)) on commit drop;
> insert into person2 values ('9'),('8');
> CREATE OR REPLACE FUNCTION unnest(anyarray)
> RETURNS SETOF anyelement as $$
>  SELECT $1[i] FROM generate_series(1,4) g(i)
> $$ LANGUAGE sql;
>
> select * from (select unnest(string_to_array('9,23,8,7,4,5',',')) ) xx,
> person2
> order by id;
>
>

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

[GENERAL] Simultaneous write requests

[Vitali Xevet]

How does Postgres handle concurrent write requests?

Cheers
Vitali Xevet

Re: [GENERAL] Warm Standby Setup Documentation

[Greg Smith]

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 call a full-size script 
with "%f %p" and put all of the transport details into it instead.  Once 
you do that, you can ship that segment all over the place if you feel 
like it, and add significantly better error detection/recovery than 
possible in a single line too.  As already mentioned, you do need to 
make sure that you don't end up blocking archiving on the master due to 
delivery failure on an optional node however.



Also, 2 minutes - is this reasonable for a heavy write database?
  


It's extremely unlikely a write-heavy database will care about the 
setting of archive_timeout.  That setting exists for the situation where 
you sometimes go some number of minutes without generating at least 16MB 
of WAL writes, and want to force a log file to ship anyway.  That 
shouldn't happen often on a busy server.  Setting archive_timeout to a 
lower value mainly is a source of overhead on mostly idle systems.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
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] Warm Standby Setup Documentation

[Greg Smith]

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
fail-over, just start shipping from the new primary to that 3rd
server, now the replacement standby, and sync any files it doesn't
have.  Then switch it into recovery.  Much faster than doing a new
base backup from the standby on larger systems.


Every time I've tried to do this it's failed because the third server 
was looking for log files starting with 0006... but the secondary 
server (new master) is now shipping files starting with 0007...  
How do I get the third server to switch over to the higher numbered 
files?  That's the part I was never able to overcome. 


One thing it's easy to miss is that you have to save every incoming WAL 
archive file on the standby, and sync them all over to the 3rd system 
before you trigger the standby to be active.  The archive_command has to 
be active and shipping to the 3rd system before the server is triggered too.


You can think of any given standby server as a base backup and some 
number of WAL segments applied to it.  So long as you never let a WAL 
file get applied to or generated by the standby who becomes the master 
without making its way to the additional system, it should always be 
possible to bring up that additional server up to date without something 
being missing.  The exact order of operations to ensure that in all case 
is certainly not obvious though.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
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] Simultaneous write requests

[Greg Smith]

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 and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
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] simultaneously reducing both memory usage and runtime for a query

[Faheem Mitha]

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 question. It is different variants on a query (well, two queries, 
but they are very similar), along with EXPLAIN ANALYZE VERBOSE and 
time-memory graphs.


The 41 page document is just background, but relevant background. It 
contains information about the schema, tables, hardware, pg config info. 
It also has transcripts of a couple of old IRC sessions, which are 
increasingly less relevant, so I may remove that.


Since I've been told by a couple of different people that the way I asked 
my question was not ideal, I'll try reposting again with a modified 
version (but still containing the same information) in a bit.


  Regards, Faheem.

--
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] simultaneously reducing both memory usage and runtime for a query

[Faheem Mitha]

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, but now I'm curious,
>> I'll have to go take a look.
>>
>> -Andy

> Faheem, you seem to be incredibly detail oriented.  We probably on
> need 10% of whats in diag.pdf:

Most of this is stuff I've been asked on #postgresql. Admittedly, in
different contexts than this query.

[snippage]

> The rest is irc chat about getting the data imported into PG, and
>  other than slowness problems, does not seem relevant to the sql in
>  opt.pdf.

True, the IRC sessions should go. I'll take them out.

> As for opt.pdf, I dont think, again, we need all that detail.  And
>  the important parts got cut off.  The explain analyze output is
>  needed, but its cut off.

> I'd recommend you paste the output here:
>
> http://explain.depesz.com/

> And give us links.  The explain analyze will have counts and info
> that we (ok not me, but Tom and others) can use to help you.

That's one way to go. I was going to paste the entirety of opt.tex
into an email. That would include all the EXPLAIN ANALYZE STUFF, but
not the graphs, and thus would be relatively self-contained. For the
graphs you'd have to look at a pdf (unless a ps.gz is preferred).

> You also seem to have gone through several revisions of the sql (I
> admit, I just skimmed the pdf's), it would be great if you could
> drop the ones you are sure are not useful, and we concentrate on
> just one or two.

Ok, I'll trim it down a bit. At least the initial queries in both
sections are not relevant. Thanks for the feedback.

   Regards,Faheem.


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