Seeing that patch development is coming along at a nice pace, it seems a
good time to discuss this question.
On 1/4/18 03:24, Simon Riggs wrote:
> At present the patch treats TRUNCATE as if it were a DELETE
>
> ...
>
> CREATE PUBLICATION insert_only FOR TABLE mydata;
> will now publish TRUNCATEs, although they were ignored in PG10
> so PG10 publications will act differently
>
> I had regarded it as a missing piece, but some may view that is a
> behaviour change in PG11
My understanding of this design is that there's no possible way to make
a PG11 database behave like a PG10 database did. For example, if someone
has a data warehouse with a single table that's subscribed to publishers
on multiple source databases, they would certainly not want truncate SQL
replicated. If they just upgrade their database without reading all the
release notes (not that anyone would ever do that), they might get a
surprise data loss in the warehouse. Of course I wouldn't suggest using
truncate on their sources - but people may still do it.
> Alternatively, we could also use WITH (publish = 'truncate') as a
> separate decision.
>
> That is an easy change if we wish it.
Of course the user could simply not _allow_ truncate commands on the
source tables via permissions and/or triggers; that seems a little more
"correct" to me from a DB design perspective. But I still like Simon's
idea here of using WITH as a separate decision. I'm sure that there will
be users somewhere who build systems based on our PG10 design - this at
least doesn't completely leave them out to dry, and I don't see any big
downsides to having the separate decision for truncate.
In addition, this seems a little more consistent. In other places that
comes to mind (e.g. triggers and privileges), truncate is treated
distinctly from delete. Makes sense to me to continue that convention.
-Jeremy
--
Jeremy Schneider
Database Engineer
Amazon Web Services
+1 312-725-9249 (m)
schnjere@amazon.com