Rails - Jul 2006 - Help with Rails and postgres with sequence numbers (global?)

Hello,

I am building an Rails applications that has to integrate an legacy 
system (lxoffice) (schema at: 
http://www.lx-office.org/uploads/media/DB_Schema_2.1.1_R_393_17_03_2005_.pdf). 
They somehow use global ids (global for some tables). I can not change 
the schema and still I want to use Rails to access it and not pure SQL.

When I try to insert a record I get the following message:
ActiveRecord::StatementInvalid: RuntimeError: ERROR     C42P01 
Mrelation "parts
group_id_seq" does not exist    Fnamespace.c    L200 
RRangeVarGetRelid: SELEC
T currval(''partsgroup_id_seq'')

This is of course true, because of the global sequence.I did look into 
the postgres_adapter and found pk_and_sequence_for but I am only 
understanding nada. ;-)

schema:
lxtest=# \d partsgroup
                             Table "public.partsgroup"
   Column   |            Type             |                Modifiers
------------+-----------------------------+-----------------------------------------
 id         | integer                     | default 
nextval((''id''::text)::regclass)
 partsgroup | text                        |
 itime      | timestamp without time zone | default now()
 mtime      | timestamp without time zone |
Triggers:
    mtime_partsgroup BEFORE UPDATE ON partsgroup FOR EACH ROW EXECUTE 
PROCEDURE set_mtime()

and log file:

  [4;36;1mSQL (0.047000)   [0;1m SELECT a.attname, 
format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
 FROM pg_attribute a LEFT JOIN pg_attrdef d
 ON a.attrelid = d.adrelid AND a.attnum = d.adnum
 WHERE a.attrelid = ''partsgroup''::regclass
 AND a.attnum > 0 AND NOT a.attisdropped
 ORDER BY a.attnum

  [4;35;1mSQL (0.016000)   BEGIN
  [4;36;1mPK and serial sequence (0.031000)   [0;1m SELECT 
attr.attname, name.nspname, seq.relname
 FROM pg_class seq,
 pg_attribute attr,
 pg_depend dep,
 pg_namespace name,
 pg_constraint cons
 WHERE seq.oid = dep.objid
 AND seq.relnamespace = name.oid
 AND seq.relkind = ''S''
 AND attr.attrelid = dep.refobjid
 AND attr.attnum = dep.refobjsubid
 AND attr.attrelid = cons.conrelid
 AND attr.attnum = cons.conkey[1]
 AND cons.contype = ''p''
 AND dep.refobjid = ''partsgroup''::regclass

  [4;35;1mPK and custom sequence (0.031000)    SELECT 
attr.attname, name.nspname, split_part(def.adsrc,
''\'''', 2)
 FROM pg_class t
 JOIN pg_namespace name ON (t.relnamespace = name.oid)
 JOIN pg_attribute attr ON (t.oid = attrelid)
 JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum)
 JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1])
 WHERE t.oid = ''partsgroup''::regclass
 AND cons.contype = ''p''
 AND def.adsrc ~* ''nextval''

  [4;36;1mLxofficePartsgroup Create (0.016000)   [0;1mINSERT INTO 
partsgroup ("itime", "mtime", "partsgroup")
VALUES(NULL, NULL,
''autos'')
  [4;35;1mSQL (0.000000)   RuntimeError: ERROR C42P01 Mrelation 
"partsgroup_id_seq" does not exist Fnamespace.c L200
RRangeVarGetRelid:
SELECT currval(''partsgroup_id_seq'')
  [4;36;1mSQL (0.016000)   [0;1mROLLBACK

Can you help me on that?


With another table it is working, but I dont understand why:


schema:

lxtest=# \d parts
                                        Table "public.parts"
       Column       |            Type             | 
Modifiers

--------------------+-----------------------------+-----------------------------
---------------------
 id                 | integer                     | not null default 
nextval((''i
d''::text)::regclass)
 partnumber         | text                        | not null
 description        | text                        |
 unit               | character varying(10)       |
 listprice          | numeric(15,5)               |
 sellprice          | numeric(15,5)               |
 lastcost           | numeric(15,5)               |
 priceupdate        | date                        | default 
date(''now''::text)
 weight             | real                        |
 onhand             | real                        | default 0
 notes              | text                        |
 makemodel          | boolean                     | default false
 assembly           | boolean                     | default false
 alternate          | boolean                     | default false
 rop                | real                        |
 inventory_accno_id | integer                     |
 income_accno_id    | integer                     |
 expense_accno_id   | integer                     |
 bin                | text                        |
 shop               | boolean                     | default false
 obsolete           | boolean                     | default false
 bom                | boolean                     | default false
 image              | text                        |
 drawing            | text                        |
 microfiche         | text                        |
 partsgroup_id      | integer                     |
 ve                 | integer                     |
 gv                 | numeric(15,5)               |
 itime              | timestamp without time zone | default now()
 mtime              | timestamp without time zone |
Indexes:
    "parts_pkey" PRIMARY KEY, btree (id)
    "parts_description_key" btree (lower(description))
    "parts_id_key" btree (id)
    "parts_partnumber_key" btree (lower(partnumber))
Triggers:
    mtime_parts BEFORE UPDATE ON parts FOR EACH ROW EXECUTE PROCEDURE 
set_mtime(
)


logfile:


  [4;35;1mSQL (0.031000)    SELECT a.attname, 
format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
 FROM pg_attribute a LEFT JOIN pg_attrdef d
 ON a.attrelid = d.adrelid AND a.attnum = d.adnum
 WHERE a.attrelid = ''parts''::regclass
 AND a.attnum > 0 AND NOT a.attisdropped
 ORDER BY a.attnum

  [4;36;1mSQL (0.016000)   [0;1mBEGIN
  [4;35;1mPK and serial sequence (0.031000)    SELECT 
attr.attname, name.nspname, seq.relname
 FROM pg_class seq,
 pg_attribute attr,
 pg_depend dep,
 pg_namespace name,
 pg_constraint cons
 WHERE seq.oid = dep.objid
 AND seq.relnamespace = name.oid
 AND seq.relkind = ''S''
 AND attr.attrelid = dep.refobjid
 AND attr.attnum = dep.refobjsubid
 AND attr.attrelid = cons.conrelid
 AND attr.attnum = cons.conkey[1]
 AND cons.contype = ''p''
 AND dep.refobjid = ''parts''::regclass

  [4;36;1mPK and custom sequence (0.031000)   [0;1m SELECT 
attr.attname, name.nspname, split_part(def.adsrc,
''\'''', 2)
 FROM pg_class t
 JOIN pg_namespace name ON (t.relnamespace = name.oid)
 JOIN pg_attribute attr ON (t.oid = attrelid)
 JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum)
 JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1])
 WHERE t.oid = ''parts''::regclass
 AND cons.contype = ''p''
 AND def.adsrc ~* ''nextval''

  [4;35;1mLxofficePart Create (0.031000)   INSERT INTO parts 
("bom", "itime", "shop", "weight",
"microfiche", "alternate", "gv",
"listprice", "mtime", "unit", "ve",
"inventory_accno_id", "makemodel",
"notes", "sellprice", "description",
"bin", "income_accno_id",
"lastcost", "obsolete", "onhand",
"priceupdate", "drawing", "rop",
"expense_accno_id", "image", "partsgroup_id",
"assembly", "partnumber")
VALUES(''f'', ''2006-07-20 14:32:07'',
''f'', 0.0, '''', ''f'', 0.0,
5.0,
''2006-07-20 14:32:07'', ''St.'', 0, 37,
''f'', ''Das ist eine Note'', 5.0, ''Das
ist ein Artikel'', '''', 184, 4.0,
''f'', 26.0, ''2006-07-18'',
'''', NULL, 37,
'''', 380, ''f'', ''test'')
  [4;36;1mSQL (0.016000)   [0;1mSELECT currval(''public.id'')
  [4;35;1mSQL (0.016000)   COMMIT
  [4;36;1mLxofficePart Load (0.031000)   [0;1mSELECT * FROM parts 
WHERE (parts.id = 404) LIMIT 1


I also have to set mtime and itime manually? Is the trigger not working?

Any help would be highly appriciated, as I am running out of time in my 
project :-(

Regards
Till Vollmer


"Ajax mit Ruby on Rails" im iX 4/2006 von Till Vollmer. Siehe auch 
www.railsnet.de

Codemart GmbH
Till Vollmer
Managing Director
Tel: +49 (0)89 1213 5359
Mob: + 49 (0)160 718 7403
Fax: +49 (0)89 1892 1347
Yahoo ID: till_vollmer
Skype: till_vollmer
www.codemart.de
till.vollmer@codemart.de





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

Till Vollmer wrote:
> ActiveRecord::StatementInvalid: RuntimeError: ERROR     C42P01 
> Mrelation "parts
> group_id_seq" does not exist    Fnamespace.c    L200 
> RRangeVarGetRelid: SELEC
> T currval(''partsgroup_id_seq'')
> 
> This is of course true, because of the global sequence.I did look into 
> the postgres_adapter and found pk_and_sequence_for but I am only 
> understanding nada. ;-)
> 
Postgresql uses the concept of ''sequences'' to hold the
''next'' value for
usage by one/more tables..  They are seperate object types with their
own names in your schema.  Usually the name of a sequence has some
relation to the table name...like if the table name was ''foo''
and the id
field that required the sequence was ''bar'', then the sequence
might be
called ''foo_bar_seq''.
Essentially here, rails thinks that the sequence related to that table
should be ''partsgroup_id_seq''.  However, it looks like all
your tables
(at least the two you showed the structure of) are sharing a single
sequence called ''id''.

I''m afraid I don''t know how rails determines the name of the
sequences...if it does it by convention (based on the table/field name)
or by reading the schema definition of the table and seeing how the
''default'' references a specific sequence.

I assume (based on the error message, it''s by convention..
This link may provide more help.

http://lists.rubyonrails.org/pipermail/rails/2005-December/006897.html




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

actually, I meant to post this link (which links to the other one)

http://www.robbyonrails.com/articles/2005/08/20/postgresql-sequences-in-rails

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

Thank you Amiel, this went already into Rails as far as I understood. 
But my case seems to be very special :-( I hope that someone can still 
provide me a fix for the posgres_adapter...

Regards
Till


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

Can''t you just set the name of the seqence in the model definition for 
this particular table?

http://railsmanual.com/class/ActiveRecord::Base/set_sequence_name/1.1.2

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