I just found an interesting issue in recent PostgreSQL releases:

CREATE VIEW view_nonsense AS SELECT 1 AS a, 2 AS b;

CREATE RULE myrule AS ON INSERT TO view_nonsense
DO INSTEAD NOTHING;

CREATE OR REPLACE FUNCTION debug() RETURNS boolean AS '
DECLARE

BEGIN
INSERT INTO view_nonsense VALUES (10, 20);
RETURN true;
END;
' LANGUAGE 'plpgsql';

SELECT debug();

The INSERT statement is not doing something useful here


[hs@athlon tmp]$ psql test < view.sql
CREATE VIEW
CREATE RULE
CREATE FUNCTION
ERROR: SPI_execute_plan failed executing query "INSERT INTO
view_nonsense VALUES (10, 20)": Unrecognized SPI code 0
CONTEXT: PL/pgSQL function "debug" line 4 at SQL statement


SPI_result_code_string(int code) and PL/pgSQL don't seem to be aware of
DO NOTHING rules.
Is it desired behaviour?

Best regards,

Hans

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/660/816 40 77
www.cybertec.at, www.postgresql.at


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:
> ERROR: SPI_execute_plan failed executing query "INSERT INTO
> view_nonsense VALUES (10, 20)": Unrecognized SPI code 0
> CONTEXT: PL/pgSQL function "debug" line 4 at SQL statement

> SPI_result_code_string(int code) and PL/pgSQL don't seem to be aware of
> DO NOTHING rules.

Hmm. What's happening is that _SPI_execute_plan() initializes its local
result variable to 0, and then that ends up getting returned because the
execute-one-query loop executes zero times. Since 0 isn't a defined SPI
result code, this seems bad.

The question is what to return instead. Of the currently defined SPI
result codes, SPI_OK_UTILITY seems the closest, but it implies that
something happened when nothing did. Is it worth inventing a new
result code SPI_OK_NOTHING (or similar) to describe this case? That
would possibly imply changing a lot of SPI-using code to handle the
new result alternative.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match