POSTGRESQL: Using Values List in Delete

Regarding the SERIALIZABLE transactions, be sure to pay attention to the "for optimal performance" tips near the bottom of this section: http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html#XACT-SERIALIZABLE These can reduce your rollback rates quite significantly.

That said, and not to discourage use of the SERIALIZABLE transaction isolation level, what you describe sounds like exactly the sort of problem that EXCLUSION CONSTRAINTs were designed to solve. Where there is a declarative constraint to enforce a business rule, that is almost always better than using imperative coding (in triggers or application code).

http://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION

http://www.postgresql.org/docs/9.1/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

To use this feature with dates you would currently need to use the TEMPORAL module:

http://pgfoundry.org/projects/temporal/

You will probably want to migrate TEMPORAL columns to a RANGE when you move to PostgreSQL version 9.2 (expected to be released this summer). The 9.2 RANGE feature, the TEMPORAL data type, and the EXCLUSION CONSTRAINT feature were all the work Jeff Davis to address problems like that posed in the question. They were designed to work together.

Full disclosure: The SERIALIZABLE transaction behavior in 9.1 was developed by myself and Dan R.K. Ports of MIT.

You can't directly do it since there's no way (that I can think of) to alternately UPDATE or DELETE from the same query... generally a query can only do one type of CRUD operation (ON DUPLICATE KEY UPDATE being an exception to this).

You could, however, make it "feel like" a single query, with a stored procedure, where you'd set up a CONTINUE HANDLER for the foreign key error you anticipate. The handler will trap the error and can be used to set a variable which you can test to see whether you hit the error and therefore need to also try the update query.

DELIMITER $$

DROP PROCEDURE IF EXISTS delete_or_update $$
CREATE PROCEDURE delete_or_update (IN input_value INT)
BEGIN

  DECLARE hit_fk_error TINYINT DEFAULT 0;
  DECLARE deleted_row_count INT DEFAULT NULL;
  DECLARE updated_row_count INT DEFAULT NULL;

  -- 1451 is probably the error code that will be generated
  -- Cannot delete or update a parent row: a foreign key constraint fails (%s)
  -- otherwise, you may need to substitute the correct error code in the next line

  DECLARE CONTINUE HANDLER FOR 1451 SET hit_fk_error = TRUE;

  DELETE FROM parent_table WHERE tested_column = input_value; /* arg to the stored proc */
  SET deleted_row_count = ROW_COUNT();

  IF (hit_fk_error = TRUE) THEN
    UPDATE parent_table SET some_other_value = 'something_else'
     WHERE tested_column = input_value;
    SET updated_row_count = ROW_COUNT();
  END If;

  SELECT deleted_row_count, updated_row_count, hit_fk_error;

END $$

DELIMITER ;

This will try the delete; if it hits a foreign key error it will then try the update (and the foreign key error will be suppressed). The procedure will return a record set with the number of rows affected by each query and whether the foreign key error occurred. If the "input_value" isn't an INT or the WHERE clause is more complex, you'll need to modify the structure, accordingly.

The potential problem I see here is that if the where clause is too broad, and matches some rows that would cause a foreign key error and other rows that wouldn't, then the query will not treat the rows individually. That could be accomplished by a more sophisticated procedure that used a cursor to find the rows in the where clause and tried to delete or update them individually by primary key after identifying them. That approach would be less efficient but more precise.