[BUGS] I have a question about using index in order statement.
Question: I have a question about using index in order statement. Why index ix_2 work by Seq Scan and index ix_3 work by Index Scan. Example : ix_2 condition : When I try explain select * from a_test order by code_ desc Postgresql response Sort (cost=11815.08..11852.56 rows=14990 width=56) Sort Key: code_ - Seq Scan on a_test (cost=1.00..10260.90 rows=14990 width=56) ix_3 condition : When I try explain select * from a_test order by lower(code_) desc Postgresql response Index Scan using ix_3 on a_test (cost=0.00..769.27 rows=14990 width=18) Table schema : CREATE TABLE a_test ( t_key_ bigint NOT NULL, code_ character varying(15) ) WITH (OIDS=TRUE); ALTER TABLE a_test OWNER TO postgres; CREATE INDEX ix_2 ON a_test USING btree (code_ DESC); CREATE INDEX ix_3 ON a_test USING btree (lower(code_::text) DESC);
Re: [BUGS] Postgresql Domain Names
Andy.Xue wrote: Dear Manager, We are China Net Technology Limited, which is the domain name register center in China.I have something need to confirm with you. we have received an application formally,one company named LEALUI Holdings Limited applies for the domain names(postgresql.hk/postgresql.tw/postgresql.mobi etc.) and the internet Brand Name(Postgresql)on the internet Nov. 2, 2007. We need to know the opinion of your company, because the domain names and keywords may relate to the usufruct of brand name on internet. we would like to get the affirmation of your company,please contact us by telephone or email as soon as possible. Please let someone in your company who is responsible for trademark or intellectual right contact me freely. People; please *do not* respond to this. Someone from -core will handle it. Thanks, Dave -- Dave Page PostgreSQL Core Team ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] I have a question about using index in order statement.
Heikki Linnakangas [EMAIL PROTECTED] writes: Attached is a patch that fixes that test case. I'm not very familiar with that piece of code, though, and I have a sneaking suspicion that the patch is either not general enough, there may be other places where we should ignore relabel nodes, or it brakes something else. If we were going to go this route, we'd need to uniformly make the equivclass.c code strip RelabelType from *all* expressions it deals with. Which might be a reasonable thing to do, since it always considers their types to be the declared input types of the operators, anyway. But it's a bigger patch than you have here. I'm a bit annoyed with the idea because I had hoped to move in the direction of getting rid of all the ad hoc RelabelType-stripping that the planner does in various places. A lot of it is pretty darn questionable from a semantic standpoint. However, because equivclass.c is only worried about opfamily semantics, any RelabelTypes on the input expressions don't matter, and so that objection doesn't apply. The basic reason that there's a problem here is that the parser is playing fast and loose by generating ORDER BY information that cites text text as the sort operator but applies it to a bare varchar Var node. So I thought about a Plan B of changing the parser to put a correct RelabelType on the sort key. I'm not sure of all the implications of that, though, and you could argue that it's an initdb-forcing change (because stored rules involving ORDER BY on varchar columns would need to change to work right). Seems a bit late in the 8.3 cycle for that. I guess the right answer is to fix equivclass.c to strip RelabelTypes, and hope to maybe take that out again someday when all this gets cleaned up. Comments? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] Postgresql Domain Names
Dear Manager, We are China Net Technology Limited, which is the domain name register center in China.I have something need to confirm with you. we have received an application formally,one company named LEALUI Holdings Limited applies for the domain names(postgresql.hk/postgresql.tw/postgresql.mobi etc.) and the internet Brand Name(Postgresql)on the internet Nov. 2, 2007. We need to know the opinion of your company, because the domain names and keywords may relate to the usufruct of brand name on internet. we would like to get the affirmation of your company,please contact us by telephone or email as soon as possible. Please let someone in your company who is responsible for trademark or intellectual right contact me freely. Best Regards, Andy.Xue Sponsoring Registrar: China Net Technology Limited. Tel:+(852)3177 1510 Fax:+(852)3177 1520 Email: [EMAIL PROTECTED] Website:www.china-net.hk
[BUGS] tsvector that can't be dumped/reloaded in 8.3beta
I was unable to dump from beta1 to beta2, and I narrowed it down to this simpler case: test= select tsvectorin(tsvectorout( to_tsvector($foo$ a.b.cd/x=mnop.q\ $foo$) )); ERROR: syntax error in tsvector: 'a.b.cd':2 'a.b.cd/x':1 '/x=mnop.q \':3 This has the same result on beta1 and beta2. I'm using en_US.UTF-8 on FreeBSD. Regards, Jeff Davis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #3713: problem with thread safety???
The following bug has been logged online: Bug reference: 3713 Logged by: Ted Petrosky Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3 beta 2 Operating system: OS X 10.5 Description:problem with thread safety??? Details: whilst configuring on a new Leopard install with this config line: ./configure --with-bonjour --enable-thread-safety --with-perl --with-python --with-libedit-preferred checking for sgmlspl... no checking thread safety of required library functions... no configure: error: thread test program failed This platform is not thread-safe. Check the file 'config.log'for the exact reason. You can use the configure option --enable-thread-safety-force to force threads to be enabled. But you must then run the program in src/test/thread and add locking function calls to your applications to guarantee thread safety. I have never seen this before on OS X and I don't really know if it is a limitation or a bug. Ted ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] could not open relation
Que tal... Les escribo en esta oportunidad para contarles algo que he analizado desde el log,y que creo que pueda ser el inconveniento para que mi BD se ponga lenta. resulta que yo tengo instalado el Postgres 8.2 en windows 2003 server en la siguiente ruta estan los datos C:\Program Files\PostgreSQL\8.2\data\base\16779 y dentro de este si existe el 2703 y muchos mas, lo que se me hace raro es que cuando el saca este tipo de error simpre trata de de buscar en una ruta como la que muestro debajo could not open relation 1663/16779/2703 Lo cual marca un error, efectivamente ya que el directorio 1663 no existe, los dos proximos si existen, quiero saber por que el PostgreSQL trata de buscar en esa ruta, será por lo que yo volvi ha compilar la BD desde un archivo SQL y este cuando se saco, existia el directorio 1663 lo digo presumiedo pero no lo se. Bueno mi pregunta es si yo puedo dejar de que el motor deje de buscar en esa ruta y quede buscando en la que en realidad debe de ser. Como lo podría hacer. Espero alguna colaboracion. -- Ing Sistemas y Telecomunicaciones Luis Felipe Lopez Amezquita 3008217523 - 3146286237 - 3173160602
Re: [BUGS] I have a question about using index in order statement.
kevin wrote:
Question:
I have a question about using index in order statement.
Why index ix_2 work by Seq Scan and index ix_3 work by Index Scan.
Example :
ix_2 condition :
When I try
explain
select * from a_test
order by code_ desc
Postgresql response
Sort (cost=11815.08..11852.56 rows=14990 width=56)
Sort Key: code_
- Seq Scan on a_test (cost=1.00..10260.90 rows=14990
width=56)
ix_3 condition :
When I try
explain
select * from a_test
order by lower(code_) desc
Postgresql response
Index Scan using ix_3 on a_test (cost=0.00..769.27 rows=14990 width=18)
Thanks for the report. This seems to have been broken by this patch back
in May:
http://archives.postgresql.org/pgsql-committers/2007-05/msg00394.php
that wraps pathkey expressions with a relabel node. Because of that,
get_eclass_for_sort_expr doesn't recognize that the ordering of the
index matches that of the query.
Attached is a patch that fixes that test case. I'm not very familiar
with that piece of code, though, and I have a sneaking suspicion that
the patch is either not general enough, there may be other places where
we should ignore relabel nodes, or it brakes something else.
I'm surprised this hasn't been noticed before. It doesn't happen with
text datatype, but varchar is very common datatype as well.
PS. Kevin, in the future, please specify which PostgreSQL version you're
using. The fact that the above DDL statements don't work until 8.3beta
releases gave it away this time :-).
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/optimizer/path/equivclass.c
===
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/optimizer/path/equivclass.c,v
retrieving revision 1.3
diff -c -r1.3 equivclass.c
*** src/backend/optimizer/path/equivclass.c 7 Jul 2007 20:46:45 - 1.3
--- src/backend/optimizer/path/equivclass.c 2 Nov 2007 11:48:12 -
***
*** 373,378
--- 373,388
EquivalenceMember *newem;
ListCell *lc1;
MemoryContext oldcontext;
+ Expr *stripped_expr;
+
+ /*
+ * Strip any relabel nodes first; they're not meaningful
+ * for ordering purposes.
+ */
+ if (IsA(expr, RelabelType))
+ stripped_expr = ((RelabelType *)expr)-arg;
+ else
+ stripped_expr = expr;
/*
* Scan through the existing EquivalenceClasses for a match
***
*** 390,395
--- 400,406
foreach(lc2, cur_ec-ec_members)
{
EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
+ Expr *em_expr;
/*
* If below an outer join, don't match constants: they're not
***
*** 399,406
cur_em-em_is_const)
continue;
if (expr_datatype == cur_em-em_datatype
! equal(expr, cur_em-em_expr))
return cur_ec; /* Match! */
}
}
--- 410,421
cur_em-em_is_const)
continue;
+ em_expr = cur_em-em_expr;
+ if (IsA(em_expr, RelabelType))
+ em_expr = ((RelabelType *)em_expr)-arg;
+
if (expr_datatype == cur_em-em_datatype
! equal(stripped_expr, em_expr))
return cur_ec; /* Match! */
}
}
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[BUGS] BUG #3714: applications using COPY are now much slower
The following bug has been logged online: Bug reference: 3714 Logged by: Radim Kolar Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3 beta2 Operating system: BSD/64bit Description:applications using COPY are now much slower Details: Applications using COPY for batch insert few records are now much slower on heavily updated table than in 8.2. Using COPY for importing database dumps in 8.3 is noticeable faster than in 8.2 I recommend to add some configuration file switch for switching COPY between old and new mode or add COPY NOLOG keyword. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3713: problem with thread safety???
Ted Petrosky [EMAIL PROTECTED] writes: checking thread safety of required library functions... no configure: error: thread test program failed This platform is not thread-safe. Check the file 'config.log'for the exact reason. And? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] I have a question about using index in order statement.
Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: I guess the right answer is to fix equivclass.c to strip RelabelTypes, and hope to maybe take that out again someday when all this gets cleaned up. That certainly looks like the easier solution. We still strip RelabelTypes in many places anyway, so doing it in one more place doesn't seem too bad to me. I reconsidered this after further thought. If we do that it'd also mean that clauses generated from EquivalenceClasses don't have RelabelType, which is definitely going in the wrong direction in a big way. What I'm just about to experiment with is the idea that lines 498-517 of pathkeys.c are in the wrong place: that should be done within make_pathkey_from_sortinfo, so that it would also happen for expressions coming from SortClauses (see the other caller of make_pathkey_from_sortinfo). Then the expressions coming in to equivclass.c should always match. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] tsvector that can't be dumped/reloaded in 8.3beta
Jeff Davis [EMAIL PROTECTED] writes: I was unable to dump from beta1 to beta2, and I narrowed it down to this simpler case: test= select tsvectorin(tsvectorout( to_tsvector($foo$ a.b.cd/x=mnop.q\ $foo$) )); ERROR: syntax error in tsvector: 'a.b.cd':2 'a.b.cd/x':1 '/x=mnop.q \':3 This looks like the already-known issue that tsvectorout fails to double backslashes ... regards, tom lane ---(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
Re: [BUGS] BUG #3713: problem with thread safety???
Theodore Petrosky [EMAIL PROTECTED] writes: as I don't know enough to tell what is happening, I will attach the config.log... Well, you didn't need to look all that hard ... configure:24893: checking thread safety of required library functions configure:24921: gcc -no-cpp-precomp -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -Kthread -kthread -pthread -pthreads -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DIN_CONFIGURE conftest.c -lz -ledit -lm -lpthread5 conftest.c:146:43: error: ./src/test/thread/thread_test.c: No such file or directory So somehow src/test/thread/thread_test.c is missing from your source tree. The only reason I've seen for that happening is when people thought that they could download just the base tarball and not the whole source distribution. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3714: applications using COPY are now much slower
Radim Kolar [EMAIL PROTECTED] writes: Applications using COPY for batch insert few records are now much slower on heavily updated table than in 8.2. Please give a *specific* test case showing what you are talking about... I recommend to add some configuration file switch for switching COPY between old and new mode or add COPY NOLOG keyword. ... because this suggestion makes no sense whatsoever. There is no old and new mode. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3715: StackBuilder failing
The following bug has been logged online: Bug reference: 3715 Logged by: Thomas Haegi Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3b2 Operating system: Windows 2003 Description:StackBuilder failing Details: This is on a vanilla 8.3b2 installation: running stackbuilder from either the start menu's shortcut or through the commandline results in the following error message box: C:\program files\postgresql\8.3-beta2\bin\StackBuilder.Exe This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. [OK] i've been using the installer version postgresql-8.3-beta2.zip. as an additional remark: the installer's last screen asks if one wants to run stackbuilder - it would be nice to give a short explanation what it is about, and why it has to be run (if it has to, at all :)) - thomas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #3715: StackBuilder failing
Bug reference: 3715 PostgreSQL version: 8.3b2 Operating system: Windows 2003 Description:StackBuilder failing some additional info to the just submitted bugreport: - pgAdminIII fails as well - postgres service starts fine - eventlog shows missing dependencies: Source: SideBySide: Dependent Assembly Microsoft.VC80.CRT could not be found and Last Error was The referenced assembly is not installed on your system. Source: SideBySide: Resolve Partial Assembly failed for Microsoft.VC80.CRT. Reference error message: The referenced assembly is not installed on your system. Source: SideBySide: Generate Activation Context failed for C:\Program Files\PostgreSQL\8.3-beta2\bin\StackBuilder.exe. Reference error message: The referenced assembly is not installed on your system. - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3706: ecpg regression: MOVE FORWARD
I haven't seen any feedback on this report, but FYI... I worked around this problem by rewriting my code to use a dynamic prepared statement instead, e.g. OLD: EXEC SQL MOVE FORWARD :RowMove IN Sniffer; NEW: const char mover[] = MOVE FORWARD ? IN Sniffer; [...] EXEC SQL PREPARE MoverSQL FROM :mover; [...] EXEC SQL EXECUTE MoverSQL USING :RowMove; [...] EXEC SQL DEALLOCATE PREPARE MoverSQL; Assuming the new restriction on the original MOVE FORWARD is intentional, it would be nice to see a warning that code changes are required get into the documentation. Thanks, Scott -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Bailey Sent: Monday, October 29, 2007 11:45 AM To: pgsql-bugs@postgresql.org Subject: [BUGS] BUG #3706: ecpg regression: MOVE FORWARD The following bug has been logged online: Bug reference: 3706 Logged by: Scott Bailey Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3beta1 Operating system: Tru64 Description:ecpg regression: MOVE FORWARD Details: The following statement: exec sql MOVE FORWARD :RowMove IN Sniffer; which worked correctly in 8.2.4 and previous versions, now fails to compile in 8.3beta1; I receive the following error message: ERROR: fetch/move count must not be a variable. This sucks and I couldn't find anything about it in the TODO list or Release Notes. Is this unintentional, or should I be recoding my program to use a loop to move forward 1 row at a time? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] I have a question about using index in order statement.
Tom Lane wrote: The basic reason that there's a problem here is that the parser is playing fast and loose by generating ORDER BY information that cites text text as the sort operator but applies it to a bare varchar Var node. So I thought about a Plan B of changing the parser to put a correct RelabelType on the sort key. I'm not sure of all the implications of that, though, and you could argue that it's an initdb-forcing change (because stored rules involving ORDER BY on varchar columns would need to change to work right). Seems a bit late in the 8.3 cycle for that. I think mentioning it in the release notes would be enough. You would just have to recreate the rules to make them work again, right? I guess the right answer is to fix equivclass.c to strip RelabelTypes, and hope to maybe take that out again someday when all this gets cleaned up. That certainly looks like the easier solution. We still strip RelabelTypes in many places anyway, so doing it in one more place doesn't seem too bad to me. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3716: utf8 crash with replace
The following bug has been logged online:
Bug reference: 3716
Logged by: Andreas Pflug
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system: Linux,Windows
Description:utf8 crash with replace
Details:
If replace is used with UTF8 client_encoding, searching for a malformed
string, the backend will crash.
SELECT replace('Ãrger', chr(195), 'Ae')
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] BUG #3715: StackBuilder failing
--- Original Message --- From: Thomas H. [EMAIL PROTECTED] To: pgsql-bugs@postgresql.org Sent: 02/11/07, 19:52:43 Subject: Re: [BUGS] BUG #3715: StackBuilder failing Bug reference: 3715 PostgreSQL version: 8.3b2 Operating system: Windows 2003 Description:StackBuilder failing some additional info to the just submitted bugreport: - pgAdminIII fails as well - postgres service starts fine - eventlog shows missing dependencies: Source: SideBySide: Dependent Assembly Microsoft.VC80.CRT could not be found and Last Error was The referenced assembly is not installed on your system. Source: SideBySide: Resolve Partial Assembly failed for Microsoft.VC80.CRT. Reference error message: The referenced assembly is not installed on your system. Source: SideBySide: Generate Activation Context failed for C:\Program Files\PostgreSQL\8.3-beta2\bin\StackBuilder.exe. Reference error message: The referenced assembly is not installed on your system. Already fixed - theres an updated build at http://developer.pgadmin.org/~dpage/postgresql-8.3-beta2-2.zip Thanks for the report though. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3715: StackBuilder failing
Already fixed - theres an updated build at http://developer.pgadmin.org/~dpage/postgresql-8.3-beta2-2.zip Thanks for the report though. thanks, works fine now. maybe worth a short note in the download directory, so that others won't report the same thing? - thomas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3717: libpq needs .so version raised to 6
The following bug has been logged online: Bug reference: 3717 Logged by: Radim Kolar Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3beta2 Operating system: FreeBSD Description:libpq needs .so version raised to 6 Details: /libexec/ld-elf.so.1: /usr/local/pgsql8.3/bin/psql: Undefined symbol PQconnectionUsedPassword both libpq from pgsql 8.2 and 8.3 are using same version of .so 5. But libpq from 8.3 have new function PQconnectionUsedPassword. .so version of 8.3 libpq needs to be raised to 6 /usr/local/pgsql8.3/lib/libpq.so.5 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3717: libpq needs .so version raised to 6
Radim Kolar [EMAIL PROTECTED] writes: both libpq from pgsql 8.2 and 8.3 are using same version of .so 5. But libpq from 8.3 have new function PQconnectionUsedPassword. .so version of 8.3 libpq needs to be raised to 6 This is not a bug. Major version bumps in sonames are for incompatible changes, not for additions of new entry points. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] could not open relation
Felipe Amezquita wrote: Hola Felipe, por favor usa la lista pgsql-es-ayuda cuando quieras preguntar en español. Si quieres escribir a -bugs, hazlo en ingles. Gracias. Les escribo en esta oportunidad para contarles algo que he analizado desde el log,y que creo que pueda ser el inconveniento para que mi BD se ponga lenta. resulta que yo tengo instalado el Postgres 8.2 en windows 2003 server en la siguiente ruta estan los datos C:\Program Files\PostgreSQL\8.2\data\base\16779 y dentro de este si existe el 2703 y muchos mas, lo que se me hace raro es que cuando el saca este tipo de error simpre trata de de buscar en una ruta como la que muestro debajo could not open relation 1663/16779/2703 El 1663 es el directorio del tablespace. ¿Quizas hiciste alguna jugarreta con tablespaces en el servidor anterior, que no fue correctamente restaurada? Lo raro es que 2703 es un indice en pg_type ... hasta donde entiendo eso deberia estar siempre en el tablespace por default. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 Bob [Floyd] used to say that he was planning to get a Ph.D. by the green stamp method, namely by saving envelopes addressed to him as 'Dr. Floyd'. After collecting 500 such letters, he mused, a university somewhere in Arizona would probably grant him a degree. (Don Knuth) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
