Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gj4oE-0004nd-9i for pgsql-admin@arkaria.postgresql.org; Mon, 14 Jan 2019 16:09:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gj4nF-00033l-27 for pgsql-admin@arkaria.postgresql.org; Mon, 14 Jan 2019 16:08:53 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gj4nE-00033X-LT for pgsql-admin@lists.postgresql.org; Mon, 14 Jan 2019 16:08:52 +0000 Received: from mail-oi1-x243.google.com ([2607:f8b0:4864:20::243]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gj4nA-0005nC-D1 for pgsql-admin@lists.postgresql.org; Mon, 14 Jan 2019 16:08:51 +0000 Received: by mail-oi1-x243.google.com with SMTP id v6so18281354oif.2 for ; Mon, 14 Jan 2019 08:08:47 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=GqVR5eYSAFLgy5zB1erSu0iC2Kb8mNou6LEPfRnuCN4=; b=u8eq99qlKXZmBzdsOa/8jtOxkbuHSKmqMM5A+2hTIPo44DhZUv7ezfFNXos6IjGz5M akamiKgwGBlc6nNIF3IA64o5oFAgYaby1s2VCO7jQmwfBb/FHl97ycA4kwY2VsGSE1wr g0xnK9UMZ3EMNOkcyRtAHUjGvRjgGKlMMl1hR1rl1+LoUSPJ5Sfa1teABmhx3JVnug4v PGlMRGbNLcchiZwnP8C4P769dvA25RJY6Evg7Q5dcb0NYzYmE/HCnyzAJwIyfRt2aWQF IHSV6cdvLODqIprKSJgOruMbPgxjyAZv0mwPonEl1HEeV2G/jmTeJO4u985F8a4Rl5pg qWQg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=GqVR5eYSAFLgy5zB1erSu0iC2Kb8mNou6LEPfRnuCN4=; b=XhMJ3UtTV0dZyj5Shi2my5ZWI/BuwLOiXwSVAXFzQGgEY8TpPfgSTdZKiJa10i5nlL 5sQ1gk7p5/kvjOrj0av/xO9oPzKKc4KXPOIHMl6RLaBHCbk9jbUBJbfdmgwExw//2iBa 9nKBPjomj2UlOibepQ8h1UiI9fG0eUtOyntt7Yiu7CJBtWDGCAPcC/Wp4MQEVCfz7D2t WclZhOiJTvy7AtEKwzIrAm6Ng5p3H9K38mmyfAC/LCMkAA0J16nej/xgOG1lugoWzQl7 QbdtjLcL0AXwMiD0yabYJwmWIMAVqeiaCrMZyBQ6Rk6uObbu9FuIERUoRGy+nzLJKzoa 5XOQ== X-Gm-Message-State: AJcUukfiTpU+eaonD1pUGXfccF77+4lUuqyXlnTPcZO+FXwz4NxmsLY8 fmBHcrkvtH3R8z0DAudAhReT2cIR X-Google-Smtp-Source: ALg8bN7p2aZcMbjIiT8IqZTmFD5k0+h3FTXhumtuVGTrhG3TqlS9XD1djexG06l0xUKorlTqO3ysJg== X-Received: by 2002:aca:2ecf:: with SMTP id u198mr16920339oiu.338.1547482126934; Mon, 14 Jan 2019 08:08:46 -0800 (PST) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id g138sm365201oib.26.2019.01.14.08.08.45 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 14 Jan 2019 08:08:46 -0800 (PST) Subject: Re: Delete Enhancement Request To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: <3f7591ea-9747-1ac6-4299-13fe66216ef2@gmail.com> Date: Mon, 14 Jan 2019 10:08:45 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------428F0DFAC4C1CCFED1E6E2A4" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------428F0DFAC4C1CCFED1E6E2A4 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 1/14/19 9:18 AM, Campbell, Lance wrote: > > Please consider adding this feature to PostgreSQL. > > Use Case: > > When deleting a large number of records with constraints and triggers > PostgreSQL is exceptionally slow. > > Suggested change: > > I believe a way to speed this processes up would be to offer an option a > user could “turn on” prior to doing the delete. > > Example: set delete-no-roll-back=true > > Now as PostgreSQL deletes rows it literally deletes them one at a time as > though the user was only deleting a single row. This means that if cancel > were executed on a delete action then it would only roll back the current > row that PostgreSQL was in the process of deleting.  Example:  if you had > 10 million row to delete and then pressed cancel after three minutes maybe > 5 million are deleted. > You seem to be asking for an unlogged transaction, but that doesn't have anything to do with efficiently deleting rows. -- Angular momentum makes the world go 'round. --------------428F0DFAC4C1CCFED1E6E2A4 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 1/14/19 9:18 AM, Campbell, Lance wrote:

Please consider adding this feature to PostgreSQL.

 

Use Case:

When deleting a large number of records with constraints and triggers PostgreSQL is exceptionally slow.

 

Suggested change:

I believe a way to speed this processes up would be to offer an option a user could “turn on” prior to doing the delete. 

 

Example:  set delete-no-roll-back=true

 

Now as PostgreSQL deletes rows it literally deletes them one at a time as though the user was only deleting a single row.  This means that if cancel were executed on a delete action then it would only roll back the current row that PostgreSQL was in the process of deleting.  Example:  if you had 10 million row to delete and then pressed cancel after three minutes maybe 5 million are deleted.


You seem to be asking for an unlogged transaction, but that doesn't have anything to do with efficiently deleting rows.


--
Angular momentum makes the world go 'round.
--------------428F0DFAC4C1CCFED1E6E2A4--