Notes on testing Postgres 10b1
Folks,
I've put together some demos on PostgreSQL 10beta1. Here's a few
feedback notes based on my experience with it.
Things I tested
----------------
* Logical replication pub/sub with replicating only two tables out of a
12-table FK heirarchy, including custom data types
* Partitioning a log-structured table, including a range type, exclusion
constraint, and foreign key.
* Various Parallel index queries on a 100m-row pgbench table
* Full text JSON search in a books database
* SCRAM authentication for local connections and replication
Positive changes beyond the obvious
-----------------------------------
* Yay defaults with replication on!
* Having defaults on the various _workers all devolve from max_workers
is also great.
* Constraint exclusion + partitioning Just Worked.
Questions
----------
Q1. Why does wal_level default to "replica" and not "logical"?
Q2: I thought we were going to finally change the pg_dump default to
"custom" format in this release? No?
Problems
--------
P1. On the publishing node, logical replication relies on the *implied*
correspondence of the application_name and the replication_slot both
being named the same as the publication in order to associate a
particular publication with a particular replication connection.
However, there's absolutely nothing preventing me from also creating a
binary replication connection by the same name It really seems like we
need a field in pg_stat_replication or pg_replication_slots which lists
the publication.
P2: If I create a subscription on a table with no primary key, I do not
recieve a warning. There should be a warning, since in most cases such
a subscription will not work. I suggest the text:
"logical replication target relation "public.fines" has no primary key.
Either create one, or set REPLICA IDENTITY index and set the published
relation to REPLICA IDENTITY FULL."
P3: apparently jsonb_to_tsvector with lang parameter isn't immutable?
This means that it can't be used for indexing:
libdata=# create index bookdata_fts on bookdata using gin ((
to_tsvector('english',bookdata)));
ERROR: functions in index expression must be marked IMMUTABLE
... and indeed it's not:
select proname, prosrc, proargtypes, provolatile from pg_proc where
proname = 'to_tsvector';
proname | prosrc | proargtypes | provolatile
-------------+------------------------+-------------+-------------
to_tsvector | jsonb_to_tsvector | 3802 | s
to_tsvector | to_tsvector_byid | 3734 25 | i
to_tsvector | to_tsvector | 25 | s
to_tsvector | json_to_tsvector | 114 | s
to_tsvector | jsonb_to_tsvector_byid | 3734 3802 | s
to_tsvector | json_to_tsvector_byid | 3734 114 | s
... can we fix that?
--
Josh Berkus
Containers & Databases Oh My!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jun 7, 2017 at 2:01 PM, Josh Berkus <josh@berkus.org> wrote:
Q1. Why does wal_level default to "replica" and not "logical"?
The difference of WAL generated is way higher between
archive->hot_standby than hot_standby->logical. And unlike replica,
logical decoding is not something that is widely spread in user's
deployments to justify changing to such a default. At least that's
what I recall on the matter.
Q2: I thought we were going to finally change the pg_dump default to
"custom" format in this release? No?
I don't recall any discussion on this matter, but my memory may fail me.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017-06-07 14:29:04 +0900, Michael Paquier wrote:
On Wed, Jun 7, 2017 at 2:01 PM, Josh Berkus <josh@berkus.org> wrote:
Q1. Why does wal_level default to "replica" and not "logical"?
The difference of WAL generated is way higher between
archive->hot_standby than hot_standby->logical. And unlike replica,
logical decoding is not something that is widely spread in user's
deployments to justify changing to such a default. At least that's
what I recall on the matter.
Right. I think what we really want there is some form of magic
switching to logical when a slot is present. Thats easy enough on the
master, a good bit harder when we allow decoding on standbys, which
Craig's working on.
Q2: I thought we were going to finally change the pg_dump default to
"custom" format in this release? No?I don't recall any discussion on this matter, but my memory may fail me.
Nothing here either.
- Andres
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 6/7/17 01:01, Josh Berkus wrote:
* Having defaults on the various _workers all devolve from max_workers
is also great.
I'm not aware of anything like that happening.
P1. On the publishing node, logical replication relies on the *implied*
correspondence of the application_name and the replication_slot both
being named the same as the publication in order to associate a
particular publication with a particular replication connection.
However, there's absolutely nothing preventing me from also creating a
binary replication connection by the same name It really seems like we
need a field in pg_stat_replication or pg_replication_slots which lists
the publication.
I'm not quite sure what you are getting at here. The application_name
seen on the publisher side is the subscription name. You can create a
binary replication connection using the same application_name, but
that's already been possible before. But the publications don't care
about any of this.
P2: If I create a subscription on a table with no primary key, I do not
recieve a warning. There should be a warning, since in most cases such
a subscription will not work. I suggest the text:"logical replication target relation "public.fines" has no primary key.
Either create one, or set REPLICA IDENTITY index and set the published
relation to REPLICA IDENTITY FULL."
At that point, we don't know what is being published. If only inserts
are being published or REPLICA IDENTITY FULL is set, then it will work.
We don't want to give warnings about things that might not be true.
More guidance on some of the potential failure cases would be good, but
it would need more refinement.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi,
On 07/06/17 07:01, Josh Berkus wrote:
Folks,
I've put together some demos on PostgreSQL 10beta1. Here's a few
feedback notes based on my experience with it.
[...snip...]Problems
--------P1. On the publishing node, logical replication relies on the *implied*
correspondence of the application_name and the replication_slot both
being named the same as the publication in order to associate a
particular publication with a particular replication connection.
However, there's absolutely nothing preventing me from also creating a
binary replication connection by the same name It really seems like we
need a field in pg_stat_replication or pg_replication_slots which lists
the publication.
What do you mean implied correspondence of application_name and the
replication_slot? We only use subscription_name as default value for
those when user does not specify something else, all three of those can
have different value if user sets it up that way. And there is no
correspondence whatsoever to names of publications. The upstream only
knows which publications to replicate because subscription gives list of
requested publications as option to START_REPLICATION walsender command.
The list of publications associated with a subscription are only stored
on the subscriber and publisher has no idea what those are.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Peter and Petr:
On 06/07/2017 05:24 PM, Peter Eisentraut wrote:
On 6/7/17 01:01, Josh Berkus wrote:
* Having defaults on the various _workers all devolve from max_workers
is also great.I'm not aware of anything like that happening.
P1. On the publishing node, logical replication relies on the *implied*
correspondence of the application_name and the replication_slot both
being named the same as the publication in order to associate a
particular publication with a particular replication connection.
However, there's absolutely nothing preventing me from also creating a
binary replication connection by the same name It really seems like we
need a field in pg_stat_replication or pg_replication_slots which lists
the publication.I'm not quite sure what you are getting at here. The application_name
seen on the publisher side is the subscription name. You can create a
binary replication connection using the same application_name, but
that's already been possible before. But the publications don't care
about any of this.
My point is that there is no system view where I can see, on the origin
node, what subscribers are subscribing to which publications. You can
kinda guess that from pg_stat_replication etc., but it's not dependable
information.
P2: If I create a subscription on a table with no primary key, I do not
recieve a warning. There should be a warning, since in most cases such
a subscription will not work. I suggest the text:"logical replication target relation "public.fines" has no primary key.
Either create one, or set REPLICA IDENTITY index and set the published
relation to REPLICA IDENTITY FULL."At that point, we don't know what is being published. If only inserts
are being published or REPLICA IDENTITY FULL is set, then it will work.
We don't want to give warnings about things that might not be true.More guidance on some of the potential failure cases would be good, but
it would need more refinement.
Hmmm, yah, I see. Let me explain why this is a UX issue as-is though:
1. User forgets to create a PK on the subscriber node.
2. User starts a subscription to the tables.
3. Subscription is successful.
4. First update hits the publisher node.
5. Subscription fails and disconnects.
The user's first thought is going to be a network issue, or a bug, or
some other problem, not a missing PK. Yeah, they can find that
information in the logs, but only if they think to look for it in the
first place, and in some environments (AWS, containers, etc.) logs can
be very hard to access.
We really need the subscription to fail at step (2), not wait for the
first update to fail. And if it doesn't fail at step 2, then we should
at least give a warning.
--
Josh Berkus
Containers & Databases Oh My!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08/06/17 03:19, Josh Berkus wrote:
Peter and Petr:
On 06/07/2017 05:24 PM, Peter Eisentraut wrote:
On 6/7/17 01:01, Josh Berkus wrote:
* Having defaults on the various _workers all devolve from max_workers
is also great.I'm not aware of anything like that happening.
P1. On the publishing node, logical replication relies on the *implied*
correspondence of the application_name and the replication_slot both
being named the same as the publication in order to associate a
particular publication with a particular replication connection.
However, there's absolutely nothing preventing me from also creating a
binary replication connection by the same name It really seems like we
need a field in pg_stat_replication or pg_replication_slots which lists
the publication.I'm not quite sure what you are getting at here. The application_name
seen on the publisher side is the subscription name. You can create a
binary replication connection using the same application_name, but
that's already been possible before. But the publications don't care
about any of this.My point is that there is no system view where I can see, on the origin
node, what subscribers are subscribing to which publications. You can
kinda guess that from pg_stat_replication etc., but it's not dependable
information.
That's like wanting the foreign server to show you which foreign tables
exist on the local server. This is not a tightly coupled system and you
are able to setup both sides without them being connected to each other
at the time of setup, so there is no way publisher can know anything.
P2: If I create a subscription on a table with no primary key, I do not
recieve a warning. There should be a warning, since in most cases such
a subscription will not work. I suggest the text:"logical replication target relation "public.fines" has no primary key.
Either create one, or set REPLICA IDENTITY index and set the published
relation to REPLICA IDENTITY FULL."At that point, we don't know what is being published. If only inserts
are being published or REPLICA IDENTITY FULL is set, then it will work.
We don't want to give warnings about things that might not be true.More guidance on some of the potential failure cases would be good, but
it would need more refinement.Hmmm, yah, I see. Let me explain why this is a UX issue as-is though:
1. User forgets to create a PK on the subscriber node.
2. User starts a subscription to the tables.
3. Subscription is successful.
4. First update hits the publisher node.
5. Subscription fails and disconnects.
The user's first thought is going to be a network issue, or a bug, or
some other problem, not a missing PK. Yeah, they can find that
information in the logs, but only if they think to look for it in the
first place, and in some environments (AWS, containers, etc.) logs can
be very hard to access.We really need the subscription to fail at step (2), not wait for the
first update to fail. And if it doesn't fail at step 2, then we should
at least give a warning.
Yes, I actually mentioned somewhere at some point that we should call
the checks we call during the replication also from the appropriate DDL
commands when possible (the information might not be available when the
DDL is executed), but never got to actually implementing it.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 6/7/17 21:19, Josh Berkus wrote:
The user's first thought is going to be a network issue, or a bug, or
some other problem, not a missing PK. Yeah, they can find that
information in the logs, but only if they think to look for it in the
first place, and in some environments (AWS, containers, etc.) logs can
be very hard to access.
You're not going to get very far with using this feature if you are not
looking in the logs for errors. These are asynchronously operating
background workers, so the only way they can communicate problems is
through the log.
I don't disagree with your general premise. We have done a fair amount
of fiddling already to show some errors as early as possible. But we
can't know all of them, and we shouldn't give the impression that we do.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 06/07/2017 06:25 PM, Petr Jelinek wrote:
On 08/06/17 03:19, Josh Berkus wrote:
Peter and Petr:
On 06/07/2017 05:24 PM, Peter Eisentraut wrote:
On 6/7/17 01:01, Josh Berkus wrote:
* Having defaults on the various _workers all devolve from max_workers
is also great.I'm not aware of anything like that happening.
P1. On the publishing node, logical replication relies on the *implied*
correspondence of the application_name and the replication_slot both
being named the same as the publication in order to associate a
particular publication with a particular replication connection.
However, there's absolutely nothing preventing me from also creating a
binary replication connection by the same name It really seems like we
need a field in pg_stat_replication or pg_replication_slots which lists
the publication.I'm not quite sure what you are getting at here. The application_name
seen on the publisher side is the subscription name. You can create a
binary replication connection using the same application_name, but
that's already been possible before. But the publications don't care
about any of this.My point is that there is no system view where I can see, on the origin
node, what subscribers are subscribing to which publications. You can
kinda guess that from pg_stat_replication etc., but it's not dependable
information.That's like wanting the foreign server to show you which foreign tables
exist on the local server. This is not a tightly coupled system and you
are able to setup both sides without them being connected to each other
at the time of setup, so there is no way publisher can know anything.
Why wouldn't the publisher know who's connected once the replication
connection as been made and the subscription has started? Or is it just
a log position, and the publisher really has no idea how many
publications are being consumed?
--
Josh Berkus
Containers & Databases Oh My!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08/06/17 03:50, Josh Berkus wrote:
On 06/07/2017 06:25 PM, Petr Jelinek wrote:
On 08/06/17 03:19, Josh Berkus wrote:
Peter and Petr:
On 06/07/2017 05:24 PM, Peter Eisentraut wrote:
On 6/7/17 01:01, Josh Berkus wrote:
* Having defaults on the various _workers all devolve from max_workers
is also great.I'm not aware of anything like that happening.
P1. On the publishing node, logical replication relies on the *implied*
correspondence of the application_name and the replication_slot both
being named the same as the publication in order to associate a
particular publication with a particular replication connection.
However, there's absolutely nothing preventing me from also creating a
binary replication connection by the same name It really seems like we
need a field in pg_stat_replication or pg_replication_slots which lists
the publication.I'm not quite sure what you are getting at here. The application_name
seen on the publisher side is the subscription name. You can create a
binary replication connection using the same application_name, but
that's already been possible before. But the publications don't care
about any of this.My point is that there is no system view where I can see, on the origin
node, what subscribers are subscribing to which publications. You can
kinda guess that from pg_stat_replication etc., but it's not dependable
information.That's like wanting the foreign server to show you which foreign tables
exist on the local server. This is not a tightly coupled system and you
are able to setup both sides without them being connected to each other
at the time of setup, so there is no way publisher can know anything.Why wouldn't the publisher know who's connected once the replication
connection as been made and the subscription has started? Or is it just
a log position, and the publisher really has no idea how many
publications are being consumed?
Plugin knows while the connection exists, but that's the thing, it goes
through pluggable interface (that can be used by other plugins, without
publications) so there would have to be some abstracted way for plugins
to give some extra information for the pg_stat_replication or similar
view. I am afraid it's bit too late to design something like that in
PG10 cycle.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 06/07/2017 07:01 PM, Petr Jelinek wrote:
On 08/06/17 03:50, Josh Berkus wrote:
On 06/07/2017 06:25 PM, Petr Jelinek wrote:
On 08/06/17 03:19, Josh Berkus wrote:
Peter and Petr:
On 06/07/2017 05:24 PM, Peter Eisentraut wrote:
On 6/7/17 01:01, Josh Berkus wrote:
* Having defaults on the various _workers all devolve from max_workers
is also great.I'm not aware of anything like that happening.
P1. On the publishing node, logical replication relies on the *implied*
correspondence of the application_name and the replication_slot both
being named the same as the publication in order to associate a
particular publication with a particular replication connection.
However, there's absolutely nothing preventing me from also creating a
binary replication connection by the same name It really seems like we
need a field in pg_stat_replication or pg_replication_slots which lists
the publication.I'm not quite sure what you are getting at here. The application_name
seen on the publisher side is the subscription name. You can create a
binary replication connection using the same application_name, but
that's already been possible before. But the publications don't care
about any of this.My point is that there is no system view where I can see, on the origin
node, what subscribers are subscribing to which publications. You can
kinda guess that from pg_stat_replication etc., but it's not dependable
information.That's like wanting the foreign server to show you which foreign tables
exist on the local server. This is not a tightly coupled system and you
are able to setup both sides without them being connected to each other
at the time of setup, so there is no way publisher can know anything.Why wouldn't the publisher know who's connected once the replication
connection as been made and the subscription has started? Or is it just
a log position, and the publisher really has no idea how many
publications are being consumed?Plugin knows while the connection exists, but that's the thing, it goes
through pluggable interface (that can be used by other plugins, without
publications) so there would have to be some abstracted way for plugins
to give some extra information for the pg_stat_replication or similar
view. I am afraid it's bit too late to design something like that in
PG10 cycle.
OK, consider it a feature request for PG11, then.
--
Josh Berkus
Containers & Databases Oh My!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 06/07/2017 06:37 PM, Peter Eisentraut wrote:
On 6/7/17 21:19, Josh Berkus wrote:
The user's first thought is going to be a network issue, or a bug, or
some other problem, not a missing PK. Yeah, they can find that
information in the logs, but only if they think to look for it in the
first place, and in some environments (AWS, containers, etc.) logs can
be very hard to access.You're not going to get very far with using this feature if you are not
looking in the logs for errors. These are asynchronously operating
background workers, so the only way they can communicate problems is
through the log.
Well, we *could* provide a system view, as we now do for archiving, and
for the same reasons.
The issue isn't that the error detail is in the log. It's somehow
letting the user know that they need to look at the log, as opposed to
somewhere else. Consider that this is asynchonous for the user as well;
they are likely to find out about the broken replication well after it
happens, and thus have a lot of log to search through.
Activity logs are a *terrible* UI for debugging systems problems. I
realize that there is information it's hard for us to provide any other
way. But the logs should be our "monitoring of last resort", where we
put stuff after we've run out of ideas on where else to put it, because
they are the hardest thing to access for a user.
--
Josh Berkus
Containers & Databases Oh My!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 6/8/17 13:15, Josh Berkus wrote:
Well, we *could* provide a system view, as we now do for archiving, and
for the same reasons.
Which view are you referring to here?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 7 June 2017 at 01:01, Josh Berkus <josh@berkus.org> wrote:
P3: apparently jsonb_to_tsvector with lang parameter isn't immutable?
This means that it can't be used for indexing:libdata=# create index bookdata_fts on bookdata using gin ((
to_tsvector('english',bookdata)));
ERROR: functions in index expression must be marked IMMUTABLE
I don't have a machine handy to check on but isn't this a strange
thing to do? Isn't there a GIN opclass on jsonb itself which would be
the default if you didn't have that to_tsvector() call -- and which
would also work properly with the jsonb operators?
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 06/09/2017 07:54 PM, Greg Stark wrote:
On 7 June 2017 at 01:01, Josh Berkus <josh@berkus.org> wrote:
P3: apparently jsonb_to_tsvector with lang parameter isn't immutable?
This means that it can't be used for indexing:libdata=# create index bookdata_fts on bookdata using gin ((
to_tsvector('english',bookdata)));
ERROR: functions in index expression must be marked IMMUTABLEI don't have a machine handy to check on but isn't this a strange
thing to do? Isn't there a GIN opclass on jsonb itself which would be
the default if you didn't have that to_tsvector() call -- and which
would also work properly with the jsonb operators?
The above is the documented way to create an FTS index on a JSONB field.
--
Josh Berkus
Containers & Databases Oh My!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers