Обсуждение: Using complex PRIMARY KEY
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.
Thanks!
Zsolt
________________________________________________________
Csatlakozzon a Bookline törzsvásárlói programjához,
és válogasson prémium és egyedi akcióink közül!
On Wed, Oct 07, 2009 at 09:19:58PM +0200, Zsolt wrote: > 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. The term for this that tends to float around for this concept (in PG anyway) is "gap-less sequences": this looks like a reasonable link: http://www.varlena.com/GeneralBits/130.php -- Sam http://samason.me.uk/
In response to Zsolt : > > 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. > > > > 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. Do you have PostgreSQL 8.4? If yes, you can use CTE-functions for that. row_number(). Unfortunately, at the moment i haven't access to my database to create an example, maybe later. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet
Mail sent from my BlackLaptop device
In response to A. Kretschmer : > In response to Zsolt : > > > > 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. > > > > > > > > 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. > > Do you have PostgreSQL 8.4? > > If yes, you can use CTE-functions for that. row_number(). > > Unfortunately, at the moment i haven't access to my database to create > an example, maybe later. Okay, i'm back and here my example: test=*# select * from house; id | name ----+-------- 1 | house1 2 | house2 (2 rows) test=*# select * from tenant; id | house_id | name ----+----------+--------- 1 | 1 | tenant1 2 | 1 | tenant2 3 | 1 | tenant3 4 | 2 | tenant4 5 | 2 | tenant5 6 | 2 | tenant6 7 | 2 | tenant7 (7 rows) test=*# select id, house_id, row_number() over (partition by house_id) as tenant_house, name from tenant order by id, house_id; id | house_id | tenant_house | name ----+----------+--------------+--------- 1 | 1 | 1 | tenant1 2 | 1 | 2 | tenant2 3 | 1 | 3 | tenant3 4 | 2 | 1 | tenant4 5 | 2 | 2 | tenant5 6 | 2 | 3 | tenant6 7 | 2 | 4 | tenant7 (7 rows) Hope that helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
Hello Does anybody know if there're any companies offering PostgreSQL 'hosting' ? By 'hosting', I mean you get access to a database to which your application connects remotely and do sql stuff. 'Hosting' company takes care of database maintenance,backup,etc. Sincerely Dragan Zubac
On Mon, Aug 15, 2011 at 7:38 AM, Dragan Zubac <zubacdragan@gmail.com> wrote: > Hello > > Does anybody know if there're any companies offering PostgreSQL 'hosting' ? > By 'hosting', I mean you get access to a database to which your > application connects remotely and do sql stuff. > 'Hosting' company takes care of database maintenance,backup,etc. > I think there are a few. Amazon web services... Command Prompt Inc..... Hub.org.... Best Wishes, Chris Travers
Dragan Zubac schrieb: > Does anybody know if there're any companies offering PostgreSQL 'hosting' ? > By 'hosting', I mean you get access to a database to which your > application connects remotely and do sql stuff. > 'Hosting' company takes care of database maintenance,backup,etc. Have a look at: http://www.postgresql.org/support/professional_hosting Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann.