Rails - Apr 2005 - reducing the number of columns written by Active Record

Hey guys,

While I was sorting out the trac tickets,  there were a few tickets
related to the way rails issues UPDATE and INSERT statements.  AR
writes to *every* column *every* time and some people have problems
with this.  It can interfere with DEFAULTS which are specified in the
database, and can cause triggers to be executed unnecessarily.

I think this may be pretty trivial to implement.

"write_attribute" could keep track of the updated attributes in an
array.  and then update and create can exclude unchanged attributes
from the sql statements.

If noone sees problems with this approach I can whip something up and
attach a ticket.
-- 
Cheers

Koz

On 4/17/05, Michael Koziarski
<koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> "write_attribute" could keep track of the updated attributes in
an
> array.  and then update and create can exclude unchanged attributes
> from the sql statements.
> 
> If noone sees problems with this approach I can whip something up and
> attach a ticket.
I like the idea, personally. It reduces the amount of crap sent back
and forth to the database ;)

-- 
Urban Artography
http://artography.ath.cx

Rob Park wrote:
>I like the idea, personally. It reduces the amount of crap sent back
>and forth to the database ;)
>  
>
Are you sure it will help your performance?

For a database that has to flush the buffer to disk before finishing 
commit (and that includes all the usual suspects, AFAIK), it may well 
turn out that you shave off a hundred microseconds from a 10 msec 
roundtrip - which would not be worth the extra complexity and bug-proneness.

-- 

Best regards,

Alexey Verkhovsky

Ruby Forum: http://ruby-forum.org        (moderator)
RForum:     http://rforum.andreas-s.net  (co-author)
Instiki:    http://instiki.org           (maintainer)

> While I was sorting out the trac tickets,  there were a few tickets
> related to the way rails issues UPDATE and INSERT statements.  AR
> writes to *every* column *every* time and some people have problems
> with this.  It can interfere with DEFAULTS which are specified in the
> database, and can cause triggers to be executed unnecessarily.
>
> I think this may be pretty trivial to implement.
>
> "write_attribute" could keep track of the updated attributes in
an
> array.  and then update and create can exclude unchanged attributes
> from the sql statements.
>
> If noone sees problems with this approach I can whip something up and
> attach a ticket.
I have no problem with this. In fact I really want it, but haven''t had
time
to do it yet.

Steve

Michael,

This would be an extremely bad idea. While I believe the current model
is a "last in wins" concept (whoever changes the record the last in a
multi-user environment gets their changes saved) - this suggestion
could/most definitely would lead to half record changes which would
reflect no ones desired record.

For example

You have an object that contains customer data and two people open the
following record

Customer ID = 1
Discount = 0%
Amount off = $0

Lets say two people from the same company phoned in and complained about
something that resulted in two different modifications to the account -
one person was offered 10% off their next order and the other person was
offered $100 of the next order. If they both opened the file and saved
their changes in the same time frame (i.e. they both opened the record
before either had saved) then the record would end up as follows

Customer ID = 1
Discount = 10%
Amount off = $100

That would be a VERY bad situation to end up in. I''m not advocating the
current situation which would have the last person that saved get their
desired result but that at least one of the results would be fully saved.

Nothing can be worse for a database then records we can''t be sure of,

That rambling being said, if you wanted to cut back on the amount of
information passed - then the better option requires some modification
to schema - you could add a "last modified" column to the tables and
use
that within your update statement to verify that no changes have been
made when you try to update - that would eliminate both issues - the
"last in wins" and half record saves. When the update was attempted
you
would use something like "UPDATE TABLE_X SET FIELD_Y = "Hello"
WHERE  PK
= 12 AND LAST_UPDATE = ''1-1-00 12:00:00''" That would mean
you would only
update records that were not changed since the user pulled them up and
would only need to send the fields that they modified.

Sorry if this is confusing - brain to fingers isn''t working well this
weekend.

John W Higgins
develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org

Michael Koziarski wrote:
> Hey guys,
> 
> While I was sorting out the trac tickets,  there were a few tickets
> related to the way rails issues UPDATE and INSERT statements.  AR
> writes to *every* column *every* time and some people have problems
> with this.  It can interfere with DEFAULTS which are specified in the
> database, and can cause triggers to be executed unnecessarily.
> 
> I think this may be pretty trivial to implement.
> 
> "write_attribute" could keep track of the updated attributes in
an
> array.  and then update and create can exclude unchanged attributes
> from the sql statements.
> 
> If noone sees problems with this approach I can whip something up and
> attach a ticket.

John W Higgins wrote:
> Michael,
> 
> This would be an extremely bad idea. While I believe the current model
> is a "last in wins" concept (whoever changes the record the last
in a
> multi-user environment gets their changes saved) - this suggestion
> could/most definitely would lead to half record changes which would
> reflect no ones desired record.
> 
> For example
> 
> You have an object that contains customer data and two people open the
> following record
> 
> Customer ID = 1
> Discount = 0%
> Amount off = $0
> 
> Lets say two people from the same company phoned in and complained about
> something that resulted in two different modifications to the account -
> one person was offered 10% off their next order and the other person was
> offered $100 of the next order. If they both opened the file and saved
> their changes in the same time frame (i.e. they both opened the record
> before either had saved) then the record would end up as follows
> 
> Customer ID = 1
> Discount = 10%
> Amount off = $100
> 
> That would be a VERY bad situation to end up in. I''m not
advocating the
> current situation which would have the last person that saved get their
> desired result but that at least one of the results would be fully saved.
> 
> Nothing can be worse for a database then records we can''t be sure
of,
> 
> That rambling being said, if you wanted to cut back on the amount of
> information passed - then the better option requires some modification
> to schema - you could add a "last modified" column to the tables
and use
> that within your update statement to verify that no changes have been
> made when you try to update - that would eliminate both issues - the
> "last in wins" and half record saves. When the update was
attempted you
> would use something like "UPDATE TABLE_X SET FIELD_Y =
"Hello" WHERE  PK
> = 12 AND LAST_UPDATE = ''1-1-00 12:00:00''" That would
mean you would only
> update records that were not changed since the user pulled them up and
> would only need to send the fields that they modified.
> 
> Sorry if this is confusing - brain to fingers isn''t working well
this
> weekend.
> 
> John W Higgins
> develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org
> 
> Michael Koziarski wrote:
> 
>>Hey guys,
>>
>>While I was sorting out the trac tickets,  there were a few tickets
>>related to the way rails issues UPDATE and INSERT statements.  AR
>>writes to *every* column *every* time and some people have problems
>>with this.  It can interfere with DEFAULTS which are specified in the
>>database, and can cause triggers to be executed unnecessarily.
>>
>>I think this may be pretty trivial to implement.
>>
>>"write_attribute" could keep track of the updated attributes
in an
>>array.  and then update and create can exclude unchanged attributes
>>from the sql statements.
>>
>>If noone sees problems with this approach I can whip something up and
>>attach a ticket.
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
Having trouble witht he send button? ;-)



-- 
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON  | www.planetargon.com
* Portland, OR  | robby-/Lcn8Y7Ot69QmPsQ1CNsNQ@public.gmane.org
* 503.351.4730  | blog.planetargon.com
* PHP, Ruby, and PostgreSQL Development
* http://www.robbyonrails.com/
****************************************/

> Having trouble witht he send button? ;-)
I think he''s just expressing how bad of an idea he thinks it is. :)

Steve

> This would be an extremely bad idea. While I believe the current model
> is a "last in wins" concept (whoever changes the record the last
in a
> multi-user environment gets their changes saved) - this suggestion
> could/most definitely would lead to half record changes which would
> reflect no ones desired record.
>
> For example
>
> You have an object that contains customer data and two people open the
> following record
>
> Customer ID = 1
> Discount = 0%
> Amount off = $0
>
> Lets say two people from the same company phoned in and complained about
> something that resulted in two different modifications to the account -
> one person was offered 10% off their next order and the other person was
> offered $100 of the next order. If they both opened the file and saved
> their changes in the same time frame (i.e. they both opened the record
> before either had saved) then the record would end up as follows
>
> Customer ID = 1
> Discount = 10%
> Amount off = $100
>
> That would be a VERY bad situation to end up in. I''m not
advocating the
> current situation which would have the last person that saved get their
> desired result but that at least one of the results would be fully saved.
>
> Nothing can be worse for a database then records we can''t be sure
of,
>
> That rambling being said, if you wanted to cut back on the amount of
> information passed - then the better option requires some modification
> to schema - you could add a "last modified" column to the tables
and use
> that within your update statement to verify that no changes have been
> made when you try to update - that would eliminate both issues - the
> "last in wins" and half record saves. When the update was
attempted you
> would use something like "UPDATE TABLE_X SET FIELD_Y =
"Hello" WHERE  PK
> = 12 AND LAST_UPDATE = ''1-1-00 12:00:00''" That would
mean you would only
> update records that were not changed since the user pulled them up and
> would only need to send the fields that they modified.
>
> Sorry if this is confusing - brain to fingers isn''t working well
this
> weekend.
The concurrency issue you''re talking about is already solved by rails.
The
developers were kind enough to implement a lock_version integer column to
indicate the version of the data in the DB. This value is checked for during
the update.

affected_rows = connection.update(
          "UPDATE #{self.class.table_name} "+
          "SET #{quoted_comma_pair_list(connection,
attributes_with_quotes(false))} " +
          "WHERE #{self.class.primary_key} = #{quote(id)} AND lock_version
#{quote(previous_value)}",
          "#{self.class.name} Update with optimistic locking"
        )

Sending less data is always a good thing. Plus, if you are really concerned
about concurrency issues, and losing previous data, you should implement an
audit trail.

Steve

<PRAYING THIS ONLY SENDS ONCE>

The lock_version is very similar to what I was talking about (very cool
to see it in there) - but I would suggest then that the "optimistic data
passing" (i.e. only changed columns) should only occur when the
lock_version column exists.

I''m not really worrying about loosing previous information. I just
don''t
want two people changing different portions of a record that shouldn''t
be changed independently, without at least knowing that the other
hasn''t
changed.

I can also only imagine the number of problems this would cause for the
unsuspecting developer that runs into an issue but never realizes the
issue until too late. Yes, we can say all we want that it''s the
developers fault - but it wouldn''t look all that great for Rails if it
happened.

I always like making people play by a tighter set of rules to get
optimizations that "may" cause issues.

John W Higgins
develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org

</PRAYING>

Steve V wrote:
>>This would be an extremely bad idea. While I believe the current model
>>is a "last in wins" concept (whoever changes the record the
last in a
>>multi-user environment gets their changes saved) - this suggestion
>>could/most definitely would lead to half record changes which would
>>reflect no ones desired record.
>>
>>For example
>>
>>You have an object that contains customer data and two people open the
>>following record
>>
>>Customer ID = 1
>>Discount = 0%
>>Amount off = $0
>>
>>Lets say two people from the same company phoned in and complained about
>>something that resulted in two different modifications to the account -
>>one person was offered 10% off their next order and the other person was
>>offered $100 of the next order. If they both opened the file and saved
>>their changes in the same time frame (i.e. they both opened the record
>>before either had saved) then the record would end up as follows
>>
>>Customer ID = 1
>>Discount = 10%
>>Amount off = $100
>>
>>That would be a VERY bad situation to end up in. I''m not
advocating the
>>current situation which would have the last person that saved get their
>>desired result but that at least one of the results would be fully
saved.
>>
>>Nothing can be worse for a database then records we can''t be
sure of,
>>
>>That rambling being said, if you wanted to cut back on the amount of
>>information passed - then the better option requires some modification
>>to schema - you could add a "last modified" column to the
tables and use
>>that within your update statement to verify that no changes have been
>>made when you try to update - that would eliminate both issues - the
>>"last in wins" and half record saves. When the update was
attempted you
>>would use something like "UPDATE TABLE_X SET FIELD_Y =
"Hello" WHERE  PK
>>= 12 AND LAST_UPDATE = ''1-1-00 12:00:00''" That
would mean you would only
>>update records that were not changed since the user pulled them up and
>>would only need to send the fields that they modified.
>>
>>Sorry if this is confusing - brain to fingers isn''t working
well this
>>weekend.
> 
> 
> The concurrency issue you''re talking about is already solved by
rails. The
> developers were kind enough to implement a lock_version integer column to
> indicate the version of the data in the DB. This value is checked for
during
> the update.
> 
> affected_rows = connection.update(
>           "UPDATE #{self.class.table_name} "+
>           "SET #{quoted_comma_pair_list(connection,
> attributes_with_quotes(false))} " +
>           "WHERE #{self.class.primary_key} = #{quote(id)} AND
lock_version > #{quote(previous_value)}",
>           "#{self.class.name} Update with optimistic locking"
>         )
> 
> Sending less data is always a good thing. Plus, if you are really concerned
> about concurrency issues, and losing previous data, you should implement an
> audit trail.
> 
> Steve
> 
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

> The lock_version is very similar to what I was talking about (very cool
> to see it in there) - but I would suggest then that the "optimistic
data
> passing" (i.e. only changed columns) should only occur when the
> lock_version column exists.
> 
> I''m not really worrying about loosing previous information. I just
don''t
> want two people changing different portions of a record that
shouldn''t
> be changed independently, without at least knowing that the other
hasn''t
> changed.
I would personally say that this form of protection is very
application dependent, and if you''re worried about it you should use
the lock_version column. Personally, I''m for the idea - most of my
applications would benefit from not having to resave all the columns.
I don''t use lock_version because it''s just not useful in the
application context.
-- 
Phillip Hutchings
http://www.sitharus.com/
sitharus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org /
sitharus-QrR4M9swfipWk0Htik3J/w@public.gmane.org

On 4/18/05, John W Higgins
<develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org>
wrote:
> <PRAYING THIS ONLY SENDS ONCE>
> 
> The lock_version is very similar to what I was talking about (very cool
> to see it in there) - but I would suggest then that the "optimistic
data
> passing" (i.e. only changed columns) should only occur when the
> lock_version column exists.
> 
> I''m not really worrying about loosing previous information. I just
don''t
> want two people changing different portions of a record that
shouldn''t
> be changed independently, without at least knowing that the other
hasn''t
> changed.
> 
> I can also only imagine the number of problems this would cause for the
> unsuspecting developer that runs into an issue but never realizes the
> issue until too late. Yes, we can say all we want that it''s the
> developers fault - but it wouldn''t look all that great for Rails
if it
> happened.
> 
> I always like making people play by a tighter set of rules to get
> optimizations that "may" cause issues.
The concurrency problem you''re describing is real.  My suggestions are:

* Selective column updates off by default
* Documentation warning the users that this is a bad idea unless
optimistic locking is on.

I wrote the current optimistic locking implementation and even I think
that with lock_version on, this selective update should still be off
by default.   Having said all that,  there are still cases where this
may be something that people want, the triggers and DEFAULT cases
being the most prominent.

I''m not really that keen on only allowing it when someone has locking
on either,  in some cases the records in question may be so rarely
updated that the user can turn this on at their own risk.

Incidentally, it''s *highly* unlikely that this will affect performance
as the overhead of opening transactions and writing to disk probably
exceed that of the extra columns.
> John W Higgins
> develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org
> 
> </PRAYING>
Your prayers were answered ;)
> Steve V wrote:
> >>This would be an extremely bad idea. While I believe the current
model
> >>is a "last in wins" concept (whoever changes the record
the last in a
> >>multi-user environment gets their changes saved) - this suggestion
> >>could/most definitely would lead to half record changes which would
> >>reflect no ones desired record.
> >>
> >>For example
> >>
> >>You have an object that contains customer data and two people open
the
> >>following record
> >>
> >>Customer ID = 1
> >>Discount = 0%
> >>Amount off = $0
> >>
> >>Lets say two people from the same company phoned in and complained
about
> >>something that resulted in two different modifications to the
account -
> >>one person was offered 10% off their next order and the other
person was
> >>offered $100 of the next order. If they both opened the file and
saved
> >>their changes in the same time frame (i.e. they both opened the
record
> >>before either had saved) then the record would end up as follows
> >>
> >>Customer ID = 1
> >>Discount = 10%
> >>Amount off = $100
> >>
> >>That would be a VERY bad situation to end up in. I''m not
advocating the
> >>current situation which would have the last person that saved get
their
> >>desired result but that at least one of the results would be fully
saved.
> >>
> >>Nothing can be worse for a database then records we can''t
be sure of,
> >>
> >>That rambling being said, if you wanted to cut back on the amount
of
> >>information passed - then the better option requires some
modification
> >>to schema - you could add a "last modified" column to the
tables and use
> >>that within your update statement to verify that no changes have
been
> >>made when you try to update - that would eliminate both issues -
the
> >>"last in wins" and half record saves. When the update was
attempted you
> >>would use something like "UPDATE TABLE_X SET FIELD_Y =
"Hello" WHERE  PK
> >>= 12 AND LAST_UPDATE = ''1-1-00 12:00:00''"
That would mean you would only
> >>update records that were not changed since the user pulled them up
and
> >>would only need to send the fields that they modified.
> >>
> >>Sorry if this is confusing - brain to fingers isn''t
working well this
> >>weekend.
> >
> >
> > The concurrency issue you''re talking about is already solved
by rails. The
> > developers were kind enough to implement a lock_version integer column
to
> > indicate the version of the data in the DB. This value is checked for
during
> > the update.
> >
> > affected_rows = connection.update(
> >           "UPDATE #{self.class.table_name} "+
> >           "SET #{quoted_comma_pair_list(connection,
> > attributes_with_quotes(false))} " +
> >           "WHERE #{self.class.primary_key} = #{quote(id)} AND
lock_version > > #{quote(previous_value)}",
> >           "#{self.class.name} Update with optimistic
locking"
> >         )
> >
> > Sending less data is always a good thing. Plus, if you are really
concerned
> > about concurrency issues, and losing previous data, you should
implement an
> > audit trail.
> >
> > Steve
> >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz

It also may be easy to overstate the performance improvement here. I 
think most DBMS will replace the entire record if it contains a varchar 
column. This was true of Sybase and SQL Server - I''m not familiar with 
the internals of the others to know for sure but seems likely, otherwise 
they''d first have to figure out if they have room for an
update-in-place
and then do it, which may cost more than just doing it. You may have the 
same problem in comparing all your columns to know if they''ve changed 
(assuming, the web page is posting back all the fields).

So all you''d potentially save is the serialization cost and network
cost
for the extra characters. I''m quite sure you could make more signficant
improvements in other areas.

Michael Koziarski wrote:
>On 4/18/05, John W Higgins
<develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org> wrote:
>  
>
>><PRAYING THIS ONLY SENDS ONCE>
>>
>>The lock_version is very similar to what I was talking about (very cool
>>to see it in there) - but I would suggest then that the "optimistic
data
>>passing" (i.e. only changed columns) should only occur when the
>>lock_version column exists.
>>
>>I''m not really worrying about loosing previous information. I
just don''t
>>want two people changing different portions of a record that
shouldn''t
>>be changed independently, without at least knowing that the other
hasn''t
>>changed.
>>
>>I can also only imagine the number of problems this would cause for the
>>unsuspecting developer that runs into an issue but never realizes the
>>issue until too late. Yes, we can say all we want that it''s the
>>developers fault - but it wouldn''t look all that great for
Rails if it
>>happened.
>>
>>I always like making people play by a tighter set of rules to get
>>optimizations that "may" cause issues.
>>    
>>
>
>The concurrency problem you''re describing is real.  My suggestions
are:
>
>* Selective column updates off by default
>* Documentation warning the users that this is a bad idea unless
>optimistic locking is on.
>
>I wrote the current optimistic locking implementation and even I think
>that with lock_version on, this selective update should still be off
>by default.   Having said all that,  there are still cases where this
>may be something that people want, the triggers and DEFAULT cases
>being the most prominent.
>
>I''m not really that keen on only allowing it when someone has
locking
>on either,  in some cases the records in question may be so rarely
>updated that the user can turn this on at their own risk.
>
>Incidentally, it''s *highly* unlikely that this will affect
performance
>as the overhead of opening transactions and writing to disk probably
>exceed that of the extra columns.
>
>  
>
>>John W Higgins
>>develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org
>>
>></PRAYING>
>>    
>>
>
>Your prayers were answered ;)
>
>  
>
>>Steve V wrote:
>>    
>>
>>>>This would be an extremely bad idea. While I believe the current
model
>>>>is a "last in wins" concept (whoever changes the
record the last in a
>>>>multi-user environment gets their changes saved) - this
suggestion
>>>>could/most definitely would lead to half record changes which
would
>>>>reflect no ones desired record.
>>>>
>>>>For example
>>>>
>>>>You have an object that contains customer data and two people
open the
>>>>following record
>>>>
>>>>Customer ID = 1
>>>>Discount = 0%
>>>>Amount off = $0
>>>>
>>>>Lets say two people from the same company phoned in and
complained about
>>>>something that resulted in two different modifications to the
account -
>>>>one person was offered 10% off their next order and the other
person was
>>>>offered $100 of the next order. If they both opened the file and
saved
>>>>their changes in the same time frame (i.e. they both opened the
record
>>>>before either had saved) then the record would end up as follows
>>>>
>>>>Customer ID = 1
>>>>Discount = 10%
>>>>Amount off = $100
>>>>
>>>>That would be a VERY bad situation to end up in. I''m
not advocating the
>>>>current situation which would have the last person that saved
get their
>>>>desired result but that at least one of the results would be
fully saved.
>>>>
>>>>Nothing can be worse for a database then records we
can''t be sure of,
>>>>
>>>>That rambling being said, if you wanted to cut back on the
amount of
>>>>information passed - then the better option requires some
modification
>>>>to schema - you could add a "last modified" column to
the tables and use
>>>>that within your update statement to verify that no changes have
been
>>>>made when you try to update - that would eliminate both issues -
the
>>>>"last in wins" and half record saves. When the update
was attempted you
>>>>would use something like "UPDATE TABLE_X SET FIELD_Y =
"Hello" WHERE  PK
>>>>= 12 AND LAST_UPDATE = ''1-1-00 12:00:00''"
That would mean you would only
>>>>update records that were not changed since the user pulled them
up and
>>>>would only need to send the fields that they modified.
>>>>
>>>>Sorry if this is confusing - brain to fingers isn''t
working well this
>>>>weekend.
>>>>        
>>>>
>>>The concurrency issue you''re talking about is already
solved by rails. The
>>>developers were kind enough to implement a lock_version integer
column to
>>>indicate the version of the data in the DB. This value is checked
for during
>>>the update.
>>>
>>>affected_rows = connection.update(
>>>          "UPDATE #{self.class.table_name} "+
>>>          "SET #{quoted_comma_pair_list(connection,
>>>attributes_with_quotes(false))} " +
>>>          "WHERE #{self.class.primary_key} = #{quote(id)} AND
lock_version >>>#{quote(previous_value)}",
>>>          "#{self.class.name} Update with optimistic
locking"
>>>        )
>>>
>>>Sending less data is always a good thing. Plus, if you are really
concerned
>>>about concurrency issues, and losing previous data, you should
implement an
>>>audit trail.
>>>
>>>Steve
>>>
>>>
>>>_______________________________________________
>>>Rails mailing list
>>>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>>http://lists.rubyonrails.org/mailman/listinfo/rails
>>>
>>>      
>>>
>>_______________________________________________
>>Rails mailing list
>>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>>    
>>
>
>
>  
>

Er, I take it back.

If we had this, then it would not be necessary to do a find before 
saving the record, and this would be a nice improvement especially for 
use cases where we just update one column.

Jeremy Huffman wrote:
> It also may be easy to overstate the performance improvement here. I 
> think most DBMS will replace the entire record if it contains a 
> varchar column. This was true of Sybase and SQL Server - I''m not 
> familiar with the internals of the others to know for sure but seems 
> likely, otherwise they''d first have to figure out if they have
room
> for an update-in-place and then do it, which may cost more than just 
> doing it. You may have the same problem in comparing all your columns 
> to know if they''ve changed (assuming, the web page is posting back
all
> the fields).
>
> So all you''d potentially save is the serialization cost and
network
> cost for the extra characters. I''m quite sure you could make more 
> signficant improvements in other areas.
>
> Michael Koziarski wrote:
>
>> On 4/18/05, John W Higgins
<develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org> wrote:
>>  
>>
>>> <PRAYING THIS ONLY SENDS ONCE>
>>>
>>> The lock_version is very similar to what I was talking about (very
cool
>>> to see it in there) - but I would suggest then that the
"optimistic
>>> data
>>> passing" (i.e. only changed columns) should only occur when
the
>>> lock_version column exists.
>>>
>>> I''m not really worrying about loosing previous
information. I just
>>> don''t
>>> want two people changing different portions of a record that
shouldn''t
>>> be changed independently, without at least knowing that the other 
>>> hasn''t
>>> changed.
>>>
>>> I can also only imagine the number of problems this would cause for
the
>>> unsuspecting developer that runs into an issue but never realizes
the
>>> issue until too late. Yes, we can say all we want that
it''s the
>>> developers fault - but it wouldn''t look all that great for
Rails if it
>>> happened.
>>>
>>> I always like making people play by a tighter set of rules to get
>>> optimizations that "may" cause issues.
>>>   
>>
>>
>> The concurrency problem you''re describing is real.  My
suggestions are:
>>
>> * Selective column updates off by default
>> * Documentation warning the users that this is a bad idea unless
>> optimistic locking is on.
>>
>> I wrote the current optimistic locking implementation and even I think
>> that with lock_version on, this selective update should still be off
>> by default.   Having said all that,  there are still cases where this
>> may be something that people want, the triggers and DEFAULT cases
>> being the most prominent.
>>
>> I''m not really that keen on only allowing it when someone has
locking
>> on either,  in some cases the records in question may be so rarely
>> updated that the user can turn this on at their own risk.
>>
>> Incidentally, it''s *highly* unlikely that this will affect
performance
>> as the overhead of opening transactions and writing to disk probably
>> exceed that of the extra columns.
>>
>>  
>>
>>> John W Higgins
>>> develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org
>>>
>>> </PRAYING>
>>>   
>>
>>
>> Your prayers were answered ;)
>>
>>  
>>
>>> Steve V wrote:
>>>   
>>>
>>>>> This would be an extremely bad idea. While I believe the
current
>>>>> model
>>>>> is a "last in wins" concept (whoever changes the
record the last in a
>>>>> multi-user environment gets their changes saved) - this
suggestion
>>>>> could/most definitely would lead to half record changes
which would
>>>>> reflect no ones desired record.
>>>>>
>>>>> For example
>>>>>
>>>>> You have an object that contains customer data and two
people open
>>>>> the
>>>>> following record
>>>>>
>>>>> Customer ID = 1
>>>>> Discount = 0%
>>>>> Amount off = $0
>>>>>
>>>>> Lets say two people from the same company phoned in and
complained
>>>>> about
>>>>> something that resulted in two different modifications to
the
>>>>> account -
>>>>> one person was offered 10% off their next order and the
other
>>>>> person was
>>>>> offered $100 of the next order. If they both opened the
file and
>>>>> saved
>>>>> their changes in the same time frame (i.e. they both opened
the
>>>>> record
>>>>> before either had saved) then the record would end up as
follows
>>>>>
>>>>> Customer ID = 1
>>>>> Discount = 10%
>>>>> Amount off = $100
>>>>>
>>>>> That would be a VERY bad situation to end up in.
I''m not
>>>>> advocating the
>>>>> current situation which would have the last person that
saved get
>>>>> their
>>>>> desired result but that at least one of the results would
be fully
>>>>> saved.
>>>>>
>>>>> Nothing can be worse for a database then records we
can''t be sure of,
>>>>>
>>>>> That rambling being said, if you wanted to cut back on the
amount of
>>>>> information passed - then the better option requires some 
>>>>> modification
>>>>> to schema - you could add a "last modified"
column to the tables
>>>>> and use
>>>>> that within your update statement to verify that no changes
have been
>>>>> made when you try to update - that would eliminate both
issues - the
>>>>> "last in wins" and half record saves. When the
update was
>>>>> attempted you
>>>>> would use something like "UPDATE TABLE_X SET FIELD_Y =
"Hello"
>>>>> WHERE  PK
>>>>> = 12 AND LAST_UPDATE = ''1-1-00
12:00:00''" That would mean you
>>>>> would only
>>>>> update records that were not changed since the user pulled
them up
>>>>> and
>>>>> would only need to send the fields that they modified.
>>>>>
>>>>> Sorry if this is confusing - brain to fingers
isn''t working well this
>>>>> weekend.
>>>>>       
>>>>
>>>> The concurrency issue you''re talking about is already
solved by
>>>> rails. The
>>>> developers were kind enough to implement a lock_version integer
>>>> column to
>>>> indicate the version of the data in the DB. This value is
checked
>>>> for during
>>>> the update.
>>>>
>>>> affected_rows = connection.update(
>>>>          "UPDATE #{self.class.table_name} "+
>>>>          "SET #{quoted_comma_pair_list(connection,
>>>> attributes_with_quotes(false))} " +
>>>>          "WHERE #{self.class.primary_key} = #{quote(id)}
AND
>>>> lock_version >>>> #{quote(previous_value)}",
>>>>          "#{self.class.name} Update with optimistic
locking"
>>>>        )
>>>>
>>>> Sending less data is always a good thing. Plus, if you are
really
>>>> concerned
>>>> about concurrency issues, and losing previous data, you should 
>>>> implement an
>>>> audit trail.
>>>>
>>>> Steve
>>>>
>>>>
>>>> _______________________________________________
>>>> Rails mailing list
>>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>>>
>>>>     
>>>
>>> _______________________________________________
>>> Rails mailing list
>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>>
>>>   
>>
>>
>>
>>  
>>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>

On 4/18/05, Jeremy Huffman
<jeremy-S3UG9Ld25dVMf0S0rWojDQC/G2K4zDHf@public.gmane.org>
wrote:
> It also may be easy to overstate the performance improvement here. I
> think most DBMS will replace the entire record if it contains a varchar
> column. This was true of Sybase and SQL Server - I''m not familiar
with
> the internals of the others to know for sure but seems likely, otherwise
> they''d first have to figure out if they have room for an
update-in-place
> and then do it, which may cost more than just doing it. You may have the
> same problem in comparing all your columns to know if they''ve
changed
> (assuming, the web page is posting back all the fields).
> 
> So all you''d potentially save is the serialization cost and
network cost
> for the extra characters. I''m quite sure you could make more
signficant
> improvements in other areas.
I should have been clearer when I said this:
> Incidentally, it''s *highly* unlikely that this will affect
performance
The proposed change will almost certainly not affect performance in
any way shape or form.  This is more for the triggers & DEFAULT case.
> Michael Koziarski wrote:
> 
> >On 4/18/05, John W Higgins
<develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org> wrote:
> >
> >
> >><PRAYING THIS ONLY SENDS ONCE>
> >>
> >>The lock_version is very similar to what I was talking about (very
cool
> >>to see it in there) - but I would suggest then that the
"optimistic data
> >>passing" (i.e. only changed columns) should only occur when
the
> >>lock_version column exists.
> >>
> >>I''m not really worrying about loosing previous
information. I just don''t
> >>want two people changing different portions of a record that
shouldn''t
> >>be changed independently, without at least knowing that the other
hasn''t
> >>changed.
> >>
> >>I can also only imagine the number of problems this would cause for
the
> >>unsuspecting developer that runs into an issue but never realizes
the
> >>issue until too late. Yes, we can say all we want that
it''s the
> >>developers fault - but it wouldn''t look all that great for
Rails if it
> >>happened.
> >>
> >>I always like making people play by a tighter set of rules to get
> >>optimizations that "may" cause issues.
> >>
> >>
> >
> >The concurrency problem you''re describing is real.  My
suggestions are:
> >
> >* Selective column updates off by default
> >* Documentation warning the users that this is a bad idea unless
> >optimistic locking is on.
> >
> >I wrote the current optimistic locking implementation and even I think
> >that with lock_version on, this selective update should still be off
> >by default.   Having said all that,  there are still cases where this
> >may be something that people want, the triggers and DEFAULT cases
> >being the most prominent.
> >
> >I''m not really that keen on only allowing it when someone has
locking
> >on either,  in some cases the records in question may be so rarely
> >updated that the user can turn this on at their own risk.
> >
> >Incidentally, it''s *highly* unlikely that this will affect
performance
> >as the overhead of opening transactions and writing to disk probably
> >exceed that of the extra columns.
> >
> >
> >
> >>John W Higgins
> >>develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org
> >>
> >></PRAYING>
> >>
> >>
> >
> >Your prayers were answered ;)
> >
> >
> >
> >>Steve V wrote:
> >>
> >>
> >>>>This would be an extremely bad idea. While I believe the
current model
> >>>>is a "last in wins" concept (whoever changes the
record the last in a
> >>>>multi-user environment gets their changes saved) - this
suggestion
> >>>>could/most definitely would lead to half record changes
which would
> >>>>reflect no ones desired record.
> >>>>
> >>>>For example
> >>>>
> >>>>You have an object that contains customer data and two
people open the
> >>>>following record
> >>>>
> >>>>Customer ID = 1
> >>>>Discount = 0%
> >>>>Amount off = $0
> >>>>
> >>>>Lets say two people from the same company phoned in and
complained about
> >>>>something that resulted in two different modifications to
the account -
> >>>>one person was offered 10% off their next order and the
other person was
> >>>>offered $100 of the next order. If they both opened the
file and saved
> >>>>their changes in the same time frame (i.e. they both opened
the record
> >>>>before either had saved) then the record would end up as
follows
> >>>>
> >>>>Customer ID = 1
> >>>>Discount = 10%
> >>>>Amount off = $100
> >>>>
> >>>>That would be a VERY bad situation to end up in.
I''m not advocating the
> >>>>current situation which would have the last person that
saved get their
> >>>>desired result but that at least one of the results would
be fully saved.
> >>>>
> >>>>Nothing can be worse for a database then records we
can''t be sure of,
> >>>>
> >>>>That rambling being said, if you wanted to cut back on the
amount of
> >>>>information passed - then the better option requires some
modification
> >>>>to schema - you could add a "last modified"
column to the tables and use
> >>>>that within your update statement to verify that no changes
have been
> >>>>made when you try to update - that would eliminate both
issues - the
> >>>>"last in wins" and half record saves. When the
update was attempted you
> >>>>would use something like "UPDATE TABLE_X SET FIELD_Y =
"Hello" WHERE  PK
> >>>>= 12 AND LAST_UPDATE = ''1-1-00
12:00:00''" That would mean you would only
> >>>>update records that were not changed since the user pulled
them up and
> >>>>would only need to send the fields that they modified.
> >>>>
> >>>>Sorry if this is confusing - brain to fingers
isn''t working well this
> >>>>weekend.
> >>>>
> >>>>
> >>>The concurrency issue you''re talking about is already
solved by rails. The
> >>>developers were kind enough to implement a lock_version integer
column to
> >>>indicate the version of the data in the DB. This value is
checked for during
> >>>the update.
> >>>
> >>>affected_rows = connection.update(
> >>>          "UPDATE #{self.class.table_name} "+
> >>>          "SET #{quoted_comma_pair_list(connection,
> >>>attributes_with_quotes(false))} " +
> >>>          "WHERE #{self.class.primary_key} = #{quote(id)}
AND lock_version > >>>#{quote(previous_value)}",
> >>>          "#{self.class.name} Update with optimistic
locking"
> >>>        )
> >>>
> >>>Sending less data is always a good thing. Plus, if you are
really concerned
> >>>about concurrency issues, and losing previous data, you should
implement an
> >>>audit trail.
> >>>
> >>>Steve
> >>>
> >>>
> >>>_______________________________________________
> >>>Rails mailing list
> >>>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >>>http://lists.rubyonrails.org/mailman/listinfo/rails
> >>>
> >>>
> >>>
> >>_______________________________________________
> >>Rails mailing list
> >>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >>http://lists.rubyonrails.org/mailman/listinfo/rails
> >>
> >>
> >>
> >
> >
> >
> >
> 

-- 
Cheers

Koz

On Sunday 17 April 2005 22:36, Steve V wrote:
> The concurrency issue you''re talking about is already solved by
> rails. The developers were kind enough to implement a lock_version
> integer column to indicate the version of the data in the DB. This
> value is checked for during the update.
I''ve tried this with PostgreSQL and stumbled on a problem where
I''m not
sure if it''s a bug. I have a lock_version column of type int4, default 
0 and with a not-null constraint. Now, when I insert a new row through 
ActiveRecord::Base#save, the generated SQL looks like this

ERROR: null value in column "lock_version" violates not-null 
constraint : INSERT INTO addresses (..., "lock_version", ...) 
VALUES(..., NULL, ...)

(When I drop the not-null constraint, the problem manifests later, when 
trying to increment the lock_version.)

The cause of the trouble is that lock_version is explicitly inserted 
with a NULL value. IMHO, that column needs to be filtered out on 
inserting.

Michael

-- 
Michael Schuerig                         Those people who smile a lot
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                          
Watch the eyes
http://www.schuerig.de/michael/    --Ani DiFranco, Outta Me, Onto You

On 4/18/05, Michael Schuerig
<michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org>
wrote:
> On Sunday 17 April 2005 22:36, Steve V wrote:
> > The concurrency issue you''re talking about is already solved
by
> > rails. The developers were kind enough to implement a lock_version
> > integer column to indicate the version of the data in the DB. This
> > value is checked for during the update.
> 
> I''ve tried this with PostgreSQL and stumbled on a problem where
I''m not
> sure if it''s a bug. I have a lock_version column of type int4,
default
> 0 and with a not-null constraint. Now, when I insert a new row through
> ActiveRecord::Base#save, the generated SQL looks like this
> 
> ERROR: null value in column "lock_version" violates not-null
> constraint : INSERT INTO addresses (..., "lock_version", ...)
> VALUES(..., NULL, ...)

The unit tests seem to cover this situation:

def test_lock_new
    p1 = Person.create({ "first_name"=>"anika"})
    p2 = Person.find(p1.id)
    assert_equal p1.id, p2.id
    p1.first_name = "Anika"
    p1.save
    
    assert_raises(ActiveRecord::StaleObjectError) {
      p2.first_name = "should fail"
      p2.save
    }
end 

Do the active record unit tests not currently pass for PSQL? or have I
missed something?

> (When I drop the not-null constraint, the problem manifests later, when
> trying to increment the lock_version.)
> 
> The cause of the trouble is that lock_version is explicitly inserted
> with a NULL value. IMHO, that column needs to be filtered out on
> inserting.
> 
> Michael
> 
> --
> Michael Schuerig                         Those people who smile a lot
> mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                     
Watch the eyes
> http://www.schuerig.de/michael/    --Ani DiFranco, Outta Me, Onto You
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz

On 4/18/05, Michael Koziarski
<koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 4/18/05, Michael Schuerig
<michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org> wrote:
> > On Sunday 17 April 2005 22:36, Steve V wrote:
> > > The concurrency issue you''re talking about is already
solved by
> > > rails. The developers were kind enough to implement a
lock_version
> > > integer column to indicate the version of the data in the DB.
This
> > > value is checked for during the update.
> >
> > I''ve tried this with PostgreSQL and stumbled on a problem
where I''m not
> > sure if it''s a bug. I have a lock_version column of type
int4, default
> > 0 and with a not-null constraint. Now, when I insert a new row through
> > ActiveRecord::Base#save, the generated SQL looks like this
> >
> > ERROR: null value in column "lock_version" violates not-null
> > constraint : INSERT INTO addresses (..., "lock_version",
...)
> > VALUES(..., NULL, ...)
> 
> The unit tests seem to cover this situation:
> 
> def test_lock_new
>     p1 = Person.create({ "first_name"=>"anika"})
>     p2 = Person.find(p1.id)
>     assert_equal p1.id, p2.id
>     p1.first_name = "Anika"
>     p1.save
> 
>     assert_raises(ActiveRecord::StaleObjectError) {
>       p2.first_name = "should fail"
>       p2.save
>     }
> end
The DDL for these tests on Psql doesn''t have a not null constraint:

CREATE TABLE people (
  id serial,
  first_name text,
  lock_version integer default 0,
  PRIMARY KEY  (id)
);

> Do the active record unit tests not currently pass for PSQL? or have I
> missed something?
> 
> 
> > (When I drop the not-null constraint, the problem manifests later,
when
> > trying to increment the lock_version.)
> >
> > The cause of the trouble is that lock_version is explicitly inserted
> > with a NULL value. IMHO, that column needs to be filtered out on
> > inserting.
> >
> > Michael
> >
> > --
> > Michael Schuerig                         Those people who smile a lot
> > mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                
Watch the eyes
> > http://www.schuerig.de/michael/    --Ani DiFranco, Outta Me, Onto You
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> 
> --
> Cheers
> 
> Koz
> 

-- 
Cheers

Koz

>
> I''ve tried this with PostgreSQL and stumbled on a problem where
I''m not
> sure if it''s a bug. I have a lock_version column of type int4,
default
> 0 and with a not-null constraint. Now, when I insert a new row through
> ActiveRecord::Base#save, the generated SQL looks like this
>
> ERROR: null value in column "lock_version" violates not-null
> constraint : INSERT INTO addresses (..., "lock_version", ...)
> VALUES(..., NULL, ...)
>
> (When I drop the not-null constraint, the problem manifests later, when
> trying to increment the lock_version.)
>
> The cause of the trouble is that lock_version is explicitly inserted
> with a NULL value. IMHO, that column needs to be filtered out on
> inserting.
I''m running PG 8.0 and lock_version works for me. I tried this real
quick
with the default scaffolding, and it worked fine, but that may be because it
outputs an editable field for the lock_version. Maybe it needs to be
specified manually even for insert. In my opinion this shouldn''t be the
case
for an insert, but maybe it''s how it works currently?

Steve

On 4/18/05, Steve V
<ruby-ChEX1j9zMF7JbC0vcoRRxNBPR1lH4CV8@public.gmane.org>
wrote:
> >
> > I''ve tried this with PostgreSQL and stumbled on a problem
where I''m not
> > sure if it''s a bug. I have a lock_version column of type
int4, default
> > 0 and with a not-null constraint. Now, when I insert a new row through
> > ActiveRecord::Base#save, the generated SQL looks like this
> >
> > ERROR: null value in column "lock_version" violates not-null
> > constraint : INSERT INTO addresses (..., "lock_version",
...)
> > VALUES(..., NULL, ...)
> >
> > (When I drop the not-null constraint, the problem manifests later,
when
> > trying to increment the lock_version.)
> >
> > The cause of the trouble is that lock_version is explicitly inserted
> > with a NULL value. IMHO, that column needs to be filtered out on
> > inserting.
> 
> I''m running PG 8.0 and lock_version works for me. I tried this
real quick
> with the default scaffolding, and it worked fine, but that may be because
it
> outputs an editable field for the lock_version. Maybe it needs to be
> specified manually even for insert. In my opinion this shouldn''t
be the case
> for an insert, but maybe it''s how it works currently?
No,  it should be fine for an insert.  After all the
Person.create({"first_name" => "Anika"}) statement
isn''t setting
lock_version explicitly.

Michael S,  what version of pgsql are you running?  
> Steve
> 
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz

On Monday 18 April 2005 02:49, Michael Koziarski wrote:
> On 4/18/05, Steve V
<ruby-ChEX1j9zMF7JbC0vcoRRxNBPR1lH4CV8@public.gmane.org> wrote:
> > I''m running PG 8.0 and lock_version works for me. I tried
this real
> > quick with the default scaffolding, and it worked fine, but that
> > may be because it outputs an editable field for the lock_version.
> > Maybe it needs to be specified manually even for insert. In my
> > opinion this shouldn''t be the case for an insert, but maybe
it''s
> > how it works currently?
>
> No,  it should be fine for an insert.  After all the
> Person.create({"first_name" => "Anika"}) statement
isn''t setting
> lock_version explicitly.
>
> Michael S,  what version of pgsql are you running?
I''m using PG 8.0.1., but I don''t think the problem lies there.
In my
opinion, ActiveRecord should not insert a NULL lock_version. When I do 
as Steve did and use a scaffolded controller to edit an object, then on 
saving the generated INSERT statement contains a NULL value for the 
lock_version column.

Michael

-- 
Michael Schuerig                         Those people who smile a lot
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                          
Watch the eyes
http://www.schuerig.de/michael/    --Ani DiFranco, Outta Me, Onto You

> I''m using PG 8.0.1., but I don''t think the problem lies
there. In my
> opinion, ActiveRecord should not insert a NULL lock_version. When I do
> as Steve did and use a scaffolded controller to edit an object, then on
> saving the generated INSERT statement contains a NULL value for the
> lock_version column.
What I don''t understand is why it''s working for some people,
and the
unit tests, but not for you.   What''s the exact error it gives you.  
The ''insert null'' problem may be able to be addressed soon.
> Michael
> 
> --
> Michael Schuerig                         Those people who smile a lot
> mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                     
Watch the eyes
> http://www.schuerig.de/michael/    --Ani DiFranco, Outta Me, Onto You
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz

On Monday 18 April 2005 11:48, Michael Koziarski wrote:
> > I''m using PG 8.0.1., but I don''t think the problem
lies there. In
> > my opinion, ActiveRecord should not insert a NULL lock_version.
> > When I do as Steve did and use a scaffolded controller to edit an
> > object, then on saving the generated INSERT statement contains a
> > NULL value for the lock_version column.
>
> What I don''t understand is why it''s working for some
people, and the
> unit tests, but not for you.   What''s the exact error it gives
you.  
> The ''insert null'' problem may be able to be addressed
soon.
But the ''insert null'' is the exact problem.

ActiveRecord::StatementInvalid in Address#create
ERROR: null value in column "lock_version" violates not-null 
constraint : INSERT INTO addresses (..., "lock_version", ...) 
VALUES(..., NULL, ...)

If I remove the not-null constraint, the trouble is only delayed, 
because then I get an exception on updating:

NoMethodError in Address#update 
undefined method `+'' for nil:NilClass

There, the value of lock_version, which is NULL, is taken and 1 is 
added. Resulting in an error, obviously.

The thing is, if the lock_version column is not set explicitly, PG is 
happy to insert the default of 0. But if it is set explicitly, well, 
then the given value is written, even if it''s a NULL.

The cause of the error may well be on my side. I''ve started out with 
Rails only a couple of days ago, so I might be misunderstanding 
something that''s utterly obvious to others.

Michael

-- 
Michael Schuerig                           The more it stays the same,
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                       
The less it changes!
http://www.schuerig.de/michael/      --Spinal Tap, The Majesty of Rock

On 4/18/05, Michael Schuerig
<michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org>
wrote:
> On Monday 18 April 2005 11:48, Michael Koziarski wrote:
> > > I''m using PG 8.0.1., but I don''t think the
problem lies there. In
> > > my opinion, ActiveRecord should not insert a NULL lock_version.
> > > When I do as Steve did and use a scaffolded controller to edit an
> > > object, then on saving the generated INSERT statement contains a
> > > NULL value for the lock_version column.
> >
> > What I don''t understand is why it''s working for some
people, and the
> > unit tests, but not for you. What''s the exact error it gives
you.
> > The ''insert null'' problem may be able to be
addressed soon.
> 
> But the ''insert null'' is the exact problem.
It does seem odd,  but we still have the question of why it affects
you and not others.   I''ve just installed postgresql at home, 
I''ll
try the test cases when I get home tonight.  If I can reproduce it
I''ll raise a ticket.

If I can''t,  then I''m not really sure what can be done.
 
> ActiveRecord::StatementInvalid in Address#create
> ERROR: null value in column "lock_version" violates not-null
> constraint : INSERT INTO addresses (..., "lock_version", ...)
> VALUES(..., NULL, ...)
> 
> If I remove the not-null constraint, the trouble is only delayed,
> because then I get an exception on updating:
> 
> NoMethodError in Address#update
> undefined method `+'' for nil:NilClass
> 
> There, the value of lock_version, which is NULL, is taken and 1 is
> added. Resulting in an error, obviously.
> 
> The thing is, if the lock_version column is not set explicitly, PG is
> happy to insert the default of 0. But if it is set explicitly, well,
> then the given value is written, even if it''s a NULL.
> 
> The cause of the error may well be on my side. I''ve started out
with
> Rails only a couple of days ago, so I might be misunderstanding
> something that''s utterly obvious to others.
> 
> Michael
> 
> --
> Michael Schuerig                           The more it stays the same,
> mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                     
The less it changes!
> http://www.schuerig.de/michael/      --Spinal Tap, The Majesty of Rock
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz

On Monday 18 April 2005 22:28, Michael Koziarski wrote:
> > But the ''insert null'' is the exact problem.
>
> It does seem odd,  but we still have the question of why it affects
> you and not others.   I''ve just installed postgresql at home,
 I''ll
> try the test cases when I get home tonight.  If I can reproduce it
> I''ll raise a ticket.
I found the reason. From 
<http://www.postgresql.org/docs/8.0/static/infoschema-columns.html>

  column_default:
  Default expression of the column (null if the current user is 
  not the owner of the table containing the column)

I was accessing the DB as a user who is not the owner, only member of a 
suitable group.

Michael

-- 
Michael Schuerig                  Airtight arguments have
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org        vacuous
conclusions.
http://www.schuerig.de/michael/   --A.O. Rorty, Explaining Emotions

Michael,

I have exactly the same problem that occurs only on the production server
(can''t
reproduce it on dev server).

Have you find an answer?

http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/1be4b617c224b008/fc753409a07230d0#fc753409a07230d0

thanks,
i


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---