Re: [GENERAL] Application takes longer time to fetch large no of records from Postgresql 9.0.3

On Mon, Oct 15, 2012 at 9:24 PM, Deven Thaker wrote: > Hi, > My application takes longer time (we see time out even) when data to be > fetched from Postgresql 9.0.3 is around 190 records. I am doing an > improvement at application level, but from database side any performance > tuning do i nee

[GENERAL] return query execute SQL-problem

Hi there, here is something I don't quite grasp (PG 9.1.3): This function: CREATE OR REPLACE FUNCTION f_aliastest() RETURNS setof text AS $BODY$ declare sql text; begin sql:='SELECT ''sometext''::text as alias'; return query execute SQL; end; $BODY$ LANGUAGE plpgsql VOLATILE; re

Re: [GENERAL] Application takes longer time to fetch large no of records from Postgresql 9.0.3

On 10/16/2012 12:40 PM, Craig Ringer wrote: On 10/16/2012 12:24 PM, Deven Thaker wrote: Hi, My application takes longer time (we see time out even) when data to be fetched from Postgresql 9.0.3 is around 190 records. I am doing an improvement at application level, but from database side any

Re: [GENERAL] return query execute SQL-problem

On 2012-10-16 10:44, Maximilian Tyrtania wrote: Hi there, here is something I don't quite grasp (PG 9.1.3): This function: CREATE OR REPLACE FUNCTION f_aliastest() RETURNS setof text AS $BODY$ declare sql text; begin sql:='SELECT ''sometext''::text as alias'; return query execute SQL; end

Re: [GENERAL] Who is LISTENing?

On 10/16/2012 07:49 AM, Raghavendra wrote: On Tue, Oct 16, 2012 at 11:01 AM, Sim Zacks wrote: On 10/15/2012 06:11 PM, rektide wrote: Hi

Re: [GENERAL] return query execute SQL-problem

On Tue, Oct 16, 2012 at 09:44:03AM +0200, Maximilian Tyrtania wrote: > Is there a way that I can make my function return the field aliases? CREATE OR REPLACE FUNCTION f_aliastest() RETURNS setof text AS function defined as above returns set of values without any name. name is chosen by pg. you ca

Re: [GENERAL] return query execute SQL-problem

Am 16.10.2012 um 11:37 schrieb hubert depesz lubaczewski : > create or replace function f_aliastest(OUT alias TEXT) returns setof record > AS … Ah, yes, I forgot about the OUT parameters, thanks for the reminder! Maximilian Tyrtania Contact King Software Entwicklung Maximilian Tyrtania http://w

Re: [GENERAL] Strategies/Best Practises Handling Large Tables

Thank you all for your suggestions. Since all of you recommended the Partition option, I decided to try it out myself. I found a function that created partition tables for every month in the last two years. A trigger was also added to the parent table to ensure that every insert into it from hence

Re: [GENERAL] Strategies/Best Practises Handling Large Tables

On Tue, Oct 16, 2012 at 09:26:09PM +1100, Chitra Creta wrote: > Thank you all for your suggestions. Since all of you recommended the > Partition option, I decided to try it out myself. > > I found a function that created partition tables for every month in the > last two years. A trigger was also

Re: [GENERAL] Who is LISTENing?

On 2012-10-15, rektide wrote: > Hi pgsql-general, > > I'm interested in writing a supervisory process that can insure worker > processes are > running/spawn new ones if not. These workers will mainly be responsible for > LISTENing to > the db, which is emitting triggered_change_notification s. >

[GENERAL] Re: transitive pruning optimization on the right side of a join for partition tables

Hi, I have tried more on this, if you just use an equality constraint on the foreign key in the right side of the join, it works. Other constraints like between, in, >,< do not work. Anyone else got to achieve this in postgresql? -- View this message in context: http://postgresql.1045698.n5.na

Re: [GENERAL] return query execute SQL-problem

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Maximilian Tyrtania > Sent: Tuesday, October 16, 2012 3:44 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] return query execute SQL-problem > > Hi there, >

Re: [GENERAL] return query execute SQL-problem

Am 16.10.2012 um 10:56 schrieb Condor : > You can use AS > select f_aliastest() AS alias; Yeah, thanks, well, my question is basically if there is a way to make the function alias-savvy. In the meantime I realized that the problem is not limited to return query execute SQL but to return query i

Re: [GENERAL] Strategies/Best Practises Handling Large Tables

On Fri, Oct 12, 2012 at 10:00 AM, Lonni J Friedman wrote: > On Fri, Oct 12, 2012 at 7:44 AM, Chitra Creta wrote: >> Hi, >> >> I currently have a table that is growing very quickly - i.e 7 million >> records in 5 days. This table acts as a placeholder for statistics, and >> hence the records are m

Re: [GENERAL] Who is LISTENing?

On Tue, Oct 16, 2012 at 5:18 AM, Jasen Betts wrote: > On 2012-10-15, rektide wrote: > > Hi pgsql-general, > > > > I'm interested in writing a supervisory process that can insure worker > processes are > > running/spawn new ones if not. These workers will mainly be responsible > for LISTENing to

[GENERAL] PostgreSQL training recommendations?

Hi pgsql-general, I'm looking for advice on good training courses for PostgreSQL (on- or off-site, on- or off-line). I'm hoping to find something that can cover basic administration, performance optimization topics, and clustering tools like Slony and pgpool for someone. I realize that Postgr

Re: [GENERAL] Strategies/Best Practises Handling Large Tables

On 10/12/2012 09:44 AM, Chitra Creta wrote: 4. Creation of daily, monthly, yearly summary tables that contains aggregated data specific to the statistics required If the partitioning doesn't help you, I strongly urge you to build one or more Fact tables with appropriate Diminsions. If you don

[GENERAL] transaction log file "000000010000097600000051" could not be archived: too many failures

HI I am getting the following error in my postgresql log file. cp: cannot stat `pg_xlog/000109760051': No such file or directory gzip: /usr/local/pgsql/wal_archive/000109760051: No such file or directory < 76800 2012-10-16 07:58:11.903 CDT 0 >LOG: archive command fa

Re: [GENERAL] PostgreSQL training recommendations?

I assume the EntrerpriseDB certification seminars are an obvious quick answer: http://www.enterprisedb.com/products-services-training/training/dba-training But TBH, I find the PostgreSQL manual to be an excelent guide if you don't mind reading. It is extremely well written (kudos to whoever is on

Re: [GENERAL] transaction log file "000000010000097600000051" could not be archived: too many failures

Mathew Thomas writes: > I am getting the following error in my postgresql log file. > cp: cannot stat `pg_xlog/000109760051': No such file or > directory If there's a .ready file corresponding to that, remove it. I'm not entirely sure how you could have ended up with a .ready file bu

Re: [GENERAL] PostgreSQL training recommendations?

On Tue, Oct 16, 2012 at 02:53:14PM -0300, Thalis Kalfigkopoulos wrote: > I assume the EntrerpriseDB certification seminars are an obvious quick > answer: > http://www.enterprisedb.com/products-services-training/training/dba-training > > But TBH, I find the PostgreSQL manual to be an excelent guid

Re: [GENERAL] PostgreSQL training recommendations?

On Tue, Oct 16, 2012 at 02:34:37PM -0400, Bruce Momjian wrote: > On Tue, Oct 16, 2012 at 02:53:14PM -0300, Thalis Kalfigkopoulos wrote: > > I assume the EntrerpriseDB certification seminars are an obvious quick > > answer: > > http://www.enterprisedb.com/products-services-training/training/dba-tra

Re: [GENERAL] PostgreSQL training recommendations?

True about the lack of framework putting the pieces together and providing an overview. Also IMHO another difficulty the manual poses is that the reader doesn't have a way to confirm his level of understanding after reading a chapter. Letting aside the concepts for which creating a scenario/test-

Re: [GENERAL] PostgreSQL training recommendations?

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Thalis Kalfigkopoulos > Sent: Tuesday, October 16, 2012 3:24 PM > To: Bruce Momjian > Cc: Matthew Kappel; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Postgre

Re: [GENERAL] PostgreSQL training recommendations?

On Tue, Oct 16, 2012 at 16:24:08 -0300, Thalis Kalfigkopoulos wrote: Also IMHO another difficulty the manual poses is that the reader doesn't have a way to confirm his level of understanding after reading a chapter. It isn't too hard to play with a toy database. I personally found (and stil

[GENERAL] Does Postgres Object-Relational Syntax follow Standard?

Hi. I was having a discussion with people at work about the Postgres object-relational syntax. The question came up: does this follow an SQL standard? Or is it rather a Postgres-only feature with different RDBMSs doing it differently? I tried some quick checks on Google and Wikipedia but co

Re: [GENERAL] PostgreSQL training recommendations?

On 10/16/12 3:24 PM, Thalis Kalfigkopoulos wrote: Now I'd understand the Pg manual writers being reluctant about shifting from manual to DB-book, but I'm guessing, the manual being as well written as it is, that many of us are already using it as a learning book anyway. The official manual is a

Re: [GENERAL] Does Postgres Object-Relational Syntax follow Standard?

On 10/17/2012 05:00 AM, Will Rutherdale (rutherw) wrote: Hi. I was having a discussion with people at work about the Postgres object-relational syntax. What syntax specifically? Do you mean table inheritance and SELECT ONLY ? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] Does Postgres Object-Relational Syntax follow Standard?

On Tue, Oct 16, 2012 at 2:00 PM, Will Rutherdale (rutherw) < ruth...@cisco.com> wrote: > Hi. > > I was having a discussion with people at work about the Postgres > object-relational syntax. The question came up: does this follow an SQL > standard? Or is it rather a Postgres-only feature with di

[GENERAL] 9.1/9.2 SERIALIZABLE: expected serialization failure between INSERT and SELECT not encountered

Hi all In this SO question: http://dba.stackexchange.com/questions/26905/how-do-i-implement-insert-if-not-found-for-transactions-at-serializable-isolatio/26909#26909 the author is running a series of queries that I'd expect to abort on commit with a serialisation failure. No such failure occur

[GENERAL] Streaming replication failed to start scenarios

Hi all, I have been testing streaming replication in windows with postgres 9.1.1. For few scenario's I haven't found a solution. Please advice me. 1. Precautions before promoting standby server to primary manually considering the dead primary server ?? 2. How could we ensure the standby has rece

[GENERAL] Replication - doubts

Hello all, I went through the documentation on replication. and am trying to understand the various options available. Does replication available in PostgreSQL always use WAL (i.e irrespective of whether it is synchronous/asynchronous, whether it is using archived logs or 'live' logs or streamin