Re: [HACKERS] Schema version management

[Joel Jacobson]

On Wed, Jul 11, 2012 at 11:20 PM, Alvaro Herrera  wrote:

> 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 _)
>
>
I think the directory structure [schema]/[type]/[name] should be the same
for all object types. I don't like "operator" being part of the filename,
it should be the directory name.

Re: [HACKERS] Schema version management

[Joel Jacobson]

On Wed, Jul 11, 2012 at 8:01 PM, Merlin Moncure  wrote:

> After extensive off-list discussion with Joel it became clear that
> per-object dumping ability really belongs in pg_restore.
>

The only benefit I could see in putting it in pg_restore is you would then
be able to do a --split on already existing historical dumps.

On the other hand, it would require you to use both pg_dump and pg_restore,
instead of only pg_dump, which makes it a bit less user-friendly.

I haven't looked at how it could be implemented in pg_restore, if its even
just
a little more complex, it's probably better to let pg_dump handle the task.

Re: [HACKERS] SP-GiST for ranges based on 2d-mapping and quad-tree

[Heikki Linnakangas]

On 12.07.2012 02:11, Alexander Korotkov wrote:

On Thu, Jul 12, 2012 at 3:03 AM, Alexander Korotkovwrote:


On Tue, Jul 3, 2012 at 10:51 AM, Jeff Davis  wrote:


Also, it would be helpful to add a couple tests to rangetypes.sql.



New version of patch is attached.



Oops, forgot to include one comment fix into patch.


Thanks. Can you do something about TrickFunctionCall2, please? 
(http://archives.postgresql.org/message-id/4fe2c968.2010...@enterprisedb.com) 
A separate patch to refactor that in the existing gist opclass would 
probably be best.


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

[Amit Kapila]

> From: pgsql-hackers-ow...@postgresql.org
[mailto:pgsql-hackers-ow...@postgresql.org] 
> On Behalf Of Jose Ildefonso Camargo Tolosa

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

I think this kind of decision should be done from outside utility or
scripts.
It would be better if from outside it can be detected that stand-by is down
during sync replication, and send command to master to change its mode or
change settings appropriately without stopping master.
Putting this kind of more and more logic into replication code will make it
more cumbersome.

With Regards,
Amit Kapila.


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

[Tom Lane]

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

Re: [HACKERS] Synchronous Standalone Master Redoux

[Daniel Farina]

On Wed, Jul 11, 2012 at 6:41 AM, Shaun Thomas  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] Synchronous Standalone Master Redoux

[Daniel Farina]

On Wed, Jul 11, 2012 at 3:03 AM, Dimitri Fontaine
 wrote:
> Daniel Farina  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] pgsql_fdw in contrib

[Peter Eisentraut]

On tor, 2012-07-12 at 06:25 +0200, Kohei KaiGai wrote:
> 2012/7/11 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?
> >
> 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

[Jose Ildefonso Camargo Tolosa]

On Wed, Jul 11, 2012 at 11:48 PM, Josh Berkus  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

[Kohei KaiGai]

2012/7/11 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?
>
This patch uses pgsql_fdw_validator for its own validator naming.

Which point was the main issue in the last commit-fest?
-- 
KaiGai Kohei 

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

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

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

[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=magpie&dt=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

[Jose Ildefonso Camargo Tolosa]

Greetings,

On Wed, Jul 11, 2012 at 9:11 AM, Shaun Thomas  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 peop

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

[Alex Hunsaker]

On Wed, Jul 11, 2012 at 1:42 PM, Alvaro Herrera
 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=magpie&dt=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] Event Triggers reduced, v1

[Robert Haas]

On Tue, Jul 10, 2012 at 10:38 AM, Dimitri Fontaine
 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.  Th

[HACKERS] emacs configuration for new perltidy settings

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

[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 
> > :
> > 
> > " * : < > ? \ / |
> 
> > 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 
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] pgsql_fdw in contrib

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

[Tom Lane]

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

[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 
> :
> 
> " * : < > ? \ / |

> 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

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

[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

[Alvaro Herrera]

Excerpts from Tom Lane's message of mié jul 11 15:47:47 -0400 2012:
> 
> Boszormenyi Zoltan  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 
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] has_language_privilege returns incorrect answer for non-superuser

[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

[Tom Lane]

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

[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=magpie&dt=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 
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] Synchronous Standalone Master Redoux

[Robert Haas]

On Tue, Jul 10, 2012 at 12:57 PM, Josh Berkus  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: [HACKERS] Synchronous Standalone Master Redoux

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

[Tom Lane]

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

[Merlin Moncure]

On Tue, Jul 10, 2012 at 5:24 PM, Merlin Moncure  wrote:
> On Sun, May 20, 2012 at 2:41 PM, Joel Jacobson  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 
>> 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
> 
>
> 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
insensiti

Re: [HACKERS] Support for array_remove and array_replace functions

[Robert Haas]

On Jul 11, 2012, at 11:53 AM, Alex Hunsaker  wrote:
> On Wed, Jul 11, 2012 at 9:54 AM, Tom Lane  wrote:
>> Marco Nenciarini  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] Support for array_remove and array_replace functions

[Tom Lane]

Alex Hunsaker  writes:
> On Wed, Jul 11, 2012 at 9:54 AM, Tom Lane  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

[Alex Hunsaker]

On Wed, Jul 11, 2012 at 9:54 AM, Tom Lane  wrote:
> Marco Nenciarini  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

[Tom Lane]

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

[Dimitri Fontaine]

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

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

[Honza Horak]

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

Honza Horak  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
 unix_socket_permissions (and possibly
 unix_socket_group) configuration parameters as
 described in .  Or you
-could set the unix_socket_directory
+could set the unix_socket_directories
 configuration parameter to place the socket file in a suitably
 restricted directory.

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;
   
  
 
- 
-  unix_socket_directory (string)
+ 
+  unix_socket_directories (string)
   
-   unix_socket_directory configuration parameter
+   unix_socket_directories configuration parameter
   
   

-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
 /tmp, but can be changed at build time.
+Directories are separated by ','.
 This parameter can only be set at server start.

 
@@ -464,7 +465,7 @@ SET ENABLE_SEQSCAN TO OFF;
 .s.PGSQL. where
  is the server's port number, an ordinary file
 named .s.PGSQL..lock will be
-created in the unix_socket_directory directory.  Neither
+created in the unix_socket_directories directories.  Neither
 file should ever be removed manually.

 
@@ -6551,7 +6552,7 @@ LOG:  CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)


 -k x
-unix_socket_directory = x
+unix_socket_directories = x


 -l
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 8717798..9cc9d42 100644
--- a/doc/src/sgml/runtime.sgml
+++ b/doc/src/sgml/runtime.sgml
@@ -1718,7 +1718,7 @@ pg_dumpall -p 5432 | psql -d postgres -p 5433
   
The simplest way to prevent spoofing for local
connections is to use a Unix domain socket directory () that has write permission only
+   linkend="guc-unix-socket-directories">) that has write permission only
for a trusted local user.  This prevents a malicious user from creating
their own socket file in that directory.  If you are concerned that
some applications might still reference /tmp for the
diff --git a/src/backend/libpq/pqcomm.c b/src/backend/libpq/pqcomm.c
index 5272811..3e22388 100644
--- a/src/backend/libpq/pqcomm.c
+++ b/src/backend/libpq/pqcom

Re: [HACKERS] Synchronous Standalone Master Redoux

[Dimitri Fontaine]

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

[Joel Jacobson]

On Wed, Jul 11, 2012 at 12:24 AM, Merlin Moncure  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.