[BUGS] data loss with pg_standby when doing a controlled failover
Running 8.3.7, I have a warm standby configuration with a archive_timeout of 10min. It's obvious that there's a 10min period where data could be lost if the master fails and the warm standby server has to take over. What's not obvious is that this is true even if the master server is shut down regularly, because it will not write out a last log segment to the archive. As a consequence, when doing a controlled failover (for maintenance purposes or so) all data changed after the last archive copy will be lost. IMHO this should be mentioned in the docs explicitly (I find it quite surprising that data can be lost even if the system is shutdown correctly), or better when shutting down the postmaster should spit all log segments containing all changes when archiving is on so the warm standby server can catch up. Regards. Andreas -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] length() return wrong result
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
- From the documentation, length(string) is 'Number of characters in string'
But it didn't :
1/ in a LATIN9 database:
postgres=# show server_encoding;
server_encoding
- -
LATIN9
postgres=# set client_encoding = utf8;
SET
postgres=# select length('é');
length
-
1
postgres=# set client_encoding = latin9;
SET
postgres=# select length('é');
length
-
2
2/ in a UTF8 database:
postgres=# show server_encoding;
server_encoding
- -
UTF8
postgres=# set client_encoding = utf8;
SET
postgres=# select length('é');
length
-
1
postgres=# set client_encoding = latin9;
SET
postgres=# select length('é');
length
-
2
- --
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
iEYEARECAAYFAknZxUQACgkQo/dppWjpEvyi+wCeNAB3706M2iSwUJGMWAQsCWyi
YRgAoJKwDRq6esZeMmnFdOAtq2d4kqoP
=6mds
-END PGP SIGNATURE-
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] length() return wrong result
Cédric Villemain wrote:
- From the documentation, length(string) is 'Number of characters in string'
But it didn't :
1/ in a LATIN9 database:
postgres=# show server_encoding;
server_encoding
- -
LATIN9
postgres=# set client_encoding = utf8;
SET
postgres=# select length('é');
length
-
1
postgres=# set client_encoding = latin9;
SET
postgres=# select length('é');
length
-
2
I bet your console works in UTF-8, so setting client_encoding=latin9 is
not the right thing to do. If you replace "select length('é')" with
"INSERT INTO foo VALUES ('é')", and check the contents of the table with
SELECT * FROM foo afterwards, you'll see that the string is garbled in
the latter case, and indeed consists of two characters.
As you noticed, server_encoding makes no difference here.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] data loss with pg_standby when doing a controlled failover
On Mon, Apr 6, 2009 at 7:37 AM, Andreas Pflug wrote: > Running 8.3.7, I have a warm standby configuration with a > archive_timeout of 10min. > > It's obvious that there's a 10min period where data could be lost if the > master fails and the warm standby server has to take over. What's not > obvious is that this is true even if the master server is shut down > regularly, because it will not write out a last log segment to the > archive. As a consequence, when doing a controlled failover (for > maintenance purposes or so) all data changed after the last archive copy > will be lost. > IMHO this should be mentioned in the docs explicitly (I find it quite > surprising that data can be lost even if the system is shutdown > correctly), or better when shutting down the postmaster should spit all > log segments containing all changes when archiving is on so the warm > standby server can catch up. You make an excellent point. If you're looking for a way to mitigate this risk, run: select pg_switch_xlog() ; Before shutting down. --Scott > > > Regards. > Andreas > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
Re: [BUGS] data loss with pg_standby when doing a controlled failover
On Mon, Apr 6, 2009 at 1:37 PM, Andreas Pflug wrote: > IMHO this should be mentioned in the docs explicitly (I find it quite > surprising that data can be lost even if the system is shutdown > correctly), or better when shutting down the postmaster should spit all > log segments containing all changes when archiving is on so the warm > standby server can catch up. See also this thread which might be interesting for you: http://archives.postgresql.org/message-id/3f0b79eb0903242329j12865d55s348f5c873a956...@mail.gmail.com -- Guillaume -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4750: UPDATE called from PL/pgSQL failed when there is ON UPDATE DO INSTEAD NOTHING rule
The following bug has been logged online: Bug reference: 4750 Logged by: Ondrej Novy Email address: on...@netbox.cz PostgreSQL version: 8.2.13 Operating system: Linux Description:UPDATE called from PL/pgSQL failed when there is ON UPDATE DO INSTEAD NOTHING rule Details: Hi, create rule on table ON UPDATE DO INSTEAD NOTHING. Then create function which calls UPDATE on this table. ERROR: SPI_execute_plan failed executing query "...": Unrecognized SPI code 0 CONTEXT: PL/pgSQL function "..." line ... at SQL statement Thank you, Ondrej Novy -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] data loss with pg_standby when doing a controlled failover
Scott Mead wrote: > > > > On Mon, Apr 6, 2009 at 7:37 AM, Andreas Pflug > mailto:pgad...@pse-consulting.de>> wrote: > > Running 8.3.7, I have a warm standby configuration with a > archive_timeout of 10min. > > It's obvious that there's a 10min period where data could be lost > if the > master fails and the warm standby server has to take over. What's not > obvious is that this is true even if the master server is shut down > regularly, because it will not write out a last log segment to the > archive. As a consequence, when doing a controlled failover (for > maintenance purposes or so) all data changed after the last > archive copy > will be lost. > IMHO this should be mentioned in the docs explicitly (I find it quite > surprising that data can be lost even if the system is shutdown > correctly), or better when shutting down the postmaster should > spit all > log segments containing all changes when archiving is on so the warm > standby server can catch up. > > > > You make an excellent point. If you're looking for a way to mitigate > this risk, run: > > select pg_switch_xlog() ; > >Before shutting down. Sort of, unless some other user succeeds to commit a transaction after pg_switch_xlog, and before the database ceases operation. My "graceful failover" procedure now includes this workaround: - shutdown server - restart server with --listen_addresses='' to prevent other users to connect (there are no local users on the server machine) - pg_switch_xlog() - shutdown finally - let the warm server continue Regards, Andreas -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] data loss with pg_standby when doing a controlled failover
Guillaume Smet wrote: > On Mon, Apr 6, 2009 at 1:37 PM, Andreas Pflug > wrote: > >> IMHO this should be mentioned in the docs explicitly (I find it quite >> surprising that data can be lost even if the system is shutdown >> correctly), or better when shutting down the postmaster should spit all >> log segments containing all changes when archiving is on so the warm >> standby server can catch up. >> > > See also this thread which might be interesting for you: > http://archives.postgresql.org/message-id/3f0b79eb0903242329j12865d55s348f5c873a956...@mail.gmail.com > It is, though not related to this problem. I'd expect pg_standby's default behaviour to be like the pseudocode's in the warm-standby documentation. To me, it's kind of unexpected that it won't continue restoring if the trigger file is present (as Kevin said, what's the use case for the current behaviour?). Regards, Andreas -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4750: UPDATE called from PL/pgSQL failed when there is ON UPDATE DO INSTEAD NOTHING rule
"Ondrej Novy" writes: > Description:UPDATE called from PL/pgSQL failed when there is ON > UPDATE DO INSTEAD NOTHING rule This is fixed for 8.4, but the fix was deemed too invasive to back-patch into existing branches. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] PostgreSQL 8.3.7: soundex function returns UTF-16 characters
Hello,
We have just discovered a problem with the soundex function in
PostgreSQL 8.3.7. The problem is easy to reproduce. The following query
returns the ASCII code of the soundex representation of the Greek letter Pi:
select ascii (soundex('Π'));
In PostgreSQL 8.2.6 the result would be 0 (character null). In
PostgreSQL 8.3.7 the return value is 944, which is the UTF-16 code of
this letter. Because PostgreSQL does not support UTF-16, this behaviour
causes problems when the return values from the soundex function are
stored in a database column. For instance, creating an index on such a
column or doing VACUUM FULL on the table results in errors like:
ERROR: could not convert string to UTF-16
Regards,
Frans
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] PostgreSQL 8.3.7: soundex function returns UTF-16 characters
Frans writes:
> We have just discovered a problem with the soundex function in
> PostgreSQL 8.3.7. The problem is easy to reproduce. The following query
> returns the ASCII code of the soundex representation of the Greek letter Pi:
> select ascii (soundex('Î '));
> In PostgreSQL 8.2.6 the result would be 0 (character null). In
> PostgreSQL 8.3.7 the return value is 944, which is the UTF-16 code of
> this letter.
Hm, I take it you are working in database encoding utf8? The
fuzzystrmatch module doesn't really work with utf8 (nor any other
multibyte encoding), because it depends on the functions.
What you'll probably get when applying it to non-ascii utf8 is
an invalidly encoded string.
This is a known limitation that probably should be better documented.
It was just as broken in 8.2 (and every previous version), though.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] PostgreSQL 8.3.7: soundex function returns UTF-16 characters
Tom Lane wrote:
Frans writes:
We have just discovered a problem with the soundex function in
PostgreSQL 8.3.7. The problem is easy to reproduce. The following query
returns the ASCII code of the soundex representation of the Greek letter Pi:
select ascii (soundex('Î '));
In PostgreSQL 8.2.6 the result would be 0 (character null). In
PostgreSQL 8.3.7 the return value is 944, which is the UTF-16 code of
this letter.
Hm, I take it you are working in database encoding utf8?
That is correct. I should have mentioned it. It is the default encoding
we use because we often deal with non-English languages. And it is
because of multilingualism that the fuzzystrmatch functions are handy.
The
fuzzystrmatch module doesn't really work with utf8 (nor any other
multibyte encoding), because it depends on the functions.
What you'll probably get when applying it to non-ascii utf8 is
an invalidly encoded string.
Well, in 8.2.6 the result for non-ASCII UTF-8 was an empty string (ASCII
code 0). You could argue that this is a valid way of expressing that the
input string could not be processed (especially if it were documented).
The nice thing about this approach is that the result is valid ASCII
(and UTF-8).
This is a known limitation that probably should be better documented.
It was just as broken in 8.2 (and every previous version), though.
But it seems there has been a recent change in the handling of non-ASCII
strings. And the result of this change is that further handling or
storing of the function output has become more difficult.
regards, tom lane
Best regards,
Frans
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] PostgreSQL 8.3.7: soundex function returns UTF-16 characters
Frans writes: > Tom Lane wrote: >> The >> fuzzystrmatch module doesn't really work with utf8 (nor any other >> multibyte encoding), because it depends on the functions. >> What you'll probably get when applying it to non-ascii utf8 is >> an invalidly encoded string. >> > Well, in 8.2.6 the result for non-ASCII UTF-8 was an empty string (ASCII > code 0). A comparison of the 8.2 and 8.3 fuzzystrmatch sources shows no difference. The behavior of the ascii() function has indeed changed, but soundex() is no more nor less broken than it was before. [ thinks for a bit... ] If you are seeing a difference in what soundex itself does, the most likely explanation is a difference in the behavior of isalpha() or perhaps toupper(). Are you running on the same underlying C library as before? Are you quite sure you have the same encoding and locale selected? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4751: Incorrect pg_dump output when dropping not null in inherited table.
"Grzegorz Junka" writes: > I don't know if these steps below are allowed, but documentation doesn't say > anything that they are not. > create table parent_table (someint integer not null); > create table child_table (id integer) inherits (parent_table); > alter table only child_table alter column someint drop not null; This is not considered valid: the result would be that "select * from parent_table" could show some null values of someint, which would be unexpected given its constraint. We are not currently enforcing that but it will probably start being enforced in 8.5 or so. (As of 8.4, a similar rule for inherited check constraints *is* enforced.) It's not really a pg_dump bug that it fails to cope with the case; rather it's a backend bug that you can get into this state at all. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4751: Incorrect pg_dump output when dropping not null in inherited table.
The following bug has been logged online: Bug reference: 4751 Logged by: Grzegorz Junka Email address: x...@poczta.onet.pl PostgreSQL version: 8.3.7 Operating system: FreeBSD 7.1 Description:Incorrect pg_dump output when dropping not null in inherited table. Details: I don't know if these steps below are allowed, but documentation doesn't say anything that they are not. If they are not allowed then an error should be raised. If they are allowed then pg_dump should create the schema correctly. In either way there is a bug somewhere. Basically what I am trying to do is inheriting a table containing a NOT NULL constraint, dropping the constraint in the inherited table, and then dumping the database schema to a file. The output file doesn't have the NOT NULL constraint dropped in the inherited table. Steps: create table parent_table (someint integer not null); create table child_table (id integer) inherits (parent_table); alter table only child_table alter column someint drop not null; Verify that it works: insert into parent_table (someint) values (null); ERROR: null value in column "someint" violates not-null constraint insert into child_table (someint) values (null); INSERT 0 1 Now dump the database: pg_dump database >mydb.db In the dumped schema the tables are defined properly but the NOT NULL constraint is not dropped from the someint column in the child_table: CREATE TABLE parent_table ( someint integer NOT NULL ); CREATE TABLE child_table ( id integer ) INHERITS (parent_table); the column someint integer is still NOT NULL in the child_table. The NOT NULL constraint is being inherited in the child_table and if it is not dropped then the schema is either recreates improperly or fails. create table child_table2 (id integer) inherits (parent_table); insert into child_table2 (someint, id) values (null, 1); ERROR: null value in column "someint" violates not-null constraint -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] data loss with pg_standby when doing a controlled failover
Hi, On Mon, Apr 6, 2009 at 11:13 PM, Andreas Pflug wrote: > Scott Mead wrote: >> >> >> >> On Mon, Apr 6, 2009 at 7:37 AM, Andreas Pflug >> mailto:pgad...@pse-consulting.de>> wrote: >> >> Running 8.3.7, I have a warm standby configuration with a >> archive_timeout of 10min. >> >> It's obvious that there's a 10min period where data could be lost >> if the >> master fails and the warm standby server has to take over. What's not >> obvious is that this is true even if the master server is shut down >> regularly, because it will not write out a last log segment to the >> archive. As a consequence, when doing a controlled failover (for >> maintenance purposes or so) all data changed after the last >> archive copy >> will be lost. >> IMHO this should be mentioned in the docs explicitly (I find it quite >> surprising that data can be lost even if the system is shutdown >> correctly), or better when shutting down the postmaster should >> spit all >> log segments containing all changes when archiving is on so the warm >> standby server can catch up. >> >> >> >> You make an excellent point. If you're looking for a way to mitigate >> this risk, run: >> >> select pg_switch_xlog() ; >> >> Before shutting down. > Sort of, unless some other user succeeds to commit a transaction after > pg_switch_xlog, and before the database ceases operation. > > My "graceful failover" procedure now includes this workaround: > - shutdown server > - restart server with --listen_addresses='' to prevent other users to > connect (there are no local users on the server machine) > - pg_switch_xlog() > - shutdown finally > - let the warm server continue What if new xlogs are generated by autovacuum or bgwriter between pg_switch_xlog and final shutdown? Those xlogs can be ignored? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
