Re: [GENERAL] Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

David G. Johnston November 13, 2015 at 10:48 PM ​ ​IN semantics w.r.t NULL can result in atrocious performance in some instances. I cannot speak to this one in particular but I'm curious if [...] WHERE setting_id = 1 AND setting_value = 'common_1' AND ( ow

Re: [GENERAL] Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

On Fri, Nov 13, 2015 at 11:25 PM, Steven Grimm wrote: > We want to find all the rows from multi_id where any of the IDs (including > its primary key) have a certain setting with a certain value. > > LATERAL seemed like the tool for the job, so we tried the following: > -

[GENERAL] Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

We have a table, call it "multi_id", that contains columns with IDs of various kinds of objects in my system, and another table that's a generic owner/key/value store for object attributes (think configuration settings, and I'll refer to this table as "settings"). To wit: -

[GENERAL] fast refresh materialized view

Does postgres has fast refresh materialized view that supports incremental refresh. In oracle , this is achieve by materialized view log. Not sure how to implement it in postgres. Confidentiality Notice:: This email, including attachments, may include non-public, proprietary, confidential

Re: [GENERAL] postgres sometimes returns no data

On 11/13/2015 09:56 AM, db042190 wrote: I looked at event viewer in both Application and System. Neither shows anything unusual around that time, just Volume Shadow Copy service stopping. Hmm, is something trying to run a backup over your Postgres data directory at the same time you are runni

Re: [GENERAL] postgres sometimes returns no data

ok, I poked around and see a reload config option in pg admin iii. I guess i'm ready to go. -- View this message in context: http://postgresql.nabble.com/postgres-sometimes-returns-no-data-tp5873720p5873846.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent

Re: [GENERAL] postgres sometimes returns no data

I looked at event viewer in both Application and System. Neither shows anything unusual around that time, just Volume Shadow Copy service stopping. -- View this message in context: http://postgresql.nabble.com/postgres-sometimes-returns-no-data-tp5873720p5873841.html Sent from the PostgreSQL -

Re: [GENERAL] postgres sometimes returns no data

run this on pg admin III to reload SELECT pg_reload_conf() ? Also, I now see the format for multiple log line prefixes...blank after each choice including last. I will try that once i'm comfortable with how I'll be reloading postgres. -- View this message in context: http://postgresq

Re: [GENERAL] postgres sometimes returns no data

On 11/13/2015 08:39 AM, db042190 wrote: currently, log line prefix is log_line_prefix = '%t '. Not sure what the blank following the t does. The blank is so this happens: 2015-11-10 00:55:11 EST HINT ^ It is a literal space to separate the log prefix from the log messa

Re: [GENERAL] postgres sometimes returns no data

a couple of these say cant be changed after session start. Is that the same as reload? Can I change it back if I do a reload? -- View this message in context: http://postgresql.nabble.com/postgres-sometimes-returns-no-data-tp5873720p5873832.html Sent from the PostgreSQL - general mailing list

Re: [GENERAL] postgres sometimes returns no data

currently, log line prefix is log_line_prefix = '%t '. Not sure what the blank following the t does. u probably want me to change it to log_line_prefix = '%t%a%u%d%r%h%p% ' or something like that? -- View this message in context: http://postgresql.nabble.com/postgres-sometimes-returns-no-data

Re: [GENERAL] postgres sometimes returns no data

On 11/13/2015 07:56 AM, db042190 wrote: pls remember that since the switches didn't run, this all (or mostly) could be related to an unknown process. My bad component appears to have started around 54 seconds into nov 10. The overall job appears to have ended around Actually 54 minutes, which

Re: [GENERAL] postgres sometimes returns no data

it looks like a lot of the log attributes ion the config are defaulting, probably to NOTICE. I see a # in front of many. If the community can advise on better choices and how bad the footprint would be for me to change these, pls let me know. I'm guessing the log excerpt I posted isn't telling u

Re: [GENERAL] postgres sometimes returns no data

pls remember that since the switches didn't run, this all (or mostly) could be related to an unknown process. My bad component appears to have started around 54 seconds into nov 10. The overall job appears to have ended around 01:01:44 which makes me wonder if a lot of the EOFs are related to som

Re: [GENERAL]

Hello, Il giorno ven, 13/11/2015 alle 13.38 +0800, Alex Luya ha scritto: > Hello, >        I created a new database by  >        create database icare; >        then quit off psql and  run: >         pg_restore --clean --create --exit-on-error --dbname=icare > icare-test.tar >          it complain

Re: [GENERAL]

On 11/13/2015 05:19 AM, Melvin Davidson wrote: It is very simple. If you have already created database icare then DO NOT use the -create flag! You can do that, you just cannot do that when the database you are connecting to, --dbname=icare, is the one you want to create. I use --dbname=postgr

Re: [GENERAL]

On 11/12/2015 09:38 PM, Alex Luya wrote: Hello, I created a new database by create database icare; then quit off psql and run: pg_restore --clean --create --exit-on-error --dbname=icare icare-test.tar Please show the pg_backup script that created icare-test.t

Re: [GENERAL] Array_to_json remove brackets

El 13/11/15 a las 10:49, Merlin Moncure escribió: On Fri, Nov 13, 2015 at 7:20 AM, Leonardo M. Ramé wrote: Hi, is there a way to get an array converted to json without brackets?. I'm getting, for example [{"field": "value"}, {"field": "value"}] and I want to get this: {"field": "value"}, {"fie

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

Thanks for all your quick replies! Turns out these are created by pg_repack and left in the database if pg_repack encounters a fatal error. In this case, the error was a hyphen in the database name which pg_repack can¹t handle. On 11/12/15, 5:38 PM, "Tom Lane" wrote: >Thomas Kellerer writ

Re: [GENERAL] Array_to_json remove brackets

On Fri, Nov 13, 2015 at 7:20 AM, Leonardo M. Ramé wrote: > Hi, is there a way to get an array converted to json without brackets?. > > I'm getting, for example [{"field": "value"}, {"field": "value"}] and I want > to get this: {"field": "value"}, {"field": "value"}. please supply an example. Als

[GENERAL] Array_to_json remove brackets

Hi, is there a way to get an array converted to json without brackets?. I'm getting, for example [{"field": "value"}, {"field": "value"}] and I want to get this: {"field": "value"}, {"field": "value"}. Regards, -- Leonardo M. Ramé http://leonardorame.blogspot.com -- Sent via pgsql-general m

Re: [GENERAL]

It is very simple. If you have already created database icare then DO NOT use the -create flag! http://www.postgresql.org/docs/9.2/interactive/app-pgrestore.html And as requested before, please provide the O/S and PostgreSQL version. On Fri, Nov 13, 2015 at 12:38 AM, Alex Luya wrote: > Hello,

Re: [GENERAL]

Who were you logged I to psql as? Does the dump switch users? On November 13, 2015 12:38:19 AM EST, Alex Luya wrote: >Hello, > I created a new database by > > create database icare; > > then quit off psql and run: > >pg_restore --clean --create --exit-on-error --dbname

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

Jeremy Harris wrote: > On 13/11/15 10:49, Thomas Kellerer wrote: >>> These indexes were *not* created by PostgreSQL. >>> We are not Oracle. >> >> Well, Oracle does not create indexes on its own either - it has the same >> strategy as Postgres: >> Indexes are only created automatically for primary

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

On 13/11/15 10:49, Thomas Kellerer wrote: >> These indexes were *not* created by PostgreSQL. >> We are not Oracle. > > Well, Oracle does not create indexes on its own either - it has the same > strategy as Postgres: > Indexes are only created automatically for primary keys and unique > constrain

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

Albe Laurenz schrieb am 13.11.2015 um 11:23: >> My questions is whether these “index_*” indexes could have been created by >> postgresql or whether I >> have an errant developer using some kinda third-party tool? > > These indexes were *not* created by PostgreSQL. > We are not Oracle. Well, Orac

Re: [GENERAL] can postgres run well on NFS mounted partitions?

John McKown wrote: > All of the above make we curious about using NFS for the data files, but > having the WAL files on a > local, perhaps SSD, device.​ I am not knowledgeable about WAL. Of course, I > don't know why the OP wants > to put the database files on an NFS. If the data file storage do

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

Doiron, Daniel wrote: > I’m troubleshooting a schema and found this: > > Indexes: > "pk_patient_diagnoses" PRIMARY KEY, btree (id) > "index_4341548" UNIQUE, btree (id) > "idx_patient_diagnoses_deleted" btree (deleted) > "idx_patient_diagnoses_diagnosis_type_id" btree (diagnosis_typ