Re: [HACKERS] Improving log capture of TAP tests with IPC::Run

[Erik Rijkers]

On Thu, June 25, 2015 06:14, Michael Paquier wrote:
>
> I have developed a patch to improve log capture of the TAP tests by
> being able to collect stderr and stdout output of each command run in
> the tests by using more extensively IPC::Run::run (instead of system()

IPC::Run is not in perl core and will not always be available.
It looks like this patch doesn't take this into account (when no IPC::Run skip 
the test or whatever)
Shouldn't that be changed?


Thanks,


Erik Rijkers




-- 
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] Improving log capture of TAP tests with IPC::Run

[Michael Paquier]

On Thu, Jun 25, 2015 at 3:59 PM, Erik Rijkers  wrote:
> IPC::Run is not in perl core and will not always be available.
> It looks like this patch doesn't take this into account (when no IPC::Run 
> skip the test or whatever)
> Shouldn't that be changed?

Yes, there have been discussions on the matter already:
http://www.postgresql.org/message-id/20141102190024.ga538...@tornado.leadboat.com
We may want to be able to capture easily the outputs of stdout and
stderr as well though and not only get the exit code. That looks
important to improve the debuggability of the whole thing when kicking
in commands. And to have that working on Windows as well..
Regards,
-- 
Michael


-- 
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] Oh, this is embarrassing: init file logic is still broken

[Tatsuo Ishii]

> On 06/23/2015 04:44 PM, Tom Lane wrote:
>> Chasing a problem identified by my Salesforce colleagues led me to the
>> conclusion that my commit f3b5565dd ("Use a safer method for determining
>> whether relcache init file is stale") is rather borked.  It causes
>> pg_trigger_tgrelid_tgname_index to be omitted from the relcache init file,
>> because that index is not used by any syscache.  I had been aware of that
>> actually, but considered it a minor issue.  It's not so minor though,
>> because RelationCacheInitializePhase3 marks that index as nailed for
>> performance reasons, and includes it in NUM_CRITICAL_LOCAL_INDEXES.
>> That means that load_relcache_init_file *always* decides that the init
>> file is busted and silently(!) ignores it.  So we're taking a nontrivial
>> hit in backend startup speed as of the last set of minor releases.
> 
> OK, this is pretty bad in its real performance effects.  On a workload
> which is dominated by new connection creation, we've lost about 17%
> throughput.

Are we going to release 9.4.5 etc. soon?

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


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

Re: [HACKERS] RFC: replace pg_stat_activity.waiting with something more descriptive

[Amit Kapila]

On Tue, Jun 23, 2015 at 2:33 AM, Robert Haas  wrote:
>
> On Mon, Jun 22, 2015 at 4:40 PM, Merlin Moncure 
wrote:
> > Instead of changing the column, can't we add a new one?  Adjusting
> > columns in PSA requires the innumerable queries written against it to
> > be adjusted along with all the wiki instructions to dev ops for
> > emergency stuck query detection etc etc.   I would also prefer to
> > query 'waiting' in some cases, especially when in emergency
> > situations; it's faster to type.
>
> If people feel strongly about backward compatibility, yes, we can do
> that.  However, if waiting continues to mean "on a heavyweight lock"
> for backward compatibility, then you could sometimes have waiting =
> false but wait_state non-null.  That seems confusing enough to be a
> bad plan, at least to me.
>

That's right if we leave the 'waiting' as it is for the sake of backward
compatibility, then it will be confusing after we add wait_event to
pg_stat_activity and if we change it such that for any kind of wait_event
waiting will be true (or entirely remove waiting), then it will break the
backward compatibility.  So we have below alternatives here:

1. Remove/Change 'waiting' in pg_stat_activity and break the backward
compatibility.  I think we should try to avoid going via this route.

2. Add 2 new columns to pg_stat_activity
waiting_resource - true for waits other heavy wait locks, false
   otherwise
wait_event - description code for the wait event

3. Add new view 'pg_stat_wait_event' with following info:
pid   - process id of this backend
waiting - true for any form of wait, false otherwise
wait_event_type - Heavy Weight Lock, Light Weight Lock, I/O wait, etc
wait_event - Lock (Relation), Lock (Relation Extension), etc

Do you think 2nd or 3rd could be viable way to proceed for this feature?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] RFC: replace pg_stat_activity.waiting with something more descriptive

[Andres Freund]

On 2015-06-25 16:07:45 +0530, Amit Kapila wrote:
> On Tue, Jun 23, 2015 at 2:33 AM, Robert Haas  wrote:
> > If people feel strongly about backward compatibility, yes, we can do
> > that.  However, if waiting continues to mean "on a heavyweight lock"
> > for backward compatibility, then you could sometimes have waiting =
> > false but wait_state non-null.  That seems confusing enough to be a
> > bad plan, at least to me.
> >
> 
> That's right if we leave the 'waiting' as it is for the sake of backward
> compatibility, then it will be confusing after we add wait_event to
> pg_stat_activity and if we change it such that for any kind of wait_event
> waiting will be true (or entirely remove waiting), then it will break the
> backward compatibility.  So we have below alternatives here:

> 1. Remove/Change 'waiting' in pg_stat_activity and break the backward
> compatibility.  I think we should try to avoid going via this route.
> 
> 2. Add 2 new columns to pg_stat_activity
> waiting_resource - true for waits other heavy wait locks, false
>otherwise
> wait_event - description code for the wait event
> 
> 3. Add new view 'pg_stat_wait_event' with following info:
> pid   - process id of this backend
> waiting - true for any form of wait, false otherwise
> wait_event_type - Heavy Weight Lock, Light Weight Lock, I/O wait, etc
> wait_event - Lock (Relation), Lock (Relation Extension), etc
> 
> Do you think 2nd or 3rd could be viable way to proceed for this feature?

3) sounds best to me. Keeping 'waiting' even makes sense in that case,
because it'll tell whether wait_event_type is currently being blocked
on. We can leave the former contents in until the next thing is being
blocked...

Greetings,

Andres Freund


-- 
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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Amit Kapila]

On Thu, Jun 25, 2015 at 4:16 PM, Andres Freund  wrote:
>
> On 2015-06-25 16:07:45 +0530, Amit Kapila wrote:
> > On Tue, Jun 23, 2015 at 2:33 AM, Robert Haas 
wrote:
> > > If people feel strongly about backward compatibility, yes, we can do
> > > that.  However, if waiting continues to mean "on a heavyweight lock"
> > > for backward compatibility, then you could sometimes have waiting =
> > > false but wait_state non-null.  That seems confusing enough to be a
> > > bad plan, at least to me.
> > >
> >
> > That's right if we leave the 'waiting' as it is for the sake of backward
> > compatibility, then it will be confusing after we add wait_event to
> > pg_stat_activity and if we change it such that for any kind of
wait_event
> > waiting will be true (or entirely remove waiting), then it will break
the
> > backward compatibility.  So we have below alternatives here:
>
> > 1. Remove/Change 'waiting' in pg_stat_activity and break the backward
> > compatibility.  I think we should try to avoid going via this route.
> >
> > 2. Add 2 new columns to pg_stat_activity
> > waiting_resource - true for waits other heavy wait locks, false
> >otherwise
> > wait_event - description code for the wait event
> >
> > 3. Add new view 'pg_stat_wait_event' with following info:
> > pid   - process id of this backend
> > waiting - true for any form of wait, false otherwise
> > wait_event_type - Heavy Weight Lock, Light Weight Lock, I/O wait, etc
> > wait_event - Lock (Relation), Lock (Relation Extension), etc
> >
> > Do you think 2nd or 3rd could be viable way to proceed for this feature?
>
> 3) sounds best to me. Keeping 'waiting' even makes sense in that case,
> because it'll tell whether wait_event_type is currently being blocked
> on. We can leave the former contents in until the next thing is being
> blocked...
>

Won't leaving former contents as it is (until the next thing is being
blocked) could give misleading information.  Currently we mark 'waiting'
as false as soon as Heavy Weight Lock is over, so following that way
sounds more appropriate, is there any reason why you want it differently
than what we are doing currently?


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] RFC: replace pg_stat_activity.waiting with something more descriptive

[Ilya Kosmodemiansky]

Hi all

On Thu, Jun 25, 2015 at 12:37 PM, Amit Kapila  wrote:
> 2. Add 2 new columns to pg_stat_activity
> waiting_resource - true for waits other heavy wait locks, false
>otherwise
> wait_event - description code for the wait event
>
> 3. Add new view 'pg_stat_wait_event' with following info:
> pid   - process id of this backend
> waiting - true for any form of wait, false otherwise
> wait_event_type - Heavy Weight Lock, Light Weight Lock, I/O wait, etc
> wait_event - Lock (Relation), Lock (Relation Extension), etc

Personally I think, that tracking waits is a not a good idea for
pg_stat_activity (at least in that straight-forward manner). One
process can wait for lots of things between 2 sampling of
pg_stat_activity and that sampling can be pretty useless.

My approach (about which Ive had a talk mentioned by Jim and which I
hope to finalize and submit within a few days) is a bit different and
I believe is more useful:

1.  Some sort of histogram of top waits within entire database by pid.
That will be an approximate one, because  I hardly believe there is a
possibility to make a precise one without significant overhead.

2. Some cyclic buffer  of more precise wait statistic inside each
worker. Sampling may be turned on if we see some issues in histogram
(1) and want to have some more details.

> Do you think 2nd or 3rd could be viable way to proceed for this feature?
>
> With Regards,
> Amit Kapila.
> EnterpriseDB: http://www.enterprisedb.com



-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Andres Freund]

On 2015-06-25 16:26:39 +0530, Amit Kapila wrote:
> Won't leaving former contents as it is (until the next thing is being
> blocked) could give misleading information.  Currently we mark 'waiting'
> as false as soon as Heavy Weight Lock is over, so following that way
> sounds more appropriate, is there any reason why you want it differently
> than what we are doing currently?

But we don't do the same for query, so I don't think that says much. I
think it'd be useful because it gives you a bit more chance to see what
you blocked on last, even if the time the backend was blocked was very
short.

Greetings,

Andres Freund


-- 
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] Removing SSL renegotiation (Was: Should we back-patch SSL renegotiation fixes?)

[Magnus Hagander]

On Jun 24, 2015 7:40 PM, "Andres Freund"  wrote:
>
> On 2015-06-24 12:57:03 -0400, Robert Haas wrote:
> > On Wed, Jun 24, 2015 at 11:11 AM, Tom Lane  wrote:
> > > Andres Freund  writes:
> > >> I, by now, have come to a different conclusion. I think it's time to
> > >> entirely drop the renegotiation support.
> > >
> > > Well, that's a radical proposal, but I think we should take it
seriously.
> > >
> > > On balance I think I agree that SSL renegotiation has not been worth
the
> > > trouble.  And we definitely aren't testing it adequately, so if we
wanted
> > > to keep it then there's even *more* work that somebody ought to
expend.
> >
> > I'd like to know what factors we are balancing against each other.
>
> Benefits:
>
> SSL renegotiation limits the exposure of on-the-wire material that's
> leaked if either client or server is hijacked during a existing
> session. With renegotiation the client/server will hopefully only have
> the currently negotiated symetric key, covering only
> session_renegotiation_limit bytes, in memory.
>
> That's nice, but from a practical point of view it's not worth all that
> much. If the server has been hacked nearly all the data is accessible
> anyway, and even if not, the hacker can just continue collecting data
> going forward.  If the client has been hacked, it's likely that it has
> been relegating data for the session to somewhere that's still
> accessible.
>
> For the server hacked case all that generally only matters if perfect
> forward secrecy (PFS) has been employed, without that the session keys
> can be recovered anyway as the private key will be accessible in memory.
>
> All this only matters for hacks that access running processes.
>
> Deficits:
>
> SSL renegotiation has had several weaknesses (c.f. CVE-2009-3555/RFC
> 5746 , although I'm not 100% sure it's possible to apply to PG) on the
> protocol level, at least partially leading to much worse vulnerabilities
> than renegotiation in the pg style fixes. The openssl implementation has
> had several bugs several of them unfixed years after they were reported
> (#3712, #2481, #2146,...). By my reading of openssl's code the current
> state is entirely broken for duplex communication.
>
> The current draft of the next version of the TLS standard removes
> renegotiation entirely.

I think this is a very strong argument against it. If the standards people
now think it's a bad idea, we shouldn't encourage it.

That's assuming they haven't replaced it with something else (even more
complicated of course), and not just removed it. But i don't think they
did.

/Magnus

Re: [HACKERS] Support for N synchronous standby servers - take 2

[Simon Riggs]

On 25 June 2015 at 05:01, Michael Paquier  wrote:

> On Thu, Jun 25, 2015 at 12:57 PM, Fujii Masao wrote:
> > On Thu, Jun 25, 2015 at 12:15 PM, Michael Paquier wrote:
> >> and that's actually equivalent to that in
> >> the grammar: 1(AAA,BBB,CCC).
> >
> > I don't think that they are the same. In the case of 1(AAA,BBB,CCC),
> while
> > two servers AAA and BBB are running, the master server may return a
> success
> > of the transaction to the client just after it receives the ACK from BBB.
> > OTOH, in the case of AAA,BBB, that never happens. The master must wait
> for
> > the ACK from AAA to arrive before completing the transaction. And then,
> > if AAA goes down, BBB should become synchronous standby.
>
> Ah. Right. I missed your point, that's a bad day... We could have
> multiple separators to define group types then:
> - "()" where the order of acknowledgement does not matter
> - "[]" where it does not.
> You would find the old grammar with:
> 1[AAA,BBB,CCC]
>

Let's start with a complex, fully described use case then work out how to
specify what we want.

I'm nervous of "it would be good ifs" because we do a ton of work only to
find a design flaw.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] RFC: replace pg_stat_activity.waiting with something more descriptive

[Amit Kapila]

On Thu, Jun 25, 2015 at 4:28 PM, Andres Freund  wrote:
>
> On 2015-06-25 16:26:39 +0530, Amit Kapila wrote:
> > Won't leaving former contents as it is (until the next thing is being
> > blocked) could give misleading information.  Currently we mark 'waiting'
> > as false as soon as Heavy Weight Lock is over, so following that way
> > sounds more appropriate, is there any reason why you want it differently
> > than what we are doing currently?
>
> But we don't do the same for query, so I don't think that says much. I
> think it'd be useful because it gives you a bit more chance to see what
> you blocked on last, even if the time the backend was blocked was very
> short.
>

Sure, that's another way to look at it, if you and or others feels that is
better,
then we can follow that way.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] RFC: replace pg_stat_activity.waiting with something more descriptive

[Amit Kapila]

On Thu, Jun 25, 2015 at 4:28 PM, Ilya Kosmodemiansky <
ilya.kosmodemian...@postgresql-consulting.com> wrote:
>
> On Thu, Jun 25, 2015 at 12:37 PM, Amit Kapila 
wrote:
> > 2. Add 2 new columns to pg_stat_activity
> > waiting_resource - true for waits other heavy wait locks, false
> >otherwise
> > wait_event - description code for the wait event
> >
> > 3. Add new view 'pg_stat_wait_event' with following info:
> > pid   - process id of this backend
> > waiting - true for any form of wait, false otherwise
> > wait_event_type - Heavy Weight Lock, Light Weight Lock, I/O wait, etc
> > wait_event - Lock (Relation), Lock (Relation Extension), etc
>
> Personally I think, that tracking waits is a not a good idea for
> pg_stat_activity (at least in that straight-forward manner).

As mentioned in the initial mail by Robert, that sometimes system becomes
slow (either due to contention on various kinds of locks or due to I/O or
due
to some other such reasons) that such kind of handy information via some
view is quite useful.  Recently while working on one of the
performance/scalability
projects, I need to use gdb to attach to different processes to see what
they
are doing (of course one can use perf or some other utilities as well) and I
found most of them were trying to wait on some LW locks, now having such
an information available via view could be really useful, because sometimes
at customer sites, we can't use gdb or perf to see what's going on.

>  One
> process can wait for lots of things between 2 sampling of
> pg_stat_activity and that sampling can be pretty useless.
>

Yeah, that's right and I am not sure if we should bother about such
scenario's
as the system is generally fine in such situations, however there are other
cases where we can find most of the backends are waiting on one or other
thing.

> My approach (about which Ive had a talk mentioned by Jim and which I
> hope to finalize and submit within a few days) is a bit different and
> I believe is more useful:
>
> 1.  Some sort of histogram of top waits within entire database by pid.
> That will be an approximate one, because  I hardly believe there is a
> possibility to make a precise one without significant overhead.
>
> 2. Some cyclic buffer  of more precise wait statistic inside each
> worker. Sampling may be turned on if we see some issues in histogram
> (1) and want to have some more details.
>

I think this is some what different kind of utility which can give us
aggregated information and I think this will address different kind of
usecase and will have somewhat more complex design and it doesn't
look impossible to use part of what will be developed as part of this
proposal.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] Should we back-patch SSL renegotiation fixes?

[Andres Freund]

On 2015-06-24 17:20:31 -0400, Robert Haas wrote:
> On Wed, Jun 24, 2015 at 3:49 PM, Andres Freund  wrote:
> > On 2015-06-24 15:41:22 -0400, Peter Eisentraut wrote:
> >> On 6/24/15 3:13 PM, Andres Freund wrote:
> >> > Meh. The relevant branches already exist, as you can disable it today.
> >> >
> >> > We could also just change the default in the back branches.
> >>
> >> One more argument for leaving everything alone.  If users don't like it,
> >> they can turn it off themselves.
> >
> > Because it's so obvious to get there from "SSL error: unexpected
> > message", "SSL error: bad write retry" or "SSL error: unexpected record"
> > to disabling renegotiation. Right?  Search the archives and you'll find
> > plenty of those, mostly in relation to streaming rep. It took -hackers
> > years to figure out what causes those, how are normal users supposed to
> > a) correlate such errors with renegotiation b) evaluate what do about
> > it?
> 
> We could document the issues, create release-note entries suggesting a
> configuration change, and/or blog about it.

The situation is this: We have broken code using broken code. I think we
either got to apply, darn nontrivial, fixes from
http://archives.postgresql.org/message-id/54DE6FAF.6050005%40vmware.com
or we got to cripple the options.

It's also not the first breakage, we've applied a lot of bandaids to
this code already. Our way of doing renegotiation also has broken
several SSL client implementations...

Right now if you use streaming rep over ssl, it breaks after a couple
hundred megabytes with obscure errors. The user might or might not
notice that. He might just see errors around syncrep or something. Or
notice that logical decoding never finishes streaming out one huge as
xact, or ...

> I don't accept the argument that there are not ways to tell users
> about things they might want to do.

We probably could do that. But why would we want to? It's just as much
work, and puts the onus on more people?

Greetings,

Andres Freund


-- 
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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Ilya Kosmodemiansky]

On Thu, Jun 25, 2015 at 1:49 PM, Amit Kapila  wrote:
>> Personally I think, that tracking waits is a not a good idea for
>> pg_stat_activity (at least in that straight-forward manner).
>
> As mentioned in the initial mail by Robert, that sometimes system becomes
> slow (either due to contention on various kinds of locks or due to I/O or
> due
> to some other such reasons) that such kind of handy information via some
> view is quite useful.  Recently while working on one of the
> performance/scalability
> projects, I need to use gdb to attach to different processes to see what
> they
> are doing (of course one can use perf or some other utilities as well) and I
> found most of them were trying to wait on some LW locks, now having such
> an information available via view could be really useful, because sometimes
> at customer sites, we can't use gdb or perf to see what's going on.

Yes, I understand such a use-case. But I hardly see if suggested
design can help for such cases.

Basically, a DBA has two reasons to take a look on waits:

1. Long response time for particular query (or some type of queries).
In that case it is good to know how much time we spend on waiting for
particular resources we need to get query results
2. Overall bad performance of a database. We know, that something goes
wrong and consumes resources, we need to identify which backend, which
query causes the most of waits.

In both cases we need a) some historical data rather than simple
snapshot b) some approach how to aggregate it  because the will be
certainly a lot of events

So my point is, we need separate interface for waits, instead of
integrating in pg_stat_activity. And it should be several interfaces:
one for approximate top of waiting sessions (like
active_sessions_history in oracle), one for detailed tracing of a
session, one for waits per resource statistics etc.

>>  One
>> process can wait for lots of things between 2 sampling of
>> pg_stat_activity and that sampling can be pretty useless.
>>
>
> Yeah, that's right and I am not sure if we should bother about such
> scenario's
> as the system is generally fine in such situations, however there are other
> cases where we can find most of the backends are waiting on one or other
> thing.

I think approach with top of waiting sessions covers both scenarios
(well, with only one exception: if we have billions of very short
waits and high  contention is the problem)

However, it maybe a good idea, to identify the resource we are waiting
for from pg_stat_activity if we are waiting for a long time.

>
> I think this is some what different kind of utility which can give us
> aggregated information and I think this will address different kind of
> usecase and will have somewhat more complex design and it doesn't
> look impossible to use part of what will be developed as part of this
> proposal.
>

I think it is more than possible to mix both approaches. My proof of
concept now is only about LWLocks - yours and Robert's is more
general, and certainly some wait event classification will be needed
for both approaches and its much better to implement one rather than
two different.

And at least, I will be interesting in reviewing your approach.

>
> With Regards,
> Amit Kapila.
> EnterpriseDB: http://www.enterprisedb.com



-- 
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
i...@postgresql-consulting.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] Should we back-patch SSL renegotiation fixes?

[Heikki Linnakangas]

On 06/25/2015 03:03 PM, Andres Freund wrote:

The situation is this: We have broken code using broken code. I think we
either got to apply, darn nontrivial, fixes from
http://archives.postgresql.org/message-id/54DE6FAF.6050005%40vmware.com
or we got to cripple the options.

It's also not the first breakage, we've applied a lot of bandaids to
this code already. Our way of doing renegotiation also has broken
several SSL client implementations...


Note that even with those patches, renegotiation is still broken in some 
scenarios: 
http://www.postgresql.org/message-id/54dcf736.2060...@vmware.com. As far 
as I can tell, OpenSSL's handling of renegotiation is fundamentally 
broken, and there is nothing we can do in the application to completely 
work around that.


+1 for changing the default to disable renegotiation, in all branches.

- Heikki



--
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] Should we back-patch SSL renegotiation fixes?

[Peter Eisentraut]

On 6/25/15 8:03 AM, Andres Freund wrote:
> Right now if you use streaming rep over ssl, it breaks after a couple
> hundred megabytes with obscure errors.

If it's that bad, then I tend to agree we should turn it off by default.



-- 
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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Amit Kapila]

On Thu, Jun 25, 2015 at 6:10 PM, Ilya Kosmodemiansky <
ilya.kosmodemian...@postgresql-consulting.com> wrote:
>
> On Thu, Jun 25, 2015 at 1:49 PM, Amit Kapila 
wrote:
> >> Personally I think, that tracking waits is a not a good idea for
> >> pg_stat_activity (at least in that straight-forward manner).
> >
> > As mentioned in the initial mail by Robert, that sometimes system
becomes
> > slow (either due to contention on various kinds of locks or due to I/O
or
> > due
> > to some other such reasons) that such kind of handy information via some
> > view is quite useful.  Recently while working on one of the
> > performance/scalability
> > projects, I need to use gdb to attach to different processes to see what
> > they
> > are doing (of course one can use perf or some other utilities as well)
and I
> > found most of them were trying to wait on some LW locks, now having such
> > an information available via view could be really useful, because
sometimes
> > at customer sites, we can't use gdb or perf to see what's going on.
>
> Yes, I understand such a use-case. But I hardly see if suggested
> design can help for such cases.
>
> Basically, a DBA has two reasons to take a look on waits:
>
> 1. Long response time for particular query (or some type of queries).
> In that case it is good to know how much time we spend on waiting for
> particular resources we need to get query results
> 2. Overall bad performance of a database. We know, that something goes
> wrong and consumes resources, we need to identify which backend, which
> query causes the most of waits.
>
> In both cases we need a) some historical data rather than simple
> snapshot b) some approach how to aggregate it  because the will be
> certainly a lot of events
>

I think this thread's proposal will help for cases, when user/DBA wants to
see where currently database is spending most time (during waits).

I understand that there is a use of historical information which can
be helpful for the kind of cases which you have explained above.

>
> I think it is more than possible to mix both approaches. My proof of
> concept now is only about LWLocks - yours and Robert's is more
> general, and certainly some wait event classification will be needed
> for both approaches and its much better to implement one rather than
> two different.
>
> And at least, I will be interesting in reviewing your approach.
>

Okay, I am planning to spend time on this patch in coming few days
and when that's ready, may be we can see if that could be useful
for what you are planning to do.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] RFC: replace pg_stat_activity.waiting with something more descriptive

[Peter Eisentraut]

On 6/22/15 1:37 PM, Robert Haas wrote:
> Currently, the only time we report a process as waiting is when it is
> waiting for a heavyweight lock.  I'd like to make that somewhat more
> fine-grained, by reporting the type of heavyweight lock it's awaiting
> (relation, relation extension, transaction, etc.).  Also, I'd like to
> report when we're waiting for a lwlock, and report either the specific
> fixed lwlock for which we are waiting, or else the type of lock (lock
> manager lock, buffer content lock, etc.) for locks of which there is
> more than one.  I'm less sure about this next part, but I think we
> might also want to report ourselves as waiting when we are doing an OS
> read or an OS write, because it's pretty common for people to think
> that a PostgreSQL bug is to blame when in fact it's the operating
> system that isn't servicing our I/O requests very quickly.

Could that also cover waiting on network?



-- 
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] pgbench - allow backslash-continuations in custom scripts

[Tatsuo Ishii]

> Look, how many people in the world develop their own pgbench scripts?
> And how many of those aren't on this list right now, reading this
> thread?  I expect I could count them on my fingers and toes.

I'm not against you break the backward compatibility of pgbench custom
scripts.

However I just want to let you know that PostgreSQL Enterprise
Consortium has been published couple of scripts along with reports on
evaluating PostgreSQL, which have been downloaded considerable
numbers.

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


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

Re: [HACKERS] RFC: replace pg_stat_activity.waiting with something more descriptive

[Tom Lane]

Andres Freund  writes:
> On 2015-06-25 16:26:39 +0530, Amit Kapila wrote:
>> Won't leaving former contents as it is (until the next thing is being
>> blocked) could give misleading information.  Currently we mark 'waiting'
>> as false as soon as Heavy Weight Lock is over, so following that way
>> sounds more appropriate, is there any reason why you want it differently
>> than what we are doing currently?

> But we don't do the same for query, so I don't think that says much. I
> think it'd be useful because it gives you a bit more chance to see what
> you blocked on last, even if the time the backend was blocked was very
> short.

The problem with the query analogy is that it's possible to tell whether
the query is active or not, by looking at the status column.  We need to
avoid a situation where you can't tell if the wait status is current or
merely the last thing waited for.

At the moment I'm inclined to think we should put this on the back burner
until we see what Ilya submits.  None of the proposals for changing
pg_stat_activity sound terribly clean to me.

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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Andres Freund]

On 2015-06-25 10:01:39 -0400, Tom Lane wrote:
> Andres Freund  writes:
> > On 2015-06-25 16:26:39 +0530, Amit Kapila wrote:
> >> Won't leaving former contents as it is (until the next thing is being
> >> blocked) could give misleading information.  Currently we mark 'waiting'
> >> as false as soon as Heavy Weight Lock is over, so following that way
> >> sounds more appropriate, is there any reason why you want it differently
> >> than what we are doing currently?
> 
> > But we don't do the same for query, so I don't think that says much. I
> > think it'd be useful because it gives you a bit more chance to see what
> > you blocked on last, even if the time the backend was blocked was very
> > short.
> 
> The problem with the query analogy is that it's possible to tell whether
> the query is active or not, by looking at the status column.  We need to
> avoid a situation where you can't tell if the wait status is current or
> merely the last thing waited for.

Well, that's what the 'waiting' column would be about in the proposal I'm
commenting about.

> At the moment I'm inclined to think we should put this on the back burner
> until we see what Ilya submits.  None of the proposals for changing
> pg_stat_activity sound terribly clean to me.

We'll see. To me that's two different things. Knowing what a backend is
currently blocked on is a somewhat different use case from keeping
longer running stats. E.g. debugging why vacuum is not progressing
(waiting for a cleanup lock on a page that needs to be frozen) is just
about impossible right now.

Greetings,

Andres Freund


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

[HACKERS] Re: [COMMITTERS] pgsql: psql: show proper row count in \x mode for zero-column output

[Peter Eisentraut]

On 3/24/15 9:04 PM, Bruce Momjian wrote:
> psql:  show proper row count in \x mode for zero-column output
> 
> Also, fix pager enable selection for such cases, and other cleanups for
> zero-column output.
> 
> Report by Thom Brown
> 
> Branch
> --
> master
> 
> Details
> ---
> http://git.postgresql.org/pg/commitdiff/376a0c4547fe98c45476647596ce9c9b394f8415

This change added an extra blank line to the output of a zero-row result.

Compare:

[9.4]
$ psql -X -d postgres -c 'select * from pg_class where false' -x
(No rows)
$

[9.5]
$ psql -X -d postgres -c 'select * from pg_class where false' -x
(0 rows)

$


Was that intentional?



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

[HACKERS] Schedule for 9.5alpha1

[Tom Lane]

There was agreement at the PGCon dev meeting that we should put out a
9.5alpha1 release as soon as possible, to encourage wider testing
(and that it should be called an "alpha", because people aren't convinced
it's up to beta quality yet).  After a little back and forth, that release
has been set for next week, ie we'll wrap Monday June 29 for public
announcement Thursday July 2.

If there are any open 9.5 issues you can fix before Monday, please do.

Also, there was agreement that we'd begin the first 9.6 commitfest
on July 1.  I anticipate forking off the REL9_5_STABLE branch on
Tuesday to make way for that.

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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Tom Lane]

Andres Freund  writes:
> On 2015-06-25 10:01:39 -0400, Tom Lane wrote:
>> The problem with the query analogy is that it's possible to tell whether
>> the query is active or not, by looking at the status column.  We need to
>> avoid a situation where you can't tell if the wait status is current or
>> merely the last thing waited for.

> Well, that's what the 'waiting' column would be about in the proposal I'm
> commenting about.

To do that, we'd have to change the semantics of the 'waiting' column so
that it becomes true for non-heavyweight-lock waits.  I'm not sure whether
that's a good idea or not; I'm afraid there may be client-side code that
expects 'waiting' to indicate that there's a corresponding row in
pg_locks.  If we're willing to do that, then I'd be okay with
allowing wait_status to be defined as "last thing waited for"; but the
two points aren't separable.

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] GiST support for UUIDs

[Tom Lane]

Paul A Jungwirth  writes:
> I'm interested in adding GiST support for the UUID column type from
> the uuid-ossp extension. This has been requested and attempted before:
> I've used Postgres for a long time, but I've only dabbled a bit in the
> source code. So I'm curious where this change would go? The btree_gist
> extension? The uuid-ossp extension? Somewhere else?

btree_gist, I'd think, assuming you are only thinking of btree-equivalent
semantics and not anything more outre.  uuid-ossp is only concerned with
UUID generation algorithms, not with storage or indexing.  btree_gist
already has a bunch of infrastructure for this type of GiST opclass,
so it should be pretty straightforward to add it there (at least up to
making it work; the overhead of an extension version bump will probably
exceed the useful payload :-( ).

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] Oh, this is embarrassing: init file logic is still broken

[Tom Lane]

Tatsuo Ishii  writes:
>>> That means that load_relcache_init_file *always* decides that the init
>>> file is busted and silently(!) ignores it.  So we're taking a nontrivial
>>> hit in backend startup speed as of the last set of minor releases.

>> OK, this is pretty bad in its real performance effects.  On a workload
>> which is dominated by new connection creation, we've lost about 17%
>> throughput.

> Are we going to release 9.4.5 etc. soon?

I can't see doing a release just for this.  If we were due for releases
anyway, sure, but we've considerably overstressed our poor packagers of
late.  Previous discussion was to the effect that we'd anticipate another
set of releases in a month or so, after some more multixact fixes have
landed.

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] GiST support for UUIDs

[Paul A Jungwirth]

On Thu, Jun 25, 2015 at 8:06 AM, Tom Lane  wrote:
> Paul A Jungwirth  writes:
>> I'm interested in adding GiST support for the UUID column type
>> . . . . So I'm curious where this change would go?
> btree_gist, I'd think

Okay, thank you for your answer! I was worried about the effects of
having btree_gist depend on uuid-ossp. People won't have to say
`CREATE EXTENSION "uuid-ossp"` if they only want `btree_gist`, right?

> the overhead of an extension version bump will probably
> exceed the useful payload :-(

Sorry to put more work on your plate. :-) I'm trying to pick something
easy to get my feet wet.

Yours,
Paul


-- 
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] Add CINE for ALTER TABLE ... ADD COLUMN

[Fabrízio de Royes Mello]

On Wed, Jun 24, 2015 at 3:36 PM, Alvaro Herrera 
wrote:
>
> Fabrízio de Royes Mello wrote:
>
> > Another rebased version.
>
> There are a number of unrelated whitespace changes in this patch; also
> please update the comment on top of check_for_column_name_collision.
>

Sorry, bad merging after a pgident run. Comments on top of
check_for_column_name collision also updated.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 207fec1..339320e 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -36,7 +36,7 @@ ALTER TABLE ALL IN TABLESPACE name
 
 where action is one of:
 
-ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
+ADD [ COLUMN ] [ IF NOT EXISTS ]column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
 DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
 ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
 ALTER [ COLUMN ] column_name SET DEFAULT expression
@@ -96,11 +96,12 @@ ALTER TABLE ALL IN TABLESPACE name
 
   

-ADD COLUMN
+ADD COLUMN [ IF NOT EXISTS ]
 
  
   This form adds a new column to the table, using the same syntax as
-  .
+  . If IF NOT EXISTS
+  is specified and the column already exists, no error is thrown.
  
 

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d394713..94791e6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -328,8 +328,8 @@ static void ATPrepAddColumn(List **wqueue, Relation rel, bool recurse, bool recu
 bool is_view, AlterTableCmd *cmd, LOCKMODE lockmode);
 static ObjectAddress ATExecAddColumn(List **wqueue, AlteredTableInfo *tab,
 Relation rel, ColumnDef *colDef, bool isOid,
-bool recurse, bool recursing, LOCKMODE lockmode);
-static void check_for_column_name_collision(Relation rel, const char *colname);
+bool recurse, bool recursing, bool if_not_exists, LOCKMODE lockmode);
+static bool check_for_column_name_collision(Relation rel, const char *colname, bool if_not_exists);
 static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid);
 static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid);
 static void ATPrepAddOids(List **wqueue, Relation rel, bool recurse,
@@ -2302,7 +2302,7 @@ renameatt_internal(Oid myrelid,
 		oldattname)));
 
 	/* new name should not already exist */
-	check_for_column_name_collision(targetrelation, newattname);
+	check_for_column_name_collision(targetrelation, newattname, false);
 
 	/* apply the update */
 	namestrcpy(&(attform->attname), newattname);
@@ -3453,11 +3453,11 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		case AT_AddColumnToView:		/* add column via CREATE OR REPLACE
 		 * VIEW */
 			address = ATExecAddColumn(wqueue, tab, rel, (ColumnDef *) cmd->def,
-	  false, false, false, lockmode);
+	  false, false, false, false, lockmode);
 			break;
 		case AT_AddColumnRecurse:
 			address = ATExecAddColumn(wqueue, tab, rel, (ColumnDef *) cmd->def,
-	  false, true, false, lockmode);
+	  false, true, false, cmd->missing_ok, lockmode);
 			break;
 		case AT_ColumnDefault:	/* ALTER COLUMN DEFAULT */
 			address = ATExecColumnDefault(rel, cmd->name, cmd->def, lockmode);
@@ -3567,14 +3567,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
 			if (cmd->def != NULL)
 address =
 	ATExecAddColumn(wqueue, tab, rel, (ColumnDef *) cmd->def,
-	true, false, false, lockmode);
+	true, false, false, cmd->missing_ok, lockmode);
 			break;
 		case AT_AddOidsRecurse:	/* SET WITH OIDS */
 			/* Use the ADD COLUMN code, unless prep decided to do nothing */
 			if (cmd->def != NULL)
 address =
 	ATExecAddColumn(wqueue, tab, rel, (ColumnDef *) cmd->def,
-	true, true, false, lockmode);
+	true, true, false, cmd->missing_ok, lockmode);
 			break;
 		case AT_DropOids:		/* SET WITHOUT OIDS */
 
@@ -4672,7 +4672,7 @@ ATPrepAddColumn(List **wqueue, Relation rel, bool recurse, bool recursing,
 static ObjectAddress
 ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
 ColumnDef *colDef, bool isOid,
-bool recurse, bool recursing, LOCKMODE lockmode)
+bool recurse, bool recursing, bool if_not_exists, LOCKMODE lockmode)
 {
 	Oid			myrelid = RelationGetRelid(rel);
 	Relation	pgclass,
@@ -4767,7 +4767,13 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	relkind = ((Form_pg_class) GETSTRUCT(reltup))->relkind;
 
 	/* new name

Re: [HACKERS] Support for N synchronous standby servers - take 2

[Sawada Masahiko]

On Thu, Jun 25, 2015 at 7:32 AM, Simon Riggs  wrote:
> On 25 June 2015 at 05:01, Michael Paquier  wrote:
>>
>> On Thu, Jun 25, 2015 at 12:57 PM, Fujii Masao wrote:
>> > On Thu, Jun 25, 2015 at 12:15 PM, Michael Paquier wrote:
>> >> and that's actually equivalent to that in
>> >> the grammar: 1(AAA,BBB,CCC).
>> >
>> > I don't think that they are the same. In the case of 1(AAA,BBB,CCC),
>> > while
>> > two servers AAA and BBB are running, the master server may return a
>> > success
>> > of the transaction to the client just after it receives the ACK from
>> > BBB.
>> > OTOH, in the case of AAA,BBB, that never happens. The master must wait
>> > for
>> > the ACK from AAA to arrive before completing the transaction. And then,
>> > if AAA goes down, BBB should become synchronous standby.
>>
>> Ah. Right. I missed your point, that's a bad day... We could have
>> multiple separators to define group types then:
>> - "()" where the order of acknowledgement does not matter
>> - "[]" where it does not.
>> You would find the old grammar with:
>> 1[AAA,BBB,CCC]
>
> Let's start with a complex, fully described use case then work out how to
> specify what we want.
>
> I'm nervous of "it would be good ifs" because we do a ton of work only to
> find a design flaw.
>

I'm not sure specific implementation yet, but I came up with solution
for this case.

For example,
- s_s_name = '1(a, b), c, d'
The priority of both 'a' and 'b' are 1, and 'c' is 2, 'd' is 3.
i.g, 'b' and 'c' are potential sync node, and the quorum commit is
enable only between 'a' and 'b'.

- s_s_name = 'a, 1(b,c), d'
priority of 'a' is 1, 'b' and 'c' are 2, 'd' is 3.
So the quorum commit with 'b' and 'c' will be enabled after 'a' down.

With this idea, I think that we could use conventional syntax as in the past.
Though?

Regards,

--
Sawada Masahiko


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

[HACKERS] Serialization errors in Postgres 9.4.0

[BRUSSER Michael]

Some time ago we upgraded from v. 8.4.4 to 9.4.0 and we are seeing errors in 
some regression tests.

LOCATION:  exec_simple_query, postgres.c:887
ERROR:  40001: could not serialize access due to read/write dependencies among 
transactions

Detail can be one of these:
   DETAIL:  Reason code: Canceled on identification as a pivot, during commit 
attempt.
   DETAIL:  Reason code: Canceled on identification as a pivot, during write.
   DETAIL:  Reason code: Canceled on identification as a pivot, during conflict 
out checking.

Location is usually one of these:
   LOCATION:  PreCommit_CheckForSerializationFailure, predicate.c:4654
   LOCATION:  OnConflict_CheckForSerializationFailure, predicate.c:4600
   LOCATION:  CheckForSerializableConflictOut, predicate.c:3888

It may end up with
   ERROR:  25P02: current transaction is aborted, commands ignored until end of 
transaction block


The database at this point is likely to have a barrage of read and write ops 
against few tables.
I understand that using serializable transaction we probably should be ready to 
retry,
but it would be helpful to understand why we did not see so many errors in the 
past.

  Did something changed from 8.4.4 to 9.4.0?
  Maybe Postgres has more aggressive predicate locking mechanism now?
  Can it be that because of the small table size it performs sequential scan 
and locks the entire table?

Sorry if any of these questions are plain stupid.
Thanks,
Michael.




This email and any attachments are intended solely for the use of the 
individual or entity to whom it is addressed and may be confidential and/or 
privileged.

If you are not one of the named recipients or have received this email in error,

(i) you should not read, disclose, or copy it,

(ii) please notify sender of your receipt by reply email and delete this email 
and all attachments,

(iii) Dassault Systemes does not accept or assume any liability or 
responsibility for any use of or reliance on this email.

For other languages, go to http://www.3ds.com/terms/email-disclaimer

Re: [HACKERS] pgbench - allow backslash-continuations in custom scripts

[Jeff Janes]

On Wed, Jun 24, 2015 at 1:22 PM, Josh Berkus  wrote:

> On 06/21/2015 01:37 PM, Fabien COELHO wrote:
> >
> >> The worst case with pgbench is that we break two people's test scripts,
> >> they read the release notes, and fix them.
> >
> > Sure, I agree that breaking pgbench custom scripts compatibility is no
> > big deal, and having pgbench consistent with psql is useful.
>
> ... apparently nobody disagrees ...
>

I'm fine re-punctuating my current scripts.  And since pgbench doesn't have
to be version-matched to the server it connects to, people can just keep
using the older version if they want to against a new server.

Are there other breaking changes we have been wanting to make?  If so,
should we try to get them all in during the same release?

Cheers,

Jeff

Re: [HACKERS] GiST support for UUIDs

[Tom Lane]

Paul A Jungwirth  writes:
> On Thu, Jun 25, 2015 at 8:06 AM, Tom Lane  wrote:
>> Paul A Jungwirth  writes:
>>> I'm interested in adding GiST support for the UUID column type
>>> . . . . So I'm curious where this change would go?

>> btree_gist, I'd think

> Okay, thank you for your answer! I was worried about the effects of
> having btree_gist depend on uuid-ossp. People won't have to say
> `CREATE EXTENSION "uuid-ossp"` if they only want `btree_gist`, right?

No, the uuid type exists in core.  It's only some creation functions that
are in that extension.

>> the overhead of an extension version bump will probably
>> exceed the useful payload :-(

> Sorry to put more work on your plate. :-) I'm trying to pick something
> easy to get my feet wet.

That work will be on your plate actually ;-).  Read the docs concerning
creation of new extension versions, and perhaps look in our git history
for previous commits that have upgraded contrib extensions.

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] Serialization errors in Postgres 9.4.0

[David Fetter]

On Thu, Jun 25, 2015 at 04:02:33PM +, BRUSSER Michael wrote:
> Some time ago we upgraded from v. 8.4.4 to 9.4.0 and we are seeing errors in 
> some regression tests.
> 
> LOCATION:  exec_simple_query, postgres.c:887
> ERROR:  40001: could not serialize access due to read/write dependencies 
> among transactions

Would you be so kind as to send along a way to reproduce the problem
you are seeing, ideally a minimal one?

> The database at this point is likely to have a barrage of read and write ops 
> against few tables.
> I understand that using serializable transaction we probably should be ready 
> to retry,

Yes

> but it would be helpful to understand why we did not see so many errors in 
> the past.

Changing the transaction isolation level, which was mislabeled
"serializable" in 8.4 but was actually snapshot isolation, can produce
differences.

https://en.wikipedia.org/wiki/Snapshot_isolation

>   Did something changed from 8.4.4 to 9.4.0?

Yes.  9.1 introduced SSI, which added serialization checks to the
snapshot isolation that existed before.

https://wiki.postgresql.org/wiki/SSI

>   Maybe Postgres has more aggressive predicate locking mechanism now?
>   Can it be that because of the small table size it performs sequential scan 
> and locks the entire table?
> 
> Sorry if any of these questions are plain stupid.

Thanks for asking.  These questions are quite reasonable.

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

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


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

Re: [HACKERS] Serialization errors in Postgres 9.4.0

[Tom Lane]

BRUSSER Michael  writes:
> Some time ago we upgraded from v. 8.4.4 to 9.4.0 and we are seeing errors in 
> some regression tests.
> LOCATION:  exec_simple_query, postgres.c:887
> ERROR:  40001: could not serialize access due to read/write dependencies 
> among transactions

9.1 substantially tightened Postgres' ideas of what "serializable" means.
If you want to go back to the behavior you had in 8.4, use REPEATABLE READ
isolation level instead of SERIALIZABLE.

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] Should we back-patch SSL renegotiation fixes?

[Joshua D. Drake]

On 06/25/2015 06:15 AM, Peter Eisentraut wrote:


On 6/25/15 8:03 AM, Andres Freund wrote:

Right now if you use streaming rep over ssl, it breaks after a couple
hundred megabytes with obscure errors.


If it's that bad, then I tend to agree we should turn it off by default.



From an "in the wild perspective", we run into this all the time.

+1 to turn it off by default in all supported branches.

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] Oh, this is embarrassing: init file logic is still broken

[Josh Berkus]

On 06/25/2015 08:12 AM, Tom Lane wrote:
> Tatsuo Ishii  writes:
 That means that load_relcache_init_file *always* decides that the init
 file is busted and silently(!) ignores it.  So we're taking a nontrivial
 hit in backend startup speed as of the last set of minor releases.
> 
>>> OK, this is pretty bad in its real performance effects.  On a workload
>>> which is dominated by new connection creation, we've lost about 17%
>>> throughput.
> 
>> Are we going to release 9.4.5 etc. soon?
> 
> I can't see doing a release just for this.  If we were due for releases
> anyway, sure, but we've considerably overstressed our poor packagers of
> late.  Previous discussion was to the effect that we'd anticipate another
> set of releases in a month or so, after some more multixact fixes have
> landed.

FWIW, I know users who will not update because of this regression.
Consider applications which regularly need to spin up 200 new
connections in 90 seconds due to usage peaks.

On the other hand, do I want to do another update release right away?
No.  Hard place, meet rock.

-- 
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] Oh, this is embarrassing: init file logic is still broken

[Peter Geoghegan]

On Wed, Jun 24, 2015 at 2:52 PM, Josh Berkus  wrote:
> OK, this is pretty bad in its real performance effects.  On a workload
> which is dominated by new connection creation, we've lost about 17%
> throughput.

Mistakes happen, but this is the kind of regression that automated
performance testing could have caught. That's another area of testing
that needs considerable improvement IMV.

-- 
Peter Geoghegan


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

Re: [HACKERS] Oh, this is embarrassing: init file logic is still broken

[Josh Berkus]

On 06/25/2015 10:47 AM, Peter Geoghegan wrote:
> On Wed, Jun 24, 2015 at 2:52 PM, Josh Berkus  wrote:
>> OK, this is pretty bad in its real performance effects.  On a workload
>> which is dominated by new connection creation, we've lost about 17%
>> throughput.
> 
> Mistakes happen, but this is the kind of regression that automated
> performance testing could have caught. That's another area of testing
> that needs considerable improvement IMV.

Well, I have a pgbench test script for it now.  If only I had some place
to check it in ...

-- 
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] Oh, this is embarrassing: init file logic is still broken

[Tom Lane]

Josh Berkus  writes:
> On 06/25/2015 08:12 AM, Tom Lane wrote:
>> I can't see doing a release just for this.  If we were due for releases
>> anyway, sure, but we've considerably overstressed our poor packagers of
>> late.  Previous discussion was to the effect that we'd anticipate another
>> set of releases in a month or so, after some more multixact fixes have
>> landed.

> FWIW, I know users who will not update because of this regression.
> Consider applications which regularly need to spin up 200 new
> connections in 90 seconds due to usage peaks.

TBH, if your app is critically dependent on backend startup time,
You're Doing It Wrong, because that means you're pissing away significant
performance by not using a connection pooler.  So I don't have a huge
amount of sympathy.

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] Schedule for 9.5alpha1

[Peter Geoghegan]

On Thu, Jun 25, 2015 at 8:00 AM, Tom Lane  wrote:
> If there are any open 9.5 issues you can fix before Monday, please do.

I have 3 pending bug fixes for UPSERT, including 2 trivial ones. This
does not include my "minor refactoring" patch, which is not a bugfix.
The last of those 3 was posted on 2015-05-30.

I'm rather frustrated that it's so difficult to get maintenance/bugfix
patches committed. Frankly, as a non-committer, I don't see a reason
to bother trying to fix any open 9.5 items by Monday. The probability
of anyone picking them up by then seems very low.

-- 
Peter Geoghegan


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

Re: [HACKERS] Schedule for 9.5alpha1

[Andres Freund]

On June 25, 2015 9:35:27 PM GMT+02:00, Peter Geoghegan  wrote:
>On Thu, Jun 25, 2015 at 8:00 AM, Tom Lane  wrote:
>> If there are any open 9.5 issues you can fix before Monday, please
>do.
>
>I have 3 pending bug fixes for UPSERT, including 2 trivial ones. This
>does not include my "minor refactoring" patch, which is not a bugfix.
>The last of those 3 was posted on 2015-05-30.
>
>I'm rather frustrated that it's so difficult to get maintenance/bugfix
>patches committed. Frankly, as a non-committer, I don't see a reason
>to bother trying to fix any open 9.5 items by Monday. The probability
>of anyone picking them up by then seems very low.

It'd have been easier if you'd done it the way I asked. This way I have to look 
much closer...


--- 
Please excuse brevity and formatting - I am writing this on my mobile phone.


-- 
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] Schedule for 9.5alpha1

[Peter Geoghegan]

On Thu, Jun 25, 2015 at 12:39 PM, Andres Freund  wrote:
> It'd have been easier if you'd done it the way I asked. This way I have to 
> look much closer...

Well, I actually prototyped your approach to that and decided against
it only after having gone to the trouble of doing so. In any case, I
think it's appropriate that you review the wholerow var bugfix patch
carefully. So I'm not talking about that one, as I think you gathered.

I'm not singling you out; clearly you were very busy with other, more
important things the entire time. I'm just giving my perspective.

-- 
Peter Geoghegan


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

Re: [HACKERS] [PATCH] Function to get size of asynchronous notification queue

[Gurjeet Singh]

Patch reviewed following the instructions on
https://wiki.postgresql.org/wiki/Reviewing_a_Patch

# Submission review
- Is the patch in a patch format which has context?
Yes. Note to other reviewers: `git diff —patience` yields patch better
suited for readability

- Does it apply cleanly to the current git master?
Yes.

- Does it include reasonable tests, necessary doc patches, etc?
Doc patch - Yes.
Tests - Yes.

# Usability review
- Does the patch actually implement the feature?
Yes.

- Do we want that?
Yes; see the discussion in mailing list.

- Do we already have it?
No.

- Does it follow SQL spec, or the community-agreed behavior?
Yes. It seems to implement the behavior agreed upon in the mailing list.

- Does it include pg_dump support (if applicable)?
N/A

- Are there dangers?
None that I could spot.

- Have all the bases been covered?
There’s room for an additional test which tests for non-zero return value.

# Feature test
- Does the feature work as advertised?
Yes. Build configured with '--enable-debug --enable-cassert CFLAGS=-O0’.
With a slightly aggressive notifications-in-a-loop script I was able to see
non-zero return value:

Session 1:
listen ggg;
begin;

Session 2:
while sleep 0.1; do echo 'notify ggg; select
pg_notification_queue_usage();' ; done | psql

- Are there corner cases the author has failed to consider?
No.

- Are there any assertion failures or crashes?
The patch exposes an already available function to the SQL interface, and
rearranges code, so it doesn’t look like the patch can induce an assertion
or a crash.

- Performance review
Not evaluated, since it’s not a performance patch, and it doesn’t seem to
impact any performance critical code path, ,either.


Additional notes:

Patch updates the docs of another function (pg_listening_channels), but the
update is an improvement so we can let it slide :)

+   proportion of the queue that is currently occupied by pending
notifications.

s/proportion/fraction/

+ * The caller must hold (at least) shared AysncQueueLock.

A possibly better wording: The caller must hold AysncQueueLock in (at
least) shared mode.

Unnecessary whitespace changes in pg_proc.h for existing functions.

+DESCR("get the current usage of the asynchronous notification queue");

A possibly better wording: get the fraction of the asynchronous
notification queue currently in use


On Thu, Jun 18, 2015 at 10:47 AM, Merlin Moncure  wrote:

> On Wed, Jun 17, 2015 at 6:15 PM, Brendan Jurd  wrote:
> > Posting v2 of the patch, incorporating some helpful suggestions from
> Merlin.
>
> Based on perfunctory scan of the code, I think this is gonna make it,
> unless you draw some objections based on lack of necessity.
>
> merlin
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>



-- 
Gurjeet Singh http://gurjeet.singh.im/

Re: [HACKERS] Oh, this is embarrassing: init file logic is still broken

[Josh Berkus]

On 06/25/2015 11:04 AM, Tom Lane wrote:
> Josh Berkus  writes:
>> On 06/25/2015 08:12 AM, Tom Lane wrote:
>>> I can't see doing a release just for this.  If we were due for releases
>>> anyway, sure, but we've considerably overstressed our poor packagers of
>>> late.  Previous discussion was to the effect that we'd anticipate another
>>> set of releases in a month or so, after some more multixact fixes have
>>> landed.
> 
>> FWIW, I know users who will not update because of this regression.
>> Consider applications which regularly need to spin up 200 new
>> connections in 90 seconds due to usage peaks.
> 
> TBH, if your app is critically dependent on backend startup time,
> You're Doing It Wrong, because that means you're pissing away significant
> performance by not using a connection pooler.  So I don't have a huge
> amount of sympathy.

Most of these apps are using a connection pooler.  But usage is very
spiky, and one doesn't want to keep around 300 idle connections all the
time for the 5 minutes when you need them.  Indeed, pgbouncer is
configured to spin down idle connections after a configured amount of
time idle, as are most connection poolers.

Regardless, I don't think it's a good idea to release again sooner than
planned.  It is a good reason to look at making connection creation time
a regular test, though; you can be sure I'll be testing 9.4.5 and 9.5a!

-- 
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] broken documentation: BackgroundWorkerInitializeConnection(NULL, NULL);

[Robert Haas]

On Thu, May 21, 2015 at 11:47 PM, Alvaro Herrera
 wrote:
> Robert Haas wrote:
>> On Fri, May 15, 2015 at 4:15 PM, Alvaro Herrera
>>  wrote:
>> >> Really?  I was thinking of the test code as throwaway.  I just wanted
>> >> to fix the bug.
>> >
>> > Oh, that's fine then.  I thought you wanted to push it.
>>
>> Nah, sorry, I shoulda been more clear about that.  That was just so I
>> could actually be sure I had the fix right.
>
> I think you forgot to push this one ...

Yeah, you're right.  Done now after two other people reminded me of
the same thing.

-- 
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] Schedule for 9.5alpha1

[Kouhei Kaigai]

> -Original Message-
> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane
> Sent: Friday, June 26, 2015 12:00 AM
> To: pgsql-hackers@postgreSQL.org
> Subject: [HACKERS] Schedule for 9.5alpha1
> 
> There was agreement at the PGCon dev meeting that we should put out a
> 9.5alpha1 release as soon as possible, to encourage wider testing
> (and that it should be called an "alpha", because people aren't convinced
> it's up to beta quality yet).  After a little back and forth, that release
> has been set for next week, ie we'll wrap Monday June 29 for public
> announcement Thursday July 2.
> 
> If there are any open 9.5 issues you can fix before Monday, please do.
>
I have a serious open item reported 1.5 month ago then reminded
several times has not been fixed up yet.

9a28c8860f777e439aa12e8aea7694f8010f3...@bpxm15gp.gisp.nec.co.jp

Patch is less than 100 lines, entirely designed according to Tom's suggestion.

The problem is, commit 1a8a4e5cde2b7755e11bde2ea7897bd650622d3e reverted
create_plan_recurse() to static function, thus, extension lost way to
transform Path node to Plan node if it wants to takes underlying child
nodes, like SeqScan, HashJoin and so on.

Tom's suggestion is to add a list of Path nodes on CustomPath structure,
to be transformed by createplan.c, instead of public create_plan_recurse().

It is nearly obvious problem, and bugfix patch already exists.

Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei 


custom-join-children.v2.patch
Description: custom-join-children.v2.patch

-- 
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] Schedule for 9.5alpha1

[Peter Geoghegan]

On Thu, Jun 25, 2015 at 3:25 PM, Kouhei Kaigai  wrote:
> It is nearly obvious problem, and bugfix patch already exists.

My current strategy is to post these items on the "PostgreSQL 9.5 Open
Items" page, even when the issue is totally trivial -- maybe that
makes a small difference, even if it sometimes feels inappropriate for
small items.

There are some other items with clear or clear-ish fixes beyond the
ones that I mentioned. For one further example not involving UPSERT,
ISTM based on pgCon discussion that "Arguable RLS security bug,
EvalPlanQual() paranoia" can be fixed with a well written doc patch,
once we figure out exactly how to explain the issue, which should be
straightforward. For another, the rest of the jsonb stuff is now
almost open-and-shut (again, this conclusion is based on discussion
during pgCon, in this instance with Andrew). That fix involves an
actual small adjustment to semantics. This is just the stuff I'm
involved in. I think that the experience of myself and Kaigai-san with
bug fixes are representative.

Some committers request that I post simple bugfixes to the next
commitfest. That might help with making sure that they eventually get
some attention, which makes some sense at a different time in the
cycle, but it does not help at all with making sure that they get
timely attention when we're up against a deadline for putting out a
release (even if it is an alpha). Besides, even when a release is not
upcoming, my strong personal preference is to fix bugs ASAP.

I'm tired of having to chase down known bugs when a patch has been
around for a long time, and an actual fix is blocking on committer
availability -- sometimes I feel the need to privately twist someone's
arm just to get something done that should be straightforward. If this
is the way things are supposed to work, we should document known bugs
in the alpha release notes.

-- 
Peter Geoghegan


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

Re: [HACKERS] Schedule for 9.5alpha1

[Michael Paquier]

On Fri, Jun 26, 2015 at 8:03 AM, Peter Geoghegan  wrote:
> On Thu, Jun 25, 2015 at 3:25 PM, Kouhei Kaigai  wrote:
>> It is nearly obvious problem, and bugfix patch already exists.
>
> My current strategy is to post these items on the "PostgreSQL 9.5 Open
> Items" page, even when the issue is totally trivial -- maybe that
> makes a small difference, even if it sometimes feels inappropriate for
> small items.

This is the right approach to me, this page being dedicated to that.
And I am doing the same when I spot something.

> I'm tired of having to chase down known bugs when a patch has been
> around for a long time, and an actual fix is blocking on committer
> availability -- sometimes I feel the need to privately twist someone's
> arm just to get something done that should be straightforward.

Patience is the key here IMO, committer time being precious. And I
guess that the requirement for the .0 release will be to clear all
those items btw, so they will be fixed at some point.

> If this is the way things are supposed to work, we should document known bugs
> in the alpha release notes.

Perhaps. Being able to track them in the code tree does not sound like
a bad thing to me.
-- 
Michael


-- 
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] pgbench - allow backslash-continuations in custom scripts

[Michael Paquier]

On Thu, Jun 25, 2015 at 10:51 PM, Tatsuo Ishii  wrote:
>> Look, how many people in the world develop their own pgbench scripts?
>> And how many of those aren't on this list right now, reading this
>> thread?  I expect I could count them on my fingers and toes.
>
> I'm not against you break the backward compatibility of pgbench custom
> scripts.
>
> However I just want to let you know that PostgreSQL Enterprise
> Consortium has been published couple of scripts along with reports on
> evaluating PostgreSQL, which have been downloaded considerable
> numbers.

pgbench is a tool for dedicated to developers. Hence people who should
be able to fix scripts properly as long as we inform them by
documenting it in the release notes so I am not sure that this is
actually a problem.
-- 
Michael


-- 
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] pgbench - allow backslash-continuations in custom scripts

[Tatsuo Ishii]

>> I'm not against you break the backward compatibility of pgbench custom
>> scripts.
>>
>> However I just want to let you know that PostgreSQL Enterprise
>> Consortium has been published couple of scripts along with reports on
>> evaluating PostgreSQL, which have been downloaded considerable
>> numbers.
> 
> pgbench is a tool for dedicated to developers. Hence people who should
> be able to fix scripts properly as long as we inform them by
> documenting it in the release notes so I am not sure that this is
> actually a problem.

I'm not sure what you mean by "developers" here but if that means
people who are developing PostgreSQL itself, I am strongly against
"pgbench is a tool for dedicated to developers" concept. Pgbench has
been heavily used by users who want to measure PostgreSQL's
performance.

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


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

Re: [HACKERS] git push hook to check for outdated timestamps

[Robert Haas]

On Wed, Jun 24, 2015 at 3:50 PM, Peter Eisentraut  wrote:
> On 6/24/15 12:55 PM, Robert Haas wrote:
>>> FWIW, I have been doing that, and I have not considered it a problem.
>>> If the patch was submitted three months ago, reviewed, and then
>>> committed unchanged, the date is what it is.  Also, when I cherry-pick a
>>> commit from master to a back branch, I keep the author timestamp the
>>> same.  I consider that a feature.
>>
>> I don't, because it means that the timestamps you see when you run git
>> log are non-linear.  I don't care myself if they are slightly out of
>> order, although it seems that others do, but I do mind when they are
>> months off.
>
> Why is that a problem?

Because I don't want to have to do git log --format=fuller to see when
the thing was committed, basically.

>> Typically when this happens to me, it's not a case of the patch being
>> unchanged.  I make changes on a branch and then use git rebase -i to
>> squash them into a single patch which I then cherry-pick.  But git
>> rebase -i keeps the timestamp of the first (oldest) commit, so I end
>> up with a commit that is timestamped as to when I began development,
>> not when I finished it.  So the date is just wrong.
>
> Sure, but then it's up to you to fix it, just like it's up to you to
> merge the commit messages and do other adjustments to the commit.  But
> this is one of many cases, and we shouldn't throw out the whole concept
> because of it.

I don't particularly think that having separate AuthorDate and
CommitterDate fields has any value.  At least, it doesn't to me.  But
a linear-looking commit history does have value to me.  Someone else
might have different priorities; those are mine.

-- 
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] Schedule for 9.5alpha1

[Peter Geoghegan]

On Thu, Jun 25, 2015 at 4:30 PM, Michael Paquier
 wrote:
>> I'm tired of having to chase down known bugs when a patch has been
>> around for a long time, and an actual fix is blocking on committer
>> availability -- sometimes I feel the need to privately twist someone's
>> arm just to get something done that should be straightforward.
>
> Patience is the key here IMO, committer time being precious. And I
> guess that the requirement for the .0 release will be to clear all
> those items btw, so they will be fixed at some point.

Some more transparency in both directions, and the ability to triage
bugs would be nice. Sometimes things aren't that complicated, but are
still important (while other times, things can be complicated and
unimportant). I have no good way of making a representation about
which category any given bug fix falls under.

Also, while the commitfest app makes it pretty likely that someone
will get around to considering a bug fix eventually, the time that
that takes is completely unbounded.

-- 
Peter Geoghegan


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

Re: [HACKERS] pgbench - allow backslash-continuations in custom scripts

[Michael Paquier]

On Fri, Jun 26, 2015 at 9:01 AM, Tatsuo Ishii  wrote:
>>> I'm not against you break the backward compatibility of pgbench custom
>>> scripts.
>>>
>>> However I just want to let you know that PostgreSQL Enterprise
>>> Consortium has been published couple of scripts along with reports on
>>> evaluating PostgreSQL, which have been downloaded considerable
>>> numbers.
>>
>> pgbench is a tool for dedicated to developers. Hence people who should
>> be able to fix scripts properly as long as we inform them by
>> documenting it in the release notes so I am not sure that this is
>> actually a problem.
>
> I'm not sure what you mean by "developers" here but if that means
> people who are developing PostgreSQL itself, I am strongly against
> "pgbench is a tool for dedicated to developers" concept. Pgbench has
> been heavily used by users who want to measure PostgreSQL's
> performance.

I meant "people who can write SQL". Sorry for the misunderstanding.
Please do not take any offense ;)
-- 
Michael


-- 
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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Amit Kapila]

On Thu, Jun 25, 2015 at 8:20 PM, Tom Lane  wrote:
>
> Andres Freund  writes:
> > On 2015-06-25 10:01:39 -0400, Tom Lane wrote:
> >> The problem with the query analogy is that it's possible to tell
whether
> >> the query is active or not, by looking at the status column.  We need
to
> >> avoid a situation where you can't tell if the wait status is current or
> >> merely the last thing waited for.
>
> > Well, that's what the 'waiting' column would be about in the proposal
I'm
> > commenting about.
>
> To do that, we'd have to change the semantics of the 'waiting' column so
> that it becomes true for non-heavyweight-lock waits.

If we introduce a new view like pg_stat_wait_event as mentioned above,
then we can avoid this problem, existing 'waiting' in pg_stat_activity
would mean same as it mean today and new column 'waiting' in
pg_stat_wait_event could indicate the waits for non-heavyweight-lock.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

[HACKERS] BRIN index bug due to WAL refactoring

[Jeff Janes]

BRIN index WAL is broken in HEAD.

Commit 2c03216d831160bedd72d4, the Revamp the WAL record format, is the
culprit.

The easiest way to see this is via streaming replication.

On master:

create table foobar as select * from generate_series(1,1);
create index on foobar using brin (generate_series );

On replica:

set enable_seqscan TO off;
explain (analyze) select count(*) from foobar ;
ERROR:  corrupted BRIN index: inconsistent range map

Cheers,

Jeff

Re: [HACKERS] BRIN index bug due to WAL refactoring

[Alvaro Herrera]

Jeff Janes wrote:
> BRIN index WAL is broken in HEAD.

Thanks for all the details.  Looking into it.



-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Schedule for 9.5alpha1

[Joshua D. Drake]

On 06/25/2015 05:09 PM, Peter Geoghegan wrote:


On Thu, Jun 25, 2015 at 4:30 PM, Michael Paquier
 wrote:

I'm tired of having to chase down known bugs when a patch has been
around for a long time, and an actual fix is blocking on committer
availability -- sometimes I feel the need to privately twist someone's
arm just to get something done that should be straightforward.


Patience is the key here IMO, committer time being precious. And I
guess that the requirement for the .0 release will be to clear all
those items btw, so they will be fixed at some point.


Some more transparency in both directions, and the ability to triage
bugs would be nice. Sometimes things aren't that complicated, but are
still important (while other times, things can be complicated and
unimportant). I have no good way of making a representation about
which category any given bug fix falls under.

Also, while the commitfest app makes it pretty likely that someone
will get around to considering a bug fix eventually, the time that
that takes is completely unbounded.


Perhaps Heroku could sponsor a committer or two for triage. I know there 
are a couple that are not currently bound by other Pg companies.


Sincerely,

JD




--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Robert Haas]

On Thu, Jun 25, 2015 at 6:46 AM, Andres Freund  wrote:
>> 1. Remove/Change 'waiting' in pg_stat_activity and break the backward
>> compatibility.  I think we should try to avoid going via this route.
>>
>> 2. Add 2 new columns to pg_stat_activity
>> waiting_resource - true for waits other heavy wait locks, false
>>otherwise
>> wait_event - description code for the wait event
>>
>> 3. Add new view 'pg_stat_wait_event' with following info:
>> pid   - process id of this backend
>> waiting - true for any form of wait, false otherwise
>> wait_event_type - Heavy Weight Lock, Light Weight Lock, I/O wait, etc
>> wait_event - Lock (Relation), Lock (Relation Extension), etc
>>
>> Do you think 2nd or 3rd could be viable way to proceed for this feature?
>
> 3) sounds best to me. Keeping 'waiting' even makes sense in that case,
> because it'll tell whether wait_event_type is currently being blocked
> on. We can leave the former contents in until the next thing is being
> blocked...

So, that's still redefining the "waiting" column, because it will now
indicate whether we are waiting on some wait event, not whether we are
waiting on specifically a heavyweight lock.  But that doesn't bother
me, because I think it's going to be darn confusing if we keep
"waiting" around with the specific meaning of "waiting for a
heavyweight lock" while also now having a notion of "waiting for
something else".  I like the idea of indicating both the most recent
wait event and whether or not we are still waiting for it - we refined
current_query to query not too long ago, and I certainly think that
was a significant improvement even if it broke some people's scripts.

I am pretty unconvinced that it's a good idea to try to split up the
wait event into two columns.  I'm only proposing ~20 wait states, so
there's something like 5 bits of information here.  Spreading that
over two text columns is a lot, and note that Amit's text would
basically recapitulate the contents of the first column in the second
one, which I cannot get excited about.

-- 
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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Robert Haas]

On Thu, Jun 25, 2015 at 6:58 AM, Ilya Kosmodemiansky
 wrote:
> 1.  Some sort of histogram of top waits within entire database by pid.
> That will be an approximate one, because  I hardly believe there is a
> possibility to make a precise one without significant overhead.

You could compute that histogram from the data I am proposing to
publish.  Indeed, it's hard to see what other fundamentally different
mechanism you would use.  The backends have got to advertise their
state in shared memory someplace, which my proposal would do, and then
you've got to poll that data somewhere else, which I'm not proposing
to do but it could be done.

> 2. Some cyclic buffer  of more precise wait statistic inside each
> worker. Sampling may be turned on if we see some issues in histogram
> (1) and want to have some more details.

That could be built on top of this, too.

Both of those ideas require the information that my proposal would
provide, but the information this proposal would provide is still
useful if we don't do those other things.

-- 
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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Robert Haas]

On Thu, Jun 25, 2015 at 9:23 AM, Peter Eisentraut  wrote:
> On 6/22/15 1:37 PM, Robert Haas wrote:
>> Currently, the only time we report a process as waiting is when it is
>> waiting for a heavyweight lock.  I'd like to make that somewhat more
>> fine-grained, by reporting the type of heavyweight lock it's awaiting
>> (relation, relation extension, transaction, etc.).  Also, I'd like to
>> report when we're waiting for a lwlock, and report either the specific
>> fixed lwlock for which we are waiting, or else the type of lock (lock
>> manager lock, buffer content lock, etc.) for locks of which there is
>> more than one.  I'm less sure about this next part, but I think we
>> might also want to report ourselves as waiting when we are doing an OS
>> read or an OS write, because it's pretty common for people to think
>> that a PostgreSQL bug is to blame when in fact it's the operating
>> system that isn't servicing our I/O requests very quickly.
>
> Could that also cover waiting on network?

Possibly.  My approach requires that the number of wait states be kept
relatively small, ideally fitting in a single byte.  And it also
requires that we insert pgstat_report_waiting() calls around the thing
that is notionally blocking.  So, if there are a small number of
places in the code where we do network I/O, we could stick those calls
around those places, and this would work just fine.  But if a foreign
data wrapper, or any other piece of code, does network I/O - or any
other blocking operation - without calling pgstat_report_waiting(), we
just won't know about it.

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


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

Re: [HACKERS] [PATCH] Function to get size of asynchronous notification queue

[Brendan Jurd]

On Fri, 26 Jun 2015 at 06:03 Gurjeet Singh  wrote:

> Patch reviewed following the instructions on
> https://wiki.postgresql.org/wiki/Reviewing_a_Patch
>
>
Thank you for your review, Gurjeet.



> s/proportion/fraction/
>

I think of these as synonymous -- do you have any particular reason to
prefer "fraction"?  I don't feel strongly about it either way, so I'm quite
happy to go with fraction if folks find that more expressive.


>
> + * The caller must hold (at least) shared AysncQueueLock.
>
> A possibly better wording: The caller must hold AysncQueueLock in (at
> least) shared mode.
>

Yes, that is more accurate.


>
> Unnecessary whitespace changes in pg_proc.h for existing functions.
>
>
I did group the asynchronous notification functions together, which seemed
reasonable as there are now three of them, and changed the tabbing between
the function name and namespace ID to match, as is done elsewhere in
pg_proc.h.  I think those changes improve readability, but again I don't
feel strongly about it.


+DESCR("get the current usage of the asynchronous notification queue");
>
> A possibly better wording: get the fraction of the asynchronous
> notification queue currently in use
>

I have no objections to your wording.

Cheers,
BJ

Re: [HACKERS] Schedule for 9.5alpha1

[Robert Haas]

On Thu, Jun 25, 2015 at 6:25 PM, Kouhei Kaigai  wrote:
> I have a serious open item reported 1.5 month ago then reminded
> several times has not been fixed up yet.
>
> 9a28c8860f777e439aa12e8aea7694f8010f3...@bpxm15gp.gisp.nec.co.jp
>
> Patch is less than 100 lines, entirely designed according to Tom's suggestion.
>
> The problem is, commit 1a8a4e5cde2b7755e11bde2ea7897bd650622d3e reverted
> create_plan_recurse() to static function, thus, extension lost way to
> transform Path node to Plan node if it wants to takes underlying child
> nodes, like SeqScan, HashJoin and so on.
>
> Tom's suggestion is to add a list of Path nodes on CustomPath structure,
> to be transformed by createplan.c, instead of public create_plan_recurse().
>
> It is nearly obvious problem, and bugfix patch already exists.

Yes, I am quite unhappy with this situation.  Tom promised me at PGCon
that he would look at this soon, but there is no sign that he has, and
he said the same thing weeks ago.  I think it can't be right to let
this sit for another month or three.  Even if the API you've
implemented is worse than something Tom can design, it is certainly
better than the status quo.  I would rather have a working but
imperfect API and have to break compatibility later in beta than have
a non-working API.

-- 
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] RFC: replace pg_stat_activity.waiting with something more descriptive

[Amit Kapila]

On Fri, Jun 26, 2015 at 9:01 AM, Robert Haas  wrote:
>
> On Thu, Jun 25, 2015 at 6:46 AM, Andres Freund  wrote:
> >> 1. Remove/Change 'waiting' in pg_stat_activity and break the backward
> >> compatibility.  I think we should try to avoid going via this route.
> >>
> >> 2. Add 2 new columns to pg_stat_activity
> >> waiting_resource - true for waits other heavy wait locks, false
> >>otherwise
> >> wait_event - description code for the wait event
> >>
> >> 3. Add new view 'pg_stat_wait_event' with following info:
> >> pid   - process id of this backend
> >> waiting - true for any form of wait, false otherwise
> >> wait_event_type - Heavy Weight Lock, Light Weight Lock, I/O wait, etc
> >> wait_event - Lock (Relation), Lock (Relation Extension), etc
> >>
> >> Do you think 2nd or 3rd could be viable way to proceed for this
feature?
> >
> > 3) sounds best to me. Keeping 'waiting' even makes sense in that case,
> > because it'll tell whether wait_event_type is currently being blocked
> > on. We can leave the former contents in until the next thing is being
> > blocked...
>
> So, that's still redefining the "waiting" column, because it will now
> indicate whether we are waiting on some wait event, not whether we are
> waiting on specifically a heavyweight lock.  But that doesn't bother
> me, because I think it's going to be darn confusing if we keep
> "waiting" around with the specific meaning of "waiting for a
> heavyweight lock" while also now having a notion of "waiting for
> something else".  I like the idea of indicating both the most recent
> wait event and whether or not we are still waiting for it - we refined
> current_query to query not too long ago, and I certainly think that
> was a significant improvement even if it broke some people's scripts.
>
> I am pretty unconvinced that it's a good idea to try to split up the
> wait event into two columns.  I'm only proposing ~20 wait states, so
> there's something like 5 bits of information here.  Spreading that
> over two text columns is a lot, and note that Amit's text would
> basically recapitulate the contents of the first column in the second
> one, which I cannot get excited about.
>

There is an advantage in splitting the columns which is if wait_event_type
column indicates Heavy Weight Lock, then user can go and check further
details in pg_locks, I think he can do that even by seeing wait_event
column, but that might not be as straightforward as with wait_event_type
column.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] Support for N synchronous standby servers - take 2

[Michael Paquier]

On Thu, Jun 25, 2015 at 8:32 PM, Simon Riggs  wrote:
> Let's start with a complex, fully described use case then work out how to
> specify what we want.

Well, one of the most simple cases where quorum commit and this
feature would be useful for is that, with 2 data centers:
- on center 1, master A and standby B
- on center 2, standby C and standby D
With the current synchronous_standby_names, what we can do now is
ensuring that one node has acknowledged the commit of master. For
example synchronous_standby_names = 'B,C,D'. But you know that :)
What this feature would allow use to do is for example being able to
ensure that a node on the data center 2 has acknowledged the commit of
master, meaning that even if data center 1 completely lost for a
reason or another we have at least one node on center 2 that has lost
no data at transaction commit.

Now, regarding the way to express that, we need to use a concept of
node group for each element of synchronous_standby_names. A group
contains a set of elements, each element being a group or a single
node. And for each group we need to know three things when a commit
needs to be acknowledged:
- Does my group need to acknowledge the commit?
- If yes, how many elements in my group need to acknowledge it?
- Does the order of my elements matter?

That's where the micro-language idea makes sense to use. For example,
we can define a group using separators and like (elt1,...eltN) or
[elt1,elt2,eltN]. Appending a number in front of a group is essential
as well for quorum commits. Hence for example, assuming that '()' is
used for a group whose element order does not matter, if we use that:
- k(elt1,elt2,eltN) means that we need for the k elements in the set
to return true (aka commit confirmation).
- k[elt1,elt2,eltN] means that we need for the first k elements in the
set to return true.

When k is not defined for a group, k = 1. Using only elements
separated by commas for the upper group means that we wait for the
first element in the set (for backward compatibility), hence:
1(elt1,elt2,eltN) <=> elt1,elt2,eltN

We could as well mix each behavior, aka being able to define for a
group to wait for the first k elements and a total of j elements in
the whole set, but I don't think that we need to go that far. I
suspect that in most cases users will be satisfied with only cases
where there is a group of data centers, and they want to be sure that
one or two in each center has acknowledged a commit to master
(performance is not the matter here if centers are not close). Hence
in the case above, you could get the behavior wanted with this
definition:
2(B,(C,D))
With more data centers, like 3 (wait for two nodes in the 3rd set):
3(B,(C,D),2(E,F,G))
Users could define more levels of group, like that:
2(A,(B,(C,D)))
But that's actually something few people would do in real cases.

> I'm nervous of "it would be good ifs" because we do a ton of work only to
> find a design flaw.

That makes sense. Let's continue arguing on it then.
-- 
Michael


-- 
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 N synchronous standby servers - take 2

[Amit Langote]

Hi,

On 2015-06-26 AM 12:49, Sawada Masahiko wrote:
> On Thu, Jun 25, 2015 at 7:32 AM, Simon Riggs  wrote:
>>
>> Let's start with a complex, fully described use case then work out how to
>> specify what we want.
>>
>> I'm nervous of "it would be good ifs" because we do a ton of work only to
>> find a design flaw.
>>
> 
> I'm not sure specific implementation yet, but I came up with solution
> for this case.
> 
> For example,
> - s_s_name = '1(a, b), c, d'
> The priority of both 'a' and 'b' are 1, and 'c' is 2, 'd' is 3.
> i.g, 'b' and 'c' are potential sync node, and the quorum commit is
> enable only between 'a' and 'b'.
> 
> - s_s_name = 'a, 1(b,c), d'
> priority of 'a' is 1, 'b' and 'c' are 2, 'd' is 3.
> So the quorum commit with 'b' and 'c' will be enabled after 'a' down.
> 

Do we really need to add a number like '1' in '1(a, b), c, d'?

The order of writing names already implies priorities like 2 & 3 for c & d,
respectively, like in your example. Having to write '1' for the group '(a, b)'
seems unnecessary, IMHO. Sorry if I have missed any previous discussion where
its necessity was discussed.

So, the order of writing standby names in the list should declare their
relative priorities and parentheses (possibly nested) should help inform about
the grouping (for quorum?)

Thanks,
Amit



-- 
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 N synchronous standby servers - take 2

[Michael Paquier]

On Fri, Jun 26, 2015 at 2:59 PM, Amit Langote wrote:
> Do we really need to add a number like '1' in '1(a, b), c, d'?
> The order of writing names already implies priorities like 2 & 3 for c & d,
> respectively, like in your example. Having to write '1' for the group '(a, b)'
> seems unnecessary, IMHO. Sorry if I have missed any previous discussion where
> its necessity was discussed.

'1' is implied if no number is specified. That's the idea as written
here, not something decided of course :)

> So, the order of writing standby names in the list should declare their
> relative priorities and parentheses (possibly nested) should help inform about
> the grouping (for quorum?)

Yes.
-- 
Michael


-- 
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 N synchronous standby servers - take 2

[Amit Langote]

On 2015-06-26 PM 02:59, Amit Langote wrote:
> On 2015-06-26 AM 12:49, Sawada Masahiko wrote:
>>
>> For example,
>> - s_s_name = '1(a, b), c, d'
>> The priority of both 'a' and 'b' are 1, and 'c' is 2, 'd' is 3.
>> i.g, 'b' and 'c' are potential sync node, and the quorum commit is
>> enable only between 'a' and 'b'.
>>
>> - s_s_name = 'a, 1(b,c), d'
>> priority of 'a' is 1, 'b' and 'c' are 2, 'd' is 3.
>> So the quorum commit with 'b' and 'c' will be enabled after 'a' down.
>>
> 
> Do we really need to add a number like '1' in '1(a, b), c, d'?
> 
> The order of writing names already implies priorities like 2 & 3 for c & d,
> respectively, like in your example. Having to write '1' for the group '(a, b)'
> seems unnecessary, IMHO. Sorry if I have missed any previous discussion where
> its necessity was discussed.
> 
> So, the order of writing standby names in the list should declare their
> relative priorities and parentheses (possibly nested) should help inform about
> the grouping (for quorum?)
> 

Oh, I missed Michael's latest message that describes its necessity. So, the
number is essentially the quorum for a group.

Sorry about the noise.

Thanks,
Amit



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

[HACKERS] WIP: ALTER TABLE ... ALTER CONSTRAINT ... SET DEFERRABLE on UNIQUE or PK

[Craig Ringer]

Hi all

Attached is a patch to implement ALTER TABLE ... ALTER CONSTRAINT ...
SET DEFERRABLE on UNIQUE or PRIMARY KEY constraints.

Currently only FOREIGN KEY constraints are supported. Others are rejected with:

constraint \"%s\" of relation \"%s\" is not a foreign key constraint

The patch also adds some regression tests for DEFERRABLE constraints.

The ALTER doesn't take effect in the session it's run in, which makes
me suspect I need to do additional cache invalidations - maybe the
index backing the constraint? Anyway, posted here as-is because I'm
out of time for now and it might be useful for someone who's looking
for info on this.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
From c0a041f0ca5d884842820538b56d82472a701c3c Mon Sep 17 00:00:00 2001
From: Craig Ringer 
Date: Fri, 26 Jun 2015 11:59:30 +0800
Subject: [PATCH] Allow ALTER TABLE...ALTER CONSTRAINT on PK and UNIQUE

We presently support DEFERRABLE constraints on PRIMARY KEY and UNIQUE
constraints, but do not permit ALTER TABLE to modify their deferrable
state.

Fix that and add some regression test coverage.
---
 src/backend/commands/tablecmds.c | 72 +++-
 src/test/regress/sql/alter_table.sql | 37 ++
 2 files changed, 76 insertions(+), 33 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d394713..5875987 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6647,22 +6647,20 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
  errmsg("constraint \"%s\" of relation \"%s\" does not exist",
 		cmdcon->conname, RelationGetRelationName(rel;
 
-	if (currcon->contype != CONSTRAINT_FOREIGN)
+	if (currcon->contype != CONSTRAINT_FOREIGN &&
+		currcon->contype != CONSTRAINT_PRIMARY &&
+		currcon->contype != CONSTRAINT_UNIQUE)
 		ereport(ERROR,
 (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
+ errmsg("constraint \"%s\" of relation \"%s\" must be FOREIGN KEY, PRIMARY KEY or UNIQUE",
 		cmdcon->conname, RelationGetRelationName(rel;
 
 	if (currcon->condeferrable != cmdcon->deferrable ||
 		currcon->condeferred != cmdcon->initdeferred)
 	{
 		HeapTuple	copyTuple;
-		HeapTuple	tgtuple;
 		Form_pg_constraint copy_con;
 		List	   *otherrelids = NIL;
-		ScanKeyData tgkey;
-		SysScanDesc tgscan;
-		Relation	tgrel;
 		ListCell   *lc;
 
 		/*
@@ -6682,44 +6680,52 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
 
 		/*
 		 * Now we need to update the multiple entries in pg_trigger that
-		 * implement the constraint.
+		 * implement the constraint if it's a foreign key constraint.
 		 */
-		tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+		if (currcon->contype == CONSTRAINT_FOREIGN)
+		{
+			HeapTuple	tgtuple;
+			ScanKeyData tgkey;
+			SysScanDesc tgscan;
+			Relation	tgrel;
 
-		ScanKeyInit(&tgkey,
-	Anum_pg_trigger_tgconstraint,
-	BTEqualStrategyNumber, F_OIDEQ,
-	ObjectIdGetDatum(HeapTupleGetOid(contuple)));
+			tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
 
-		tgscan = systable_beginscan(tgrel, TriggerConstraintIndexId, true,
-	NULL, 1, &tgkey);
+			ScanKeyInit(&tgkey,
+		Anum_pg_trigger_tgconstraint,
+		BTEqualStrategyNumber, F_OIDEQ,
+		ObjectIdGetDatum(HeapTupleGetOid(contuple)));
 
-		while (HeapTupleIsValid(tgtuple = systable_getnext(tgscan)))
-		{
-			Form_pg_trigger copy_tg;
+			tgscan = systable_beginscan(tgrel, TriggerConstraintIndexId, true,
+		NULL, 1, &tgkey);
 
-			copyTuple = heap_copytuple(tgtuple);
-			copy_tg = (Form_pg_trigger) GETSTRUCT(copyTuple);
+			while (HeapTupleIsValid(tgtuple = systable_getnext(tgscan)))
+			{
+Form_pg_trigger copy_tg;
 
-			/* Remember OIDs of other relation(s) involved in FK constraint */
-			if (copy_tg->tgrelid != RelationGetRelid(rel))
-otherrelids = list_append_unique_oid(otherrelids,
-	 copy_tg->tgrelid);
+copyTuple = heap_copytuple(tgtuple);
+copy_tg = (Form_pg_trigger) GETSTRUCT(copyTuple);
 
-			copy_tg->tgdeferrable = cmdcon->deferrable;
-			copy_tg->tginitdeferred = cmdcon->initdeferred;
-			simple_heap_update(tgrel, ©Tuple->t_self, copyTuple);
-			CatalogUpdateIndexes(tgrel, copyTuple);
+/* Remember OIDs of other relation(s) involved in FK constraint */
+if (copy_tg->tgrelid != RelationGetRelid(rel))
+	otherrelids = list_append_unique_oid(otherrelids,
+		 copy_tg->tgrelid);
 
-			InvokeObjectPostAlterHook(TriggerRelationId,
-	  HeapTupleGetOid(tgtuple), 0);
+copy_tg->tgdeferrable = cmdcon->deferrable;
+copy_tg->tginitdeferred = cmdcon->initdeferred;
+simple_heap_update(tgrel, ©Tuple->t_self, copyTuple);
+CatalogUpdateIndexes(tgrel, copyTuple);
 
-			heap_freetuple(copyTuple);
-		}
+InvokeObjectPostAlterHook(TriggerRelationId,
+