Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Tom Lane <tgl@sss.pgh.pa.us>, Steve Wampler <swampler@noao.edu>
Subject: Re: Tagging rows into collections?
Date: Thu, 20 Jun 2002 11:12:42 +0100
User-Agent: KMail/1.4.1
Cc: postgres-sql <pgsql-sql@postgresql.org>
References: <3D10AAFB.D51B1B66@noao.edu> <7413.1024521571@sss.pgh.pa.us>
In-Reply-To: <7413.1024521571@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200206201112.43003.dev@archonet.com>

On Wednesday 19 Jun 2002 10:19 pm, Tom Lane wrote:
> Steve Wampler <swampler@noao.edu> writes:
> >  An event has:  timestamp,event_name,list_of_attributes
> >    The list_of_attributes are simple (string) name,value pairs.
> >
> > However, although selection performance isn't a priority, the
> > ability to reconstruct the events from the database is needed
> > and the above simple table doesn't provide enough information
> > to do so.  (The resolution on the timestamp field isn't
> > enough to distinquish separate events that have the same name.)
>
> What PG version are you using?  In 7.2 the default timestamp resolution
> is microseconds, rather than seconds.  That might be enough to fix your
> problem.

Still doesn't *guarantee* uniqueness though, just makes it less likely.

> If not, your two-table approach sounds reasonable.  You could stick
> with one table and use arrays for the name/value columns, but that
> will make searches harder.

How about using a sequence to generate unique numbers for you? Looks like a=
=20
SERIAL type won't be much use, but a sequence can used without tying it to =
a=20
field.

One thing to be careful of - if you have multiple clients inserting then th=
e=20
numbers won't necessarily be in order. That is, client 1 might insert=20
10,11,12,13 and client 2 20,21,22,23 but in time-order they might be=20
10,11,20,12,22,23,13. This is because each client will get a batch of numbe=
rs=20
to use (for efficiency reasons). Be aware that I'm not 100% certain on that=
=20
last sentence.

- Richard Huxton