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
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
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?
> 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?
> 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.
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.
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?
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!
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!
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.
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.
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.
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,
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...
* 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...
