Help with Rails and postgres with sequence numbers (global?)

tvollmer · July 20, 2006, 3:10pm

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.

and log file:

e[4;36;1mSQL (0.047000)e[0m e[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
e[0m
e[4;35;1mSQL (0.016000)e[0m e[0mBEGINe[0m
e[4;36;1mPK and serial sequence (0.031000)e[0m e[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
e[0m
e[4;35;1mPK and custom sequence (0.031000)e[0m e[0m 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’
e[0m
e[4;36;1mLxofficePartsgroup Create (0.016000)e[0m e[0;1mINSERT INTO
partsgroup (“itime”, “mtime”, “partsgroup”) VALUES(NULL, NULL,
‘autos’)e[0m
e[4;35;1mSQL (0.000000)e[0m e[0mRuntimeError: ERROR C42P01 Mrelation
“partsgroup_id_seq” does not exist Fnamespace.c L200 RRangeVarGetRelid:
SELECT currval(‘partsgroup_id_seq’)e[0m
e[4;36;1mSQL (0.016000)e[0m e[0;1mROLLBACKe[0m

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

--------------------±----------------------------±----------------------------

logfile:

e[4;35;1mSQL (0.031000)e[0m e[0m 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
e[0m
e[4;36;1mSQL (0.016000)e[0m e[0;1mBEGINe[0m
e[4;35;1mPK and serial sequence (0.031000)e[0m e[0m 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
e[0m
e[4;36;1mPK and custom sequence (0.031000)e[0m e[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’
e[0m
e[4;35;1mLxofficePart Create (0.031000)e[0m e[0mINSERT 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’)e[0m
e[4;36;1mSQL (0.016000)e[0m e[0;1mSELECT currval(‘public.id’)e[0m
e[4;35;1mSQL (0.016000)e[0m e[0mCOMMITe[0m
e[4;36;1mLxofficePart Load (0.031000)e[0m e[0;1mSELECT * FROM parts
WHERE (parts.id = 404) LIMIT 1e[0m

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

“Ajax mit Ruby on Rails” im iX 4/2006 von Till V… Siehe auch
www.railsnet.de

Codemart GmbH
Till V.
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
[email protected]

tvollmer · July 20, 2006, 3:28pm

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

tvollmer · July 20, 2006, 3:46pm

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

tvollmer · July 20, 2006, 4:34pm

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

tvollmer · July 20, 2006, 5:04pm

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