[BUGS] ILIKE fails with accented letters on utf8 locale
Hello,
using a database with utf8 encoding and utf8 locale, accented letters are
not properly compared.
Test to reproduce: it converts a lowercase letter (the sequence '\xc3\xa8'
is the utf8 encoding of the unicode 'LATIN SMALL LETTER E WITH GRAVE') into
uppercase and then compares the two letters with a non case sensitive
operator. I expect this to be an identity, so the value expected from the
query is 't'.
$ initdb --encoding=utf8 --locale=en_US.utf8 en_utf8
$ pg_ctl -D en_utf8 start
$ psql postgres
postgres=# SELECT upper('\xc3\xa8') ILIKE '\xc3\xa8';
?column?
--
f
The locale it_IT.utf8 shows the same problem too. The result is true for
it_IT.iso-8859-1 locale.
Is it a PostgreSQL related problem or a libc one?
Thank you
--
Daniele Varrazzo - Develer S.r.l.
http://www.develer.com
---(end of broadcast)---
TIP 1: 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 #5469: regexp_matches() has poor behaviour and more poor documentation
The following bug has been logged online: Bug reference: 5469 Logged by: Daniele Varrazzo Email address: daniele.varra...@gmail.com PostgreSQL version: 8.4 Operating system: any Description:regexp_matches() has poor behaviour and more poor documentation Details: regexp_matches() has been recently discussed (http://archives.postgresql.org/pgsql-bugs/2010-04/msg00026.php): it is a setof function and as such it can drop results. Unfortunately it is an useful function to newcomers who use SQL, use regexps but don't arrive to read the chapter 34.4.7. (i.e. Extending SQL -> Query Language (SQL) Functions -> SQL Functions Returning Sets) and are not so enlightened to know that "setof text[]" means "if it doesn't match, it drops the record". They just expect the function to be a LIKE on steroids. Please describe the behavior in the documentation of the function (i.e. table 9-6. and section 9.7.3), possibly provide a function with a saner interface, i.e. returning a text[] of the first match or NULL on no match, or document a workaround (suitable for an user knowing regexps but not setof-returning functions) to make the function not dropping record (e.g. I fixed the "bug" adding a "|" at the end of the pattern, so that the function returns an array of NULL in case of no match: I don't think it is a trivial workaround). -- 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 #5469: regexp_matches() has poor behaviour and more poor documentation
On Wed, May 26, 2010 at 4:51 AM, Robert Haas wrote: > On Mon, May 24, 2010 at 9:16 AM, Daniele Varrazzo > wrote: >> regexp_matches() has been recently discussed >> (http://archives.postgresql.org/pgsql-bugs/2010-04/msg00026.php): it is a >> setof function and as such it can drop results. >> >> Unfortunately it is an useful function to newcomers who use SQL, use regexps >> but don't arrive to read the chapter 34.4.7. (i.e. Extending SQL -> Query >> Language (SQL) Functions -> SQL Functions Returning Sets) and are not so >> enlightened to know that "setof text[]" means "if it doesn't match, it drops >> the record". They just expect the function to be a LIKE on steroids. >> >> Please describe the behavior in the documentation of the function (i.e. >> table 9-6. and section 9.7.3), possibly provide a function with a saner >> interface, i.e. returning a text[] of the first match or NULL on no match, >> or document a workaround (suitable for an user knowing regexps but not >> setof-returning functions) to make the function not dropping record (e.g. I >> fixed the "bug" adding a "|" at the end of the pattern, so that the function >> returns an array of NULL in case of no match: I don't think it is a trivial >> workaround). > > I'm not sure that it's very productive to refer to the behavior of our > code as insane. We do document this in section 9.7.3, pretty > explicitly: > > "The regexp_matches function returns all of the captured substrings > resulting from matching a POSIX regular expression pattern. It has the > syntax regexp_matches(string, pattern [, flags ]). If there is no > match to the pattern, the function returns no rows. If there is a > match, the function returns a text array whose n'th element is the > substring matching the n'th parenthesized subexpression of the pattern > (not counting "non-capturing" parentheses; see below for details)." > > I think that's pretty clear. Your mileage may vary, of course. "If there is no match to the pattern, the function returns no rows" is easily overlooked as "it returns null", or some other behaviour that don't change the returned set. The point is, because the function is listed in the string function, you would expect the function to manipulate text, not the dataset. The function as it is is not safe to be used in a construct SELECT foo, bar, regexp_matches(bar, pattern) FROM table; unless you really wanted: SELECT foo, bar, regexp_matches(bar, pattern) FROM table WHERE bar ~ pattern; otherwise you have to take measures to be able to deal with records in which the pattern is not matched, for example: SELECT foo, bar, regexp_matches(bar, pattern || '|') FROM table; the latter still doesn't work when bar is NULL: in this case the record is dropped anyway, so I don't think it can be proposed as general solution. The characteristics of returning a set of text[] is useful when the user wants all the matches, not only the first one: the behaviour is selected specifying the flag 'g' as third argument. From this point of view, I hope it can be stated that in its current form the regexp_matches() has not the most optimal interface. Please accept my apology for the tone being too rude in my previous message. > I'm less confident than what we have in table 9-6 (other string > functions, in section 9.4, string functions and operators) is clear on > first reading, but neither do I immediately know how to improve it. > Perhaps instead of critiquing our insanity you could provide some > specific suggestions for improvement. > > Similarly, if you think we should have another function besides > regexp_matches(), rather than just complaining that we don't, it would > be more useful to suggest a name and a specific behavior and ideally > maybe even provide a patch (or just the docs portion of a patch) - > especially if you can point to a specific use-case that is hard to do > with the SRF but would be easier with a function with a different > interface. Below I assume an alternative function is provided. I have problems in finding a name for the function, as regexp_matches() is already used. I would call it regexp_match() in reference to the fact that it returns a single value (being an array) and not a list of matches as potentially regexp_matches() could. The quite similar names could be a problem though. Because table 9-6 is the index people look for when they have a task related to strings, I would say wording should be: [regexp_matches:] Return all groups of captured substrings resulting from matching a POSIX regular expression against the string. Warning: in case of no match, te
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
On Sun, May 30, 2010 at 4:45 AM, Robert Haas wrote: > On Sat, May 29, 2010 at 5:00 PM, Bruce Momjian wrote: >> I have updated the patch, attached, to clarify that this returns text >> arrays, and that you can force it to always return one row using >> COALESCE() and a '|' pattern (the later suggested by Daniele Varrazzo). > > I don't find this part to be something we should include in the > documentation. If we want to include a workaround, how about defining > a non-SRF that just calls the SRF and returns the first row? I think a documentation correction could be backported without problem to all the currently maintained version of PostgreSQL (which would be of good google value, as very often google searches lands you to previous releases doc pages), whereas a easier to use function would be a new feature and as such could only be introduced in 9.0 or even 9.1. -- Daniele -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] extract(epoch from infinity) is not 0
Hello, =# select extract(epoch from 'infinity'::timestamp); date_part --- 0 A better value would be 'infinity'::float8. Ditto for -infinity. I'm trying to use a box-based index to represent the intervals in a table containing a pair of fields date_from, date_to (timestamps), where semi-open intervals are represented with +/- infinity. Building the boxes using extract(epoch from ...) creates wrong entries as semi-open intervals are converted into a box with a corner in (0,0). -- Daniele -- 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 #6453: auto_explain produces bad json output
The following bug has been logged on the website:
Bug reference: 6453
Logged by: Daniele Varrazzo
Email address: daniele.varra...@gmail.com
PostgreSQL version: 9.1.2
Operating system: Any
Description:
Bug verified on PG 9.0.5 and 9.1.2.
auto_explain in json format produces an output such as:
[
"Query Text": "select pg_sleep(2);",
"Plan": {
"Node Type": "Result",
[...]
"Plan Width": 0
}
]
The representation is wrapped in a pair of [], but the content is a mapping:
it should have been a pair of {}.
--
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 #6453: auto_explain produces bad json output
On Sun, Feb 12, 2012 at 12:30 PM, wrote: > auto_explain in json format produces an output such as: > > [ > "Query Text": "select pg_sleep(2);", Oh, sorry, I see it's been already discussed, just in the last days... -- Daniele -- 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_upgrade fails with missing FTS resources
Hello, pg_upgrade fails on missing FTS dictionaries requiring external files. --check fails to detect the incompatibility. E.g. after installed italian_fts (http://pgxn.org/dist/italian_fts/) into a database, the pg_upgrade_dump_db.sql contains the command: CREATE TEXT SEARCH DICTIONARY italian_ispell ( TEMPLATE = pg_catalog.ispell, dictfile = 'italian_ispell', afffile = 'italian_ispell', stopwords = 'italian_ispell' ); that fails with a: psql:/var/lib/postgresql/pg_upgrade_dump_db.sql:56891: ERROR: could not open dictionary file "/usr/share/postgresql/9.1/tsearch_data/italian_ispell.dict": No such file or directory I think it would be enough to detect the incompatibility at --check time, as much as it's done for the required libraries, and only for the builtin dict types. If you want the issue to be fixed I can try to fix it myself: it seems easy enough. -- Daniele -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Can't move extension out of pg_catalog
After moving an extension into the pg_catalog schema, it seems impossible to move it somewhere else: test=# create extension ltree; CREATE EXTENSION test=# alter extension ltree set schema pg_catalog; ALTER EXTENSION test=# alter extension ltree set schema public; ERROR: cannot remove dependency on schema pg_catalog because it is a system object. It is still possible to drop it. Tested with PostgreSQL 9.1.3. -- Daniele -- 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 #6734: create table (like ...) fails if an index has a comment
The following bug has been logged on the website: Bug reference: 6734 Logged by: Daniele Varrazzo Email address: daniele.varra...@gmail.com PostgreSQL version: 9.1.4 Operating system: Linux Description: Weird, isn't it? Test case below. Dropping the comment, the create table command works as expected. The command fails with an: "ERROR: relation "foo2_f1_idx" already exists". begin; CREATE TABLE foo ( id serial primary key, f1 integer NOT NULL ); CREATE INDEX foo_idx1 ON foo (f1); CREATE INDEX foo_idx2 ON foo (f1) WHERE id > 10; COMMENT ON INDEX foo_idx2 IS 'whatever'; create table foo2 (like foo including all); rollback; -- 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 #6734: create table (like ...) fails if an index has a comment
On Fri, Jul 13, 2012 at 2:24 PM, Ryan Kelly wrote: > On Fri, Jul 13, 2012 at 12:00:14PM +, daniele.varra...@gmail.com wrote: >> The following bug has been logged on the website: >> >> Bug reference: 6734 >> Logged by: Daniele Varrazzo >> Email address: daniele.varra...@gmail.com >> PostgreSQL version: 9.1.4 >> Operating system: Linux >> Description: >> >> Weird, isn't it? Test case below. >> >> Dropping the comment, the create table command works as expected. The >> command fails with an: "ERROR: relation "foo2_f1_idx" already exists". > The comments on chooseIndexName in src/backend/parser/parse_utilcmd.c say: > > * XXX this is inherently broken because the indexes aren't created > * immediately, so we fail to resolve conflicts when the same name is > * derived for multiple indexes. > > Which looks like the case here. So it seems like > chooseIndexName/ChooseIndexName might need to take a list of any indexes > names that have already been created to avoid this. For the work I'm doing now (a migration of a table with a dozen of indexes, that will need further process downstream) I'm finding it would be much better to have name generation more predictable: even without the bug, the index names are not very descriptive. Yes, they contain the field name and the new table name, but the name may have been something meaningful such as "open_contracts_idx". For conflicting names (such as the ones in the test case, without the comment) I guess it's undefined, or arbitrary anyway, which one gets the numeric suffix. Wouldn't it be better to call the indexes NEWTABLE_OLDINDEXNAME? They would be more verbose, but generation of two equally named indexes in the same table would be impossible (as OLDINDEXNAMEs are distinct) and it would be easy to map old indexes into new ones. The "add a number suffix" behavior would be still required to avoid conflict with the name of an index of another table, but I guess this is already handled by the current implementation. -- Daniele -- 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 #6734: create table (like ...) fails if an index has a comment
On Fri, Jul 13, 2012 at 4:11 PM, Tom Lane wrote: > Daniele Varrazzo writes: >> Wouldn't it be better to call the indexes NEWTABLE_OLDINDEXNAME? > > Then the CREATE LIKE command would fail altogether if that name were > already taken. Postponing the selection of the index name to the time > when DefineIndex runs is really the only safe thing. I've actually suggested to keep the numeric suffix to disambiguate in case the name is already taken, as I assume the current "create like" does. However this is not strictly related to the bug, it's more an usability problem, so won't bother you with it anymore. -- Daniele -- 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 #7572: virtualxid lock held by bgwriter on promoted slaves
The following bug has been logged on the website: Bug reference: 7572 Logged by: Daniele Varrazzo Email address: daniele.varra...@gmail.com PostgreSQL version: 9.1.4 Operating system: Linux Description: Hello, when a slave is promoted, the pgwriter keeps holding a lock with virtualxid "1/1" and virtualtransaction "-1/0". Such lock stops pg_reorg to run (as reported here: http://pgfoundry.org/tracker/index.php?func=detail&aid=1011203&group_id=1000411&atid=1376) but I've verified the same condition on 9.1.4). Is it possible to free that lock on slave promotion? Is it safe to ignore that lock for pg_reorg sake? The program is which is probably waiting for all the transactions to finish before swapping the table in the system catalog but I'm not sure about that yet. -- 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 #7797: datetime + '1 month'::interval is going outside of a month's bounds
On Mon, Jan 7, 2013 at 5:19 PM, Ryan Kelly wrote: > '1 month'::interval is the same as '30 days'::interval. No, it's not. # select '2012-07-31'::date + '1 month'::interval, '2012-07-31'::date + '30 days'::interval; ?column? | ?column? -+- 2012-08-31 00:00:00 | 2012-08-30 00:00:00 -- Daniele -- 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 #7797: datetime + '1 month'::interval is going outside of a month's bounds
On Mon, Jan 7, 2013 at 5:47 PM, Ryan Kelly wrote: > Alright, now I'm thoroughly confused. What magic makes this true: """ Internally interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. """ http://www.postgresql.org/docs/9.1/static/datatype-datetime.html -- Daniele -- 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 #8118: Wrong sorting text
>> Bug reference: 8118 >> Logged by: whiplash >> Email address: whipl...@bss.org.ua >> PostgreSQL version: 9.2.2 >> Operating system: Linux (Fedora 11, Fedora 16 and Ubuntu 12.04) >> Description: >> >> I execute query (1): >> >> SELECT t.name >> FROM >> ( >> SELECT 'AAA AAA' AS name >> UNION ALL >> SELECT 'AAA_AAA' >> UNION ALL >> SELECT 'BBB_AAA' >> UNION ALL >> SELECT 'BBB AAB' >> ) t >> ORDER BY t.name >> >> and I getting a result: >> >> AAA AAA >> AAA_AAA >> BBB_AAA >> BBB AAB It's a matter of collate. If you want ascii ordering specify collate "C". ... ORDER BY t.name collate "C"; name - AAA AAA AAA_AAA BBB AAB BBB_AAA (4 rows) -- Daniele -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
