date:20090826

Jean-Michel Pouréj...@poure.com wrote:
 
 Some queries range between 400ms and 700ms.
 
Please post the results of EXPLAIN ANALYZE, not just EXPLAIN.
 
Also, it helps if you can show the definition of all tables used in
the query (including indexes) and any lines from your postgresql.conf
file which are not at the default value.  (And of course, any
overrides to those settings which were in effect on the connection
when the query was run.)
 
Please repost with that the pgsql-performance list -- this is very
unlikely to be something which will lead to a change to the PostgreSQL
product, which is what this list is about; it's probably just
something you can tune.  (Discussion can always come back here if a
problem with PostgreSQL itself is found.)
 
-Kevin


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

Re: [HACKERS] 8.5 release timetable, again

Le mercredi 26 août 2009 à 01:36 -0600, Rick Gigger a écrit :
 One possible reason that replication is more critical now than it
 was  
 a year ago is the rise in cloud computing.  The ability to fire up  
 instances on demand is much more useful when some of those boxes can  
 be database servers and those databases servers can replicate the  
 primary database and start doing something useful.  As far as I can  
 tell this one feature alone is the one thing that makes it hard to  
 convince people to migrate away from Mysql despite it's demonstrable  
 inferiority in many other areas.

I think you should have a deep look at
these two manuals that I wrote for Drupal:

Guidelines for writing MySQL and PostgreSQL compliant SQL
http://drupal.org/node/14

and

Quidelines for writing SQL efficient code:
http://drupal.org/node/559302

I have been using PostgreSQL since 1998. I took part in the development
of pgAdmin 1 and pgAdmin 2. I am very proud of the PostgreSQL community,
but I think it should fix some important issues in the domain of SQL
compliance and compatibility.

When reading developers comments on Drupal web site, it seems that there
is deep misunderstanding between developers and SQL databases. I would
say that 1% of developers know database technology. For example, most
Drupal developers think that an INNER JOIN is faster than a LEFT JOIN.

The reality of facts is that developers will not even test PostgreSQL
and stop using it after the first SQL warning or error.

So I would recommend focussing on usability.

Then you can work on replication and materilized views. You probably
know that a cloud requires several computers. With materialized view, a
single computer can perform 100 times better. I see plenty of of
possibilities to improve speed using materialized views.

But first and firstly ... focus on usability. Then make a pre-release of
a PostgreSQL 8.4.2 release or so ... We need to wipe out this MySQL
issue once for all.

If there is a compat MySQL mode or functions, then include it in core.
This is too important for PostgreSQL success.

Why MySQL usability is achieved add materialized views and MySQL is dead
in the performance statistics, beaten 99% by PostgreSQL.

Kind regards, 
Jean-Michel


signature.asc
Description: Ceci est une partie de message numériquement signée

Re: [HACKERS] pretty print viewdefs

Andrew Dunstan and...@dunslane.net writes:
 The tiny patch attached fixes a long-standing peeve of mine (and at 
 least one of my clients'), namely that the target list printed in 
 viewdefs are unreadable.

Personally I think this will take up enough vertical space to make
things less readable on-screen, not more so.  But that's just MHO.
It probably depends a lot on the sorts of views you tend to look at...

regards, tom lane

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

Re: [HACKERS] PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query

Le mercredi 26 août 2009 à 10:07 -0400, Andrew Dunstan a écrit :
 
 Isn't that cast effectively a no-op?

What is no-op in English (I am French, pardon my English).
I will update table definition in 5 minutes.

Stay tuned.

Kind regards,
Jean-Michel


signature.asc
Description: Ceci est une partie de message numériquement signée

Re: [HACKERS] PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query

Jean-Michel Pouréj...@poure.com wrote:
Le mercredi 26 août 2009 à 10:07 -0400, Andrew Dunstan a écrit :
 
 Isn't that cast effectively a no-op?
 
 What is no-op 
 
It's short for no operation -- meaning it doesn't really result in
anything being done.  You are probably way off base in your
assumptions about why these queries aren't running faster, but have
not provided enough information to determine the actual reason.
 
-Kevin

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

Re: [HACKERS] PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query

Jean-Michel Pouré wrote:
 Le mercredi 26 août 2009 à 10:07 -0400, Andrew Dunstan a écrit :
  
  Isn't that cast effectively a no-op?
 
 What is no-op in English (I am French, pardon my English).

“no-op” means “an operation which does nothing”.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query




Jean-Michel Pouré wrote:

Le mercredi 26 août 2009 à 10:07 -0400, Andrew Dunstan a écrit :
  

Isn't that cast effectively a no-op?



What is no-op in English (I am French, pardon my English).
I will update table definition in 5 minutes.
  


see http://en.wikipedia.org/wiki/No-op

cheers

andrew


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

Re: [HACKERS] 8.5 release timetable, again

Jean-Michel Pouréj...@poure.com wrote:
 focus on usability.
 
It's not clear to me what you feel is needed.  That could mean many
things
 
-Kevin

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

Re: [HACKERS] PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query




Jean-Michel Pouré wrote:

Dear friends,

Thank you for your previous answers. I am running into a systemic
problem using Drupal under PostgreSQL 8.4

Drupal relies heavily on a domain derived from int:

CREATE DOMAIN int_unsigned
  AS integer
   CONSTRAINT int_unsigned_check CHECK ((VALUE = 0));

Analysing slow queries, I noticed that PostgreSQL 8.4 would cast data
from int4 to int_unsigned.


  


Isn't that cast effectively a no-op?

cheers

andrew

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

Re: [HACKERS] PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query

Jean-Michel =?ISO-8859-1?Q?Pour=E9?= j...@poure.com writes:
 Analysing slow queries, I noticed that PostgreSQL 8.4 would cast data
 from int4 to int_unsigned.

 Details and query plan: http://drupal.org/node/559986
 Some queries range between 400ms and 700ms.

I see no reason whatever to think that that domain has anything to do
with the choice of query plan.

regards, tom lane

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

Re: [HACKERS] pretty print viewdefs




Tom Lane wrote:

Andrew Dunstan and...@dunslane.net writes:
  
The tiny patch attached fixes a long-standing peeve of mine (and at 
least one of my clients'), namely that the target list printed in 
viewdefs are unreadable.



Personally I think this will take up enough vertical space to make
things less readable on-screen, not more so.  But that's just MHO.
It probably depends a lot on the sorts of views you tend to look at...


  


Well, I could work out if the bit that will be added to the line will 
run it over some limit (like 80 chars) and only put in the line break 
then, but it would involve a lot more code.


When you're dealing with a view that has 40 or 50 fields, having them 
all run together over a dozen or two lines is just horrible.


cheers

andrew

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

[HACKERS] PostgreSQL does CAST implicitely between int and a domain derived from int during SELECT query

Dear friends,

Thank you for your previous answers. I am running into a systemic
problem using Drupal under PostgreSQL 8.4

Drupal relies heavily on a domain derived from int:

CREATE DOMAIN int_unsigned
  AS integer
   CONSTRAINT int_unsigned_check CHECK ((VALUE = 0));

Analysing slow queries, I noticed that PostgreSQL 8.4 would cast data
from int4 to int_unsigned.

Details and query plan: http://drupal.org/node/559986
Some queries range between 400ms and 700ms.

The problem is that Drupal send 300 SQL queries per page. Of course
these queries are cached in PHP. But not solving it makes it difficult
to run a Drupal site under heavy load.

What do you think? Is this because of the constraint or is this a
possible feature missing in PostgreSQL. What solution do you recommend
to solve this problem? Is there a way to use a custom type? Should we
convert the schema to int and add local constraints?

A lot of SQL code hanging around could be impacted and this seems like a
serious issue to me. So it seems difficult to convert all these
applications. For usability, it should be better to avoid cast between
int and int_unsigned.

Kind regards,
Jean-Michel




signature.asc
Description: Ceci est une partie de message numériquement signée

Re: [HACKERS] PostgreSQL does CAST implicitely between int and a domain derive from int during SELECT query

Le mercredi 26 août 2009 à 09:16 -0500, Kevin Grittner a écrit :
 Please repost with that the pgsql-performance list

Done: http://drupal.org/node/559986

Kind regards, JMP


signature.asc
Description: Ceci est une partie de message numériquement signée

Re: [HACKERS] We should Axe /contrib/start-scripts

Tom Lane t...@sss.pgh.pa.us wrote:
 
 In general I'd not recommend that an init script go messing with the
 contents of the postmaster.pid file, which it would have to do to
 have any of this logic in the script.
 
But LSB specifically provides the pidofproc function to extract the
pid info.
 
http://refspecs.freestandards.org/LSB_3.1.0/LSB-Core-generic/LSB-Core-generic/iniscrptfunc.html
 
This brings me back round to what I was looking at recently -- the
possibility of trying to make an LSB-conforming init script for
PostgreSQL.  I'm having a lot of trouble, though, trying to get either
the postmaster or pg_ctl to behave well with the start_daemon function
implementations available to me.  Is there a fundamental mismatch
there, or am I probably just missing some crucial detail?  (The
killproc function seems to work just fine, however, as long as I use
the -p switch and give it the right signal.)
 
And there's the usual question: is there interest in such a script?
 
-Kevin

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

Re: [HACKERS] pretty print viewdefs

Andrew Dunstan and...@dunslane.net writes:
 When you're dealing with a view that has 40 or 50 fields, having them 
 all run together over a dozen or two lines is just horrible.

True, but is having them span a couple of screens vertically going to
be much better?  There'll be a whole lot of wasted whitespace.

I'm not dead set against this change, just trying to consider
alternative viewpoints.

regards, tom lane

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

Re: [HACKERS] pretty print viewdefs

2009-08-26 Thread Pavel Stehule

2009/8/26 Andrew Dunstan and...@dunslane.net:

 [originally sent from wrong account :-( ]


 The tiny patch attached fixes a long-standing peeve of mine (and at least
 one of my clients'), namely that the target list printed in viewdefs are
 unreadable.

 example output now looks like this:

   regression=# select pg_get_viewdef('shoe',true);
                   pg_get_viewdef
 ---
     SELECT
        sh.shoename,
        sh.sh_avail,
        sh.slcolor,
        sh.slminlen,
        sh.slminlen * un.un_fact AS slminlen_cm,
        sh.slmaxlen,
        sh.slmaxlen * un.un_fact AS slmaxlen_cm,
        sh.slunit
       FROM shoe_data sh, unit un
      WHERE sh.slunit = un.un_name;



I am not sure - this should by task for client application. But Pg
should have some pretty print function - it is easy implemented there.
Personally, I prefere Celko's notation, it is little bit more compact

SELECT  sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen,
  sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen,
  sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit
   FROM shoe_data sh, unit un
  WHERE sh.slunit = un.un_name;

but, sure - this is my personal preference.

 Is there any objection?

I thing so default should be unformated with some pretty printing support.

regards
Pavel Stehule


 cheers

 andrew




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



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

Re: [HACKERS] We should Axe /contrib/start-scripts

Kevin Grittner kevin.gritt...@wicourts.gov writes:
 This brings me back round to what I was looking at recently -- the
 possibility of trying to make an LSB-conforming init script for
 PostgreSQL.  I'm having a lot of trouble, though, trying to get either
 the postmaster or pg_ctl to behave well with the start_daemon function
 implementations available to me.  Is there a fundamental mismatch
 there, or am I probably just missing some crucial detail?

start_daemon doesn't provide for switching to a non-root userid
according to that spec, so it seems like *it's* missing a crucial
detail.  The functions seem woefully underspecified anyway, eg
it's unclear if killproc is supposed to wait to see whether the
daemon terminates.

regards, tom lane

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

Re: [HACKERS] 8.5 release timetable, again

Le mercredi 26 août 2009 à 09:30 -0500, Kevin Grittner a écrit :
 It's not clear to me what you feel is needed.  That could mean many
 things

Dear Kevin,

I rarely post on Hackers, so I will try to explain:
* I use PostgreSQL since 1998.
* I took part in the development of pgAdmin 12.
* I love PostgreSQL and I believe MySQL sucks. Recently I was forced to
use MySQL for Kdenlive.org project and the database sometimes stops
responding sending nothing or crap. I believe that if you use MySQL in
your company for a paid work, you can die of a heart attack.

But at the same time:
* I rewrote very long and tedious queries from PHPBB. Maybe 100 of them.
They are now part of PhpBB3. I drove all queries below 30 ms and this
enables PhpBB to scale easily. I consider this is my work.
* I think Drupal queries presently have performance problems. If I
wanted, I would be able to drive down Drupal web site, using a
collection of simple queries on projects, forum and comments. But I
don't of course.

This is why I wrote http://drupal.org/node/559302 and
http://drupal.org/node/14

Everytime I try a new Drupal module under PostgreSQL, I run into tiny
SQL problems ranging from error to performance drop. The most
problematic problem is http://drupal.org/node/559986

To fix a problem, I need to open a thread on Drupal web site and wait
for the maintainer to discuss and commit.

To give an example, Drupal includes a query optimizer written in PHP,
which sometimes adds DISTINCT to queries. In the forum, some
incredible query fetches 22000 rows, copies the rows to an arrays and
then computes the array. This allows to display previous and next
message.

But we are not going to change the world of MySQL users, which believe
they know SQL programming and in reality are complete beginners, who
like to boast about farms and replication, just like Windows users like
to collect Adobe products on DVDs and discuss with friends about them.

IMHO for what I know from the porting work (I worked heavily on PHPBB3
and now Drupal), the first goal is to achieve compatibility with issues
mentioned there: http://drupal.org/node/14 and add mysql compat
functions in PostgreSQL core without breaking existing code.

Then I can insure that 99% of MySQL compatibility problems will be
behind.

When this is achieve, we will be able to start education of developers.
And this will take another decade.

To win over MySQL, the best is to work on materialized views. There are
very good articles available from hackers. Why not port to C.
Materialized which which update when the data is needed would be
perfect.

Then we can convince Drupal hackers to add views in the schema. The
trick would be that MySQL would support normal views, whereas we would
also support materialized. We can do the same with nearly all available
frameworks: PhpBB, etc ...

Web apps are 95% of PostgreSQL possible users.

Kind regards,
Jean-Michel


signature.asc
Description: Ceci est une partie de message numériquement signée

Re: [HACKERS] pretty print viewdefs




Pavel Stehule wrote:
I am not sure - this should by task for client application. 



pg_get_viewdef() already has a pretty print mode, and this change would 
only affect output from that mode. Non-pretty printed output would be 
unchanged.


My argument is that the pretty print mode for target lists is not at all 
pretty.


I don't see why this has the be invented in every client. Then we'd have 
to do it in psql, pg_dump and so on. If any client doesn't like our 
pretty print output it can get the raw viewdef and do its own formatting.



But Pg
should have some pretty print function - it is easy implemented there.
Personally, I prefere Celko's notation, it is little bit more compact

SELECT  sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen,
  sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen,
  sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit
   FROM shoe_data sh, unit un
  WHERE sh.slunit = un.un_name;

but, sure - this is my personal preference.
  



To do that we would need to keep track of how much space was used on the 
line and how much space what we were adding would use. It's doable, but 
it's a lot more work.



  

Is there any objection?



I thing so default should be unformated with some pretty printing support.

  



Please look at the function definition. You already have the option of 
formatted or unformatted output.


cheers

andrew

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

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-26 Thread Chander Ganesan

Kevin Grittner wrote:

Tom Lane t...@sss.pgh.pa.us wrote:

In general I'd not recommend that an init script go messing with the
contents of the postmaster.pid file, which it would have to do to
have any of this logic in the script.

But LSB specifically provides the pidofproc function to extract the

pid info.

I think Tom meant that you don't want to modify the contents of that
file (or its timestamp). Reading from the file (and using the data
there) is a whole different story, and is - in part - why the file
exists in the first place.

http://refspecs.freestandards.org/LSB_3.1.0/LSB-Core-generic/LSB-Core-generic/iniscrptfunc.html

This brings me back round to what I was looking at recently -- the

possibility of trying to make an LSB-conforming init script for
PostgreSQL. I'm having a lot of trouble, though, trying to get either
the postmaster or pg_ctl to behave well with the start_daemon function
implementations available to me. Is there a fundamental mismatch
there, or am I probably just missing some crucial detail? (The
killproc function seems to work just fine, however, as long as I use
the -p switch and give it the right signal.)

And there's the usual question: is there interest in such a script?

The script is undoubtedly useful, if nothing more than to provide a
template for Linux distros. However, I think by itself it is used quite
broadly by admins that choose to install from source rather than using a
pre-packaged distribution.

Some time ago I started work on a HeartBeat/OpenAIS resource management
script for PostgreSQL to integrate it more closely with HeartbeatV2 (to
support resource monitoring on a standby, auto-setup of standby node,
etc.) but haven't worked on it since hitting a couple walls that are
somewhat related to this issue. Having a better (and foolproof)
start/stop LSB script would definitely help that project when I get back
to it..

--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
919-463-0999/877-258-8987
http://www.otg-nc.com

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

Re: [HACKERS] 8.5 release timetable, again




Jean-Michel Pouré wrote:


Everytime I try a new Drupal module under PostgreSQL, I run into tiny
SQL problems ranging from error to performance drop. The most
problematic problem is http://drupal.org/node/559986
  


I strongly suspect this post badly mis-diagnoses the problem.



IMHO for what I know from the porting work (I worked heavily on PHPBB3
and now Drupal), the first goal is to achieve compatibility with issues
mentioned there: http://drupal.org/node/14 and add mysql compat
functions in PostgreSQL core without breaking existing code.
  



That might be your goal, but it's not the community's goal, I believe. 
There are already external projects for compatibility libraries. You are 
never going to achieve 100% compatibility.





To win over MySQL, the best is to work on materialized views. There are
very good articles available from hackers. Why not port to C.
Materialized which which update when the data is needed would be
perfect.
  


IIRC some work was being done on materialised views.




Web apps are 95% of PostgreSQL possible users.
  



Most applications these days have a web front end. But that doesn't mean 
the database needs to be terribly aware of them. To the database, a web 
server is just another client.


cheers

andrew



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

Re: [HACKERS] 8.5 release timetable, again

2009-08-26 Thread Michael Glaesemann



On Aug 26, 2009, at 11:18 , Jean-Michel Pouré wrote:


Web apps are 95% of PostgreSQL possible users.



Where does this figure come from?

Michael Glaesemann
grzm seespotcode net




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

Re: [HACKERS] 8.5 release timetable, again

Jean-Michel Pouréj...@poure.com wrote:
 Kevin Grittner a écrit :
 It's not clear to me what you feel is needed.
 
 http://drupal.org/node/559302
 
These look like performance issues.
 
 http://drupal.org/node/14
 
These are MySQL compatibility issues.
 
So when you talk about focusing on usablility improvements you mean
that priority should be given to supporting MySQL-specific syntax
extensions and ensuring that there are no queries where the MySQL
optimizer comes up with a more efficient plan than PostgreSQL?
 
One concern I have is that you don't mention PostgreSQL configuration
in your performance advice, and I seem to remember you said that you
didn't tune your postgresql.conf file beyond boosting the
shared_buffers setting.  If that's true, you might be somewhat
surprised with the performance improvements if you tweak just a few
other settings.
 
You might want to see what suggestions you get from:
 
http://pgfoundry.org/projects/configurator/
 
-Kevin

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

Re: [HACKERS] We should Axe /contrib/start-scripts

Tom Lane t...@sss.pgh.pa.us wrote: 
 
 start_daemon doesn't provide for switching to a non-root userid
 according to that spec, so it seems like *it's* missing a crucial
 detail.
 
Hmmm...  I didn't see anything requiring that it only by run by root. 
Do you see something that suggests that it must be?
 
I've been making my attempts as the owner of the cluster, assuming
that the init script would su to that before calling that function.
 
 it's unclear if killproc is supposed to wait to see whether the
 daemon terminates.
 
I think it's pretty clear, in the context of the whole spec, that it
should give it *some* time to make the attempt, since it says that it
should return zero if the daemon was successfully terminated and
non-zero otherwise.  I think that also suggests that it doesn't expect
you to wait forever.  You do kinda have to read between the lines and
take the spec from a holistic perspective, I'll grant you that
 
-Kevin

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

Re: [HACKERS] pg_hba.conf: samehost and samenet

2009-08-26 Thread Magnus Hagander

2009/8/25 Alvaro Herrera alvhe...@commandprompt.com:
 Stef Walter wrote:
 Magnus Hagander wrote:

  and not just use SIOCGIFCONF for all Unixen?

 I do know that using SIOCGIFCONF on AIX comes with strange wrinkles and
 variable length data structures etc... getifaddrs() on AIX is a far more
 maintainable interface.

 Clearly the getifaddrs code looks nicer.  How can we distinguish the
 SIOCGIFCONF implementations that have wrinkles from those that don't?
 Is there some autoconf macro?

If there are some that do have it, and these platforms support
getifaddrs(), that certainly seems like a worthwhile reason. I was
just looking for the case when the SIOGIFCONF code would be identical
on all platforms - in which case we'd jus tbe maintaining some
unnecessary code. So it seems fine here.


 Something to keep in mind -- my getifaddrs(3) manpage says that on BSD
 it can return addresses that have ifa_addr set to NULL, which your code
 doesn't seem to check.

Eek. This is not defined by any standard, is it? I wonder how many
different behaviours we can find there :(

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

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

[HACKERS] pretty print viewdefs



The tiny patch attached fixes a long-standing peeve of mine (and at 
least one of my clients'), namely that the target list printed in 
viewdefs are unreadable.


example output now looks like this:

   regression=# select pg_get_viewdef('shoe',true);
   pg_get_viewdef
   ---

 SELECT
sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
   FROM shoe_data sh, unit un
  WHERE sh.slunit = un.un_name;


Is there any objection?

cheers

andrew
Index: src/backend/utils/adt/ruleutils.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.306
diff -c -r1.306 ruleutils.c
*** src/backend/utils/adt/ruleutils.c	1 Aug 2009 19:59:41 -	1.306
--- src/backend/utils/adt/ruleutils.c	26 Aug 2009 12:51:23 -
***
*** 2665,2670 
--- 2665,2672 
  
  		appendStringInfoString(buf, sep);
  		sep = , ;
+ 		appendContextKeyword(context, , -PRETTYINDENT_STD, 
+ 			 PRETTYINDENT_STD, PRETTYINDENT_VAR);
  		colno++;
  
  		/*

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

Re: [HACKERS] 8.5 release timetable, again




Robert Haas wrote:

I am assuming that at least Hot Standby and Streaming Replication will
likely require two CommitFests to go from the point where they are
seriously reviewable to actual commit.  So if they hit the 9/15 date,
they should make 8.5 even with just three CommitFests.  If they don't
hit the 9/15 date, then a 3-CommitFest cycle will probably be too
short for them to make it in.  But if we schedule a fourth CommitFest
in January in the hopes of seeing one of those patches committed, then
ISTM we're basically speculating that the patch authors will not hit
the 9/15 date but that they will hit an 11/15 date.



My concern is not just with those features, but with the whole ratio of 
the window for new work to the total development cycle. That ratio keeps 
going down and the time the tree is effectively frozen to new features 
keeps going up. I'd like to see us keep the tree open as long as 
possible but be much more ruthless about chopping off things that aren't 
ready at the end. That way we can quickly get to a beta and get on with 
the next cycle. I realise the idea is that significant features must be 
submitted by the penultimate CF, but I'm not too sure how well that's 
going to work in practice. That just seems like we're relabelling things 
rather than a fundamental change. At the very least my vote goes for 
four CFs rather than three.


cheers

andrew

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

Re: [HACKERS] pretty print viewdefs

Andrew Dunstan wrote:
 
 [originally sent from wrong account :-( ]

Andrew, you can login to the majordomo site and set your secondary
address as an alias of this one.  This means it'll recognize the other
address and allow you to post from there without going through the
moderator queue.  Of course, that address will not receive any mail from
majordomo.

Note that if you do this, it will work automatically for all lists, not
just -hackers, so it is a lot better than subscribing to each list and
setting it nomail.

It only takes a minute (but you need your Majordomo list password, which
can be emailed to you if you don't have it).
http://www.postgresql.org/mailpref/pgsql-hackers

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] 8.5 release timetable, again

On Wed, Aug 26, 2009 at 12:07 PM, Andrew Dunstanand...@dunslane.net wrote:
 Robert Haas wrote:

 I am assuming that at least Hot Standby and Streaming Replication will
 likely require two CommitFests to go from the point where they are
 seriously reviewable to actual commit.  So if they hit the 9/15 date,
 they should make 8.5 even with just three CommitFests.  If they don't
 hit the 9/15 date, then a 3-CommitFest cycle will probably be too
 short for them to make it in.  But if we schedule a fourth CommitFest
 in January in the hopes of seeing one of those patches committed, then
 ISTM we're basically speculating that the patch authors will not hit
 the 9/15 date but that they will hit an 11/15 date.

 My concern is not just with those features, but with the whole ratio of the
 window for new work to the total development cycle. That ratio keeps going
 down and the time the tree is effectively frozen to new features keeps going
 up.

I think that's a very valid concern.  Against that, if release cycles
become very long, then features can hit the tree more of the time, but
they don't get into a released version for ages.

 I'd like to see us keep the tree open as long as possible but be much
 more ruthless about chopping off things that aren't ready at the end. That
 way we can quickly get to a beta and get on with the next cycle

I'm happy to assist with that, but recall that even after we ended CF
2008-11 another four months went by before release.  That's a whole
lotta time for the tree to be closed right there.

 I realise
 the idea is that significant features must be submitted by the penultimate
 CF, but I'm not too sure how well that's going to work in practice. That
 just seems like we're relabelling things rather than a fundamental change.
 At the very least my vote goes for four CFs rather than three.

Fair enough, more votes are good.

...Robert

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

Re: [HACKERS] pretty print viewdefs




Alvaro Herrera wrote:

Andrew Dunstan wrote:
  

[originally sent from wrong account :-( ]



Andrew, you can login to the majordomo site and set your secondary
address as an alias of this one.  This means it'll recognize the other
address and allow you to post from there without going through the
moderator queue.  Of course, that address will not receive any mail from
majordomo.

  


Thanks, that's one MD feature I didn't know about or had forgotten. Nice.

cheers

andrew

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

Re: [HACKERS] 8.5 release timetable, again

Andrew Dunstan and...@dunslane.net wrote:
 
 the window for new work to the total development cycle. That ratio
 keeps going down and the time the tree is effectively frozen to new
 features keeps going up. I'd like to see us keep the tree open as
 long as possible but be much more ruthless about chopping off things
 that aren't ready at the end. That way we can quickly get to a beta
 and get on with the next cycle. I realise the idea is that
 significant features must be submitted by the penultimate CF, but
 I'm not too sure how well that's going to work in practice. That
 just seems like we're relabelling things rather than a fundamental
 change. At the very least my vote goes for four CFs rather than
 three.
 
Unless the community can reduce the time between the start of the last
commit-fest and the release, you're limited to an average of four
months of programming time per year for new features (assuming that
people are observing the rules about what they should be doing during
commit-fests and beta testing).  If you want to move the next release
back into Spring rather than Summer (which is the season in which 8.4
was released -- at least of those of us in the Northern Hemisphere),
you would need to shorten that to three months for this release.
 
Unless...
 
Both the ruthless cutting of anything not totally ready at the end of
a commit-fest, *and* reducing the time from the end of the last
commit-fest to release would be needed to get that up to five months
per year.  We obviously don't want less testing during the beta cycle,
but delaying the release while the release notes are developed at the
end of the cycle seems like an obvious target for improvement.  I'd
bet there are others, though I don't know what they are
 
-Kevin

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

Re: [HACKERS] pretty print viewdefs

2009-08-26 Thread Andreas Pflug

Andrew Dunstan wrote:


 But Pg
 should have some pretty print function - it is easy implemented there.
 Personally, I prefere Celko's notation, it is little bit more compact

 SELECT  sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen,
   sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen,
   sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit
FROM shoe_data sh, unit un
   WHERE sh.slunit = un.un_name;

 but, sure - this is my personal preference.
   


 To do that we would need to keep track of how much space was used on
 the line and how much space what we were adding would use. It's
 doable, but it's a lot more work.

When initially implementing the pretty option, I ran into the same
consideration. Back then, I decided not to try any line breaking on the
column list. Instead, I treated the columns as just a bunch of
columns, laying the emphasis on the from-clause (with potentially many
joined tables).
So a pretty column formatting should still be white-space saving.

Regards,
Andreas

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

Re: [HACKERS] 8.5 release timetable, again

 Robert Haas wrote:
 I am assuming that at least Hot Standby and Streaming Replication will
 likely require two CommitFests to go from the point where they are
 seriously reviewable to actual commit.

FWIW, I think that both HS and SR are special cases: if we ever see
reviewable patches for them, people will probably be willing to work
on them outside the CommitFest framework.  We shouldn't be setting
the schedule with the idea that those will only be dealt with in CFs.
To my mind the CF process is for dealing with run of the mill patches.

Andrew Dunstan and...@dunslane.net writes:
 My concern is not just with those features, but with the whole ratio of 
 the window for new work to the total development cycle. That ratio keeps 
 going down and the time the tree is effectively frozen to new features 
 keeps going up.

Yup.  This is a huge problem and we need to deal with it somehow.  At
the same time, I'm worried that our beta testing process is already
inadequate.  We've found several rather embarrassing bugs in 8.4, for
instance, things that should have been found in beta IMO.  Shortening
beta or encouraging people to start next-cycle development instead of
testing doesn't seem like a wise move.  You can't just develop all
the time, you have to test  debug too ...

regards, tom lane

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

[HACKERS] pretty print viewdefs



[originally sent from wrong account :-( ]


The tiny patch attached fixes a long-standing peeve of mine (and at 
least one of my clients'), namely that the target list printed in 
viewdefs are unreadable.


example output now looks like this:

   regression=# select pg_get_viewdef('shoe',true);
   pg_get_viewdef
   ---

 SELECT
sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
   FROM shoe_data sh, unit un
  WHERE sh.slunit = un.un_name;


Is there any objection?

cheers

andrew


Index: src/backend/utils/adt/ruleutils.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.306
diff -c -r1.306 ruleutils.c
*** src/backend/utils/adt/ruleutils.c	1 Aug 2009 19:59:41 -	1.306
--- src/backend/utils/adt/ruleutils.c	26 Aug 2009 12:51:23 -
***
*** 2665,2670 
--- 2665,2672 
  
  		appendStringInfoString(buf, sep);
  		sep = , ;
+ 		appendContextKeyword(context, , -PRETTYINDENT_STD, 
+ 			 PRETTYINDENT_STD, PRETTYINDENT_VAR);
  		colno++;
  
  		/*


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

Re: [HACKERS] 8.5 release timetable, again




Tom Lane wrote:

Andrew Dunstan and...@dunslane.net writes:
  
My concern is not just with those features, but with the whole ratio of 
the window for new work to the total development cycle. That ratio keeps 
going down and the time the tree is effectively frozen to new features 
keeps going up.



Yup.  This is a huge problem and we need to deal with it somehow.  At
the same time, I'm worried that our beta testing process is already
inadequate.  We've found several rather embarrassing bugs in 8.4, for
instance, things that should have been found in beta IMO.  Shortening
beta or encouraging people to start next-cycle development instead of
testing doesn't seem like a wise move.  You can't just develop all
the time, you have to test  debug too ...


  


Perhaps some more formalised beta program would be useful. I have at 
least one client who could probably be persuaded to devote some 
resources to Beta testing. Maybe we need a Beta Program co-ordinator or 
some such animal. I suspect that plenty of possible beta testers don't 
even know when we go beta.


cheers

andrew

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

Re: [HACKERS] pretty print viewdefs

Andreas Pflug escribió:

 When initially implementing the pretty option, I ran into the same
 consideration. Back then, I decided not to try any line breaking on the
 column list. Instead, I treated the columns as just a bunch of
 columns, laying the emphasis on the from-clause (with potentially many
 joined tables).
 So a pretty column formatting should still be white-space saving.

It would be neat to have a way of detecting the client terminal's width
(psql knows it; it'd have to pass it as an additional parameter) and
output as many columns as fit on each line.  This is a much more
invasive change though.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] 8.5 release timetable, again

Andrew Dunstan escribió:

 Perhaps some more formalised beta program would be useful. I have at
 least one client who could probably be persuaded to devote some
 resources to Beta testing. Maybe we need a Beta Program co-ordinator
 or some such animal. I suspect that plenty of possible beta testers
 don't even know when we go beta.

This seems a good idea.  Possibly pushing the betas more aggresively to
current users would make them tested not only by PG hackers ...

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] 8.5 release timetable, again

Andrew Dunstan and...@dunslane.net wrote:
 
 Perhaps some more formalised beta program would be useful. I have
 at least one client who could probably be persuaded to devote some 
 resources to Beta testing. Maybe we need a Beta Program co-ordinator
 or some such animal. I suspect that plenty of possible beta testers
 don't even know when we go beta.
 
In some shops it would be necessary to have a date set months in
advance of the start of the beta to have any chance of getting
managers to assign hardware and staff for a really good test.  I
usually have to work in tests on my own time on whatever hardware
happens to be in standby status, when I can get it.  Hitting such a
date would seem to require a significant change from prior releases;
although the just-completed commit-fest could be taken as evidence
that such a thing is possible.
 
-Kevin

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

Re: [HACKERS] pretty print viewdefs

2009-08-26 Thread decibel


On Aug 26, 2009, at 9:02 AM, Andrew Dunstan wrote:
The tiny patch attached fixes a long-standing peeve of mine (and at  
least one of my clients'), namely that the target list printed in  
viewdefs are unreadable.


example output now looks like this:

   regression=# select pg_get_viewdef('shoe',true);
   pg_get_viewdef
---

 SELECT
sh.shoename,
sh.sh_avail,



Did we kill the idea of trying to retain the original view  
definition? Granted, that doesn't really help for SELECT *...

--
Decibel!, aka Jim C. Nasby, Database Architect  deci...@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828



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

[HACKERS] Upcoming minor releases

The packager team is planning minor releases of 7.4.X to 8.4.X.  The
packaging of the releases will be done on September 3-4, with release
due on September 9 (late to avoid a US holiday on September 7).

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

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] 8.5 release timetable, again

Dear Kevin

 So when you talk about focusing on usablility improvements you mean
 that priority should be given to supporting MySQL-specific syntax
 extensions and ensuring that there are no queries where the MySQL
 optimizer comes up with a more efficient plan than PostgreSQL?

Yes. PostgreSQL should be able to run MySQL code quoted here:

This is a prerequisite for people to be willing to test and adopt
PostgreSQL. People are not willing to debug frameworks like Drupal and
port them to PostgreSQL. We are quite alone and lost.

 One concern I have is that you don't mention PostgreSQL configuration
 in your performance advice, and I seem to remember you said that you
 didn't tune your postgresql.conf file beyond boosting the
 shared_buffers setting.  If that's true, you might be somewhat
 surprised with the performance improvements if you tweak just a few
 other settings.

shared_buffer 24M.

Kind regards,
Jean-Michel


signature.asc
Description: Ceci est une partie de message numériquement signée

Re: [HACKERS] 8.5 release timetable, again

2009-08-26 Thread Matthew T. O'Connor


Alvaro Herrera wrote:

This seems a good idea.  Possibly pushing the betas more aggresively to
current users would make them tested not only by PG hackers ...


Isn't this the purpose of the new alpha releases, at lease to some extent.


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

Re: [HACKERS] pretty print viewdefs




decibel wrote:

On Aug 26, 2009, at 9:02 AM, Andrew Dunstan wrote:
The tiny patch attached fixes a long-standing peeve of mine (and at 
least one of my clients'), namely that the target list printed in 
viewdefs are unreadable.


example output now looks like this:

   regression=# select pg_get_viewdef('shoe',true);
   pg_get_viewdef   
---

 SELECT
sh.shoename,
sh.sh_avail,



Did we kill the idea of trying to retain the original view definition? 
Granted, that doesn't really help for SELECT *...




That has nothing at all to do with the issue. The question is not about 
whether to keep the original, it's about how to format the reconstructed 
query.


cheers

andrew

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

Re: [HACKERS] 8.5 release timetable, again

Matthew T. O'Connor matt...@zeut.net writes:
 Alvaro Herrera wrote:
 This seems a good idea.  Possibly pushing the betas more aggresively to
 current users would make them tested not only by PG hackers ...

 Isn't this the purpose of the new alpha releases, at lease to some extent.

The alpha releases as currently constituted are practically the exact
opposite of what's being suggested here :-(.  We are pushing them out
without very much advertisement, and certainly without any formal
program to encourage significant testing.  That's more or less forced by
the decision that alpha releases should be low-overhead, but I think
we're unlikely to get wide-ranging test coverage that way.

regards, tom lane

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

Re: [HACKERS] 8.5 release timetable, again

On Wed, Aug 26, 2009 at 1:01 PM, Tom Lanet...@sss.pgh.pa.us wrote:
 Yup.  This is a huge problem and we need to deal with it somehow.  At
 the same time, I'm worried that our beta testing process is already
 inadequate.  We've found several rather embarrassing bugs in 8.4, for
 instance, things that should have been found in beta IMO.  Shortening
 beta or encouraging people to start next-cycle development instead of
 testing doesn't seem like a wise move.  You can't just develop all
 the time, you have to test  debug too ...

Sure, but an aimless mandate to do testing for 4 (or 8, or 12) months
doesn't necessarily buy you much, either.  I'm good at focused
activity - but there was nothing focused about 8.4 beta that I could
see.  Maybe we need some kind of TestFest process.

...Robert

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

Re: [HACKERS] 8.5 release timetable, again

2009/8/26 Jean-Michel Pouré j...@poure.com:
 Dear Kevin

 So when you talk about focusing on usablility improvements you mean
 that priority should be given to supporting MySQL-specific syntax
 extensions and ensuring that there are no queries where the MySQL
 optimizer comes up with a more efficient plan than PostgreSQL?

 Yes. PostgreSQL should be able to run MySQL code quoted here:

 This is a prerequisite for people to be willing to test and adopt
 PostgreSQL. People are not willing to debug frameworks like Drupal and
 port them to PostgreSQL. We are quite alone and lost.

Er, so we should debug Drupal for them?  I find it difficult to
believe that's the best use of our time.

 One concern I have is that you don't mention PostgreSQL configuration
 in your performance advice, and I seem to remember you said that you
 didn't tune your postgresql.conf file beyond boosting the
 shared_buffers setting.  If that's true, you might be somewhat
 surprised with the performance improvements if you tweak just a few
 other settings.

 shared_buffer 24M.

That doesn't actually respond to anything he said in that paragraph.

...Robert

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

2009-08-26 Thread Josh Berkus


 So when you talk about focusing on usablility improvements you mean
 that priority should be given to supporting MySQL-specific syntax
 extensions and ensuring that there are no queries where the MySQL
 optimizer comes up with a more efficient plan than PostgreSQL?

Well, I'd be interested in this just because I *always* want to improve
the optimizer.  I think these cases demonstrate places we could improve
our optimizer still further, just as looking for the same cases with
Oracle or SQL Server does.

 Yes. PostgreSQL should be able to run MySQL code quoted here:
 
 This is a prerequisite for people to be willing to test and adopt
 PostgreSQL. People are not willing to debug frameworks like Drupal and
 port them to PostgreSQL. We are quite alone and lost.

First, off, the evidence is against that; people are doing the work to
port things.  And creating new projects which are based on Postgres.

Second, we're not going to support MySQL's *bugs* and *bad design
decisions* which is what lazy developers actually want; they want
something exactly the same as MySQL, including bugs.  If they want that,
they can use MySQL.  We are not MySQL, and trying to out-MySQL MySQL is
stupid, just as it would be to copy Oracle exactly.

Now, there are things which MySQL does better which we should fix,
because they are real problems for our users who already like
PostgreSQL.  These include simple replication, upgrade in place, driver
maintenance, covering indexes, MERGE, etc.  But we'll do these because
they make *Postgres* better, not because MySQL has them.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

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

Re: [HACKERS] 8.5 release timetable, again

2009-08-26 Thread David Fetter

On Wed, Aug 26, 2009 at 02:46:43PM -0400, Tom Lane wrote:
 Matthew T. O'Connor matt...@zeut.net writes:
  Alvaro Herrera wrote:
  This seems a good idea.  Possibly pushing the betas more
  aggresively to current users would make them tested not only by
  PG hackers ...
 
  Isn't this the purpose of the new alpha releases, at lease to some
  extent.
 
 The alpha releases as currently constituted are practically the
 exact opposite of what's being suggested here :-(.  We are pushing
 them out without very much advertisement, and certainly without any
 formal program to encourage significant testing.  That's more or
 less forced by the decision that alpha releases should be
 low-overhead, but I think we're unlikely to get wide-ranging test
 coverage that way.

How would you recommend that this change?

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [HACKERS] 8.5 release timetable, again

David Fetter da...@fetter.org writes:
 On Wed, Aug 26, 2009 at 02:46:43PM -0400, Tom Lane wrote:
 The alpha releases as currently constituted are practically the
 exact opposite of what's being suggested here :-(.  We are pushing
 them out without very much advertisement, and certainly without any
 formal program to encourage significant testing.  That's more or
 less forced by the decision that alpha releases should be
 low-overhead, but I think we're unlikely to get wide-ranging test
 coverage that way.

 How would you recommend that this change?

As far as the alpha releases go, I wouldn't --- I see no evidence that
we have the manpower to formalize them any more than they are now.
I do like the idea of trying to reach out to more beta testers and
manage that phase more aggressively.  Maybe if we can make something
happen there, it would be possible to do the same for alphas later.

regards, tom lane

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

2009-08-26 Thread Joshua D. Drake

On Wednesday 26 August 2009 12:17:25 pm Josh Berkus wrote:

  Yes. PostgreSQL should be able to run MySQL code quoted here:
 
  This is a prerequisite for people to be willing to test and adopt
  PostgreSQL. People are not willing to debug frameworks like Drupal and
  port them to PostgreSQL. We are quite alone and lost.

 First, off, the evidence is against that; people are doing the work to
 port things.  And creating new projects which are based on Postgres.

Yeah the above is pretty much a non starter anymore. We out rank MySQL on 
several polls in terms of popularity. Our channel is growing whereas the MySQL 
one is shrinking. Our community isn't splintered and even projects like Drupal 
are making it a specific point to fix the original mysql centric design.

 Now, there are things which MySQL does better which we should fix,
 because they are real problems for our users who already like
 PostgreSQL.  These include simple replication, upgrade in place, driver
 maintenance, covering indexes, MERGE, etc.  But we'll do these because
 they make *Postgres* better, not because MySQL has them.

Agreed.

Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc : 503-667-4564 - http://www.commandprompt.com/
Since 1997, Consulting, Development, Support, Training

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

Re: [HACKERS] pretty print viewdefs

On Wed, Aug 26, 2009 at 7:47 PM, Andrew Dunstanand...@dunslane.net wrote:
 Did we kill the idea of trying to retain the original view definition?
 Granted, that doesn't really help for SELECT *...

 That has nothing at all to do with the issue. The question is not about
 whether to keep the original, it's about how to format the reconstructed
 query.

I suspect Jim's thinking that if we keep the original we don't have to
reconstruct the query ever. Unfortunately cases like select * -- and
that's not the only case, think of columns that have been renamed --
throw a wrench in the works for that.

I agree with Tom's concerns -- think of that guy who was bumping up
against the 1600 column limit. At least if they're on one line you can
still see the structure of the query albeit with a very very wide
scrollbar...

But for typical queries I do agree one per line is better. That is
actually how I format my queries when they have complex expressions in
the target list. Perhaps formatting one per line whenever there's an
alias or the value is a complex expression but putting any unaliased
columns (such as produced by select *) in a single line would be a
good compromise?

Incidentally, how does your patch format a complex subquery in the target list?

 but I think on balance this is probably better. In the extreme think
of that guy a few days ago who was bumping up against the 1600 column
limit. Assuming he had a few layers of nested subqueries his

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

 Second, we're not going to support MySQL's *bugs* and *bad design
 decisions* which is what lazy developers actually want; they want
 something exactly the same as MySQL, including bugs.  If they want
 that,
 they can use MySQL.  We are not MySQL, and trying to out-MySQL MySQL
 is
 stupid, just as it would be to copy Oracle exactly.

I understand what you mean. 

To tell you how lazy MySQL people are is my last experience in the
Drupal world. In short, on my devel server, Drupal previous/next link
display SQL script returns 21.000 rows. 

Of course, it should return only two rows. 

The 21.000 rows are then processed step by step by a PHP script. 

I open a bug and one of Drupal core developers answers: 
Jean-Michel, this is a friendly warning, please change your behavior.
This is getting really annoying. 

In fact, this core developer does not like the way I try to explain how
to use databases. I wrote two tutorials: 

http://drupal.org/node/559302
and 
http://drupal.org/node/14

The truth is that Drupal core developers do not believe fixing the
prev/next link script is important. They don't care for SQL and don't
understand the relationship between SQL queries and CPU cycles.

Read this page: http://drupal.org/project/prev_next

This performance issue was known for several months and these MySQL
developers did not even fixed it.

Finally, I escalated to the founder of the Drupal community to ask him
to integrate a proper SQL Previous/Next script into Drupal.

Their last answer: Holy crap jmpoure, this is not how the community
works. We don't beg to Dries.

Read: http://drupal.org/node/559424

 Now, there are things which MySQL does better which we should fix,
 because they are real problems for our users who already like
 PostgreSQL.  These include simple replication, upgrade in place,
 driver
 maintenance, covering indexes, MERGE, etc.  But we'll do these because
 they make *Postgres* better, not because MySQL has them.

After reading my story, I hope we can agree that noone is going to port
any MySQL code to PostgreSQL ever. This demands too much intellectual
efforts. Many people will migrate from DB2 and Oracle to PostgreSQL. But
no MySQL developer is going to use PostgreSQL if he needs to modify SQL
queries. I don't want to be offensive, but I really believe it.

So we should support a minimal set of MySQL SQL instructions. 

After several years of porting MySQL code to PostrgeSQL, I believe that
this limited list is enough: http://drupal.org/node/14

This is quite a straightforward need. Without this list of issues,
PostgreSQL may never be able to run popular products developed under
MySQL. Think of all commercial and free software projects. The impact of
MySQLisms are huge. I can only compare it to Windows vs. GNU/Linux or
FreeBSD. This is what comes in mind first.

We are not leaving in a perfect world and there no reason to achieve
perfectness. So let's support this list, please:
http://drupal.org/node/14

Kind regards,
Jean-Michel


signature.asc
Description: Ceci est une partie de message numériquement signée

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

2009/8/26 Jean-Michel Pouré j...@poure.com:
 After reading my story, I hope we can agree that noone is going to port
 any MySQL code to PostgreSQL ever. This demands too much intellectual

Surely this is a complete overgeneralization...

...Robert

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

Jean-Michel Pour? wrote:
 To tell you how lazy MySQL people are is my last experience in the
 Drupal world. In short, on my devel server, Drupal previous/next link
 display SQL script returns 21.000 rows. 

Yes, we have seen this too.  We have always targeted serious database
developers, and those wanting to be serious.  It is difficult to cater
to MySQL eccentricities and maintain a serious database offering.

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

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

Jean-Michel Pouré wrote:

 We are not leaving in a perfect world and there no reason to achieve
 perfectness. So let's support this list, please:
 http://drupal.org/node/14

Have you tried Drupal 7?  It's said to have many of these corrected.
Maybe you should stop wasting your time with 6.x.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

2009/8/26 Jean-Michel Pouré j...@poure.com:
 After reading my story, I hope we can agree that noone is going to port
 any MySQL code to PostgreSQL ever. This demands too much intellectual
 efforts. Many people will migrate from DB2 and Oracle to PostgreSQL. But
 no MySQL developer is going to use PostgreSQL if he needs to modify SQL
 queries. I don't want to be offensive, but I really believe it.

 So we should support a minimal set of MySQL SQL instructions.

 After several years of porting MySQL code to PostrgeSQL, I believe that
 this limited list is enough: http://drupal.org/node/14

 This is quite a straightforward need. Without this list of issues,
 PostgreSQL may never be able to run popular products developed under
 MySQL. Think of all commercial and free software projects. The impact of
 MySQLisms are huge. I can only compare it to Windows vs. GNU/Linux or
 FreeBSD. This is what comes in mind first.

 We are not leaving in a perfect world and there no reason to achieve
 perfectness. So let's support this list, please:
 http://drupal.org/node/14


I'm starting to see how you rubbed the Drupal people the wrong way..

Most of the items on your list are misdiagnosed problems or are
features that we think are bugs in MySQL. You would get further asking
questions and for suggestions than you would insisting that you know
better and Postgres should change its interface to match MySQL.

With your current approach you're likely to get dismissed out of hand,
not unlike what I can well believe happened in the Drupal world. That
would be unfortunate because I think there are 2 or 3 real
improvements hidden in your list.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] 8.5 release timetable, again

2009-08-26 Thread Peter Eisentraut

On ons, 2009-08-26 at 14:26 -0400, Robert Haas wrote:
 Sure, but an aimless mandate to do testing for 4 (or 8, or 12) months
 doesn't necessarily buy you much, either.  I'm good at focused
 activity - but there was nothing focused about 8.4 beta that I could
 see.  Maybe we need some kind of TestFest process.

Yeah, exactly.  I can't imagine end users would know what to do during
beta.  Even assuming that you have release notes at the beginning of
beta, you can't expect people to go through every item and do a formal
test for it.  Surely it's been tested before, else it would not be in
the release, right?


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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

Le mercredi 26 août 2009 à 22:06 +0100, Greg Stark a écrit :
 With your current approach you're likely to get dismissed out of hand,
 not unlike what I can well believe happened in the Drupal world. 
This is the case.

 That
 would be unfortunate because I think there are 2 or 3 real
 improvements hidden in your list.

Then explain I don't have your skills.

Thanks.


signature.asc
Description: Ceci est une partie de message numériquement signée

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

Le mercredi 26 août 2009 à 16:56 -0400, Alvaro Herrera a écrit :
 Have you tried Drupal 7?  It's said to have many of these corrected.
 Maybe you should stop wasting your time with 6.x.

I am running a large community on the Net and people would like to
migrate our framework to Drupal. We agreed to.

I already assembled 30 modules to develop a complete service. 

But it does not scale very well as my testings show that queries are
slow. I wrote SQL guides to inform the Drupal community. It seems that
Drupal developers do not make a relationship between database and CPU
time or memory usage. Therefore Drupal PHP cache is filled with SQL
queries. At it demands more and more memory.

I am probably going to migrate to Drupal 6.x in a few days and I will
fix queries by hand in case of problems. This is what happened when we
migrated to PhpBB.

Bye, Jean-Michel


signature.asc
Description: Ceci est une partie de message numériquement signée

Re: [HACKERS] 8.5 release timetable, again

2009-08-26 Thread Josh Berkus

Tom, all,

 As far as the alpha releases go, I wouldn't --- I see no evidence that
 we have the manpower to formalize them any more than they are now.
 I do like the idea of trying to reach out to more beta testers and
 manage that phase more aggressively.  Maybe if we can make something
 happen there, it would be possible to do the same for alphas later.

Well, we need a concrete list of things to beta test if we want people
to do this in any organized fashion.  It's easy enough for us to say we
need beta testers; but without telling our community *what* to test,
people just download, compile, do a select * from table, and say it works.

Once we have a list, we can launch a simple app/wiki page where people
can report results of their tests.

Without all that, the task is too amorphous for us to expect much from
the community-at-large.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

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

Re: [HACKERS] 8.5 release timetable, again




Josh Berkus wrote:

Tom, all,

  

As far as the alpha releases go, I wouldn't --- I see no evidence that
we have the manpower to formalize them any more than they are now.
I do like the idea of trying to reach out to more beta testers and
manage that phase more aggressively.  Maybe if we can make something
happen there, it would be possible to do the same for alphas later.



Well, we need a concrete list of things to beta test if we want people
to do this in any organized fashion.  It's easy enough for us to say we
need beta testers; but without telling our community *what* to test,
people just download, compile, do a select * from table, and say it works.

Once we have a list, we can launch a simple app/wiki page where people
can report results of their tests.

Without all that, the task is too amorphous for us to expect much from
the community-at-large.

  



Actually, what I had in mind was getting people to run their 
applications etc. in some non-production environment on the beta. I 
talked to a client today and he said sure, we have several development 
environments and we can put one or two on the beta and then let the 
developers just do their thing on it. Testing the things we know about 
is in a way less important than making sure nothing else got broken.


cheers

andrew

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

Re: [HACKERS] 8.5 release timetable, again

On Wed, Aug 26, 2009 at 5:12 PM, Peter Eisentrautpete...@gmx.net wrote:
 On ons, 2009-08-26 at 14:26 -0400, Robert Haas wrote:
 Sure, but an aimless mandate to do testing for 4 (or 8, or 12) months
 doesn't necessarily buy you much, either.  I'm good at focused
 activity - but there was nothing focused about 8.4 beta that I could
 see.  Maybe we need some kind of TestFest process.

 Yeah, exactly.  I can't imagine end users would know what to do during
 beta.  Even assuming that you have release notes at the beginning of
 beta, you can't expect people to go through every item and do a formal
 test for it.  Surely it's been tested before, else it would not be in
 the release, right?

I would sure hope so.  Testing features individually makes a whole lot
more sense to me than testing the release as a whole.  Just trying a
bunch of random stuff and seeing if anything breaks is not a very
productive activity.  On the other hand, testing individual features
is frequently very productive, but it's my understanding of the way PG
does things that that is supposed to happen before the patch is
committed.

It appears to me that most of the really nasty bugs that have been
found in 8.4.0 relate to one of the following three things, each of
which seems to be related to multiple back-branch commits.

1. SEMI/ANTI join support.
2. running the bgwriter during recovery (infrastructure changes for recovery)
3. deadman switch

Maybe some of these weren't tested well enough prior to commit?  Or
perhaps they're just more significant changes and therefore likely
spots for rough edges.

I think there is a lot of merit (as Andrew suggests) in running a
production application on a beta version of the database just to see
if anything funny happens.  But insisting that all PG developers stop
doing development to focus ONLY on that activity doesn't seem very
reasonable: not everyone is well-placed to do that kind of experiment,
or cares to do so.  Conversely, there are many people who are NOT
developers who ARE well-placed to beta test (for example, Kevin
Grittner does a lot more testing than he does development, and I think
there are few people on this mailing list who would argue that the
quality of that testing is any less than kickass).

...Robert

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

Re: [HACKERS] 8.5 release timetable, again

2009-08-26 Thread Dimitri Fontaine

Hi,

Peter Eisentraut pete...@gmx.net writes:
 On ons, 2009-08-26 at 14:26 -0400, Robert Haas wrote:
 Sure, but an aimless mandate to do testing for 4 (or 8, or 12) months
 doesn't necessarily buy you much, either.  I'm good at focused
 activity - but there was nothing focused about 8.4 beta that I could
 see.  Maybe we need some kind of TestFest process.

 Yeah, exactly.  I can't imagine end users would know what to do during
 beta.  Even assuming that you have release notes at the beginning of
 beta, you can't expect people to go through every item and do a formal
 test for it.  Surely it's been tested before, else it would not be in
 the release, right?

Well we all know that developpers are really bad at testing, because
they tend to test for cases they though about while developping the code
rather than being creative and running a full application against the
overall new product.

Now, it could be that what we miss is some tool suite to enable
application people to test their full code against our new releases and
check results, performance, plans, etc.

I know about a couple of tools to get there, Tsung and Playr. And the
focus is performance testing and scalability, not that it still works.

Is the offering good enough? We might need to run some kind of tutorials
for users to be able to run large tests easily, and maybe think about
some newer tools allowing to compare logs of two application runs in two
database versions (capture all queries and result in a database, then
have a way to diff). Then beta testing would mean having a spare machine
where to run the magic regression test suite against some internal
application.

Regards,
-- 
dim

Tsung: http://tsung.erlang-projects.org/
Playr: https://area51.myyearbook.com/trac.cgi/wiki/Playr

PS: sql level unit testing isn't an answer here as it means the
application already have the tests when entering beta. Hard sell.

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

Re: [HACKERS] pretty print viewdefs

Greg Stark gsst...@mit.edu writes:
 I agree with Tom's concerns -- think of that guy who was bumping up
 against the 1600 column limit. At least if they're on one line you can
 still see the structure of the query albeit with a very very wide
 scrollbar...

 But for typical queries I do agree one per line is better. That is
 actually how I format my queries when they have complex expressions in
 the target list. Perhaps formatting one per line whenever there's an
 alias or the value is a complex expression but putting any unaliased
 columns (such as produced by select *) in a single line would be a
 good compromise?

Yeah, I was wondering about adopting some rule like that too.

It would be pretty easy to adjust that loop so that columns that aren't
simple Vars are put on their own line, while Vars are allowed to share
a line.  I dunno whether users would see that as inconsistent, though.

regards, tom lane

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

Re: [HACKERS] 8.5 release timetable, again

Robert Haas robertmh...@gmail.com writes:
 On Wed, Aug 26, 2009 at 5:12 PM, Peter Eisentrautpete...@gmx.net wrote:
 ... Surely it's been tested before, else it would not be in
 the release, right?

 I would sure hope so.  Testing features individually makes a whole lot
 more sense to me than testing the release as a whole.  Just trying a
 bunch of random stuff and seeing if anything breaks is not a very
 productive activity.

I beg to disagree.  New features have presumably been tested, in
isolation, by the original developer and reviewers.  The things we tend
to miss are bad side-effects on corner cases and seemingly-unrelated
features.  So testing as a whole is exactly what beta is for, to my
mind.

 I think there is a lot of merit (as Andrew suggests) in running a
 production application on a beta version of the database just to see
 if anything funny happens.

... but here we seem to be coming out at the same place anyway.  Getting
people to put their existing apps onto a beta is very productive.
We have to encourage people to do more of that while it's still beta,
instead of waiting till .0 or .1 or later.

regards, tom lane

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

2009/8/26 Jean-Michel Pouré j...@poure.com:
 Le mercredi 26 août 2009 à 22:06 +0100, Greg Stark a écrit :
 That
 would be unfortunate because I think there are 2 or 3 real
 improvements hidden in your list.

 Then explain I don't have your skills.

What I'm suggesting is that you should take a different direction,
both with Drupal and with us. Instead of saying Postgres is wrong
because of your analysis, ask why what's going wrong and what the
right way to fix it is.  So instead of telling people what to do
you're asking for their help. People are a lot more friendly when
asked for help than when being bossed around.

Your list:

 PostgreSQL and MySQL do not use the same concatenation funtions (D6 only, 
 fixed in D7)

Personally I don't see a problem with us adding this to Postgres now
that we have variadic functions. I'm not sure why others are so
dead-set against it; it seems a lot less burdensome than some of the
Oracle compatibility stuff we have.

 PostgreSQL does CAST implicitely between int and a domain derived from int 
 (unsolved)

This is misdiagnosed. It's not clear where the problem lies if there's
a problem but it likely has nothing to do with the cast. If you posted
with information and asked for help people would suggest things to
test to get more information and eventually find the best way to fix
it.

 PostgreSQL does not allow DELETE on JOINS
 PostgreSQL does not allow UPDATE or DELETE on multiple tables (not solved)

These are a open issues. I would like to have better support here but
it's a major feature, not just a minor compatibility issue. Again if
you posted the queries and asked for suggestions people would help you
rewrite the queries.

 PostgreSQL does not allow mutiple ROW inserts (PostgreSQL 8.2 + only)

As you point out this was added to Postgres 3 years ago

 PostgreSQL does not allow nested ORDER BY

I don't think this is something we're interested in doing. The grammar
is hard enough to maintain as it is.

 PostgreSQL does not automatically cast data between BOOLEAN and INT
 PostgreSQL does not automatically cast data between INT and VARCHAR/CHAR

These are things we've gone out of our way to NOT do. At some cost
too. Being loose here makes it easy to miss errors in your SQL.

 PostgreSQL requires all non-aggregated fields to be present in the GROUP BY 
 clause

As I explained at length we could do something here but it would be a
major feature and it would still not be compatible with MySQL unless
you happen to be using it in a particular (common) way. For more
general uses you have to use DISTINCT ON.

 PostgreSQL requires single quotes and not double-quotes

There's no way in the world we would switch this. We're an SQL engine
and we parse the SQL language which uses quotes in a certain way. If
we switched this we would be parsing some other very different
language and everything else written in SQL (including, say, internal
queries for referential integrity checks or psql tab completion or
pg_dump) would break.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

2009/8/26 Greg Stark gsst...@mit.edu:
 PostgreSQL does not automatically cast data between BOOLEAN and INT
 PostgreSQL does not automatically cast data between INT and VARCHAR/CHAR

 These are things we've gone out of our way to NOT do. At some cost
 too. Being loose here makes it easy to miss errors in your SQL.


Actually it always bothered me that we don't have implicit casts from
integer-boolean. I can't see any ambiguity or unintentional effects
this would cause problems with. Am I missing something?


-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

Greg Stark wrote:
  PostgreSQL and MySQL do not use the same concatenation funtions (D6 only, 
  fixed in D7)
 
 Personally I don't see a problem with us adding this to Postgres now
 that we have variadic functions. I'm not sure why others are so
 dead-set against it; it seems a lot less burdensome than some of the
 Oracle compatibility stuff we have.

The Oracle functions are there to add functionality that can't be easily
performed using ANSI syntax;  this is not true of concact() and ||.

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

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

Greg Stark gsst...@mit.edu wrote:
 
 Actually it always bothered me that we don't have implicit casts
 from integer-boolean. I can't see any ambiguity or unintentional
 effects this would cause problems with. Am I missing something?
 
I'd be at least a little bit concerned about how such automatic
casting to boolean might interact with bug #3822:
 
http://archives.postgresql.org/pgsql-bugs/2007-12/msg00145.php
 
-Kevin

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

Re: [HACKERS] 8.5 release timetable, again

On Wed, Aug 26, 2009 at 11:15 PM, Tom Lanet...@sss.pgh.pa.us wrote:
 ... but here we seem to be coming out at the same place anyway.  Getting
 people to put their existing apps onto a beta is very productive.
 We have to encourage people to do more of that while it's still beta,
 instead of waiting till .0 or .1 or later.

+1 for the goal being to get users to test their applications on beta.

I also wonder if we've adopted the wrong strategy with betas by
stopping development during them. It seems to be the worst of both
worlds in that both developers and users are unhappy.

Perhaps we should fork 8.6 right away after 8.5.beta is released (I'm
assuming *after* any open issues are closed) and start a commitfest
with any pending patches. While we do that call for users to test
8.5.beta with their applications and wait a fixed amount of time for
any bugs to turn up. That lets us have a long beta period for users to
test on without stopping development.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] 8.5 release timetable, again

2009-08-26 Thread Guillaume Smet

On Thu, Aug 27, 2009 at 12:03 AM, Dimitri
Fontainedfonta...@hi-media.com wrote:
 Is the offering good enough? We might need to run some kind of tutorials
 for users to be able to run large tests easily, and maybe think about
 some newer tools allowing to compare logs of two application runs in two
 database versions (capture all queries and result in a database, then
 have a way to diff). Then beta testing would mean having a spare machine
 where to run the magic regression test suite against some internal
 application.

Well, people may recall I spent a lot of time testing 8.3 before and
during beta. The important words are a lot of time, probably one
month full time spread on 3 months to find *only* 3 problems Tom,
Alvarro and Andrew fixed: yes one month for only 3 problems
identified, reported, discussed and fixed.
The problem isn't to connect your application to the database - it's
the easy part: if you have a large one, you probably won't see the
anomalies.
The application I used for my tests is displaying every SQL query at
the bottom of the page with the time spent executing the query, I was
switching from the 8.1 site to the 8.3 site to check everything
manually.
I also got all the urls of this application (more than one million),
and use a load test tool to load each page and pgFouine to grab any
error from the PostgreSQL logs.

Even with these information and this work, I'm pretty sure I would
have missed a join problem which would have returned 2 or 3 more rows
even with the time I spent working on it.

My plan at the time was to develop an application which would parse
the query logs from the server, replay the queries on 2 PG servers
with both versions and report me the anomalies (difference in the
number of rows, difference in the content, queries slower than with
the old version).

I haven't had the opportunity to work on the 8.4 beta test due to my
daily (and often nightly) job work load but the idea is still there
and IMHO it's really necessary if we want to be able to detect the
problems we only discovered after the release.

-- 
Guillaume

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

Re: [HACKERS] pretty print viewdefs




Tom Lane wrote:

Greg Stark gsst...@mit.edu writes:
  

I agree with Tom's concerns -- think of that guy who was bumping up
against the 1600 column limit. At least if they're on one line you can
still see the structure of the query albeit with a very very wide
scrollbar...



  

But for typical queries I do agree one per line is better. That is
actually how I format my queries when they have complex expressions in
the target list. Perhaps formatting one per line whenever there's an
alias or the value is a complex expression but putting any unaliased
columns (such as produced by select *) in a single line would be a
good compromise?



Yeah, I was wondering about adopting some rule like that too.

It would be pretty easy to adjust that loop so that columns that aren't
simple Vars are put on their own line, while Vars are allowed to share
a line.  I dunno whether users would see that as inconsistent, though.
  



Yeah, probably, I don't like it much.

I do have a solution that wraps when running line length over 80 instead 
of on every col:


SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen,
   sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen,
   sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit
  FROM shoe_data sh, unit un
 WHERE sh.slunit = un.un_name;


It's not a huge amount of code.

Maybe we need a couple of extra pg_get_viewdef() variants. One to wrap 
on some provided line length, one to wrap on every column. psql could 
use the first, pg_dump could use the second.


I really can't believe anyone wants a single line with 1600 column specs ...

cheers

andrew


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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

2009-08-26 Thread Josh Berkus

Jean-Michel,

 The truth is that Drupal core developers do not believe fixing the
 prev/next link script is important. They don't care for SQL and don't
 understand the relationship between SQL queries and CPU cycles.

I doubt this would be an accurate description of all Drupal developers.
 The Drupal developers I've met have been very intelligent; most don't
know much about SQL database, but then I know zero JavaScript so we're even.

The answer to this is to be educational and not confrontational.  And
submit patches ... to a current development version and not an old version.

 After reading my story, I hope we can agree that noone is going to port
 any MySQL code to PostgreSQL ever.

Again, the evidence is against you.

 So we should support a minimal set of MySQL SQL instructions. 

In a loadable module, sure.  Patches?

 After several years of porting MySQL code to PostrgeSQL, I believe that
 this limited list is enough: http://drupal.org/node/14

There's other stuff as well.  Also, see the updates people have made to
your list.

 can only compare it to Windows vs. GNU/Linux or
 FreeBSD. This is what comes in mind first.

Well, I don't use Windows either.  So that's a pretty weak argument with
me ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

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

Re: [HACKERS] pretty print viewdefs

Andrew Dunstan and...@dunslane.net writes:
 I do have a solution that wraps when running line length over 80 instead 
 of on every col:

  SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen,
 sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen,
 sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit
FROM shoe_data sh, unit un
   WHERE sh.slunit = un.un_name;

 It's not a huge amount of code.

Well, let's see it?  What do you do with expressions that don't fit?

 Maybe we need a couple of extra pg_get_viewdef() variants. One to wrap 
 on some provided line length, one to wrap on every column. psql could 
 use the first, pg_dump could use the second.

pg_dump doesn't use prettyprinting at all, and won't if I have anything
to say about it.  But I could see teaching the psql \d views to pass
along whatever psql thinks the window width is.

regards, tom lane

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

Re: [HACKERS] pretty print viewdefs




Tom Lane wrote:

Andrew Dunstan and...@dunslane.net writes:
  
I do have a solution that wraps when running line length over 80 instead 
of on every col:



  

 SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit
   FROM shoe_data sh, unit un
  WHERE sh.slunit = un.un_name;



  

It's not a huge amount of code.



Well, let's see it?  What do you do with expressions that don't fit?
  


See attached.

We don't apply the wrapping unless there has been a column printed on 
the line (except for the SELECT line).


So we can run over the limit on a line, but if we do there's only one 
column spec. I think that's acceptable.


  
Maybe we need a couple of extra pg_get_viewdef() variants. One to wrap 
on some provided line length, one to wrap on every column. psql could 
use the first, pg_dump could use the second.



pg_dump doesn't use prettyprinting at all, and won't if I have anything
to say about it.  But I could see teaching the psql \d views to pass
along whatever psql thinks the window width is.


  


OK, but I'd still like to have the only one col per line variant available.

cheers

andrew

Index: src/backend/utils/adt/ruleutils.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.306
diff -c -r1.306 ruleutils.c
*** src/backend/utils/adt/ruleutils.c	1 Aug 2009 19:59:41 -	1.306
--- src/backend/utils/adt/ruleutils.c	26 Aug 2009 23:09:00 -
***
*** 2649,2659 
  {
  	StringInfo	buf = context-buf;
  	char	   *sep;
! 	int			colno;
  	ListCell   *l;
  
  	sep =  ;
  	colno = 0;
  	foreach(l, targetList)
  	{
  		TargetEntry *tle = (TargetEntry *) lfirst(l);
--- 2649,2669 
  {
  	StringInfo	buf = context-buf;
  	char	   *sep;
! 	int			colno, linecol;
  	ListCell   *l;
+ 	int save_len;
+ 	char   *line_start;
+ 
+ 	line_start = strrchr(buf-data,'\n');
+ 	if (line_start == NULL)
+ 		line_start = buf-data;
+ 	else
+ 		line_start++;
+ 
  
  	sep =  ;
  	colno = 0;
+ 	linecol = 1;
  	foreach(l, targetList)
  	{
  		TargetEntry *tle = (TargetEntry *) lfirst(l);
***
*** 2666,2671 
--- 2676,2683 
  		appendStringInfoString(buf, sep);
  		sep = , ;
  		colno++;
+ 		linecol++;
+ 		save_len = buf-len;
  
  		/*
  		 * We special-case Var nodes rather than using get_rule_expr. This is
***
*** 2703,2708 
--- 2715,2748 
  			if (attname == NULL || strcmp(attname, colname) != 0)
  appendStringInfo(buf,  AS %s, quote_identifier(colname));
  		}
+ 
+ 		/* handle line overflow */
+ 		if (strlen(line_start)  80  linecol  1  PRETTY_INDENT(context))
+ 		{
+ 			StringInfoData thiscol;
+ 			
+ 			initStringInfo(thiscol);
+ 			
+ 			/* save what we just added */
+ 			appendStringInfoString(thiscol,buf-data + save_len);
+ 
+ 			/* wipe it out from the buffer */
+ 			buf-len = save_len;
+ 			buf-data[save_len] = '\0';
+ 
+ 			/* add a line break and reindent */
+ 			appendContextKeyword(context, , -PRETTYINDENT_STD, 
+  PRETTYINDENT_STD, PRETTYINDENT_VAR);
+ 
+ 			/* and now put back what we wiped out */
+ 			appendStringInfoString(buf,thiscol.data);
+ 
+ 			/* reset the counters */
+ 			line_start = strrchr(buf-data,'\n') + 1;
+ 			linecol = 0;
+ 
+ 			pfree(thiscol.data);
+ 		}
  	}
  }
  

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

Re: [HACKERS] 8.5 release timetable, again

2009-08-26 Thread Peter Eisentraut

On ons, 2009-08-26 at 18:15 -0400, Tom Lane wrote:
  I think there is a lot of merit (as Andrew suggests) in running a
  production application on a beta version of the database just to see
  if anything funny happens.
 
 ... but here we seem to be coming out at the same place anyway.  Getting
 people to put their existing apps onto a beta is very productive.
 We have to encourage people to do more of that while it's still beta,
 instead of waiting till .0 or .1 or later.

I think people should be running their applications' system tests on top
of the new PostgreSQL.  Just installing the application, clicking three
buttons, and I-don't-have-more-time-than-this helps a little but not
much.

Of course many people won't have system tests, which is why this process
is a problem.

To pick up a current example: Drupal system tests pass with 8.5betaN
is nice and useful.  I ran our app on 8.5betaN and didn't see any
issues is interesting, but ultimately doesn't help much.

Much of the delay and uncertainty during beta in my mind comes from the
situation that we wait for negative results and don't trust the release
until we have seen and fixed enough of them.  Instead of waiting for
concrete, positive results and producing the release with confidence.


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

Re: [HACKERS] pretty print viewdefs

On Wed, Aug 26, 2009 at 11:49 PM, Andrew Dunstanand...@dunslane.net wrote:
 Maybe we need a couple of extra pg_get_viewdef() variants. One to wrap on
 some provided line length, one to wrap on every column. psql could use the
 first, pg_dump could use the second.

 I really can't believe anyone wants a single line with 1600 column specs ...

Uhm, why not? People generally don't care about the list of columns at
all if it's just generated by select *. If they're reading it at all
it's to see the WHERE clauses and FROM clauses and so on.

I think wrapping it at 80 columns gets the worst of both worlds. Then
you have dozens or even hundreds of lines just listing columns making
it hard to see the rest of the query. At least if it's all on one line
you can just not scroll to the right and see the rest of the query on
your screen.

An alternative to my previous compromise which might be more consistent:

List the columns one per line if *any* of the columns has an alias or
is a complex expression. If they're all simple columns then put them
all one line.

At least that way you won't have any weird column lists that switch
back and forth between styles.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] Logging configuration changes

2009-08-26 Thread Peter Eisentraut

On sön, 2009-07-12 at 22:55 +0300, Peter Eisentraut wrote:
 On occasion, I would have found it useful if a SIGHUP didn't only log *that* 
 it reloaded the configuration files, but also logged *what* had changed 
 (postgresql.conf changes in particular, not so much interested in 
 pg_hba.conf).  Especially in light of the common mistake of forgetting to 
 uncomment a changed value, this would appear to be useful.

Looked into this, looks trivial, except that this would log *all*
parameters that were set as a result of a reload, instead of only the
ones that changed.  We don't have the information of what it was
previously readily available.

I think it would still be useful that way, but some people might find it
annoying.

Comments?



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

Re: [HACKERS] We should Axe /contrib/start-scripts

I wrote:
 Kevin Grittner kevin.gritt...@wicourts.gov writes:
 Thanks Andrew, Alvaro, and Chander.  You've given me some thoughts to
 toss around.  Of course, any of these is going to be somewhat more
 complex than using [ pg_ctl -w ]

 Yeah.  I wonder if we shouldn't expend a bit more effort to make that
 way bulletproof.  As I mentioned the other day, if there were a way for
 pg_ctl to pass down its parent's PID then we could have the postmaster
 exclude that as a false match, and then using pg_ctl would be just as
 safe as invoking the postmaster directly.

 The two ways I can see to do that are to add a command line switch
 to the postmaster, or to pass the PID as an environment variable,
 say PG_GRANDPARENT_PID.  The latter is a bit uglier but it would
 require touching much less code (and documentation).

Attached is a simple patch that uses the environment-variable approach.
This is a whole lot more self-contained than what would be needed to
pass the PID as an explicit switch, so I'm inclined to do it this way.
You could argue that a bad guy could confuse matters by intentionally
passing an existing postmaster's PID in this variable --- but someone
with the ability to launch the postmaster can probably also remove an
existing lockfile, so I don't think there's a credible security risk.

Any objections?

regards, tom lane

Index: src/backend/utils/init/miscinit.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/init/miscinit.c,v
retrieving revision 1.176
diff -c -r1.176 miscinit.c
*** src/backend/utils/init/miscinit.c   12 Aug 2009 20:53:30 -  1.176
--- src/backend/utils/init/miscinit.c   26 Aug 2009 23:24:56 -
***
*** 683,689 
int len;
int encoded_pid;
pid_t   other_pid;
!   pid_t   my_pid = getpid();
  
/*
 * We need a loop here because of race conditions.  But don't loop 
forever
--- 683,728 
int len;
int encoded_pid;
pid_t   other_pid;
!   pid_t   my_pid,
!   my_p_pid,
!   my_gp_pid;
!   const char *envvar;
! 
!   /*
!* If the PID in the lockfile is our own PID or our parent's or
!* grandparent's PID, then the file must be stale (probably left over 
from
!* a previous system boot cycle).  We need to check this because of the
!* likelihood that a reboot will assign exactly the same PID as we had 
in
!* the previous reboot, or one that's only one or two counts larger and
!* hence the lockfile's PID now refers to an ancestor shell process.  We
!* allow pg_ctl to pass down its parent shell PID (our grandparent PID)
!* via the environment variable PG_GRANDPARENT_PID; this is so that
!* launching the postmaster via pg_ctl can be just as reliable as
!* launching it directly.  There is no provision for detecting
!* further-removed ancestor processes, but if the init script is written
!* carefully then all but the immediate parent shell will be root-owned
!* processes and so the kill test will fail with EPERM.  Note that we
!* cannot get a false negative this way, because an existing postmaster
!* would surely never launch a competing postmaster or pg_ctl process
!* directly.
!*/
!   my_pid = getpid();
! 
! #ifndef WIN32
!   my_p_pid = getppid();
! #else
!   /*
!* Windows hasn't got getppid(), but doesn't need it since it's not
!* using real kill() either...
!*/
!   my_p_pid = 0;
! #endif
! 
!   envvar = getenv(PG_GRANDPARENT_PID);
!   if (envvar)
!   my_gp_pid = atoi(envvar);
!   else
!   my_gp_pid = 0;
  
/*
 * We need a loop here because of race conditions.  But don't loop 
forever
***
*** 745,761 
/*
 * Check to see if the other process still exists
 *
!* If the PID in the lockfile is our own PID or our parent's 
PID, then
!* the file must be stale (probably left over from a previous 
system
!* boot cycle).  We need this test because of the likelihood 
that a
!* reboot will assign exactly the same PID as we had in the 
previous
!* reboot.  Also, if there is just one more process launch 
in this
!* reboot than in the previous one, the lockfile might mention 
our
!* parent's PID.  We can reject that since we'd never be 
launched
!* directly by a competing postmaster.  We can't detect 
grandparent
!* processes unfortunately, but if the init script is written
!* carefully then all but the

Re: [HACKERS] 8.5 release timetable, again

Peter Eisentraut wrote:
 Much of the delay and uncertainty during beta in my mind comes from the
 situation that we wait for negative results and don't trust the release
 until we have seen and fixed enough of them.  Instead of waiting for
 concrete, positive results and producing the release with confidence.

Yep, that is our dilemma.

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

  + If your life is a hard drive, Christ can be your backup. +

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

[HACKERS] Linux LSB init script

Here's a first rough cut of Linux script which attempts to launch
PostgreSQL as a somewhat LSB conforming application.  It's very
lightly tested and I haven't gone through to confirm that every corner
case is handled exactly right; in fact on my kubuntu workstation (the
only place I've tested so far) the status command returns 4 (program
or service status is unknown) when I think it should be returning 3
(program is not running), so I've got something to chase down there.
And I haven't even tried to deal with the false positive problem
yet.
 
But before I spend a lot of time fine-tuning it, I wanted to post this
as a proof-of-concept draft and see if people think it's on the right
track.
 
Did I do anything which is considered bad technique?
 
Am I using any techniques which aren't sufficiently portable?
 
Is anything just outright *wrong*?
 
Comments welcome.
 
-Kevin
 



linux-lsb
Description: Binary data

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

Re: [HACKERS] We should Axe /contrib/start-scripts

On Thu, Aug 27, 2009 at 12:32 AM, Tom Lanet...@sss.pgh.pa.us wrote:
 Attached is a simple patch that uses the environment-variable approach.
 This is a whole lot more self-contained than what would be needed to
 pass the PID as an explicit switch, so I'm inclined to do it this way.
 You could argue that a bad guy could confuse matters by intentionally
 passing an existing postmaster's PID in this variable --- but someone
 with the ability to launch the postmaster can probably also remove an
 existing lockfile, so I don't think there's a credible security risk.

 Any objections?

So with this change you would have the startup script not remove the
lock file? Postgres would refuse to start up if there was another
process with the same uid running as long as it wasn't pg_ctl or the
parent of pg_ctl?

This could still fail if the startup script runs some other commands
with  to background them and those commands happen to land with the
pid of postgres? Or the startup script runs pg_ctl within a ( )
subshell?

Or are you suggesting making this change but then still recommending
the startup script you have now. So this would just be to make the
problem less severe for people who use pg_ctl without being aware of
the problem?


-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] 8.5 release timetable, again

2009-08-26 Thread Rick Gigger



On Aug 26, 2009, at 8:17 AM, Jean-Michel Pouré wrote:


Le mercredi 26 août 2009 à 01:36 -0600, Rick Gigger a écrit :

One possible reason that replication is more critical now than it
was
a year ago is the rise in cloud computing.  The ability to fire up
instances on demand is much more useful when some of those boxes can
be database servers and those databases servers can replicate the
primary database and start doing something useful.  As far as I can
tell this one feature alone is the one thing that makes it hard to
convince people to migrate away from Mysql despite it's demonstrable
inferiority in many other areas.


I think you should have a deep look at
these two manuals that I wrote for Drupal:

Guidelines for writing MySQL and PostgreSQL compliant SQL
http://drupal.org/node/14

and

Quidelines for writing SQL efficient code:
http://drupal.org/node/559302

I have been using PostgreSQL since 1998. I took part in the  
development
of pgAdmin 1 and pgAdmin 2. I am very proud of the PostgreSQL  
community,

but I think it should fix some important issues in the domain of SQL
compliance and compatibility.

When reading developers comments on Drupal web site, it seems that  
there

is deep misunderstanding between developers and SQL databases. I would
say that 1% of developers know database technology. For example, most
Drupal developers think that an INNER JOIN is faster than a LEFT JOIN.

The reality of facts is that developers will not even test PostgreSQL
and stop using it after the first SQL warning or error.

So I would recommend focussing on usability.

Then you can work on replication and materilized views. You probably
know that a cloud requires several computers. With materialized  
view, a

single computer can perform 100 times better. I see plenty of of
possibilities to improve speed using materialized views.

But first and firstly ... focus on usability. Then make a pre- 
release of

a PostgreSQL 8.4.2 release or so ... We need to wipe out this MySQL
issue once for all.

If there is a compat MySQL mode or functions, then include it in core.
This is too important for PostgreSQL success.

Why MySQL usability is achieved add materialized views and MySQL is  
dead

in the performance statistics, beaten 99% by PostgreSQL.


This may be your experience and maybe there are stats to back this  
up.  I was simply saying, that in my experience I have consulted with  
companies using cloud computing services (ie EC2) and mysql.  They are  
performance conscious.  When they have to fire up more boxes, they pay  
for it immediately.  When they ran into problems getting good  
performance out of mysql it was very easy to show them how to get  
better performance using postgres.  (Often this was just: do the same  
thing in postgres, and look, it's faster!).  But they also rely on  
replication to be able to scale.  And without it they just weren't  
interested.


Porting any project has it's own set of issues, I was speaking to the  
case where people are evaluating databases for a new project.  I was  
not however trying to make any kind of statement as too how important  
it is as compared to any other specific feature.


I was just trying to say that in my experience current trends indicate  
that having easy to set up replication is getting more important over  
time, not less, and not the same.  Other features may be more  
important.  Getting it right is certainly more important that getting  
it soon (for reasonable values of soon and right of course IMHO).   
The experience of others my be totally different, but that is mine.


Just wanted to clarify what I was actually trying to say because this  
response seems to indicate that I didn't make certain things clear.


- Rick




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

Re: [HACKERS] pretty print viewdefs




Greg Stark wrote:

At least if it's all on one line
you can just not scroll to the right and see the rest of the query on
your screen.


  


This is where the confusion arises.

This is not possible on any terminal program I use - they don't scroll 
left and right, they wrap, and the result in this case is horrible.


cheers

andrew

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

Re: [HACKERS] We should Axe /contrib/start-scripts

Greg Stark gsst...@mit.edu writes:
 On Thu, Aug 27, 2009 at 12:32 AM, Tom Lanet...@sss.pgh.pa.us wrote:
 Attached is a simple patch that uses the environment-variable approach.

 So with this change you would have the startup script not remove the
 lock file?

Huh?  The startup script shouldn't *ever* remove the lock file.
That's been true all along, and this doesn't change it.

 This could still fail if the startup script runs some other commands
 with  to background them and those commands happen to land with the
 pid of postgres? Or the startup script runs pg_ctl within a ( )
 subshell?

Yup, and that's been true all along too.  This patch makes it possible
to write a safe initscript that uses pg_ctl --- it doesn't make it
impossible to write an unsafe one.

In practice, the situations where people would need to write unsafe
constructs have been largely eliminated anyway.  Before we had a builtin
syslogger process, people often wanted to do something like

su - postgres -c postmaster | logrotate

which is quite unsafe because there's probably an intermediate shell
process.  No need for that anymore.  But notice this is just as unsafe
whether you use pg_ctl or postmaster directly ...

regards, tom lane

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

Greg Stark gsst...@mit.edu writes:
 Actually it always bothered me that we don't have implicit casts from
 integer-boolean. I can't see any ambiguity or unintentional effects
 this would cause problems with. Am I missing something?

Personally, as an old Pascal-lover, I always thought that C's failure
to distinguish between int and boolean was the single biggest design
mistake in the language.  I'm very glad that SQL doesn't make that
mistake, and I don't want to go against the standard to introduce it.

regards, tom lane

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

Re: [HACKERS] We should Axe /contrib/start-scripts

On Thu, Aug 27, 2009 at 1:01 AM, Tom Lanet...@sss.pgh.pa.us wrote:
 So with this change you would have the startup script not remove the
 lock file?

 Huh?  The startup script shouldn't *ever* remove the lock file.
 That's been true all along, and this doesn't change it.

I thought that was the whole difference between using pg_ctl to start
up and the initscripts. Since the init script knows it's starting
something up at boot time it knows any lock files are left over and
might contain the pid of one of the  other processes the init script
starts.

I did have another thought. It could compare the time from uptime to
the timestamp on the lock file. If the server's been restarted since
the time in the lock file then it must be stale. uhm. unless clock's
been changed...

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] We should Axe /contrib/start-scripts

Greg Stark gsst...@mit.edu writes:
 On Thu, Aug 27, 2009 at 1:01 AM, Tom Lanet...@sss.pgh.pa.us wrote:
 Huh?  The startup script shouldn't *ever* remove the lock file.
 That's been true all along, and this doesn't change it.

 I thought that was the whole difference between using pg_ctl to start
 up and the initscripts. Since the init script knows it's starting
 something up at boot time it knows any lock files are left over and
 might contain the pid of one of the  other processes the init script
 starts.

An initscript knows no such thing.  It's very standard to do, eg,
service postgresql stop and service postgresql start to restart
a service.  If initscripts were coded as you suggest, all it would
take is accidentally reversing the order of those commands to thoroughly
scramble your database.

 I did have another thought. It could compare the time from uptime to
 the timestamp on the lock file. If the server's been restarted since
 the time in the lock file then it must be stale. uhm. unless clock's
 been changed...

Yeah, you can't trust system clocks too much either :-(.

I was actually having second thoughts about the idea of using file
locking.  The only environment in which I've heard of file locks not
being trustworthy is NFS, and if you're running a DB on NFS you've
probably got worse problems than this one.  Notably, if you mistakenly
try to start postmasters on two different machines against the same
NFS-mounted directory, the PID-based interlock will certainly fail, while
file locking might save you.  So maybe we should take another look at
that.  Has anyone heard of other contexts in which file locks don't
work?  Has Windows got them?

But in the meanwhile, I think this patch is a step forward and won't
break anything that works now.

regards, tom lane

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

Re: [HACKERS] We should Axe /contrib/start-scripts




Tom Lane wrote:


I was actually having second thoughts about the idea of using file
locking.  The only environment in which I've heard of file locks not
being trustworthy is NFS, and if you're running a DB on NFS you've
probably got worse problems than this one.  Notably, if you mistakenly
try to start postmasters on two different machines against the same
NFS-mounted directory, the PID-based interlock will certainly fail, while
file locking might save you.  So maybe we should take another look at
that.  Has anyone heard of other contexts in which file locks don't
work?  Has Windows got them?


  


Yes. But they are mandatory rather than advisory, I believe.

cheers

andrew

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

Re: [HACKERS] We should Axe /contrib/start-scripts

Andrew Dunstan and...@dunslane.net writes:
 Tom Lane wrote:
 Has anyone heard of other contexts in which file locks don't
 work?  Has Windows got them?

 Yes. But they are mandatory rather than advisory, I believe.

Probably wouldn't matter for our purposes?  I guess what we'd need is a
writer's lock that still allows other processes to read the file.

regards, tom lane

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

Re: [HACKERS] 8.5 release timetable, again

2009-08-26 Thread Stephen Frost

* Andrew Dunstan (and...@dunslane.net) wrote:
 Actually, what I had in mind was getting people to run their  
 applications etc. in some non-production environment on the beta. I  
 talked to a client today and he said sure, we have several development  
 environments and we can put one or two on the beta and then let the  
 developers just do their thing on it. Testing the things we know about  
 is in a way less important than making sure nothing else got broken.

I agree entirely with Andrew here- what we need are a set of users who
would be willing to run their actual applications against a beta release
in a testing environment.  The Beta-Mom position would be working with
some list of users who've volunteered to do that; prodding them when a
new beta comes out, poking them for feedback, working with them on
issues they run into, etc.

The other possible group of users are those who are interested and
willing to beta-test actual new external-facing features.  That'd be
great to have as well, but I don't believe is as important.  New
features having bugs are a much smaller impact, overall, than bugs which
have been introduced in existing code-paths due to changes.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] [COMMITTERS] pgsql: Update release notes for 7.4.26, 8.0.22, 8.1.18, 8.2.14, 8.3.8,

2009-08-26 Thread Itagaki Takahiro

Could you fix this bug before release 8.4.1?
I sent a patch, but no response in a week...

[BUGS] fillfactor hides autovacuum parameters in 8.4.0
http://archives.postgresql.org/message-id/20090818185219.9da6.52131...@oss.ntt.co.jp


momj...@postgresql.org (Bruce Momjian) wrote:
 Log Message:
 ---
 Update release notes for 7.4.26, 8.0.22, 8.1.18, 8.2.14, 8.3.8, 8.4.1.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center



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

Re: [HACKERS] [COMMITTERS] pgsql: Update release notes for 7.4.26, 8.0.22, 8.1.18, 8.2.14, 8.3.8,

Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes:
 Could you fix this bug before release 8.4.1?
 I sent a patch, but no response in a week...

I think that one's on Alvaro's queue.

regards, tom lane

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

Re: [HACKERS] MySQL Compatibility WAS: 8.5 release timetable, again

On Thu, Aug 27, 2009 at 1:24 AM, Tom Lanet...@sss.pgh.pa.us wrote:
 Greg Stark gsst...@mit.edu writes:
 Actually it always bothered me that we don't have implicit casts from
 integer-boolean. I can't see any ambiguity or unintentional effects
 this would cause problems with. Am I missing something?

 Personally, as an old Pascal-lover, I always thought that C's failure
 to distinguish between int and boolean was the single biggest design
 mistake in the language.  I'm very glad that SQL doesn't make that
 mistake, and I don't want to go against the standard to introduce it.

I'm sure I can think of bigger flaws in C than that :)

I tend to think SQL has more in common with lisp than either of those,
perhaps because of the tinge of functional programming style.

But I think you're generalizing my suggestion to the point of building
a straw man to say failure to distinguish. You could argue that
using a boolean in places where integers are expected could be
confusing or dangerous. But using other data types where boolean
values are expected is perfectly reasonable and safe -- especially in
cases like integer where people do expect it to work and the behaviour
is very predictable.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] [COMMITTERS] pgsql: Update release notes for 7.4.26, 8.0.22, 8.1.18, 8.2.14, 8.3.8,

Tom Lane wrote:
 Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes:
  Could you fix this bug before release 8.4.1?
  I sent a patch, but no response in a week...
 
 I think that one's on Alvaro's queue.

And, yes, the release notes will be updated before we package the
release, so anything new that is added will be documented as well.

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

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] Logging configuration changes

Peter Eisentraut pete...@gmx.net writes:
 On sÃ¶n, 2009-07-12 at 22:55 +0300, Peter Eisentraut wrote:
 On occasion, I would have found it useful if a SIGHUP didn't only log *that*
 it reloaded the configuration files, but also logged *what* had changed 
 (postgresql.conf changes in particular, not so much interested in 
 pg_hba.conf).  Especially in light of the common mistake of forgetting to 
 uncomment a changed value, this would appear to be useful.

 Looked into this, looks trivial, except that this would log *all*
 parameters that were set as a result of a reload, instead of only the
 ones that changed.  We don't have the information of what it was
 previously readily available.

 I think it would still be useful that way, but some people might find it
 annoying.

Seems to me it would be too chatty to be useful, at least for people who
set more than one or two things in postgresql.conf.  Would it be that
hard to track which values actually changed?  Without having looked at
the code, I'm thinking that much of the infrastructure must be there
already now that we have support for undoing commented-out settings.

regards, tom lane

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

Re: [HACKERS] pretty print viewdefs

Andrew Dunstan and...@dunslane.net writes:
 Tom Lane wrote:
 Well, let's see it?  What do you do with expressions that don't fit?

 See attached.

This isn't going to work as-is, because (a) buf-data can be moved
around by repalloc, and (b) you're not allowing for newlines being
introduced within the column expressions.  You could probably fix it,
but given the lack of consensus for a line-length-based approach, I'm
not sure it's worth putting more effort into.

regards, tom lane

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

Re: [HACKERS] pretty print viewdefs