Re: csv format for psql

[Joshua D. Drake]

On 03/28/2018 12:35 PM, David G. Johnston wrote:
On Monday, March 26, 2018, Daniel Verite > wrote:



We could even support only the comma and make it non-configurable
based on the fact it's Comma-Separated-Values, not
Whatever-Separated-Values, except that won't do much
to serve the users interests, as the reality is that
people use various separators.


I like to call it "Character Separated Values" now for just that reason.


Isn't the actual wording Character Delimited Values? I may be picking at 
hairs here but every single time I use anything to import a CSV or other 
delimited file (TAB or | usually) that is what the import screen says.


JD



David J.



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Online enabling of checksums

[Joshua D. Drake]

On 04/05/2018 01:12 PM, Andres Freund wrote:

On 2018-04-05 22:06:36 +0200, Magnus Hagander wrote:

I have now pushed this latest version with some minor text adjustments and
a catversion bump.

Thanks for all the reviews!

I want to be on the record that I think merging a nontrival feature that
got submitted 2018-02-21, just before the start of the last last CF, is
an abuse of process, and not cool.  We've other people working hard to
follow the process, and circumventing it like this just signals to
people trying to follow the rules that they're fools.

Merging ~2kloc patches like that is going to cause pain. And even if
not, it causes procedual damage.


Perhaps I am missing something but there has been a lot of public 
discussion on this feature for the last 7 weeks of which you barely 
participated. I certainly understand wanting some notice before commit 
but there has been lots of discussion, multiple people publicly 
commenting on the patch and Magnus has been very receptive to all 
feedback (that I have seen). Perhaps we are being a sensitive because of 
another patch that is actually ramrodding the process and we need to 
take a step back?


Thanks,

JD




- Andres



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Online enabling of checksums

[Joshua D. Drake]

On 04/05/2018 02:01 PM, Andres Freund wrote:


No. See link above.

Please don't use "we" in this childishness implying fashion.


The term "we" was used on purpose because I too was annoyed and I was 
trying to be objective, non-combative and productive.


JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

[Joshua D. Drake]

On 04/09/2018 09:45 AM, Robert Haas wrote:

On Mon, Apr 9, 2018 at 8:16 AM, Craig Ringer  wrote:

In the mean time, I propose that we fsync() on close() before we age FDs out
of the LRU on backends. Yes, that will hurt throughput and cause stalls, but
we don't seem to have many better options. At least it'll only flush what we
actually wrote to the OS buffers not what we may have in shared_buffers. If
the bgwriter does the same thing, we should be 100% safe from this problem
on 4.13+, and it'd be trivial to make it a GUC much like the fsync or
full_page_writes options that people can turn off if they know the risks /
know their storage is safe / don't care.

I have a really tough time believing this is the right way to solve
the problem.  We suffered for years because of ext3's desire to flush
the entire page cache whenever any single file was fsync()'d, which
was terrible.  Eventually ext4 became the norm, and the problem went
away.  Now we're going to deliberately insert logic to do a very
similar kind of terrible thing because the kernel developers have
decided that fsync() doesn't have to do what it says on the tin?  I
grant that there doesn't seem to be a better option, but I bet we're
going to have a lot of really unhappy users if we do this.


I don't have a better option but whatever we do, it should be an optional
(GUC) change. We have plenty of YEARS of people not noticing this issue and
Robert's correct, if we go back to an era of things like stalls it is going
to look bad on us no matter how we describe the problem.

Thanks,

JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

[Joshua D. Drake]

-hackers,

I reached out to the Linux ext4 devs, here is ty...@mit.edu response:

"""
Hi Joshua,

This isn't actually an ext4 issue, but a long-standing VFS/MM issue.

There are going to be multiple opinions about what the right thing to
do.  I'll try to give as unbiased a description as possible, but
certainly some of this is going to be filtered by my own biases no
matter how careful I can be.

First of all, what storage devices will do when they hit an exception
condition is quite non-deterministic.  For example, the vast majority
of SSD's are not power fail certified.  What this means is that if
they suffer a power drop while they are doing a GC, it is quite
possible for data written six months ago to be lost as a result.  The
LBA could potentialy be far, far away from any LBA's that were
recently written, and there could have been multiple CACHE FLUSH
operations in the since the LBA in question was last written six
months ago.  No matter; for a consumer-grade SSD, it's possible for
that LBA to be trashed after an unexpected power drop.

Which is why after a while, one can get quite paranoid and assume that
the only way you can guarantee data robustness is to store multiple
copies and/or use erasure encoding, with some of the copies or shards
written to geographically diverse data centers.

Secondly, I think it's fair to say that the vast majority of the
companies who require data robustness, and are either willing to pay
$$$ to an enterprise distro company like Red Hat, or command a large
enough paying customer base that they can afford to dictate terms to
an enterprise distro, or hire a consultant such as Christoph, or have
their own staffed Linux kernel teams, have tended to use O_DIRECT.  So
for better or for worse, there has not been as much investment in
buffered I/O and data robustness in the face of exception handling of
storage devices.

Next, the reason why fsync() has the behaviour that it does is one
ofhe the most common cases of I/O storage errors in buffered use
cases, certainly as seen by the community distros, is the user who
pulls out USB stick while it is in use.  In that case, if there are
dirtied pages in the page cache, the question is what can you do?
Sooner or later the writes will time out, and if you leave the pages
dirty, then it effectively becomes a permanent memory leak.  You can't
unmount the file system --- that requires writing out all of the pages
such that the dirty bit is turned off.  And if you don't clear the
dirty bit on an I/O error, then they can never be cleaned.  You can't
even re-insert the USB stick; the re-inserted USB stick will get a new
block device.  Worse, when the USB stick was pulled, it will have
suffered a power drop, and see above about what could happen after a
power drop for non-power fail certified flash devices --- it goes
double for the cheap sh*t USB sticks found in the checkout aisle of
Micro Center.

So this is the explanation for why Linux handles I/O errors by
clearing the dirty bit after reporting the error up to user space.
And why there is not eagerness to solve the problem simply by "don't
clear the dirty bit".  For every one Postgres installation that might
have a better recover after an I/O error, there's probably a thousand
clueless Fedora and Ubuntu users who will have a much worse user
experience after a USB stick pull happens.

I can think of things that could be done --- for example, it could be
switchable on a per-block device basis (or maybe a per-mount basis)
whether or not the dirty bit gets cleared after the error is reported
to userspace.  And perhaps there could be a new unmount flag that
causes all dirty pages to be wiped out, which could be used to recover
after a permanent loss of the block device.  But the question is who
is going to invest the time to make these changes?  If there is a
company who is willing to pay to comission this work, it's almost
certainly soluble.  Or if a company which has a kernel on staff is
willing to direct an engineer to work on it, it certainly could be
solved.  But again, of the companies who have client code where we
care about robustness and proper handling of failed disk drives, and
which have a kernel team on staff, pretty much all of the ones I can
think of (e.g., Oracle, Google, etc.) use O_DIRECT and they don't try
to make buffered writes and error reporting via fsync(2) work well.

In general these companies want low-level control over buffer cache
eviction algorithms, which drives them towards the design decision of
effectively implementing the page cache in userspace, and using
O_DIRECT reads/writes.

If you are aware of a company who is willing to pay to have a new
kernel feature implemented to meet your needs, we might be able to
refer you to a company or a consultant who might be able to do that
work.  Let me know off-line if that's the case...

- Ted
"""

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  

Re: Reopen logfile on SIGHUP

[Joshua D. Drake]

On 04/10/2018 12:17 PM, Tom Lane wrote:

Robert Haas  writes:

On Tue, Feb 27, 2018 at 6:12 PM, Tom Lane  wrote:

IOW, I think a fair response to this is "if you're using logrotate with
Postgres, you're doing it wrong".



Well, the original post says that this is how the PGDG RPMs are doing
it on Debian/Ubuntu.  I wonder if that's due to some Debian/Ubuntu
policy or just a preference on the part of whoever did the packaging
work.  Anyway it's a little hard to argue that the configuration is
insane when we're shipping it.


We, as in the core project, are not shipping it.


Well, yes we are at least from an external perception problem. The name 
says it all, PGDG RPMs. They are either the official PostgreSQL.Org RPMs 
or they aren't. If they aren't they shouldn't be called PGDG RPMs nor 
should they be available from yum.postgresql.org and apt.postgresql.org 
respectively.


Note: I am not advocating the removal of those packages. I am advocating 
that the core project of PostgreSQL.Org in fact does ship those packages 
and that is how people see it outside of our email silo.


JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

[Joshua D. Drake]

-hackers,

The thread is picking up over on the ext4 list. They don't update their 
archives as often as we do, so I can't link to the discussion. What 
would be the preferred method of sharing the info?


Thanks,

JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

[Joshua D. Drake]

On 04/10/2018 12:51 PM, Joshua D. Drake wrote:

-hackers,

The thread is picking up over on the ext4 list. They don't update their 
archives as often as we do, so I can't link to the discussion. What 
would be the preferred method of sharing the info?


Thanks to Anthony for this link:

http://lists.openwall.net/linux-ext4/2018/04/10/33

It isn't quite real time but it keeps things close enough.

jD




Thanks,

JD





--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Creation of wiki page for open items of v11

[Joshua D. Drake]

On 04/11/2018 10:06 AM, Alvaro Herrera wrote:

Andres Freund wrote:

On 2018-04-11 13:54:34 -0300, Alvaro Herrera wrote:

The other proposal was that we could have a simple web app to track open
items.  After all, we now know what we need from it.  A wiki page seems
more laborious.  (The commitfest app also sprung from a wiki page.)


Growing a number of non-issue issue trackers...


Yes, because the generic ones are confusing, hard to search, easy to
misuse, easy for things to get lost, easy for dupes to crop up --- easy
for things go wrong all over the place.


Just like now except at least with an issue tracker it is easy to move 
items from one place to another, assign ownership and track what is 
actually going on.




The dedicated apps have a very limited purpose and work the way we want,
avoiding all these problems: in essence, they are but a glorified


No, they work the way you want which in turn creates an ever higher 
barrier of entry for new community members.



We've had wiki pages for open items for 10 years now.  It's gotten
boring and tiresome.
https://wiki.postgresql.org/wiki/Category:Open_Items


At least with an issue tracker it would be easy to see and know what is 
going on there.


jD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Cleanup of intro.sgml

[Joshua D. Drake]

-hackers,

I went through and made some readability and modernization of the 
intro.sgml today. Patch attached.


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
Postgres centered full stack support, consulting and development.
Advocate: @amplifypostgres || Get help: https://commandprompt.com/
* Unless otherwise stated, opinions are my own.   *

diff --git a/doc/src/sgml/intro.sgml b/doc/src/sgml/intro.sgml
index 3038826311..7b23a8a918 100644
--- a/doc/src/sgml/intro.sgml
+++ b/doc/src/sgml/intro.sgml
@@ -4,78 +4,77 @@
  Preface
 
  
-  This book is the official documentation of
+  This is the official documentation of
   PostgreSQL.  It has been written by the
-  PostgreSQL developers and other
-  volunteers in parallel to the development of the
-  PostgreSQL software.  It describes all
-  the functionality that the current version of
-  PostgreSQL officially supports.
+  PostgreSQL community in parallel with the 
+   development of the PostgreSQL database.  
+   It describes the functionality of current version of
+  PostgreSQL.
  
-
+	
  
-  To make the large amount of information about
-  PostgreSQL manageable, this book has been
-  organized in several parts.  Each part is targeted at a different
-  class of users, or at users in different stages of their
+  This book has been broken up in to categories to better manage
+  the large amount of information about
+  PostgreSQL.  Each part is targeted at 
+  a different category of user, or at users in different stages of their
   PostgreSQL experience:
 
   

 
   is an informal introduction for new users.
+  It includes information on topics such as installation, architecture
+  and how to create your first database.
 

 

 
   documents the SQL query
- language environment, including data types and functions, as well
- as user-level performance tuning.  Every
- PostgreSQL user should read this.
+ language, including data types and functions. It also includes 
+ user-level performance tuning hints. All
+ PostgreSQL users should read this.
 

 

 
   describes the installation and
- administration of the server.  Everyone who runs a
- PostgreSQL server, be it for private
- use or for others, should read this part.
+ administration of the PostgreSQL server.  
+ Everyone who administrates a
+ PostgreSQL server, should read this part.
 

 

 
-  describes the programming
- interfaces for PostgreSQL client
- programs.
+  describes the C based programming
+ interfaces for PostgreSQL. Topics such as
+ Python or Go are not discussed in this section. 
 

 
 

 
-  contains information for
- advanced users about the extensibility capabilities of the
- server.  Topics include user-defined data types and
- functions.
+  contains information on
+ the extensibility of PostgreSQL.
+ server.  Topics include user-defined data types, procedural languages,
+ and triggers.
 

 

 
-  contains reference information about
- SQL commands, client and server programs.  This part supports
- the other parts with structured information sorted by command or
- program.
+  contains reference information including
+ SQL commands, client applications and server applications.  
 

 

 
   contains assorted information that might be of
- use to PostgreSQL developers.
+ use to PostgreSQL internals developers.
 

   
@@ -120,6 +119,29 @@
 

 
+   PostgreSQL also supports a comprehensive array of Enterprise level
+   features:
+
+   
+
+ Unstructured data via JSON
+
+ Binary and Logical Replication
+
+
+ Hot Backups
+
+
+ Partioniing
+
+
+ Materialized Views
+
+
+ Procedures
+
+   
+
Also, PostgreSQL can be extended by the
user in many ways, for example by adding new
 
@@ -146,8 +168,8 @@
   
 
   
-   And because of the liberal license,
-   PostgreSQL can be used, modified, and
+   The liberal license allows
+   PostgreSQL to be used, modified, and
distributed by anyone free of charge for any purpose, be it
private, commercial, or academic.
   

Re: Cleanup of intro.sgml

[Joshua D. Drake]

On 8/5/19 1:13 PM, Chapman Flack wrote:

On 8/5/19 3:20 PM, Joshua D. Drake wrote:

intro.sgml today. Patch attached.

Things I noticed quickly:

broken up in to categoriess/in to/into/


Got it, I can make that change.



Unstructured data via JSON(or XML ?)


On this one, there is a lot of argument about whether XML is structured 
or not. I do agree that adding XML support would be good though as many 
people think that JSON rules the world but the old money companies are 
still using XML.




s/Partioniing/Partitioning/


Thanks for the catch. I will make the change.

JD




Regards,
-Chap




--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
Postgres centered full stack support, consulting and development.
Advocate: @amplifypostgres || Get help: https://commandprompt.com/
* Unless otherwise stated, opinions are my own.   *

Re: Cleanup of intro.sgml

[Joshua D. Drake]

Rev 2 attached.


Added:

SQL/JSON

SQL/XML

Fixed spelling mistakes

Fixed a missing closing tag.




--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
Postgres centered full stack support, consulting and development.
Advocate: @amplifypostgres || Get help: https://commandprompt.com/
* Unless otherwise stated, opinions are my own.   *

diff --git a/doc/src/sgml/intro.sgml b/doc/src/sgml/intro.sgml
index 3038826311..2a05985bb3 100644
--- a/doc/src/sgml/intro.sgml
+++ b/doc/src/sgml/intro.sgml
@@ -4,78 +4,77 @@
  Preface
 
  
-  This book is the official documentation of
+  This is the official documentation of
   PostgreSQL.  It has been written by the
-  PostgreSQL developers and other
-  volunteers in parallel to the development of the
-  PostgreSQL software.  It describes all
-  the functionality that the current version of
-  PostgreSQL officially supports.
+  PostgreSQL community in parallel with the 
+   development of the PostgreSQL database.  
+   It describes the functionality of current version of
+  PostgreSQL.
  
-
+	
  
-  To make the large amount of information about
-  PostgreSQL manageable, this book has been
-  organized in several parts.  Each part is targeted at a different
-  class of users, or at users in different stages of their
+  This book has been broken up into categories to better manage
+  the large amount of information about
+  PostgreSQL.  Each part is targeted at 
+  a different category of user, or at users in different stages of their
   PostgreSQL experience:
 
   

 
   is an informal introduction for new users.
+  It includes information on topics such as installation, architecture
+  and how to create your first database.
 

 

 
   documents the SQL query
- language environment, including data types and functions, as well
- as user-level performance tuning.  Every
- PostgreSQL user should read this.
+ language, including data types and functions. It also includes 
+ user-level performance tuning hints. All
+ PostgreSQL users should read this.
 

 

 
   describes the installation and
- administration of the server.  Everyone who runs a
- PostgreSQL server, be it for private
- use or for others, should read this part.
+ administration of the PostgreSQL server.  
+ Everyone who administrates a
+ PostgreSQL server, should read this part.
 

 

 
-  describes the programming
- interfaces for PostgreSQL client
- programs.
+  describes the C based programming
+ interfaces for PostgreSQL. Topics such as
+ Python or Go are not discussed in this section. 
 

 
 

 
-  contains information for
- advanced users about the extensibility capabilities of the
- server.  Topics include user-defined data types and
- functions.
+  contains information on
+ the extensibility of PostgreSQL.
+ server.  Topics include user-defined data types, procedural languages,
+ and triggers.
 

 

 
-  contains reference information about
- SQL commands, client and server programs.  This part supports
- the other parts with structured information sorted by command or
- program.
+  contains reference information including
+ SQL commands, client applications and server applications.  
 

 

 
   contains assorted information that might be of
- use to PostgreSQL developers.
+ use to PostgreSQL internals developers.
 

   
@@ -120,6 +119,33 @@
 

 
+   PostgreSQL also supports a comprehensive array of Enterprise level
+   features:
+
+   
+
+ Unstructured data via JSON and SQL/JSON
+
+
+ SQL/XML
+
+
+ Binary and Logical Replication
+
+
+ Hot Backups
+
+
+ Partitioning
+
+
+ Materialized Views
+
+
+ Procedures
+
+   
+
Also, PostgreSQL can be extended by the
user in many ways, for example by adding new
 
@@ -146,8 +172,8 @@
   
 
   
-   And because of the liberal license,
-   PostgreSQL can be used, modified, and
+   The liberal license allows
+   PostgreSQL to be used, modified, and
distributed by anyone free of charge for any purpose, be it
private, commercial, or academic.
   

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

[Joshua D. Drake]

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

Re: CFM for January commitfest?

[Joshua D. Drake]

On 01/02/2018 05:06 PM, Ryan Murphy wrote:


Now that the January fest has nominally started, we need somebody
to act as CF manager.  Any volunteers?


What are the responsibilities?  How many hours per week are typically 
given for this role?


This might help:

https://wiki.postgresql.org/wiki/CommitFest_Checklist

JD



Best,
Ryan



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

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

[Joshua D. Drake]

On 01/03/2018 07:49 AM, Jeff Janes wrote:


O.k. what does it tell us though? Is it a resource issue? Is it a 
barrier of entry issue?


Lack of ownership/ruthlessness.  While I can edit it to remove items 
that don't seem desirable (or comprehensible, or whatever) I'm not 
likely to do so, unless I'm the one who added it in the first place. 
Maybe it made more sense or was more important to someone else, like 
the person who added it.  At one time many of the items didn't have 
links to the relevant email discussions (or more detailed wiki pages 
of their own), so those would have been good targets for purging but I 
think Bruce hunted down and added links for most of them.


Another problem is that wikimedia doesn't have a "git blame" like 
feature.  I've been frustrated before trying to figure out who added 
an item and when, so I could research it a bit more.


It seems to me that this is rather easily solved with an issue tracker 
or enhancement to the commitfest app. With an issue tracker we just set 
a status of "wishlist" and then set up a simple public report that 
allows people to see wishlist items. Plus we get the benefit of an issue 
tracker.


The commitfest app could work in a similar fashion. The commitfest app 
could be replaced by an issue tracker to, but we as a community tend to 
suffer from NIH, so it might be less of a battle to just adjust the 
commitfest app.


Either one of these options allows to consolidation of tools as well as 
information portals both of which would help the community.


Heck we could go a step further and actually allow (authenticated) 
voting on various features. This would provide the community the ability 
to more easily interact with -hackers on various features that would be 
desirable. (I am not suggesting that the voting dictate our direction 
but to allow feedback on new and interesting ideas that show community 
support)


Of course that takes resources. There have been discussions of getting 
an issue tracker in the past but the priority has appeared to drop to 
the wayside.


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

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

[Joshua D. Drake]

On 01/03/2018 09:00 AM, Alvaro Herrera wrote:

Joshua D. Drake wrote:

There's already https://postgresql.uservoice.com/forums/21853-general
which seems to work pretty well.  Here's the list of completed items:
https://postgresql.uservoice.com/forums/21853-general?status_id=124172


Well that's interesting, I have never heard of that. Is that something 
we want to promote more?


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

Re: Finalizing logical replication limitations as well as potential features

[Joshua D. Drake]

On 12/21/2017 06:15 PM, Craig Ringer wrote:
On 22 December 2017 at 05:24, Joshua D. Drake <mailto:j...@commandprompt.com>> wrote:


-Hackers,


Lastly, I noted that a full sync of a replication set is performed
by a COPY, this is fine for small sets but if we have a large data
set that may take some time it may be a problem with overall
performance and maintenance. We may want to see if we can do an
initial sync incrementally (optional) via a cursor (?) and queue
all changed rows until the sync completes?


I'm not sure I understand this.

The COPY is streamed from source to destination, IIRC it's not 
buffering to a tempfile or anything. So I fail to see what using a 
cursor would gain you. No matter whether you're using a cursor, a 
COPY, or something else, you have to hold down a specific xmin and 
work with the same snapshot for the whole sync operation. If you 
instead did something like incremental SELECTs, each with a new 
xmin+snapshot, across ranges of a PK your copy would see changes from 
different points in time depending on where in the copy it was up to, 
and you'd get an inconsistent view. It could possibly be worked around 
with some tricky key-range-based filtering of the applied 
change-stream if you were willing to require that no PK updates may 
occur, but it'd probably be bug city. It's hard enough to get sync 
correct at all.


I am not sure that this is entirely true. Granted it is easiest just to 
do everything within a snapshot but we shouldn't have to. It would be 
possible to perform incremental (even parallel) syncs whether copy or 
other mechanism. We would have to track changes to the table as we sync 
but that isn't impossible either (especially if we have a PK). I would 
think that this would only be valid within async replication but it is 
possible. We just queue/audit the changes as they happen and sync up the 
changes after the initial sync completes. Multi-phase sync baby :D


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

Re: Finalizing logical replication limitations as well as potential features

[Joshua D. Drake]

On 01/04/2018 01:26 PM, Alvaro Herrera wrote:

Joshua D. Drake wrote:


We just queue/audit the changes as they happen and sync up the changes
after the initial sync completes.

This already happens.  There is an initial sync, and there's logical
decoding that queues any changes that exist "after" the sync's snapshot.

What you seem to want is to have multiple processes doing the initial
COPY in parallel -- each doing one fraction of the table.  Of course,
they would have to use the same snapshot.  That would make sense only
if the COPY itself is the bottleneck and not the network, or the I/O
speed of the origin server.  This doesn't sound a common scenario to me.


Not quite but close. My thought process is that we don't want to sync 
within a single snapshot a 100-500mil row table (or worse). Unless I am 
missing something there, that has the potential to be a very long 
running transaction especially if we are syncing more than one relation.


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

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

[Joshua D. Drake]

On 01/10/2018 09:22 AM, Tom Lane wrote:

... but I don't think it fixes that, because you couldn't send this new
request without making an assumption about the server version being
new enough to support it.  My entire beef with making server_version_num
be GUC_REPORT is that it would encourage people to write client code that
fails outright against older servers.  I'm afraid what you are suggesting
will be an equally attractive nuisance.


It seems to me that is not our problem. Why do we care if some developer 
says, "I only work with 9.6"? If I am understanding your complaint.


JD



regards, tom lane



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

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

[Joshua D. Drake]

On 01/10/2018 09:36 AM, Tom Lane wrote:

It seems to me that is not our problem. Why do we care if some developer

says, "I only work with 9.6"? If I am understanding your complaint.

I don't care at all if J. Random Developer's homegrown code only works
with the PG version he's using.  The concern I have is that unwanted
server version dependencies will sneak into widely used code, like
psql, or libpq, or jdbc.  Or another way of putting it: Robert's proposal
is a protocol version break, just like most stuff at this level.  Trying
to pretend it isn't doesn't make it not one.


That makes sense, thanks for clarifying.

JD


regards, tom lane



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

Re: documentation is now XML

[Joshua D. Drake]

On 01/23/2018 04:59 PM, Bruce Momjian wrote:

On Thu, Nov 23, 2017 at 03:39:24PM -0500, Tom Lane wrote:

Also, we're way overdue for getting out from under the creaky TeX-based
toolchain for producing PDFs.  Every time we make releases, I worry
whether we're going to get blindsided by its bugs with hotlinks that get
split across pages, since page breaks tend to vary in position depending

I am coming in late here, but I am not aware of any open source
professional typesetting software that has output quality as good as
TeX.


It has been ages since I have dealt with this but as I recall but moving 
to XML we can use XSLT to push to PDF. The resulting product is as good 
(if not better in some ways) to old fashion SGML->TEX->PDF.

Here are a couple of articles:

https://www.codeproject.com/Articles/595650/Creating-PDF-documents-from-XML
https://www.ibm.com/developerworks/library/x-xmlxsltpdf/index.html

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

Re: Better Upgrades

[Joshua D. Drake]

On 02/05/2018 04:09 PM, David Fetter wrote:

Does this seem worth coding up in its current form?


No. The pg_upgrade utility is awesome and I have commended Bruce on 
multiple occasions about his work with it. That being said, the
"solution" is to support in-place upgrades and our work should be toward 
that. The idea that we can support and upgrade to the catalog plus 
backward and forward support for changes to the page files (upgrade the 
page files as they are accessed/written to) is a much longer, more 
mature and reasonable approach to this problem[1].


JD

1. Props to Theo for bringing this up about a decade ago.



Best,
David.



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

replication_slots usability issue

[Joshua D. Drake]

-Hackers,


Working on 9.6 today (unsure if fixed in newer versions). Had an issue 
where the wal was 280G despite max_wal_size being 8G. Found out there 
were stale replication slots from a recent base backup. I went to drop 
the replication slots and found that since the wal_level was set to 
minimal vs replica or higher, I couldn't drop the replication slot. 
Clearly that makes sense for creating a replication slot but it seems 
like an artificial limitation for dropping them.



JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: replication_slots usability issue

[Joshua D. Drake]

On 10/29/18 11:26 AM, Andres Freund wrote:


On October 29, 2018 1:31:56 PM EDT, "Joshua D. Drake"  
wrote:

-Hackers,


Working on 9.6 today (unsure if fixed in newer versions). Had an issue
where the wal was 280G despite max_wal_size being 8G. Found out there
were stale replication slots from a recent base backup. I went to drop
the replication slots and found that since the wal_level was set to
minimal vs replica or higher, I couldn't drop the replication slot.
Clearly that makes sense for creating a replication slot but it seems
like an artificial limitation for dropping them.

Uh, huh? How did you manage to start a server with existing slots with that 
configuration? It should have errored out at start...


Well, this is the recovery.conf:

standby_mode = 'on'
recovery_target = 'immediate'
primary_slot_name = 'testing_db01'
primary_conninfo = 'user=replication 
passfile=/var/lib/postgresql/.pgpass host=db01 port=5432 sslmode=prefer 
sslcompression=1 krbsrvname=postgres target_session_attrs=any'


recovery_target_action = 'promote'

The machine came up clean and the only reason I noticed the problem is 
that it ran out of disk space. I cleared enough disk space to get it to 
come up again and noticed that there were replication slots that were 
identical to the primary.



JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: replication_slots usability issue

[Joshua D. Drake]

On 10/30/18 10:52 AM, Andres Freund wrote:

On 2018-10-30 11:51:09 +0900, Michael Paquier wrote:

On Mon, Oct 29, 2018 at 12:13:04PM -0700, Andres Freund wrote:

I don't think this quite is the problem. ISTM the issue is rather that
StartupReplicationSlots() *needs* to check whether wal_level > minimal,
and doesn't. So you can create a slot, shutdown, change wal_level,
startup. A slot exists but won't work correctly.

It seems to me that what we are looking for is just to complain at
startup if we find any slot data and if trying to start up with
wal_level = minimal.

Right, we really should just call CheckSlotRequirements() before doing
so. I'll make it so, once I'm actually awake and had some coffee.


Why not just disable the slot and report an INFO: line?


JD






Er...  At the same time, shouldn't RestoreSlotFromDisk() *not* use PANIC
if more slots are found in pg_replslot than max_replication_slots can
handle.  A FATAL is fine at startup, PANIC blows up a core file, which
is clearly overdoing it if the goal is to give a recommendation at the
end.

I can't get particularly excited about this. I guess we can change it,
but I'd only do so in master.


Greetings,

Andres Freund



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Connection limit doesn't work for superuser

[Joshua D. Drake]

On 11/7/18 10:49 AM, Robert Haas wrote:

On Wed, Nov 7, 2018 at 1:14 PM Tom Lane  wrote:

I think that having superusers be immune to datconnlimit is actually
the right thing; for one reason, because datconnlimit can be set by
database owners, who should not be able to lock superusers out of
their database.

Yeah, that's a reasonable argument, although they'd also be locking
themselves out of the database, and the superuser could undo it by
connecting to some other database.


If people are okay with having rolconnlimit act
differently from datconnlimit in this respect, then I'll withdraw
my objection.

Is there any particular reason why they should be consistent?  It's
not obvious to me, but sometimes I'm dumb.


IMO, super users should only be affected by 
superuser_reserved_connections. Otherwise we are getting into fine grain 
of potential foot guns.



JD








--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: House style for DocBook documentation?

[Joshua D. Drake]

On 1/21/19 8:46 AM, Chapman Flack wrote:

On 01/21/19 09:12, Alvaro Herrera wrote:


(thinks to self half-seriously about an XSL transform for generating
printed output that could preserve link-texted links, add raised numbers,
and produce a numbered URLs section at the back)

Well, if you have the time and inclination, and you think such changes
are improvements, feel free to propose them.  Do keep in mind we have a
number of outputs that would be good to keep consistent.

Well, "consistent" what I was half-thinking about, FSVO "consistent".

For me, if I'm looking at an online document, I would prefer to see
the descriptive text of the link, rather than a long jaggy URL. If I
want to see the URL, I can hover over it, and if I want to go there,
I can click it.

But the point's well taken that in /printed output/, that's of no use.
Which is, in a sense, an inconsistency: in one format, you can follow the
links, while in another, you're out of luck.

Maybe a simpler transform for printed output, rather than collecting
all URLs into one section at the back, would just be to follow any
 that has link text with a  containing the same ulink
without the link text, so it shows the URL, and that would be right at
the bottom of the same 'page'.

That'd be an introductory XSL exercise

In practice, applying such a transform "for printed output" would
probably mean applying it when generating PDF output, which of course
can also be viewed online (and probably most often is, these days).


I don't think that is a good idea. PDFs have had the ability to embed 
hyperlinks under descriptive text for years. If we are going to expand 
links for printed output, we should have a specific build / modification 
to the make file for printed output. I also wonder if we are trying to 
solve the 1% problem here. Who is really going to "print" our docs? If 
they do print the docs, they are likely not going to "type in" a long 
URL. They are going to go online (or to the PDF) and click the link that 
they saw within the printed page.


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn and Network: https://postgresconf.org
***  A fault and talent of mine is to tell it exactly how it is.  ***

Re: House style for DocBook documentation?

[Joshua D. Drake]

On 1/21/19 10:01 AM, Alvaro Herrera wrote:

On 2019-Jan-21, Chapman Flack wrote:


But the point's well taken that in /printed output/, that's of no use.
Which is, in a sense, an inconsistency: in one format, you can follow the
links, while in another, you're out of luck.

Maybe a simpler transform for printed output, rather than collecting
all URLs into one section at the back, would just be to follow any
 that has link text with a  containing the same ulink
without the link text, so it shows the URL, and that would be right at
the bottom of the same 'page'.

Of course, the text would also be clickable, right?  I think putting the
URL in a footnote is good in that case; it works both on screen and on
paper, which should alleviate JD's concerns.


Yeah I could see that. I thought about that but was wondering if it was 
possible to auto cite?


JD





I wouldn't think it important to apply the same treatment when making HTML.

Right, only PDF.



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn and Network: https://postgresconf.org
***  A fault and talent of mine is to tell it exactly how it is.  ***

Re: House style for DocBook documentation?

[Joshua D. Drake]

On 1/21/19 10:11 AM, Chapman Flack wrote:

On 01/21/19 12:07, Joshua D. Drake wrote:

Who is really going to "print" our docs? If they do print the
docs, they are likely not going to "type in" a long URL.

QR code in footnote (ducks and runs).


Funny, although I know why you said that. I don't think it is that bad 
of an idea. Everyone uses QR Codes now. Have a QR code that 
automatically opens a page that lists all the expanded links based on 
the page it is placed?


That is pretty cool but I am not sure if that is something that would 
happen in this round.


JD






--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn and Network: https://postgresconf.org
***  A fault and talent of mine is to tell it exactly how it is.  ***

Re: Thread-unsafe coding in ecpg

[Joshua D. Drake]

On 1/21/19 12:05 PM, Tom Lane wrote:

Andres Freund  writes:

Seems jacana might not have like this change?
https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jacana&dt=2019-01-21%2019%3A01%3A28

Hmm.  So mingw doesn't provide access to _configthreadlocale().
That's unfortunate, at least if we think that mingw is still a viable
production platform, because it means we can't make ecpg thread-safe
on that platform.

Is there a newer version of mingw that does have this functionality?
I'm not sure whether to install a version check or just assume that
it's never there.


Apparently this can be done with thee 64bit version:

https://stackoverflow.com/questions/33647271/how-to-use-configthreadlocale-in-mingw

JD




regards, tom lane



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn and Network: https://postgresconf.org
***  A fault and talent of mine is to tell it exactly how it is.  ***

Re: make install getting slower

[Joshua D. Drake]

On 12/4/18 12:31 PM, Andres Freund wrote:

Hi,

best of three of
time make -s install INSTALL=/bin/true

9.3: 1.53s
9.4: 1.55s
9.5: 1.90s
9.6: 2.10s
10: 2.45s
11: 2.81s
master: 2.99s

Obviously a part of that can be explained by the tree getting bigger,
but that can't be all of it.

It also gets a bit worse if LLVM is enabled (due to the bitcode files
getting installed), but that's "just" 0.3s for me. We should optimize
that for fewer install invocations / using cp, but that seems seperate.

I think this also has gotten more pronounced with newer OS versions, as
the overhead of the spectre/meltdown mitigations have increased the cost
of syscalls and forks.

Obviously this doesn't terribly matter for actual installations, but I
do find it fairly annoying when running regression tests...


Wouldn't using -j option with make help this?


JD




Greetings,

Andres Freund



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Statement-level rollback

[Joshua D. Drake]

On 12/7/18 12:50 PM, Alvaro Herrera wrote:

On 2018-Dec-07, Robert Haas wrote:


More generally, whether or not we should "keep something away from our
users" really depends on how likely the upsides are to occur relative
to the downsides.  We don't try to keep users from running DELETE
because they might delete data they want; that would be nanny-ism.
But we do try to keep them from reading dirty data from an uncommitted
transaction because we can't implement that without a risk of server
crashes, and that's too big a downside to justify the upside.  If we
could do it safely, we might.

 From that point of view, this is doubtless not the worst feature
PostgreSQL will ever have, but it sure ain't the best.

Well, look at this from this point of view: EnterpriseDB implemented
this because of customer demand (presumably).  Fujitsu also implemented
this for customers.  The pgjdbc driver implemented this for its users.
Now 2ndQuadrant also implemented this, and not out of the goodness of
our hearts.  Is there any room to say that there is no customer demand
for this feature?


Amazon also implemented something similar for the database migration 
tool. I am unsure if they do it similarly with the DMS. With the DMT it 
definitely had the XID issue that some are concerned with but I would 
argue that is the cost of doing business.


JD






--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: "interesting" issue with restore from a pg_dump with a database-wide search_path

[Joshua D. Drake]

On 07/06/2018 11:27 AM, Larry Rosenman wrote:

when I pg_dump -Fc the database and then try to restore it after a
create database, I get errors.  To get a clean restare I need to do:


Knowing the errors would be helpful.

jD



---
\set DB `echo ${DB}`
CREATE SCHEMA IF NOT EXISTS postgis;
CREATE SCHEMA IF NOT EXISTS topology;
CREATE SCHEMA IF NOT EXISTS tiger;
SET search_path=public,postgis,tiger,topology;
ALTER DATABASE :DB SET search_path=public,postgis,tiger,topology;
\c
CREATE EXTENSION fuzzystrmatch schema postgis;
-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis schema postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology schema topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
CREATE EXTENSION postgis_sfcgal schema postgis;
-- rule based standardizer
CREATE EXTENSION address_standardizer schema postgis;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us schema postgis;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder schema tiger;
-- routing functionality
CREATE EXTENSION pgrouting schema postgis;
-- spatial foreign data wrappers
CREATE EXTENSION ogr_fdw schema postgis;
-- LIDAR support
CREATE EXTENSION pointcloud schema postgis;
-- LIDAR Point cloud patches to geometry type cases
CREATE EXTENSION pointcloud_postgis schema postgis;

Is the need to do this expected?

This is 10.4 on FreeBSD.





--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: shared-memory based stats collector

[Joshua D. Drake]

On 07/06/2018 11:57 AM, Andres Freund wrote:

On 2018-07-06 14:49:53 -0400, Robert Haas wrote:

I think we also have to ask ourselves in general whether snapshots of
this data are worth what they cost.  I don't think anyone would doubt
that a consistent snapshot of the data is better than an inconsistent
view of the data if the costs were equal.  However, if we can avoid a
huge amount of memory usage and complexity on large systems with
hundreds of backends by ditching the snapshot requirement, then we
should ask ourselves how important we think the snapshot behavior
really is.

Indeed. I don't think it's worthwhile major additional memory or code
complexity in this situation. The likelihood of benefitting from more /
better stats seems far higher than a more accurate view of the stats -
which aren't particularly accurate themselves. They don't even survive
crashes right now, so I don't think the current accuracy is very high.



Will stats, if we move toward the suggested changes be "less" accurate 
than they are now? We already know that stats are generally not accurate 
but they are close enough. If we move toward this change will it still 
be close enough?


JD



Greetings,

Andres Freund



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: shared-memory based stats collector

[Joshua D. Drake]

On 07/06/2018 12:34 PM, Robert Haas wrote:

On Fri, Jul 6, 2018 at 3:02 PM, Joshua D. Drake  wrote:

Will stats, if we move toward the suggested changes be "less" accurate than
they are now? We already know that stats are generally not accurate but they
are close enough. If we move toward this change will it still be close
enough?

There proposed change would have no impact at all on the long-term
accuracy of the statistics.  It would just mean that there would be
race conditions when reading them, so that for example you would be
more likely to see a count of heap scans that doesn't match the count
of index scans, because an update arrives in between when you read the
first value and when you read the second one.  I don't see that
mattering a whole lot, TBH, but maybe I'm missing something.


I agree that it probably isn't a big deal. Generally speaking when we 
look at stats it is to get an "idea" of what is going on. We don't care 
if we are missing an increase/decrease of 20 of any particular value 
within stats. Based on this and what Andres said, it seems like a net 
win to me.


JD





--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: patch to allow disable of WAL recycling

[Joshua D. Drake]

On 07/10/2018 01:15 PM, Jerry Jelinek wrote:
Thanks to everyone who took the time to look at the patch and send me 
feedback.  I'm happy to work on improving the documentation of this 
new tunable to clarify when it should be used and the implications. 
I'm trying to understand more specifically what else needs to be done 
next. To summarize, I think the following general concerns were 
brought up.


For #6, there is no feasible way for us to recreate our workload on 
other operating systems or filesystems. Can anyone expand on what 
performance data is needed?




I think a simple way to prove this would be to run BenchmarkSQL against 
PostgreSQL in a default configuration with pg_xlog/pg_wal on a 
filesystem that is COW (zfs) and then run another test where 
pg_xlog/pg_wal is patched with your patch and new behavior and then run 
the test again. BenchmarkSQL is a more thorough benchmarking tool that 
something like pg_bench and is very easy to setup.


The reason you would use a default configuration is because it will 
cause a huge amount of wal churn, although a test with a proper wal 
configuration would also be good.


Thanks,

JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Have an encrypted pgpass file

[Joshua D. Drake]

On 07/18/2018 04:25 PM, Tom Lane wrote:

Alvaro Herrera  writes:

Seems to me that passing %-specifiers to the command would make it more
useful (%u for "user", "host" etc) -- your command could refuse to give
you a password for the superuser account for instance but grant one for
a read-only user.

It would also provide a *very* fertile source of shell-script-injection
vulnerabilities.  (Whaddya mean, you tried to use a user name with a
quote mark in it?)

This is exactly the kind of area in which I'm concerned for the
possibility of sloppily-written scripts being a net negative for
security.


Although I appreciate the concern, can we not worried about this? Your 
argument basically boils down to: Dumb will be Dumb. That will not 
change no matter what we do as is obvious by the number of people STILL 
using postgres as their connected web app user. The usability of this 
feature if fleshed out correctly is pretty large.


JD


regards, tom lane



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: How can we submit code patches that implement our (pending) patents?

[Joshua D. Drake]

On 07/23/2018 12:06 PM, Bruce Momjian wrote:

So, is it FUD?  The core needs paid-for legal advice, not speculation.

I'm quite certain that a software license can make a patent grant to the
satisfaction of many open source communities, and almost certainly to
the satisfaction of the PG community.  But it will take an IP lawyer to
review or write such a license.

And is the payback worth it?  Many don't think so.


Although Nico is correct, I also think we need to consider what the 
community wants here. Historically, we have always explicitly avoided 
anything to do with patents to the point where some hackers won't even 
read white papers on patented methods. I do think there is a definite 
technological advantage for PostgreSQL if there was a license that core 
could accept that was patent friendly but frankly, I don't think that 
core or the community has the desire to work through the cost of doing so.


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Deprecating, and scheduling removal of, pg_dump's tar format.

[Joshua D. Drake]

On 07/26/2018 07:41 PM, Christophe Pettus wrote:

On Jul 26, 2018, at 19:35, Peter Geoghegan  wrote:

Why, specifically, would it make them unhappy?

Forensic and archive backups in .tar format (which I know of users doing) would 
require a two-step restore process on newer versions.


I am a +1 for removing the format, though I would suggest we leave it as 
a restore option (pg_restore) for at least two more major releases. It 
is nice to get rid of cruft.


JD




--
-- Christophe Pettus
x...@thebuild.com




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

Re: Deprecating, and scheduling removal of, pg_dump's tar format.

[Joshua D. Drake]

On 07/27/2018 10:05 AM, Andres Freund wrote:



Yeah.  I don't mind removing really marginal features to ease
maintenance, but I'm not sure that this one is all that marginal or
that we'd save that much maintenance by eliminating it.

My point is more that it forces users to make choices whenever they use
pg_dump. And the tar format has plenty downsides that aren't immediately
apparent.  By keeping something with only a small upside around, we
force users to waste time.


Correct. Sometimes it is best to limit choices, someone may chose tar 
because it is a command they have used but not fully understand what 
that means within the context of PostgreSQL. Then they are going to have 
something happen, they will ask for help either on the lists or from a 
consulting firm and the first they either will say is, "Don't use the 
tar format" or at least, "You should be using one of the other formats".


Why invite the overhead?

JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Online enabling of checksums

[Joshua D. Drake]

On 07/31/2018 12:45 PM, Bruce Momjian wrote:



Hi!,

Thanks for reviewing, I’ve updated the patch with the above mentioned incorrect
linkends as well as fixed the comments you made in a previous review.

The CF-builder-bot is red, but it’s because it’s trying to apply the already
committed patch which is in the attached datallowconn thread.

I think checksumhelper_cost_delay should be checksum_helper_cost_delay.
 ^

Is "helper" the right word?


Based on other terminology within the postgresql.conf should it be 
"checksum_worker_cost_delay"?


JD





--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Online enabling of checksums

[Joshua D. Drake]

On 08/01/2018 09:20 AM, Alvaro Herrera wrote:



my problem is that I think the "restart" approach is just using the
entirely wrong hammer to solve the problem at hand.  At the very least
it's very problematic in respect to replicas, which need to know about
the setting too, and can have similar problems the restart on the
primary is supposed to prevent.

If we define "restart" to mean taking all the servers down
simultaneously, that can be planned.


People in mission critical environments do not "restart all servers". 
They fail over to a secondary to do maintenance on a primary. When you 
have a system where you literally lose thousands of dollars every minute 
the database is down you can't do what you are proposing. When you have 
a system that if the database is down for longer than X minutes, you 
actually lose a whole day because all of the fabricators have to 
revalidate before they begin work, you can't do that. Granted that is 
not the majority (which you mention) but let's not forget them.


The one place where a restart does happen and will continue to happen 
for around 5 (3 if you incorporate pg_logical and 9.6) more years is 
upgrades. Although we have logical replication for upgrades now, we are 
5 years away from the majority of users being on a version of PostgreSQL 
that supports logical replication for upgrades. So, I can see an 
argument for an incremental approach because people could enable 
checksums as part of their upgrade restart.



For users that cannot do that,
that's too bad, they'll have to wait to the next release in order to
enable checksums (assuming they fund the necessary development).  But


I have to say, as a proponent of funded development for longer than most 
I like to see this refreshing take on the fact that this all does take 
money.



there are many systems where it *is* possible to take everything down
for five seconds, then back up.  They can definitely take advantage of
checksummed data.


This is a good point.


Currently, the only way to enable checksums is to initdb and create a
new copy of the data from a logical backup, which could take hours or
even days if data is large, or use logical replication.


Originally, I was going to -1 how this is being implemented. I too wish 
we had the "ALTER DATABASE ENABLE CHECKSUM" or equivalent without a 
restart. However, being able to just restart is a huge step forward from 
what we have now.


Lastly, I think Alvaro has a point with the incremental development and 
I also think some others on this thread need to, "show me the patch" 
instead of being armchair directors of development.


JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

GSOC 2018

[Joshua D. Drake]

-hackers,

Who is coordinating GSOC this year?


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

Re: PG 11 feature count

[Joshua D. Drake]

On 05/17/2018 04:29 PM, Bruce Momjian wrote:

I regularly track the number of items documented in each major release.
I use the attached script.  You might be surprised to learn that PG 11
has the lowest feature count of any release back through 7.4:

7.4 280
8.0 238
8.1 187
8.2 230
8.3 237
8.4 330
9.0 252
9.1 213
9.2 250
9.3 187
9.4 217
9.5 200
9.6 220
10  194
11  167



Our goal should be less features so this is awesome. There is a point 
where we should want to reach where we are refining only limitations to 
perfection, not continuing to create the hot new thing. That is maturity 
in the product.


Congrats to everyone for such a fantastic looking release,

JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Joshua D. Drake]

On 06/03/2018 11:29 AM, Tom Lane wrote:


https://wiki.postgresql.org/wiki/Code_of_Conduct

We are now asking for a final round of community comments.
Please send any public comments to the pgsql-general list (only).
If you wish to make a private comment, you may send it to
c...@postgresql.org.


Thanks for all the efforts on this. It is nice to see us explicitly 
moving toward modernizing our community policies and creating an openly 
inclusive community. There are a couple of notes I have about this:


I think we need language that explicitly states that this is about 
participation within postgresql.org only. It is not postgresql.org's 
mission or purpose to police actions outside of their domain. The 
following minor modification would work:


"To that end, we have established this Code of Conduct for community 
interaction and participation within the Postgresql.org project."


There is no language that protects different political or social views. 
In today's climate it is important especially as we are a worldwide 
project. Something simple like the following should be enough:


"Examples of personal characteristics include, but are not limited to 
age, race, national origin or ancestry, religion, political affiliation, 
social class, gender, or sexual orientation."


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Joshua D. Drake]

On 06/03/2018 04:08 PM, Gavin Flower wrote:


My comments:

1) Reiterate my contention that this is a solution is search of 
problem. Still it looks like it is going forward, so see below.


2) "... engaging in behavior that may bring the PostgreSQL project 
into disrepute, ..."
This to me is overly broad and pulls in actions that may happen 
outside the community. Those if they are actually an issue should be 
handled where they occur not here.


This is good point. There are those who would think that one has 
performed an action that brings the project into disrepute and a similar 
sized bias that suggests that in fact that isn't the case. This based on 
the CoC would be judged by the CoC committee.


It is my hope that PostgreSQL.Org -Core chooses members for that 
committee that are exceedingly diverse otherwise it is just an echo 
chamber for a single ideology and that will destroy this community.




3) "... members must be sensitive to conduct that may be considered 
offensive by fellow members and must refrain from engaging in such 
conduct. "


Again overly broad, especially given the hypersensitivity of people 
these days. I have found that it is enough to disagree with someone to 
have it called offensive. This section should be removed as proscribed 
behavior is called out in detail in the paragraphs above it.


"considered offensive by fellow members"

Is definitely too broad. The problem comes in here:

I might possibly say that "I'm the master in this area" when talking to 
someone on a technical subject.  In the sense that I'm better at that 
particular skill, but some hypersensitive American could get their 
knickers in a twist (notice, that in this context, no gender is implied 
-- also in using that that expression "get their knickers in a twist" 
could offend some snowflake) claiming that I'm suggesting that whoever 


"snowflake", I find that term hilarious others find it highly offensive. 
Which is correct?


I'm talking to is my slave!  I heard of an American university that 
doesn't want people to use the term master, like in an MSc, because of 
the history of slavery.


The PostgreSQL project already has this problem, note we don't use the 
terms Master and Slave in reference to replication anymore.




I've used the expressions "sacrifice a willing virgin" and "offering my 
first born to the gods" as ways to ensure success of resolving a 
technical issue.  The people I say that to, know what I mean -- and they 
implicitly know that I'm not seriously suggesting such conduct.  Yet, if 
I wrote that publicly, it is conceivable that someone might object!


Yes and that is a problem. We need to have some simple barrier of 
acceptance that we are all adults here (or should act like adults). 
Knowing your audience is important.


Consider a past advertising campaign in Australia to sell government 
Bonds.  They used two very common hand gestures that are very 
Australian.  Bond sales dropped.  On investigation, they found the bonds 
were mainly bought by old Greek people, who found the gestures obscene. 
The gestures?  Thumbs up, and the okay gesture formed by touching the 
thumb with the next finger -- nothing sexually suggestive to most 
Australians, but traditional Greeks found them offensive.


Using Australia as an example, my understanding is that the word c**t is 
part of nomenclature but in the states the word is taboo and highly 
frowned upon.



Be very careful in attempting to codify 'correct' behaviour!



Correct. I think one way to look at all of this is, "if you wouldn't say 
it to your boss or a client don't say it here". That too has problems 
but generally speaking I think it keeps the restrictions rational.


JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Joshua D. Drake]

On 06/05/2018 07:45 AM, Chris Travers wrote:


It is my hope that PostgreSQL.Org -Core chooses members for that
committee that are exceedingly diverse otherwise it is just an echo
chamber for a single ideology and that will destroy this community.


If I may suggest:  The committee should be international as well and 
include people from around the world.  The last thing we want is for it 
to be dominated by people from one particular cultural viewpoint.




+1



"considered offensive by fellow members"

Is definitely too broad. The problem comes in here:

I might possibly say that "I'm the master in this area" when
talking to someone on a technical subject.  In the sense that
I'm better at that particular skill, but some hypersensitive
American could get their knickers in a twist (notice, that in
this context, no gender is implied -- also in using that that
expression "get their knickers in a twist" could offend some
snowflake) claiming that I'm suggesting that whoever


"snowflake", I find that term hilarious others find it highly
offensive. Which is correct?


I agree with both concerns in the above exchange.

This is an economic common project.  The goal should be for people to 
come together and act civilly.  Waging culture war using the code of 
conduct itself should be a violation of the code of conduct and this 
goes on *all* (not just one or two) sides.




[snip]



Yes and that is a problem. We need to have some simple barrier of
acceptance that we are all adults here (or should act like adults).
Knowing your audience is important.


I would point out also that the PostgreSQL community is nice and 
mature.  At PGConf US I saw what appeared to be two individuals with red 
MAGA hats.  And yet everyone managed to be civil.  We manage to do 
better than the US does on the whole in this regard and we should be 
proud of ourselves.


To be fair, those were South Africans but yes, nobody gave them any 
public grief as far as I know.




Correct. I think one way to look at all of this is, "if you wouldn't
say it to your boss or a client don't say it here". That too has
problems but generally speaking I think it keeps the restrictions
rational.


I will post a more specific set of thoughts here but in general I think 
the presumption ought to be that people are trying to work together.  
Misunderstanding can happen.  But let's try to act in a collegial and 
generally respectful way around eachother.


+1

JD




--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: libpq compression

[Joshua D. Drake]

On 06/06/2018 10:20 AM, Konstantin Knizhnik wrote:

Well, psql really allows to specify complete connection string with -d 
options (although it is not mentioned in help).
But still I think that it is inconvenient to require user to write 
complete connection string to be able to specify compression option, 
while everybody prefer to use -h, -U, -p  options to specify 
correspondent components of connection string.


From a barrier to entry and simplicity sake I agree. We should have a 
standard flag.


JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: I'd like to discuss scaleout at PGCon

[Joshua D. Drake]

On 06/22/2018 11:28 AM, Merlin Moncure wrote:


Key features from my perspective:
*) fdw in parallel.  how do i do it today? ghetto implemented parallel
queries with asynchronous dblink

*) column store


Although not in core, we do have this as an extension through Citus 
don't we?


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Do we accept doc changes to back branches?

[Joshua D. Drake]

-hackers,


I don't recall, do we allow non-bug fix (or what constitutes a bug) for 
back branches with docs? I have been reviewing the logical replication 
docs and they could use some love but I need to know which branch I 
should start work on.



Thanks,


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.org
* Unless otherwise stated, opinions are my own.   *

Logical replication without a Primary Key

[Joshua D. Drake]

-Hackers,

In the docs it says:

"
If the table does not have any suitable key, then it can be set to 
replica identity“full”, which means the entire row becomes the key.


"

How does that work? Is it using one of the hidden columns on a row?


Thanks,


JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Logical replication without a Primary Key

[Joshua D. Drake]

On 12/07/2017 05:30 AM, Peter Eisentraut wrote:



How does that work? Is it using one of the hidden columns on a row?

It means that for example if an update record is produced, the entire
row is included in the record as the key.


Thanks Peter, Craig also responded,

The confusion I have is what if we have two rows that are identical and 
now that I think about it we would just update both rows, yes? That 
would make sense because it would produce two updated rows.


Thanks,

JD







--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Logical replication without a Primary Key

[Joshua D. Drake]

On 12/07/2017 10:49 AM, Robert Haas wrote:

On Thu, Dec 7, 2017 at 9:43 AM, Petr Jelinek
 wrote:

No it won't, it will update only one row, it does not try to find
multiple matching rows.

Good, because that's exactly what it should do.  I mean, if you have
on the master two tuples that are identical, and you update one of
them, then the replica had better update exactly one of them as well.
Since they are identical, it doesn't matter *which* one gets updated
on the replica, but if you update *both* of them on the replica, then
things are out of sync.


Well I think that is a problem actually. If I have:

A    B   C
foo,bar,baz
foo,bar,baz

And then I say:

UPDATE test set A = 1 where C = baz

I have updated two rows because there is no primary key to identify the 
differences. Both of those rows should be updated and thus replicated 
otherwise, logical replication (of this specific table) provides 
inaccurate data on the subscriber.


Thanks,

JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Logical replication without a Primary Key

[Joshua D. Drake]

On 12/07/2017 12:39 PM, Andres Freund wrote:


Not a problem. If you updated both rows, then there's two cases:
a) the update actually changed the column values. In which case the first 
per-row
change that's replicated updates the first row, but the second one won't
again find it as matching in all columns.
b) the update didn't actually change anything. In which case the same
row gets updated twice, but because the column values didn't change,
that doesn't matter.


I may be misunderstanding what is said above but if I ran a test:

Publisher:
reptest=# \d foorep
 Table "public.foorep"
 Column | Type | Collation | Nullable | Default
+--+---+--+-
 one    | text |   |  |
 two    | text |   |  |
Publications:
    "reptestpub"

reptest=# select * from foorep;
 one | two
-+-
 c   | b
 c   | b
 c   | b
(3 rows)

reptest=# update foorep set one = 'd';
UPDATE 3
reptest=# select * from foorep;
 one | two
-+-
 d   | b
 d   | b
 d   | b
(3 rows)

Subscriber before Publisher update:
reptest=# select * from foorep ;
 one | two
-+-
 c   | b
 c   | b
 c   | b
(3 rows)

Subscriber after Publisher update:
reptest=# select * from foorep ;
 one | two
-+-
 d   | b
 d   | b
 d   | b
(3 rows)

This is the behavior I was expecting. As I said, I may have 
misunderstood the responses but it is acting as I would expect.


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

Re: Logical replication without a Primary Key

[Joshua D. Drake]

On 12/18/2017 12:52 PM, Andres Freund wrote:


Just ot make sure: You're saying there's no problem here, and that
logical rep is behaving correctly, right?


Correct. I am not sure where the miscommunication was (fully willing to 
accept it was on my side) but if I update multiple rows in a single 
statement, all the rows that were modified get replicated. That is the 
behavior I would have expected.



FWIW, I wonder if we need to add a warning somewhere about FULL
replication, given it's essentially O(#changes * #rows) -> O(n^2) for
updating the whole table.


The docs do mention it is a performance hit but something a little more 
"IF YOU DO THIS, BEWARE" may be good.


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

Finalizing logical replication limitations as well as potential features

[Joshua D. Drake]

-Hackers,


As I continue to test Logical Replication and its native capabilities I 
have found the following:



It appears it is not possible to do this:

publisher->public.foo replicates to subscriber->private.foo

This one seems like a relatively simple fix (theoretically) and I think 
should be done. I can imagine dozens of scenarios where I want a source 
to replicate to a destination but within a different schema.


It also appears I can't do this:

publisher->public.foo replicates to subscriber->partitioned->public.foo

Here I was hoping that we could have multiple sources replicating into 
single partitioned table that would automatically segregate the database 
based on the partition key.


Lastly, I noted that a full sync of a replication set is performed by a 
COPY, this is fine for small sets but if we have a large data set that 
may take some time it may be a problem with overall performance and 
maintenance. We may want to see if we can do an initial sync 
incrementally (optional) via a cursor (?) and queue all changed rows 
until the sync completes?


Just some thoughts, I tried to ask on Slack as well as IRC but it 
doesn't appear many of the community are doing much with Logical Rep yet.



Thanks in advance,


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

Re: Windows vs C99 (was Re: C99 compliance for src/port/snprintf.c)

[Joshua D. Drake]

On 08/21/2018 11:06 AM, Andrew Dunstan wrote:




XP at least is essentially a dead platform for us. My animals are not 
able to build anything after release 10.


I wouldn't think XP should even be on our list anymore. Microsoft hasn't 
supported it in 4 years.


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Joshua D. Drake]

On 09/14/2018 01:31 AM, Chris Travers wrote:


I apologize for the glacial slowness with which this has all been
moving.
The core team has now agreed to some revisions to the draft CoC
based on
the comments in this thread; see

https://wiki.postgresql.org/wiki/Code_of_Conduct

(That's the updated text, but you can use the diff tool on the page
history tab to see the changes from the previous draft.)


I really have to object to this addition:
"This Code is meant to cover all interaction between community 
members, whether or not it takes place within postgresql.org 
 infrastructure, so long as there is not 
another Code of Conduct that takes precedence (such as a conference's 
Code of Conduct)."


That covers things like public twitter messages over live political 
controversies which might not be personally directed.   At least if 
one is going to go that route, one ought to *also* include a safe 
harbor for non-personally-directed discussions of philosophy, social 
issues, and politics.  Otherwise, I think this is asking for trouble.  
See, for example, what happened with Opalgate and how this could be 
seen to encourage use of this to silence political controversies 
unrelated to PostgreSQL.


I think this is a complicated issue. On the one hand, postgresql.org has 
no business telling people how to act outside of postgresql.org. Full stop.


On the other hand if you are (note: contributor, not community member 
which is different) contributor to PostgreSQL, your actions speak about 
PostgreSQL. So I am not sure what a good plan of action here would be.


One area where this is going to cause a lot of issues is within the 
social constructs of the micro-communities. Are we going to ban Chinese 
members because their government is anti Christian and anti Muslim? Are 
we going to ban members of countries that are not as progressive 
thinking about LGBT rights? Are we going to tell evangelical Christians 
or devout Muslims that they are unwelcome because they are against Gay 
marriage? Are we going to ban Atheists because they think Christians are 
fools?


I think the answer would be, "no" unless they post an opinion... Is that 
really what our community is becoming, thought police?


There was a time when Open Source was about code and community. It is 
clear that it is becoming about authority and politics.


I am the individual that initiated this whole process many moons ago 
with the intent that we have a simple, "be excellent to each other" code 
of conduct. What we have now (although much better than previous drafts) 
is still an over reach.


tl;dr; The willingness of people to think they are right is only 
exceeded by their willingness to oppress those they don't agree with.



JD
--

Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Joshua D. Drake]

On 09/14/2018 07:14 AM, Dave Page wrote:



On Fri, Sep 14, 2018 at 3:08 PM, Joshua D. Drake <mailto:j...@commandprompt.com>> wrote:


On 09/14/2018 01:31 AM, Chris Travers wrote:


I apologize for the glacial slowness with which this has all
been moving.
The core team has now agreed to some revisions to the draft
CoC based on
the comments in this thread; see

https://wiki.postgresql.org/wiki/Code_of_Conduct
<https://wiki.postgresql.org/wiki/Code_of_Conduct>

(That's the updated text, but you can use the diff tool on
the page
history tab to see the changes from the previous draft.)


I really have to object to this addition:
"This Code is meant to cover all interaction between community
members, whether or not it takes place within postgresql.org
<http://postgresql.org> infrastructure, so long as there is not
another Code of Conduct that takes precedence (such as a
conference's Code of Conduct)."

That covers things like public twitter messages over live
political controversies which might not be personally directed.  
At least if one is going to go that route, one ought to *also*
include a safe harbor for non-personally-directed discussions of
philosophy, social issues, and politics. Otherwise, I think this
is asking for trouble.  See, for example, what happened with
Opalgate and how this could be seen to encourage use of this to
silence political controversies unrelated to PostgreSQL.


I think this is a complicated issue. On the one hand,
postgresql.org <http://postgresql.org> has no business telling
people how to act outside of postgresql.org
<http://postgresql.org>. Full stop.


I'm going to regret jumping in here, but...

I disagree. If a community member decides to join forums for other 
software and then strongly promotes PostgreSQL to the point that they 
become abusive or offensive to people making other software choices, 
then they are clearly bringing the project into disrepute and we 
should have every right to sanction them by preventing them 
participating in our project in whatever ways are deemed appropriate.


We all know that PostgreSQL is the only database we should use and 
anybody using a different one just hasn't been enlightened yet. :P


I think we need to define community member. I absolutely see your point 
of the individual is a contributor but community member is rather 
ethereal in this context don't you think?


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Joshua D. Drake]

On 09/14/2018 07:51 AM, Dave Page wrote:
If that business is publicly bringing the project into disrepute, or 
harassing other community members and they approach us about it, then 
it becomes our business.






If it's unrelated to PostgreSQL, then it's your personal business
and not something the project would get involved in.


O.k. so this isn't clear (at least to me) within the CoC. I want
to make sure I understand. You are saying that if a community
member posts on Twitter that they believe gays are going to hell,
reporting that to the CoC committee would result in a
non-violation UNLESS they referenced postgresql within the post?


Yes, I believe so. Isn't that what "To that end, we have established 
this Code of Conduct for community interaction and participation in 
the project’s work and the community at large." basically says?


Honestly, no. At least not to me especially when you consider the 
sentence right after that, "This Code is meant to cover all interaction 
between community members, whether or not it takes place within 
postgresql.org infrastructure, so long as there is not another Code of 
Conduct that takes precedence (such as a conference's Code of Conduct)."


Based on your clarification, I am feeling better but the language 
doesn't read that way to me.


I wish this was easier but have we considered that all channels that we 
would be concerned with already have CoC's and therefore our CoC is 
fairly powerless? Sure they call them Terms of Use but that's what they 
are, Code of Conducts.


Thanks,

JD

--

Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Joshua D. Drake]

On 09/14/2018 07:36 AM, Dave Page wrote:



On Fri, Sep 14, 2018 at 3:21 PM, James Keener > wrote:



Now, you may say that (2) would be rejected by the
committee, but I would
counter that it's still a stain on me and something that
will forever appear
along side my name in search results and that the amount
of time and
stress it'd take me to defend myself would make my
voluntarily leaving
the community, which would be seen as an admission of
guilt, my only
option.


If you had read the policy, you would know that wouldn't
happen as reports and details of reports are to be kept
confidential.


That doesn't mean I won't be strung along and it doesn't mean that
the attacker can't release those details. Remember, I'm worried
about politically motivated attacks, and attacks meant to silence
opposing viewpoints, not legitimate instances of harassment.


Sure, but an attacker can do that now. Having the CoC doesn't change 
anything there, though it does give us a framework to deal with it.



People are shitheads. People are assholes. We're not
agreeing to join
some organization and sign an ethics clause when signing
up for the mailing
list.  The current moderators can already remove bad
actors from the list.
How they act outside of the list is non of this list's
concern.


The lists are just one of many different ways people in this
community interact.


So? We interact with people outside of specific groups all the
time. Baring specific
agreements to the contrary, why should any one group claim
responsibility of my
personal business?


If that business is publicly bringing the project into disrepute, or 
harassing other community members and they approach us about it, then 
it becomes our business.


If it's unrelated to PostgreSQL, then it's your personal business and 
not something the project would get involved in.


O.k. so this isn't clear (at least to me) within the CoC. I want to make 
sure I understand. You are saying that if a community member posts on 
Twitter that they believe gays are going to hell, reporting that to the 
CoC committee would result in a non-violation UNLESS they referenced 
postgresql within the post?


JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Joshua D. Drake]

On 09/14/2018 06:59 AM, Robert Eckhardt wrote:



I really have to object to this addition:
"This Code is meant to cover all interaction between community members,
whether or not it takes place within postgresql.org 
infrastructure, so long as there is not another Code of Conduct that takes
precedence (such as a conference's Code of Conduct)."


I second that objection. It is not in PGDG's remit to cure the world, for
whatever form of cure you ascribe to. This is especially true as 'community
member' has no strict definition.

I understand the concern, however, if you look at how attacks happen
it is frequently through other sites. Specifically under/poorly
moderated sites. For specific examples, people who have issues with
people on Quora will frequently go after them on Facebook and Twitter.


Yes but are we to be the School Principal for the world?


these aren't a solution looking for a problem. If we just want to look
at the clusterfuck that is happening in the reddis community right now
we can see conversations spilling onto twitter and into ad hominem
vitriol.


Sure and that is unfortunate but isn't it up to the individual to deal 
with it through appropriate channels for whatever platform they are on? 
All of these platforms are:


1. Voluntary to use
2. Have their own Terms of Use and complaint departments
3. If it is abuse there are laws

I agree that within Postgresql.org we must have a professional code of 
conduct but the idea that an arbitrary committee appointed by an 
unelected board can decide the fate of a community member based on 
actions outside of the community is a bit authoritarian don't you think?


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Joshua D. Drake]

On 09/14/2018 07:41 AM, James Keener wrote:

> Community is people who joined it

We're not a "community."


I do not think you are going to get very many people on board with that 
argument. As anyone who knows me will attest I am one of the most 
contrarian members of this community but I still agree that it is a 
community.


JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Joshua D. Drake]

On 09/17/2018 08:11 AM, Dmitri Maziuk wrote:

On Sun, 16 Sep 2018 12:52:34 +
Martin Mueller  wrote:


... The overreach is dubious on both practical and theoretical grounds. "Stick to 
your knitting " or the KISS principle seem good advice in this context.

Moderated mailing lists ain't been broken all these years, therefore they need 
fixing. Obviously.


Folks,

At this point it is important to accept that the CoC is happening. We 
aren't going to stop that. The goal now is to insure a CoC that is 
equitable for all community members and that has appropriate 
accountability. At hand it appears that major concern is the CoC trying 
to be authoritative outside of community channels. As well as wording 
that is a bit far reaching. Specifically I think people's main concern 
is these two sentences:


"To that end, we have established this Code of Conduct for community 
interaction and participation in the project’s work and the community at 
large. This Code is meant to cover all interaction between community 
members, whether or not it takes place within postgresql.org 
infrastructure, so long as there is not another Code of Conduct that 
takes precedence (such as a conference's Code of Conduct)."


If we can constructively provide feedback about those two sentences, 
great (or constructive feedback on other areas of the CoC). If we can't 
then this thread needs to stop. It has become unproductive.


My feedback is that those two sentences provide an overarching authority 
that .Org does not have the right to enforce and that it is also largely 
redundant because we allow that the idea that if another CoC exists, 
then ours doesn't apply. Well every single major collaboration channel 
we would be concerned with (including something like Blogger) has its 
own CoC within its Terms of use. That effectively neuters the PostgreSQL 
CoC within places like Slack, Facebook, Twitter etc...


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *