2009/10/8 Zsolt <zshor...@freemail.hu> > > This is our first project using PostgerSQL, where I have a problem I cant > solve on a neat way (I assume PGSQL should provide a nice solution...). > > So we have an old xBase based program we are trying to port to PostgreSQL > while we should keep the original data structure especially the ID fields > must be kept as this IDs are already used in other systems. > > The problem is with two table, one is storing the data of houses the other > the data of tenants in a given houses. > > > > > > Something like this: > > CREATE TABLE house ( > > house_id SERIAL, > > ..... > > CONSTRAINT pk_house_id PRIMARY KEY(house_id) > > > > ) WITHOUT OIDS; > > > > > > CREATE TABLE tenant ( > > tenant_id SERIAL, > > house_id INTEGER REFERENCES house(house_id), > > ..... > > CONSTRAINT pk_tenant_house_id PRIMARY KEY(tenant_id, house_id) > > > > ) WITHOUT OIDS; > > > > For a given house I would like to start the numbering of tenants from 1. > Each house could have tenant_ID=1, obviously in this case the house_ID will > differ. The combination of tenant_ID and house_ID will be the unique > identifier of each tenant. > > > > I'm just looking for the best solution to insert new rows into the tenant > database without worrying about keeping the above mentioned logic in mind. > Should I create a stored procedure to add a new tenant and this will > calculate the new house_id+tenant_id combination (only the house_id would be > passed to the stored procedure, the tenat_id will be calculated by the sp). > In this case how can I avoid that two concurrent user would try to add > records in the same time without getting an exception due to violating the > pk_tenant_house_id constraint? Or should I add a new field in the house > table storing the last issued tenant_id in the given house (max_tenant_id) > which will be used while adding a new record to tenant, and will be updated > by a trigger on the tenant table? Or am I thinking on a wrong way and there > is a better mechanism provided by PostgreSQL for this problem? > > > > Any other suggestions would be greatly appreciated. >
For the logic you mentioned, do not use SERIAL for tenant_id, use integer/bigint instead. You'll have to combine 2 solutions here, probably combine them in a stored procedure. 1) Lock the Tenant table for a small duration, possibly using LOCK command. 2) Get the next number in incremental order, like insert into tenant( tenant_id,house_id, ... ) values( (select max(tenant_id)+1 from tenant where house_id = <H_id_parameter>), <H_id_parameter>, ... ); where H_id_parameter is the house_id value your application is trying to operate on. Best regards, -- Lets call it Postgres EnterpriseDB http://www.enterprisedb.com gurjeet[.sin...@enterprisedb.com singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com Twitter: singh_gurjeet Skype: singh_gurjeet Mail sent from my BlackLaptop device
