Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199502092245.OAA09775@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Date: Thu, 9 Feb 1995 17:45:02 -0500
From: Mike Doughney <mike@ss1.digex.net>
Message-Id: <199502092245.AA11024@ss1.digex.net>
To: postgres@postgres.Berkeley.EDU
Subject: "Non-functional update" bogus notice problems
Resent-To: postgres-redist@postgres.Berkeley.EDU
Resent-Date: Thu, 09 Feb 95 14:45:12 -0800
Resent-XMts: smtp


I'm receiving this message when attempting to replace certain
attributes in an instance of a class during the same transaction in
which the instance was appended.  The replacements are different from
the current contents of the instance.  The message is spurious in that 
subsequent inspection of the classes shows that the replacements did
take effect (the update was 'functional'). 

There are two btree indexes set up on this class; if the indexes are
not defined the messages do not appear.  This class will be large
enough that I should use indexes and I would rather not redirect and
filter the notice messages from application users if that can be
avoided. 

I'm using Postgres v4.2.   Any suggestions for workarounds or bug
fixes appreciated. 

Here is an example using output from a pgperl program; typing these
commands at the monitor produces the same results.

**** Create class 'USERS' and indices

create USERS (
        USER_SHELL=char16,
        USER_IP=char16,
        USER_ACCT=char16,
        USER_LLOG=char16,
        USER_LL_TIME=abstime,
        USER_NOMAIL=int2 )

**** Notice message isn't generated if these defines are removed

define index BY_USER_IP on USERS
         using btree (USER_IP char16_ops)
define index BY_USER_SHELL on USERS
         using btree (USER_SHELL char16_ops)

**** Begin

**** Append instance

append USERS (
                        USER_SHELL="xyz",
                        USER_LLOG="c722333f00005ea8",
                        USER_LL_TIME="Feb  9 00:00:24 1995"::abstime 
                                - "@ 0 seconds"::reltime  )

**** Retrieve instance

retrieve portal usrport
                    (u.all) from u in USERS
                    where u.USER_SHELL = "xyz"

(this is USER_SHELL USER_LLOG USER_LL_TIME:)
existing user record: xyz c722333f00005ea8 Thu Feb 09 00:00:24 1995 EST

**** Do replacement and get notice message

replace USERS (
                        USER_LLOG="c722333f00005eaa",
                        USER_LL_TIME="Feb  9 00:02:27 1995"::abstime 
                                - "@ 0 seconds"::reltime)
                        where ( USERS.USER_SHELL="xyz" )
                        and ( USERS.USER_LL_TIME < 
                        ("Feb  9 00:02:27 1995"::abstime 
                                - "@ 0 seconds"::reltime) )
NOTICE:Feb  9 17:21:48:Non-functional update, only first update is performed

**** End

**** Inspect using monitor and see that replace was effective

Query sent to backend is "retrieve (USERS.all) where USERS.USER_SHELL="xyz" "
-------------------------------------------------------------------------------------
| USER_SHELL  | USER_IP     | USER_ACCT   | USER_LLOG   | USER_LL_TIME| USER_NOMAIL |
-------------------------------------------------------------------------------------
| xyz        |             |             | c722333f00005eaa| Thu Feb 09 00:02:27 1995 EST|             |
-------------------------------------------------------------------------------------


==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU".
   If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================
              URL: http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/