Обсуждение: affected rows count

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.

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?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

On Mon, 2008-12-22 at 15:07 -0500, Bruce Momjian 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?
> 

This may not be the problem he's talking about, but it's bothered me for
a while that there is no way to control the value returned for the
affected rows.

For instance, if you have an updatable view that uses a function that
updates a table in a remote database, it would be nice to be able to
pass that value back to the client.

Regards,Jeff Davis

On 2008-12-22, at 21:07, Bruce Momjian 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?
Dunno what's wrong with me lately. I was under impression, that about  
1/2 year ago on 8.1 I wasn't able to get row count anymore if there  
was a trigger on a table. Well, affected row count would be always 0  
than.
But trying now on cvs head, it all works great. heh, I am terribly  
sorry...

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
 

On 2008-12-22, at 22:35, Dawid Kuroczko wrote:
>
>
> 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.

that's not quite what I meant. It is correct behavior in this  
situation - since we didn't update anything.
Like I said, it is my bad - I should have tested it before emailing - 
hackers... So far my ratio of useful emails here is very low. :/