Re: [BUGS] hstore parser incorrectly handles malformed input
On Fri, Apr 27, 2012 at 03:12, Tom Lane wrote: > Does anybody else have an opinion as to which of these solutions is > more preferable? > I think all unquoted whitespace should be ignored, so I prefer your solution. (note: I haven't actually tested it, I'm going off these emails) > And should we regard this as a back-patchable bug > fix, or a definition change suitable only for HEAD? > Since this is removing a syntax error and not creating one, I'd say it should be safe to backpatch.
Re: [BUGS] hstore parser incorrectly handles malformed input
On Fri, Apr 27, 2012 at 11:27:03AM +0200, Vik Reykja wrote:
> On Fri, Apr 27, 2012 at 03:12, Tom Lane wrote:
>
> > Does anybody else have an opinion as to which of these solutions is
> > more preferable?
> >
>
> I think all unquoted whitespace should be ignored, so I prefer your
> solution. (note: I haven't actually tested it, I'm going off these emails)
As long as we make it consistent on both sides of the '=>' (and document
it, too), then I don't really care either way. Currently you have to use
quotes to get an empty key, so I thought it natural to that you should
have to quote to get an empty value.
I've attached a modified version of Tom's patch which also allows empty
keys.
>
>
> > And should we regard this as a back-patchable bug
> > fix, or a definition change suitable only for HEAD?
> >
>
> Since this is removing a syntax error and not creating one, I'd say it
> should be safe to backpatch.
-Ryan Kelly
diff --git a/contrib/hstore/hstore_io.c b/contrib/hstore/hstore_io.c
index 0eb48cf..f03dcdc 100644
--- a/contrib/hstore/hstore_io.c
+++ b/contrib/hstore/hstore_io.c
@@ -74,7 +74,15 @@ get_val(HSParser *state, bool ignoreeq, bool *escaped)
}
else if (*(state->ptr) == '=' && !ignoreeq)
{
-elog(ERROR, "Syntax error near '%c' at postion %d", *(state->ptr), (int4) (state->ptr - state->begin));
+/* Empty key is perfectly OK */
+state->ptr--;
+return true;
+ }
+ else if (*(state->ptr) == ',' && ignoreeq)
+ {
+/* Empty value is perfectly OK */
+state->ptr--;
+return true;
}
else if (*(state->ptr) == '\\')
{
@@ -191,7 +199,7 @@ parse_hstore(HSParser *state)
if (st == WKEY)
{
if (!get_val(state, false, &escaped))
-return;
+return; /* end of string */
if (state->pcur >= state->plen)
{
state->plen *= 2;
@@ -236,7 +244,10 @@ parse_hstore(HSParser *state)
else if (st == WVAL)
{
if (!get_val(state, true, &escaped))
-elog(ERROR, "Unexpected end of string");
+ {
+/* end of string, treat as empty value */
+state->ptr--;
+ }
state->pairs[state->pcur].val = state->word;
state->pairs[state->pcur].vallen = hstoreCheckValLen(state->cur - state->word);
state->pairs[state->pcur].isnull = false;
--
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 #6619: Misleading output from slave when host is not running
The following bug has been logged on the website: Bug reference: 6619 Logged by: Petteri Räty Email address: petteri.r...@aalto.fi PostgreSQL version: 9.1.3 Operating system: Gentoo Linux Description: I setup a hot standby master and slave following instructions at: http://michael.otacoo.com/postgresql-2/postgres-9-1-setup-a-synchronous-stand-by-server-in-5-minutes/ I left archive mode off. When I started the slave without the master running I got the following output: $ postgres -D gsd-replica/ LOG: database system was interrupted while in recovery at log time 2012-04-25 12:01:33 UTC HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. LOG: entering standby mode WARNING: WAL was generated with wal_level=minimal, data may be missing HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup. FATAL: hot standby is not possible because wal_level was not set to "hot_standby" on the master server HINT: Either set wal_level to "hot_standby" on the master, or turn off hot_standby here. LOG: startup process (PID 28761) exited with exit code 1 LOG: aborting startup due to startup process failure The error message above on the FATAL line is wrong (or at least misleading). The real problem should be that it can't connect to the master. The wal_level on the master is hot_standby (captured after I started it): =# SHOW wal_level; wal_level - hot_standby (1 row) -- 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 #6619: Misleading output from slave when host is not running
On Fri, Apr 27, 2012 at 8:47 AM, wrote: > LOG: entering standby mode > WARNING: WAL was generated with wal_level=minimal, data may be missing > HINT: This happens if you temporarily set wal_level=minimal without taking > a new base backup. > FATAL: hot standby is not possible because wal_level was not set to > "hot_standby" on the master server > HINT: Either set wal_level to "hot_standby" on the master, or turn off > hot_standby here. > LOG: startup process (PID 28761) exited with exit code 1 > LOG: aborting startup due to startup process failure > > The error message above on the FATAL line is wrong (or at least misleading). > The real problem should be that it can't connect to the master. The > wal_level on the master is hot_standby (captured after I started it): The HINT that we should simply set something on the master is a little misleading with respect to timing. However, if the master and the standby aren't even connected and you know that, how did you expect there to be a causal link between the setting on the master and the state of the standby? What do you suggest the messages say? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] hstore parser incorrectly handles malformed input
Ryan Kelly writes: > As long as we make it consistent on both sides of the '=>' (and document > it, too), then I don't really care either way. Currently you have to use > quotes to get an empty key, so I thought it natural to that you should > have to quote to get an empty value. > I've attached a modified version of Tom's patch which also allows empty > keys. Hm ... I don't agree that keys and values are interchangeable, and I don't see that empty keys are a good thing (whereas empty values are clearly a reasonable edge case). So I think this is going a bit far; it seems to me it'd be giving up a lot of syntax-error detection capability in return for some not-actually-helpful symmetry. On the other hand, I seldom use hstore so I'm probably not the best person to be judging the appropriateness of these options. Any other votes out there? 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 #6620: Out of memory error
The following bug has been logged on the website: Bug reference: 6620 Logged by: sathish Email address: sath...@myc2s.com PostgreSQL version: 9.1.3 Operating system: windows Xp 32 bit Description: i have a Postgis table with about 2 million polyline records. The most number of points I have in the geometry field is about 500. I have a java program that does a select for most of these records and do some processing with them before writing them to a file. Unfortunately, I seem to keep getting this error: execute failed: out of memory for query result This query works fine with less select statement, my next largest table. I believe the program is failing at the first execute after prepare on this table. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] 9.1.3 backends getting stuck in 'startup'
A few times today, we've seen postgresql 9.1.3 backends on Ubuntu 11.10 x86_64
get stuck in 'startup' mode. By that I mean the set_ps_output mode. Postgres
is installed via Martin Pitt's packages.
It manifests like this:
Server has been humming along fine, then suddenly many backends get stuck in
'startup' mode and the ps output looks like so:
postgres 2425 3.5 0.3 8695020 483868 ? Ss 08:56 0:41 postgres:
consprod consprod 192.168.1.150(41606) startup
postgres 2427 4.1 0.3 8695020 511756 ? Ss 08:56 0:48 postgres:
consprod consprod 192.168.1.152(60876) startup
postgres 2429 3.5 0.3 8695020 497252 ? Ss 08:56 0:40 postgres:
consprod consprod 192.168.1.151(41930) startup
postgres 2431 3.7 0.3 8695020 502004 ? Ss 08:56 0:43 postgres:
consprod consprod 192.168.1.151(41933) startup
postgres 2435 3.8 0.3 8695020 439080 ? Ss 08:56 0:44 postgres:
consprod consprod 192.168.1.157(37656) startup
postgres 2437 3.5 0.3 8695020 482304 ? Ss 08:56 0:41 postgres:
consprod consprod 192.168.1.153(45071) startup
postgres 2438 3.4 0.3 8695020 506776 ? Ss 08:56 0:39 postgres:
consprod consprod 192.168.1.153(45072) startup
postgres 2441 3.7 0.3 8695020 495652 ? Ss 08:56 0:43 postgres:
consprod consprod 192.168.1.153(45075) startup
postgres 2442 3.7 0.3 8695020 503336 ? Ss 08:56 0:43 postgres:
consprod consprod 192.168.1.153(45076) startup
postgres 2443 4.0 0.3 8695020 511760 ? Ss 08:56 0:46 postgres:
consprod consprod 192.168.1.153(45077) startup
postgres 2445 3.4 0.3 8695020 478632 ? Ss 08:56 0:39 postgres:
consprod consprod 192.168.1.155(54413) startup
postgres 2446 3.7 0.3 8695020 464380 ? Ss 08:56 0:43 postgres:
consprod consprod 192.168.1.154(46186) startup
postgres 2448 3.8 0.3 8695024 474400 ? Ss 08:56 0:44 postgres:
consprod consprod 192.168.1.154(46187) startup
postgres 2449 3.8 0.3 8695020 464932 ? Rs 08:56 0:45 postgres:
consprod consprod 192.168.1.154(46216) startup
postgres 2450 3.3 0.3 8695020 515200 ? Ss 08:56 0:38 postgres:
consprod consprod 192.168.1.152(60890) startup
postgres 2452 3.3 0.3 8695020 493384 ? Ss 08:56 0:38 postgres:
consprod consprod 192.168.1.152(60892) startup
postgres 2453 3.7 0.3 8695020 490168 ? Ss 08:56 0:44 postgres:
consprod consprod 192.168.1.152(60893) startup
postgres 2456 3.4 0.3 8695020 449348 ? Ss 08:56 0:40 postgres:
consprod consprod 192.168.1.156(50829) startup
Restarting required '-m immediate'.
Stracing reveals lots of semops:
semop(589837, {{3, 1, 0}}, 1) = 0
semop(524299, {{11, 1, 0}}, 1) = 0
semop(557068, {{6, 1, 0}}, 1) = 0
semop(458761, {{2, 1, 0}}, 1) = 0
semop(557068, {{9, 1, 0}}, 1) = 0
read(8, ":\n\0\0\210\261\224\316\4\0\1\0\364\0\0 \0 \4
\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
read(8, ":\n\0\0\320\262\224\316\4\0\1\0\364\0\0 \0 \4
\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
backtrace looks like this:
0x7f7dd1d1f497 in semop () at ../sysdeps/unix/syscall-template.S:82
82 in ../sysdeps/unix/syscall-template.S
#0 0x7f7dd1d1f497 in semop () at ../sysdeps/unix/syscall-template.S:82
#1 0x7f7dd3d67b18 in PGSemaphoreLock (sema=0x7f7dce0e8210, interruptOK=0
'\000') at pg_sema.c:418
#2 0x7f7dd3da67c5 in LWLockAcquire (lockid=FirstBufMappingLock,
mode=LW_SHARED) at lwlock.c:464
#3 0x7f7dd3d91289 in BufferAlloc (foundPtr=0x7fff84d3b45e "",
strategy=0x7f7dd5f359e0, blockNum=201362, forkNum=MAIN_FORKNUM,
relpersistence=112 'p', smgr=) at bufmgr.c:531
#4 ReadBuffer_common (smgr=0x7f7dd5f39f00, relpersistence=112 'p',
forkNum=MAIN_FORKNUM, blockNum=201362, mode=RBM_NORMAL,
strategy=0x7f7dd5f359e0, hit=0x7fff84d3b4cf "") at bufmgr.c:325
#5 0x7f7dd3d91ca4 in ReadBufferExtended (reln=0x7f7dd3b17540,
forkNum=MAIN_FORKNUM, blockNum=201362, mode=RBM_NORMAL, strategy=) at bufmgr.c:246
#6 0x7f7dd3be3d46 in heapgetpage (scan=0x7f7dd5f351c0, page=201362) at
heapam.c:223
#7 0x7f7dd3be4732 in heapgettup (scan=0x7f7dd5f351c0, dir=, nkeys=2, key=0x7f7dd5f358a0) at heapam.c:556
#8 0x7f7dd3be52f5 in heap_getnext (scan=0x7f7dd5f351c0,
direction=) at heapam.c:1345
#9 0x7f7dd3e66167 in RelationBuildTupleDesc (relation=0x7f7dd3b23ee8) at
relcache.c:468
#10 RelationBuildDesc (targetRelId=, insertIt=1 '\001') at
relcache.c:882
#11 0x7f7dd3e66bdd in load_critical_index (indexoid=2655, heapoid=2603) at
relcache.c:3080
#12 0x7f7dd3e6839b in RelationCacheInitializePhase3 () at relcache.c:2890
#13 0x7f7dd3e7f931 in InitPostgres (in_dbname=,
dboid=2228467824, username=, out_dbname=0x0) at postinit.c:816
#14 0x7f7dd3db26d8 in PostgresMain (argc=, argv=, username=0x7f7dd5e4bc90 "consprod") at postgres.c:3650
#15 0x7f7dd3d75923 in BackendRun (port=0x7f7dd5e96ea0) at postmaster.c:3606
#16 BackendStartup (port=0x7f7dd5e96ea0) at postmaster.c:3291
#17 Serv
Re: [BUGS] 9.1.3 backends getting stuck in 'startup'
On 04/27/12 09:07, Jeff Frost wrote: > A few times today, we've seen postgresql 9.1.3 backends on Ubuntu 11.10 x86_64 > get stuck in 'startup' mode. By that I mean the set_ps_output mode. Postgres > is installed via Martin Pitt's packages. quick followup on this..when it happens, you can connect successfully to the postgres db, but as soon as you \c consprod, you get stuck. -- 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] 9.1.3 backends getting stuck in 'startup'
Jeff Frost writes: > A few times today, we've seen postgresql 9.1.3 backends on Ubuntu 11.10 x86_64 > get stuck in 'startup' mode. Well, the one you backtraced seems to be waiting for somebody else to release one of the buffer mapping locks ... which is not a lock I'd expect to get held long, ever. Could you check all the Postgres processes and see which of them have backtraces different from that? We need to figure out what's sitting on that lock and why. 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] Errors on CREATE TABLE IF NOT EXISTS
On Mon, Apr 23, 2012 at 7:49 AM, Matteo Beccati wrote: > I've tried to come up with a self-contained test case but I haven't been > able to replicate the error above. However the following script performs a > few concurrent CREATE TABLE IF NOT EXISTS statements that produce some > unexpected errors (using 9.1.2). > ERROR: duplicate key value violates unique constraint > "pg_type_typname_nsp_index" This is normal behavior for CREATE TABLE either with or without IF NOT EXISTS. CREATE TABLE does a preliminary check to see whether a name conflict exists. If so, it either errors out (normally) or exits with a notice (in the IF NOT EXISTS case). But there's a race condition: a conflicting transaction can create the table after we make that check and before we create it ourselves. If this happens, then you get the failure you're seeing, because the btree index machinery catches the problem when we do the actual system catalog inserts. Now, this is not very user-friendly, but we have no API to allow inserting into a table with a "soft" error if uniqueness would be violated. Had we such an API we could handle a number of situations more gracefully, including this one. Since we don't, the only option is to let the btree machinery error out if it must. The bottom line is that CREATE TABLE IF NOT EXISTS doesn't pretend to handle concurrency issues any better than regular old CREATE TABLE, which is to say not very well. You should use some other system to coordinate near-simultaneous creation of tables, such as perhaps doing pg_advisory_lock/CINE/pg_advisory_unlock. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 #6619: Misleading output from slave when host is not running
On Fri, Apr 27, 2012 at 3:47 AM, wrote: > When I started the slave without the master running I got the following > output: > > $ postgres -D gsd-replica/ > LOG: database system was interrupted while in recovery at log time > 2012-04-25 12:01:33 UTC > HINT: If this has occurred more than once some data might be corrupted and > you might need to choose an earlier recovery target. > LOG: entering standby mode > WARNING: WAL was generated with wal_level=minimal, data may be missing > HINT: This happens if you temporarily set wal_level=minimal without taking > a new base backup. > FATAL: hot standby is not possible because wal_level was not set to > "hot_standby" on the master server > HINT: Either set wal_level to "hot_standby" on the master, or turn off > hot_standby here. > LOG: startup process (PID 28761) exited with exit code 1 > LOG: aborting startup due to startup process failure > > The error message above on the FATAL line is wrong (or at least misleading). I think it's trying to tell you that you had wal_level=minimal configured on the master *at the time you took the base backup*. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] 9.1.3 backends getting stuck in 'startup'
On 04/27/12 10:14, Tom Lane wrote: > Jeff Frost writes: >> A few times today, we've seen postgresql 9.1.3 backends on Ubuntu 11.10 >> x86_64 >> get stuck in 'startup' mode. > Well, the one you backtraced seems to be waiting for somebody else to > release one of the buffer mapping locks ... which is not a lock I'd > expect to get held long, ever. Could you check all the Postgres > processes and see which of them have backtraces different from that? > We need to figure out what's sitting on that lock and why. > Thanks Tom. We've failed over to the replica which seems to have the issue happen less often, so I'll try and get the backtraces as soon as it's reproduced. -- 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] 9.1.3 backends getting stuck in 'startup'
On 04/27/12 11:54, Jeff Frost wrote: > On 04/27/12 10:14, Tom Lane wrote: >> Jeff Frost writes: >>> A few times today, we've seen postgresql 9.1.3 backends on Ubuntu 11.10 >>> x86_64 >>> get stuck in 'startup' mode. >> Well, the one you backtraced seems to be waiting for somebody else to >> release one of the buffer mapping locks ... which is not a lock I'd >> expect to get held long, ever. Could you check all the Postgres >> processes and see which of them have backtraces different from that? >> We need to figure out what's sitting on that lock and why. >> > Thanks Tom. We've failed over to the replica which seems to have the issue > happen less often, so I'll try and get the backtraces as soon as it's > reproduced. Alright, found one that's a little different (at least it wasn't in InitPostgres): #0 0x7f252f612297 in semop () at ../sysdeps/unix/syscall-template.S:82 #1 0x7f253160ccc0 in PGSemaphoreLock (sema=0x7f252ba65c00, interruptOK=0 '\000') at pg_sema.c:418 #2 0x7f253164cea5 in LWLockAcquire (lockid=, mode=LW_SHARED) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/lmgr/lwlock.c:464 #3 0x7f253163889a in BufferAlloc (smgr=0x7f2532c79f60, relpersistence=, forkNum=, blockNum=, mode=, strategy=, hit=0x7fffea5f9ecf "") at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:531 #4 ReadBuffer_common (smgr=0x7f2532c79f60, relpersistence=, forkNum=, blockNum=, mode=, strategy=, hit=0x7fffea5f9ecf "") at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:325 #5 0x7f25316391e4 in ReadBufferExtended (reln=0x7f231ee95438, forkNum=MAIN_FORKNUM, blockNum=166364, mode=RBM_NORMAL, strategy=0xecaf9628) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:246 #6 0x7f2531490fa3 in _bt_relandgetbuf (rel=0x7f231ee95438, obuf=77863, blkno=1, access=1) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/nbtree/nbtpage.c:651 #7 0x7f2531494cda in _bt_search (rel=0x7f231ee95438, keysz=1, scankey=0x7fffea5f9ff0, nextkey=, bufP=0x7fffea5fa9fc, access=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/nbtree/nbtsearch.c:121 #8 0x7f25314951bf in _bt_first (scan=, dir=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/nbtree/nbtsearch.c:841 #9 0x7f2531493954 in btgettuple (fcinfo=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/nbtree/nbtree.c:315 #10 0x7f2531721003 in FunctionCall2Coll (flinfo=0x0, collation=3932134720, arg1=1, arg2=18446744073709551615) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/fmgr/fmgr.c:1319 #11 0x7f253148d0d5 in index_getnext (scan=0x7f2532eff820, direction=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/index/indexam.c:499 #12 0x7f25316c392e in get_actual_variable_range (root=, vardata=, sortop=, min=, max=0x7f2532efc8e0) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/adt/selfuncs.c:4748 #13 0x7f25316c7969 in ineq_histogram_selectivity (root=, vardata=0x7fffea5fb250, opproc=, isgt=0 '\000', constval=, consttype=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/adt/selfuncs.c:805 #14 0x7f25316c810c in scalarineqsel (root=0x7f2532d18c00, operator=, isgt=-1 '\377', vardata=0x7fffea5fb250, constval=47095179, consttype=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/adt/selfuncs.c:535 #15 0x7f25316c85fc in mergejoinscansel (root=0x7f2532d18c00, clause=, opfamily=, strategy=, nulls_first=0 '\000', leftstart=0x7fffea5fb468, leftend=0x7fffea5fb460, rightstart=0x7fffea5fb458, rightend=0x7fffea5fb450) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/adt/selfuncs.c:2843 #16 0x7f25315e11d3 in cached_scansel (path=0x7f2532efbac0, root=0x7f2532d18c00, sjinfo=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/optimizer/path/costsize.c:2099 #17 cost_mergejoin (path=0x7f2532efbac0, root=0x7f2532d18c00, sjinfo=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/optimizer/path/costsize.c:1847 #18 0x7f2531605e40 in create_mergejoin_path (root=0x7f2532d18c00, joinrel=0x7f2532efbbf0, jointype=JOIN_LEFT, sjinfo=0x7f2532ef3180, outer_path=0x7f2532ef6e50, inner_path=, restrict_clauses=0x7f2532efc310, pathkeys=0x0, mergeclauses=0x7f2532efc540, outersortkeys=0x7f2532efc4f0, innersortkeys=0x7f2532efc590) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/optimizer/util/pathnode.c:1554 #19 0x7f25315e8559 in sort_inner_and_outer (root=0x7f2532d18c00, joinrel=0x7f2532efbbf0, outerrel=, innerrel=, jointype=, sjinfo=, restrictlist=0x7f2532efc310) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/optimizer/path/joinpath.c:305 #20 add_paths_to_joinrel (root=0x7f2532d18c00, joinrel=0x7f2532efbbf0, outerrel=, innerrel=, jointype=, sjinfo=, restrictlist=0x7f2532efc310) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/optimizer/path/
Re: [BUGS] 9.1.3 backends getting stuck in 'startup'
Jeff Frost writes: > Alright, found one that's a little different (at least it wasn't in > InitPostgres): It's still blocking at bufmgr.c:531 though ... so AFAICS this is another victim of somebody monopolizing a buffer mapping lock. 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] 9.1.3 backends getting stuck in 'startup'
On 04/27/12 12:17, Tom Lane wrote: > Jeff Frost writes: >> Alright, found one that's a little different (at least it wasn't in >> InitPostgres): > It's still blocking at bufmgr.c:531 though ... so AFAICS this is another > victim of somebody monopolizing a buffer mapping lock. > > Any idea what I should be looking for in the backtraces? I would imagine I can ignore any that are in InitPostgres, but that still leaves quite a few to look through. -- 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] 9.1.3 backends getting stuck in 'startup'
Jeff Frost writes: > Any idea what I should be looking for in the backtraces? > I would imagine I can ignore any that are in InitPostgres, but that still > leaves quite a few to look through. I think you can probably skip all that are blocked in LWLockAcquire called from bufmgr.c:531, at least for a first pass. Calls from elsewhere in bufmgr.c might be more interesting, and anything that's not blocked at an LWLockAcquire at all might be even more interesting. 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] 9.1.3 backends getting stuck in 'startup'
On 04/27/12 12:27, Tom Lane wrote: > Jeff Frost writes: >> Any idea what I should be looking for in the backtraces? >> I would imagine I can ignore any that are in InitPostgres, but that still >> leaves quite a few to look through. > I think you can probably skip all that are blocked in LWLockAcquire > called from bufmgr.c:531, at least for a first pass. Calls from > elsewhere in bufmgr.c might be more interesting, and anything that's not > blocked at an LWLockAcquire at all might be even more interesting. > > regards, tom lane Here we go. One that's not blocked on acquiring the lock, but does have bufmgr.c in it: #0 0x7f252f601a50 in __read_nocancel () at ../sysdeps/unix/syscall-template.S:82 #1 0x7f253163bced in read (file=, buffer=0x7f251f3a1fa0 ":\n", amount=) at /usr/include/bits/unistd.h:45 #2 FileRead (file=, buffer=0x7f251f3a1fa0 ":\n", amount=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/file/fd.c:1191 #3 0x7f25316547a6 in mdread (reln=, forknum=, blocknum=5825, buffer=0x7f251f3a1fa0 ":\n") at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/smgr/md.c:640 #4 0x7f25316387f1 in ReadBuffer_common (smgr=0x7f2532d38310, relpersistence=, forkNum=, blockNum=, mode=, strategy=, hit=0x7fffea5fb47f "") at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:438 #5 0x7f25316391e4 in ReadBufferExtended (reln=0x7f2532cf6990, forkNum=MAIN_FORKNUM, blockNum=5825, mode=RBM_NORMAL, strategy=0x4ff74188) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:246 #6 0x7f25314817bf in heapgetpage (scan=0x7f2532d335d0, page=5825) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/heap/heapam.c:223 #7 0x7f25314821ba in heapgettup (scan=0x7f2532d335d0, dir=, nkeys=, key=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/heap/heapam.c:556 #8 0x7f25314827bc in heap_getnext (scan=0x7f2532d335d0, direction=523902880) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/heap/heapam.c:1345 #9 0x7f2531711570 in RelationBuildTupleDesc (targetRelId=, insertIt=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:468 #10 RelationBuildDesc (targetRelId=, insertIt=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:882 #11 0x7f2531712f26 in RelationIdGetRelation (relationId=2616) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:1568 #12 0x7f2531485aab in relation_open (relationId=2616, lockmode=523902880) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/heap/heapam.c:907 #13 0x7f2531485b33 in heap_open (relationId=6, lockmode=523902880) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/heap/heapam.c:1077 #14 0x7f25317106d7 in LookupOpclassInfo (operatorClassOid=10035, numSupport=1) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:1278 #15 0x7f2531710c48 in IndexSupportInitialize (relation=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:1159 #16 RelationInitIndexAccessInfo (relation=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:1102 #17 0x7f2531711d91 in RelationBuildDesc (targetRelId=, insertIt=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:904 #18 0x7f253171201d in load_critical_index (indexoid=2693, heapoid=2618) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:3080 #19 0x7f25317124d2 in RelationCacheInitializePhase3 () at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:2892 #20 0x7f25317293e9 in InitPostgres (in_dbname=, dboid=, username=, out_dbname=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/init/postinit.c:816 #21 0x7f253165a908 in PostgresMain (argc=828737552, argv=, username=0x7f2532b90820 "monitoring") at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/tcop/postgres.c:3650 #22 0x7f2531619ad3 in BackendRun () at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:3606 #23 BackendStartup () at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:3291 #24 ServerLoop () at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:1455 #25 0x7f253161c57c in PostmasterMain (argc=851231024, argv=0x7f2532b8c8f0) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:1116 #26 0x7f25315baec3 in main (argc=3, argv=0x7f2532b8c8e0) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/main/main.c:199 -- Jeff Frost CTO, PostgreSQL Experts, Inc. Phone: 1-888-PG-EXPRT x506 FAX: 415-762-5122 http://www.pgexperts.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your sub
Re: [BUGS] 9.1.3 backends getting stuck in 'startup'
> I think you can probably skip all that are blocked in LWLockAcquire > called from bufmgr.c:531, at least for a first pass. Calls from > elsewhere in bufmgr.c might be more interesting, and anything that's not > blocked at an LWLockAcquire at all might be even more interesting. A few more that include bufmgr.c: #0 0x7f62b6150583 in __select_nocancel () at ../sysdeps/unix/syscall-template.S:82 #1 0x7f62b82948ca in pg_usleep (microsec=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/port/pgsleep.c:43 #2 0x7f62b8159678 in CheckpointWriteDelay (flags=, progress=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/bgwriter.c:722 #3 0x7f62b81806ab in BufferSync (flags=64) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:1283 #4 CheckPointBuffers (flags=64) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:1764 #5 0x7f62b7fea0e3 in CheckPointGuts (checkPointRedo=..., flags=64) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/transam/xlog.c:7989 #6 0x7f62b7ff5a3c in CreateCheckPoint (flags=64) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/transam/xlog.c:7797 #7 0x7f62b8159a46 in BackgroundWriterMain () at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/bgwriter.c:502 #8 0x7f62b80115c2 in AuxiliaryProcessMain (argc=, argv=0x7fff9045db00) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/bootstrap/bootstrap.c:422 #9 0x7f62b815fe8b in StartChildProcess (type=BgWriterProcess) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:4504 #10 0x7f62b8162ec2 in reaper (postgres_signal_arg=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:2424 #11 #12 0x7f62b6150583 in __select_nocancel () at ../sysdeps/unix/syscall-template.S:82 #13 0x7f62b81614d3 in ServerLoop () at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:1415 #14 0x7f62b816457c in PostmasterMain (argc=-1186270896, argv=0x7f62b9471190) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:1116 #15 0x7f62b8102ec3 in main (argc=5, argv=0x7f62b9471170) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/main/main.c:199 #0 0x7f62b6149a50 in __read_nocancel () at ../sysdeps/unix/syscall-template.S:82 #1 0x7f62b8183ced in read (file=, buffer=0x7f613ff51fa0 ":\n", amount=) at /usr/include/bits/unistd.h:45 #2 FileRead (file=, buffer=0x7f613ff51fa0 ":\n", amount=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/file/fd.c:1191 #3 0x7f62b819c7a6 in mdread (reln=, forknum=, blocknum=39946, buffer=0x7f613ff51fa0 ":\n") at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/smgr/md.c:640 #4 0x7f62b81807f1 in ReadBuffer_common (smgr=0x7f62b955d090, relpersistence=, forkNum=, blockNum=, mode=, strategy=, hit=0x7fff9045d5cf "") at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:438 #5 0x7f62b81811e4 in ReadBufferExtended (reln=0x7f62b7eef540, forkNum=MAIN_FORKNUM, blockNum=39946, mode=RBM_NORMAL, strategy=0x4ff74188) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:246 #6 0x7f62b7fc97bf in heapgetpage (scan=0x7f62b9558350, page=39946) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/heap/heapam.c:223 #7 0x7f62b7fca1ba in heapgettup (scan=0x7f62b9558350, dir=, nkeys=, key=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/heap/heapam.c:556 #8 0x7f62b7fca7bc in heap_getnext (scan=0x7f62b9558350, direction=1073029024) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/heap/heapam.c:1345 #9 0x7f62b8259570 in RelationBuildTupleDesc (targetRelId=, insertIt=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:468 #10 RelationBuildDesc (targetRelId=, insertIt=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:882 #11 0x7f62b825af26 in RelationIdGetRelation (relationId=2601) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:1568 #12 0x7f62b7fcdaab in relation_open (relationId=2601, lockmode=1073029024) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/heap/heapam.c:907 #13 0x7f62b7fcdb33 in heap_open (relationId=8, lockmode=1073029024) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/heap/heapam.c:1077 #14 0x7f62b8252d82 in SearchCatCache (cache=0x7f62b94c9e70, v1=403, v2=, v3=0, v4=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/catcache.c:1184 #15 0x7f62b8258a30 in RelationInitIndexAccessInfo (relation=0x7f62b7efa828) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/utils/cache/relcache.c:1010 #16 0x7f62b8259d91 in RelationBuildDesc (targetRelId=, insertIt=) at
Re: [BUGS] 9.1.3 backends getting stuck in 'startup'
It's tough to catch the thing in the act on the new server as they always eventually clear..though they still sit in startup for up to 30 seconds on occasion. A few here that look interesting: #0 0x7f62b6150583 in __select_nocancel () at ../sysdeps/unix/syscall-template.S:82 #1 0x7f62b82948ca in pg_usleep (microsec=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/port/pgsleep.c:43 #2 0x7f62b8195119 in s_lock (lock=0x7f60a5ff3540 "", file=0x7f62b83a7408 "/build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/lmgr/lwlock.c", line=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/lmgr/s_lock.c:113 #3 0x7f62b8194cb7 in LWLockRelease (lockid=38) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/lmgr/lwlock.c:593 #4 0x7f62b81808dd in BufferAlloc (smgr=0x7f62b95a2910, relpersistence=, forkNum=, blockNum=, mode=, strategy=, hit=0x7fff9045d92f "") at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:745 #5 ReadBuffer_common (smgr=0x7f62b95a2910, relpersistence=, forkNum=, blockNum=, mode=, strategy=, hit=0x7fff9045d92f "") at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:325 #6 0x7f62b81811e4 in ReadBufferExtended (reln=0x7f60a58bea30, forkNum=MAIN_FORKNUM, blockNum=14895, mode=RBM_NORMAL, strategy=0x18a994d6) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/storage/buffer/bufmgr.c:246 #7 0x7f62b7fd5110 in index_getnext (scan=0x7f62b978dc90, direction=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/access/index/indexam.c:514 #8 0x7f62b80e2946 in IndexNext (node=0x7f62b978cb08) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/nodeIndexscan.c:78 #9 0x7f62b80d77b7 in ExecScanFetch (node=0x7f62b978cb08, accessMtd=, recheckMtd=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/execScan.c:82 #10 ExecScan (node=0x7f62b978cb08, accessMtd=, recheckMtd=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/execScan.c:132 #11 0x7f62b80d0068 in ExecProcNode (node=0x7f62b978cb08) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/execProcnode.c:398 #12 0x7f62b80e51c7 in ExecMergeJoin (node=0x7f62b978c6b8) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/nodeMergejoin.c:1285 #13 0x7f62b80cffc8 in ExecProcNode (node=0x7f62b978c6b8) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/execProcnode.c:443 #14 0x7f62b80dba99 in ExecAppend (node=0x7f62b9783ee0) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/nodeAppend.c:209 #15 0x7f62b80d00a8 in ExecProcNode (node=0x7f62b9783ee0) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/execProcnode.c:375 #16 0x7f62b80dda58 in agg_fill_hash_table (node=0x7f62b97837d0) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/nodeAgg.c:1237 #17 ExecAgg (node=0x7f62b97837d0) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/nodeAgg.c:1007 #18 0x7f62b80cff78 in ExecProcNode (node=0x7f62b97837d0) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/execProcnode.c:466 #19 0x7f62b80ceed2 in ExecutePlan (queryDesc=0x7f62b9783130, direction=NoMovementScanDirection, count=0) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/execMain.c:1439 #20 standard_ExecutorRun (queryDesc=0x7f62b9783130, direction=NoMovementScanDirection, count=0) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/executor/execMain.c:313 #21 0x7f62b81a45c7 in PortalRunSelect (portal=0x7f62b94afdf0, forward=, count=0, dest=0x7f62b9543080) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/tcop/pquery.c:943 #22 0x7f62b81a5a30 in PortalRun (portal=, count=, isTopLevel=, dest=, altdest=, completionTag=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/tcop/pquery.c:787 #23 0x7f62b81a310c in exec_execute_message (argc=, argv=, username=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/tcop/postgres.c:1963 #24 PostgresMain (argc=, argv=, username=) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/tcop/postgres.c:3983 #25 0x7f62b8161ad3 in BackendRun () at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:3606 #26 BackendStartup () at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:3291 #27 ServerLoop () at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:1455 #28 0x7f62b816457c in PostmasterMain (argc=-1186270896, argv=0x7f62b9471190) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/postmaster/postmaster.c:1116 #29 0x7f62b8102ec3 in main (argc=5, argv=0x7f62b9471170) at /build/buildd/postgresql-9.1-9.1.3/build/../src/backend/main/main.c:199 = [Thread debugging using libthread_db enabled] PinBuffer (buf=0x7f60ac8748f0, strategy=0x0) at
Re: [BUGS] 9.1.3 backends getting stuck in 'startup'
Jeff Frost writes: > and I've got 81 more that do not contain bufmgr.c and are also not block on > LWLockAcquire. Hm ... no smoking gun in what you showed so far. I also took another look through 9.1 bufmgr.c, and I'm darned if I can see any code path there that holds a buffer mapping lock for any long interval. One possible theory is that you're using pg_buffercache_pages(), which does take all those locks. It tries its best to not hold them long, but with a sufficiently large buffer cache there could be an issue. (What is the shared_buffers setting on this installation, anyway?) 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] 9.1.3 backends getting stuck in 'startup'
On 04/27/12 17:30, Tom Lane wrote: > Jeff Frost writes: >> and I've got 81 more that do not contain bufmgr.c and are also not block on >> LWLockAcquire. > Hm ... no smoking gun in what you showed so far. I also took another > look through 9.1 bufmgr.c, and I'm darned if I can see any code path > there that holds a buffer mapping lock for any long interval. > > One possible theory is that you're using pg_buffercache_pages(), which > does take all those locks. It tries its best to not hold them long, > but with a sufficiently large buffer cache there could be an issue. > (What is the shared_buffers setting on this installation, anyway?) > Tom, what does the startup code path read? The list of relations or something? Just wondering what the contention would be. Oh, good idea! Looks like pg_buffercache is installed in this DB. Customer reports that it has been installed since the server has existed (and on the previous server) but is not currently being used, though the issue with the hanging startups did not start until this morning. Could it still cause contention even if it's not being executed? shared_buffers is 8GB on this machine which has 128GB of RAM. It seems the pooling software that's being used has a max connection lifetime of 1hr which is what makes this so painful. -- Jeff Frost CTO, PostgreSQL Experts, Inc. Phone: 1-888-PG-EXPRT x506 FAX: 415-762-5122 http://www.pgexperts.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] 9.1.3 backends getting stuck in 'startup'
On 04/27/12 17:45, Jeff Frost wrote: > Oh, good idea! Looks like pg_buffercache is installed in this DB. Customer > reports that it has been installed since the server has existed (and on the > previous server) but is not currently being used, though the issue with the > hanging startups did not start until this morning. Could it still cause > contention even if it's not being executed? BTW, just to be safe, it is now removed. -- 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] 9.1.3 backends getting stuck in 'startup'
Jeff Frost writes: > Oh, good idea! Looks like pg_buffercache is installed in this DB. Customer > reports that it has been installed since the server has existed (and on the > previous server) but is not currently being used, though the issue with the > hanging startups did not start until this morning. Could it still cause > contention even if it's not being executed? No, it shouldn't. Perhaps we should start asking questions around the idea of "so what changed this morning?". There has to have been some triggering cause, a configuration change or application change or something. 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] log_collector doesn't respond to reloads
> Well, sorry, but you can't have that. SHOW will tell you what your own > backend thinks the value of the GUC variable is, but there is no way to > know what's happening inside the logging collector process. And I'd be > against trying to add a signaling mechanism that would support telling > you that, because it would add fragility to the logging collector, which > we don't want. Hmmm. There's really no feasible way to determine where the actual logging collector is logging to? > I don't buy that argument. If things are working as intended, the > collector ought to create a new file in the commanded location > immediately. I would think any normal DBA would look for that, just > to check that the SIGHUP had worked. You're assuming that the change is happening attended. Consider automated changes being deployed via puppet or chef, possibly across many servers. You can end up in a situation where the logs aren't going where they're supposed to due to some external problem, and that the DBA has no way to find out what went wrong because he doesn't know where the logs are *now*. Mind you, I don't have a solution to suggest, but I think it's a real problem. And fixing the rotation bug will help that a lot; it would narrow down the problem to the current day/hour, which would make it much more likely that someone would know that the log location had changed. > BTW, what log messages were you getting exactly? I'd have expected > something about "could not open log file" as well as the "disabling > automatic rotation" one. 2012-04-26 16:38:21 PDT [10180]: [2-1] user=,db= LOG: received SIGHUP, reloading configuration files 2012-04-26 16:38:21 PDT [10181]: [1-1] user=,db= LOG: could not open log file "/pglogs/check/logs/datacollection-2012-04-26-16-38": No such file or directory 2012-04-26 16:38:21 PDT [10181]: [2-1] user=,db= LOG: disabling automatic rotation (use SIGHUP to re-enable) So, yes, exactly. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] 9.1.3 backends getting stuck in 'startup'
On 04/27/12 18:15, Tom Lane wrote: > No, it shouldn't. Perhaps we should start asking questions around the > idea of "so what changed this morning?". There has to have been some > triggering cause, a configuration change or application change or > something. Oh, we've been asking those questions all day long! No packages were installed, postgresql.conf last edited on 3/29/2012. Last application update 4/24/2012. There were some DDL changes, but those were reverted and the problem persists. To make it more interesting, today is a slow day. And we just saw the issue recur with pg_buffercache uninstalled. :-/ -- Jeff Frost CTO, PostgreSQL Experts, Inc. Phone: 1-888-PG-EXPRT x506 FAX: 415-762-5122 http://www.pgexperts.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] 9.1.3 backends getting stuck in 'startup'
On 04/27/12 18:27, Jeff Frost wrote: > To make it more interesting, today is a > slow day. And since it's a slow day..one more question..any further logging we could do to help find the culprit? -- 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] log_collector doesn't respond to reloads
Josh Berkus writes: > You can end up in a situation where the logs aren't going where they're > supposed to due to some external problem, and that the DBA has no way to > find out what went wrong because he doesn't know where the logs are *now*. Well, if nothing else, lsof would help. Another possibility is that we might change the logging collector process to show its current target filename in ps status (although might there be security/privacy issues with that?). Neither of those things will help Windows users of course, but the sorts of cases you're presenting aren't going to be happening on Windows boxes. [ thinks some more... ] A lower-tech solution would be to always write the name of the current log target file into some small text file in $PGDATA. On the whole though, I think this is an invented problem. We've never heard a complaint from the field about it. 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 #6621: Constant Segmentation Faults in the Postmaster
The following bug has been logged on the website: Bug reference: 6621 Logged by: Kris Bushover Email address: k...@spiceworks.com PostgreSQL version: 9.1.3 Operating system: Red Hat Enterprise Linux Server release 6.2 (Santi Description: We have been getting constant segmentation faults (signal 11) when running PostgreSQL 9.1. We have replaced the hardware (RAM), updated PG to the latest version, and made changes to our configuration file as recommended by EnterpriseDB. I was able to get a core dump after the last fault, pasted below. Core was generated by `postgres: dbuser db_production 10.10.11.66(46200) EXPLAIN'. Program terminated with signal 11, Segmentation fault. #0 AllocSetAlloc (context=0x296f060, size=16) at aset.c:639 639 set->freelist[fidx] = (AllocChunk) chunk->aset; (gdb) bt #0 AllocSetAlloc (context=0x296f060, size=16) at aset.c:639 #1 0x0070b725 in MemoryContextAllocZeroAligned (context=0x296f060, size=16) at mcxt.c:571 #2 0x005b6ab5 in makeString (str=0x7f096602eb90 "@@") at value.c:55 #3 0x0068c720 in generate_operator_name (operid=3636, arg1=3614, arg2=3615) at ruleutils.c:7148 #4 0x0068f9f3 in get_oper_expr (node=0x2b55bc0, context=0x7fffba0fbc10, showimplicit=) at ruleutils.c:5762 #5 get_rule_expr (node=0x2b55bc0, context=0x7fffba0fbc10, showimplicit=) at ruleutils.c:4920 #6 0x0068f948 in get_rule_expr (node=0x2b558d0, context=0x7fffba0fbc10, showimplicit=) at ruleutils.c:5004 #7 0x0069385d in deparse_expression_pretty (expr=0x2b558d0, dpcontext=0x2bae6f8, forceprefix=, showimplicit=0 '\000', prettyFlags=0, startIndent=0) at ruleutils.c:2118 #8 0x0051f434 in show_expression (node=0x2b558d0, qlabel=0x80ae9c "Filter", planstate=, ancestors=, useprefix=0 '\000', es=0x7fffba0fc4f0) at explain.c:1322 #9 0x005201d1 in ExplainNode (planstate=0x2b8a400, ancestors=0x2b5f3e0, relationship=0x80afb5 "Outer", plan_name=, es=0x7fffba0fc4f0) at explain.c:1045 #10 0x00520109 in ExplainNode (planstate=0x2b8a190, ancestors=0x2b5f3e0, relationship=0x80afb5 "Outer", plan_name=, es=0x7fffba0fc4f0) at explain.c:1197 #11 0x00520109 in ExplainNode (planstate=0x2b89ef0, ancestors=0x2b5f3e0, relationship=0x80afc1 "Subquery", plan_name=, es=0x7fffba0fc4f0) at explain.c:1197 #12 0x005204b9 in ExplainNode (planstate=0x2b89620, ancestors=0x2b5f3e0, relationship=0x80b02c "Member", plan_name=, es=0x7fffba0fc4f0) at explain.c:1234 #13 0x005218a5 in ExplainMemberNodes (plans=, planstates=, ancestors=0x2b5f3e0, es=0x7fffba0fc4f0) at explain.c:1697 #14 0x005201f3 in ExplainNode (planstate=0x2b78e70, ancestors=0x2b5f3e0, relationship=0x80afb5 "Outer", plan_name=, es=0x7fffba0fc4f0) at explain.c:1229 #15 0x00520109 in ExplainNode (planstate=0x2b78340, ancestors=0x2b5f3e0, relationship=0x80afb5 "Outer", plan_name=, es=0x7fffba0fc4f0) at explain.c:1197 #16 0x00520109 in ExplainNode (planstate=0x2b77a50, ancestors=0x2b5f3e0, relationship=0x80afb5 "Outer", plan_name=, es=0x7fffba0fc4f0) at explain.c:1197 #17 0x00520109 in ExplainNode (planstate=0x2b71460, ancestors=0x2b5f3e0, relationship=0x80afb5 "Outer", plan_name=, es=0x7fffba0fc4f0) at explain.c:1197 #18 0x00520109 in ExplainNode (planstate=0x2b711f0, ancestors=0x2b5f3e0, relationship=0x0, plan_name=, es=0x7fffba0fc4f0) at explain.c:1197 #19 0x00521259 in ExplainOnePlan (plannedstmt=0x2b5b310, es=0x7fffba0fc4f0, queryString=, params=0x0) at explain.c:404 #20 0x0052170b in ExplainOneQuery (stmt=0x2981830, queryString=, params=0x0, dest=0x29709e8) at explain.c:296 #21 ExplainQuery (stmt=0x2981830, queryString=, params=0x0, dest=0x29709e8) at explain.c:202 #22 0x00634137 in PortalRunUtility (portal=0x296c8a0, utilityStmt=0x2981830, isTopLevel=1 '\001', dest=0x29709e8, completionTag=0x7fffba0fc5a0 "") at pquery.c:1184 #23 0x006353bd in FillPortalStore (portal=0x296c8a0, isTopLevel=1 '\001') at pquery.c:1058 #24 0x006358ff in PortalRun (portal=0x296c8a0, count=9223372036854775807, isTopLevel=1 '\001', dest=0x2928ab0, altdest=0x2928ab0, completionTag=0x7fffba0fc920 "") at pquery.c:782 #25 0x006331ee in exec_execute_message (argc=, argv=, username=) at postgres.c:1963 #26 PostgresMain (argc=, argv=, username=) at postgres.c:3983 #27 0x005f46c9 in BackendRun () at postmaster.c:3606 #28 BackendStartup () at postmaster.c:3291 #29 ServerLoop () at postmaster.c:1455 #30 0x005f6e5c in PostmasterMain (argc=, argv=) at postmaster.c:1116 #31 0x00598a40 in main (argc=5, argv=0x28524d0) at main.c:199 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] ld.so.1: initdb: fatal: libxslt.so.1: open failed: No such file or directory
To Support, I'm assisting our dba on this issue during a db setup. When the dba runs: initdb -D directory_path, the following errors results: dbadev-postgres[1]% initdb -D /u01/app/postgres/postgres_data ld.so.1: initdb: fatal: libxslt.so.1: open failed: No such file or directory Killed Please advise any solution/recommendation to resolve the setup. Thanks, Mitchell Ng DTIC-ZT Unix Systems Administrator Ft. Belvoir, VA 703-767-7048 (office) -- 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] 9.1.3 backends getting stuck in 'startup'
On Apr 27, 2012, at 6:34 PM, Jeff Frost wrote: > On 04/27/12 18:27, Jeff Frost wrote: >> To make it more interesting, today is a >> slow day. > > And since it's a slow day..one more question..any further logging we could do > to help find the culprit? FYI, reindexed the system tables and reduced shared_buffers to 2GB. Still seeing the issue with regularity. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
