On Mon, Dec 22, 2008 at 9:07 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Grzegorz Jaskiewicz wrote:
>> Hey folks,
>>
>> It doesn't stop to bug me, that postgres will return 0 number of
>> affected rows, if table is triggered.
>> Now, question is - is this fixable, but no one cares, or is it some
>> sort of a design/implementation flaw and we just have to live with it.
>
> Would you show us an example of your problem?
If I understand the problem correctly:
atlantis=> CREATE TABLE foo (i int PRIMARY KEY, t text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
atlantis=> CREATE TABLE bar (i int PRIMARY KEY, t text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"bar_pkey" for table "bar"
CREATE TABLE
atlantis=> INSERT INTO foo (i,t) SELECT n, '#'||n FROM
generate_series(0,99) AS g(n);
INSERT 0 100
atlantis=> INSERT INTO bar (i) SELECT i FROM foo;
INSERT 0 100
atlantis=> UPDATE foo SET t='##'||t;
UPDATE 100
atlantis=> CREATE OR REPLACE FUNCTION foo_trigger() RETURNS trigger AS
$$ BEGIN UPDATE bar SET t=NEW.t WHERE i=NEW.i; RETURN NULL; END; $$
LANGUAGE plpgsql;
atlantis=> CREATE TRIGGER foo_update BEFORE UPDATE ON foo FOR EACH ROW
EXECUTE PROCEDURE foo_trigger();
CREATE TRIGGER
CREATE FUNCTION
atlantis=> UPDATE foo SET t='##'||t;
UPDATE 0
^^^^^^^^^^
Grzegorz means such a situation. Personally I understand the current
behavior to be correct -- since no row in that table is updated.
OTOH when you use triggers for emulating table partitioning it leads
to confusion (parent table was not updated, but the child table is
(or isn't because there were really 0 rows updated -- you can't really tell)).
Best regards, Dawid
-- .................. ``The essence of real creativity is a certain: *Dawid Kuroczko* : playfulness, a
flittingfrom idea to idea: qnex42@gmail.com : without getting bogged down by fixated demands.''`..................'
Sherkaner Underhill, A Deepness in the Sky, V. Vinge