Re: Pg 16: will pg_dump & pg_restore be faster?

[Jonathan S. Katz]

On 5/30/23 10:05 PM, David Rowley wrote:


My understanding had been that concurrency was required, but I see the
commit message for 00d1e02be mentions:


Even single threaded
COPY is measurably faster, primarily due to not dirtying pages while
extending, if supported by the operating system (see commit 4d330a61bb1).


If that's the case then maybe the beta release notes could be edited
slightly to reflect this. Maybe something like:

"Relation extensions have been improved allowing faster bulk loading
of data using COPY. These improvements are more significant when
multiple processes are concurrently loading data into the same table."

The current text of "PostgreSQL 16 can also improve the performance of
concurrent bulk loading of data using COPY up to 300%." does lead me
to believe that nothing has been done to improve things when only a
single backend is involved.


Typically once a release announcement is out, we'll only edit it if it's 
inaccurate. I don't think the statement in the release announcement is 
inaccurate, as it specifies that concurrent bulk loading is faster.


I had based the description on what Andres described in the original 
discussion and through reading[1], which showed a "measurable" 
improvement as the commit message said, but it was not to the same 
degree as concurrently loading. It does still seem impactful -- the 
results show up to 20% improvement on a single backend -- but the bigger 
story was around the concurrency.


I'm -0.5 for revising the announcement, but I also don't want people to 
miss out on testing this. I'd be OK with this:


"PostgreSQL 16 can also improve the performance of bulk loading of data, 
with some tests showing using up to 300% improvement when concurrently 
executing `COPY` commands."


Thanks,

Jonathan

[1] 
https://www.postgresql.org/message-id/20221029025420.eplyow6k7tgu6...@awork3.anarazel.de




OpenPGP_signature
Description: OpenPGP digital signature

Re: Upgrading from 11 to 13

[Jonathan S. Katz]

On 4/1/21 6:06 PM, Bruce Momjian wrote:
An explicit sentence stating that major upgrades can skip major versions is
>> needed.  The document is written assuming the reading knows this, and just
>> makes a few minor notes on the topic:
>> e.g., "If you are upgrading across several major versions, be sure to read 
>> the
>> release notes for each intervening version."
> 
> I have two patches --- one for our website

Reviewed, made a couple of edits, and pushed to the website[1].

Jonathan

[1] https://www.postgresql.org/support/versioning/



OpenPGP_signature
Description: OpenPGP digital signature

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Jonathan S. Katz]

On 4/2/19 2:08 PM, Magnus Hagander wrote:
> On Tue, Apr 2, 2019 at 5:31 PM Andres Freund  <mailto:and...@anarazel.de>> wrote:
> 
> Hi,
> 
> On 2019-04-02 07:35:02 -0500, Brad Nicholson wrote:
> > Michael Paquier mailto:mich...@paquier.xyz>>
> wrote on 04/02/2019 01:05:01 AM:
> >
> > > From: Michael Paquier  <mailto:mich...@paquier.xyz>>
> > > To: "Jonathan S. Katz"  <mailto:jk...@postgresql.org>>
> > > Cc: Tom Lane mailto:t...@sss.pgh.pa.us>>,
> Magnus Hagander
> > > mailto:mag...@hagander.net>>, Daniel
> Verite mailto:dan...@manitou-mail.org>>,
> > > pgsql-general  <mailto:pgsql-general@lists.postgresql.org>>
> > > Date: 04/02/2019 01:05 AM
> > > Subject: Re: CVE-2019-9193 about COPY FROM/TO PROGRAM
> > >
> > > On Mon, Apr 01, 2019 at 10:04:32AM -0400, Jonathan S. Katz wrote:
> > > > +1, though I’d want to see if people get noisier about it
> before we
> > rule
> > > > out an official response.
> > > >
> > > > A blog post from a reputable author who can speak to security
> should
> > > > be good enough and we can make noise through our various channels.
> > >
> > > Need a hand?  Not sure if I am reputable enough though :)
> > >
> > > By the way, it could be the occasion to consider an official
> > > PostgreSQL blog on the main website.  News are not really a model
> > > adapted for problem analysis and for entering into technical
> details.
> >
> > A blog post would be nice, but it seems to me have something about
> this
> > clearly in the manual would be best, assuming it's not there
> already.  I
> > took a quick look, and couldn't find anything.
> 
> https://www.postgresql.org/docs/devel/sql-copy.html
> 
> "Note that the command is invoked by the shell, so if you need to pass
> any arguments to shell command that come from an untrusted source, you
> must be careful to strip or escape any special characters that might
> have a special meaning for the shell. For security reasons, it is best
> to use a fixed command string, or at least avoid passing any user input
> in it."
> 
> "Similarly, the command specified with PROGRAM is executed directly by
> the server, not by the client application, must be executable by the
> PostgreSQL user. COPY naming a file or command is only allowed to
> database superusers or users who are granted one of the default roles
> pg_read_server_files, pg_write_server_files, or
> pg_execute_server_program, since it allows reading or writing any file
> or running a program that the server has privileges to access."
> 
> Those seem reasonable to me?
> 
> 
> Agreed, that part can't really be much clearer.
> 
> But perhaps we should add a warning box
> to https://www.postgresql.org/docs/11/sql-createrole.html that basically
> says "creating a superuser means they can x, y and z"?

Yeah, I think that's the path forward -- make it much clearer by putting
it in the warning box and just re-stating that this is what it means.

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Jonathan S. Katz]

On 4/2/19 1:05 AM, Michael Paquier wrote:
> On Mon, Apr 01, 2019 at 10:04:32AM -0400, Jonathan S. Katz wrote:
>> +1, though I’d want to see if people get noisier about it before we rule
>> out an official response.
>>
>> A blog post from a reputable author who can speak to security should
>> be good enough and we can make noise through our various channels.
> 
> Need a hand?  Not sure if I am reputable enough though :)

I believe you are, and any blog entries helping the matter are welcome :)

> By the way, it could be the occasion to consider an official
> PostgreSQL blog on the main website.  News are not really a model
> adapted for problem analysis and for entering into technical details.

I think this is warrants a longer discussion, albeit for a different day.

Jonathan



signature.asc
Description: OpenPGP digital signature

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Jonathan S. Katz]

> On Apr 1, 2019, at 9:55 AM, Tom Lane  wrote:
> 
> Magnus Hagander  writes:
>>> On Sat, Mar 30, 2019 at 10:16 PM Tom Lane  wrote:
>>> Yeah; this is supposing that there is a security boundary between
>>> Postgres superusers and the OS account running the server, which
>>> there is not.  We could hardly have features like untrusted PLs
>>> if we were trying to maintain such a boundary.
> 
>> I wonder if we need to prepare some sort of official response to that.
>> I was considering writing up a blog post about it, but maybe we need
>> something more official?
> 
> Blog post seems like a good idea.  As for an "official" response,
> it strikes me that maybe we need better documentation.

+1, though I’d want to see if people get noisier about it before we rule
out an official response.

A blog post from a reputable author who can speak to security should
be good enough and we can make noise through our various channels.

Jonathan 

Re: User documentation vs Official Docs

[Jonathan S. Katz]

> On Jul 16, 2018, at 8:08 PM, Alvaro Herrera  wrote:
> 
> On 2018-Jul-16, Joshua D. Drake wrote:
> 
>> Think of this (if we can figure out how to pull this off): User on
>> StackOverflow says, "How do I do X", someone answers with a direct
>> link to a recipe on PostgreSQL.Org that tells them exactly how to do X
>> (with caveats of course).  There isn't much more user friendly than
>> that.
> 
> Sounds like wiki pages could solve need this pretty conveniently.  If
> and when the content is mature enough and migrates to the tutorial main
> documentation pages, the wiki pages can be replaced with redirects to
> those.

We’ve also tried to use the website to point to some already existing
resources to learn PostgreSQL:

https://www.postgresql.org/docs/online-resources/ 


Some of these includes tutorials that people have put together. If other
resources exist, I’m sure the -www team would be happy to review and
add them. We could also consider renaming the page to make it more
clear that it links to tutorials and the like.

That said, I’m sure contributions to improving the tutorial in the docs
would be well received. I figure it would just take a bit of work from people
who want to add to it. I see it being no different than getting a large patch
in, just some collaborative efforts from people who want to make it better
and some community back-and-forth.

Jonathan

Re: Code of Conduct plan

[Jonathan S. Katz]

> On Jun 8, 2018, at 4:46 AM, Simon Riggs  wrote:
> 
> On 6 June 2018 at 19:22, Tom Lane  wrote:
>> I wrote:
>>> Yeah, somebody else made a similar point upthread.  I guess we felt that
>>> the proper procedure was obvious given the structure, but maybe not.
>>> I could support adding text to clarify this, perhaps along the line of
>> 
>> Hmm ... actually, there's another special case that's not discussed,
>> which is what happens if a committee or core member wants to file a
>> complaint against someone else?  They certainly shouldn't get to rule
>> on their own complaint.  So maybe change "complaint against" to
>> "complaint by or against" in my proposed addition, and then we're good.
> 
> Which brings up the further complication of in which order are things
> dealt with?
> 
> If people file complaints against each other. Is there benefit in
> rushing to file a complaint?
> 
> "The Committee will inform the complainant and the alleged violator of
> their decision at that time." That is unclear.
> 
> Are complaints considered AFTER information has been collected from
> both parties? If so, it doesn't matter who complains first, both
> parties will get their say.
> 
> But if the person being complained about only hears of the complaint
> after judgement has been made this means there is benefit in being the
> first to complain, which will encourage people to complain early so
> they can get their boot in first. And also cause double the volume of
> complaints, since it will be necessary to counter-complain in order
> for the alleged violator to get their say.

Earlier it says:

"With the cooperation of all parties, the Committee will aim to complete the
investigation in a period of two weeks from the receipt of the complaint.”

which I interpret as “The CoC committee will collect information in order to
make a fair decision” which would involve talking to the alleged violator(s).

Perhaps we need an additional line that says the CoC committee will be
reaching out to all parties involved in a complaint, just to be clear?

> Would it not be better to consider arbitration as the first step in
> dispute resolution? Do we need judgement by a committee as the first
> step? Do we even have time for judges to judge?

I have noticed it is in the nature of our community for people to try and work
things out amongst themselves first before escalating to others, or to take one
another aside to try and work things out.  For the minor issues that crop up 
(and
I know “minor” is relative), I hope that remains the case.  I view the CoC as 
being
in place for having a way to report abusive behavior and harassment and
knowing we will ensure our community is a safe, fun place to collaborate.

Jonathan

Re: Code of Conduct plan

[Jonathan S. Katz]

> On Jun 5, 2018, at 3:16 PM, Tom Lane  wrote:
> 
> Chris Travers  writes:
>> On Tue, Jun 5, 2018 at 8:42 PM, James Keener  wrote:
>>> The question is: how can you (honestly) make people feel like we'll take
>>> complaints seriously, while also not allowing for the politics that I've
>>> seen surround recent incarnations of Codes of Conduct?
> 
>> At the end I see signals in the current CoC that make me hopeful.  Phrases
>> like "common interest" occur.  There are some minor changes I think would
>> help avoid problems.  But they aren't big deals.  The big thing is I trust
>> our community not to exclude people based, for example, on political or
>> cultural perspectives and thats really important.
> 
> The one thing that gives me any hope of success is that this has
> historically been an apolitical community, so that these sorts of problems
> don't naturally arise.  As long as it stays that way, I think a CoC can
> work to smooth out edge-case situations.  I tend to agree that a CoC
> could not fix tensions in a community that naturally needs to deal with
> political or religious issues.  If someone tries to inflame political or
> religious feelings among the PG community, I hope we have the sense to
> walk away.  (Maybe we could put something in the CoC about that, but
> I have the sense that it'd do more harm than good.)

I would say that the ethos of the community cannot be codified, but is
something the community leaders must continue to exemplify.

Jonathan

Re: Code of Conduct plan

[Jonathan S. Katz]

> On Jun 5, 2018, at 12:32 PM, Tom Lane  wrote:
> 
> James Keener  writes:
>> I don't participate too much here, but I've never see a group implement
>> a code of conduct go well.

There’s also a lot of evidence to the contrary, where groups have
successfully implemented CoCs as well by extension, the corporate
environment and policies and procedures organizations have put in
place to create safe working environments. To echo a point Peter G. made
upthread, yes, mistakes are made and yes nothing will be perfect, but the
main goal is to ensure that if someone is being harassed by a community
member, they have an appropriate avenue to safely report it and ensure
the CoC committee will review.

> Yeah, personally I'm a bit worried about this too.  The proposed CoC
> does contain provisions to try to prevent misusing it, but whether those
> are strong enough remains to be seen --- and it'll depend a good deal
> on the judgment of the committee members.  We have a provision in there
> for periodic review of the CoC, and it'll be important to adjust it if
> we see abuses.

If you read the reporting guidelines, it is requested that someone filing a
report provides as much evidence as possible, and that is a really
important provision, both for the person reporting and for the committee
to review and adjudicate fairly.

And having the independence and the check-and-balance with the core
committee is also key too, to ensure each report is given a fair, objective
review to the best of the abilities of each committee.

> In general, the PG community has a long track record of mostly civil
> interactions, so I'm optimistic that that will continue.  The CoC should
> only come into play in cases where people are not following community
> norms.  If we were trying to impose a CoC to improve a situation where
> not-so-civil interactions were common, I agree that it likely wouldn't
> work.

+1

Jonathan

Re: Code of Conduct plan

[Jonathan S. Katz]

> On Jun 4, 2018, at 7:23 PM, Tom Lane  wrote:
> 
> Vik Fearing  writes:
>> Also I think official text should have its own page on the website,
>> rather than just be on the wiki.  Hopefully that's already planned.
> 
> Right; we'll mark the formal blessing of the text by moving it onto
> the main website.  The translated versions should end up there too.

I assumed this would be put onto the website, just wanting for the
“final word.”

And +1 for translations.

Jonathan

Re: Code of Conduct plan

[Jonathan S. Katz]

> On Jun 4, 2018, at 6:41 PM, Tom Lane  wrote:
> 
> "Joshua D. Drake"  writes:
>> On 06/04/2018 01:46 PM, Tom Lane wrote:
>>> ... The reason for this is an unfortunate situation that took place in
>>> the FreeBSD community awhile back [1], wherein one community member was
>>> abusing another via Twitter, and their existing CoC failed to cover that
>>> because it had been explicitly written to cover only community-run forums.
>>> So we're trying to learn from that mistake, and make sure that if such a
>>> situation ever came up here, the CoC committee would have authority to
>>> act.
> 
>> O.k. I can see that. The problem I am trying to prevent is contributor X 
>> being disciplined for behavior that has nothing to do with 
>> PostgreSQL.Org. I am not sure what the exact good solution is for that 
>> but it is none of our business if contributor X gets into a fight 
>> (online or not) with anyone who is not within the PostgreSQL.Org community.
> 
> Fair.  As written, I think that would only fall under the CoC if somebody
> made an argument that it was bringing disrepute to the PG community.
> The extent to which that would hold up would depend a lot on details,
> like who was involved.  Peripheral community members would probably not
> be considered to be representing the community ... at the other extreme,
> members of the core team had better keep our noses clean at all times.
> That's the price of community leadership.

+1.

I would add that if you choose to contribute to PostgreSQL and make
representations that you contribute to PostgreSQL, then you are acting
as an ambassador of the community in various forums, and as such should
be mindful of how you treat people, regardless of your level of contribution.

I would also say I’m less concerned about people fighting (disputes happen
all the time amongst the best of friends) vs. someone targeting and/or harassing
people inappropriately, which is very different.  And to reiterate, according 
to the
CoC, should someone file a report, it is reviewed by a committee of people
who will do their best to determine whether or not the behavior was 
inappropriate
and/or brings disrepute to the PG community.

Jonathan

Re: Code of Conduct plan

[Jonathan S. Katz]

> On Jun 3, 2018, at 7:08 PM, Gavin Flower  
> wrote:
> 
> On 04/06/18 07:32, Adrian Klaver wrote:
>> On 06/03/2018 11:29 AM, Tom Lane wrote:
>>> Two years ago, there was considerable discussion about creating a
>>> Code of Conduct for the Postgres community, as a result of which
>>> the core team announced a plan to create an exploration committee
>>> to draft a CoC [1].  That process has taken far longer than expected,
>>> but the committee has not been idle.  They worked through many comments
>>> and many drafts to produce a version that seems acceptable in the view
>>> of the core team.  This final(?) draft can be found at
>>> 
>>> 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.
>>> 
>>> The initial membership of the CoC committee will be announced separately,
>>> but shortly.
>>> 
>>> Unless there are substantial objections, or nontrivial changes as a result
>>> of this round of comments, we anticipate making the CoC official as of
>>> July 1 2018.
>> 
>> 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.
>> 
>> 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.
> 

[truncated]

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

I believe the main goal of the CoC is the opposite: it’s to ensure that
people do feel welcome to participate in the PostgreSQL community and
that if they are unfortunately subject to an incident that they have a safe
means of reporting it versus codifying what is “correct."

There is also  a committee around the CoC and why there will be multiple
individuals on the committee, so that way any complaints can be fairly
researched, discussed, and resolved. There are also several checks and
balances with the enforcement of the CoC that should help ensure that any
complaints are handled as fairly as possible.

Anyway, a big +1 to the effort of everyone who worked on the CoC for
the past several years. From feedback in other forums through the years,
I know it does make a difference to have a code of conduct in terms of
helping people to feel more welcome and knowing that there is an
avenue for them to voice feedback in the case of an unfortunate incident.

Jonathan

Re: New website

[Jonathan S. Katz]

> On Apr 20, 2018, at 2:57 PM, Adrian Klaver  wrote:
> 
> On 04/20/2018 11:33 AM, Jonathan S. Katz wrote:
>>> On Apr 18, 2018, at 8:38 PM, Adrian Klaver >> <mailto:adrian.kla...@aklaver.com>> wrote:
>>> 
>>> Clicking on the Mailing List link on front page should take you to page 
>>> that includes the archives.
>> We’ve added a button that brings you to the archives from the homepage.
> 
> Thanks.
> 
> While we are on the subject of Mailing Lists, it would help new users if they 
> could see the list choices and what each list covers. Basically what you see 
> when you go to the Archive section. As it is now going here:
> 
> https://lists.postgresql.org/
> 
> is a leap into the unknown:)

Yeah…this may comes as a surprise, but we’ve had some off-list discussions
about that ;-)

This may be something to move to -www but we now have a situation where we
have information about mailing lists in 3 separate apps:

pgweb
pgarchives
pglister

In which all 3 serve different purposes and we need to iron out the workflow
a bit more between all 3.

Jonathan

Re: New website

[Jonathan S. Katz]

> On Apr 18, 2018, at 8:38 PM, Adrian Klaver  wrote:
> 
> Clicking on the Mailing List link on front page should take you to page that 
> includes the archives.

We’ve added a button that brings you to the archives from the homepage.

Thanks,

Jonathan

Re: New website

[Jonathan S. Katz]

> On Apr 18, 2018, at 6:40 PM, Adrian Klaver  wrote:
> 
>> On 04/18/2018 03:07 PM, Peter Geoghegan wrote:
>> On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver
>>  wrote:
>>> I would contact the Webmaster but Contact goes to a big image of an elephant
>>> head. That is also where Downloads, Support and Donate lands. Might have
>>> been a good idea to roll out a demo site for testing first. Will reserve
>>> judgment on the site design until it is functioning.
>> I really don't think it's practical to give everyone a veto on a
>> website design. It took years to launch this website redesign.
>> Honestly, I was beginning to think that it would never happen.
>> Anyway, I did notice something myself, which is that the git logo
>> links to https://git.postgresql.org/gitweb/, which has many
>> non-very-important git repos. Instead, it should point to the main
>> PostgreSQL repository's gitweb page, which is at
>> https://git.postgresql.org/gitweb/?p=postgresql.git.
> 
> And to get to the mailing list archives(arguably one of the most important 
> links) you have to:

We designed the website with 3 user profiles:

- New to PostgreSQL
- Users
- Hackers

These are 3 very different experiences. It’s hard
to find a balance for all of these different needs.

When we analyzed the traffic we opted to make
various trade offs based on what the most
accessed pages.

We did note that the people who need to access
the mailing list archives know how to get there.

The nice thing about a website is that it is easy
to iterate over and we can rapidly add things in
should they be necessary.

> Click on Community, click on Mailing Lists on the left sidebar, then scroll 
> to the bottom of page to find the search box.
> 
> I am one of the No votes in the survey.

I’m sorry to hear that.  However based on our data
we opted to focus optimizing the experience for
getting people using PostgreSQL in their apps to
the right places.  If you have constructive feedback
on how we can improve in other areas I’m happy to
listen.  As someone who does answer webmas...@postgresql.org feel free
to reach out.

Jonathan