pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
6+ messages / 4 participants
[nested] [flat]
* BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
@ 2021-07-06 21:03 PG Bug reporting form <noreply@postgresql.org>
2021-07-06 23:32 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2021-07-06 21:03 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: porton@narod.ru
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.
Therefore in this case ALTER TABLE should be able to create an index
without referential checking.
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
2021-07-06 21:03 BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION PG Bug reporting form <noreply@postgresql.org>
@ 2021-07-06 23:32 ` David G. Johnston <david.g.johnston@gmail.com>
2021-07-07 00:04 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION Pantelis Theodosiou <ypercube@gmail.com>
2021-07-07 00:33 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION Victor Porton <porton.victor@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: David G. Johnston @ 2021-07-06 23:32 UTC (permalink / raw)
To: porton@narod.ru, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
>
> 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.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
2021-07-06 21:03 BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION PG Bug reporting form <noreply@postgresql.org>
2021-07-06 23:32 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION David G. Johnston <david.g.johnston@gmail.com>
@ 2021-07-07 00:04 ` Pantelis Theodosiou <ypercube@gmail.com>
2021-07-07 00:09 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Pantelis Theodosiou @ 2021-07-07 00:04 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: porton@narod.ru, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Jul 7, 2021 at 12:32 AM David G. Johnston <
david.g.johnston@gmail.com> 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.
>
>>
>> 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.
>
> David J.
>
Besides all the good advice by David J., your delete statement has some
issues because it's written more complex than it could.
Transactions is referenced twice, once without correlation and secondly
with correlation.
As a result, if transactions has 0 rows, the statement would delete no rows
at all from tags (although I suppose you'd want to delete them all).
And if transactions is big enough, you may not get an efficient plan.
I suggest you try using this rewrite (besides adding the suggested index):
delete from tags
where not exists (select * from
transactions where tags.tx_id=transactions.id);
Pantelis
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
2021-07-06 21:03 BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION PG Bug reporting form <noreply@postgresql.org>
2021-07-06 23:32 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION David G. Johnston <david.g.johnston@gmail.com>
2021-07-07 00:04 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION Pantelis Theodosiou <ypercube@gmail.com>
@ 2021-07-07 00:09 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2021-07-07 00:09 UTC (permalink / raw)
To: Pantelis Theodosiou <ypercube@gmail.com>; +Cc: porton@narod.ru, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Jul 6, 2021 at 5:05 PM Pantelis Theodosiou <ypercube@gmail.com>
wrote:
>
>
Transactions is referenced twice, once without correlation and secondly
> with correlation.
>
Yeah, the USING clause of DELETE is basically an INNER JOIN. If you need
an ANTI-JOIN you can write that via NOT EXISTS just like you would in a
normal SELECT query. You don't get the option to write an anti-join using
a left join and testing for null.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
2021-07-06 21:03 BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION PG Bug reporting form <noreply@postgresql.org>
2021-07-06 23:32 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION David G. Johnston <david.g.johnston@gmail.com>
@ 2021-07-07 00:33 ` Victor Porton <porton.victor@gmail.com>
2021-07-07 00:43 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Victor Porton @ 2021-07-07 00:33 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
2021-07-06 21:03 BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION PG Bug reporting form <noreply@postgresql.org>
2021-07-06 23:32 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION David G. Johnston <david.g.johnston@gmail.com>
2021-07-07 00:33 ` Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION Victor Porton <porton.victor@gmail.com>
@ 2021-07-07 00:43 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2021-07-07 00:43 UTC (permalink / raw)
To: Victor Porton <porton.victor@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Jul 6, 2021 at 5:33 PM Victor Porton <porton.victor@gmail.com>
wrote:
>
> That's a typo, I meant foreign keys, not indexes.
>
OK, then add the "NOT VALID" qualifier to your ALTER TABLE command. But
you will have to perform validation at some point...
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.
>
It's hard to follow the advice contained in an email that doesn't seem to
understand how things work today.
The subject of the report - which isn't a bug by the way - is to skip
referential checking when adding an index. PostgreSQL doesn't do that
today...
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-07-07 00:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-06 21:03 BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION PG Bug reporting form <noreply@postgresql.org>
2021-07-06 23:32 ` David G. Johnston <david.g.johnston@gmail.com>
2021-07-07 00:04 ` Pantelis Theodosiou <ypercube@gmail.com>
2021-07-07 00:09 ` David G. Johnston <david.g.johnston@gmail.com>
2021-07-07 00:33 ` Victor Porton <porton.victor@gmail.com>
2021-07-07 00:43 ` David G. Johnston <david.g.johnston@gmail.com>
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