Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m13kj-0006yI-G0 for pgsql-bugs@arkaria.postgresql.org; Wed, 07 Jul 2021 09:21:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m13ki-0002Dq-8R for pgsql-bugs@arkaria.postgresql.org; Wed, 07 Jul 2021 09:21:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m0vVj-0001NQ-85 for pgsql-bugs@lists.postgresql.org; Wed, 07 Jul 2021 00:33:55 +0000 Received: from mail-ej1-x630.google.com ([2a00:1450:4864:20::630]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1m0vVg-0004Fy-Ao for pgsql-bugs@lists.postgresql.org; Wed, 07 Jul 2021 00:33:54 +0000 Received: by mail-ej1-x630.google.com with SMTP id i20so456070ejw.4 for ; Tue, 06 Jul 2021 17:33:52 -0700 (PDT) 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=NG2H7P+ZyqkG1O85wNVyv2cfHihXBsH3Jup4Y/MEPOI=; b=nwF/5jMRX8Xx7Qq+8+3yA+pIqXXrBJzmjFKKp5Sm0prxudij0rwrvCc8Z/vdMbVtH4 ytICj84yZXG1eBNqJ7v3hy+N1T6a0u7Ap9cG0xWdRt43UFuLmZgLuAc+6/9ZqicqTaZ9 M2CgIsUKG4XJLEHqcVVi5wmmzvA4OOkXaGU7IhCo+/P4hng/au19t2d/Cc5cr83RuzWk Fl/RCJD76Hd9wK3gh/gfh9SHp89fexqmFeq0L9EuMAvPz3MFKtvNNKG4LZs3745TIFzV iCs6AXqF7DnsUBqqaQXC/A1jmrdgAG+lCj9UyzmC2SrcTZm1CfGamX6+crz4N/499+49 7SeA== 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=NG2H7P+ZyqkG1O85wNVyv2cfHihXBsH3Jup4Y/MEPOI=; b=I88pHmKcGyrSpaSMZS1NxjsZ9f9l/o4iLxgjlmNuLcSOC/fCd/IGPGj5reHWOHuCQZ LfNeLjir82XOyCBeHtckQ6txCsas7E7CBR8aSQLxzcijDO98jxn/FBC48h9FyqgDLxLA YIivv24bHv/96oVryc0zATzh/kKRgvLqCag1OcHq8Z2oJFS5XZWbalxGjNs/+ES13jPq +sutVG8mSzFU515f2qLrsvCyqD4ZK6PTkKGBjmG7ngauMCunE+Ooq/YFo1iRGopW1Bw/ mEJA2juvmgL8ikgDJd5LFr4GwSoMrQxWdMA6a0OzfCUTo/EiwegvmyWcLODK/VJ6UvMA PXow== X-Gm-Message-State: AOAM5302GiUL6F6N4sW66mUWDmh0KTsV95G8BsNSZBRBYEKrb8Np85gs kJbGFNUEItt9a8QL0yT3CecPrTyRan0WkA== X-Google-Smtp-Source: ABdhPJw2E+TRqfEpTyra+3/D8ebOqF12Ix3tkEzCpdKSn6QsK0pxSgoCaeV61n3Z+Er18iTpjXQDbQ== X-Received: by 2002:a17:906:35d4:: with SMTP id p20mr1646220ejb.212.1625618030499; Tue, 06 Jul 2021 17:33:50 -0700 (PDT) Received: from [10.137.0.17] ([87.71.212.44]) by smtp.gmail.com with ESMTPSA id z10sm7853890edd.11.2021.07.06.17.33.49 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 06 Jul 2021 17:33:50 -0700 (PDT) Subject: Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION To: "David G. Johnston" , pgsql-bugs@lists.postgresql.org References: <17085-26d62a05c4c37991@postgresql.org> From: Victor Porton Message-ID: Date: Wed, 7 Jul 2021 03:33:49 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.11.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------BB6A4F1991E42F01C102716C" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------BB6A4F1991E42F01C102716C Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 7/7/21 2:32 AM, David G. Johnston wrote: > On Tue, Jul 6, 2021 at 3:29 PM PG Bug reporting form > > wrote: > > The following bug has been logged on the website: > > Bug reference:      17085 > Logged by:          Victor Porton > Email address: porton@narod.ru > PostgreSQL version: 13.3 > Operating system:   Linux > Description: > > When there is either ON DELETE NO ACTION or ON UPDATE NO ACTION for a > foreign key, referential integrity is not preserved anyway. > > > Yes, it is preserved, the delete or update is prevented. > > > Thereforein this case ALTER TABLE should  be able to create an index > > without referential checking. > > > Indexes are single table only and thus do not care about referential > integrity or perform any referential checking. That's a typo, I meant foreign keys, not indexes. > > > It is especially useful for removing superfluous data like: > > delete from tags using transactions where not exists(select * from > transactions where tags.tx_id=transactions.id > ); > > I've got into a trouble: This command runs too long because of no > foreign > key, but I can't create a foreign key because this command didn't > run yet to > make referntial integrity working. > > > Foreign keys likewise don't affect performance...they are strictly > concerned with data updates.  Now, a FK column is not indexed by > default so depending on how queries are written, and the data in for > the FK, there may be a performance gain from adding an index. > > All that is to say if you want to turn a non-FK column into an FK you > may find it helpful to add the index first, fix the data, then add the FK. Anyway, after following my advice PostgreSQL would become more "logical" and more convenient for users. There is no reason to have different rules for ALTER TABLE and for DELETE/UPDATE. --------------BB6A4F1991E42F01C102716C Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 7/7/21 2:32 AM, David G. Johnston wrote:
On Tue, Jul 6, 2021 at 3:29 PM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      17085
Logged by:          Victor Porton
Email address:      porton@narod.ru
PostgreSQL version: 13.3
Operating system:   Linux
Description:       

When there is either ON DELETE NO ACTION or ON UPDATE NO ACTION for a
foreign key, referential integrity is not preserved anyway.

Yes, it is preserved, the delete or update is prevented.


Therefore in this case ALTER TABLE should  be able to create an index
without referential checking.

Indexes are single table only and thus do not care about referential integrity or perform any referential checking.
That's a typo, I meant foreign keys, not indexes.

It is especially useful for removing superfluous data like:

delete from tags using transactions where not exists(select * from
transactions where tags.tx_id=transactions.id);

I've got into a trouble: This command runs too long because of no foreign
key, but I can't create a foreign key because this command didn't run yet to
make referntial integrity working.


Foreign keys likewise don't affect performance...they are strictly concerned with data updates.  Now, a FK column is not indexed by default so depending on how queries are written, and the data in for the FK, there may be a performance gain from adding an index.

All that is to say if you want to turn a non-FK column into an FK you may find it helpful to add the index first, fix the data, then add the FK.

Anyway, after following my advice PostgreSQL would become more "logical" and more convenient for users.

There is no reason to have different rules for ALTER TABLE and for DELETE/UPDATE.

--------------BB6A4F1991E42F01C102716C--