Re: [HACKERS] Using quicksort for every external sort run

[Peter Geoghegan]

On Wed, Nov 18, 2015 at 3:29 PM, Peter Geoghegan  wrote:
>> Overall this is very nice.  Doing some real world index builds of
>> short text (~20 bytes ascii) identifiers, I could easily get speed ups
>> of 40% with your patch if I followed the philosophy of "give it as
>> much maintenance_work_mem as I can afford".  If I fine-tuned the
>> maintenance_work_mem so that it was optimal for each sort method, then
>> the speed up quite a bit less, only 22%.  But 22% is still very
>> worthwhile, and who wants to spend their time fine-tuning the memory
>> use for every index build?
>
> Thanks, but I expected better than that.

It also might have been that you used a "quicksort with spillover".
That still uses a heap to some degree, in order to avoid most I/O, but
with a single backend sorting that can often be slower than the
(greatly overhauled) "external merge" sort method (both of these
algorithms are what you'll see in EXPLAIN ANALYZE, which can be a
little confusing because it isn't clear what the distinction is in
some cases).

You might also very occasionally see an "external sort" (this is also
a description from EXPLAIN ANALYZE), which is generally slower (it's a
case where we were unable to do a final on-the-fly merge, either
because random access is requested by the caller, or because multiple
passes were required -- thankfully this doesn't happen most of the
time).

-- 
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] Freeze avoidance of very large table.

[Masahiko Sawada]

On Mon, Nov 23, 2015 at 6:27 AM, Jeff Janes  wrote:
> On Sun, Nov 22, 2015 at 8:16 AM, Masahiko Sawada  
> wrote:
>
>> Thank you for taking the time to review this patch!
>> The updated version patch is attached.
>
> I am skeptical about just copying the old page header to be two new
> page headers.  I don't know what the implications for this will be on
> pd_lsn.  Since pg_upgrade can only run on a cluster that was cleanly
> shutdown, I think that just copying it from the old page to both new
> pages it turns into might be fine.  But pd_checksum will certainly be
> wrong, breaking pg_upgrade for cases where checksums are turned on in.
> It needs to be recomputed on both new pages.  It looks like there is
> no precedence for doing that in pg_upgrade so this will be breaking
> new ground.
>

Yeah, we need to consider to compute checksum if enabled.
I've changed the patch, and attached.
Please review it.

Regards,

--
Masahiko Sawada


000_add_frozen_bit_into_visibilitymap_v27.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] Using quicksort for every external sort run

[Simon Riggs]

On 20 November 2015 at 22:58, Peter Geoghegan  wrote:


> The numbers speak for themselves here. I just want to be clear about
> the disadvantages of what I propose, even if it's well worth it
> overall in most (all?) cases.
>

My feeling is that numbers rarely speak for themselves, without LSD. (Which
numbers?)

How are we doing here? Keen to see this work get committed, so we can move
onto parallel sort. What's the summary?

How about we commit it with a sort_algorithm = 'foo' parameter so we can
compare things before release of 9.6?

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

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

Re: [HACKERS] Additional role attributes && superuser review

[Alvaro Herrera]

Stephen Frost wrote:

> Even so, in the interest of having more fine-grained permission
> controls, I've gone ahead and added a pg_switch_xlog default role.
> Note that this means that pg_switch_xlog() can be called by both
> pg_switch_xlog roles and pg_backup roles.  I'd be very much against
> removing the ability to call pg_switch_xlog from the pg_backup role as
> that really is a capability which is needed by users running backups and
> it'd just add unnecessary complexity to require users setting up backup
> tools to grant two different roles to get the backup to work.

Isn't it simpler to grant pg_switch_xlog to pg_backup in the default
config?

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


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

Re: [HACKERS] Re: In-core regression tests for replication, cascading, archiving, PITR, etc.

[Alvaro Herrera]

Michael Paquier wrote:

> - Manage node information using package/class PostgresNode.pm and have
> RecoveryTest use it. I have actually made PostgresNode bare-bone and simple
> on purpose: one can initialize the node, append configuration parameters to
> it and manage it through start/stop/restart (we may want to add reload and
> promote actually if needed).

This looks great as a starting point.  I think we should make TestLib
depend on PostgresNode instead of the other way around.  I will have a
look at that (I realize this means messing with the existing tests).

> I have also arrived at the conclusion that it is not really worth
> adding a node status flag in PostgresNode because the port number
> saved there is sufficient when doing free port lookup, and the list of
> nodes used in a recovery test are saved in an array.

I don't disagree with this in principle, but I think the design that you
get a new PostgresNode object by calling get_free_port is strange.  I
think the port lookup code should be part of either TestLib or
PostgresNode, not RecoveryTest.

> - Add new module RecursiveCopy to be used for base backups. This removes
> the dependency with Archive::Tar. PostgresNode makes use of that when
> initializing a node from a backup.

Great.

> - Tests have been updated to use the PostgresNode objects instead of the
> port number as identifier. That's more portable.

Makes sense.

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


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

Re: [HACKERS] New email address

[Larry Rosenman]

On 2015-11-24 13:43, Alvaro Herrera wrote:

Larry Rosenman wrote:

On 2015-11-24 13:11, Tom Lane wrote:
>Kevin Grittner  writes:
>>On Tue, Nov 24, 2015 at 11:10 AM, Tom Lane  wrote:
change the From header (and add a Reply-To, so replies still work).
>
>>If this were done, would the other steps (not changing the subject
>>or body of the email) be necessary?
>
>See my followup: I think it's probably true that we could skip those
>changes.  But Rudy commented that there's a lot of underdocumented
>subtlety here.  There might be reasons I'm missing why we'd need to
>stop doing those things.  It doesn't seem like DMARC as such would
>force that, but perhaps those things trigger some popular antispam
>heuristics.



Any Header or Body changes will invalidate most, if not all, DKIM
signatures.  Since DKIM is used as part
of DMARC, it's a problem.

Not sure what MajorDomo2 will allow you to do.


We can turn off header and body changes easily enough, but changing the
"From:" address requires patching the Majordomo2 source code; and, as
you may already be aware, the maintainers gave up on Mj2 a decade ago,
so we'd be on our own for that.  I cannot promise any sort of timeline
for getting that done; and since that's an essential part of the 
recipe,

I don't see any point in doing the other changes either for the time
being.

I think the breakage of DKIM signatures is already causing some pain
(though nowhere near the level of DMARC).

Of course, removing all the "List-" headers *and* our custom footers is
a huge step backwards in terms of mailing list functionality :-(  Also,
removing the [HACKERS] etc tags will annoy some people, for sure.
You don't have to remove the List- headers.  DKIM says what headers it's 
using.


--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 214-642-9640 E-Mail: l...@lerctr.org
US Mail: 7011 W Parmer Ln, Apt 1115, Austin, TX 78729-6961


--
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] New email address

[Tom Lane]

Larry Rosenman  writes:
> On 2015-11-24 13:11, Tom Lane wrote:
>> Kevin Grittner  writes:
>>> If this were done, would the other steps (not changing the subject
>>> or body of the email) be necessary?
>> 
>> See my followup: I think it's probably true that we could skip those
>> changes.

> Any Header or Body changes will invalidate most, if not all, DKIM 
> signatures.  Since DKIM is used as part of DMARC, it's a problem.

Yeah, but SPF is also used as part of DMARC, which means that merely
forwarding somebody's email out of our listserv is probably going to look
like spam, even if we didn't change anything at all about the message
contents.  Also, some sources sign Reply-To: and/or Sender: (Yahoo,
at least, does the former) which means you can't replace those headers
either without breaking the DKIM signature.  The only fix for that is to
rewrite From:, and once you do that I don't see a convincing argument why
you can't also rewrite Subject: and add a footer if you feel like it.

(For context, Rudy had implemented the no-header-change and no-footers
changes on our neighborhood list quite some time ago; the From: changes
and local DKIM-Signature: were the things that went in last week.  I'm of
the opinion that he could revert the former now that he's done the latter;
but he seems to think not, for reasons he was not able to explain to me
convincingly.  It seems to me that the first two changes were meant to
allow the source's DKIM-Signature to still apply, and now that he's given
up on that strategy, I don't see what they're buying.)

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] New email address

[Tom Lane]

Larry Rosenman  writes:
> On 2015-11-24 13:43, Alvaro Herrera wrote:
>> Of course, removing all the "List-" headers *and* our custom footers is
>> a huge step backwards in terms of mailing list functionality :-(  Also,
>> removing the [HACKERS] etc tags will annoy some people, for sure.

> You don't have to remove the List- headers.  DKIM says what headers it's 
> using.

Yeah.  RFC 6376 is worth a quick look if you want to opine knowledgeably
about this.  Basically, the DKIM crypto hash covers the message body plus
those header fields enumerated in the DKIM-Signature header, and 6376
gives this advice:

   The From header field MUST be signed (that is, included in the "h="
   tag of the resulting DKIM-Signature header field).  Signers SHOULD
   NOT sign an existing header field likely to be legitimately modified
   or removed in transit.  In particular, [RFC5321] explicitly permits
   modification or removal of the Return-Path header field in transit.
   Signers MAY include any other header fields present at the time of
   signing at the discretion of the Signer.

  INFORMATIVE OPERATIONS NOTE: The choice of which header fields to
  sign is non-obvious.  One strategy is to sign all existing, non-
  repeatable header fields.  An alternative strategy is to sign only
  header fields that are likely to be displayed to or otherwise be
  likely to affect the processing of the message at the receiver.  A
  third strategy is to sign only "well-known" headers.  Note that
  Verifiers may treat unsigned header fields with extreme
  skepticism, including refusing to display them to the end user or
  even ignoring the signature if it does not cover certain header
  fields.  For this reason, signing fields present in the message
  such as Date, Subject, Reply-To, Sender, and all MIME header
  fields are highly advised.

I think the advice to sign Reply-To and Sender is rather ill-advised,
particularly the latter, as signing that absolutely would break mailing
list forwarding.

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] Additional role attributes && superuser review

[Stephen Frost]

On Tuesday, November 24, 2015, Alvaro Herrera 
wrote:

> Stephen Frost wrote:
>
> > Even so, in the interest of having more fine-grained permission
> > controls, I've gone ahead and added a pg_switch_xlog default role.
> > Note that this means that pg_switch_xlog() can be called by both
> > pg_switch_xlog roles and pg_backup roles.  I'd be very much against
> > removing the ability to call pg_switch_xlog from the pg_backup role as
> > that really is a capability which is needed by users running backups and
> > it'd just add unnecessary complexity to require users setting up backup
> > tools to grant two different roles to get the backup to work.
>
> Isn't it simpler to grant pg_switch_xlog to pg_backup in the default
> config?
>

I'm not against it, but it would imply a set of data lines for
pg_auth_members, which we don't have today. We can't easily directly GRANT
the role due to the restrictions put in place to prevent regular users from
changing the system roles.  On the other hand, we could change the check to
only apply when we aren't in bootstrap mode.

Thanks!

Stephen

Re: [HACKERS] New email address

[Stefan Kaltenbrunner]

On 11/24/2015 07:55 PM, Tom Lane wrote:
> I wrote:
>> "Rudolph T. Maceyko"  writes:
>>> The basic changes since Yahoo implemented their p=reject DMARC policy
>>> last year (and others followed) were:
>>> * make NO CHANGES to the body of the message--no headers, footers, etc. 
>>> * make NO CHANGES to the subject header of the message--no more
>>> "[Highland Park]" 
>>> * when mail comes to the list from a domain that uses a p=reject DMARC
>>> policy, CHANGE THE FROM HEADER so that it comes from the list.
> 
> After further off-list discussion with Rudy, I'm not entirely convinced
> by his reasoning for dropping Subject-munging and footer-addition; it
> seems like that might be at least in part a limitation of his
> mailman-based infrastructure.
> 
> The clearly critical thing, though, is that when forwarding a message from
> a person at a DMARC-using domain, we would have to replace the From: line
> with something @postgresql.org.  This is what gets it out from under the
> original domain's DMARC policy.

exactly

> 
> The other stuff Rudy did, including adding the list's own DKIM-Signatures
> and publishing DMARC and SPF policy for the list domain, is not
> technically necessary (yet) but it makes the list traffic less likely to
> get tagged as spam by antispam heuristics.  And, as he noted, there are
> feedback loops that mean once some traffic gets tagged as spam it becomes
> more likely that future traffic will be.

well the purpose of the feedback loops is for the receiving ISP to feed
back information about (mostly) user tagged "I dont want this email"
(which is what the work on - not actual heuristics triggering) stuff -
from historical experience that works very very poor for a mailing list
like ours because in almost all cases subscribers are simply using the
"this is spam" feature to declare an email as "unwanted" and using it as
a shortcut to actually unsubscribing (and not thinking about any further
impact).


> 
> If Rudy's right that Gmail is likely to start using p=reject DMARC policy,
> we are going to have to do something about this before that; we have too
> many people on gmail.  I'm not exactly in love with replacing From:
> headers but there may be little alternative.  We could do something like
>   From: Persons Real Name 
>   Reply-To: ...
> so that at least the person's name would still be readable in MUA
> displays.
> 
> We'd have to figure out whether we want the Reply-To: to be the original
> author or the list; as I recall, neither of those are fully satisfactory.

well this is basically what it boils down to - we will absolutely have
to do is replacing "From:" (assuming the gmail rumour is true which I'm
not entirely convinced though) but what are we prepared to replace the
current system with and are we accepting that the lists are going to
work differently.


Stefan


-- 
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] Additional role attributes && superuser review

[Stephen Frost]

Michael,

* Michael Paquier (michael.paqu...@gmail.com) wrote:
> On Sat, Nov 21, 2015 at 2:29 AM, Stephen Frost  wrote:
> > * Michael Paquier (michael.paqu...@gmail.com) wrote:
> > Even so, in the interest of having more fine-grained permission
> > controls, I've gone ahead and added a pg_switch_xlog default role.
> > Note that this means that pg_switch_xlog() can be called by both
> > pg_switch_xlog roles and pg_backup roles.  I'd be very much against
> > removing the ability to call pg_switch_xlog from the pg_backup role as
> > that really is a capability which is needed by users running backups and
> > it'd just add unnecessary complexity to require users setting up backup
> > tools to grant two different roles to get the backup to work.
> 
> There is going to be many opinions regarding the granularity of this
> control, each one of us having a different opinion at the end. I don't
> think this should be a stopper for this patch, hence I am fine with the
> judgement you think is good. We could still more finely tune those default
> roles later in the dev cycle of 9.6 (10.0?).

Agreed.

> Thanks, this looks good to me.

Great.

> I guess that's better than nothing.

Agreed.

> I don't think you mean to refer to the switch of segments files here. Same
> comment for pg_current_xlog_insert_location, pg_last_xlog_receive_location
> and pg_last_xlog_replay_location.

Urgh.  Got a bit ahead of myself there, apologies.  I've updated all of
these and a number of other minor typos and incorrect comments along the
way.

> +   ereport(ERROR,
> +   (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
> +errmsg("must be superuser or member of
> pg_file_settings to see all config file settings")));
> Should avoid abbreviations => "all configuration file settings".

Fixed.

>
> -\dg[+] [  linkend="APP-PSQL-patterns"> class="parameter">pattern ]
> +\dgS[+] [  linkend="APP-PSQL-patterns"> class="parameter">pattern ]
>  
> I'm picky here, but that should be "\dg[S+]". Same for \du[S+].

Fixed

Updated patch attached.

Thanks!

Stephen
From 37b4627352287328ad28cd167620f51c26a68f04 Mon Sep 17 00:00:00 2001
From: Stephen Frost 
Date: Wed, 18 Nov 2015 11:50:57 -0500
Subject: [PATCH 1/3] Add note regarding permissions in pg_catalog

Add a note to the system catalog section pointing out that while
modifying the permissions on catalog tables is possible, it's
unlikely to have the desired effect.
---
 doc/src/sgml/catalogs.sgml | 11 +++
 1 file changed, 11 insertions(+)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 97ef618..3b7768c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -21,6 +21,17 @@
particularly esoteric operations, such as adding index access methods.
   
 
+  
+   
+Changing the permissions on objects in the system catalogs, while
+possible, is unlikely to have the desired effect as the internal
+lookup functions use a cache and do not check the permissions nor
+policies of tables in the system catalog.  Further, permission
+changes to objects in the system catalogs are not preserved by
+pg_dump or across upgrades.
+   
+  
+
  
   Overview
 
-- 
2.5.0


From 408a8ee807edbe875ff9fd860ebfd6c859dcffb8 Mon Sep 17 00:00:00 2001
From: Stephen Frost 
Date: Wed, 30 Sep 2015 07:04:55 -0400
Subject: [PATCH 2/3] Reserve the "pg_" namespace for roles

This will prevent users from creating roles which begin with "pg_" and
will check for those roles before allowing an upgrade using pg_upgrade.

This will allow for default roles to be provided at initdb time.
---
 doc/src/sgml/ref/psql-ref.sgml  |  8 +--
 src/backend/catalog/catalog.c   |  5 ++--
 src/backend/commands/user.c | 40 
 src/backend/utils/adt/acl.c | 41 +
 src/bin/pg_dump/pg_dumpall.c|  2 ++
 src/bin/pg_upgrade/check.c  | 40 ++--
 src/bin/psql/command.c  |  4 ++--
 src/bin/psql/describe.c |  5 +++-
 src/bin/psql/describe.h |  2 +-
 src/bin/psql/help.c |  4 ++--
 src/include/utils/acl.h |  1 +
 src/test/regress/expected/rolenames.out | 18 +++
 src/test/regress/sql/rolenames.sql  |  8 +++
 13 files changed, 166 insertions(+), 12 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 5899bb4..e0de107 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1336,13 +1336,15 @@ testdb=
 
 
   
-\dg[+] [ pattern ]
+\dg[S+] [ pattern ]
 
 
 Lists database roles.
 (Since the concepts of users and groups have been
 unified into roles, this command 

Re: [HACKERS] New email address

[Alvaro Herrera]

Larry Rosenman wrote:
> On 2015-11-24 13:11, Tom Lane wrote:
> >Kevin Grittner  writes:
> >>On Tue, Nov 24, 2015 at 11:10 AM, Tom Lane  wrote:
> change the From header (and add a Reply-To, so replies still work).
> >
> >>If this were done, would the other steps (not changing the subject
> >>or body of the email) be necessary?
> >
> >See my followup: I think it's probably true that we could skip those
> >changes.  But Rudy commented that there's a lot of underdocumented
> >subtlety here.  There might be reasons I'm missing why we'd need to
> >stop doing those things.  It doesn't seem like DMARC as such would
> >force that, but perhaps those things trigger some popular antispam
> >heuristics.

> Any Header or Body changes will invalidate most, if not all, DKIM
> signatures.  Since DKIM is used as part
> of DMARC, it's a problem.
> 
> Not sure what MajorDomo2 will allow you to do.

We can turn off header and body changes easily enough, but changing the
"From:" address requires patching the Majordomo2 source code; and, as
you may already be aware, the maintainers gave up on Mj2 a decade ago,
so we'd be on our own for that.  I cannot promise any sort of timeline
for getting that done; and since that's an essential part of the recipe,
I don't see any point in doing the other changes either for the time
being.

I think the breakage of DKIM signatures is already causing some pain
(though nowhere near the level of DMARC).

Of course, removing all the "List-" headers *and* our custom footers is
a huge step backwards in terms of mailing list functionality :-(  Also,
removing the [HACKERS] etc tags will annoy some people, for sure.

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


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

Re: [HACKERS] Revisiting pg_stat_statements and IN() (Was: Re: pg_stat_statements fingerprinting logic and ArrayExpr)

[Peter Geoghegan]

On Tue, Nov 24, 2015 at 10:55 PM, Jim Nasby  wrote:
> I'm not following your point. Obviously you can't compare int to text that
> doesn't convert back to an int, but that's not what I was talking about.

I didn't see what else you could have meant. In any case, the type
text has no involvement in your example.

pg_stat_statements sees the following SQL queries as equivalent:

select integer '5';

select 6;

select 7::int4;

-- 
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] pg_hba_lookup function to get all matching pg_hba.conf entries

[Haribabu Kommi]

On Tue, Nov 17, 2015 at 9:37 AM, Peter Eisentraut  wrote:
> On 11/16/15 2:37 AM, Haribabu Kommi wrote:
>> On Mon, Nov 16, 2015 at 2:30 PM, Peter Eisentraut  wrote:
>>> On 7/21/15 5:15 AM, Haribabu Kommi wrote:
 With the output of this view, administrator can identify the lines
 that are matching for the given
 criteria easily without going through the file.
>>>
>>> How is this useful?  I could see the use if you want to debug cases of
>>> user foo on host bar says they can't connect, but you can't impersonate
>>> them to verify it.  But then all you need is a function with a scalar
>>> result, not a result set.
>>
>> Do you mean the function should return true or false based on the connection
>> status with the provided arguments?
>>
>> I also feel difficult to understand the function result as compared to a 
>> view.
>
> An hba lookup is essentially a lookup by user name, database name,
> client address, yielding an authentication method (possibly with
> parameters).  So I think this function should work that way as well:
> arguments are user name, database name, and so on, and the return value
> is an authentication method.  Maybe it would be some kind of record,
> with line number and some parameters.
>
> That would address the use case I put forth above.  I don't know whether
> that's what you were going for.

Thanks. Here I attached the poc patch that returns authentication method of the
first matched hba entry in pg_hba.conf with the given input values.
Currently these
functions returns text type. Based on the details required to be
printed, it can
be changed.

postgres=# select pg_hba_lookup('all', 'all');
 pg_hba_lookup
---
 trust
(1 row)

comments for the approach?

Regards,
Hari Babu
Fujitsu Australia


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

[HACKERS] What .gitignore files do in the tarball?

[Victor Wagner]

Collegues,

I've noticed that source distribution archive of the postgresql contain
more than hundred of .gitignore files and one .gitattributes.

Is it just a bug nobody bothered to fix, or these files can make
any sense outside git repository?

Fix of the problem is quite trivial:

diff --git a/GNUmakefile.in b/GNUmakefile.in
index 15fba9f..beef51a 100644
--- a/GNUmakefile.in
+++ b/GNUmakefile.in
@@ -93,7 +93,7 @@ distdir-location:
 
 distdir:
rm -rf $(distdir)* $(dummy)
-   for x in `cd $(top_srcdir) && find . \( -name CVS -prune \) -o
\( -name .git -prune \) -o -print`; do \
+   for x in `cd $(top_srcdir) && find . \( -name CVS -prune \) -o
\( -name .git -prune \) -o \( \! -name ".git*" -print \)`; do \
file=`expr X$$x : 'X\./\(.*\)'`; \ if test -d "$(top_srcdir)/$$file" ;
then \ mkdir "$(distdir)/$$file" && chmod 777 "$(distdir)/$$file"; \
-- 
  Victor Wagner



-- 
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] problem with msvc linker - cannot build orafce

[Pavel Stehule]

Dne 24. 11. 2015 15:44 napsal uživatel "Chapman Flack" <
c...@anastigmatix.net>:
>
> On 11/24/2015 05:33 AM, Kisung Kim wrote:
> > 2015-11-24 8:12 GMT+09:00 Chapman Flack :
> >> On 11/23/15 15:14, Tom Lane wrote:
> >>> Lack of PGDLLIMPORT on the extern declaration, no doubt.
> >>
> > Actually, we encountered the situation before couple of months.
> > A client wanted to use orafce on Windows and the same build problem
> > occurred.
> > We performed a workaround to edit the PG source to export unresolved
> > symbols,
> > which I think of not a good solution.
>
> >> Has anyone got the stomach to try such a thing and see what happens?
> >> I don't have MSVC here.
> >
> > We have the environment to test your ideas.
> > Can you explain your ideas with more detail?
>
> Well, the main idea is just this:  *IF* it is sufficient to declare
> a variable PGDLLIMPORT only in the code that is importing it (the
> big IF because I don't know whether that is true, but something I
> saw in that long earlier thread seemed to suggest it) ...
>
> Then ... the chief problem that needs to be solved is only that
> MSVC won't allow you to redeclare something with PGDLLIMPORT if
> it is also declared without PGDLLIMPORT in a .h file that you
> include. In other words, you can't simply:
>
> #include 
> extern PGDLLIMPORT pg_tz session_timezone; /* the right way now */
>
> because it was already declared the wrong way in pgtime.h.
>
> So one idea is just this:
>
> #define session_timezone decoy_session_timezone;
> #include 
> #undef decoy_session_timezone;
>
> extern PGDLLIMPORT pg_tz session_timezone; /* the right way now */
>
> which is not a multiple declaration of the same thing, because
> what got declared the wrong way in pgtime.h is now some other thing
> named decoy_session_timezone.  You might need to supply a thing by
> that name, to avoid a linker complaint:
>
> pg_tz decoy_session_timezone; /* never used */
>
> IF the original premise is true, then this technique ought to be
> usable in most cases. It would, however, break in cases where the
> .h file declares macros or inline functions that refer to the
> symbol, because they would all end up referring to the decoy.
>
> My other idea, especially if there were several symbols needing
> to be treated this way, would be to do it all in one dedicated
> .c file, so any of the possible problems with #defining away parts
> of an .h file would be contained in one place, and that file could
> have a simple getter function:
>
> pg_tz getSessionTimezone() { return session_timezone; }
>
> which would be used in the rest of the code instead of referring
> to the global directly. (In that case, of course, the same getter
> function would have to be provided in the non-MSVC case too.)
> A setter function could also be made, if the code needs it.
>

I'll do these checks tomorrow.

Thank you very much, msvc is big unknown for me

regards

Pavel

Re: [HACKERS] New email address

[Tom Lane]

I wrote:
> "Rudolph T. Maceyko"  writes:
>> The basic changes since Yahoo implemented their p=reject DMARC policy
>> last year (and others followed) were:
>> * make NO CHANGES to the body of the message--no headers, footers, etc. 
>> * make NO CHANGES to the subject header of the message--no more
>> "[Highland Park]" 
>> * when mail comes to the list from a domain that uses a p=reject DMARC
>> policy, CHANGE THE FROM HEADER so that it comes from the list.

After further off-list discussion with Rudy, I'm not entirely convinced
by his reasoning for dropping Subject-munging and footer-addition; it
seems like that might be at least in part a limitation of his
mailman-based infrastructure.

The clearly critical thing, though, is that when forwarding a message from
a person at a DMARC-using domain, we would have to replace the From: line
with something @postgresql.org.  This is what gets it out from under the
original domain's DMARC policy.

The other stuff Rudy did, including adding the list's own DKIM-Signatures
and publishing DMARC and SPF policy for the list domain, is not
technically necessary (yet) but it makes the list traffic less likely to
get tagged as spam by antispam heuristics.  And, as he noted, there are
feedback loops that mean once some traffic gets tagged as spam it becomes
more likely that future traffic will be.

If Rudy's right that Gmail is likely to start using p=reject DMARC policy,
we are going to have to do something about this before that; we have too
many people on gmail.  I'm not exactly in love with replacing From:
headers but there may be little alternative.  We could do something like
From: Persons Real Name 
Reply-To: ...
so that at least the person's name would still be readable in MUA
displays.

We'd have to figure out whether we want the Reply-To: to be the original
author or the list; as I recall, neither of those are fully satisfactory.

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] WIP: About CMake v2

[YUriy Zhuravlev]

Hello hackers.

News about CMake:
I built postgres, initdb, createdb, psql, pg_ctl using CMake.
After make install you can run initdb after run postgres after createdb and 
use it by psql. Only for Linux now and realy bugy (and the code is very dirt) 
but it work!
If someone wants to test or to help:
https://github.com/stalkerg/postgres_cmake

Thanks.

PS All define for pg_config.h generate and testing truly
-- 
YUriy Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres 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] parallelism and sorting

[Amit Kapila]

On Tue, Nov 24, 2015 at 7:53 PM, Robert Haas  wrote:
>
> On Tue, Nov 24, 2015 at 7:59 AM, Amit Kapila 
wrote:
> > On Tue, Nov 24, 2015 at 8:59 AM, Robert Haas 
wrote:
> >> One idea about parallel sort is that perhaps if multiple workers feed
> >> data into the sort, they can each just sort what they have and then
> >> merge the results.
> >
> > Sounds like a good approach for parallel sorting, however small
extension
> > to it that could avoid merging the final results is that workers
allocated
> > for sort will perform range-based sorting. A simple example to sort
integers
> > from 1-100 will be, worker-1 will be responsible for sorting any integer
> > between 1-50 and worker-2 will be responsible for sorting integers from
> > 51-100 and then master backend just needs to ensure that it first
returns
> > the tuples from worker-1 and then from worker-2.  I think it has some
> > similarity to your idea-5 (use of repartition), but not exactly same.
>
> This is not so easy to accomplish for a couple of reasons.  First, how
> would you know where to partition the range?


I was thinking to form range map by referring histogram from stats.

>
>   That would work fine if
> you had all the data in sorted order to begin with, but of course if
> you had that you wouldn't be sorting it.  Second, remember that the
> data is probably arriving in separate streams in each worker - e.g.
> the sort may be being fed by a parallel sequential scan.


True, at this moment I am not sure what is the best way to reduce that
overhead, but may be some form of min tuple can be used for the same.

>
>   If you do
> what I'm proposing, those workers don't need to communicate with each
> other except for the final merge at the end; but to do what you're
> proposing, you'd need to move each tuple from the worker that got it
> originally to the correct worker.  I would guess that would be at
> least as expensive as the final merge pass you are hoping to avoid,
> and maybe significantly moreso.
>

I think we can evaluate pros and cons of each approach and then proceed
with one which is more promising.

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

Re: [HACKERS] New email address

[Tom Lane]

Magnus Hagander  writes:
> On Tue, Nov 24, 2015 at 4:00 PM, Tom Lane  wrote:
>> If anyone thinks we might be motivated to become DMARC compliant,
>> I can inquire for more details.  But I won't bother unless there's
>> real interest.

> I'd definitely be interested at least in what they're doing. Whether we'd
> actually implement it would depend on the implications of course, but if
> they've actually figured out how to do it, it could be useful.

Forwarded with Rudy's permission ...

regards, tom lane

--- Forwarded Message

Date:Tue, 24 Nov 2015 10:34:45 -0500
From:"Rudolph T. Maceyko" 
To:  Tom Lane 
Subject: Re: How did you fix HP list for DMARC compliance, exactly?

Hi Tom, 

The basic changes since Yahoo implemented their p=reject DMARC policy
last year (and others followed) were:
* make NO CHANGES to the body of the message--no headers, footers, etc. 

* make NO CHANGES to the subject header of the message--no more
"[Highland Park]" 

* when mail comes to the list from a domain that uses a p=reject DMARC
policy, CHANGE THE FROM HEADER so that it comes from the list.
Otherwise, when that message would be verified by any site that checks
DMARC, it would fail (and probably would not be delivered, or would be
considered spam). 

That last point was the big one, and is something that Mailman supports
(in recent versions). It provides the option either to wrap the
"offending" message in an attachment, or to do what we do now, which is
to change the From header (and add a Reply-To, so replies still work).
You could also elect to change *every* message that way, which seems
like overkill (at least it does today). 

All of that is necessary in order to avoid DMARC problems. Of course,
you CAN ban subscribers from these domains, but the list of p=reject
DMARC policy sites will only grow. I read that Google is considering
implementing it next year. 

Anyway, in addition to all of that, I've implemented DKIM
verification/signing and our own DMARC policy (but NOT p=reject), as
well as greylisting. These are just ways to elevate our anti-spam
profile (and reputation). I've also set up feedback loops for AOL and
Yahoo (and I'm trying for Comcast) so they don't ding us just because
one of their subscribers "accidentally" marks a message that came
through the list as spam. Running a mail server is hard these days... 

-Rudy 

On 2015-11-24 10:19, Tom Lane wrote: 

> I'm curious about what changes you made for this. And, of course: did
> it work?
> 
> I'm inquiring on behalf of an open-source project I'm involved in,
> who might be interested in fixing their lists similarly:
> http://www.postgresql.org/message-id/flat/cacjxuspcjafu81izz0vcmk78eteq4_ejgcjk402wwwxvezr...@mail.gmail.com
>  [1]
> 
> I don't believe they run the same list software you do, so exact
> instructions probably aren't useful, but a functional spec for what
> needs to happen would be very valuable.
> 
> Thanks!
> 
> regards, tom lane

--- End of Forwarded Message


-- 
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] Bug in numeric multiplication

[Tom Lane]

I wrote:
> Note that div[qi+1], and indeed all the remaining dividend digits, are
> large negative values.  This is what you'd expect if the carry propagation
> step hasn't run for awhile, which is a precondition for div[qi] being
> large enough to cause an issue.  When we compute 218943 * 1, we will
> indeed get an overflow, and the result will wrap around to some large
> negative value (2^32 less than it should be).  Then we will add that to
> div[qi+1], and we'll get *another* overflow, wrapping what nominally
> should have been a negative sum around to a positive value (2^32 more than
> it should be).  So the two overflows cancel and we get exactly the correct
> new value of div[qi+1].

> I do not know whether it's possible to devise a test case where you don't
> get offsetting overflows.  It may be that there's no operational bug here.
> Still, the code is surely not behaving per face value.

After further thought I've pretty well convinced myself that there is
indeed no observable bug, at least as long as you assume that overflow
within the multiplication will behave as stated above.  The proof goes
like this:

We already know that the divisor-subtraction step cannot cause an overflow
(modulo the question of div[qi] possibly exceeding the maxdiv limit,
which I'll get back to).  So what is at stake is just the possibility of
overflow in the final update that transfers leftover digits from div[qi]
to div[qi+1].

To analyze that, consider just the first two dividend and divisor terms in
the qdigit calculation, that is during each loop iteration approximate
qdigit as
qdigit ~= trunc((div[qi]*NBASE + div[qi+1]) /
(var2digits[0]*NBASE + var2digits[1]))
This holds whether or not we do a carry propagation step.  Now write
what the divisor-subtraction step updates div[qi] to as
div[qi]' = div[qi] - qdigit * var2digits[0]
and the updated div[qi+1] as
div[qi+1]' = div[qi+1] - qdigit * var2digits[1]
So, if we temporarily disregard the possibility of overflow within the
final calculation
div[qi+1]'' = div[qi+1]' + div[qi]'*NBASE
we can see that div[qi+1]'' is going to be
= div[qi+1]' + div[qi]'*NBASE
= div[qi+1] - qdigit * var2digits[1] +
  (div[qi] - qdigit * var2digits[0])*NBASE
= div[qi]*NBASE + div[qi+1] -
  qdigit * (var2digits[0]*NBASE + var2digits[1])
Comparing that to the approximate value of qdigit, we can see that
what we've got here is a modulo calculation, and the value of div[qi+1]''
is going to cancel to zero except for a remainder modulo
var2digits[0]*NBASE + var2digits[1], which of course must fall between 0
and NBASE*NBASE+NBASE (since the truncation is towards minus infinity).

Now of course this is only an approximation.  The main source of error is
that we've omitted the lower-order dividend terms.  Including div[qi+2]
could change the result by at most about INT_MAX/NBASE (which is the most
it could add to the numerator in the qdigit expression above, which will
propagate more or less linearly to div[qi+1]'').  Similarly, adding
div[qi+3] could add at most INT_MAX/NBASE/NBASE.  So we aren't anywhere
near the overflow threshold.  Including the lower-order divisor terms
could change the value of qdigit by a multiplicative factor of no more
than about 1/NBASE.  Lastly, roundoff error in the floating-point part
of the calculation could cause qdigit to be off by one count either
way.  None of these effects are going to let the final div[qi+1] value
get to more than two or three times NBASE squared, which is still
an order of magnitude less than INT_MAX.

Therefore, the final div[qi+1] value cannot overflow an int, and even
though it might be larger than what maxdiv would suggest, it's not going
to be large enough to cause overflow in the next loop iteration either.
It obviously won't cause overflow during carry propagation, and as for
the next divisor-subtraction step, we can do an analysis similar to the
above but approximating qdigit with just these terms:
qdigit ~= trunc((div[qi] + div[qi+1]/NBASE) / var2digits[0])
Plugging that into
div[qi]' = div[qi] - qdigit * var2digits[0]
shows that the updated div[qi] in any loop iteration is going to be just
about -div[qi+1]/NBASE, plus a truncation term that's between 0 and
var2digits[0], plus lower-order terms that aren't going to get you very
much past INT_MAX/NBASE.  So div[qi]' is never an overflow hazard in any
loop iteration.

In short, it's impossible for the end result of the div[qi+1] update
calculation to overflow, or even to get large enough to create a problem
in the next loop iteration.  However, the intermediate result
div[qi]*NBASE can overflow as per the example I showed before.

We could just ignore this on the grounds that it doesn't matter given sane
behavior of integer arithmetic.  Or, if we wanted to be more paranoid, we
could do the multiplication-and-addition in int64 arithmetic; but that
would probably slow things 

Re: [HACKERS] New email address

[Kevin Grittner]

On Tue, Nov 24, 2015 at 11:10 AM, Tom Lane  wrote:

> Forwarded with Rudy's permission ...
> From:"Rudolph T. Maceyko" 

> * when mail comes to the list from a domain that uses a p=reject DMARC
> policy, CHANGE THE FROM HEADER so that it comes from the list.
> Otherwise, when that message would be verified by any site that checks
> DMARC, it would fail (and probably would not be delivered, or would be
> considered spam).

> change the From header (and add a Reply-To, so replies still work).

If this were done, would the other steps (not changing the subject
or body of the email) be necessary?

-- 
Kevin Grittner
EDB: 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] New email address

[Tom Lane]

Kevin Grittner  writes:
> On Tue, Nov 24, 2015 at 11:10 AM, Tom Lane  wrote:
>>> change the From header (and add a Reply-To, so replies still work).

> If this were done, would the other steps (not changing the subject
> or body of the email) be necessary?

See my followup: I think it's probably true that we could skip those
changes.  But Rudy commented that there's a lot of underdocumented
subtlety here.  There might be reasons I'm missing why we'd need to
stop doing those things.  It doesn't seem like DMARC as such would
force that, but perhaps those things trigger some popular antispam
heuristics.

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] New email address

[Larry Rosenman]

On 2015-11-24 13:11, Tom Lane wrote:

Kevin Grittner  writes:

On Tue, Nov 24, 2015 at 11:10 AM, Tom Lane  wrote:

change the From header (and add a Reply-To, so replies still work).



If this were done, would the other steps (not changing the subject
or body of the email) be necessary?


See my followup: I think it's probably true that we could skip those
changes.  But Rudy commented that there's a lot of underdocumented
subtlety here.  There might be reasons I'm missing why we'd need to
stop doing those things.  It doesn't seem like DMARC as such would
force that, but perhaps those things trigger some popular antispam
heuristics.

regards, tom lane
Any Header or Body changes will invalidate most, if not all, DKIM 
signatures.  Since DKIM is used as part

of DMARC, it's a problem.

Not sure what MajorDomo2 will allow you to do.


--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 214-642-9640 E-Mail: l...@lerctr.org
US Mail: 7011 W Parmer Ln, Apt 1115, Austin, TX 78729-6961


--
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] Revisiting pg_stat_statements and IN() (Was: Re: pg_stat_statements fingerprinting logic and ArrayExpr)

[Tom Lane]

Peter Geoghegan  writes:
> On Tue, Dec 10, 2013 at 1:30 AM, Peter Geoghegan  wrote:
>> pg_stat_statements' fingerprinting logic considers the following two
>> statements as distinct:
>> 
>> select 1 in (1, 2, 3);
>> select 1 in (1, 2, 3, 4);
>> 
>> This is because the ArrayExpr jumble case jumbles any ArrayExpr's list
>> of elements recursively. In this case it's a list of Const nodes, and
>> the fingerprinting logic jumbles those nodes indifferently.

I think this is a vastly oversimplified explanation of the problem.
In particular, because the planner will flatten an ArrayExpr containing
only Const nodes to an array constant (see eval_const_expressions),
I don't believe the case ever arises in exactly the form you posit here.

A portion of the problem is possibly due to the heuristics in
parse_expr.c's transformAExprIn():

 * We try to generate a ScalarArrayOpExpr from IN/NOT IN, but this is only
 * possible if there is a suitable array type available.  If not, we fall
 * back to a boolean condition tree with multiple copies of the lefthand
 * expression.  Also, any IN-list items that contain Vars are handled as
 * separate boolean conditions, because that gives the planner more scope
 * for optimization on such clauses.

If the original text actually involves a variable number of Vars, then you
will end up with a boolean expression with a varying number of OR arms,
even if the Vars later get flattened to constants.  However, it's not
clear to me that anyone would expect such cases to be treated as
identical.  Another possibility is a type clash, for example
"x IN (42, 44.1)" will end up as a boolean tree for lack of a common
type for the would-be array elements.  That case might possibly be an
issue in practice.

But what seems more likely to be annoying people is cases in which the
original text contains a varying number of Param markers.  Those might or
might not get folded to constants during planning depending on context,
so that they might or might not look different to pg_stat_statements.

So I suspect the real problem here is that we might want all of these
things to look identical to pg_stat_statements:

   ARRAY[$1, $2, 42]
   ARRAY[$1, $2, $3, 47]
   '{1,2,3,47}'::int[]

Don't see a very clean way to do that ...

regards, tom lane


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

Re: [HACKERS] New email address

[José Luis Tallón]

On 11/24/2015 07:55 PM, Tom Lane wrote:

[snip]
The clearly critical thing, though, is that when forwarding a message from
a person at a DMARC-using domain, we would have to replace the From: line
with something @postgresql.org.  This is what gets it out from under the
original domain's DMARC policy.


One possibility that comes to mind:

- Remove the sender's DMARC headers+signature **after thoroughly 
checking it** (to minimize the amount of UBE/UCE/junk going in)
- Replace the sender's (i.e. 'From:' header) with 
list-sender+munched-em...@postgresql.org (VERP-ified address)


- Add the required headers, footers, change the subject line, etc

- DKIM-sign the resulting message with postgresql.org's keys before 
sending it

[snip]

If Rudy's right that Gmail is likely to start using p=reject DMARC policy,
we are going to have to do something about this before that; we have too
many people on gmail.  I'm not exactly in love with replacing From:
headers but there may be little alternative.  We could do something like
From: Persons Real Name 
Reply-To: ...
so that at least the person's name would still be readable in MUA
displays.

Yup


We'd have to figure out whether we want the Reply-To: to be the original
author or the list; as I recall, neither of those are fully satisfactory.
Or just strip it, though that trump the sender's explicit preference 
(expressed by setting the header)



I might be able to help a bit with implementation if needed.


/ J.L.



--
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] Re: In-core regression tests for replication, cascading, archiving, PITR, etc.

[Michael Paquier]

On Wed, Nov 25, 2015 at 6:22 AM, Alvaro Herrera 
wrote:

> Michael Paquier wrote:
>
> > - Manage node information using package/class PostgresNode.pm and have
> > RecoveryTest use it. I have actually made PostgresNode bare-bone and
> simple
> > on purpose: one can initialize the node, append configuration parameters
> to
> > it and manage it through start/stop/restart (we may want to add reload
> and
> > promote actually if needed).
>
> This looks great as a starting point.  I think we should make TestLib
> depend on PostgresNode instead of the other way around.  I will have a
> look at that (I realize this means messing with the existing tests).
>

Makes sense. My thoughts following that is that we should keep a track of
the nodes started as an array which is part of TestLib, with PGHOST set
once at startup using tempdir_short. That's surely an refactoring patch
somewhat independent of the recovery test suite. I would not mind writing
something among those lines if needed.


> > I have also arrived at the conclusion that it is not really worth
> > adding a node status flag in PostgresNode because the port number
> > saved there is sufficient when doing free port lookup, and the list of
> > nodes used in a recovery test are saved in an array.
>
> I don't disagree with this in principle, but I think the design that you
> get a new PostgresNode object by calling get_free_port is strange.  I
> think the port lookup code should be part of either TestLib or
> PostgresNode, not RecoveryTest.
>

I'd vote for TestLib. I have written PostgresNode this way to allow users
to set up arbitrary port numbers if they'd like to do so. That's more
flexible.
-- 
Michael

Re: [HACKERS] [PROPOSAL] VACUUM Progress Checker.

[Jim Nasby]

On 11/24/15 2:02 AM, Amit Langote wrote:

It just occurred to me that we could do the instrumentation in
>lazy_tid_reaped(). It might seem bad to do in increment for every tuple in
>an index, but we're already doing a bsearch over the dead tuple list.
>Presumably that's going to be a lot more expensive than an increment
>operation.

Just to clarify, does this mean we report index vacuum progress in terms
of index items processed (not pages)? If so, how do we get total number of
index items to process (presumably across all indexes) for a given phase 2
round? As a context, we'd report phase 1 progress in terms of heap pages
processed of total heap pages.


You'd get it from pg_class.reltuples for each index. Since all index 
vacuuming is done strictly on a per-index-tuple basis, that's probably 
the most accurate way to do it anyway.


Also, while it might be interesting to look at the total number of index 
tuples, I think it's probably best to always report on a per-index 
basis, as well as which index is being processed. I suspect there could 
be a very large variance of tuple processing speed for different index 
types. Eventually it might be worth it to allow index AMs to provide 
their own vacuuming feedback, but I think that's way out of scope for 
this patch. :)

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] Using quicksort for every external sort run

[Peter Geoghegan]

On Tue, Nov 24, 2015 at 3:32 PM, Simon Riggs  wrote:
> My feeling is that numbers rarely speak for themselves, without LSD. (Which
> numbers?)

Guffaw.

> How are we doing here? Keen to see this work get committed, so we can move
> onto parallel sort. What's the summary?

I showed a test case where a CREATE INDEX sort involving 5 runs and a
merge only took about 18% longer than an equivalent fully internal
sort [1] using over 5 times the memory. That's about 2.5X faster than
the 9.5 performance on the same system with the same amount of memory.

Overall, the best cases I saw were the original "quicksort with
spillover" cases [2]. They were just under 4X faster. I care about
that less, though, because that will happen way less often, and won't
help with larger sorts that are even more CPU bound.

There is a theoretical possibility that this is slower on systems
where multiple merge passes are required as a consequence of not
having runs as long as possible (due to not using replacement
selection heap). That will happen very infrequently [3], and is very
probably still worth it.

So, the bottom line is: This patch seems very good, is unlikely to
have any notable downside (no case has been shown to be regressed),
but has yet to receive code review. I am working on a new version with
the first two commits consolidated, and better comments, but that will
have the same code, unless I find bugs or am dissatisfied. It mostly
needs thorough code review, and to a lesser extent some more
performance testing.

Parallel sort is very important. Robert, Amit and I had a call about
this earlier today. We're all in agreement that this should be
extended in that direction, and have a rough idea about how it ought
to fit together with the parallelism primitives. Parallel sort in 9.6
could certainly happen -- that's what I'm aiming for. I haven't really
done preliminary research yet; I'll know more in a little while.

> How about we commit it with a sort_algorithm = 'foo' parameter so we can
> compare things before release of 9.6?

I had a debug GUC (like the existing one to disable top-N heapsorts)
that disabled "quicksort with spillover". That's almost the opposite
of what you're asking for, though, because that makes us never use a
heap. You're asking for me to write a GUC to always use a heap.

That's not a good way of testing this patch, because it's inconvenient
to consider the need to use a heap beyond the first run (something
that now exists solely for the benefit of "quicksort with spillover";
a heap will often never be used even for the first run). Besides, the
merge optimization is a big though independent part of this, and
doesn't make sense to control with the same GUC.

If I haven't gotten this right, we should not commit the patch. If the
patch isn't superior to the existing approach in virtually every way,
then there is no point in making it possible for end-users to disable
with messy GUCs -- it should be reverted.

[1] Message: 
http://www.postgresql.org/message-id/cam3swzrihaf7jdf923zz2qhdjierqp5uu_+jpumvumed0z9...@mail.gmail.com
 Attachment:
http://www.postgresql.org/message-id/attachment/39660/quicksort_external_test.txt

[2] 
http://www.postgresql.org/message-id/CAM3SWZTzLT5Y=VY320NznAyz2z_em3us6x=7rxmeuma9z9y...@mail.gmail.com

[3] 
http://www.postgresql.org/message-id/CAM3SWZTX5=nhxppogpirqsh4cr+bpqs6r7ktax0hmqinlf-...@mail.gmail.com
-- 
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] parallelism and sorting

[Jim Nasby]

On 11/23/15 5:47 PM, Robert Haas wrote:

2. In Parallel Seq Scan, the determination of what page to scan next
isn't dependent on the contents of any page previously scanned.  In
Parallel Index Scan, it is.  Therefore, the amount of effective
parallelism is likely to be less.  This doesn't mean that trying to
parallelize things here is worthless: one backend can be fetching the
next index page while some other backend is processing the tuples from
a page previously read.


Presumably we could simulate that today by asking the kernel for the 
next page in advance, like we do for seqscans when 
effective_io_concurrency > 1. My guess is a parallel worker won't help 
there.


Where a parallel worker might provide a lot of benefit is separating 
index scanning from heap scanning (to check visibility or satisfy a 
filter). It wouldn't surprise me if a single worker reading an index 
could keep a number of children busy retrieving heap tuples and 
processing them. It might be nice if an index scan node just fired up 
it's own workers and talked to them directly.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] Using quicksort for every external sort run

[Simon Riggs]

On 25 November 2015 at 00:33, Peter Geoghegan  wrote:


> Parallel sort is very important. Robert, Amit and I had a call about
> this earlier today. We're all in agreement that this should be
> extended in that direction, and have a rough idea about how it ought
> to fit together with the parallelism primitives. Parallel sort in 9.6
> could certainly happen -- that's what I'm aiming for. I haven't really
> done preliminary research yet; I'll know more in a little while.


Glad to hear it, I was hoping to see that.


> > How about we commit it with a sort_algorithm = 'foo' parameter so we can
> > compare things before release of 9.6?
>
> I had a debug GUC (like the existing one to disable top-N heapsorts)
> that disabled "quicksort with spillover". That's almost the opposite
> of what you're asking for, though, because that makes us never use a
> heap. You're asking for me to write a GUC to always use a heap.
>

I'm asking for a parameter to confirm results from various algorithms, so
we can get many eyeballs to confirm your work across its breadth. This is
similar to the original trace_sort parameter which we used to confirm
earlier sort improvements. I trust it will show this is good and can be
removed prior to release of 9.6.

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

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

Re: [HACKERS] [PROPOSAL] VACUUM Progress Checker.

[Amit Langote]

On 2015/11/25 9:32, Jim Nasby wrote:
> On 11/24/15 2:02 AM, Amit Langote wrote:
>> Just to clarify, does this mean we report index vacuum progress in terms
>> of index items processed (not pages)? If so, how do we get total number of
>> index items to process (presumably across all indexes) for a given phase 2
>> round? As a context, we'd report phase 1 progress in terms of heap pages
>> processed of total heap pages.
> 
> You'd get it from pg_class.reltuples for each index. Since all index
> vacuuming is done strictly on a per-index-tuple basis, that's probably the
> most accurate way to do it anyway.

Important to remember though that the reltuples would be latest as of the
last VACUUM/ANALYZE.

> Also, while it might be interesting to look at the total number of index
> tuples, I think it's probably best to always report on a per-index basis,
> as well as which index is being processed. I suspect there could be a very
> large variance of tuple processing speed for different index types.
> Eventually it might be worth it to allow index AMs to provide their own
> vacuuming feedback, but I think that's way out of scope for this patch. :)

Agreed.

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] parallelism and sorting

[Jim Nasby]

On 11/24/15 7:10 AM, Ants Aasma wrote:

The use case I have in mind is a table containing multiple years worth
of (approximately) time series data, where overwhelming majority of
queries are explicitly interested in recent data. Having a partial
index with WHERE tstamp > $some_recent_tstamp cutting out 90+% of
tuples was extremely helpful for performance for both index size
reasons and having to process less tuples. This index needs to be
periodically rebuilt with a newer timestamp constant, and the rebuild
would be a lot faster if it could use the existing index to perform an
index only scan of 10% of data instead of scanning and sorting the
full table.


There are other cases where you'd want to build an index off an existing 
index as well. It's not that uncommon to have small, specialized indexes 
that are fully or partially a subset of another index.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] Revisiting pg_stat_statements and IN() (Was: Re: pg_stat_statements fingerprinting logic and ArrayExpr)

[Jim Nasby]

On 11/24/15 1:29 PM, Tom Lane wrote:

So I suspect the real problem here is that we might want all of these
things to look identical to pg_stat_statements:

ARRAY[$1, $2, 42]
ARRAY[$1, $2, $3, 47]
'{1,2,3,47}'::int[]

Don't see a very clean way to do that ...


Another not-uncommon case is IN ( '1', '2', ... , '2342' ); in other 
words, treating an integer as text. A lot of frameworks like to do that 
and just push the problem onto the database. I'm not sure what 
pg_stat_statements would ultimately see in that case..


Since there's a few different things people might want, maybe a good 
first step is to allow extending/changing the jumbling decision at the C 
level. That would make it easy for a knowledgeable enough person to come 
up with an alternative as a plugin that regular users could use.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] WIP: About CMake v2

[Michael Paquier]

On Wed, Nov 25, 2015 at 1:35 AM, YUriy Zhuravlev  wrote:

> News about CMake:
> I built postgres, initdb, createdb, psql, pg_ctl using CMake.
> After make install you can run initdb after run postgres after createdb and
> use it by psql. Only for Linux now and realy bugy (and the code is very
> dirt)
> but it work!
> If someone wants to test or to help:
> https://github.com/stalkerg/postgres_cmake
>
> PS All define for pg_config.h generate and testing truly
>

Thanks for sharing the progress! That's really something that would pay
long-term.
-- 
Michael

Re: [HACKERS] Revisiting pg_stat_statements and IN() (Was: Re: pg_stat_statements fingerprinting logic and ArrayExpr)

[Peter Geoghegan]

On Tue, Nov 24, 2015 at 5:39 PM, Jim Nasby  wrote:
> Another not-uncommon case is IN ( '1', '2', ... , '2342' ); in other words,
> treating an integer as text. A lot of frameworks like to do that and just
> push the problem onto the database. I'm not sure what pg_stat_statements
> would ultimately see in that case..

They do?

postgres=# select 5::int4 in ('5');
 ?column?
──
 t
(1 row)

postgres=# select 5::int4 in ('5a');
ERROR:  22P02: invalid input syntax for integer: "5a"
LINE 1: select 5::int4 in ('5a');
   ^
-- 
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] [PROPOSAL] VACUUM Progress Checker.

[Jim Nasby]

On 11/24/15 7:02 PM, Amit Langote wrote:

You'd get it from pg_class.reltuples for each index. Since all index
>vacuuming is done strictly on a per-index-tuple basis, that's probably the
>most accurate way to do it anyway.

Important to remember though that the reltuples would be latest as of the
last VACUUM/ANALYZE.


True, but in cases where you care about monitoring a vacuum I suspect 
it'll be close enough.


Might be worth a little extra effort to handle the 0 case though. If you 
really wanted to get fancy you could see how the current heap 
tuples/page count compares to reltuples/relpages from pg_class for the 
heap... but I suspect that's pretty serious overkill.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] Using quicksort for every external sort run

[Peter Geoghegan]

On Tue, Nov 24, 2015 at 4:46 PM, Simon Riggs  wrote:
>> I had a debug GUC (like the existing one to disable top-N heapsorts)
>> that disabled "quicksort with spillover". That's almost the opposite
>> of what you're asking for, though, because that makes us never use a
>> heap. You're asking for me to write a GUC to always use a heap.
>
>
> I'm asking for a parameter to confirm results from various algorithms, so we
> can get many eyeballs to confirm your work across its breadth. This is
> similar to the original trace_sort parameter which we used to confirm
> earlier sort improvements. I trust it will show this is good and can be
> removed prior to release of 9.6.

 My patch updates trace_sort messages. trace_sort doesn't change the
behavior of anything. The only time we've ever done anything like this
was for Top-N heap sorts.

This is significantly more inconvenient than you think. See the
comments in the new dumpbatch() function.

-- 
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] Re: In-core regression tests for replication, cascading, archiving, PITR, etc.

[Alvaro Herrera]

Michael Paquier wrote:
> On Wed, Nov 25, 2015 at 6:22 AM, Alvaro Herrera 
> wrote:
> 
> > Michael Paquier wrote:

> > This looks great as a starting point.  I think we should make TestLib
> > depend on PostgresNode instead of the other way around.  I will have a
> > look at that (I realize this means messing with the existing tests).
> 
> Makes sense. My thoughts following that is that we should keep a track of
> the nodes started as an array which is part of TestLib, with PGHOST set
> once at startup using tempdir_short. That's surely an refactoring patch
> somewhat independent of the recovery test suite. I would not mind writing
> something among those lines if needed.

OK, please do.

We can split this up in two patches: one introducing PostgresNode
(+ RecursiveCopy) together with the refactoring of existing test code,
and a subsequent one introducing RecoveryTest and the corresponding
subdir.  Sounds good?

> > > I have also arrived at the conclusion that it is not really worth
> > > adding a node status flag in PostgresNode because the port number
> > > saved there is sufficient when doing free port lookup, and the list of
> > > nodes used in a recovery test are saved in an array.
> >
> > I don't disagree with this in principle, but I think the design that you
> > get a new PostgresNode object by calling get_free_port is strange.  I
> > think the port lookup code should be part of either TestLib or
> > PostgresNode, not RecoveryTest.
> 
> I'd vote for TestLib. I have written PostgresNode this way to allow users
> to set up arbitrary port numbers if they'd like to do so. That's more
> flexible.

That works for me.

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


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

Re: [HACKERS] Re: In-core regression tests for replication, cascading, archiving, PITR, etc.

[Michael Paquier]

On Wed, Nov 25, 2015 at 10:55 AM, Alvaro Herrera 
wrote:

> Michael Paquier wrote:
> > On Wed, Nov 25, 2015 at 6:22 AM, Alvaro Herrera <
> alvhe...@2ndquadrant.com>
> > wrote:
> >
> > > Michael Paquier wrote:
>
> > > This looks great as a starting point.  I think we should make TestLib
> > > depend on PostgresNode instead of the other way around.  I will have a
> > > look at that (I realize this means messing with the existing tests).
> >
> > Makes sense. My thoughts following that is that we should keep a track of
> > the nodes started as an array which is part of TestLib, with PGHOST set
> > once at startup using tempdir_short. That's surely an refactoring patch
> > somewhat independent of the recovery test suite. I would not mind writing
> > something among those lines if needed.
>
> OK, please do.
>
> We can split this up in two patches: one introducing PostgresNode
> (+ RecursiveCopy) together with the refactoring of existing test code,
> and a subsequent one introducing RecoveryTest and the corresponding
> subdir.  Sounds good?
>

Yeah, that matches my line of thoughts. Will do so.
-- 
Michael

Re: [HACKERS] New email address

[Tom Lane]

Greg Stark  writes:
> It'll still mess up everyone's contact book which will fill up with
> these fake email addresses. And the Reply-To will mean private
> responses will go to the list.

Yeah, it's not pretty.  But I'm not sure we're gonna have much choice
if Gmail changes their policy.

> Fwiw I'm all for dropping the footer and the [HACKERS] which are both
> ill-advised imho. But modifying the From: header seems really broken.

IMO the footer is a *very* good idea; when we started using the current
form of that, it greatly reduced the amount of "how do I unsubscribe"
noise.  But having said that, it probably wouldn't need to be on every
message to be effective.  I personally like the subject-munging but
could live without it.

[ thinks for a bit... ]  I wonder whether we could do something like this:

* Leave the From: and Reply-To: alone.

* Add the footer only if the message isn't DKIM-signed.

* Give up Subject-munging.  (Munging only non-signed messages would be
way too confusing.)

I think that would put us in a situation where DKIM signatures would still
pass, at least unless the source insisted on signing Sender: too.  We
might still have some issues with SPF checks, but not breaking DKIM would
be a step forward.

If things change to the point where only a small minority of messages get
the footers because most people are using DKIM, then we might have to
reconsider that part.  But that seems far away yet.

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] parallelism and sorting

[Robert Haas]

On Tue, Nov 24, 2015 at 7:44 PM, Jim Nasby  wrote:
> On 11/23/15 5:47 PM, Robert Haas wrote:
>> 2. In Parallel Seq Scan, the determination of what page to scan next
>> isn't dependent on the contents of any page previously scanned.  In
>> Parallel Index Scan, it is.  Therefore, the amount of effective
>> parallelism is likely to be less.  This doesn't mean that trying to
>> parallelize things here is worthless: one backend can be fetching the
>> next index page while some other backend is processing the tuples from
>> a page previously read.
>
> Presumably we could simulate that today by asking the kernel for the next
> page in advance, like we do for seqscans when effective_io_concurrency > 1.

We don't do any such thing.  We prefetch for bitmap heap scans, not seq scans.

> My guess is a parallel worker won't help there.
> Where a parallel worker might provide a lot of benefit is separating index
> scanning from heap scanning (to check visibility or satisfy a filter). It
> wouldn't surprise me if a single worker reading an index could keep a number
> of children busy retrieving heap tuples and processing them.

Fortunately, the design I'm describing permits that exact thing.

> It might be
> nice if an index scan node just fired up it's own workers and talked to them
> directly.

That would be a bad idea, I'm pretty sure.  Passing tuples between
workers is expensive and needs to be minimized.  I am quite confident
that the right model for making parallelism better is to push as much
stuff beneath the Gather node as possible - that is, each worker
should have as many different things as possible that it can do
without incurring communication overhead.  Single purpose workers that
only assist with one part of the computation and then relay data to
some other process are exactly what we want to avoid.

-- 
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] Minor comment edits in nodeGather.c

[Robert Haas]

On Tue, Nov 24, 2015 at 1:06 AM, Amit Langote
 wrote:
> While going through nodeGather.c, I noticed portions of the file header
> comment that may have been obsoleted by recent revisions of the relevant
> parellelism code. For example, there is a reference to PartialSeqScan node
> which did not make it into the tree. Attached fixes it. Also, wondering if
> the semantics of Gather node is that of Scan or more generic Plan? That is
> to ask whether the following edit makes sense:
>
>   * nodeGather.c
> - *   Support routines for scanning a plan via multiple workers.
> + *   Support routines for getting the result from a plan via multiple
> + *   workers.
>   *

Well I think "scanning a plan" is clear enough even if it's
technically a Scan.  But I agree the second change is needed.

-- 
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] Using quicksort for every external sort run

[Peter Geoghegan]

On Tue, Nov 24, 2015 at 6:31 PM, Peter Geoghegan  wrote:
> (Note that the time taken to copy tuples comprising the final run is
> not displayed or accounted for)

I mean, comprising the second last run, the run shown, run 40.


-- 
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] pg_receivexlog: spurious error message connecting to 9.3

[Robert Haas]

On Tue, Nov 24, 2015 at 8:32 AM, Fujii Masao  wrote:
> On Tue, Nov 24, 2015 at 7:00 PM, Marco Nenciarini
>  wrote:
>> Hi Robert,
>>
>> On 17/11/15 20:10, Robert Haas wrote:
>>> On Tue, Nov 10, 2015 at 1:35 AM, Craig Ringer  wrote:
 On 10 November 2015 at 01:47, Marco Nenciarini
  wrote:

> I've attached a little patch that removes the errors when connected to 
> 9.3.

 Looks good to me. No point confusing users.

 The other callers of RunIdentifySystem are pg_basebackup and
 pg_receivelogical.

 pg_basebackup doesn't ask for the db_name (passes null).

 pg_receivelogical handles it being null already (and if it didn't,
 it'd die with or without this patch).

 pg_receivexlog expects it to be null and fails gracefully if it isn't.

 So this change just removes some pointless noise.
>>>
>>> The fprintf(stderr, ...) does not cause a non-local exit, so the
>>> "else" just after it should be deleted.  Otherwise, when that branch
>>> is taken, *db_name doesn't get initialized at all.
>>>
>>> Actually, I'd suggest doing it like the attached instead, which seems
>>> a bit tighter.
>>>
>>
>> I agree, your patch is better.
>
> +else if (PQserverVersion(conn) >= 90400)
>  fprintf(stderr,
>  _("%s: could not identify system: got %d rows and
> %d fields, expected %d rows and %d or more fields\n"),
>  progname, PQntuples(res), PQnfields(res), 1, 4);
>  }
>
> In the above case, PQclear(res) should be called and FALSE should be returned?

Hmm, yeah, it looks like that would be more consistent with what the
other parts of this function do.

-- 
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] Minor comment edits in nodeGather.c

[Robert Haas]

On Tue, Nov 24, 2015 at 9:31 PM, Robert Haas  wrote:
> On Tue, Nov 24, 2015 at 1:06 AM, Amit Langote
>  wrote:
>> While going through nodeGather.c, I noticed portions of the file header
>> comment that may have been obsoleted by recent revisions of the relevant
>> parellelism code. For example, there is a reference to PartialSeqScan node
>> which did not make it into the tree. Attached fixes it. Also, wondering if
>> the semantics of Gather node is that of Scan or more generic Plan? That is
>> to ask whether the following edit makes sense:
>>
>>   * nodeGather.c
>> - *   Support routines for scanning a plan via multiple workers.
>> + *   Support routines for getting the result from a plan via multiple
>> + *   workers.
>>   *
>
> Well I think "scanning a plan" is clear enough even if it's
> technically a Scan.  But I agree the second change is needed.

Err, even if it's NOT technically a scan.

-- 
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] Revisiting pg_stat_statements and IN() (Was: Re: pg_stat_statements fingerprinting logic and ArrayExpr)

[Tom Lane]

I wrote:
> Peter Geoghegan  writes:
>> This is because the ArrayExpr jumble case jumbles any ArrayExpr's list
>> of elements recursively. In this case it's a list of Const nodes, and
>> the fingerprinting logic jumbles those nodes indifferently.

> I think this is a vastly oversimplified explanation of the problem.
> In particular, because the planner will flatten an ArrayExpr containing
> only Const nodes to an array constant (see eval_const_expressions),
> I don't believe the case ever arises in exactly the form you posit here.

Um ... disregard that.  For some reason I was thinking that
pg_stat_statements looks at plan trees, but of course what it looks at
is the query tree immediately post-parse-analysis.  So the behavior of
the const-folding pass is not relevant.

The heuristics in transformAExprIn() are still relevant, though, and
I suspect that the question of whether Params should be considered
the same as Consts is also highly relevant.

I wonder whether we could improve this by arranging things so that both
Consts and Params contribute zero to the jumble hash, and a list of these
things also contributes zero, regardless of the length of the list.

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] Using quicksort for every external sort run

[Peter Geoghegan]

On Tue, Nov 24, 2015 at 5:42 PM, Greg Stark  wrote:
> Actually I kind of agree. What I would like to see is a series of
> numbers for increasing sizes of sorts plotted against the same series
> for the existing algorithm. Specifically with the sort size varying to
> significantly more than the physical memory on the machine. For
> example on a 16GB machine sorting data ranging from 1GB to 128GB.

There already was a test case involving a 1TB/16 billion tuple sort
[1] (well, a 1TB gensort Postgres table [2]). Granted, I don't have a
large number of similar test cases across a variety of scales, but
there are only so many hours in the day. Disappointingly, the results
at that scale were merely good, not great, but there was probably
various flaws in how representative the hardware used was.

> There's a lot more information in a series of numbers than individual
> numbers. We'll be able to see whether all our pontificating about the
> rates of growth of costs of different algorithms or which costs
> dominate at which scales are actually borne out in reality.

You yourself said that 1GB is sufficient to get a single-pass merge
phase for a sort of about 4TB - 8TB, so I think the discussion of the
growth in costs tells us plenty about what can happen at the high end.
My approach might help less overall, but it certainly won't falter.

See the 1TB test case -- output from trace_sort is all there.

> And see
> where the break points are where I/O overtakes memory costs. And it'll
> be clearer where to look for problematic cases where the new algorithm
> might not dominate the old one.

I/O doesn't really overtake memory cost -- if it does, then it should
be worthwhile to throw more sequential I/O bandwidth at the problem,
which is a realistic, economical solution with a mature implementation
(unlike buying more memory bandwidth). I didn't do that with the 1TB
test case.

If you assume, as cost_sort() does, that it takes N log2(N)
comparisons to sort some tuples, then it breaks down like this:

10 items require 33 comparisons, ratio 3.32192809489
100 items require 664 comparisons, ratio 6.64385618977
1,000 items require 9,965 comparisons, ratio 9.96578428466
1,000,000 items require 19,931,568 comparisons, ratio 19.9315685693
1,000,000,000 items require 29,897,352,853 comparisons, ratio 29.897352854
16,000,000,000 items require 542,357,645,663 comparisons, ratio 33.897352854

The cost of writing out and reading runs should be more or less in
linear proportion to their size, which is a totally different story.
That's the main reason why "quicksort with spillover" is aimed at
relatively small sorts, which we expect more of overall.

I think the big issue is that a non-parallel sort is significantly
under-powered when you go to sort 16 billion tuples. It's probably not
very sensible to do so if you have a choice of parallelizing the sort.
There is no plausible way to do replacement selection in parallel,
since you cannot know ahead of time with any accuracy where to
partition workers, as runs can end up arbitrarily larger than memory
with presorted inputs. That might be the single best argument for what
I propose to do here.

This is what Corey's case showed for the final run with 30GB
maintenance_work_mem:

LOG:  starting quicksort of run 40: CPU 1815.99s/19339.80u sec elapsed
24910.38 sec
LOG:  finished quicksorting run 40: CPU 1820.09s/19565.94u sec elapsed
25140.69 sec
LOG:  finished writing run 40 to tape 39: CPU 1833.76s/19642.11u sec
elapsed 25234.44 sec

(Note that the time taken to copy tuples comprising the final run is
not displayed or accounted for)

This is the second last run, run 40, so it uses the full 30GB of
maintenance_work_mem. We spend 00:01:33.75 writing the run. However,
we spent 00:03:50.31 just sorting the run. That's roughly the same
ratio that I see on my laptop with far smaller runs. I think the
difference isn't wider because the server is quite I/O bound -- but we
could fix that by adding more disks.

[1] 
http://www.postgresql.org/message-id/CAM3SWZQtdd=q+ef1xszayg1cioyqj7szfcl08gyqchpjtgn...@mail.gmail.com
[2] https://github.com/petergeoghegan/gensort
-- 
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] Using quicksort for every external sort run

[Greg Stark]

On Wed, Nov 25, 2015 at 12:33 AM, Peter Geoghegan  wrote:
> On Tue, Nov 24, 2015 at 3:32 PM, Simon Riggs  wrote:
>> My feeling is that numbers rarely speak for themselves, without LSD. (Which
>> numbers?)
>
> Guffaw.

Actually I kind of agree. What I would like to see is a series of
numbers for increasing sizes of sorts plotted against the same series
for the existing algorithm. Specifically with the sort size varying to
significantly more than the physical memory on the machine. For
example on a 16GB machine sorting data ranging from 1GB to 128GB.

There's a lot more information in a series of numbers than individual
numbers. We'll be able to see whether all our pontificating about the
rates of growth of costs of different algorithms or which costs
dominate at which scales are actually borne out in reality. And see
where the break points are where I/O overtakes memory costs. And it'll
be clearer where to look for problematic cases where the new algorithm
might not dominate the old one.

-- 
greg


-- 
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] New email address

[Greg Stark]

On Tue, Nov 24, 2015 at 10:03 PM, José Luis Tallón
 wrote:
>> From: Persons Real Name 
>> Reply-To: ...
>> so that at least the person's name would still be readable in MUA
>> displays.
>
> Yup

It'll still mess up everyone's contact book which will fill up with
these fake email addresses. And the Reply-To will mean private
responses will go to the list.

Fwiw I'm all for dropping the footer and the [HACKERS] which are both
ill-advised imho. But modifying the From: header seems really broken.


-- 
greg


-- 
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] Minor comment edits in nodeGather.c

[Amit Langote]

On 2015/11/25 11:31, Robert Haas wrote:
> On Tue, Nov 24, 2015 at 1:06 AM, Amit Langote
>  wrote:
>> While going through nodeGather.c, I noticed portions of the file header
>> comment that may have been obsoleted by recent revisions of the relevant
>> parellelism code. For example, there is a reference to PartialSeqScan node
>> which did not make it into the tree. Attached fixes it. Also, wondering if
>> the semantics of Gather node is that of Scan or more generic Plan? That is
>> to ask whether the following edit makes sense:
>>
>>   * nodeGather.c
>> - *   Support routines for scanning a plan via multiple workers.
>> + *   Support routines for getting the result from a plan via multiple
>> + *   workers.
>>   *
> 
> Well I think "scanning a plan" is clear enough even if it's
> technically a Scan.

Okay, ripped that out in the attached.

Thanks,
Amit

diff --git a/src/backend/executor/nodeGather.c b/src/backend/executor/nodeGather.c
index b6e82d1..10e2319 100644
--- a/src/backend/executor/nodeGather.c
+++ b/src/backend/executor/nodeGather.c
@@ -11,7 +11,8 @@
  * or have not started up yet.  It then merges all of the results it produces
  * and the results from the workers into a single output stream.  Therefore,
  * it will normally be used with a plan where running multiple copies of the
- * same plan does not produce duplicate output, such as PartialSeqScan.
+ * same plan does not produce duplicate output, such as parallel-aware
+ * SeqScan.
  *
  * Alternatively, a Gather node can be configured to use just one worker
  * and the single-copy flag can be set.  In this case, the Gather node will

-- 
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] problem with msvc linker - cannot build orafce

[Craig Ringer]

On 24 November 2015 at 07:12, Chapman Flack  wrote:

>
> What I (think I) took away from it was:
>
> 1.  Un-PGDLLIMPORTed references to global *functions* work ok.
> Maybe they are thunked and a little less efficient, but they work.
>
> 2.  Un-PGDLLIMPORTed references to global *variables*, not so much.
> They used to silently link (at least on some buildfarm critters)
> but hold bogus data (maybe a thunk, taken as data?).
>
> 3.  The one concrete *action* taken in the course of that thread was to
> tweak the build process to make sure such cases at least *fail*
> because that's better than silent bogosity.
>

Correct on all points.

The question that interests me most right now: how, if at all, can the
> extension author/maintainer work around this issue when it crops up?
>

AFAIK the only way to do it is to use the .pdb files to find the address of
the variable's storage, then create a pointer-to-var that way, after
adjusting for the DLL/EXE's base load address - which on some Windows
versions is randomized.

You might be able to do it with dbghelp.dll . I haven't looked into it
properly.


> Obviously, the Right Thing To Do is report it and get the PGDLLIMPORT
> added here, but still for years to come the extension will have to cope
> with being built against PG distributions that lack it.


It's safe to add in a point-release because nobody could've been using it
before, so all you have to do is require a certain minimum point release.
We all know how easy it is to get users to apply point releases ;)

If we annotated extern functions too, we could build on UNIX with
-fvisibility=hidden and get immediate linker errors on *nix too. As far as
I can tell gcc doesn't have the option to control default visibility
separately for functions and data exports. We do that on Windows by
generating a .DEF file, since Windows doesn't support it directly either.
Doing that on *nix would require using readelf then
using --retain-symbols-file at link time. Imagine how popular that'd be.

So currently we rely on the buildfarm complaining if things are broken on
Windows, but of course that only works for core and in-tree extensions.
(Who cares about anything else, right?).

See https://gcc.gnu.org/wiki/Visibility for details on visibility.

Now, I thought I spotted, somewhere in that long thread, the hint of an
> idea that the magic works as long as the *extension* has the variable
> declared PGDLLIMPORT, even if it wasn't declared that way when the PG
> executable itself was built. Anybody else remember that, or did I
> imagine it?
>

I haven't tested, but I believe you can declare it with an extra level of
pointer indirection, without __declspec(dllimport), and chase the pointer.

As I understand it (and I'm far from an expert here) the symbol in the PE
symbol table points to the address of a pointer to the data. MSVC doesn't
know if your "extern" references a variable in the same module (in which
case you don't have that indirection) or will be resolved by dynamic
linking to point to a pointer to the data. I find this bizarre, given that
ELF "just does it"... but PE is pretty weird and a fair bit older, a
hacked-up variant of COFF. Anyway, __declspec(dllimport) tells MSVC "I'm
going to be linking this from an external DLL, do the pointer chasing for
me please".

See https://msdn.microsoft.com/en-us/library/aa271769(v=vs.60).aspx "Using
__declspec(dllexport) and __declspec(dllimport) on Data".

I *think* it's safe to do this even if the var is declared
__declspec(dllexport). The dllexport declaration makes sure the export is
present without the use of a .DEF file to force it. You can
__declspec(dllimport) whether it was __declspec(dllexported)'ed or exported
via a .DEF file.

We auto-export functions in our DEF files, but not variables.


> You *might* get away with creating a separate C file (how about
> chamberofhorrors.c?) that, rather revoltingly, *doesn't* include the
> proper PostgreSQL .h files, only duplicates the necessary declarations
> with PGDLLIMPORT added, and exports some getter/setter methods
> to the rest of the extension code.  (I like the idea of one
> chamberofhorrors
> better than scattering such rubbish all over the project.)
>

I don't think that's necessary, per above. You just have to access the vars
via pointer indirection always, so long as *any* Pg version you support has
ever lacked dllexport or DEF entry, so you can't dllimport the var.

You could enable direct dllimport if PG_VERSION_NUM shows you're on a new
enough version, but you'd need to use conditionally compiled inlines or
something to switch between the methods of accessing it, so there's not
much point. You just declare

extern int* log_min_messages_p;

... and use that, probably also #define'ing log_min_messages away after
including the Pg headers so that you can't reference it accidentally.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 

Re: [HACKERS] custom function for converting human readable sizes to bytes

[Pavel Stehule]

Hi

2015-11-23 19:47 GMT+01:00 Alvaro Herrera :

> Pavel Stehule wrote:
>
> > so pg_size_bytes is good enough for everybody?
>
> That seems good enough to me.
>
> I would have it accept GiB and GB and have both transform to base 2, and
> have an optional boolean flag whose non-default value turns the GB
> interpretation into base 10, leaving the GiB interpretation unaffected.
>

attached proof concept based on parser "parse_int" from guc.c

It works well to 1TB what is enough for memory setting, but too low for
proposed target.

There are two ways

1. enhance the "parse_int"

2. using independent implementation - there is some redundant code, but we
can support duble insted int, and we can support some additional units.

Regards

Pavel


> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
new file mode 100644
index 5ee59d0..3fcd203
*** a/src/backend/utils/adt/dbsize.c
--- b/src/backend/utils/adt/dbsize.c
***
*** 25,30 
--- 25,31 
  #include "storage/fd.h"
  #include "utils/acl.h"
  #include "utils/builtins.h"
+ #include "utils/guc.h"
  #include "utils/numeric.h"
  #include "utils/rel.h"
  #include "utils/relfilenodemap.h"
*** pg_size_pretty_numeric(PG_FUNCTION_ARGS)
*** 700,705 
--- 701,728 
  }
  
  /*
+  * Convert human readable size to long int
+  *
+  */
+ Datum
+ pg_size_bytes(PG_FUNCTION_ARGS)
+ {
+ 	text	   *arg = PG_GETARG_TEXT_PP(0);
+ 	char	   *str = text_to_cstring(arg);
+ 	const char *hintmsg;
+ 	int	   result;
+ 
+ 	if (!parse_int(str, , GUC_UNIT_KB, ))
+ 		ereport(ERROR,
+ 			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ 			 errmsg("parameter \"%s\" isn't valid size value",
+ 	str),
+ 			hintmsg ? errhint("%s", _(hintmsg)) : 0));
+ 
+ 	PG_RETURN_INT64(((int64)result) * 1024);
+ }
+ 
+ /*
   * Get the filenode of a relation
   *
   * This is expected to be used in queries like
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index d8640db..b68c8fa
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*** DATA(insert OID = 2286 ( pg_total_relati
*** 3662,3667 
--- 3662,3669 
  DESCR("total disk space usage for the specified table and associated indexes");
  DATA(insert OID = 2288 ( pg_size_pretty			PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 25 "20" _null_ _null_ _null_ _null_ _null_ pg_size_pretty _null_ _null_ _null_ ));
  DESCR("convert a long int to a human readable text using size units");
+ DATA(insert OID = 3317 ( pg_size_bytes			PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 20 "25" _null_ _null_ _null_ _null_ _null_ pg_size_bytes _null_ _null_ _null_ ));
+ DESCR("convert a human readable text with size units to long int bytes");
  DATA(insert OID = 3166 ( pg_size_pretty			PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 25 "1700" _null_ _null_ _null_ _null_ _null_ pg_size_pretty_numeric _null_ _null_ _null_ ));
  DESCR("convert a numeric to a human readable text using size units");
  DATA(insert OID = 2997 ( pg_table_size			PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ pg_table_size _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
new file mode 100644
index e610bf3..227e5f5
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*** extern Datum pg_relation_size(PG_FUNCTIO
*** 462,467 
--- 462,468 
  extern Datum pg_total_relation_size(PG_FUNCTION_ARGS);
  extern Datum pg_size_pretty(PG_FUNCTION_ARGS);
  extern Datum pg_size_pretty_numeric(PG_FUNCTION_ARGS);
+ extern Datum pg_size_bytes(PG_FUNCTION_ARGS);
  extern Datum pg_table_size(PG_FUNCTION_ARGS);
  extern Datum pg_indexes_size(PG_FUNCTION_ARGS);
  extern Datum pg_relation_filenode(PG_FUNCTION_ARGS);

-- 
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] problem with msvc linker - cannot build orafce

[Craig Ringer]

>
>
> I don't think that's necessary, per above. You just have to access the
> vars via pointer indirection always, so long as *any* Pg version you
> support has ever lacked dllexport or DEF entry, so you can't dllimport the
> var.
>
> You could enable direct dllimport if PG_VERSION_NUM shows you're on a new
> enough version, but you'd need to use conditionally compiled inlines or
> something to switch between the methods of accessing it, so there's not
> much point. You just declare
>
> extern int* log_min_messages_p;
>
> ... and use that, probably also #define'ing log_min_messages away after
> including the Pg headers so that you can't reference it accidentally.
>


Actually, if __declspec(dllexport) or a .DEF entry was added in, say,
9.4.5, you could probably just:

#if PG_VERSION_NUM < 90405
extern int* log_min_messages_p;
#define log_min_messages (*log_min_messages_p)
#endif

after including all PostgreSQL headers. It won't work for inline functions
defined in PostgreSQL headers, but should otherwise be OK I think.

(again, untested)

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] WIP: About CMake v2

[Craig Ringer]

On 29 August 2015 at 02:04, Tom Lane  wrote:

> Christopher Browne  writes:
> > (Does CMake run on a VAX 11/780??  :-))
>
> Yeah.  I see the two major risks as being:
>
> 1. We limit ourselves to platforms that cmake works on.
>
> 2. We lose the ability to handle weird special-case tests that are
> possible (if not necessarily pleasant) with autoconf/gmake.
>
> I might be overly worried about #2 --- but the impression I have of cmake
> is that they've spent lots of time on "make easy cases easy" and maybe not
> enough on "make hard cases possible".
>

I would agree with that, and even go further to "make easy but common cases
ugly anyway".

I converted Scribus to CMake years ago as part of porting to Windows. It
promised to be a cleaner, nicer build system. It delivered in some ways and
was rather ugly in others.

Its configuration/scripting language feels like someone thought "hey, M4 is
horrible, lets make sure we keep that it-hurts-so-good feeling in our build
system too".

Many prewritten CMake modules fail to follow basic practices that make them
work anywhere but where the author wanted/needed. I rewrote pretty much
every CMake module I used in the build. That was years ago, mind, but I
haven't seen CMake exactly taking over the world since then.

For me CMake saved lots of pain on Windows, but added pain on Linux distros
and other platforms where I had to deal more directly with package naming
differences, path differences, etc. Maybe I was doing it wrong, or maybe
it's improved since.

To me, CMake is just another build system. It has some great features and
some truly awful ones, just like most others. It does have the major
advantage of working on Windows, but I'd want to know just how much of a
hit we were going to take on other platforms. If it breaks m68k netbsd I
really, really don't care, but platforms a sane person would use with a
modern Pg shouldn't be hurt in the name of making Windows nicer if it
doesn't actually gain real functionality.

A possible appeal of CMake to me is that we could more easily support
PGXS-like functionality sanely on Windows. It *really* sucks that we don't
have anything like that now, and how hard extensions are to build on
Windows. OTOH I think it's reasonably practical to use CMake to build a
"PGXS-alike" for windows that works with existing unmodified Pg installs of
existing versions... so the win isn't big.

Anyway, we won't know unless somebody tries it.  There will certainly be
> some pluses and some minuses, and we'll have to decide whether the pluses
> outweigh the minuses.  I don't think we can tell that without a fairly
> detailed attempt at making it work.
>

I agree.

It's pretty much a case of "show us how it looks and we'll decide whether
to use your hours and hours of work or throw it away". Don't forget the
docs builds... unless of course the CMake conversion is also proposed to
come with a full conversion of the docs to a less prehistoric,
easier-to-work-with format. In which case you can quadruple the hours
you're gambling too.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] problem with msvc linker - cannot build orafce

[Tom Lane]

Craig Ringer  writes:
> Actually, if __declspec(dllexport) or a .DEF entry was added in, say,
> 9.4.5, you could probably just:

> #if PG_VERSION_NUM < 90405
> extern int* log_min_messages_p;
> #define log_min_messages (*log_min_messages_p)
> #endif

> after including all PostgreSQL headers. It won't work for inline functions
> defined in PostgreSQL headers, but should otherwise be OK I think.

Some of these workarounds look like they would break if we add the missing
PGDLLIMPORT in future releases.  That would be nasty :-(.  Am I misreading
it?

regards, tom lane


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

Re: [HACKERS] problem with msvc linker - cannot build orafce

[Craig Ringer]

On 25 November 2015 at 13:36, Tom Lane  wrote:

> Craig Ringer  writes:
> > Actually, if __declspec(dllexport) or a .DEF entry was added in, say,
> > 9.4.5, you could probably just:
>
> > #if PG_VERSION_NUM < 90405
> > extern int* log_min_messages_p;
> > #define log_min_messages (*log_min_messages_p)
> > #endif
>
> > after including all PostgreSQL headers. It won't work for inline
> functions
> > defined in PostgreSQL headers, but should otherwise be OK I think.
>
> Some of these workarounds look like they would break if we add the missing
> PGDLLIMPORT in future releases.  That would be nasty :-(.  Am I misreading
> it?
>
>
I don't think they will, but without testing and more digging I can't be
sure. If marking the variable __declspec(dllexport) causes its import table
entry to be omitted then yes, that'd break things.

I'll try to dig out my Windows VM and prep a few tests once I've delivered
on the promised pglogical downstream.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] [DESIGN] ParallelAppend

[Amit Kapila]

On Mon, Nov 23, 2015 at 10:39 PM, Robert Haas  wrote:
>
> On Mon, Nov 23, 2015 at 7:45 AM, Amit Kapila 
wrote:
> > Without this patch, that 0.5 (or 50% of leaders effort) is considered
for
> > Gather node irrespective of the number of workers or other factors, but
> > I think with Patch that is no longer true and that's what I am worrying
> > about.
>
> Nope, that patch does not change that at all.  We probably should, but
> this patch does not.
>

I have taken some performance data with this patch.


- Select data from inheritance hierarchy with very few tuples.


Create table parent_rel(c1 int, c2 text);
Create table child1_rel () Inherits (parent_rel);
Create table child2_rel () Inherits (parent_rel);

insert into parent_rel values(generate_series(1,15), '');
insert into child1_rel values(generate_series(10,20),'aaa');
insert into child2_rel values(generate_series(20,30),'aaa');

Analyze parent_rel;
Analyze child1_rel;
Analyze child2_rel;

set max_parallel_degree=4;
set parallel_setup_cost=0;
set parallel_tuple_cost=0.01;

postgres=# explain select count(*) from parent_rel;
  QUERY PLAN


--
 Aggregate  (cost=2.71..2.72 rows=1 width=0)
   ->  Gather  (cost=0.00..2.62 rows=37 width=0)
 Number of Workers: 1
 ->  Append  (cost=0.00..2.25 rows=37 width=0)
   ->  Parallel Seq Scan on parent_rel  (cost=0.00..0.77
rows=15 width=0)
   ->  Parallel Seq Scan on child1_rel  (cost=0.00..0.74
rows=11 width=0)
   ->  Parallel Seq Scan on child2_rel  (cost=0.00..0.74
rows=11 width=0)


I have changed parallel_setup_cost and parallel_tuple_cost, so
it is selecting Gather path even for a small relation.  However,
the same won't be true for non-inheritence relation as if the number
of pages in relation are below than threshold (1000), it won't select
parallel path.  Now here we might want to have similar restriction for
Append Relation as well, that if combining all the child subpaths doesn't
have more than threshold number of pages, then don't try to build the
parallel path.

- Choose the data set that fits in shared_buffers and then run statements
with different selectivity and max_parallel_degree

Test setup

1. Use,  pgbench -i -s 100  to create initial data.
2. Use attached pgbench_partitions.sql to create 10 partitions with equal
data.
3. Use, parallel_append.sh to execute statements with different Selectivity
and max_parallel_degree (changed parallel_tuple_cost to 0.001)

Selection_criteria – 1% of rows will be selected and used costly function
evaluation for each row



Head



*max_parallel_degree* *exec_time (ms)* *workers_used*
0 76202 0
2 28556 2
4 21620 3
8 21693 3
16 21654 3
32 21579 3
64 21474 3



Patch



*max_parallel_degree* *exec_time (ms)* *workers_used*
0 77027 0
2 27088 2
4 16648 4
8 13730 5
16 13787 5
32 13794 5
64 13872 5


So here we can see that with Patch, performance is better, but I
think that is mainly due to number of workers working on a plan.
It is not clear that if we would have allowed more workers to
work at higher max_parallel_degree whether that can give us any
substantial benefit, but anyway I think thats a generic worker allocation
improvement which is not directly related to this patch.  The data
at different selectivities can be found in the attached document,
more or less that shows a similar trend.  Apart from this, I have tried
with data set which doesn't fit shared buffers, but fit in RAM, for that
also it shows similar trend.

Patch looks good, apart from worker allocation stuff, but I think we
can deal with that separately.


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


pgbench_partitions.sql
Description: Binary data


parallel_append.sh
Description: Bourne shell script


parallel_append_data.ods
Description: application/vnd.oasis.opendocument.spreadsheet

-- 
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] Revisiting pg_stat_statements and IN() (Was: Re: pg_stat_statements fingerprinting logic and ArrayExpr)

[Jim Nasby]

On 11/24/15 7:46 PM, Peter Geoghegan wrote:

On Tue, Nov 24, 2015 at 5:39 PM, Jim Nasby  wrote:

Another not-uncommon case is IN ( '1', '2', ... , '2342' ); in other words,
treating an integer as text. A lot of frameworks like to do that and just
push the problem onto the database. I'm not sure what pg_stat_statements
would ultimately see in that case..


They do?

postgres=# select 5::int4 in ('5');
  ?column?
──
  t
(1 row)

postgres=# select 5::int4 in ('5a');
ERROR:  22P02: invalid input syntax for integer: "5a"
LINE 1: select 5::int4 in ('5a');
^


I'm not following your point. Obviously you can't compare int to text 
that doesn't convert back to an int, but that's not what I was talking 
about.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] custom function for converting human readable sizes to bytes

[Jim Nasby]

On 11/24/15 10:57 PM, Pavel Stehule wrote:

+errmsg("parameter \"%s\" isn't valid size value",


Should read " isn't a valid size value"
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] [PROPOSAL] VACUUM Progress Checker.

[Amit Langote]

On 2015/11/21 14:38, Jim Nasby wrote:
> On 11/19/15 7:29 PM, Amit Langote wrote:
>>> Another option is to provide the means for the index scan routines to
>>> >report their progress. Maybe every index AM won't use it, but it'd
>>> >certainly be a lot better than staring at a long_running boolean.
>> The boolean would be a workaround for sure. I'm also slightly tempted by
>> the idea of instrumenting vacuum scans of individual index AM's bulkdelete
>> methods. One precedent is how vacuum_delay_point() are sprinkled around in
>> the code. Another problem to solve would be to figure out how to pass
>> progress parameters around - via some struct or could they be globals just
>> like VacuumCost* variables are...
> 
> It just occurred to me that we could do the instrumentation in
> lazy_tid_reaped(). It might seem bad to do in increment for every tuple in
> an index, but we're already doing a bsearch over the dead tuple list.
> Presumably that's going to be a lot more expensive than an increment
> operation.

Just to clarify, does this mean we report index vacuum progress in terms
of index items processed (not pages)? If so, how do we get total number of
index items to process (presumably across all indexes) for a given phase 2
round? As a context, we'd report phase 1 progress in terms of heap pages
processed of total heap pages.

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

[Amit Langote]

On 2015/11/21 5:46, Robert Haas wrote:
> On Thu, Nov 19, 2015 at 2:18 AM, Amit Langote
>  wrote:
>> As someone pointed out upthread, the final heap truncate phase can take
>> arbitrarily long and is outside the scope of lazy_scan_heap() to
>> instrument. Perhaps a bool, say, waiting_heap_trunc could be reported for
>> the same. Note that, it would have to be reported from lazy_vacuum_rel().
> 
> I don't think reporting booleans is a very good idea.  It's better to
> report that some other way, like use one of the strings to report a
> "phase" of processing that we're currently performing.

Yeah, that might be better. One possible downside of booleans I didn't
foresee is that too many of them might clutter the progress view. What
would've been the names of boolean columns in the progress view are better
reported as strings as the value of a single column, as you seem to suggest.

> 
>> IMHO, float progress parameters (st_progress_param_float[]) can be taken
>> out. They are currently unused and it's unlikely that some command would
>> want to report them.
> 
> If they are not used, they shouldn't be included in this patch, but we
> should be open to adding them later if it proves useful.

Certainly.

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] pg_receivexlog: spurious error message connecting to 9.3

[Marco Nenciarini]

Hi Robert,

On 17/11/15 20:10, Robert Haas wrote:
> On Tue, Nov 10, 2015 at 1:35 AM, Craig Ringer  wrote:
>> On 10 November 2015 at 01:47, Marco Nenciarini
>>  wrote:
>>
>>> I've attached a little patch that removes the errors when connected to 9.3.
>>
>> Looks good to me. No point confusing users.
>>
>> The other callers of RunIdentifySystem are pg_basebackup and
>> pg_receivelogical.
>>
>> pg_basebackup doesn't ask for the db_name (passes null).
>>
>> pg_receivelogical handles it being null already (and if it didn't,
>> it'd die with or without this patch).
>>
>> pg_receivexlog expects it to be null and fails gracefully if it isn't.
>>
>> So this change just removes some pointless noise.
> 
> The fprintf(stderr, ...) does not cause a non-local exit, so the
> "else" just after it should be deleted.  Otherwise, when that branch
> is taken, *db_name doesn't get initialized at all.
> 
> Actually, I'd suggest doing it like the attached instead, which seems
> a bit tighter.
> 

I agree, your patch is better. 

Regards,
Marco

-- 
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciar...@2ndquadrant.it | www.2ndQuadrant.it



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] problem with msvc linker - cannot build orafce

[Kisung Kim]

2015-11-24 8:12 GMT+09:00 Chapman Flack :

> On 11/23/15 15:14, Tom Lane wrote:
> > Lack of PGDLLIMPORT on the extern declaration, no doubt.
> >
> > The fact that we've not heard this before implies that either nobody has
> > ever tried to use orafce on Windows, or it only very recently grew a
> > dependency on session_timezone.
>
>
Actually, we encountered the situation before couple of months.
A client wanted to use orafce on Windows and the same build problem
occurred.
We performed a workaround to edit the PG source to export unresolved
symbols,
which I think of not a good solution.

2015-11-24 8:12 GMT+09:00 Chapman Flack :

> Has anyone got the stomach to try such a thing and see what happens?
> I don't have MSVC here.
>
> -Chap


We have the environment to test your ideas.
Can you explain your ideas with more detail?





(C)Bitnine, Kisung Kim, Ph.D
https://sites.google.com/site/kisungresearch/
E-mail : ks...@bitnine.co.kr
Office phone : 070-4800-3321
Mobile phone : 010-7136-0834
Fax : 02-568-1332

Re: [HACKERS] parallelism and sorting

[Robert Haas]

On Tue, Nov 24, 2015 at 7:59 AM, Amit Kapila  wrote:
> On Tue, Nov 24, 2015 at 8:59 AM, Robert Haas  wrote:
>> One idea about parallel sort is that perhaps if multiple workers feed
>> data into the sort, they can each just sort what they have and then
>> merge the results.
>
> Sounds like a good approach for parallel sorting, however small extension
> to it that could avoid merging the final results is that workers allocated
> for sort will perform range-based sorting. A simple example to sort integers
> from 1-100 will be, worker-1 will be responsible for sorting any integer
> between 1-50 and worker-2 will be responsible for sorting integers from
> 51-100 and then master backend just needs to ensure that it first returns
> the tuples from worker-1 and then from worker-2.  I think it has some
> similarity to your idea-5 (use of repartition), but not exactly same.

This is not so easy to accomplish for a couple of reasons.  First, how
would you know where to partition the range?  That would work fine if
you had all the data in sorted order to begin with, but of course if
you had that you wouldn't be sorting it.  Second, remember that the
data is probably arriving in separate streams in each worker - e.g.
the sort may be being fed by a parallel sequential scan.  If you do
what I'm proposing, those workers don't need to communicate with each
other except for the final merge at the end; but to do what you're
proposing, you'd need to move each tuple from the worker that got it
originally to the correct worker.  I would guess that would be at
least as expensive as the final merge pass you are hoping to avoid,
and maybe significantly moreso.

-- 
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] GIN pending list clean up exposure to SQL

[Fujii Masao]

On Sun, Nov 22, 2015 at 1:15 PM, Jaime Casanova
 wrote:
> On 21 November 2015 at 03:54, Jim Nasby  wrote:
>> On 11/19/15 10:47 AM, Jaime Casanova wrote:
>>>
>>> - only superusers?
>>
>>
>> I would think the owner of the table (index?) should also be able to run
>> this.
>
> agreed, that makes sense

Also the function should ensure that the server is running in
normal mode (not recovery mode) and the specified relation is
NOT other session's temporary table.

I added the similar function into pg_bigm extension
(pg_gin_pending_cleanup function in
https://osdn.jp/projects/pgbigm/scm/git/pg_bigm/blobs/master/bigm_gin.c).
It might help us improve the patch.

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] Re: In-core regression tests for replication, cascading, archiving, PITR, etc.

[Michael Paquier]

On Tue, Nov 24, 2015 at 2:14 PM, Michael Paquier
wrote:

> I'll rework this patch and will update a new version soon.
>

So, attached is a new patch addressing all the comments received. The new
version has the following changes:
- Print more verbosely stderr output in case of error in psql
- Add recovery test suite to SUBDIRS in src/test/Makefile
- Add strict and warnings to what is used in the new modules of this patch
- Manage node information using package/class PostgresNode.pm and have
RecoveryTest use it. I have actually made PostgresNode bare-bone and simple
on purpose: one can initialize the node, append configuration parameters to
it and manage it through start/stop/restart (we may want to add reload and
promote actually if needed). However, more complex configuration is left to
RecoveryTest.pm, which is in charge of appending the configuration
dedicated to streaming, archiving, etc though a set of routines working on
PostgresNode objects. I have also arrived at the conclusion that it is not
really worth adding a node status flag in PostgresNode because the port
number saved there is sufficient when doing free port lookup, and the list
of nodes used in a recovery test are saved in an array.
- Add new module RecursiveCopy to be used for base backups. This removes
the dependency with Archive::Tar. PostgresNode makes use of that when
initializing a node from a backup.
- Tests have been updated to use the PostgresNode objects instead of the
port number as identifier. That's more portable.

Hopefully I have missed nothing.
Regards,
-- 
Michael
diff --git a/src/bin/pg_rewind/RewindTest.pm b/src/bin/pg_rewind/RewindTest.pm
index a4c1737..ea219d7 100644
--- a/src/bin/pg_rewind/RewindTest.pm
+++ b/src/bin/pg_rewind/RewindTest.pm
@@ -125,38 +125,6 @@ sub check_query
 	}
 }
 
-# Run a query once a second, until it returns 't' (i.e. SQL boolean true).
-sub poll_query_until
-{
-	my ($query, $connstr) = @_;
-
-	my $max_attempts = 30;
-	my $attempts = 0;
-	my ($stdout, $stderr);
-
-	while ($attempts < $max_attempts)
-	{
-		my $cmd = [ 'psql', '-At', '-c', "$query", '-d', "$connstr" ];
-		my $result = run $cmd, '>', \$stdout, '2>', \$stderr;
-
-		chomp($stdout);
-		$stdout =~ s/\r//g if $Config{osname} eq 'msys';
-		if ($stdout eq "t")
-		{
-			return 1;
-		}
-
-		# Wait a second before retrying.
-		sleep 1;
-		$attempts++;
-	}
-
-	# The query result didn't change in 30 seconds. Give up. Print the stderr
-	# from the last attempt, hopefully that's useful for debugging.
-	diag $stderr;
-	return 0;
-}
-
 sub append_to_file
 {
 	my ($filename, $str) = @_;
diff --git a/src/test/Makefile b/src/test/Makefile
index b713c2c..7f7754f 100644
--- a/src/test/Makefile
+++ b/src/test/Makefile
@@ -12,7 +12,7 @@ subdir = src/test
 top_builddir = ../..
 include $(top_builddir)/src/Makefile.global
 
-SUBDIRS = regress isolation modules
+SUBDIRS = regress isolation modules recovery
 
 # We don't build or execute examples/, locale/, or thread/ by default,
 # but we do want "make clean" etc to recurse into them.  Likewise for ssl/,
diff --git a/src/test/perl/PostgresNode.pm b/src/test/perl/PostgresNode.pm
new file mode 100644
index 000..90ad3bd
--- /dev/null
+++ b/src/test/perl/PostgresNode.pm
@@ -0,0 +1,162 @@
+# PostgresNode, simple node representation for regression tests
+#
+# Regression tests should use this basic class infrastructure to define
+# nodes that need to be used in the complex scenarios. This object is wanted
+# simple with only a basic set of routines able to configure, initialize
+# and manage a node.
+
+package PostgresNode;
+
+use strict;
+use warnings;
+
+use RecursiveCopy;
+use TestLib;
+
+sub new {
+	my $class = shift;
+	my $pghost = shift;
+	my $pgport = shift;
+	my $self = {
+		_port => undef,
+		_host => undef,
+		_basedir => undef,
+		_connstr => undef,
+		_applname => undef
+			};
+
+	# Set up each field
+	$self->{_port} = $pgport;
+	$self->{_host} = $pghost;
+	$self->{_basedir} = TestLib::tempdir;
+	$self->{_connstr} = "port=$pgport host=$pghost";
+	$self->{_applname} = "node_$pgport";
+	bless $self, $class;
+	return $self;
+}
+
+# Get routines for various variables
+sub getPort {
+	my( $self ) = @_;
+	return $self->{_port};
+}
+sub getHost {
+	my( $self ) = @_;
+	return $self->{_host};
+}
+sub getConnStr {
+	my( $self ) = @_;
+	return $self->{_connstr};
+}
+sub getDataDir {
+	my ( $self ) = @_;
+	return $self->{_basedir} . '/pgdata';
+}
+sub getApplName {
+	my ( $self ) = @_;
+	return $self->{_applname};
+}
+sub getArchiveDir {
+	my ( $self ) = @_;
+	return $self->{_basedir} . '/archives';
+}
+sub getBackupDir {
+	my ( $self ) = @_;
+	return $self->{_basedir} . '/backup';
+}
+
+# Dump node information
+sub dumpNodeInfo {
+	my ( $self ) = @_;
+	print 'Data directory: ' . $self->getDataDir() . "\n";
+	print 'Backup directory: ' . $self->getBackupDir() . "\n";
+	print 'Archive directory: ' . $self->getArchiveDir() . "\n";
+	print 'Connection string: ' . 

Re: [HACKERS] problem with msvc linker - cannot build orafce

[Chapman Flack]

On 11/24/2015 05:33 AM, Kisung Kim wrote:
> 2015-11-24 8:12 GMT+09:00 Chapman Flack :
>> On 11/23/15 15:14, Tom Lane wrote:
>>> Lack of PGDLLIMPORT on the extern declaration, no doubt.
>>
> Actually, we encountered the situation before couple of months.
> A client wanted to use orafce on Windows and the same build problem
> occurred.
> We performed a workaround to edit the PG source to export unresolved
> symbols,
> which I think of not a good solution.

>> Has anyone got the stomach to try such a thing and see what happens?
>> I don't have MSVC here.
> 
> We have the environment to test your ideas.
> Can you explain your ideas with more detail?

Well, the main idea is just this:  *IF* it is sufficient to declare
a variable PGDLLIMPORT only in the code that is importing it (the
big IF because I don't know whether that is true, but something I
saw in that long earlier thread seemed to suggest it) ...

Then ... the chief problem that needs to be solved is only that
MSVC won't allow you to redeclare something with PGDLLIMPORT if
it is also declared without PGDLLIMPORT in a .h file that you
include. In other words, you can't simply:

#include 
extern PGDLLIMPORT pg_tz session_timezone; /* the right way now */

because it was already declared the wrong way in pgtime.h.

So one idea is just this:

#define session_timezone decoy_session_timezone;
#include 
#undef decoy_session_timezone;

extern PGDLLIMPORT pg_tz session_timezone; /* the right way now */

which is not a multiple declaration of the same thing, because
what got declared the wrong way in pgtime.h is now some other thing
named decoy_session_timezone.  You might need to supply a thing by
that name, to avoid a linker complaint:

pg_tz decoy_session_timezone; /* never used */

IF the original premise is true, then this technique ought to be
usable in most cases. It would, however, break in cases where the
.h file declares macros or inline functions that refer to the
symbol, because they would all end up referring to the decoy.

My other idea, especially if there were several symbols needing
to be treated this way, would be to do it all in one dedicated
.c file, so any of the possible problems with #defining away parts
of an .h file would be contained in one place, and that file could
have a simple getter function:

pg_tz getSessionTimezone() { return session_timezone; }

which would be used in the rest of the code instead of referring
to the global directly. (In that case, of course, the same getter
function would have to be provided in the non-MSVC case too.)
A setter function could also be made, if the code needs it.



-- 
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] New email address

[Tom Lane]

Alvaro Herrera  writes:
> Magnus Hagander wrote:
>> That's a direct effect of the dmarc policy change. Yahoo no longer supports
>> their customers using mailing lists. They changed their policies for such
>> emails to hard reject, which makes Gmail (and presumably others) stick them
>> in spam.. It would happen to all the emails except the ones where you are
>> on direct cc.

> FWIW we've been rejecting posts coming from @yahoo.com addresses for a
> long time now, since DMARC was first introduced.  We didn't get around
> to blocking other domains owned by Yahoo such as ymail.com or national
> yahoo subdomains, but I assume (without checking) that those will cause
> trouble too and we will have to block them out in order not to fill our
> queues with useless bounces.

FWIW, my neighborhood's mailing list just recently implemented some
changes that were supposed to allow the list to work again for Yahoo
and other DMARC-affected users, after quite some time without service.
I don't know how successful they were at that, nor how difficult the
changes were ... but I do know the list server was offline for more
than a day while the changes went in, so it was less than trivial.
The only real change I can detect from looking at mail headers is that
it seems the list may now be attaching its own DKIM-Signature header
to emails that had one upon arrival.

If anyone thinks we might be motivated to become DMARC compliant,
I can inquire for more details.  But I won't bother unless there's
real interest.

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] New email address

[Magnus Hagander]

On Tue, Nov 24, 2015 at 4:00 PM, Tom Lane  wrote:

> Alvaro Herrera  writes:
> > Magnus Hagander wrote:
> >> That's a direct effect of the dmarc policy change. Yahoo no longer
> supports
> >> their customers using mailing lists. They changed their policies for
> such
> >> emails to hard reject, which makes Gmail (and presumably others) stick
> them
> >> in spam.. It would happen to all the emails except the ones where you
> are
> >> on direct cc.
>
> > FWIW we've been rejecting posts coming from @yahoo.com addresses for a
> > long time now, since DMARC was first introduced.  We didn't get around
> > to blocking other domains owned by Yahoo such as ymail.com or national
> > yahoo subdomains, but I assume (without checking) that those will cause
> > trouble too and we will have to block them out in order not to fill our
> > queues with useless bounces.
>
> FWIW, my neighborhood's mailing list just recently implemented some
> changes that were supposed to allow the list to work again for Yahoo
> and other DMARC-affected users, after quite some time without service.
> I don't know how successful they were at that, nor how difficult the
> changes were ... but I do know the list server was offline for more
> than a day while the changes went in, so it was less than trivial.
> The only real change I can detect from looking at mail headers is that
> it seems the list may now be attaching its own DKIM-Signature header
> to emails that had one upon arrival.
>
> If anyone thinks we might be motivated to become DMARC compliant,
> I can inquire for more details.  But I won't bother unless there's
> real interest.
>
>
I'd definitely be interested at least in what they're doing. Whether we'd
actually implement it would depend on the implications of course, but if
they've actually figured out how to do it, it could be useful.

We've discussed just forcibly stripping the DKIM headers of those emails,
but that's unlikely to help - I'm sure large mail providers will "know"
that yahoo mail is supposed to carry DKIM and thus fail. The whole point of
DKIM is to prevent changing the headers after all - and we do change the
headers.

Yahoo has a page on it (can't find the ref this moment) where they simply
say "there is no mailinglist software supporting this. There are some
experimental patches for an old version of mailman that people will perhaps
consider merging at some time in the future."


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] New email address

[Tom Lane]

Magnus Hagander  writes:
> On Tue, Nov 24, 2015 at 4:00 PM, Tom Lane  wrote:
>> If anyone thinks we might be motivated to become DMARC compliant,
>> I can inquire for more details.  But I won't bother unless there's
>> real interest.

> I'd definitely be interested at least in what they're doing. Whether we'd
> actually implement it would depend on the implications of course, but if
> they've actually figured out how to do it, it could be useful.

OK, will ask.

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] New email address

[Alvaro Herrera]

Magnus Hagander wrote:

> That's a direct effect of the dmarc policy change. Yahoo no longer supports
> their customers using mailing lists. They changed their policies for such
> emails to hard reject, which makes Gmail (and presumably others) stick them
> in spam.. It would happen to all the emails except the ones where you are
> on direct cc.

FWIW we've been rejecting posts coming from @yahoo.com addresses for a
long time now, since DMARC was first introduced.  We didn't get around
to blocking other domains owned by Yahoo such as ymail.com or national
yahoo subdomains, but I assume (without checking) that those will cause
trouble too and we will have to block them out in order not to fill our
queues with useless bounces.

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


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

[HACKERS] Re: Revisiting pg_stat_statements and IN() (Was: Re: pg_stat_statements fingerprinting logic and ArrayExpr)

[Greg Stark]

On Tue, Nov 24, 2015 at 7:53 AM, Peter Geoghegan  wrote:
> * How do other people feel about this? Personally, I've seen enough
> problems of this kind in the field that "slippery slope" arguments
> against this don't seem very compelling.

I also always felt there should be some kind of ??? symbol to
represent a list of constants. In my experience these lists are
actually *more* likely to be variables being inlined than single
constants since it's easy to use :1 etc for single constants and quite
a bit trickier to do it for lists. I guess that might be changed these
days since I think you can do  =any(?::int[]) and construct an array
literal as a parameter. But plenty of code actually constructs lists
of question marks to interpolate.

I have also seen code where I would have needed *not* to have this
jumbling though. I think this is a general problem with jumbling that
there needs to be some kind of intelligence that deduces when it's
important to break out the statistics by constant. In my case it was
an IN query where specific values were very common but others very
rare. Partial indexes ended up being the solution and we had to
identify which partial indexes were needed.

Incidentally there's another feature pg_stat_statements *really*
needs. A way to convert a jumbled statement into one that can be
prepared easily. The use of ? instead of :1 :2 etc makes this a
mechanical but annoying process. Adding ??? would make it even more
annoying. Even just a function that does this (and takes an optional
list of counts for lists I guess?) would be a big help.


-- 
greg


-- 
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] New email address

[Magnus Hagander]

On Tue, Nov 24, 2015 at 12:58 PM, Alvaro Herrera 
wrote:

> Magnus Hagander wrote:
>
> > That's a direct effect of the dmarc policy change. Yahoo no longer
> supports
> > their customers using mailing lists. They changed their policies for such
> > emails to hard reject, which makes Gmail (and presumably others) stick
> them
> > in spam.. It would happen to all the emails except the ones where you are
> > on direct cc.
>
> FWIW we've been rejecting posts coming from @yahoo.com addresses for a
> long time now, since DMARC was first introduced.  We didn't get around
> to blocking other domains owned by Yahoo such as ymail.com or national
> yahoo subdomains, but I assume (without checking) that those will cause
> trouble too and we will have to block them out in order not to fill our
> queues with useless bounces.
>

Yes. The difference is they changed it from a soft fail to a hard reject,
AIUI. From all of their domains (Kevin forwarded me the responses from
Yahoo support). So the problem got worse, but it's the same basic one.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] Re: Revisiting pg_stat_statements and IN() (Was: Re: pg_stat_statements fingerprinting logic and ArrayExpr)

[Ants Aasma]

On Tue, Nov 24, 2015 at 2:25 PM, Greg Stark  wrote:
> On Tue, Nov 24, 2015 at 7:53 AM, Peter Geoghegan  wrote:
>> * How do other people feel about this? Personally, I've seen enough
>> problems of this kind in the field that "slippery slope" arguments
>> against this don't seem very compelling.

+1 for changing jumbling logic to consider any number of constants as identical.

> I have also seen code where I would have needed *not* to have this
> jumbling though. I think this is a general problem with jumbling that
> there needs to be some kind of intelligence that deduces when it's
> important to break out the statistics by constant. In my case it was
> an IN query where specific values were very common but others very
> rare. Partial indexes ended up being the solution and we had to
> identify which partial indexes were needed.

This is an issue with jumbling in general and very vaguely related to
merging the number of constants in IN. I think a better solution for
this type of issue would be a way to sample the parameter values and
possibly EXPLAIN ANALYZE results to logs. Having runtime intelligence
in PostgreSQL that would be capable of distinguishing interesting
variations from irrelevant doesn't seem like a feasible plan. In my
view the best we could do is to aim to have entries roughly correspond
to application query invocation points and leave the more complex
statistical analysis use cases to more specialized tools.

Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de


-- 
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] parallelism and sorting

[Amit Kapila]

On Tue, Nov 24, 2015 at 8:59 AM, Robert Haas  wrote:
>
>
> One idea about parallel sort is that perhaps if multiple workers feed
> data into the sort, they can each just sort what they have and then
> merge the results.


Sounds like a good approach for parallel sorting, however small extension
to it that could avoid merging the final results is that workers allocated
for sort will perform range-based sorting. A simple example to sort integers
from 1-100 will be, worker-1 will be responsible for sorting any integer
between 1-50 and worker-2 will be responsible for sorting integers from
51-100 and then master backend just needs to ensure that it first returns
the tuples from worker-1 and then from worker-2.  I think it has some
similarity to your idea-5 (use of repartition), but not exactly same.



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

Re: [HACKERS] pg_receivexlog: spurious error message connecting to 9.3

[Fujii Masao]

On Tue, Nov 24, 2015 at 7:00 PM, Marco Nenciarini
 wrote:
> Hi Robert,
>
> On 17/11/15 20:10, Robert Haas wrote:
>> On Tue, Nov 10, 2015 at 1:35 AM, Craig Ringer  wrote:
>>> On 10 November 2015 at 01:47, Marco Nenciarini
>>>  wrote:
>>>
 I've attached a little patch that removes the errors when connected to 9.3.
>>>
>>> Looks good to me. No point confusing users.
>>>
>>> The other callers of RunIdentifySystem are pg_basebackup and
>>> pg_receivelogical.
>>>
>>> pg_basebackup doesn't ask for the db_name (passes null).
>>>
>>> pg_receivelogical handles it being null already (and if it didn't,
>>> it'd die with or without this patch).
>>>
>>> pg_receivexlog expects it to be null and fails gracefully if it isn't.
>>>
>>> So this change just removes some pointless noise.
>>
>> The fprintf(stderr, ...) does not cause a non-local exit, so the
>> "else" just after it should be deleted.  Otherwise, when that branch
>> is taken, *db_name doesn't get initialized at all.
>>
>> Actually, I'd suggest doing it like the attached instead, which seems
>> a bit tighter.
>>
>
> I agree, your patch is better.

+else if (PQserverVersion(conn) >= 90400)
 fprintf(stderr,
 _("%s: could not identify system: got %d rows and
%d fields, expected %d rows and %d or more fields\n"),
 progname, PQntuples(res), PQnfields(res), 1, 4);
 }

In the above case, PQclear(res) should be called and FALSE should be returned?

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] parallelism and sorting

[Ants Aasma]

On Tue, Nov 24, 2015 at 5:29 AM, Robert Haas  wrote:
> On Mon, Nov 23, 2015 at 8:45 PM, Peter Geoghegan  wrote:
>> Beyond that, CREATE INDEX and CLUSTER utility
>> cases will also need to be parallelized without all this executor
>> infrastructure.
>
> Or, alternatively, CREATE INDEX and CLUSTER could be refactored to use
> the executor.  This is might sound crazy, but maybe it's not.  Perhaps
> we could have the executor tree output correctly-formed index tuples
> that get funneled into a new kind of DestReceiver that puts them into
> the index.  I don't know if that's a GOOD idea, but it's an idea.

Having CREATE INDEX use the executor seems like a useful idea for
reasons unrelated to parallelism.

The use case I have in mind is a table containing multiple years worth
of (approximately) time series data, where overwhelming majority of
queries are explicitly interested in recent data. Having a partial
index with WHERE tstamp > $some_recent_tstamp cutting out 90+% of
tuples was extremely helpful for performance for both index size
reasons and having to process less tuples. This index needs to be
periodically rebuilt with a newer timestamp constant, and the rebuild
would be a lot faster if it could use the existing index to perform an
index only scan of 10% of data instead of scanning and sorting the
full table.

Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de


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