Re: [HACKERS] Determine state of cluster (HA)

[Joshua D. Drake]

On 10/16/2017 07:31 PM, Craig Ringer wrote:

On 17 October 2017 at 01:02, Joshua D. Drake <j...@commandprompt.com> wrote:

On 10/15/2017 07:39 PM, Craig Ringer wrote:



- Get info about master. We should finish merging recovery.conf into
postgresql.conf.



Definitely.


There's a patch from Abhijit Menon-Sen you could adopt for PostgreSQL
11 for that.



Do you have a link to this?

Thanks!

JD

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

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


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

Re: [HACKERS] Determine state of cluster (HA)

[Joshua D. Drake]

On 10/16/2017 03:55 AM, Magnus Hagander wrote:



On Mon, Oct 16, 2017 at 4:39 AM, Craig Ringer <cr...@2ndquadrant.com 
<mailto:cr...@2ndquadrant.com>> wrote:


On 13 October 2017 at 08:50, Joshua D. Drake <j...@commandprompt.com
<mailto:j...@commandprompt.com>> wrote:
> 5.  There is no way to connect to a db node with something akin to
> SQL-Server's "application intent" flags, to allow a connection to be
> rejected if we wish it to be a read/write connection.  This helps detect 
the
> state of the node directly without having to ask any further questions of
> the node, and makes it easier to "stall" during connection until a proper
> connection can be made.

That sounds desirable, and a good step toward eventually being able to
transparently re-route read/write queries from replica to master.
Which is where I'd like to land up eventually.


It also sounds a lot like the connection parameter target_session_attrs


Ahh, this is part of the new libpq failover right?

Thanks,

JD


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

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


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

Re: [HACKERS] Determine state of cluster (HA)

[Joshua D. Drake]

On 10/15/2017 07:39 PM, Craig Ringer wrote:

On 13 October 2017 at 08:50, Joshua D. Drake <j...@commandprompt.com> wrote:

-Hackers,

I had a long call with a firm developing front end proxy/cache/HA for
Postgres today. Essentially the software is a replacement for PGPool in
entirety but also supports analytics etc... When I was asking them about
pain points they talked about the below and I was wondering if this is a
problem we would like to solve.


IMO: no one node knows the full state of the system, or can know it.


That isn't exactly true. We do know if our replication state is current 
but only from the master which is part of the problem.




I'd love PostgreSQL to help users more with scaling, HA, etc. But I
think it's a big job. We'd need:

- a node topology of some kind, communicated between nodes
- heartbeat and monitoring
- failover coordination
- pooling/proxying
- STONITH/fencing
- etc.


I don't think we need all of that. This is more of a request to make it 
easier for those deploying HA to determine the state of Postgres.




That said, I do think it'd be very desirable for us to introduce a
greater link from a standby to master:

- Get info about master. We should finish merging recovery.conf into
postgresql.conf.


Definitely.


b. Attempt to connect to the host directly, if not...
c. use the slave and use the hostname via dblink to connect to the master,
as the hostname , i.e. select * from dblink('" + connInfo + "
dbname=postgres', 'select inet_server_addr()') AS t(inet_server_addr inet).
This is necessary in the event the hostname used in the recovery.conf file
is not resolvable from the outside.


OK, so "connect directly" here means from some 3rd party, the one
doing the querying of the replica.


1.  The dblink call doesn't have a way to specify a timeout, so we have to
use Java futures to control how long this may take to a reasonable amount of
time;


statement_timeout doesn't work?


That would be a work around definitely but I think it would be better to 
say: ALTER SYSTEM SET PROMOTE TIMEOUT '120' (Example, let's not get off 
into the weeds :P) and if the standby can't receive a ping/ack within 
120 it will promote itself.



PostgreSQL can't do anything about this one.


Yes that's true.


4.  It doesn't support cascading replication very well, although we could
augment the logic to allow us to map the relationship between nodes.
5.  There is no way to connect to a db node with something akin to
SQL-Server's "application intent" flags, to allow a connection to be
rejected if we wish it to be a read/write connection.  This helps detect the
state of the node directly without having to ask any further questions of
the node, and makes it easier to "stall" during connection until a proper
connection can be made.


That sounds desirable, and a good step toward eventually being able to
transparently re-route read/write queries from replica to master.
Which is where I'd like to land up eventually.

Again, that'd be a sensible patch to submit, quite separately to the
other topics.


Great.




6.  The master, on shutdown, will not actually close and disable connections
as it shuts down, instead, it will issue an error that it is shutting down
as it does so.


Er, yes? I don't understand what you are getting at here.


Yes, I will need to go back to them on this one. I think what they mean 
is that if we have a connection that is getting closed it doesn't return 
why it is closing. It just throws an error.




Can you describe expected vs actual behaviour in more detail?



I will need to get back to them on this but I think the behavior would 
be to have a return value of why the connection was closed vs just 
throwing an error. Say, "RETURN 66" means someone executed 
pg_going_to_failover() vs pg_terminate_backend() which could be for 
different reasons.


Thanks for responding, I am mostly the intermediary here,

JD


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

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


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

Re: [HACKERS] Determine state of cluster (HA)

[Joshua D. Drake]

On 10/12/2017 05:50 PM, Joshua D. Drake wrote:

-Hackers,


Bumping this.



I had a long call with a firm developing front end proxy/cache/HA for 
Postgres today. Essentially the software is a replacement for PGPool in 
entirety but also supports analytics etc... When I was asking them about 
pain points they talked about the below and I was wondering if this is a 
problem we would like to solve:


  Per your request, here is our failover issue.

1.  In a modern devops environment, the database should be able to scale 
and morph over time based on need.
2.  Tools that are leveraging the database should be able to easily 
discover and potentially control (with permissions) the database. 
Currently, you can discover the master and what nodes are syncing off of 
it, but on a failure, a tool can't easily discover what orchestration 
has done on the back-end to make the cluster whole again, i.e. from the 
slave, you can't discover the master reliably and easily.


The logic that our code now uses is to:

1.  Find the master
2.  Add replication nodes per the master's configuration.

To find a master, we start with a list of candidate nodes that MAY be a 
master at any point, and:

1. issue "SELECT pg_is_in_recovery()" to find if it is a slave
a. If so, use "SELECT pg_read_file('recovery.conf')" to extract the host
b. Attempt to connect to the host directly, if not...
c. use the slave and use the hostname via dblink to connect to the 
master, as the hostname , i.e. select * from dblink('" + connInfo + " 
dbname=postgres', 'select inet_server_addr()') AS t(inet_server_addr 
inet).  This is necessary in the event the hostname used in the 
recovery.conf file is not resolvable from the outside.
d. Use the dblink connection to ID the master node via select 
inet_server_addr();

e. connect to the IP provided by the master.
f.  Repeat through nodes until we get a master.

Issues:
1.  The dblink call doesn't have a way to specify a timeout, so we have 
to use Java futures to control how long this may take to a reasonable 
amount of time;
2.  NAT mapping may result in us detecting IP ranges that are not 
accessible to the application nodes.
3.  there is no easy way to monitor for state changes as they happen, 
allowing faster failovers, everything has to be polled based on events;
4.  It doesn't support cascading replication very well, although we 
could augment the logic to allow us to map the relationship between nodes.
5.  There is no way to connect to a db node with something akin to 
SQL-Server's "application intent" flags, to allow a connection to be 
rejected if we wish it to be a read/write connection.  This helps detect 
the state of the node directly without having to ask any further 
questions of the node, and makes it easier to "stall" during connection 
until a proper connection can be made.
6.  The master, on shutdown, will not actually close and disable 
connections as it shuts down, instead, it will issue an error that it is 
shutting down as it does so.


Fundamentally, the biggest issue is that it is very hard to determine 
the state of the cluster by asking all the nodes, in particular in the 
case of a failure.  Some state information is lost that is necessary to 
talk to the cluster moving forward in a reliable manner.






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

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


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

[HACKERS] Determine state of cluster (HA)

[Joshua D. Drake]

-Hackers,

I had a long call with a firm developing front end proxy/cache/HA for 
Postgres today. Essentially the software is a replacement for PGPool in 
entirety but also supports analytics etc... When I was asking them about 
pain points they talked about the below and I was wondering if this is a 
problem we would like to solve:


 Per your request, here is our failover issue.

1.  In a modern devops environment, the database should be able to scale 
and morph over time based on need.
2.  Tools that are leveraging the database should be able to easily 
discover and potentially control (with permissions) the database. 
Currently, you can discover the master and what nodes are syncing off of 
it, but on a failure, a tool can't easily discover what orchestration 
has done on the back-end to make the cluster whole again, i.e. from the 
slave, you can't discover the master reliably and easily.


The logic that our code now uses is to:

1.  Find the master
2.  Add replication nodes per the master's configuration.

To find a master, we start with a list of candidate nodes that MAY be a 
master at any point, and:

1. issue "SELECT pg_is_in_recovery()" to find if it is a slave
a. If so, use "SELECT pg_read_file('recovery.conf')" to extract the host
b. Attempt to connect to the host directly, if not...
c. use the slave and use the hostname via dblink to connect to the 
master, as the hostname , i.e. select * from dblink('" + connInfo + " 
dbname=postgres', 'select inet_server_addr()') AS t(inet_server_addr 
inet).  This is necessary in the event the hostname used in the 
recovery.conf file is not resolvable from the outside.
d. Use the dblink connection to ID the master node via select 
inet_server_addr();

e. connect to the IP provided by the master.
f.  Repeat through nodes until we get a master.

Issues:
1.  The dblink call doesn't have a way to specify a timeout, so we have 
to use Java futures to control how long this may take to a reasonable 
amount of time;
2.  NAT mapping may result in us detecting IP ranges that are not 
accessible to the application nodes.
3.  there is no easy way to monitor for state changes as they happen, 
allowing faster failovers, everything has to be polled based on events;
4.  It doesn't support cascading replication very well, although we 
could augment the logic to allow us to map the relationship between nodes.
5.  There is no way to connect to a db node with something akin to 
SQL-Server's "application intent" flags, to allow a connection to be 
rejected if we wish it to be a read/write connection.  This helps detect 
the state of the node directly without having to ask any further 
questions of the node, and makes it easier to "stall" during connection 
until a proper connection can be made.
6.  The master, on shutdown, will not actually close and disable 
connections as it shuts down, instead, it will issue an error that it is 
shutting down as it does so.


Fundamentally, the biggest issue is that it is very hard to determine 
the state of the cluster by asking all the nodes, in particular in the 
case of a failure.  Some state information is lost that is necessary to 
talk to the cluster moving forward in a reliable manner.



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

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


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

Re: [HACKERS] Columnar storage support

[Joshua D. Drake]

On 10/09/2017 01:03 PM, legrand legrand wrote:

Is there a chance that pluggable storage permits creation of a columnar rdbms
as monetDB in PostgreSQL ?
Thanks un advance for thé answer


The extension C-Store from Citus is probably what you are looking for.

jD





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html





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

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


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

Re: [HACKERS] search path security issue?

[Joshua D. Drake]

On 10/05/2017 02:54 PM, David G. Johnston wrote:
On Thu, Oct 5, 2017 at 2:37 PM, Joshua D. Drake <j...@commandprompt.com 
<mailto:j...@commandprompt.com>>wrote:


I get being able to change my search_path on the fly but it seems
odd that as user foo I can change my default search path?


Seems down-right thoughtful of us to allow users to change their own 
defaults instead of forcing them to always change things on-the-fly or 
bug a DBA to change the default for them.


It seems that if a super user changes the search path with ALTER 
USER/ROLE, then the user itself should not (assuming not an elevated 
privilege) should not be able to change it. Again, I get being able to 
do it with SET but a normal user shouldn't be able to reset a super user 
determined setting.


Shrug,

JD



David J.
​



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

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


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

[HACKERS] search path security issue?

[Joshua D. Drake]

-hackers,

Please see the below:

"""
postgres=# create user foo;
CREATE ROLE
postgres=# create schema foo;
CREATE SCHEMA
postgres=# alter role foo set search_path to 'foo';
ALTER ROLE
postgres=# \q
jd@jd-wks:~$ psql -U foo postgres
psql (9.6.5)
Type "help" for help.

postgres=> show search_path;
 search_path
-
 foo
(1 row)

postgres=> alter role foo set search_path to default;
ALTER ROLE
postgres=> show search_path;
 search_path
-
 foo
(1 row)

postgres=> \q


jd@jd-wks:~$ psql -U foo postgres
psql (9.6.5)
Type "help" for help.

postgres=> show search_path;
   search_path
-
 "$user", public
(1 row)


I get being able to change my search_path on the fly but it seems odd 
that as user foo I can change my default search path?


JD



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

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


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

Re: [HACKERS] 64-bit queryId?

[Joshua D. Drake]

On 10/01/2017 04:22 PM, Robert Haas wrote:

On Sun, Oct 1, 2017 at 3:48 PM, Greg Stark  wrote:

Well these kinds of monitoring systems tend to be used by operations
people who are a lot more practical and a lot less worried about
theoretical concerns like that.


+1, well said.


In context the point was merely that the default
pg_stat_statements.max of 5000 isn't sufficient to argue that 32-bit
values are enough. It wouldn't be hard for there to be 64k different
queries over time and across all the databases in a fleet and at that
point it becomes likely there'll be a 32-bit collision.


Yeah.

I think Alexander Korotkov's points are quite good, too.



+1 to both of these as well.

jD

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

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


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

Re: [HACKERS] Built-in plugin for logical decoding output

[Joshua D. Drake]

On 09/25/2017 11:31 AM, Alvaro Hernandez wrote:




Whether or not they are included in a managed environment is generally 
based on two things:


1. Safety (why RDS doesn't allow certain C extensions)
2. Community/Popularity (Exactly why RDS has PostGIS)
    A. Demand with a prerequisite of #1


     This is very clear. Now tell me: how many output plugins do you see 
included in RDS. And in GCP's PostgreSQL? Azure Postgres? Heroku?


From RDS:

Logical Replication for PostgreSQL on Amazon RDS

Beginning with PostgreSQL version 9.4, PostgreSQL supports the streaming 
of WAL changes using logical replication slots. Amazon RDS supports 
logical replication for a PostgreSQL DB instance version 9.4.9 and 
higher and 9.5.4 and higher. Using logical replication, you can set up 
logical replication slots on your instance and stream database changes 
through these slots to a client like pg_recvlogical. Logical slots are 
created at the database level and support replication connections to a 
single database.


PostgreSQL logical replication on Amazon RDS is enabled by a new 
parameter, a new replication connection type, and a new security role. 
The client for the replication can be any client that is capable of 
establishing a replication connection to a database on a PostgreSQL DB 
instance.


The most common clients for PostgreSQL logical replication are AWS 
Database Migration Service or a custom-managed host on an AWS EC2 
instance. The logical replication slot knows nothing about the receiver 
of the stream; there is no requirement that the target be a replica 
database. Note that if you set up a logical replication slot and do not 
read from the slot, data can be written to your DB instance's storage 
and you can quickly fill up the storage on your instance.


"""

I don't see why others wouldn't be available either. In fact, I am not 
sure why you couldn't use the JSON ones now. (Although I have not tested 
it).


JD




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

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


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

Re: [HACKERS] Built-in plugin for logical decoding output

[Joshua D. Drake]

On 09/25/2017 10:43 AM, Andres Freund wrote:

On 2017-09-25 10:38:52 -0700, Joshua D. Drake wrote:

On 09/25/2017 10:32 AM, Petr Jelinek wrote:

On 25/09/17 19:26, Tom Lane wrote:

Alvaro Hernandez <a...@ongres.com> writes:




There is already about 3 million output plugins out there so I think we
did reasonable job there. The fact that vast majority of that are
various json ones gives reasonable hint that we should have that one in
core though.


And I am sure that 2ndQuadrant would be happy to add it to their version of
Postgres and maintain it themselves.

https://www.2ndquadrant.com/en/resources/2ndqpostgres/


This doesn't seem like a good way to argue.



Sorry, that wasn't supposed to be negative. My point was that 
2ndQuadrant has a distribution of Postgres that they support. If 
2ndQuadrant wants the feature, they could add it to their own without 
burdening the wider community further. It provides 2ndQuadrant what they 
are arguing for, benefits 2ndQuadrant as it increases the visibility and 
opportunity of their distribution for wider use.


This is essentially what BigSQL and EDB are doing quite successfully. 
They add what the Core .Org community won't or doesn't (for whatever 
reason) and that makes their distribution attractive for their users.


Thanks,

JD


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

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


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

Re: [HACKERS] Built-in plugin for logical decoding output

[Joshua D. Drake]

On 09/25/2017 10:32 AM, Petr Jelinek wrote:

On 25/09/17 19:26, Tom Lane wrote:

Alvaro Hernandez  writes:




There is already about 3 million output plugins out there so I think we
did reasonable job there. The fact that vast majority of that are
various json ones gives reasonable hint that we should have that one in
core though.


And I am sure that 2ndQuadrant would be happy to add it to their version 
of Postgres and maintain it themselves.


https://www.2ndquadrant.com/en/resources/2ndqpostgres/

Thanks,

JD

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

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


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

Re: [HACKERS] Built-in plugin for logical decoding output

[Joshua D. Drake]

On 09/25/2017 10:19 AM, Petr Jelinek wrote:

On 25/09/17 18:48, Alvaro Hernandez wrote:





     In my opinion, logical decoding plugins that don't come with core
are close to worthless (don't get me wrong):



I respectfully disagree.


As do I.




- They very unlikely will be installed in managed environments (an area
growing significantly).


Whether or not they are included in a managed environment is generally 
based on two things:


1. Safety (why RDS doesn't allow certain C extensions)
2. Community/Popularity (Exactly why RDS has PostGIS)
A. Demand with a prerequisite of #1


- As anything that is not in core, raises concerns by users.


I find this a rather failing argument in today's market. If they are 
willing to migrate to Postgres, they are more than likely willing to use 
other open source software. Especially when combined with an expert 
telling them to.




- Distribution and testing are non-trivial: many OS/archs combinations.



Yes, it is. Why would we want to increase that burden to this community?


Thanks,

JD


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

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


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

Re: [HACKERS] Built-in plugin for logical decoding output

[Joshua D. Drake]

On 09/25/2017 10:15 AM, Gregory Brail wrote:
Yes. I'm advocating something "built-in" to Postgres. Any or all of 
those are likely a great starting point.


I don't see a benefit to having this "in postgres". The whole reason we 
have built out a mature and extensible product is so that not everything 
needs to be in postgres. We instead have a huge landscape of extensions 
created by our community that we don't have to manage in core.


Thanks,

JD



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

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


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

Re: [HACKERS] Built-in plugin for logical decoding output

[Joshua D. Drake]

On 09/25/2017 09:59 AM, Gregory Brail wrote:

However, I can't find any docs for the output format of pgoutput, which 
is going to make it less likely for people to be able to consume it. Is 
anyone working on docs? I know that it's a painful process.


I also think that a JSON-format (or configurable format) plugin would 
make this part of PG much more usable and I'd encourage the community to 
come up with one.



https://github.com/ildus/decoder_json
https://github.com/posix4e/jsoncdc
https://github.com/leptonix/decoding-json
https://github.com/Aloomaio/psql-json-decoder

Thanks,

JD

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

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


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

Re: [HACKERS] SCRAM in the PG 10 release notes

[Joshua D. Drake]

On 09/21/2017 07:51 AM, Peter Eisentraut wrote:

On 9/20/17 15:52, Jeff Janes wrote:

 I think that the addition of a link to
 > https://wiki.postgresql.org/wiki/List_of_drivers
  would be appropriate.

 I don't have any expectation that that list will be kept up to date.

I am not confident that it will be either, but what could we ever have
more confidence in being kept up-to-date than something anyone can
update which is hosted on a community asset?

If we put such a list linked from the documentation, we have to keep it
up to date for years, and no one is committing to do that.

I don't think it's our job to maintain lists of which third-party
products are ready to take advantage of new features in PostgreSQL.  I
don't see a list of GUIs ready to work with the new partitioning or
monitoring tools ready to work with the new xlog/wal naming.  If some
folks want to maintain such lists, that's great, but it's not a release
issue.


Peter is correct.

JD


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

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


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

Re: [HACKERS] PG 10 release notes

[Joshua D. Drake]

On 09/19/2017 09:32 AM, 'Bruce Momjian' wrote:

On Tue, Sep 19, 2017 at 12:30:01PM -0400, Tom Lane wrote:

"'Bruce Momjian'"  writes:

On Tue, Sep 19, 2017 at 12:22:39PM -0400, Tom Lane wrote:

We don't normally release-note documentation changes.  If this
wasn't purely a documentation change, then I was probably in error
to decide it didn't need to be in the notes.



It was purely a documentation change, but it was a documented change in a
long-standing and popular practice of not using too many shared buffers
on Windows, so I thought it wise to add it.


Well, if the intent of the note was to encourage people to raise
shared_buffers, it didn't do a very good job of that as written,
because I sure didn't understand it that way.


Do you have any suggestions since it is not a code change that I can
point to?  My guess is that the limitation was removed years ago, but we
only found out recently.


My guess is that the limitation was removed as of 9.3 with the work Haas 
did with shared buffers. Thus, yes it was years ago. I think that 
listing it regardless of the documentation change could be useful. 
Something like:


"""
Better support for large shared_buffers configurations including the 
Windows platform. Users are encouraged to review their shared_buffer 
settings against the size of their active data set and reconfigure 
appropriately.

"""

It is pretty much practitioner given that if your active data set can 
fit in shared_buffers and you aren't going to adversely affect the 
ability for the system to operate, that you should configure a high 
setting. I have seen settings as much as 96GB doing wonderfully in 
production.


JD






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

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


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

Re: [HACKERS] Is it time to kill support for very old servers?

[Joshua D. Drake]

On 09/18/2017 04:54 AM, Robert Haas wrote:

On Mon, Sep 18, 2017 at 7:17 AM, Andres Freund  wrote:

Private:


Not so much.



Well, as much as the Internet is actually private:

https://ilccyberreport.files.wordpress.com/2013/06/nsa11.jpg

JD

;)


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

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


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

Re: [HACKERS] Proposal: global index

[Joshua D. Drake]

On 08/24/2017 10:52 AM, Adam Brusselback wrote:
My understanding is that global indexes allow foreign keys to work 
naturally with partitioned tables, or tables in an inheritance 
hierarchy.  That is pretty big IMO, as it allows you to partition a 
table without making a trade-off in your database integrity.


It is, in fact the reason that even with 10 we don't really have 
partitioning as much as syntactical sugar for partitioning. (Not trying 
to take away from that, having the syntactical sugar is a huge first step).


JD


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

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


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

Re: [HACKERS] Updating line length guidelines

[Joshua D. Drake]

On 08/20/2017 09:32 AM, Andres Freund wrote:

On 2017-08-20 09:29:39 -0700, Joshua D. Drake wrote:

On 08/20/2017 07:49 AM, Andres Freund wrote:

Hi,

We currently still have the guideline that code should fit into an 80
character window. But an increasing amount of the code, and code
submissions, don't adhere to that (e.g. copy.c, which triggered me to
write this email). And I mean outside of accepted "exceptions" like
error messages.  And there's less need for such a relatively tight limit
these days.  Perhaps we should up the guideline to 90 or 100 chars?


Considering the prominence of high resolution monitors and the fact that we
don't really review patches (outside of commentary) in email much anymore,
it seems that may even be a bit archaic. On my standard FHD screen using a
standard size font I have a line length of 210 before I wrap.


People commonly display multiple buffers side-by-side...  But leaving
that aside, longer and longer lines actually become just hard to read
and hint that statements should be broken across lines / indentation
reduced by splitting inner blocks into their own functions.


Good point. I think ~ 100 is probably a good idea.

JD



- Andres




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

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


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

Re: [HACKERS] Updating line length guidelines

[Joshua D. Drake]

On 08/20/2017 07:49 AM, Andres Freund wrote:

Hi,

We currently still have the guideline that code should fit into an 80
character window. But an increasing amount of the code, and code
submissions, don't adhere to that (e.g. copy.c, which triggered me to
write this email). And I mean outside of accepted "exceptions" like
error messages.  And there's less need for such a relatively tight limit
these days.  Perhaps we should up the guideline to 90 or 100 chars?


Considering the prominence of high resolution monitors and the fact that 
we don't really review patches (outside of commentary) in email much 
anymore, it seems that may even be a bit archaic. On my standard FHD 
screen using a standard size font I have a line length of 210 before I wrap.


Sincerely,

JD



Greetings,

Andres Freund





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

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


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

Re: [HACKERS] On Complex Source Code Reading Strategy

[Joshua D. Drake]

On 07/27/2017 04:45 PM, Tom Lane wrote:

Peter Geoghegan  writes:

2. Start somewhere. I have no idea where that should be, but it has to
be some particular place that seems interesting to you.


Don't forget to start with the available documentation, ie
https://www.postgresql.org/docs/devel/static/internals.html
You should certainly read
https://www.postgresql.org/docs/devel/static/overview.html
and depending on what your interests are, there are probably other
chapters of Part VII that are worth your time.

Also keep an eye out for README files in the part of the source
tree you're browsing in.


The doxygen instance at doxygen.postgresql.org is also helpful in 
navigating dependencies as you start attacking a features.


JD



regards, tom lane





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

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


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

Re: [HACKERS] Create language syntax is not proper in pg_dumpall and not working using pg_upgrade

[Joshua D. Drake]

On 07/25/2017 10:24 AM, Andres Freund wrote:

On 2017-07-25 13:18:25 -0400, Robert Haas wrote:

On Tue, Jul 25, 2017 at 1:13 PM, Andres Freund  wrote:

On 2017-07-25 13:10:11 -0400, Robert Haas wrote:

On Tue, Jul 25, 2017 at 1:06 PM, Tom Lane  wrote:

Is this assumption, like, documented someplace?



Yes, and?  We can try to address countless intentionally unsupported
edge-cases, but it's going to cost code, complexity and time. And very
likely it's going to add hard to find, test and address bugs. pg_dump is
complicated as is, I don't think trying to address every conceivable
weirdness is a good idea. There's plenty more fundamental things wrong
(e.g. DDL concurrent with a dump sometimes breaking that dump).

I'm not sure what you're arguing for here.


Isn't the simplest solution just to actually document this? Code is not 
documentation except for those reading code. End users, sql developers, 
DBAs etc, should never have to open doxygen.postgresql.org to figure 
this stuff out.


JD


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

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


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

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

[Joshua D. Drake]

On 07/23/2017 12:03 PM, Joshua D. Drake wrote:

As you can see even with aggressive vacuuming, over a period of 36 hours 
life gets increasingly miserable.


The largest table is:

postgres=# select 
pg_size_pretty(pg_total_relation_size('bmsql_order_line'));

  pg_size_pretty

  148 GB
(1 row)



[snip]


With the PK being

postgres=# select 
pg_size_pretty(pg_relation_size('bmsql_order_line_pkey'));

  pg_size_pretty

  48 GB
(1 row)

I tried to see how much data we are dealing with here:


-hackers,

I cleaned up the table with VACUUM FULL and ended up with the following:

postgres=# select 
pg_size_pretty(pg_total_relation_size('bmsql_order_line'));

 pg_size_pretty

 118 GB
(1 row)

postgres=# select pg_size_pretty(pg_relation_size('bmsql_order_line_pkey'));
 pg_size_pretty

 27 GB
(1 row)

Does this suggest that we don't have a cleanup problem but a 
fragmentation problem (or both at least for the index)? Having an index 
that is almost twice the uncleaned up size isn't that uncommon.


Thanks in advance,

JD


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

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


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

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

[Joshua D. Drake]

Hello,

I changed the test to run for 6 hours at a time regardless of number of 
transactions. I also changed the du command to only look at the database 
(previously wal logs were included). This is the clearest indication of 
the problem I have been able to produce.


Again, this is with 128 clients and 500 warehouses. The first test is a 
clean test, everything dropped, vacuumed etc... Each subsequent test is 
just starting the test again to have breakpoints.



-+---
 autovacuum  | on
 autovacuum_analyze_scale_factor | 0.1
 autovacuum_analyze_threshold| 50
 autovacuum_freeze_max_age   | 2
 autovacuum_max_workers  | 12
 autovacuum_multixact_freeze_max_age | 4
 autovacuum_naptime  | 10
 autovacuum_vacuum_cost_delay| 0
 autovacuum_vacuum_cost_limit| 5000
 autovacuum_vacuum_scale_factor  | 0.1
 autovacuum_vacuum_threshold | 50
 autovacuum_work_mem | -1
 log_autovacuum_min_duration | -1
 max_wal_size| 640
 checkpoint_timeout  | 86400
 checkpoint_completion_target| 0.5

Starting base metric
50G /srv/main/base

Test 1:
90G /srv/main/base
TPS: 838

Test 2:
121G/srv/main/base
TPS: 725

Test 3:
146G/srv/main/base
TPS: 642

Test 4:
171G/srv/main/base
TPS: 549

Test 5:
189G/srv/main/base
TPS: 489

Test 6:
208G/srv/main/base
TPS: 454

As you can see even with aggressive vacuuming, over a period of 36 hours 
life gets increasingly miserable.


The largest table is:

postgres=# select 
pg_size_pretty(pg_total_relation_size('bmsql_order_line'));

 pg_size_pretty

 148 GB
(1 row)

postgres=# \d bmsql_order_line
 Table "public.bmsql_order_line"
 Column |Type | Modifiers
+-+---
 ol_w_id| integer | not null
 ol_d_id| integer | not null
 ol_o_id| integer | not null
 ol_number  | integer | not null
 ol_i_id| integer | not null
 ol_delivery_d  | timestamp without time zone |
 ol_amount  | numeric(6,2)|
 ol_supply_w_id | integer |
 ol_quantity| integer |
 ol_dist_info   | character(24)   |
Indexes:
"bmsql_order_line_pkey" PRIMARY KEY, btree (ol_w_id, ol_d_id, 
ol_o_id, ol_number)

Foreign-key constraints:
"ol_order_fkey" FOREIGN KEY (ol_w_id, ol_d_id, ol_o_id) REFERENCES 
bmsql_oorder(o_w_id, o_d_id, o_id)
"ol_stock_fkey" FOREIGN KEY (ol_supply_w_id, ol_i_id) REFERENCES 
bmsql_stock(s_w_id, s_i_id)


With the PK being

postgres=# select pg_size_pretty(pg_relation_size('bmsql_order_line_pkey'));
 pg_size_pretty

 48 GB
(1 row)

I tried to see how much data we are dealing with here:

postgres=# select count(*) from bmsql_order_line;
   count
---
 910324839
(1 row)

Time: 503965.767 ms

And just to show that we were pushing to get these numbers:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
   2.380.002.201.980.00   93.44

Device:tpsMB_read/sMB_wrtn/sMB_readMB_wrtn
sdb2027.40   239.99 0.05   1199  0
sda   0.80 0.00 0.01  0  0



So we have 910M rows, and it took 8.39941667 minutes to count them at 
240MB/s.


I know this is a lot of data and as I said previously, happy to let 
anyone look at it. However, we clearly have something deeper to look into.


Thanks in advance,

JD


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

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


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

[HACKERS] possible effective_io_concurrency performance regression

[Joshua D. Drake]

-hackers,

While updating my Postgres performance curriculum I was doing some 
testing with effective_io_concurrency and I may have found a regression. 
I am aware that the parameter only works under certain conditions. 
However, what I appear to have found is that if it is set to anything 
but 0, it is a regression for (at least benchmarksql tpc-c) workloads.


See here:

Testing with the TPC style benchmark shows that on local systems, 
setting this between 0 – 48 keeps the TPS within noise level. However, 
testing also shows that on cloud systems such as Google Cloud Compute 
setting this setting to anything greater than 0 results in an 
approximately 10% performance degradation on TPS:


Local/GCE   effective_io_concurrencyTPS

GCE OFF 47951

8   43098

1   43233
LOCAL
0   9939

4   9960

16  9955

48  9958


I was able to produce these results pretty consistently. I wonder if any 
has seen this on EBS? GCE instance is 16CPU, 59GB memory, 240MB 
Sustained rate SSD with 15k IOPS.


Thanks,

JD

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

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


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

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

[Joshua D. Drake]

On 07/20/2017 08:58 PM, Joshua D. Drake wrote:

On 07/19/2017 07:57 PM, Tom Lane wrote:

Peter Geoghegan <p...@bowt.ie> writes:



Test 1: 55G/srv/main
TPS:955

Test 2: 112G/srv/main
TPS:531 (Not sure what happened here, long checkpoint?)

Test 3: 109G/srv/main
TPS:868

Test 4: 143G
TPS:840

Test 5: 154G
TPS: 722

I am running the query here:

https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_index 


This query didn't finish after 12 hours. Here is the new set:

name |  setting
-+---
 autovacuum  | on
 autovacuum_analyze_scale_factor | 0.1
 autovacuum_analyze_threshold| 50
 autovacuum_freeze_max_age   | 2
 autovacuum_max_workers  | 12
 autovacuum_multixact_freeze_max_age | 4
 autovacuum_naptime  | 10
 autovacuum_vacuum_cost_delay| 0
 autovacuum_vacuum_cost_limit| 5000
 autovacuum_vacuum_scale_factor  | 0.1
 autovacuum_vacuum_threshold | 50
 autovacuum_work_mem | -1
 log_autovacuum_min_duration | -1

I have only ran one test but it is pretty telling:

Test 1: 60G /srv/main
TPS: 914

Test 2: 92G /srv/main
TPS: Still running

I will post a update after the third or fourth test depending on the 
numbers. I created this instance exactly for these tests so if someone 
wants to poke around I can give access.


Thanks,

JD


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

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


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

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

[Joshua D. Drake]

On 07/20/2017 11:54 PM, Sokolov Yura wrote:

On 2017-07-21 06:58, Joshua D. Drake wrote:

On 07/19/2017 07:57 PM, Tom Lane wrote:

Peter Geoghegan <p...@bowt.ie> writes:





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

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


Have you measured increased vacuum ring buffer?


No, not yet. I think we are still in the proving the problem stage.

JD



This will require recompilation, though.

With regards,



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

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


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

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

[Joshua D. Drake]

On 07/19/2017 07:57 PM, Tom Lane wrote:

Peter Geoghegan  writes:

My argument for the importance of index bloat to the more general
bloat problem is simple: any bloat that accumulates, that cannot be
cleaned up, will probably accumulate until it impacts performance
quite noticeably.


But that just begs the question: *does* it accumulate indefinitely, or
does it eventually reach a more-or-less steady state?  The traditional
wisdom about btrees, for instance, is that no matter how full you pack
them to start with, the steady state is going to involve something like
1/3rd free space.  You can call that bloat if you want, but it's not
likely that you'll be able to reduce the number significantly without
paying exorbitant costs.

I'm not claiming that we don't have any problems, but I do think it's
important to draw a distinction between bloat and normal operating
overhead.


Agreed but we aren't talking about 30% I don't think. Here is where I am 
at. It took until 30 minutes ago for the tests to finish:


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| 20
 autovacuum_vacuum_cost_limit| -1
 autovacuum_vacuum_scale_factor  | 0.2
 autovacuum_vacuum_threshold | 50
 autovacuum_work_mem | -1
 log_autovacuum_min_duration | -1


Test 1: 55G /srv/main
TPS:955

Test 2: 112G/srv/main
TPS:531 (Not sure what happened here, long checkpoint?)

Test 3: 109G/srv/main
TPS:868

Test 4: 143G
TPS:840

Test 5: 154G
TPS:722

I am running the query here:

https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_index

And will post a followup. Once the query finishes I am going to launch 
the tests with autovacuum_vacuum_cost_limit of 5000. Is there anything 
else you folks would like me to change?


JD




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

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


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

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

[Joshua D. Drake]

On 07/20/2017 06:28 AM, Stephen Frost wrote:


It's not clear off-hand how much that would improve this case, as
the database size appears to pretty quickly get beyond the OS memory
size (and only in the first test is the DB starting size less than
system memory to begin with).


FYI,

I will be posting new numbers in a few hours. I had been planning on 
posting this last night but... KDE.


JD


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

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


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

[HACKERS] autovacuum can't keep up, bloat just continues to rise

[Joshua D. Drake]

Hello,

At PGConf US Philly last week I was talking with Jim and Jan about 
performance. One of the items that came up is that PostgreSQL can't run 
full throttle for long periods of time. The long and short is that no 
matter what, autovacuum can't keep up. This is what I have done:


Machine:

16vCPU
59G Memory
10G SSD (/)
500G SSD /srv/main/9.6 (PGDATA) : 240MB Sustained with 15k IOPS
* Yes, we really got 240MB sustained performance

I used benchmarksql which is a tpc-c benchmark similar to pgbench but 
supposedly more thorough.


https://sourceforge.net/projects/benchmarksql/

PostgreSQL 9.6 on Ubuntu 16.04 x64.

postgresql.conf:

max_connections: 1000 (just to keep it out of the way)
shared_buffers: 32G (Awesome work Haas)
work_mem: 32M
maintenance_work_mem: 2G
effective_io_concurrency: 1

	* Before anybody suggests increasing this, on GCE over a dozen tests, 
anything but disabling this appears to be a performance hit of ~ 10% (I 
can reproduce those tests if you like on another thread).


synchronous_commit: off
checkpoint_timeout: 60min
max_wal_size: 5G
random_page_cost: 1
effective_cache_size: 32GB
*this probably should be more like 50 but still
autovacuum_max_workers: 12
* One for each table + a couple for system tables
autovacuum_vacuum_scale_factor: 0.1
autovacuum_cost_delay: 5ms

Here are the benchmarksql settings for all 4 runs:

17:07:54,268 [main] INFO   jTPCC : Term-00, warehouses=500
17:07:54,269 [main] INFO   jTPCC : Term-00, terminals=128
17:07:54,272 [main] INFO   jTPCC : Term-00, runTxnsPerTerminal=10
17:07:54,273 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=30
17:07:54,273 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=false
17:07:54,274 [main] INFO   jTPCC : Term-00,
17:07:54,274 [main] INFO   jTPCC : Term-00, newOrderWeight=45
17:07:54,274 [main] INFO   jTPCC : Term-00, paymentWeight=43
17:07:54,274 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
17:07:54,275 [main] INFO   jTPCC : Term-00, deliveryWeight=4
17:07:54,275 [main] INFO   jTPCC : Term-00, stockLevelWeight=4

For run 0, I started with:

vacuumdb -U postgres -fz;./runBenchmark.sh my_postgres.properties

And then for each subsequent run, I just ran the benchmark without the 
vacuum full so that PostgreSQL could prove us wrong. It didn't. Here is 
the break down of the results:


RUN START DISK SIZE END DISK SIZE   TPS/Terminal
0   54  78  868.6796875
1   78  91  852.4765625
2   91  103 741.4609375
3   103 116 686.125

The good news is, PostgreSQL is not doing half bad against 128 
connections with only 16vCPU. The bad news is we more than doubled our 
disk size without getting reuse or bloat under control. The concern here 
is that under heavy write loads that are persistent, we will eventually 
bloat out and have to vacuum full, no matter what. I know that Jan has 
done some testing and the best he could get is something like 8 days 
before PostgreSQL became unusable (but don't quote me on that).


I am open to comments, suggestions, running multiple tests with 
different parameters or just leaving this in the archive for people to 
reference.


Thanks in advance,

JD


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

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


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

Re: [HACKERS] Revisiting NAMEDATALEN

[Joshua D. Drake]

On 07/03/2017 11:31 AM, Emrul wrote:

Hi hackers,

This question came up again on Reddit:
https://www.reddit.com/r/PostgreSQL/comments/6kyyev/i_have_hit_the_table_name_length_limit_a_number/
and I thought I'd echo it here.

I totally am on board with short, descriptive names and a good convention.
However, there are just so many cases where 63 characters can't
descriptively describe a column name.  I've been on projects where we have
one table maybe with only a few thousand records but hundreds of columns
each uniquely describing an attribute on the record.  It is a challenge
bordering on impossible to fit them into a consistently named field of <63
characters that someone can later refer to and know what piece of
information it actually refers to.

Is this something that can be revisited for an upcoming release? Also, are
there any technical problems that would be created by increasing this
attribute?


Although I appreciate the sentiment this seems over the top:

datasystem_adjustmentmanagement_mm_datasystem_adjustmentmanagement_products

You can always use COMMENT ON to explode the actual meaning.

JD


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

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


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

Re: [HACKERS] Optional message to user when terminating/cancelling backend

[Joshua D. Drake]

On 06/26/2017 07:15 AM, Joel Jacobson wrote:

+1

On Tue, Jun 20, 2017 at 8:54 PM, Alvaro Herrera
 wrote:

Unless you have a lot of users running psql manually, I don't see how
this is actually very useful or actionable.  What would the user do with
the information?  Hopefully your users already trust that you'd keep the
downtime to the minimum possible.


I think this feature would be useful for PgTerminator
(https://github.com/trustly/pgterminator)
a tool which automatically kills unprotected processes that could
potentially be the reason why

X number of protected important processes have been waiting for >Y seconds.


When I'm guilty of locking this in the production DB and get killed by
PgTerminator,
it would be nice to know the reason, e.g. that it was PgTerminator
that killed me
and what process I was blocking.


And not just the pid but literally "what".

jD


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

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


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

Re: [HACKERS] TAP backpatching policy

[Joshua D. Drake]

On 05/31/2017 07:49 AM, Robert Haas wrote:

On Tue, May 30, 2017 at 9:12 PM, Craig Ringer  wrote:

Thoughts? Backpatch new TAP methods, etc, into back branches routinely?


[...]


 When customers start to doubt that, then they
become reluctant to apply new minor versions in their entirety and ask
for individual commits to be cherry-picked, or just don't upgrade at
all. 


This may be true, on the other hand that isn't .Org's problem. Customers 
are CMD, EDB, 2Qs problem. .Org's problem is: How do we ensure the best 
possible result for PostgreSQL.


I think comprehensive testing (which I am sure you agree) is bullet 
point on that list.



One could argue that commits to the testing framework shouldn't
make customers nervous, but what people should be worried about and
what they are actually worried about do not always match.  It is
useful to be able to show a customer a list of things that were done
in a minor release and see nothing in there that can be described as
optional tinkering.


This is about narrative. You don't say "optional tinkering". You say, 
"Initiate code modification for comprehensive TAP (testing) framework".


That makes customers knees weak and they swoon.


back-patching (to avoid churning a supposedly-stable branch).  I'm not
sure exactly what I think about this issue, but I'm very skeptical of
the idea that back-patching less conservatively will have no
downsides.


There is never not a downside. The question is, "Does the upside 
outweigh the downside?". In this case I would argue it is fairly obvious.


Thanks,

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] TAP backpatching policy

[Joshua D. Drake]

On 05/30/2017 09:52 PM, Stephen Frost wrote:

Tom,



Um ... but we still have 2 live pre-9.4 branches.  If your proposal
doesn't extend to back-porting all of this stuff as far as 9.2,
I don't see what this is really buying.  We'd still need version cutoff
checks in the tests.


I don't believe the explicit goal of this is to remove the version
checks but rather to provide improved testing coverage in our
back-branches.  If we have to keep a version cutoff check for that, so
be it.


+1




(If you *do* propose back-patching all this stuff as far as 9.2, I'm not
quite sure what I'd think about that.  But the proposal as stated seems
like questionable half measures.)


I find that to be an extremely interesting idea, for my own 2c, but I'm
not sure how practical it is.
It is perfectly reasonable to say, "We have added comprehensive testing 
through the TAP project. Unfortunately, it is only reasonable (due to 
code changes) to back port them from 9.4 and above."


*IF* we were to try to go back farther than 9.4, I would say that 9.3 is 
the only one that would be worth it anyway. 9.2 was a great release but 
it's day is over or at least it is a spectacle of a setting sun:


https://www.postgresql.org/support/versioning/

Thanks,

JD




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Potential issue with alter system

[Joshua D. Drake]

On 05/04/2017 12:49 PM, Tom Lane wrote:

"Joshua D. Drake" <j...@commandprompt.com> writes:

So I did this:




If you have other entries you want to keep in the postgresql.auto.conf
file, you could get away with manually editing it to remove the newline.


Got it. Thanks for digging in. This is actually a very real and easy way 
to explain to people why they need to keep up to date on their dot releases.


Thanks,

JD



regards, tom lane




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

[HACKERS] Potential issue with alter system

[Joshua D. Drake]

Folks,

So I did this:

postgres=# alter system set archive_command to 'rsynv -av %p 
postgres@52.3.141.224:/data/archive/%f

';

Note the new line. It properly created in postgresql.auto.conf:

archive_command = 'rsynv -av %p postgres@52.3.141.224:/data/archive/%f
'
(note the new line)

I noticed I spelled rsync wrong and now I get this:

postgres=# alter system set archive_command to 'rsync -av %p 
postgres@52.3.141.224:/data/archive/%f'

;
ERROR:  could not parse contents of file "postgresql.auto.conf"

This is 9.5.2 (Yes, I know... I am in the process of setting up 
replication to 9.5.6 for failover).


JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] logical replication syntax (was DROP SUBSCRIPTION, query cancellations and slot handling)

[Joshua D. Drake]

On 05/02/2017 05:13 AM, Tom Lane wrote:

Robert Haas  writes:

On Thu, Apr 20, 2017 at 7:46 AM, Petr Jelinek
 wrote:

DROP SUBSCRIPTION mysub NODROP SLOT;



Having said that, I doubt that anyone would argue that CREATE USER is
anything but legacy syntax, or that our more recent syntax designs aren't
better models to follow.

It's not quite too late to revisit the syntax of the log rep commands
... shall we add this as an open item?


I would think so. Just in reading this, even if we keep a similar syntax 
it should be DROP SLOT NO or DROP SLOT FALSE.


JD




regards, tom lane





--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] PG 10 release notes

[Joshua D. Drake]

On 05/01/2017 05:40 AM, Robert Haas wrote:

On Tue, Apr 25, 2017 at 9:56 PM, Bruce Momjian  wrote:

First, I don't think RFC references belong in the release notes, let
alone RFC links.


Why not?  I think RFC references are a great thing to include in the
release notes, and including links seems helpful, too.


I could see not including RFC references in a PR but release notes seem 
to be the perfect place for them.


JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] RFC: ALTER SYSTEM [...] COMMENT

[Joshua D. Drake]

On 04/26/2017 10:31 AM, Tom Lane wrote:

"Joshua D. Drake" <j...@commandprompt.com> writes:

I wouldn't fight hard to change it but really if we think about it, what
makes more sense from usability perspective?



CREATE TABLE foo() COMMENT IS


I think it's likely to be impossible to shoehorn such a thing into every
type of CREATE command without making COMMENT a fully reserved word,
which is going to be a very hard sell.


Well if it is a complete uphill battle, this is certainly not the 
feature that I am going to dig my heels in about.





2. Make it so comments are appended not replaced.


Backwards compatibility fail ... not to mention that you haven't offered
an argument as to why everyone would think this is an improvement.


"Everyone" is a bit of a stretch for every single feature we have.

I would think that most people that work with production systems would 
like to know the history of any object modification.


Thanks,

jD





regards, tom lane




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] RFC: ALTER SYSTEM [...] COMMENT

[Joshua D. Drake]

On 04/26/2017 10:14 AM, Stephen Frost wrote:

JD,



Having COMMENT ON accept a general query whose result is then cast to
text and stored as the comment would allow this to be done, eg:

COMMENT ON table IS (pg_get_comment('table') || ' new text');


Dig it, although we probably want the equivalent of:

COMMENT ON table IS (pg_get_comment('table') || '\n\n' || ' new text');

Or something like that.



We could also have new syntax along these lines, for this specific case:

COMMENT ON table ADD ' new text';

Though we have this pretty powerful language, seems a bit of a shame to
invent something new for working with comments.


Agreed and I think that using existing COMMENT ON capability is likely 
to get this pushed farther down the road.


I wouldn't fight hard to change it but really if we think about it, what 
makes more sense from usability perspective?


CREATE TABLE foo() COMMENT IS

or

CREATE TABLE foo;
COMMENT ON TABLE foo IS

Thanks,

JD




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

[HACKERS] RFC: ALTER SYSTEM [...] COMMENT

[Joshua D. Drake]

-hackers,

We have had ALTER SYSTEM for some time now. It is awesome to be able to 
make changes that can be system wide without ever having to hit a shell 
but it does lack a feature that seems like an oversight, the ability to 
comment.


Problem we are trying to solve:

Having documentation for changes to GUC parameters that are modified via 
ALTER SYSTEM.


Why?

Because documentation is good and required for a proper production system.

How?

I propose:

Add a column to pg_settings comment(text)
Change the grammar to allow:

ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | 
DEFAULT } COMMENT 'comment'


Example:

ALTER SYSTEM SET maintenance_work_mem TO '1GB' COMMENT IS 'Increased to 
allow autovacuum to be more efficient';


Potential issues:

Does not use existing comment functionality. Alternate solution which 
would decrease functionality is:


COMMENT ON SETTING setting IS 'comment';

Looking forward, we may want to do the following:

1. Make it so any object can have a comment with creation, e.g;

CREATE TABLE table () COMMENT IS '';

2. Make it so comments are appended not replaced.

Thanks in advance,

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] parallel "return query" is no good

[Joshua D. Drake]

On 03/23/2017 10:03 AM, Robert Haas wrote:

On Thu, Mar 23, 2017 at 12:50 PM, Robert Haas  wrote:

Commit 7aea8e4f2daa4b39ca9d1309a0c4aadb0f7ed81b allowed a parallel
plan to be generated when for a RETURN QUERY or RETURN QUERY EXECUTE
statement in a PL/pgsql block.  As it turns out, the analysis that led
to this decision was totally wrong-headed, because the plan will
always be executed using SPI_cursor_fetch(portal, true, 50), which
will cause ExecutePlan() to get invoked with a count of 50, which will
cause it to run the parallel plan serially, without workers.
Therefore, passing CURSOR_OPT_PARALLEL_OK is a bad idea here; all it
can do is cause us to pick a parallel plan that's slow when executed
serially instead of the best serial plan.

The attached patch fixes it.  I plan to commit this and back-patch it
to 9.6, barring objections or better ideas.


I guess the downside of back-patching this is that it could cause a
plan change for somebody which ends up being worse.  On the whole,
serial execution of queries intended to be run in parallel isn't
likely to work out well, but it's always possible somebody has a cases
where it happens to be winning, and this could break it.  So maybe I
should do this only in master?  Thoughts?


I think the greater good of a fix applies here. +1 to 9.6.






--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

[Joshua D. Drake]

On 03/19/2017 01:01 PM, Tom Lane wrote:

Andreas Karlsson  writes:

As for if we should have backwards compatibility for the old names I am
leaning weakly for providing it in the case of createuser. I can see end
users being pissed off that the createuser command is suddenly gone
without any warning when they upgrade. On the flip side I have no idea
how much work it would be to maintain those legacy names.


It seems reasonably trivial to me as far as the code goes --- just
create a symlink during installation.  (On Windows I suppose we'd have
to make a second physical copy, but these files are not so large that
that seems unacceptable.)


Windows supports symlinks:

https://msdn.microsoft.com/en-us/library/windows/desktop/aa365680(v=vs.85).aspx

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Removing binaries (was: createlang/droplang deprecated)

[Joshua D. Drake]

On 03/18/2017 01:12 PM, Magnus Hagander wrote:

Magnus Hagander > writes:

2017-03-18 14:00 GMT+01:00 Peter Eisentraut >:



createuser, dropuser - definitely pollutes the namespace, people do
sometimes try them for the wrong thing. Unlike the db ones they do add
value though -- I don't think we have a psql way of in a single command
doing what --pwprompt on createuser does, do we? But given that we are
in the process of breaking a lot of other scripts for 10, perhaps we
should rename it to pg_createuser?


We have one chance in the near future to shake things up, break things 
for the better and lose a lot of long time issues. Making things 
consistent and declarative (pg_ for example) is a great opportunity.


+1

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

[HACKERS] temp_buffers vs temp vs local and explain

[Joshua D. Drake]

-hackers,

I was reviewing an explain plan today and with some help from Andrew G, 
I got a lot more information than I deserved. It did however bring up 
quite a usability issue that I think we should consider.


Let's review the following two lines:

Sort Method: external merge  Disk: 19352kB
   Buffers: shared hit=257714, temp read=8822 written=8808

Now the first line is pretty obvious. We spilled over work_mem and hit 
the disk for ~ 20MB of use.


The second line is not so clear.

Buffers, shared_buffers? We hit 257714 of those. That makes sense but 
what about temp? Temp refers to temp files, not temp_buffers or temp 
tables. Temp buffers refers to a temp table (ala create temp table) but 
is represented as local in an explain plan. Further the values of temp 
are blocks, not bytes.


Basically, it is a little convoluted.

I am not 100% what the answer here is but it seems more consistency 
might be a good start.


Also, it would be a huge boon for many (almost all) of our users if we 
could just do (something like) this:


EXPLAIN (ANALYZE,SUMMARY)

And it said:

Query 1

shared_buffers
  *
  *
work_mem
  * Total Used =
  * In Memory =
  * On Disk =
Rows
  * Estimated =
  * Actual =

etc...

I know that access to the details are needed but for day to day 
operations for a huge portion of our users, they just want to know how 
much memory they need, or if they need a faster disk etc...


Thanks,

JD




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Odd listen_addresses behavior

[Joshua D. Drake]

On 03/15/2017 12:57 PM, Tom Lane wrote:


I'm suspicious that you have an override of listen_addresses somewhere ---
for instance, the "-i" postmaster command line switch effectively is
--listen-addresses='*'.


Yep postgresql.auto.conf.

Thanks, sorry for the noise.

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

[HACKERS] Odd listen_addresses behavior

[Joshua D. Drake]

-hackers,

I found this today:

jd@jd-wks:~/snap/postgresql96/common/data$ 
/snap/postgresql96/19/usr/bin/pg_ctl -D data stop

pg_ctl: directory "data" does not exist

jd@jd-wks:~/snap/postgresql96/common/data$ cd ..

jd@jd-wks:~/snap/postgresql96/common$ 
/snap/postgresql96/19/usr/bin/pg_ctl -D data stop

waiting for server to shut down...LOG:  received fast shutdown request
.LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down
 done
server stopped

jd@jd-wks:~/snap/postgresql96/common$ grep listen_addresses 
data/postgresql.conf

listen_addresses = '192*'   # what IP address(es) to listen on;

-- I wasn't actually expecting the above to work. I was just testing.


jd@jd-wks:~/snap/postgresql96/common$ 
/snap/postgresql96/19/usr/bin/pg_ctl -D data start

server starting

jd@jd-wks:~/snap/postgresql96/common$ postgresql96.psql -U jd -h 
localhost postgres

psql (9.6.2)
Type "help" for help.

postgres=# show listen_addresses ;
 listen_addresses
--
 *
(1 row)


I grant that this is obscure but perhaps we should do something about it?

Thanks,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

[HACKERS] Should we eliminate or reduce HUP from docs?

[Joshua D. Drake]

Hello,

I am a bad speaker, I am writing a talk three weeks before the 
conference (as opposed to on the plane). I noticed in the docs we still 
reference the passing of SIGHUP for reloading conf file but we now have 
pg_reload_conf();


It seems the use of pg_reload_conf() would provide a better canonical 
interface to our users. Especially those users who are not used to 
interacting with the OS (Windows, Oracle etc...) for databases.


Sincerely,

JD
--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Replication vs. float timestamps is a disaster

[Joshua D. Drake]

On 02/22/2017 02:45 PM, Tom Lane wrote:

Andres Freund  writes:

On 2017-02-22 08:43:28 -0500, Tom Lane wrote:

(To be concrete, I'm suggesting dropping --disable-integer-datetimes
in HEAD, and just agreeing that in the back branches, use of replication
protocol with float-timestamp servers is not supported and we're not
going to bother looking for related bugs there.  Given the lack of field
complaints, I do not believe anyone cares.)


What I *am* willing to spend time on is removing float-timestamp code
in HEAD.  I've not yet heard anybody speak against doing that (or at
least, nothing I interpreted as a vote against it).  If I've not heard
any complaints by tomorrow, I'll get started on that.


Rip it out!

Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] I propose killing PL/Tcl's "modules" infrastructure

[Joshua D. Drake]

On 02/25/2017 10:14 AM, Tom Lane wrote:


Now, we could try to fix this bug, and add the regression test coverage
that the code clearly lacks, and upgrade the documentation about it from
its currently very sad state.  But I think the right answer is just to
remove the feature altogether.  It's evidently not being used, and it's
kind of insecure by design, and it would not be that hard for someone
to provide equivalent functionality entirely in userland if they really
wanted it.


I don't see a reason to keep pl/tcl in core at all so ripping out the 
functionality seems the least disruptive and perhaps even a deprecation 
of the PL (at least from a core perspective) in v10.


JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Documentation improvements for partitioning

[Joshua D. Drake]

On 02/23/2017 09:27 AM, Peter Geoghegan wrote:

On Thu, Feb 23, 2017 at 8:08 AM, Simon Riggs  wrote:



* "Good work so far, but there is still a very significant amount of
work to do."

There is always more work to do, so why say so? I think that the
implication is that this isn't complete as a feature that goes into
the next release, which I disagree with. There is nothing
disappointing to me about this feature, and, as I said, I am
unsurprised that it doesn't support certain things.




I don't think we need to start going down the avenue of "you could be 
nicer". We can all be nicer and we all have our good and bad days.


If we start worrying about egos to this degree, we will never get 
anything done.


JD
--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Documentation improvements for partitioning

[Joshua D. Drake]

On 02/15/2017 06:10 AM, Simon Riggs wrote:

On 13 February 2017 at 05:21, Amit Langote
 wrote:



If I issue DROP TABLE elsewhere, it doesn't refuse to drop because it
has indexes, sequences etc on it. So why should it just because it has
partitions?


Because partitions may have data.

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] [PATCH] Rename pg_switch_xlog to pg_switch_wal

[Joshua D. Drake]

-Hackers,

From the field. I do not care what you chose, I care that:

1. It is consistent
2. It is readable/understandable
3. It is documented
4. It is done wholesale (because of usability)

That's it. So whatever meets that criteria, let's go for it. That may 
mean that certain commands look a little off but with the goal of 
consistency that is o.k.. It becomes explainable.


JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Checksums by default?

[Joshua D. Drake]

On 01/25/2017 05:25 PM, Peter Geoghegan wrote:

On Wed, Jan 25, 2017 at 1:22 PM, Peter Geoghegan  wrote:

I understand that my experience with storage devices is unusually
narrow compared to everyone else here. That's why I remain neutral on
the high level question of whether or not we ought to enable checksums
by default. I'll ask other hackers to answer what may seem like a very
naive question, while bearing what I just said in mind. The question
is: Have you ever actually seen a checksum failure in production? And,
if so, how helpful was it?


No.

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Checksums by default?

[Joshua D. Drake]

On 01/25/2017 11:41 AM, Tom Lane wrote:

Stephen Frost  writes:

Would you say that most user's databases run fast enough with checksums
enabled?  Or more than most, maybe 70%?  80%?  In today's environment,
I'd probably say that it's more like 90+%.


It would be nice if there were some actual evidence about this, rather
than numbers picked out of the air.


I agree that it's unfortunate that we haven't put more effort into
fixing that- I'm all for it, but it's disappointing to see that people
are not in favor of changing the default as I believe it would both help
our users and encourage more development of the feature.


I think the really key point is that a whole lot of infrastructure work
needs to be done still, and changing the default before that work has been
done is not going to be user-friendly.  The most pressing issue being the
difficulty of changing the setting after the fact.  It would be a *whole*
lot easier to sell default-on if there were a way to turn it off, and yet
you want us to buy into default-on before that way exists.  Come back
after that feature is in, and we can talk.


+1

Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Checksums by default?

[Joshua D. Drake]

On 01/21/2017 09:09 AM, Tom Lane wrote:

Stephen Frost  writes:

As for checksums, I do see value in them and I'm pretty sure that the
author of that particular feature did as well, or we wouldn't even have
it as an option.  You seem to be of the opinion that we might as well
just rip all of that code and work out as being useless.


Not at all; I just think that it's not clear that they are a net win
for the average user,


Tom is correct here. They are not a net win for the average user. We 
tend to forget that although we collectively have a lot of enterprise 
installs where this does matter, we collectively do not equal near the 
level of average user installs.


From an advocacy perspective, the average user install is the one that 
we tend most because that tending (in theory) will grow something that 
is more fruitful e.g; the enterprise install over time because we 
constantly and consistently provided a reasonable and expected 
experience to the average user.


Sincerely,

JD




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Packages: Again

[Joshua D. Drake]

On 01/17/2017 09:26 AM, Robert Haas wrote:

On Fri, Jan 13, 2017 at 7:24 PM, Peter Geoghegan  wrote:

MERGE isn't UPSERT, and isn't even in competition with UPSERT as a
feature. I've written reams of text explaining why this is so in
precise detail, ...



Hello,

This is the webinar that started this whole thread (well the original 
thread, not this weird MERGE/UPSERT stuff):


https://www.commandprompt.com/blog/postgresql_for_oracle_people/

Thank you to everyone that responded. You will see in this Webinar that 
at least from the Oracle people perspective, PostgreSQL is not an option 
unless it has packages.


The other item that people bring up a few times is Oracle Forms but as 
that is actually external (although dependent) on Oracle, I don't see 
that as our responsibility.


Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Packages: Again

[Joshua D. Drake]

On 01/12/2017 03:35 PM, Craig Ringer wrote:






So far that's all "that'd be nice, but isn't a technical barrier" stuff.

Package variables for example. When _do_ you _need_ them? For what? (I'm
aware of some uses but "when you need them" helps us not at all).



Well my answer would be, "because we want Oracle people to have an easy 
time migrating". I know that isn't the -hackers answer but clearly there 
is a demand for them. I would note that EDB Advanced server supports 
them, exactly because there is a demand for them.


Again, I am not making the technical argument here. I don't have the 
time to research it. I am making a usability argument for a potentially 
huge portion of database users to allow PostgreSQL to be more attractive 
to them, argument.


I also received this today:

"""
Well, packages make programming much easier. Not only do you keep 
related procedures together, you can also have private package variables 
and the package initialization. Also, packages are units of security, so 
you can grant permissions on the package to the entire group of users 
and if you later modify the package and add a function, you don't need 
to grant it separately.

"""

Sincerely,

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Packages: Again

[Joshua D. Drake]

On 01/11/2017 04:12 PM, Craig Ringer wrote:


What aspects / features of packages were the key issues?


Unfortunately we didn't get too far into it because the webinar was 
about Postgres specifically. That said, I have been doing some followup. 
Here is some of it:


because packages[1]

o break the dependency chain (no cascading invalidations when you 
install a new package body -- if you have procedures that call 
procedures -- compiling one will invalidate your database)


o support encapsulation -- I will be allowed to write MODULAR, easy to 
understand code -- rather then MONOLITHIC, non-understandable procedures


o increase my namespace measurably. package names have to be unique in a 
schema, but I can have many procedures across packages with the same 
name without colliding


o support overloading

o support session variables when you need them

o promote overall good coding techniques, stuff that lets you write code 
that is modular, understandable, logically grouped together


Note: I am not arguing the technical merits here. My goal is 100%, how 
do we get Oracle folks a true Open Source Oracle alternative.


As I get more from people, I will post.

Sincerely,

JD

1. 
https://asktom.oracle.com/pls/asktom/f?p=100:11:0P11_QUESTION_ID:7452431376537




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Retiring from the Core Team

[Joshua D. Drake]

On 01/12/2017 07:48 AM, Jonathan Katz wrote:



On Jan 11, 2017, at 7:29 PM, Josh Berkus  wrote:

It's been a long, fun ride, and I'm proud of the PostgreSQL we have
today: both the database, and the community.  Thank you for sharing it
with me.


Thank you for all of your contributions to the PostgreSQL community and paving 
the way for many of the advocacy efforts that we can do today through your 
leadership.  I wish you the best of luck in all of your endeavors!



Josh,

Thank you for all your service. May your new adventures be as fulfilling!

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Packages: Again

[Joshua D. Drake]

On 01/11/2017 11:32 AM, Pavel Stehule wrote:



We have a schemas instead - the PostgreSQL schema is close to Oracle
packages.


No. It isn't.

A Package is essentially a class with dependencies. It has nothing to do 
with schemas outside of being named qualified. For example:


https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/packages.htm#i4362

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

[HACKERS] Packages: Again

[Joshua D. Drake]

-hackers,

I know we have talked about this before but today it was impressed upon 
me rather firmly. I presented a Webinar: Postgres for Oracle People. The 
attendees were 90% pl/pgsql developers. 330 people registered for an 
event that was only allowed to host 100 people. The webinar went on for 
2 hours. (it was only scheduled for one hour, that is how interactive it 
was)


By far the tagline of this webinar from attendees was, "We can not port 
without packages"


So this is a reality. If we want tried and true Oracle developers to 
port to PostgreSQL, we must provide some level of package capability.


There are some that would say we don't need them. You are right, we 
don't need them. We should however want them if we want to continue to 
stomp through the business sector and continue growth.


I use this post to inspire conversation on how we can get this done.

Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] RustgreSQL

[Joshua D. Drake]

On 01/10/2017 08:12 AM, Robert Haas wrote:


Really?  What language would you pick in a vacuum?  The Linux kernel
is written in C, too, for pretty much the same reasons: it's the
canonical language for system software.  I don't deny that there may
be some newer languages out which could theoretically be used and work
well, but do any of them really have a development community and user
base around them that is robust enough that we'd want to be downstream
of it?  C has its annoyances, but its sheer pervasiveness is an
extremely appealing feature.


If we boil this down, I don't think any of this idea has to do with the 
fact that our database is written in C. I think it has to do with C is 
no longer "hip". We don't want to be hip. We are database people. Leave 
hip to MongoDB.


We want performance, stability, maturity and portability. (Not 
necessarily in that order).


There is not a single above hardware language (E.g; let's not rewrite in 
assembly) that provides those four requirements.


Rust is awesome. It is also 5 years old.
Go is awesome. It is also 8 years old.

C is awesome. It is 39 years old.

In human terms, C is the only one of these that has been around long 
enough to realize it isn't a teenager (or child really), and although 
you may still be able to do the things you could in your 20s, you are 
going to pay for them the next day.


JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Make pg_basebackup -x stream the default

[Joshua D. Drake]

On 12/15/2016 10:23 AM, Peter Eisentraut wrote:

On 11/8/16 12:45 PM, Magnus Hagander wrote:

Per some discussions with a number of different people at pgconfeu, here
is a patch that changes the default mode of pg_basebackup to be
streaming the wal, as this is what most users would want -- and those
that don't want it have to make other changes as well. Doing the "most
safe" thing by default is a good idea.

The new option "-x none" is provided to turn this off and get the
previous behavior back.


I would have expected that the "stream" method would become the default.
 Just a short while ago it was proposed to remove the "fetch" method
altogether, and it was only kept because of some niche use cases.  I
don't think "fetch" is the most safe method, because it requires
wal_keep_segments to be configured.


IMO, if you are using fetch just using archive_command. Let's rip it out 
of pg_basebackup or at least deprecate it.


jD






--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: pg_authid.rolpassword format (was Re: [HACKERS] Password identifiers, protocol aging and SCRAM protocol)

[Joshua D. Drake]

On 12/14/2016 11:41 AM, Stephen Frost wrote:

* Heikki Linnakangas (hlinn...@iki.fi) wrote:

On 14 December 2016 20:12:05 EET, Bruce Momjian  wrote:

On Wed, Dec 14, 2016 at 11:27:15AM +0100, Magnus Hagander wrote:



Storing plaintext passwords has been bad form for just about forever and
I wouldn't be sad to see our support of it go.  At the least, as was
discussed somewhere, but I'm not sure where it ended up, we should give
administrators the ability to control what ways a password can be
stored.  In particular, once a user has migrated all of their users to
SCRAM, they should be able to say "don't let new passwords be in any
format other than SCRAM-SHA-256".


It isn't as bad as it used to be. I remember with PASSWORD was the 
default. I agree that we should be able to set a policy that says, "we 
only allow X for password storage".


JD




Thanks!

Stephen




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Time to up bgwriter_lru_maxpages?

[Joshua D. Drake]

On 11/28/2016 11:40 AM, Jim Nasby wrote:

With current limits, the most bgwriter can do (with 8k pages) is 1000
pages * 100 times/sec = 780MB/s. It's not hard to exceed that with
modern hardware. Should we increase the limit on bgwriter_lru_maxpages?


Considering a single SSD can do 70% of that limit, I would say yes.

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Mail thread references in commits

[Joshua D. Drake]

On 11/17/2016 01:02 PM, Andrew Dunstan wrote:

I love seeing references to email threads in commit messages. It would
make them a lot friendlier though if a full http URL were included
instead of just a Message-ID, i.e. instead of  put
. I know this is
a bit more trouble. but not that much, and it would make it very easy to
follow with just a mouse click.


This thread as a whole is going on and on. Instead I thought I would 
reply back to the OP and see where that goes.


I too like to see references in commit messages to the email threads. It 
definitely makes it easier to track the collaboration of the commit.


I wonder if now is the time (again) to consider an issue tracker. Think 
about it! This whole thread would be solved with a commit message that said:


Allow replicated slaves to load balance plans to take load off the 
master per #18235 -- Robert Haas


Then all you have to do is hit #18235 into the issue tracker and viola!

I know our community historically has been against most things NIH but 
if we step back objectively --- using an issue tracker for this is 
certainly not any more crazy that implementing yet another new wheel.


Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Fix checkpoint skip logic on idle systems by tracking LSN progress

[Joshua D. Drake]

On 11/10/2016 09:33 AM, David Steele wrote:

On 11/10/16 10:28 AM, Stephen Frost wrote:


diff --git a/src/backend/access/transam/xlog.c 
b/src/backend/access/transam/xlog.c

[...]

+   if (log_checkpoints)
+   ereport(LOG, (errmsg("checkpoint skipped")));


Do we really need to log that we're skipping a checkpoint..?  As the
point of this is to avoid write activity on a system which is idle, it
doesn't make sense to me to add a new cause for writes to happen when
we're idle.


log_checkpoints is not enabled by default, though, so if the user does
enable it don't you think they would want to know when checkpoints
*don't* happen?


Yes but I don't know that it needs to be anywhere below DEBUG2 (vs 
log_checkpoints).


Sincerely,

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Issues with building snap packages and psql

[Joshua D. Drake]

On 10/26/2016 12:10 PM, Joshua D. Drake wrote:

On 10/26/2016 11:33 AM, Robert Haas wrote:

On Wed, Oct 26, 2016 at 2:22 PM, Joshua D. Drake



It is also inconsistent with how other programs behave. For example
if psql
uses readline, and you change the value of $HOME, then readline uses
$HOME/.inputrc whereas psql does not use $HOME/.psqlrc.


Sounds like the same issue that Rudolf Gavlas complained about in
https://www.postgresql.org/message-id/CAEH6cQqbdbXoUHJBbX9ixwfjFFsUC-a8hFntKcci=diwgbb...@mail.gmail.com



This problem clearly an improper implementation on our part. We have 
found a work around by wrapping the launch of psql so we can set PSQLRC. 
At some point, perhaps with v10 we could change this. This problem has 
been noted on other ends of the innertubes as well (we submitted this as 
a potential bug to Ubuntu and they too found that we don't handle this 
correctly).


Sincerely,

JD





--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] pg_hba_file_settings view patch

[Joshua D. Drake]

On 10/26/2016 12:54 PM, Josh Berkus wrote:

On 10/26/2016 12:24 PM, Tom Lane wrote:

Robert Haas  writes:

FWIW, I'm -1 on using JSON here.  I don't believe that we should start
using JSON all over the place just because we can.  If we do that,
we'll end up with a mishmash of styles, and maybe look silly when JSON
is replaced by the new and much better SDGJHSDR format.


I concur.  JSON isn't a core datatype and I don't want to see it treated
as one.  We should redesign this view so that it doesn't rely on anything
more advanced than arrays.


Huh?  Sure it is.   Ships in PostgreSQL-core.

I mean, I'm not particularly in favor of using JSON for this (arrays
seem OK), but that seems like an invalid reason not to.


-1 to JSON for this.

JD






--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Issues with building snap packages and psql

[Joshua D. Drake]

On 10/26/2016 11:33 AM, Robert Haas wrote:

On Wed, Oct 26, 2016 at 2:22 PM, Joshua D. Drake <j...@commandprompt.com> wrote:

After some further investigation and collaboration with #postgresql it looks
like what we have here is an actual bug or at least improper implementation.
Apparently, we use getpwuid on the euid to locate the 'home directory'. This
is incorrect (as well as inconsistent to our own documentation) [1]. Our
documentation says that dotfiles are ~/.whatever, and ~ on its own in shell
means the value of $HOME, it does NOT mean the current user's homedir from
getpwuid.


It is also inconsistent with how other programs behave. For example if psql
uses readline, and you change the value of $HOME, then readline uses
$HOME/.inputrc whereas psql does not use $HOME/.psqlrc.


Sounds like the same issue that Rudolf Gavlas complained about in
https://www.postgresql.org/message-id/CAEH6cQqbdbXoUHJBbX9ixwfjFFsUC-a8hFntKcci=diwgbb...@mail.gmail.com


It does sound similar, unfortunately the limited response doesn't seem 
to address the issue in that we are doing it in a way that nobody (or at 
least commonly) else does.


To me this sounds strikingly familiar to how we ignored how everyone 
else handled commenting out parameters vs how we thought we were right 
until we finally decided everyone else was already correct and changed 
it. (metaphorically speaking)


That said, I am not interested in a philosophical argument. It appears 
to me that based on the referenced PDF that we are wrong. How do we fix 
it? What are the implications of that? I just want to solve the problem.


Sincerely,

JD




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Issues with building snap packages and psql

[Joshua D. Drake]

Hello,

After some further investigation and collaboration with #postgresql it 
looks like what we have here is an actual bug or at least improper 
implementation. Apparently, we use getpwuid on the euid to locate the 
'home directory'. This is incorrect (as well as inconsistent to our own 
documentation) [1]. Our documentation says that dotfiles are 
~/.whatever, and ~ on its own in shell means the value of $HOME, it does 
NOT mean the current user's homedir from getpwuid.



It is also inconsistent with how other programs behave. For example if 
psql uses readline, and you change the value of $HOME, then readline 
uses $HOME/.inputrc whereas psql does not use $HOME/.psqlrc.


Sincerely,

JD

1. 
http://pubs.opengroup.org/onlinepubs/9699919799/utilities/V3_chap02.html#tag_18_06_01


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Issues with building snap packages and psql

[Joshua D. Drake]

On 10/26/2016 10:26 AM, Andres Freund wrote:



On October 26, 2016 8:09:11 PM GMT+03:00, "Joshua D. Drake" 
<j...@commandprompt.com> wrote:


postgres=# \! export
export HOME='/home/jd/snap/postgresql96/1'


That doesn't do much. Isn't that just spawning a shell in which you set the 
variable, which then immediately exits? And this the value isn't set anywhere 
relevant?


I was assuming (and TGL corrected me) that psql was going to pull the 
$HOME variable to determine where to read/write .psqlrc and .psql_history.


Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Issues with building snap packages and psql

[Joshua D. Drake]

On 10/26/2016 10:16 AM, Tom Lane wrote:

"Joshua D. Drake" <j...@commandprompt.com> writes:

Is psql getting the home directory from /etc/passwd?


Yes, of course.


If so what can we do about that?


Fix your /etc/passwd entry?  Seems unlikely that psql is the only
program that believes that entry.


It isn't that simple. Snap packages are similar to a chroot. My home 
directory *is* /home/jd but snap packages can't write to that because of 
the isolation.


Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

[HACKERS] Issues with building snap packages and psql

[Joshua D. Drake]

Hello,

CMD has been working on building snap packages. It has been an adventure 
and we are very close to having them complete. In fact they are complete 
enough that we have published them in the snap repo.


jd@jd-wks:~$ snap find postgres
NameVersion  Developer  Notes  Summary
postgresql969.6.0cmd-  PostgreSQL is a powerful, 
open source object-relational database system.
postgresql959.5.3cmd-  PostgreSQL is a powerful, 
open source object-relational database system.
postgresql949.4.9cmd-  PostgreSQL is a powerful, 
open source object-relational database system.
postgresql939.3.14   cmd-  PostgreSQL is a powerful, 
open source object-relational database system.


However, we have found a problem with permissions that we haven't been 
able to overcome. The error presents as a simple:


could not save history to file "/home/jd/.psql_history": Permission denied

We tried the work around as presented here:

https://www.commandprompt.com/blog/psql_tip_change_history_location/

It didn't work and I couldn't figure out why. It was especially 
confusing because if you are in psql via snap $HOME is set correctly:


postgres=# \! export
export HOME='/home/jd/snap/postgresql96/1'

Then I traced it and saw this:

open("/etc/passwd", O_RDONLY|O_CLOEXEC) = 7
lseek(7, 0, SEEK_CUR)   = 0
fstat(7, {st_mode=S_IFREG|0644, st_size=2581, ...}) = 0
mmap(NULL, 2581, PROT_READ, MAP_SHARED, 7, 0) = 0x7f1f9e5fc000
lseek(7, 2581, SEEK_SET)= 2581
munmap(0x7f1f9e5fc000, 2581)= 0
close(7)= 0
[...]
futex(0x1096088, FUTEX_WAIT, 0, NULL)   = 0
futex(0x1096088, FUTEX_WAIT, 0, NULL/home/jd/.psqlrc: Permission denied
psql (9.6.0)
Type "help" for help.

postgres=# \q
could not save history to file "/home/jd/.psql_history": Permission denied
 
+++ exited with 0 +++

Is psql getting the home directory from /etc/passwd? If so what can we 
do about that?


Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

[HACKERS] Does it make sense to add a -q (quiet) flag to initdb?

[Joshua D. Drake]

Hello,

Per: https://www.commandprompt.com/blog/can_i_make_initdb_quiet/

This was a question that was asked on #postgresql. Obviously we found a 
work around but I wonder if it makes sense to add a -q to solve some of 
these issues? (I could see it being useful with automation).


Sincerely,

JD
--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Remove autovacuum GUC?

[Joshua D. Drake]

Hello,

What about a simpler solution to all of this. Let's just remove it from 
postgresql.conf. Out of sight. If someone needs to test they can but a 
uneducated user won't immediately know what to do about that "autovacuum 
process" and when they look it up the documentation is exceedingly blunt 
about why to *not* turn it off.


Sincerely,

JD


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

Re: [HACKERS] Remove autovacuum GUC?

[Joshua D. Drake]

On 10/20/2016 09:24 AM, Robert Haas wrote:

On Thu, Oct 20, 2016 at 11:53 AM, Joshua D. Drake <j...@commandprompt.com> 
wrote:

The right answer isn't the answer founded in the reality for many if not
most of our users.


I think that's high-handed nonsense.  Sure, there are some
unsophisticated users who do incredibly stupid things and pay the
price for it, but there are many users who are very sophisticated and
make good decisions and don't want or need the system itself to act as
a nanny.  When we constrain the range of possible choices because


That argument suggests we shouldn't have autovacuum :P


somebody might do the wrong thing, sophisticated users are hurt
because they can no longer make meaningful choices, and stupid users
still get in trouble because that's what being stupid does.  The only
way to fix that is to help people be less stupid.  You can't tell
adult users running enterprise-grade software "I'm sorry, Dave, I
can't do that".  Or at least it can cause a few problems.


As mentioned in an other reply, I am not suggesting we can't turn off 
autovacuum as a whole. Heck, I even suggested being able to turn it off 
per database (versus just per table). I am suggesting that we get rid of 
a foot gun for unsophisticated (and thus majority of our users).





I mean that the right answer for -hackers isn't necessarily the right answer
for users. Testing? Users don't test. They deploy. Education? If most people
read the docs, CMD and a host of other companies would be out of business.


I've run into these kinds of situations, but I know for a fact that
there are quite a few EnterpriseDB customers who test extremely
thoroughly, read the documentation in depth, and really understand the
system at a very deep level.


Those aren't exactly the users we are talking about are we? I also run 
into those users all the time.




And I've seen customers solve real production problems by doing
exactly that, and scheduling vacuums manually.  Have I seen people


1 != 10


cause bigger problems than the ones they were trying to solve?  Yes.
Have I recommended something a little less aggressive than completely
shutting autovacuum off as perhaps being a better solution?  Of
course.  But I'm not going to sit here and tell somebody "well, you
know, what you are doing is working whereas the old thing was not
working, but trust me, the way that didn't work was way better...".



I find it interesting that we are willing to do that every time we add a 
feature but once we have that feature it is like pulling teeth to show 
the people that implemented those features that some people don't think 
it was better :P


Seriously though. I am only speaking from experience from 20 years of 
customers. CMD also has customers just like yours but we also run into 
lots and lots of people that still do really silly things like we have 
already discussed.


Sincerely,

Joshua D. Drake


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Renaming of pg_xlog and pg_clog

[Joshua D. Drake]

On 10/20/2016 09:12 AM, Stephen Frost wrote:

* Tom Lane (t...@sss.pgh.pa.us) wrote:

Robert Haas  writes:



That said, I'd also like to see a --force or similar option or mechanism
put in place to reduce the risk of users trashing their system because
they think pg_resetwal is "safe." ("It's just gonna reset things to make
the database start again, should be fine.").

pg_destroydb almost seems like a better choice, though I suppose
'pg_clearwal' would be more acceptable.  Doesn't have quite the same
impact though.


pg_dropwal

Users won't *drop* things they shouldn't on purpose (usually) but they 
will reset and will clear them. Destroydb isn't anymore accurate because 
it doesn't destroy it. Instead it makes it so I can log in again and see 
my data.


(Yes we all know the real implications with it but from a DUH user 
perspective...)


Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Disable autovacuum guc?

[Joshua D. Drake]

On 10/20/2016 08:54 AM, Josh Berkus wrote:

On 10/20/2016 06:34 AM, Joshua D. Drake wrote:

On 10/19/2016 07:22 PM, Josh Berkus wrote:

On 10/19/2016 06:27 PM, Joshua D. Drake wrote:



Hrm, true although that is by far a minority of our users. What if we
made it so we disabled the autovacuum guc but made it so you could
disable autovacuum per database (ALTER DATABASE SET or something such
thing?).


Well, that wouldn't fix the problem; people would just disable it per
database, even if it was a bad idea.


I doubt this very much. It requires a different level of sophistication.

General users (not you, not me, and certainly not Haas or Lane) don't 
run anything but an application backed to an ORM. They understand a conf 
file but they aren't going to touch anything they consider

"underneath".

Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Remove autovacuum GUC?

[Joshua D. Drake]

On 10/20/2016 07:12 AM, Robert Haas wrote:

On Wed, Oct 19, 2016 at 9:24 PM, Joshua D. Drake <j...@commandprompt.com> wrote:

Setting autovacuum=off is at least useful for testing purposes and
I've used it that way.  On the other hand, I haven't seen a customer
disable this unintentionally in years.  Generally, the customers I've
worked with have found subtler ways of hosing themselves with
autovacuum.  One of my personal favorites is autovacuum_naptime='1 d'
-- for the record, that did indeed work out very poorly.


Yes, I have seen that as well and you are right, it ends poorly.



I think that this the kind of problem that can only properly be solved
by education.  If somebody thinks that they want to turn off
autovacuum, and you keep them from turning it off, they just get
frustrated.  Sometimes, they then find a back-door way of getting what


I think I am coming at this from a different perspective than the 
-hackers. Let me put this another way.


The right answer isn't the answer founded in the reality for many if not 
most of our users.


What do I mean by that?

I mean that the right answer for -hackers isn't necessarily the right 
answer for users. Testing? Users don't test. They deploy. Education? If 
most people read the docs, CMD and a host of other companies would be 
out of business.


I am not saying I have the right solution but I am saying I think we 
need a *different* solution. Something that limits a *USERS* choice to 
turn off autovacuum. If -hackers need testing or enterprise developers 
need testing, let's account for that but for the user that says this:


My machine/instance bogs down every time autovacuum runs, oh I can turn 
it off


Let's fix *that* problem.

Sincerely,

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Indirect indexes

[Joshua D. Drake]

On 10/20/2016 06:39 AM, Alvaro Herrera wrote:

Bruce Momjian wrote:



Also, it seems indirect indexes would be useful for indexing columns
that are not updated frequently on tables that are updated frequently,
and whose primary key is not updated frequently.  That's quite a logic
problem for users to understand.


I don't think we should be optimizing only for dumb users.  In any case,
updating primary key values is very rare; some would say it never
happens.


Just because a person doesn't understand a use case doesn't make them dumb.

That said would it be possible to make this index an extension (like 
rum?). Assuming of course we can get any required infrastructure changes 
done in a general way.


I do think the feature has merit.

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Disable autovacuum guc?

[Joshua D. Drake]

On 10/19/2016 07:22 PM, Josh Berkus wrote:

On 10/19/2016 06:27 PM, Joshua D. Drake wrote:

Hello,

After all these years, we are still regularly running into people who
say, "performance was bad so we disabled autovacuum". I am not talking
about once in a while, it is often. I would like us to consider removing
the autovacuum option. Here are a few reasons:

1. It does not hurt anyone
2. It removes a foot gun
3. Autovacuum is *not* optional, we shouldn't let it be
4. People could still disable it at the table level for those tables
that do fall into the small window of, no maintenance is o.k.
5. People would still have the ability to decrease the max_workers to 1
(although I could argue about that too).


People who run data warehouses where all of the data comes in as batch
loads regularly disable autovacuum, and should do so.  For the DW/batch
load use-case, it makes far more sense to do batch loads interspersed
with ANALYZEs and VACUUMS of loaded/updated tables.


Hrm, true although that is by far a minority of our users. What if we 
made it so we disabled the autovacuum guc but made it so you could 
disable autovacuum per database (ALTER DATABASE SET or something such 
thing?).


Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

[HACKERS] Remove autovacuum GUC?

[Joshua D. Drake]

Hello,

After all these years, we are still regularly running into people who 
say, "performance was bad so we disabled autovacuum". I am not talking 
about once in a while, it is often. I would like us to consider removing 
the autovacuum option. Here are a few reasons:


1. It does not hurt anyone
2. It removes a foot gun
3. Autovacuum is *not* optional, we shouldn't let it be
4. People could still disable it at the table level for those tables 
that do fall into the small window of, no maintenance is o.k.
5. People would still have the ability to decrease the max_workers to 1 
(although I could argue about that too).


Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

[HACKERS] Disable autovacuum guc?

[Joshua D. Drake]

Hello,

After all these years, we are still regularly running into people who 
say, "performance was bad so we disabled autovacuum". I am not talking 
about once in a while, it is often. I would like us to consider removing 
the autovacuum option. Here are a few reasons:


1. It does not hurt anyone
2. It removes a foot gun
3. Autovacuum is *not* optional, we shouldn't let it be
4. People could still disable it at the table level for those tables 
that do fall into the small window of, no maintenance is o.k.
5. People would still have the ability to decrease the max_workers to 1 
(although I could argue about that too).


Sincerely,

JD
--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Indirect indexes

[Joshua D. Drake]

On 10/18/2016 11:28 AM, Alvaro Herrera wrote:


Vacuuming presents an additional challenge: in order to remove index
items from an indirect index, it's critical to scan the PK index first
and collect the PK values that are being removed.  Then scan the
indirect index and remove any items that match the PK items removed.
This is a bit problematic because of the additional memory needed to
store the array of PK values.  I haven't implemented this yet.


As a whole I think the idea is interesting but the above scares me. Are 
we trading initial performance gains for performance degradation through 
maintenance? Since autovacuum is an indeterminate launch we could have a 
situation where even a medium level updated laden table becomes a source 
of contentions for IO and memory resources. I don't know that we would 
see issues on modern bare metal but considering our implementation space 
is places like RDS and GCE now, this is a serious consideration.


Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

[HACKERS] restoration after crash slowness, any way to improve?

[Joshua D. Drake]

-hackers,

So this is more of a spit balling thread than anything. As I understand 
it, if we have a long running transaction or a large number of wal logs 
and we crash, we then have to restore those logs on restart to the last 
known good transaction. No problem.


I recently ran a very long transaction. I was building up a large number 
of rows into a two column table to test index performance. I ended up 
having to kill the connection and thus the transaction after I realized 
I had an extra zero in my generate_series(). (Side note: Amazing the 
difference a single zero can make ;)). When coming back up, I watched 
the machine and I was averaging anywhere from 60MBs to 97MBs on writes. 
That in itself isn't that bad over a single thread and a single SSD, 
doing what it is doing.


However, since I know this machine can get well over 400MBs when using 
multiple connections I can't help but wonder if there is anything we can 
do to make restoration more efficient without sacrificing the purpose of 
what it is doing?


Can we have multiple readers pull transaction logs into shared_buffers 
(on recovery only), sort the good transactions and then push them back 
to the walwriter or bgwriter to the pages?


Anyway, like I said, spitballing and I thought I would start the thread.

Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Renaming of pg_xlog and pg_clog

[Joshua D. Drake]

On 08/29/2016 10:00 AM, Daniel Verite wrote:


Let's imagine that pg_xlog is named wal instead.
How does that help our user with the disk space problem?
Does that point to a path of resolution? I don't see it.
What do we think that user's next move will be?
After all, WAL means Write Ahead *Log*.


If, they look it up (which they would likely have to) they will see it 
isn't removable. :D That is the point I am making. If it is called xlog 
the brain says "logs". If it says wal the brain says, "What is wal?"


At that point they have to look it up and then if they still delete it; 
well that is what emergency rates are for :P


Sincerely,

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Renaming of pg_xlog and pg_clog

[Joshua D. Drake]

On 08/29/2016 08:07 AM, Tom Lane wrote:

"Joshua D. Drake" <j...@commandprompt.com> writes:

Also as a note to the idea that we make break things for external user
space; the next version being v10 is the exact time to do that.


Let's please drop this meme that "v10 is a great time to break things".
We don't want this to be any worse than any other major-version upgrade.


The moment you break backward compatibility, it will be worse. We are 
talking about breaking backward compatibility. So let's just accept it 
as it is, there is a mentality about a major jump. A major jump 
(9.6->10) is *the* perfect time to make world changing, changes.



If we throw thirty different major incompatibilities in at once, we're
going to be hearing about how painful it was for the next decade, even if
any one of them individually would have been manageable.  Or, if we make
the pain factor too high, users will simply not upgrade, and we'll be
faced with demands that we support 9.6 forever.


Whiners always find a reason to whine.

Let's be on two feet here. I am not saying we should jump to using json 
notation for our next release. I am simply stating that any largish 
(even if it is a small patch) changes to expected behavior should be 
done with care. We have a window because no matter how much you yell, I 
yell, Magnus yells, or anybody else yells; the telling story will be, 
"10.0 is a huge jump from 9.6". Most people *WOULD NOT CARE* if we only 
changed one thing. They care that we jumped 4 releases. That type of 
communication implies BIG CHANGES.


Whether you like it or not. Whether I like it or not.

Sincerely,

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Renaming of pg_xlog and pg_clog

[Joshua D. Drake]

On 08/29/2016 06:42 AM, Daniel Verite wrote:


Aside from that, we might also question how much of the excuse
"pg_xlog looked like it was just deletable logs" is a lie made up
after the fact, because anybody wrecking a database is not against
deflecting a bit of the blame to the software, that's human.

The truth being that they took the gamble that postgres
will somehow recover from the deletion, at the risk of possibly
loosing the latest transactions. That doesn't necessarily look
so bad when current transactions are failing anyway for lack of disk
space, users are yelling at you, and you're frantically searching for
anything to delete in the FS to come back online quickly.
Personally I'm quite skeptical of the *name* of the directory
playing that much of a role in this scenario.


Oh it makes perfect sense.

User who isn't a postgres/database person installs X software. X 
software uses PostgreSQL and you have read on the intertubes about how 
Postgres is awesome. So you install it, get everything up and running 
from the README.md on Github. You walk away.


A year later, after it becomes crucial to whatever it is you do the 
system crashes. You have no idea what is going on, you just set this up 
on some recycled server or VM. You log in, see that all the space and 
you find that you are using a ton of disk space. You look around for 
anything you can delete. You find a directory called pg_xlog, it says 
log, junior ignorant, don't want to be a sysadmin 101 says, "delete logs".


Boom, crushed server. Let us not forget that by far the number of 
PostgreSQL users we have, have never done ANYTHING with postgres except 
make it so something like Drupal can connect to it.


JD






Best regards,




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Renaming of pg_xlog and pg_clog

[Joshua D. Drake]

On 08/29/2016 12:04 AM, Magnus Hagander wrote:

On Mon, Aug 29, 2016 at 2:45 AM, Jim Nasby > wrote:

On 8/26/16 4:08 PM, Andres Freund wrote:

Splitting of ephemeral data seems to have a benefit, the rest
seems more
like rather noisy busywork to me.


People accidentally blowing away pg_clog or pg_xlog is a pretty
common occurrence, and I don't think there's all that many tools
that reference them. I think it's well worth renaming them.


Pretty sure every single backup tool or script out there is referencing
pg_xlog. If it's not, then it's broken...


No, not really. Consider a filesytem backup using archiving and base 
backups. It doesn't care one lick about pg_xlog. And I guarantee you 
that there are tons of people running a backup like that considering the 
same script would work all the way back to 8.2 (.1?).


Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Renaming of pg_xlog and pg_clog

[Joshua D. Drake]

On 08/27/2016 11:11 AM, Tom Lane wrote:

Alvaro Herrera  writes:

I'm for renaming too, but I'd go with Peter E's suggestion: move pg_xlog
to something like $PGDATA/var/wal or $PGDATA/srv/wal or something like that.


I think that would make sense if we were to relocate *everything* under
PGDATA into some FHS-like subdirectory structure.  But I'm against moving
the config files for previously-stated reasons, and I doubt it makes sense
to adopt an FHS-like structure only in part.


I think that is a very reasonable suggestion.

Also as a note to the idea that we make break things for external user 
space; the next version being v10 is the exact time to do that.


JD



regards, tom lane





--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] pg_dump with tables created in schemas created by extensions

[Joshua D. Drake]

On 08/26/2016 04:15 PM, Tomas Vondra wrote:

On 08/27/2016 12:37 AM, Tom Lane wrote:

=?UTF-8?B?TWFydMOtbiBNYXJxdcOpcw==?=  writes:

Looking at this issue today, I found that we are not setting a
dependency for an index created inside an extension.


Surely the index has a dependency on a table, which depends on the
extension?

If you mean that you want an extension to create an index on a table
that doesn't belong to it, but it's assuming pre-exists, I think
that's just stupid and we need not support it.



I don't see why that would be stupid (and I'm not sure it's up to us to
just decide it's stupid).


+1, there are a lot of workflow patterns that make sense and don't make 
sense depending on your perspective, consider safe mode with MySQL. I 
personally think it is stupid too but it also obviously has a huge 
useful user base.




Imagine you use extensions to break the application into modules. You
have a "basic" extension, with the table, and a "search" extension
implementing fulltext search on top of that table. Isn't it natural to
keep the gin indexes in the search extension?

So the basic--1.0.sql will do something like

  CREATE TABLE x ( ...)

and search--1.0.sql would do

  CREATE INDEX y ON x USING gin (z);
  CREATE FUNCTION search_objects(..) ...

because the index and function doing the search are somewhat tightly
coupled. I admit this is just an example and I don't know how many
people use extensions this way, but I don't see why this would be
inherently stupid pattern?


It isn't and in fact shows that as we extend Pg, the more we allow 
people flexibility in developing WITHIN the database, the more we will 
be able to influence them to do so. That is a good thing.


Or maybe we should just tell everyone, use an ORM it will solve all your 
problems. (/sarcasm)



Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Renaming of pg_xlog and pg_clog

[Joshua D. Drake]

On 08/26/2016 09:28 AM, Tom Lane wrote:

Magnus Hagander  writes:

On Aug 26, 2016 5:54 PM, "Peter Eisentraut" <
peter.eisentr...@2ndquadrant.com> wrote:

If we're going to do some renaming, then I suggest we do a
mini-file-system structure under $PGDATA, like

$PGDATA/etc
$PGDATA/log
$PGDATA/run (lock files etc.)
$PGDATA/tmp
$PGDATA/var

The names of all the things under "var" could still be refined, but it's
much less likely that users will confuse data with configuration or
plain logs under that scheme



Interesting idea. I worry a bit that this might encourage distributions to
split it up into different places though, and I'm not sure we want to
encourage that..


Yeah, I'm afraid that these names are not as well standardized as Peter
probably wishes they were.  Also, I'd just as soon not move/rename things
that don't really need it.  If, for example, we decide to move
postgresql.conf to etc/postgresql.conf, that is going to break a metric
ton of stuff that doesn't need to get broken AFAICS.


I am not so sure that is accurate. Yes, Windows is an outlying but any 
Unix person is going to easily understand etc log run. Further as Linux 
is by far our most run platform (outside of possibly Windows) it will 
feel right at home for the largest growing user base. Personally I 
really like the idea of:


$PGDATA/etc/postgresql.conf
$PGDATA/log/Thu.log
$PGDATA/run/postmaster.pid
$PGDATA/var/base/16758

Although... wouldn't run be under var?

JD




regards, tom lane





--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Renaming of pg_xlog and pg_clog

[Joshua D. Drake]

On 08/25/2016 07:39 PM, Michael Paquier wrote:

Hi all,

I am relaunching $subject as 10 development will begin soon. As far as
I know, there is agreement that we can do something here. Among the
different proposals I have found:
- pg_clog renamed to pg_commit_status, pg_xact or pg_commit
- pg_xlog renamed to pg_xjournal, pg_wal or pg_journal


I think the use of the pg_ prefix is redundant. Just a directory called: 
wal will do (for example).


In reference to pg_xlog specifically, it should be wal. It is the Write 
Ahead Log, not the Journal (although I recognize they can be 
synonymous). All the documentation talks about Write Ahead Log.


Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] Renaming of pg_xlog and pg_clog

[Joshua D. Drake]

On 08/26/2016 03:48 AM, Magnus Hagander wrote:



Same reason I'm also +1 for Stephens suggestion to put all things that
should not be in a base backup into the same directory. That may break
things now, but it will simplify things down the road. And doing it at
the same time as renaming these things makes a lot of sense, because it
causes breakage that tool-builders *have* to look at, and then they will
hopefully also notice the other change.


If this is done this fall, developers will have at least a year to fix 
their utilities.


JD




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] [GENERAL] C++ port of Postgres

[Joshua D. Drake]

On 08/16/2016 09:33 AM, Robert Haas wrote:

On Tue, Aug 16, 2016 at 10:47 AM, Jim Nasby  wrote:

On 8/16/16 2:52 AM, Gavin Flower wrote:



I agree with your statement that one of our biggest problems is
getting more developers interested in working on PostgreSQL.  Even if
there's only a 10% chance that something like this will help, why not?
 We're not talking about moving the earth.


Right. It is just reality that less people are learning C which means 
less people will be interested in joining a project that is focused or 
(required) to be C.


Sincerely,

JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

[Joshua D. Drake]

On 08/11/2016 10:46 AM, Kevin Grittner wrote:

On Wed, Aug 10, 2016 at 5:14 PM, Tom Lane  wrote:

Kevin Grittner  writes:



But a DBA who has a problem doesn't care what the truth will be in
a year or two -- the interest is in *right now* on one particular
cluster.


If you are a DBA wanting to know how fine-grained the locking is
in a particular index type, you really need to read the source code
or ask a hacker.




This has to be a joke. With the greatest respect, this show a world of 
disconnect from the people that actually use this software.


JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [HACKERS] Proposal for CSN based snapshots

[Joshua D. Drake]

On 08/10/2016 09:04 AM, Stephen Frost wrote:

* Joshua D. Drake (j...@commandprompt.com) wrote:

+1 for Robert here, removing async commit is a non-starter. It is
PostgreSQL performance 101 that you disable synchronous commit
unless you have a specific data/business requirement that needs it.
Specifically because of how much faster Pg is with async commit.


I agree that we don't want to get rid of async commit, but, for the
archive, I wouldn't recommend using it unless you specifically understand
and accept that trade-off, so I wouldn't lump it into a "PostgreSQL
performance 101" group- that's increasing work_mem, shared_buffers, WAL
size, etc.  Accepting that you're going to lose *committed* transactions
on a crash requires careful thought and consideration of what you're
going to do when that happens, not the other way around.


Yes Stephen, you are correct which is why I said, "unless you have a 
specific data/business requirement that needs it".


Thanks!

jD



Thanks!

Stephen




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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