From: | Ladislav Lenart <lenartlad(at)volny(dot)cz> |
---|---|
To: | "List, Postgres" <pgsql-general(at)postgresql(dot)org> |
Subject: | [PG9.1] CTE usage |
Date: | 2013-09-16 09:58:53 |
Message-ID: | 5236D65D.4080705@volny.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2013-09-16 11:26:01 | Re: [PG9.1] CTE usage |
Previous Message | Stuart Bishop | 2013-09-16 08:24:44 | Re: hot_standby_feedback |