Re: Using complex PRIMARY KEY

2009/10/8 Zsolt <zshorvat(at)freemail(dot)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[(dot)singh](at)EnterpriseDB(dot)com

singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet

Mail sent from my BlackLaptop device