Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Поиск
Список
Период
Сортировка
От Mark Rofail
Тема Re: [HACKERS] GSoC 2017: Foreign Key Arrays
Дата
Msg-id CAJvoCutWOXV1fNu0dZOxerqXmrna+7OgFPrnXEshURuFpPZG6g@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [HACKERS] GSoC 2017: Foreign Key Arrays  (Mark Rofail <markm.rofail@gmail.com>)
Ответы Re: [HACKERS] GSoC 2017: Foreign Key Arrays  (Mark Rofail <markm.rofail@gmail.com>)
Re: [HACKERS] GSoC 2017: Foreign Key Arrays  (Alexander Korotkov <aekorotkov@gmail.com>)
Список pgsql-hackers
Дерево обсуждения
This is the query fired upon any UPDATE/DELETE for RI checks:

SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND ...] FOR KEY SHARE OF x

in  the case of foreign key arrays, it's wrapped in this query:

SELECT 1 WHERE 
    (SELECT count(DISTINCT y) FROM unnest($1) y) 
    = (SELECT count(*) FROM (<QUERY>) z)

This is where the limitation appears, the DISTINCT keyword. Since in reality, count(DISTINCT) will fall back to the default btree opclass for the array element type regardless of the opclass indicated in the access method. Thus I believe going around DISTINCT is the way to go.

This is what I came up with:

SELECT 1 WHERE 
    (SELECT COUNT(*)
        FROM
        (
            SELECT y
            FROM unnest($1) y
            GROUP BY y
        )
    ) 
    = (SELECT count(*) (<QUERY>) z)

I understand there might be some syntax errors but this is just a proof of concept.

Is this the right way to go?
It's been a week and I don't think I made significant progress. Any pointers? 

Best Regards,
MarkRofail

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Paul A Jungwirth
Дата:
Сообщение: Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server
Следующее
От: Jonathan Katz
Дата:
Сообщение: Re: [HACKERS] Draft release notes up for review