Postgresql – Update Select in postgresql

postgresqlupdate

I have three tables, in form of grandparent, parent, and child. By this I mean a foreign key exists in child linking to parent, and another foreign key exists in parent linking to grandparent. Child and grandparent's only connection is via parent, and I currently have to join to ask questions like who are child's grandparents and who are grandparent's children.

I realised that a tiny bit of denormalisation is going to make my application code nicer to write, so, I want to add another foreign key in child, which links directly to grandparent (which never changes).
This of course is redundant information, but it means I don't have to join to ask questions like give me all of child's grandparents, or give me all of grandparents grandchildren. This will make my app faster and my application code better, and I don't really need advice on the whys of doing this, but the how.

I've added the columns and I'm looking for advice on an UPDATE query to update the database with the correct values for the grandparent id in the child's new foreign key grandparent_id.

The actual tables are not grandparent, parent and child, but minicourse, topic and lesson, as below.

I tried the below query, from here, but, as I suspected, it is not quite what's needed here. The below updates all children with the same value (i.e. the wrong value, seems to be the first one it gets) of the grandparent id. I want each child updated with the correct grandparent_id.
I'm rather new to operations like this in sql, so help would be much appreciated.

UPDATE lessons
SET minicourse_id=subquery.minicourse_id
FROM (
  SELECT topics.minicourse_id
  FROM topics
  JOIN lessons
    ON topics.id=lessons.topic_id
) AS subquery;

I could just do it manually (there are only about 10 minicourses) by running an update like this 10 times, having found what the values are by regular queries:

UPDATE lessons 
SET 
  minicourse_id =<I enter an actual minicourse_id here> 
WHERE 
  lesson_id in(<I enter a manually-found list of lesson_id's here>);

but I thought it could be cool to learn how to do it with sql instead.

Best Answer

The problem with your query is that the subquery is not correlated. You dodn't need and you shouldn't use lessons inside the subquery.

UPDATE lessons
SET minicourse_id = subquery.minicourse_id
FROM (
  SELECT topics.minicourse_id
  FROM topics
  WHERE topics.id = lessons.topic_id   -- this refers to the "lesson"
                                       -- of the main query
) AS subquery ;

In fact, it can be written without a subquery at all:

UPDATE lessons 
SET minicourse_id = topics.minicourse_id 
FROM topics 
WHERE topics.id = lessons.topic_id ;

or with a different subquery:

UPDATE lessons 
SET minicourse_id 
    = ( SELECT minicourse_id 
        FROM topics 
        WHERE id = lessons.topic_id
      ) ;

Regarding your design, I assume you have added a lessons.minicourse_id column and this foreign key:

ALTER TABLE lessons
    ADD minicourse_id INT ;

ALTER TABLE lessons
    ADD FOREIGN KEY (minicourse_id) 
        REFERENCES minicourses (id) ;

While this achieves what you want, there is a small issue: you may end up with rows in child (lessons) that refer to a grandparent (minicourse) A and also to a parent (topics) that refers to a different grandparent B.

Of course if all your applications and users have code that is correct, this won't happen. But I suggest you enforce this in the database level and not (only) in the application level. This is quite easy to do, with the following change in the foreign keys.

It basically sets the FK to minicourses to be "through" lessons, without a direct FK. You'll still be able to use direct joins between lessons and minicourses:

ALTER TABLE lessons
    ADD minicourse_id INT ;

-- the UPDATE is the same!
UPDATE lessons 
SET minicourse_id = topics.minicourse_id 
FROM topics 
WHERE topics.id = lessons.topic_id ;

ALTER TABLE lessons
    ALTER minicourse_id SET NOT NULL ;

-- we need this for the FK below
ALTER TABLE topics 
    ADD CONSTRAINT minicourse_topic_UQ 
        UNIQUE (minicourse_id, id) ;

-- the FK is "lessons -> topics"
ALTER TABLE lessons
    ADD CONSTRAINT lessons_to_topics_FK2
        FOREIGN KEY (minicourse_id, topic_id) 
        REFERENCES topics (minicourse_id, id) ;

-- drop the previous FK to topics
ALTER TABLE lessons
    DROP CONSTRAINT lessons_to_topics_FK ;

Related Solutions

Postgresql – Depending on Foreign Key Constraints to avoid deletion

It seems like your intention is to delete the parent record when the last child is deleted.

If so, while you can issue an unconditional DELETE to remove the parent and let foreign key relationships with existing children prevent it, this may not be the best strategy. To make it work at all you must do the DELETE of the parent in a different transaction (or sub-transaction) to the DELETE of the child, otherwise the whole transaction's work will be undone when the DELETE of the parent fails and the transaction aborts.

Doing it in an entirely separate transaction is not very safe as it'll leave a window where there's a parent with no children - and if you ever re-use keys it's totally unacceptable.

Deleting the child then deleting the parent in a subtransaction using SAVEPOINT and ROLLBACK TO SAVEPOINT is OK, albeit awkward. You need to think about what happens when two concurrent transactions delete the last two children of the same parent, though; you'd land up with an orphan parent record because each would see the "remaining" child the other one is in the process of deleting. To prevent that you'll need to SELECT ... FOR UPDATE the parent before deleting the child.

BEGIN;
SELECT 1 FROM parent WHERE parent_id = 1 FOR UPDATE;
DELETE FROM child WHERE child_id = 11 and parent_id = 1;
SAVEPOINT delete_parent;
DELETE FROM parent WHERE parent_id = 1;
-- In the application see if the DELETE was successful. If it was, COMMIT.
-- if it failed, run:
ROLLBACK TO SAVEPOINT delete_parent;
-- before committing the deletion of the child.
COMMIT;

Alternately, because you're forcing transactions that remove children from a parent to occur serially, you can just replace the SAVEPOINT and everything below with:

DELETE FROM parent 
WHERE parent_id = 1 AND NOT EXISTS (
  SELECT 1 FROM child c WHERE c.parent_id = parent.parent_id
);

An alternative strategy is to use a SERIALIZABLE isolation transaction to optimistically delete the parent instead of using the initial SELECT ... FOR UPDATE. If somebody else creates a new child or does something else that'll conflict, serializable isolation in PostgreSQL 9.1 or above will abort one of the conflicting transactions. Your application must be prepared to deal with errors and re-run aborted transactions.

Postgresql – Automatic index creation for primary vs. foreign keys in Postgresql

I don't think that - a default index generation for foreign key columns - would lead to serious problems.

It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not.

If they had taken the opposite decision, then we'd be restricted. There would be an index for each and every foreign key constraint. If someone wanted to have some foreign key columns not indexed, they'd be limited or forced.

Now there is only the additional burden of having to add indexes to match the foreign key constraints - for those who want to.

Personally, I almost always add respective indexes for a foreign key constraint. But often, the index does not match exactly the constraint. For example the Fk may be TABLE c (..., FOREIGN KEY (a_id) REFERENCES a (a_id) ) and the index might be single (a_id) or composite (a_id, b_id) because it helps other queries as well, not only those that use a_id.

Or I might have (more rare cases of) 2 foreign key constraints that can be covered but 1 index - eg.: FK (a_id) REFERENCES a (a_id), ... FK (a_id, b_id) REFERENCES b (a_id, b_id).

When creating an index, there are also many options, like the type of index (btree, hash, Gin, Gist and other exotic types), so we're free to choose what is best for our situation.

Also note that Oracle, SQL Server, DB2 and Firebird also do not automatically create indexes for FOREIGN KEY columns, so it's not like that PostgreSQL developers were alone in this. And I'm sure there were many discussions on the matter and they considered both options, I'm only speculating on whey they came to the decision. Looking through the old Postgres discussion lists may reveal more about the actual reasons they considered.

Best Answer

Related Solutions

Postgresql – Depending on Foreign Key Constraints to avoid deletion

Postgresql – Automatic index creation for primary vs. foreign keys in Postgresql

Related Question