[GENERAL] converting in() clause into a with prefix?

I have a horribly-performing query similar to below, and I'd like to convert it to use a "WITH mytable as ( ... ) " without having to re-architect my code. For some reason, using a WITH prefix seems to generally work much faster than IN() sub clause even allowing identical results. (runs in

Re: [GENERAL] converting in() clause into a with prefix?

On Fri, Oct 16, 2015 at 2:18 PM, Benjamin Smith wrote: > I have a horribly-performing query similar to below, and I'd like to > convert > it to use a "WITH mytable as ( ... ) " without having to re-architect my > code. > For some reason, using a WITH prefix seems to

Re: [GENERAL] question

On 10/16/2015 12:10 PM, anj patnaik wrote: Thanks. what is the recommended command/options for backup and how to restore? I found the below online. let me know if this is better and how to restore. Thank you pg_dump -Fc '' | xz -3 dump.xz Again, why would compress an already compressed

Re: [GENERAL] converting in() clause into a with prefix?

On Fri, Oct 16, 2015 at 11:18 AM, Benjamin Smith wrote: > I have a horribly-performing query similar to below, and I'd like to > convert > it to use a "WITH mytable as ( ... ) " without having to re-architect my > code. > For some reason, using a WITH prefix seems to

Re: [GENERAL] ID column naming convention

On 10/15/15 8:28 PM, Gavin Flower wrote: It would seem to be very dodgy to us a join based on apparently very different semantic implied by 'blah.person_id = foo.invoice_id'!!! :-) Because 2 fields in different tables have the same name, it does not necessarily mean they have the same

Re: [GENERAL] ID column naming convention

On Fri, Oct 16, 2015 at 02:28:25PM +1300, Gavin Flower wrote: > Since 'id' is only used to indicate a PRIMARY KEY, there is less confusion > in joins, and it is clear when something is a foreign key rather than a > PRIMARY KEY. Given that "id" often has meaning outside the database I much prefer

Re: [GENERAL] question

On Fri, Oct 16, 2015 at 8:27 AM, Guillaume Lelarge wrote: > 2015-10-15 23:05 GMT+02:00 Adrian Klaver : >> On 10/15/2015 01:35 PM, anj patnaik wrote: ... >>> ./pg_dump -t RECORDER -Fc postgres | gzip > /tmp/dump >>> Are there any other options

Re: [GENERAL] question

2015-10-15 23:05 GMT+02:00 Adrian Klaver : > On 10/15/2015 01:35 PM, anj patnaik wrote: > >> Hello all, >> I will experiment with -Fc (custom). The file is already growing very >> large. >> >> I am running this: >> ./pg_dump -t RECORDER -Fc postgres | gzip > /tmp/dump

Re: [GENERAL] pgpool ssl handshake failure

AI Rumman wrote: > I am using pgpool-II version 3.4.3 (tataraboshi). > Where my database is Postgresql 8.4. > > I am trying to configure ssl mode from client and between pgpool and database > it is non-ssl. > I configured as document and now I am getting this in my log: > > 2015-10-13

Re: [GENERAL] Standby pg_dump Conflict with Recovery

On 10/15/2015 03:30 PM, Louis Battuello wrote: On Oct 15, 2015, at 6:16 PM, Adrian Klaver > wrote: How did you set and temporarily enable the settings I changed the settings in the postgresql.conf file, restarted the standby

Re: [GENERAL] Standby pg_dump Conflict with Recovery

On 10/16/2015 07:50 AM, Louis Battuello wrote: Also in the standby logs before and after the ERROR? The primary log was clean. The standby contained the same error as the pg_dump output log: < 2015-10-15 01:10:50 EDT [42613] : [1-1] user=postgres,db=,remote=::1(55426) > ERROR: canceling

Re: [GENERAL] question

Hello all, I will experiment with -Fc (custom). The file is already growing very large. I am running this: ./pg_dump -t RECORDER -Fc postgres | gzip > /tmp/dump Are there any other options for large tables to run faster and occupy less disk space? Below is memory info: [root@onxl5179 tmp]#

Re: [GENERAL] Standby pg_dump Conflict with Recovery

> On Oct 16, 2015, at 9:35 AM, Adrian Klaver wrote: > > On 10/15/2015 03:30 PM, Louis Battuello wrote: >> >>> On Oct 15, 2015, at 6:16 PM, Adrian Klaver >> > wrote: >>> > >>> >>> How did you set and

Re: [GENERAL] converting in() clause into a with prefix?

On 10/16/15 1:18 PM, Benjamin Smith wrote: I have a horribly-performing query similar to below, and I'd like to convert it to use a "WITH mytable as ( ... ) " without having to re-architect my code. For some reason, using a WITH prefix seems to generally work much faster than IN() sub clause

Re: [GENERAL] postgres function

On 10/14/15 8:38 AM, Ramesh T wrote: Hi All, Do we have function like regexp_substr in postgres..? in oracle this function seach the - from 1 to 2 and return result, regexp_substr(PART_CATG_DESC,'[^-]+', 1, 2) Use regexp_split_to_array(string text, pattern text [, flags text ]):