Tom Lane wrote:
> Karl Nack <karlnack@futurityinc.com> writes:
> > I notice the row count does not reflect the newly-inserted row, which
> > suggests that the trigger is not seeing changes made to the table. This
> > seems to be exactly opposite of what's in the manual:
> > http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html
> > http://www.postgresql.org/docs/8.3/interactive/trigger-example.html
>
> The reason is that you've declared the function STABLE, which causes it
> to use the calling query's starting snapshot. So it cannot see any
> in-progress changes of the calling query. Declare it VOLATILE (or
> let it default to that) and it will act as you expect.
>
> I'm not sure if the cited portions of the manual ought to contain notes
> about this or not. It seems a bit off-topic for them, but if other
> people have been bit by this, then maybe ... comments anyone?
I have applied the attached documentation patch to subtly mention this
issue.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/create_function.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v
retrieving revision 1.90
diff -c -c -r1.90 create_function.sgml
*** doc/src/sgml/ref/create_function.sgml 14 Feb 2010 01:01:35 -0000 1.90
--- doc/src/sgml/ref/create_function.sgml 25 Feb 2010 22:21:23 -0000
***************
*** 289,295 ****
return the same result for the same argument values, but that its
result could change across SQL statements. This is the appropriate
selection for functions whose results depend on database lookups,
! parameter variables (such as the current time zone), etc. Also note
that the <function>current_timestamp</> family of functions qualify
as stable, since their values do not change within a transaction.
</para>
--- 289,297 ----
return the same result for the same argument values, but that its
result could change across SQL statements. This is the appropriate
selection for functions whose results depend on database lookups,
! parameter variables (such as the current time zone), etc. (It is
! inappropriate for <literal>AFTER</> triggers that wish to
! query rows modified by the current command.) Also note
that the <function>current_timestamp</> family of functions qualify
as stable, since their values do not change within a transaction.
</para>