Re: [GENERAL] Stored procedure version control

On 07/01/2016 06:17 PM, Jim Nasby wrote: On 6/30/16 9:16 AM, Merlin Moncure wrote: It's not really necessary to create version down scripts. In five years of managing complex database environments we've never had to roll a version back and likely never will; in the event of a disaster it's

Re: [GENERAL] Stored procedure version control

On 6/30/16 9:16 AM, Merlin Moncure wrote: It's not really necessary to create version down scripts. In five years of managing complex database environments we've never had to roll a version back and likely never will; in the event of a disaster it's probably better to restore from backup

Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

OK. Will change our query generation code to not use the view. (I have tried the LEFT JOIN approach, but it just does not seem to perform.) Thanks, Igor PS: Here are the numbers for the real production query (will not provide details): Original query: 300s Query on a

Re: [GENERAL] How safe is pg_basebackup + continuous archiving?

On 6/30/16 8:30 AM, Stephen Frost wrote: > How can we check for backup corruption in this case? Thanks you very much. There has been some discussion about a specific tool for checking the checksums throughout the entire system. I don't know of anyone activly working on that, unfortunately.

Re: [GENERAL] 9.6 beta2 win-x64 download links still point to beta1

On Fri, Jul 1, 2016 at 10:12:46PM +0200, Thomas Kellerer wrote: > Bruce Momjian schrieb am 28.06.2016 um 05:36: > >On Fri, Jun 24, 2016 at 07:44:17AM +0200, Thomas Kellerer wrote: > >>the Beta2 downloads on > >> > >> http://www.enterprisedb.com/products-services-training/pgdownload > >>

Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

On Fri, Jul 1, 2016 at 3:33 PM, Sfiligoi, Igor wrote: > No, I don't want to use LEFT JOINS. > I want to use regular joins. > > But (as mentioned in my other follow-up), all the fields are not > null (was not in the original email, sorry), and are foreign > keys, so it is

Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

Nope, no difference how I express the joins: create view v1 as select c.id, c.a_id, c.b1_id, c.b2_id , c.b3_id, a.name a_name, b1.name b1_name, b2.name b2_name, b3.name b3_name from c join a on (c.a_id=a.id) join b b1 on (c.b1_id=b1.id) join b b2 on (c.b2_id=b2.id) join b b3 on

Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

On 07/01/2016 01:28 PM, Sfiligoi, Igor wrote: Sorry... the example was incomplete. All the fields are defined as not-null. So it is guaranteed to always match the join. And PostgreSQL release notes claim that PGSQL can do at least partial join removal:

Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

No, I don't want to use LEFT JOINS. I want to use regular joins. But (as mentioned in my other follow-up), all the fields are not null (was not in the original email, sorry), and are foreign keys, so it is guaranteed to always match. The key part (in my mind) is that I am not filtering on any

Re: [GENERAL] PSQL does not remove obvious useless joins

On Fri, Jul 1, 2016 at 12:17 PM, Sfiligoi, Igor wrote: > We have a view that is very generic, and we noticed that PostgreSQL is not > very good at removing useless joins, which makes our queries very slow. > > We could change our code to avoid the view and write ad-hoc

Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

Sorry... the example was incomplete. All the fields are defined as not-null. So it is guaranteed to always match the join. And PostgreSQL release notes claim that PGSQL can do at least partial join removal: https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.0#Join_Removal I was

Re: [GENERAL] 9.6 beta2 win-x64 download links still point to beta1

Bruce Momjian schrieb am 28.06.2016 um 05:36: On Fri, Jun 24, 2016 at 07:44:17AM +0200, Thomas Kellerer wrote: the Beta2 downloads on http://www.enterprisedb.com/products-services-training/pgdownload http://www.enterprisedb.com/products-services-training/pgbindownload still lead to

Re: [GENERAL] PSQL does not remove obvious useless joins

On Fri, Jul 1, 2016 at 12:17 PM, Sfiligoi, Igor wrote: > Hello. > > We have a view that is very generic, and we noticed that PostgreSQL is not > very good at removing useless joins, which makes our queries very slow. > > We could change our code to avoid the view and write

Re: [GENERAL] Update multiple rows in a table with different values

Hi Tom, Thanks for your suggestion. I got it working: CREATE OR REPLACE FUNCTION s.updatefunc1(BigInt[], BigInt[]) RETURNS void as $$ BEGIN FOR i IN array_lower($1, 1) .. array_upper($1, 1) LOOP update s.t1 SET c3 = $2[i] WHERE c2 = $1[i]; END LOOP; END; $$

[GENERAL] PSQL does not remove obvious useless joins

Hello. We have a view that is very generic, and we noticed that PostgreSQL is not very good at removing useless joins, which makes our queries very slow. We could change our code to avoid the view and write ad-hoc queries to the underlying tables, but would prefer not to, if there is a way

Re: [GENERAL] Log archiving failing. Seems to be wrong timeline

Hi Jeff, Done as you advised and now things are working again. Many thanks Chris On 30/06/16 20:19, Jeff Janes wrote: On Thu, Jun 30, 2016 at 3:53 AM, Chris Lewis wrote: Hello, We have 2 postgresql servers (v 9.4.2) master and slave in streaming replication. The

Re: [GENERAL] Update multiple rows in a table with different values

shankha writes: > PREPARE updatearrayplan(BigInt[], BigInt[]) AS > for i in size($1) > DO > update s.t1 > SET c3 = $2[$i] > WHERE c2 = $1[$i] > END FOR > In this prepared statement I am just trying to explain

Re: [GENERAL] Update multiple rows in a table with different values

Hi Adrian, I am using Postgres version 9.3. PREPARE updatearrayplan(BigInt[], BigInt[]) AS for i in size($1) DO update s.t1 SET c3 = $2[$i] WHERE c2 = $1[$i] END FOR In this prepared statement I am just trying to explain the algorithm.

Re: [GENERAL] Update multiple rows in a table with different values

On Fri, Jul 1, 2016 at 10:26 AM, shankha wrote: > > PREPARE updatearrayplan(BigInt[], BigInt[]) AS > for i in size($1) > DO > update s.t1 > SET c3 = $2[$i] > WHERE c2 = $1[$i] > END FOR > > EXECUTE

Re: [GENERAL] Update multiple rows in a table with different values

On 07/01/2016 07:26 AM, shankha wrote: Greetings, I have the following schema: CREATE TABLE "s"."t1" ( "c1" BigSerial PRIMARY KEY, "c2" BigInt NOT NULL, "c3" BigInt ) WITH (OIDS=FALSE); Unless you have a very old version of Postgres, OIDS=FALSE is the

[GENERAL] Update multiple rows in a table with different values

Greetings, I have the following schema: CREATE TABLE "s"."t1" ( "c1" BigSerial PRIMARY KEY, "c2" BigInt NOT NULL, "c3" BigInt ) WITH (OIDS=FALSE); INSERT INTO s.t1 (c2, c3) VALUES (10, 100); INSERT INTO s.t1 (c2, c3) VALUES (20, 200); INSERT

Re: [GENERAL] table name size

Sridhar N Bamandlapally writes: > Is there any specific reason not releasing any version with "NAMEDATALEN 255" > in file src/include/pg_config_manual.h ? It would bloat the system catalogs by a rather substantial amount, as well as in-memory images of those catalogs

Re: [GENERAL] Sub-query having NULL row returning FALSE result

Hi Just for info. Actual query in Oracle (below) -- MERGE INTO relDocumentTypeMetaDataName t USING (SELECT ? as DocumentTypeID, ? as DocumentContextID, ? as MetaDataNameID, ? as DocumentAbstractionIndexID FROM DUAL) s ON (t.MetaDataNameID = s.MetaDataNameID AND

Re: [GENERAL] Replication with non-read-only standby.

Il 01/07/2016 05:21, Venkata Balaji N ha scritto: On Thu, Jun 30, 2016 at 11:15 PM, Nick Babadzhanian wrote: Setup: 2 PostgreSQL servers are geographically spread. The first one

Re: [GENERAL] table name size

Hi Is there any specific reason not releasing any version with "NAMEDATALEN 255" in file src/include/pg_config_manual.h ? this will really nice if we do with "NAMEDATALEN 255" src/include/pg_config_ manual.h Please Thanks Sridhar OpenText On Mon, Jun 13, 2016 at 5:40 PM, David G. Johnston <