Re: [HACKERS] Schema version management

2012-07-11 Thread Joel Jacobson
On Wed, Jul 11, 2012 at 12:24 AM, Merlin Moncure mmonc...@gmail.com wrote:

 What does your patch do that you can't already do with pg_restore?


Please read through the entire thread, think question has already been
answered.


Re: [HACKERS] Synchronous Standalone Master Redoux

2012-07-11 Thread Dimitri Fontaine
Daniel Farina dan...@heroku.com writes:
 Notable caveat: one can't very easily measure or bound the amount of
 transaction loss in any graceful way  as-is.  We only have unlimited
 lag and 2-safe or bust.

  ¡per-transaction!

You can change your mind mid-transaction and ask for 2-safe or bust.
That's the detail we've not been talking about in this thread and makes
the whole solution practical in real life, at least for me.

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] Ability to listen on two unix sockets

2012-07-11 Thread Honza Horak

On 07/02/2012 09:45 PM, Tom Lane wrote:

Honza Horak hho...@redhat.com writes:

On 06/15/2012 05:40 PM, Honza Horak wrote:

I realized the patch has some difficulties -- namely the socket path in the 
data dir lock file, which currently uses one port for socket and the same for 
interface. So to allow users to use arbitrary port for all unix sockets, we'd 
need to add another line only for unix socket, which doesn't apply for other 
platforms. Or we could just say that the first socket will allways use the 
default port (PostPortNumber), which is a solution I prefer currently, but will 
be glad for any other opinion. This is also why there is still un-necesary 
string splitting in pg_ctl.c, which will be removed after the issue above is 
solved.


I did a review pass over this patch.



I have finally an enhanced patch, see the attachment and feel free to 
comment.



Well, not so much forbids as silently ignores, which doesn't seem like
great user-interface design to me.  If we're going to adopt this solution
I think we need it to throw an error instead of just ignoring the port
specification.


Alternate-port-number support has been removed from the patch, as per 
Tom's e-mail from 07/03/12. It can be add in the future, if we really 
need it.



* I'm not especially thrilled with propagating SplitUnixDirectories calls
into those two places anyway, nor with the weird decision for
SplitUnixDirectories to return a separate mainSocket value.  Perhaps
what would be most sensible is to attach an assign hook to the
unix_socket_directories GUC parameter that would automatically split the
string and store the components into a globally-visible List variable
(which could replace the globally-visible string value we have now).


Replacing the old global string value would probably need a new 
configuration type List to be added, since otherwise guc works with it 
as with a string. Adding that seems like too big overhead to me and thus 
it seems better to add a new global (List *) variable and let the 
original value of type (char *) to store non-parsed value.


Except that I believe all other Tom's comments have been involved.

Regards,
Honza
diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml
index cfdb33a..679c40a 100644
--- a/doc/src/sgml/client-auth.sgml
+++ b/doc/src/sgml/client-auth.sgml
@@ -838,7 +838,7 @@ omicron bryanh  guest1
 varnameunix_socket_permissions/varname (and possibly
 varnameunix_socket_group/varname) configuration parameters as
 described in xref linkend=runtime-config-connection.  Or you
-could set the varnameunix_socket_directory/varname
+could set the varnameunix_socket_directories/varname
 configuration parameter to place the socket file in a suitably
 restricted directory.
/para
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 3a0b16d..67997d6 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -445,17 +445,18 @@ SET ENABLE_SEQSCAN TO OFF;
   /listitem
  /varlistentry
 
- varlistentry id=guc-unix-socket-directory xreflabel=unix_socket_directory
-  termvarnameunix_socket_directory/varname (typestring/type)/term
+ varlistentry id=guc-unix-socket-directories xreflabel=unix_socket_directories
+  termvarnameunix_socket_directories/varname (typestring/type)/term
   indexterm
-   primaryvarnameunix_socket_directory/ configuration parameter/primary
+   primaryvarnameunix_socket_directories/ configuration parameter/primary
   /indexterm
   listitem
para
-Specifies the directory of the Unix-domain socket on which the
+Specifies the directories of the Unix-domain sockets on which the
 server is to listen for
 connections from client applications.  The default is normally
 filename/tmp/filename, but can be changed at build time.
+Directories are separated by ','.
 This parameter can only be set at server start.
/para
 
@@ -464,7 +465,7 @@ SET ENABLE_SEQSCAN TO OFF;
 literal.s.PGSQL.replaceable//literal where
 replaceable/ is the server's port number, an ordinary file
 named literal.s.PGSQL.replaceable/.lock/literal will be
-created in the varnameunix_socket_directory/ directory.  Neither
+created in the varnameunix_socket_directories/ directories.  Neither
 file should ever be removed manually.
/para
 
@@ -6551,7 +6552,7 @@ LOG:  CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
/row
row
 entryoption-k replaceablex/replaceable/option/entry
-entryliteralunix_socket_directory = replaceablex/replaceable//entry
+entryliteralunix_socket_directories = replaceablex/replaceable//entry
/row
row
 entryoption-l/option/entry
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 8717798..9cc9d42 100644
--- 

Re: [HACKERS] Synchronous Standalone Master Redoux

2012-07-11 Thread Shaun Thomas

On 07/10/2012 06:02 PM, Daniel Farina wrote:


For example, what if DRBD can only complete one page per second for
some reason?  Does it it simply have the primary wait at this glacial
pace, or drop synchronous replication and go degraded?  Or does it do
something more clever than just a timeout?


That's a good question, and way beyond what I know about the internals. 
:) In practice though, there are configurable thresholds, and if 
exceeded, it will invalidate the secondary. When using Pacemaker, we've 
actually had instances where the 10G link we had between the servers 
died, so each node thought the other was down. That lead to the 
secondary node self-promoting and trying to steal the VIP from the 
primary. Throw in a gratuitous arp, and you get a huge mess.


That lead to what DRBD calls split-brain, because both nodes were 
running and writing to the block device. Thankfully, you can actually 
tell one node to discard its changes and re-subscribe. Doing that will 
replay the transactions from the good node on the bad one. And even 
then, it's a good idea to run an online verify to do a block-by-block 
checksum and correct any differences.


Of course, all of that's only possible because it's a block-level 
replication. I can't even imagine PG doing anything like that. It would 
have to know the last good transaction from the primary and do an 
implied PIT recovery to reach that state, then re-attach for sync commits.



Regardless of what DRBD does, I think the problem with the
async/sync duality as-is is there is no nice way to manage exposure
to transaction loss under various situations and requirements.


Which would be handy. With synchronous commits, it's given that the 
protocol is bi-directional. Then again, PG can detect when clients 
disconnect the instant they do so, and having such an event implicitly 
disable synchronous_standby_names until reconnect would be an easy fix. 
The database already keeps transaction logs, so replaying would still 
happen on re-attach. It could easily throw a warning for every 
sync-required commit so long as it's in degraded mode. Those alone are 
very small changes that don't really harm the intent of sync commit.


That's basically what a RAID-1 does, and people have been fine with that 
for decades.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
stho...@optionshouse.com



__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email

--
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] Synchronous Standalone Master Redoux

2012-07-11 Thread Dimitri Fontaine
Shaun Thomas stho...@optionshouse.com writes:
 Regardless of what DRBD does, I think the problem with the
 async/sync duality as-is is there is no nice way to manage exposure
 to transaction loss under various situations and requirements.

Yeah.

 Which would be handy. With synchronous commits, it's given that the protocol
 is bi-directional. Then again, PG can detect when clients disconnect the
 instant they do so, and having such an event implicitly disable

It's not always possible, given how TCP works, if I understand correctly.

 synchronous_standby_names until reconnect would be an easy fix. The database
 already keeps transaction logs, so replaying would still happen on
 re-attach. It could easily throw a warning for every sync-required commit so
 long as it's in degraded mode. Those alone are very small changes that
 don't really harm the intent of sync commit.

We already have that, with the archives. The missing piece is how to
apply that to Synchronous Replication…

 That's basically what a RAID-1 does, and people have been fine with that for
 decades.

… and we want to cover *data* availability (durability), not just
service availability.

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] Support for array_remove and array_replace functions

2012-07-11 Thread Tom Lane
Marco Nenciarini marco.nenciar...@2ndquadrant.it writes:
 Patch v3 attached.

I'm looking at this patch now.  The restriction of array_remove to
one-dimensional arrays seems a bit annoying.  I see the difficulty:
if the input is multi-dimensional then removing some elements could
lead to a non-rectangular array, which isn't supported.  However,
that could be dealt with by decreeing that the *result* is
one-dimensional and of the necessary length, regardless of the
dimensionality of the input.

I'm not actually certain whether that's a better definition or not.
But one less error case seems like generally a good thing.
Comments?

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] Support for array_remove and array_replace functions

2012-07-11 Thread Alex Hunsaker
On Wed, Jul 11, 2012 at 9:54 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Marco Nenciarini marco.nenciar...@2ndquadrant.it writes:
 Patch v3 attached.

 I'm looking at this patch now.  The restriction of array_remove to
 one-dimensional arrays seems a bit annoying.  I see the difficulty:
 if the input is multi-dimensional then removing some elements could
 lead to a non-rectangular array, which isn't supported.  However,
 that could be dealt with by decreeing that the *result* is
 one-dimensional and of the necessary length, regardless of the
 dimensionality of the input.

Makes sense to me. +1

The other option ISTM is to replace removed entries with NULL-- which
I don't really like.

-- 
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] Support for array_remove and array_replace functions

2012-07-11 Thread Tom Lane
Alex Hunsaker bada...@gmail.com writes:
 On Wed, Jul 11, 2012 at 9:54 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 I'm looking at this patch now.  The restriction of array_remove to
 one-dimensional arrays seems a bit annoying.  I see the difficulty:
 if the input is multi-dimensional then removing some elements could
 lead to a non-rectangular array, which isn't supported.  However,
 that could be dealt with by decreeing that the *result* is
 one-dimensional and of the necessary length, regardless of the
 dimensionality of the input.

 Makes sense to me. +1

 The other option ISTM is to replace removed entries with NULL-- which
 I don't really like.

Well, you can do that with array_replace, so I don't see a need to
define array_remove that way.

regards, tom lane

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


Re: [HACKERS] Support for array_remove and array_replace functions

2012-07-11 Thread Robert Haas
On Jul 11, 2012, at 11:53 AM, Alex Hunsaker bada...@gmail.com wrote:
 On Wed, Jul 11, 2012 at 9:54 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Marco Nenciarini marco.nenciar...@2ndquadrant.it writes:
 Patch v3 attached.
 
 I'm looking at this patch now.  The restriction of array_remove to
 one-dimensional arrays seems a bit annoying.  I see the difficulty:
 if the input is multi-dimensional then removing some elements could
 lead to a non-rectangular array, which isn't supported.  However,
 that could be dealt with by decreeing that the *result* is
 one-dimensional and of the necessary length, regardless of the
 dimensionality of the input.
 
 Makes sense to me. +1

+1 from me, too.

...Robert

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


Re: [HACKERS] Schema version management

2012-07-11 Thread Merlin Moncure
On Tue, Jul 10, 2012 at 5:24 PM, Merlin Moncure mmonc...@gmail.com wrote:
 On Sun, May 20, 2012 at 2:41 PM, Joel Jacobson j...@trustly.com wrote:
 Hi,

 I just read a very interesting post about schema version management.

 Quote: You could set it up so that every developer gets their own
 test database, sets up the schema there, takes a dump, and checks that
 in. There are going to be problems with that, including that dumps
 produced by pg_dump are ugly and optimized for restoring, not for
 developing with, and they don't have a deterministic output order. (
 http://petereisentraut.blogspot.com/2012/05/my-anti-take-on-database-schema-version.html
 )

 Back in December 2010, I suggested a new option to pg_dump, --split,
 which would write the schema definition of each object in separate
 files:

 http://archives.postgresql.org/pgsql-hackers/2010-12/msg02285.php

 Instead of a huge plain text schema file, impossible to version
 control, all tables/sequences/views/functions are written to separate
 files, allowing the use of a version control software system, such as
 git, to do proper version controlling.

 The deterministic output order problem mentioned in the post above,
 is not a problem if each object (table/sequence/view/function/etc) is
 written to the same filename everytime.
 No matter the order, the tree of files and their content will be
 identical, no matter the order in which they are dumped.

 I remember a lot of hackers were very positive about this option, but
 we somehow failed to agree on the naming of files in the tree
 structure. I'm sure we can work that out though.

 I use this feature in production, I have a cronjob which does a dump
 of the schema every hour, committing any eventual changes to a
 separate git branch for each database installation, such as
 production, development and test.
 If no changes to the schema have been made, nothing will be committed
 to git since none of the files have changed.

 It is then drop-dead simple to diff two different branches of the
 database schema, such as development or production, or diffing
 different revisions allowing point-in-time comparison of the schema.

 This is an example of the otuput of a git log --summary for one of the
 automatic commits to our production database's git-repo:

 --
 commit 18c31f8162d851b0dac3bad7e80529ef2ed18be3
 Author: Production Database production.datab...@trustly.com
 Date:   Fri May 4 15:00:04 2012 +0200

 Update of database schema Linux DB0 2.6.26-2-amd64 #1 SMP Wed Aug
 19 22:33:18 UTC 2009 x86_64 GNU/Linux Fri, 04 May 2012 15:00:04 +0200

  create mode 100644
 gluepay-split/public/CONSTRAINT/openingclosingbalances_pkey.sql
  create mode 100644
 gluepay-split/public/CONSTRAINT/openingclosingbalances_source_key.sql
  create mode 100644 
 gluepay-split/public/SEQUENCE/seqopeningclosingbalance.sql
  create mode 100644 gluepay-split/public/TABLE/openingclosingbalances.sql
 --

 Here we can see we apparently deployed a new table,
 openingclosingbalances around Fri May 4 15:00:04.

 Without any manual work, I'm able to follow all changes actually
 _deployed_ in each database.

 At my company, a highly database-centric stored-procedure intensive
 business dealing with mission-critical monetary transactions, we've
 been using this technique to successfully do schema version management
 without any hassle for the last two years.

 Hopefully this can add to the list of various possible _useful_ schema
 version management methods.

 What does your patch do that you can't already do with pg_restore?

 create function foo(a int, b int, c text) returns int as $$ select 0;
 $$ language sql;
 CREATE FUNCTION

 pg_dump -Fc postgres -s  postgres.dump
 pg_restore -l postgres.dump  | grep FUNCTION
 196; 1255 32939 FUNCTION public foo(integer, integer, text) merlin

 pg_restore -P foo(integer, integer, text) postgres.dump
 function body follows

 it's fairly easy to wrap pg_restore with a smalls script that extracts
 function bodies and writes them out to file names.  this is a great
 and underused feature, so I'd argue that if you wanted to formalize
 per object file extraction you should be looking at expanding
 pg_restore, not pg_dump.

After extensive off-list discussion with Joel it became clear that
per-object dumping ability really belongs in pg_restore.  It already
has some machinery for that, and has the nice property that you can
pull objects out of dumps long after the fact, not just when the dump
happens.  It then remains to be worked out of pg_restore should be
given the ability to write directly to files as Joel was gunning for
or simply extended to improve upon the current TOC based facilities,
or both.

As noted, choosing a reversible unambiguous filename based on the
database primitive is nastiness of the first order.  For example,
besides the mentioned issues, some filesystems (windows) use case
insensitive entries.  What do you do about that?  Given that all the
reasonable escape characters are 

Re: [HACKERS] Support for array_remove and array_replace functions

2012-07-11 Thread Tom Lane
Marco Nenciarini marco.nenciar...@2ndquadrant.it writes:
 Patch v3 attached.

Applied with mostly-but-not-entirely cosmetic adjustments.

I left array_remove throwing error for multi-dimensional arrays for
the moment, because I realized that changing the dimensionality as
I suggested would conflict with the optimization to return the original
array if there were no matches.  I don't think we'd want the definition
to read multidimensional arrays are changed to one dimension, but only
if at least one element is removed --- that's getting a little too
weird.  If anyone's really hot to make it work on multi-D arrays, we
could consider disabling that optimization; it's not clear to me that
it's worth a lot.  But for now I'm willing to stick with the
throw-an-error approach.

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] Synchronous Standalone Master Redoux

2012-07-11 Thread Josh Berkus
On 7/11/12 6:41 AM, Shaun Thomas wrote:
 Which would be handy. With synchronous commits, it's given that the
 protocol is bi-directional. Then again, PG can detect when clients
 disconnect the instant they do so, and having such an event implicitly
 disable synchronous_standby_names until reconnect would be an easy fix.
 The database already keeps transaction logs, so replaying would still
 happen on re-attach. It could easily throw a warning for every
 sync-required commit so long as it's in degraded mode. Those alone are
 very small changes that don't really harm the intent of sync commit.

So your suggestion is to have a switch allow degraded, where if the
sync standby doesn't respond within a certain threshold, will switch to
async with a warning for each transaction which asks for sync?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://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] Synchronous Standalone Master Redoux

2012-07-11 Thread Robert Haas
On Tue, Jul 10, 2012 at 12:57 PM, Josh Berkus j...@agliodbs.com wrote:
 Per your exchange with Heikki, that's not actually how SyncRep works in
 9.1.  So it's not giving you what you want anyway.

 This is why we felt that the sync rep if you can mode was useless and
 didn't accept it into 9.1.  The *only* difference between sync rep and
 async rep is whether or not the master waits for ack that the standby
 has written to log.

 I think one of the new modes in 9.2 forces synch-to-DB before ack.  No?

No.  Such a mode has been discussed and draft patches have been
circulated, but nothing's been committed.  The new mode in 9.2 is less
synchronous than the previous mode (wait for remote write rather than
remote fsync), not more.

Now, if we DID have such a mode, then many people would likely attempt
to use synchronous replication in that mode as a way of ensuring that
read queries can't see stale data, rather than as a method of
providing increased durability.  And in that case it sure seems like
it would be useful to wait only if the standby is connected.  In fact,
you'd almost certainly want to have multiple standbys running
synchronously, and have the ability to wait for only those connected
at the moment.  You might also want to have a way for standbys that
lose their connection to the master to refuse to take any new
snapshots until the slave is reconnected and has caught up.  Then you
could guarantee that any query run on the slave will see all the
commits that are visible on the master (and possibly more, since
commits become visible on the slave first), which would be useful for
many applications.

-- 
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: [SPAM] [MessageLimit][lowlimit] Re: [HACKERS] pl/perl and utf-8 in sql_ascii databases

2012-07-11 Thread Alvaro Herrera

Excerpts from Alvaro Herrera's message of mar jul 10 16:23:57 -0400 2012:
 Excerpts from Kyotaro HORIGUCHI's message of mar jul 03 04:59:38 -0400 2012:
  Hello, Here is regression test runs on pg's also built with
  cygwin-gcc and VC++.
  
  The patches attached following,
  
  - plperl_sql_ascii-4.patch : fix for pl/perl utf8 vs sql_ascii
  - plperl_sql_ascii_regress-1.patch : regression test for this patch.
   I added some tests on encoding to this.
  
  I will mark this patch as 'ready for committer' after this.
 
 I have pushed these changes to HEAD, 9.2 and 9.1.  Instead of the games
 with plperl_lc_*.out being copied around, I just used the ASCII version
 as plperl_lc_1.out and the UTF8 one as plperl_lc.out.

... and this story hasn't ended yet, because one of the new tests is
failing.  See here:

http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=magpiedt=2012-07-11%2010%3A00%3A04

The interesting part of the diff is:

***
*** 34,41 
return ($str ne $match ? $code.DIFFER : $code.ab\x{5ddd}cd);
  $$ LANGUAGE plperl;
  SELECT encode(perl_utf_inout(E'ab\xe5\xb1\xb1cd')::bytea, 'escape')
!   encode  
! --
!  NotUTF8:ab\345\267\235cd
! (1 row)
! 
--- 34,38 
return ($str ne $match ? $code.DIFFER : $code.ab\x{5ddd}cd);
  $$ LANGUAGE plperl;
  SELECT encode(perl_utf_inout(E'ab\xe5\xb1\xb1cd')::bytea, 'escape')
! ERROR:  character with byte sequence 0xe5 0xb7 0x9d in encoding UTF8 has no 
equivalent in encoding LATIN1
! CONTEXT:  PL/Perl function perl_utf_inout


I am not sure what can we do here other than remove this function and
query from the test.

-- 
Á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] [PATCH] lock_timeout and common SIGALRM framework

2012-07-11 Thread Tom Lane
Boszormenyi Zoltan z...@cybertec.at writes:
 Attached are the refreshed patches. InitializeTimeouts() can be called
 twice and PGSemaphoreTimedLock() returns bool now. This saves
 two calls to get_timeout_indicator().

I'm starting to look at this patch now.  There are a number of cosmetic
things I don't care for, the biggest one being the placement of
timeout.c under storage/lmgr/.  That seems an entirely random place,
since the functionality provided has got nothing to do with storage
let alone locks.  I'm inclined to think that utils/misc/ is about
the best option in the existing backend directory hierarchy.  Anybody
object to that, or have a better idea?

Another thing that needs some discussion is the handling of
InitializeTimeouts.  As designed, I think it's completely unsafe,
the reason being that if a process using timeouts forks off another
one, the child will inherit the parent's timeout reasons and be unable
to reset them.  Right now this might not be such a big problem because
the postmaster doesn't need any timeouts, but what if it does in the
future?  So I think we should drop the base_timeouts_initialized
protection, and that means we need a pretty consistent scheme for
where to call InitializeTimeouts.  But we already have the same issue
with respect to on_proc_exit callbacks, so we can just add
InitializeTimeouts calls in the same places as on_exit_reset().

Comments?

I'll work up a revised patch and post 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] has_language_privilege returns incorrect answer for non-superuser

2012-07-11 Thread Peter Eisentraut
On tis, 2012-07-10 at 15:28 -0700, Joe Conway wrote:
 So I think this boils down to what we think the output of the various
 has_*_privilege() functions *should* tell you:
 
 1) privileges possessed even though they may not
be usable
  -or-
 2) privileges possessed and usable
 
 Personally I'm interested in answering the latter question -- what are
 all the things role X can do and see.
 
 But historically (and perhaps correctly) these functions have always
 done the former -- so maybe all we need are some words of warning in
 the documentation of these functions?

The second question is much more difficult to answer than the first.
You could have sepgsql in the way, for example.

The functions very clearly check whether a privilege is being held, and
elsewhere it is documented what you can do with these privileges.  A
particular action might very well require multiple privileges.


-- 
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] [PATCH] lock_timeout and common SIGALRM framework

2012-07-11 Thread Alvaro Herrera

Excerpts from Tom Lane's message of mié jul 11 15:47:47 -0400 2012:
 
 Boszormenyi Zoltan z...@cybertec.at writes:
  Attached are the refreshed patches. InitializeTimeouts() can be called
  twice and PGSemaphoreTimedLock() returns bool now. This saves
  two calls to get_timeout_indicator().
 
 I'm starting to look at this patch now.  There are a number of cosmetic
 things I don't care for, the biggest one being the placement of
 timeout.c under storage/lmgr/.  That seems an entirely random place,
 since the functionality provided has got nothing to do with storage
 let alone locks.  I'm inclined to think that utils/misc/ is about
 the best option in the existing backend directory hierarchy.  Anybody
 object to that, or have a better idea?

I agree with the proposed new location.

 Another thing that needs some discussion is the handling of
 InitializeTimeouts.  As designed, I think it's completely unsafe,
 the reason being that if a process using timeouts forks off another
 one, the child will inherit the parent's timeout reasons and be unable
 to reset them.  Right now this might not be such a big problem because
 the postmaster doesn't need any timeouts, but what if it does in the
 future?  So I think we should drop the base_timeouts_initialized
 protection, and that means we need a pretty consistent scheme for
 where to call InitializeTimeouts.  But we already have the same issue
 with respect to on_proc_exit callbacks, so we can just add
 InitializeTimeouts calls in the same places as on_exit_reset().

I do agree that InitializeTimeouts is not optimally placed.  We
discussed this upthread.

Some of the calls of on_exit_reset() are placed in code that's about to
die.  Surely we don't need InitializeTimeouts() then.  Maybe we should
have another routine, say InitializeProcess (noting we already
InitProcess so maybe some name would be good), that calls both
on_exit_reset and InitializeTimeouts.

-- 
Á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] HTTP API experimental implementation

2012-07-11 Thread Peter Eisentraut
On tis, 2012-07-10 at 08:59 +0200, Björn Harrtell wrote:
 Hey all,
 
 I've begun an implementation of the proposed HTTP API [1] (with some
 changes) using node.js
 
 The project lives at
 https://github.com/bjornharrtell/postgresql-http-server and
 basic functionality is in place.
 
 Feedback appriciated!
 
 [1] http://wiki.postgresql.org/wiki/HTTP_API

The problem I see with this is that if someone else were to take this
wiki page and the email thread that preceded it, and tried to implement
it, it would look similar but completely different in detail to yours.
If there isn't anything like a standard for this out there, it's just
going to be another proprietary API/protocol, except that it is
transported over HTTP.

I'm also not sure about mangling together some of the proposed use
cases.  I can see the value in exposing the database structures over
HTTP for browsing and exploration.  But is JSON the right format for
this?  If you used, for example, XML with some stylesheets, you could
easily produce lists and tables in a browser, which would serve this
much better.

On the other hand, I can see the value of this for embedded or mobile
applications.  But then you don't need the whole database metadata
stuff.  A simple and efficient query pass-through would be enough and
easier to design.



-- 
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] [PATCH] lock_timeout and common SIGALRM framework

2012-07-11 Thread Tom Lane
I wrote:
 I'm starting to look at this patch now.

After reading this further, I think that the sched_next option is a
bad idea and we should get rid of it.  AFAICT, what it is meant to do
is (if !sched_next) automatically do disable_all_timeouts(true) if
the particular timeout happens to fire.  But there is no reason the
timeout's callback function couldn't do that; and doing it in the
callback is more flexible since you could have logic about whether to do
it or not, rather than freezing the decision at RegisterTimeout time.
Moreover, it does not seem to me to be a particularly good idea to
encourage timeouts to have such behavior, anyway.  Each time we add
another timeout we'd have to look to see if it's still sane for each
existing timeout to use !sched_next.  It would likely be better, in
most cases, for individual callbacks to explicitly disable any other
individual timeout reasons that should no longer be fired.

I am also underwhelmed by the timeout_start callback function concept.
In the first place, that's broken enable_timeout, which incorrectly
assumes that the value it gets must be now (see its schedule_alarm
call).  In the second place, it seems fairly likely that callers of
get_timeout_start would likewise want the clock time at which the
timeout was enabled, not the timeout_start reference time.  (If they
did want the latter, why couldn't they get it from wherever the callback
function had gotten it?)  I'm inclined to propose that we drop the
timeout_start concept and instead provide two functions for scheduling
interrupts:

enable_timeout_after(TimeoutName tn, int delay_ms);
enable_timeout_at(TimeoutName tn, TimestampTz fin_time);

where you use the former if you want the standard GetCurrentTimestamp +
n msec calculation, but if you want the stop time calculated in some
other way, you calculate it yourself and use the second function.

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] Schema version management

2012-07-11 Thread Peter Eisentraut
On tis, 2012-07-10 at 17:54 -0400, Andrew Dunstan wrote:
 In general, NTFS forbids the use of these printable ASCII chars in 
 filenames (see 
 http://en.wikipedia.org/wiki/Filename#Comparison_of_filename_limitations:
 
  * :   ? \ / |

 Many of these could be used in operators.

Yeah, that's a bummer.  Then I guess some escape mechanism would be OK.
I could imagine an operator  on a custom data type being dumped into a
file named operator_%3C.sql.  Still better than putting them all in one
file.

Of course, argument types need to be dealt with as well, just like with
functions (plus prefix/postfix).


-- 
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] [PATCH] lock_timeout and common SIGALRM framework

2012-07-11 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes:
 Excerpts from Tom Lane's message of mié jul 11 15:47:47 -0400 2012:
 ... that means we need a pretty consistent scheme for
 where to call InitializeTimeouts.  But we already have the same issue
 with respect to on_proc_exit callbacks, so we can just add
 InitializeTimeouts calls in the same places as on_exit_reset().

 I do agree that InitializeTimeouts is not optimally placed.  We
 discussed this upthread.

 Some of the calls of on_exit_reset() are placed in code that's about to
 die.  Surely we don't need InitializeTimeouts() then.  Maybe we should
 have another routine, say InitializeProcess (noting we already
 InitProcess so maybe some name would be good), that calls both
 on_exit_reset and InitializeTimeouts.

Yeah, I was wondering about that too, but it seems a bit ad-hoc from a
modularity standpoint.  I gave some consideration to the idea of putting
these calls directly into fork_process(), but we'd have to be very sure
that there would never be a case where it was incorrect to do them after
forking.

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] pgsql_fdw in contrib

2012-07-11 Thread Peter Eisentraut
On tor, 2012-06-14 at 21:29 +0900, Shigeru HANADA wrote:
 I'd like to propose pgsql_fdw, FDW for PostgreSQL, as a contrib module
 in core, again.

Do you have any new proposals regarding naming, and how to deal with
postgresql_fdw_validator, and dblink?


-- 
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] Schema version management

2012-07-11 Thread Alvaro Herrera

Excerpts from Peter Eisentraut's message of mié jul 11 17:03:03 -0400 2012:
 
 On tis, 2012-07-10 at 17:54 -0400, Andrew Dunstan wrote:
  In general, NTFS forbids the use of these printable ASCII chars in 
  filenames (see 
  http://en.wikipedia.org/wiki/Filename#Comparison_of_filename_limitations:
  
   * :   ? \ / |
 
  Many of these could be used in operators.
 
 Yeah, that's a bummer.  Then I guess some escape mechanism would be OK.
 I could imagine an operator  on a custom data type being dumped into a
 file named operator_%3C.sql.  Still better than putting them all in one
 file.
 
 Of course, argument types need to be dealt with as well, just like with
 functions (plus prefix/postfix).

operator_!___numeric.sql (postfix, name does not need escape)
operator_%7C%2F_integer__.sql (prefix)
operator_%3C_bit_varying__bit_varying.sql (type name with spaces,
changed to _)

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


[HACKERS] emacs configuration for new perltidy settings

2012-07-11 Thread Peter Eisentraut
This might be useful for some people.  Here is an emacs configuration
for perl-mode that is compatible with the new perltidy settings.  Note
that the default perl-mode settings produce indentation that will be
completely shredded by the new perltidy settings.

(defun pgsql-perl-style ()
  Perl style adjusted for PostgreSQL project
  (interactive)
  (setq tab-width 4)
  (setq perl-indent-level 4)
  (setq perl-continued-statement-offset 4)
  (setq perl-continued-brace-offset 4)
  (setq perl-brace-offset 0)
  (setq perl-brace-imaginary-offset 0)
  (setq perl-label-offset -2))

(add-hook 'perl-mode-hook
   (lambda ()
 (if (string-match postgresql buffer-file-name)
 (pgsql-perl-style



-- 
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] Event Triggers reduced, v1

2012-07-11 Thread Robert Haas
On Tue, Jul 10, 2012 at 10:38 AM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
 Mind you, if I ran the world, this would probably be broken up
 differently: I'd have ddl_command_start covering all the
 CREATE/ALTER/DROP commands and nothing else; and separate firing
 points for anything else I wanted to support.  It's not too late to
 make that change, hint, hint.  But if we're not gonna do that then I

 Let's see about doing that. I guess we would have ddl_command_start and
 command_start, and I would think that the later is the most generic. So
 we would certainly want DDL commands to run first the command_start
 event triggers then the ddl_command_start event triggers, whereas a
 NOTIFY would only run command_start triggers, and a GRANT command would
 run maybe command_start then dcl_command_start triggers?

 If that's where we're going to, we can commit as-is and expand later.

That's not quite what I was thinking.  I actually can't imagine any
situation where you want an event trigger that gets fired on EVERY
command for which we can support command_start.  If you're trying to
prevent or replicate DDL, that's too much.  If you're trying to do
logging or auditing, it's not enough, since there will still be
commands that aren't supported, and it'll be grossly inefficient to
boot.  You really want something like log_min_duration_statement=0 for
those cases.  So it seems to me that the use case for a command_start
trigger, conceived in the broadest possible way so that every single
command we can support is included, is razor-thin.

So my proposal for the present patch would be:

1. Rename command_start to ddl_command_start.
2. Remove support for everything other than CREATE, ALTER, and DROP.
3. Pass the operation and the SQL object type as separate magic variables.

Then we can add dcl_command_start, etc. in follow-on patches.

 think that we'd better try to cast the net as broadly as reasonably
 possible.  It seems to me that our excuse for not including things
 like UPDATE and DELETE is a bit thin; surely there are people who
 would like a sort of universal trigger that applies to every relation
 in the system.  Of course there are recursion problems there that need
 to be thought long hard about, and no I don't really want to go there
 right now, but I'll bet you a nickle that someone is going to ask why
 it doesn't work that way.

 The current reason why we only support 149 SQL commands and variations
 is because we want a patch that's easy enough to review and agree on. So
 I think we will in the future be able to add new firing point at places
 where maybe some discussion is needed.

Agreed.

 Such places, in my mind, include the NOTIFY mechanism, DCLs, and global
 objects such as databases and tablespaces and roles. I'd be happy to see
 event triggers embrace support for those. Maybe in v2 though?

Yep, sure.  Note that the proposal above constrains the list of
commands we support in v1 in a very principled way: CREATE, ALTER,
DROP.  Everything else can be added later under a different (but
similarly situated) firing point name.  If we stick with command_start
then I think we're going to be forever justifying our decisions as to
what got included or excluded; which might be worth it if it seemed
likely that there'd be much use for such a command trigger, but it
doesn't (to me, anyway).

 Another advantage to recasting this as ddl_command_start is that we
 quite easily pass the operation (CREATE, ALTER, DROP) and the named
 object type (TABLE, FUNCTION, CAST) as separate arguments.  I think

 That's a good idea. I don't think we should replace the current tag
 support with that though, because some commands are harder to stow into
 the operation and type model (in supported commands, mainly LOAD).

I'm imagining that ddl_command_start triggers would get the
information this way, but LOAD might be covered by something like
admin_command_start that just gets the command tag.

 So I've included partial support for that in the attached patch, in the
 simplest way possible, just so that we can see where it leads in term of
 using the feature. The next step here is to actually go in each branch
 of the process utility switch and manually decorate the command context
 with the current operation and objecttype when relevant.

[...]
 Done in the attached. Filling that array was… an interesting use case
 for Emacs Keyboard Macros spanning 3 different buffers, maintaining it
 should be easy enough now.

Yep, looks better.  It looks like you've got
EventTriggerCommandTagsEntry mapping the command tag to an ETC_*
constant; I think the need for that hash goes away entirely if you
just pass this information down from the ProcessUtility() switch.  At
any rate having NameData involved seems like it's probably not too
good an idea; if for some reason we need to keep that hash, use a
NUL-terminated string and initialize the hash table with string_hash
instead of tag_hash.  That'll be simpler and also allows the 

Re: [SPAM] [MessageLimit][lowlimit] Re: [HACKERS] pl/perl and utf-8 in sql_ascii databases

2012-07-11 Thread Alex Hunsaker
On Wed, Jul 11, 2012 at 1:42 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:

 I have pushed these changes to HEAD, 9.2 and 9.1.  Instead of the games
 with plperl_lc_*.out being copied around, I just used the ASCII version
 as plperl_lc_1.out and the UTF8 one as plperl_lc.out.

 ... and this story hasn't ended yet, because one of the new tests is
 failing.  See here:

 http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=magpiedt=2012-07-11%2010%3A00%3A04

 [...]
   SELECT encode(perl_utf_inout(E'ab\xe5\xb1\xb1cd')::bytea, 'escape')
 ! ERROR:  character with byte sequence 0xe5 0xb7 0x9d in encoding UTF8 has 
 no equivalent in encoding LATIN1
 ! CONTEXT:  PL/Perl function perl_utf_inout


 I am not sure what can we do here other than remove this function and
 query from the test.

Hrm, me neither. I say drop em.

-- 
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] Synchronous Standalone Master Redoux

2012-07-11 Thread Jose Ildefonso Camargo Tolosa
Greetings,

On Wed, Jul 11, 2012 at 9:11 AM, Shaun Thomas stho...@optionshouse.com wrote:
 On 07/10/2012 06:02 PM, Daniel Farina wrote:

 For example, what if DRBD can only complete one page per second for
 some reason?  Does it it simply have the primary wait at this glacial
 pace, or drop synchronous replication and go degraded?  Or does it do
 something more clever than just a timeout?


 That's a good question, and way beyond what I know about the internals. :)
 In practice though, there are configurable thresholds, and if exceeded, it
 will invalidate the secondary. When using Pacemaker, we've actually had
 instances where the 10G link we had between the servers died, so each node
 thought the other was down. That lead to the secondary node self-promoting
 and trying to steal the VIP from the primary. Throw in a gratuitous arp, and
 you get a huge mess.

That's why Pacemaker *recommends* STONITH (Shoot The Other Node In The
Head).  Whenever the standby decides to promote itself, it would just
kill the former master (just in case)... the STONITH thing have to use
an independent connection.  Additionally, redundant link between
cluster nodes is a must.


 That lead to what DRBD calls split-brain, because both nodes were running
 and writing to the block device. Thankfully, you can actually tell one node
 to discard its changes and re-subscribe. Doing that will replay the
 transactions from the good node on the bad one. And even then, it's a
 good idea to run an online verify to do a block-by-block checksum and
 correct any differences.

 Of course, all of that's only possible because it's a block-level
 replication. I can't even imagine PG doing anything like that. It would have
 to know the last good transaction from the primary and do an implied PIT
 recovery to reach that state, then re-attach for sync commits.


 Regardless of what DRBD does, I think the problem with the
 async/sync duality as-is is there is no nice way to manage exposure
 to transaction loss under various situations and requirements.


 Which would be handy. With synchronous commits, it's given that the protocol
 is bi-directional. Then again, PG can detect when clients disconnect the
 instant they do so, and having such an event implicitly disable
 synchronous_standby_names until reconnect would be an easy fix. The database
 already keeps transaction logs, so replaying would still happen on
 re-attach. It could easily throw a warning for every sync-required commit so
 long as it's in degraded mode. Those alone are very small changes that
 don't really harm the intent of sync commit.

 That's basically what a RAID-1 does, and people have been fine with that for
 decades.



I can't believe how many times I have seen this topic arise in the
mailing list... I was myself about to start a thread like this!
(thanks Shaun!).

I don't really get what people wants out of the synchronous streaming
replication DRBD (that is being used as comparison) in protocol C
is synchronous (it won't confirm a write unless it was written to disk
on both nodes).  PostgreSQL (8.4, 9.0, 9.1, ...) will work just fine
with it, except that you don't have a standby that you can connect
to... also, you need to setup a dedicated volume to put the DRBD block
device, setup DRBD, then put the filesystem on top of DRBD, and handle
the DRBD promotion, partition mount (with possible FS error handling),
and then starting PostgreSQL after the FS is correctly mounted..

With synchronous streaming replication you can have about the same:
the standby will have the changes written to disk before master
confirms commit I don't really care if standby has already applied
the changes to its DB (although that would certainly be nice) the
point is: the data is on the standby, and if the master were to crash,
and I were to promote the standby: the standby would have the same
commited data the server had before it crashed.

So, why are we, HA people, bothering you DB people so much?: simplify
the things, it is simpler to setup synchronous streaming replication,
than having to setup DRBD + pacemaker rules to make it promote DRBD,
mount FS, and then start pgsql.

Also, there is an great perk to synchronous replication with Hot
Standby: you have a read/only standby that can be used for some things
(even though it doesn't always have exactly the same data as the
master).

I mean, a lot of people here have a really valid point: 2-safe
reliability is great, but how good is it if when you lose it, ALL the
system just freeze? I mean, RAID1 gives you 2-safe reliability, but no
one would use it if the machine were to freeze when you lose 1 disk,
same for DRBD: it offers 2-safe reliability too (at block-level), but
it doesn't freeze if the secondary goes away!

Now, I see some people who are arguing because, apparently,
synchronous replication is not an HA feature (those who says that SR
doesn't fit the HA environment)... please, those people, answer why is
synchronous 

Re: [SPAM] [MessageLimit][lowlimit] Re: [HACKERS] pl/perl and utf-8 in sql_ascii databases

2012-07-11 Thread Kyotaro HORIGUCHI
Hmm... Sorry for immature patch..

 ... and this story hasn't ended yet, because one of the new tests is
 failing.  See here:
 
 http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=magpiedt=2012-07-11%2010%3A00%3A04
 
 The interesting part of the diff is:
...
   SELECT encode(perl_utf_inout(E'ab\xe5\xb1\xb1cd')::bytea, 'escape')
 ! ERROR:  character with byte sequence 0xe5 0xb7 0x9d in encoding UTF8 has 
 no equivalent in encoding LATIN1
 ! CONTEXT:  PL/Perl function perl_utf_inout
 
 
 I am not sure what can we do here other than remove this function and
 query from the test.

I've run the regress only for the environment capable to handle
the character U+5ddd (Japanese character which means river)...

The byte sequences which can be decoded and the result byte
sequences of encoding from a unicode character vary among the
encodings.

The problem itself which is the aim of this thread could be
covered without the additional test. That confirms if
encoding/decoding is done as expected on calling the language
handler. I suppose that testing for the two cases and additional
one case which runs pg_do_encoding_conversion(), say latin1,
would be enough to confirm that encoding/decoding is properly
done, since the concrete conversion scheme is not significant
this case.

So I recommend that we should add the test for latin1 and omit
the test from other than sql_ascii, utf8 and latin1. This might
be archieved by create empty plperl_lc.sql and plperl_lc.out
files for those encodings.

What do you think about that?


regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

== My e-mail address has been changed since Apr. 1, 2012.

-- 
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] pl/perl and utf-8 in sql_ascii databases

2012-07-11 Thread Kyotaro HORIGUCHI
Very sorry for rotten subject. I resent the message with correct subject.
# Our mail server insisted that the message is spam. sigh..

Hmm... Sorry for immature patch..

 ... and this story hasn't ended yet, because one of the new tests is
 failing.  See here:
 
 http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=magpiedt=2012-07-11%2010%3A00%3A04
 
 The interesting part of the diff is:
...
   SELECT encode(perl_utf_inout(E'ab\xe5\xb1\xb1cd')::bytea, 'escape')
 ! ERROR:  character with byte sequence 0xe5 0xb7 0x9d in encoding UTF8 has 
 no equivalent in encoding LATIN1
 ! CONTEXT:  PL/Perl function perl_utf_inout
 
 
 I am not sure what can we do here other than remove this function and
 query from the test.

I've run the regress only for the environment capable to handle
the character U+5ddd (Japanese character which means river)...

The byte sequences which can be decoded and the result byte
sequences of encoding from a unicode character vary among the
encodings.

The problem itself which is the aim of this thread could be
covered without the additional test. That confirms if
encoding/decoding is done as expected on calling the language
handler. I suppose that testing for the two cases and additional
one case which runs pg_do_encoding_conversion(), say latin1,
would be enough to confirm that encoding/decoding is properly
done, since the concrete conversion scheme is not significant
this case.

So I recommend that we should add the test for latin1 and omit
the test from other than sql_ascii, utf8 and latin1. This might
be archieved by create empty plperl_lc.sql and plperl_lc.out
files for those encodings.

What do you think about that?


regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

== My e-mail address has been changed since Apr. 1, 2012.

-- 
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] Synchronous Standalone Master Redoux

2012-07-11 Thread Josh Berkus

 Please, stop arguing on all of this: I don't think that adding an
 option will hurt anybody (specially because the work was already done
 by someone), we are not asking to change how the things work, we just
 want an option to decided whether we want it to freeze on standby
 disconnection, or if we want it to continue automatically... is that
 asking so much?

The objection is that, *given the way synchronous replication currently
works*, having that kind of an option would make the synchronous
setting fairly meaningless.  The only benefit that synchronous
replication gives you is the guarantee that a write on the master is
also on the standby.  If you remove that guarantee, you are using
asynchronous replication, even if the setting says synchronous.

I think what you really want is a separate auto-degrade setting.  That
is, a setting which says if no synchronous standby is present,
auto-degrade to async/standalone, and start writing a bunch of warning
messages to the logs and whenever anyone runs a synchronous
transaction.  That's an approach which makes some sense, but AFAICT
somewhat different from the proposed patch.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://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] pgsql_fdw in contrib

2012-07-11 Thread Kohei KaiGai
2012/7/11 Peter Eisentraut pete...@gmx.net:
 On tor, 2012-06-14 at 21:29 +0900, Shigeru HANADA wrote:
 I'd like to propose pgsql_fdw, FDW for PostgreSQL, as a contrib module
 in core, again.

 Do you have any new proposals regarding naming, and how to deal with
 postgresql_fdw_validator, and dblink?

This patch uses pgsql_fdw_validator for its own validator naming.

Which point was the main issue in the last commit-fest?
-- 
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] Synchronous Standalone Master Redoux

2012-07-11 Thread Jose Ildefonso Camargo Tolosa
On Wed, Jul 11, 2012 at 11:48 PM, Josh Berkus j...@agliodbs.com wrote:

 Please, stop arguing on all of this: I don't think that adding an
 option will hurt anybody (specially because the work was already done
 by someone), we are not asking to change how the things work, we just
 want an option to decided whether we want it to freeze on standby
 disconnection, or if we want it to continue automatically... is that
 asking so much?

 The objection is that, *given the way synchronous replication currently
 works*, having that kind of an option would make the synchronous
 setting fairly meaningless.  The only benefit that synchronous
 replication gives you is the guarantee that a write on the master is
 also on the standby.  If you remove that guarantee, you are using
 asynchronous replication, even if the setting says synchronous.

I know how synchronous replication works, I have read it several
times, I have seen it in the real life, I have seen it in virtual test
environments.  And no, it doesn't makes synchronous replication
meaningless, because it will work synchronous if it have someone to
sync to, and work async (or standalone) if it doesn't: that's perfect
for HA environment.


 I think what you really want is a separate auto-degrade setting.  That
 is, a setting which says if no synchronous standby is present,
 auto-degrade to async/standalone, and start writing a bunch of warning
 messages to the logs and whenever anyone runs a synchronous
 transaction.  That's an approach which makes some sense, but AFAICT
 somewhat different from the proposed patch.

Certainly, different to current patch, the one I saw I believe it had
all of that you say there: except the additional warning.

As synchronous standby currently is, it just doesn't fit the HA usage,
and if you really want to keep it that way, it doesn't belong to the
HA chapter on the pgsql documentation, and should be moved.  And NO
async replication will *not* work for HA, because the master can have
more transactions than standby, and if the master crashes, the standby
will have no way to recover these transactions, with synchronous
replication we have *exactly* what we need: the data in the standby,
after all, it will apply it once we promote it.

Ildefonso.

-- 
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] pgsql_fdw in contrib

2012-07-11 Thread Peter Eisentraut
On tor, 2012-07-12 at 06:25 +0200, Kohei KaiGai wrote:
 2012/7/11 Peter Eisentraut pete...@gmx.net:
  On tor, 2012-06-14 at 21:29 +0900, Shigeru HANADA wrote:
  I'd like to propose pgsql_fdw, FDW for PostgreSQL, as a contrib module
  in core, again.
 
  Do you have any new proposals regarding naming, and how to deal with
  postgresql_fdw_validator, and dblink?
 
 This patch uses pgsql_fdw_validator for its own validator naming.
 
 Which point was the main issue in the last commit-fest?

That this module should be called postgresql_fdw.



-- 
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] Synchronous Standalone Master Redoux

2012-07-11 Thread Daniel Farina
On Wed, Jul 11, 2012 at 3:03 AM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
 Daniel Farina dan...@heroku.com writes:
 Notable caveat: one can't very easily measure or bound the amount of
 transaction loss in any graceful way  as-is.  We only have unlimited
 lag and 2-safe or bust.

   ¡per-transaction!

 You can change your mind mid-transaction and ask for 2-safe or bust.
 That's the detail we've not been talking about in this thread and makes
 the whole solution practical in real life, at least for me.

It's a pretty good feature, but it's pretty dissatisfying that one
cannot have the latency of asynchronous transactions while not
exposing users  to unbounded loss as an administrator or provider (as
opposed to a user that sets synchronous commit, as you are saying).

If I had a strong opinion on *how* this should be tunable, I'd voice
it, but I think it's worth insisting that there is a missing part of
this continuum that involves non-zero but not-unbounded risk
management and transaction loss that is under-served.  DRBD seems to
have some heuristic that makes people happy that's somewhere
in-between.  I'm not saying it should be copied, but the fact it makes
people happy may be worth understanding.

I was quite excited for the syncrep feature because it does open the
door to write those, even if painfully, at all, since we now have both
unbounded and strictly bounded.

--
fdr

-- 
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] Synchronous Standalone Master Redoux

2012-07-11 Thread Daniel Farina
On Wed, Jul 11, 2012 at 6:41 AM, Shaun Thomas stho...@optionshouse.com wrote:
 Regardless of what DRBD does, I think the problem with the
 async/sync duality as-is is there is no nice way to manage exposure
 to transaction loss under various situations and requirements.


 Which would be handy. With synchronous commits, it's given that the protocol
 is bi-directional. Then again, PG can detect when clients disconnect the
 instant they do so, and having such an event implicitly disable
 synchronous_standby_names until reconnect would be an easy fix. The database
 already keeps transaction logs, so replaying would still happen on
 re-attach. It could easily throw a warning for every sync-required commit so
 long as it's in degraded mode. Those alone are very small changes that
 don't really harm the intent of sync commit.

 That's basically what a RAID-1 does, and people have been fine with that for
 decades.

But RAID-1 as nominally seen is a fundamentally different problem,
with much tinier differences in latency, bandwidth, and connectivity.
Perhaps useful for study, but to suggest the problem is *that* similar
I think is wrong.  I think your wording is even more right here than
you suggest: That's *basically* what a RAID-1 does.

I'm pretty unhappy with many user-facing aspects of this formulation,
even though I think the fundamental need being addressed is
reasonable.  But, putting that aside, why not write a piece of
middleware that does precisely this, or whatever you want? It can live
on the same machine as Postgres and ack synchronous commit when nobody
is home, and notify (e.g. page) you in the most precise way you want
if nobody is home for a while.

-- 
fdr

-- 
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] has_language_privilege returns incorrect answer for non-superuser

2012-07-11 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes:
 On tis, 2012-07-10 at 15:28 -0700, Joe Conway wrote:
 But historically (and perhaps correctly) these functions have always
 done the former -- so maybe all we need are some words of warning in
 the documentation of these functions?

 The second question is much more difficult to answer than the first.
 You could have sepgsql in the way, for example.

 The functions very clearly check whether a privilege is being held, and
 elsewhere it is documented what you can do with these privileges.  A
 particular action might very well require multiple privileges.

That's a fair argument, but I think it's reasonable to expect that
(1) the privileges required to do something are easily identified and
can be checked from the SQL level; (2) there's a reasonable amount of
consistency in the behavior for different object types.

In these terms, the example of needing schema usage privilege seems
like a different case from lanpltrusted.  We have
has_schema_privilege(), so there's support for queries to probe that
component of privilege; and the issue is common across all object types
that live within schemas.  Furthermore, client-side code would probably
need to be aware of the schema-privilege angle anyway, because if you
don't have schema usage privilege on s, you aren't even going to be
able to name table s.t to the has_table_privilege function, at least
not to the name-based variants of it.

So it seems arguably reasonable to me for has_language_privilege()
to take superuserness and lanpltrusted into account, without thereby
concluding that other privilege() functions must do more than they
do today.  If we don't want it to do that, then I think we ought to
offer some other function that *does* consider those things ... but
I'm not seeing the value of separating it out.

Having said that, I do think your argument has some merit with respect
to the internal pg_language_aclcheck() function.  That is, I'd want
to see any code changes here made in the has_language_privilege
functions, not at the aclcheck level.

The sepgsql point is worth discussing too.  I have not been paying
close attention to the sepgsql patches, but I have the distinct
impression that they create a non-examinable privilege barrier,
ie there's no way to inquire whether you have the privilege to do
X except by actually trying it.  Is that really the way we want
things to go?

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