Re: [BUGS] BUG #5475: Problem during Instalation

[Dave Page]

On Wed, Jun 9, 2010 at 4:58 AM, Craig Ringer
 wrote:

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

Aside from the fact that such a technique would probably end up on
Bugtraq quicker than I could write the report myself, many people do
need the password for setting up additional services such as pgAgent,
and for actually logging into the database they just installed.

-- 
Dave Page
EnterpriseDB UK: 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

[Dean Rasheed]

On 9 June 2010 07:58, Dean Rasheed  wrote:
> I prefer my patch - but then I suppose I would say that :-)
>

Seriously though, I can think of a number of good arguments in favour
of the "quote all string values unconditionally" approach:

- It's the simplest, least obtrusive fix to our code.
- It's the least likely to have bugs in our code.
- It's the least likely to lead to bugs in client code.
- It gives clean, consistent output (IMO).
- It's the easiest to machine parse (marginally).
- Strings are always strings.
- It's still perfectly human readable.
- Re arguments about mailers chopping it up, it's easier to piece
together again if you know all long lines end with a double quote.

I love Florian's example, because it illustrates that there are all
sorts of YAML output that we *could* produce that would be technically
equivalent. However, I think this is a case of the simplest solution
being the best.

Regards,
Dean

-- 
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 09/06/10 15:47, Dave Page wrote:
> On Wed, Jun 9, 2010 at 4:58 AM, Craig Ringer
>  wrote:
> 
>> 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.
> 
> Aside from the fact that such a technique would probably end up on
> Bugtraq quicker than I could write the report myself, many people do
> need the password for setting up additional services such as pgAgent,
> and for actually logging into the database they just installed.

Only because the PostgreSQL system user account password is coupled to
the account of the "postgres" user in the PostgreSQL database cluster
(right?). I'm not at a Windows box right now so I can't test to see if
altering the Pg role's password changes the system password or vice
versa, but I'd be surprised if they did.

Personally I'm firmly of the opinion that the user should never need to
know anything about the password (if any) for the "postgres" Windows
user account that's used for the service account.

As for bugtraq: If the password is in a registry key readable only by
the administrator user, then anyone who can read the password can also
change the password for the account, read other critical passwords from
the system, etc. Admittedly it'd be stored in cleartext, but so are
plenty of stored passwords. Now, if that password is the same as the one
used for the db admin user, then yes that'd be an absolutely awful idea,
but I was presuming that as it'd be a behind-the-scenes generated
password it'd be completely independent from the "postgres" role in the
db cluster if this method was used.

It'd be even better, of course, to find out how others avoid this whole
issue and do the same. I'm going to do some digging and see if I can
find that out, so I can give you some useful information instead of
hand-waving.

-- 
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

-- 
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 #5495: RI/FK on self and inherited table

[Martin Edlman]

The following bug has been logged online:

Bug reference:  5495
Logged by:  Martin Edlman
Email address:  edl...@fortech.cz
PostgreSQL version: 8.4.4
Operating system:   Scientific Linux 5.5 (RHEL)
Description:RI/FK on self and inherited table
Details: 

I have a table

net.device(id serial, parent_id integer null, name varchar(100), ip_address
inet, ...)
device_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES net.device(id) ON
UPDATE CASCADE

and a table

net.computer(parent_id integer not null, ...) inherits (net.device);

I have a script which inserts records to both tables and it always stops on
the same record for net.computer with followin message

ERROR:  insert or update on table "device" violates foreign key constraint
"device_parent_id_fkey"
DETAIL:  Key (parent_id)=(19947) is not present in table "device".

But the record is there, it was inserted into net.computer so it's
selectable from net.device and from net.computer.

select id,parent_id,name,ip_address from net.device where id = 19947;
  id   | parent_id |  name   | ip_address 
---+---+-+
 19947 |  1649 | pc-lit-customer | 213.213.213.213/30

select id,parent_id,name,ip_address from net.computer where id = 19947;
  id   | parent_id |  name   | ip_address 
---+---+-+
 19947 |  1649 | pc-lit-digistyl | 213.250.201.222/30

There are other records in the net.computer which are referenced in other
records as the parent_id.

It really puzzles me.

If you want I can send you full definition of the net.device and the
net.computer including indices, triggers etc.

I have inserted 1795 records to net.device

select count(*) from only net.device;
 count 
---
  1795

and 5105 records to net.computer

select count(*) from only net.computer;
 count 
---
  5105

That's 6900 records in total

select count(*) from net.device;
 count 
---
  6900

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

[Dave Page]

On Wed, Jun 9, 2010 at 8:56 AM, Craig Ringer
 wrote:

> Only because the PostgreSQL system user account password is coupled to
> the account of the "postgres" user in the PostgreSQL database cluster
> (right?). I'm not at a Windows box right now so I can't test to see if
> altering the Pg role's password changes the system password or vice
> versa, but I'd be surprised if they did.

It won't, but the point is that we have to ask for a password anyway
so we don't gain anything by not asking the user for one of them.

> Personally I'm firmly of the opinion that the user should never need to
> know anything about the password (if any) for the "postgres" Windows
> user account that's used for the service account.

So how would you install something like pgAgent, which you would most
likely want to run under the same account?

> As for bugtraq: If the password is in a registry key readable only by
> the administrator user, then anyone who can read the password can also
> change the password for the account, read other critical passwords from
> the system, etc.

You can have multiple administrators on a machine, and storage of a
plain text password in the registry would allow knowledge of that
password to leak from one administrator to another, which may cause
security concerns in a tightly controlled environment. As far as I'm
aware, Windows doesn't provide any way for an Administrator to read
any other local/domain passwords in anything other than an
encrypted/hashed form, so this would be a new issue, not normally
seen.

> It'd be even better, of course, to find out how others avoid this whole
> issue and do the same. I'm going to do some digging and see if I can
> find that out, so I can give you some useful information instead of
> hand-waving.

Most other services use one of the 'special' accounts like 'Network
Service', however doing that with Postgres doesn't necessarily play
well with features like COPY, which is why we've avoiding doing that
since 8.0.


-- 
Dave Page
EnterpriseDB UK: 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

[Craig Ringer]

On 09/06/10 16:29, Dave Page wrote:
> On Wed, Jun 9, 2010 at 8:56 AM, Craig Ringer
>  wrote:
> 
>> Only because the PostgreSQL system user account password is coupled to
>> the account of the "postgres" user in the PostgreSQL database cluster
>> (right?). I'm not at a Windows box right now so I can't test to see if
>> altering the Pg role's password changes the system password or vice
>> versa, but I'd be surprised if they did.
> 
> It won't, but the point is that we have to ask for a password anyway
> so we don't gain anything by not asking the user for one of them.

The issue is that the user has to know if they've installed Pg before
(and thus already have a "postgres" service account) or not. Some
clearly don't, or have forgotten the service account password long ago.


In general, users expect that an uninstall of an app will remove the
app's configuration. Pg ... sort of ... does. It leaves the user account
in place, though it created it in the first place. So the user has to
know if Pg has been installed previously and if so what the service
account password was. This demonstrably confuses people who try to
uninstall and reinstall Pg to resolve issues (service account
configuration related or other).

As you've pointed out good reasons why just storing a generated password
may not be a good idea, let me propose another approach that might help
users with what's clearly a confusing point for many:

- During the install, test for the existence of a 'postgres' user
account before displaying the password prompt panel of the installer
"wizard".

- If such an account does not exist, display the existing UI password
prompt ui with simplified language indicating that you're creating a new
password not entering one already set. People seem to struggle with this
at present.

- If such an account already exists, tell the user that PostgreSQL has
been installed in the past or another version is currently installed, so
they must provide the password that was supplied during that prior
installation. If they do not know the password, they may press an
offered "reset password" button to enter a new one, but they're warned
that doing so will cause any other versions of PostgreSQL to stop working.

-- or possibly . 

- When the postgres user password is reset on the PostgreSQL service via
the Pg installer, update or offer to update *all* service registrations
for any service using the postgres account so that they use the same
password, thus avoiding breaking old versions when the user has
forgotten the service account password.

>> Personally I'm firmly of the opinion that the user should never need
>> to know anything about the password (if any) for the "postgres"
>> Windows user account that's used for the service account.
>
> So how would you install something like pgAgent, which you would most
> likely want to run under the same account?

Installers run as admin; it'd read the registry key during installation
and use it for the CreateService call.

> You can have multiple administrators on a machine, and storage of a
> plain text password in the registry would allow knowledge of that
> password to leak from one administrator to another, which may cause
> security concerns in a tightly controlled environment. As far as I'm
> aware, Windows doesn't provide any way for an Administrator to read
> any other local/domain passwords in anything other than an
> encrypted/hashed form, so this would be a new issue, not normally
> seen.

True, and a good point, particularly on domain setups.

OTOH, if it's a generated password, leaking it has little effect as it's
unique to the machine and only grants access to a service account that
the admin user already has access to by virtue of their admin status on
that machine. It's not re-used anywhere and if you can read it you can
change it or become that user.

Nonetheless, I'll grant that I understand and agree with your caution
about that notion. I don't like it much either, I just don't like the
way things work right now any more, as the questions and posts here
suggest that it causes plenty of confusion for users.

> Most other services use one of the 'special' accounts like 'Network
> Service', however doing that with Postgres doesn't necessarily play
> well with features like COPY, which is why we've avoiding doing that
> since 8.0.

That much makes perfect sense and I certainly wasn't arguing that it
should live in the generic service account. There are clear and good
reasons to run Pg in its own service account, well beyond merely
separating it from other services.

A bit of digging reveals that most decent services are using local or
domain accounts, with passwords, and are just living with the
administrator hassle this creates, expecting admins to be competent
enough to change and maintain service passwords. I'm not sure Pg can
rely on that for desktop installs, though, so it might need to offer a
way to take service account password management into its 

Re: [BUGS] BUG #5475: Problem during Instalation

[Sachin Srivastava]

On 6/9/10 2:33 PM, Craig Ringer wrote:

On 09/06/10 16:29, Dave Page wrote:
   

On Wed, Jun 9, 2010 at 8:56 AM, Craig Ringer
  wrote:

 

Only because the PostgreSQL system user account password is coupled to
the account of the "postgres" user in the PostgreSQL database cluster
(right?). I'm not at a Windows box right now so I can't test to see if
altering the Pg role's password changes the system password or vice
versa, but I'd be surprised if they did.
   

It won't, but the point is that we have to ask for a password anyway
so we don't gain anything by not asking the user for one of them.
 

The issue is that the user has to know if they've installed Pg before
(and thus already have a "postgres" service account) or not. Some
clearly don't, or have forgotten the service account password long ago.


In general, users expect that an uninstall of an app will remove the
app's configuration. Pg ... sort of ... does. It leaves the user account
in place, though it created it in the first place. So the user has to
know if Pg has been installed previously and if so what the service
account password was. This demonstrably confuses people who try to
uninstall and reinstall Pg to resolve issues (service account
configuration related or other).
   
If a user has admin rights, he can anytime change the password for an 
existing 'postgres' user account in case he doesn't remember the 
password anymore.


That said, i believe the text/prompt that asks for password in the 
installer is clear enough to avoid any confusion. Cant say people 
actually read all that though.




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

Re: [BUGS] BUG #5475: Problem during Instalation

[Craig Ringer]

On 09/06/10 17:26, Sachin Srivastava wrote:

> If a user has admin rights, he can anytime change the password for an
> existing 'postgres' user account in case he doesn't remember the
> password anymore.

... but people clearly don't know how, given the frequency of questions
about this on the lists. I'm sure the majority do, but enough don't that
I'm trying to suggest possible usability improvements for the less
expert members of the user base.

Even some help text pointing them to Control Panel -> Administrative
Tools -> Computer Management, then Local Users and Groups would help.

Of course, if they change the service account password that way they'll
break any existing services using the account. They might also think
they're changing the "postgres" cluster user password instead, and get
most thoroughly unexpected results when they try to log in next time...

> That said, i believe the text/prompt that asks for password in the
> installer is clear enough to avoid any confusion. Cant say people
> actually read all that though.

I don't think it's too by myself, but the number of people asking for
help on the lists suggests that it's confusing to some. An enhancement
in the installer could remove the need for that.

It's nothing more than a suggestion, I just hope you'll consider
examining this part of the process in light of some of the questions
that turn up on -bugs and -general about this.

-- 
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.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 #5475: Problem during Instalation

[Dave Page]

On Wed, Jun 9, 2010 at 10:03 AM, Craig Ringer
 wrote:
> - During the install, test for the existence of a 'postgres' user
> account before displaying the password prompt panel of the installer
> "wizard".
>
> - If such an account does not exist, display the existing UI password
> prompt ui with simplified language indicating that you're creating a new
> password not entering one already set. People seem to struggle with this
> at present.
>
> - If such an account already exists, tell the user that PostgreSQL has
> been installed in the past or another version is currently installed, so
> they must provide the password that was supplied during that prior
> installation. If they do not know the password, they may press an
> offered "reset password" button to enter a new one, but they're warned
> that doing so will cause any other versions of PostgreSQL to stop working.

The current message is:

Please provide a password for the database superuser (${superaccount})
and service account (${serviceaccount}). If the service account
already exists in Windows, you must enter the current password for the
account. If the account does not exist, it will be created when you
click 'Next'.

Or, in upgrade mode:

Please provide a password for service account (${serviceaccount}).

There is a point beyond which further simplification of text really
doesn't help (because people just aren't reading it in the first
place). Personally, I think we're pretty darn close to that point, but
I'm open to better phrasing.

I'm unconvinced that the extra complexity involved in checking the
existence of the service account to tweak the message is worthwhile. I
don't think it buys us extra simplicity that would suddenly make
people understand.

> -- or possibly . 
>
> - When the postgres user password is reset on the PostgreSQL service via
> the Pg installer, update or offer to update *all* service registrations
> for any service using the postgres account so that they use the same
> password, thus avoiding breaking old versions when the user has
> forgotten the service account password.

That would break any of them that have implemented anything like your
previous suggestion (with the password stored in the registry). Plus,
an installation of PostgreSQL should never modify any other software
that may be installed, imho.

A hint, or button to fire up the computer management MMC might be
feasible, but I'm not entirely sure. I've seen these sort of bug
report from people who have never ventured into the stuff in the
Administrative Tools folder, so even guiding them there may not help.

>> So how would you install something like pgAgent, which you would most
>> likely want to run under the same account?
>
> Installers run as admin; it'd read the registry key during installation
> and use it for the CreateService call.

That might work for one of our installers, but not if someone installs
manually, or using a 3rd party package.

> OTOH, if it's a generated password, leaking it has little effect as it's
> unique to the machine and only grants access to a service account that
> the admin user already has access to by virtue of their admin status on
> that machine.

Which breaks any audit trail because you have no way of knowing which
admin may login as the service user.

-- 
Dave Page
EnterpriseDB UK: 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 Wed, Jun 9, 2010 at 2:58 AM, Dean Rasheed  wrote:
> 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.

Really?  I enter:

- foo
- bar
- .baz

And it produces this JSON:

[
  "foo",
  "bar",
  ".baz"
]

That looks OK to me.

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

[Dean Rasheed]

On 9 June 2010 12:07, Robert Haas  wrote:
> On Wed, Jun 9, 2010 at 2:58 AM, Dean Rasheed  wrote:
>> 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.
>
> Really?  I enter:
>
> - foo
> - bar
> - .baz
>
> And it produces this JSON:
>
> [
>  "foo",
>  "bar",
>  ".baz"
> ]
>
> That looks OK to me.
>

Ah, OK I didn't test those cases properly before composing my email.
It's actually only a "." on its own that it can't parse.

- just: write some
- yaml:
  - .

ERROR:

invalid literal for float(): .

I'm not sure if that's valid YAML or not.

My comment about numbers still applies though. The following are
different values:

- just: write some
- yaml:
  - 123
  - "123"

[
  {
"just": "write some"
  },
  {
"yaml": [
  123,
  "123"
]
  }
]

Regards,
Dean

-- 
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 Wed, Jun 9, 2010 at 7:23 AM, Dean Rasheed  wrote:
> On 9 June 2010 12:07, Robert Haas  wrote:
>> On Wed, Jun 9, 2010 at 2:58 AM, Dean Rasheed  
>> wrote:
>>> 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.
>>
>> Really?  I enter:
>>
>> - foo
>> - bar
>> - .baz
>>
>> And it produces this JSON:
>>
>> [
>>  "foo",
>>  "bar",
>>  ".baz"
>> ]
>>
>> That looks OK to me.
>>
>
> Ah, OK I didn't test those cases properly before composing my email.
> It's actually only a "." on its own that it can't parse.

Well, at first blush, that looks like it might be a bug in the parser.
 I don't see anything in the spec to indicate that that case should be
treated specially.

> My comment about numbers still applies though. The following are
> different values:
>
> - just: write some
> - yaml:
>  - 123
>  - "123"

Well, you can't have abc mean the same thing as "abc" but then
complain that 123 isn't equivalent to "123"...

This format is really a pain to work with.

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

[Dean Rasheed]

On 9 June 2010 12:32, Robert Haas  wrote:
> On Wed, Jun 9, 2010 at 7:23 AM, Dean Rasheed  wrote:
>> On 9 June 2010 12:07, Robert Haas  wrote:
>>> On Wed, Jun 9, 2010 at 2:58 AM, Dean Rasheed  
>>> wrote:
 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.
>>>
>>> Really?  I enter:
>>>
>>> - foo
>>> - bar
>>> - .baz
>>>
>>> And it produces this JSON:
>>>
>>> [
>>>  "foo",
>>>  "bar",
>>>  ".baz"
>>> ]
>>>
>>> That looks OK to me.
>>>
>>
>> Ah, OK I didn't test those cases properly before composing my email.
>> It's actually only a "." on its own that it can't parse.
>
> Well, at first blush, that looks like it might be a bug in the parser.
>  I don't see anything in the spec to indicate that that case should be
> treated specially.
>

Yeah, I think it *is* valid, and JYaml parses it OK.
Some people will no doubt say "if your parser can't handle it, get a
better parser", but I'd rather not make it more difficult than it
needs to be.


>> My comment about numbers still applies though. The following are
>> different values:
>>
>> - just: write some
>> - yaml:
>>  - 123
>>  - "123"
>
> Well, you can't have abc mean the same thing as "abc" but then
> complain that 123 isn't equivalent to "123"...
>

Yeah. I know that JYaml parses 123 to a java.lang.Integer, and "123"
to a java.lang.String.


> This format is really a pain to work with.
>

Agreed :-(

- Dean

-- 
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:
> please test.

Well your patch definitely fixes my original bug, and AFAICT always
produces valid YAML output now. I've only found one case where a
particular parser has difficulty parsing the output, and you'd have to
write a pretty perverse query to hit that case.

So that just leaves this sort of thing:

explain (format yaml) select * from foo as "123";
   QUERY PLAN
-
 - Plan:+
 Node Type: Seq Scan+
 Relation Name: foo +
 Alias: 123 +
 Startup Cost: 0.00 +
 Total Cost: 23.10  +
 Plan Rows: 1310+
 Plan Width: 32
(1 row)

Does anyone care that Alias will sometimes be a string, and sometimes a number?

ITSM that, since postgresql knows that it's a string, it ought to
output something that parsers can unambiguously treat as a string too.

But this is also a pretty obscure case that probably only someone
deliberately trying to be awkward would do (which is me, with my
tester hat on :-)).

Regards,
Dean

-- 
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 Wed, Jun 9, 2010 at 8:46 AM, Dean Rasheed  wrote:
> On 9 June 2010 03:48, Robert Haas  wrote:
>> please test.
>
> Well your patch definitely fixes my original bug, and AFAICT always
> produces valid YAML output now. I've only found one case where a
> particular parser has difficulty parsing the output, and you'd have to
> write a pretty perverse query to hit that case.

Excellent.

> So that just leaves this sort of thing:
>
> explain (format yaml) select * from foo as "123";
>       QUERY PLAN
> -
>  - Plan:                +
>     Node Type: Seq Scan+
>     Relation Name: foo +
>     Alias: 123         +
>     Startup Cost: 0.00 +
>     Total Cost: 23.10  +
>     Plan Rows: 1310    +
>     Plan Width: 32
> (1 row)
>
> Does anyone care that Alias will sometimes be a string, and sometimes a 
> number?
>
> ITSM that, since postgresql knows that it's a string, it ought to
> output something that parsers can unambiguously treat as a string too.
>
> But this is also a pretty obscure case that probably only someone
> deliberately trying to be awkward would do (which is me, with my
> tester hat on :-)).

I guess we could do this by (a) conditionalizing the YAML case in
ExplainProperty() in the same way that the JSON case is currently
conditionalized, and (b) changing the first if statement in
escape_yaml() to set needs_quoting = true unless the first character
is alphabetic or an underscore.

By the way, can I ask why you're not just using the JSON format for
this?  I mean, I'm glad you are, because it exposed a bug that we got
fixed before release, but it seems a little masochistic...!

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

[Craig Ringer]

On 09/06/10 18:09, Dave Page wrote:

> I'm unconvinced that the extra complexity involved in checking the
> existence of the service account to tweak the message is worthwhile. I
> don't think it buys us extra simplicity that would suddenly make
> people understand.

In the end, you know this much better than me, but thanks for listening
to me and giving considered comments.

Personally I still think the UI is confusing (double-entry of existing
password, mixing service account and db superuser into single password)
but alternatives may not be any less confusing. In fact, I tried to mock
up a few and found that they all required WAY too much knowledge of the
difference between the service and db accounts, etc, to be viable, or
boiled down to the current one with more babble.

Unless service account and db user account are separated (which only
makes sense if the service account password management can be made
transparent in some way, which it seems it can't really) then the
current way is pretty good once you really dig down and compare it to
the alternatives.

Once again, thanks for taking the time to go through it.

-- 
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.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 #5475: Problem during Instalation

[Dave Page]

On Wed, Jun 9, 2010 at 2:03 PM, Craig Ringer
 wrote:
> On 09/06/10 18:09, Dave Page wrote:
>
>> I'm unconvinced that the extra complexity involved in checking the
>> existence of the service account to tweak the message is worthwhile. I
>> don't think it buys us extra simplicity that would suddenly make
>> people understand.
>
> In the end, you know this much better than me, but thanks for listening
> to me and giving considered comments.

Well we have been through many of these issues before, but that
doesn't mean you won't have better ideas that we haven't thought
about.

> Personally I still think the UI is confusing (double-entry of existing
> password, mixing service account and db superuser into single password)
> but alternatives may not be any less confusing. In fact, I tried to mock
> up a few and found that they all required WAY too much knowledge of the
> difference between the service and db accounts, etc, to be viable, or
> boiled down to the current one with more babble.

FYI, under the hood of the installer, the two passwords are separate,
so advanced users can control them independently on the command line.

> Unless service account and db user account are separated (which only
> makes sense if the service account password management can be made
> transparent in some way, which it seems it can't really) then the
> current way is pretty good once you really dig down and compare it to
> the alternatives.

Yeah - we used to have them completely independent in the MSI
installers, and some people found that very confusing too. One of the
major design aims of the 'one-clicks' was to simplify the process
significantly.

> Once again, thanks for taking the time to go through it.

No problem. Thanks for the ideas. Keep 'em coming :-)


-- 
Dave Page
EnterpriseDB UK: 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 =)

[Tom Lane]

Mark Kirkwood  writes:
> 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.

Nonsense.  ANALYZE and the optimizer work with the datatype's usual
notion of '=', whatever it is.

It's possible that we should install a simplified code path in analyze.c
that can collect width data for a column even in the absence of any '='
operator.  I'm less than convinced that it's worth the trouble though.
Do you have an actual example where such data would have affected a
plan choice?

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 #5495: RI/FK on self and inherited table

[Tom Lane]

"Martin Edlman"  writes:
> [ FK doesn't see a key that is present in a child table ]

That's how FK checks work, see the "Caveats" section at the bottom of
http://www.postgresql.org/docs/8.4/static/ddl-inherit.html

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 Wed, Jun 9, 2010 at 9:35 AM, Dean Rasheed  wrote:
>>> Does anyone care that Alias will sometimes be a string, and sometimes a 
>>> number?
>> I guess we could do this by (a) conditionalizing the YAML case in
>> ExplainProperty() in the same way that the JSON case is currently
>> conditionalized, and (b) changing the first if statement in
>> escape_yaml() to set needs_quoting = true unless the first character
>> is alphabetic or an underscore.
> Yes, I think that would do it.

After further review, it appears to me that this change is pretty much
required, because otherwise a string like 0xa won't be quoted.  I
might think it's OK for "123" to turn into 123, but I'm not going to
be so happy about "0xa" turning into 10.  Please test the attached
patch.

>> By the way, can I ask why you're not just using the JSON format for
>> this?  I mean, I'm glad you are, because it exposed a bug that we got
>> fixed before release, but it seems a little masochistic...!
>
> Actually I doubt that I will use this feature at all! I only use
> EXPLAIN from psql, and usually I'm the only one who needs to read it,
> so the TEXT format will remain my preferred option.
>
> I was just doing some random beta testing, working through the list of
> cool new features.

Quick, somebody give this man a cigar!

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


yet_another_yaml_fix.patch
Description: Binary data

-- 
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, Jun 9, 2010 at 6:09 AM, Dave Page  wrote:
> Please provide a password for the database superuser (${superaccount})
> and service account (${serviceaccount}). If the service account
> already exists in Windows, you must enter the current password for the
> account. If the account does not exist, it will be created when you
> click 'Next'.

I think that's REALLY confusing.  It seems to me that asking for a
password which might be used either to log into an existing account or
to set the password for an account that's about to be created is not
very user-friendly at all.  And we get questions about it here
regularly.  Why not:

If (account exists)
  prompt user to log into account
else
  tell user account will be created, ask for account pw
prompt user for db superuser pw

-- 
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 Wed, Jun 9, 2010 at 1:14 AM, 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?

I'd vote for !memcmp().

There can be (and probably already are) other ways to test for other
kinds of equality, too, 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] BUG #5475: Problem during Instalation

[Robert Haas]

On Wed, Jun 9, 2010 at 10:52 AM, Dave Page  wrote:
> On Wed, Jun 9, 2010 at 3:50 PM, Robert Haas  wrote:
>> On Wed, Jun 9, 2010 at 6:09 AM, Dave Page  wrote:
>>> Please provide a password for the database superuser (${superaccount})
>>> and service account (${serviceaccount}). If the service account
>>> already exists in Windows, you must enter the current password for the
>>> account. If the account does not exist, it will be created when you
>>> click 'Next'.
>>
>> I think that's REALLY confusing.  It seems to me that asking for a
>> password which might be used either to log into an existing account or
>> to set the password for an account that's about to be created is not
>> very user-friendly at all.  And we get questions about it here
>> regularly.  Why not:
>>
>> If (account exists)
>>  prompt user to log into account
>> else
>>  tell user account will be created, ask for account pw
>> prompt user for db superuser pw
>
> Because without additional text, the user still doesn't know that
> they're also setting the superuser password for the cluster.

I'm suggesting that you prompt for that separately, as shown in the
above pseudocode.  It seems to me that conflating the postgres user
account password with the database superuser password is confusing...
IJWH, 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] BUG #5475: Problem during Instalation

[Dave Page]

On Wed, Jun 9, 2010 at 3:50 PM, Robert Haas  wrote:
> On Wed, Jun 9, 2010 at 6:09 AM, Dave Page  wrote:
>> Please provide a password for the database superuser (${superaccount})
>> and service account (${serviceaccount}). If the service account
>> already exists in Windows, you must enter the current password for the
>> account. If the account does not exist, it will be created when you
>> click 'Next'.
>
> I think that's REALLY confusing.  It seems to me that asking for a
> password which might be used either to log into an existing account or
> to set the password for an account that's about to be created is not
> very user-friendly at all.  And we get questions about it here
> regularly.  Why not:
>
> If (account exists)
>  prompt user to log into account
> else
>  tell user account will be created, ask for account pw
> prompt user for db superuser pw

Because without additional text, the user still doesn't know that
they're also setting the superuser password for the cluster.



-- 
Dave Page
EnterpriseDB UK: 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

[Dave Page]

On Wed, Jun 9, 2010 at 3:57 PM, Robert Haas  wrote:
> I'm suggesting that you prompt for that separately, as shown in the
> above pseudocode.  It seems to me that conflating the postgres user
> account password with the database superuser password is confusing...
> IJWH, of course.

As I said in a previous email in this thread, having the two password
separated also confused people which is why we simplified to one.


-- 
Dave Page
EnterpriseDB UK: 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

[Tom Lane]

Robert Haas  writes:
> On Wed, Jun 9, 2010 at 9:35 AM, Dean Rasheed  wrote:
>>> Does anyone care that Alias will sometimes be a string, and sometimes a 
>>> number?

> After further review, it appears to me that this change is pretty much
> required, because otherwise a string like 0xa won't be quoted.  I
> might think it's OK for "123" to turn into 123, but I'm not going to
> be so happy about "0xa" turning into 10.  Please test the attached
> patch.

I still agree with Dean's original proposal: always quote the values of
strings.

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 Wed, Jun 9, 2010 at 11:03 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Wed, Jun 9, 2010 at 9:35 AM, Dean Rasheed  
>> wrote:
 Does anyone care that Alias will sometimes be a string, and sometimes a 
 number?
>
>> After further review, it appears to me that this change is pretty much
>> required, because otherwise a string like 0xa won't be quoted.  I
>> might think it's OK for "123" to turn into 123, but I'm not going to
>> be so happy about "0xa" turning into 10.  Please test the attached
>> patch.
>
> I still agree with Dean's original proposal: always quote the values of
> strings.

I'd still rather rip the format out entirely than do that.  Dean's
proposal was based on the idea that it would be safe to quote only the
values and not the keys, which is not something I care to bank on.

-- 
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 =)

[Tom Lane]

Robert Haas  writes:
> On Wed, Jun 9, 2010 at 1:14 AM, Tom Lane  wrote:
>> Yes, that was considered and rejected, IIRC.  What is your definition
>> of equality for xml?

> I'd vote for !memcmp().

Surely not.  xml is not text.

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

[Tom Lane]

Robert Haas  writes:
> On Wed, Jun 9, 2010 at 11:03 AM, Tom Lane  wrote:
>> I still agree with Dean's original proposal: always quote the values of
>> strings.

> I'd still rather rip the format out entirely than do that.

I'd be on board with that too ;-)

> Dean's
> proposal was based on the idea that it would be safe to quote only the
> values and not the keys, which is not something I care to bank on.

Why not?  Surely we can restrict EXPLAIN's set of key names to be safe.

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

[Dean Rasheed]

On 9 June 2010 16:05, Robert Haas  wrote:
> On Wed, Jun 9, 2010 at 11:03 AM, Tom Lane  wrote:
>> Robert Haas  writes:
>>> On Wed, Jun 9, 2010 at 9:35 AM, Dean Rasheed  
>>> wrote:
> Does anyone care that Alias will sometimes be a string, and sometimes a 
> number?
>>
>>> After further review, it appears to me that this change is pretty much
>>> required, because otherwise a string like 0xa won't be quoted.  I
>>> might think it's OK for "123" to turn into 123, but I'm not going to
>>> be so happy about "0xa" turning into 10.  Please test the attached
>>> patch.
>>
>> I still agree with Dean's original proposal: always quote the values of
>> strings.
>
> I'd still rather rip the format out entirely than do that.  Dean's
> proposal was based on the idea that it would be safe to quote only the
> values and not the keys, which is not something I care to bank on.
>

Why? There are only a small number of keys, and we're completely in
control of them. You'd very quickly notice if one of them needed
quoting, which is not the case with values.

Dean

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

[Kevin Grittner]

Dean Rasheed  wrote:
 
> So that just leaves this sort of thing:
> 
> explain (format yaml) select * from foo as "123";
 
What about other quoted identifiers?  If I recall correctly, a
quoted identifier can contain *any* characters (although a quote
must be represented as two adjacent quotes).
 
test=# create schema "0";
CREATE SCHEMA
test=# create table "0"."123" ("456" int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"123_pkey" for table "123"
CREATE TABLE
test=# explain (format yaml) select * from "0"."123" where "456" =
'789';
QUERY PLAN
---
 - Plan:  +
 Node Type: Index Scan+
 Scan Direction: Forward  +
 Index Name: "\"123_pkey\""   +
 Relation Name: 123   +
 Alias: 123   +
 Startup Cost: 0.00   +
 Total Cost: 8.27 +
 Plan Rows: 1 +
 Plan Width: 4+
 Index Cond: "(\"456\" = 789)"
(1 row)
 
Also, the trailing spaces in this format (stripped from this email
to avoid odd spacing) are more than a little annoying.

-Kevin


-- 
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 Wed, Jun 9, 2010 at 11:14 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Wed, Jun 9, 2010 at 11:03 AM, Tom Lane  wrote:
>>> I still agree with Dean's original proposal: always quote the values of
>>> strings.
>
>> I'd still rather rip the format out entirely than do that.
>
> I'd be on board with that too ;-)
>
>> Dean's
>> proposal was based on the idea that it would be safe to quote only the
>> values and not the keys, which is not something I care to bank on.
>
> Why not?  Surely we can restrict EXPLAIN's set of key names to be safe.

It seems to me that it would be easy for a future patch to break this
by accident.

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

[Tom Lane]

Robert Haas  writes:
> On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane  wrote:
>> Why not?  Surely we can restrict EXPLAIN's set of key names to be safe.

> It seems to me that it would be easy for a future patch to break this
> by accident.

Really?  What likely key names would be in need of quoting?  I can't
imagine accepting a field name that contains punctuation or leading
or trailing whitespace, for example.

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

[Dean Rasheed]

On 9 June 2010 17:25, Tom Lane  wrote:
> Robert Haas  writes:
>> On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane  wrote:
>>> Why not?  Surely we can restrict EXPLAIN's set of key names to be safe.
>
>> It seems to me that it would be easy for a future patch to break this
>> by accident.
>
> Really?  What likely key names would be in need of quoting?  I can't
> imagine accepting a field name that contains punctuation or leading
> or trailing whitespace, for example.
>

And any of those things would break the XML output too.
I can't imagine that going unnoticed.

Dean

-- 
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 Wed, Jun 9, 2010 at 12:25 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane  wrote:
>>> Why not?  Surely we can restrict EXPLAIN's set of key names to be safe.
>
>> It seems to me that it would be easy for a future patch to break this
>> by accident.
>
> Really?  What likely key names would be in need of quoting?  I can't
> imagine accepting a field name that contains punctuation or leading
> or trailing whitespace, for example.

It seemed to me, in particular, that someone might use a # symbol,
like "# of Iterations".

Maybe I'm being paranoid.

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

[Dean Rasheed]

On 9 June 2010 17:52, Robert Haas  wrote:
> On Wed, Jun 9, 2010 at 12:25 PM, Tom Lane  wrote:
>> Robert Haas  writes:
>>> On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane  wrote:
 Why not?  Surely we can restrict EXPLAIN's set of key names to be safe.
>>
>>> It seems to me that it would be easy for a future patch to break this
>>> by accident.
>>
>> Really?  What likely key names would be in need of quoting?  I can't
>> imagine accepting a field name that contains punctuation or leading
>> or trailing whitespace, for example.
>
> It seemed to me, in particular, that someone might use a # symbol,
> like "# of Iterations".
>

Then the resulting XML tagname would be invalid too
I think they would soon realise/be told that it was a bad idea.

Dean

-- 
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 Wed, Jun 9, 2010 at 12:57 PM, Dean Rasheed  wrote:
> On 9 June 2010 17:52, Robert Haas  wrote:
>> On Wed, Jun 9, 2010 at 12:25 PM, Tom Lane  wrote:
>>> Robert Haas  writes:
 On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane  wrote:
> Why not?  Surely we can restrict EXPLAIN's set of key names to be safe.
>>>
 It seems to me that it would be easy for a future patch to break this
 by accident.
>>>
>>> Really?  What likely key names would be in need of quoting?  I can't
>>> imagine accepting a field name that contains punctuation or leading
>>> or trailing whitespace, for example.
>>
>> It seemed to me, in particular, that someone might use a # symbol,
>> like "# of Iterations".
>>
>
> Then the resulting XML tagname would be invalid too
> I think they would soon realise/be told that it was a bad idea.

Hmm, you're right.  Maybe we should go with your approach, then.

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

[Dean Rasheed]

On 9 June 2010 14:14, Robert Haas  wrote:
> On Wed, Jun 9, 2010 at 8:46 AM, Dean Rasheed  wrote:
>> On 9 June 2010 03:48, Robert Haas  wrote:
>>> please test.
>>
>> Well your patch definitely fixes my original bug, and AFAICT always
>> produces valid YAML output now. I've only found one case where a
>> particular parser has difficulty parsing the output, and you'd have to
>> write a pretty perverse query to hit that case.
>
> Excellent.
>
>> So that just leaves this sort of thing:
>>
>> explain (format yaml) select * from foo as "123";
>>       QUERY PLAN
>> -
>>  - Plan:                +
>>     Node Type: Seq Scan+
>>     Relation Name: foo +
>>     Alias: 123         +
>>     Startup Cost: 0.00 +
>>     Total Cost: 23.10  +
>>     Plan Rows: 1310    +
>>     Plan Width: 32
>> (1 row)
>>
>> Does anyone care that Alias will sometimes be a string, and sometimes a 
>> number?
>>
>> ITSM that, since postgresql knows that it's a string, it ought to
>> output something that parsers can unambiguously treat as a string too.
>>
>> But this is also a pretty obscure case that probably only someone
>> deliberately trying to be awkward would do (which is me, with my
>> tester hat on :-)).
>
> I guess we could do this by (a) conditionalizing the YAML case in
> ExplainProperty() in the same way that the JSON case is currently
> conditionalized, and (b) changing the first if statement in
> escape_yaml() to set needs_quoting = true unless the first character
> is alphabetic or an underscore.
>

Yes, I think that would do it.


> By the way, can I ask why you're not just using the JSON format for
> this?  I mean, I'm glad you are, because it exposed a bug that we got
> fixed before release, but it seems a little masochistic...!
>

Actually I doubt that I will use this feature at all! I only use
EXPLAIN from psql, and usually I'm the only one who needs to read it,
so the TEXT format will remain my preferred option.

I was just doing some random beta testing, working through the list of
cool new features.

Dean

-- 
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 Wed, Jun 9, 2010 at 12:58 PM, Robert Haas  wrote:
> On Wed, Jun 9, 2010 at 12:57 PM, Dean Rasheed  
> wrote:
>> On 9 June 2010 17:52, Robert Haas  wrote:
>>> On Wed, Jun 9, 2010 at 12:25 PM, Tom Lane  wrote:
 Robert Haas  writes:
> On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane  wrote:
>> Why not?  Surely we can restrict EXPLAIN's set of key names to be safe.

> It seems to me that it would be easy for a future patch to break this
> by accident.

 Really?  What likely key names would be in need of quoting?  I can't
 imagine accepting a field name that contains punctuation or leading
 or trailing whitespace, for example.
>>>
>>> It seemed to me, in particular, that someone might use a # symbol,
>>> like "# of Iterations".
>>>
>>
>> Then the resulting XML tagname would be invalid too
>> I think they would soon realise/be told that it was a bad idea.
>
> Hmm, you're right.  Maybe we should go with your approach, then.

After thinking about this further, I think I'd still like to take one
more crack at fixing this without quoting absolutely everything.  I
argued against this feature, but we decided to take it, and it seems
that one of the major arguments that is being put forward is that it
will be more readable than JSON, because it will have less
punctuation.  While the idea of optimizing a machine-readable format
for human-readability doesn't typically carry much water around here,
it's really the only use case for having this particular feature at
all, so, if we're not going to rip it out, ISTM we ought to respect
what it's there for.  I would be more than willing to agree that if
one more attempt isn't sufficient to fix the problem then we'll either
quote everything, or rip the whole thing out.

-- 
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 Wed, Jun 9, 2010 at 12:58 PM, Robert Haas  wrote:
> On Wed, Jun 9, 2010 at 12:57 PM, Dean Rasheed  
> wrote:
>> On 9 June 2010 17:52, Robert Haas  wrote:
>>> On Wed, Jun 9, 2010 at 12:25 PM, Tom Lane  wrote:
 Robert Haas  writes:
> On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane  wrote:
>> Why not?  Surely we can restrict EXPLAIN's set of key names to be safe.

> It seems to me that it would be easy for a future patch to break this
> by accident.

 Really?  What likely key names would be in need of quoting?  I can't
 imagine accepting a field name that contains punctuation or leading
 or trailing whitespace, for example.
>>>
>>> It seemed to me, in particular, that someone might use a # symbol,
>>> like "# of Iterations".
>>>
>>
>> Then the resulting XML tagname would be invalid too
>> I think they would soon realise/be told that it was a bad idea.
>
> Hmm, you're right.  Maybe we should go with your approach, then.

After thinking about this further, I think I'd still like to take one
more crack at fixing this without quoting absolutely everything.  I
argued against this feature, but we decided to take it, and it seems
that one of the major arguments that is being put forward is that it
will be more readable than JSON, because it will have less
punctuation.  While the idea of optimizing a machine-readable format
for human-readability doesn't typically carry much water around here,
it's really the only use case for having this particular feature at
all, so, if we're not going to rip it out, ISTM we ought to respect
what it's there for.  I would be more than willing to agree that if
one more attempt isn't sufficient to fix the problem then we'll either
quote everything, or rip the whole thing out.

-- 
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 #5495: RI/FK on self and inherited table

[Kevin Grittner]

"Martin Edlman"  wrote:
 
> ERROR:  insert or update on table "device" violates foreign key
> constraint "device_parent_id_fkey"
> DETAIL:  Key (parent_id)=(19947) is not present in table "device".
> 
> But the record is there, it was inserted into net.computer so it's
> selectable from net.device and from net.computer.
 
This is not a bug, but a known limitation.  Bring up this page and
look for the places it mentions "foreign key":
 
http://www.postgresql.org/docs/8.4/interactive/ddl-inherit.html
 
-Kevin

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

[Joel Henrique]

Thanks to EveryBody! =)


---
"Seja em você a mudança que quer para o mundo."
 Mahatma Gandhi
"A mente que se abre a uma nova idéia jamais voltará ao seu tamanho
original."

  Albert Einstein

JOwEL Henrique
(82) 8830-2627



2010/6/9 Dave Page 

> On Wed, Jun 9, 2010 at 10:03 AM, Craig Ringer
>  wrote:
> > - During the install, test for the existence of a 'postgres' user
> > account before displaying the password prompt panel of the installer
> > "wizard".
> >
> > - If such an account does not exist, display the existing UI password
> > prompt ui with simplified language indicating that you're creating a new
> > password not entering one already set. People seem to struggle with this
> > at present.
> >
> > - If such an account already exists, tell the user that PostgreSQL has
> > been installed in the past or another version is currently installed, so
> > they must provide the password that was supplied during that prior
> > installation. If they do not know the password, they may press an
> > offered "reset password" button to enter a new one, but they're warned
> > that doing so will cause any other versions of PostgreSQL to stop
> working.
>
> The current message is:
>
> Please provide a password for the database superuser (${superaccount})
> and service account (${serviceaccount}). If the service account
> already exists in Windows, you must enter the current password for the
> account. If the account does not exist, it will be created when you
> click 'Next'.
>
> Or, in upgrade mode:
>
> Please provide a password for service account (${serviceaccount}).
>
> There is a point beyond which further simplification of text really
> doesn't help (because people just aren't reading it in the first
> place). Personally, I think we're pretty darn close to that point, but
> I'm open to better phrasing.
>
> I'm unconvinced that the extra complexity involved in checking the
> existence of the service account to tweak the message is worthwhile. I
> don't think it buys us extra simplicity that would suddenly make
> people understand.
>
> > -- or possibly . 
> >
> > - When the postgres user password is reset on the PostgreSQL service via
> > the Pg installer, update or offer to update *all* service registrations
> > for any service using the postgres account so that they use the same
> > password, thus avoiding breaking old versions when the user has
> > forgotten the service account password.
>
> That would break any of them that have implemented anything like your
> previous suggestion (with the password stored in the registry). Plus,
> an installation of PostgreSQL should never modify any other software
> that may be installed, imho.
>
> A hint, or button to fire up the computer management MMC might be
> feasible, but I'm not entirely sure. I've seen these sort of bug
> report from people who have never ventured into the stuff in the
> Administrative Tools folder, so even guiding them there may not help.
>
> >> So how would you install something like pgAgent, which you would most
> >> likely want to run under the same account?
> >
> > Installers run as admin; it'd read the registry key during installation
> > and use it for the CreateService call.
>
> That might work for one of our installers, but not if someone installs
> manually, or using a 3rd party package.
>
> > OTOH, if it's a generated password, leaking it has little effect as it's
> > unique to the machine and only grants access to a service account that
> > the admin user already has access to by virtue of their admin status on
> > that machine.
>
> Which breaks any audit trail because you have no way of knowing which
> admin may login as the service user.
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise Postgres Company
>

Re: [BUGS] Invalid YAML output from EXPLAIN

[Robert Haas]

On Wed, Jun 9, 2010 at 3:50 PM, Tom Lane  wrote:
> Dean Rasheed  writes:
>> Hmm. Well it's quite subjective, but IMO it's already more readable
>> than JSON regardless of whether or not values are quoted, simply
>> because it doesn't have [ ] and { } for lists and maps, which for JSON
>> adds significantly to the number of lines in longer plans.
>
> Yeah.  Also, I think it would be fair to not quote values that are known
> constants (for example, Node Type: Seq Scan) and are chosen to not need
> quoting.  It's just the things that are variables that worry me.

Passing down information about which things are known constants seems
more complicated to me than just getting the quoting rules right in
the first place.  If you look at the patch I proposed, you'll see that
it's really quite simple and only a slight tightening of what I
committed already.

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

[Dean Rasheed]

On 9 June 2010 19:50, Robert Haas  wrote:
> After thinking about this further, I think I'd still like to take one
> more crack at fixing this without quoting absolutely everything.  I
> argued against this feature, but we decided to take it, and it seems
> that one of the major arguments that is being put forward is that it
> will be more readable than JSON, because it will have less
> punctuation.

Hmm. Well it's quite subjective, but IMO it's already more readable
than JSON regardless of whether or not values are quoted, simply
because it doesn't have [ ] and { } for lists and maps, which for JSON
adds significantly to the number of lines in longer plans.

Compare

   QUERY PLAN

 - Plan:   +
 Node Type: Nested Loop+
 Join Type: Inner  +
 Startup Cost: 0.00+
 Total Cost: 57313.15  +
 Plan Rows: 4579600+
 Plan Width: 16+
 Plans:+
   - Node Type: Seq Scan   +
 Parent Relationship: Outer+
 Relation Name: foo+
 Alias: f1 +
 Startup Cost: 0.00+
 Total Cost: 31.40 +
 Plan Rows: 2140   +
 Plan Width: 8 +
   - Node Type: Materialize+
 Parent Relationship: Inner+
 Startup Cost: 0.00+
 Total Cost: 42.10 +
 Plan Rows: 2140   +
 Plan Width: 8 +
 Plans:+
   - Node Type: Seq Scan   +
 Parent Relationship: Outer+
 Relation Name: foo+
 Alias: f2 +
 Startup Cost: 0.00+
 Total Cost: 31.40 +
 Plan Rows: 2140   +
 Plan Width: 8

with
  QUERY PLAN
---
 [+
   {  +
 "Plan": {+
   "Node Type": "Nested Loop",+
   "Join Type": "Inner",  +
   "Startup Cost": 0.00,  +
   "Total Cost": 57313.15,+
   "Plan Rows": 4579600,  +
   "Plan Width": 16,  +
   "Plans": [ +
 {+
   "Node Type": "Seq Scan",   +
   "Parent Relationship": "Outer",+
   "Relation Name": "foo",+
   "Alias": "f1", +
   "Startup Cost": 0.00,  +
   "Total Cost": 31.40,   +
   "Plan Rows": 2140, +
   "Plan Width": 8+
 },   +
 {+
   "Node Type": "Materialize",+
   "Parent Relationship": "Inner",+
   "Startup Cost": 0.00,  +
   "Total Cost": 42.10,   +
   "Plan Rows": 2140, +
   "Plan Width": 8,   +
   "Plans": [ +
 {+
   "Node Type": "Seq Scan",   +
   "Parent Relationship": "Outer",+
   "Relation Name": "foo",+
   "Alias": "f2", +
   "Startup Cost": 0.00,  +
   "Total Cost": 31.40,   +
   "Plan Rows": 2140, +
   "Plan Width": 8+
 }+
   ]  +
 }+
   ]  +
 }+
   }  +
 ]

For me, the presence or absence of quotes around the values would make
little difference to the readability, compared to all those brackets.



> While the idea of optimizing a machine-readable format
> for human-readability doesn't typically carry much water around here,
> it's really the only use case for having this particular feature at
> all, so, if we're not going to rip it out, ISTM we ought to respect
> what it's there for.  I would be more than willing to agree that if
> one more attempt isn't sufficient to fix the problem then we'll either
> quote everything, or rip the whole thing out.
>
> --
> 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/pgsq

Re: [BUGS] Invalid YAML output from EXPLAIN

[Tom Lane]

Dean Rasheed  writes:
> Hmm. Well it's quite subjective, but IMO it's already more readable
> than JSON regardless of whether or not values are quoted, simply
> because it doesn't have [ ] and { } for lists and maps, which for JSON
> adds significantly to the number of lines in longer plans.

Yeah.  Also, I think it would be fair to not quote values that are known
constants (for example, Node Type: Seq Scan) and are chosen to not need
quoting.  It's just the things that are variables that worry me.

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 #5496: Link does not open though wish to use following site

[John R Pierce]

Sunny wrote:

The following bug has been logged online:

Bug reference:  5496
Logged by:  Sunny
Email address:  goodmeet7...@naver.com
PostgreSQL version: 8.4
Operating system:   windows xp
Description:Link does not open though wish to use following site
Details: 


Link does not open though wish to use following site
(http://www.postgresql.org/download/windows)
  

...

works fine here?!?

the link on that page links to 
http://www.enterprisedb.com/products/pgdownload.do#windows


btw, this is really not a 'bug' in postgresql.



--
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 10/06/10 02:17, Tom Lane wrote:

Mark Kirkwood  writes:
   

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.
 

Nonsense.  ANALYZE and the optimizer work with the datatype's usual
notion of '=', whatever it is.

   


Slow down the reading Tom... and read what I was actually saying - note 
the"conceptually". Of course the code uses the datatype's defined "=".



It's possible that we should install a simplified code path in analyze.c
that can collect width data for a column even in the absence of any '='
operator.
   


Yeah I was thinking along the same lines.


Do you have an actual example where such data would have affected a
plan choice?


   


Not at the moment, I was thinking that anywhere that used such datatypes 
in a subquery of similar might be a likely case. I guess I was looking 
at this as a case of "this is an area where we have less accurate 
optimizer data that we could have", and thinking of ways to improve it.


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] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Bruce Momjian]

Robert Haas wrote:
> On Sun, Jun 6, 2010 at 2:53 PM, Dimitri Fontaine  
> wrote:
> > Robert Haas  writes:
> >>> Well as Bruce said this option won't solve the OP's problem, unless the
> >>> application he's using for managing the backups do use the option.
> >>
> >> Well, that's a pretty trivial change to the backup script. ?+1 from me on
> >> providing a pg_dump option.
> >
> > The application still have to have been using the option in the past.
> 
> Well, if your point is that it's too late to help anyone upgrading
> from 8.3 to 8.4, then I agree with you.  But we will likely add more
> keywords at some point in the future, and while providing an output
> format that quotes everything won't fix every potential problem, it
> might make life easier for some people.  I certainly have had times
> where it would have saved me hassle and aggravation.

The point is that if WINDOW was not a reserved word in 8.3 but is in
8.4, then every reference to a user column of WINDOW in any 8.4
application will need to be double-quoted, and odds are the user did not
do that in 8.3.

I think users would rather have the restore fail, and know right away
they have an issue, than to do the upgrade, and find out later that some
of their application queries fail and they need to run around fixing
them.  (FYI, pg_upgrade would use the new pg_dump and would not fail.)

In a way, the fact that the restore fails can be seen as a feature ---
they get the error before the go live on 8.4.  (Yeah, I am serious.)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

-- 
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 #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Robert Haas]

On Wed, Jun 9, 2010 at 9:10 PM, Bruce Momjian  wrote:
> Robert Haas wrote:
>> On Sun, Jun 6, 2010 at 2:53 PM, Dimitri Fontaine  
>> wrote:
>> > Robert Haas  writes:
>> >>> Well as Bruce said this option won't solve the OP's problem, unless the
>> >>> application he's using for managing the backups do use the option.
>> >>
>> >> Well, that's a pretty trivial change to the backup script. ?+1 from me on
>> >> providing a pg_dump option.
>> >
>> > The application still have to have been using the option in the past.
>>
>> Well, if your point is that it's too late to help anyone upgrading
>> from 8.3 to 8.4, then I agree with you.  But we will likely add more
>> keywords at some point in the future, and while providing an output
>> format that quotes everything won't fix every potential problem, it
>> might make life easier for some people.  I certainly have had times
>> where it would have saved me hassle and aggravation.
>
> The point is that if WINDOW was not a reserved word in 8.3 but is in
> 8.4, then every reference to a user column of WINDOW in any 8.4
> application will need to be double-quoted, and odds are the user did not
> do that in 8.3.
>
> I think users would rather have the restore fail, and know right away
> they have an issue, than to do the upgrade, and find out later that some
> of their application queries fail and they need to run around fixing
> them.  (FYI, pg_upgrade would use the new pg_dump and would not fail.)
>
> In a way, the fact that the restore fails can be seen as a feature ---
> they get the error before the go live on 8.4.  (Yeah, I am serious.)

Eeh, I've had this happen to me on earlier releases, and it didn't
feel like a feature to me.  YMMV, 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] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Alvaro Herrera]

Excerpts from Bruce Momjian's message of mié jun 09 21:10:21 -0400 2010:

> I think users would rather have the restore fail, and know right away
> they have an issue, than to do the upgrade, and find out later that some
> of their application queries fail and they need to run around fixing
> them.  (FYI, pg_upgrade would use the new pg_dump and would not fail.)

I think it is quite a stretch to consider this a feature.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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 #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Bruce Momjian]

Robert Haas wrote:
> > I think users would rather have the restore fail, and know right away
> > they have an issue, than to do the upgrade, and find out later that some
> > of their application queries fail and they need to run around fixing
> > them. ?(FYI, pg_upgrade would use the new pg_dump and would not fail.)
> >
> > In a way, the fact that the restore fails can be seen as a feature ---
> > they get the error before the go live on 8.4. ?(Yeah, I am serious.)
> 
> Eeh, I've had this happen to me on earlier releases, and it didn't
> feel like a feature to me.  YMMV, of course.

Would you have preferred later application failure?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

-- 
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 #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Bruce Momjian]

Alvaro Herrera wrote:
> Excerpts from Bruce Momjian's message of mi?? jun 09 21:10:21 -0400 2010:
> 
> > I think users would rather have the restore fail, and know right away
> > they have an issue, than to do the upgrade, and find out later that some
> > of their application queries fail and they need to run around fixing
> > them.  (FYI, pg_upgrade would use the new pg_dump and would not fail.)
> 
> I think it is quite a stretch to consider this a feature.

How about "a desireable behavior considering the alternatives"?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

-- 
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 #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

[Robert Haas]

On Wed, Jun 9, 2010 at 9:35 PM, Bruce Momjian  wrote:
> Robert Haas wrote:
>> > I think users would rather have the restore fail, and know right away
>> > they have an issue, than to do the upgrade, and find out later that some
>> > of their application queries fail and they need to run around fixing
>> > them. ?(FYI, pg_upgrade would use the new pg_dump and would not fail.)
>> >
>> > In a way, the fact that the restore fails can be seen as a feature ---
>> > they get the error before the go live on 8.4. ?(Yeah, I am serious.)
>>
>> Eeh, I've had this happen to me on earlier releases, and it didn't
>> feel like a feature to me.  YMMV, of course.
>
> Would you have preferred later application failure?

YES!  It's a heck of a lot easier to fix the application than it is to
doctor the dump output with vi.

-- 
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] BUG #5496: Link does not open though wish to use following site

[Sunny]

The following bug has been logged online:

Bug reference:  5496
Logged by:  Sunny
Email address:  goodmeet7...@naver.com
PostgreSQL version: 8.4
Operating system:   windows xp
Description:Link does not open though wish to use following site
Details: 

Link does not open though wish to use following site
(http://www.postgresql.org/download/windows)

---
One click installer
The one click installer is designed to be as straightforward as possible and
the fastest way to get up and running with PostgreSQL on Windows.

Download the one click installer 
Advanced users can also download a zip archive of the binaries, without the
installer.

The one click installer is maintained by Dave Page at EnterpriseDB
---



Link does not open though wish to use following site

-- 
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 #5497: plpythonu gives cache lookup error

[David Gardner]

The following bug has been logged online:

Bug reference:  5497
Logged by:  David Gardner
Email address:  dgard...@creatureshop.com
PostgreSQL version: 9.0beta2
Operating system:   Debian Linux
Description:plpythonu gives cache lookup error
Details: 

hdpsdb=# CREATE OR REPLACE FUNCTION py_test(to_list text[])
  RETURNS text AS
$BODY$
return 'here'
$BODY$
  LANGUAGE 'plpythonu' VOLATILE;
CREATE FUNCTION
hdpsdb=# SELECT * FROM py_test('{"a","b","c"}');
ERROR:  cache lookup failed for type 98
CONTEXT:  PL/Python function "py_test"
hdpsdb=# SELECT version();
 version


-
 PostgreSQL 9.0beta2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Debian 4.4.4-3) 4.4.4, 64-bit
(1 row)

hdpsdb=#

-- 
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 #5497: plpythonu gives cache lookup error

[Tom Lane]

"David Gardner"  writes:
> Description:plpythonu gives cache lookup error

Fixed, thanks for the report!

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 Wed, Jun 9, 2010 at 4:47 PM, Dean Rasheed  wrote:
> On 9 June 2010 20:56, Robert Haas  wrote:
>> On Wed, Jun 9, 2010 at 3:50 PM, Tom Lane  wrote:
>>> Dean Rasheed  writes:
 Hmm. Well it's quite subjective, but IMO it's already more readable
 than JSON regardless of whether or not values are quoted, simply
 because it doesn't have [ ] and { } for lists and maps, which for JSON
 adds significantly to the number of lines in longer plans.
>>>
>>> Yeah.  Also, I think it would be fair to not quote values that are known
>>> constants (for example, Node Type: Seq Scan) and are chosen to not need
>>> quoting.  It's just the things that are variables that worry me.
>>
>> Passing down information about which things are known constants seems
>> more complicated to me than just getting the quoting rules right in
>> the first place.  If you look at the patch I proposed, you'll see that
>> it's really quite simple and only a slight tightening of what I
>> committed already.
>>
>
> Reading the YAML spec, I've just spotted yet another case that'll
> break what you're proposing: if you don't quote "true" and "false",
> the parser will think they're booleans rather than strings.
>
> This is really why I'm opposed to this approach. There are just so
> many gotchas that it's impossible to be 100% sure that you've
> accounted for them all.

OK, I give up.

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

[Dean Rasheed]

On 9 June 2010 20:56, Robert Haas  wrote:
> On Wed, Jun 9, 2010 at 3:50 PM, Tom Lane  wrote:
>> Dean Rasheed  writes:
>>> Hmm. Well it's quite subjective, but IMO it's already more readable
>>> than JSON regardless of whether or not values are quoted, simply
>>> because it doesn't have [ ] and { } for lists and maps, which for JSON
>>> adds significantly to the number of lines in longer plans.
>>
>> Yeah.  Also, I think it would be fair to not quote values that are known
>> constants (for example, Node Type: Seq Scan) and are chosen to not need
>> quoting.  It's just the things that are variables that worry me.
>
> Passing down information about which things are known constants seems
> more complicated to me than just getting the quoting rules right in
> the first place.  If you look at the patch I proposed, you'll see that
> it's really quite simple and only a slight tightening of what I
> committed already.
>

Reading the YAML spec, I've just spotted yet another case that'll
break what you're proposing: if you don't quote "true" and "false",
the parser will think they're booleans rather than strings.

This is really why I'm opposed to this approach. There are just so
many gotchas that it's impossible to be 100% sure that you've
accounted for them all.

Regards,
Dean

-- 
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 Wed, Jun 9, 2010 at 4:48 PM, Robert Haas  wrote:
> On Wed, Jun 9, 2010 at 4:47 PM, Dean Rasheed  wrote:
>> On 9 June 2010 20:56, Robert Haas  wrote:
>>> On Wed, Jun 9, 2010 at 3:50 PM, Tom Lane  wrote:
 Dean Rasheed  writes:
> Hmm. Well it's quite subjective, but IMO it's already more readable
> than JSON regardless of whether or not values are quoted, simply
> because it doesn't have [ ] and { } for lists and maps, which for JSON
> adds significantly to the number of lines in longer plans.

 Yeah.  Also, I think it would be fair to not quote values that are known
 constants (for example, Node Type: Seq Scan) and are chosen to not need
 quoting.  It's just the things that are variables that worry me.
>>>
>>> Passing down information about which things are known constants seems
>>> more complicated to me than just getting the quoting rules right in
>>> the first place.  If you look at the patch I proposed, you'll see that
>>> it's really quite simple and only a slight tightening of what I
>>> committed already.
>>>
>>
>> Reading the YAML spec, I've just spotted yet another case that'll
>> break what you're proposing: if you don't quote "true" and "false",
>> the parser will think they're booleans rather than strings.
>>
>> This is really why I'm opposed to this approach. There are just so
>> many gotchas that it's impossible to be 100% sure that you've
>> accounted for them all.
>
> OK, I give up.

I have committed your patch, with some changes to the comments.

Thanks for bearing with me.

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