Saving ActiveRecord in PostgreSQL without id

I have a small table that is used to temporarily queue requests, as such
it does not have an id field (or any other primary key), because it
doesn't make sense to have one.

Sure about that? You won't be accessing specific/individual entries
for any reason whatsoever..?

How will you be looking them up without a key, and what do you gain by
not having one?

Isak

yaman 666 wrote:

How will you be looking them up without a key, and what do you gain by
not having one?

There're 2 unique keys for that. Since entries are continuously
entered/deleted - that would be continuous auto-incrementing and
deletion in the sequence table, thousands times a day, which is really
not needed.

So is there any way to actually make ActiveRecord save without needing
an id?

http://compositekeys.rubyforge.org/

class TempRequest < ActiveRecord::Base
  # set_primary_keys *keys - turns on composite key functionality
  set_primary_keys :pk1, :pk2
end

> How will you be looking them up without a key, and what do you gain by
> not having one?

There're 2 unique keys for that. Since entries are continuously
entered/deleted - that would be continuous auto-incrementing and
deletion in the sequence table, thousands times a day, which is really
not needed.

Sequences aren't that expensive. If you did benchmark and find a
bottleneck, I doubt the sequence and one additional index is why.

If you aren't already, make sure you bulk all statements that belong
together in a transaction block.

If the amount of insert/deletes is large, consider tweaking autovacuum
settings for that table, and perhaps increase max_fsm_pages to keep
track of all the dead tuples.

So is there any way to actually make ActiveRecord save without needing
an id?

You don't need an id column no, but you do need a PK. See
AR::Base.set_primary_key. Be aware that you need to use YourModel#id
to refer that column.

There are also various plugins you could try if you want a composite PK.

IMO you really shouldn't go off the beaten path unless you have a
really good reason to, but YMMV..

Isak