Re: [HACKERS] [BUGS] Invalid YAML output from EXPLAIN

[Florian Weimer]

* Greg Smith:

> Florian Weimer wrote:
>> It has been claimed before that YAML is a superset of JSON, so why
>> can't the YAML folks use the existing JSON output instead?
>>   
>
> Because JSON just crosses the line where it feels like there's so much
> markup that people expect a tool is necessary to read it, which has
> always been the issue with XML too--bad human readability.

But YAML is not human-readable.  There are human-readable subsets of
it, but the general serializers do not produce them, and specific
serializers are difficult to get right (as we've seen).

> EXPLAIN (FORMAT YAML) SELECT * FROM customers WHERE customerid>1000
> ORDER BY zip;
> QUERY PLAN
> -
> - Plan:+
> Node Type: Sort+
> Startup Cost: 4449.30  +
> Total Cost: 4496.80+
> Plan Rows: 19000   +
> Plan Width: 268+
> Sort Key:  +
>   - zip+
> Plans: +
>   - Node Type: Seq Scan+
> Parent Relationship: Outer +
> Relation Name: customers   +
> Alias: customers   +
> Startup Cost: 0.00 +
> Total Cost: 726.00 +
> Plan Rows: 19000   +
> Plan Width: 268+
> Filter: (customerid > 1000)

What does your parser do with this (equivalent but shorter) YAML
output?

- Plan: !!map
&0 Node Type: Sort
&1 Startup Cost: 4449.30
&2 Total Cost: 4496.80
&3 Plan Rows: &5 19000
&4 Plan Width: &6 268
Sort Key: ["zip"]
Plans: !!seq
  - *0: Seq Scan
Parent Relationship: Outer
Relation Name: &7 customers
Alias: *7
*1: 0.00
*2: 726.00
*3: *5
*4: *6
Filter: (customerid > 1000)

Looking at the spec, it's rather difficult to come up with a readable
subset which can parsed easily and is general in the sense that it can
express empty strings, strings with embedded newlines, and so on.
YAML's rules for dealing with whitespace are fairly complex, but are
probably needed to get a more compact notation than JSON.

-- 
Florian Weimer
BFK edv-consulting GmbH   http://www.bfk.de/
Kriegsstraße 100  tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

-- 
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 #5493: Character Encoding is not happenning properly

[Bakkiya]

The following bug has been logged online:

Bug reference:  5493
Logged by:  Bakkiya
Email address:  bakk...@gmail.com
PostgreSQL version: 8.3.8
Operating system:   Suse Linux 10
Description:Character Encoding is not happenning properly
Details: 

Hi,
We have postgresql 8.3.8 version on 32 bit and 64 bit SLES servers. On both
the servers we are having the client encoding as UNICODE
show CLIENT_ENCODING --- "unicode"
We have created the database with UTF8 encoding. 
But when we give uppoer/lower of any extended characters, it is behaving
differently.
Example:
32 Bit m/c:
select upper('ê') -- "Ê"
64 Bit m/c:
select upper('ê') -- "ê"

Don't think it as an issue with OS. Can you plese help us in solving this.

Thanks.

-- 
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 #5494: pg_dump dependiences sequence problem

[Ovidiu Tanasiciuc]

The following bug has been logged online:

Bug reference:  5494
Logged by:  Ovidiu Tanasiciuc
Email address:  tanasic...@yahoo.com
PostgreSQL version: 8.4.1
Operating system:   Windows XP
Description:pg_dump dependiences sequence problem
Details: 

I have 2 tables a and b
a have column id - bigserial
b have column Id - bigserial with I uppercase
both automatic have 2 sequences
the problem is then i make pg_dump with data only and insert command,even
like or from pgadmin
E:/Programe/PostgreSQL/8.4/bin\pg_dump.exe --host servercnp --port 5432
--username postgres --format plain --data-only --column-inserts --verbose
--file "C:\Users\ovidiu\Desktop\x.sql" --table public.a secretariat

the table a.sql file have the line
SELECT pg_catalog.setval('a_id_seq', 4392, false);
but the tabel b doesn't have this line.

this is a bug because it's mean tha pg_depend doesn't read ok dependiences
for columns with uppercase character.  

my problem is that i like to make a backup without command set sequences,but
i can't find any parameters for this.

Anyway, even if is corect to make backup with set seuences includes, please
make to work and for columns uppercases.

-- 
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 #5493: Character Encoding is not happenning properly

[Tom Lane]

"Bakkiya"  writes:
> We have postgresql 8.3.8 version on 32 bit and 64 bit SLES servers. On both
> the servers we are having the client encoding as UNICODE
> show CLIENT_ENCODING --- "unicode"
> We have created the database with UTF8 encoding. 
> But when we give uppoer/lower of any extended characters, it is behaving
> differently.

Sounds like you have different locale settings in the two databases.

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 #5494: pg_dump dependiences sequence problem

[Tom Lane]

"Ovidiu Tanasiciuc"  writes:
> I have 2 tables a and b
> a have column id - bigserial
> b have column Id - bigserial with I uppercase
> both automatic have 2 sequences
> the problem is then i make pg_dump with data only and insert command,even
> like or from pgadmin
> E:/Programe/PostgreSQL/8.4/bin\pg_dump.exe --host servercnp --port 5432
> --username postgres --format plain --data-only --column-inserts --verbose
> --file "C:\Users\ovidiu\Desktop\x.sql" --table public.a secretariat

> the table a.sql file have the line
> SELECT pg_catalog.setval('a_id_seq', 4392, false);
> but the tabel b doesn't have this line.

Are you looking carefully?  It would actually look like

SELECT pg_catalog.setval('"b_Id_seq"', something, false);

Another likely theory is that you didn't actually make the column "Id"
as a serial, but tried to attach a sequence to it manually.  In that
case I'd guess you forgot the ALTER SEQUENCE OWNED BY step.

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] Invalid YAML output from EXPLAIN

[Robert Haas]

On Mon, Jun 7, 2010 at 4:14 AM, Dean Rasheed  wrote:
> Testing 9.0 beta, I found that EXPLAINing certain queries in YAML
> format will produce invalid YAML, for example:
>
> explain (format yaml) select * from foo where str_val = 'a: b';
>
> The problem in this case is that a colon followed by whitespace is not
> allowed in an unquoted plain YAML string because a parser would
> interpret it as the start of a map.
>
> So the current code in escape_yaml() is inadequate for producing valid
> YAML. I think it would have to also consider at least the following
> characters as special "-"  ":"  "["  "]"  "{"  "}"  ","  "\""  "'"
> "|"  "*"  "&". Technically, it would also need to trap empty strings,
> and strings with leading or trailing whitespace.
>
> Making escape_yaml() completely bulletproof with this approach would
> be quite difficult, and (IMO) not worth the effort, especially given
> that an important requirement is that the output be machine readable,
> and in my experience YAML parsers are often far from perfect.
>
> I would therefore argue for simply calling escape_json() to produce
> double quoted output for all string values, and only have numeric
> values unquoted. This is not really any less human readable, and is
> far more machine readable.
>
> Patch attached.

I've committed a patch which I think will address this issue without
uglifying the output quite so much.  Also, I didn't like the idea of
not applying escaping to both the keys and values, even though we
think we'll never have a key that requires escaping.  With this
approach, that change isn't needed.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Invalid YAML output from EXPLAIN

[Robert Haas]

On Tue, Jun 8, 2010 at 10:47 PM, Robert Haas  wrote:
> On Mon, Jun 7, 2010 at 4:14 AM, Dean Rasheed  wrote:
>> Testing 9.0 beta, I found that EXPLAINing certain queries in YAML
>> format will produce invalid YAML, for example:
>>
>> explain (format yaml) select * from foo where str_val = 'a: b';
>>
>> The problem in this case is that a colon followed by whitespace is not
>> allowed in an unquoted plain YAML string because a parser would
>> interpret it as the start of a map.
>>
>> So the current code in escape_yaml() is inadequate for producing valid
>> YAML. I think it would have to also consider at least the following
>> characters as special "-"  ":"  "["  "]"  "{"  "}"  ","  "\""  "'"
>> "|"  "*"  "&". Technically, it would also need to trap empty strings,
>> and strings with leading or trailing whitespace.
>>
>> Making escape_yaml() completely bulletproof with this approach would
>> be quite difficult, and (IMO) not worth the effort, especially given
>> that an important requirement is that the output be machine readable,
>> and in my experience YAML parsers are often far from perfect.
>>
>> I would therefore argue for simply calling escape_json() to produce
>> double quoted output for all string values, and only have numeric
>> values unquoted. This is not really any less human readable, and is
>> far more machine readable.
>>
>> Patch attached.
>
> I've committed a patch which I think will address this issue without
> uglifying the output quite so much.  Also, I didn't like the idea of
> not applying escaping to both the keys and values, even though we
> think we'll never have a key that requires escaping.  With this
> approach, that change isn't needed.

Er, I should also say, thanks for the report, and please test.  I am
definitely not an expert on YAML.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] BUG #5492: Query performs slowly and sequence corrupted

[Robert Haas]

On Mon, Jun 7, 2010 at 5:33 PM, Greg Stark  wrote:
> On Mon, Jun 7, 2010 at 5:36 PM, Piergiorgio Buongiovanni
>  wrote:
>> I reused the previous command to re-set the sequence value to the right one,
>> but I see that the START value is now 59100. I reused the previous command
>> another time and the START value is now 30440.
>>
>> I think this is a bug. I have a lot of problems with this sequence.
>
> Sequences wouldn't directly affect retrieval times. But one way you
> could get both of these symptoms is by having an application which
> inserts many rows but aborts and rolls back the inserts without
> committing. Perhaps a large copy which is interrupted. That would fill
> the table with garbage dead records which could slow down retrieval
> depending on the access method and also increase the sequence value.

If this is what happened, CLUSTER on the table might be enough to fix
the problem.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] BUG #5475: Problem during Instalation

[Robert Haas]

On Wed, May 26, 2010 at 9:48 AM, Joel Henrique  wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5475
> Logged by:          Joel Henrique
> Email address:      j...@cefet-al.br
> PostgreSQL version: 8.4.4-1
> Operating system:   Windows 2003 Server
> Description:        Problem during Instalation
> Details:
>
> When I try to install postgres it asks for a password.
> It says that if the service already exists I should put the current
> password, otherwise a service will be created with new password.
>
> I've neves installed postgres before. What kind of password is that? I can't
> install postgres here.

I feel like we've had this question a few times before, and answered
it, but I'm not a Windows guy and can't remember the answer.  Can we
add an FAQ entry for this, or something?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] BUG #5473: problema para reinstalar postgresql

[Robert Haas]

2010/5/25 carolina :
>
> The following bug has been logged online:
>
> Bug reference:      5473
> Logged by:          carolina
> Email address:      caro.herrer...@hotmail.com
> PostgreSQL version: postgresql 8.4
> Operating system:   windows xp
> Description:        problema para reinstalar postgresql
> Details:
>
> Hola..tengo el siquiente problema, por razones tecnicas tuve que desinstalar
> postgresql de mi maquina, al final de la desinstalacion me envio un mensaje
> donde me decia que ni el usuario ni la base se habian eliminado. Cuando
> Quise volver a instalarlo, se instalo pero al tratar de ejecutarlo me sale
> el siguiente error: Server doesn't listen
> The server doesn't accept connections: the connection library reports
>
> could not connect to server: Connection refused (0x274D/10061) Is the
> server running on host "127.0.0.1" and accepting TCP/IP connections on port
>
> 5432?
>
> Por lo que me fui a archivos de programa y borre una carpeta que estaba de
> nombre postgresql y tambien borre el usuario postgres, sin embargo nada
> funciona, solo se instala el pgadminIII pero no puedo crear una base de
> datos porque cada vez q lo intento me sale el mismo error.
>
> Tambien entre por modo a prueba de fallos y busque todo lo que tuviera
> nombre postgres y lo elimine y volvi a instalar pero nada me funciona..que
> puedo hacer??

Por favor pregunta aqui: http://archives.postgresql.org/pgsql-es-ayuda/

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

[BUGS] Re: [BUGS] Reinicialização - Pg_Listener

[Robert Haas]

2010/5/26 MarceloG :
> Olá pessoal,
> no meu aplicativo, ao invés de tabelas temporárias, utilizo o pg_listener
> para verificar e impedir que o mesmo usuário acesse o sistema
> simultaneamente.
> Eu disparo um "notify usuario+código" e, a cada acesso, leio a tabela/
> catálogo pg_listener para saber se o usuário está ou não logado.
> Assim, quando a conexão for encerrada, todos os "notify" da conexão são
> automaticamente deletados.
> Esse procedimento vai ser modificado.
> Todavia, recentemente, em virtude de queda de energia, o servidor deixou de
> funcionar de forma anormal.
> Assim, os registros na tabela/ catálogo pg_listener foram mantidos,
> obrigando um procedimento manual de deleção dos registros então existentes.
> Assim, acho que em todas as inicializações do servidor, a tabela/ catálogo
> pg_listener deve ser "limpa" ou seja, deletados todos os registros
> existentes.

This is an English-language mailing list, but you could try here:

https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Bad optimizer data for xml (WAS: xml data type implications of no =)

[Robert Haas]

On Thu, Jun 3, 2010 at 7:16 PM, Mark Kirkwood
 wrote:
> Maybe I gave this guy a bad title - is it a concern that the 'width'
> estimate is so far off for xml datatypes (because of no = op)? It seemed to
> me that this could result in some bad plan choices (e.g in subqueries etc).

It's possible.  I don't really see a reason not to add an = operator
for XML - does anyone else?

It would need to be done by updating src/include/catalog/pg_*.h,
rather than via SQL, of course.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] Bad optimizer data for xml (WAS: xml data type implications of no =)

[Mark Kirkwood]

On 09/06/10 15:22, Robert Haas wrote:

On Thu, Jun 3, 2010 at 7:16 PM, Mark Kirkwood
  wrote:
   

Maybe I gave this guy a bad title - is it a concern that the 'width'
estimate is so far off for xml datatypes (because of no = op)? It seemed to
me that this could result in some bad plan choices (e.g in subqueries etc).
 

It's possible.  I don't really see a reason not to add an = operator
for XML - does anyone else?

It would need to be done by updating src/include/catalog/pg_*.h,
rather than via SQL, of course.

   


Heh, sure should - I merely included the SQL stuff in case anyone else 
wanted to reproduce what I was seeing!


Cheers

Mark

--
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 #5475: Problem during Instalation

[Craig Ringer]

On 9/06/2010 11:06 AM, Robert Haas wrote:

On Wed, May 26, 2010 at 9:48 AM, Joel Henrique  wrote:


The following bug has been logged online:

Bug reference:  5475
Logged by:  Joel Henrique
Email address:  j...@cefet-al.br
PostgreSQL version: 8.4.4-1
Operating system:   Windows 2003 Server
Description:Problem during Instalation
Details:

When I try to install postgres it asks for a password.
It says that if the service already exists I should put the current
password, otherwise a service will be created with new password.

I've neves installed postgres before. What kind of password is that? I can't
install postgres here.


I feel like we've had this question a few times before, and answered
it, but I'm not a Windows guy and can't remember the answer.  Can we
add an FAQ entry for this, or something?


Really, the installer on Windows needs to stash the password in an 
admin-only-readable registry key, read it from there on install, and 
test to make sure it works. If it does, Pg need not even bother the user 
with the account password at all.


It'd be worth looking at how (eg) ASP.NET and IIS handle their account 
passwords. They don't seem to have these issues.


Perhaps a FAQ entry would be a good idea in the mean time, though. Let 
me see if I can put something together.


--
Craig Ringer

--
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] Bad optimizer data for xml (WAS: xml data type implications of no =)

[Tom Lane]

Robert Haas  writes:
> It's possible.  I don't really see a reason not to add an = operator
> for XML - does anyone else?

Yes, that was considered and rejected, IIRC.  What is your definition
of equality for xml?

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 #5475: Problem during Instalation

[Sachin Srivastava]

On 5/26/10 7:18 PM, Joel Henrique wrote:

The following bug has been logged online:

Bug reference:  5475
Logged by:  Joel Henrique
Email address:  j...@cefet-al.br
PostgreSQL version: 8.4.4-1
Operating system:   Windows 2003 Server
Description:Problem during Instalation
Details:

When I try to install postgres it asks for a password.
It says that if the service already exists I should put the current
password, otherwise a service will be created with new password.

I've neves installed postgres before. What kind of password is that? I can't
install postgres here.
   
Since you dont have a 'postgres' user, Just give any password and the a 
new user 'postgres' will be created with the supplied password which 
then will be used as the service account owner of PostgreSQL service.


In case you have a postgres user pre-existing on your system, supply its 
password when asked by the installer.


You can anytime change the password by going to administartive tasks--> 
Users and groups ..

Thanks!

   



--
Regards,
Sachin Srivastava
EnterpriseDB , the Enterprise Postgres 
 company.

Re: [BUGS] Bad optimizer data for xml (WAS: xml data type implications of no =)

[Mark Kirkwood]

On 09/06/10 17:14, Tom Lane wrote:

Robert Haas  writes:
   

It's possible.  I don't really see a reason not to add an = operator
for XML - does anyone else?
 

Yes, that was considered and rejected, IIRC.  What is your definition
of equality for xml?
   


Yes - but in that previous discussion the optimizer (lack of) 
information was not considered (or known I suspect), so maybe a rethink 
is worthwhile?


It seems that the nub of this issue is that there are conceptually two 
types of =, one for datatype specific comparison, and one for optimizer 
statistical information calculation. However the system allows only the 
first, so if you don't (or can't) have one then you lose some possibly 
important optimization data.


regards

Mark

--
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] Invalid YAML output from EXPLAIN

[Dean Rasheed]

On 9 June 2010 03:48, Robert Haas  wrote:
> Er, I should also say, thanks for the report, and please test.  I am
> definitely not an expert on YAML.
>

I'm not an expert on YAML either, but I don't think this works (at
least it breaks against the online YAML parser here:
http://yaml-online-parser.appspot.com/). If the string starts with a
".", then it tries to treat it as a floating point number and baulks
if the rest of the string isn't a valid number.

Actually that gives me another argument for quoting *all* string
values: if a string value happens to be a valid number and we output
it unquoted, then parsers will treat is a number, forcing the user to
cast it to a string in their code. This is likely to lead to bugs in
user code, where things initially appear to work, then unexpectedly
start failing in pathalogical cases.

Personally, I'd also not try to escape keys at all. If in the future
we add a new key, then we'd be forced to choose between quoting it, or
more likely choosing a different key that doesn't require quoting,
which is what most people do in my (admittedly limited) experience.

In short, despite everything that's been said on this thread, I still
prefer my patch - but then I suppose I would say that :-)

Cheers,
Dean

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