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

[Adrian Klaver]

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 to 9.5's data dir using pg_upgrade 
utility.


3. After successful upgrade, uninstall PostgreSQL 8.4

Here the issue is that PostgreSQL 8.4 could be installed with any locale 
(English or French etc.) and to have successful data directory upgrade, 
PostgreSQL 9.5 must be installed with the same locale as that of 8.4.


Supplying the output of query 'Show lc_collate' (executed on PostgreSQL 
8.4) to PostgreSQL 9.5 installation leads to failed installation. It 
generates the error 'specified locale is not supported'.


Has the Windows version been upgraded between the install of 8.4 and the 
upgrade to 9.5?




Here is the example:

Output (PostgreSQL 8.4) of query 'SHOW LC_COLLATE': English_United 
States.1252


A. --locale "English_United States.1252"

While installing PostgreSQL 9.5, If we pass locale option as above, 
installer aborts with an error 'specified locale is not supported'.


B. --locale "English, United States"

If we pass locale option as above, installation is successful and 
cluster locale is set to 'English, United States'.


*Given that one has access to PostgreSQL 8.4, how would one know what 
value to pass to the parameter --locale while installing PostgreSQL 9.5?*


Thanks and Regards,

Mayank Agrawal




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

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 null as an operand, output null.  Many
of the underlying functions likewise are defined to be STRICT.  At this
point its fair to say its a long-standing convention.

Aggregation of a null has mixed results as to whether the null is processed
or ignored - the later typically in the math aggregations.

The following is a relatively easy work around for those who must handle
the possibility of nulls in their data.

​select '{"a":1}'::jsonb || COALESCE(null, '{}')::jsonb;
​
While most such expressions could be seen to have an obvious default
behavior when dealing with null SQL generally forces the user to make an
explicit declaration of intent and falls back to "unknown" if that is not
done.

David J.

[GENERAL] Desired behavior for || (jsonb_concat)

[Seamus Abshere]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

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 Test"
}
export TEST_ENVVAR='Test'
export -f testfunction

psql "service=postgres" < output.txt 2>&1

//output.txt

Enter
EnvVar: Test
Invoking Function...
./psql-call-bash: line 7: testfunction: command not found
Enter
EnvVar: Test
Invoking Function...
Function Test

I was really hoping the first output block would match the second,
specifically the "Function Test" line being present instead of
"./psql-call-bash: line 7: testfunction: command not found"

Apparently exported variables go someplace different than exported
functions :(

I don't suppose this is something that can be fixed in psql...

David J.

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

[Tom Lane]

=?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 as dead after updates,

Have you checked for uncommitted prepared transactions?
If "select * from pg_prepared_xacts" finds anything, that's
probably your problem.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tamás András Kálmán]

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 actual rows returned by count() is around 700k, but
n_live_tup has reached 10 million today (every day about 2/3 of the table
entries is updated and it reached this state after 2 weeks of running):

avmgmt=# select * from pg_stat_user_tables where relname = 'button';

relid |schemaname |  relname   | seq_scan | seq_tup_read | idx_scan
| idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd |
n_live_tup | n_dead_tup |  last_vac

uum  |last_autovacuum| last_analyze |
last_autoanalyze| vacuum_count | autovacuum_count | analyze_count |
autoanalyze_count

---+---++--+
--+--+---+---+---+--
-+---+++--

-+---+--
+---+--+
--+---+---

63500 | system_data | button |   30 | 38112282 | 14373411 |
235636001 |  4392 |  10838978 |  2259 |278851 |   10409289
| 549289 | 2017-07-06 00:01:

50.710945+02 | 2017-07-06 06:22:21.040109+02 |  | 2017-07-06
06:16:07.687727+02 |   16 |   31 | 0
|33

(1 row)


There are no active connections in transaction. Autovacuum is enabled and
there is a nightly vacuum cron job as well, but it doesn't have an effect
on these rows in n_live_tup, they just seems to keep accumulating.


Stopping the application and restarting PSQL followed up with a a FULL
VACUUM on these tables works, then it starts to grow again.


Let me know if you have any ideas why PostgreSQL is holding onto those
rows, if there is some particular piece of information, I can share it.


Many thanks for your help in advance!


Best,

Tamas Kalman.

Re: [GENERAL] CREATE AGGREGATE on jsonb concat

[Seamus Abshere]

> 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 like such an obvious feature.

> On Thu, Jul 6, 2017, at 04:53 PM, Tom Lane wrote:
> Doesn't jsonb_agg() do exactly that?

hi Tom,

That aggregates into an array. Our `jsonb_collect` aggregates into an
object.

Best,
Seamus


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] CREATE AGGREGATE on jsonb concat

[Tom Lane]

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 like such an obvious feature.

Doesn't jsonb_agg() do exactly that?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Justin Pryzby]

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 with many
ALTERs*children*columns?

32 cols and 2 children=> 12MB
256 cols and 11 children  => 74MB
256 cols and 111 children => 582MB
512 cols and 11 children  => 229MB

(in our "huge" case, there were ~1600 columns and maybe even more children)

I was testing with this command
PGHOST=/tmp PGPORT= sh -ec 'for maxcols in 512 ; do 
~/src/postgresql.install/bin/postgres -D ~/src/postgres.dat -c port= & 
sleep 4; cols=$(for d in `seq 1 $maxcols`; do echo "ADD c$d int,"; done 
|xargs); PGOPTIONS="-c client_min_messages=warning" psql postgres -qc "DROP 
TABLE t CASCADE" || [ $? -eq 1 ]; psql postgres -qc "CREATE TABLE t()"; for c 
in `seq 1 11`; do psql postgres -qc "CREATE TABLE c$c() INHERITS(t)"; done; for 
d in `seq 1 $maxcols`; do echo "ALTER TABLE t ADD c$d int;"; done 
|PGOPTIONS="-c client_min_messages=DEBUG3 -c log_statement_stats=on" psql 
postgres -c "ALTER TABLE t ${cols%,}" 2>/tmp/pg.err2; 
~/src/postgresql.install/bin/pg_ctl -swD ~/src/postgres.dat stop; done'

..and log_statment_stats with a variation on the getrusage patch here 
https://www.postgresql.org/message-id/20170615145824.GC15684%40telsasoft.com

Justin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Renaming Raster Table

[Adrian Klaver]

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 rename the raster table and I would like to know the 
affected tables and the required changes to be done without affecting 
the functionality of the raster/overviews*.




The docs seem to cover this:

http://postgis.net/docs/manual-2.2/using_raster_dataman.html#RT_Raster_Loader

"-l OVERVIEW_FACTOR

Create overview of the raster. For more than one factor, separate 
with comma(,). Overview table name follows the pattern o_overview 
factor_table, where overview factor is a placeholder for numerical 
overview factor and table is replaced with the base table name. Created 
overview is stored in the database and is not affected by -R. Note that 
your generated sql file will contain both the main table and overview 
tables."


In addition it is going to produce an SQL file that contains the changes 
for you to look at.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] CREATE AGGREGATE on jsonb concat

[Seamus Abshere]

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,
Seamus

--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
https://www.faraday.io


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Renaming Raster Table

[David G. Johnston]

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 postgres -p
> 5432 -d 
>
> Now, *I want to rename the raster table and I would like to know the
> affected tables and the required changes to be done without affecting the
> functionality of the raster/overviews*.
>

​While you may get a response here the PostGIS project appears to host its
own mailing lists and you might find you get a faster/better response if
you ask this very product specific question over there.

David J.

[GENERAL] Renaming Raster Table

[Osahon Oduware]

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
affected tables and the required changes to be done without affecting the
functionality of the raster/overviews*.

Re: [GENERAL] Trying to reduce xlog wal files

[Steve DeLong]

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 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 files so it could catch
up without us manually moving files from the backup server.  We
have since upgraded the slave so it never falls behind and would
like to reduce the wals files kept in pg_xlog.  I have updated
postgresql.conf checkpoint_segments from 1000 to 250 and
checkpoint_timeout from 16min to 5min.  I ran SELECT
pg_reload_conf(); and using pg_controldata I have confirmed the
settings and checkpoints are happening at 5 min intervals.
However, file count in pg_xlog stays at 1152 and never changes.
How can I get this to reduce the file count?

Thanks



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



*>I have updated postgresql.conf checkpoint_segments from 1000 to 250
*
*But what is the value of wal_keep_segments?
*

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Trying to reduce xlog wal files

[Melvin Davidson]

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 behind because
> of hardware being slower and tuned postgres to keep over 1000k wal files so
> it could catch up without us manually moving files from the backup server.
> We have since upgraded the slave so it never falls behind and would like to
> reduce the wals files kept in pg_xlog.  I have updated postgresql.conf
> checkpoint_segments from 1000 to 250 and checkpoint_timeout from 16min to
> 5min.  I ran SELECT pg_reload_conf(); and using pg_controldata I have
> confirmed the settings and checkpoints are happening at 5 min intervals.
> However, file count in pg_xlog stays at 1152 and never changes. How can I
> get this to reduce the file count?
>
> Thanks
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


*>I have updated postgresql.conf checkpoint_segments from 1000 to 250*

*But what is the value of wal_keep_segments?*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Trying to reduce xlog wal files

[Steve DeLong]

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 files so it could catch up without us manually moving files 
from the backup server.  We have since upgraded the slave so it never 
falls behind and would like to reduce the wals files kept in pg_xlog.  I 
have updated postgresql.conf checkpoint_segments from 1000 to 250 and 
checkpoint_timeout from 16min to 5min.  I ran SELECT pg_reload_conf(); 
and using pg_controldata I have confirmed the settings and checkpoints 
are happening at 5 min intervals. However, file count in pg_xlog stays 
at 1152 and never changes. How can I get this to reduce the file count?


Thanks



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tim Uckun]

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

[Yong Zhang]

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:

root@server3#: systemctl stop postgresql@9.6-main.service
root@server3#: runuser -s /bin/bash postgres -c 
'/usr/lib/postgresql/9.6/bin/pg_rewind --target-pgdata 
/var/lib/postgresql/9.6/main/ --source-server="host=10.10.4.7 port=5432 
user=postgres dbname=postgres"'
servers diverged at WAL position 0/4B60 on timeline 17

could not find previous WAL record at 0/4B60: invalid record length at 
0/4B60: wanted 24, got 0
Failure, exiting

What is wrong here? Thanks.