Re: [HACKERS] [ADMIN] Replication slots and isolation levels

2015-10-29 Thread Vladimir Borodin

> 29 окт. 2015 г., в 14:03, Michael Paquier  
> написал(а):
> 
> On Thu, Oct 29, 2015 at 11:33 AM, Vladimir Borodin wrote:
>> 29 окт. 2015 г., в 13:12, Michael Paquier написал(а):
>>> In the case of repeatable read the standby will wait before applying
>>> the VACUUM WAL record cleaning up a relation page. Hence you won't get
>>> conflicts in this case.
>> 
>> Standby will receive but will not apply? Or master will not vacuum needed by
>> standby pages? It seems that the second one is happening because replication
>> lag on standby does not increase while issuing such repeatable read
>> transaction.
> 
> Standby will receive the record but not replay it until the
> transaction doing REPEATABLE READ transactions that needs those rows
> commits on the standby. The WAL flush position on the standby
> continues to move on.

By replication lag on standby I mean exactly replay_location, not 
flush_location.

> This depends of course on
> max_standby_streaming_delay which may decide or not to force the
> transaction to cancel if it takes too long. Someone feel free to
> correct me if I am missing something here.

Well, the initial problem is that in read commited mode heavy SELECT-statement 
hits max_standby_streaming_delay but in repeatable read mode doesn’t. My 
question is if it is expected behavior? If yes, why is it so?

Thanks for your response!

> -- 
> Michael


--
Да пребудет с вами сила…
https://simply.name/ru



Re: [HACKERS] [ADMIN] Replication slots and isolation levels

2015-10-29 Thread Michael Paquier
On Thu, Oct 29, 2015 at 12:35 PM, Vladimir Borodin wrote:
> 29 окт. 2015 г., в 14:03, Michael Paquier написал(а):
>> Standby will receive the record but not replay it until the
>> transaction doing REPEATABLE READ transactions that needs those rows
>> commits on the standby. The WAL flush position on the standby
>> continues to move on.
>
> By replication lag on standby I mean exactly replay_location, not
> flush_location.
> Well, the initial problem is that in read commited mode heavy
> SELECT-statement hits max_standby_streaming_delay but in repeatable read
> mode doesn’t. My question is if it is expected behavior? If yes, why is it
> so?

Er, well. If I enforce on master the deletion then VACUUM-cleanup of a
page with a REPEATABLE READ transaction on standby still expecting to
have this page items visible until its commit the startup process puts
itself in waiting state when trying to replay the cleanup record, and
the replay_location does not move on, still the wal receiver gets WAL
in parallel, so it continues to flush things and flush_position
progresses. With a READ COMMITTED transaction running on the standby,
this transaction considers as visible stuff that has been committed,
so WAL replay can move on, and indeed there is a risk to face a
recovery conflict. So this behavior as-is is correct, based on how
isolation levels should behave when a node performs recovery.
-- 
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] psql: add \pset true/false

2015-10-29 Thread Tom Lane
Marko Tiikkaja  writes:
> On 10/29/15 11:51 AM, Daniel Verite wrote:
>> Personally I think it would be worth having, but how about
>> booleans inside ROW() or composite types ?

> There's not enough information sent over to do that in the client.
> Note that this works the same way as  \pset null  with  SELECT 
> ROW(NULL), so I don't consider it a show stopper for the patch.

The problem with that argument is that \pset null is already a kluge
(but at least a datatype-independent one).  Now you've added a datatype
specific kluge of the same ilk.  It might be useful, it might be short,
but that doesn't make it not a kluge.

The really key argument that hasn't been addressed here is why does such
a behavior belong in psql, rather than elsewhere?  Surely legibility
problems aren't unique to psql users.  Moreover, there are exactly
parallel facilities for other datatypes on the server side: think
DateStyle or bytea_output.  So if you were trying to follow precedent
rather than invent a kluge, you'd have submitted a patch to create a GUC
that changes the output of boolout().

Now, that would create a debate about backwards compatibility and whether
making bool output more readable was worth possibly breaking some
applications, but I do not think this patch should escape scrutiny for the
same issue.  There are plenty of people with shell scripts that look at
psql output, which might get broken by careless use of this \pset.

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

2015-10-29 Thread Дмитрий Воронин
>  It's a problem. See this recent discussion:
>  
> http://www.postgresql.org/message-id/flat/20150710115735.gh26...@alap3.anarazel.de

Postgresmen, we have a SQL function "current_database", which can be called by 
statement "SELECT CURRENT_CATALOG".

If we will use CURRENT_CATALOG keyword, we can update syntax of COMMENT 
statement:

COMMENT ON DATABASE CURRENT_CATALOG IS 'comment';

And pg_dump will create this line for database. What are you think about this 
idea? Later, I send a patch with it.

P.S. Maybe we can apply this mechanism for dumping SECURITY LABEL statetment.


-- 
Best regards, Dmitry Voronin


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

2015-10-29 Thread Fujii Masao
On Thu, Oct 22, 2015 at 12:47 AM, Masahiko Sawada  wrote:
> On Tue, Oct 20, 2015 at 8:10 PM, Beena Emerson  
> wrote:
>>
>> On Mon, Oct 19, 2015 at 8:47 PM, Masahiko Sawada 
>> wrote:
>>>
>>>
>>> Hi,
>>>
>>> Attached patch is a rough patch which supports multi sync replication
>>> by another approach I sent before.
>>>
>>> The new GUC parameters are:
>>> * synchronous_standby_num, which specifies the number of standby
>>> servers using sync rep. (default is 0)
>>> * synchronous_replication_method, which specifies replication method;
>>> priority or quorum. (default is priority)
>>>
>>> The behaviour of 'priority' and 'quorum' are same as what we've been
>>> discussing.
>>> But I write overview of these here again here.
>>>
>>> [Priority Method]
>>> The standby server has each different priority, and the active standby
>>> servers having the top N priroity are become sync standby server.
>>> If synchronous_standby_names = '*', the all active standby server
>>> would be sync standby server.
>>> If you want to set up standby like 9.5 or before, you can set
>>> synchronous_standby_num = 1.
>>>
>>
>>
>> I used the following setting with 2 servers A and D connected:
>>
>> synchronous_standby_names = 'A,B,C,D'
>> synchronous_standby_num = 2
>> synchronous_replication_method = 'priority'
>>
>> Though s_r_m = 'quorum' worked fine, changing it to 'priority' caused
>> segmentation fault.
>>
>
> Thank you for taking a look!
> This patch is a tool for discussion, so I'm not going to fix this bug
> until getting consensus.
>
> We are still under the discussion to find solution that can get consensus.
> I felt that it's difficult to select from the two approaches within
> this development cycle, and there would not be time to implement such
> big feature even if we selected.
> But this feature is obviously needed by many users.
> So I'm considering more simple and extensible something solution, the
> idea I posted is one of them.
> The another worth considering approach is that just specifying the
> number of sync standby. It also can cover the main use cases in
> some-cases.

Yes, it covers main and simple use case like "I want to have multiple
synchronous replicas!". Even if we miss quorum commit at the first
version, the feature is still very useful.

Regards,

-- 
Fujii Masao


-- 
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] Disabling START TRANSACTION for a SuperUser

2015-10-29 Thread Bernd Helmle


--On 27. Oktober 2015 14:07:06 + Kevin Grittner 
wrote:

>  It would be a boon to big shops if they could
> declare (preferably with the option to set it at a role level) that
> specific default_transaction_* settings could not be overridden.

A while ago i was faced with exactly the same problem. Thinking about it
again, i think that this also applies to various other parameters a DBA
wants to restrict to its roles. E.g. resource consumption limits (work_mem,
...), session constraints like the discussed transaction modes or even not
allowing to change the application_name.

afaicr, Oracle has a CREATE PROFILE which is primilarily intended to add
resource or password restrictions to users. Maybe this can be seen as a
blueprint to introduce the concept of GUC profiles to postgres, where a set
with maybe restrictions on the allowed values for certain GUCs can be
attached to roles. That for sure is a huge project.

Another idea (and maybe not that invasive like the profile idea) might be
to just introduce a concept of "read only" GUCs. A role would get a list of
GUCs which it is not allowed to change if given with ALTER ROLE...SET
(maybe restricted to PGC_USERSET). That could be stored along the same way
like pg_db_role_settings. However, i haven't checked how complicated this
would be to incorporate into the guc assign hooks, but maybe its doable
somehow.

-- 
Thanks

Bernd


-- 
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] [ADMIN] Replication slots and isolation levels

2015-10-29 Thread Vladimir Borodin

> 29 окт. 2015 г., в 15:29, Michael Paquier  
> написал(а):
> 
> On Thu, Oct 29, 2015 at 12:35 PM, Vladimir Borodin wrote:
>> 29 окт. 2015 г., в 14:03, Michael Paquier написал(а):
>>> Standby will receive the record but not replay it until the
>>> transaction doing REPEATABLE READ transactions that needs those rows
>>> commits on the standby. The WAL flush position on the standby
>>> continues to move on.
>> 
>> By replication lag on standby I mean exactly replay_location, not
>> flush_location.
>> Well, the initial problem is that in read commited mode heavy
>> SELECT-statement hits max_standby_streaming_delay but in repeatable read
>> mode doesn’t. My question is if it is expected behavior? If yes, why is it
>> so?
> 
> Er, well. If I enforce on master the deletion then VACUUM-cleanup of a
> page with a REPEATABLE READ transaction on standby still expecting to
> have this page items visible until its commit the startup process puts
> itself in waiting state when trying to replay the cleanup record, and
> the replay_location does not move on, still the wal receiver gets WAL
> in parallel, so it continues to flush things and flush_position
> progresses. With a READ COMMITTED transaction running on the standby,
> this transaction considers as visible stuff that has been committed,
> so WAL replay can move on, and indeed there is a risk to face a
> recovery conflict. So this behavior as-is is correct, based on how
> isolation levels should behave when a node performs recovery.

Everything you describe is exactly true for setups without replication slots. 
And the ability to run heavy SELECT statements on hot standby without 
replication lag and recovery conflicts was the reason why I tried to use them. 
And the documentation [0] directly says that «Replication slots provide an 
automated way to ensure ... that the master does not remove rows which could 
cause a recovery conflict even when the standby is disconnected». My question 
is why is it true for REPEATABLE READ transactions but it doesn’t work for READ 
COMMITED queries? Seems, that «even when the standby is disconnected» is much 
stronger limitation and READ COMMITED should work fine, but it doesn’t.

If I understand right, with hot_standby_feedback = on standby tells the master 
xmin of the earliest transaction on standby. And autovacuum worker on master 
takes it into account when doing vacuum cleanup (because it can see it from 
pg_replications_slots view), AFAIK with GetOldestXmin function. But I don’t 
understand why with READ COMMITED transactions xmin in pg_replication_slots 
view on master continues to increase while with REPEATABLE READ xmin freezes 
until this transaction finishes.

[0] 
http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS
 

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


--
Да пребудет с вами сила…
https://simply.name/ru



Re: [HACKERS] pg_dump

2015-10-29 Thread Дмитрий Воронин
David, do you want to have one dumper program for postgres?

Maybe it will be a good idea to make a dumper with some dumping levels:
- all cluster
- global objects
- database level


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


Re: [HACKERS] pg_dump

2015-10-29 Thread David Fetter
On Thu, Oct 29, 2015 at 06:37:46PM +0300, Дмитрий Воронин wrote:
> David, do you want to have one dumper program for postgres?

Yes, and pg_dump appears to be the best candidate for evolution into
one.

That there are two separate ones is the result of design decisions
that may very well have made sense at the time they were made, but no
longer do.

> Maybe it will be a good idea to make a dumper with some dumping levels:
> - all cluster
> - global objects
> - database level

I'm not sure I understand.  Is there some utility in dividing this
into these particular levels?

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] Personal note: changing employers

2015-10-29 Thread David Fetter
On Wed, Oct 28, 2015 at 06:14:27PM -0400, Tom Lane wrote:
> As of the end of this month, I will be departing Salesforce.com and
> joining Crunchy Data Solutions (http://crunchydatasolutions.com),
> whom you might recognize as being already the employers of Stephen
> Frost, Joe Conway, and Greg Smith, as well as a few other folk who
> hang out on pghackers.
> 
> While I enjoyed working at Salesforce, it was sucking an awfully large
> percentage of my time away from the PG community.  With this change,
> I expect to return to spending more-or-less full time on community work.

It's great that you're doing what you want to do.  I take it that
Crunchy's model involves more FOSS and less special sauce...

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

2015-10-29 Thread David Fetter
On Thu, Oct 29, 2015 at 10:51:20AM -0400, Tom Lane wrote:
> =?koi8-r?B?5M3J1NLJyiD3z9LPzsnO?=  writes:
> >> �It's a problem. See this recent discussion:
> >> �http://www.postgresql.org/message-id/flat/20150710115735.gh26...@alap3.anarazel.de
> 
> > Postgresmen, we have a SQL function "current_database", which can be called 
> > by statement "SELECT CURRENT_CATALOG".
> 
> > If we will use CURRENT_CATALOG keyword, we can update syntax of COMMENT 
> > statement:
> 
> > COMMENT ON DATABASE CURRENT_CATALOG IS 'comment';
> 
> > And pg_dump will create this line for database. What are you think about 
> > this idea?
> 
> We don't need hasty patches.  What we need is a re-think of the division
> of labor between pg_dump and pg_dumpall.  Up to now, pg_dump has only been
> charged with dumping/restoring the data "inside" an individual database,
> not with handling any database-level properties.  Those are the
> responsibility of pg_dumpall.
> 
> I'd be the first to agree that maybe this wasn't the best design, but at
> least it's consistent.  If we're going to change things, we need to start
> by deciding where we're going to re-draw the line, and figuring out what
> sort of impact that will have in terms of compatibility considerations
> and users' backup/restore procedures.

In this vein, I'd like humbly to suggest that we draw the line in a
way that finishes the already accomplished work of excluding
pg_dumpall entirely.  We should probably leave a pg_dumpall-compatible
wrapper for pg_dump for a few versions, but not if doing so cramps
development.

What pg_dumpall now still does that pg_dump doesn't is, as far as I
know:

- Whole-instance dumping (its original purpose, as far as I can tell)
- Auth (roles/secrets)
- Tablespaces

I believe that a relatively short patch to pg_dump would allow us to
make better versions of at least the first two.  By better, I mean,

- Not require that a whole-instance dump be in a single file, and
- Be able to dump only the auth stuff relevant to the specified DB
  objects.

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

2015-10-29 Thread Дмитрий Воронин

>>  Maybe it will be a good idea to make a dumper with some dumping levels:
>>  - all cluster
>>  - global objects
>>  - database level
>
> I'm not sure I understand. Is there some utility in dividing this
> into these particular levels?

-all cluster -- it's what pg_dumpall and pg_dump do.
- global objects -- it's dumping tablespaces, roles, database(structure).
- database level -- it's dumping all inside one database.

-- 
Best regards, Dmitry Voronin


-- 
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] Personal note: changing employers

2015-10-29 Thread Simon Riggs
On 28 October 2015 at 23:14, Tom Lane  wrote:

> As of the end of this month, I will be departing Salesforce.com and
> joining Crunchy Data Solutions (http://crunchydatasolutions.com),
> whom you might recognize as being already the employers of Stephen
> Frost, Joe Conway, and Greg Smith, as well as a few other folk who
> hang out on pghackers.
>

Well done and good luck.


> While I enjoyed working at Salesforce, it was sucking an awfully large
> percentage of my time away from the PG community.  With this change,
> I expect to return to spending more-or-less full time on community work.
>

Excellent news. Glad to have you back; we all missed you.

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

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


Re: [HACKERS] pg_dump

2015-10-29 Thread Jeff Janes
On Thu, Oct 29, 2015 at 7:51 AM, Tom Lane  wrote:
> =?koi8-r?B?5M3J1NLJyiD3z9LPzsnO?=  writes:
>>> šIt's a problem. See this recent discussion:
>>> šhttp://www.postgresql.org/message-id/flat/20150710115735.gh26...@alap3.anarazel.de
>
>> Postgresmen, we have a SQL function "current_database", which can be called 
>> by statement "SELECT CURRENT_CATALOG".
>
>> If we will use CURRENT_CATALOG keyword, we can update syntax of COMMENT 
>> statement:
>
>> COMMENT ON DATABASE CURRENT_CATALOG IS 'comment';
>
>> And pg_dump will create this line for database. What are you think about 
>> this idea?
>
> We don't need hasty patches.  What we need is a re-think of the division
> of labor between pg_dump and pg_dumpall.  Up to now, pg_dump has only been
> charged with dumping/restoring the data "inside" an individual database,
> not with handling any database-level properties.

I don't understand this comment.  The whole point of the thread is
that pg_dump (with -C or -Fc) is already dumping this database-level
information, but in a way that doesn't reload if the database name has
changed.  The info is already there, and at least in the case of
COMMENT it has been for a long time.

Cheers,

Jeff


-- 
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] [ADMIN] Replication slots and isolation levels

2015-10-29 Thread Oleksii Kliukin

> On 29 Oct 2015, at 14:39, Vladimir Borodin  wrote:
> 
> f I understand right, with hot_standby_feedback = on standby tells the master 
> xmin of the earliest transaction on standby. And autovacuum worker on master 
> takes it into account when doing vacuum cleanup (because it can see it from 
> pg_replications_slots view), AFAIK with GetOldestXmin function. But I don’t 
> understand why with READ COMMITED transactions xmin in pg_replication_slots 
> view on master continues to increase while with REPEATABLE READ xmin freezes 
> until this transaction finishes.

Could it be a consequence of how REPEATABLE READ transactions handle snapshots? 
With REPEATABLE READ the snapshot is acquired only once at the beginning of a 
transaction; a READ COMMITTED transaction re-evaluates its snapshot with each 
new command.

http://www.postgresql.org/docs/current/static/transaction-iso.html

Kind regards,
--
Oleksii



Re: [HACKERS] pg_dump

2015-10-29 Thread Tom Lane
=?koi8-r?B?5M3J1NLJyiD3z9LPzsnO?=  writes:
>> šIt's a problem. See this recent discussion:
>> šhttp://www.postgresql.org/message-id/flat/20150710115735.gh26...@alap3.anarazel.de

> Postgresmen, we have a SQL function "current_database", which can be called 
> by statement "SELECT CURRENT_CATALOG".

> If we will use CURRENT_CATALOG keyword, we can update syntax of COMMENT 
> statement:

> COMMENT ON DATABASE CURRENT_CATALOG IS 'comment';

> And pg_dump will create this line for database. What are you think about this 
> idea?

We don't need hasty patches.  What we need is a re-think of the division
of labor between pg_dump and pg_dumpall.  Up to now, pg_dump has only been
charged with dumping/restoring the data "inside" an individual database,
not with handling any database-level properties.  Those are the
responsibility of pg_dumpall.

I'd be the first to agree that maybe this wasn't the best design, but at
least it's consistent.  If we're going to change things, we need to start
by deciding where we're going to re-draw the line, and figuring out what
sort of impact that will have in terms of compatibility considerations
and users' backup/restore procedures.

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

2015-10-29 Thread David Fetter
On Thu, Oct 29, 2015 at 07:03:12PM +0300, Дмитрий Воронин wrote:
> 
> >>  Maybe it will be a good idea to make a dumper with some dumping levels:
> >>  - all cluster
> >>  - global objects
> >>  - database level
> >
> > I'm not sure I understand. Is there some utility in dividing this
> > into these particular levels?
> 
> -all cluster -- it's what pg_dumpall and pg_dump do.
> - global objects -- it's dumping tablespaces, roles, database(structure).
> - database level -- it's dumping all inside one database.

More specifically, I am not quite understanding the distinction
between "all cluster" and "global objects."

What do you have in mind on the implementation side?  Do you think
pg_dump is a suitable baseline, or were you thinking of something
different, and if so, what?

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] [PROPOSAL] VACUUM Progress Checker.

2015-10-29 Thread Syed, Rahila
Hello,

Please find attached an updated patch.

>Flag isn't reset on error.
Corrected in the attached.

> + pgstat_reset_activityflag;
>Does this actually compile?
It does compile but with no effect.  It has been corrected.

>snprintf()?  I don't think you need to keep track of schemaname_len at all.
memcpy() has been replaced by snprintf() to avoid calculating schemaname_len.

>In fact, I wonder if you need to send total_pages at all -- surely the client 
>can add both total_heap_pages and total_index_pages by itself ...
This has  been corrected in the attached patch.

>It seems a bit strange that the remaining progress_param entries are not 
>initialized to anything.  Also, why aren't the number of params of each type 
>saved too?  
The number of params for each command remains constant hence it has been 
hardcoded.

>In the receiving code you check whether each value equals 0, and if it does 
>then report NULL, but imagine vacuuming a table with no indexes where the 
>number of index pages is going to be zero.
>Shouldn't we display zero there rather than null?
Agree.  IIUC, NULL should rather be used when a value is invalid. But for valid 
values like 'zero index pages' it is clearer to display 0. It has been 
corrected in the attached. 

>This patch lacks a comment somewhere explaining how this whole thing works.
Have added few lines in pgstat.h inside PgBackendStatus struct.

>I believe you don't need this include.
Corrected.

>This not only adds an unnecessary empty line at the end of the struct 
>declaration, but also fails to preserve the "st_" prefix used in all the other 
>fields
Corrected.

Thank you,
Rahila Syed


__
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.

Vacuum_progress_checker_v6.patch
Description: Vacuum_progress_checker_v6.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] Move PinBuffer and UnpinBuffer to atomics

2015-10-29 Thread Alexander Korotkov
On Thu, Sep 24, 2015 at 6:36 PM, Alexander Korotkov <
a.korot...@postgrespro.ru> wrote:

> On Thu, Sep 24, 2015 at 6:32 PM, Andres Freund  wrote:
>
>> On 2015-09-15 20:16:10 +0300, YUriy Zhuravlev wrote:
>> > We will be tested.
>>
>> Did you have a chance to run some benchmarks?
>>
>
> Yes, we now have 60 physical cores intel server and we're running
> benchmarks on it.
>

We got a consensus with Andres that we should commit the CAS version first
and look to other optimizations.
Refactored version of atomic state patch is attached. The changes are
following:
1) Macros are used for access refcount and usagecount.
2) likely/unlikely were removed. I think introducing of likely/unlikely
should be a separate patch since it touches portability. Also, I didn't see
any performance effect of this.
3) LockBufHdr returns the state after taking lock. Without using atomic
increments it still can save some loops on skip atomic value reading.

--
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


pinunpin-cas.patch
Description: Binary data

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


Re: [HACKERS] Freeze avoidance of very large table.

2015-10-29 Thread Masahiko Sawada
On Wed, Oct 28, 2015 at 12:58 PM, Amit Kapila  wrote:
> On Sat, Oct 24, 2015 at 2:24 PM, Masahiko Sawada 
> wrote:
>>
>> On Sat, Oct 24, 2015 at 10:59 AM, Amit Kapila 
>> wrote:
>> >
>> > I think we can display information about relallfrozen it in
>> > pg_stat_*_tables
>> > as suggested by you.  It doesn't make much sense to keep it in pg_class
>> > unless we have some usecase for the same.
>> >
>>
>> I'm thinking a bit about implementing the read-only table that is
>> restricted to update/delete and is ensured that whole table is frozen,
>> if this feature is committed.
>> The value of relallfrozen might be useful for such feature.
>>

Thank you for reviewing!

> If we need this for read-only table feature, then better lets add that
> after discussing the design of that feature.  It doesn't seem to be
> advisable to have an extra field in system table which we might
> need in yet not completely-discussed feature.

I changed it so that the number of frozen pages is stored in
pg_stat_all_tables as statistics information.
Also, the tests related to counting all-visible bit and skipping
vacuum are added to visibility map test, and the test related to
counting all-frozen is added to stats collector test.

Attached updated v20 patch.

> Review Comments:
> ---
> 1.
>   /*
> - * Find buffer to insert this tuple into.  If the page is all visible,
> - * this will also pin
> the requisite visibility map page.
> + * Find buffer to insert this tuple into.  If the page is all
> visible
> + * or all frozen, this will also pin the requisite visibility map and
> + * frozen map page.
>
>  */
>   buffer = RelationGetBufferForTuple(relation, heaptup->t_len,
>
>   InvalidBuffer, options, bistate,
>
>
> I think it is sufficient to say in the end 'visibility map page'.
> Let's not include 'frozen map page'.

Fixed.

>
> 2.
> + * corresponding page has been completely frozen, so the visibility map is
> also
> + * used for anti-wraparound
> vacuum, even if freezing tuples is required.
>
> /all tuple/all tuples
> /freezing tuples/freezing of tuples

Fixed.

> 3.
> - * Are all tuples on heapBlk visible to all, according to the visibility
> map?
> + * Are all tuples on heapBlk
> visible or frozen to all, according to the visibility map?
>
> I think it is better to modify the above statement as:
> Are all tuples on heapBlk visible to all or are marked as frozen, according
> to the visibility map?

Fixed.

> 4.
> + * releasing *buf after it's done testing and setting bits, and must set
> flags
> + * which indicates what flag
> we want to test.
>
> Here are you talking about the flags passed to visibilitymap_set(), if
> yes, then above comment is not clear, how about:
>
> and must pass flags
> for which it needs to check the value in visibility map.

Fixed.

> 5.
> + * both how many pages we skipped according to all-frozen bit of visibility
> + * map and how many
> pages we freeze page, so we can update relfrozenxid if
>
> In above sentence word 'page' after freeze sounds redundant.
> /we freeze page/we freeze
>
> Another suggestion:
> /sum of them/sum of two

Fixed.

> 6.
> + * This block is at least all-visible according to visibility map.
> +
>  * We check whehter this block is all-frozen or not, to skip to
>
> whether is mis-spelled

Fixed.

> 7.
> + * If we froze any tuples or any tuples are already frozen,
> + * mark the buffer
> dirty, and write a WAL record recording the changes.
>
> Here, I think WAL record is written only when we mark some
> tuple/'s as frozen not if we they are already frozen,
> so in that regard, I think above comment is wrong.

It's wrong.
Fixed.

> 8.
> + /*
> + * We cant't allow upgrading with link mode between 9.5 or before and 9.6
> or later,
> + *
> because the format of visibility map has been changed on version 9.6.
> + */
>
>
> a. /cant't/can't
> b. changed on version 9.6/changed in version 9.6
> b. Won't such a change needs to be updated in pg_upgrade
> documentation (Notes Section)?

Fixed.
And updated document.

> 9.
> @@ -180,6 +181,13 @@ transfer_single_new_db(pageCnvCtx *pageConverter,
>
> new_cluster.controldata.cat_ver >= VISIBILITY_MAP_CRASHSAFE_CAT_VER)
>   vm_crashsafe_match = false;
>
> +
> /*
> + * Do we need to rewrite visibilitymap?
> + */
> + if (old_cluster.controldata.cat_ver <
> VISIBILITY_MAP_FROZEN_BIT_CAT_VER &&
> + new_cluster.controldata.cat_ver >=
> VISIBILITY_MAP_FROZEN_BIT_CAT_VER)
> + vm_rewrite_needed = true;
>
> ..
>
> @@ -276,7 +285,15 @@ transfer_relfile(pageCnvCtx *pageConverter, FileNameMap
> *map,
>   {
>
> pg_log(PG_VERBOSE, "copying \"%s\" to \"%s\"\n", old_file, new_file);
>
> - if ((msg =
> copyAndUpdateFile(pageConverter, old_file, new_file, true)) != NULL)
> + /*
> +
>  * Do we need to rewrite visibilitymap?
> + */
> + if (strcmp
> (type_suffix, "_vm") == 0 &&
> + old_cluster.controldata.cat_ver <
> VISIBILITY_MAP_FROZEN_BIT_CAT_VER &&
> + 

[HACKERS] planner doesn't use bitmap index

2015-10-29 Thread Pavel Stehule
Hi

There is interesting query on stackoverflow
http://stackoverflow.com/questions/33418157/query-too-slow-in-postgresql-in-table-with-12m-rows
- and it looks like planner issue.

I have empty tables test1 and test2

set enable_seqscan to off;
create table test1(a int, b int);
create index on test1(a);
analyze test1;

-- expected behave
postgres=# explain select * from test1 where a = 1 and b = 2;
   QUERY PLAN
═
Bitmap Heap Scan on test1  (cost=4.24..14.94 rows=1 width=8)
  Recheck Cond: (a = 1)
  Filter: (b = 2)
  ->  Bitmap Index Scan on test1_a_idx  (cost=0.00..4.24 rows=11 width=0)
Index Cond: (a = 1)
(5 rows)


create table test2(a timestamp with time zone, b int);
create index on test2(a);
analyze test2;

-- I was surprised, so following query can use index
postgres=# explain  select a from test2 where a at time zone
'America/Santiago' >= now() at time zone 'America/Santiago' ;
  QUERY
PLAN
══
Index Only Scan using test2_a_idx on test2  (cost=0.13..12.18 rows=1
width=8)
  Filter: (timezone('America/Santiago'::text, a) >=
timezone('America/Santiago'::text, now()))
(2 rows)

but

why, the index isn't used in this case?
postgres=# explain  select a,b from test2 where a at time zone
'America/Santiago' >= now() at time zone 'America/Santiago' ;
  QUERY
PLAN
══
Seq Scan on test2  (cost=100.00..101.04 rows=1 width=12)
  Filter: (timezone('America/Santiago'::text, a) >=
timezone('America/Santiago'::text, now()))
(2 rows)

or in this case?
postgres=# explain  select a from test2 where a at time zone
'America/Santiago' >= now() at time zone 'America/Santiago' and b = 1 ;
 QUERY
PLAN

Seq Scan on test2  (cost=100.00..101.05 rows=1 width=8)
  Filter: ((b = 1) AND (timezone('America/Santiago'::text, a) >=
timezone('America/Santiago'::text, now(
(2 rows)

Composite index fixes it. But it should to work without composite index too?
create index on test2(a,b);

postgres=# explain  select a from test2 where a at time zone
'America/Santiago' >= now() at time zone 'America/Santiago' and b = 1 ;
  QUERY
PLAN
══
Index Only Scan using test2_a_b_idx on test2  (cost=0.13..12.18 rows=1
width=8)
  Index Cond: (b = 1)
  Filter: (timezone('America/Santiago'::text, a) >=
timezone('America/Santiago'::text, now()))
(3 rows)

Tested on master.

Regards

Pavel


Re: [HACKERS] [DOCS] max_worker_processes on the standby

2015-10-29 Thread Fujii Masao
On Wed, Oct 28, 2015 at 3:07 AM, Alvaro Herrera
 wrote:
> Alvaro Herrera wrote:
>> Robert Haas wrote:
>> > On Sat, Oct 17, 2015 at 5:37 PM, Petr Jelinek  wrote:
>> > > I agree with that sentiment.
>> > >
>> > > Attached patch adds variable to the shmem which is used for module
>> > > activation tracking - set to true in ActiveCommitTs() and false in
>> > > DeactivateCommitTs(). All the checks inside the commit_ts code were 
>> > > changed
>> > > to use this new variable. I also removed the static variable Alvaro 
>> > > added in
>> > > previous commit because it's not needed anymore.
>> >
>> > That sounds good to me.  On a quick read-through it looks OK too.
>>
>> A revised version is attached.
>
> Pushed.

I found another strange behavior on track_commit_timestamp.
Here are the steps to reproduce it.

1. Start the master and standby servers with track_commit_timestamp enabled.
Since committs is activated in standby, pg_last_committed_xact() can
successfully return the timestamp of last transaction as expected.

2. Disable track_commit_timestamp in the master and restart the master server.
The parameter-change WAL record is streamed to the standby and committs
is deactivated. pg_last_committed_xact() causes an ERROR in the standby.

3. Run checkpoint in the master.

4. Run restartpoint in the standby after the checkpoint WAL record generated
in #3 is replicated to the standby.

5. Restart the standby server.
Committs is activated in the standby because track_commit_timestamp is
enabled. Since there is no parameter-change WAL record since last
restartpoint, committs is not deactivated. So pg_last_committed_xact()
can successfully return the timestamp.

6. Enable track_commit_timestamp in the master and restart the master server.

7. Disable track_commit_timestamp in the master and restart the master server.
Back to the same situation as #2. That is, pg_last_committed_xact() causes
an ERROR.

8. Promote the standby server to new master.
Since committs is still inactive even after the promotion,
pg_last_committed_xact() keeps causing an ERROR though
track_commit_timestamp is on.

What I think strange is that pg_last_committed_xact() behaves differently
in #2, #5, and #7 though the settings of track_commit_timestamp are same
in both servers, i.e., it's disabled in the master but enabled in the standby.

I was thinking that whether committs is active or not should depend on
the setting of track_commit_timestamp *after* the promotion.
The behavior in #8 looked strange.

Regards,

-- 
Fujii Masao


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


[HACKERS] ALTER ... OWNER TO ... vs. ALTER DEFAULT PRIVILEGES

2015-10-29 Thread David Fetter
Folks,

I've run into a problem recently, and I can't be the first to have
done so, and it's this.

We have a pretty sophisticated capability via ALTER DEFAULT
PRIVILEGES.  When the creating role creates something in a schema so
altered, all kinds of nice recursive granting happens.  That's well
and good.

BUT

When we change an object's owner, we have no practical access to those
default privileges, even when we want them applied.

If this were a green field project, I would advocate that the things
ALTER DEFAULT PRIVILEGES does should be applied by default on change
of ownership.  I would still argue that this behavior should become
the default, but I would expect to lose that argument.

Since it's not a green field project, I would like to propose the
following addition to the ALTER ... OWNER TO ... construct:

ALTER ... OWNER TO ... [{NEW | OLD} DEFAULT PRIVILEGES]

What say?

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] Patch: Implement failover on libpq connect level.

2015-10-29 Thread Peter Eisentraut
On 10/28/15 4:18 AM, Victor Wagner wrote:
> On Mon, 26 Oct 2015 16:25:57 -0400
> Peter Eisentraut  wrote:
> 
>> Also, this assumes that all the components other than host and port
>> are the same.  Earlier there was a discussion about why the ports
>> would ever need to be different.  Well, why can't the database names
>> be different? I could have use for that.
> 
> Because of way postgresql replication is implemented.

There are multiple types of PostgreSQL replication, and there will be
others in the future.



-- 
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] Are we sufficiently clear that jsonb containment is nested?

2015-10-29 Thread Peter Geoghegan
On Fri, Jun 26, 2015 at 8:05 AM, Robert Haas  wrote:
> I would be fine with adding a *compact* example of this kind to the
> table that begins section 8.14.3.  I probably would not back-patch it,
> because the absence of that example is not an error in the
> documentation, but I will not complain if someone else does.

How about the attached? It's a compact refinement of my original
example of more complicated jsonb containment.

I still think it would be a good idea to go back to 9.4. I have reason
to believe that people are getting confused on this point.

-- 
Peter Geoghegan
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 118fb35..f5c8fca 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -300,11 +300,21 @@ SELECT '{"foo": {"bar": "baz"}}'::jsonb @ '{"foo": {}}'::jsonb;
The general principle is that the contained object must match the
containing object as to structure and data contents, possibly after
discarding some non-matching array elements or object key/value pairs
-   from the containing object.
+   from the containing object.  Containment is nested.
But remember that the order of array elements is not significant when
doing a containment match, and duplicate array elements are effectively
considered only once.
+   This example shows containment with complex nesting:
   
+
+-- By convention, the "doc" jsonb column has an object at the top
+-- level.  Each object contains a "tags" array, which itself
+-- contains key/value pairs.  Often, "term" tags are available among
+-- tag pairs.
+--
+-- Return "site_name" where at least these two terms appear:
+SELECT doc->'site_name' from websites where doc @ '{"tags":[{"term":"paris" }, {"term":"food"}]}';
+
 
   
As a special exception to the general principle that the structures

-- 
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 EXTRA_CFLAGS to configure

2015-10-29 Thread Peter Eisentraut
On 10/28/15 6:55 AM, Andres Freund wrote:
> 1) ./configure CFLAGS=... essentially breaks --enable-debug and related
>options,

If assigning to CFLAGS breaks --enable-debug, then we should fix that by
reordering things a bit.

> overwrites -O2 as the default and such. That's imo pretty
>confusing.

I'm not so concerned about this case.  -O2 is a default, and if you want
something different, then the default doesn't apply.  If a user works at
this level of detail, they can also keep that in mind.

Adding more ways to set the same thing would just create more
potentional uncertainty about in which order and precedence things are
set and checked.  It might make sense to you now, but that doesn't mean
in will make sense to the next person.

> 2) I like to be able to pass -Werror or something to configure without
>breaking a bunch of configure tests that won't work with Werror.
> 
> A good way to do that seems to be to add a separate variable for that
> purpose? Unless someobdy has a better idea?

I think we already had this discussion numerous times, and about all the
ways that have been proposed have been rejected at least once before. ;-)




-- 
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] planner doesn't use bitmap index

2015-10-29 Thread Tom Lane
Pavel Stehule  writes:
> -- I was surprised, so following query can use index
> postgres=# explain  select a from test2 where a at time zone
> 'America/Santiago' >= now() at time zone 'America/Santiago' ;
>   QUERY
> PLAN
> ══════════════════════════════════════════════════════════════════════════════════════════════
> Index Only Scan using test2_a_idx on test2  (cost=0.13..12.18 rows=1
> width=8)
>   Filter: (timezone('America/Santiago'::text, a) >=
> timezone('America/Santiago'::text, now()))
> (2 rows)

This plan isn't actually "using" the index in any meaningful way; it's
applying the where condition as a filter.  It happens to be sane to use
the index as a dumb data source, because it can be an index-only scan, and
that might (if you're lucky and don't hit too many recheckable rows) be
cheaper than a seqscan.  But we don't consider plain indexscans as worth
the trouble to consider in such cases, because a full-table plain
indexscan can never beat a seqscan, either in the planner's cost model or
in reality.

> why, the index isn't used in this case?
> postgres=# explain  select a,b from test2 where a at time zone
> 'America/Santiago' >= now() at time zone 'America/Santiago' ;

Can't be an index-only scan because of the use of b, so there's no
possible way that this can be better than a seqscan.

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] planner doesn't use bitmap index

2015-10-29 Thread Pavel Stehule
2015-10-29 19:20 GMT+01:00 Tom Lane :

> Pavel Stehule  writes:
> > -- I was surprised, so following query can use index
> > postgres=# explain  select a from test2 where a at time zone
> > 'America/Santiago' >= now() at time zone 'America/Santiago' ;
> >   QUERY
> > PLAN
> >
> ══
> > Index Only Scan using test2_a_idx on test2  (cost=0.13..12.18 rows=1
> > width=8)
> >   Filter: (timezone('America/Santiago'::text, a) >=
> > timezone('America/Santiago'::text, now()))
> > (2 rows)
>
> This plan isn't actually "using" the index in any meaningful way; it's
> applying the where condition as a filter.  It happens to be sane to use
> the index as a dumb data source, because it can be an index-only scan, and
> that might (if you're lucky and don't hit too many recheckable rows) be
> cheaper than a seqscan.  But we don't consider plain indexscans as worth
> the trouble to consider in such cases, because a full-table plain
> indexscan can never beat a seqscan, either in the planner's cost model or
> in reality.
>
> > why, the index isn't used in this case?
> > postgres=# explain  select a,b from test2 where a at time zone
> > 'America/Santiago' >= now() at time zone 'America/Santiago' ;
>
> Can't be an index-only scan because of the use of b, so there's no
> possible way that this can be better than a seqscan.
>

I understand.

Than you for explanation.

Regards

Pavel


>
> regards, tom lane
>


Re: [HACKERS] proposal: PL/Pythonu - function ereport

2015-10-29 Thread Pavel Stehule
2015-10-28 7:25 GMT+01:00 Catalin Iacob :

> On Tue, Oct 27, 2015 at 9:34 AM, Pavel Stehule 
> wrote:
> > Hi
> >
> > 2015-10-23 7:34 GMT+02:00 Catalin Iacob :
> >> The current code doesn't build on Python3 because the 3rd argument of
> >> PyMethod_New, the troubled one you need set to NULL has been removed.
> >> This has to do with the distinction between bound and unbound methods
> >> which is gone in Python3.
> >
> >
> > this part of is well isolated, and we can do switch for Python2 and
> Python3
> > without significant problems.
>
> I had a quick look at this and at least 2 things are needed for Python3:
>
> * using PyInstanceMethod_New instead of PyMethod_New; as
> http://bugs.python.org/issue1587 and
> https://docs.python.org/3/c-api/method.html explain that's the new way
> of binding a PyCFunction to a class, PyMethod_New(func, NULL) fails
>
> * in the PLy_spi_error_methods definition, __init__ has to take
> METH_VARARGS | METH_KEYWORDS not just METH_KEYWORDS; in Python2
> METH_KEYWORDS implied METH_VARARGS so it's not needed (it also doesn't
> hurt) but if I don't add it, in Python3 I get:
> ERROR:  SystemError: Bad call flags in PyCFunction_Call. METH_OLDARGS
> is no longer supported!
>
> >> Still, the above link shows a (more verbose but maybe better)
> >> alternative: don't use PyErr_NewException and instead define an
> >> SPIError type with each slot spelled out explicitly. This will remove
> >> the "is it safe to set the third argument to NULL" question.
> >
> > Should be there some problems, if we lost dependency on basic exception
> > class? Some compatibility issues? Or is possible to create full type with
> > inheritance support?
>
> It's possible to give it a base type, see at
> https://hg.python.org/cpython/rev/429cadbc5b10/ this line before
> calling PyType_Ready:
> PyComError_Type.tp_base = (PyTypeObject*)PyExc_Exception;
>
> PyErr_NewException is a shortcut for defining simple Exception
> deriving types, usually one defines a type with the full PyTypeObject
> definition.
>
> In the meantime, I had a deeper look at the 2.7.10 code and I trust
> that PyMethod_New with the last argument set to NULL is ok. Setting
> that to NULL will lead to the PyMethod representing __init__ im_class
> being NULL. But that PyMethod object is not held onto by C code, it's
> added to the SPIError class' dict. From there, it is always retrieved
> from Python via an instance or via the class (so SPIError().__init__
> or SPIError.__init__) which will lead to instancemethod_descr_get
> being called because it's the tp_descr_get slot of PyMethod_Type and
> that code knows the class you're retrieving the attribute from
> (SPIError in this case), checks if the PyMethod already has a not NULL
> im_class (which SPIError.__init__ doesn't) and, if not, calls
> PyMethod_New again and passes the class (SPIError) as the 3rd
> argument.
>
> Given this, I think it's ok to keep using PyErr_NewException rather
> than spelling out the type explicitly.
>

Thank you very much for your analyse. I am sending new version of proposed
patch with Python3 support. Fixed missing check of dictionary
initialization.

Regards

Pavel
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
new file mode 100644
index 015bbad..bf468e1
*** a/doc/src/sgml/plpython.sgml
--- b/doc/src/sgml/plpython.sgml
*** $$ LANGUAGE plpythonu;
*** 1205,1210 
--- 1205,1235 
  approximately the same functionality
 

+ 
+   
+Raising Errors
+ 
+
+ The plpy module provides several possibilities to
+ to raise a exception:
+
+ 
+
+ 
+  SPIError([ message [, detail [, hint [, sqlstate  [, schema  [, table  [, column  [, datatype  [, constraint ])
+  
+
+ The constructor of SPIError exception (class) supports keyword parameters. 
+ 
+ DO $$
+   raise plpy.SPIError('custom message', hint = 'It is test only');
+ $$ LANGUAGE plpythonu;
+ 
+
+  
+ 
+
+   
   
  
   
diff --git a/src/pl/plpython/expected/plpython_error.out b/src/pl/plpython/expected/plpython_error.out
new file mode 100644
index 1f52af7..ac985c6
*** a/src/pl/plpython/expected/plpython_error.out
--- b/src/pl/plpython/expected/plpython_error.out
*** EXCEPTION WHEN SQLSTATE 'SILLY' THEN
*** 422,424 
--- 422,473 
  	-- NOOP
  END
  $$ LANGUAGE plpgsql;
+ /* possibility to set all accessable fields in custom exception
+  */
+ DO $$
+ raise plpy.SPIError('This is message text.',
+ detail = 'This is detail text',
+ hint = 'This is hint text.')
+ $$ LANGUAGE plpythonu;
+ ERROR:  plpy.SPIError: This is message text.
+ DETAIL:  This is detail text
+ HINT:  This is hint text.
+ CONTEXT:  Traceback (most recent call last):
+   PL/Python anonymous code block, line 4, in 
+ hint = 'This is hint text.')
+ PL/Python anonymous code block
+ \set VERBOSITY verbose
+ 

Re: [HACKERS] ALTER ... OWNER TO ... vs. ALTER DEFAULT PRIVILEGES

2015-10-29 Thread Tom Lane
David Fetter  writes:
> Since it's not a green field project, I would like to propose the
> following addition to the ALTER ... OWNER TO ... construct:
> ALTER ... OWNER TO ... [{NEW | OLD} DEFAULT PRIVILEGES]
> What say?

I'd say "you haven't actually defined what either of those options mean".

regards, tom lane


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


[HACKERS] Within CF app, "Bug Fixes" should be "Bug Fixes/Refactoring"

2015-10-29 Thread Peter Geoghegan
I think that within the CF app, we should either rename the patch
topic "Bug Fixes" to "Bug Fixes/Refactoring", or introduce a new
"Refactoring" topic. I prefer the first approach.

-- 
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] Within CF app, "Bug Fixes" should be "Bug Fixes/Refactoring"

2015-10-29 Thread Michael Paquier
On Thu, Oct 29, 2015 at 8:33 PM, Peter Geoghegan  wrote:
> I think that within the CF app, we should either rename the patch
> topic "Bug Fixes" to "Bug Fixes/Refactoring", or introduce a new
> "Refactoring" topic. I prefer the first approach.

I would vote for the second approach, with a separate category for refactoring.
-- 
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] Within CF app, "Bug Fixes" should be "Bug Fixes/Refactoring"

2015-10-29 Thread Mike Blackwell
​

On Thu, Oct 29, 2015 at 2:45 PM, Michael Paquier 
 wrote:

> On Thu, Oct 29, 2015 at 8:33 PM, Peter Geoghegan  wrote:
> > I think that within the CF app, we should either rename the patch
> > topic "Bug Fixes" to "Bug Fixes/Refactoring", or introduce a new
> > "Refactoring" topic. I prefer the first approach.
>
> I would vote for the second approach, with a separate category for
> refactoring.
>
> ​
So if a bug fix involved some refactoring, which would you put it under?
Or would you
expect separate CF entries for the refactoring and the fix?​

​
​
​
​

__
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
mike.blackw...@rrd.com
http://www.rrdonnelley.com



* *
​


Re: [HACKERS] Within CF app, "Bug Fixes" should be "Bug Fixes/Refactoring"

2015-10-29 Thread Tom Lane
Michael Paquier  writes:
> On Thu, Oct 29, 2015 at 8:33 PM, Peter Geoghegan  wrote:
>> I think that within the CF app, we should either rename the patch
>> topic "Bug Fixes" to "Bug Fixes/Refactoring", or introduce a new
>> "Refactoring" topic. I prefer the first approach.

> I would vote for the second approach, with a separate category for 
> refactoring.

Ditto.  Bug fixes are not at all like refactoring --- in particular, we'd
usually not consider refactoring as fit material for back-patching.

"Refactoring" seems rather a narrow definition of what might show up
in such a category, btw.  Maybe "Code Beautification" would be a
suitable title?  I'm bikeshedding though.

regards, tom lane


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


Re: [HACKERS] Within CF app, "Bug Fixes" should be "Bug Fixes/Refactoring"

2015-10-29 Thread Peter Geoghegan
On Thu, Oct 29, 2015 at 1:10 PM, Tom Lane  wrote:
> Ditto.  Bug fixes are not at all like refactoring --- in particular, we'd
> usually not consider refactoring as fit material for back-patching.
>
> "Refactoring" seems rather a narrow definition of what might show up
> in such a category, btw.  Maybe "Code Beautification" would be a
> suitable title?  I'm bikeshedding though.

I think that there is value in limiting the number of topics. But I
hardly but much weight on this. Any of the above are fine.


-- 
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] Are we sufficiently clear that jsonb containment is nested?

2015-10-29 Thread Tom Lane
Peter Geoghegan  writes:
> On Thu, Oct 29, 2015 at 1:16 PM, Tom Lane  wrote:
>> I think the existing text is largely my fault, so I'll do something with
>> this.

> Good. Thanks.

After studying the proposed patch a bit more, I still think the example
is good, but the added text doesn't do much to explain your point.  If
I get what your point is, which maybe I don't, I think the attached might
clarify it better.  What do you think of this version?

regards, tom lane

diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 118fb35..b2efc80 100644
*** a/doc/src/sgml/json.sgml
--- b/doc/src/sgml/json.sgml
*** SELECT '"bar"'::jsonb @ '["bar"]'::j
*** 319,324 
--- 319,346 
  
  

+Keep in mind that containment is nested; an appropriate
+query can skip explicit selection of sub-objects.  As an example, suppose
+that our doc column contains objects at the top level,
+with all or most objects containing tags fields that contain
+arrays of sub-objects.  This query finds entries in which sub-objects
+containing both "term":"paris" and "term":"food"
+appear, ignoring any cases where such keys appear outside
+the tags array:
+ 
+ -- Return "site_name" where at least the terms "paris" and "food" appear:
+ SELECT doc->'site_name' FROM websites
+   WHERE doc @ '{"tags":[{"term":"paris"}, {"term":"food"}]}';
+ 
+One could accomplish the same thing with, say,
+ 
+ SELECT doc->'site_name' FROM websites
+   WHERE doc->'tags' @ '[{"term":"paris"}, {"term":"food"}]';
+ 
+but that approach is less flexible, and often less efficient as well.
+   
+ 
+   
  jsonb also has an existence operator, which is
  a variation on the theme of containment: it tests whether a string
  (given as a text value) appears as an object key or array

-- 
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] ALTER ... OWNER TO ... vs. ALTER DEFAULT PRIVILEGES

2015-10-29 Thread David Fetter
On Thu, Oct 29, 2015 at 02:25:14PM -0400, Tom Lane wrote:
> David Fetter  writes:
> > Since it's not a green field project, I would like to propose the
> > following addition to the ALTER ... OWNER TO ... construct:
> > ALTER ... OWNER TO ... [{NEW | OLD} DEFAULT PRIVILEGES]
> > What say?
> 
> I'd say "you haven't actually defined what either of those options mean".

Good point.

This morning, when this came up most recently for me, I wanted to
change the owner of a table, which was all well and good, and I wanted
the default privileges of the new owner applied to it, which was, to
coin a phrase, all unwell and bad.

Had this been part of the original ALTER DEFAULT PRIVILEGES patch,
those privileges would simply have been applied.  Since it wasn't, I'm
ass-u-me'ing that changing the default behavior to that is going to
cause (possibly legitimate) anxiety.

On thinking this through, I'd like to propose a more extensible
syntax, as below.

If we're going with "preserve current behavior for current syntax," I
propose that:

- Leaving the clause out would apply no privileges (modulo event
  triggers. Whee!), preserving current behavior.

- ALTER ... OWNER TO ... WITH (NEW DEFAULT PRIVILEGES) applies any
  applicable default privileges for the new owner.

- ALTER ... OWNER TO ... WITH (OLD DEFAULT PRIVILEGES) ensures that
  any any applicable default privileges for the previous owner are
  applied, whether they already had been or not.

It would be legal to supply both WITH stanzas, as in WITH (NEW DEFAULT
PRIVILEGES, OLD DEFAULT PRIVILEGES).

If we're going with "current behavior is pretty crazy, and should
never have been this way," (my opinion) I propose that:

- Leaving the clause out would wipe the default privileges slate clean
  and apply any default privileges for the new owner, i.e. behave as
  though the object had just been created.

- ALTER ... OWNER TO ... WITH ( [OLD DEFAULT PRIVILEGES [, NO NEW
  PRIVILEGES] ) would ensure the previous default privileges had been
  applied and not apply any new default privileges, respectively.
  Order shouldn't matter.

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] Are we sufficiently clear that jsonb containment is nested?

2015-10-29 Thread Peter Geoghegan
On Thu, Oct 29, 2015 at 1:16 PM, Tom Lane  wrote:
> I think the existing text is largely my fault, so I'll do something with
> this.

Good. Thanks.

>> I still think it would be a good idea to go back to 9.4. I have reason
>> to believe that people are getting confused on this point.
>
> You didn't present evidence backing that up, but I agree that
> clarification is a sufficient reason to back-patch doc changes.

It's difficult to provide evidence for the existence of a perception
among users when it's a perception that a quasi-reasonable limitation
exists. The fact that jsonb_path_ops can make indexing complex jsonb
documents practical is fairly novel, so I'm not surprised that users
seem to imagine that containment does not work in a nested fashion.
"Existence" (the ? operator) actually has this limitation.

-- 
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] Are we sufficiently clear that jsonb containment is nested?

2015-10-29 Thread Tom Lane
Peter Geoghegan  writes:
> On Fri, Jun 26, 2015 at 8:05 AM, Robert Haas  wrote:
>> I would be fine with adding a *compact* example of this kind to the
>> table that begins section 8.14.3.  I probably would not back-patch it,
>> because the absence of that example is not an error in the
>> documentation, but I will not complain if someone else does.

> How about the attached? It's a compact refinement of my original
> example of more complicated jsonb containment.

You seem to have injected unrelated text into the middle of a discussion.
I think what you added is fine, but not where you put it; it would make
more sense as a standalone para.

I think the existing text is largely my fault, so I'll do something with
this.

> I still think it would be a good idea to go back to 9.4. I have reason
> to believe that people are getting confused on this point.

You didn't present evidence backing that up, but I agree that
clarification is a sufficient reason to back-patch doc changes.

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] Are we sufficiently clear that jsonb containment is nested?

2015-10-29 Thread Peter Geoghegan
On Thu, Oct 29, 2015 at 2:02 PM, Tom Lane  wrote:
> After studying the proposed patch a bit more, I still think the example
> is good, but the added text doesn't do much to explain your point.  If
> I get what your point is, which maybe I don't, I think the attached might
> clarify it better.  What do you think of this version?

Robert seemed to want to keep the example short, which I took on
board, but I myself think that your more worked out treatment is
better. I think this revision makes my point very well. I recommend
committing it.

-- 
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] Within CF app, "Bug Fixes" should be "Bug Fixes/Refactoring"

2015-10-29 Thread Josh Berkus
On 10/29/2015 01:10 PM, Tom Lane wrote:
> Michael Paquier  writes:
>> On Thu, Oct 29, 2015 at 8:33 PM, Peter Geoghegan  wrote:
>>> I think that within the CF app, we should either rename the patch
>>> topic "Bug Fixes" to "Bug Fixes/Refactoring", or introduce a new
>>> "Refactoring" topic. I prefer the first approach.
> 
>> I would vote for the second approach, with a separate category for 
>> refactoring.
> 
> Ditto.  Bug fixes are not at all like refactoring --- in particular, we'd
> usually not consider refactoring as fit material for back-patching.
> 
> "Refactoring" seems rather a narrow definition of what might show up
> in such a category, btw.  Maybe "Code Beautification" would be a
> suitable title?  I'm bikeshedding though.

"Miscellaneous"?

-- 
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] Are we sufficiently clear that jsonb containment is nested?

2015-10-29 Thread Tom Lane
Peter Geoghegan  writes:
> Robert seemed to want to keep the example short, which I took on
> board, but I myself think that your more worked out treatment is
> better. I think this revision makes my point very well. I recommend
> committing it.

After further thought I realized that part of the point you'd been
making was that people might fail to distinguish the behaviors of
containment and existence operators in this regard.  So I think the
example needs to make that point explicitly.  I whacked it around
a bit more and committed it.

regards, tom lane


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


[HACKERS] Did the "Full-text search in PostgreSQL in milliseconds" patches land?

2015-10-29 Thread Colin 't Hart
Hi,

I've been reading
wiki.postgresql.org/images/2/25/Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf
with interest and am wondering if these patches ever made it in to the
"official" version of Postgresql?

I've tried doing some of the queries as described in the slides using 9.5b1
but I get the "No operator matches the given name and argument type(s)."
error.

Thanks,

Colin


Re: [HACKERS] Freeze avoidance of very large table.

2015-10-29 Thread Masahiko Sawada
On Fri, Oct 30, 2015 at 1:26 AM, Masahiko Sawada  wrote:
> On Wed, Oct 28, 2015 at 12:58 PM, Amit Kapila  wrote:
>> On Sat, Oct 24, 2015 at 2:24 PM, Masahiko Sawada 
>> wrote:
>>>
>>> On Sat, Oct 24, 2015 at 10:59 AM, Amit Kapila 
>>> wrote:
>>> >
>>> > I think we can display information about relallfrozen it in
>>> > pg_stat_*_tables
>>> > as suggested by you.  It doesn't make much sense to keep it in pg_class
>>> > unless we have some usecase for the same.
>>> >
>>>
>>> I'm thinking a bit about implementing the read-only table that is
>>> restricted to update/delete and is ensured that whole table is frozen,
>>> if this feature is committed.
>>> The value of relallfrozen might be useful for such feature.
>>>
>
> Thank you for reviewing!
>
>> If we need this for read-only table feature, then better lets add that
>> after discussing the design of that feature.  It doesn't seem to be
>> advisable to have an extra field in system table which we might
>> need in yet not completely-discussed feature.
>
> I changed it so that the number of frozen pages is stored in
> pg_stat_all_tables as statistics information.
> Also, the tests related to counting all-visible bit and skipping
> vacuum are added to visibility map test, and the test related to
> counting all-frozen is added to stats collector test.
>
> Attached updated v20 patch.
>
>> Review Comments:
>> ---
>> 1.
>>   /*
>> - * Find buffer to insert this tuple into.  If the page is all visible,
>> - * this will also pin
>> the requisite visibility map page.
>> + * Find buffer to insert this tuple into.  If the page is all
>> visible
>> + * or all frozen, this will also pin the requisite visibility map and
>> + * frozen map page.
>>
>>  */
>>   buffer = RelationGetBufferForTuple(relation, heaptup->t_len,
>>
>>   InvalidBuffer, options, bistate,
>>
>>
>> I think it is sufficient to say in the end 'visibility map page'.
>> Let's not include 'frozen map page'.
>
> Fixed.
>
>>
>> 2.
>> + * corresponding page has been completely frozen, so the visibility map is
>> also
>> + * used for anti-wraparound
>> vacuum, even if freezing tuples is required.
>>
>> /all tuple/all tuples
>> /freezing tuples/freezing of tuples
>
> Fixed.
>
>> 3.
>> - * Are all tuples on heapBlk visible to all, according to the visibility
>> map?
>> + * Are all tuples on heapBlk
>> visible or frozen to all, according to the visibility map?
>>
>> I think it is better to modify the above statement as:
>> Are all tuples on heapBlk visible to all or are marked as frozen, according
>> to the visibility map?
>
> Fixed.
>
>> 4.
>> + * releasing *buf after it's done testing and setting bits, and must set
>> flags
>> + * which indicates what flag
>> we want to test.
>>
>> Here are you talking about the flags passed to visibilitymap_set(), if
>> yes, then above comment is not clear, how about:
>>
>> and must pass flags
>> for which it needs to check the value in visibility map.
>
> Fixed.
>
>> 5.
>> + * both how many pages we skipped according to all-frozen bit of visibility
>> + * map and how many
>> pages we freeze page, so we can update relfrozenxid if
>>
>> In above sentence word 'page' after freeze sounds redundant.
>> /we freeze page/we freeze
>>
>> Another suggestion:
>> /sum of them/sum of two
>
> Fixed.
>
>> 6.
>> + * This block is at least all-visible according to visibility map.
>> +
>>  * We check whehter this block is all-frozen or not, to skip to
>>
>> whether is mis-spelled
>
> Fixed.
>
>> 7.
>> + * If we froze any tuples or any tuples are already frozen,
>> + * mark the buffer
>> dirty, and write a WAL record recording the changes.
>>
>> Here, I think WAL record is written only when we mark some
>> tuple/'s as frozen not if we they are already frozen,
>> so in that regard, I think above comment is wrong.
>
> It's wrong.
> Fixed.
>
>> 8.
>> + /*
>> + * We cant't allow upgrading with link mode between 9.5 or before and 9.6
>> or later,
>> + *
>> because the format of visibility map has been changed on version 9.6.
>> + */
>>
>>
>> a. /cant't/can't
>> b. changed on version 9.6/changed in version 9.6
>> b. Won't such a change needs to be updated in pg_upgrade
>> documentation (Notes Section)?
>
> Fixed.
> And updated document.
>
>> 9.
>> @@ -180,6 +181,13 @@ transfer_single_new_db(pageCnvCtx *pageConverter,
>>
>> new_cluster.controldata.cat_ver >= VISIBILITY_MAP_CRASHSAFE_CAT_VER)
>>   vm_crashsafe_match = false;
>>
>> +
>> /*
>> + * Do we need to rewrite visibilitymap?
>> + */
>> + if (old_cluster.controldata.cat_ver <
>> VISIBILITY_MAP_FROZEN_BIT_CAT_VER &&
>> + new_cluster.controldata.cat_ver >=
>> VISIBILITY_MAP_FROZEN_BIT_CAT_VER)
>> + vm_rewrite_needed = true;
>>
>> ..
>>
>> @@ -276,7 +285,15 @@ transfer_relfile(pageCnvCtx *pageConverter, FileNameMap
>> *map,
>>   {
>>
>> pg_log(PG_VERBOSE, "copying \"%s\" to \"%s\"\n", old_file, new_file);
>>
>> - if ((msg =
>> 

Re: [HACKERS] Are we sufficiently clear that jsonb containment is nested?

2015-10-29 Thread Peter Geoghegan
On Thu, Oct 29, 2015 at 4:03 PM, Tom Lane  wrote:
> After further thought I realized that part of the point you'd been
> making was that people might fail to distinguish the behaviors of
> containment and existence operators in this regard.  So I think the
> example needs to make that point explicitly.  I whacked it around
> a bit more and committed it.

Looks good.

-- 
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] PoC: Partial sort

2015-10-29 Thread Peter Geoghegan
On Tue, Oct 20, 2015 at 4:17 AM, Alexander Korotkov
 wrote:
> Planner regression is fixed in the attached version of patch. It appears
> that get_cheapest_fractional_path_for_pathkeys() behaved wrong when no
> ordering is required.

I don't see an entry in the CF app for this. This seems like something
I should review, though.

-- 
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] Rework the way multixact truncations work

2015-10-29 Thread Andres Freund
Hi,

On October 29, 2015 7:59:03 AM GMT+01:00, Noah Misch  wrote:
>On Tue, Oct 27, 2015 at 03:44:10PM +0100, Andres Freund wrote:
>> On 2015-10-24 22:07:00 -0400, Noah Misch wrote:
>> > On Tue, Sep 22, 2015 at 07:57:27PM +0200, Andres Freund wrote:
>> > > On 2015-09-22 13:38:58 -0400, Robert Haas wrote:
>> > > > - If SlruDeleteSegment fails in unlink(), shouldn't we at the
>very
>> > > > least log a message?  If that file is still there when we loop
>back
>> > > > around, it's going to cause a failure, I think.
>> > > 
>> > > The existing unlink() call doesn't, that's the only reason I
>didn't add
>> > > a message there. I'm fine with adding a (LOG or WARNING?)
>message.
>> 
>> Note that I didn't add the warning after all, as it'd be too noisy
>> during repeated replay, as all the files would already be gone. We
>could
>> only emit it when the error is not ENOFILE, if people prefer that.
>> 
>> 
>> > Unlinking old pg_clog files is strictly an optimization.  If you
>were to
>> > comment out every unlink() call in slru.c, the only ill effect on
>CLOG is the
>> > waste of disk space.  Is the same true of MultiXact?
>> 
>> Well, multixacts are a lot larger than the other SLRUs, I think that
>> makes some sort of difference.
>
>That helps; thanks.  Your design seems good.  I've located only insipid
>defects.

Great!

> I propose to save some time by writing a patch series
>eliminating
>them, which you could hopefully review.  Does that sound good?

Yes, it does.

Andres

--- 
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] Is there any ordering to the values in guc.c?

2015-10-29 Thread Fabien COELHO



On 10/28/15 10:27 AM, Bill Moran wrote:

See subject. Aside from them being divvied up by datatype, they seem
to be ordered randomly. Since I'm putting together a patch that will
add some GUCs, do I just add them to the end of the list?


The initial commit grouped them logically, and it went downhill from
there. :)


Some "per-section" comments in the file would help to remain consistent.

--
Fabien.


--
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] Cross-check recent documentation changes

2015-10-29 Thread Robert Haas
On Thu, Oct 29, 2015 at 2:40 AM, Robins  wrote:
> Was reviewing recent commits, and it seems the following commit adds an
> extra line to some comments. Just wanted to cross-check if that was
> intentional.

I don't see that it changed any comments at all?

-- 
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] Rework the way multixact truncations work

2015-10-29 Thread Noah Misch
On Tue, Oct 27, 2015 at 03:44:10PM +0100, Andres Freund wrote:
> On 2015-10-24 22:07:00 -0400, Noah Misch wrote:
> > On Tue, Sep 22, 2015 at 07:57:27PM +0200, Andres Freund wrote:
> > > On 2015-09-22 13:38:58 -0400, Robert Haas wrote:
> > > > - If SlruDeleteSegment fails in unlink(), shouldn't we at the very
> > > > least log a message?  If that file is still there when we loop back
> > > > around, it's going to cause a failure, I think.
> > > 
> > > The existing unlink() call doesn't, that's the only reason I didn't add
> > > a message there. I'm fine with adding a (LOG or WARNING?) message.
> 
> Note that I didn't add the warning after all, as it'd be too noisy
> during repeated replay, as all the files would already be gone. We could
> only emit it when the error is not ENOFILE, if people prefer that.
> 
> 
> > Unlinking old pg_clog files is strictly an optimization.  If you were to
> > comment out every unlink() call in slru.c, the only ill effect on CLOG is 
> > the
> > waste of disk space.  Is the same true of MultiXact?
> 
> Well, multixacts are a lot larger than the other SLRUs, I think that
> makes some sort of difference.

That helps; thanks.  Your design seems good.  I've located only insipid
defects.  I propose to save some time by writing a patch series eliminating
them, which you could hopefully review.  Does that sound good?


-- 
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] Foreign join pushdown vs EvalPlanQual

2015-10-29 Thread Robert Haas
On Thu, Oct 29, 2015 at 6:05 AM, Kouhei Kaigai  wrote:
> In this case, the EPQ slot to store the joined tuple is still
> a challenge to be solved.
>
> Is it possible to use one or any of EPQ slots that are setup for
> base relations but represented by ForeignScan/CustomScan?

Yes, I proposed that exact thing upthread.

> In case when ForeignScan run a remote join that involves three
> base foreign tables (relid=2, 3, 5 for example), for example,
> no other code touches this slot. So, it is safe even if we put
> a joined tuple on EPQ slots of underlying base relations.
>
> In this case, EPQ slots are initialized as below:
>
>   es_epqTuple[0] ... EPQ tuple of base relation (relid=1)
>   es_epqTuple[1] ... EPQ of the joined tuple (for relis=2, 3 5)
>   es_epqTuple[2] ... EPQ of the joined tuple (for relis=2, 3 5), copy of above
>   es_epqTuple[3] ... EPQ tuple of base relation (relid=4)
>   es_epqTuple[4] ... EPQ of the joined tuple (for relis=2, 3 5), copy of above
>   es_epqTuple[5] ... EPQ tuple of base relation (relid=6)

You don't really need to initialize them all.  You can just initialize
es_epqTuple[1] and leave 2 and 4 unused.

> Then, if FDW/CSP is designed to utilize the preliminary joined
> tuples rather than local join, it can just raise the tuple kept
> in one of the EPQ slots for underlying base relations.
> If FDW/CSP prefers local join, it can perform as like local join
> doing; check join condition and construct a joined tuple by itself
> or by alternative plan.

Right.

-- 
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] psql: add \pset true/false

2015-10-29 Thread Robert Haas
On Thu, Oct 29, 2015 at 1:32 AM, Marko Tiikkaja  wrote:
>> 2. If you're the sort of person liable to be confused by t/f, you
>> probably aren't in the target audience for psql anyway.
>
> Really?  The difference between t/f is that the vertical squiggle is
> flipped, THAT'S IT.  Consider:
>
> t t f f f
> f t f t f
>
> Saying that I'm not target audience for not being able to see WTF is going
> on above I find offending.

Sorry, no offense intended.  It's really just never happened to me
that I've had a problem with this, and I've been using psql for quite
a few years now.  I do agree that if you have a bunch of values in a
row it's more apt to be confusing than with just one, but they won't
normally be as closely spaced as you have them there, because psql
inserts spacing and borders and column headers are usually more than
one character.

But I don't really want to argue about this.  I respect your opinion,
and I've given you mine, and wherever we end up based on the opinions
of others is OK with me.

-- 
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] Cross-check recent documentation changes

2015-10-29 Thread Amit Langote
On 2015/10/29 17:10, Robert Haas wrote:
> On Thu, Oct 29, 2015 at 2:40 AM, Robins  wrote:
>> Was reviewing recent commits, and it seems the following commit adds an
>> extra line to some comments. Just wanted to cross-check if that was
>> intentional.
> 
> I don't see that it changed any comments at all?

I think he means error messages, for example, why errmsg is replaced by
errmsg_plural in the concerned source code line (the following change)

-errmsg("database with OID %u must be vacuumed before %d
more multixact members are used",
+errmsg_plural("database with OID %u must be vacuumed
before %d more multixact member is used",
+  "database with OID %u must be vacuumed
before %d more multixact members are used",
+   MultiXactState->offsetStopLimit - nextOffset +
nmembers,

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] [ADMIN] Replication slots and isolation levels

2015-10-29 Thread Vladimir Borodin

> 27 окт. 2015 г., в 19:45, Vladimir Borodin  написал(а):
> 
> Hi all.
> 
> I’m wondering why do I get conflicts with recovery on hot standby using 
> replication slots and read commited isolation level? And if I start 
> repeatable read transaction I don’t get any errors. Below is some diagnostics.

+hackers@

Could anybody explain, why this is happening?

> 
> I’m using 9.4.4 (upgrade is planned) from yum.postgresql.org 
>  packages on both master and standby. Configs are 
> the same on both master and standby:
> 
> rpopdb01d/postgres M # SELECT name, setting FROM pg_settings
> WHERE category LIKE 'Replication%' or category LIKE 'Write-Ahead Log';
>  name | setting
> --+-
>  hot_standby  | on
>  hot_standby_feedback | on
>  max_replication_slots| 1
>  max_standby_archive_delay| 3
>  max_standby_streaming_delay  | 3
>  max_wal_senders  | 10
>  synchronous_standby_names|
>  vacuum_defer_cleanup_age | 20
>  wal_keep_segments| 64
>  wal_receiver_status_interval | 1
>  wal_receiver_timeout | 6
>  wal_sender_timeout   | 3000
> (12 rows)
> 
> Time: 1.583 ms
> rpopdb01d/postgres M #
> 
> On the master I’ve created a physical replication slot and attached standby 
> to it, I do see changing xmin and restart_lsn fields in pg_replication_slots 
> view.
> 
> rpopdb01d/postgres M # select * from pg_replication_slots ;
>  slot_name| plugin | slot_type | datoid | database | active |
> xmin| catalog_xmin |  restart_lsn
> --++---++--+++--+---
>  rpopdb01e_domain_com | [null] | physical  | [null] | [null]   | t  | 
> 2127399287 |   [null] | 960B/415C79C8
> (1 row)
> 
> Time: 0.463 ms
> rpopdb01d/postgres M #
> 
> When I start a read commited transaction on standby (or use autocommit mode, 
> doesn’t matter) I still see that xmin in pg_replication_slots view on master 
> increases. If I do run a heavy SELECT statement, at some point of time 
> (presumably after vacuum_defer_cleanup_age expires) standby starts to lag 
> replication apply and when it hits max_standby_streaming_delay I get 40001 
> sql code, either ERROR or FATAL:
> 
> rpopdb01e/rpopdb R # SHOW transaction_isolation ;
>  transaction_isolation
> ---
>  read committed
> (1 row)
> 
> Time: 0.324 ms
> rpopdb01e/rpopdb R # SELECT count(*) FROM big_table;
> ERROR:  40001: canceling statement due to conflict with recovery
> DETAIL:  User query might have needed to see row versions that must be 
> removed.
> LOCATION:  ProcessInterrupts, postgres.c:2990
> Time: 199791.339 ms
> rpopdb01e/rpopdb R #
> 
> 
> 
> rpopdb01e/rpopdb R # SHOW transaction_isolation ;
>  transaction_isolation
> ---
>  read committed
> (1 row)
> 
> Time: 0.258 ms
> rpopdb01e/rpopdb R # BEGIN;
> BEGIN
> Time: 0.067 ms
> rpopdb01e/rpopdb R # SELECT count(*) FROM big_table;
> FATAL:  40001: terminating connection due to conflict with recovery
> DETAIL:  User was holding a relation lock for too long.
> LOCATION:  ProcessInterrupts, postgres.c:2857
> server closed the connection unexpectedly
>   This probably means the server terminated abnormally
>   before or while processing the request.
> The connection to the server was lost. Attempting reset: Succeeded.
> Time: 307864.830 ms
> rpopdb01e/rpopdb R #
> 
> The behavior is the same as expected to be without using replication slots.
> 
> But when I start repeatable read transaction xmin field in 
> pg_replication_slots view on master freezes (while restart_lsn is still 
> increasing) and I don’t get any replication lag and conflicts with recovery. 
> When I end this transaction, xmin starts increasing again.
> 
> rpopdb01e/rpopdb R # begin transaction isolation level repeatable read;
> BEGIN
> Time: 0.118 ms
> rpopdb01e/rpopdb R # SELECT count(*) FROM big_table;
>count
> 
>  3106222429
> (1 row)
> 
> Time: 411944.889 ms
> rpopdb01e/rpopdb R # ROLLBACK;
> ROLLBACK
> Time: 0.269 ms
> rpopdb01e/rpopdb R #
> 
>  And that is what I expect. Am I missing something or is it expected behavior 
> in read commited mode?
> 
> Thanks in advance.
> 
> --
> May the force be with you…
> https://simply.name 


--
May the force be with you…
https://simply.name



Re: [HACKERS] Re: [BUGS] BUG #13611: test_postmaster_connection failed (Windows, listen_addresses = '0.0.0.0' or '::')

2015-10-29 Thread Tatsuo Ishii
> On Tue, Oct 27, 2015 at 05:31:25PM +0900, Tatsuo Ishii wrote:
>> > No, PQping("host='127.0.0.1'") fails to reach a listen_addresses='::' 
>> > server
>> > on many systems.  Here's what I thought Kondo was proposing:
>> > 
>> > --- a/src/bin/pg_ctl/pg_ctl.c
>> > +++ b/src/bin/pg_ctl/pg_ctl.c
>> > @@ -649,5 +649,9 @@ test_postmaster_connection(pgpid_t pm_pid, bool 
>> > do_checkpoint)
>> >  
>> > -  /* If postmaster is listening 
>> > on "*", use localhost */
>> > +  /* explanation here */
>> >if (strcmp(host_str, "*") == 0)
>> >strcpy(host_str, 
>> > "localhost");
>> > +  else if (strcmp(host_str, 
>> > "0.0.0.0") == 0)
>> > +  strcpy(host_str, 
>> > "127.0.0.1");
>> > +  else if (strcmp(host_str, "::") 
>> > == 0)
>> > +  strcpy(host_str, "::1");
>> >  
>> 
>> I see. Would you like to commit this?
> 
> I am happy to finish it, but I am no less happy if you finish it.  Which do
> you prefer?

Please go ahead and commit.

> Should the back-branch commits mirror the master branch?  A more-cautious
> alternative would be to, in back branches, wrap the change in #ifdefs so it
> takes effect only on Windows, OpenBSD and NetBSD.  It could break setups with
> local firewall rules that block connections to "127.0.0.1" or "::1" without
> blocking "0.0.0.0" or "::".  Such firewall rules sound outlandish enough that
> I would be fairly comfortable not worrying about this and making the change
> unconditional in all branches.  It's a judgment call, though.

I think back patching with #ifdefs is better. On Windows etc. the case
has been broken anyway and the fix should only bring benefits to users.

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] [ADMIN] Replication slots and isolation levels

2015-10-29 Thread Michael Paquier
On Thu, Oct 29, 2015 at 9:42 AM, Vladimir Borodin wrote:
> I’m wondering why do I get conflicts with recovery on hot standby using
> replication slots and read commited isolation level? And if I start
> repeatable read transaction I don’t get any errors. Below is some
> diagnostics.

In the case of repeatable read the standby will wait before applying
the VACUUM WAL record cleaning up a relation page. Hence you won't get
conflicts in this case.
-- 
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] Cross-check recent documentation changes

2015-10-29 Thread Amit Langote

Robins,

On 2015/10/29 10:40, Robins wrote:
> Hi,
> 
> Was reviewing recent commits, and it seems the following commit adds an
> extra line to some comments. Just wanted to cross-check if that was
> intentional.
> 
> Commit: http://goo.gl/zxA00l
> Pre-Commit: http://goo.gl/2DpLxi
> Post-Commit: http://goo.gl/eKcNm3

You can see a minor change in the "extra line" - member vs. members in the
first and second version, respectively, of essentially the same message.
errmsg_plural() function determines whether to output the singular version
or the plural based on numeric value of the first variadic argument passed
to it. For example, if "%d" in the formatted output turns out be 1, "... 1
multixact members ..." in the output message sounds awkward. So does "...
10 multixact member". Hence the committed change.

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] [ADMIN] Replication slots and isolation levels

2015-10-29 Thread Vladimir Borodin

> 29 окт. 2015 г., в 13:12, Michael Paquier  
> написал(а):
> 
> On Thu, Oct 29, 2015 at 9:42 AM, Vladimir Borodin wrote:
>> I’m wondering why do I get conflicts with recovery on hot standby using
>> replication slots and read commited isolation level? And if I start
>> repeatable read transaction I don’t get any errors. Below is some
>> diagnostics.
> 
> In the case of repeatable read the standby will wait before applying
> the VACUUM WAL record cleaning up a relation page. Hence you won't get
> conflicts in this case.

Standby will receive but will not apply? Or master will not vacuum needed by 
standby pages? It seems that the second one is happening because replication 
lag on standby does not increase while issuing such repeatable read transaction.

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


--
Да пребудет с вами сила…
https://simply.name/ru



Re: [HACKERS] psql: add \pset true/false

2015-10-29 Thread Daniel Verite
Marko Tiikkaja wrote:

> Since the default t/f output for booleans is not very user friendly, 
> attached is a patch which enables you to do for example the following:

Personally I think it would be worth having, but how about
booleans inside ROW() or composite types ?

test=> \pset true 1
Boolean TRUE display is "1".
test=> \pset false 0
Boolean FALSE display is "0".

test #1: 

test=> select 't'::bool,'f'::bool;
 bool | bool 
--+--
 1| 0

test #2:

test=> select ('t'::bool,'f'::bool);
  row  
---
 (t,f)

test #3:

test=> create type abc as (a bool, b bool, c bool);
test=> select (true,false,true)::abc;
   row   
-
 (t,f,t)


I understand that the patch translates t/f only if the output type
has the OID for bool, which is not the case in #2 or #3, but I guess
users would expect #2 to output (1,0) and #3 (1,0,1).


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
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] psql: add \pset true/false

2015-10-29 Thread Marko Tiikkaja

On 10/29/15 11:51 AM, Daniel Verite wrote:

Marko Tiikkaja wrote:


Since the default t/f output for booleans is not very user friendly,
attached is a patch which enables you to do for example the following:


Personally I think it would be worth having, but how about
booleans inside ROW() or composite types ?


There's not enough information sent over to do that in the client.

Note that this works the same way as  \pset null  with  SELECT 
ROW(NULL), so I don't consider it a show stopper for the patch.



.m


--
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] [ADMIN] Replication slots and isolation levels

2015-10-29 Thread Michael Paquier
On Thu, Oct 29, 2015 at 11:33 AM, Vladimir Borodin wrote:
> 29 окт. 2015 г., в 13:12, Michael Paquier написал(а):
>> In the case of repeatable read the standby will wait before applying
>> the VACUUM WAL record cleaning up a relation page. Hence you won't get
>> conflicts in this case.
>
> Standby will receive but will not apply? Or master will not vacuum needed by
> standby pages? It seems that the second one is happening because replication
> lag on standby does not increase while issuing such repeatable read
> transaction.

Standby will receive the record but not replay it until the
transaction doing REPEATABLE READ transactions that needs those rows
commits on the standby. The WAL flush position on the standby
continues to move on. This depends of course on
max_standby_streaming_delay which may decide or not to force the
transaction to cancel if it takes too long. Someone feel free to
correct me if I am missing something here.
-- 
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] Cube extension kNN support

2015-10-29 Thread Stas Kelvich
Hello.

That is updated version of the patch with proper update scripts.

Also i’ve noted that documentation states the wrong thing:

“It does not matter which order the opposite corners of a cube are entered in. 
The cube functions automatically swap values if needed to create a uniform 
"lower left — upper right" internal representation."

But in practice cubes stored "as is" and that leads to problems with getting 
cubes sorted along specific dimension directly from index.
As a simplest workaround i’ve deleted that sentence from docs and implemented 
two coordinate getters -> and ~>. First one returns
coordinate of cube as it stored, and second returns coordinate of cube 
normalised to (LL,UR)-form.

Other way to fix thing is to force ’normalization’ while creating cube. But 
that can produce wrong sorts with already existing data.

> On 09 Jul 2015, at 16:40, Alexander Korotkov  wrote:
> 
> Hi!
> 
> On Sat, May 9, 2015 at 6:53 AM, Stas Kelvich  wrote:
> Patch is pretty ready, last issue was about changed extension interface, so 
> there should be migration script and version bump.
> Attaching a version with all migration stuff.
> 
> I can't see cube--1.0--1.1.sql in the patch. Did forget to include it?
> 
> --
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company 

Stas Kelvich
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



distances.patch
Description: Binary data

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