Re: [BUGS] LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...
Well, thanks for trying it - but that is strange. Yes, my database is created with datcollate "C" - the script displays that (see below). The only thing I can think of that might explain different outcome is if, in your environment, the planner did not choose the index-access plan after the index was created, and/or did not add the two range predicates. Is that the case? The script output shows the two plans before and after creating the index. Here is the output from running the script just now (and I ran it on a very different system than before just to get another sample point and it exhibits the same bug). Is your output different? Do you think you could post the output you get? Regards, John Lumby --- /home/postgres/bin/demo_like_42p22.sh WARNING this script will (try to) drop and recreate a database named LIKE_42P22 press enter to continue or Ctl-C to cancel NOTICE: database "LIKE_42P22" does not exist, skipping DROP DATABASE CREATE TABLE Tue Dec 13 08:50:12 EST 2011 rc= 0 inserted 1 entities Tue Dec 13 09:05:30 EST 2011 ANALYZE datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace ++--++--+---+--+--+---+--+--- LIKE_42P22 | 10 | 6 | C | C | f | t | -1 | 11910 | 670 | 1663 (1 row) nspname | relname | attrelid | attname | atttypid | typname | collname -+-+--+---+--+-+-- public | entity | 26506 | discriminator | 1043 | varchar | default (1 row) QUERY PLAN --- Aggregate (cost=190.26..190.27 rows=1 width=0) -> Seq Scan on entity e1 (cost=0.00..189.00 rows=505 width=0) Filter: ((discriminator)::text ~~ 'DEPLOY%'::text) (3 rows) count --- 500 (1 row) CREATE INDEX ANALYZE QUERY PLAN -- Aggregate (cost=23.71..23.72 rows=1 width=0) -> Index Scan using entity_discriminator on entity e1 (cost=0.00..22.45 rows=505 width=0) Index Cond: (((discriminator)::text >= 'DEPLOY'::text) AND ((discriminator)::text < 'DEPLOZ'::text)) Filter: ((discriminator)::text ~~ 'DEPLOY%'::text) (4 rows) ERROR: could not determine which collation to use for string comparison HINT: Use the COLLATE clause to set the collation explicitly. > To: johnlu...@hotmail.com > CC: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] LIKE predicate and ERROR: 42P22: could not determine > which collation to use for string comparison - HINT: Use the COLLATE clause > ... > Date: Mon, 12 Dec 2011 16:38:53 -0500 > From: t...@sss.pgh.pa.us > > John Lumby writes: > > Sorry about that, here is a script to demonstrate using a much simpler > > example. > > It presumes linux and that there is a bash shell at /bin/bash. > > > You should see table created and loaded (takes a few minutes), then > > successful query explain and run, > > then create index and re-run the explain (now uses index) and query (now > > fails) > > Hmm ... I tried this script on both HEAD and 9.1, and see no failure. > Possibly it requires some non-default configuration setting to show > the problem? Also, are you certain your database is ending up with > LC_COLLATE and LC_CTYPE set to "C"? I am not sure that createdb pays > attention to those as locale settings. > > 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 -- 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 #6333: pgAdmin 14.1 - Listbox stupidities
The following bug has been logged on the website: Bug reference: 6333 Logged by: Daniel Migowski Email address: dmigow...@ikoffice.de PostgreSQL version: 9.1.2 Operating system: Windows 7 Description: I have a user group "ikofficeusers", and a user "ik" in my database. In pgAdmin it is now not possible to choose the user "ik" from the role list when e.g. creating a new database, because when the focus looses the combo control, it switches to "ikofficeusers". This problem could be solved by simply ordering users and roles alphabetically. -- 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] LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...
John Lumby writes: > Is your output different? Do you think you could post the output you get? Mine looks like yours, except no error. [ looks closer... ] Are you sure you're running a 9.1.2 server? The large difference in datlastsysoid seems like it could not have happened if you had production 9.1.x system catalog contents. Now I'm suspicious there's something wrong with your catalog entries, not the code itself. DROP DATABASE CREATE TABLE Tue Dec 13 09:59:18 EST 2011 rc= 0 inserted 1 entities Tue Dec 13 10:00:30 EST 2011 ANALYZE datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace ++--++--+---+--+--+---+--+--- LIKE_42P22 | 10 |6 | C | C| f | t | -1 | 12772 | 1795 | 1663 (1 row) nspname | relname | attrelid |attname| atttypid | typname | collname -+-+--+---+--+-+-- public | entity |41366 | discriminator | 1043 | varchar | default (1 row) QUERY PLAN --- Aggregate (cost=190.26..190.27 rows=1 width=0) -> Seq Scan on entity e1 (cost=0.00..189.00 rows=505 width=0) Filter: ((discriminator)::text ~~ 'DEPLOY%'::text) (3 rows) count --- 500 (1 row) CREATE INDEX ANALYZE QUERY PLAN -- Aggregate (cost=23.71..23.72 rows=1 width=0) -> Index Scan using entity_discriminator on entity e1 (cost=0.00..22.45 rows=505 width=0) Index Cond: (((discriminator)::text >= 'DEPLOY'::text) AND ((discriminator)::text < 'DEPLOZ'::text)) Filter: ((discriminator)::text ~~ 'DEPLOY%'::text) (4 rows) count --- 500 (1 row) regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...
Yes, I'm definitely running 9.1.2 (on the output I sent earlier today). I've also tried again on yet another different system and get the same bug there. I do have other databases in my cluster which probably explains the different datlastsysoid. I am at a loss to explain what could be different. I've run it on different machines, different linux distros and both postgres 9.1 releases, and see same result every time. There must be some (other) external environmental factor. Do you happen to know of any? I wonder if the locale in effect when I ran initdb affects it? I would have had LANG=C and LC_ALL=C exported. Do you have any system where you could try an initdb with those environment vars exported? What are the values of relevant locale-type env vars when you ran initdb on your test sys? Regards, John Lumby -- 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] LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...
John Lumby writes: > I do have other databases in my cluster which probably explains the different > datlastsysoid. No, it wouldn't. datlastsysoid only counts objects created during initdb. [ thinks for a bit ] The most likely explanation for the different datlastsysoid value is that the number of pre-created pg_collation entries is quite a bit different on my box than yours, as a result of a different list from "locale -a". It's hard to see why that would have anything to do with your issue, but I have a feeling it does somehow. What platforms have you tried this on, exactly? I tried it on Fedora 14. > What are the values of relevant locale-type env vars when you ran initdb on > your test sys? Shrug ... C. That's my normal working environment. I did try it with en_US.utf8 too, just for grins. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6333: pgAdmin 14.1 - Listbox stupidities
On Tue, 2011-12-13 at 15:17 +, dmigow...@ikoffice.de wrote: > The following bug has been logged on the website: > > Bug reference: 6333 > Logged by: Daniel Migowski > Email address: dmigow...@ikoffice.de > PostgreSQL version: 9.1.2 > Operating system: Windows 7 > Description: > > I have a user group "ikofficeusers", and a user "ik" in my database. > > In pgAdmin it is now not possible to choose the user "ik" from the role list > when e.g. creating a new database, because when the focus looses the combo > control, it switches to "ikofficeusers". This problem could be solved by > simply ordering users and roles alphabetically. > It seems you're reporting a pgAdmin's bug. pgsql-bugs isn't the list for that kind of reports. You should send that to the pgadmin-support mailing list. Regards. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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 #6317: sequence error for pg_dump
On Fri, Dec 2, 2011 at 4:48 AM, wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6317
> Logged by: hua ming fei
> Email address: h...@alkheme.com
> PostgreSQL version: 9.0.1
> Operating system: Centos 5.5
> Description:
>
> use pg_dump when didn't close the application , weird things occured . the
> current value of one sequence is more slower than the max number of one
> primary key base on the sequence . So, I looked into the dump file , found
> command"
> SELECT pg_catalog.setval('attrnamemaster_attrnameid_seq', 3009,
> true);" to
> set sequence values number , but the import data, there are many lines with
> number larger than the sequence value :
> 3012 38 2147483647 t 10041 material material
> material 1004.10041 4.1 f
> 3013 38 2147483647 t 10042 disable
> 1004.10042 4.2 f
Without a reproducible test case, it's tough to see how anyone's going
to be able to fix any bug that may be lurking here. When you use
vague phrases like "when didn't close the application, weird things
occurred", it's kind of hard to figure out what exactly you did. If
you come up with a specific sequence of steps involving simple tools
like pg_dump and psql that produces a result you think is wrong,
someone here can probably (a) tell you whether the behavior is
intended, and (b) if not, fix it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] could not truncate directory "pg_subtrans": apparent wraparound
On Sun, Dec 4, 2011 at 7:27 AM, MirrorX wrote: > hello to all, > > i found this error in the logs on the hot_standby server. this error > appeared only once a few days back and hasn't come up again. the version i > am using is 9.0.5 and the primary was loaded by restoring dump files > (previous version was 8.4.4). i have never switched to this server and i am > using streaming replication. the activity of the primary is very low, it > creates about 5-10 wal archives every day and has always been like that. the > error does not appear on the primary. > > if you need more details about the servers plz tell me and i will post > them.any information that you may have is more than welcome. thx in advance Could you send the output of pg_controldata on the standby, and also the contents of pg_clog on that machine? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] LIKE predicate and ERROR: 42P22: could not determine which collation to use for string comparison - HINT: Use the COLLATE clause ...
(Horribly red face) All my fault. It turns out I had applied a source-code change for some additional dtracing which I had merged forward from an older version but which was incorrect in V9. After removing that it works fine. Really sorry about that John Lumby > To: johnlu...@hotmail.com > CC: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] LIKE predicate and ERROR: 42P22: could not determine > which collation to use for string comparison - HINT: Use the COLLATE clause > ... > Date: Tue, 13 Dec 2011 14:04:02 -0500 > From: t...@sss.pgh.pa.us > > John Lumby writes: > > I do have other databases in my cluster which probably explains the > > different datlastsysoid. > > No, it wouldn't. datlastsysoid only counts objects created during > initdb. > > [ thinks for a bit ] The most likely explanation for the different > datlastsysoid value is that the number of pre-created pg_collation > entries is quite a bit different on my box than yours, as a result of a > different list from "locale -a". It's hard to see why that would have > anything to do with your issue, but I have a feeling it does somehow. > > What platforms have you tried this on, exactly? I tried it on Fedora 14. > > > What are the values of relevant locale-type env vars when you ran initdb on > > your test sys? > > Shrug ... C. That's my normal working environment. I did try it with > en_US.utf8 too, just for grins. > > 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 -- 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 #6334: initdb not working
The following bug has been logged on the website: Bug reference: 6334 Logged by: Wilfried Weiss Email address: wilfried.we...@nsg.com PostgreSQL version: 9.1.2 Operating system: AIX 5300-12-04-1119 Description: I successfully compiled 9.1.2 using IBM xlc 9.0 and gnu make 3.80-1. When trying to initialise the database, no directory structure is created. I used parameter -d for more debug info but did not find any useful hint why initdb is not working. I am using PostgreSQL since Version 7.0.4, but that never happened to me. Any hint is very much appreciated. Regards Wilfried -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
