Re: Code of Conduct Committee Volunteer Drive

[Chris Travers]

On Tue, Aug 27, 2024, 5:09 AM Chris Travers  wrote:

> This message is being sent from the Community Code of Conduct Committee,
> with the approval of the Core Team.
>
> As part of the Community CoC policy, the Committee membership is to be
> refreshed on an annual basis. We are seeking up to 3 volunteers to serve on
> the Committee for the coming year, October 1, 2024 - September 30, 2024.
>
Correction.  The term ends on 30 September 2025.

> We are seeking people who reflect the diversity of the PostgreSQL
> community, with the goal to have members from multiple countries and varied
> demographics.
>
> The time commitment for Committee involvement varies, based on internal
> administrative work and the number of active investigations. We estimate an
> average of 5 to 10 hours per month, but that could increase if there is an
> increase in the number of incident reports.
>
> If you are interested, please complete the questionnaire below, and email
> your responses to the Committee at c...@postgresql.org no later than
> September 15, 2024 at 05:00 PM UTC.
>
> The Questionnaire
>
> Your name:
>
> Current employer:
>
> Current country of residence:
>
> (We ask for employer and residence because one of the goals of the
> Committee is to have representation from a variety of geographical areas.
> We also want to avoid a concentration of members from one company.)
>
> 1. What interests you about being on the CoC Committee?
>
> 2. Have you been on another CoC Committee, or had a similar role at
> another organization? (Prior experience is not required, it's just helpful
> to know everyone's background.)
>
> 3. What else do you want to tell us about yourself that is helpful for us
> to know about your potential involvement with the CoC Committee?
>
> Please be sure to send your reply to the CoC email listed above. Thank you!
>
> Regards,
>
> Chris Travers
>
> Acting Chair
>
> PostgreSQL Community Code of Conduct Committee
>
>

Code of Conduct Committee Volunteer Drive

[Chris Travers]

This message is being sent from the Community Code of Conduct Committee,
with the approval of the Core Team.

As part of the Community CoC policy, the Committee membership is to be
refreshed on an annual basis. We are seeking up to 3 volunteers to serve on
the Committee for the coming year, October 1, 2024 - September 30, 2024.

We are seeking people who reflect the diversity of the PostgreSQL
community, with the goal to have members from multiple countries and varied
demographics.

The time commitment for Committee involvement varies, based on internal
administrative work and the number of active investigations. We estimate an
average of 5 to 10 hours per month, but that could increase if there is an
increase in the number of incident reports.

If you are interested, please complete the questionnaire below, and email
your responses to the Committee at c...@postgresql.org no later than
September 15, 2024 at 05:00 PM UTC.

The Questionnaire

Your name:

Current employer:

Current country of residence:

(We ask for employer and residence because one of the goals of the
Committee is to have representation from a variety of geographical areas.
We also want to avoid a concentration of members from one company.)

1. What interests you about being on the CoC Committee?

2. Have you been on another CoC Committee, or had a similar role at another
organization? (Prior experience is not required, it's just helpful to know
everyone's background.)

3. What else do you want to tell us about yourself that is helpful for us
to know about your potential involvement with the CoC Committee?

Please be sure to send your reply to the CoC email listed above. Thank you!

Regards,

Chris Travers

Acting Chair

PostgreSQL Community Code of Conduct Committee

Postgresql Code of Conduct Committee Update

[Chris Travers]

The Code of Conduct Committee is currently operating at reduced capacity
due to multiple departures this year, and therefore acting with the minimum
membership allowed by the code of conduct.

Our current membership is noted at
https://www.postgresql.org/about/policies/coc_committee/

Alexandra Abramova, Sergei Kim, Simon Pain, and Chris Travers currently sit
on the committee.
Chris Travers is currently acting as chair.

More information about those on the committee can be found at the above
link.

Best Wishes,
Chris Travers
Interim Chair, Code of Conduct Committee

Re: 2FA - - - was Re: Password complexity/history - credcheck?

[Chris Travers]

On Mon, Jun 24, 2024 at 8:00 PM o1bigtenor  wrote:

>
>
> On Sun, Jun 23, 2024 at 10:10 AM Greg Sabino Mullane 
> wrote:
>
>> On Sun, Jun 23, 2024 at 5:30 AM Martin Goodson 
>> wrote:
>>
>>> I believe that our security team is getting most of this from our
>>> auditors, who seem convinced that minimal complexity, password history
>>> etc are the way to go despite the fact that, as you say, server-side
>>> password checks can't really be implemented when the database receives a
>>> hash rather than a clear text password and password minimal complexity
>>> etc is not perhaps considered the gold standard it once was.
>>>
>>> In fact, I think they see a hashed password as a disadvantage.
>>
>>
>> Wow, full stop right there. This is a hill to die on.
>>
>> Push back and get some competent auditors. This should not be a DBAs
>> problem. Your best bet is to use Kerberos, and throw the password
>> requirements out of the database realm entirely.
>>
>> Also, the discussion should be about 2FA, not password history/complexity.
>>
>>
> Hmmm - - - - 2FA - - - - what I've seen of it so far is that
> authentication is most often done
> using totally insecure tools (emailing some numbers or using SMS). Now if
> you were espousing
> the use of security dongles and such I would agree - - - - otherwise you
> are promoting the veneering
> of insecurity on insecurity with the hope that this helps.
>
> IMO having excellent passwords far trumps even 2FA - - - - 2FA is useful
> when simple or quite
> easily broken passwords are required.  Now when you add the lack of SMS
> possibilities (due to lack of signal) 2FA is an usually potent PITA because
> of course SMS 'always' works (except it doesn't()).
>
> (Can you tell that I've been bitten in the posterior repeatedly with this
> garbage?)
>

For 2FA, a simple solution is to require a password plus
clientcert=sameuser.  This allows you to authorize devices/user accounts
for specific remote database connections and provides that second factor --
i.e. something you have as well as something you know.

>
>
> Regards
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Proposing a PostgreSQL Independent Professionals Network

[Chris Travers]

Hi everyone,

At Pgconf.dev I had a number of conversations around improving support and
diversity in the community, as well as discussions of interest groups
within the community.  Out of this has come an idea I would like to float
for improving both support for independent professionals in the community
and also improve diversity by reducing structural barriers to building
careers within the community

At think point this is just an idea for some community infrastructure.  I
can help but I don't have the time to drive it by myself.  My vision for
the PIPN would be:

1.  A platform for independent PostgreSQL professionals could advertise
themselves and their services for consulting, contracting, subcontracting,
etc.  In line with many conversations at the unconference and hallway
track, this would not be limited to coding or DBA work but include other
areas of expertise as well.  The goal is to reduce the financial cost and
risk of going independent by providing ways of sharing opportunities, as
well as ensuring that larger consulting shops including PGX and Stormatics
could use this to scout subcontractors when needed.

2.  A platform for mutual support for independent professionals.  This is
important for reducing the barrier to entry and thus increasing diversity
of people who choose to build careers around our software and our
community..

This platform would be entirely not for profit.

In coming up with this idea I want to think in particular Stacy Haysler for
her insights, thoughts, and support.  I would also like to thank Corey
Huinker for conversations on the diversity front and everyone who
participated in the community engagement unconference session.

If you would like to get involved and you are looking for a place to start,
we need at least the following (though other contributions are appreciated):

1.  Governance committee, making decisions about how things work, policies,
etc.

2.  Development of the web platform.

3.  Advocacy and outreach

4.  Hosting donations and the like

All work on this project would be credited.At present time commitment
irequirements are not set.  Any amount of help is welcome no matter how
small.

Is anyone interested in helping out?

Best Wishes,
Chris Travers

Re: Question on overall design

[Chris Travers]

On Tue, Dec 12, 2023 at 2:11 AM Ron Johnson  wrote:

> On Mon, Dec 11, 2023 at 4:41 AM Dominique Devienne 
> wrote:
>
>> On Sun, Dec 10, 2023 at 5:56 PM Ron Johnson 
>> wrote:
>>
>>> * We departitioned because SELECT statements were *slow*.  All
>>> partitions were scanned, even when the partition key was specified in the
>>> WHERE clause.
>>>
>>
>> Surely that's no the case on newer PostgreSQL, is it? Otherwise what's
>> the point of partitioning?
>> Also, I remember reading something about recent improvements with a large
>> number of partitions, no?
>>
>> As someone who's interested on partitioning, I'd appreciate details.
>> Thanks, --DD
>>
>
> This was on 12.5.  v13 was just released, and we weren't confident about
> running a mission-critical system on a .1 version.
>

Something's wrong if all partitions are scanned even when the partition
clause is explicit in the where clause.

There are however some things which can cause problems here, such as type
casts of the partition key, or when the partition key is being brought in
from a join.

>
> All "transaction" tables were partitioned by month on partion_date, while
> the PK was table_name_id, partition_date.
>
> Queries were _slow_, even when the application knew the partion_date range
> (since queries might span months).  PG just wouldn't prune.
>

Was there a datatype issue here?  Like having a partition key of type
timestamp, but the query casting from date?

>
> When I departitioned the tables, performance became acceptable.
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: running \copy through perl dbi ?

[Chris Travers]

\copy in psql just wraps PostgreSQL's COPY FROM STDIN.

if you are trying to do it from your own client program it is trivial to
change to that call instead.

On Mon, Dec 11, 2023 at 4:09 PM Vincent Veyron 
wrote:

> On Fri, 8 Dec 2023 10:45:28 -0500
> David Gauthier  wrote:
> >
> > I'm trying to run a PG client side "\copy" command from a perl script.  I
> > tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> > ERROR:  syntax error at or near "\"
> >
> > I can do this with a command line approach, attaching to the DB  then run
> > using...
>
> Duh! I just realized that what I proposed with system() is a command line
> approach.
>
> As David Johnston mentionned, you can use the SQL COPY command.
>
> However, you need then to deal with permissions so that the server may
> write the file, so I wonder what approach is cleaner?
>

I wouldn't do COPY FROM FILE in that case.  I would do COPY FROM STDIN and
hten write the data.

Here's the general docs in the DBD::Pg module:
https://metacpan.org/pod/DBD::Pg#COPY-support

The general approach is to COPY FROM STDIN and then use pg_putcopydata for
each row, and finally pg_putcopyend to close out this.  It's not too
different from what psql does in the background.

>
>
> --
>
> Bien à vous, Vincent Veyron
>
> https://marica.fr
> Logiciel de gestion des contentieux juridiques, des contrats et des
> sinistres d'assurance
>
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Fixing or Mitigating this ERROR: invalid page in block 35217 of relation base/16421/3192429

[Chris Travers]

On Thu, Nov 30, 2023 at 9:03 AM Abdul Qoyyuum 
wrote:

> Hi Chris,
>
> On Wed, Nov 29, 2023 at 7:38 PM Chris Travers 
> wrote:
>
>>
>>
>> On Wed, Nov 29, 2023 at 4:36 PM Abdul Qoyyuum 
>> wrote:
>>
>>> Hi all,
>>>
>>> Knowing that it's a data corruption issue, the only way to fix this is
>>> to vacuum and reindex the database. What was suggested was the following:
>>>
>>> SET zero_damaged_pages = 0; # This is so that we can have the
>>> application to continue to run
>>> VACUUM FULL VERBOSE ANALYSE; # Do a full vacuum and analyse the problem
>>> if possible.
>>> REINDEX DATABASE "core"; # Then do a reindex and clean it up.
>>>
>>
>> So first, to clear up some confusion on my part here:
>>
>> This procedure doesn't make a lot of sense to me.  But did it clear up
>> the issue?
>>
> Yeah it did fix the issue before (same issue as last year) and it has
> fixed the problem that just happened a few days ago (almost exactly a year
> ago).
>
>>
>> In any of these cases, it is extremely important to diagnose the system
>> properly.  If you have a fault in your storage device or RAID controller,
>> for example, you are asking for more corruption and data loss later.
>>
>
>> At first I thought maybe you mistyped something and then realized there
>> were a few issues with the process so it actually didn't make sense.
>>
>> First, zero_damaged_pages defaults to 0, and I can think of no reason to
>> set  it explicitly.
>> Secondly, a vacuum full has to reindex, so there is no reason to do a
>> reindex following.  Your whole procedure is limited to a vacuum full, when
>> a reindex is the only part that could affect this.   If it did work,
>> reindexing is the only part that would have been helpful.
>>
> Oh that makes sense actually. Thanks.
>

So for a temporary workaround, it sounds like reindexing helps for now, but
yeah this really needs deeper investigation.

>
>> On to the question of what to do next
>>
>>>
>>> We're on Postgresql 12. This has worked before it happened (almost
>>> exactly a year ago) and I think this needs a more permanent solution. I've
>>> looked at routine vacuuming and checked the autovacuum is set to on and the
>>> following configurations:
>>>
>>> core=> select name, setting from pg_settings where name like
>>> 'autovacuum%';
>>> name |  setting
>>> -+---
>>>  autovacuum  | on
>>>  autovacuum_analyze_scale_factor | 0.1
>>>  autovacuum_analyze_threshold| 50
>>>  autovacuum_freeze_max_age   | 2
>>>  autovacuum_max_workers  | 3
>>>  autovacuum_multixact_freeze_max_age | 4
>>>  autovacuum_naptime  | 60
>>>  autovacuum_vacuum_cost_delay| 2
>>>  autovacuum_vacuum_cost_limit| -1
>>>  autovacuum_vacuum_scale_factor  | 0.2
>>>  autovacuum_vacuum_threshold | 50
>>>  autovacuum_work_mem | -1
>>> (12 rows)
>>>
>>> Can anyone advise if there's anything else we can do? We have no clue
>>> what causes the invalid page block and we are running a High Availability
>>> cluster set up but we are hoping that there may be a way to mitigate it.
>>>
>>>
>> You need to figure out why the corruption is happening.  This is most
>> likely, in my experience, not a PostgreSQL bug, but usually something that
>> happens on the hardware layer or an environmental factor.  It could be
>> failin storage or CPU.  Or it could be something like bad electrical input
>> or insufficient cooling (I have seen index and even table corruption issues
>> from both of these).
>>
>> If this is a server you run, the first things I would check are:
>> 1.  Is there a good-quality UPS that the server is plugged into?  Are the
>> batteries in good working order?
>>
> The servers are dual powered and hooked up to both supplied electricity,
> with a backup generator and if that fails, it will switch over to the UPS.
> All of these are supplied and maintained by the data centre that the
> servers are at. There have been no electrical problems so far.
>
>> 2.  Is the server somewhere that may be sitting in a pocket of hot air?
>>
> As you can imagine, the data centre has air-conditioning and floored fans
> blowing hot air up and out,

Re: Fixing or Mitigating this ERROR: invalid page in block 35217 of relation base/16421/3192429

[Chris Travers]

On Wed, Nov 29, 2023 at 4:36 PM Abdul Qoyyuum 
wrote:

> Hi all,
>
> Knowing that it's a data corruption issue, the only way to fix this is to
> vacuum and reindex the database. What was suggested was the following:
>
> SET zero_damaged_pages = 0; # This is so that we can have the application
> to continue to run
> VACUUM FULL VERBOSE ANALYSE; # Do a full vacuum and analyse the problem if
> possible.
> REINDEX DATABASE "core"; # Then do a reindex and clean it up.
>

So first, to clear up some confusion on my part here:

This procedure doesn't make a lot of sense to me.  But did it clear up the
issue?

In any of these cases, it is extremely important to diagnose the system
properly.  If you have a fault in your storage device or RAID controller,
for example, you are asking for more corruption and data loss later.

At first I thought maybe you mistyped something and then realized there
were a few issues with the process so it actually didn't make sense.

First, zero_damaged_pages defaults to 0, and I can think of no reason to
set  it explicitly.
Secondly, a vacuum full has to reindex, so there is no reason to do a
reindex following.  Your whole procedure is limited to a vacuum full, when
a reindex is the only part that could affect this.   If it did work,
reindexing is the only part that would have been helpful.

On to the question of what to do next

>
> We're on Postgresql 12. This has worked before it happened (almost exactly
> a year ago) and I think this needs a more permanent solution. I've looked
> at routine vacuuming and checked the autovacuum is set to on and the
> following configurations:
>
> core=> select name, setting from pg_settings where name like 'autovacuum%';
> name |  setting
> -+---
>  autovacuum  | on
>  autovacuum_analyze_scale_factor | 0.1
>  autovacuum_analyze_threshold| 50
>  autovacuum_freeze_max_age   | 2
>  autovacuum_max_workers  | 3
>  autovacuum_multixact_freeze_max_age | 4
>  autovacuum_naptime  | 60
>  autovacuum_vacuum_cost_delay| 2
>  autovacuum_vacuum_cost_limit| -1
>  autovacuum_vacuum_scale_factor  | 0.2
>  autovacuum_vacuum_threshold | 50
>  autovacuum_work_mem | -1
> (12 rows)
>
> Can anyone advise if there's anything else we can do? We have no clue what
> causes the invalid page block and we are running a High Availability
> cluster set up but we are hoping that there may be a way to mitigate it.
>
>
You need to figure out why the corruption is happening.  This is most
likely, in my experience, not a PostgreSQL bug, but usually something that
happens on the hardware layer or an environmental factor.  It could be
failin storage or CPU.  Or it could be something like bad electrical input
or insufficient cooling (I have seen index and even table corruption issues
from both of these).

If this is a server you run, the first things I would check are:
1.  Is there a good-quality UPS that the server is plugged into?  Are the
batteries in good working order?
2.  Is the server somewhere that may be sitting in a pocket of hot air?

Once you have ruled these out, the next things to check are CPU, memory,
and storage health.  Unfortunately checking these is harder but you can
check SMART indications, and other diagnostic indicators.

However, once these errors start happening, you are in danger territory and
need to find out why (and correct the underlying problem) before you get
data loss.

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

NUMA, PostgreSQL and docker images

[Chris Travers]

Hi everyone,

Does anyone here know if the default PostgreSQL images set NUMA policies?
I am assuming not?  Is there an easy way to make them do this?

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Presentation tools used ?

[Chris Travers]

On Mon, Oct 23, 2023 at 8:30 AM Steve Litt 
wrote:

> Achilleas Mantzios said on Sun, 22 Oct 2023 08:50:10 +0300
>
> >Hello All
> >
> >I am going to give a talk about PostgerSQL, so I'd like to ask you
> >people what do you use for your presentations, also I have no idea how
> >the remote control works to navigate through slides. I have seen it,
> >but never came close to using one.
> >
> >I have access to google slides and libreoffice Impress.  What tools
> >would you suggest ? What's your setup ?
>
> I use presentations in my work, both given by myself and given by
> trainers. I can tell you Libreoffice Impress is absolute garbage. It
> intermittently loses style definitions. As far as google slides, I know
> nothing about them except I don't trust Google. Also, I'm not fan of
> Software as a Service (SaaS) for non-big-enterprise usage. I prefer to
> keep it all on my hard disk. That's where my PostgreSQL software
> resides.
>

One big problem on most of these also is that you have presentation and
content tied together.  So retheming a presentation is difficult or
impossible.

This is one area where Beamer (which I see you mentioned) really shines.  I
can change my presentations if someone wants them themed differently
separate from my content.

>
> Beamer (a LaTeX package) is the Cadillac of the industry, but only if
> you're willing to put in the work. I've done presentations in
> VimOutliner, but it's not "pretty" and so is only appropriate for
> certain audiences. I created Free Software called HTMLSlides, but it's
> not easy to use. I don't recommend it.
>

Also I have noticed a lot of folks in the community (myself included) use
Beamer mostly.

I love it.  It makes my life a LOT easier.

>
> If you don't want to use Beamer, my advice would be to research tools
> that convert Markdown to slides. Markdown is lightning quick to author
> in, very much unlike Beamer.
>
> Two other suggestions:
>
> 1) Please have mercy on your audience members with poor vision, and use
>black type on white background. Yeah, it's not "pretty" and it's not
>"hip", but you won't lose people who can't read purple on blue.
>Likewise, use large fonts so everyone can read. If you need small
>fonts to reveal all your info, you need to split the slide in two.
>

+1

I do sometimes make an exception for this when something is sponsored and
the sponsoring company requests it.

>
> 2) Don't read from your slides. If it's necessary to read the slide,
>what I do is tell the audience to read the slide, and then after
>they've read it I ask for questions and give them answers. But
>typically, my slides are an overview, and my verbal presentation is
>a dialog between myself and the audience.


Slides should be a mnemonic device for you as a speaker and for the
audience later, not a source of direct information except when you need a
visual exploration and then the images are helpful.



>
>
> HTH,
>
> SteveT
>
> Steve Litt
>
> Autumn 2023 featured book: Rapid Learning for the 21st Century
> http://www.troubleshooters.com/rl21
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Question About PostgreSQL Extensibility

[Chris Travers]

On Mon, Oct 16, 2023 at 10:59 PM Laurenz Albe 
wrote:

> On Fri, 2023-10-13 at 13:55 +, felix.quin...@yahoo.com wrote:
> > For the same reason that you can use python or perl in postgresql. It's
> just another language.
> > I have .net code running on several sql servers and to change the
> database to postgresql I have to reprogram them.
>
> Yes, you'll have to rewrite them.
>

Or pick up the pldotnet handler and patch it to work on the new version.
My experience forward porting such things is that it is usually trivial.

Of course there may be other reasons to rewrite but it really depends on a
lot of factors.


>
> Yours,
> Laurenz Albe
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Question About PostgreSQL Extensibility

[Chris Travers]

On Thu, Oct 12, 2023 at 10:09 PM Ilya Kosmodemiansky 
wrote:

> Hi Sepideh,
>
> > From: Sepideh Eidi 
> > Date: Thu, Oct 12, 2023 at 2:35 PM
> > Subject: Question About PostgreSQL Extensibility
> > To: pgsql-general@lists.postgresql.org <
> pgsql-general@lists.postgresql.org>
>
> > We have some .net assemblies and in your documents, I didn’t find any
> support for this type of files that is executable in DB or not.
>
> PostgreSQL doesn't support .net assemblies like SQL Server does. In
> Postgres you can certainly write stored procedures in different
> languages, for example in C, but I am not sure if it would solve your
> problem
>

Also if you are self-hosting, you can write and add your own language
handlers.  So if you need to support .Net assemblies, this is at least in
theory possible.

Note that there are significant reasons to consider rewriting in other
languages, however.  The large one is that .Net prefers a very different
threading model than Postgres and so that's something that has to be solved
(though the Pl/Java folks have solved that problem for Java).


>
> v. G.,
> Ilya
>
>
>
>
>
> best regards,
> Ilya Kosmodemiansky,
> CEO, Data Egret GmbH
> Herrenstr. 1 A 2,
> Spiesen-Elversberg, Germany
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Postgres partition max limit

[Chris Travers]

On Wed, Sep 6, 2023 at 5:27 PM Daulat  wrote:

> Hi Team,
>
> Do we have a max limit of partitions for a table in postgres?
>

As far as I can tell, there is no real hard limit to that per se.  After
all, you will eventually run out of OIDs for pg_attribute but that would
affect the number of columns in the database.

However I can say that based on past testing you probably don't want
thousands of partitions in your database.  At least last time I tried,
planning time would eventually become unreasonably high though I assume
things have improved somewhat since then (and to be fair, that system was
also in a pretty unusual configuration that may have made things worse).

Since that is a gradual process the specific limits for your application
may depend on your latency requirements for the queries in your
application.   I would recommend testing your application by creating all
the partitions you expect even if they are empty, and seeing how long
EXPLAIN takes to run.  If that's good enough, then go for it.  If it is too
long then you could pre-calculate what partition to hit or you could use
fewer partitions.

>
> Thanks.
>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Backup Copy of a Production server.

[Chris Travers]

On Mon, Aug 7, 2023 at 7:00 PM KK CHN  wrote:

>
>
> On Mon, Aug 7, 2023 at 10:49 AM Ron  wrote:
>
>> On 8/7/23 00:02, KK CHN wrote:
>>
>> List,
>>
>> I am in need to copy a production PostgreSQL server  data( 1 TB)  to  an
>> external storage( Say USB Hard Drive) and need to set up a backup server
>> with this data dir.
>>
>> What is the trivial method to achieve this ??
>>
>>
pg_basebackup backs up the data dir.

Alternatively you could use another tool like pgbackrest which also has
some nice features regarding incremental backups, wal management etc and
parallel backup and restore.

>
>>
>> 1. Is Sqldump an option at a production server ?? (  Will this affect the
>> server performance  and possible slowdown of the production server ? This
>> server has a high IOPS). This much size 1.2 TB will the Sqldump support ?
>> Any bottlenecks ?
>>
>>
A sql dump can be had with pg_dumpall, but this is a different backup
strategy.  I would recommend in that case pg_dumpall -g to dump only
globals (roles and tablespaces) and then pg_dump on the databases
individually with format selection to either custom (if parallelism is not
required) or tar (if it is).  See the pg_dump man page for details.

The file size will depend on file format selected etc.  My naive guess for
custom format would be maybe 200-400GB.  For tar format probably more
(double or more) but total size depends on many factors and cannot be
reliably estimated.  In rare cases, it could even be larger than your data
directory.

>
>> Whether or not there will be bottlenecks depends on how busy (CPU and
>> disk load) the current server is.
>>
>
pg_basebackup is limited  by the fact that it is single threaded on both
sides (aside from wal), and this also limits disk I/O as well as network
throughput (if you have a long fat pipe).

pg_dump is also limited by having to interpret and serialize the output,
and also, if you have large text or binary fields, having to retrieve these
one at a time.  Additionally you could have lock contention.

>
>>
>> 2. Is copying the data directory from the production server to an
>> external storage and replace the data dir  at a  backup server with same
>> postgres version and replace it's data directory with this data dir copy is
>> a viable option ?
>>
>>
There is a lot of complexity to doing that right.  If you want to do that,
look at using pgbackrest.

>
>>
>>
>> # cp  -r   ./data  /media/mydb_backup  ( Does this affect the Production
>> database server performance ??)   due to the copy command overhead ?
>>
>>
>> OR  doing a WAL Replication Configuration to a standby is the right
>> method to achieve this ??
>>
>>
That is often also used, but you need to define what you want out of a
backup.  A standby will protect you from hardware failure for the most
part.  It will not, without a lot of other thought and configuration,
protect you from an administrator accidently dropping an important table or
database.   WAL archiving and backups can help there though (and hence my
recommendation for pgbackrest, which can also restore the data directory
and/or wals to a standby).

>
>>
>> But you say you can't establish a network connection outside the DC.  ( I
>> can't do for a remote machine .. But I can do  a WAL replication to another
>> host in the same network inside the DC. So that If I  do a sqldump  or Copy
>> of Data dir of the standby server it won't affect the production server, is
>> this sounds good  ?  )
>>
>
With a good backup archive accessible from only the places it needs to be
accessed, this problem goes away.

>
>>
>>
>>  This is to take out the database backup outside the Datacenter and our
>> DC policy won't allow us to establish a network connection outside the DC
>> to a remote location for WAL replication .
>>
>>
>> If you're unsure of what Linux distro & version and Postgresql version
>> that you'll be restoring the database to, then the solution is:
>> DB=the_database_you_want_to_backup
>> THREADS=
>> cd $PGDATA
>> cp -v pg_hba.conf postgresql.conf /media/mydb_backup
>> cd /media/mydb_backup
>> pg_dumpall --globals-only > globals.sql
>>
>
> What is the relevance of  globals-only and  what this will do  ${DB}.log
> // or is it  ${DB}.sql  ?
>
> pg_dump --format=d --verbose --jobs=$THREADS $DB &> ${DB}.log  // .log
>> couldn't get an idea what it mean
>>
>> If you're 100% positive that the system you might someday restore to is
>> *exactly* the same distro & version, and Postgresql major version, then
>> I'd use PgBackRest.
>>
>> --
>> Born in Arizona, moved to Babylonia.
>>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Reset Postgresql users password

[Chris Travers]

You can use a DO block or write a function do to this.

It takes some practice (and you need to use EXECUTE FORMAT())

If users need to be able to change their own users, something like this
works:

CREATE FUNCTION change_my_password(in_password, text)
returns void language plpgsql as
$$
begin
   EXECUTE FORMAT($F$ALTER USER $I WITH PASSWORD %L$F$, session_user,
in_password);
end;
$$ SECURITY DEFINER;

On Mon, Jul 17, 2023 at 9:28 AM Ron  wrote:

> On 7/12/23 14:28, Johnathan Tiamoh wrote:
> > Hello,
> >
> > I wish to find out if there is a way to reset all users in Postgresql
> > password to the same password at once.
>
> To the same value??
>
> --
> Born in Arizona, moved to Babylonia.
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Interconnected views

[Chris Travers]

On Fri, Jun 2, 2023, 09:36 Oliver Kohll  wrote:

> Hi,
>
> Just wondering, does anyone else create apps which might not have 'big'
> data, but quite complex arrangements of views joining to each other?
>
> If so, do you have scripts to aid refactoring them e.g. drop/recreate/test
> them in the right order etc.?
>

In Ledgersmb, we did this and also same with functions.  We used versioned
scripts, database schema change management tools, and reload utilities as
well as Pgtap for testing.

>
> I'm really impressed with the way Postgres handles these multi-layered
> views. Some of our explain analyze outputs could fill a book each! Tools
> like Depesz' can be very useful. Sometimes a little tweaking or a judicious
> index is necessary, but the performance is very reliable and scalable.
>
> Blog post about it here:
> https://blog.agilebase.co.uk/2023/05/21/refactoring-sql-views/
>

Yeah, I have always been impressed by Postgres here too.

>
> Oliver
>
> --
> See us at the Bath Digital Festival , 12th July
>

Blog post series on commitfests and patches

[Chris Travers]

Hi all;

I have been writing a few blog posts trying to shed some light on the
development process of PostgreSQL , what's coming and what I hope we see
more of.

I would be very much interested in feedback as to whether people
(particularly non-Postgres contributors) find this useful or not.

The latest entry is at:
https://www.timescale.com/blog/a-postgresql-developers-perspective-five-interesting-patches-from-januarys-commitfest/?utm_source=timescaledb&utm_medium=linkedin&utm_campaign=mar-2023-advocacy&utm_content=tsdb-blog


-- 
Best Wishes,
Chris Travers

Re: WAL Archiving and base backup

[Chris Travers]

On Fri, Jan 14, 2022 at 8:27 PM Ron  wrote:

> On 1/14/22 12:31 PM, Stephen Frost wrote:
> > Greetings,
> >
> > * Issa Gorissen (issa-goris...@usa.net) wrote:
> >> Thx a lot. I thought about it but was not so sure about having a complex
> >> script (compared to the very simple version when using the exclusive
> backup
> >> - but this this is deprecated...).
> >>
> >> I will test your option with the simpler version and post it back to it
> can
> >> maybe land in PostgreSQL documentation.
> > The PG docs show how the command works and that's it.  The commands
> > in the docs aren't intended to be actually used in production
> > environments.  Writing a full solution involves having a good
> > understanding of the PG code and how WAL archiving, backups, et al, are
> > done.  If you're not familiar with this portion of the PG code base, I'd
> > strongly suggest you look at using solutions written and maintained by
> > folks who are.
>
> Needing to read the PG source code to write a workable PITR recovery
> solution is a serious flaw in PG documentation (and why I use PgBackRest).
>
> The documentation of two other RDBMSs that I've worked with (Rdb/VMS and
> SQL
> Server) are perfectly clear on how to do such backups and restores with
> relatively small amounts of scripting.
>

So when I was writing my own backup solutions many years ago, I didn't
generally read the code to do that.  I think the problem is that there is a
lot of stuff that goes on around the backup and recovery process where to
make it safe you need to understand all the other things going on.

I can remember at least one case from those years ago when a needed backup
suddenly wasn't PITR-restorable when I needed it to be and that took some
urgent troubleshooting.  I got it resolved but I also understand why those
building,such tools read the code and more importantly understand
implications of design choices in that context.

Backups are critical pieces of infrastructure and one wants to make sure
that weird corner cases don't suddenly render your backup useless when your
production system dies.  And while I do think the docs could be improved, I
agree they will probably never be good enough for people to just roll their
own solutions.


>
> > Trying to write documentation on how to develop a complete solution
> > would be quite an effort and would certainly go beyond bash scripting
> > and likely wouldn't end up getting used anyway- those who are developing
> > such solutions are already reading through the actual code.

>
> > Thanks,
> >
> > Stephen
>
> --
> Angular momentum makes the world go 'round.
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: ZFS filesystem - supported ?

[Chris Travers]

On Mon, Oct 25, 2021 at 10:18 AM Laurenz Albe 
wrote:

> On Sat, 2021-10-23 at 11:29 +, Laura Smith wrote:
> > Given an upcoming server upgrade, I'm contemplating moving away from XFS
> to ZFS
> > (specifically the ZoL flavour via Debian 11).
> > BTRFS seems to be falling away (e.g. with Redhat deprecating it etc.),
> hence my preference for ZFS.
> >
> > However, somewhere in the back of my mind I seem to have a recollection
> of reading
> > about what could be described as a "strong encouragement" to stick with
> more traditional options such as ext4 or xfs.
>
> ZFS is probably reliable, so you can use it with PostgreSQL.
>
> However, I have seen reports of performance tests that were not favorable
> for ZFS.
> So you should test if the performance is good enough for your use case.
>

It very much depends on lots of factors.

On the whole ZFS on spinning disks is going to have some performance...
rough corners.  And it is a lot harder to reason about a lot of things
including capacity and performance when you are doing copy on write on both
the db and FS level, and have compression in the picture.  And there are
other areas of complexity, such as how you handle partial page writes.

On the whole I think for small dbs it might perform well enough.  On large
or high velocity dbs I think you will have more problems than expected.

Having worked with PostgreSQL on ZFS I wouldn't generally recommend it as a
general tool.

Best Wishes,
Chris Travers

>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: JOB | DBA (Canada)

[Chris Travers]

On Thu, Oct 14, 2021 at 5:37 PM Gavin Flower 
wrote:

> On 15/10/21 02:00, James Tobin wrote:
> > Hello, we are working with an employer that is looking to hire someone
> > capable of managing Mongo and Sybase databases at their office in
> > Canada.  Consequently, I had hoped that some members of this list may
> > like to discuss further.  Kind regards, James
> >
> >
> If they wanted people from this list the they should upgrade to
> PostgreSQL, they'll find PostgreSQL faster and more reliable than
> Mongo.  I suspect  the same might true for Sybase, but I've never done a
> detailed comparison.
>

Indeed.  MongoDB has some interesting features like capped collections
which make it useful as a cache, but my experience always leaves me feeling
like performance and scalability are lacking.

>
>
> Cheers,
> Gavin
>
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: JOB | DBA (Canada)

[Chris Travers]

On Thu, Oct 14, 2021 at 3:00 PM James Tobin  wrote:

> Hello, we are working with an employer that is looking to hire someone
> capable of managing Mongo and Sybase databases at their office in
> Canada.  Consequently, I had hoped that some members of this list may
> like to discuss further.  Kind regards, James
>

If you are posting here, is it because they want to move all these to
PostgreSQL?


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: export to parquet

[Chris Travers]

On Wed, Aug 26, 2020 at 9:00 PM Scott Ribe 
wrote:

> I have no Hadoop, no HDFS. Just looking for the easiest way to export some
> PG tables into Parquet format for testing--need to determine what kind of
> space reduction we can get before deciding whether to look into it more.
>
> Any suggestions on particular tools? (PG 12, Linux)
>
> For simple exporting, the simplest thing is a single-node instance of
Spark.

You can read parquet files in Postgres using
https://github.com/adjust/parquet_fdw if you so desire but it does not
support writing as parquet files are basically immutable.


>
> --
> Scott Ribe
> scott_r...@elevated-dev.com
> https://www.linkedin.com/in/scottribe/
>
>
>
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Implement a new data type

[Chris Travers]

On Wed, Aug 12, 2020 at 2:01 AM raf  wrote:

> On Tue, Aug 11, 2020 at 06:38:39AM -0700, Miles Elam <
> miles.e...@productops.com> wrote:
>
> > Also of note: PostgreSQL already has a money type (
> > https://www.postgresql.org/docs/current/datatype-money.html)
> > But you shouldn't use it (
> > https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money).
> >
> > I only bring it up so that you can know to make your money type a
> slightly
> > different name to avoid a conflict. Money is deceptively hard to
> implement
> > correctly. I'd recommend reading the second link if you have not already
> to
> > avoid previously known issues.
>
> I use decimal(10,2) for whole cents, and decimal(12,6)
> for sub-cents. Single currency only. I didn't know
> there was a money type originally, but it wouldn't be
> usable for me anyway without the ability to specify the
> scale and precision.
>

It is worth noting that decimal is an alias for numeric in Postgres.   For
that reason you will have less confusion if you use numeric instead.

>
> I recommend considering passing values to the database
> as "decimal '1.23'" rather than bare numeric literals,
> just so there's no chance of the value being
> interpreted as a float at any stage by postgres. Maybe
> that's being too paranoid but that's a good idea when
> it comes to money. :-)
>

I don't think the type designation buys you anything. unless it is a part
of an arithmetic expression  The single quotes do and cannot be omitted
here.

So I think there is a difference between ('1.23' + 1)::numeric and
'1.23'::numeric + 1 but there is also a difference between 1.23::numeric +
1 and '1.23'::numeric + 1

But there is no reason to add the cast when doing something like an insert
of a single value.

>
> Perhaps the incorporation of currency would make a new
> money type interesting. Currencies probably don't
> change as often as timezones but there would probably
> still be some ongoing need for updates.
>

The existing money type has another problem in that the currency it is
attached to is taken from the current locale.  So if you change your locale
settings you can change a value from, say, 100 IDR to 100 EUR at least for
display purposes.

I have some thoughts about how to do a multi-currency type but I am not
actually sure you get anything by tying the data together instead of having
it in separate columns.

>
> cheers,
> raf
>
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Oracle vs. PostgreSQL - a comment

[Chris Travers]

On Wed, Jun 3, 2020 at 7:45 PM Andreas Joseph Krogh 
wrote:

> På onsdag 03. juni 2020 kl. 18:50:12, skrev Jeremy Schneider <
> schnj...@amazon.com>:
>
> > On 6/2/20 1:30 PM, Stephen Frost wrote:
> >> No, nothing does as PG doesn't support it as we have one WAL stream for
> >> the entire cluster.
>
> On 6/2/20 11:38, Ron wrote:
> > Right.  Making WAL files specific to a database should be high on the
> > list of priorities.
>
> Did Oracle change this?  Last time I looked, I don't think Oracle
> supported local redo in their multitenant architecture either.
>
>
>
> Regardless of what Oracle does, I agree this would be a huge step in the
> right direction for pg-DBAs.
> I have absolutely no clue about how much work is required etc., but I
> think it's kind of strange that no companies have invested in making this
> happen.
>

I manage database clusters where the number of databases is a reason not to
do logical replication based upgrades, where pg_upgrade is far preferred
instead.

If this were to be the case, I would be very concerned that a bunch of
things would have to change:
1.  Shared catalogs would have txid problems unless you stay with global
txids and then how do local wal streams work there?
2.  Possibility that suddenly streaming replication has the possibility of
different databases having different amounts of lag
3.  Problems with io management on WAL on high throughput systems (I have
systems where a db cluster generates 10-20TB of WAL per day)

So I am not at all sure this would be a step in the right direction or
worth the work.

>
> --
> Andreas Joseph Krogh
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: kind of a bag of attributes in a DB . . .

[Chris Travers]

On Sat, Sep 14, 2019 at 5:11 PM Albretch Mueller  wrote:

>  just download a bunch of json info files from youtube data Feeds
>
>  Actually, does postgresql has a json Driver of import feature?
>

Sort of  There  are a bunch of features around JSON and JSONB data
types which could be useful.

>
>  the metadata contained in json files would require more than one
> small databases, but such an import feature should be trivial
>

It is not at all trivial for a bunch of reasons inherent to the JSON
specification.  How to handle duplicate keys, for example.

However writing an import for JSON objects into a particular database is
indeed trivial.

>
>  C
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: kind of a bag of attributes in a DB . . .

[Chris Travers]

On Sat, Sep 7, 2019 at 5:17 PM Albretch Mueller  wrote:

> Say, you get lots of data and their corresponding metadata, which in
> some cases may be undefined or undeclared (left as an empty string).
> Think of youtube json files or the result of the "file" command.
>
> I need to be able to "instantly" search that metadata and I think DBs
> are best for such jobs and get some metrics out of it.
>
> I know this is not exactly a kosher way to deal with data which can't
> be represented in a nice tabular form, but I don't find the idea that
> half way off either.
>
> What is the pattern, anti-pattern or whatever relating to such design?
>
> Do you know of such implementations with such data?
>

We do the debug logs of JSONB with some indexing.It works in some
limited cases but you need to have a good sense of index possibilities and
how the indexes actually work.


> lbrtchx
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Featured Big Name Users of Postgres

[Chris Travers]

At Adjust GmbH we have 5-10 PB data in Postgres.


On Tue, Jun 11, 2019 at 9:28 PM Ireneusz Pluta/wp.pl  wrote:

> W dniu 2019-06-11 o 19:45, Igal Sapir pisze:
> > I'm doing a presentation about Postgres to SQL Server users this
> weekend, and I want to showcase
> > some of the big names that use Postgres, e.g. MasterCard, Government
> agencies, Banks, etc.
> >
> You might be interested in this:
> https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-hello-postgres
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Storage Inefficiency In PostgreSQL

[Chris Travers]

On Mon, Apr 15, 2019 at 10:43 AM Ray Cheung <
ray.che...@silverpowersystems.com> wrote:

> Hi ,
>
> We are currently contemplating switching from MySQL to PostgreSQL, the main
> attraction being the use of the TimescaleDB extension. Having done much of
> the ground investigation there is one area of significant concern - the
> storage requirement of PostgreSQL. Put simply, comparing like for like for
> a
> set of tables, PostgreSQL consumes far more storage space than MySQL:
>
> - MySQL (5.6): 156 MB
> - PostgreSQL (11.2): 246 MB
> - PostgreSQL + TimescaleDB (partitioned/chunked data): 324 MB
>
> I've also submitted this in stackoverflow:
>
> https://stackoverflow.com/questions/55655272/how-to-reduce-postgresql-databa
> se-size
> <https://stackoverflow.com/questions/55655272/how-to-reduce-postgresql-database-size>
> .
>
> I can rearrange the table/column-alignment to save 6 bytes per row of the
> main table, with a saving of a few mega-bytes. Not enough to make any real
> difference. Does anyone know:
>
> - Why PostgreSQL is so storage inefficient in comparison?
>

The storage strategies are different enough you can't really assume direct
comparisons.

Long story short, iMySQL is optimized for two things:  primary key lookups,
and reducing disk I/O from updates to heavily indexed tables.
PostgreSQL is optimized for a lot of things, including access through
secondary indexes and sequential scans.  This means that both tables and
indexes are structured differently.


> - What existing methods can be used to reduce the storage consumption (I've
> already tried realignment and vacuum full)?
>

You could take a look at extensions that give you foreign data wrappers for
columnar stores, but note this has a number of important tradeoffs in
performance and is not recommended for OLTP systems.  However if space is
your primary concern, I would assume you are trying to set up some sort of
OLAP system?


> - Are there any plans to address this storage consumption inefficiency (in
> comparison to MySQL) problem?
>

Long run  pluggable storage should give people a different set of options
and choices to make here.

>
> Many thanks,
>
> sps-ray
>
>
> ---
> This email has been checked for viruses by Avast antivirus software.
> https://www.avast.com/antivirus
>
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: When to store data that could be derived

[Chris Travers]

Meant to send this to the list but hit the wrong button.

On Sun, Mar 24, 2019 at 9:45 AM Ron  wrote:

> On 3/24/19 3:05 AM, Frank wrote:
> >
> >
> > On 2019-03-24 9:25 AM, Ron wrote:
> >> On 3/24/19 1:42 AM, Frank wrote:
> >>> Hi all
> >>>
> >>> As I understand it, a  general rule of thumb is that you should never
> >>> create a physical column if the data could be derived from existing
> >>> columns. A possible reason for breaking this rule is for performance
> >>> reasons.
> >>>
> >>> I have a situation where I am considering breaking the rule, but I am
> >>> not experienced enough in SQL to know if my reason is valid. I would
> >>> appreciate it if someone could glance at my 'before' and 'after'
> >>> scenarios and see if, from a 'gut-feel' point of view, I should
> proceed.
> >>>
> >
> > [snip]
> >
> >>
> >> Sure the second query joins a lot of tables, but is pretty
> straightforward.
> >>
> >> What REALLY worries me is whether or not the query optimiser would look
> >> at the WHERE CASE, run away screaming and then make it use sequential
> >> scans. Thus, even query #1 would be slow.
> >>
> >
> > I had not realised that. I hope someone else chimes in on this.
>
> In every DBMS that I've used, the lside (left side) needs to be static
> (not
> "a" static) instead of variable (like a function).
>
> For example, this always leads to a sequential scan:
> WHERE EXTRACT(DAY FROM DATE_FIELD) = 5
>


PostgreSQL allows expression indexes

So you can:

create index foo on bar ((id % 1000));

And then use the index on:

select * from bar where id % 1000 = 45;

You could similarly

create index foo on bar (extract(day from date_field));

The left side needs to be indexed (and an immutable expression) but beyond
that.

>
> >
> >>
> >> Is this a historical data set that's never updated, or current data
> >> that's constantly added to?
> >>
> >
> > It is the latter - current data constantly added to.
> >
> > Frank
> >
>
> --
> Angular momentum makes the world go 'round.
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: When to store data that could be derived

[Chris Travers]

On Sun, Mar 24, 2019 at 9:05 AM Frank  wrote:

>
>
> On 2019-03-24 9:25 AM, Ron wrote:
> > On 3/24/19 1:42 AM, Frank wrote:
> >> Hi all
> >>
> >> As I understand it, a  general rule of thumb is that you should never
> >> create a physical column if the data could be derived from existing
> >> columns. A possible reason for breaking this rule is for performance
> >> reasons.
> >>
> >> I have a situation where I am considering breaking the rule, but I am
> >> not experienced enough in SQL to know if my reason is valid. I would
> >> appreciate it if someone could glance at my 'before' and 'after'
> >> scenarios and see if, from a 'gut-feel' point of view, I should proceed.
> >>
>
> [snip]
>
> >
> > Sure the second query joins a lot of tables, but is pretty
> straightforward.
> >
> > What REALLY worries me is whether or not the query optimiser would look
> > at the WHERE CASE, run away screaming and then make it use sequential
> > scans. Thus, even query #1 would be slow.
> >
>
> I had not realised that. I hope someone else chimes in on this.
>

There are a few other things to note here.

1  If the data is frequently used in its derivative form, putting it in a
function helps, and
2.  You can index the output of the function which means you run it on
insert/update time and can often avoid running it on selection time if it
is just a part of the where clause.

In my experience usually  we have used trigger-updated functions when the
field values are very large or expensive, and may need to be part of the
column list, and functions with functional indexes when we don't need to
ever put them in the select column list or where the columns are small and
easy to calculate.

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Fwd: Camel case identifiers and folding

[Chris Travers]

On Sat, Mar 16, 2019 at 3:15 PM Tom Lane  wrote:

> Steve Haresnape  writes:
> > As I said, I don't want to quote my identifiers. I know what that does. I
> > want to specify them in a certain way, see them in that same way, but
> refer
> > to them in any old way.
> > You can call it normalize or fold or whatever. It's a bad design choice,
> > and not even a completely compliant choice.
>
> > Is a cure contemplated? I know it's not just me that dislikes this.
>
> No.
>
> There have been previous discussions of allowing variant case-folding
> rules, and the conclusion has always been that it would break so much
> stuff as to be entirely not worth the trouble.
>
> The big problem with making significant semantics changes like this
> be optional is that authors of general-purpose tools then have to be
> prepared to cope with all the possibilities.  That's a pretty enormous
> cost to load onto other people.  If it *only* affected the core code,
> maybe you could find somebody to do the work and call it done, but
> actually the implications would reverberate across the entire Postgres
> ecosystem.  That's a tough call to make for a change that can't even
> be painted as meeting a widely-favored goal like better SQL spec
> compliance.
>

Yeah.  I remember the annoyances caused by the removal of the implicit
casts in 8.3 and I think this would be orders of magnitude worse.   The
cast changes were clearly worth the (significant) pain.  I seriously doubt
changing case folding would.

>
> Now, in the spirit of full disclosure, I should say that the only form
> of this idea that people have really spent significant effort looking
> at is exactly the fully-SQL-spec-compliant case-folding rule, ie just
> like Postgres normally does it except unquoted identifiers fold to
> all-upper-case not all-lower.  Perhaps there's some reason why what
> you want would be less painful than that turns out to be ... but I'm
> not seeing such a reason offhand.  In fact I suspect your preference
> is actually worse, it'd require behavior changes in more places.
> As an example, I believe your request would require case-insensitive
> uniqueness enforcement in the system catalogs' unique indexes on names.
> You have no idea how large a can of worms that opens (but I'll just
> mention that "which characters are letters" doesn't even have a well
> defined universal answer).
>

+1

>
> regards, tom lane
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Camel case identifiers and folding

[Chris Travers]

On Thu, Mar 14, 2019 at 11:07 PM Steve Haresnape <
s.haresn...@creativeintegrity.co.nz> wrote:

> I'm porting a sql server database to postgresql 9.6. My camelCase
> identifiers are having their humps removed. This is disconcerting and sad.
>
> Is there a cure for this?
>

Double quote them.

>
> I don't want to quote my identifiers unless I have to. I don't want to use
> underscores. Everywhere else I write code I use camel case. I makes me feel
> warm inside.
>

The SQL standard mandates case folding.  We don't quite follow the standard
(which mandates folding to upper case) because nobody here likes that, and
so we fold to lower case.  In most cases, however, our behavior is close to
that of the standard.

>
> This is folding in the same sense Procrustes folded his guests to fit his
> bed.
>
> The sql standard has some dumb stuff in it. Do not appeal to it. It can be
> safely ignored in this regard (by Microsoft, and anyone else wishing to
> prevent the propagation of stupidity).
>

MySQL also ignores the standard and does not fold cases.  You can weigh
this point vs what you give up by going that direction.

>
> The only mechanism available to put any semantics into the database is via
> the naming of identifiers. Why screw with that? Imagine what would happen
> if your IDE decided to do that to your java code.
>
>
> Different languages address case and identifiers differently, no?

Why should PostgreSQL follow a Java standard?


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: POSTGRES/MYSQL

[Chris Travers]

On Mon, Mar 11, 2019 at 7:32 PM Sonam Sharma  wrote:

> Hi All,
>
> We are planning to migrate our database into any open source DB.
> Can someone please help me in knowing which one will be better among
> POSTGRESQL and MYSQL.
>
> In what terms postgres is better than MYSQL.
>

If course you will get a lot of pro-Postgres answers here.  I am going to
try to give a balanced one.

I:  Performance

MySQL with InnoDB performs differently than PostgreSQL.  Primary key
lookups are marginally less expensive.  Secondary index lookups are
significantly more expensive.  Sequential scans are much more expensive.
If all you are ever doing is primary key lookups, MySQL might perform
better.  For most real-world workloads, PostgreSQL does better.

Also MySQL has a query cache that allows the results of very common queries
to be much faster.  PostgreSQL has a more complex cache system which
performs better on complex workloads.  So in most cases, Postgres is better
(assuming appropriate tuning on both).

For updates, MySQL avoids a lot of index write overhead.  PostgreSQL has
more overhead per update.  In some cases this is a big deal.  However I
have never seen such a situation that made PostgreSQL unsuitable.

I suspect parallel query is faster on MySQL but I have seen internal
parallelism lead to deadlocks with only a single session running.  In other
words, bulk inserts deadlocking against themselves.

II:  Porting

MySQL has per-client modes of query which affect what data can be properly
stored and how data can be retrieved.  The positive side is that MySQL is
ok at pretending to be other database systems but not so good at ensuring
data integrity (strict mode can be turned off by any writer, so you can't
always trust what is written).  The downside is that MySQL doesn't have as
much of a rich feature set of compliant features, so what you gain from
being able to use a supported dialect you may lose in having to rewrite
queries anyway.

PostgreSQL is fairly strict about data insertion and does not support
multiple dialects of SQL, so porting non-ANSI-SQL queries to PostgreSQL can
sometimes take more effort, but the feature set supported is much higher
so  Six of one, half a dozen of the other.

III:  Licensing

MySQL is owned by Oracle and GPL licensed.  PostgreSQL is BSD-licensed and
owned by the individual contributors.  If you are considering MySQL you
might want to use MariaDB instead.  But PostgreSQL avoids most of these
issues and ensures that even if you are distributing the db with a
proprietary application, there are no licensing implications of doing that.


> Regards,
> Sonam
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Where **not** to use PostgreSQL?

[Chris Travers]

On Thu, Feb 28, 2019 at 1:50 PM Nicolas Grilly 
wrote:

> On Thu, Feb 28, 2019 at 1:24 PM Chris Travers 
> wrote:
>
>> 1.  a) TB-scale full text search systems.
>>  b) PostgreSQL's full text search is quite capable but not so
>> powerful that it can completely replace Lucene-based systems.  So you have
>> to consider complexity vs functionality if you are tying with other data
>> that is already in PostgreSQL.  Note further that my experience with at
>> least ElasticSearch is that it is easier to scale something built on
>> multiple PostgreSQL instances into the PB range than it is to scale
>> ElasticSearch into the PB range.
>>  c) Solr or ElasticSearch
>>
>

> One question about your use of PostgreSQL for a TB-scale full-text search
> system: Did you order search results using ts_rank or ts_rank_cd? I'm
> asking because in my experience, PostgreSQL full-text search is extremely
> efficient, until you need ranking. It's because the indexes don't contain
> the necessary information for ranking, and because of this the heap has to
> be consulted, which implies a lot of random IO.
>
> I'd be curious to know a bit more about your experience in this regard.
>


Where I did this on the TB scale, we had some sort of ranking but it was
not based on ts_rank.

On the PB scale systems I work on now, it is distributed, and we don't
order in PostgreSQL (or anywhere else, though if someone wants to write to
disk and sort, they can do this I guess)

>
> Regards,
>
> Nicolas Grilly
>
> PS: A potential solution to the performance issue I mentioned is this PG
> extension: https://github.com/postgrespro/rum
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Where **not** to use PostgreSQL?

[Chris Travers]

On Thu, Feb 28, 2019 at 1:09 PM Pavel Stehule 
wrote:

> Hi
>
> čt 28. 2. 2019 v 12:47 odesílatel Thomas Güttler <
> guettl...@thomas-guettler.de> napsal:
>
>> Hi experts,
>>
>> where would you suggest someone to **not** use PostgreSQL?
>>
>
Hard question.  There are a lot of general places where PostgreSQL is not
by itself the best fit, and where a careful weighing of pros and cons would
need to be made before deciding to use it.

Having used PostgreSQL in place of ElasticSearch for PB-scale deployments,
as a high-throughput queue system, and near-real-time OLAP workloads, I am
fairly aware of how hard it can be pushed.

So the answers here are not "don't use PostgreSQL here" but "think about it
first and consider alternatives."



>
>> Why would you do this?
>>
>
Replacing with "What would you consider to be the tradeoffs?"

>
>> What alternative would you suggest instead?
>>
>
So a few possibilities:

1.  a) TB-scale full text search systems.
 b) PostgreSQL's full text search is quite capable but not so powerful
that it can completely replace Lucene-based systems.  So you have to
consider complexity vs functionality if you are tying with other data that
is already in PostgreSQL.  Note further that my experience with at least
ElasticSearch is that it is easier to scale something built on multiple
PostgreSQL instances into the PB range than it is to scale ElasticSearch
into the PB range.
 c) Solr or ElasticSearch

2.  a) High performance job queues
 b) PostgreSQL index and table structures are not well suited to large
numbers of ordered deletes.  There are ways around these problems and again
if other data is in PostgreSQL, the tradeoff is around transactional
behavior and complexity there vs ease of scaling performance.
 c) Redis if the job queue easily fits into a small enough amount of
memory, or Kafka if it does not

On to where you actually should never use PostgreSQL:

Don't use PostgreSQL for things where you do not want or cannot guarantee
transactional atomicity.
While it is possible to have untrusted languages have side effects in the
real world, the fact is that mixing transactions and non-transactional
behavior in this way adds a lot of really ugly complexity.
Use another development environment instead.


>
> Don't use Postgres like cache, don't use Postgres for non transactional
> short life often updated data.
>
> Use inmemory databases instead
>
> Pavel
>
>
>>
>> Regards,
>>Thomas Güttler
>>
>>
>> --
>> Thomas Guettler http://www.thomas-guettler.de/
>> I am looking for feedback:
>> https://github.com/guettli/programming-guidelines
>>
>>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Impact on PostgreSQL due to Redhat acquisition by IBM

[Chris Travers]

On Wed, Nov 14, 2018 at 8:06 AM Sachin Kotwal  wrote:

> Hi PostgreSQL lovers,
>
> I heard news that Redhat is going to acquired by IBM. IBM has its on
> database. And they have history of selling applications with their own
> hardware (software and hardware together).
>
> As per my knowledge PostgreSQL community has better support for Redhat
> family than any other platforms.
>

I don't know that is the case.  PostgreSQL support is different on Debian,
RedHat, Gentoo, etc.  The Debian folks, for example have a lot of tooling
around running many different instances of different versions on the same
host (as does Gentoo by the way).  In that respect, RedHat seems behind ;-)
 On the other hand, that lack of functionality means running it on RedHat
is a bit simpler if more limited.

I say this as someone who has a lot of experience running PostgreSQL on all
of the above.  The platforms are different in how they approach PostgreSQL
and what they want you to be able to do with it.


>
> Does community is going to support/focus more Debian platforms ?
>

As the community is growing I suspect that all platforms will get more
focus.  But usually the question is what the distro does to support
PostgreSQL for their own use cases.


> Does community has any plan to switch their main supported platform?
>

That question makes no sense.  We don't have a main supported platform.


> Please share if any other plan.
>
>
> --
>
> Thanks and Regards,
> Sachin Kotwal
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: DB size difference after restore

[Chris Travers]

On Wed, Oct 3, 2018 at 2:59 PM Sonam Sharma  wrote:

>
>
> On Wed, Oct 3, 2018 at 6:21 PM Sonam Sharma  wrote:
>
>> Hello Ben,
>>
>> When we do \l+ , it is different than source, when we load backup from
>> target server.
>>
>Backup is taken using pg_dump  and its loaded as psql db name 
>

It's normal that there is a size difference.

Basically you have a database you dump which may have many versions of
visible rows or may have free space in the table, etc.

You take the most recent consistent backup of the visible data when you
take a dump.

You create a database with only that information in it.  So one generally
expects it to be smaller.  In for a db of reasonable size and load the
difference may be 2x or more.



>
>> Regards,
>>
>> Sonam
>>
>>
>>
>>
>>
>>
>> On Wed, Oct 3, 2018 at 6:17 PM Benjamin Scherrey <
>> scher...@proteus-tech.com> wrote:
>>
>>> If you're talking about space on drive then you can expect the new one
>>> to be smaller generally as it has been straight efficient writes rather
>>> than a bunch of updates and deletes which create "holes" in the physical
>>> file space.
>>>
>>> It helps if you are more detailed as to what you've observed if you want
>>> a more specific answer.
>>>
>>>   - - Ben Scherrey
>>>
>>> On Wed, Oct 3, 2018, 7:43 PM Sonam Sharma  wrote:
>>>
>>>> I have restored the database from backup dump but the size of source
>>>> and target databases are different. What can be the reason for this ?
>>>>
>>>> Regards,
>>>> Sonam
>>>>
>>>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: How to investigate what postgres is spending time on

[Chris Travers]

On Sun, Sep 23, 2018 at 1:15 PM Kim Rose Carlsen  wrote:

> Hi
>
>
> I have some simple INSERT / UPDATE queries, that takes a long time the
> first time they are run in out test environment, but I'm not sure what
> postgres is doing and what I can do to help it. Whats common is that the
> table contains many rows in the order of about 20 millions.
>
>
> Query:
>
> INSERT INTO communication.request_parameter (request_id,
> template_version_parameter_id, parameter_value)
>  VALUES (1222, 1211, 122) RETURNING request_parameter_id
>
> Row from pg_stat_statements:
> ---+++--++--++++++++++++++++++-
> userid | dbid   | queryid| query  
>   | calls  | total_time   | min_time   | max_time 
>   | mean_time  | stddev_time| rows   | shared_blk | 
> shared_blk | shared_blk | shared_blk | local_blks | local_blks | local_blks | 
> local_blks | temp_blks_ | temp_blks_ | blk_read_t | blk_write_ |
> ---+++--++--++++++++++++++++++-
> 16385  | 16389  | 2064198912 | INSERT INTO 
> communication.request_parameter (request_id, tem | 98 | 646.393451
>| 0.03   | 638.712758 | 6.59585154081633   | 64.1818799227704   | 98   
>   | 2850   | 24 | 21 | 0  | 0  | 0
>   | 0  | 0  | 0  | 0  | 0  | 0
>   |
>
> Description of table:
> # \d communication.request_parameter
>  Table
> "communication.request_parameter"
> Column |   Type| Collation | Nullable
> |
> Default
>
> ---+---+---+--+---
>  request_parameter_id  | integer   |   | not null
> |
> nextval('communication.request_parameter_request_parameter_id_seq'::regclass)
>  request_id| integer   |   | not null
> |
>  template_version_parameter_id | integer   |   | not null
> |
>  parameter_value   | character varying |   |
> |
> Indexes:
> "request_parameter_pkey" PRIMARY KEY, btree (request_parameter_id)
> "request_parameter_parameter_value_idx" btree (parameter_value)
> "request_parameter_request_id_idx" btree (request_id)
> "request_parameter_template_version_parameter_id_idx" btree
> (template_version_parameter_id)
> Foreign-key constraints:
> "request_parameter_request_id_fkey" FOREIGN KEY (request_id)
> REFERENCES communication.request(request_id)
> "request_parameter_template_version_parameter_id_fkey" FOREIGN KEY
> (template_version_parameter_id) REFERENCES
> communication.template_version_parameter(template_version_parameter_id)
>
> This only happens in testing, and on a cold bootet database. The test
> database is constructed with pg_dump and restore on fresh postgres
> installation.
>

Sounds like warming up the cache, but still in a test environment you may
want to add auto_explain to your list of preloads and perhaps set it to
dump explain analyze when it hits a certain threshold.  Note that while
dumping the query plans has very little overhead, timing the query plan
nodes does impact performance in a negative way.

>
>
> Best Regards
> Kim Carlsen
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct

[Chris Travers]

On Wed, Sep 19, 2018 at 11:31 PM Bruce Momjian  wrote:

> On Wed, Sep 19, 2018 at 11:24:29AM +1000, Julian Paul wrote:
> > It's overly long and convoluted.
> >
> > "inclusivity" Is a ideologue buzzword of particular individuals that
> offer
> > very little value apart from excessive policing of speech and behaviour
> > assumed to be a problem where none exist.
> >
> > "Personal attacks and negative comments on personal characteristics are
> > unacceptable, and will not be permitted. Examples of personal
> > characteristics include, but are not limited to age, race, national
> origin
> > or ancestry, religion, gender, or sexual orientation."
> >
> > So just leaving it at "Personal attacks" and ending it there won't do
> > obviously. I'm a big advocate of people sorting out there own personal
> > disputes in private but...
> >
> > "further personal attacks (public or *private*);"
> >
> > ...lets assume people don't have the maturity for that and make it all
> > public.
> >
> > "may be considered offensive by fellow members" - Purely subjective and
> > irrelevant to a piece of community software.
>
> You might notice that a bullet list was removed and those example items
> were added 18 months ago:
>
>
> https://wiki.postgresql.org/index.php?title=Code_of_Conduct&diff=31924&oldid=29402
>
> I realize that putting no examples has its attractions, but some felt
> that having examples would be helpful.  I am not a big fan of the
> "protected groups" concept because it is often exploited, which is why
> they are listed more as examples.
>

I suspect most of us could probably get behind the groups listed in the
antidiscrimination section of the European Charter of Fundamental Rights at
least as a compromise.

Quoting the  English version:

"Any discrimination based on any ground such as sex, race, colour, ethnic
or social origin, genetic features, language, religion or belief, political
or any other opinion, membership of a national minority, property, birth,
disability, age or sexual orientation shall be prohibited."

The inclusion of "political or any other opinion" is a nice addition and
prevents a lot of concern.

>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I.  As I am, so you will be. +
> +  Ancient Roman grave inscription +
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

ve lawyers. More likely we waste a lot of hot air. Like
> this mail, probably.
>
> There are intangible but very real (IMO) costs to being a community that
> welcomes an unhealthy and hostile communication style, harassment and
> personal attacks in the guise of technical argument, bullying defended as
> making sure you have the right stuff to survive in a "meritocracy", etc.
> Thankfully we are generally not such a community. But try asking a few
> women you know in the Postgres community - if you can find any! - how their
> experience at conferences has been. Then ask if maybe there are still a few
> things we could work on changing.
>
> I've found it quite confronting dealing with some of the more heated
> exchanges on hackers from some of our most prominent team members. I've
> sent the occasional gentle note to ask someone to chill and pause before
> replying, too. And I've deserved to receive one a couple of times, though I
> never have, as I'm far from free from blame here.
>

But that happens to everyone.  Male, female, etc.  And yes, such notes are
good.

I think you are right to point to harassment though.  I have seen people in
this community resort to some really aggressive tactics with other members,
particularly off-list (and sometimes in person).  The interactions on the
postgresql.org infrastructure have always been good except in a few cases.
That is the one really important reason for enforcement against off-list
actions.  It is not (and can't be) about politics.  It has to be about
personally directed campaigns of harassment.

>
> People love to point to LKML as the way it "must" be done to succeed in
> software. Yet slowly that community has also come to recognise that verbal
> abuse under the cloak of technical discussion is harmful to quality
> discussion and drives out good people, harming the community long term.
> Sure, not everything has to be super-diplomatic, but there's no excuse for
> verbal bullying and wilful use of verbal aggression either. As widely
> publicised, even Linus has recently recognised aspects of this, despite
> being the poster child of proponents of abusive leadership for decades.
>
> We don't have a culture like that. So in practice, I don't imagine the CoC
> will see much use. The real problematic stuff that happens in this
> community happens in conference halls and occasionally by private mail,
> usually in the face of a power imbalance that makes the recipient/victim
> reluctant to speak out. I hope a formal CoC will give them some hope
> they'll be heard if they do take the personal risk to speak up. I've seen
> so much victim blaming in tech that I'm not convinced most people
> experiencing problems will be willing to speak out anyway, but hopefully
> they'll be more so with a private and receptive group to talk to.
>

I will say also that where I have seen the most problems I would not speak
out in detail because I don't feel like they rise to a level where the CoC
should be involved.


>
> Let me be clear here, I'm no fan of trial by rabid mob. That's part of why
> something like the CoC and a backing body is important. Otherwise people
> are often forced to silently endure, or go loudly public. The latter tends
> to result in a big messy explosion that hurts the community, those saying
> they're victim(s) and the alleged perpetrator(s), no matter what the facts
> and outcomes. It also encourages people to jump on one comment and run way
> too far with it, instead of looking at patterns and giving people chances
> to fix their behaviour.
>
> I don't want us to have this:
> https://techcrunch.com/2013/03/21/a-dongle-joke-that-spiraled-way-out-of-control/
> . Which is actually why I favour a CoC, one with a resolution process and
> encouragement toward some common sense. Every player in that story was an
> idiot, and while none deserved the abuse and harrassment that came their
> way, it's a shame it wan't handled by a complaint to a conference CoC group
> instead.
>
> I'd like the CoC to emphasise that while we don't want to restrain people
> from "calling out" egregious behaviour, going via the CoC team is often
> more likely to lead to constructive communication and positive change.
>

Agreed on this.

My objection to the additional wording is simply that a) I think it does
not tackle the problem it needs to tackle, and b) creates a claim which
covers a bunch of things that it really shouldn't.  It's a serious bug and
I still hope it gets fixed before it causes problems.

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


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct

[Chris Travers]

On Tue, Sep 18, 2018 at 8:35 PM Tom Lane  wrote:

> Stephen Frost  writes:
> > I would ask that you, and anyone else who has a suggestion for how to
> > improve or revise the CoC, submit your ideas to the committee by
> > email'ing c...@postgresql.org.
> > As was discussed previously, the current CoC isn't written in stone and
> > it will be changed and amended as needed.
>
> The change process is spelled out explicitly in the CoC document.
>
> I believe though that the current plan is to wait awhile (circa 1 year)
> and get some experience with the current version before considering
> changes.
>

My $0.02:

If you are going to have a comment period, have a comment period and
actually deliberate over changes.

If you are going to just gather feedback and wait a year, use some sort of
issue system.

Otherwise, there is no reason to think that feedback gathered now will have
any impact at all in the next revision.

>
>     regards, tom lane
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct

[Chris Travers]

On Tue, Sep 18, 2018 at 4:35 PM Tomas Vondra 
wrote:

> On 09/18/2018 01:47 PM, James Keener wrote:
> >  > following a long consultation process
> >
> > It's not a consultation if any dissenting voice is simply ignored.
> > Don't sugar-coat or politicize it like this -- it was rammed down
> > everyone's throats. That is core's right, but don't act as everyone's
> > opinions and concerns were taken into consideration.
>
> I respectfully disagree.
>
> I'm not sure which dissenting voices you think were ignored, but from
> what I've observed in the various CoC threads the core team took the
> time to respond to all comments. That does not necessarily mean the
> resulting CoC makes everyone happy, but unfortunately that's not quite
> possible. And it does not mean it was not an honest consultation.
>
> IMO the core team did a good job in listening to comments, tweaking the
> wording and/or explaining the reasoning. Kudos to them.
>

I said I would stand aside my objections after the last point I mentioned
them but I did not feel that my particular objection and concern with
regard to one specific sentence added got much of a hearing.  This being
said, it is genuinely hard to sort through the noise and try to reach the
signal.  I think the resurgence of the debate about whether we need a code
of conduct made it very difficult to discuss specific objections to
specific wording.  So to be honest the breakdown was mutual.

>
> > There are a good number of folks who are concerned that this CoC is
> > overreaching and is ripe for abuse. Those concerns were always
> > simply, plainly, and purposely ignored.
> No, they were not. There were multiple long discussions about exactly
> these dangers, You may dislike the outcome, but it was not ignored.
>

Also those of us who had specific, actionable concerns were often drowned
out by the noise.  That's deeply unfortunate.

I think those of us who had specific concerns about one specific sentence
that was added were drowned out by those who seemed to be opposed to the
idea of a code of conduct generally.

I would have appreciated at least a reason why the concerns I had about the
fact that the addition a) doesn't cover what it is needs to cover, and b)
will attract complaints that it shouldn't cover was not considered valid.
But I can understand that given the noise-to-signal ratio of the discussion
made such discussion next to impossible.

Again I find that regrettable.

>
> >  > Please take time to read and understand the CoC, which is intended to
> > ensure that PostgreSQL remains an open and enjoyable project for anyone
> > to join and participate in.
> >
> > I sincerely hope so, and that it doesn't become a tool to enforce social
> > ideology like in other groups I've been part of. Especially since this
> > is the main place to come to get help for PostgreSQL and not a social
> club.
> >
>
> Ultimately, it's a matter of trust that the CoC committee and core team
> apply the CoC in a careful and cautious way. Based on my personal
> experience with most of the people involved in both groups I'm not
> worried about this part.
>

I would actually go further than you here.  The CoC committee *cannot*
apply the CoC in the way that the opponents fear.  The fact is, Europe has
anti-discrimination laws regarding social and political ideology (something
the US might want to consider as it would help avoid problems on this list
;-) ).  And different continents have different norms on these sorts of
things.  Pushing a social ideology via the code of conduct would, I
suspect, result in everything from legal action to large emerging markets
going elsewhere.  So I don't think ti is a question of "trust us" but
rather that the community won't let that sort of abuse happen no matter who
is on the CoC committee.

>
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Logical locking beyond pg_advisory

[Chris Travers]

On Mon, Sep 17, 2018 at 6:04 PM marcelo  wrote:

>
>
> I´m using an ORM (Devart´s) to access the database, so, I cannot "select
> ... FOR UPDATE". The application paradigm is that a user have a list of
> records (after a query) and she could update or delete any of them as the
> business rules allows it. So, at least an advisory lock is a must.
> I´m convinced by now: I would stay with advisory locks... expecting no app
> crash could occur...
>

I would say to fix this in the ORM rather than reinvent what the database
already gives you in the database.



> Thank you all.
> Marcelo
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
>  Libre
> de virus. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
> <#m_-9091154853724945458_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Mon, Sep 17, 2018 at 6:08 PM Steve Litt 
wrote:

> On Mon, 17 Sep 2018 17:39:20 +0200
> Chris Travers  wrote:
>
>
> > Exactly.  And actually the first sentence is not new.  The second one
> > is a real problem though.  I am going to try one last time at an
> > additional alternative.
> >
> > " 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 of conduct covers all interaction between
> > community members on the postgresql.org infrastructure.  Conduct
> > outside the postgresql.org infrastructure may call the Code of
> > Conduct committee to act as long as the interaction (or interaction
> > pattern) is community-related, other parties are unable to act, and
> > the Code of Conduct committee determines that it is in the best
> > interest of the community to apply this Code of Conduct."
>
> Chris,
>
> Would you be satisfied with the CoC if the current 2nd paragraph of the
> Introduction were replaced by the paragraph you wrote above?
>

Yes.  Or something like it.  It need not be exact.

I recognize a need  to be able to take enforcement to some areas off-list
activity, for what it's worth.

>
>
> SteveT
>
> Steve Litt
> September 2018 featured book: Quit Joblessness: Start Your Own Business
> http://www.troubleshooters.com/startbiz
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Mon, Sep 17, 2018 at 5:28 PM Joshua D. Drake 
wrote:

> 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)."
>

Exactly.  And actually the first sentence is not new.  The second one is a
real problem though.  I am going to try one last time at an additional
alternative.

" 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 of conduct covers all interaction between community
members on the postgresql.org infrastructure.  Conduct outside the
postgresql.org infrastructure may call the Code of Conduct committee to act
as long as the interaction (or interaction pattern) is community-related,
other parties are unable to act, and the Code of Conduct committee
determines that it is in the best interest of the community to apply this
Code of Conduct."

This solves a number of important problems.

1.  It provides a backstop (as Tom Lane suggested was needed) against a
conference refusing to enforce their own code of conduct in a way the
community finds acceptable while the current wording does not provide any
backstop as long as there is a code of conduct for a conference.
2.  It provides a significant barrier to applying the code of conduct to,
say, political posts on, say, Twitter.
3.  It preserves the ability of the Code of Conduct Committee to act in the
case where someone takes a pattern of harassment off-list and
off-infrastructure.  And it avoids arguing whether Facebook's Community
Standards constitute "another Code of Conduct that takes precedence."

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

Fascinating that this would, on its face, not apply to a harassment
campaign carried out over twitter, but it would apply to a few comments
made over drinks at a bar.

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

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Logical locking beyond pg_advisory

[Chris Travers]

On Mon, Sep 17, 2018 at 5:09 PM Merlin Moncure  wrote:

> On Sun, Sep 16, 2018 at 3:53 PM marcelo  wrote:
> >
> > I need a mechanism of "logical locking" more ductile than the
> pg_advisory family.
> > I'm thinking of a table ("lock_table") that would be part of the
> database, with columns
> > * tablename varchar - name of the table "locked"
> > * rowid integer, - id of the row "locked"
> > * ownerid varchar, - identifier of the "user" who acquired the lock
> > * acquired timestamp - to be able to release "abandoned" locks after a
> certain time
> >
> > and a group of functions
> > 1) lock_table (tablename varchar, ownerid varchar) bool - get to lock
> over the entire table, setting rowid to zero
> > 2) unlock_table (tablename varchar, ownerid varchar) bool - unlock the
> table, if the owner is the recorded one
> > 3) locked_table (tablename varchar, ownerid varchar) bool - ask if the
> table is locked by some user other than the ownerid argument
> > 4) lock_row (tablename varchar, rowid integer, ownerid varchar) bool -
> similar to pg_try_advisory_lock
> > 5) unlock_row (tablename varchar, rowid integer, ownerid varchar) bool -
> similar to pg_advisory_unlock
> > 6) unlock_all (ownerid varchar) bool - unlock all locks owned by ownerid
> >
> > The timeout (default, maybe 15 minutes) is implicitly applied if the
> lock is taken by another user (there will be no notification).
> > Redundant locks are not queued, they simply return true, may be after an
> update of the acquired column.
> > Successful locks insert a new row, except the rare case of a timeout,
> which becomes an update (ownerid and acquired)
> > Unlock operations deletes the corresponding row
> >
> > My question is double
> > a) What is the opinion on the project?
> > b) What are the consequences of the large number of inserts and deletions
> > c) Performance. In fact, pg_advisory* implies a network roundtrip, but
> (I think) no table operations.
>
> Why can't you use the advisory lock functions?  The challenge with
> manually managed locks are they they are slow and you will lose the
> coordination the database provides you.  For example, if your
> application crashes you will have to clean up all held locks yourself.
> Building out that infrastructure will be difficult.
>

First, I think in an ideal world, you wouldn't handle this problem with
either approach but sometimes you have to.

I have done both approaches actually.  LedgerSMB uses its own lock table
because locks have to persist across multiple HTTP requests and we have
various automatic cleanup processes.

When I was working on  the queue management stuff at Novozymes we used
advisory locks extensively.

These two approaches have serious downsides:
1.  Lock tables are *slow* and require careful thinking through cleanup
scenarios.  In LedgerSMB we tied to the application session with an ON
DELETE event that would unlock the row.  We estimated that for every 2
seconds that the db spent doing useful work, it spent 42 seconds managing
the locks.  Additionally the fact that locks take effect on snapshot
advance is a problem here.

2.  In my talk, "PostgreSQL at 10TB and  Beyond" I talk about a problem we
had using advisory locks for managing rows that were being processed for
deletion.  Since the deletion was the scan for items at the head of an
index, under heavy load we could spend long enough checking dead rows that
the locks could go away with our snapshot failing to advance.  This would
result in duplicate processing.  So the fact that advisory locks don't
really follow snapshot semantics is a really big problem here since it
means you can have race conditions in advisory locks that can't happen with
other locking issues.  I still love advisory locks but they are not a good
tool for this.

The real solution most of the time is actually to lock the rows by
selecting FOR UPDATE and possibly SKIP LOCKED.  The way update/delete row
locking in PostgreSQL works is usually good enough except in a few rare
edge cases.  Only in *very rare* cases do lock tables or advisory locks
make sense for actual row processing.

>
> merlin
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

ing that could be used to apply
pressure from outside to get rid of community members for activity that is
not related to PostgreSQL (in particular, unrelated political involvement,
opinions, and participation).

If you aren't open to rewriting even that one sentence, I hope maybe you
can leave that sentence off and assert that it is up to the Code of Conduct
community to develop the scope of application based on actual complaints
and circumstances.

Again for reference the only change I am objecting to is the addition of "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 don't think that sentence solves the
problems you are trying to solve, and I think it creates new ones.

However I have said my piece.  Unless there are replies that provide
something new for me to add, I won't continue arguing over that from here.

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Sat, Sep 15, 2018 at 4:47 AM James Keener  wrote:

>
>
> The preceding's pretty simple. An attacker goes after an individual,
>> presumably without provocation and/or asymetrically. The attacked
>> person is on this mailing list. IMHO this attacker must choose between
>> continuing his attacks, and belonging to the Postgres community.
>>
>> What's tougher is the person who attacks groups of people.
>>
>>
> The preceding's pretty simple. An "attacker" voices their political
> opinions
> or other unorthodoxy or unpopular stance, but in no way directs it at the
> postgres user base or on a postgres list. The "attacked"
> person is on this mailing list. IMHO this "attacker" must choose between
> continuing to voice their opinion, and belonging to the Postgres community.
>

The protection there is a culturally diverse code of conduct committee who
can then understand the relationship between politics and culture.  And
just to note, you can't solve problems of abuse by adopting mechanistically
applied rules.

Also a lot of the major commercial players have large teams in areas where
there is a legal right to not face discrimination on the basis of political
opinion.  So I don't see merely expressing an unpopular political opinion
as something the code of conduct committee could ever find actionable, nor
do I think political donations or membership in political or religious
organizations etc would be easy to make actionable.

But I understand the sense of insecurity.  Had I not spent time working in
Asia and Europe, my concerns would be far more along these lines.  As it
is, I don't think the code of conduct committee will allow themselves to be
used to cause continental splits in the community or to internationalize
the politics of the US.

I think the bigger issue is that our community *will* take flak and
possibly be harmed if there is an expectation set that picking fights in
this way over political opinions is accepted.  Because while I don't see
the current community taking action on the basis of political views, I do
see a problem more generally with how these fights get picked and would
prefer to see some softening of language to protect the community in that
case.  But again, I am probably being paranoid.

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Fri, Sep 14, 2018 at 7:47 PM Peter Geoghegan  wrote:

> On Fri, Sep 14, 2018 at 10:31 AM, Dimitri Maziuk 
> wrote:
> > So let me get this straight: you want to have a "sanctioned" way to deny
> > people access to postgresql community support channel?
>
> Yes.
>
> > "Because
> > somebody who may or may not be the same person, allegedly said something
> > somewhere that some other tweet disagreed with on faceplant"?
> >
> > Great plan if you do for-pay postgresql support for the living.
>
> You can make your own conclusions about my motivations, just as I'll
> make my own conclusions about yours. I'm not going to engage with you
> on either, though.
>

With regard to the  concerns about authoritarianism, I have to defend the
Code of Conduct here.

It's not anything of the above.  The PostgreSQL project has a pretty good
track record of ensuring that people can participate across boundaries of
culture, ethnicity, political ideology (which is always informed by culture
and ethnicity), and the like.  On the whole I trust the committee to make
sound judgments.

The thing is, yes it is scary that someone might be effectively denied
access to commons based on false accusations, but it is also concerning
that people might be driven away from commons by aggressive harassment (on
or off list) or the like.  The code of conduct is a welcome step in that
goal.  I think we should trust long-standing communities with a track
record of being generally cultivating access to the commons with decisions
which foster that.   The fact is, at least I would hope we all agree that

This is basic governance.  Communities require arbitration and management
of the economic commons we build together and this is a part of that.  I am
pretty sure that's why the expansive wording was included.  And I support
the right of the committee to act even for off-list behavior when it is
appropriate to do so.  That part, I am not questioning.  I think that's
important.

So I think a lot of the hysteria misses the point.  We have good people.
We have a generally good track record of getting along.  We have a track
record of not being mean to eachother because of differences in political,
social, religious, etc. belief.  The committee as a custodian of this
community can't really take the hard sides on divisive issues that we might
expect in, say, an American corporation like Mozilla or Google.  I think
people who worry about this don't get the weight of responsibility that
will be placed on such individuals to support a breathtakingly diverse
international project and keep the peace, giving people room for civic
engagement even on divisive issues.

And frankly I am probably being paranoid here though I find paranoia is a
good thing when it comes to care of databases and computer systems.  But I
do worry about the interactions between the PostgreSQL community and the
larger world with things worded this way.



> --
> Peter Geoghegan
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Fri, Sep 14, 2018 at 4:51 PM Dave Page  wrote:

>
>
> On Fri, Sep 14, 2018 at 3:43 PM, Joshua D. Drake 
> wrote:
>
>> 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?
>>
>
> 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?
>
> And in the end, a broad scope is required to some extent.

I want to be clear about where my concern and objection is:

1.  I think PostgreSQL, as an international project with people from many
different walks of life and different cultures needs to stay out of culture
war topics or assigning truth values to political viewpoints to the extent
absolutely possible.  We do this today and we must continue to do this.
2.  Compared to the rest of the world, people from my culture (the US) have
a tendency to take disagreements regarding political policies, social
theories, etc. personally and see abuse/attack where mere disagreement was
present.  People making complaints aren't necessarily acting in bad faith.
3.  If we don't set the expectation ahead of time that we remain
pluralistic in terms of political philosophy, culture, then it is way too
easy to end up in a situation where people are bringing up bad press for
failing to kick out people who disagree with them.

Like it or not there are precedents for this in the open source community,
such as the dismissal of Brendan Eich, and in an international project with
developers from all kinds of cultures with different views on deeply
divisive issues, such conflicts could hurt our community.

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Fri, Sep 14, 2018 at 4:16 PM Tom Lane  wrote:

> [ Let's try to trim this discussion to just -general, please ]
>
> Robert Eckhardt  writes:
> > On Fri, Sep 14, 2018 at 9:41 AM, Adrian Klaver
> >  wrote:
> >> On 9/14/18 1:31 AM, Chris Travers 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 <
> 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)."
>
> >> 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.
>
> Actually, that addition was in response to concerns that the previous
> version didn't delimit the intended scope of the document *at all*.
> So I would say it's more restricted now than the previous version.
>
> I feel that most of the concerns being raised today are straw men.
> If the PG lists were a place for political discussion, there'd be
> valid points to worry about as to whether a CoC might be used to
> stifle free speech.  But every example that's been given has been
> not merely off-topic but wildly so, so I don't find the discussion
> to be very realistic.
>

If the code of conduct limited conduct that related to postgresql.org
infrastructure, I would agree.  This one explicitly includes all kinds of
interactions which are beyond that.

I assume "all interaction between members" could include having a few beers
at a pub, or being in an argument over the scope of human rights on
facebook, and I think there are people who will read it that way.

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Fri, Sep 14, 2018 at 4:14 PM Dave Page  wrote:

>
>
> On Fri, Sep 14, 2018 at 3:08 PM, Joshua D. Drake 
> 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
>>>
>>> (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.
>>
>
> 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.
>

 Actually, the easier case here is not being abusive to MySQL users, as the
code of conduct really doesn't clearly cover that anyway.  The easier case
is where two people have a feud and one person carries on a harassment
campaign over various forms of social media.  The current problem is:

1.  The current code of conduct is not clear as to whether terms of
service/community standards of, say, Reddit, supersede or not, and
2.  The community has to act (even if it is includes behavior at a
conference which has its own code of conduct)

So I think the addition is both over inclusive and under inclusive.   It is
over inclusive because it invites a certain group of (mostly American)
people to pick fights (not saying this is all Americans).  And it is under
inclusive because there are cases where the code of conduct *should* be
employed when behavior includes behavior at events which might have their
own codes of conduct.

On the other side, consider someone carrying on a low-grade harassment
campaign against another community member at a series of conferences where
each conference may not amount to a real actionable concern but where the
pattern as a whole might.  There's the under inclusive bit.

So I don't like this clause because I think it invites problems and doesn't
solve issues.
-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Fri, Sep 14, 2018 at 11:45 AM Ilya Kosmodemiansky 
wrote:

> On Fri, Sep 14, 2018 at 10:31 AM, Chris Travers 
> 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)."
> >
> > 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 point has nothing to do with _correct_ discussions or
> public tweets.
>
> If one community member tweets publicly and in a way which abuses
> other community members, it is obvious CoC violation. It is hard to
> imagine healthy community if someone interacts with others  correctly
> on the list or at a conference because the CoC stops him doing things
> which he will do on private capacity to the same people when CoC
> doesnt apply.
>
> If someone reports CoC violation just because other community member's
> _correct_ public tweet or whatsoever  expressed different
> political/philosophical/religious views, this is a quite different
> story. I suppose CoC committee and/or Core team in this case should
> explain the reporter the purpose of CoC rather than automatically
> enforce it.
>

So first, I think what the clause is trying to do is address cases where
harassment targeting a particular community member takes place outside the
infrastructure and frankly ensuring that the code of conduct applies in
these cases is important and something I agree with.

However, let's look at problem cases:

"I am enough of a Marxist to see gender as a qualitative relationship to
biological reproduction and maybe economic production too."

I can totally imagine someone arguing that such a tweet might be abusive,
and certainly not "correct."

Or consider:

"The effort to push GLBT rights on family-business economies is nothing
more than an effort at corporate neocolonialism."

Which would make the problem more clear.  Whether or not a comment like
that occurring outside postgresql.org infrastructure would be considered
"correct" or "abusive" is ultimately a political decision and something
which, once that fight is picked, has no reasonable solution in an
international and cross-cultural product (where issues like sexuality,
economics, and how gender and individualism intersect will vary
dramatically across members around the world).  There are people who will
assume that both of the above statements are personally offensive and
attacks on the basis of gender identity even if they are critiques of
political agendas severable from that.  Worse, the sense of attack
themselves could be seen as attacks on culture or religions of other
participants.

Now neither of these comments would be tolerated as viewpoints expressed on
PostgreSQL.org email lists because they are off-topic, but once one expands
the code of conduct in this way they become fair game.  Given the way
culture war issues are shaping up particularly in the US, I think one has
to be very careful not to set an expectation that this applies to literally
everything that anyone does anywhere.

So maybe something more like:

"Conduct that occurs outside the postgresql.org infrastructure is not
automatically excluded from enforcement of this code of conduct.  In
particular if other parties are unable to act, and if it is, on balance, in
the interest of the global community to apply the code of conduct, then the
code of conduct shall apply."

>
> > --
> > Best Wishes,
> > Chris Travers
> >
> > Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
> > lock-in.
> > http://www.efficito.com/learn_more
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Fri, Sep 14, 2018 at 10:31 AM Chris Travers 
wrote:

>
>
> On Wed, Sep 12, 2018 at 10:53 PM Tom Lane  wrote:
>
>> I wrote:
>> > Stephen Frost  writes:
>> >> We seem to be a bit past that timeline...  Do we have any update on
>> when
>> >> this will be moving forward?
>> >> Or did I miss something?
>>
>> > Nope, you didn't.  Folks have been on holiday which made it hard to keep
>> > forward progress going, particularly with respect to selecting the
>> initial
>> > committee members.  Now that Magnus is back on shore, I hope we can
>> > wrap it up quickly --- say by the end of August.
>>
>> 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.
>

Suggestion instead:

"Personally directed behavior is not automatically excluded from this code
of conduct merely because it does not happen on the postgresql.org
infrastructure.  In the case where a dispute of such a nature occurs
outside said infrastructure, if other parties are unable to act, this code
of conduct may be considered where it is, on the balance, in the interest
of the global community to do so."

This preserves the ability to act, without basically providing the same
invitation for problems.


>> I think we are about ready to announce the initial membership of the
>> CoC committee, as well, but that should be a separate post.
>>
>> regards, tom lane
>>
>>
>
> --
> Best Wishes,
> Chris Travers
>
> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
> lock-in.
> http://www.efficito.com/learn_more
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Wed, Sep 12, 2018 at 10:53 PM Tom Lane  wrote:

> I wrote:
> > Stephen Frost  writes:
> >> We seem to be a bit past that timeline...  Do we have any update on when
> >> this will be moving forward?
> >> Or did I miss something?
>
> > Nope, you didn't.  Folks have been on holiday which made it hard to keep
> > forward progress going, particularly with respect to selecting the
> initial
> > committee members.  Now that Magnus is back on shore, I hope we can
> > wrap it up quickly --- say by the end of August.
>
> 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 we are about ready to announce the initial membership of the
> CoC committee, as well, but that should be a separate post.
>
> regards, tom lane
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Postgresql

[Chris Travers]

On Sun, Aug 19, 2018 at 7:57 PM Sonam Sharma  wrote:

> Thank you! Can you please help me with any advantages/disadvantages.. my
> db size is less than 10gb. I am very new to this.
>

At 10GB you are unlikely to hit performance limitations that make you think
about how to use PostgreSQL more effectively.  You will have to avoid doing
the sorts of things you want to avoid doing on all databases.

DB2 and PostgreSQL are both very feature-rich databases but in somewhat
different directions.   For example, DB2 has more storage options than
PostgreSQL does.   But again at 10GB it is unlikely you will have to worry
about these.

Where PostgreSQL really shines is in generally workload tuning and
programmability.  Especially programmability.  PostgreSQL is extremely
extensible and this means not only can you build your own extensions for
whatever you need to do (assuming some skill) but there are tons of
extensions you can use for free including great ones like PostGIS.

Generally you can expect at 10GB to have to avoid needless complexity like
repeated sequential scans. Indexing becomes a bit important.  By 100GB you
have to pay significant attention to index strategies and disk access.  I
am aware of single databases of up to 50TB in size used in production and
federated storage environments into the petabytes.

In general I can think of no reason not to use PostgreSQL unless your
salary depends on paying license fees

>
> On Sun, Aug 19, 2018, 11:25 PM Adrian Klaver 
> wrote:
>
>> On 08/19/2018 10:53 AM, Sonam Sharma wrote:
>> >
>> >
>> >
>> >
>> > I am planning to migrate my db from db2 to postgresql. Before that I
>> > wanted to know is postgresql better than db2? Is it completely free ?
>> > Any performance issues or any limitations?
>>
>> Yes, yes, depends
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Replication protocol question for logical replication.

[Chris Travers]

While we are building a streaming logical backup solution for our data
warehouse environment, we made a surprising discovery that select
statements could be executed in a replication connection but I cannot find
any clear documentation that says this is supported but I think it ought to
be since that way you can, for example, copy a table to stdout.

Is this expected?

Chriss-MBP:postgres christravers$ PGSSLMODE=prefer psql "host=localhost
port=5432 dbname=postgres replication=database" -c "IDENTIFY_SYSTEM"

  systemid   | timeline |  xlogpos   |  dbname

-+--++--

 6562045063708868894 |1 | 0/CC9F4920 | postgres

(1 row)


Chriss-MBP:postgres christravers$ PGSSLMODE=prefer psql "host=localhost
port=5432 dbname=postgres replication=database" -c "SELECT VERSION()"

version


---

 PostgreSQL 10.4 on x86_64-apple-darwin17.5.0, compiled by Apple LLVM
version 9.1.0 (clang-902.0.39.2), 64-bit

(1 row)


I am asking because if this is clearly intended to be supported we would
like to work with upstream library vendors to make sure this functionality
is included. My reading of the docs is that parameterized queries are not
possible but simple queries are.  Is this correct?

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Fri, Jun 8, 2018 at 11:18 AM, Justin Clift  wrote:

> On 2018-06-08 09:46, Simon Riggs wrote:
> 
>
>> Would it not be better to consider arbitration as the first step in
>> dispute resolution?
>>
>
> This bit sounds like it'd need to be on a case-by-case basis.
>
> It's pretty easy to imagine scenarios where arbitration wouldn't be
> appropriate.
>
> Whether or not they come about in the PG Community or not is a
> different matter.
>
> My point being that arbitration isn't necessarily automatically the
> right direction.
>
> I'd probably leave it up to the CoC team/people to figure it out. :)


+1

If it were me I would just say that CoC has an obligation to try in good
faith to resolve things in line with the common interest of an
international community and leave it at that.

>
>
> + Justin
>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Fri, Jun 8, 2018 at 7:53 AM, Tom Lane  wrote:

> Christophe Pettus  writes:
> > 2. I don't think that there is a country where someone being driven out
> of a technical community by harassment is an acceptable local value.
>
> Yeah, this.  People that I've known and respected, and who did not seem
> at all thin-skinned, have left this community because of harassment.
> We need to try to stop that, not because of "political correctness",
> but to ensure that our community has a long-term future.
>
> It's not a simple thing, and I don't envy the CoC committee's task.
> For instance, I hope we can all agree that sexual harassment is
> unacceptable --- but I can imagine that what one person thought was
> friendly banter was harassment to the other, particularly if different
> cultures are involved.  The committee will likely have to sort out such
> situations and try to reconcile the two people without either starting a
> war or driving away either person.  They may not always succeed.  But not
> trying is not a better answer.


For what its worth, I am 100% in agreement with everything Tom just said
here.
-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Fri, Jun 8, 2018 at 7:09 AM, Christophe Pettus  wrote:

>
> > On Jun 7, 2018, at 21:00, Gavin Flower 
> wrote:
> >
> >> I have to say that this seems like a red herring to me.
> > Not entirely.  American web sites tend to insist on weird date format,
> and insist on the archaic imperial units rather than the metric system that
> most people in the world use.
>
> Then you will be pleased to know that neither writing dates day-of-month
> first, nor using meters, will be Code of Conduct violations. :)
>

However, this is a symptom of a much larger and deeper set of issues.
Americans, particularly in the cities, tend to take deep offense to
political disagreements and this happens on both sides, because in the US,
politics is often extremely binary.

A closely related problem here is that in the US, this often constitutes a
sort of signaling as to whether someone is going to get a fair chance or
not.  This is very different from Europe, where political discrimination is
barred under the European Charter of Fundamental Rights.

>
> > For example try defining something simple, like what is a car!
> [...]
> >
> > Try defining success at university
>
> It is equally unlikely that the Code of Conduct committee will need to
> decide what a car is, or whether or not someone has succeeded at university.
>
> I'm not trying to be snide, but this does seem to be exactly what I was
> talking about: When asked for examples of cultural differences that might
> run afoul of the CoC, the examples don't seem to be either relevant (i.e.,
> they are not things the CoC committee will have to address), or are clearly
> contextual in a way that a human will have no trouble understanding.
>

I think they are likely to have to decide whether wearing a MAGA hat at a
conference is allowed (and now that I know the people who did this were
South Africans I personally feel bad about not conversing with them).

They might also have to decide whether statements like the following is
disparaging based on protected group characteristics:

"I am enough of a Marxist to see gender as a qualitative relationship to
biological reproduction, and this is something GLBT groups don't fit into
very well."

>
> > I've called a friend of mine a bastard, but he took it as a mark of
> respect in the context of our discussion.
>
> This is why we have human beings, rather than a regex, forming the Code of
> Conduct committee.  It's important to remember that the CoC committee is
> not going to be going around policing the community for potential
> violations; their job is to resolve actual situations between real people.
> It's not their job to define values; it's their job to resolve situations.
> In my experience in dealing with CoC issues, the situations (while often
> complex) are rarely of the form, "This word does not mean anything bad
> where I come from."
>

This is true but it is needed that the committee is culturally diverse and
advertised as such.  Otherwise I am concerned that it would signal to some
people that a certain sort of abuse would be permitted.

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


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Thu, Jun 7, 2018 at 2:14 AM, Jan Claeys  wrote:

> On Wed, 2018-06-06 at 07:27 +0200, Chris Travers wrote:
> > The real fear here is the code of conduct being co-opted as a weapon
> > of world-wide culture war and that's what is driving a lot of the
> > resistance here.  This is particularly an American problem here and
> > it causes  a lot of resistance among people who were, until the
> > second world war, subject to some pretty serious problems by colonial
> > powers.
>
> I don't see how this could happen any more than it already can, because
> as far as I can tell the goal is not to discuss complaints in public;
> the committee would handle cases in private.  And if committee members
> would try to abuse their power, I'm pretty sure they would be removed.
>

Right.  I think the fears are overblown but you do have to remember that we
started this whole public side of the process when there was a real effort
by some in around open source to push contributor codes of conducts that
were expressly political (the Contributor Covenant for example) and in the
wake of Opalgate.

I do not doubt that at some point we will face the same.  I don't doubt
that such efforts will be unsuccessful.  But I do think they will put the
project through some public controversy and grief and so we are best off to
try to minimize the attack surface.

>
> > Putting a bunch of American lawyers, psychologists, sociologists,
> > marketers etc on the board in the name of diversity would do way more
> > harm than good.
>
> I didn't say they have to be American, and I didn't say there has to be
> a bunch of them.  I just said it would be good if there were also
> people who aren't (just only) developers, DBAs or other very technical
> people.
>

Ok I get what your concern is now.  I am not sure the formal qualifications
matter but I would agree that the committee needs to be staffed with people
we trust to be good "people people" rather than good "tech people."

>
>
> --
> Jan Claeys
>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Wed, Jun 6, 2018 at 2:07 AM, Jan Claeys  wrote:

> On Tue, 2018-06-05 at 16:45 +0200, Chris Travers wrote:
> > 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.
>
> Being international/intercultural certainly has some value, but I think
> it's at least as useful to have people with different competencies and
> professional backgrounds.
>
> For example: having some people who have a background in something like
> psychology, sociology, education, law, human resources, marketing, etc.
> (in addition to the likely much easier to find developers, DBAs and IT
> managers) would be valuable too.
>

Besides what the others have said I don't think this would help.

The real fear here is the code of conduct being co-opted as a weapon of
world-wide culture war and that's what is driving a lot of the resistance
here.  This is particularly an American problem here and it causes  a lot
of resistance among people who were, until the second world war, subject to
some pretty serious problems by colonial powers.

Putting a bunch of American lawyers, psychologists, sociologists, marketers
etc on the board in the name of diversity would do way more harm than good.


>
>
> --
> Jan Claeys
>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Tue, Jun 5, 2018 at 8:42 PM, James Keener  wrote:

> I accidentally didn't send this to the whole list.  I'll let Chris resend
> his response if he'd like.
>
> On Tue, Jun 5, 2018 at 1:58 PM, James Keener  wrote:
>
>> I think the fundamental outcome is likely to be that people who cause
>>> trouble are likely to get trouble.  This sort of case really doesn't worry
>>> me.  I am sure whoever is stirring the pot will be asked at least to cease
>>> doing so.
>>>
>>>
>> Are you implying that either of my RPCs are causing "trouble" for either
>> advancing a technical proposal, not wanting to change wording they feel is
>> clear and non-political, or for voicing their concerns that a proposal is
>> highly offensive?
>>
>
There's an old Icelandic mythic poem "Lokasenna" which describes what I
have seen happening very well.  If you come to the feast to pick fights,
fights is what one will get.

>
>> The whole point of the CoC is that people shouldn't feel like they're
>> causing "trouble" if they feel like they're being picked on or offended or
>> marginalized. That's specifically why people want them: they want to know,
>> or at least feel like, they'll be taken seriously if someone is
>> legitimately picking on them or marginalizing them.
>>
>> I complain a lot about the CoC, but I agree with Tom (I think it was) in
>> saying that there does need to be some written framework for how disputes
>> are handled by the organization. I just feel that CoC has, unfortunately,
>> become a politically charged term that often find themselves talking about
>> politically charged subjects instead of saying you should focus on
>> technical topics and not on the person when discussing a technical topic
>> and how a dispute will be handled if someone is misbehaving. I've seen them
>> used as weapons in real life and have watch disputes play out over the
>> internet, e.g. the famous push for opal to adop the Contributor Covenent by
>> someone not affiliated with the project and who (potentially/allegedly)
>> misunderstood a partial conversation they heard. (
>> https://github.com/opal/opal/issues/941).
>>
>> 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?
>>
>> Jim
>>
>
>
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.

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Tue, Jun 5, 2018 at 7:01 PM, James Keener  wrote:

> I'm sorry for the double post.
>
> > 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.
>
> What does fairly mean?
>
> Let's role play. I'll be a homophobic person.
>
> You've just submitted a proposal suggesting that we change master-master
> replication to be multi-partner replication. I've told you I don't like the
> wording because of it's implication of supporting homosexual marriage,
> which I believe to be a personal offense to me, my marriage, and my "deeply
> held religious beliefs". You tell me that's not your intent and that you do
> not plan to change your proposed wording. You continue to use the term in
> all correspondences on the list and I continually tell you that supporting
> gay marriage is offensive and that you need to not be so deeply offensive.
> I submit all our correspondences to the CoC committee and complain that
> you're purposely using language that is extremely offensive.
>
> What is a "fair" outcome? Should you be banned? Should you be forced to
> change the wording of your proposal that no one else has complained about
> and others support? What is a fair, just outcome?
>

I think the fundamental outcome is likely to be that people who cause
trouble are likely to get trouble.  This sort of case really doesn't worry
me.  I am sure whoever is stirring the pot will be asked at least to cease
doing so.

But let's look at all fairness in a more likely scenario where someone
involved in, say, Human Rights Campaign posts something arguing that
marriage is not a bond that binds parents to their children but something
that exists solely for the benefit of the spouses and a conservative from,
say, India, complains.  Do we ask the individual to change his or her
signature?

What happens if the signature proclaims that Tibet should be free and
Chinese folks on the list worry about ramifications for participating in
these cases?

But worse, what if by not taking sides, we say that this isn't big enough
for us to adjudicate and so the conservative from India puts up a quote on
his email signature citing Gandhi's view that accepting consent-based
morality to sexual contact leads to accepting homosexual contact, and this
leads to misery for everyone.  When challenged he points out it is just
social critique like the other signature.

Now what do we do?  Do we side with one or the other?  Or do we ban both or
refuse to get involved?  At that point there are no longer any good options
but I will state my preference would be to reiterate to both that we ought
to have a live-and-let-live culture and this applies to cultural
differences on concepts of gender and marriage.

This sort of thing will happen.  I have watched calls for pushing gay and
lesbian roles on television in the US lead to policies of censorship of
Western media in countries like Indonesia (where Glee among other shows are
now formally banned), and this is one issue which is incredibly divisive
throughout the world with a lot of people having very deep-seated feelings
on the issue, where one can expect small differences to lead to big
conflicts.  And I think we want to avoid wading into those conflicts.

>
> Jim
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Tue, Jun 5, 2018 at 6:21 PM, James Keener  wrote:

> I also think that a CoC focuses on the wrong things. If someone is
> disruptive, they need to be told to leave, just like in every forum ever.
>
> We should focus on ensuring that the code and documentation is free from
> slurs and culturally specific idioms. We should hold gatekeepers
> accountable for making decisions based on technical merit and not the
> person who proposed an idea or submitted a patch.
>
> We can't control the behavior of the internet as a whole. We can control
> our codebase and our gatekeepers.
>


I think in our case those fears are overblown.

There is a very well-founded fear among a lot of people of ideological
litmus tests being imposed on economic commons.  The current impetus for a
code of conduct here followed one attempt at that on some other projects.
On my blog I have discussed these things.  One can find them there.  I
think a whole lot of us understand that at some point there will be an
attempt to use our code of conduct to that end.  This has been discussed
before and one of the key points is that not  having a code of conduct
doesn't really protect us because the MO in these cases has been "Look at
that extremely offensive viewpoint!  You should have a code of conduct we
can use to throw him out!"  So having a code of conduct doesn't hurt and it
may provide a bulwark against some of the larger efforts in this regard.
In essence often not having a code of conduct is an encouragement for
people to push a politically charged code of conduct.  Having a politically
neutral code of conduct at least suggests we have rejected the politically
charged ones.

We are an international and largely politically neutral project.  I doubt
that as a community we would have tolerated trying to harass, for example,
either side in the recent Irish referendum to stop using PostgreSQL if they
were, or that we would tolerate an effort to politically hijack the
community for culture war issues, or trying to drive people out for trying
to form viable third parties in the US political landscape.  An
international CoC committee is our best defense against an effort to co-opt
the community in the way you are worried about.

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

On Sun, Jun 3, 2018 at 8:29 PM, 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


Reading through this, it seems like a generally useful and fair set of
rules.I want to offer some comments though about some specific issues
here.

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

One of the issues I see here is the issue of cross-cultural attacks, and a
certain American slant on where inappropriate behavior might begin when it
comes to disparaging remarks.  In my blog I covered one hypothetical about
an argument via email signatures over a culture war issue like same-sex
marriage for example where one side might put forth an American viewpoint
and someone else might condemn sexual ethics that permit accepting
homosexual contact using, say, Gandhi as an authority.

This is a serious issue.  It won't go away.  There will be, at some point,
Americans trying to push these sorts of issues via email signatures and the
like, and it will cause conflict.  The current code of conduct makes it
very clear that the second viewpoint is not welcome, but is very ambiguous
on the first viewpoint.  I.e. arguing that marriage shouldn't be a bond
that binds parents to their children but solely exists for the benefit of
the spouses could be a cultural attack and hence an attack on the national
backgrounds of many people in the community around the world but that isn't
clear.  My concern is that the current code of conduct will lead to these
disputes ensuring that the CoC community gets to decide who gets to feel
like they are not protected, and I think we all agree that's not what we
want.

For this reason I think the introduction should be left as is, but I would
suggest one of two modifications to the second section (Inclusivity):

1.  Either include culture as a part of the protected criteria to indicate
that this definitely is protected and that culture-war pushing will not be
tolerated any more than any other disturbance of the peace, or
2.  Note that trolling or divisive political behavior likely to threaten
the peace will be dealt with as a violation of the code of conduct, or
3.  Simply demand civility and leave a lot of the examples out.

On to the code of conduct committee:

This needs to be explicitly international and ideally people from very
different cultures.  This is the best protection against one small group
within one country deciding to push a political agenda via the Code of
Conduct.  I would recommend adding a note here that the committee will be
international and culturally diverse, and tasked with keeping the peace and
facilitating a productive and collegial environment.


> 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.
>
> regards, tom lane
>
> [1] https://www.postgresql.org/message-id/56a8516b.8000...@agliodbs.com
>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Code of Conduct plan

[Chris Travers]

 highly frowned
> upon.


Again key point that a CoC committee needs to be international and used to
addressing these sorts of issues.


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

-- 
Best Regards,
Chris Travers
Database Administrator

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin

Re: Re: PostgreSQL needs percentage function

[Chris Travers]

If you want one:

create or replace percent(numeric, numeric) returns numeric language sql as
$$ select ($1 / 100) * $2; $$;

On Tue, Dec 19, 2017 at 4:51 PM, Melvin Davidson 
wrote:

>
>
> On Tue, Dec 19, 2017 at 4:54 AM, Pavel Stehule 
> wrote:
>
>>
>>
>> 2017-12-19 10:13 GMT+01:00 Nick Dro :
>>
>>> This is exactly why I think there should be some build-in function for
>>> that...
>>> Percentage calculation exists in almost any databse and information
>>> system - it requires from use to implement many functions on thier own for
>>> something that is very basic.
>>> The general idea of my request is that postgresql should have build in
>>> function for percentage calculation it doesn't have to me the exact example
>>> I gave. Any thing will be better than none.
>>>
>>
>> I don't agree - there is not compatibility or portability reasons. The
>> calculation using numeric operators is pretty simple, and possibility to
>> create own function is here too.
>>
>> So there is not any reason to increase a postgresql catalogue.
>>
>> Please. don't do top post
>>
>> Regards
>>
>> Pavel
>>
>>
>>>
>>>
>>>
>>> ב דצמ׳ 18, 2017 18:28, Michael Nolan כתב:
>>>
>>>
>>>
>>> On Mon, Dec 18, 2017 at 6:23 AM, Nick Dro 
>>> wrote:
>>>
>>> Hi,
>>> Why PostgreSQL doesn't have build-in function to calculate percentage?
>>> somthing like percent(number,%
>>> for example:
>>> select percent(100,1) will calculate 1% of 100 = 1
>>> select percent(25,20) will calculate 20% of 25 = 5
>>>
>>> Seems like a nice addition to the math functions list:
>>> https://www.postgresql.org/docs/9.5/static/functions-math.html
>>>
>>> This is veryhelpull function, many uses percentage calculation in thier
>>> work and it will simplify the process. Percentage calculation is considered
>>> a basic math operation and I think postgresql should support it as a
>>> build-in function.
>>> Seems like easy to implment isn't it?
>>>
>>>
>>> It's a bit trickier than that, because you';ll have to deal with
>>> integers, real, double precision, etc.  You may also want to deal with null
>>> values.  I found it more useful to write a function that displays X as a
>>> percentage of Y, rounded to 1 digit to the right of the decimal point.
>>> --
>>> Mike Nolan
>>>
>>>
>>
> >Percentage calculation exists in almost any databse and information
> system
>
> That is not exactly true. AFAIK, only Oracle has a Percentage function.
> SQL Server and MySQL do not.
> It has already been shown that it is just as easy to code percentage
> inline  (EG: SELECT (50.8 x 5.2) / 100 AS pct;   ## .026416
> as it is to call a function SELECT pct(50.8, 5.2);
> Please do not false statements to justify a request for a non-needed
> enhancement.
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: PostgreSQL suitable?

[Chris Travers]

On Tue, Dec 19, 2017 at 3:07 PM, Kellner Thiemo 
wrote:

> Hi
>
> We are developing a data warehouse of which the integration layer will
> start with over 100 TB of data. There are not many entities though we
> probably can partition and foremost we should use inheritance for the lab
> results. I just was wondering if PostgreSQL was able to cope with. In case
> it depends on the modelling kind, we have not yet decided between classic
> erd, anchor modelling and data vault.
>
> Does someone have experience with such a set up?
>

There are a significant number of issues that happen when you try to put
that much data in PostgrSQL.  Remember there is a hard limit of 32TB per
table.

I currently help administer an analytics environment where 400TB of data is
pre-aggregated into 32TB of ready-to-serve metrics.  We generally try to
keep our initial point of entry databases to under 20TB of possible.

Nonetheless it is quite possible either using distributed add-ons like
Citus, forks like Postgres-XL, or (as we did at Adjust) a mixture of data
consolidation and application-level sharding.

As a plug, I expect to deliver talks about this in various places.  Stay
tuned ;-)



>
> Kind regards
>
> Thiemo
>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Searching for big differences between values

[Chris Travers]

On Nov 30, 2017 08:35, "Durumdara"  wrote:

Hello!

Somewhere the users made mistakes on prices (stock).

I need to search for big differences between values.
For example:

20
21
21,5
30
28
..
46392 <-
46392 <-

But it could be:

42300
43100
44000
43800
65000 <-
42100

Human eye could locate these values, but there we need to check 30.000
articles and 450.000 values.

Do you have any idea, how to this with SQL?

In most cases the first values are ok, the second interval (after a date)
it could be wrong...

I don't know how to define the solution, but I think PGSQL have intelligent
solution for this problem.

We need to search for elements have bigger value like base price *
tolerance.
But the base price is calculated dynamically from the lower values... The
tolerance is lower on highest base prices.


The good news is relational dbs excel at performing.  However you have not
provided nearly enough info to help write a query.  How are such things
calculated?


Thank you for any help!

Best regards
   dd