[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 TAB 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
Re: [BUGS] Errors on CREATE TABLE IF NOT EXISTS
Hi Alvaro, On 01/05/2012 21:36, Alvaro Herrera wrote: The question you were asking has already been answered, but I think it's worth pointing out that a partitioned-insert trigger that has to check whether the partition exist beforehand is a lot slower than one that doesn't have to. Our usual suggestion is to create the partitions by some other means, e.g. create a couple months worth of weekly partitions, a couple of months ahead of time, via cron. The insert trigger is then assured that the partition exists, and it can become faster by not having to check. Thanks for the suggestion. I will surely follow the advice as soon as the load starts to grow. For now catching the table not found exception within the insert trigger and creating the table on the fly seems a good balance between performance and ease of use. Cheers -- Matteo Beccati Development Consulting - http://www.beccati.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] BUG #6624: Tab completion of identifier containing single backslash triggers warnings
On Wed, May 02, 2012 at 12:59:58PM +, stu...@stuartbishop.net wrote:
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 \
I am unable to reproduce this failure on my copy of 9.1.3. Have you
perhaps changed any server settings?
---
(at this point, hit TAB 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
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 Wed, May 2, 2012 at 11:21 AM, Bruce Momjian br...@momjian.us wrote: On Wed, May 02, 2012 at 12:59:58PM +, stu...@stuartbishop.net wrote: # CREATE SEQUENCE \foo; CREATE SEQUENCE # \ds \ I am unable to reproduce this failure on my copy of 9.1.3. Have you perhaps changed any server settings? I only get the error if I: SET standard_conforming_strings TO off; otherwise, it works fine. Perhaps it's still worth fixing though. Josh -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] pg_dump: aborting because of server version mismatch
Hi,
I am trying to create a daily backup cron script but it fails with an error
as below:
Any pointers to resolve this will be greatly appreciated.
Thanks,
Mitesh Shah
mitesh.s...@stripes39.com
*(1) Error:*
bash-3.2$ sh pg_backup_rotated_orig.sh
Making backup directory in /Users/miteshshah/Documents/2012-05-02-daily/
-e
Performing full backups
-e
Plain backup of mitesh
*pg_dump: server version: 9.1.2; pg_dump version: 9.0.5*
*pg_dump: aborting because of server version mismatch*
-e
All database backups complete!
*(2.1) * /Users/miteshshah/Desktop/*pg_backup.config file:*
#
## POSTGRESQL BACKUP CONFIG ##
##
# hostname to adhere to pg_hba policies.
HOSTNAME= host
# username to connect to database as.
USERNAME=user
# Database name
DATABASE=database
# This dir will be created if it doesn't exist. This must be writable by
the user the script is
# running as.
BACKUP_DIR=backup_dir
# SCRIPTPATH
SCRIPTPATH=path
# Will produce a custom-format backup if set to yes
ENABLE_CUSTOM_BACKUPS=yes
# Will produce a gzipped plain-format backup if set to yes
ENABLE_PLAIN_BACKUPS=yes
SETTINGS FOR ROTATED BACKUPS
# Which day to take the weekly backup from (1-7 = Monday-Sunday)
DAY_OF_WEEK_TO_KEEP=5
# Number of days to keep daily backups
DAYS_TO_KEEP=7
# How many weeks to keep weekly backups
WEEKS_TO_KEEP=5
##
*(2.2) * /Users/miteshshah/Desktop/*pg_backup_rotated_orig.sh*
#!/bin/bash
###
### LOAD CONFIG ###
###
source /Users/miteshshah/Desktop/pg_backup.config
###
START THE BACKUPS
###
function perform_backups()
{
SUFFIX=$1
FINAL_BACKUP_DIR=$BACKUP_DIR`date +\%Y-\%m-\%d`$SUFFIX/
echo Making backup directory in $FINAL_BACKUP_DIR
if ! mkdir -p $FINAL_BACKUP_DIR; then
echo Cannot create backup directory in $FINAL_BACKUP_DIR.
Go and fix it!
exit 1;
fi;
###
## FULL BACKUPS ###
###
echo -e \n\nPerforming full backups
echo -e \n
if [ $ENABLE_PLAIN_BACKUPS = yes ]
then
echo Plain backup of $DATABASE
if ! pg_dump -Fp -h $HOSTNAME -U $USERNAME
$DATABASE | gzip $FINAL_BACKUP_DIR$DATABASE.sql.gz.in_progress; then
echo [!!ERROR!!] Failed to produce plain
backup database $DATABASE
else
mv
$FINAL_BACKUP_DIR$DATABASE.sql.gz.in_progress
$FINAL_BACKUP_DIR$DATABASE.sql.gz
fi
fi
if [ $ENABLE_CUSTOM_BACKUPS = yes ]
then
echo Custom backup of $DATABASE
if ! pg_dump -Fc -h $HOSTNAME -U $USERNAME
$DATABASE -f $FINAL_BACKUP_DIR$DATABASE.custom.in_progress; then
echo [!!ERROR!!] Failed to produce custom
backup database $DATABASE
else
mv
$FINAL_BACKUP_DIR$DATABASE.custom.in_progress
$FINAL_BACKUP_DIR$DATABASE.custom
fi
fi
echo -e \nAll database backups complete!
}
# DAILY BACKUPS
# Delete daily backups 7 days old or more
find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name *-daily -exec rm
-rf '{}' ';'
Re: [BUGS] pg_dump: aborting because of server version mismatch
Mitesh Shah mitesh.s...@stripes39.com wrote: *pg_dump: server version: 9.1.2; pg_dump version: 9.0.5* *pg_dump: aborting because of server version mismatch* This is not a bug. Use a version of pg_dump which is at least as new as the server. The older version of pg_dump is unlikely to be able to recognize everything in the newer server, -Kevin -- 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
Josh Kupershmidt schmi...@gmail.com writes: On Wed, May 2, 2012 at 11:21 AM, Bruce Momjian br...@momjian.us wrote: On Wed, May 02, 2012 at 12:59:58PM +, stu...@stuartbishop.net wrote: # CREATE SEQUENCE \foo; CREATE SEQUENCE # \ds \ I am unable to reproduce this failure on my copy of 9.1.3. Have you perhaps changed any server settings? I only get the error if I: SET standard_conforming_strings TO off; otherwise, it works fine. Perhaps it's still worth fixing though. AFAICT, you get this behavior in every release back to 8.1, if you have both escape_string_warning ON and standard_conforming_strings OFF; which was the default configuration from 8.2 through 9.0. The lack of prior complaints suggests pretty strongly that backslashes in names are not common in the field. The only way we could suppress such warnings would be if we made tab-complete.c use E'' strings for literals containing name prefixes; which is perhaps doable but it would mean having tab-complete.c roll its own string escaping rather than use any facility now available from libpq. (PQescapeString and friends presume that the result will be emitted as a plain string.) This issue isn't peculiar to tab-complete.c, either; in general, *any* client that uses PQescapeString and friends is going to get bombarded with this type of warning when the server is configured that way, even though what it's doing is perfectly safe. I don't remember at the moment why we designed those functions that way, but presumably the alternatives were worse. 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. 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] select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
hello: i see a lot ,missing chunk number 0 for toast value 96635 in pg_toast_2619,, in background aler log.select * from iclock ,no data retrun,indicate missing chunk number 0 for toast value 96635 in pg_toast_2619. i use pg_dump table,then truncate table,then pg_dump it current database,i still return this error, but i pg_dump it to others database,i can work. i deal with pg_dump the whole database,then pg_dump to new database,it can work. i want to know what reason it can happen? and another ways to deal with it ? thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/select-table-indicate-missing-chunk-number-0-for-toast-value-96635-in-pg-toast-2619-tp5682176.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.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: [seapug] Re: [BUGS] pg_dump: aborting because of server version mismatchT
There used to be a switch in pg_dump that would disable the version, you can even see that's in the man page. However, it is not reccommended and the most recent versions have fully deprecated the switch. Technically speaking that means that you can even use an older version of pg_dump and it wouldn't generate any errors although I wouldn't want to test it out on important stuff. Robert Bernier On Wednesday, May 02, 2012 02:53:53 pm Kevin Grittner wrote: Mitesh Shah mitesh.s...@stripes39.com wrote: *pg_dump: server version: 9.1.2; pg_dump version: 9.0.5* *pg_dump: aborting because of server version mismatch* This is not a bug. Use a version of pg_dump which is at least as new as the server. The older version of pg_dump is unlikely to be able to recognize everything in the newer server, -Kevin -- 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] select table indicate missing chunk number 0 for toast value 96635 in pg_toast_2619
leo xu leoxu8...@gmail.com writes: i see a lot ,missing chunk number 0 for toast value 96635 in pg_toast_2619,, in background aler log.select * from iclock ,no data retrun,indicate missing chunk number 0 for toast value 96635 in pg_toast_2619. There is a known bug that can cause that symptom, but it is fixed in recent update releases. What PG version are you running? If it's not at least one of the releases cited below, update. regards, tom lane Author: Tom Lane t...@sss.pgh.pa.us Branch: master [08e261cbc] 2011-11-01 19:49:58 -0400 Branch: REL9_1_STABLE Release: REL9_1_2 [5e4dd5f63] 2011-11-01 19:48:43 -0400 Branch: REL9_0_STABLE Release: REL9_0_6 [7f797d27f] 2011-11-01 19:48:49 -0400 Branch: REL8_4_STABLE Release: REL8_4_10 [b05ce7550] 2011-11-01 19:48:56 -0400 Branch: REL8_3_STABLE Release: REL8_3_17 [7e03d2849] 2011-11-01 19:49:01 -0400 Branch: REL8_2_STABLE Release: REL8_2_23 [b24e6cafc] 2011-11-01 19:49:06 -0400 Fix race condition with toast table access from a stale syscache entry. If a tuple in a syscache contains an out-of-line toasted field, and we try to fetch that field shortly after some other transaction has committed an update or deletion of the tuple, there is a race condition: vacuum could come along and remove the toast tuples before we can fetch them. This leads to transient failures like missing chunk number 0 for toast value N in pg_toast_2619, as seen in recent reports from Andrew Hammond and Tim Uckun. The design idea of syscache is that access to stale syscache entries should be prevented by relation-level locks, but that fails for at least two cases where toasted fields are possible: ANALYZE updates pg_statistic rows without locking out sessions that might want to plan queries on the same table, and CREATE OR REPLACE FUNCTION updates pg_proc rows without any meaningful lock at all. The least risky fix seems to be an idea that Heikki suggested when we were dealing with a related problem back in August: forcibly detoast any out-of-line fields before putting a tuple into syscache in the first place. This avoids the problem because at the time we fetch the parent tuple from the catalog, we should be holding an MVCC snapshot that will prevent removal of the toast tuples, even if the parent tuple is outdated immediately after we fetch it. (Note: I'm not convinced that this statement holds true at every instant where we could be fetching a syscache entry at all, but it does appear to hold true at the times where we could fetch an entry that could have a toasted field. We will need to be a bit wary of adding toast tables to low-level catalogs that don't have them already.) An additional benefit is that subsequent uses of the syscache entry should be faster, since they won't have to detoast the field. Back-patch to all supported versions. The problem is significantly harder to reproduce in pre-9.0 releases, because of their willingness to flush every entry in a syscache whenever the underlying catalog is vacuumed (cf CatalogCacheFlushRelation); but there is still a window for trouble. -- 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 t...@sss.pgh.pa.us 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 stu...@stuartbishop.net 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
