Re: [GENERAL] (Might be a bug) locale issue while upgrading data directory from PostgreSQL 8.4 to 9.5

On 07/04/2017 10:23 PM, Mayank Agrawal wrote: Hello, I am upgrading PostgreSQL 8.4 to 9.5 on Windows. There is some issue related to locale. Details are as follows: The steps that I am following are: 1. Install PostgreSQL 9.5 (8.4 is already installed). 2. Migrate data from 8.4's data dir

Re: [GENERAL] Desired behavior for || (jsonb_concat)

On Thu, Jul 6, 2017 at 3:25 PM, Seamus Abshere wrote: > hi, > > # select '{"a":1}'::jsonb || null; > ?column? > -- > null > (1 row) > > Is there a theoretical reason that this has to return null as opposed to > just {"a":1}? > > Most operators in SQL, when given a

[GENERAL] Desired behavior for || (jsonb_concat)

hi, # select '{"a":1}'::jsonb || null; ?column? -- null (1 row) Is there a theoretical reason that this has to return null as opposed to just {"a":1}? Thanks, Seamus -- Seamus Abshere, SCEA https://github.com/seamusabshere https://linkedin.com/in/seamusabshere https://www.faraday.io

[GENERAL] psql doesn't pass on exported shell environment functions

In hindsight I'm not surprised but couldn't find a ready explanation on the web and figured I'd inquire here. In short: "export VAR" and "export -f functionname" behave differently when psql is acting as a relay. //main-script #!/usr/bin/env bash function testfunction() { echo "Function

Re: [GENERAL] Modified rows are not marked as dead and as such vacuum is unable to clean them up

=?UTF-8?B?VGFtw6FzIEFuZHLDoXMgS8OhbG3DoW4=?= writes: > we have a PostgreSQL 9.3.4 database with multiple large tables, that keep > growing. > Looking at the pg_stat of the front runner table that is currently about 1 > Gb big, it seems, that stale row data is not marked

[GENERAL] Modified rows are not marked as dead and as such vacuum is unable to clean them up

Hello Dear List, This is my first post here. we have a PostgreSQL 9.3.4 database with multiple large tables, that keep growing. Looking at the pg_stat of the front runner table that is currently about 1 Gb big, it seems, that stale row data is not marked as dead after updates, the number of

Re: [GENERAL] CREATE AGGREGATE on jsonb concat

> Seamus Abshere writes: > > We do this in our database: > > > CREATE AGGREGATE jsonb_collect(jsonb) ( > > SFUNC = 'jsonb_concat', > > STYPE = jsonb, > > INITCOND = '{}' > > ); > > > Is there some other built-in aggregate I'm missing that would do the > > same

Re: [GENERAL] CREATE AGGREGATE on jsonb concat

Seamus Abshere writes: > We do this in our database: > CREATE AGGREGATE jsonb_collect(jsonb) ( > SFUNC = 'jsonb_concat', > STYPE = jsonb, > INITCOND = '{}' > ); > Is there some other built-in aggregate I'm missing that would do the > same thing? It just feels

[GENERAL] huge RAM use in multi-command ALTER of table heirarchy

I've seen this before while doing SET STATISTICS on a larger number of columns using xargs, but just came up while doing ADD of a large number of columns. Seems to be roughly linear in number of children but superlinear WRT columns. I think having to do with catalog update / cache invalidation

Re: [GENERAL] Renaming Raster Table

On 07/06/2017 08:52 AM, Osahon Oduware wrote: Hi All, I used the raster2pgsql tool to load a raster with overviews into a PostGIS database using the following format: raster2pgsql -s -d -Y -e -I -C -F -M -l 2,4,8,16 /path/to/raster/file | psql -h -U postgres -p 5432 -d Now, *I want to

[GENERAL] CREATE AGGREGATE on jsonb concat

hi, We do this in our database: CREATE AGGREGATE jsonb_collect(jsonb) ( SFUNC = 'jsonb_concat', STYPE = jsonb, INITCOND = '{}' ); Is there some other built-in aggregate I'm missing that would do the same thing? It just feels like such an obvious feature. Thanks for your advice,

Re: [GENERAL] Renaming Raster Table

On Thu, Jul 6, 2017 at 8:52 AM, Osahon Oduware wrote: > Hi All, > > I used the raster2pgsql tool to load a raster with overviews into a > PostGIS database using the following format: > raster2pgsql -s -d -Y -e -I -C -F -M -l 2,4,8,16 > /path/to/raster/file | psql -h -U

[GENERAL] Renaming Raster Table

Hi All, I used the raster2pgsql tool to load a raster with overviews into a PostGIS database using the following format: raster2pgsql -s -d -Y -e -I -C -F -M -l 2,4,8,16 /path/to/raster/file | psql -h -U postgres -p 5432 -d Now, *I want to rename the raster table and I would like to know the

Re: [GENERAL] Trying to reduce xlog wal files

Exactly it!! I had changed that to 1000 also when we needed more. Reduced that and it fixed it immediately. Thank you!! On 07/06/2017 11:00 AM, Melvin Davidson wrote: On Thu, Jul 6, 2017 at 10:44 AM, Steve DeLong > wrote: I am

Re: [GENERAL] Trying to reduce xlog wal files

On Thu, Jul 6, 2017 at 10:44 AM, Steve DeLong wrote: > I am running Postgresql 9.3.15 and I am trying to reduce the amount of wal > files being recycled in pg_xlog. Archive is set up and working correctly. > A while ago we had problems with the streaming slave falling

[GENERAL] Trying to reduce xlog wal files

I am running Postgresql 9.3.15 and I am trying to reduce the amount of wal files being recycled in pg_xlog. Archive is set up and working correctly. A while ago we had problems with the streaming slave falling behind because of hardware being slower and tuned postgres to keep over 1000k wal

Re: [GENERAL] Is PL-PGSQL interpreted or complied?

Interesting, thank you. I was curious to know how it worked. Cheers.

[GENERAL] pg_rewind could not find previous WAL record at 0/4B000060: invalid record length at 0/4B000060: wanted 24, got 0

Hi, all I have 1 master and 2 slave PostgreSQL servers named e.g. server1(master), server2(slave) and server3(slave) OS: Ubuntu 16.04 Postgresql version is 9.6. Consider server1 down, then I touched a trigger file to server2, and then from server3 I run pg_rewind, But still get below errors: