From: Janning Vygen <vygen@gmx.de>
To: pgsql-sql@postgresql.org
Subject: Re: Bitfields always atomic? Other way to store attributes?
Date: Mon, 27 Mar 2006 21:20:20 +0200
User-Agent: KMail/1.8.3
Cc: Bryce Nesbitt <bryce1@obviously.com>
References: <44270BF6.6080109@obviously.com>
In-Reply-To: <44270BF6.6080109@obviously.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200603272120.20288.vygen@gmx.de>

Am Sonntag, 26. M=E4rz 2006 23:47 schrieb Bryce Nesbitt:
> Dear List;
>
> If I have two threads modifying the same "bit" field:
>     thread1=3D> update table set bf=3Dbf | '01000'
>     thread2=3D> update table set bf=3Dbf | '10000'
> Will this operation always be safe (e.g. result in bf=3D'11000')? =20

yes, Thats what "ACID" (http://en.wikipedia.org/wiki/ACID) is all about.=20

> Or must=20
> I wrap things in
> explicit transactions?

every statement is in it's own transaction as long as you dont start one by=
=20
yourself.

> My application is to give attributes to an address table.  But maybe
> there is a better way?
>
> I want to mark each addresses with attributes, e.g. the person may be a
> "friend", "on my holiday card list", "owe me money", be an "employee", a
> "volunteer on the xxx project", or none of the above.
>
> I could assign each role a bit.
>
> Or, create a string field: "Friend,Money, Emp,VolXXX".
>
> Or, create related tables:
>         friend_pk,         address_id
>         cardlist_pk,       address_id
>         money_pk,        address_id,    amount_owed
>         volunteer_pk,    address_id
>
> Any thoughts?

create a table with attributes and a table with addresse "address" and then=
=20
link them via a third table address_addressattributes, something like this:

create table address (
  add_id serial not null primary key,
  add_name text not null,
  add_street ...
  ...
);

create table addressattributes (
  aa_id serial not null primary key,
  aa_name text not null unique
);

insert into address_attributes (aa_name) values ('Friend');
insert into address_attributes (aa_name) values ('Money');

create table address_addressattributes (
  add_aa_id serial primary key,
  aa_id int4 not null references address_attributes (aa_id),
  add_id int4 not null references address (add_id)
)

this is called a many-to-many relation.=20


kind regards,
janning