public inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedFrom: Hannu Krosing <hannuk@google.com>
To: Andres Freund <andres@anarazel.de>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Vitaly Davydov <v.davydov@postgrespro.ru>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Support logical replication of DDLs, take2
Date: Thu, 30 Apr 2026 16:02:56 +0200
Message-ID: <CAMT0RQS-Tr9kuP6ChJaL7UjzY1gbtnAqOuPHZesKVpSkwHJiVw@mail.gmail.com> (raw)
In-Reply-To: <2oh4o4zvj2jreituesxgglmnseo2m2brffms3lvao6mseemto6@32v67ejhxlht>
References: <CAD21AoCzjzr5VqVkpVWkN7V6vrFhac=yXSNMz6rkSE+KOPPU0w@mail.gmail.com>
<CAExHW5ufb1CAcEP1o03M3c=q6AgCzdSLOFaxbC-dJN-k7ymqaA@mail.gmail.com>
<38690b0e-f91b-46fa-b72a-57775612e463@postgrespro.ru>
<CAD21AoCTdgHGcEQLwbqXR1E6aC1VyO8wOB4S4U6nU50bZQ+NzQ@mail.gmail.com>
<CAD21AoCzT3sytVbimRNdjRF=N3R-8ddaWKW95EzsdderXqcm4g@mail.gmail.com>
<CAFiTN-t1d3vzTMy4X=R_9m2AOmyVKpYQWKfZnOrat2voBi-K9g@mail.gmail.com>
<CAA4eK1LoUyyPGZExMRuhQ2g=_PvpE-NQra-dcE1OF=WeWnURZQ@mail.gmail.com>
<CAMT0RQQQ5DAE74GSFnEAtEOGN3tXcXYt==fR1gPZ+PGZD+waPg@mail.gmail.com>
<CAFiTN-vG4z03ar5PMYT3ehe+yzKH8+nPR4uqhNEhnax0DmOq_g@mail.gmail.com>
<CAMT0RQThNce3+0MEzbqNWov7uZZQbiyFrhrod3g+MeKoAqjRbw@mail.gmail.com>
<2oh4o4zvj2jreituesxgglmnseo2m2brffms3lvao6mseemto6@32v67ejhxlht>
On Wed, Apr 29, 2026 at 2:10 PM Andres Freund <andres@anarazel.de> wrote:
>
> Hi,
>
> On 2026-04-29 10:07:04 +0200, Hannu Krosing wrote:
> > On Wed, Apr 29, 2026 at 5:39 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > > I am trying to understand your idea. If we are trying to deparse from
> > > an actual system table using a snapshot, why don't we just use the
> > > WAL? I mean, the WAL should contain the actual catalog modifications
> > > it has made.
> >
> > We have the full data in the catalog and we would likely need catalog
> > queries for any change, even when de-parsing the tree.
> >
> > And we should not add the extra load on the original DDL side, just as
> > we don't for DML.
>
> That can't be a relevant cost compared to everything else.
Probably not. But unless we somehow encode "everything" at that point
we will make building different DDL decoders harder down the line.
So why not just save the normally serialised parse tree at this point
and let the decoders decide to do whatever they need.
> > At most we could just serialize the statement tree into the WAL,
> > though even that may be an overkill if we can get the change from
> > existing records.
> >
> > - insert new row in pg_class --> extract the CREATE TABLE (or INDEX, or ...)
> > - update row in pg_class or insert, update or delete a row in
> > pg_attribute --> extract ALTER TABLE
> > - except when it just updates relfilenod --> extract TRUNCATE
> > - delete row in pg_class --> DROP TABLE
> > - dml on pg_constraint --> ALTER TABLE
> >
> > ... etc
>
> That doesn't work in the general case, think of
> ALTER TABLE ... ALTER COLUMN ... TYPE foo USING (...)
>
> There's a big difference between USING(foo::int8) and USING (pg_size_bytes(foo))
> but it's nowhere visible in the WAL.
It can't be a big difference if it is not visible in the WAL.
Currently, we do treat DML exactly this way (or arguably worse).
In the following all the updates are decoded exactly the same
CREATE TABLE t(id int primary key, data text);
INSERT INTO t VALUES(1, 'one');
UPDATE t SET data='one' where id=1;
UPDATE t SET id=id;
UPDATE t SET id=10-9;
UPDATE t SET data='one';
ALL of the above get decoded as "UPDATE t SET data='one' where id=1;"
That is, we do not care how the values got there, as long as the end
result is the same.
And we do not track which fields were actually changed
The only reasons I see why we could not do the same for DDL are
1. it would be significantly more expensive to do so
or
2. we plan to fix some of that for DML as well and to start tracking
more of the intent in DML by extracting that from the statement trees.
--
Hannu
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: hannuk@google.com, andres@anarazel.de, dilipbalaut@gmail.com, amit.kapila16@gmail.com, sawada.mshk@gmail.com, v.davydov@postgrespro.ru, ashutosh.bapat.oss@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Support logical replication of DDLs, take2
In-Reply-To: <CAMT0RQS-Tr9kuP6ChJaL7UjzY1gbtnAqOuPHZesKVpSkwHJiVw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox