Rails - Sep 2009 - postgres adapater problem with create_table :id => false

Hi All,

When saving a record to a table that doesn''t use primary key, the
adapater raises

ActiveRecord::StatementInvalid: PGError: ERROR:  column "id" does not
exist

That''s because the postgres adapter does,

INSERT INTO ... RETURNING "id"

And "id" doesn''t exist for a table that doesn''t ues
it.

The offending block of code is from

PostgreSQLAdapter#insert(sql, name = nil, pk = nil, id_value = nil,
sequence_name = nil)

if supports_insert_with_returning?
  pk, sequence_name = *pk_and_sequence_for(table) unless pk
  if pk
    id = select_value("#{sql} RETURNING #{quote_column_name(pk)}")
    clear_query_cache
    return id
  end
end

The problem is that ActiveRecord::Base.create
would pass in the primary_key "id" for the
parameter pk even though the ActiveRecord class
doesn''t have a primary key.

# this would work
if supports_insert_with_returning?
  # this would work
  pk, sequence_name = *pk_and_sequence_for(table)
  if pk
    id = select_value("#{sql} RETURNING #{quote_column_name(pk)}")
    clear_query_cache
    return id
  end
end


Or is there a way to set the primary key to nil?

class Foo < ActiveRecord::Base
 set_primary_key nil
 primary_key # => ""
end

Howard

I think you want to be using the gem "pg" as your postgresql adapter,
not "postgres".

gem list pg -r
*** REMOTE GEMS ***
pg (0.8.0)

This adapter definitely supports tables without a primary key -
typical join table used for habtm models.  For instance the following
migration works to provide role <-> user join.  This works with
ruby1.8.7 or 1.9.1 and rails 2.3.0-2.3.4, postgres version 8.3.7.

NNNN_create_role_users.rb
class CreateRoleUsers < ActiveRecord::Migration
  def self.up
    create_table :roles_users, :id => false do |t|
      t.integer :role_id
      t.integer :user_id
    end
  end

models/role.rb
class Role < ActiveRecord::Base
  has_and_belongs_to_many :users

models/user.rb
class User < ActiveRecord::Base
  has_and_belongs_to_many :roles


On Sep 16, 4:36 pm, Howard Yeh
<hay...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Hi All,
>
> When saving a record to a table that doesn''t use primary key, the
> adapater raises
>
> ActiveRecord::StatementInvalid: PGError: ERROR:  column "id" does
not
> exist
>
> That''s because the postgres adapter does,
>
> INSERT INTO ... RETURNING "id"
>
> And "id" doesn''t exist for a table that doesn''t
ues it.
>
> The offending block of code is from
>
> PostgreSQLAdapter#insert(sql, name = nil, pk = nil, id_value = nil,
> sequence_name = nil)
>
> if supports_insert_with_returning?
>   pk, sequence_name = *pk_and_sequence_for(table) unless pk
>   if pk
>     id = select_value("#{sql} RETURNING
#{quote_column_name(pk)}")
>     clear_query_cache
>     return id
>   end
> end
>
> The problem is that ActiveRecord::Base.create
> would pass in the primary_key "id" for the
> parameter pk even though the ActiveRecord class
> doesn''t have a primary key.
>
> # this would work
> if supports_insert_with_returning?
>   # this would work
>   pk, sequence_name = *pk_and_sequence_for(table)
>   if pk
>     id = select_value("#{sql} RETURNING
#{quote_column_name(pk)}")
>     clear_query_cache
>     return id
>   end
> end
>
> Or is there a way to set the primary key to nil?
>
> class Foo < ActiveRecord::Base
>  set_primary_key nil
>  primary_key # => ""
> end
>
> Howard

Howard Yeh wrote:
> Hi All,
> 
> When saving a record to a table that doesn''t use primary key, the
> adapater raises
> 
> ActiveRecord::StatementInvalid: PGError: ERROR:  column "id" does
not
> exist
> 
[....]
> 
> The problem is that ActiveRecord::Base.create
> would pass in the primary_key "id" for the
> parameter pk even though the ActiveRecord class
> doesn''t have a primary key.
When would you ever want to use an AR model without a primary key?  The 
only case I can think of where Rails doesn''t want a primary key is on 
habtm join tables, which don''t have an associated model class.

[...]
>
> 
> Or is there a way to set the primary key to nil?
> 
> class Foo < ActiveRecord::Base
>  set_primary_key nil
>  primary_key # => ""
> end
Seems like a really bad idea.  Without a primary key, there is no way to 
uniquely identify a record.  Why do you want to do this?
> 
> Howard
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

On Sep 16, 6:48 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> Howard Yeh wrote:
> > Hi All,
>
> > When saving a record to a table that doesn''t use primary key,
the
> > adapater raises
>
> > ActiveRecord::StatementInvalid: PGError: ERROR:  column "id"
does not
> > exist
>
> [....]
>
> > The problem is that ActiveRecord::Base.create
> > would pass in the primary_key "id" for the
> > parameter pk even though the ActiveRecord class
> > doesn''t have a primary key.
>
> When would you ever want to use an AR model without a primary key?  The
> only case I can think of where Rails doesn''t want a primary key is
on
> habtm join tables, which don''t have an associated model class.
The particular case is for a has_many association, but I want to avoid
the overhead of having two indices.

It''ll be insertion only, so duplicate foreign key is acceptable.

The association has the bag semantics.

If this doesn''t work, what''s "create_table :id =>
false"  ever used
for?
> [...]
>
>
>
> > Or is there a way to set the primary key to nil?
>
> > class Foo < ActiveRecord::Base
> >  set_primary_key nil
> >  primary_key # => ""
> > end
>
> Seems like a really bad idea.  Without a primary key, there is no way to
> uniquely identify a record.  Why do you want to do this?
>
>
>
> > Howard
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.

Howard Yeh wrote:
[...]
> The particular case is for a has_many association, but I want to avoid
> the overhead of having two indices.
That strikes me as a foolish premature optimization, rather like saying 
that you don''t want headlights on your car because they take too much 
power from the engine. :)
> 
> It''ll be insertion only, so duplicate foreign key is acceptable.
Well, of course duplicate foreign keys would be acceptable.  But what do 
you mean by "insertion only"?  Do you mean you''ll never be
reading from
the table?  If so, why have it?
> 
> The association has the bag semantics.
I''m not familiar with that term.  What do you mean?
> 
> If this doesn''t work, what''s "create_table :id =>
false"  ever used
> for?
For tables without AR models.  In a typical Rails app, that would only 
be habtm join tables.

AR wants a primary key, and in any case, it''s poor practice not to have
one, even if it''s composite.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

-- 
Posted via http://www.ruby-forum.com/.

On Sep 17, 2:13 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> Howard Yeh wrote:
>
> [...]
>
> > The particular case is for a has_many association, but I want to avoid
> > the overhead of having two indices.
>
> That strikes me as a foolish premature optimization, rather like saying
> that you don''t want headlights on your car because they take too
much
> power from the engine. :)
>
>
>
> > It''ll be insertion only, so duplicate foreign key is
acceptable.
>
> Well, of course duplicate foreign keys would be acceptable.  But what do
> you mean by "insertion only"?  Do you mean you''ll never
be reading from
> the table?  If so, why have it?
>
>
>
> > The association has the bag semantics.
>
> I''m not familiar with that term.  What do you mean?
>
It just means under a key there can be many items. That''s exactly what
I want, and it seems silly to have a primary key I don''t use. But
nevermind that, if rails insists, so be it.
>
>
> > If this doesn''t work, what''s "create_table :id
=> false"  ever used
> > for?
>
> For tables without AR models.  In a typical Rails app, that would only
> be habtm join tables.
>
> AR wants a primary key, and in any case, it''s poor practice not to
have
> one, even if it''s composite.
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
>
> --
> Posted viahttp://www.ruby-forum.com/.

Howard Yeh wrote:
> On Sep 17, 2:13 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
> s.net> wrote:
>>
>> > The association has the bag semantics.
>>
>> I''m not familiar with that term.  What do you mean?
>>
> 
> It just means under a key there can be many items. 
Yeah, I looked up the term after I responded.
> That''s exactly what
> I want, and it seems silly to have a primary key I don''t use. 
But you *will* be using the primary key, I think.  How will you be using 
the data in this table?
> But
> nevermind that, if rails insists, so be it.
Well, it certainly won''t hurt anything, and it will make database 
administration a lot easier, in addition to making Rails happier.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

Rails doesn''t insist - it''s only Marnen.

On Sep 17, 7:42 pm, Howard Yeh
<hay...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Sep 17, 2:13 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
>
>
>
> s.net> wrote:
> > Howard Yeh wrote:
>
> > [...]
>
> > > The particular case is for a has_many association, but I want to
avoid
> > > the overhead of having two indices.
>
> > That strikes me as a foolish premature optimization, rather like
saying
> > that you don''t want headlights on your car because they take
too much
> > power from the engine. :)
>
> > > It''ll be insertion only, so duplicate foreign key is
acceptable.
>
> > Well, of course duplicate foreign keys would be acceptable.  But what
do
> > you mean by "insertion only"?  Do you mean you''ll
never be reading from
> > the table?  If so, why have it?
>
> > > The association has the bag semantics.
>
> > I''m not familiar with that term.  What do you mean?
>
> It just means under a key there can be many items. That''s exactly
what
> I want, and it seems silly to have a primary key I don''t use. But
> nevermind that, if rails insists, so be it.
>
>
>
> > > If this doesn''t work, what''s "create_table
:id => false"  ever used
> > > for?
>
> > For tables without AR models.  In a typical Rails app, that would only
> > be habtm join tables.
>
> > AR wants a primary key, and in any case, it''s poor practice
not to have
> > one, even if it''s composite.
>
> > Best,
> > --
> > Marnen Laibow-Koserhttp://www.marnen.org
> > mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
>
> > --
> > Posted viahttp://www.ruby-forum.com/.

Rick Lloyd wrote:
> Rails doesn''t insist - it''s only Marnen.
What is that supposed to mean?

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

So the original question was:

   Is it possible to have a table without a primary key?

Two answers were given:

   Yes it''s possible and allowed and occasionally useful. 
Here''s how
it''s
   done.

   Why would you ever want to do that?  It flies in the face of All
That''s
   Rails.  You''ll be sorry if you do that.

That''s what I meant by that.

On Sep 19, 10:10 am, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> Rick Lloyd wrote:
> > Rails doesn''t insist - it''s only Marnen.
>
> What is that supposed to mean?
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.

We have 30-40 tables in a legacy db with no PKs. These tables hold
various logging events and may be searched by a given day, a given
user id, a given event, etc., but not by a particular row id. So there
are reasonable reasons to not have PKs on all tables. Unfortunately,
AR really like PKs...
If I set_primary_key nil I can''t do a create (I get an interning empty
string error). If I set the PK to an arbitrary integer column then
that column is set to NULL (on an insert), despite its value being
passed in the attributes hash... I don''t yet have a solution.

Jeff

On Sep 16, 6:48 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> Howard Yeh wrote:
> > Hi All,
>
> > When saving a record to a table that doesn''t use primary key,
the
> > adapater raises
>
> > ActiveRecord::StatementInvalid: PGError: ERROR:  column "id"
does not
> > exist
>
> [....]
>
> > The problem is that ActiveRecord::Base.create
> > would pass in the primary_key "id" for the
> > parameter pk even though the ActiveRecord class
> > doesn''t have a primary key.
>
> When would you ever want to use an AR model without a primary key?  The
> only case I can think of where Rails doesn''t want a primary key is
on
> habtm join tables, which don''t have an associated model class.
>
> [...]
>
>
>
> > Or is there a way to set the primary key to nil?
>
> > class Foo < ActiveRecord::Base
> >  set_primary_key nil
> >  primary_key # => ""
> > end
>
> Seems like a really bad idea.  Without a primary key, there is no way to
> uniquely identify a record.  Why do you want to do this?
>
>
>
> > Howard
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.

JeffV wrote:
> We have 30-40 tables in a legacy db with no PKs. These tables hold
> various logging events and may be searched by a given day, a given
> user id, a given event, etc., but not by a particular row id. So there
> are reasonable reasons to not have PKs on all tables. 
I think you''re confusing the concept of a *surrogate* PK with a PK in 
general.  Sure, in the case of your logging tables, it''s probably 
reasonable not to have a separate Rails-style id field (surrogate PK). 
But it is advisable -- I''d even say essential -- to find some existing 
field or combination of fields that can be guaranteed unique for each 
record, and to declare them as a (perhaps composite) PK.

> Unfortunately,
> AR really like PKs...
And so it should.  If you don''t want a surrogate PK, then declare some 
other field the PK.  The composite_primary_keys plugin may help here. I 
maintain -- and I believe big-name DB gurus would say likewise -- that a 
table without *some* sort of PK is virtually always a Bad Thing, with or 
without Rails. .
> If I set_primary_key nil I can''t do a create (I get an interning
empty
> string error). If I set the PK to an arbitrary integer column then
> that column is set to NULL (on an insert), despite its value being
> passed in the attributes hash... I don''t yet have a solution.
See what composite_primary_keys will do for you.
> 
> Jeff
> 
> On Sep 16, 6:48�pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

I tried the composite_primary_keys (2.3.2) plugin, with the same
overall result: I cannot insert a row (via Postgres) into a table
without a PK. My model now has this line:
   set_primary_keys :doc_id, :user_id, :view_date, :view_time  #
legacy tbl, include all cols

My insert error is thus:
Mcolumn "doc_id,user_id,view_date,view_time" does not exist
P170
Fparse_expr.c
L421
RtransformColumnRef: INSERT INTO "document_ref_access"
("doc_id",
"user_id",
 "view_date", "view_time") VALUES (1,
E''W:5:a_name'', ''2009-10-19'',
''2009-10-1
9 14:52:11.605227'') RETURNING
"doc_id,user_id,view_date,view_time")

The RETURNING clause seems to be causing the problem.  If I set the PK
to, say, :doc_id (mind you this col is NOT a PK for this table) the
insert works but the doc_id col is set to NULL. Any other suggestions?

Thanks,
Jeff

On Oct 16, 12:32 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> JeffV wrote:
> > We have 30-40 tables in a legacy db with no PKs. These tables hold
> > various logging events and may be searched by a given day, a given
> > user id, a given event, etc., but not by a particular row id. So there
> > are reasonable reasons to not have PKs on all tables.
>
> I think you''re confusing the concept of a *surrogate* PK with a PK
in
> general.  Sure, in the case of your logging tables, it''s probably
> reasonable not to have a separate Rails-style id field (surrogate PK).
> But it is advisable -- I''d even say essential -- to find some
existing
> field or combination of fields that can be guaranteed unique for each
> record, and to declare them as a (perhaps composite) PK.
>
> > Unfortunately,
> > AR really like PKs...
>
> And so it should.  If you don''t want a surrogate PK, then declare
some
> other field the PK.  The composite_primary_keys plugin may help here. I
> maintain -- and I believe big-name DB gurus would say likewise -- that a
> table without *some* sort of PK is virtually always a Bad Thing, with or
> without Rails. .
>
> > If I set_primary_key nil I can''t do a create (I get an
interning empty
> > string error). If I set the PK to an arbitrary integer column then
> > that column is set to NULL (on an insert), despite its value being
> > passed in the attributes hash... I don''t yet have a solution.
>
> See what composite_primary_keys will do for you.
>
>
>
> > Jeff
>
> > On Sep 16, 6:48 pm, Marnen Laibow-Koser
<rails-mailing-l...@andreas-
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.

Solved (at least for now):
I used the composite primary keys plugin but listed only a single (and
fake) primary key:
  set_primary_keys :doc_id  # note plural!

:doc_id is not a PK, and for this logging table it''s possible there is
no combination of columns that will yield a single row, depending on
the timing of multi- inserts. But the insert now works and I can move
on!

Jeff

On Oct 19, 3:14 pm, JeffV
<jeff.h.va...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I tried the composite_primary_keys (2.3.2) plugin, with the same
> overall result: I cannot insert a row (via Postgres) into a table
> without a PK. My model now has this line:
>    set_primary_keys :doc_id, :user_id, :view_date, :view_time  #
> legacy tbl, include all cols
>
> My insert error is thus:
> Mcolumn "doc_id,user_id,view_date,view_time" does not exist
> P170
> Fparse_expr.c
> L421
> RtransformColumnRef: INSERT INTO "document_ref_access"
("doc_id",
> "user_id",
>  "view_date", "view_time") VALUES (1,
E''W:5:a_name'', ''2009-10-19'',
> ''2009-10-1
> 9 14:52:11.605227'') RETURNING
"doc_id,user_id,view_date,view_time")
>
> The RETURNING clause seems to be causing the problem.  If I set the PK
> to, say, :doc_id (mind you this col is NOT a PK for this table) the
> insert works but the doc_id col is set to NULL. Any other suggestions?
>
> Thanks,
> Jeff
>
> On Oct 16, 12:32 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
>
>
>
> s.net> wrote:
> > JeffV wrote:
> > > We have 30-40 tables in a legacy db with no PKs. These tables
hold
> > > various logging events and may be searched by a given day, a
given
> > > user id, a given event, etc., but not by a particular row id. So
there
> > > are reasonable reasons to not have PKs on all tables.
>
> > I think you''re confusing the concept of a *surrogate* PK with
a PK in
> > general.  Sure, in the case of your logging tables, it''s
probably
> > reasonable not to have a separate Rails-style id field (surrogate PK).
> > But it is advisable -- I''d even say essential -- to find some
existing
> > field or combination of fields that can be guaranteed unique for each
> > record, and to declare them as a (perhaps composite) PK.
>
> > > Unfortunately,
> > > AR really like PKs...
>
> > And so it should.  If you don''t want a surrogate PK, then
declare some
> > other field the PK.  The composite_primary_keys plugin may help here.
I
> > maintain -- and I believe big-name DB gurus would say likewise -- that
a
> > table without *some* sort of PK is virtually always a Bad Thing, with
or
> > without Rails. .
>
> > > If I set_primary_key nil I can''t do a create (I get an
interning empty
> > > string error). If I set the PK to an arbitrary integer column
then
> > > that column is set to NULL (on an insert), despite its value
being
> > > passed in the attributes hash... I don''t yet have a
solution.
>
> > See what composite_primary_keys will do for you.
>
> > > Jeff
>
> > > On Sep 16, 6:48 pm, Marnen Laibow-Koser
<rails-mailing-l...@andreas-
>
> > Best,
> > --
> > Marnen Laibow-Koserhttp://www.marnen.org
> > mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> > --
> > Posted viahttp://www.ruby-forum.com/.

JeffV wrote:
> Solved (at least for now):
> I used the composite primary keys plugin but listed only a single (and
> fake) primary key:
>   set_primary_keys :doc_id  # note plural!
> 
> :doc_id is not a PK, and for this logging table it''s possible
there is
> no combination of columns that will yield a single row, depending on
> the timing of multi- inserts. But the insert now works and I can move
> on!
>
Great!  Now declare doc_id as the PK in the DB.
> Jeff
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

doc_id is not a PK. An accurate model of the table would be:
set_primary_key nil
But the PG postgres adapter is unhappy with that because it''s
generating something like:
  INSERT into the_table VALUES (val1, val2...) RETURNING pk
when pk is nil the postgres adapter is unhappy.
For some reason the composite_primary_keys plugin fixes the problem
(as only a single PK is specified).
To me the problem is the PG adapter. I don''t know why it uses
RETURNING in its INSERT statement.

Jeff

On Oct 20, 9:14 am, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> JeffV wrote:
> > Solved (at least for now):
> > I used the composite primary keys plugin but listed only a single (and
> > fake) primary key:
> >   set_primary_keys :doc_id  # note plural!
>
> > :doc_id is not a PK, and for this logging table it''s possible
there is
> > no combination of columns that will yield a single row, depending on
> > the timing of multi- inserts. But the insert now works and I can move
> > on!
>
> Great!  Now declare doc_id as the PK in the DB.
>
> > Jeff
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.

JeffV wrote:
> doc_id is not a PK. An accurate model of the table would be:
> set_primary_key nil
Again: determine a suitable unique index (potentially composite, say, 
doc_id and something else) and declare that as your primary key in the 
DB.  This will have many advantages and virtually no downside AFAIK.
> But the PG postgres adapter is unhappy with that because it''s
> generating something like:
>   INSERT into the_table VALUES (val1, val2...) RETURNING pk
> when pk is nil the postgres adapter is unhappy.
> For some reason the composite_primary_keys plugin fixes the problem
> (as only a single PK is specified).
> To me the problem is the PG adapter. I don''t know why it uses
> RETURNING in its INSERT statement.
No, the problem is in your insistence that you don''t want a PK. 
Really,
any unique index will do the trick.  Stop fighting Rails and good DB 
design and declare one. :)
> 
> Jeff
> 
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

The downside is pretty large: it doesn''t work if I define a composite
PK. If you read my posting above you''ll see that I get a Postgres
adapter error on the RETURNING statement. That and the fact that this
is a logging table (in a legacy DB) with no PK(s). The columns are:
doc_id (not unique), user_id (not unique), date viewed (not unique),
and the combination is not unique.  That''s it! No PK. It''s not
my
insistence, but rather the nature of a logging table. A row in this
table is never updated, just INSERTs and SELECTs. I cannot add a
row_id column to this table as that would break many COBOL programs.
So that is life in the real world. By claiming the table has a single
PK via "set_primary_keys" (note it''s plural) it works.

Jeff

On Oct 21, 12:50 am, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
> JeffV wrote:
> > doc_id is not a PK. An accurate model of the table would be:
> > set_primary_key nil
>
> Again: determine a suitable unique index (potentially composite, say,
> doc_id and something else) and declare that as your primary key in the
> DB.  This will have many advantages and virtually no downside AFAIK.
>
> > But the PG postgres adapter is unhappy with that because it''s
> > generating something like:
> >   INSERT into the_table VALUES (val1, val2...) RETURNING pk
> > when pk is nil the postgres adapter is unhappy.
> > For some reason the composite_primary_keys plugin fixes the problem
> > (as only a single PK is specified).
> > To me the problem is the PG adapter. I don''t know why it uses
> > RETURNING in its INSERT statement.
>
> No, the problem is in your insistence that you don''t want a PK.
 Really,
> any unique index will do the trick.  Stop fighting Rails and good DB
> design and declare one. :)
>
>
>
> > Jeff
>
> Best,
> --
> Marnen Laibow-Koserhttp://www.marnen.org
> mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> --
> Posted viahttp://www.ruby-forum.com/.

JeffV wrote:
> The downside is pretty large: it doesn''t work if I define a
composite
> PK. If you read my posting above you''ll see that I get a Postgres
> adapter error on the RETURNING statement. 
No, your posting above says that you get the error when you define a 
*nil* PK, not a *composite* one.
> That and the fact that this
> is a logging table (in a legacy DB) with no PK(s). The columns are:
> doc_id (not unique), user_id (not unique), date viewed (not unique),
> and the combination is not unique.  That''s it! No PK.
The combination is not unique?  The same user could generate several 
records for the same doc with the *exact same* date viewed?  It''s not a
timestamp?
> It''s not my
> insistence, but rather the nature of a logging table. 
Um, no.  It is simple to design a logging table where a unique index can 
be extracted.
> A row in this
> table is never updated, just INSERTs and SELECTs. I cannot add a
> row_id column to this table as that would break many COBOL programs.
Really?  COBOL complains if all the data is there, but there''s an extra
column?
> So that is life in the real world.
The real world of bad DB design, maybe.
> By claiming the table has a single
> PK via "set_primary_keys" (note it''s plural) it works.
If you''re claiming that the doc_id is the PK, then I''d be
surprised if
Rails would ever fetch more than one record per doc_id.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> 
> Jeff
> 
> On Oct 21, 12:50�am, Marnen Laibow-Koser <rails-mailing-l...@andreas-
-- 
Posted via http://www.ruby-forum.com/.

Marnen Laibow-Koser wrote:
> JeffV wrote:
>> The downside is pretty large: it doesn''t work if I define a
composite
>> PK. If you read my posting above you''ll see that I get a
Postgres
>> adapter error on the RETURNING statement. 
> 
> No, your posting above says that you get the error when you define a 
> *nil* PK, not a *composite* one.
>
My mistake.  I missed a post where you *did* say this.  Doing some 
research...
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

-- 
Posted via http://www.ruby-forum.com/.

Marnen Laibow-Koser wrote:
> Marnen Laibow-Koser wrote:
>> JeffV wrote:
>>> The downside is pretty large: it doesn''t work if I define
a composite
>>> PK. If you read my posting above you''ll see that I get a
Postgres
>>> adapter error on the RETURNING statement. 
>> 
>> No, your posting above says that you get the error when you define a 
>> *nil* PK, not a *composite* one.
>>
> 
> My mistake.  I missed a post where you *did* say this.  Doing some 
> research...
Research done.  I created a sample app testing composite_primary_keys, 
which is up on Github at http://github.com/marnen/cpk_sample if you want 
to play around with it.  The app uses one table (entries) with three 
integer fields (alpha, beta, gamma) and no id field.  There are several 
branches:

* no_index_nil_keys -- no index on DB table, Entry.set_primary_keys nil. 
Doesn''t work.

* no_index_all_keys -- no index on DB table, Entry.set_primary_keys 
:alpha, :beta, :gamma. Works for reading and writing (that is, 
Entry.create and Entry.all both work as expected).

* all_index_all_keys -- primary key(alpha beta gamma) on DB table, 
Entry.set_primary_keys :alpha, :beta, :gamma.  Works for reading and 
writing.

I think you were getting the error in a no_index_all_keys situation, and 
I cannot reproduce that error.  I''m using Mac OS X 10.6.1, Ruby 1.8.7, 
Rails 2.3.4, composite_primary_keys 2.3.2, and PostgreSQL 8.4.1.  Let me 
know if you get different results with my code...
> Best,
> --
> Marnen Laibow-Koser
> http://www.marnen.org
> marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.