Re: Hash Joins vs. Bloom Filters / take 2

[Patrick Krecker]

On Thu, Mar 1, 2018 at 4:04 PM, David Steele  wrote:
> On 3/1/18 6:52 PM, Tomas Vondra wrote:
>>
>> On 03/02/2018 12:31 AM, Andres Freund wrote:
>>>
>>>
>>>
>>> On March 1, 2018 3:22:44 PM PST, Tomas Vondra
>>>  wrote:



 On 03/01/2018 11:01 PM, Andres Freund wrote:
>
> Hi,
>
> On 2018-02-20 22:23:54 +0100, Tomas Vondra wrote:
>>
>> So I've decided to revive the old patch, rebase it to current

 master,
>>
>> and see if we can resolve the issues that killed it in 2016.
>
>
> There seems to be some good discussion in the thread. But the patch
> arrived just before the last commitfest and certainly isn't a trivial
> cleanup patch. Therefore I think it should be moved to the next CF?
>

 It isn't a massive invasive patch either, though, so I object to moving
 it to 2018-09 right away.
>>>
>>>
>>> Why do we have rules around not submitting large stuff to the last
>>> cf, if we just not follow through? We're neck deep in patches that
>>> are older. And you've already gotten a fair bit of feedback..
>>>
>>
>> It was not my intention to break (or even bend) the CF rules, of course.
>> I haven't considered the patch to be "large stuff", while you do. I see
>> Peter Geoghegan agrees with your conclusion on another thread, so go
>> ahead and move it to 2018-09.
>
>
> After reviewing the thread I also agree that this should be pushed to
> 2018-09, so I have done so.
>
> I'm very excited by this patch, though.  In general I agree with Peter that
> a higher rate of false positives is acceptable to save memory.  I also don't
> see any reason why this can't be tuned with a parameter. Start with a
> conservative default and allow the user to adjust as desired.
>
> --
> -David
> da...@pgmasters.net
>

Hi All --

I'm curious what has to be done to move this patch along. I looked
through the patched and I noticed that the work for deciding whether to
instantiate the bloom filter in single-batch mode is not complete yet
(or if it's in this change, I can't find it), contrary to this
comment:

+* When starting in a single-batch mode, we do nothing initially.
+* If the whole hash table fits into a single batch, we can get
+* sufficiently accurate ndistinct estimate by simply counting
+* occupied buckets (thanks to shooting for NTUP_PER_BUCKET=1),
+* or perhaps we could use something more elaborate (e.g. HLL).
+* But we only build the bloom filter if the hash table is large
+* enough to exceed on-CPU caches (e.g. 4MB).

I did some basic benchmarking with two tables and a simple where
clause filtering 90% of rows and it does yield about a 1.2x speedup in
my tests. In the pathological case where two tables are joined on a
pk/fk relationship with no filtering, the penalty seems to be about
1-2%.

Patrick

Re: TODO item for broken \s with libedit seems fixed

[Patrick Krecker]

> On Feb 27, 2018, at 06:25, Tom Lane  wrote:
> 
> Tatsuo Ishii  writes:
>> Would you like to do this yourself? Or shall I do this?
> 
> Go ahead, I have a bunch of other stuff to do ...
> 
>regards, tom lane

I don’t think I have permission to edit the page, otherwise I would be happy to.

Thanks,
Patrick

Re: TODO item for broken \s with libedit seems fixed

[Patrick Krecker]

On Mon, Feb 26, 2018 at 4:54 PM, Tatsuo Ishii  wrote:
>> It appears this was fixed back in 2014 with 750c5ee. I propose for it
>> to be removed from the TODO list.
>
> Yes, I confirmed it by using Ubuntu + libedit. I have not tested it on
> Mac OS X yet though.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp

I tested on macOS 10.12.6.

Patrick

TODO item for broken \s with libedit seems fixed

[Patrick Krecker]

It appears this was fixed back in 2014 with 750c5ee. I propose for it
to be removed from the TODO list.

Thanks,
Patrick

Re: Option to ensure monotonic timestamps

[Patrick Krecker]

On Tue, Feb 20, 2018 at 9:51 AM, Andres Freund  wrote:
> Hi,
>
> Leaving Tom's concerns aside:
>
> On 2018-02-19 13:42:31 -0700, Brent Kerby wrote:
>> Hi, I'm new to Postgres hacking, and I'm interested in the possibility of a
>> new feature to make it possible to ensure that Postgres-generated
>> timestamps never decrease even if the system clock may step backwards. My
>> use case is that I'm implementing a form of temporal tables based on
>> transaction commit timestamps (as returned by pg_xact_commit_timestamp),
>> and to ensure the integrity of the system I need to know that the ordering
>> of the commit timestamps will always be consistent with the order in which
>> the transactions actually committed.
>
> The acquiration of the commit timestamp and the actual visibility of the
> commit will not necessarily be sufficient for many things. A backend can
> theoretically sleep for an hour between
>
> static TransactionId
> RecordTransactionCommit(void)
> {
> ...
> SetCurrentTransactionStopTimestamp();
> /* here */
> XactLogCommitRecord(xactStopTimestamp,
> nchildren, children, 
> nrels, rels,
> nmsgs, invalMessages,
> 
> RelcacheInitFileInval, forceSyncCommit,
> MyXactFlags,
> InvalidTransactionId 
> /* plain commit */ );
> }
>
> static void
> CommitTransaction(void)
> {
> ...
> /*
>  * We need to mark our XIDs as committed in pg_xact.  This is 
> where we
>  * durably commit.
>  */
> latestXid = RecordTransactionCommit();
>
> /* here */
>
> /*
>  * Let others know about no transaction in progress by me. Note that 
> this
>  * must be done _before_ releasing locks we hold and _after_
>  * RecordTransactionCommit.
>  */
> ProcArrayEndTransaction(MyProc, latestXid);
>
> whether that affects your approach I do not know.
>
>
>> Any thoughts?
>
> Why are you looking to do something timestamp based in the first place?
> It's a bit hard to give good advice without further information...
>
> Greetings,
>
> Andres Freund
>

Hi Brent --

I haven't heard of temporal tables before, but I guess it's a feature
of SQL Server 2016. It sounds similar to some recent work in progress
to add "AS OF" to SELECT statements:
https://www.postgresql.org/message-id/78aadf6b-86d4-21b9-9c2a-51f1efb8a...@postgrespro.ru

Patrick

Re: TODO item: WAL replay of CREATE TABLESPACE with differing directory structure

[Patrick Krecker]

On Tue, Feb 13, 2018 at 8:24 PM, Michael Paquier <mich...@paquier.xyz> wrote:
> On Tue, Feb 13, 2018 at 01:44:34PM -0800, Patrick Krecker wrote:
>> I am searching for a way to make a contribution to Postgres and I came
>> across this TODO item (I realize there has been some controversy
>> around the TODO list [1], and I hope that my use of it doesn't spark
>> another discussion about removing it altogether):
>
> Well, it will point out again that TODO items are hard, complicated and
> mostly impossible projects.
>
>> "Allow WAL replay of CREATE TABLESPACE to work when the directory
>> structure on the recovery computer is different from the original"
>>
>> Currently it looks like tablespaces have to live inside the data
>> directory on the replica, notwithstanding administrator intervention
>> by manipulating the tablespace directory with symlinks after (or even
>> before?) it has been created via replay.
>
> Let's be clear here. There is no hard restriction with tablespace paths
> within the data directory, though you should not do that, and you get a
> nice warning when trying to do so with CREATE TABLESPACE (see 33cb8ff6).
> This also causes pg_basebackup to fail.  It is also bad design to create
> tablespaces within the data directory as those are aimed at making hot
> paths work on different partitions with different I/O properties.

Sorry, my language was imprecise here. What I meant is that the
pg_tablespace directory contains no symlinks when a tablespace
creation is streamed to a replica, i.e. the data files reside within
pg_tablespace on the replica.

>> Is the idea behind this task to allow the master to instruct the
>> replica where to put the tablespace on its filesystem, so as to allow
>> it to live outside of the data directory without direct manipulation
>> of the filesystem?
>
> WAL records associated to CREATE TABLESPACE (xl_tblspc_create_rec)
> register the location where a tablespace is located.  The location of a
> tablespace is not saved in the system catalogs, which offers flexibility
> in the way the symlink from pg_tblspc can be handled.  This is where the
> tablespace path remapping of pg_basebackup becomes handy, because you
> can repurpose paths easily when taking a base backup, but this forces
> you to create tablespaces first, and then create standbys.  We have also
> a set of existing problems:
> 1) If a primary and its standby are on the same server and you issue a
> CREATE TABLESPACE, then they would try to write to the same paths.
> 2) How do we design at DDL level a command which allows for specifying
> different paths depending on the node where the recovery happens.
>
> You would need in both cases a sort of ability to define a node name, so
> as for 1) you append the node name to the path and both primary and
> standby can use the same tablespace path, but with different sub-paths.
> And for 2), you can enforce a patch name by defining as well a path
> associated to a node name so as when xl_tblspc_create_rec records are
> replayed at recovery, you know which path to create.  Just designing
> that the right way as its own set of complications.
>
>> If this task is a worthwhile endeavor, I would be happy to take it on.
>> If not, I am open to other ideas :)
>
> This is part of the difficult, perhaps-not-worth doing impossible
> problems.  As a first contribution, you may want something easier.

Thank you for the response. I would suggest that we link to it from
the wiki so as to provide clarification to future readers of the todo
list.

> --
> Michael

TODO item: WAL replay of CREATE TABLESPACE with differing directory structure

[Patrick Krecker]

Hi Hackers --

I am searching for a way to make a contribution to Postgres and I came
across this TODO item (I realize there has been some controversy
around the TODO list [1], and I hope that my use of it doesn't spark
another discussion about removing it altogether):

"Allow WAL replay of CREATE TABLESPACE to work when the directory
structure on the recovery computer is different from the original"

Currently it looks like tablespaces have to live inside the data
directory on the replica, notwithstanding administrator intervention
by manipulating the tablespace directory with symlinks after (or even
before?) it has been created via replay.

Is the idea behind this task to allow the master to instruct the
replica where to put the tablespace on its filesystem, so as to allow
it to live outside of the data directory without direct manipulation
of the filesystem?

If this task is a worthwhile endeavor, I would be happy to take it on.
If not, I am open to other ideas :)

Thanks,
Patrick

[1] 
https://www.postgresql.org/message-id/CA+TgmoZC3CyzZDY1fWChRNOY-5SBjUkB1w=c6y6jmqhtavu...@mail.gmail.com

Re: TODO list (was Re: Contributing with code)

[Patrick Krecker]

On Tue, Jan 2, 2018 at 3:42 PM, Joshua D. Drake  wrote:
> On 01/02/2018 11:17 AM, Robert Haas wrote:
>>
>> On Sun, Dec 31, 2017 at 2:31 PM, Peter Geoghegan  wrote:
>>>
>>> On Sun, Dec 31, 2017 at 10:42 AM, Tom Lane  wrote:

 If we're not going to maintain/curate it properly, I agree it's not
 worth keeping it around.  But I'd rather see somebody put some effort
 into it ...
>>>
>>> If somebody was going to resolve to put some effort into maintaining
>>> it to a high standard then it probably would have happened already.
>>> The fact that it hasn't happened tells us plenty.
>>
>> +1, and well said.
>
>
> O.k. what does it tell us though? Is it a resource issue? Is it a barrier of
> entry issue? What does deleting it solve? What problems (and there is a very
> large obvious one) are caused by deleting it?
>
> Right now, the TODO list is the "only" portal to "potential" things we
> "might" want. If we delete it we are just creating yet another barrier of
> entry to potential contribution. I think we need to consider an alternative
> solution because of that.
>
> Thanks,
>
> JD
>
>
> --
> Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
>
> PostgreSQL centered full stack support, consulting and development.
> Advocate: @amplifypostgres || Learn: https://postgresconf.org
> * Unless otherwise stated, opinions are my own.   *
>
>

As a person looking to become a postgres contributor, perhaps I can
offer some perspective on this. I think there is value in providing
*some* starting point for new contributors in the form of concrete
problems to solve. The value I hope to extract from the time spent on
my first feature comes mostly from the learning experience and not
from the acceptance of the feature itself. I would not be upset if my
work was never accepted as long as I understand why. I expect most
people picking features at random from a TODO list would have a
similar outlook on their first contribution.