Обсуждение: value

INSERT INTO history.my_table_history select new.*, now(), CURRENT_USER, 'INSERT'::character varying;

INSERT INTO history.my_table_history select new.*,  now(), CURRENT_USER, 'UPDATE'::character varying;

On Wed, Sep 15, 2010 at 03:16:55PM +0000, Gissur Þórhallsson wrote:
> Hi there,
>
> I have a somewhat peculiar problem.
>
> To begin with, here are links to my schema and rules: my_table and
> associated rules <http://postgresql.pastebin.com/0eCSuvkU> and
> my_table_history <http://postgresql.pastebin.com/cGm617Cp>
> [etc.]
> Does anybody have any idea what is going on?

Yes.  You're using RULEs where TRIGGERs would do.  Change to TRIGGERs.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

2010/9/16 Gissur Þórhallsson <gissur@loftmyndir.is>:
>> Yes.  You're using RULEs where TRIGGERs would do.  Change to TRIGGERs.
>
> While this could possibly solve my problem in particular; it doesn't explain
> why this is happening.
> Is this somehow expected behavior on an INSERT rule?
>

Rules can be pretty tricky things to work with, and this is one of the
well-known gotchas (to those who know it well!).

Consider the following simplified version of your example:

CREATE TABLE foo(a serial, b text);
CREATE TABLE bar(a int, b text);

CREATE RULE ins_rule AS ON INSERT TO foo
  DO ALSO INSERT INTO bar VALUES(new.a, new.b);

You might think that the rule would guarantee that any insert into foo
would be mirrored with an identical insert on bar. However, this is
not the case. Consider, for example, this insert:

INSERT INTO foo(b) VALUES ('Row 1'), ('Row 2'), ('Row 3');

What the rule will actually do is cause 2 separate INSERT commands to
be executed. The first will add 3 rows to foo, choosing 3 successive
values for 'a' from the sequence. The second command is an insert into
bar, and since 'a' isn't specified, it will use the default for 'a'
from foo, causing another 3 values to be pulled from the sequence. So
the end result is:

SELECT * FROM foo;
 a |   b
---+-------
 1 | Row 1
 2 | Row 2
 3 | Row 3
(3 rows)

SELECT * FROM bar;
 a |   b
---+-------
 4 | Row 1
 5 | Row 2
 6 | Row 3
(3 rows)

which is probably not what you might expect.

It's this sort of thing that makes many people prefer triggers to rules.

Regards,
Dean

2010/9/16 Gissur Þórhallsson <gissur@loftmyndir.is>:
>> which is probably not what you might expect.
>
> No, indeed it is not.
> My solution - which seems to be working - is replacing:
> new.my_table_id
> with:
> currval(pg_get_serial_sequence('my_table', 'my_table_id'))
> in the on_insert rule
>

OK, but you still need to be careful. That trick will only work if you
insert rows one at a time into the table. If you have a single insert
that inserts multiple rows (as in my example), it would fail because
the currval() call would return the same value for each row in the
insert created by the rule.

My advice would be to convert to triggers.

Regards,
Dean


> This does the trick.
> Thanks,
> Gissur Þórhallsson
>
> Loftmyndir ehf.
> Laugavegur 13
> IS 101 Reykjavík - Iceland
> sími (tel): (+354) 540 2500
> tölvupóstur (email): gissur@loftmyndir.is
>