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

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,

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?

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. :slight_smile:

> 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.

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

Rick Lloyd wrote:

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

What is that supposed to mean?

Best,

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.

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

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

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

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,

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

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

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 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 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...