Re: [HACKERS] WIP: extensible enums

2010-10-13 Thread Robert Haas
On Fri, Oct 1, 2010 at 7:12 AM, Andrew Dunstan and...@dunslane.net wrote:
 On 10/01/2010 04:35 AM, Dean Rasheed wrote:

 2). In enum_ccmp(), when you cache the full list of elements, you're
 not updating mycache-sort_list_length, so it will keep fetching the
 full list each time. Also, I think that function could use a few more
 comments.

 Good catch. Will fix.

 3). I think you need to update psql so that \dT+ returns the enum
 elements in the right order.

 Yeah. Will do.

 I will post a revised patch soon.

Should we postpone this to the next CommitFest?

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

-- 
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] security hook on authorization

2010-10-13 Thread Robert Haas
2010/8/24 KaiGai Kohei kai...@ak.jp.nec.com:
 I tried to revise the patch. It allows plugins to get control next to
 client authentication, but before returning the status to users.

 This change enables plugins which should be invoked on authentication
 failed to utilize this hook, not only assignment of session security
 label.
 At the same time, it disables to hook on SET SESSION AUTHORIZATION.
 But it is a bit unclear whether we should hook here, or not.

Stephen -

You've been listed as a reviewer for this in the CF app since 9/17 -
are you planning to review it?

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

-- 
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] leaky views, yet again

2010-10-13 Thread Robert Haas
2010/10/12 KaiGai Kohei kai...@ak.jp.nec.com:
 I noticed the previous patch has an obvious conflict to the latest
 git mater, and it does not have any documentation updates.

 So, I rebased the patch, and added descriptions about secure views.
 Rest of parts are unchanged.

It seems that we have rough agreement that the existing VIEW feature
is adequate for column filtering but not for row filtering.  The
attack vector is that the planner might reorder quals such that a
value not intended to be visible to the user is passed to a function
which has a side-effect that can expose the value passed to it: either
overtly (as by a user-defined function that writes it to the table or
prints it using RAISE NOTICE) or in some more subtle way (as in the
case where division by zero exposes throws an exception when passed
zero, but not some other value).  With the possible exception of Tom,
everyone seems to agree that it would be a good step forward to
provide a way of plugging these holes, even if it didn't cover subtler
information leaks such as by reading the EXPLAIN output or timing
query execution.

1. Does anyone wish to argue (or continue arguing) that plugging these
more overt information leaks is not worthwhile?

2. Supposing that the answer to question #1 is in the negative, does
anyone wish to argue that this patch as currently written is an
adequate solution to this problem?  It seems obvious to me that it
isn't.

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

-- 
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] Issues with Quorum Commit

2010-10-13 Thread Heikki Linnakangas

On 13.10.2010 08:21, Fujii Masao wrote:

On Sat, Oct 9, 2010 at 4:31 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com  wrote:

It shouldn't be too hard to fix. Walsender needs to be able to read WAL from
preceding timelines, like recovery does, and walreceiver needs to write the
incoming WAL to the right file.


And walsender seems to need to transfer the current timeline history to
the standby. Otherwise, the standby cannot recover the WAL file with new
timeline. And the standby might need to create the timeline history file
in order to recover the WAL file with new timeline even after it's restarted.


Yes, true, you need that too.

It might be good to divide this work into two phases, teaching archive 
recovery to notice new timelines appearing in the archive first, and 
doing the walsender/walreceiver changes after that.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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] Issues with Quorum Commit

2010-10-13 Thread Robert Haas
On Wed, Oct 13, 2010 at 2:43 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
 On 13.10.2010 08:21, Fujii Masao wrote:

 On Sat, Oct 9, 2010 at 4:31 AM, Heikki Linnakangas
 heikki.linnakan...@enterprisedb.com  wrote:

 It shouldn't be too hard to fix. Walsender needs to be able to read WAL
 from
 preceding timelines, like recovery does, and walreceiver needs to write
 the
 incoming WAL to the right file.

 And walsender seems to need to transfer the current timeline history to
 the standby. Otherwise, the standby cannot recover the WAL file with new
 timeline. And the standby might need to create the timeline history file
 in order to recover the WAL file with new timeline even after it's
 restarted.

 Yes, true, you need that too.

 It might be good to divide this work into two phases, teaching archive
 recovery to notice new timelines appearing in the archive first, and doing
 the walsender/walreceiver changes after that.

There's another problem here we should think about, too.  Suppose you
have a master and two standbys.  The master dies.  You promote one of
the standbys, which turns out to be behind the other.  You then
repoint the other standby at the one you promoted.  Congratulations,
your database is now very possible corrupt, and you may very well get
no warning of that fact.  It seems to me that we would be well-advised
to install some kind of bullet-proof safeguard against this kind of
problem, so that you will KNOW that the standby needs to be re-synced.
 I mention this because I have a vague feeling that timelines are
supposed to prevent you from getting different WAL histories confused
with each other, but they don't actually cover all the cases that can
happen.

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

-- 
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] Issues with two-server Synch Rep

2010-10-13 Thread Fujii Masao
On Fri, Oct 8, 2010 at 3:05 AM, Josh Berkus j...@agliodbs.com wrote:
 Adding a Synch Standby
 ---
 What is the procedure for adding a new synchronous standby in your
 implementation?  That is, how do we go from having a standby server with
 an empty PGDATA to having a working synchronous standby?

In my patch, you still have to take a base backup from the master
and start the standby from that.

 Snapshot Publication
 -
 During 9.0 development discussion, one of the things we realized we
 needed for synch standby was publication of snapshots back to the master
 in order to prevent query cancel on the standby.  Without this, the
 synch standby is useless for running read queries.  Does your patch
 implement this?

No. I think that this has almost nothing to do with sync rep itself.

To solve this problem, I think that we should implement the mechanism
like UNDO segment on the standby instead of snapshot publication. That
is, the replay of the VACUUM operation copies the old version of tuple
to somewhere instead of removing it immediately, until all the transactions
which can see that have gone away. Though it would be difficult to
implement this.

 Management
 ---
 One of the serious flaws currently in HS/SR is complexity of
 administration.  Setting up and configuring even a single master and
 single standby requires editing up to 6 configuration files in Postgres,
 as well as dealing with file permissions.  As such, any Synch Rep patch
 must work together with attempts to simplify administration.  How does
 your design do this?

No. What is worse is that my patch introduces new configuration file
standbys.conf.

Aside from the patch, I agree to specify the synchronous standbys in
the existing conf file like postgresql.conf on the master or recovery.conf
on the standby instead of adding new conf file.

 Monitoring
 ---
 Synch rep offers severe penalties to availability if a synch standby
 gets behind or goes down.  What replication-specific monitoring tools
 and hooks are available to allow administators to take action before the
 database becomes unavailable?

Yeah, if you choose recv or fsync as synchronization level, recovery on
the standby might fall behind the master even in sync rep. This delay
would increase the failover time.

To monitor that, you can use pg_current_xlog_location on the master and
pg_last_xlog_replay_location on the standby. My patch hasn't provided
another monitoring mechanism.

 Degradation
 
 In the event that the synch rep standby falls too far behind or becomes
 unavailable, or is deliberately taken offline, what are you envisioning
 as the process for the DBA resolving the situation?  Is there any
 ability to commit stuck transactions?

Since my patch hasn't provided wait-forever option, there is obviously
no capability to resume the transactions which are waiting until the
standby has caught up with.

 Client Consistency
 -
 With a standby in apply mode, and a master failure at the wrong time,
 there is the possibility that the Standby will apply a transaction at
 the same time that the master crashes, causing the client to never
 receive a commit message.  Once the client reconnects to the standby,
 how will it know whether its transaction was committed or not?

This problem can happen even if you don't use replication. So this
should be addressed separately from sync rep.

 As a lesser case, a standby in apply mode will show the results of
 committed transactions *before* they are visible on the master.  Is
 there any need to handle this?  If so, how?

The cluster-side snapshot would be required in order to ensure that
all the standbys return the same result. Export snapshots to other sessions
feature which was proposed in Cluster Developer Meeting is one step for
that, I think.
http://wiki.postgresql.org/wiki/ClusterFeatures

 Performance
 
 As with XA, synch rep has the potential to be so slow as to be unusable.
  What optimizations to you make in your approach to synch rep to make it
 faster than two-phase commit?  What other performance optimizations have
 you added?

To allow walsender to send the WAL records which have not been fsync'd yet
(i.e., WAL is written and sent in parallel) would increase the performance
significantly. Obviously my patch has not provided this improvement.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
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] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread Dimitri Fontaine
Tom Lane t...@sss.pgh.pa.us writes:
 I agree with Josh's proposal: keep mechanically-generated settings in a
 separate file, and don't even pretend to allow comments to be kept there.

And then, when you SET PERMANENT knob TO value (or whatever the syntax
is), you never know what value is selected at next startup or SIGHUP.

I know I'm alone on this, but I much prefer the all-machine-friendly
proposal that still makes it possible to hand-edit the files. You get
remote editing, comments, and the code is *very easy* to write.

The only drawback is that we're not used to it so it might look odd, or
outright ugly. I mean, the directory containing the files that you're
not supposed to edit manually at all any more looks strange. How big a
problem is that, when it allows for implementing the feature easily?

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Issues with Quorum Commit

2010-10-13 Thread Markus Wanner
On 10/13/2010 06:43 AM, Fujii Masao wrote:
 Unfortunately even enough standbys don't increase write-availability
 unless you choose wait-forever. Because, after promoting one of
 standbys to new master, you must keep all the transactions waiting
 until at least one standby has connected to and caught up with new
 master. Currently this wait time is not short.

Why is that? Don't the standbies just have to switch from one walsender
to another? If there's any significant delay in switching, this either
hurts availability or robustness, yes.

 Hmm.. that increases the number of procedures which the users must
 perform at the failover.

I only consider fully automated failover. However, you seem to be
worried about the initial setup of sync rep.

 At least, the users seem to have to wait
 until the standby has caught up with new master, increase quorum_commit
 and then reload the configuration file.

For switching from a single node to a sync replication setup with one or
more standbies, that seems reasonable. There are way more components you
need to setup or adjust in such a case (network, load balancer, alerting
system and maybe even the application itself).

There's really no other option, if you want the kind of robustness
guarantee that sync rep with wait forever provides. OTOH, if you just
replicate to whatever standby is there and don't care much if it isn't,
the admin doesn't need to worry much about quorum_commit - it doesn't
have much of an effect anyway.

Regards

Markus Wanner

-- 
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] Issues with Quorum Commit

2010-10-13 Thread Fujii Masao
On Wed, Oct 13, 2010 at 3:43 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
 On 13.10.2010 08:21, Fujii Masao wrote:

 On Sat, Oct 9, 2010 at 4:31 AM, Heikki Linnakangas
 heikki.linnakan...@enterprisedb.com  wrote:

 It shouldn't be too hard to fix. Walsender needs to be able to read WAL
 from
 preceding timelines, like recovery does, and walreceiver needs to write
 the
 incoming WAL to the right file.

 And walsender seems to need to transfer the current timeline history to
 the standby. Otherwise, the standby cannot recover the WAL file with new
 timeline. And the standby might need to create the timeline history file
 in order to recover the WAL file with new timeline even after it's
 restarted.

 Yes, true, you need that too.

 It might be good to divide this work into two phases, teaching archive
 recovery to notice new timelines appearing in the archive first, and doing
 the walsender/walreceiver changes after that.

OK. In detail,

1. After failover, when the standby connects to new master, walsender transfers
   the current timeline history in the handshake processing.

2. If the timeline history in the master is inconsistent with that in
the standby,
   walreceiver terminates the replication connection.

3. Walreceiver creates the timeline history file.

4. Walreceiver signals the change of timeline history to startup process and
   makes it read the timeline history file. After this, startup process tries
   to recover the WAL files with even new timeline ID.

5. After the handshake, walsender sends the WAL from preceding timelines,
   like recovery does, and walreceiver writes the incoming WAL to the right
   file.

Am I missing something?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
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] Issues with Quorum Commit

2010-10-13 Thread Fujii Masao
On Wed, Oct 13, 2010 at 3:50 PM, Robert Haas robertmh...@gmail.com wrote:
 There's another problem here we should think about, too.  Suppose you
 have a master and two standbys.  The master dies.  You promote one of
 the standbys, which turns out to be behind the other.  You then
 repoint the other standby at the one you promoted.  Congratulations,
 your database is now very possible corrupt, and you may very well get
 no warning of that fact.  It seems to me that we would be well-advised
 to install some kind of bullet-proof safeguard against this kind of
 problem, so that you will KNOW that the standby needs to be re-synced.

Yep. This is why I said it's not easy to implement that.

To start the standby without taking a base backup from new master after
failover, the user basically has to promote the standby which is ahead
of the other standbys (e.g., by comparing pg_last_xlog_replay_location
on each standby).

As the safeguard, we seem to need to compare the location at the switch
of the timeline on the master with the last replay location on the standby.
If the latter location is ahead AND the timeline ID of the standby is not
the same as that of the master, we should emit warning and terminate the
replication connection.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
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] wip: functions median and percentile

2010-10-13 Thread Pavel Stehule
Hello

I am looking on SQL standard for some info about within group
clause. This clause is necessary for functions:

rank, dense_rank, cume_dist, percent_rank and percentile_disc and
persentile_cont. These functions needs a clause WITHIN GROUP.

If I understand, then these functions are not simple aggregates - its
some between window functions and aggregates.

Questions:

* is clause WITHIN GROUP just syntactic sugar for our aggregate with
ORDER BY? I am thinking so not. There are predefined set of functions
that can be used with this clause.

* what is correct implementation of these functions?  When I am
looking on parameters, these functions are very similar to window
functions. So there are two two ways for implementation. Implement it
as special case of window functions or implement it as special case of
aggregates.

Regards

Pavel Stehule

2010/10/12 Hitoshi Harada umi.tan...@gmail.com:
 2010/10/12 Pavel Stehule pavel.steh...@gmail.com:
 Hello

 2010/10/11 Greg Stark gsst...@mit.edu:
 On Sun, Oct 10, 2010 at 2:16 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 It was pointed out upthread that while median isn't presently
 in the standard, Oracle defines it in terms of percentile_cont(0.5)
 which *is* in the standard.

 Uhmm, then why don't we implement that? We could provide median() as a
 short-cut but percentile_cont() doesn't sound much harder to implement
 than median() and more general.

 The problem is in interface. The original patch did it, but I removed
 it. We cannot to unsure immutability of some parameters now. Can we
 enhance a AGGREGATE to allow some mark like IMMUTABLE parameter and
 probably we should to support ANSI syntax:

 PERCENTILE_CONT ( expression1 )
 WITHIN GROUP ( ORDER BY expression2 [ ASC | DESC ] )

 This syntax allows to divide a muttable and immutable parameters.

 If this is only a syntax sugar for mutable/immutable parameter, then I
 guess it's time to take it serious to implement in our syntax,
 although I'm not sure if it affects more execution model than
 interface.

 Regards,



 --
 Hitoshi Harada


-- 
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] wip: functions median and percentile

2010-10-13 Thread Peter Eisentraut
On mån, 2010-10-11 at 20:46 +0200, Pavel Stehule wrote:
 The problem is in interface. The original patch did it, but I removed
 it. We cannot to unsure immutability of some parameters now.

How about you store the immutable parameter in the transition state
and error out if it changes between calls?


-- 
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] wip: functions median and percentile

2010-10-13 Thread Pavel Stehule
2010/10/13 Peter Eisentraut pete...@gmx.net:
 On mån, 2010-10-11 at 20:46 +0200, Pavel Stehule wrote:
 The problem is in interface. The original patch did it, but I removed
 it. We cannot to unsure immutability of some parameters now.

 How about you store the immutable parameter in the transition state
 and error out if it changes between calls?


yes, it's possibility. Now I looking there and I see the as more
important problem the conformance with ANSI SQL. see my last post.
There can be a kind of aggregate functions based on tuplesort.

Regards

Pavel




-- 
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] wip: functions median and percentile

2010-10-13 Thread Pavel Stehule
2010/10/13 Pavel Stehule pavel.steh...@gmail.com:
 2010/10/13 Peter Eisentraut pete...@gmx.net:
 On mån, 2010-10-11 at 20:46 +0200, Pavel Stehule wrote:
 The problem is in interface. The original patch did it, but I removed
 it. We cannot to unsure immutability of some parameters now.

 How about you store the immutable parameter in the transition state
 and error out if it changes between calls?


 yes, it's possibility. Now I looking there and I see the as more
 important problem the conformance with ANSI SQL. see my last post.
 There can be a kind of aggregate functions based on tuplesort.

more - all these functions needs to solve same problem with planner
and hash agg. So maybe is time to add a flag ISTUPLESORTED to pg_proc
and add solve these functions together.

Pavel


 Regards

 Pavel





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


[HACKERS] How to reliably detect if it's a promoting standby

2010-10-13 Thread Tatsuo Ishii
Hi,

I'm looking for a way to reliably detect if it's a promoting
standby. This is neccessary for pgpool-II manage streaming replication
clusters. When primary goes down, standby *could* start promoting to
primary. The only way to find it is calling
pg_is_in_recovery(). Problem is, it returns true even if standby is
promoting. If pgpool-II waits for cetain period, it may finishes
promotion, thus it returns false. But how long should we wait?

Any idea?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

-- 
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] ISN patch that applies cleanly with git apply

2010-10-13 Thread Jan Otto
hi peter,

 I would like to hear what people think of my observations about the
 design of contrib/isn. In particular, I'd like Jan Otto to contribute
 - he probably has more domain knowledge than I do. I haven't heard
 from Jan about the proposed regression test.
 
 In producing this patch, did you work off the listing of all the
 628,000 assigned publisher codes that is only available in book form
 at a cost of €558? How might I verify the correctness of the new
 ISBN_range, preferably without spending €558?:-)

no, i used only the official information from international isbn agency to 
produce this patch. 

Range Message (XML) from http://www.isbn-international.org/page/ranges

we have much more information (payed) about publishers and its prefixes 
internally, but for
hyphenation we dont need more information as provided in the above xml-file.

additionally i put in support for the 979-prefix, because new ranges get 
applied to this new
prefix. as far as i know old ranges will never change.

regards, jan

Re: [HACKERS] ISN patch that applies cleanly with git apply

2010-10-13 Thread Jan Otto
hi tom,

 Peter Eisentraut asked Jan to produce a regression test for the ISN
 contrib module, which he is apparently working on. I would like to see
 him more clearly explaining how that will work though - so far, it's
 really just been described in very broad strokes.
 
 Even more to the point, what about a link to the relevant changes in the
 standard?  It's impossible for anyone to tell whether these changes are
 sane in a vacuum, and a regression test will prove nothing at all except
 perhaps self-consistency.

we can only prove self-consistency, because there is no algorithm behind
the scene. the ranges gets applied to publishers depending on how much
books they publishing over time and probably other criteria.

of course, we can build regression-tests for checkdigits and convert-functions.
e.g. convert an isbn-10 to isbn-13.

regards, jan

-- 
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] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread Robert Haas
On Wed, Oct 13, 2010 at 3:41 AM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
 Tom Lane t...@sss.pgh.pa.us writes:
 I agree with Josh's proposal: keep mechanically-generated settings in a
 separate file, and don't even pretend to allow comments to be kept there.

 And then, when you SET PERMANENT knob TO value (or whatever the syntax
 is), you never know what value is selected at next startup or SIGHUP.

 I know I'm alone on this, but I much prefer the all-machine-friendly
 proposal that still makes it possible to hand-edit the files.

You're not alone on this at all: I agree 100%.  I don't like your
proposed syntax, but I completely agree with your concern.  I don't
see what's wrong with having the initial contents of postgresql.conf
look like this (these are the settings that are uncommented by default
on my machine):

# type man postgresql.conf for help on editing this file
max_connections = 100
shared_buffers = 32MB
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

When you type 'man postgresql.conf' it can tell you all of the things
that are currently in the file as comments.  It's just that they'll be
in a man page rather in the file itself.  I don't see what's bad about
that.

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

-- 
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] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

2010-10-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 I spent some time hacking on this.  It doesn't appear to be too easy
 to get levenshtein_less_equal() working without slowing down plain old
 levenshtein() by about 6%.

Is that really enough slowdown to be worth contorting the code to avoid?
I've never heard of an application where the speed of this function was
the bottleneck.

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] leaky views, yet again

2010-10-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 With the possible exception of Tom,
 everyone seems to agree that it would be a good step forward to
 provide a way of plugging these holes, even if it didn't cover subtler
 information leaks such as by reading the EXPLAIN output or timing
 query execution.

 1. Does anyone wish to argue (or continue arguing) that plugging these
 more overt information leaks is not worthwhile?

Yeah, I will.  Plugging an overt information leak without plugging
other channels in the same area isn't a security improvement.  It's
merely PR, and rather lame PR at that.  An attacker is not bound to
use only the attack methods you'd like him to.

This would only be a security improvement if there were plausible attack
scenarios in which the attacker would have access to the plugged channel
and not access to the other known channels.  Now, perhaps that's the
case, but no one has put forward an argument showing it.  I think the
burden of proof is on those who favor the patch to put forward that
argument, not for those who don't favor it to try to prove that no such
scenario exists.

 2. Supposing that the answer to question #1 is in the negative, does
 anyone wish to argue that this patch as currently written is an
 adequate solution to this problem?  It seems obvious to me that it
 isn't.

In that case, one's opinion about #1 hardly matters does 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] Issues with two-server Synch Rep

2010-10-13 Thread Peter Eisentraut
On mån, 2010-10-11 at 18:44 -0700, Greg Stark wrote:
 So we've been over this. All the pieces you need are already there:
 you can handle this without any nasty comment grunging by just writing
 the new setting to a postgresql.auto and including that from
 postgresql.conf. Include a note in postgresql.auto warning users any
 changes in this file will be thrown away when the file is rewritten.
 This is the same method used in .emacs.custom or a million other
 places people wanted automatically written config files.

Seems like a reasonable solution, although I've never heard
of .emacs.custom; my emacsen have always written their custom settings
somewhere in the middle of the .emacs proper.


-- 
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] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

2010-10-13 Thread Alvaro Herrera
Excerpts from Tom Lane's message of mié oct 13 10:32:36 -0300 2010:
 Robert Haas robertmh...@gmail.com writes:
  I spent some time hacking on this.  It doesn't appear to be too easy
  to get levenshtein_less_equal() working without slowing down plain old
  levenshtein() by about 6%.
 
 Is that really enough slowdown to be worth contorting the code to avoid?
 I've never heard of an application where the speed of this function was
 the bottleneck.

What if it's used on a expression index on a large table?

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 You're not alone on this at all: I agree 100%.  I don't like your
 proposed syntax, but I completely agree with your concern.  I don't
 see what's wrong with having the initial contents of postgresql.conf
 look like this (these are the settings that are uncommented by default
 on my machine):

 # type man postgresql.conf for help on editing this file
 max_connections = 100
 shared_buffers = 32MB
 datestyle = 'iso, mdy'
 lc_messages = 'en_US.UTF-8'
 lc_monetary = 'en_US.UTF-8'
 lc_numeric = 'en_US.UTF-8'
 lc_time = 'en_US.UTF-8'
 default_text_search_config = 'pg_catalog.english'

I'm not sure if anybody is particularly against the initial contents
looking like that.  The big problem, which both you and Dimitri are
conveniently ignoring, is that if people are allowed to hand-edit
the file they are going to introduce comments that no mechanical
parser will do a nice job of preserving.  And they're not going to be
happy when SET PERMANENT has a side-effect of throwing away their
comments.

I don't see anything particularly wrong with Josh's proposal of keeping
machine-generated and person-generated text in separate files.  Dimitri
complains that the behavior will be confusing if there are conflicting
settings, but I think that's hogwash.  We already have the ability for
pg_settings to tell you which file, and even which line, set the active
value of the setting.  It's not going to be hard for people to figure
that out.

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] Extensions, this time with a patch

2010-10-13 Thread David Fetter
On Tue, Oct 12, 2010 at 08:57:09PM +0200, Dimitri Fontaine wrote:
 Hi,
 
 Please find attached a WIP patch for extension's pg_dump support in
 PostgreSQL, following design decisions that we've outlined earlier at
 this year's and last year's PGCon developer meetings.
 
 What's in the patch?
 
 An extension is a new SQL object with a catalog and two commands to
 manage them (reserved to superuser):
 
   CREATE EXTENSION extension ;
   DROP EXTENSION [IF EXISTS] extension [ RESTRICT | CASCADE ];

Kudos!

  - User Documentation.  Where in the manual do I write it?

Parts belong in Server Administration, others in Server Programming.

  - Extension Upgrading
 
Should this be done by means of 'create extension' or some other
command, like 'alter extension foo upgrade'? The command would
run the SQL script again, which would be responsible for any
steps the extension author might find necessary to run.

As people will want to up- or downgrade extensions to a particular
version, this should probably be something more like ALTER EXTENSION
... SET VERSION [version number | LATEST | PREVIOUS ]... or something
like that.

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
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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] leaky views, yet again

2010-10-13 Thread KaiGai Kohei
(2010/10/13 22:43), Tom Lane wrote:
 Robert Haasrobertmh...@gmail.com  writes:
 With the possible exception of Tom,
 everyone seems to agree that it would be a good step forward to
 provide a way of plugging these holes, even if it didn't cover subtler
 information leaks such as by reading the EXPLAIN output or timing
 query execution.
 
 1. Does anyone wish to argue (or continue arguing) that plugging these
 more overt information leaks is not worthwhile?
 
 Yeah, I will.  Plugging an overt information leak without plugging
 other channels in the same area isn't a security improvement.  It's
 merely PR, and rather lame PR at that.  An attacker is not bound to
 use only the attack methods you'd like him to.
 
It seems to me an extreme opinion, and different from the standard
point of security view.

It is a quotation from the classic of security evaluation criteria.
Trusted Computer System Evaluation Criteria (TCSEC, DoD) says in
the chapter of A GUIDELINE ON COVERT CHANNELS as follows:

http://csrc.nist.gov/publications/history/dod85.pdf
| From a security perspective, covert channels with low bandwidths represent a
| lower threat than those with high bandwidths. However, for many types of
| covert channels, techniques used to reduce the bandwidth below a certain rate
| (which depends on the specific channel mechanism and the system architecture)
| also have the effect of degrading the performance provided to legitimate
| system users. Hence, a trade-off between system performance and covert
| channel bandwidth must be made

The overt channels has a capability to leak massive invisible information,
so we need to consider them as a serious threat to be fixed up in higher
priority.
However, it is doubtful whether the rest of channels provides enough
bandwidth as actual threat. It also means degree of the threat is
relatively small than the overt channels.

Previous security researcher pointed out security is trading-off,
not all-or-nothing. If we can plug most part of the threat with
reasonable performance degrading, it is worthwhile to fix up.

Thanks,
-- 
KaiGai Kohei kai...@kaigai.gr.jp

-- 
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] Extensions, this time with a patch

2010-10-13 Thread Alvaro Herrera
Excerpts from David Fetter's message of mié oct 13 11:27:56 -0300 2010:
 On Tue, Oct 12, 2010 at 08:57:09PM +0200, Dimitri Fontaine wrote:

   - Extension Upgrading
  
 Should this be done by means of 'create extension' or some other
 command, like 'alter extension foo upgrade'? The command would
 run the SQL script again, which would be responsible for any
 steps the extension author might find necessary to run.
 
 As people will want to up- or downgrade extensions to a particular
 version, this should probably be something more like ALTER EXTENSION
 ... SET VERSION [version number | LATEST | PREVIOUS ]... or something
 like that.

Does this mean that the control file should contain a version number in
the filename?  Otherwise, I don't see how you'd have more than one
version of the control file.

Also, if upgrading is necessary, there will need to be one upgrade
control file that says how to upgrade from version N to N+1.

I don't think we should really support the downgrade case.  It has the
potential to get too messy -- and for what gain?

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

2010-10-13 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes:
 Excerpts from Tom Lane's message of mié oct 13 10:32:36 -0300 2010:
 Robert Haas robertmh...@gmail.com writes:
 I spent some time hacking on this.  It doesn't appear to be too easy
 to get levenshtein_less_equal() working without slowing down plain old
 levenshtein() by about 6%.
 
 Is that really enough slowdown to be worth contorting the code to avoid?
 I've never heard of an application where the speed of this function was
 the bottleneck.

 What if it's used on a expression index on a large table?

So?  Expression indexes don't result in multiple evaluations of the
function.  If anything, that context would probably be even less
sensitive to the function runtime than non-index use.

But the main point is that 6% performance penalty in a non-core function
is well below my threshold of pain.  If it were important enough to care
about that kind of performance difference, it'd be in core.  I'd rather
see us keeping the code simple, short, and maintainable.

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] Issues with two-server Synch Rep

2010-10-13 Thread Simon Riggs
On Thu, 2010-10-07 at 11:05 -0700, Josh Berkus wrote:
 Simon, Fujii,
 
 What follows are what I see as the major issues with making two-server
 synch replication work well.  I would like to have you each answer them,
 explaining how your patch and your design addresses each issue.  I
 believe this will go a long way towards helping the majority of the
 community understand the options we have from your code, as well as
 where help is still needed.

Happy to answer your questions. Please add me to the copy list if you
address me directly.

 Adding a Synch Standby
 ---
 What is the procedure for adding a new synchronous standby in your
 implementation?  That is, how do we go from having a standby server with
 an empty PGDATA to having a working synchronous standby?

Same as adding a streaming standby.

Only difference is that *if* you don't want standby to be a synch
standby then you would set 
synchronous_replication_service = off

My understanding is that other approaches are significantly more complex
at this point, with required changes on the master, and also on the
standby should we wish the standby to be a failover target.

 Snapshot Publication
 -
 During 9.0 development discussion, one of the things we realized we
 needed for synch standby was publication of snapshots back to the master
 in order to prevent query cancel on the standby.  Without this, the
 synch standby is useless for running read queries.  

Don't see much difference there. 

This isn't isn't needed for sync rep. It can be added, as soon as we
have a channel to pass info back from standby to master. That is a small
commit that can be added after we commit something; I will handle that -
it is a requirement that will be addressed.

 Does your patch
 implement this?  Please describe.

No, but that isn't needed for sync rep.

 Management
 ---
 One of the serious flaws currently in HS/SR is complexity of
 administration.  Setting up and configuring even a single master and
 single standby requires editing up to 6 configuration files in Postgres,
 as well as dealing with file permissions.  As such, any Synch Rep patch
 must work together with attempts to simplify administration.  How does
 your design do this?

Simplification of the existing framework is possible, though is not a
goal of sync rep. My proposed approach is to add as few mandatory
parameters as possible to avoid over-complexity.

Complexity of administration is very important, because getting it wrong
has a critical impact on availability and can lead to data loss.

In the two node case this post covers, my patch requires 1 parameter,
added to the existing postgresql.conf on the master. That parameter does
not need to be changed should failover occur. So no parameter changes
are required at failover, nor can mistakes happen because of
misconfiguration.

 Monitoring
 ---
 Synch rep offers severe penalties to availability if a synch standby
 gets behind or goes down.  What replication-specific monitoring tools
 and hooks are available to allow administators to take action before the
 database becomes unavailable?

I don't see any differences here. It's easy to add an SRF that shows
current status of standbys.

 Degradation
 
 In the event that the synch rep standby falls too far behind or becomes
 unavailable, or is deliberately taken offline, what are you envisioning
 as the process for the DBA resolving the situation?  

Add a new standby as quickly as possible. This only happens if the DBA
had not provided sufficient standbys in the first place.

 Is there any
 ability to commit stuck transactions?

Yes, an operator function.

 Client Consistency
 -
 With a standby in apply mode, and a master failure at the wrong time,
 there is the possibility that the Standby will apply a transaction at
 the same time that the master crashes, causing the client to never
 receive a commit message.  Once the client reconnects to the standby,
 how will it know whether its transaction was committed or not?

It wouldn't, but this situation already occurs even without sync rep.
Any user issuing COMMIT at time of server crash may that their
transaction was committed and they received no commit message. There is
no tell me if the last thing I did worked function, since the client
doesn't record the xid.

 As a lesser case, a standby in apply mode will show the results of
 committed transactions *before* they are visible on the master.  Is
 there any need to handle this?  If so, how?

No need to handle it. It's how it works. As long as there are more than
one clog then we will have commits happening at different times.

 Performance
 
 As with XA, synch rep has the potential to be so slow as to be unusable.
  What optimizations to you make in your approach to synch rep to make it
 faster than two-phase commit?  What other performance optimizations have
 you added?

Applications 

Re: [HACKERS] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread Andrew Dunstan



On 10/13/2010 10:25 AM, Tom Lane wrote:

Robert Haasrobertmh...@gmail.com  writes:

You're not alone on this at all: I agree 100%.  I don't like your
proposed syntax, but I completely agree with your concern.  I don't
see what's wrong with having the initial contents of postgresql.conf
look like this (these are the settings that are uncommented by default
on my machine):
# type man postgresql.conf for help on editing this file
max_connections = 100
shared_buffers = 32MB
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

I'm not sure if anybody is particularly against the initial contents
looking like that.  The big problem, which both you and Dimitri are
conveniently ignoring, is that if people are allowed to hand-edit
the file they are going to introduce comments that no mechanical
parser will do a nice job of preserving.  And they're not going to be
happy when SET PERMANENT has a side-effect of throwing away their
comments.

I don't see anything particularly wrong with Josh's proposal of keeping
machine-generated and person-generated text in separate files.  Dimitri
complains that the behavior will be confusing if there are conflicting
settings, but I think that's hogwash.  We already have the ability for
pg_settings to tell you which file, and even which line, set the active
value of the setting.  It's not going to be hard for people to figure
that out.


+1. Preserving the comments when you change the value could make the 
comments totally bogus. Separating machine-generated values into a 
separate file makes plenty of sense to me.


Which one wins, though? I can see cases being made for both.

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] Issues with two-server Synch Rep

2010-10-13 Thread Simon Riggs
On Mon, 2010-10-11 at 11:07 -0700, Josh Berkus wrote:

 Absolutely.  For a synch standby, you can't tolerate any standby delay
 at all.  This means that anywhere from 1/4 to 3/4 of queries on the
 standby would be cancelled on any high-traffic OLTP server.  Hence,
 useless.

Don't agree with your numbers there and you seem to be assuming no
workarounds would be in use. A different discussion, I think.

 Interaction?  My opinion is that the two are completely incompatible.
 You can't have synch rep and also have standby_delay  0.

I would agree that adding an apply mode only makes sense when we have
master feedback to ensure that standby delay is minimised. But that's
not the only use case for sync rep and it doesn't actually help that
much. 

Adding the feedback channel looks trivial to me, once we've got the main
sync rep patch in. I'll handle that.

For this reason, I've removed the apply mode from my patch, for now. I
want to get the simplest possible patch agreed and then add things
later.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


-- 
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] Extensions, this time with a patch

2010-10-13 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes:
 Also, if upgrading is necessary, there will need to be one upgrade
 control file that says how to upgrade from version N to N+1.

 I don't think we should really support the downgrade case.  It has the
 potential to get too messy -- and for what gain?

I think we could leave that to the extension author to decide.  Basically,
what is needed is a script file that says how to replace version M by
version N.  If the author cares to supply a script for going from
M to M-1, it's no extra logic in the extension manager to be able to
apply that one.  In many cases it wouldn't be very practical to do,
but then you just don't offer the script.

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] ISN patch that applies cleanly with git apply

2010-10-13 Thread Peter Geoghegan
On 13 October 2010 13:45, Jan Otto as...@me.com wrote:
 we can only prove self-consistency, because there is no algorithm behind
 the scene. the ranges gets applied to publishers depending on how much
 books they publishing over time and probably other criteria.

What about the issue I raised about new ranges coming into use in the
future? That isn't made any worse by your patch, but I'd like to hear
your thoughts on that.

 of course, we can build regression-tests for checkdigits and 
 convert-functions.
 e.g. convert an isbn-10 to isbn-13.

Nothing has changed there. The ISBN-13 checkdigit is the same as
EAN-13 checkdigit (after all, the ISBN-13 is an EAN-13), and the
conversion from ISBN-10 to 13 just involves taking away the bookland
country code (first 3 digits) and changing the checkdigit (last
digit).

Although that's fairly simple, I'd like to hear in more detail how the
regression test will work.

I'd also like to establish just how sensible it is for us to attempt
to hyphenate ISBN-13s. After all, the XML file you linked to has a
timestamp from just two days ago:

MessageDateMon, 11 Oct 2010 15:56:34 GMT/MessageDate

Maybe it's too late for that though.

-- 
Regards,
Peter Geoghegan

-- 
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] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

2010-10-13 Thread Robert Haas
On Wed, Oct 13, 2010 at 10:51 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Alvaro Herrera alvhe...@commandprompt.com writes:
 Excerpts from Tom Lane's message of mié oct 13 10:32:36 -0300 2010:
 Robert Haas robertmh...@gmail.com writes:
 I spent some time hacking on this.  It doesn't appear to be too easy
 to get levenshtein_less_equal() working without slowing down plain old
 levenshtein() by about 6%.

 Is that really enough slowdown to be worth contorting the code to avoid?
 I've never heard of an application where the speed of this function was
 the bottleneck.

 What if it's used on a expression index on a large table?

 So?  Expression indexes don't result in multiple evaluations of the
 function.  If anything, that context would probably be even less
 sensitive to the function runtime than non-index use.

 But the main point is that 6% performance penalty in a non-core function
 is well below my threshold of pain.  If it were important enough to care
 about that kind of performance difference, it'd be in core.  I'd rather
 see us keeping the code simple, short, and maintainable.

Well, then you have to wonder whether it's worth having the
lesss-than-or-equal-to version around at all.  That's only about 2x
faster on the same test case.   I do think it's likely that people who
call this function will call it many times, however - e.g. trying to
find the closest matches from a dictionary for a given input string,
so the worry about performance doesn't seem totally out of place.

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

-- 
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] leaky views, yet again

2010-10-13 Thread Kevin Grittner
KaiGai Kohei kai...@kaigai.gr.jp wrote:
 
 Previous security researcher pointed out security is trading-off,
 not all-or-nothing. If we can plug most part of the threat with
 reasonable performance degrading, it is worthwhile to fix up.
 
I had the pleasure of hearing Admiral Grace Hopper[1] speak at an
ACM luncheon once.  When she discussed security, she asserted that
there was no such thing as security which could not be breached. 
The goal of security efforts should not be to make it perfect,
because you can't; any time you convince yourself you have that you
are simply fooling yourself and missing the vulnerabilities.  In her
view the goal was to make the costs of breaching security higher to
the perpetrator than the benefits.  Each obstacle in their way helps
tip the scales in your favor.
 
-Kevin
 
http://en.wikipedia.org/wiki/Grace_Hopper
 


-- 
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] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 +1. Preserving the comments when you change the value could make the 
 comments totally bogus. Separating machine-generated values into a 
 separate file makes plenty of sense to me.

 Which one wins, though? I can see cases being made for both.

IIRC the proposal was that postgresql.conf (the people-editable file)
would have include postgresql.auto in it.  You could put that at
the top, bottom, or even middle to control how the priority works.
So it's user-configurable.  I think the factory default ought to
be to have it at the top, which would result in manually edited
settings (if any) overriding SET PERMANENT.

Basically the way I'd like to see this go is that SET PERMANENT
gets attached on the side of what's there now, and people who are
used to the old way don't have to change their habits at all.
If the new way is as much better as its advocates claim, use of
manual editing of postgresql.conf will soon die off; then at some
future date we could consider whether to remove that file or at
least delete all the comments it contains out-of-the-box.  About
the only change I want to make immediately is that initdb ought
to shove its settings into postgresql.auto instead of mucking with
postgresql.conf.

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] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

2010-10-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Wed, Oct 13, 2010 at 10:51 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 But the main point is that 6% performance penalty in a non-core function
 is well below my threshold of pain.

 Well, then you have to wonder whether it's worth having the
 lesss-than-or-equal-to version around at all.  That's only about 2x
 faster on the same test case.

Same test case?  I thought they did different things?

 I do think it's likely that people who
 call this function will call it many times, however - e.g. trying to
 find the closest matches from a dictionary for a given input string,
 so the worry about performance doesn't seem totally out of place.

No doubt, but the actual function runtime is only one component of the
cost of applying it to a lot of dictionary entries --- I would think
that the table read costs are the larger component anyway.

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] leaky views, yet again

2010-10-13 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes:
 I had the pleasure of hearing Admiral Grace Hopper[1] speak at an
 ACM luncheon once.  When she discussed security, she asserted that
 there was no such thing as security which could not be breached. 
 The goal of security efforts should not be to make it perfect,
 because you can't; any time you convince yourself you have that you
 are simply fooling yourself and missing the vulnerabilities.  In her
 view the goal was to make the costs of breaching security higher to
 the perpetrator than the benefits.  Each obstacle in their way helps
 tip the scales in your favor.

That's all true, but you have to consider how much the obstacle actually
gets in their way versus how painful it is on your end to create and
maintain the obstacle.  I don't think this proposed patch measures up
very well on either end of that tradeoff.

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] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread David Christensen

On Oct 13, 2010, at 10:24 AM, Tom Lane wrote:

 Andrew Dunstan and...@dunslane.net writes:
 +1. Preserving the comments when you change the value could make the 
 comments totally bogus. Separating machine-generated values into a 
 separate file makes plenty of sense to me.
 
 Which one wins, though? I can see cases being made for both.
 
 IIRC the proposal was that postgresql.conf (the people-editable file)
 would have include postgresql.auto in it.  You could put that at
 the top, bottom, or even middle to control how the priority works.
 So it's user-configurable.  I think the factory default ought to
 be to have it at the top, which would result in manually edited
 settings (if any) overriding SET PERMANENT.

Since this is just touching the local servers' postgresql.conf.auto (or 
whatever), any reason why SET PERMANENT couldn't be used on a read-only 
standby?  Could this be to manage some of the failover scenarios (i.e., setting 
any relevant config from a central clusterware|whatever)?

Regards,

David
--
David Christensen
End Point Corporation
da...@endpoint.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] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread Dimitri Fontaine
Tom Lane t...@sss.pgh.pa.us writes:
 I'm not sure if anybody is particularly against the initial contents
 looking like that.  The big problem, which both you and Dimitri are
 conveniently ignoring, is that if people are allowed to hand-edit
 the file they are going to introduce comments that no mechanical
 parser will do a nice job of preserving.

IMO the only reason why my proposal is sound is that is address the
point. Consider:

cat postgresql.conf.d/work_mem
16MB
This database needs at least such a value.
Note it's overridden in some ROLEs setup.


With such a format (name is filename, value is first line content's,
rest is comments), it's easy to preserve comments and have them machine
editable. What do I miss?

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Extensions, this time with a patch

2010-10-13 Thread David Fetter
On Wed, Oct 13, 2010 at 11:36:02AM -0300, Alvaro Herrera wrote:
 Excerpts from David Fetter's message of mié oct 13 11:27:56 -0300 2010:
  On Tue, Oct 12, 2010 at 08:57:09PM +0200, Dimitri Fontaine wrote:
 
- Extension Upgrading
   
  Should this be done by means of 'create extension' or some other
  command, like 'alter extension foo upgrade'? The command would
  run the SQL script again, which would be responsible for any
  steps the extension author might find necessary to run.
  
  As people will want to up- or downgrade extensions to a particular
  version, this should probably be something more like ALTER EXTENSION
  ... SET VERSION [version number | LATEST | PREVIOUS ]... or something
  like that.
 
 Does this mean that the control file should contain a version number in
 the filename?  Otherwise, I don't see how you'd have more than one
 version of the control file.

Excellent idea!

 Also, if upgrading is necessary, there will need to be one upgrade
 control file that says how to upgrade from version N to N+1.

This, too, is an excellent idea.

 I don't think we should really support the downgrade case.  It has
 the potential to get too messy -- and for what gain?

I think there should be something extension authors should be able to
provide for the downgrade case, given that an upgrade could cause a
system-wide failure.

Unfortunately, the extensions not provided with a downgrade option are
the ones likeliest to need it.  Authors who write and test downgrade
code are much less likely to have their upgrades cause such failures
in the first place, but there's not much to do about that.

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
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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] leaky views, yet again

2010-10-13 Thread Robert Haas
On Wed, Oct 13, 2010 at 9:43 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 With the possible exception of Tom,
 everyone seems to agree that it would be a good step forward to
 provide a way of plugging these holes, even if it didn't cover subtler
 information leaks such as by reading the EXPLAIN output or timing
 query execution.

 1. Does anyone wish to argue (or continue arguing) that plugging these
 more overt information leaks is not worthwhile?

 Yeah, I will.  Plugging an overt information leak without plugging
 other channels in the same area isn't a security improvement.It's
 merely PR, and rather lame PR at that.  An attacker is not bound to
 use only the attack methods you'd like him to.

You may as well argue that I shouldn't bother locking the doors to my
house because a determined attacker could simply break the windows.
They certainly could, and yet I am altogether convinced that a habit
of locking my doors when I am away reduces the chances that my house
will be burgled.  Breaking the windows would be altogether more
obvious and more likely to be result in the police being summoned.
Locking the doors won't protect me against someone who is bound and
determined to rob my house specifically, but it provides fairly good
protection against someone who walks around the neighborhood and robs
each unlocked house, which is not an unrealistic threat model.  But
let us suppose that I went a step further and purchased the best
burglar alarm money can buy, reinforced steel doors, and an expensive
alarm monitoring service.  Further, let us suppose that I retained a
24x7 armed guard.  This would likely be a waste of money because
there's not a whole lot in my house worth stealing (and if there were
I wouldn't post the details to a public mailing list) but let us
suppose that I did it anyway.  I would now be about as secure against
burglary as one can hope to be, and yet I'm still pretty sure that the
CIA could manage to clandestinely remove something from my home
against my will, were they of a mind to do so.

In other words - true, an attacker isn't bound to use only the attack
methods I'd like him to - but on the other hand, I'm not bound to care
about protecting myself against every type of attack.  I set a goal
for what level of security I'd like to achieve, and then I try to meet
it.  You seem to believe that being able to infer the total size of a
table or the frequency of some particular key in the table is
equivalent to being able to trivially read every row of it.  That
seems off the wall to me, and I'd like to see you justify that
position.  I and others have already posted examples of situations
where this is not the case, such as my example of allowing sales reps
to view only their own customers.  I believe Kevin has posted similar
examples: the number of cases is not a secret, but details of
individual ones may be.  These cases are taken from real business
situations and I don't understand, what, if anything, you find
implausible about them.

http://archives.postgresql.org/pgsql-hackers/2010-10/msg00299.php

 This would only be a security improvement if there were plausible attack
 scenarios in which the attacker would have access to the plugged channel
 and not access to the other known channels.  Now, perhaps that's the
 case, but no one has put forward an argument showing it.  I think the
 burden of proof is on those who favor the patch to put forward that
 argument, not for those who don't favor it to try to prove that no such
 scenario exists.

I don't favor this particular patch, but I think you're the only
person arguing that there is no subset of this problem which is both
tractable and useful.  As far as I can tell, Heikki, KaiGai, Stephen
Frost, Kevin Grittner, and myself are all on approximately the same
page about where a meaningful dividing line can be drawn.  The
question is not really whether the attacker would have access to the
un-plugged channels but how much and what type of information they
actually leak.  AIUI, the vectors that the proposed approach doesn't
block are basically EXPLAIN and query response times.  What can you
infer from those?  AFAICS, you're not going to be able to do better
than inferring whether a given value is an MCV, which is of a totally
different order than the wholesale data leakage which can be trivially
created using today's system.  And even that can be made much harder
by blocking EXPLAIN, which can be done quite easily using
ProcessUtility_hook, and therefore doesn't need to be addressed by the
patch.  But even suppose someone can reliably infer MCVs.  It does not
follow that because I'm worried about someone enumerating the contents
of my entire table that I am also worried about them learning the MCVs
of those columns which have non-unique indices.

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

-- 
Sent via pgsql-hackers mailing list 

Re: [HACKERS] leaky views, yet again

2010-10-13 Thread Robert Haas
On Wed, Oct 13, 2010 at 11:45 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Kevin Grittner kevin.gritt...@wicourts.gov writes:
 I had the pleasure of hearing Admiral Grace Hopper[1] speak at an
 ACM luncheon once.  When she discussed security, she asserted that
 there was no such thing as security which could not be breached.
 The goal of security efforts should not be to make it perfect,
 because you can't; any time you convince yourself you have that you
 are simply fooling yourself and missing the vulnerabilities.  In her
 view the goal was to make the costs of breaching security higher to
 the perpetrator than the benefits.  Each obstacle in their way helps
 tip the scales in your favor.

 That's all true, but you have to consider how much the obstacle actually
 gets in their way versus how painful it is on your end to create and
 maintain the obstacle.  I don't think this proposed patch measures up
 very well on either end of that tradeoff.

I think it would behoove us to try to separate concerns about this
particular patch from concerns about the viability of the whole
approach.  Whether or not it's useful to do X is a different question
than whether it can be done with few enough lines of code and/or
whether this patch actually does it well.

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

-- 
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] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

2010-10-13 Thread Andres Freund
On Wednesday 13 October 2010 16:18:01 Alvaro Herrera wrote:
 Excerpts from Tom Lane's message of mié oct 13 10:32:36 -0300 2010:
  Robert Haas robertmh...@gmail.com writes:
   I spent some time hacking on this.  It doesn't appear to be too easy
   to get levenshtein_less_equal() working without slowing down plain old
   levenshtein() by about 6%.
  
  Is that really enough slowdown to be worth contorting the code to avoid?
  I've never heard of an application where the speed of this function was
  the bottleneck.
 
 What if it's used on a expression index on a large table?
Its hard to use it as an sensible expression index, given that you use it to 
calculate difference between two strings.

Whats more important is, that its used for sorting the results of a query - 
where its more important that its fast.

Andres

-- 
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] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread Robert Haas
On Wed, Oct 13, 2010 at 10:25 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 You're not alone on this at all: I agree 100%.  I don't like your
 proposed syntax, but I completely agree with your concern.  I don't
 see what's wrong with having the initial contents of postgresql.conf
 look like this (these are the settings that are uncommented by default
 on my machine):

 # type man postgresql.conf for help on editing this file
 max_connections = 100
 shared_buffers = 32MB
 datestyle = 'iso, mdy'
 lc_messages = 'en_US.UTF-8'
 lc_monetary = 'en_US.UTF-8'
 lc_numeric = 'en_US.UTF-8'
 lc_time = 'en_US.UTF-8'
 default_text_search_config = 'pg_catalog.english'

 I'm not sure if anybody is particularly against the initial contents
 looking like that.  The big problem, which both you and Dimitri are
 conveniently ignoring, is that if people are allowed to hand-edit
 the file they are going to introduce comments that no mechanical
 parser will do a nice job of preserving.  And they're not going to be
 happy when SET PERMANENT has a side-effect of throwing away their
 comments.

But creating a separate file doesn't fix that problem.  It just moves
it around.  If people will expect comments in postgresql.conf to get
preserved, then why won't they also expect comments in
postgresql.conf.auto to get preserved?

If the answer is because postgresql.conf has always worked that way
before, then add one more line to the proposed initial contents
saying it's not true any more.  It can be the same line you were going
to put in postgresql.conf.auto explaining the same fact.

# The SQL command SET PERMANENT rewrites this file and may move or
remove comment lines.

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

-- 
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] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

2010-10-13 Thread Robert Haas
On Wed, Oct 13, 2010 at 11:42 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Wed, Oct 13, 2010 at 10:51 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 But the main point is that 6% performance penalty in a non-core function
 is well below my threshold of pain.

 Well, then you have to wonder whether it's worth having the
 lesss-than-or-equal-to version around at all.  That's only about 2x
 faster on the same test case.

 Same test case?  I thought they did different things?

levenshtein_less_equal(a, b, max_d) returns the same value as
levenshtein(a, b) if levenshtein(a, b) = max_d.  Otherwise it returns
max_d + 1.  So it's the same test case with a small distance bound (2)
applied.  As Alexander says, the value of levenshtein_less_equal
accelerates pretty rapidly when long strings are involved, so it seems
worth having, but I'm not sure I agree that the slowdown to the basic
function is negligible.  It is not really all that much #ifdef hackery
to avoid it.

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

-- 
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] leaky views, yet again

2010-10-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Wed, Oct 13, 2010 at 11:45 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 That's all true, but you have to consider how much the obstacle actually
 gets in their way versus how painful it is on your end to create and
 maintain the obstacle.  I don't think this proposed patch measures up
 very well on either end of that tradeoff.

 I think it would behoove us to try to separate concerns about this
 particular patch from concerns about the viability of the whole
 approach.  Whether or not it's useful to do X is a different question
 than whether it can be done with few enough lines of code and/or
 whether this patch actually does it well.

I think I left the wrong impression: I'm concerned about the whole
approach.  I haven't even read this particular patch lately.  I think
that trying to guarantee that the planner applies independent
constraints in a particular order will be expensive, fragile, and prone
to recurring security bugs no matter how it's implemented --- unless you
do it by lobotomizing query pullup/pushdown, which seems unacceptable
from a performance standpoint.

Just to give one example of what this patch misses (probably; as I said
I haven't read it lately), what about selectivity estimation?  One of
the things we like to do when we have an otherwise-unknown function is
to try it on all the histogram members and see what percentage yield
true.  That might already represent enough of an information leak to be
objectionable ... and yet, if we don't do it, the consequences for
performance could be pretty horrid because we'll have to work without
any reasonable selectivity estimate at all.  There are cases where this
technique isn't applied at the moment but probably should be, which is
why I characterize the leak-prevention idea as creating future security
issues: doing that is a constraint that will have to be accounted for in
every future planner change, and we are certain to miss the implications
sometimes.

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] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 But creating a separate file doesn't fix that problem.  It just moves
 it around.  If people will expect comments in postgresql.conf to get
 preserved, then why won't they also expect comments in
 postgresql.conf.auto to get preserved?

Because postgresql.conf.auto will have a nice leading comment telling
people (1) not to hand-edit the file, (2) if they do so anyway,
not to expect comments to be preserved, and (3) the place to do manual
editing of settings is postgresql.conf.

 If the answer is because postgresql.conf has always worked that way
 before, then add one more line to the proposed initial contents
 saying it's not true any more.

Sorry, wrong answer.  The objection to this is not whether you tell
people that you're taking away the ability to keep useful comments
in postgresql.conf, it's that you're taking away the ability.

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] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

2010-10-13 Thread Alexander Korotkov
 No doubt, but the actual function runtime is only one component of the
 cost of applying it to a lot of dictionary entries --- I would think
 that the table read costs are the larger component anyway.

Data domain can be not only dictionary but also something like article
titles, urls and so on. On such relatively long strings (about 100
characters and more) this component will be significant (especially if most
part of the table is lying in cache). In this case search of near strings
can be accelerated in more than 10 times. I think that this use case
justifies presence of separate leveshtein_less_equal function.


With best regards,
Alexander Korotkov.


Re: [HACKERS] levenshtein_less_equal (was: multibyte charater set in levenshtein function)

2010-10-13 Thread Alexander Korotkov

 In this case search of near strings can be accelerated in more than 10
 times.

I mean that component of function runtime can be accelerated in more than 10
times. Of course, actual overall speedup depends on many other factors, but
I belive that it also can be significant.


With best regards,
Alexander Korotkov.


Re: [HACKERS] [JDBC] Support for JDBC setQueryTimeout, et al.

2010-10-13 Thread Radosław Smogura
This, what I see in your patch, is sending additional statement to server. 
This adds some unnecessery (especially TCP/IP) latency. gura 

  I sent such patch fully in Java
  (http://archives.postgresql.org/pgsql-jdbc/2009-11/msg00010.php),
  implementing cancellation with Timer and cancel query facility of
  PSQL server,
 
 Would you like to update it?

I updated patch to latets CVS version, I didn't have time to remove some 
trashes from it.

If something will be wrong with patch, give a feedback.

Kind regards,
Radosław Smogura
http://softperience.pl


statemnt_to_20101013.patch.gz
Description: GNU Zip compressed 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] leaky views, yet again

2010-10-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 You seem to believe that being able to infer the total size of a
 table or the frequency of some particular key in the table is
 equivalent to being able to trivially read every row of it.

I don't say that they're equivalent.  I do say that what this patch is
mostly trying to do is solve a PR problem, and from the PR standpoint
it doesn't help: the OMG Postgres exposes my information crowd is not
going to distinguish leaks that only expose MCVs from those that
trivially allow sucking out the entire table.  There are furthermore
plenty of situations where statistical information *is* of interest to
attackers; the traditional example is obtaining the min and max of a
salary column to infer something about what particular people are
getting paid.  So I think if we accept this patch or something like it,
we are going to spend a large part of the next ten years trying to close
other holes of the same ilk, and that's not a development plan I'm
willing to buy into.  I am much happier just making the statement that
we don't try to prevent that type of leak than giving people the
impression that we are committed to trying to prevent 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] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread Josh Berkus

 IIRC the proposal was that postgresql.conf (the people-editable file)
 would have include postgresql.auto in it.  You could put that at
 the top, bottom, or even middle to control how the priority works.
 So it's user-configurable.  I think the factory default ought to
 be to have it at the top, which would result in manually edited
 settings (if any) overriding SET PERMANENT.

Right, I think that's the behavior which will result in the least newbie
confusion.

So what we'd add to postgresql.conf would look something like this:

# Link to auto-generated configuration file.
# Do not edit the auto-generated file or remove this link;
# instead, edit settings below in this file and they will
# override the auto-generated file.

include 'postgresql.conf.auto'

 the only change I want to make immediately is that initdb ought
 to shove its settings into postgresql.auto instead of mucking with
 postgresql.conf.

That would be good.

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://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] Path question

2010-10-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 Another awkwardness of this patch is that it makes
 create_append_path() and consequently set_dummy_rel_pathlist() take an
 additional root argument.  While there's nothing terribly
 unreasonable about this on its face, it's only necessary so that
 create_append_path() can call cost_sort(), which takes root but
 doesn't actually use it.  I'm not sure whether it's better to leave
 this as-is or to remove the root argument from cost_sort().

Right offhand the cleanest answer to that seems to be to leave
create_append_path alone, and make a separate function named something
like create_ordered_append_path that handles the case where cost_sort
might be needed.  I rather wonder if we don't want two separate
execution-time node types anyway, since what Append does seems
significantly different from Merge (and MergeAppend would be just a
misnomer).

I have to run off for a doctors appointment, will continue looking at
this patch when I get back.

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] [JDBC] Support for JDBC setQueryTimeout, et al.

2010-10-13 Thread Kevin Grittner
Rados*aw Smogurarsmog...@softperience.eu wrote:
 
 I updated patch to latets CVS version, I didn't have time to
 remove some trashes from it.
 
 If something will be wrong with patch, give a feedback.
 
I skimmed it and agree that it is the right general approach --
using java.util.Timer to trigger the cancel method.  It doesn't
confuse the function of the setQueryTimeout method of the JDBC
driver with the statement_timeout GUC of PostgreSQL, which strike me
as no more or less similar to each other than the brakes on my car
are to a highway guardrail -- both are designed to stop something,
but under different circumstances.
 
I certainly can't fault you for lack of testing, since about
two-thirds of the patch is testing classes.  I didn't see any need
to include the last two classes (ByteConverter and
InfiniteTimerTask); can you explain why those are in there?
 
That said, some of the details of the implementation gave me pause
-- there seem to be rather more moving parts and more places to
check things than the overall complexity of the problem would seem
to warrant; however, it's entirely possible that on closer review
I'll find that they were necessary for reasons which escape me on a
quick scan of the code.
 
If you could add this to the open CommitFest, I'll be glad to review
it (if nobody beats me to it):
 
https://commitfest.postgresql.org/action/commitfest_view/open
 
-Kevin

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


[HACKERS] Why do we have a database specification in .pgpass?

2010-10-13 Thread Bruce Momjian
We have a database specification in .pgpass:

hostname:port:database:username:password

What is the purpose of 'database' since username/password combinations
are global, not per database?  I would like to documents its purpose.

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

  + It's impossible for everything to be true. +

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


[HACKERS] shmget error text reports funny max_connections numbers

2010-10-13 Thread Peter Eisentraut
Since MaxBackends is actually max_connections + autovacuum_max_workers +
1, when you get an error message from shmget() it will tell you

reduce ... its max_connections parameter (currently 104)

when you actually set

max_connections = 100

This looks a bit silly.

Should we just make the error messages report MaxBackends -
autovacuum_max_workers - 1, or is it worthwhile calling out
autovacuum_max_workers separately?



-- 
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] leaky views, yet again

2010-10-13 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote:
 
 the OMG Postgres exposes my information crowd is not going to
 distinguish leaks that only expose MCVs from those that trivially
 allow sucking out the entire table.
 
Well, I'd be in the crowd that would go OMG over one but not the
other.  At least in our case management software I can't think of
any MCVs which would be a problem, while exposing entire tables
would be a big problem.
 
If you get the name, address, birth date, or even the social
security number in isolation, it doesn't mean much.  If you get all
of those for one party, it does.  I suppose that if you could find
that a particular name was used somewhere in the Party table but it
was not visible in the public record, you could guess that someone
by that name (which is certainly not guaranteed to be unique!) was
somehow involved in some role in a juvenile, mental commitment,
adoption, sealed, or other confidential case -- but what role in
what kind of case would still be a complete mystery, making it much
less of a leak than the row in its entirety, much less the entire
table (which could expose, for example, who adopted whom --
information not available from a single row).
 
If you are arguing that the ability of someone to know that someone,
somewhere, who has had contact with the Wisconsin court system has
social security number 987-65-4321 is the same as knowing who has
that social security number, and all the demographics about that
person, you're dangerously mistaken.
 
-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] SQL command to edit postgresql.conf, with comments

2010-10-13 Thread Garick Hamlin

On Wed, Oct 13, 2010 at 12:56:15PM -0400, Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
  But creating a separate file doesn't fix that problem.  It just moves
  it around.  If people will expect comments in postgresql.conf to get
  preserved, then why won't they also expect comments in
  postgresql.conf.auto to get preserved?
 
 Because postgresql.conf.auto will have a nice leading comment telling
 people (1) not to hand-edit the file, (2) if they do so anyway,
 not to expect comments to be preserved, and (3) the place to do manual
 editing of settings is postgresql.conf.
 
  If the answer is because postgresql.conf has always worked that way
  before, then add one more line to the proposed initial contents
  saying it's not true any more.
 
 Sorry, wrong answer.  The objection to this is not whether you tell
 people that you're taking away the ability to keep useful comments
 in postgresql.conf, it's that you're taking away the ability.
 
   regards, tom lane

I like this approach.  I was just wondering if there is a simple 
tweak to this schema to make it work more easily with standbys.  If
there was a GUC that controlled the 'auto filename' and it could expand
something like %h to hostname (or name if we had something like standby 
registration).  This would allow each standby to store its local settings 
in a different location and have something like a unified set of config
files.  

I suppose something like symlinking postgresql.auto hostname.auto on each 
machine might achieve a similar effect...

Garick

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


[HACKERS] duplicate connection failure messages

2010-10-13 Thread Peter Eisentraut
This surprised me:

psql -p 5 -h localhost
psql: could not connect to server: Connection refused
Is the server running on host localhost and accepting
TCP/IP connections on port 5?
could not connect to server: Connection refused
Is the server running on host localhost and accepting
TCP/IP connections on port 5?

It shows the same error message twice.  I can reproduce this back to PG
8.2.

It appears to have something to do with localhost resolving to an IPv4
and an IPv6 address, since it doesn't happen with other host names that
only resolve to one address.  However, fe-connect.c claims:

/*
 * Try to initiate a connection to one of the addresses
 * returned by pg_getaddrinfo_all().  conn-addr_cur is the
 * next one to try. We fail when we run out of addresses
 * (reporting the error returned for the *last* alternative,
 * which may not be what users expect :-().
 */



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


[HACKERS] FreeBSD 8.0 i386, plpythonu, threaded Python not supported on this platform

2010-10-13 Thread Sergey Burladyan
Hi all!

./configure --prefix=$HOME/inst/pg-9 --enable-nls --enable-debug 
--enable-depend --enable-cassert --enable-thread-safety --with-pgport=5431 
--with-libxml --with-libxslt --with-python --with-perl --with-tcl 
FLEX=/usr/local/bin/flex

--8---cut here---start-8---
checking for python... /usr/local/bin/python
checking for Python distutils module... yes
checking Python configuration directory... /usr/local/lib/python2.6/config
checking how to link an embedded Python application... 
-L/usr/local/lib/python2.6/config -lpython2.6 -lutil -lm
checking whether Python is compiled with thread support... yes
configure: error: threaded Python not supported on this platform
--8---cut here---end---8---

Where can I find a description of this problem? Is any chance to fix it?

-- 
Sergey Burladyan

-- 
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] host name support in pg_hba.conf

2010-10-13 Thread Peter Eisentraut
On tis, 2010-10-12 at 17:03 -0400, Tom Lane wrote:
 Oh, I had an idea for a small improvement to this.  It doesn't seem
 unlikely that pg_hba.conf could contain multiple entries with the same
 host name (but, presumably, different user and/or database names).  As
 this is coded, you'll do a forward DNS lookup for each one until
 finding
 the complete match.  You could easily prevent that by adding an
 additional cache field to struct Port, along the lines of
 +1 = remote_hostname is known to resolve to client's IP
 address
 -1 = remote_hostname is known NOT to resolve to client's IP
 address
 0 = we have not done the forward DNS lookup yet.
 With this additional field we could guarantee to do not more than two
 DNS lookups per connection attempt.

That's a very good idea.  I will revise my patch.


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