Re: [PG9.1] CTE usage - Mailing list pgsql-general

Nevermind, I already found the root cause of my problem: boolean logic of NULL
in conjunction with the NOT IN operator. My real usecase was a bit more involved:

WITH
items_to_delete AS (
    SELECT
        item.id AS item_id,
        item.item_type1_id AS item_type1_id,
        item.item_type2_id AS item_type2_id
    FROM item
    WHERE ... -- limit the set of items to delete
),
ok_items AS (
    -- 'Required' because the planner otherwise chose a very inneficient plan.
    SELECT
        item.id AS item_id,
        item.item_type1_id,
        item.item_type2_id
    FROM item
    EXCEPT
    SELECT * FROM items_to_delete
),
delete_items AS (
    DELETE FROM item
    WHERE item.id NOT IN (SELECT item_id FROM ok_items)
),
delete_items_type1 AS (
    DELETE FROM item_type1
    WHERE item_type1.id NOT IN (SELECT item_type1_id FROM ok_items)
),
delete_items_type2 AS (
    DELETE FROM item_type2
    WHERE item_type2.id NOT IN (SELECT item_type2_id FROM ok_items)
)
SELECT 1;

This does not work because the NOT IN argument in delete_items_type1 and
delete_items_type2 contain NULLs. When I change the CTEs like this:

delete_items_typeX AS (
    DELETE FROM item_typeX
    WHERE item_typeX.id NOT IN (
        SELECT item_typeX_id
        FROM ok_items
        WHERE item_typeX_id IS NOT NULL
    )
)

everything works as it should.


Ladislav Lenart


On 16.9.2013 13:57, Ladislav Lenart wrote:
> On 16.9.2013 13:26, Alban Hertroys wrote:
>> On 16 September 2013 11:58, Ladislav Lenart <lenartlad@volny.cz> wrote:
>>> Hello all.
>>>
>>> I am curious about the following usage of CTEs:
>>>
>>> Imagine three tables:
>>>  * item (id, item_type1_id, item_type2_id, ...)
>>>  * item_type1 (id, ...)
>>>  * item_type2 (id, ...)
>>> where
>>>  * item_type1_id is FK to item_type1 (id)
>>>  * item_type2_id is FK to item_type2 (id)
>>>
>>> Items are of two types (type1 and type2). Each item type has different data
>>> columns. An item is either of type1 (item_type1_id is populated) or of type2
>>> (item_type2_id is populated). I want to delete some items along with the
>>> corresponding rows in the tables item_type1 and item_type2 (they have no meaning
>>> without the 'parent'). I have written the following CTE (I want to compute
>>> items_to_delete only once):
>>
>> Wouldn't it be much easier to define an FK constraint with ON DELETE CASCADE?
>> With that, you only need to worry about which rows you delete from the
>> parent table and dependant children will be removed automatically.
>
>
> Hello.
>
> I don't quite follow. Having item.item_type1_id FK with ON DELETE CASCADE would
> delete ITEM (the parent) when ITEM_TYPE1 (the child) is deleted. You suggests
> the opposite direction. Could you please describe your proposal in more detail
> (just the list of tables with their columns)?
>
> Nevertheless, I am still curious about my original question(s):
>  * Whether is this style of CTE usage discouraged (i.e. rely on the in-order
> evaluation of CTEs without even mentioning them in the top query).
>  * Any idea what could be wrong in my example.
>
> Thank you,
>
> Ladislav Lenart
>
>
>