Re: [GENERAL] Changing collate & ctype for an existing database

[rihad]

On 07/12/2017 11:25 PM, Tom Lane wrote:

rihad  writes:

What if only English letters are used in the textual indices (ascii
0-127), would they still be impacted after datctype
"C"->"en_US.UTF-8" change?

Yes, as even minimal testing would have told you.  C sort order is
more case-sensitive, for instance.

regards, tom lane
.

Thanks. It would be great if initdb didn't assume an implicit encoding, 
to prevent such fundamental configuration mistakes in the future. More 
often than not collation/ctype settings of an ssh login session used to 
run initdb aren't what must be used to set up the cluster. It'd be great 
if initdb didn't go any further if not provided with an explicit 
encoding. The error message would require the user to think twice before 
proceeding, and to read up on the matter. Explicit is better than 
implicit, as the old saying goes :)




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

[GENERAL] Monitoring of a hot standby with a largely idle master

[Jeff Janes]

I think that none of the recovery information functions (
https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE)
can distinguish a hot standby which is connected to an idle master, versus
one which is disconnected.  For example, because the master has crashed, or
someone has changed the firewall rules.

Is there a way to monitor from SQL the last time the standby was able to
contact the master and initiate streaming with it?  Other than trying to
write a function that parses it out of pg_log?

Cheers,

Jeff

Re: [GENERAL] Changing collate & ctype for an existing database

[Tom Lane]

rihad  writes:
> What if only English letters are used in the textual indices (ascii 
> 0-127), would they still be impacted after datctype 
> "C"->"en_US.UTF-8" change?

Yes, as even minimal testing would have told you.  C sort order is
more case-sensitive, for instance.

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

Re: [GENERAL] BDR node removal and rejoin

[Zhu, Joshua]

Thanks for the clarification.

Looks like I am running into a different issue: while trying to pin down 
precisely the steps (and the order in which to perform them) needed to 
remove/rejoin a node, the removal/rejoining exercise was repeated a number of 
times, and stuck again:


  1.  The status of the re-joining node (node4) on other nodes is “I”
  2.  The status of the re-joining node on the node4 itself started at “I”, 
changed to “o”, then stuck there
  3.  From the log file for node4, the following entries are constantly being 
generated:

2017-07-12 10:37:46 PDT [24943:bdr 
(6334686800251932108,1,43865,):receive:::1(33883)]DEBUG:  0: received 
replication command: IDENTIFY_SYSTEM
2017-07-12 10:37:46 PDT [24943:bdr 
(6334686800251932108,1,43865,):receive:::1(33883)]LOCATION:  
exec_replication_command, walsender.c:1309
2017-07-12 10:37:46 PDT [24943:bdr 
(6334686800251932108,1,43865,):receive:::1(33883)]DEBUG:  08003: unexpected EOF 
on client connection
2017-07-12 10:37:46 PDT [24943:bdr 
(6334686800251932108,1,43865,):receive:::1(33883)]LOCATION:  SocketBackend, 
postgres.c:355
2017-07-12 10:37:46 PDT [24944:bdr 
(6408408103171110238,1,24713,):receive:::1(33884)]DEBUG:  0: received 
replication command: IDENTIFY_SYSTEM
2017-07-12 10:37:46 PDT [24944:bdr 
(6408408103171110238,1,24713,):receive:::1(33884)]LOCATION:  
exec_replication_command, walsender.c:1309
2017-07-12 10:37:46 PDT [24944:bdr 
(6408408103171110238,1,24713,):receive:::1(33884)]DEBUG:  08003: unexpected EOF 
on client connection
2017-07-12 10:37:46 PDT [24944:bdr 
(6408408103171110238,1,24713,):receive:::1(33884)]LOCATION:  SocketBackend, 
postgres.c:355
2017-07-12 10:37:46 PDT [24946:bdr 
(6334686760735153516,1,43845,):receive:::1(33885)]DEBUG:  0: received 
replication command: IDENTIFY_SYSTEM
2017-07-12 10:37:46 PDT [24946:bdr 
(6334686760735153516,1,43845,):receive:::1(33885)]LOCATION:  
exec_replication_command, walsender.c:1309
2017-07-12 10:37:46 PDT [24946:bdr 
(6334686760735153516,1,43845,):receive:::1(33885)]DEBUG:  08003: unexpected EOF 
on client connection
2017-07-12 10:37:46 PDT [24946:bdr 
(6334686760735153516,1,43845,):receive:::1(33885)]LOCATION:  SocketBackend, 
postgres.c:355
2017-07-12 10:37:49 PDT [24949:bdr 
(6394432535408825526,1,37325,):receive:::1(33892)]DEBUG:  0: received 
replication command: IDENTIFY_SYSTEM
2017-07-12 10:37:49 PDT [24949:bdr 
(6394432535408825526,1,37325,):receive:::1(33892)]LOCATION:  
exec_replication_command, walsender.c:1309
2017-07-12 10:37:49 PDT [24949:bdr 
(6394432535408825526,1,37325,):receive:::1(33892)]DEBUG:  08003: unexpected EOF 
on client connection
2017-07-12 10:37:49 PDT [24949:bdr 
(6394432535408825526,1,37325,):receive:::1(33892)]LOCATION:  SocketBackend, 
postgres.c:355

What do these entries say? and what can be done to correct the situation (there 
have been no change with respect to either postgres  or network configuration 
in the remove/rejoin exercise)?

Thanks

From: Craig Ringer [mailto:cr...@2ndquadrant.com]
Sent: Wednesday, July 12, 2017 1:59 AM
To: Zhu, Joshua 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR node removal and rejoin

On 11 July 2017 at 05:49, Zhu, Joshua 
> wrote:
An update… after manually removing the record for ‘node4’ from bdr.bdr_nodes, 
corresponding record in bdr.bdr_connections, and associated replication slot 
(with pg_drop_replication_slot), rejoining was successful.

I was under the impression that there is no need to perform manual cleanup 
before a removed node (with database dropped and recreated) rejoining a BDR 
group.

BDR1 requires that you manually remove the bdr.bdr_nodes entry if you intend to 
re-use the same node name.


--
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] Changing collate & ctype for an existing database

[rihad]

On 07/12/2017 09:31 PM, Tom Lane wrote:

rihad  writes:

On 07/12/2017 01:54 PM, Albe Laurenz wrote:

As you see, your index is still sorted according to the C collation
and scanning it returns wrong results.

This ordering issue can certainly be classified as an inconsistency, but
nothing to lose sleep over. Is this all that is normally meant when
saying "index corruption"?

Laurenz neglected to point out that if the index isn't sorted the way that
the system assumes it is, then searches may fail to find values that are
present (due to descending into the wrong subtree), and by the same token
insertions may fail to enforce uniqueness.  That's pretty corrupt in
my book.

regards, tom lane

What if only English letters are used in the textual indices (ascii 
0-127), would they still be impacted after datctype 
"C"->"en_US.UTF-8" change? Encoding has always been UTF8, btw.



postgres=# \l
  List of databases
   Name|  Owner   | Encoding |   Collate   |Ctype| Access 
privileges

---+--+--+-+-+---
 mydb| myuser   | UTF8 | C   | C   |



--
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] Changing collate & ctype for an existing database

[rihad]

On 07/12/2017 09:31 PM, Tom Lane wrote:

rihad  writes:

On 07/12/2017 01:54 PM, Albe Laurenz wrote:

As you see, your index is still sorted according to the C collation
and scanning it returns wrong results.

This ordering issue can certainly be classified as an inconsistency, but
nothing to lose sleep over. Is this all that is normally meant when
saying "index corruption"?

Laurenz neglected to point out that if the index isn't sorted the way that
the system assumes it is, then searches may fail to find values that are
present (due to descending into the wrong subtree), and by the same token
insertions may fail to enforce uniqueness.  That's pretty corrupt in
my book.

regards, tom lane


Wow. It sure is.



--
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] Changing collate & ctype for an existing database

[Tom Lane]

rihad  writes:
> On 07/12/2017 01:54 PM, Albe Laurenz wrote:
>> As you see, your index is still sorted according to the C collation
>> and scanning it returns wrong results.

> This ordering issue can certainly be classified as an inconsistency, but 
> nothing to lose sleep over. Is this all that is normally meant when 
> saying "index corruption"?

Laurenz neglected to point out that if the index isn't sorted the way that
the system assumes it is, then searches may fail to find values that are
present (due to descending into the wrong subtree), and by the same token
insertions may fail to enforce uniqueness.  That's pretty corrupt in
my book.

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

Re: [GENERAL] Changing collate & ctype for an existing database

[rihad]

On 07/12/2017 01:54 PM, Albe Laurenz wrote:

rihad wrote:

Hi there. We have a working database that was unfortunately created by
initdb with default ("C") collation & ctype. All other locale specific
settings have the value en_US.UTF-8 in postgresql.conf. The database
itself is multilingual and all its data is stored in UTF-8. Sorting
doesn't work correctly, though. To fix that, can I just do this:


update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
where datname='mydb';


This does seem to work on a testing copy of the database, i.e. select
lower('БлаБлаБла') now works correctly when connected to that database.


Is there still any chance for corrupting data by doing this, or indexes
stopping working etc?

p.s.: postgres 9.6.3

As explained, yes.  Indexes on string columns will be corrupted.

See this example:

test=# CREATE DATABASE breakme LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
test=# \c breakme
breakme=# CREATE TABLE sort(id integer PRIMARY KEY, val text NOT NULL);
breakme=# INSERT INTO sort VALUES (1, 'LITTLE'), (2, 'big'), (3, 'b-less');
breakme=# CREATE INDEX ON sort(val);
breakme=# SET enable_seqscan=off;  -- force index use
breakme=# SELECT * FROM sort ORDER BY val;
┌┬┐
│ id │  val   │
├┼┤
│  1 │ LITTLE │
│  3 │ b-less │
│  2 │ big│
└┴┘
(3 rows)

breakme=# UPDATE pg_database SET datcollate='en_US.UTF-8', 
datctype='en_US.UTF-8' WHERE datname='breakme';
breakme=# \c breakme
breakme=# SET enable_seqscan=off;  -- force index use
breakme=# SELECT * FROM sort ORDER BY val;
┌┬┐
│ id │  val   │
├┼┤
│  1 │ LITTLE │
│  3 │ b-less │
│  2 │ big│
└┴┘
(3 rows)

breakme=# SET enable_seqscan=on;  -- this and the following force sequential 
scan
breakme=# SET enable_bitmapscan=off;
breakme=# SET enable_indexscan=off;
breakme=# SET enable_indexonlyscan=off;
breakme=# SELECT * FROM sort ORDER BY val;  -- this returns the correct order
┌┬┐
│ id │  val   │
├┼┤
│  2 │ big│
│  3 │ b-less │
│  1 │ LITTLE │
└┴┘
(3 rows)

As you see, your index is still sorted according to the C collation
and scanning it returns wrong results.

Yours,
Laurenz Albe


This ordering issue can certainly be classified as an inconsistency, but 
nothing to lose sleep over. Is this all that is normally meant when 
saying "index corruption"? What about updating or deleting the wrong row 
addressed by the textual index that hasn't been rebuilt after 
datcollate/datctype change, complete table/database corruption, or other 
scary night-time stories of this kind? Possible?




--
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] Please say it isn't so

[Christopher Browne]

On 12 July 2017 at 00:51, Steve Litt  wrote:
> Hi all,
>
> Please tell me this is a mistake:
>
> https://wiki.postgresql.org/wiki/Systemd
>
> Why a database system should care about how processes get started is
> beyond me. Systemd is an entangled mess that every year subsumes more
> and more of the operating system, in a very non-cooperative way.
>
> There are almost ten init systems. In every one of those init systems,
> one can run a process supervisor, such as runit or s6 or
> daemontools-encore, completely capable of starting the postgres server.
>
> Every year, systemd further hinders interoperability, further erodes
> interchangeability of parts, and continues to address problems with
> WONTFIX. In the long run, you do your users no favor by including
> init-system specific code in Postgres or its makefiles. If systemd
> can't correctly start Postgres, I guarantee you that s6 or runit,
> running on top of systemd, can.
>
> Postgres doesn't care which language makes a query to it. Why
> should Postgres care which init system started it? I hope you can free
> Postgres of init-specific code, and if for some reason you can't do
> that, at least don't recommend init-specific code.

I'm did a bit of review work on the relevant change, once upon a time...

The change was not to "make PostgreSQL forcibly dependent on
SystemD"; since PostgreSQL is expected to run properly on systems
that SystemD does not support, it should be obvious that to do
that would be horribly inappropriate.  Only an [insert insult here]
would suggest that, right???

What was added was entirely *optional*.

On systems where one is using SystemD, PostgreSQL can do a
couple of bits of integration that actually are somewhat useful.

a) At startup, PostgreSQL will notify SystemD when it is completed
with startup processes (e.g. - the recovery that may take extra
time if you had an unclean shutdown).

This is useful in that it means SystemD knows when PostgreSQL
is actually ready to accept user access.

It can defer startup of dependent services.

That dependency management is Actually Useful, and is one of the
things that is useful about having something smarter than Good Old
SysVInit.  It means that if you had an unclean system shutdown,
meaning that it takes longer for PostgreSQL to start up on the next
boot, this doesn't lead to a cascade of service failures.

I'm not particularly thrilled with SystemD; it has a habit of making
its scope creep.  But the above was one of the legitimate cases
where "a better init" is actually fairly desirable, where traditional
sysV init was pretty creaky.

b) There's some "manage logs using SystemD's logging"
integration.

I'm not as "gruntled" about that, but if someone wants to centralize
their logging, that's their choice, and it's a reasonable thing to desire.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


-- 
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] DATA Integrity & Recovery

[stevenchang1213]

I just finished a point in time  recovery  from a rsync physical data file 
backup + wal archive.Of course it works!You can also achieve the same goal 
through pg_basebackup.
By your descriptions, your vendor seems to use logical backup through pg_dump.
Anyway you can only achieve no data loss through physical backup. 
Check your contract with your vendor,  file a suie if penalty term exists.
steven 
 原始訊息 自： chris faber  日期： 2017/7/12  22:27 
 (GMT+08:00) 至： pgsql-general@postgresql.org 主旨： [GENERAL] DATA Integrity & 
Recovery 
Postgres 9.2
We have a POSTGRES database that we have been backing up via Incremental 
backups.
We had an incident where we had to recover from backup. Our software vendor has 
completed a restore and we have lost 10 days of data. There is no explanation 
as to the reason we have sustained this loss.
I would appreciate the communities help in the following:
1. Determine if data from the incremental backups can be restored or 
recovered.2. Determine if data can be recovered from individual files backed up 
from main Postgres data directory.
Thanks in advanceChris

[GENERAL] Manage slot in logical/pglogical replication

[dpat]

Hi,
i have configure a master-replica replication with new pglogical 2.0.
I have to replicate data over MPLS/VPN, so there is a possibility that the
link temporarily interrupts.
I know that you have to be accurately estimated pg_xlog folder.
How can I handle the prolonged interruption of the link?
Can I just extend the folder? Or drop the slot?
I can create a consumer who temporarily saves the data in an external file?
thank you so much
Denis




--
View this message in context: 
http://www.postgresql-archive.org/Manage-slot-in-logical-pglogical-replication-tp5970961.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] DATA Integrity & Recovery

[Albe Laurenz]

chris faber wrote:
> Postgres 9.2
> 
> We have a POSTGRES database that we have been backing up via Incremental 
> backups.

You are talking of a physical base backup and WAL archives, right?

> We had an incident where we had to recover from backup. Our software vendor 
> has completed
> a restore and we have lost 10 days of data. There is no explanation as to the 
> reason we
> have sustained this loss.

Then press your software wendor for a reason.
They did the restore, so they should know.

> I would appreciate the communities help in the following:
> 
> 1. Determine if data from the incremental backups can be restored or 
> recovered.

If properly done, you can recover to any point of time after the backup with
a base backup and WAL archives.

> 2. Determine if data can be recovered from individual files backed up from 
> main Postgres
> data directory.

That is more tricky.  There is no straightforward way to extract such
information, particularly if the commit log is missing.
If that file is from a base backup, there is the additional difficulty
that the file could be in an inconsistent state.

You would have to hire a specialist for such work.


It sounds like you should consider letting somebody more reliable than
your software vendor manage your database backups.

Yours,
Laurenz Albe

-- 
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] DATA Integrity & Recovery

[Rich Shepard]

On Wed, 12 Jul 2017, chris faber wrote:


I would appreciate the communities help in the following:

1. Determine if data from the incremental backups can be restored or
recovered.
2. Determine if data can be recovered from individual files backed up from
main Postgres data directory.


Chris,

  I am very far from being a postgres admin expert, but my understanding
from years ago is that general system backups (and I use dirvish every day
for incremental backups of all partitions) are different from
postgres-specific backups using pg_dump and pg_dumpall. I never looked into
the differences or why they mattered, but each week I run pg_dumpall and
save the .sql file in a database_backup/ directory.

  This does not answer your specific questions but does offer a strategy for
future operations.

HTH,

Rich


--
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] loading file with en dash character into postgres 9.6.1 database

[Hu, Patricia]

Thanks Laurenz, that nailed it. It was what Tom was saying, except I didn't 
figure out how. 

Thanks,
Patricia 


-Original Message-
From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] 
Sent: Wednesday, July 12, 2017 5:31 AM
To: 'Tom Lane *EXTERN*'; Hu, Patricia
Cc: pgsql general (pgsql-general@postgresql.org)
Subject: [EXTERNAL] RE: [GENERAL] loading file with en dash character into 
postgres 9.6.1 database

Tom Lane wrote:
> "Hu, Patricia"  writes:
>> The server and client encoding are both set to UTF8, and according to 
>> this http://www.fileformat.info/info/unicode/char/2013/index.htm en 
>> dash is a valid UTF8 character, but when running a script with insert 
>> statement with en dash character in it, I got the error below.
>> psql:activity_type.lst:379: ERROR:  invalid byte sequence for 
>> encoding "UTF8": 0x96
> 
> Well, that certainly isn't valid UTF8, so your script file isn't in UTF8.
> 
>> If I set client_encoding to WIN1252, the same file will be run ok but 
>> afterwards the en dash character showed up as "û", instead of the en 
>> dash character "-"
> 
> This indicates that your terminal program does *not* think its 
> encoding is WIN1252.  Having loaded that script file, you need to 
> revert client_encoding to whatever your terminal program is using, or 
> non-ASCII characters are going to be displayed wrong.
> 
> A bit of poking around suggests that your terminal may be operating 
> with code page 437 or similar, as 0x96 is "û" in that encoding --- 
> according to Wikipedia, at least:
> https://en.wikipedia.org/wiki/Code_page_437
> I don't think Postgres supports that as a client_encoding setting, so 
> one way or another you're going to need to switch the terminal 
> program's character set setting.

Running "chcp 1252" in your Windows console before starting psql should do the 
trick.

Yours,
Laurenz Albe

Confidentiality Notice::  This email, including attachments, may include 
non-public, proprietary, confidential or legally privileged information.  If 
you are not an intended recipient or an authorized agent of an intended 
recipient, you are hereby notified that any dissemination, distribution or 
copying of the information contained in or transmitted with this e-mail is 
unauthorized and strictly prohibited.  If you have received this email in 
error, please notify the sender by replying to this message and permanently 
delete this e-mail, its attachments, and any copies of it immediately.  You 
should not retain, copy or use this e-mail or any attachment for any purpose, 
nor disclose all or any part of the contents to any other person. Thank you.

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

[GENERAL] DATA Integrity & Recovery

[chris faber]

Postgres 9.2

We have a POSTGRES database that we have been backing up via Incremental
backups.

We had an incident where we had to recover from backup. Our software vendor
has completed a restore and we have lost 10 days of data. There is no
explanation as to the reason we have sustained this loss.

I would appreciate the communities help in the following:

1. Determine if data from the incremental backups can be restored or
recovered.
2. Determine if data can be recovered from individual files backed up from
main Postgres data directory.

Thanks in advance
Chris

Re: [GENERAL] Please say it isn't so

[Steve Crawford]

On Tue, Jul 11, 2017 at 9:51 PM, Steve Litt 
wrote:

> Hi all,
>
> Please tell me this is a mistake:
>
> https://wiki.postgresql.org/wiki/Systemd
>
> Why a database system should care about how processes get started is
> beyond me. Systemd is an entangled mess that every year subsumes more
> and more of the operating system, in a very non-cooperative way.
>
> There are almost ten init systems. In every one of those init systems,
> one can run a process supervisor, such as runit or s6 or
> daemontools-encore, completely capable of starting the postgres server.
>
> Every year, systemd further hinders interoperability, further erodes
> interchangeability of parts, and continues to address problems with
> WONTFIX. In the long run, you do your users no favor by including
> init-system specific code in Postgres or its makefiles. If systemd
> can't correctly start Postgres, I guarantee you that s6 or runit,
> running on top of systemd, can.
>
> Postgres doesn't care which language makes a query to it. Why
> should Postgres care which init system started it? I hope you can free
> Postgres of init-specific code, and if for some reason you can't do
> that, at least don't recommend init-specific code.
>
>
Take a deep breath...

You are looking at a page about PostgreSQL with specifics surrounding
installation on a machine running systemd. In that case it is naturally
recommended to compile using the --with-systemd option to better integrate
with systemd.

As the docs about that option say, "...This improves integration if the
server binary is started under systemd but has no impact otherwise..." You
are no more required to use systemd than you are to run PostgreSQL on
Windows but the options are available to you.

Cheers,
Steve

Re: [GENERAL] Changing collate & ctype for an existing database

[Albe Laurenz]

rihad wrote:
> Hi there. We have a working database that was unfortunately created by
> initdb with default ("C") collation & ctype. All other locale specific
> settings have the value en_US.UTF-8 in postgresql.conf. The database
> itself is multilingual and all its data is stored in UTF-8. Sorting
> doesn't work correctly, though. To fix that, can I just do this:
> 
> 
> update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8'
> where datname='mydb';
> 
> 
> This does seem to work on a testing copy of the database, i.e. select
> lower('БлаБлаБла') now works correctly when connected to that database.
> 
> 
> Is there still any chance for corrupting data by doing this, or indexes
> stopping working etc?
> 
> p.s.: postgres 9.6.3

As explained, yes.  Indexes on string columns will be corrupted.

See this example:

test=# CREATE DATABASE breakme LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
test=# \c breakme
breakme=# CREATE TABLE sort(id integer PRIMARY KEY, val text NOT NULL);
breakme=# INSERT INTO sort VALUES (1, 'LITTLE'), (2, 'big'), (3, 'b-less');
breakme=# CREATE INDEX ON sort(val);
breakme=# SET enable_seqscan=off;  -- force index use
breakme=# SELECT * FROM sort ORDER BY val;
┌┬┐
│ id │  val   │
├┼┤
│  1 │ LITTLE │
│  3 │ b-less │
│  2 │ big│
└┴┘
(3 rows)

breakme=# UPDATE pg_database SET datcollate='en_US.UTF-8', 
datctype='en_US.UTF-8' WHERE datname='breakme';
breakme=# \c breakme
breakme=# SET enable_seqscan=off;  -- force index use
breakme=# SELECT * FROM sort ORDER BY val;
┌┬┐
│ id │  val   │
├┼┤
│  1 │ LITTLE │
│  3 │ b-less │
│  2 │ big│
└┴┘
(3 rows)

breakme=# SET enable_seqscan=on;  -- this and the following force sequential 
scan
breakme=# SET enable_bitmapscan=off;
breakme=# SET enable_indexscan=off;
breakme=# SET enable_indexonlyscan=off;
breakme=# SELECT * FROM sort ORDER BY val;  -- this returns the correct order
┌┬┐
│ id │  val   │
├┼┤
│  2 │ big│
│  3 │ b-less │
│  1 │ LITTLE │
└┴┘
(3 rows)

As you see, your index is still sorted according to the C collation
and scanning it returns wrong results.

Yours,
Laurenz Albe

-- 
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] loading file with en dash character into postgres 9.6.1 database

[Albe Laurenz]

Tom Lane wrote:
> "Hu, Patricia"  writes:
>> The server and client encoding are both set to UTF8, and according to this
>> http://www.fileformat.info/info/unicode/char/2013/index.htm en dash is a 
>> valid UTF8
>> character, but when running a script with insert statement with en dash 
>> character in it, I
>> got the error below.
>> psql:activity_type.lst:379: ERROR:  invalid byte sequence for encoding 
>> "UTF8": 0x96
> 
> Well, that certainly isn't valid UTF8, so your script file isn't in UTF8.
> 
>> If I set client_encoding to WIN1252, the same file will be run ok
>> but afterwards the en dash character showed up as "û", instead of the en 
>> dash character
>> "-"
> 
> This indicates that your terminal program does *not* think its encoding
> is WIN1252.  Having loaded that script file, you need to revert
> client_encoding to whatever your terminal program is using, or non-ASCII
> characters are going to be displayed wrong.
> 
> A bit of poking around suggests that your terminal may be operating
> with code page 437 or similar, as 0x96 is "û" in that encoding ---
> according to Wikipedia, at least:
> https://en.wikipedia.org/wiki/Code_page_437
> I don't think Postgres supports that as a client_encoding setting,
> so one way or another you're going to need to switch the terminal
> program's character set setting.

Running "chcp 1252" in your Windows console before starting psql
should do the trick.

Yours,
Laurenz Albe

-- 
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] BDR node removal and rejoin

[Craig Ringer]

On 11 July 2017 at 05:49, Zhu, Joshua  wrote:

> An update… after manually removing the record for ‘node4’ from
> bdr.bdr_nodes, corresponding record in bdr.bdr_connections, and associated
> replication slot (with pg_drop_replication_slot), rejoining was successful.
>
>
>
> I was under the impression that there is no need to perform manual cleanup
> before a removed node (with database dropped and recreated) rejoining a BDR
> group.
>

BDR1 requires that you manually remove the bdr.bdr_nodes entry if you
intend to re-use the same node name.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Systemd support (was:Re: [GENERAL] Please say it isn't so)

[Mark Morgan Lloyd]

On 12/07/17 05:00, Steve Litt wrote:

Hi all,

Please tell me this is a mistake:

https://wiki.postgresql.org/wiki/Systemd

Why a database system should care about how processes get started is
beyond me. Systemd is an entangled mess that every year subsumes more
and more of the operating system, in a very non-cooperative way.

There are almost ten init systems. In every one of those init systems,
one can run a process supervisor, such as runit or s6 or
daemontools-encore, completely capable of starting the postgres server.

Every year, systemd further hinders interoperability, further erodes
interchangeability of parts, and continues to address problems with
WONTFIX. In the long run, you do your users no favor by including
init-system specific code in Postgres or its makefiles. If systemd
can't correctly start Postgres, I guarantee you that s6 or runit,
running on top of systemd, can.

Postgres doesn't care which language makes a query to it. Why
should Postgres care which init system started it? I hope you can free
Postgres of init-specific code, and if for some reason you can't do
that, at least don't recommend init-specific code.


OTOH since systemd is what's being supported by a significant number of 
distributions it makes sense to at least try to work robustly with it.


While my preference would have been to have made such a change at a 
major version transition, the reality is that database systems are 
competitive, and not keeping up with the underlying platform would have 
been very much to PostgreSQL's disadvantage,


OP: Please note that you do yourself no favours at all by posting a 
subject line which could very easily be misinterpreted as spam.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]


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