Re: A Modest Upgrade Proposal

On 8 July 2016 at 02:41, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> > Personally, I'm in the group of people that don't see the need for DDL.
> > There are already many successful features that don't utilize DDL, such
> as
> > backup, advisory locks and some features that use DDL that don't really
> need
> > to such as LISTEN/NOTIFY, full text search etc.. Also note that both
> Oracle
> > and SQLServer have moved away from DDL in favour of function APIs, most
> > NoSQL databases and almost all languages prefer functional interfaces
> over
> > parsed text languages, so I don't see a huge industry revival for DDL as
> > means of specifying things.
>
> DDL is our standard way of getting things into the system catalogs.
> We have no system catalog metadata that is intended to be populated by
> any means other than DDL. If you want to add a column to a table, you
> say ALTER TABLE .. ADD COLUMN. If you want to add a column to an
> extension, you say ALTER EXTENSION .. ADD TABLE. If you want to add
> an option to a foreign table, you say ALTER FOREIGN TABLE .. OPTIONS
> (ADD ..). Therefore, I think it is entirely reasonable and obviously
> consistent with existing practice that if you want to add a table to a
> replication set, you should write ALTER REPLICATION SET .. ADD TABLE.
> I don't understand why logical replication should be the one feature
> that departs from the way that all of our other features work. Sure,
> we have other features that do not involve DDL, but (1) one of your
> examples is full text search, which of course does have DDL, and was
> moved from an interface that did not involve DDL to one that did
> because the latter is better and (2) your other examples don't involve
> defining catalog contents, which makes them apples-to-oranges
> comparisons.
>

pg_am has existed for decades without supporting DDL and we have gone to
great lengths over many years to allow catalog tables to be
inserted/updated/deleted by normal SQL rather than DDL, so not all catalog
access is via DDL. One of my examples was full text search and it does have
DDL, but that was an anti-example; all the feedback I have is that it was
much easier to use before it had DDL and that forcing it to use DDL pretty
much killed it for most users.

Anyway, backups and replication slots don't use DDL because they need to
work on standbys. So if you are arguing in favour of forcing logical
replication to never work on standbys, I'm interested in why that
restriction is useful and sensible, especially since we already agreed that
a failover mechanism for use of logical replication on standbys was
desirable. It seems likely that we're discussing this at too high a level
and that we each see things the other does not.

> The burden of proof isn't on me to demonstrate why this feature "needs
> DDL"; it's on you to explain why replication-related operations that
> establish persistent database state don't need to behave just like all
> other commands. Really, where this jumped the shark for me is when
> you argued that this stuff didn't even need pg_dump support. Come on.
> This feature doesn't get a pass from handling all of the things that
> every existing similar feature needs to deal with.

As I already said, I accept that there needs to be some way to backup
replication config; the meeting continued after you left.

I note also that replication slots aren't backed up by pg_dump; I see
analogy here and think that at least some parts of logical replication will
be similar and not require DDL at all, just as slots do not.

pg_dump support doesn't require DDL, in any case, nor is it certain yet
that pg_dump is the right utility for backup.

The main point I see is that the user interface mechanisms have very little
to do with DDL or not. Having a command called ALTER REPLICATION SLOT or a
function called pg_alter_replication_slot() makes little real difference to
a user.

We have much to discuss in terms of security, the way it should work and
what options to support and a sidetrack into syntax isn't warranted at this
early stage. Please lets discuss those important things first, then return
to whether DDL makes sense or not; it may do, or may not, or more likely
which parts of it need DDL and which do not.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services