[BUGS] BUG #5019: Nao funciona

[Eduardo]

The following bug has been logged online:

Bug reference:  5019
Logged by:  Eduardo
Email address:  edua...@vip10.com.br
PostgreSQL version: 8.3
Operating system:   Vista 64 bits
Description:Nao funciona
Details: 

Utilizava a versao do Vista 32 bits e o Post gres rodava normalmente.
Comprei um PC novo e ele veio com o Windons Vista 64 bits, e este programa
nao roda mais.
O que faço para resolver meu problema.
Obrigado.

-- 
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 #3323: Wrong charset for the lower and upper functions

[Eduardo Santos]

The following bug has been logged online:

Bug reference:  3323
Logged by:  Eduardo Santos
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system:   Windows
Description:Wrong charset for the lower and upper functions
Details: 

When you have the latin character ç and use the lower or the upper
function, instead of return the approprite character, it returns the oposite
one, so the comparisons in the whole DB are wrong. If you have a word such
as Serviço, and you use the lower function, the result is serviÇo, instead
of serviço. the other side is also true: if you use upper in the same eord,
the result is SERVIçO, instead of SERVIÇO.

select upper('Serviço');
Expected result: SERVIÇO
Actual result: SERVIçO

select lower('Serviço');
Expected result: serviço
Actual result: serviÇo

---(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 #3323: Wrong charset for the lower and upper functions

[Eduardo Ferreira]

Hi,

I haven't seen this issue for windows console, but I don't think it affects
my DB. Anyway, I'm using Unicode (UTF8), and this is the only problem I had.
If it had something to do with the encoding, the results would be very
different (such as the wrong character as result for the function).

My guess is that the order is reversed for the ç only. I'll try to fix the
windows console issue and tell you the results later. Thanks for your help.

2007/5/30, Euler Taveira de Oliveira <[EMAIL PROTECTED]>:


Eduardo Santos wrote:

> When you have the latin character ç and use the lower or the upper
> function, instead of return the approprite character, it returns the
oposite
> one, so the comparisons in the whole DB are wrong. If you have a word
such
> as Serviço, and you use the lower function, the result is serviÇo,
instead
> of serviço. the other side is also true: if you use upper in the same
eord,
> the result is SERVIçO, instead of SERVIÇO.
>
Did you read [1] for issues with code page on Windows console windows?
What kind of encoding are you using? I haven't reproduce your problem
here.

[1] http://www.postgresql.org/docs/8.2/static/app-psql.html

--
  Euler Taveira de Oliveira
  http://www.timbira.com/

[BUGS] BUG #1972: index error with space character

[Eduardo Soares]

The following bug has been logged online:

Bug reference:  1972
Logged by:  Eduardo Soares
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system:   Linux Fedora 4
Description:index error with space character
Details: 

In above example the "AZTES Z" should be after the "AZTESA". It happens with
any encoding type. The DB not see the space character. The index shoulb see
the space and put "AZTES Z" together with "AZTES". In above list the
"AZTESA" should be the first.

Thanks for the help.

table=# insert into edu values ('AZTES Z');
INSERT 133634 1
table=# insert into edu values ('AZTESA');
INSERT 133635 1
table=# SELECT * FROM EDU ORDER BY NOME DESC;
 nome
---
 AZTES Z
 AZTESA
 AZTES
 ÃNTES
 ANTES
(8 registros)

---(end of broadcast)---
TIP 6: explain analyze is your friend

Fwd: [BUGS] BUG #1972: index error with space character

[Eduardo RBS]

-- Forwarded message --
From: Eduardo RBS <[EMAIL PROTECTED]>
Date: 18/10/2005 13:31
Subject: Re: [BUGS] BUG #1972: index error with space character
To: Richard Huxton 


Hellow..
Thank you very much for the attention.
I need a locate that not ignores the space chracater and also sort
accents like á or ã.
I made configuration using locale... see it..

Using C is almost good because it sort correctly and see the spaces..
but it does not sort the portuguese accents.. note the last two
lines..
$ LC_COLLATE=C sort b.txt
aa a
aa z

aaaz
aaz
eado
eza
édina
émaster

and with the "my" locale pt_BR it sort correctly the accents but
ignores the space chracater
LC_COLLATE=pt_BR.utf8 sort b.txt
aa a

aaab
aaaz
aaz
aa z
eado
édina
émaster
eza

What i need should a merge of C and pt_BR.. i mean.. a locale that see
the spaces like C but sort accents like pt_BR..
I tried several others locales.. and only C see the space character.

Thanks for the attention.

--
[]'s
Eduardo RBS
http://linuxstok.sourceforge.net


2005/10/18, Richard Huxton :
> Eduardo Soares wrote:
> > Operating system:   Linux Fedora 4
> > Description:index error with space character
> > Details:
> >
> > In above example the "AZTES Z" should be after the "AZTESA". It happens with
> > any encoding type. The DB not see the space character. The index shoulb see
> > the space and put "AZTES Z" together with "AZTES". In above list the
> > "AZTESA" should be the first.
>
> Sorting order is determined by your locale, and is different from your
> encoding. For example, en_GB ignores spaces but C doesn't:
>
> $ LC_COLLATE=en_GB.UTF-8 sort unsorted.txt
> aa a
> 
> aaab
> aa b
>
> $ LC_COLLATE=C sort unsorted.txt
> aa a
> aa b
> 
> aaab
>
> See "man locale" for details on how to find out what locales are setup
> on your machine. See the documentation for details on how to set locale
> on a database cluster.
>
> HTH
> --
>Richard Huxton
>Archonet Ltd
>


--
[]'s
Eduardo RBS
http://linuxstok.sourceforge.net

---(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

[BUGS] BUG #6207: fali to get lock on parent table after two consecutive updates to the same row in child table

[Eduardo Piombino]

The following bug has been logged online:

Bug reference:  6207
Logged by:  Eduardo Piombino
Email address:  drak...@gmail.com
PostgreSQL version: 9.1.0
Operating system:   Windows XP
Description:fali to get lock on parent table after two consecutive
updates to the same row in child table
Details: 

Hi, I ran across some behavior that I don't know if it is expected or not (I
wouldn't personally have expected pg, IMHO, to behave like that). I've read
the TODOs and the FAQs but was unable to find anything related to this
specific matter.

BUG #4401: concurrent updates to a table blocks one update indefinitely, is
the closest reference I found to my case, but it is not exactly the case
(and it was also disregarded as a bug).

The case is I have two tables.
Then I have two pg sessions, each one working exclusively on each table,
that is, session 1, working only on table a, session 2 working only on table
b.

Everything works fine, I can get a lock on each table on each session,
commit changes, etc.

Problem came when I tried to update the child table a, which holds a fk to
b, after doing two updates to the same record in a. The other session will
fail to obtain the lock after the second update (but won't fail immediately
after the first one).

Tom Lane suggested that it may have something to do to some optimization in
the management of the foreign keys after two updates to the same row in the
child table, but I got no concrete answer as if this behavior is what is
expected from the engine or not.

I'm no expert on the subject, by far less an expert on any details of the
internals of the implementations of the fks optimizations.

But apart from the logical explanation of why this actually happens (cause
it does happen, and I'm almost sure that there will be a reasonable
explanation), I ask you if you really consider this to be the expected
behavior or if it is the way you would like pg to behave.

Following is a test case with which you can very easily reproduce the
issue.
Open two pg sessions to the database.
Statements should be executed in the following order.
-- session T1 means that the following lines should be executed from pg
session 1.
-- session T2 means that the following lines should be executed from pg
session 2.

create table b (
id bigint not null,
x double precision,
constraint pk_b primary key (id));

create table a (
id bigint not null,
id_b bigint,
x double precision,
constraint pk_a primary key (id),
constraint fk_b foreign key (id_b) references b (id));

insert into b (id, x) values (1, 0);
insert into a (id, id_b, x) values (1, 1, 0);

-- session T1
begin transaction;
select * from a where id = 1 for update nowait;
update a set x = x + 1 where id = 1;

(now switch to the second terminal)

-- session T2
begin transaction;
select * from b where id = 1 for update nowait; -- Query returned
successfully: 1 row affected, 47 ms execution time.
rollback;

(now switch back to the first terminal)

-- session T1
rollback;
begin transaction;
select * from a where id = 1 for update nowait;
update a set x = x + 1 where id = 1;
update a set x = x + 1 where id = 1;

(now switch back to the second terminal)

-- session T2
begin transaction;
select * from b where id = 1 for update nowait; -- ERROR: could not obtain
lock on row in relation "b". SQL state: 55P03

(in this case, T2 fails to obtain the lock on b (?))

thanks in advance,
eduardo

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] Move constant evaluation to inside IN subselect

[Cesar Eduardo Barros]

constant IN (SELECT col FROM ...)
and
EXISTS (SELECT col FROM ... WHERE col = constant)
are equivalent. Moving the constant to inside the subquery can make a
big difference.

teste=# create table teste (id integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'teste_pkey' for table 'teste'
CREATE
teste=# explain select 15 in (select id from teste);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0)
  SubPlan
->  Seq Scan on teste  (cost=0.00..20.00 rows=1000 width=4)

EXPLAIN
teste=# explain select exists (select 1 from teste where id = 15);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0)
  InitPlan
->  Index Scan using teste_pkey on teste  (cost=0.00..4.82 rows=1
width=0)

EXPLAIN

The stats from the planer seem to be wrong; the seq scan won't return
the desired row immediately. This might be another bug.

The planner should convert
_non-null constant_ IN (SELECT _non-aggregate_ FROM ... WHERE ...)
into
EXISTS (SELECT 1 FROM ... WHERE (...) AND _non-aggregate_ = _non-null constant_)

-- 
Cesar Eduardo Barros
ElNet Hightech -- Administrador de Sistemas Unix
[EMAIL PROTECTED]



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[BUGS] INSERT .. SELECT should redo SELECT if a duplicate key is found

[Cesar Eduardo Barros]

When you are trying to insert a row in a table with a UNIQUE constraint,
unless it already exists, you can try something like:

INSERT INTO table (id) SELECT val WHERE NOT EXISTS (SELECT 1 FROM table WHERE id = val)

However, this does not work as expected if another backend inserts a row
with the same unique column(s).

Example:

psql 1:
teste=# select version();
version
---
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

teste=# create table teste (id integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'teste_pkey' for table 
'teste'
CREATE
teste=# begin;
BEGIN
teste=# insert into teste select 5 where not exists (select 1 from teste where id = 5);
INSERT 826780 1

psql 2:
teste=# begin;
BEGIN
teste=# insert into teste select 5 where not exists (select 1 from teste where id = 5);
[sits there waiting for the other backend]

psql 1:
teste=# commit;
COMMIT

psql 2:
ERROR:  Cannot insert a duplicate key into unique index teste_pkey
teste=# commit;
COMMIT

The result I expected would be that it would redo the whole query after
the lock is released, instead of just redoing the INSERT part (and not
the SELECT).

Without it, I'm forced to use table locks to avoid the concurrent insert
(which was what I was trying to avoid with the subselect).

Adding FOR UPDATE on the subselect (done by repeating the subselect with
FOR UPDATE as a standalone query before the insert, since subselects
can't have FOR UPDATE) makes no difference (of course, there's no row
for it to lock yet).

The problem is that the INSERT is being split in two atomic pieces
(obtaining the data to insert with a SELECT and inserting it) when it
should be just one atomic piece. Postgres tries to do the right thing by
waiting if another transaction has inserted something with the same
unique values, but it should check if the conditions in the SELECT part
of the query still hold true after the blocking transaction is commited.
If that was done, it would notice that the "not exists (select 1 from
teste where id = 5)" part is now false for that row, and refrain from
inserting it, which is the Right Thing.

The documentation says reevaluating the WHERE condition is done for
UPDATE, DELETE and SELECT FOR UPDATE, but says nothing about INSERT. My
opinion is that INSERT should also reevaluate the WHERE condition.

-- 
Cesar Eduardo Barros
ElNet Hightech -- Administrador de Sistemas Unix
[EMAIL PROTECTED]



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [BUGS] INSERT .. SELECT should redo SELECT if a duplicate key is found

[Cesar Eduardo Barros]

On Sat, Jul 06, 2002 at 12:18:56PM -0300,  wrote:
> 
> When you are trying to insert a row in a table with a UNIQUE constraint,
> unless it already exists, you can try something like:
> 
> INSERT INTO table (id) SELECT val WHERE NOT EXISTS (SELECT 1 FROM table WHERE id = 
>val)
> 
> However, this does not work as expected if another backend inserts a row
> with the same unique column(s).
> 

The same thing also happens with DELETE:

psql 1:
teste=# create table teste (id integer primary key, parent integer references teste 
(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'teste_pkey' for table 
'teste'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
teste=# insert into teste values (1, null);
INSERT 826882 1
teste=# begin;
BEGIN

psql 2:
teste=# begin;
BEGIN
teste=# select 1 from teste where id = 1 for update;
 ?column?
--
1
(1 row)

psql 1:
teste=# delete from teste where not exists (select 1 from teste where parent = 1);
[sits there waiting]

psql 2:
teste=# insert into teste values (2,1);
INSERT 826884 1
teste=# commit;
COMMIT

psql 1:
ERROR:   referential integrity violation - key in teste still referenced from 
teste
teste=# commit;
COMMIT


So, the problem is probably with the subselects. I wonder if UPDATE has
the same problem.

-- 
Cesar Eduardo Barros
ElNet Hightech -- Administrador de Sistemas Unix
[EMAIL PROTECTED]



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[BUGS] Query Tools

[Eduardo Velasquez M]

En Pgadmin III el Query Tools no esta funcionando. Se bloquea. Que hay que 
hacer para poder habilitarlo.


Eduardo Velasquez M

[BUGS] BUG #6030: Query Tools bloqueado

[Eduardo Velasquez M]

The following bug has been logged online:

Bug reference:  6030
Logged by:  Eduardo Velasquez M
Email address:  lev...@hotmail.com
PostgreSQL version: 9.0.3.1
Operating system:   windows 7
Description:Query Tools bloqueado
Details: 

Esta opcion se me bloqueo en PgAdmin III.
Aun a pesar que se desinstala postgres e instala nuevamente.
El programa se queda pegado y no responde.
Que solución se ha encontrado?

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs