[BUGS] Crash 8.2.5 backend using tsearch2
Hi. I've tracked down some crashes we are having and produced a test case using the nasty data. The sample data used to trigger the crash is 6MB in size. It doesn't crash immediately, instead chewing up CPU for several minutes before crashing. http://launchpadlibrarian.net/9501485/crashme.sql I've only tried reproducing this with latest released Ubuntu packages (Feisty) for 8.2.5. Also reported in the Ubuntu bug tracker too: https://bugs.launchpad.net/ubuntu/+source/postgresql/+bug/144740 -- Stuart Bishop <[EMAIL PROTECTED]> http://www.stuartbishop.net/ signature.asc Description: OpenPGP digital signature
[BUGS] Nasty tsvector can make dumps unrestorable
To continue our streak of bad luck, here is the second tsearch2 bug we found
this week.
The attached script creates a tsvector with a value that can be dumped using
pg_dump, but not loaded again using pg_restore. This causes restores of a
dump containing this value to fail.
This script only tested with PG 8.2.5 under Ubuntu Feisty so far, although
we found the original problem under 8.2.4 on Ubuntu Dapper.
Also reported in the Ubuntu bug tracker at:
https://bugs.launchpad.net/ubuntu/+source/postgresql-8.2/+bug/146382
--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
\i /usr/share/postgresql/8.2/contrib/tsearch2.sql
CREATE TABLE Foo(bar tsvector);
INSERT INTO Foo(bar) VALUES ('''0'':58A ''1'':92A,127A ''2'':5A ''e'':146A,151A ''n'':148A ''r'':147A ''10'':51A ''12'':50A ''45'':52A ''ad'':149A ''id'':32A ''ip'':86A ''nt'':68A ''200'':57A ''5.0'':69A ''5.5'':66A ''bug'':6A,12A ''e.g'':145A ''get'':54A ''one'':11A,17A ''ran'':9A ''tri'':95A ''use'':119A,138A ''255c'':61A ''8303'':38A,94A,133A ''blob'':35A,130A ''give'':106A ''hint'':137A ''hole'':75A ''line'':104A,126A ''msie'':65A ''null'':93A ''psql'':102A ''tool'':105A ''valu'':37A,132A ''warn'':116A,117A,167A ''zope'':4A,45A,81A ''+0200'':53A ''along'':111A ''error'':26A,28A,109A,163A ''escap'':121A,140A,144A ''extra'':115A ''found'':40A,135A ''liter'':125A ''secur'':78A ''start'':154A ''anonym'':48A ''compat'':64A ''execut'':97A ''follow'':42A,114A ''import'':7A ''insert'':20A,29A ''is/was'':84A ''logfil'':46A ''messag'':36A,110A,131A ''ncould'':71A ''postgr'':14A ''result'':158A ''server'':90A ''string'':124A,141A,157A ''syntax'':27A,142A,162A ''window'':67A ''address'':87A ''command'':103A ''content'':33A,128A ''generat'':22A ''sequenc'':34A,129A ''t312461'':70A ''webserv'':82A ''without'':165A ''/scripts'':60A ''complain'':15A ''http/1.1'':56A ''sqlobject'':24A ''statement'':21A,99A ''02/may/2002'':49A ''mozilla/4.0'':63A ''nonstandard'':118A ''213.46.5.117'':59A,83A ''messagechunk'':31A ''/p_/zopebutton'':55A ''programmingerror'':25A ''nystrom.no-ip.org'':91A ''np3ee224f1.dip.t-dialin.net'':47A ''255cwinnt/system32/cmd.exe?/c+dir+c:'':62A');
signature.asc
Description: OpenPGP digital signature
[BUGS] BUG #5857: pg_restore --clean dropping type too soon
The following bug has been logged online: Bug reference: 5857 Logged by: Stuart Bishop Email address: stu...@stuartbishop.net PostgreSQL version: 8.4.6 Operating system: Ubuntu 10.10 Description:pg_restore --clean dropping type too soon Details: "pg_restore --clean" appears to have an ordering problem, where a custom type is being dropped before some functions that use that custom type as a parameter, which fails. $ psql -d foo -f /usr/share/postgresql/8.4/contrib/debversion.sql > /dev/null psql:/usr/share/postgresql/8.4/contrib/debversion.sql:28: NOTICE: return type debversion is only a shell psql:/usr/share/postgresql/8.4/contrib/debversion.sql:34: NOTICE: argument type debversion is only a shell psql:/usr/share/postgresql/8.4/contrib/debversion.sql:40: NOTICE: return type debversion is only a shell psql:/usr/share/postgresql/8.4/contrib/debversion.sql:46: NOTICE: argument type debversion is only a shell $ pg_dump --format=c foo | pg_restore --clean | grep debversion [...] DROP FUNCTION public.debversion(character); DROP TYPE public.debversion CASCADE; DROP FUNCTION public.debversionsend(debversion); [...] The relevant part of debversion.sql seems to be: CREATE TYPE debversion; CREATE OR REPLACE FUNCTION debversionin(cstring) RETURNS debversion AS 'textin' LANGUAGE 'internal' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION debversionout(debversion) RETURNS cstring AS 'textout' LANGUAGE 'internal' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION debversionrecv(internal) RETURNS debversion AS 'textrecv' LANGUAGE 'internal' STABLE STRICT; CREATE OR REPLACE FUNCTION debversionsend(debversion) RETURNS bytea AS 'textsend' LANGUAGE 'internal' STABLE STRICT; CREATE TYPE debversion ( LIKE = text, INPUT = debversionin, OUTPUT = debversionout, RECEIVE= debversionrecv, SEND = debversionsend, -- make it a non-preferred member of string type category CATEGORY = 'S', PREFERRED = 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 #6528: pglesslog still referenced in docs, but no 9.1 support
The following bug has been logged on the website: Bug reference: 6528 Logged by: Stuart Bishop Email address: stuart.bis...@canonical.com PostgreSQL version: 9.1.3 Operating system: Ubuntu Description: In section 24.3.5.1 of the docs on setting up continuous archiving, there are a few paragraphs and examples for using pglesslog (pg_compresslog and pg_decompresslog). This tool did not get out of beta for 9.0 and the only reports I find for 9.1 are for not being able to compile it. Suggest removing it from the docs unless someone wants to pull this tool into the core. -- 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 #6532: pg_upgrade fails on Python stored procedures
On Thu, Mar 15, 2012 at 8:54 PM, Andres Freund wrote: > Hi, > > On Thursday, March 15, 2012 02:13:29 PM stu...@stuartbishop.net wrote: >> The 9.1.3 changelog states pg_upgrade's handing of plpython stored >> procedures was fixed, but that does not appear to be the case: >> ... >> access file "$libdir/plpython": No such file or directory > Well. That looks like you didn't install plpython on the new cluster. Are you > sure its there? Yes, it is there. I can see the library with the new name of plpython2.so, not the old plpython.so from 8.4. createlang installs the language just fine if I build a cluster and database myself. -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #6532: pg_upgrade fails on Python stored procedures
On Thu, Mar 15, 2012 at 9:01 PM, Stuart Bishop wrote: > Yes, it is there. I can see the library with the new name of > plpython2.so, not the old plpython.so from 8.4. createlang installs > the language just fine if I build a cluster and database myself. As expected, symlinking plpython2.so to plpython.so works around things. I have no idea if this work around will cause problems when upgrading the db to PG 9.2+. -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #6624: Tab completion of identifier containing single backslash triggers warnings
On Thu, May 3, 2012 at 5:29 AM, Tom Lane wrote: > I'm inclined to think that if we got this far without complaint, there's > not a lot of point in writing new string-escaping support to solve what > is now a legacy problem. I'm fine with that - I wasn't even sure if I should bother reporting the issue, except for the chance that this bug might have deeper impact I hadn't thought of. -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #7500: hot-standby replica crash after an initial rsync
I believe I just hit this same issue, but with PG 9.1.3: <@:32407> 2012-08-29 10:02:09 UTC LOG: shutting down <@:32407> 2012-08-29 10:02:09 UTC LOG: database system is shut down <[unknown]@[unknown]:31687> 2012-08-29 13:34:03 UTC LOG: connection received: host=[local] <[unknown]@[unknown]:31687> 2012-08-29 13:34:03 UTC LOG: incomplete startup packet <@:31686> 2012-08-29 13:34:03 UTC LOG: database system was interrupted; last known up at 2012-08-29 13:14:47 UTC <@:31686> 2012-08-29 13:34:03 UTC LOG: entering standby mode <@:31686> 2012-08-29 13:34:03 UTC LOG: redo starts at A92/5F20 <@:31686> 2012-08-29 13:34:03 UTC FATAL: could not access status of transaction 208177034 <@:31686> 2012-08-29 13:34:03 UTC DETAIL: Could not read from file "pg_multixact/offsets/0C68" at offset 131072: Success. <@:31686> 2012-08-29 13:34:03 UTC CONTEXT: xlog redo create multixact 208177034 offset 1028958730: 1593544329 1593544330 <@:31681> 2012-08-29 13:34:03 UTC LOG: startup process (PID 31686) exited with exit code 1 <@:31681> 2012-08-29 13:34:03 UTC LOG: terminating any other active server processes This was attempting to rebuild a hot standby after switching my master to a new server. In between the shutdown and the attempt to restart: - The master was put into backup mode. - The datadir was rsynced over, using rsync -ahhP --delete-before --exclude=postmaster.pid --exclude=pg_xlog - The master was taken out of backup mode. - The pg_xlog directory was emptied - The pg_xlog directory was rsynced across from the master. This included all the WAL files from before the promotion, throughout backup mode, and a few from after backup mode was left. - recovery.conf was created (hot_standby=on, primary_conninfo=..., recovery_target_timeline=latest) - The hot standby was started. Unfortunately I didn't have the disk space around to keep everything, and am attempting the rebuild again now. -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #7500: hot-standby replica crash after an initial rsync
On Wed, Aug 29, 2012 at 10:59 PM, Andres Freund wrote: > On Wednesday, August 29, 2012 05:32:31 PM Stuart Bishop wrote: >> I believe I just hit this same issue, but with PG 9.1.3: >> >> <@:32407> 2012-08-29 10:02:09 UTC LOG: shutting down >> <@:32407> 2012-08-29 10:02:09 UTC LOG: database system is shut down >> <[unknown]@[unknown]:31687> 2012-08-29 13:34:03 UTC LOG: connection >> received: host=[local] >> <[unknown]@[unknown]:31687> 2012-08-29 13:34:03 UTC LOG: incomplete >> startup packet >> <@:31686> 2012-08-29 13:34:03 UTC LOG: database system was >> interrupted; last known up at 2012-08-29 13:14:47 UTC >> <@:31686> 2012-08-29 13:34:03 UTC LOG: entering standby mode >> <@:31686> 2012-08-29 13:34:03 UTC LOG: redo starts at A92/5F20 >> <@:31686> 2012-08-29 13:34:03 UTC FATAL: could not access status of >> transaction 208177034 >> <@:31686> 2012-08-29 13:34:03 UTC DETAIL: Could not read from file >> "pg_multixact/offsets/0C68" at offset 131072: Success. >> <@:31686> 2012-08-29 13:34:03 UTC CONTEXT: xlog redo create multixact >> 208177034 offset 1028958730: 1593544329 1593544330 >> <@:31681> 2012-08-29 13:34:03 UTC LOG: startup process (PID 31686) >> exited with exit code 1 >> <@:31681> 2012-08-29 13:34:03 UTC LOG: terminating any other active >> server processes >> >> This was attempting to rebuild a hot standby after switching my master >> to a new server. In between the shutdown and the attempt to restart: >> >> - The master was put into backup mode. >> - The datadir was rsynced over, using rsync -ahhP --delete-before >> --exclude=postmaster.pid --exclude=pg_xlog >> - The master was taken out of backup mode. >> - The pg_xlog directory was emptied >> - The pg_xlog directory was rsynced across from the master. This >> included all the WAL files from before the promotion, throughout >> backup mode, and a few from after backup mode was left. > Thats not valid, you cannot easily guarantee that youve not copied files that > were in the progress of being written to. Use a recovery_command if you do not > want all files to be transferred via the replication connection. But do that > only for files that have been archived via an archive_command beforehand. Ok. I had assumed this was fine, as the docs explicitly tell me to copy across any unarchived WAL files when doing failover. I think my confusion is because the docs for building a standby refer to the section on recovering from a backup, but I have a live server. I'll just let the WAL files get sucked over the replication connection if that works - this seems much simpler. I don't think I saw this mentioned in the docs. I had been assuming enough WAL needed to be available to bring the DB up to a consistent state before streaming replication would start. > Did you have a backup label in the rsync'ed datadir? In Maxim's case I could > detect that he had not via line numbers, but I do not see them here... Yes, the backup_label copied across (confirmed in scrollback from the rsync). -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #7546: Backups on hot standby cancelled despite hot_standby_feedback=on
On Wed, Sep 19, 2012 at 5:45 AM, Fujii Masao wrote: > On Mon, Sep 17, 2012 at 7:28 PM, wrote: >> The following bug has been logged on the website: >> >> Bug reference: 7546 >> Logged by: Stuart Bishop >> Email address: stu...@stuartbishop.net >> PostgreSQL version: 9.1.5 >> Operating system: Ubuntu 12.10 >> Description: >> >> I have a primary and a hot standby using streaming replication. The hot >> standby specifies 'hot_standby_feedback=on' with other replication settings >> set to default. >> >> If a vacuum occurs on the primary while pg_dump is dumping a large table, >> the pg_dump is cancelled, usually with the following error: >> >> ERROR: canceling statement due to conflict with recovery >> DETAIL: User was holding shared buffer pin for too long. > > Maybe I'm missing something, but ISTM that hot_standby_feedback doesn't > eliminate the query cancels caused by buffer pin lock which you encountered. > It eliminates only the query cancels caused by cleanup of rows. So you might > need to set max_standby_streaming_delay to -1, to avoid query cancels. I have also seen the following (but only on production, not with my test case): ERROR: canceling statement due to conflict with recovery DETAIL: User was holding a relation lock for too long. If you are correct, it seems impossible with 9.1 to have reliable long transactions on a hot standby without allowing the hot standby to lag behind by the length of the longest transaction. I was hoping when the docs state "this parameter can be used to eliminate query cancels caused by cleanup records" that it would cover all the background maintenance. Disabling autovacuum during the backup window would be one work around. -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #7546: Backups on hot standby cancelled despite hot_standby_feedback=on
On Wed, Sep 19, 2012 at 3:38 PM, Andres Freund wrote: >> >> ERROR: canceling statement due to conflict with recovery >> >> DETAIL: User was holding shared buffer pin for too long. >> > Maybe I'm missing something, but ISTM that hot_standby_feedback doesn't >> > eliminate the query cancels caused by buffer pin lock which you >> > encountered. It eliminates only the query cancels caused by cleanup of >> > rows. So you might need to set max_standby_streaming_delay to -1, to >> > avoid query cancels. > I think the above is just a case of the backend waiting a long time to send > data out due to your rate limiting. Why it does that holding a buffer pin may > be worth investigating... I put the rate limiting in there to trigger the problem consistently with a small data set. On the main production server I see this problem, it normally fails dumping a table with nearly 2 billion rows to local disk (pg_dump -Fc). The trick seems to be that vacuum needs to start running on the primary after the dump starts on the hot standby, and the vacuum complete before the dump finishes. >> I have also seen the following (but only on production, not with my test >> case): >> >> ERROR: canceling statement due to conflict with recovery >> DETAIL: User was holding a relation lock for too long. >> >> If you are correct, it seems impossible with 9.1 to have reliable long >> transactions on a hot standby without allowing the hot standby to lag >> behind by the length of the longest transaction. > Is it possible that you perform such commands on tables used on the standby? Yes, it is quite possible CREATE INDEX CONCURRENTLY was run on the primary during the backup window. I can't discount other commands either, but if that is the case one of our ops needs a spanking. I've only been able to reproduce the buffer pin error in isolation. -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #7546: Backups on hot standby cancelled despite hot_standby_feedback=on
On Wed, Sep 19, 2012 at 6:56 PM, Andres Freund wrote: > You see buffer pin errors or you see the relation errors? Those arent the > same. > So if you see no buffer pin errors but relation lock ones in production your > way to reproduce the issue isn't telling us very much ;) On production I have seen both. I have only been able to confirm and isolate the trigger of the buffer pin errors, and thus this bug report. I mentioned the rarer relation lock error just in case it was relevant. > Do you have DDL logging enabled so you could investigate that? > > I really wonder why we don't bother logging the dbOid/relOid pair before we > cancel transactions due to lock conflicts. That should make investigating such > issues considerably easier... I do have DDL logging enabled. I've waded through a two hour period on the primary where the problem must have occurred and can see no DDL except for the creation and dropping of temporary tables. My parser is unfortunately grep and my eyeballs to filter out the noise. -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #7546: Backups on hot standby cancelled despite hot_standby_feedback=on
Yesterday's dump failed with a new error. pg_stat_replication tells me replication has been connected for nearly a month, so no network issues. I've confirmed hot_standby_feedback is still set to 'on': ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. As before, the only error I've been able to reproduce in isolation is the buffer pin error I originally reported. -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #7661: pgstattuple from unpackaged fails on old installation
On Thu, Nov 15, 2012 at 1:42 PM, Craig Ringer wrote: >> The pgstattuple upgrade from unpackaged script expects the pgstatindex >> function to exist, but it does not on particularly old databases (My >> pgstattuple originally installed with 8.3 or 8.4?). pg_relpages(text) was missing too. > That's a known issue with several of the extensions. You need to upgrade > the contrib module install to the current version, *then* wrap the > unpackaged contrib module into an extension with "FROM UNPACKAGED". Yeah, just thought I'd stick it in the... umm... bugtracker, as so far 'FROM unpackaged' has failed in 66% of up updates. Is the real solution is for the foo--unpackaged--1.0.sql script to recreate missing objects before adding them to the extension? -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #7651: Superfluous calls to functions used for indexing
On Sun, Nov 11, 2012 at 10:45 PM, wrote: > Bug: When making an index over a function, then selecting a result that does > not contain the function call (but orders on it), Superfluous function calls > are made. This possibly because the plan creates a projection containing the > function value. I think this is the same issue as was discussed here, dating from PostgreSQL 8.1: http://postgresql.1045698.n5.nabble.com/Slow-functional-indexes-td2059587.html -- Stuart Bishop http://www.stuartbishop.net/ -- 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 #8450: pg_basebackup blocks until WAL archiving successful
On Mon, Sep 23, 2013 at 3:33 PM, Heikki Linnakangas > I can see why you'd want that, but it seems equally problematic to let > pg_basebackup return, when the WAL files haven't been archived yet and you > therefore don't in fact have valid, restorable backup yet. Have you > considered using the --xlog-method=stream option, to include the WAL files > in the backup? That will make your backups somewhat larger, as the WAL files > are included, but in that mode pg_basebackup won't wait for the archival and > the backup will be restorable even if archive_command is failing. I'm supporting PG 9.1 at the moment so cannot rely on --xlog-method=stream. I agree that the current behavior is for most use cases better, and I think that the behavior I want should be explicitly enabled with an option. -- Stuart Bishop http://www.stuartbishop.net/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
