Обсуждение: [PG9.1] CTE usage

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):

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
,
delete_items AS (
    DELETE FROM item
    WHERE item.id IN (SELECT item_id FROM items_to_delete)
),
delete_items_type1 AS (
    DELETE FROM item_type1
    WHERE item_type1.id IN (SELECT item_type1_id FROM items_to_delete)
),
delete_items_type2 AS (
    DELETE FROM item_type2
    WHERE item_type2.id IN (SELECT item_type2_id FROM items_to_delete)
)
SELECT 1;

Should this work? I thought that CTEs are evaluated once in the order of
definition, regardless when/if they are used, so:
 * First, items_to_delete is populated from the existing data.
 * Then delete_items deletes some items according to items_to_delete.
 * Then delete_items_type1 deletes some items of type1 according to items_to_delete.
 * Finally delete_items_type2 deletes some items of type2 according to
items_to_delete.

Does the deletes somehow modify the contents of the 'temporary table'
items_to_delete? The thing is I ran a very similar script on our testing
environment as a part of our DB migration to the new model and have just
realized that only items were deleted (i.e. no item_type1 and item_type2).

Hence I would like to ask if any of you see some obvious flaw in this style of
CTE usage.

Thank you in advance,

Ladislav Lenart

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.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

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

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
>
>
>

On 09/16/2013 04:57 AM, Ladislav Lenart wrote:
> On 16.9.2013 13:26, Alban Hertroys wrote:

>>
>> 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.

http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html

"..In addition, when the data in the referenced columns is changed,
certain actions are performed on the data in this table's columns. The
ON DELETE clause specifies the action to perform when a referenced row
in the referenced table is being deleted. .."

"..CASCADE
Delete any rows referencing the deleted row, or update the values of the
referencing column(s) to the new values of the referenced columns,
respectively.
.."




--
Adrian Klaver
adrian.klaver@gmail.com

On 16.9.2013 15:50, Adrian Klaver wrote:
> On 09/16/2013 04:57 AM, Ladislav Lenart wrote:
>> On 16.9.2013 13:26, Alban Hertroys wrote:
>
>>>
>>> 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.
>
> http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html
>
> "..In addition, when the data in the referenced columns is changed,
> certain actions are performed on the data in this table's columns. The
> ON DELETE clause specifies the action to perform when a referenced row
> in the referenced table is being deleted. .."
>
> "..CASCADE
> Delete any rows referencing the deleted row, or update the values of the
> referencing column(s) to the new values of the referenced columns,
> respectively.
> .."


Hello.

Thank you but I have read this in the official documentation before posting my
(previous) reply. So to quote the important bit about CASCADE:

    Delete any rows referencing the deleted row

My example defines the table item with FK to the table item_type1 and FK to the
table item_type2. Specifying anything on these two constraints does not help one
bit when I delete an item, because item_type1 nor item_type2 does not reference
any... Therefore I suspect that Alban Hertroys had a different model in mind where:
 * item would not have any FKs,
 * item_type1 would have FK to item,
 * item_type2 would have FK to item?

I just wasn't sure, hence I have asked him for a more detailed answer. However,
I am pretty sure ON DELETE CASCADE would not help in my current setup.

Ladislav Lenart

On 09/16/2013 07:38 AM, Ladislav Lenart wrote:
> On 16.9.2013 15:50, Adrian Klaver wrote:
>> On 09/16/2013 04:57 AM, Ladislav Lenart wrote:
>>> On 16.9.2013 13:26, Alban Hertroys wrote:
>>

>> .."
>
>
> Hello.
>
> Thank you but I have read this in the official documentation before posting my
> (previous) reply. So to quote the important bit about CASCADE:
>
>      Delete any rows referencing the deleted row
>
> My example defines the table item with FK to the table item_type1 and FK to the
> table item_type2. Specifying anything on these two constraints does not help one
> bit when I delete an item, because item_type1 nor item_type2 does not reference
> any... Therefore I suspect that Alban Hertroys had a different model in mind where:
>   * item would not have any FKs,
>   * item_type1 would have FK to item,
>   * item_type2 would have FK to item?
>
> I just wasn't sure, hence I have asked him for a more detailed answer. However,
> I am pretty sure ON DELETE CASCADE would not help in my current setup.


I guess the question is whether you actually have set up FK
relationships between items.item_type1_id, items.item_type2_id and the
respective ids in item_type1 and item_type2?

In other words do you have REFERENCE item_type1 ... on item_type1_id?

If so and you add the ON DELETE CASCADE, you could DELETE from
item_type1 and it would delete the respective items rows.

>
> Ladislav Lenart
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 16.9.2013 17:12, Adrian Klaver wrote:
> On 09/16/2013 07:38 AM, Ladislav Lenart wrote:
>> On 16.9.2013 15:50, Adrian Klaver wrote:
>>> On 09/16/2013 04:57 AM, Ladislav Lenart wrote:
>>>> On 16.9.2013 13:26, Alban Hertroys wrote:
>>>
>
>>> .."
>>
>>
>> Hello.
>>
>> Thank you but I have read this in the official documentation before posting my
>> (previous) reply. So to quote the important bit about CASCADE:
>>
>>      Delete any rows referencing the deleted row
>>
>> My example defines the table item with FK to the table item_type1 and FK to the
>> table item_type2. Specifying anything on these two constraints does not help one
>> bit when I delete an item, because item_type1 nor item_type2 does not reference
>> any... Therefore I suspect that Alban Hertroys had a different model in mind where:
>>   * item would not have any FKs,
>>   * item_type1 would have FK to item,
>>   * item_type2 would have FK to item?
>>
>> I just wasn't sure, hence I have asked him for a more detailed answer. However,
>> I am pretty sure ON DELETE CASCADE would not help in my current setup.
>
>
> I guess the question is whether you actually have set up FK
> relationships between items.item_type1_id, items.item_type2_id and the
> respective ids in item_type1 and item_type2?
>
> In other words do you have REFERENCE item_type1 ... on item_type1_id?
>
> If so and you add the ON DELETE CASCADE, you could DELETE from
> item_type1 and it would delete the respective items rows.

Yes, this would work.

Ladislav Lenart

Ladislav Lenart 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)

The mental model that comes to mind is:

item (item_id)
item_type1 (type1_id, item_id <FK>)
item_type2 (type2_id, item_id <FK>)

Or even better:

item (item_id <PK>)
item_type1 (item_id <PK; FK>)
item_type2 (item_id <PK; FK>)

You'd need a trigger on these tables if you want to enforce the "only a
single type allowed" restriction but otherwise this model is much more
usual.


The other mental hang-up with your model is that the "item_type1_id" has a
one-to-one relationship with the item_type1 table so that deleting the item
means it is OK now to delete the associated type.  This is unusual.

David J.






--
View this message in context: http://postgresql.1045698.n5.nabble.com/PG9-1-CTE-usage-tp5771048p5771104.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 16.9.2013 17:30, David Johnston wrote:
> Ladislav Lenart 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)
>
> The mental model that comes to mind is:
>
> item (item_id)
> item_type1 (type1_id, item_id <FK>)
> item_type2 (type2_id, item_id <FK>)
>
> Or even better:
>
> item (item_id <PK>)
> item_type1 (item_id <PK; FK>)
> item_type2 (item_id <PK; FK>)
>
> You'd need a trigger on these tables if you want to enforce the "only a
> single type allowed" restriction but otherwise this model is much more
> usual.
>
>
> The other mental hang-up with your model is that the "item_type1_id" has a
> one-to-one relationship with the item_type1 table so that deleting the item
> means it is OK now to delete the associated type.  This is unusual.
>
> David J.

Thank you for your insightful comments. I will give it a thought.

Ladislav Lenart

Le lundi 16 septembre 2013 à 08:30 -0700, David Johnston a écrit :
> Ladislav Lenart 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)
>
> The mental model that comes to mind is:
>
> item (item_id)
> item_type1 (type1_id, item_id <FK>)
> item_type2 (type2_id, item_id <FK>)
>
> Or even better:
>
> item (item_id <PK>)
> item_type1 (item_id <PK; FK>)
>
> item_type2 (item_id <PK; FK>)
>

I would suggest :

item (item_id <PK>, id_type integer)
item_type1 (item_id <PK; FK>)
item_type2 (item_id <PK; FK>)

where item.id_type is either type_1 or type_2

So that the application knows which table to use?

But chances are the OP can't change his model easily.



--
Salutations, Vincent Veyron
http://marica.fr/
Gestion des contentieux juridiques, des sinistres d'assurance et des contrats