Forum: Ruby on Rails postgres adapater problem with create_table :id => false

F0bef81280041011039a28a49e83c7dd?d=identicon&s=25 Howard Yeh (Guest)
on 2009-09-16 22:38
(Received via mailing list)
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
Af2ce6689213fdb78913a9662b18da6b?d=identicon&s=25 Rick Lloyd (ricklloyd)
on 2009-09-17 03:36
(Received via mailing list)
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
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-09-17 03:48
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@marnen.org
F0bef81280041011039a28a49e83c7dd?d=identicon&s=25 Howard Yeh (Guest)
on 2009-09-17 22:44
(Received via mailing list)
On Sep 16, 6:48 pm, Marnen Laibow-Koser <rails-mailing-l...@andreas-
s.net> wrote:
>
> > 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?
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-09-17 23:13
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@marnen.org
F0bef81280041011039a28a49e83c7dd?d=identicon&s=25 Howard Yeh (Guest)
on 2009-09-18 01:44
(Received via mailing list)
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. 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.
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-09-18 04:28
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@marnen.org
Af2ce6689213fdb78913a9662b18da6b?d=identicon&s=25 Rick Lloyd (ricklloyd)
on 2009-09-19 13:44
(Received via mailing list)
Rails doesn't insist - it's only Marnen.
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-09-19 16:10
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@marnen.org
Af2ce6689213fdb78913a9662b18da6b?d=identicon&s=25 Rick Lloyd (ricklloyd)
on 2009-09-20 05:50
(Received via mailing list)
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-
4be1eeee12fc5640011eb7d06e288ec5?d=identicon&s=25 JeffV (Guest)
on 2009-10-16 10:35
(Received via mailing list)
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-
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-10-16 21:32
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@marnen.org
4be1eeee12fc5640011eb7d06e288ec5?d=identicon&s=25 JeffV (Guest)
on 2009-10-20 00:21
(Received via mailing list)
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-
4be1eeee12fc5640011eb7d06e288ec5?d=identicon&s=25 JeffV (Guest)
on 2009-10-20 17:44
(Received via mailing list)
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
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-10-20 18:14
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@marnen.org
4be1eeee12fc5640011eb7d06e288ec5?d=identicon&s=25 JeffV (Guest)
on 2009-10-21 03:00
(Received via mailing list)
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-
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-10-21 09:50
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@marnen.org
4be1eeee12fc5640011eb7d06e288ec5?d=identicon&s=25 JeffV (Guest)
on 2009-10-21 18:09
(Received via mailing list)
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-
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-10-21 18:18
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@marnen.org
>
> Jeff
>
> On Oct 21, 12:50�am, Marnen Laibow-Koser <rails-mailing-l...@andreas-
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-10-21 19:00
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@marnen.org
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-10-21 23:10
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@marnen.org
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.