[BUGS] BUG #6532: pg_upgrade fails on Python stored procedures
The following bug has been logged on the website: Bug reference: 6532 Logged by: Stuart Bishop Email address: stu...@stuartbishop.net PostgreSQL version: 9.1.3 Operating system: Ubuntu Description: 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: postgres@aargh:~$ /usr/lib/postgresql/9.1/bin/pg_upgrade --version pg_upgrade (PostgreSQL) 9.1.3 postgres@aargh:~$ /usr/lib/postgresql/9.1/bin/pg_upgrade --old-bindir=/usr/lib/postgresql/8.4/bin --new-bindir=/usr/lib/postgresql/9.1/bin --old-datadir=8.4/main --new-datadir=9.1/mig --old-port=5433 --new-port=5435 Performing Consistency Checks - Checking current, bin, and data directories ok Checking cluster versions ok Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system oid user data typesok Checking for contrib/isn with bigint-passing mismatch ok Checking for large objects ok Creating catalog dump ok Checking for prepared transactions ok Checking for presence of required libraries ok | If pg_upgrade fails after this point, you must | re-initdb the new cluster before continuing. | You will also need to remove the ".old" suffix | from 8.4/main/global/pg_control.old. Performing Upgrade -- Adding ".old" suffix to old global/pg_control ok Analyzing all rows in the new cluster ok Freezing all rows on the new clusterok Deleting new commit clogs ok Copying old commit clogs to new server ok Setting next transaction id for new cluster ok Resetting WAL archives ok Setting frozenxid counters in new cluster ok Creating databases in the new cluster ok Adding support functions to new cluster ok Restoring database schema to new cluster psql:/var/lib/postgresql/pg_upgrade_dump_db.sql:3992: ERROR: could not access file "$libdir/plpython": No such file or directory There were problems executing "/usr/lib/postgresql/9.1/bin/psql" --set ON_ERROR_STOP=on --no-psqlrc --port 5435 --username "postgres" -f "/var/lib/postgresql/pg_upgrade_dump_db.sql" --dbname template1 >> "/dev/null" Failure, exiting The relevant section of pg_upgrade_dump_db.sql is: CREATE FUNCTION plpython_call_handler() RETURNS language_handler LANGUAGE c AS '$libdir/plpython', 'plpython_call_handler'; -- 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 #6624: Tab completion of identifier containing single backslash triggers warnings
The following bug has been logged on the website:
Bug reference: 6624
Logged by: Stuart Bishop
Email address: stu...@stuartbishop.net
PostgreSQL version: 9.1.3
Operating system: Ununtu
Description:
psql tab completion can emit WARNING messages if escape_string_warning is
set to true. The trigger seems to be an identifier containing a single
backslash.
# CREATE SEQUENCE "\foo";
CREATE SEQUENCE
# \ds "\
(at this point, hit to trigger completion)
# drop sequence "\WARNING: nonstandard use of \\ in a string literal
LINE 1: ... substring(pg_catalog.quote_ident(c.relname),1,2)='"\\' AND ...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 3: ...ing(pg_catalog.quote_ident(n.nspname) || '.',1,2)='"\\' AND ...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 3: ...alog.quote_ident(nspname) || '.',1,2) = substring('"\\',1,pg...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 5: ... || '.' || pg_catalog.quote_ident(c.relname),1,2)='"\\' AND ...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 5: ...og.quote_ident(n.nspname) || '.',1,2) = substring('"\\',1,pg...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 5: ...alog.quote_ident(nspname) || '.',1,2) = substring('"\\',1,pg...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
foo" ;
--
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 #7546: Backups on hot standby cancelled despite hot_standby=on
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. I can excercise this problem with the following script: #!/bin/sh dbname="repl_test" master_port=5432 slave_port=5434 rows=200 slow="pv --rate-limit 20k" createdb -p $master_port $dbname psql -p $master_port -d $dbname -f - < /dev/null) & sleep 5; psql -p $master_port -d $dbname -c "vacuum verbose BigStuff;" -- 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 #7661: pgstattuple from unpackaged fails on old installation
The following bug has been logged on the website: Bug reference: 7661 Logged by: Stuart Bishop Email address: stu...@stuartbishop.net PostgreSQL version: 9.1.6 Operating system: Ubuntu 12.04 Description: 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?). # create extension pgstattuple; ERROR: function "pgstattuple" already exists with same argument types # create extension if not exists pgstattuple from unpackaged; ERROR: function pgstatindex(text) does not exist -- 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 #8450: pg_basebackup blocks until WAL archiving successful
The following bug has been logged on the website: Bug reference: 8450 Logged by: Stuart Bishop Email address: stu...@stuartbishop.net PostgreSQL version: 9.1.9 Operating system: all Description: Hi. pg_basebackup blocks until all necessary WAL files have been archived by archive_command. This can take a few minutes under normal circumstances, and indefinitely if archive_command is failing. I would like to be able to disable this check, as I am streaming backups to a system that can time out my connection if it does not receive new data for a short while. This makes unattended backups problematic. -- 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 #4539: to_char(to_timestamp('...','....')) returns the wrong result
The following bug has been logged online:
Bug reference: 4539
Logged by: Stuart Green
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.11
Operating system: Centos 5
Description:to_char(to_timestamp('...','')) returns the wrong
result
Details:
The query
select to_char(to_timestamp('2008-11-14 16:00:00','-MM-DD
HH24:MM:SS'),'day');
is returning monday as the day in all the dbase version I Have test, 8.2.11,
8.2.10, and 8.1.11
The query
select to_char(to_timestamp('2008-11-14','-MM-DD'),'day');
returns friday, which is correct, if you replace to_timestamp with to_date
it returns friday which is correct.
Specify the 24 hour clock or not in the timestamp query doesn't make a
difference in the result, it still returns monday, when it should be friday.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Regular expression not working [^xyz]
I wasn't aware that Postgres accepted regular expressions (they seem
undocumented) until reading the draft of Bruce Momjian's forthcoming
book. However, now having looked at them, I have found a bug in
matching a particular expression from a PostgreSQL database. I am
using:
PostgreSQL 7.0.2 on sparc-sun-solaris2.7, compiled by gcc 2.8.1
I have created a very brief table ('bug') that has one 'text' field
('title') and three records:
\connect - postgres
CREATE TABLE "bug" (
"title" text
);
COPY "bug" FROM stdin;
abcdefghijklm
nopqrstuvwxyz
aeiou
\.
The following queries work as expected, EXCEPT for [2]. I believe
that [2] and [3] should return identical results, but [2] simply
lists ALL titles:
[1] select title from bug where title ~* '[ae]';
Lists titles that contain one of the letters a or e: WORKS
[2] select title from bug where title ~* '[^ae]';
Lists ALL titles rather than those that don't contain a or e:
DOESN'T WORK
(Note: 'select title from bug where title ~* '[^a];' does not work either
[3] select title from bug where title !~* '[ae]';
Lists all titles that don't contain a, q, x or z: WORKS
[4] select title from bug where title ~* '^[^ae]';
Lists all titles that do not begin with a, q, x or z: WORKS
I hope this helps with development,
Regards,
Stuart Peters
Stuart Peters, Dept of Sociology, University of Surrey, Guildford GU2 7XH,
United Kingdom. Tel: +44 (0)1483 259292 Fax: +44 (0)1483 259551
Electronic Publishing Resource Service - EPRESS http://www.epress.ac.uk/
[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] [SQL/BUGS] (possible) inconsistent behaviour of timestamp?
Here's an "interesting" timestamp related postgreSQL quirk:
testdb2=# select "timestamp"('now');
timestamp
2002-11-26 13:47:12.454157
(1 row)
testdb2=# select 'now'::timestamp;
timestamptz
--
2002-11-26 13:47:34.88358+00
(1 row)
testdb2=# select timestamp 'now';
timestamptz
---
2002-11-26 13:47:47.701731+00
(1 row)
The first SELECT returns a 'timestamp', but the next two return a
'timestamptz' (presumably with timezone); is this inconsitent behaviour?
Cheers,
Stuart.
Royal Veterinary College
London, UK
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[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
[BUGS] Rules for Inserting Serials
I'm running the 7.1Beta4 (rpm binary) version on Mandrake 7.1. Rules that insert serials from the new object into another table don't give the same value as those inserted in the actual table. This looks to have been covered b4 here: http://www.postgresql.org/mhonarc/pgsql-bugs/2000-04/msg00060.html If this is indeed to remain the case I think it should be clearly documented in the rules section (I guess it would affect all defaults that use functions). Any way heres my script and stuff: <> <> - Stuart tbl_ins_test.sct ins_rule_test.sql
[BUGS] 7.1b6 Compile fail on cygwin with --enable-cassert
severity: FYI uname -srm CYGWIN_98-4.10 1.1.7(0.31/3/2) i586 Spec: win98SE, PIII 733MHz, 64MB If configured with ./configure --enable-cassert then there is an error while makeing: $make gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I. -I../../../../src/include -I/usr/local/include -c -o pl_funcs.o pl_funcs.c dlltool --export-all --output-def plpgsql.def pl_parse.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o dllwrap -o plpgsql.dll --dllname plpgsql.dll --def plpgsql.def pl_parse.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o ../../../../src/utils/dllinit.o -L../../../../src/backend -lpostgres -lcygipc -lcygwin -lcrypt -lkernel32 pl_exec.o(.text+0x709):pl_exec.c: undefined reference to `assert_enabled' pl_exec.o(.text+0x730):pl_exec.c: undefined reference to `FailedAssertion' collect2: ld returned 1 exit status dllwrap: gcc exited with status 1 make[4]: *** [libplpgsql.a] Error 1 make[4]: Leaving directory `/usr/src/postgresql-7.1beta6/src/pl/plpgsql/src' make[3]: *** [all] Error 2 make[3]: Leaving directory `/usr/src/postgresql-7.1beta6/src/pl/plpgsql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/src/postgresql-7.1beta6/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/usr/src/postgresql-7.1beta6/src' make: *** [all] Error 2 Full configure output: <> Full Make Output: <> -Stuart [Side FYI: I had an error with the bug reporting tool: 1: Gave a to large code example (over 8140 - upgrade to 7.1) 2: Went Back and erased the bottom box + made couple of alteration to the long version and re submitted. It seem both sent an entry as anonymous (with one being empty) ] enbl_casrt_cnf.out enbl_casrt_mk.out ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] RAISE concatination/variables in plpgsql
SEVERITY:Minor Anoyance
In the plpgsql docs it has the following example:
RAISE NOTICE ''Id number '' || key || '' not found!'';
When I put a function round this statement it gives a compile error at the
|.
Also when fiddling if I put a variable first it complains about that
variable (eg key || '' val.'')
Here is the script I ran:
DROP FUNCTION tstktxt(text);
CREATE FUNCTION tstktxt(text) RETURNS text AS '
DECLARE
key ALIAS FOR $1;
BEGIN
RAISE NOTICE ''Id number '' || key || '' not found!'';
RETURN key;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION tstkint(int4);
CREATE FUNCTION tstkint(int4) RETURNS int4 AS '
DECLARE
key ALIAS FOR $1;
BEGIN
RAISE NOTICE ''Id number '' || key || '' not found!'';
RETURN key;
END;
' LANGUAGE 'plpgsql';
SELECT tstktxt('Test');
SELECT tstkint(42);
This gave the following result:
DROP
CREATE
DROP
CREATE
psql:core/kytst.sql:21: NOTICE: plpgsql: ERROR during compile of tstktxt
near line 4
psql:core/kytst.sql:21: ERROR: parse error at or near "|"
psql:core/kytst.sql:22: NOTICE: plpgsql: ERROR during compile of tstkint
near line 4
psql:core/kytst.sql:22: ERROR: parse error at or near "|"
Is this a bug or documentation error?
I'm running on cygwin 1.1.7, cygipc 1.9.2, on win98SE
Here's the postmaster output (with -d2):
<>
z
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[BUGS] After ~Crash Sequence not correct
Severity: Annoyance Setup: PostgreSQL 7.1.2 on Dual PIII WinNT4 server + Cygwin 1.3.2 PostgreSQL is run as a service and when rebooting (which doesn't shutdown - previously on cygwin list (hence the ~Crash in title)) I have twice noticed that a sequence was not properly updated, however records with Primary keys based upon the sequence where present in the table. (I *think* it was a different table & sequence last time). - Stuart ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] After ~Crash Sequence not correct
No. I had fsync on. - Stuart -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 19 December 2001 18:49 To: Mikheev, Vadim Cc: Henshall, Stuart - WCP; [EMAIL PROTECTED] Subject: Re: [BUGS] After ~Crash Sequence not correct "Mikheev, Vadim" <[EMAIL PROTECTED]> writes: > It was made to avoid WAL-loging on each nextval call, ie it should work > like OID pre-fetching: value stored in WAL must always "exceed" values > returned by nextval so on the after-crash-restart sequence should be > advanced to value which was never returned by nextval (for non-cycled > sequences). Maybe I made some mistakes in implementation? Oh, okay. What I saw was that the next nextval() after restart was higher than what I was expecting; but that's correct given the prefetch behavior. But we've seen several reports wherein the value appeared to go backwards after a crash. Stuart, you weren't running with -F (fsync off) by any chance, were you? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [CYGWIN] [BUGS] resource leak in 7.2
There was a cygwin postgresql 7.1.3-2 release to fix a file handle problem (init_rels() not closing pg_internal.init): http://archives.postgresql.org/pgsql-cygwin/2002-01/msg00034.php which refers to: http://archives.postgresql.org/pgsql-cygwin/2002-01/msg00029.php Maybe this is it? - Stuart > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: 05 February 2002 16:21 > To: Tom Pfau > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [CYGWIN] [BUGS] resource leak in 7.2 > > > "Tom Pfau" <[EMAIL PROTECTED]> writes: > > There was a bug in 7.1.2 that was fixed in 7.1.3 that may be back in > > 7.2. > > I downloaded 7.2 yesterday morning and started testing it. > I left some > > procedures running overnight performing random updates in a table. > > Looking at task manager this morning, the three backend > processes are > > each consuming about 1.5e6 handles. > > I see nothing in the CVS logs to make me think that any > file-handle-leakage > bug was fixed between 7.1.2 and 7.1.3; and even less to make me think > that 7.1.3 contains any fixes that weren't also applied to > the 7.2 line. > > You're going to have to be a great deal more specific. > > regards, tom lane > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] Results of 8.0.0 beta 2 on Red Hat Advanced Server 2.1 x86
Thought you might like to know: Red Hat Advanced Server 2.1 (based on Red Hat 7.2) on an HP DL380 dual Xeon x86 server Linux 2.4.9-e.41enterprise #1 SMP Wed Jun 16 23:33:07 EDT 2004 i686 unknown gcc version 2.96 2731 (Red Hat Linux 7.2 2.96-128.7.2) glibc-2.2.4-32.15 ./configure make make check All tests PASSED ./configure --enable-thread-safety --with-pam --with-java --with-openssl make make check All tests PASSED We use Postgresql daily, we store a database of over 12 million rows and everything hums along nicely. Keep up the good work. -- Stuart Stock Dresdner Kleinwort Wasserstein (Japan) Ltd. The information contained herein is confidential and is intended solely for the addressee. Access by any other party is unauthorised without the express written permission of the sender. If you are not the intended recipient, please contact the sender either via the company switchboard on +44 (0)20 7623 8000, or via e-mail return. If you have received this e-mail in error or wish to read our e-mail disclaimer statement and monitoring policy, please refer to http://www.drkw.com/disc/email/ or contact the sender. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] Interval+tz-tz gives unexpected result
Title: Interval+tz-tz gives unexpected result dev=# select '0 sec'::interval+'2002/10/21 09:48'::timestamptz-'2002/10/21 09:30'::timestamptz; ?column? -- -09:30 (1 row) I would have expected '00:18' dev=# select version(); version -- PostgreSQL 7.2.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5 (1 row) Thank you, - Stuart
[BUGS] timeofday()::timestamp - not casting
Title: timeofday()::timestamp - not casting From 7.2.3: dpt=> select timeofday()::timestamp; timeofday -- 2002-11-28 11:47:37.06366+00 (1 row) all OK however from 7.3rc2 (on cygwin): dev=# select timeofday()::timestamp; ERROR: Bad timestamp external representation 'Thu Nov 28 11:26:46.488000 2002 G MTST' dev=# select timeofday(); timeofday --- Thu Nov 28 11:26:59.454000 2002 GMTST (1 row) - Stuart
Re: [BUGS] timeofday()::timestamp - not casting
Title: RE: [BUGS] timeofday()::timestamp - not casting Tom Lane wrote: > "Henshall, Stuart - Design & Print" > <[EMAIL PROTECTED]> writes: >> dev=# select timeofday()::timestamp; >> ERROR: Bad timestamp external representation 'Thu Nov 28 >> 11:26:46.488000 2002 GMTST' > > It works for me. Where in the world is cygwin getting "GMTST" from > --- surely that's not a real timezone name? > > regards, tom lane Just rechecked & 7.2.3 doesn't work on my machine (win9x - I'd been lazy and just connected to a server to check 7.2 which is NT4). Hmmm... maybe GMT daylight Saving Time? Oh well will try and investigate to see if anything is wrong, or if this is a win9x peculiarity (strongly suspected). Thanks, - Stuart
