Re: [PATCH] Erase the distinctClause if the result is unique by definition

On Tue, Feb 11, 2020 at 12:22 AM Ashutosh Bapat <
ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:

>
>
>>
>> [PATCH] Erase the distinctClause if the result is unique by
>> definition
>>
>
> I forgot to mention this in the last round of comments. Your patch was
> actually removing distictClause from the Query structure. Please avoid
> doing that. If you remove it, you are also removing the evidence that this
> Query had a DISTINCT clause in it.
>

Yes, I removed it because it is the easiest way to do it. what is the
purpose of keeping the evidence?

>
>
>>
>>
>> However the patch as presented has some problems
>> 1. What happens if the primary key constraint or NOT NULL constraint gets
>> dropped between a prepare and execute? The plan will no more be valid and
>> thus execution may produce non-distinct results.
>>
>> Will this still be an issue if user use doesn't use a "read uncommitted"
>> isolation level? I suppose it should be ok for this case. But even
>> though
>> I should add an isolation level check for this. Just added that in the
>> patch
>> to continue discussing of this issue.
>>
>
> In PostgreSQL there's no "read uncommitted".
>

Thanks for the hint, I just noticed read uncommitted is treated as read
committed
in Postgresql.

> But that doesn't matter since a query can be prepared outside a
> transaction and executed within one or more subsequent transactions.
>

Suppose after a DDL, the prepared statement need to be re-parsed/planned
if it is not executed or it will prevent the DDL to happen.

The following is my test.

postgres=# create table t (a int primary key, b int not null, c int);
CREATE TABLE
postgres=# insert into t values(1, 1, 1), (2, 2, 2);
INSERT 0 2
postgres=# create unique index t_idx1 on t(b);
CREATE INDEX

postgres=# prepare st as select distinct b from t where c = $1;
PREPARE
postgres=# explain execute st(1);
QUERY PLAN
-------------------------------------------------
Seq Scan on t (cost=0.00..1.02 rows=1 width=4)
Filter: (c = 1)
(2 rows)
...
postgres=# explain execute st(1);
QUERY PLAN
-------------------------------------------------
Seq Scan on t (cost=0.00..1.02 rows=1 width=4)
Filter: (c = $1)
(2 rows)

-- session 2
postgres=# alter table t alter column b drop not null;
ALTER TABLE

-- session 1:
postgres=# explain execute st(1);
QUERY PLAN
-------------------------------------------------------------
Unique (cost=1.03..1.04 rows=1 width=4)
-> Sort (cost=1.03..1.04 rows=1 width=4)
Sort Key: b
-> Seq Scan on t (cost=0.00..1.02 rows=1 width=4)
Filter: (c = $1)
(5 rows)

-- session 2
postgres=# insert into t values (3, null, 3), (4, null, 3);
INSERT 0 2

-- session 1
postgres=# execute st(3);
b
---

(1 row)

and if we prepare sql outside a transaction, and execute it in the
transaction, the other session can't drop the constraint until the
transaction is ended.

> --
> Best Wishes,
> Ashutosh Bapat
>