The following bug has been logged online:

Bug reference: 6200
Logged by: Daniel Farina
Email address: daniel (AT) heroku (DOT) com
PostgreSQL version: 9.0.4
Operating system: Ubuntu 10.04
Description: standby bad memory allocations on SELECT
Details:

A huge thanks to Conrad Irwin of Rapportive for furnishing virtually all the
details of this bug report.

The following error occurs up to a couple of times a day on a busy
PostgreSQL database:

ERROR: invalid memory alloc request size 18446744073709551613

The occurrence rate is somewhere in the one per tens-of-millions of
queries.

The number is always the same (2**64 - 3), and there's no obvious
pattern in the distribution of errors (they don't even appear to be
correlated with system load). The error has not been recorded on the
primary database, even though the same workload is submitted.

These errors do not reproduce, seeming to evaporate almost immediately on
the standby, so durable/long lasting index corruption is not likely. This
problem has persisted among multiple generations of hot standbys on
different hardware and sourced from different base backups.

At least once, a hot standby was promoted to a primary and the errors seem
to discontinue, but then reappear on a newly-provisioned standby.

The VERSION() string is:
PostgreSQL 9.0.4 on x86_64-pc-linux-gnu, compiled by GCC
gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit

The problem is confined to a particular access patterns and schema objects,
enumerated below:

The points2 table looks like:

Table "public.points2"
Column | Type |
Modifiers
------------------+-----------------------------+---------------------------
---------------------------
id | integer | not null default nextval('points2_id_seq'::regclass)
identifier | text | not null
scope_id | integer | not null
class_number | smallint | not null
authorization_id | integer | not null
sum_json | text | not null
amended_at | timestamp without time zone | not null
Indexes:
"points2_pkey" PRIMARY KEY, btree (id)
"points2_special" btree (identifier_hash(identifier), scope_id,
class_number, authorization_id)

CREATE FUNCTION identifier_hash(text) RETURNS bigint IMMUTABLE
LANGUAGE SQL AS $$
SELECT ('x' || md5($1))::bit(64)::bigint;
$$;

This has only been seen on queries of the form:

SELECT * FROM "points2" WHERE
(identifier_hash(identifier) = identifier_hash('1104131405')
AND identifier = '1104131405'
AND scope_id = 0
AND authorization_id = 0
AND class_number = 25)

Though this table is accessed similarly frequently by queries of the form:

SELECT points2.* FROM points2
JOIN (VALUES (8585261297509044776, 0, 47,
'ae9064e6f24127c6a1f483cd71e14e64'))
AS query(hashed_identifier, scope_id, class_number, identifier)
ON identifier_hash(points2.identifier) = query.hashed_identifier
AND points2.scope_id = query.scope_id
AND points2.class_number = query.class_number
AND points2.identifier = query.identifier;

these do not trigger the problem.

The table is always updated to or inserted into one row at a time
(using the "id" primary key for updates), though we sometimes update
multiple rows in a single transaction, synchronous_commit is turned off for
connections that touch the points2 table on the primary.

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

"Daniel Farina" <daniel (AT) heroku (DOT) com> writes:
This isn't really enough information to reproduce the problem ...

.... and that statement is going to discourage anyone from even trying,
since with such a low occurrence rate it's going to be impossible to be
sure whether the setup to reproduce the problem is correct. So if you'd
like this to be fixed, you're either going to need to show us exactly
how to reproduce it, or investigate it yourself.

The way that I'd personally proceed to investigate it would probably be
to change the "invalid memory alloc request size" size errors (in
src/backend/utils/mmgr/mcxt.c; there are about four occurrences) from
ERROR to PANIC so that they'll provoke a core dump, and then use gdb
to get a stack trace, which would provide at least a little more
information about what happened. However, if you are only able to
reproduce it in a production server, you might not like that approach.
Perhaps you can set up an extra standby that's only there for testing,
so you don't mind if it crashes?

regards, tom lane

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

On 09.09.2011 18:02, Tom Lane wrote:
If that's not possible or doesn't reproduce the issue, there's also
functions in glibc to produce a backtrace without aborting the program:
https://www.gnu.org/s/libc/manual/ht...acktraces.html.

I think you could also fork() + abort() to generate a core dump, not
just a backtrace.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

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

On Thu, Sep 8, 2011 at 11:33 PM, Daniel Farina <daniel (AT) heroku (DOT) com> wrote:

So the query that fails is a btree index on a hot standby. I don't
fully accept it as an HS bug, but lets assume that it is and analyse
what could cause it.

The MO is certain user queries, only observed in HS. So certain
queries might be related to the way we use indexes or not.

There is a single and small difference between how a btree index
operates in HS and "normal" operation, which relates to whether we
kill tuples in the index. That's simple code and there's no obvious
bugs there, nor anything that specifically allocates memory even. So
the only bug that springs to mind is something related to how we
navigate hot chains with/without killed tuples. i.e. the bug is not
actually HS related, but is only observed under conditions typical in
HS.

HS touches almost nothing else in user space, apart from snapshots. So
there could be a bug there also, maybe in CopySnapshot().

--
*Simon Riggs****************** http://www.2ndQuadrant.com/
*PostgreSQL Development, 24x7 Support, Training & Services

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

Hello,
We upgraded to postgres 9.1.2 two weeks ago, and we are also experiencing
an issue that seems very similar to the one reported as bug 6200. We see
approximately 2 dozen alloc errors per day across 3 slaves, and we are
getting one segfault approximately every 3 days. We did not experience
this issue before our upgrade (we were on version 8.4, and used skytools
for replication).

We are attempting to get a core dump on segfault (our last attempt did not
work due to a config issue for the core dump). We're also attempting to
repro the alloc errors on a test setup, but it seems like we may need quite
a bit of load to trigger the issue. We're not certain that the alloc
issues and the sefaults are "the same issue" - but it seems that it may be
since the OP for bug 6200 sees the same behavior. We have seen no issues
on the master, all alloc errors and segfaults have been on the slaves.

We've seen the alloc errors on a few different tables, but most frequently
on logins. Rows are added to the logins table one-by-one, and updates
generally happen one row at a time. The table is pretty basic, it looks
like this...

CREATE TABLE logins
(
login_id bigserial NOT NULL,
<snip - a bunch of columns>
CONSTRAINT logins_pkey PRIMARY KEY (login_id ),
<snip - some other constraints...>
)
WITH (
FILLFACTOR=80,
OIDS=FALSE
);

The queries that trigger the alloc error on this table look like this (we
use hibernate hence the funny underscoring...)
select login0_.login_id as login1_468_0_, l... from logins login0_ where
login0_.login_id=$1

The alloc error in the logs looks like this:
-01-12_080925.log:2012-01-12 17:33:46 PST [16034]: [7-1] [24/25934] ERROR:
invalid memory alloc request size 18446744073709551613

The alloc error is nearly always for size 18446744073709551613 - though we
have seen one time where it was a different amount...

We have been in touch with the OP for bug 6200, who said he may have time
to help us out a bit on debugging this. It seems like what is being
suggested is getting a build of postgres that will capture a stack trace
for each alloc issue and/or simply dump core when that happens. As this is
a production system we would prefer the former. As I mentioned above we're
also trying to get a core dump for the segfault.

We are treating this as extremely high priority as it is currently causing
2 dozen failures for users of our site per day, as well as a few min of
downtime for the segfault every 3 days. I realize there may be little that
the postgres experts can do until we provide more information - but since
our use case is really not very complicated here (basic use of HS), and
another site is also experiencing it, I figured it would be worth posting
about what we're seeing.

Thanks,
-Bridget Frey
Redfin

On Mon, Jan 23, 2012 at 3:22 PM, Bridget Frey <bridget.frey (AT) redfin (DOT) com> wrote:
Hmm, that number in hex works out to 0xfffffffffffffffd, which makes
it sound an awful lot like the system (for some unknown reason)
attempted to allocate -3 bytes of memory. I've seen something like
this once before on a customer system running a modified version of
PostgreSQL. In that case, the problem turned out to be page
corruption. Circumstances didn't permit determination of the root
cause of the page corruption, however, nor was I able to figure out
exactly how the corruption I saw resulted in an allocation request
like this. It would be nice to figure out where in the code this is
happening and put in a higher-level guard so that we get a better
error message.

You want want to compile a modified PostgreSQL executable that puts an
extremely long sleep (like a year) just before this error is reported.
Then, when the system hangs at that point, you can attach a debugger
and pull a stack backtrace. Or you could insert an abort() at that
point in the code and get a backtrace from the core dump.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Thanks for the info - that's very helpful. We had also noted that the
alloc seems to be -3 bytes. We have run pg_check and it found no instances
of corruption. We've also replayed queries that have failed, and have never
been able to get the same query to fail twice. In the case you
investigated, was there permanent page corruption - e.g. you could run the
same query over and over and get the same result?

It really does seem like this is an issue either in Hot Standby or very
closely related to that feature, where there is temporary corruption of a
btree index that then disappears. Our master is not experiencing any
malloc issues, while the 3 slaves get about a dozen per day, despite
similar workloads. We haven't have a slave segfault since we set it up to
produce a core dump, but we're expecting to have that within the next few
days (assuming we'll continue to get a segfault every 3-4 days). We're
also planning to set up one slave that will panic when it gets a malloc
issue, as you (and other posters on 6400) had suggested.

Thanks again for the help, and we'll keep you posted as we learn more...
-B

On Fri, Jan 27, 2012 at 6:31 AM, Robert Haas <robertmhaas (AT) gmail (DOT) com> wrote:



--
Bridget Frey Director, Data & Analytics Engineering | Redfin

bridget.frey (AT) redfin (DOT) com | tel: 206.576.5894

On Fri, Jan 27, 2012 at 1:31 PM, Bridget Frey <bridget.frey (AT) redfin (DOT) com> wrote:
Yes. I observed that the infomask bits on several tuples had somehow
been overwritten by nonsense. I am not sure whether there were other
kinds of corruption as well - I suspect probably so - but that's the
only one I saw with my own eyes, courtesy of pg_filedump.

The case I investigated involved corruption on the master, and I think
it predated Hot Standby. However, the symptom is generic enough that
it seems quite possible that there's more than one way for it to
happen. :-(

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

I work with Bridget at Redfin.

We have a core dump from a once-in-5-days (multi-million queries) hot
standby segfault in pg 9.1.2 . (It might or might be the same root issue as
the "alloc" errors. If I should file a new bug report, let me know.

The postgres executable that crashed did not have debugging symbols
installed, and we were unable to debug (gdb) the core file using a debug
build of postgres. (Symbols didn't match.) Running gdb against a non-debug
postgres executable gave us this stack trace:


[root@query-7 core]# gdb -q -c /postgres/core/query-9.core.19678
/usr/pgsql-9.1/bin/postgres-non-debug
Reading symbols from /usr/pgsql-9.1/bin/postgres-non-debug...(no debugging
symbols found)...done.

warning: core file may not match specified executable file.
[New Thread 19678]

warning: no loadable sections found in added symbol-file system-supplied
DSO at 0x7fffdcd58000
Core was generated by `postgres: datamover stingray_prod 10.11.0.134(54140)
SELEC'.
Program terminated with signal 11, Segmentation fault.
#0 0x000000000045694c in nocachegetattr ()



(gdb) bt
#0 0x000000000045694c in nocachegetattr ()
#1 0x00000000006f93c9 in ?? ()
#2 0x00000000006fa231 in tuplesort_puttupleslot ()
#3 0x0000000000573ad1 in ExecSort ()
#4 0x000000000055cdda in ExecProcNode ()
#5 0x000000000055bcd1 in standard_ExecutorRun ()
#6 0x0000000000623594 in ?? ()
#7 0x0000000000624ae0 in PortalRun ()
#8 0x00000000006220f2 in PostgresMain ()
#9 0x00000000005e6ba4 in ?? ()
#10 0x00000000005e791c in PostmasterMain ()
#11 0x000000000058b9ae in main ()



We have the (5GB) core file, and are happy to do any more forensics anyone
can advise.

Please instruct.

I hope this helps point to a root cause and resolution....

Thank you,

Mike Brauwerman
Data Team, Redfin

On Fri, Jan 27, 2012 at 10:53 AM, Robert Haas <robertmhaas (AT) gmail (DOT) com> wrote:



--
Mike Brauwerman
Data Team, Redfin

On 28 January 2012 21:34, Michael Brauwerman
<michael.brauwerman (AT) redfin (DOT) com> wrote:
Ideally, you'd be able to install debug information packages, which
should give a more detailed and useful stack trace, as described here:

http://wiki.postgresql.org/wiki/Gett...d_on_Linux/BSD

--
Peter Geoghegan * * * http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

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