Thread: Using complex PRIMARY KEY

Using complex PRIMARY KEY

From

Zsolt

Date:

07 October 2009, 19:27:19

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!

Re: Using complex PRIMARY KEY

From

Sam Mason

Date:

07 October 2009, 20:34:29

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/

Re: Using complex PRIMARY KEY

From

"A. Kretschmer"

Date:

08 October 2009, 05:32:06

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)

Re: Using complex PRIMARY KEY

From

Gurjeet Singh

Date:

08 October 2009, 07:33:03

2009/10/8 Zsolt <zshorvat@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[.singh]@EnterpriseDB.com

singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: Using complex PRIMARY KEY

From

"A. Kretschmer"

Date:

08 October 2009, 07:54:39

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)

PostgreSQL 'hosting'

From

Dragan Zubac

Date:

15 August 2011, 14:39:59

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

Re: PostgreSQL 'hosting'

From

Chris Travers

Date:

15 August 2011, 18:25:09

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

Re: PostgreSQL 'hosting'

From

Torsten Zühlsdorff

Date:

16 August 2011, 08:56:11

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.