From: Victor Porton <porton.victor@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
Date: Wed, 7 Jul 2021 03:33:49 +0300
Message-ID: <f62de8d8-1c2d-04c4-9a9e-7c7119441651@gmail.com> (raw)
In-Reply-To: <CAKFQuwbiU1kswK7fkKf9k=qT-_hTwhbNyWOoy-s_1j55cx=Z7w@mail.gmail.com>
References: <17085-26d62a05c4c37991@postgresql.org>
<CAKFQuwbiU1kswK7fkKf9k=qT-_hTwhbNyWOoy-s_1j55cx=Z7w@mail.gmail.com>
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 <mailto: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 <mailto: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
> <http://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.
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-bugs@postgresql.org
Cc: porton.victor@gmail.com, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
In-Reply-To: <f62de8d8-1c2d-04c4-9a9e-7c7119441651@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox