Re: [GENERAL] FIRST_VALUE: need to group by argument?

On Mon, May 23, 2016 at 11:48 PM, Guyren Howe wrote: > I am missing something here. > > I have two tables: > > orders > id > > delivery_route_segments > id, > order_id, > position, > completed > > I want to find the first uncompleted deliver_route_segment for each order, > by position. Seems to m

[GENERAL] Members in the Middle East?

Hi, Do we have folks in this group based out of the Middle East, preferably in UAE? We currently don't have a user group in the GCC region and I would like to help start one up. Thanks! - Umair

[GENERAL] 9.5 regression with unwanted nested loop left join

We are trying to upgrade a client app to Postgres 9.5, but are running into some performance regression issues (even though the curent db is 8.x!). One in particular that is puzzling me involves a query that keeps slipping into a nested loop left join, rather than a much preferred hash join. Th

Re: [GENERAL] 9.5 regression with unwanted nested loop left join

Greg Sabino Mullane writes: > We are trying to upgrade a client app to Postgres 9.5, but are running > into some performance regression issues (even though the curent db is 8.x!). > One in particular that is puzzling me involves a query that keeps slipping > into a nested loop left join, rather

Re: [GENERAL] pg_upgrade error regarding hstore operator

Hello, All I have the same issue after pg_upgrade from 9.3 to 9.5. pg_dump generates excess commands like CREATE OPERATOR FAMILY bit_ops USING gin; ... while all of this is done during CREATE EXTENSION (i have only btree_gin and plpgsql installed) -- View this message in context: http://pos

[GENERAL] ERROR: MultiXactId xxx has not been created yet

We have a database (PostgreSQL 9.3.10) which is reporting this error on a TOAST table on a VACUUM. Is there a canonical way of repairing this? The table is *huge*, so a VACUUM FULL or pg_dump / pg_restore is probably not going to work. -- -- Christophe Pettus x...@thebuild.com -- Sent v

Re: [GENERAL] ERROR: MultiXactId xxx has not been created yet

Christophe Pettus wrote: > We have a database (PostgreSQL 9.3.10) which is reporting this error on a > TOAST table on a VACUUM. Is there a canonical way of repairing this? The > table is *huge*, so a VACUUM FULL or pg_dump / pg_restore is probably not > going to work. I don't think toast tabl

Re: [GENERAL] FIRST_VALUE: need to group by argument?

On Tue, May 24, 2016 at 8:50 AM, David G. Johnston wrote: > SELECT x, sum(i), sum(sum(i)) OVER (PARTITION BY x) FROM ( VALUES (a,1), > (a,2), (b,3) ) val (x,i) GROUP BY x > yields > a, 3, 6 > b, 3, 6 Thank you for this enlightening explanation! I was, however, very confused from this specific bi

Re: [GENERAL] pg_upgrade error regarding hstore operator

parihaaraka writes: > I have the same issue after pg_upgrade from 9.3 to 9.5. > pg_dump generates excess commands like > CREATE OPERATOR FAMILY bit_ops USING gin; > ... > while all of this is done during CREATE EXTENSION This is fixed in the latest round of minor releases, but not in a way th

Re: [GENERAL] FIRST_VALUE: need to group by argument?

On Tue, May 24, 2016 at 12:12 PM, Manuel Gómez wrote: > On Tue, May 24, 2016 at 8:50 AM, David G. Johnston > wrote: > > SELECT x, sum(i), sum(sum(i)) OVER (PARTITION BY x) FROM ( VALUES (a,1), > > (a,2), (b,3) ) val (x,i) GROUP BY x > > yields > > a, 3, 6 > > b, 3, 6 > > Thank you for this enlig

Re: [GENERAL] Members in the Middle East?

On 25/05/16 02:18, Umair Shahid wrote: Hi, Do we have folks in this group based out of the Middle East, preferably in UAE? We currently don't have a user group in the GCC region and I would like to help start one up. Thanks! - Umair All the best! What does 'GCC' stand for? My first thou

Re: [GENERAL] Members in the Middle East?

On May 24, 2016, at 1:16 PM, Gavin Flower wrote: > What does 'GCC' stand for? Gulf Cooperative Council. :) https://en.wikipedia.org/wiki/Gulf_Cooperation_Council -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chan

Re: [GENERAL] 9.5 regression with unwanted nested loop left join

On Tue, May 24, 2016 at 11:19:04AM -0400, Tom Lane wrote: > 8.4 avoids this trap only because it doesn't consider injecting a > materialize there. > > So a brute-force fix to restore the pre-9.0 behavior would be > "set enable_material = off". But really the problem is that it's > unobvious that

[GENERAL] SPI returns error for SAVEPOINT

A plandl (language handler for Andl) function is called as follows: BEGIN; SELECT plandl_compile($1); // argument is Andl code COMMIT; Inside: >SPI_exec("BEGIN",...) returns error SPI_ERROR_TRANSACTION. As expected. >SPI_exec("SAVEPOINT xyz",...) returns error SPI_ERROR_TRANSAC

Re: [GENERAL] SPI returns error for SAVEPOINT

On Tue, May 24, 2016 at 9:55 PM, dandl wrote: > A plandl (language handler for Andl) function is called as follows: > > BEGIN; > SELECT plandl_compile($1); // argument is Andl code > COMMIT; > > Inside: > >>SPI_exec("BEGIN",...) returns error SPI_ERROR_TRANSACTION. As expected. >

Re: [GENERAL] SPI returns error for SAVEPOINT

> >>SPI_exec("BEGIN",...) returns error SPI_ERROR_TRANSACTION. As expected. > >>SPI_exec("SAVEPOINT xyz",...) returns error SPI_ERROR_TRANSACTION. Not > > expected. > > The docs say that this is expected: > https://www.postgresql.org/docs/devel/static/spi-spi-execute.html > SPI_ERROR_TRANSACTION >

Re: [GENERAL] Members in the Middle East?

On Wed, May 25, 2016 at 1:16 AM, Gavin Flower wrote: > On 25/05/16 02:18, Umair Shahid wrote: > >> Hi, >> >> Do we have folks in this group based out of the Middle East, preferably >> in UAE? We currently don't have a user group in the GCC region and I would >> like to help start one up. >> >> Th