Re: [GENERAL] upgrading to 9.3

On 11/06/2013 03:08 PM, zach cruise wrote: moving from 8.1 to 9.3, and redesigning at the same time (using navicat and psql). have access to both 8.1 and 9.3. and by redesigning i mean, going from multiple databases to multiple schemas. so what's the best approach? Having just done that, I ca

[GENERAL] upgrading to 9.3

moving from 8.1 to 9.3, and redesigning at the same time (using navicat and psql). have access to both 8.1 and 9.3. and by redesigning i mean, going from multiple databases to multiple schemas. so what's the best approach?

Re: [GENERAL] After upgrade to 9.3, streaming replication fails to start

On 11/6/13, 11:32 AM, Jeff Janes wrote: On Wed, Nov 6, 2013 at 9:40 AM, Jeff Ross > wrote: _postgresql@nirvana:/var/postgresql $ cat start_hot_standby.sh #!/bin/sh backup_label=wykids_`date +%Y-%m-%d` #remove any existing wal files on the standby ss

Re: [GENERAL] Full text search on partial URLs

On 11/06/2013 02:04 PM, bricklen wrote: On Wed, Nov 6, 2013 at 10:53 AM, Zev Benjamin mailto:zev-pg...@strangersgate.com>> wrote: On 11/06/2013 01:47 PM, bricklen wrote: On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin mailto:zev-pg...@strangersgate.com>

Re: [GENERAL] Full text search on partial URLs

On Wed, Nov 6, 2013 at 10:53 AM, Zev Benjamin wrote: > On 11/06/2013 01:47 PM, bricklen wrote: > >> >> On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin >> mailto:zev-pg...@strangersgate.com>> wrote: >> >> Hi, >> >> I have Postgres full text search set up for my application and it's >> bee

Re: [GENERAL] Full text search on partial URLs

On 11/06/2013 01:47 PM, bricklen wrote: On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin mailto:zev-pg...@strangersgate.com>> wrote: Hi, I have Postgres full text search set up for my application and it's been working great! However, my users would like their searches to turn up

Re: [GENERAL] Full text search on partial URLs

On Wed, Nov 6, 2013 at 10:45 AM, Zev Benjamin wrote: > Hi, > > I have Postgres full text search set up for my application and it's been > working great! However, my users would like their searches to turn up > parts of URLs. For example, they would like a search for "foobar" to turn > up a docum

[GENERAL] Full text search on partial URLs

Hi, I have Postgres full text search set up for my application and it's been working great! However, my users would like their searches to turn up parts of URLs. For example, they would like a search for "foobar" to turn up a document that contains the string "http://example.com/foobar/blah

Re: [GENERAL] After upgrade to 9.3, streaming replication fails to start

On Wed, Nov 6, 2013 at 9:40 AM, Jeff Ross wrote: _postgresql@nirvana:/var/postgresql $ cat start_hot_standby.sh > #!/bin/sh > backup_label=wykids_`date +%Y-%m-%d` > #remove any existing wal files on the standby > ssh dukkha.internal rm -rf /wal/* > #stop the standby server if it is running > ssh

[GENERAL] After upgrade to 9.3, streaming replication fails to start

Hi all, I recently installed 9.3 into my work servers. With 9.2 I had streaming replication working. Primary server is on nirvana, standby server is on dukkha. Upgrade on nirvana went just fine--no problems there. Initial installation on dukkha went fine as well, but streaming replication

Re: [GENERAL] Is it advisable to pg_upgrade directly from 9.0 to 9.3?

On 11/06/2013 01:06 AM, Greg Burek wrote: Hello, How advisable or well known is it to take a 9.0 era db directly to 9.3 using the latest pg_upgrade binary? Sure. I have done it all the way back to 8.3 (with 9.2). You should of course test as pg_upgrade is a tool not a perfect solution. IMO,

Re: [GENERAL] WITH RECURSIVE doesn't work properly for me

Yeah, that can explain why it doesn't work. Thank you very much:) On Wed, Nov 6, 2013 at 8:40 AM, Albe Laurenz wrote: > Jing Fan wrote: > > On Wed, Nov 6, 2013 at 8:10 AM, Albe Laurenz > wrote: > >> Let's assume that we have three nodes A, B and C. > >> Also, A points to B, B points to C and C

Re: [GENERAL] WITH RECURSIVE doesn't work properly for me

Jing Fan wrote: > On Wed, Nov 6, 2013 at 8:10 AM, Albe Laurenz wrote: >> Let's assume that we have three nodes A, B and C. >> Also, A points to B, B points to C and C points to B. >> >> Let's assume that we already generated (A, B, 1) and (A, C, 2) >> in previous iterations. >> >> Then the "recu

Re: [GENERAL] WITH RECURSIVE doesn't work properly for me

But after this iteration, the paths will be: A B 1 B C 1 C B 1 A C 2 A B 3 in next iteration, the recursive statement will generate (A,C,2), (A,B,3), and (A,C,4), after the group by, it will still be (A,C,2) and (A,B,3) so I think it should stop after this iteration. On Wed, Nov 6, 2013 at 8:10

Re: [GENERAL] WITH RECURSIVE doesn't work properly for me

Jing Fan wrote: > I am sorry but I still don't understand why it doesn't work. Possibly I > misunderstand how with > recursive works? > In my opinion, > with recursive table as{ > seed statement > union > recursive statement > } > In every iteration, It will just generate results from

Re: [GENERAL] WITH RECURSIVE doesn't work properly for me

I am sorry but I still don't understand why it doesn't work. Possibly I misunderstand how with recursive works? In my opinion, with recursive table as{ seed statement union recursive statement } In every iteration, It will just generate results from seed statement union recursive statem

Re: [GENERAL] Is it advisable to pg_upgrade directly from 9.0 to 9.3?

I don't think that there will be too much trouble, as long as you follow every changelog tip (9.0->9.1, 9.1->9.2 and 9.2->9.3) On Wed, Nov 6, 2013 at 7:06 AM, Greg Burek wrote: > Hello, > How advisable or well known is it to take a 9.0 era db directly to 9.3 > using the latest pg_upgrade binary

Re: [GENERAL] pg_start_backup('label',true) why do I need 2nd parameter?

On Tue, 5 Nov 2013 19:27:52 -0800 (PST) David Johnston wrote: > Bill Moran wrote > > How long that takes is a factor of other settings (as David mentioned) and > > also depedent on what other transactions may be running. > > While I am inclined to believe this is true the documentation is unclea

Re: [GENERAL] Row Level Access

Maciej Mrowiec wrote: > I'm working on RBAC implementation over posgresql and I was wondering is > there any common technique to > achieve row level access control ? > > So far I'm considering using WITH clause in template like this: > > WITH AS ( SELECT . ) ; > > Which would be just prepe

[GENERAL] Some questions about postgresql's default text search parser

Hi eveyone, I've been trying to understand the text search parser's behaviour. Looking at the source code [1] it seems as if there was a sophisticated FSM mapping the input string to a list of tuples of category (as defined in [1], lines 32-56, or [2]) and a substring from the original one order b

[GENERAL] Row Level Access

Hello List :D I'm working on RBAC implementation over posgresql and I was wondering is there any common technique to achieve row level access control ? So far I'm considering using WITH clause in template like this: WITH AS ( SELECT . ) ; Which would be just prepended to any specified quer

[GENERAL] Is it advisable to pg_upgrade directly from 9.0 to 9.3?

Hello, How advisable or well known is it to take a 9.0 era db directly to 9.3 using the latest pg_upgrade binary? I imagine that the upgrade path between adjacent major versions is more battle tested, but we have several dbs that we would like to bring up to date without meandering through ever ma

Re: [GENERAL] Junk date getting uploaded into date field

On 11/5/2013 10:29 AM, Steve Crawford wrote: The to_date and to_timestamp functions do minimal input error-checking and are intended for conversion of non-standard formats that cannot be handled by casting. These functions will attempt to convert illegal dates to the best of their ability, e.g.

[GENERAL] Where can I find the 9.3 beta1 rpm now ?

Hi everybody. I downloaded and tested 9.3 beta 1 back in june and used for a while. Today I upgraded to 9.3.1 but just discovered that database cannot be directly used, so I need to switch back to the beta version in order to dump the data. Is there any site I can download the old beta rpm's ?

Re: [GENERAL] Junk date getting uploaded into date field

Steve Crawford wrote: >> There is a comment in utils/adt/formatting.c: >> >> * This function does very little error checking, e.g. >> * to_timestamp('20096040','MMDD') works >> >> > I think the place for such warnings in addition to the source-code is in > the documentation. This or similar

Re: [GENERAL] WITH RECURSIVE doesn't work properly for me

Jing Fan wrote: > If the grouping inside CTE is executed, I don't think it would generate > result like > > src_id | dest_id | dist > +-+-- >3384 |6236 |1 >3384 |1739 |2 >3384 |6236 |3 >3384 |1739 |4 >3384 |6236 |5 >