Re: [HACKERS] [BUGS] BUG #14825: enum type: unsafe use?

[Christophe Pettus]

> On Sep 25, 2017, at 07:55, Andrew Dunstan  
> wrote:
> Let's ask a couple of users who I think are or have been actually
> hurting on this point. Christophe and David, any opinions?

Since about 90% of what I encounter in this area are automatically-generated 
migrations, having a clear set of (perhaps restrictive) rules which never fail 
is the most important.  It's easy to split the CREATE or ALTERs out into their 
own transaction, and leave usage (such as populating a table from a migration) 
to a second transaction.

It's not clear to me that this is a vote either way, but I think the easiest 
thing to explain ("you cannot use a new enum value in the same transaction that 
created it") is the best in this situation.

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

Re: [HACKERS] [COMMITTERS] pgsql: Implement table partitioning.

[Christophe Pettus]

> On Dec 9, 2016, at 22:52, Keith Fiske  wrote:
> On Fri, Dec 9, 2016 at 10:01 PM, Robert Haas  wrote:
>> One thing that's tricky/annoying about this is that if you have a
>> DEFAULT partition and then add a partition, you have to scan the
>> DEFAULT partition for data that should be moved to the new partition.
>> That makes what would otherwise be a quick operation slow.  Still, I'm
>> sure there's a market for that feature.
> 
> I would find that perfectly acceptable as long as a caveat about the 
> performance impact was included in the documentation.

+1.  I don't think it's conceptually different from adding a column with a 
default, in that regard; you just have to know the impact.

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

[HACKERS] HashAggregate row estimate = 200

[Christophe Pettus]

Something I've noticed frequently is that HashAggregate will, especially if the 
children are Append with one of the nodes a non-seqscan, estimate 200 rows 
rather than a calculated vlaue.  Where is that value coming from?  The 
statistics target, a hardwired constant, or something else?
--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Alter or rename enum value

[Christophe Pettus]

On Mar 27, 2016, at 7:20 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> I do not know whether this would be a meaningful improvement for
> common use-cases, though.

It would certainly be a step forward over the current situation.  It would mean 
that a specific imaginable use-case (inserting a new enum value, then 
populating a dimension table for it) would have to be done as two migrations 
rather than one, but that is much more doable in most tools than having a 
migration run without a transaction at all.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Alter or rename enum value

[Christophe Pettus]

On Mar 26, 2016, at 7:40 AM, Andrew Dunstan <and...@dunslane.net> wrote:
> It would be nice if we could find a less broad brush approach to dealing with 
> the issue.

I don't know how doable this is, but could we use the existing mechanism of 
marking an index invalid if it contains an enum type to which a value was 
added, and the transaction was rolled back?  For the 90% use case, that would 
be acceptable, I would expect.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Alter or rename enum value

[Christophe Pettus]

On Mar 25, 2016, at 11:50 AM, Andrew Dunstan <and...@dunslane.net> wrote:

> I don't believe anyone knows how to do that safely.

The core issue, for me, is that not being able to modify enum values in a 
transaction breaks a very wide variety of database migration tools.  Even a 
very brutal solution like marking indexes containing the altered type invalid 
on a ROLLBACK would be preferable to the current situation.

--
-- Christophe Pettus
   x...@thebuild.com



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

[HACKERS] Deciding which index to use

[Christophe Pettus]

Where in the optimizer code does PostgreSQL decide which of several 
possibly-matching partial indexes to use?
--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 28, 2014, at 6:27 AM, Robert Haas robertmh...@gmail.com wrote:

 Taken individually, none of those decisions seem crazy, but taken
 together it's pretty weird.  Instead of inventing a new type (jsonb)
 designed from the ground up to do what we want, we're, well, we're
 doing what Christophe says: creating our own proprietary hierarchical
 type and then making the hierarchical type everyone else uses depend
 on it.  Described in those terms, it's hard for me to believe that
 anyone here thinks that's not a strange thing to do.

A lot of it is that we're getting really tied up in knots about terminology.  
Because of the history of the project, it's being approached as jsonb depends 
on hstore2, rather than, We need a binary format, BSON won't cut it, but 
hstore2 is creating one, so let's use the same for both to avoid duplication of 
effort.

Put that last way, it's a more sensible decision.  My specific concern was 
Well, if you want binary json, install hstore is a very strange presentation 
to give to customers.  Many of the user-facing objections can be solved just by 
removing the implicit cast from jsonb to hstore, and the remaining operators 
(if they don't make it into this patch) can be added over time.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 28, 2014, at 1:03 PM, Josh Berkus j...@agliodbs.com wrote:
 However, the problems with admins not wanting to install -contrib aren't
 really about what's in or not in -contrib.

I'll also mention that an increasingly large number of people are running 
PostgreSQL in an environment where they don't get to pick what packages are 
installed on their server (RDS, for example).  Telling them that something is 
in -contrib can very well be telling them You can't have it in those cases.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 28, 2014, at 1:35 PM, Peter Geoghegan p...@heroku.com wrote:
 I don't think hstore-style indexing is advanced; it's the main
 reason for there being a jsonb, in my view.

jsonb is significantly faster than json even without indexing; there are plenty 
of reasons to have jsonb even if we don't initially have indexing operations 
for it.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 28, 2014, at 1:34 PM, Peter Geoghegan p...@heroku.com wrote:

 Amazon RDS Postgres has hstore.

Just observing that putting something in -contrib does not mean every 
installation can automatically adopt it.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 28, 2014, at 2:12 PM, Peter Geoghegan p...@heroku.com wrote:

 AFAICT, we have these
 operator classes that work fine with jsonb for the purposes of
 hstore-style indexing (the hstore operator classes).

That assumes that it is acceptable that jsonb be packaged in the hstore 
extension.  To put it mildly, there's no consensus on that point; indeed, I 
think there's consensus that's a non-starter.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 27, 2014, at 5:31 PM, Peter Geoghegan p...@heroku.com wrote:

 Now, it's confusing that it has to go through hstore, perhaps, but
 that's hardly all that bad in and of itself.

Yes, it is.  It strikes me as irrational to have jsonb depend on hstore.  Let's 
be honest with ourselves: if we were starting over, we wouldn't start by 
creating our own proprietary hierarchical type and then making the hierarchical 
type everyone else uses depend on it.  hstore exists because json didn't.  But 
json does now, and we shouldn't create a jsonb dependency on hstore.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 27, 2014, at 8:04 PM, Peter Geoghegan p...@heroku.com wrote:

 I'm hearing a lot about how important jsonb is, but not much on how to
 make the simple jsonb cases that are currently broken (as illustrated
 by my earlier examples [1], [2]) work.

Surely, the answer is to define a jsonb || jsonb (and likely the other 
combinatorics of json and jsonb), along with the appropriate GIN and GiST 
interfaces for jsonb.  Why would that not work?

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 27, 2014, at 8:31 PM, Peter Geoghegan p...@heroku.com wrote:

 On Thu, Feb 27, 2014 at 8:23 PM, Christophe Pettus x...@thebuild.com wrote:
 Surely, the answer is to define a jsonb || jsonb (and likely the other 
 combinatorics of json and jsonb), along with the appropriate GIN and GiST 
 interfaces for jsonb.  Why would that not work?
 
 I'm not the one opposed to putting jsonb stuff in the hstore module!

My proposal is that we break the dependencies of jsonb (at least, at the 
user-visible level) on hstore2, thus allowing it in core successfully. jsonb || 
jsonb returning hstore seems like a bug to me, not a feature we should be 
supporting.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 27, 2014, at 9:12 PM, Craig Ringer cr...@2ndquadrant.com wrote:

 On 02/28/2014 12:43 PM, Christophe Pettus wrote:
 My proposal is that we break the dependencies of jsonb (at least, at the 
 user-visible level) on hstore2, thus allowing it in core successfully. jsonb 
 || jsonb returning hstore seems like a bug to me, not a feature we should be 
 supporting.
 
 Urgh, really?
 
 That's not something I'd be excited to be stuck with into the future.

The reason that we're even here is that there's no jsonb || jsonb operator (or 
the other operators that one would expect).

If you try || without the hstore, you get an error, of course:

postgres=# select '{foo:{bar:yellow}}'::jsonb || '{}'::jsonb;
ERROR:  operator does not exist: jsonb || jsonb
LINE 1: select '{foo:{bar:yellow}}'::jsonb || '{}'::jsonb;
 ^
HINT:  No operator matches the given name and argument type(s). You might need 
to add explicit type casts.


The reason it works with hstore installed is that there's an implicit cast from 
hstore to jsonb:

postgres=# create extension hstore;
CREATE EXTENSION
postgres=# select '{foo:{bar:yellow}}'::jsonb || '{}'::jsonb;
 ?column? 
--
 foo={bar=yellow}
(1 row)

--

But I think we're piling broken on broken here.  Just creating an appropriate 
jsonb || jsonb operator solves this problem.  That seems the clear route 
forward.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 27, 2014, at 9:28 PM, Peter Geoghegan p...@heroku.com wrote:

 The only problem with that is now you have to move the implementation
 of ||, plus a bunch of other hstore operators into core. That seems
 like a more difficult direction to move in from a practical
 perspective, and I'm not sure that you won't hit a snag elsewhere.

Implementing operators for new types in PostgreSQL is pretty well-trod ground.  
I really don't know what snags we might hit.

 I suppose that putting it in core would be slightly preferable
 given the strategic importance of jsonb, but it's not something that
 I'd weigh too highly.

I'm completely unsure how to parse the idea that something is strategically 
important but we shouldn't put it in core.  If json was important enough to 
make it into core, jsonb certainly is.

Honestly, I really don't understand the resistance to putting jsonb in core.  
There are missing operators, yes; that's a very straight-forward hole to plug.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 27, 2014, at 9:59 PM, Peter Geoghegan p...@heroku.com wrote:

 I don't find that very reassuring.

Obviously, we have to try it, and that will decide it.

 I don't understand why an extension is seen as not befitting
 of a more important feature.

contrib/ is considered a secondary set of features; I routinely get pushback 
from clients about using hstore because it's not in core, and they are thus 
suspicious of it.  The educational project required to change that far exceeds 
any technical work we are talking about here..  There's a very large 
presentational difference between having a feature in contrib/ and in core, at 
the minimum, setting aside the technical issues (such as the 
extensions-calling-extensions problem).

We have an existence proof of this already: if there was absolutely no 
difference between having things being in contrib/ and being in core, full text 
search would still be in contrib/.

 You are basically suggesting putting all of hstore in core, because
 jsonb and hstore are approximately the same thing. That seem quite a
 bit more controversial than putting everything in the hstore
 extension.

Well, controversy is just a way of saying there are people who don't like the 
idea, and I get that.  But I don't see the basis for the dislike.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 27, 2014, at 11:15 PM, Peter Geoghegan p...@heroku.com wrote:

 I don't think that's obvious at all. Anyone is free to spend their
 time however they please, but personally I don't think that that's a
 wise use of anyone's time.

I believe you are misunderstanding me.  If there are actual technical problems 
or snags to migrating jsonb into core with full operator and index support, 
then the way we find out is to do the implementation, unless you know of a 
specific technical holdup already.

 There are no technical issues of any real consequence in this specific 
 instance.

There was no technical reason that json couldn't have been an extension, 
either, but there were very compelling presentational reasons to have it in 
core.  jsonb has exactly the same presentational issues.

 Yes, people who have the ability to block the feature entirely. I am
 attempting to build consensus by reaching a compromise that weighs
 everyone's concerns.

The thing I still haven't heard is why jsonb in core is a bad idea, except that 
it is too much code.  Is that the objection?

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] jsonb and nested hstore

[Christophe Pettus]

On Feb 25, 2014, at 1:57 PM, Hannu Krosing ha...@2ndquadrant.com wrote:

 It is not in any specs, but nevertheless all major imlementations do it and
 some code depends on it.

I have no doubt that some code depends on it, but all major implementations 
is too strong a statement.  BSON, in particular, does not have stable field 
order.

--
-- Christophe Pettus
   x...@thebuild.com



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

[HACKERS] Streaming replication bug in 9.3.2, WAL contains references to invalid pages

[Christophe Pettus]

Greetings,

We've had two clients experience a crash on the secondary of a streaming 
replication pair, running PostgreSQL 9.3.2.  In both cases, the messages were 
close to this example:

2013-12-30 18:08:00.464 PST,,,23869,,52ab4839.5d3d,16,,2013-12-13 09:47:37 
PST,1/0,0,WARNING,01000,page 45785 of relation base/236971/365951 is 
uninitialized,xlog redo vacuum: rel 1663/236971/365951; blk 45794, 
lastBlockVacuumed 45784
2013-12-30 18:08:00.465 PST,,,23869,,52ab4839.5d3d,17,,2013-12-13 09:47:37 
PST,1/0,0,PANIC,XX000,WAL contains references to invalid pages,xlog redo 
vacuum: rel 1663/236971/365951; blk 45794, lastBlockVacuumed 45784
2013-12-30 18:08:00.950 PST,,,23866,,52ab4838.5d3a,8,,2013-12-13 09:47:36 
PST,,0,LOG,0,startup process (PID 23869) was terminated by signal 6: 
Aborted,

In both cases, the indicated relation was a primary key index.  In one case, 
rebuilding the primary key index caused the problem to go away permanently (to 
date).  In the second case, the problem returned even after a full dump / 
restore of the master database (that is, after a dump / restore of the master, 
and reimaging the secondary, the problem returned at the same primary key 
index, although of course with a different OID value).

It looks like this has been experienced on 9.2.6, as well:


http://www.postgresql.org/message-id/flat/CAL_0b1s4QCkFy_55kk_8XWcJPs7wsgVWf8vn4=jxe6v4r7h...@mail.gmail.com

Let me know if there's any further information I can provide.

Best,
--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 13, 2013, at 8:52 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Please apply commit 478af9b79770da43a2d89fcc5872d09a2d8731f8 and see
 if that doesn't fix it for you.

Great, thanks.  Would the statement_timeout firing invoke this path?  (I'm 
wondering why this particular installation was experiencing this.)

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 13, 2013, at 1:49 PM, Merlin Moncure mmonc...@gmail.com wrote:
 Is this an edge case or something that will hit a lot of users?

My understanding (Tom can correct me if I'm wrong, I'm sure) is that it is an 
issue for servers on 9.3.2 where there are a lot of query cancellations due to 
facilities like statement_timeout or lock_timeout that cancel a query 
asynchronously.  I assume pg_cancel_backend() would apply as well.

We've only seen it on one client, and that client had a *lot* (thousands on 
thousands) of statement_timeout cancellations.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 13, 2013, at 8:52 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Please apply commit 478af9b79770da43a2d89fcc5872d09a2d8731f8 and see
 if that doesn't fix it for you.

It appears to fix it.  Thanks!

--
-- Christophe Pettus
   x...@thebuild.com



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

[HACKERS] stuck spinlock

[Christophe Pettus]

Greetings,

Immediately after an upgrade from 9.3.1 to 9.3.2, we have a client getting 
frequent (hourly) errors of the form:

/var/lib/postgresql/9.3/main/pg_log/postgresql-2013-12-12_211710.csv:2013-12-12 
21:40:10.328 
UTC,n,n,32376,10.2.1.142:52451,52aa24eb.7e78,5,SELECT,2013-12-12 
21:04:43 UTC,9/7178,0,PANIC,XX000,stuck spinlock (0x7f7df94672f4) detected at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/storage/buffer/bufmgr.c:1099,,redacted

uname -a: Linux postgresql3-master 3.8.0-33-generic #48~precise1-Ubuntu SMP Thu 
Oct 24 16:28:06 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux.

Generally, there's no core file (which is currently enable), as the postmaster 
just normally exits the backend.

Diagnosis suggestions?
 
--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 12, 2013, at 3:37 PM, Peter Geoghegan p...@heroku.com wrote:
 Show pg_config output.

Below; it's the Ubuntu package.

BINDIR = /usr/lib/postgresql/9.3/bin
DOCDIR = /usr/share/doc/postgresql-doc-9.3
HTMLDIR = /usr/share/doc/postgresql-doc-9.3
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/9.3/server
LIBDIR = /usr/lib
PKGLIBDIR = /usr/lib/postgresql/9.3/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/9.3/man
SHAREDIR = /usr/share/postgresql/9.3
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/9.3/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-tcl' '--with-perl' '--with-python' '--with-pam' 
'--with-openssl' '--with-libxml' '--with-libxslt' 
'--with-tclconfig=/usr/lib/tcl8.5' '--with-tkconfig=/usr/lib/tk8.5' 
'--with-includes=/usr/include/tcl8.5' 'PYTHON=/usr/bin/python' 
'--mandir=/usr/share/postgresql/9.3/man' 
'--docdir=/usr/share/doc/postgresql-doc-9.3' 
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' 
'--datadir=/usr/share/postgresql/9.3' '--bindir=/usr/lib/postgresql/9.3/bin' 
'--libdir=/usr/lib/' '--libexecdir=/usr/lib/postgresql/' 
'--includedir=/usr/include/postgresql/' '--enable-nls' 
'--enable-integer-datetimes' '--enable-thread-safety' '--enable-debug' 
'--disable-rpath' '--with-ossp-uuid' '--with-gnu-ld' '--with-pgport=5432' 
'--with-system-tzdata=/usr/share/zoneinfo' 'CFLAGS=-g -O2 -fstack-protector 
--param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security 
-fPIC -pie -I/usr/include/mit-krb5 -DLINUX_OOM_ADJ=0' 
'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -Wl,--as-needed 
-L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-krb5' '--with-krb5' 
'--with-gssapi' '--with-ldap' 'CPPFLAGS=-D_FORTIFY_SOURCE=2'
CC = gcc
CPPFLAGS = -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 
-I/usr/include/tcl8.5
CFLAGS = -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat 
-Wformat-security -Werror=format-security -fPIC -pie -I/usr/include/mit-krb5 
-DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g
CFLAGS_SL = -fpic
LDFLAGS = -L../../../src/common -Wl,-Bsymbolic-functions -Wl,-z,relro 
-Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 
-L/usr/lib/x86_64-linux-gnu/mit-krb5 -L/usr/lib/x86_64-linux-gnu -Wl,--as-needed
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgport -lpgcommon -lxslt -lxml2 -lpam -lssl -lcrypto -lkrb5 -lcom_err 
-lgssapi_krb5 -lz -ledit -lcrypt -ldl -lm 
VERSION = PostgreSQL 9.3.2

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 12, 2013, at 3:33 PM, Andres Freund and...@2ndquadrant.com wrote:

 Any other changes but the upgrade? Maybe a different compiler version?

Just the upgrade; they're using the Ubuntu packages from apt.postgresql.org.

 Also, could you share some details about the workload? Highly
 concurrent? Standby? ...

The workload is not very highly concurrent; actually quite lightly loaded.   
There are a very large number (442,000) of user tables.  No standby attached.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 12, 2013, at 3:18 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Hm, a PANIC really ought to result in a core file.  You sure you don't
 have that disabled (perhaps via a ulimit setting)?

Since it's using the Ubuntu packaging, we have pg_ctl_options = '-c' in 
/etc/postgresql/9.3/main/pg_ctl.conf.

 As for the root cause, it's hard to say.  The file/line number says it's
 a buffer header lock that's stuck.  I rechecked all the places that lock
 buffer headers, and all of them have very short code paths to the
 corresponding unlock, so there's no obvious explanation how this could
 happen.

The server was running with shared_buffers=100GB, but the problem has 
reoccurred now with shared_buffers=16GB.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 12, 2013, at 4:04 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 If you aren't getting a core file for a PANIC, then core
 files are disabled.

And just like that, we get one.  Stack trace:

#0  0x7f699a4fa425 in raise () from /lib/x86_64-linux-gnu/libc.so.6
(gdb) bt
#0  0x7f699a4fa425 in raise () from /lib/x86_64-linux-gnu/libc.so.6
#1  0x7f699a4fdb8b in abort () from /lib/x86_64-linux-gnu/libc.so.6
#2  0x7f699c81991b in errfinish ()
#3  0x7f699c81a477 in elog_finish ()
#4  0x7f699c735db3 in s_lock ()
#5  0x7f699c71e1f0 in ?? ()
#6  0x7f699c71eaf9 in ?? ()
#7  0x7f699c71f53e in ReadBufferExtended ()
#8  0x7f699c56d03a in index_fetch_heap ()
#9  0x7f699c67a0b7 in ?? ()
#10 0x7f699c66e98e in ExecScan ()
#11 0x7f699c6679a8 in ExecProcNode ()
#12 0x7f699c67407f in ExecAgg ()
#13 0x7f699c6678b8 in ExecProcNode ()
#14 0x7f699c664dd2 in standard_ExecutorRun ()
#15 0x7f6996ad928d in ?? ()
   from /usr/lib/postgresql/9.3/lib/auto_explain.so
#16 0x7f69968d3525 in ?? ()
   from /usr/lib/postgresql/9.3/lib/pg_stat_statements.so
#17 0x7f699c745207 in ?? ()
#18 0x7f699c746651 in PortalRun ()
#19 0x7f699c742960 in PostgresMain ()
#20 0x7f699c6ff765 in PostmasterMain ()
#21 0x7f699c53bea2 in main ()


--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

/buildd/postgresql-9.3-9.3.2/build/../src/backend/tcop/postgres.c:1048
#25 PostgresMain (argc=optimized out, argv=optimized out, 
---Type return to continue, or q return to quit---
dbname=0x7f699dd3e138 nbuild, username=optimized out)
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/tcop/postgres.c:4005
#26 0x7f699c6ff765 in BackendRun (port=0x7f699dd82050)
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/postmaster/postmaster.c:3999
#27 BackendStartup (port=0x7f699dd82050)
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/postmaster/postmaster.c:3688
#28 ServerLoop ()
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/postmaster/postmaster.c:1589
#29 PostmasterMain (argc=optimized out, argv=optimized out)
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/postmaster/postmaster.c:1258
#30 0x7f699c53bea2 in main (argc=5, argv=0x7f699dd3c1a0)
at /tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/main/main.c:196

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

 '\001', dest=0x7fa042a955e0, 
altdest=0x7fa042a955e0, completionTag=0x7fff19489fa0 )
at /tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/tcop/pquery.c:790
#26 0x7fa041d3d960 in exec_simple_query (
query_string=0x7fa0426034a0 SELECT COUNT(*) FROM \signups\  WHERE 
(signups.active_customer_expires_at  '2013-11-11' and 
(signups.overdue_invoices_count is null or signups.overdue_invoices_count = 
0)))
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/tcop/postgres.c:1048
#27 PostgresMain (argc=optimized out, argv=optimized out, 
dbname=0x7fa0425eb138 nbuild, username=optimized out)
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/tcop/postgres.c:4005
#28 0x7fa041cfa765 in BackendRun (port=0x7fa04262f050)
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/postmaster/postmaster.c:3999
#29 BackendStartup (port=0x7fa04262f050)
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/postmaster/postmaster.c:3688
#30 ServerLoop ()
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/postmaster/postmaster.c:1589
#31 PostmasterMain (argc=optimized out, argv=optimized out)
at 
/tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/postmaster/postmaster.c:1258
#32 0x7fa041b36ea2 in main (argc=5, argv=0x7fa0425e91a0)
at /tmp/buildd/postgresql-9.3-9.3.2/build/../src/backend/main/main.c:196
--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 12, 2013, at 5:45 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Presumably, we are seeing the victim rather than the perpetrator of
 whatever is going wrong.

This is probing about a bit blindly, but the only thing I can see about this 
system that is in some way unique (and this is happening on multiple machines, 
so it's unlikely to be hardware) is that there are a relatively large number of 
relations (like, 440,000+) distributed over many schemas.  Is there anything 
that pins a buffer that is O(N) to the number of relations?

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 12, 2013, at 6:15 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Are you possibly using any nonstandard extensions?

No, totally stock PostgreSQL.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

0.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
md0   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
md1   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
dm-0  0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
dm-1  0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
sdc   0.00 0.00   33.000.00 0.32 0.0019.88 
0.123.523.520.00   0.61   2.00
dm-2  0.00 0.00   33.000.00 0.32 0.0019.88 
0.123.523.520.00   0.61   2.00
sdd   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
   1.080.000.290.000.00   98.62

Device: rrqm/s   wrqm/s r/s w/srMB/swMB/s avgrq-sz 
avgqu-sz   await r_await w_await  svctm  %util
sda   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
sdb   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
md0   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
md1   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
dm-0  0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
dm-1  0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
sdc   0.00 0.006.004.00 0.07 0.0320.80 
0.000.000.000.00   0.00   0.00
dm-2  0.00 0.006.004.00 0.07 0.0320.80 
0.000.000.000.00   0.00   0.00
sdd   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
   0.500.000.080.040.00   99.37

Device: rrqm/s   wrqm/s r/s w/srMB/swMB/s avgrq-sz 
avgqu-sz   await r_await w_await  svctm  %util
sda   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
sdb   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
md0   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
md1   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
dm-0  0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
dm-1  0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00
sdc   0.00 0.00   11.003.00 0.22 0.1249.14 
0.000.000.000.00   0.00   0.00
dm-2  0.00 0.00   11.003.00 0.22 0.1249.14 
0.000.000.000.00   0.00   0.00
sdd   0.00 0.000.000.00 0.00 0.00 0.00 
0.000.000.000.00   0.00   0.00


--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 12, 2013, at 6:41 PM, Andres Freund and...@2ndquadrant.com wrote:

 Christophe: are there any unusual ERROR messages preceding the crash,
 possibly some minutes before?

Interestingly, each spinlock PANIC is *followed*, about one minute later (+/- 
five seconds) by a canceling statement due to statement timeout on that exact 
query.  The queries vary enough in text that it is unlikely to be a coincidence.

There are a *lot* of canceling statement due to statement timeout messages, 
which is interesting, because:

postgres=# show statement_timeout;
 statement_timeout 
---
 0
(1 row)

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] stuck spinlock

[Christophe Pettus]

On Dec 12, 2013, at 7:40 PM, Peter Geoghegan p...@heroku.com wrote:
 Couldn't that just be the app setting it locally?

Yes, that's what is happening there (I had to check with the client's 
developers).  It's possible that the one-minute repeat is due to the 
application reissuing the query, rather than specifically related to the 
spinlock issue.  What this does reveal is that all the spinlock issues have 
been on long-running queries, for what it is worth.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 20, 2013, at 3:57 PM, Andres Freund and...@2ndquadrant.com wrote:

 On 2013-11-20 15:52:22 -0800, Josh Berkus wrote:
 Oh, so this doesn't just happen when the base backup is first taken;
 *any* time the standby is restarted, it can happen. (!!!)
 
 Yes.

So, to be completely clear, any secondary running the affected versions which 
is started with hot_standby=on could potentially be corrupted even if it never 
connects to a primary?

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 19, 2013, at 6:59 AM, Andres Freund and...@2ndquadrant.com wrote:

 Yes. There's less expensive ways to do it, but those seem to complicated
 to suggest.

If this is something that could be built into to a tool, acknowledging the 
complexity, I'd be happy to see about building it.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 19, 2013, at 10:25 AM, Andres Freund and...@2ndquadrant.com wrote:
 I am not sure how much code it's going to take (I'd hope somewhat less),
 but it certainly will take some time to agree how it should be built and
 then building and integrating it.

Given that the situation we're in right now is that we have an unknown number 
of silently corrupt secondaries out there which will only be discovered when 
someone promotes them to being a primary (possibly because the current primary 
died without a backup), I'd say that this is something pretty urgent.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 19, 2013, at 10:29 AM, Andres Freund and...@2ndquadrant.com wrote:

 It's pretty unlikely that any automated testing would have cought this,
 the required conditions are too unlikely for that.

I would expect that promote secondary while primary is under heavy load is 
clear-cut test case.  What concerns me more is that we don't seem to have a 
framework to put in a regression test on the bug you just found (and thank you 
for finding it so quickly!).


--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 19, 2013, at 10:51 AM, Andres Freund and...@2ndquadrant.com wrote:

 You seem to imply that I/we should do that work?

No, just that it be done.  Of course, the more support from the professional PG 
community that is given to it, the better.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

Hi, Andres,

From my understanding, the problem only occurs over streaming replication; if 
the secondary was never a hot standby, and only used the archived WAL 
segments, that would be safe.  Is that correct?
--
-- Christophe Pettus
   x...@thebuild.com



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

[HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

Three times in the last two weeks, we have experience data corruption secondary 
servers using streaming replication on client systems.  The versions involved 
are 9.0.14, 9.2.5, and 9.3.1.  Each incident was separate; two cases they were 
for the same client (9.0.14 and 9.3.1), one for a different client (9.2.5).

The details of each incident are similar, but not identical.

The details of each incident are:

INCDIDENT #1: 9.0.14 -- A new secondary (S1) was initialized using rsync off of 
an existing, correct primary (P1) for the base backup, and using WAL-E for WAL 
segment shipping.  Both the primary and secondary were running 9.0.14.  S1 
properly connected to the primary once the it was caught up on WAL segments, 
and S1 was then promoted as a primary using the trigger file.

No errors in the log files on either system.

After promotion, it was discovered that there was significant data loss on S1.  
Rows that were present on P1 were missing on S1, and some rows were duplicated 
(including duplicates that violated primary key and other unique constraints).  
The indexes were corrupt, in that they seemed to think that the duplicates were 
not duplicated, and that the missing rows were still present.

Because the client's schema included a last_updated field, we were able to 
determine that all of the rows that were either missing or duplicated had been 
updated on P1 shortly (3-5 minutes) before S1 was promoted.  It's possible, but 
not confirmed, that there were active queries (including updates) running on P1 
at the moment of S1's promotion.

As a note, P1 was created from another system (let's call it P0) using just WAL 
shipping (no streaming replication), and no data corruption was observed.

P1 and S1 were both AWS instances running Ubuntu 12.04, using EBS (with xfs as 
the file system) as the data volume.

P1 and S1 have been destroyed at this point.


INCIDENT #2: 9.3.1 -- In order to repair the database, a pg_dump was taken of 
S1y, after having dropped the primary and unique constraints, and restored into 
a new 9.3.1 server, P2.  Duplicate rows were purged, and missing rows were 
added again.  The database, a new primary, was then put back into production, 
and ran without incident.

A new secondary, S2 was created off of the primary.  This secondary was created 
using pg_basebackup using --xlog-method=stream, although the WAL-E archiving 
was still present.

S2 attached to P2 without incident and no errors in the logs, but 
nearly-identical corruption was discovered (although this time without the 
duplicated rows, just missing rows).  At this point, it's not clear if there 
was some clustering in the last_updated timestamp for the rows that are 
missing from S2.  No duplicated rows were observed.

P2 and S2 are both AWS instances running Ubuntu 12.04, using EBS (with xfs as 
the file system) as the data volume.

No errors in the log files on either system.

P2 and S2 are still operational.


INCIDENT #3: 9.2.5 -- A client was migrating a large database from a 9.2.2 
system (P3) to a new 9.2.5 system (S3) using streaming replication.  As I 
personally didn't do the steps on this one, I don't have quite as much 
information, but the basics are close to incident #2: When S3 was promoted 
using the trigger file, no errors were observed and the database came up 
normally, but rows were missing from S3 that were present on P3.

P1 is running Centos 6.3 with ext4 as the file system.

P2 is running Centos 6.4 with ext3 as the file system.

Log shipping in this case was done via rsync.

P3 and S3 are still operational.

No errors in the log files on either system.

--

Obviously, we're very concerned that a bug was introduced in the latest minor 
release.  We're happy to gather data as required to assist in diagnosing this.
--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 18, 2013, at 10:58 AM, Christophe Pettus x...@thebuild.com wrote:
 As a note, P1 was created from another system (let's call it P0) using just 
 WAL shipping (no streaming replication), and no data corruption was observed.

As another data point, P0 was running 9.0.13, rather than 9.0.14.
--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 18, 2013, at 11:28 AM, Andres Freund and...@2ndquadrant.com wrote:
 Could you detail how exactly the base backup was created? Including the
 *exact* logic for copying?

0. Before any of this began, P1 was archiving WAL segments to AWS-S3.
1. pg_start_backup('', true) on P1.
2. Using rsync -av on P1, the entire $PGDATA directory was pushed from P1 to S2.
3. Once the rsync was complete, pg_stop_backup() on P1.
4. Create appropriate recovery.conf on S1.
5. Bring up PostgreSQL on S1.
6. PostgreSQL recovers normally (pulling WAL segments from WAL-E), and 
eventually connects to P1.
 
 Do you have the log entries for the startup after the base backup?

Sadly, not anymore.

 This server is gone, right?

Correct.

 Could you list the *exact* steps you did to startup the cluster?

0. Before any of this began, P2 was archiving WAL segments to AWS-S3.
1. Initial (empty) data directory deleted on S2.
2. New data directory created with:

/usr/lib/postgresql/9.3/bin/pg_basebackup --verbose --progress 
--xlog-method=stream --host=ip --user=repluser --pgdata=/data/9.3/main

3. Once the pg_basebackup completed, create appropriate recovery.conf on S1.
4. Bring up PostgreSQL on S2.
5. PostgreSQL recovers normally (pulling a small number of WAL segments from 
WAL-E), and eventually connects to P2.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 18, 2013, at 11:47 AM, Andres Freund and...@2ndquadrant.com wrote:

 Without deleting any data, including pg_xlog/, backup.label, anything?

Correct.

 Did you have hot_standby enabled on all of those machines? Even on the
 9.0.13 cluster?

Yes.

 That was just recovery command and primary conninfo?

Correct.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 18, 2013, at 11:47 AM, Andres Freund and...@2ndquadrant.com wrote:

 Did you have hot_standby enabled on all of those machines? Even on the
 9.0.13 cluster?

Actually, it's a bit more complex than this:

1. We don't know about P0, the 9.0.13 machine.  I assume it was, but it was 
managed elsewhere.
2. P1 never had hot_standby = 'on', as it was never intended to be a hot 
stand_by.
3. S1 did have hot_standby = 'on.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 18, 2013, at 11:47 AM, Andres Freund and...@2ndquadrant.com wrote:
 Without deleting any data, including pg_xlog/, backup.label, anything?

One more correction: After rsync finished and the pg_base_backup() was issued, 
the contents of pg_xlog/ on S1 were deleted.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 18, 2013, at 12:00 PM, Christophe Pettus x...@thebuild.com wrote:

 One more correction: After rsync finished and the pg_base_backup() was 
 issued, the contents of pg_xlog/ on S1 were deleted.

pg_stop_backup(), sorry.

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 18, 2013, at 12:57 PM, Andres Freund and...@2ndquadrant.com wrote:

 Were there any kind of patterns in the lost data? What kind of workload
 are they running? I have an idea what the issue might be...

On the P1  S1 case, the data corrupted was data modified in the last few 
minutes before the switchover.  I don't want to over-analyze, but it was within 
the checkpoint_timeout value for that sever.

On the P2  S2 case, it's less obvious what the pattern is, since there was no 
cutover.

Insufficient information on the P3  S3 case.

Each of them is a reasonably high-volume OLTP-style workload.  The P1/P2 client 
has a very high level of writes; the P3 more read-heavy, but still a fair 
number of writes. 

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

Great!  If there's any further data I can supply to help, let me know.

On Nov 18, 2013, at 2:15 PM, Andres Freund and...@2ndquadrant.com wrote:

 Hi,
 
 Afaics it's likely a combination/interaction of bugs and fixes between:
 * the initial HS code
 * 5a031a5556ff83b8a9646892715d7fef415b83c3
 * f44eedc3f0f347a856eea8590730769125964597
 
 But that'd mean nobody noticed it during 9.3's beta...
 
 Greetings,
 
 Andres Freund
 
 -- 
 Andres Freundhttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services
 
 
 -- 
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] Data corruption issues using streaming replication on 9.0.14/9.2.5/9.3.1

[Christophe Pettus]

On Nov 18, 2013, at 2:26 PM, Andres Freund and...@2ndquadrant.com wrote:

 Trying to reproduce the issue with and without hot_standby=on would be
 very helpful, but I guess that's time consuming?

I've been working on it, but I haven't gotten it to fail yet.  I'll keep at it.

--
-- Christophe Pettus
   x...@thebuild.com



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

[HACKERS] Getting the clog bits for a particular xid

[Christophe Pettus]

As part of doing some database corruption investigation, I'm trying to get the 
pg_clog/ bit pair for a particular transaction.

Let's say we check on a particular tuple, and get:

SELECT xmin, id FROM mytable WHERE pk=4727366;

   xmin|id
---+--
 107898222 |  4727366

Each pg_clog file (as least, as of 9.3.1) is 256KB, so there are 256KB * 8 
bits/byte / 2 bits/transaction = 1M transactions per file

So:

107898222 / 1048576 = 102, or 0x0066
107898222 % 1048576 = 943470.

So, we're looking at file 0x0066.  It's the 943470th transaction in that file, 
or the 943470*2 = 1886940th bit.  So, (counting from the MSB being 0), it's the 
4th and 5th bit of byte offset 235867 in that file.

Is that correct?
--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] ereport documentation patch

[Christophe Pettus]

On Aug 19, 2013, at 11:28 PM, Heikki Linnakangas wrote:

 On 19.08.2013 23:40, Christophe Pettus wrote:
 Is it reasonable to note in the documentation that ereport does not return 
 if the error severity is greater than or equal to ERROR?
 
 Yeah, it probably would be good to mention that. Got a patch?

Attached!


ereport-no-return-doc.patch
Description: Binary data



--
-- Christophe Pettus
   x...@thebuild.com


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

[HACKERS] ereport documentation patch

[Christophe Pettus]

Is it reasonable to note in the documentation that ereport does not return if 
the error severity is greater than or equal to ERROR?

--
-- Christophe Pettus
   x...@thebuild.com



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

Re: [HACKERS] pg_rewind, a tool for resynchronizing an old master after failover

[Christophe Pettus]

On May 28, 2013, at 12:49 PM, Alvaro Herrera wrote:

 We've had request from companies because they wanted to distribute
 Postgres and lawyers weren't comfortable with copyright statements in
 assorted files.  In those cases we've asked the people mentioned in such
 copyright statements, got approval to remove the offending copyright
 lines, and removed them.

I assume this topic has come up and been rejected for some reason, but just in 
case: The Django project requires an explicit agreement for contributions that 
end up in the main source tree for it, part of which is the acceptance of the 
Django license and copyright notice.  (I don't have my copy right in front of 
me, but I don't think it's a full-on assignment of copyright.)

--
-- Christophe Pettus
   x...@thebuild.com



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

[HACKERS] Logging both start and end of temporary file usage

[Christophe Pettus]

In working with s a client to analyze their temp file usage, it became useful 
to know when a temporary file was created as well as when it was closed.  That 
way, we could process the logs to determine a high water mark of overall temp 
file usage, to know how high it was safe (in that workload) to set work_mem.  
So, I wrote a quick patch that logged both the open and close of the temp file.

Since the final size of the file isn't known at the time that the file is 
created, the patch just logs the filename.  The particular file can be 
correlated with the size by the name when the close message is logged.  Of 
course, there's no information about the pattern of the file size over time, 
but it's a bit more information than was there before.

As we don't know the size of the file until close time, the open is only logged 
if log_temp_files is 0 (the idea being that's maximum logging).

If this sounds like something worthwhile in general, I can package it up as a 
proper patch.
--
-- Christophe Pettus
   x...@thebuild.com


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

[HACKERS] xlog min recovery request ... is past current point ...

[Christophe Pettus]

PostgreSQL 9.0.4:

While bringing up a streaming replica, and while it is working its way through 
the WAL segments before connecting to the primary, I see a lot of messages of 
the form:

2012-02-01 21:26:13.978 PST,,,24448,,4f2a1e61.5f80,54,,2012-02-01 21:25:53 
PST,1/0,0,LOG,0,restored log file 00010DB40065 from 
archive,
2012-02-01 21:26:14.032 PST,,,24448,,4f2a1e61.5f80,55,,2012-02-01 21:25:53 
PST,1/0,0,WARNING,01000,xlog min recovery request DB5/42E15098 is past current 
point DB4/657FA490,writing block 5 of relation base/155650/156470_vm
xlog redo insert: rel 1663/155650/1658867; tid 9640/53
2012-02-01 21:26:14.526 PST,,,24448,,4f2a1e61.5f80,56,,2012-02-01 21:25:53 
PST,1/0,0,LOG,0,restored log file 00010DB40066 from 
archive,

All of these are on _vm relations.  The recovery completed successfully and the 
secondary connected to the primary without issue, so: Are these messages 
something to be concerned over?

--
-- Christophe Pettus
  x...@thebuild.com


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

Re: [HACKERS] and it's not a bunny rabbit, either

[Christophe Pettus]

On Dec 26, 2010, at 7:55 PM, Robert Haas wrote:

 tables do not support %s
 views do not support %s
 indexes do not support %s

The more detail we can give, the better, of course.  Nothing's more frustrating 
than having a command with an error like, Object does not support requested 
operation.  Thanks, computer program: Swerved off road, hit tree is about as 
useful.

--
-- Christophe Pettus
   x...@thebuild.com


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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Christophe Pettus]

Python 2.6.1 (r261:67515, Jun 24 2010, 21:47:49) 
[GCC 4.2.1 (Apple Inc. build 5646)] on darwin
Type help, copyright, credits or license for more information.
 isinstance(10,int)
True
 isinstance(1e10,int)
False

--
-- Christophe Pettus
   x...@thebuild.com


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

Re: [HACKERS] hstores in pl/python

[Christophe Pettus]

On Dec 13, 2010, at 7:19 PM, Robert Haas wrote:
 If we decree that Python dictionaries map
 onto hstore, does that mean they DON'T map onto json, or Pavel's
 hand-wavy proposal for associative arrays?  Because from 10,000 feet
 it sure isn't obvious why hstore would be preferable to either of the
 other two, except that it already exists and the early bird gets the
 worm.

I'll mention that psycopg2, the most widely Python DBI implementation for 
PostgreSQL, has a built-in mapping of hstore to dict, so signs are definitely 
pointing towards a hstore == dict standardization.  It also suffers from the 
problem that it needs to sniff the hstore OID, which is somewhat annoying, 
especially in a web environment where the sniff has to happen repeatedly.

--
-- Christophe Pettus
   x...@thebuild.com


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

Re: [HACKERS] Final(?) proposal for wal_sync_method changes

[Christophe Pettus]

On Dec 7, 2010, at 2:43 PM, Josh Berkus wrote:
 Because nobody sane uses OSX on the server?

The XServe running 10.5 server and 9.0.1 at the other end of the office takes 
your remark personally. :)

--
-- Christophe Pettus
   x...@thebuild.com


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

Re: [HACKERS] Range types

[Christophe Pettus]

On Dec 15, 2009, at 3:40 PM, Jeff Davis wrote:


Based on the premise that timestamps are a continuous value and the
granularity/precision is entirely an implementation detail, you're
right. But I disagree with the premise, at least in some cases that I
think are worthwhile.


The argument is, in essence:

DECIMAL is continuous.
DECIMAL(10,3) is discrete.

timestamptz in general is a continuous value (unless we're talking  
Planck times :) ).  There is no way for us to guarantee that  
next(timestamptz) will have the same value across all platforms; its  
epsilon is platform dependent.


However, if we specify a scale on timestamptz, it becomes much more  
useful.  Just making up a syntax, if we had timestamptz(milliseconds),  
then it's discrete and we know what next(timestamptz(milliseconds)) is.


But in the current implementation, the only way I can see making that  
work is if we specify a scale for timestamptz, and that strikes me as  
a big change to its semantics.


--
-- Christophe Pettus
   x...@thebuild.com


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

[HACKERS] Apprentices? (was =patch - Report the schema...)

[Christophe Pettus]

On Nov 15, 2009, at 5:14 PM, Andrew Dunstan wrote:
Then I think we need to start being more creative about ways to ease  
the path for people who want to get people involved.



With that as an inspiration, I'd like to offer a modest proposal:  
Apprentices.


In my case, I would be very exciting about participating more in PG  
development.  I have a reasonable amount of C/C++ experience (20-ish  
years), and have been a PG admin/client developer since 1998, but I  
lack enough familiarity with the codebase to do patch review on non- 
trivial patches, or to launch in and do development on a non-trivial  
feature.  (And, as has been noted, there are essential no non-trivial  
features needing work.)


On the other hand, I'm sure there are those such as myself who are  
perfectly capable in *assisting* in some way: Reviewing a patch for  
specific things, writing test cases, working on a subsection of a  
larger patch.  This also is a solution for the intimidating prospect  
of working on a large patch and being sold, Um, that's really  
completely the wrong thing.  Sorry.


Not every primary contribution or patch reviewer is going to be  
comfortable working with other people, because of temperament or work  
style, but I'm sure some are.  Might this help?

--
-- Christophe Pettus
   x...@thebuild.com


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

Re: [HACKERS] Proposal: String key space for advisory locks

[Christophe Pettus]

On Oct 27, 2009, at 4:37 PM, Merlin Moncure wrote:

'as is', advisory locks is a fantastic feature that can be used for
signaling, mutexing, etc that are relatively difficult things to do in
the transactional world of sql.  My main gripe is that the 'shared id'
method for doing record pessimistic locks is basically a nuclear
missile pointed at your shared buffers if you don't have lot of
discipline in the queries that lock IDs.  Maybe this argues for more
of a 'sql exposed' pessimistic lock feature that operates on similar
level as 'for update'...I'm not sure...curious what thoughts you have
about improving them.


Advisory locks have, as you say, a raft of very useful characteristics:

1. Enforced as much or as little as you wish, depending on your  
application design.

2. Race-condition-free.
3. Cleaned up automatically on session end.

Of course, 2^64 potential entries are enough for anyone.  The  
usability issue comes when you have multiple domains that you want to  
apply advisory locks to in a single database.  For example, if you  
have multiple tables (one of which, just for example, has a character  
pk), and perhaps some inter-client mutex signaling for things that are  
outside of a transactional model (this client is importing a file  
from an outside source, so don't you do it), it's unappealing to have  
to come up with ways of representing those in a 64-bit namespace.


Hashing isn't a terrible solution, assuming collisions don't become an  
issue; a well-designed hashtext64() would help a lot.

--
-- Christophe Pettus
   x...@thebuild.com


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

Re: [HACKERS] Proposal: String key space for advisory locks

[Christophe Pettus]

On Oct 26, 2009, at 5:24 PM, Itagaki Takahiro wrote:


Hmmm, hashtext() returns int32. ,
Can you reduce the collision issue if we had hashtext64()?


That would certainly reduce the chance of a collison considerably,  
assuming the right algorithm.


--
-- Christophe Pettus
   x...@thebuild.com


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

[HACKERS] Proposal: String key space for advisory locks

[Christophe Pettus]

Greetings,

I'd like to propose a potential patch, and wanted to get preliminary  
feedback on it before I started looking into the design.


Summary:Add a string key space to the advisory lock functionality.

Rationale:

Right now, the key spaces (the range of unique values that can be used  
as identity) for advisory locks are either a bigint or two ints.  This  
is, of course, technically more than one could imaginably need in any  
application.  The difficulty arises when the number of potential  
advisory locks is related to rows in one or more tables.


For example, suppose one wanted to use advisory locks to signal that a  
queue entry is being processed, and entries in that queue have a  
primary key that's also a bigint.  There's no problem; the advisory  
lock id is the primary key for the row.


And, then, one wants to use an advisory lock to signal that a  
particular record in another table is being processed in a long-term  
process.  One has a series of unappealing alternatives at that point,  
mostly involving encoding a table ID and the primary key of a record  
into the 64 bit number, or just hoping that the primary key doesn't  
overflow an int, and using the 2 x int form.


API Changes:

Overloading the various advisory lock functions to take a suitable  
string type (varchar(64)?) in addition to the bigint / 2 x int  
variations.  As with the bigint / 2 x int forms, this string namespace  
would be disjoint from the other key spaces.


Thanks in advance for any comments.
--
-- Christophe Pettus
   x...@thebuild.com


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

Re: [HACKERS] [GENERAL] contrib/plantuner - enable PostgreSQL planner hints

[Christophe Pettus]

On Oct 16, 2009, at 10:04 AM, decibel wrote:

Out of curiosity, did you look at doing hints as comments in a query?


I don't think that a contrib module could change the grammar.

--
-- Christophe Pettus
   x...@thebuild.com


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