Re: [BUGS] LATIN2 ORDER BY
On 2002.08.28 at 13:20:29 +0200, Kristóf Kontor wrote: Hi ! My problem is that i have LATIN2 encoding in my postgres and ISO-8859-2 charset on my linux, but when i'am ordering strings in psql with acutes it does not work properly... (psql version = 7.1, linux = debian woody) Are you sure, that you've system locale (LANG variable) set to something.ISO8859-2 when you've invoked initdb command? Are you sure that postmaster process does run with this locale? Typically debian postinst script takes care of it, but it can fail if locales package wasn't configured yet when postgresql package is installed. These two items are required by postgres to operate correctly with charsets other than SQLANSI. I've had no problems when I began to take them into account with any of various cyrillic charsets, since 7.1.0 Actually, only LC_CTYPE and LC_COLLATE categories are needed. BTW, version of postgresql in Debian woody is 7.2.1 So it is strange, that you have earilier one. Have you installed it from Debian package or build by hand? If later, it is better to revert to Debian version. -- Victor Wagner [EMAIL PROTECTED] Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] bug \! cd
On 2002.07.26 at 12:00:01 -0600, Kathy zhu wrote: I did '\i cd' on solaris. It doesn't change the directory. It stays where it is. It is not a bug, it is a feature. Of any Unix, including Solaris, Linux etc. Current directory is a propriety of running process. When process is finished, its current directory is forgotten, and parent process doesn't know anything about it. \! in psql starts shell as child process and executes given command in it. So, executing a command cd couldn't affect psql process by design. If there is a bug, it is that there is no backslash-command to change working direcory. But there is such command - \cd, at least in 7.2.1 -- Victor Wagner [EMAIL PROTECTED] Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Referential integrity problem postgresql 7.2 ?
On 2002.06.11 at 14:43:17 -0400, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: It should work (and does in current sources). If you look in the archives you should be able to get info on how to patch 7.2 (it came up recently, I'm not sure which list, and Tom Lane sent the message in question). BTW, should we back-patch that into 7.2.*? I was resistant to the idea I would appreciate this. I doubt that I it would fix problem with update sometable set a=a+1 where there exist unique index on sometable(a), but it would make postgresql behavoir closer to standard SQL. In my (user) point of view, it is obvoisly bugfix, rather than added feature, so it has right to appear in 7.2.x release. because of concern about lack of testing, but seeing that we've gotten several complaints maybe we should do it anyway. -- Victor Wagner [EMAIL PROTECTED] Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] What's the difference?
Victor Wagner [EMAIL PROTECTED] writes: As far as I understand, following three queries are exactly equivalent: Same results, but the second two constrain the planner's choice of join order. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html Whether this is a feature or a bug depends on context... regards, tom lane I can agree that this is feature if one uses natural or inner joins. But if query semantic needs outer joins there is no way to tell the planner that it is free to choose order of joining. Only thing left is to join with result of subquery, which makes entire query much less readable. I'd think that simpliest way of writing query should result in most freedom for optimizer to choose an execution plan. It is so for inner joins, but not for outer ones. -- Victor Wagner [EMAIL PROTECTED] Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] What's the difference?
As far as I understand, following three queries are exactly equivalent: select item.item_id, item.title, author.email from item, author, item_link where item.item_id=author.item_id and item_link.active=item.item_id and item_link.linktype_id='MODERATES' and item_link.passive='bob_news'; select item.item_id, item.title, author.email from item natural join author, item_link where item.item_id=item_link.active and item_link.linktype_id='MODERATES' and item_link.passive='bob_news'; select item,item_id, item.title, author.email from item inner join author on (item.item_id = author.item_id), item_link where item.item_id=item_link.active and item_link.linktype_id='MODERATES' and item_link.passive='bob_news'; However, first query takes 0.004 seconds to execute with following execution plan: Nested Loop (cost=0.00..17.63 rows=1 width=68) - Nested Loop (cost=0.00..11.67 rows=1 width=37) - Index Scan using active_linked on item_link (cost=0.00..6.01 rows=1 width=10) - Index Scan using item_key on item (cost=0.00..5.65 rows=1 width=27) - Index Scan using author_key on author (cost=0.00..5.95 rows=1 width=31) And second two - about 1.5 seconds with following plan Nested Loop (cost=97.34..10078.92 rows=1 width=68) - Index Scan using active_linked on item_link (cost=0.00..6.01 rows=1 width=10) - Materialize (cost=10025.58..10025.58 rows=3787 width=58) - Hash Join (cost=97.34..10025.58 rows=3787 width=58) - Seq Scan on item (cost=0.00..8250.76 rows=108676 width=27) - Hash (cost=87.87..87.87 rows=3787 width=31) - Seq Scan on author (cost=0.00..87.87 rows=3787 width=31 I've tried to reorder tables in the FROM clause, putting ITEM_LINK first, but it makes no difference. Additional information about database: Cardinality of tables: item: 108941 autor: 3791 item_link: 132031 Primary key of author and item tables consists of one field item_id, and there are no other identically named tables. Primary key of item_link consists of fields active, passive, linktype_id and there are several supplementary indices. Index active_linked was created by following command: create UNIQUE index ACTIVE_LINKED on ITEM_LINK(PASSIVE,LINKTYPE_ID,ACTIVE); All key fields are VARCHAR(20), database created with encoding WIN, but these fields contain ascii characters only. PostgreSQL version 7.2.0. BTW, if I use item left outer join author, execution plan is simular with second one and execution time is about 4 seconds. I feel that it is possible to use execution plan simular with first one, and make outer join queries fly (as it happens in Oracle). -- Victor Wagner [EMAIL PROTECTED] Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] Problem with locale on Solaris 8.0 intel
On the the Solaris 8.0 platform, when database is build using Russian WIN encoding and initdb performed using LANG=ru_RU.ANSI1251, some russian letters for instance small letter e and small letter che according to unicode consortium naming, are considered equal. so I cannot insert into key field of some table russian words for this (ETO) and what(CHTO). Problem doesn't occur on Linux with ru_RU.CP1251 locale Locale itself is correctly written perl script --- use locale; print ((\375\362\356 eq \367\362\356)?1:0,\n); returns 0 with locale ru_RU.ANSI1251 -- Victor Wagner [EMAIL PROTECTED] Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[BUGS] Syntax of stored procedures is not checked
In some languages used for stored procedures in PostgreSQL it is possible to catch some programming errors statically. At least Perl has this property (see -c in perldoc perlrun ) and it seems that PL/PGsql and SQL too allows to parse a function definition and catch errors in it without actual execution It would be be very nice feature if upon CREATE FUNCTION statement these checks would be performed. -- Victor Wagner [EMAIL PROTECTED] Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[BUGS] Invalid outer joins with subquery
Your name :
Your email address :
System Configuration
-
Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) : Solaris 8, Linux
(various versions)
PostgreSQL version (example: PostgreSQL-7.1): PostgreSQL-7.1
Compiler used (example: gcc 2.8.0) : various gcc
Please enter a FULL description of your problem:
I've found that PostgreSQL 7.1 incorrectly handles outer joins
when second table is subquery, which returns constant as one of columns.
Here is an example SQL script which demonstartes the problem
create table foo (
key_fld varchar(20),
value_fld varchar(20));
insert into foo values ('a','a');
insert into foo values ('b','b');
insert into foo values ('c','c');
insert into foo values ('d','d');
create table bar (
key_fld varchar(20),
unused varchar(20));
insert into bar values ('a','true');
insert into bar values ('c','true');
create view baz as
select key_fld, 'true' as flag from bar;
select value_fld,flag from
foo left join (select key_fld, 'true' as flag from bar) a
on foo.key_fld = a.key_fld
;
select value_fld,flag from
foo left join (select key_fld, unused as flag from bar) a
on foo.key_fld = a.key_fld;
select value_fld, flag from
foo left join baz on
foo.key_fld = baz.key_fld;
In my opinion, all three queries should return same result
value_fld | flag
---+--
a | true
b |
c | true
d |
But both queries where constant is used in either subquery or view
definition, return
value_fld | flag
---+--
a | true
b | true
c | true
d | true
In Oracle these queries, (rewirtten according to Oracle outer join syntax)
return same result.
--
Victor Wagner [EMAIL PROTECTED]
Chief Technical Officer Office:7-(095)-748-53-88
Communiware.Net Home: 7-(095)-135-46-61
http://www.communiware.net http://www.ice.ru/~vitus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
