Subject: Re: logical replication syntax (was DROP SUBSCRIPTION,
 query cancellations and slot handling)
To: Tom Lane <tgl@sss.pgh.pa.us>
References: 
 <CA+TgmoZmkbpAWRzVKDVcHnTBkYjJEFS8=09RL-G3zgdozCLFHQ@mail.gmail.com>
 <27478.1493727233@sss.pgh.pa.us> <29431.1493730652@sss.pgh.pa.us>
 <360a1932-7af4-609b-94f1-2ad419430869@2ndquadrant.com>
 <9174dc31-787a-a2f6-818f-cb4a5c1dfddb@2ndquadrant.com>
 <30166.1493731904@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>,
 Michael Paquier <michael.paquier@gmail.com>,
 PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
From: Petr Jelinek <petr.jelinek@2ndquadrant.com>
Message-ID: <e39ca2a7-75b9-398c-eb92-a80a566f151b@2ndquadrant.com>
Date: Tue, 2 May 2017 16:14:23 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
In-Reply-To: <30166.1493731904@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 02/05/17 15:31, Tom Lane wrote:
> Petr Jelinek <petr.jelinek@2ndquadrant.com> writes:
>> Let me expand, if we don't drop the slot by default when dropping
>> subscription, we'll have a lot of users with dead slots laying around
>> holding back WAL/catalog_xmin, that's really bad. If we do drop by
>> default like now, we need option to not do that, neither RESTRICT, nor
>> CASCADE fit that.
> 
> I'm not sure which part of "you can't have an option in DROP" isn't
> clear to you.  Whatever the default behavior is always has to work,
> because that is what's going to happen during DROP OWNED BY, or
> DROP DATABASE. 

You are saying it like there was some guarantee that those commands
can't fail because of other objects. AFAIK for example drop database can
trivially fail just because there is replication slot in it before PG10
(IIRC Craig managed to fix that in 10 for unrelated reasons).


> If you want more than one behavior during DROP,
> you need to drive that off something represented as a persistent
> property of the object, not off an option in the DROP command.
> 

I don't see how changing behavior as object property will change
anything in terms of not failing to cascade. If you use CREATE or ALTER
to say that subscription must drop slot and that fails then the cascade
will still fail so then you need to run ALTER again to change that
property. I fail to see how that's easier than running the DROP directly.

So the only way to fulfill the requirement you stated is to just not try
to drop the slot, ever, on DROP SUBSCRIPTION. That makes the default
behavior leave resources on upstream that will eventually cause that
server to stop unless user notices before. I think we better invent
something that limits how much inactive slots can hold back WAL and
catalog_xmin in this release as well then.

We should also not create the slot (at least by default) on CREATE
SUBSCRIPTION to have some symmetry.

-- 
  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