Re: [GENERAL] Archiving and recovering pg_stat_tmp

On Thu, Jun 20, 2013 at 3:17 PM, Sameer Thakur wrote: > Hello, > I was trying to figure out how does one recover server statistics to the > same snapshot to which a database is restored after PITR. > The steps i had in mind were > 1.Set up WAL archiving > 2.On server shutdown one would need to bac

Re: [GENERAL] variadic args to C functions

Hello I wrote lot of C VARIADIC functions - some examples are in core - "format" function Schema | Name| Result data type | Argument data types | Type +---+--+--+ pg_catalog | concat| text | VARIADIC "an

[GENERAL] Tow kinds of different result while using create index concurrently

Hello: I have question about PG's "create index concurrently". I think it is a bug perhaps. I make two tables tab01 and tab02, they have no relationships. I think "create index concurrently " on tab02 will not be influenced by transaction on tab01. But the result differs: My first prog

Re: [GENERAL] LDAP authentication timing out

On Thu, Jun 20, 2013 at 7:24 AM, James Sewell wrote: > Hello All, > > I have the following config: > > hostsamerole+myrole samenetldap > ldapserver="ldap1,ldap2,ldap3" ldapbinddn="mybinddn" > ldapbindpasswd="mypass" ldapbasedn="mybase" ldapsearchattribute="myatt" >

Re: [GENERAL] Snapshot backups

On Thu, Jun 20, 2013 at 8:45 AM, James Sewell wrote: > Hey All, > > This is a message to confirm my thoughts / validate a possible approach. > > In a situation where PGDATA and {XLOG, ARCHIVELOG} are on different > SAN/NAS volumes and a backup is to be initiated do pg_start_backup and > pg_stop_ba

Re: [GENERAL] Archiving and recovering pg_stat_tmp

>Documentation mentions following: Thanks, but how does this relate to statistics recovery wrt PITR? regards Sameer

Re: [GENERAL] Postgres DB crashing

On 18/06/13 18:31, bhanu udaya wrote: Hello, Greetings. My PostgresSQL (9.2) is crashing after certain load tests. Currently, postgressql is crashing when simulatenously 800 to 1000 threads are run on a 10 million records schema. Not sure, if we have to tweak some more parameters of postgres. Cu

Re: [GENERAL] Archiving and recovering pg_stat_tmp

On Thu, Jun 20, 2013 at 6:05 PM, Sameer Thakur wrote: >>Documentation mentions following: > Thanks, but how does this relate to statistics recovery wrt PITR? Upon clean server shutdown, you have the statistics files stored in the pg_stat (previously global/) directory, which persists across serve

Re: [GENERAL] Postgres DB crashing

On Thu, Jun 20, 2013 at 5:17 AM, Richard Huxton wrote: > On 18/06/13 18:31, bhanu udaya wrote: >> >> Hello, >> Greetings. >> >> My PostgresSQL (9.2) is crashing after certain load tests. Currently, >> postgressql is crashing when simulatenously 800 to 1000 threads are run >> on a 10 million record

Re: [GENERAL] Exporting Data

guilherme wrote > I need to get some information in database and export it. > > Is there a way to make PostgreSQL to export one data line to a new file? > Like this: > 1 FIRST LINE --> line1.txt; > 2 SECOND LINE --> line2.txt; > 3 THIRD LINE --> line3.txt > ... > and so... > > I know that I can i

[GENERAL] Do not understand high estimates of index scan vs seq scan

Hi all, I do not understand why postgreSQL estimates an index scan only half as fast as a seq scan: system=# explain select * from queuelog;QUERY PLAN --- Seq Scan on queuelog (cost=0.00..20530.29 rows=

Re: [GENERAL] Exporting Data

On 06/20/2013 06:06 AM, David Johnston wrote: guilherme wrote I need to get some information in database and export it. Is there a way to make PostgreSQL to export one data line to a new file? Like this: 1 FIRST LINE --> line1.txt; 2 SECOND LINE --> line2.txt; 3 THIRD LINE --> line3.txt ... and

[GENERAL] Order by with column ordinal and collate - fails to parse

Hi all, I seem to be having problems constructing a query that combines the use of distinct, order by, and collate key words. For instance: # select distinct(value) from properties order by 1 collate "C"; ERROR: collations are not supported by type integer LINE 1: ... distinct(value) from pro

Re: [GENERAL] Postgres DB crashing

On Thursday, June 20, 2013 07:52:21 AM Merlin Moncure wrote: > OP needs to explore use of connection pooler, in particular pgbouncer. > Anyways none of this explains why the server is actually crashing. It might be hitting file descriptor limits. I didn't dig into the earlier part of this thread

Re: [GENERAL] Order by with column ordinal and collate - fails to parse

On 06/20/2013 07:05 AM, Tim Kane wrote: Hi all, I seem to be having problems constructing a query that combines the use of distinct, order by, and collate key words. For instance: # select distinct(value) from properties order by 1 collate "C"; ERROR: collations are not supported by type int

Re: [GENERAL] Order by with column ordinal and collate - fails to parse

Nice one. Yep, that works. Cheers.. I'll submit a bug report for this, though I'm on the fence as to wether this is actually a bug per se.. I would have reasonably expected my original syntax to have worked (as it does without any ORDER BY).. On Thu, Jun 20, 2013 at 3:44 PM, Adrian Klaver wrote:

[GENERAL] Replication with Drop: could not open relation with OID

Hello, I have a database server which do a complex  views calculation,  the result of those views are shipped to another database servers via a simple  replication tool which have a high  client loads. The tool  is creating a  table, and indexes based on predefined conf.,   then drop the t

[GENERAL] coalesce function

Hi All, I am using coalesce(firstname,lastname), to get the result if first name is 'NULL' it will give me lastname or either way. I am having data like instead of NULL, blank null ( i mean something like '' ) for which coalesce is not working, is there any workaround or other function available

Re: [GENERAL] coalesce function

Torsdag 20. juni 2013 21.45.02 skrev itishree sukla: > Hi All, > > I am using coalesce(firstname,lastname), to get the result if first name is > 'NULL' it will give me lastname or either way. I am having data like > instead of NULL, blank null ( i mean something like '' ) for which > coalesce is

Re: [GENERAL] coalesce function

Hi, http://www.postgresql.org/docs/9.1/static/functions-conditional.html describes NULLIF, when combined with COALESCE it should answer your request. HTH Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl Convince Microsoft! They need to add TRUNCATE PARTITION in

[GENERAL] Exporting Data

I need to get some information in database and export it. Is there a way to make PostgreSQL to export one data line to a new file? Like this: 1 FIRST LINE --> line1.txt; 2 SECOND LINE --> line2.txt; 3 THIRD LINE --> line3.txt ... and so... I know that I can import all information into a unique fi

[GENERAL] unexpected external sort Disk

Hello, I have table with 37 million entries the whole table has a size of 2.3 GB Although I have set the work_mem to 10 GB I see the an unexpected external sort Disk in Explain Analyze for around 650MB of data EXPLAIN ANALYZE SELECT application_id, price_tier FROM application_prices orde

Re: [GENERAL] Tow kinds of different result while using create index concurrently

On Thu, Jun 20, 2013 at 1:27 AM, 高健 wrote: > Hello: > > > > I have question about PG's "create index concurrently". I think it is a > bug perhaps. > > > > I make two tables tab01 and tab02, they have no relationships. > > I think "create index concurrently " on tab02 will not be influenced by >

Re: [GENERAL] Do not understand high estimates of index scan vs seq scan

On Thu, Jun 20, 2013 at 6:47 AM, Antonio Goméz Soto < antonio.gomez.s...@gmail.com> wrote: > Hi all, > > I do not understand why postgreSQL estimates an index scan only half as > fast as a seq scan: > > system=# explain select * from queuelog;QUERY > PLAN >

Re: [GENERAL] unexpected external sort Disk

On Thu, Jun 20, 2013 at 6:12 AM, Manuel Kniep wrote: > Hello, > > I have table with 37 million entries the whole table has a size of 2.3 GB > > Although I have set the work_mem to 10 GB > There is one piece of memory used in in-memory sorting that (currently) has to be a single contiguous allo

Re: [GENERAL] PSA: If you are running Precise/12.04 upgrade your kernel.

On 06/17/2013 04:00 PM, Joshua D. Drake wrote: http://postgresql.1045698.n5.nabble.com/Ubuntu-12-04-3-2-Kernel-Bad-for-PostgreSQL-Performance-td5735284.html tl;dr for that thread seems to be a driver problem (fusionIO?), I'm unsure if Ubuntu specific or in the upstream kernel. That instance w

Re: [GENERAL] Exporting Data

On Jun 20, 2013, at 14:33, guilherme wrote: > I need to get some information in database and export it. > > Is there a way to make PostgreSQL to export one data line to a new file? > Like this: > 1 FIRST LINE --> line1.txt; > 2 SECOND LINE --> line2.txt; > 3 THIRD LINE --> line3.txt > ... > and

[GENERAL] Problem with left join when moving a column to another table

I am having some problems moving a column to another table and fixing some views that rely on it. I want to move the area_id column from t_offerprice_pipe to t_offerprice and then left join the results. When I have only one table I get the correct results. area_id is currently in the t_offerpric

Re: [GENERAL] PSA: If you are running Precise/12.04 upgrade your kernel.

Good to know. I've got a few spare machines I might be able to test 3.2 kernels on in the next few months On Thu, Jun 20, 2013 at 12:54 PM, Shaun Thomas wrote: > On 06/17/2013 04:00 PM, Joshua D. Drake wrote: > >>> >>> http://postgresql.1045698.n5.nabble.com/Ubuntu-12-04-3-2-Kernel-Bad-for-Postg

Re: [GENERAL] Problem with left join when moving a column to another table

Jason Long-2 wrote > I am having some problems moving a column to another table and fixing > some views that rely on it. I want to move the area_id column from > t_offerprice_pipe to t_offerprice and then left join the results. > > When I have only one table I get the correct results. area_id is

Re: [GENERAL] intagg

On Thu, Jun 20, 2013 at 12:22 AM, Andrew Bartley wrote: > Sorry that should be aggregate int_array_aggregate not function > > > On 20 June 2013 08:16, Andrew Bartley wrote: >> >> Hi All, >> >> I am trying to use the intagg extension. in 9.1.9 >> >> I have created the extension as such "CREATE EXT

Re: [GENERAL] intagg

Andrew Bartley writes: > function int_array_aggregate(integer[]) does not exist int_array_aggregate() takes integers, not arrays of integers. Depending on exactly what semantics you'd like to have, you could probably build a custom aggregate to do this without any new C code --- try basing it on

Re: [GENERAL] coalesce function

itishree sukla wrote > Hi All, > > I am using coalesce(firstname,lastname), to get the result if first name > is > 'NULL' it will give me lastname or either way. I am having data like > instead of NULL, blank null ( i mean something like '' ) for which > coalesce is not working, is there any work

Re: [GENERAL] Problem with left join when moving a column to another table

David, Thank you very much for your response. Below is a script that will reproduce the problem with comments included. /***/ --drop table t_item; --drop table t_price_base_table; --drop table t_price_original_with_area_id; --this table repres

Re: [GENERAL] coalesce function

On Fri, Jun 21, 2013 at 7:36 AM, David Johnston wrote: > SELECT input > FROM ( SELECT unnest($1) AS input ) src > WHERE input IS NOT NULL AND input <> '' > LIMIT 1; Does this guarantee the order of the results returned? Using LIMIT without ORDER BY is something I'v

[GENERAL] Easiest way to compare the results of two queries row by row and column by column

Can someone suggest the easiest way to compare the results from two queries to make sure they are identical? I am rewriting a large number of views and I want to make sure that nothing is changes in the results. Something like select compare_results('select * from v_old', 'select * from v_new')

Re: [GENERAL] Problem with left join when moving a column to another table

Jason Long-2 wrote > David, > > Thank you very much for your response. > Below is a script that will reproduce the problem with comments > included. > > > > /* > This is the new query that is not working correctly. > I am trying to left join the base table by its id and area_id. > I nee

Re: [GENERAL] Problem with left join when moving a column to another table

On Thu, 2013-06-20 at 15:37 -0700, David Johnston wrote: > Jason Long-2 wrote > > David, > > > > Thank you very much for your response. > > Below is a script that will reproduce the problem with comments > > included. > > > > > > > > /* > > This is the new query that is not working correc

[GENERAL] Re: Easiest way to compare the results of two queries row by row and column by column

Jason Long-2 wrote > Can someone suggest the easiest way to compare the results from two > queries to make sure they are identical? First thing that comes to mind: WITH before_qry (col1, col2, col3) AS ( VALUES (1,1,1),(2,2,2),(3,3,3) ) , after_qry (col1, col2, col3) AS ( VALUE

Re: [GENERAL] Easiest way to compare the results of two queries row by row and column by column

On Thu, Jun 20, 2013 at 3:18 PM, Jason Long wrote: > Can someone suggest the easiest way to compare the results from two > queries to make sure they are identical? > > I am rewriting a large number of views and I want to make sure that > nothing is changes in the results. > > Something like > > se

Re: [GENERAL] Re: Easiest way to compare the results of two queries row by row and column by column

Thank you. I will give it a try. I have never used WITH before. Thank you for the tips. On Thu, 2013-06-20 at 16:05 -0700, David Johnston wrote: > Jason Long-2 wrote > > Can someone suggest the easiest way to compare the results from two > > queries to make sure they are identical? > > First

Re: [GENERAL] Problem with left join when moving a column to another table

Jason Long-2 wrote >> Jason Long-2 wrote > > > There is a unique constraint on the real price table. I hadn't thought > of how I will enforce the constraint across two tables. > size_id and area_id will have to be unique across both > t_price_base_table and t_price_original_with_area_id. I will

Re: [GENERAL] LDAP authentication timing out

Hey, Thanks for the reply Magnus. I'm getting some packet captures now - I just thought I'd throw this out there in case anyone else had faced similar problems. This is EDB PPAS, I'm following up with them in parallel. Cheers, James Sewell James Sewell Solutions Architect _

Re: [GENERAL] Snapshot backups

Thanks Magnus, Could you elaborate a bit more on this? I've been having a look at do_pg_start_backup() and I can't really see anything apart from enabling full page writes and running a checkpoint to avoid getting a torn page. I could be missing something easily though, as I'm not familiar with t

Re: [GENERAL] Tow kinds of different result while using create index concurrently

Thanks Jeff But What I can't understand is: In My first test, the "create index concurrently" works well. In My second test, the "create index concurrently" can not work. The difference is only on ecpg's select statement : One use host variable of char (its value is of integer 14) in select sta

[GENERAL] Circular references

I was given a dump of an existing remote schema and database, and the restore on my local system failed. Looking into it, I found a circular parent-child/child-parent relationship, and I don't believe this existing structure is viable. To summarize, the organization entity has an attribute of creat

Re: [GENERAL] Archiving and recovering pg_stat_tmp

On Thu, Jun 20, 2013 at 8:32 PM, Amit Langote wrote: > On Thu, Jun 20, 2013 at 6:05 PM, Sameer Thakur wrote: >>>Documentation mentions following: >> Thanks, but how does this relate to statistics recovery wrt PITR? > > Upon clean server shutdown, you have the statistics files stored in > the pg_s

Re: [GENERAL] Circular references

Melvin Call writes: > I was given a dump of an existing remote schema and database, and the > restore on my local system failed. Looking into it, I found a circular > parent-child/child-parent relationship, and I don't believe this existing > structure is viable. To summarize, the organization ent

Re: [GENERAL] Problem with left join when moving a column to another table

On Thu, 2013-06-20 at 16:22 -0700, David Johnston wrote: > Jason Long-2 wrote > >> Jason Long-2 wrote > > > > > > There is a unique constraint on the real price table. I hadn't thought > > of how I will enforce the constraint across two tables. > > size_id and area_id will have to be unique

Re: [GENERAL] Problem with left join when moving a column to another table

Jason Long-2 wrote > Does the syntax you showed me have performance benefits vs joining a > bunch of views together? As a general rule CTE/WITH is going to be worse performing than the equivalent view definition - depending on the view is actually used in the query of course. They both have their

Re: [GENERAL] Archiving and recovering pg_stat_tmp

> >"You need to have statistics recovered to the same state as they were > >when you took the FS level backup of your database after shutting down > >the server." > Correct > > >"Shutting down" is important since that is when you would have > >statistics files ($PGDATA/pg_stat/*.stat) availabl

Re: [GENERAL] Migration from DB2 to PostgreSQL

>PostgreSQL has no such capability. Unless you need that and >want to code it yourself, the best solution would be to >write a function that just ignores the third argument. For time being I will write a function that just ignores the third argument. but if we really want to create such funct

Re: [GENERAL] Archiving and recovering pg_stat_tmp

On Fri, Jun 21, 2013 at 2:44 PM, Sameer Thakur wrote: > >> >"You need to have statistics recovered to the same state as they were >> >when you took the FS level backup of your database after shutting down >> >the server." > > Correct >> >> >> >"Shutting down" is important since that is when yo

Re: [GENERAL] Archiving and recovering pg_stat_tmp

On Fri, Jun 21, 2013 at 11:35 AM, Amit Langote wrote: > On Fri, Jun 21, 2013 at 2:44 PM, Sameer Thakur > wrote: > > > >> >"You need to have statistics recovered to the same state as they were > >> >when you took the FS level backup of your database after shutting down > >> >the server." > > > >

Re: [GENERAL] Easiest way to compare the results of two queries row by row and column by column

On 06/21/2013 01:07 AM, Jeff Janes wrote: > On Thu, Jun 20, 2013 at 3:18 PM, Jason Long > > wrote: > > Can someone suggest the easiest way to compare the results from two > queries to make sure they are identical? > > I am rewriting a large number