Date: Fri, 23 Apr 93 03:28:16 -0700
From: fernando@afrodita.etsimo.uniovi.es (Fernando Ariznavarreta)
Subject: Problems writing a Unique number generator.
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
To: postgres@postgres.Berkeley.EDU (Postgres Lista de correo.)
Message-Id: <9304231026.AA01587@afrodita.etsimo.uniovi.es>
Content-Type: text
Content-Transfer-Encoding: 7BIT
Content-Length: 2809

	Hello,
	We are developing an application in which we need to identify each
tuple in the database by a unique number.
	First, we thought about using postgres oid, but later we realized they
wouldn't help us, because when we modify data in one tuple, another tuple
is created with a different oid, and all other tuples refering it still refer
the old one.
	So we tried to implement an iid type (internal identifier type) and
a unique iid generator and identify each tuple by an iid only at cration time
(using a rule on append, not on replace).
	The problem came implementig the generator.
	We implemented it as a counter using a table to contain the actual
counter state and a function to increment it. Here is the code to implement the
iid generator:

****** 
       HERE GOES CODE TO DEFINE IID TYPE, AND FUNCTIONS TO OPERATE ON IT
       (INCREMENT AN IID, COMPARE IID'S ....
******

/* Define table to store iid sequence number. */
create iid_value(value= iid)
\g

/* Initialize with initial iid value. */
append iid_value(value= 0::iid)
\g

/* Don't let Modify iid value, by anyone but generate_iid. */
define rule NoIidValappd is
            on append to iid_value
       do instead nothing
\g
define rule NoIidValrep is
            on replace to iid_value
       do instead nothing
\g

/* Define function to generate a different iid each time it is called */
define function generate_iid(language="postquel",returntype= iid)
                as "retrieve (value= iidinc(iid_value.value))"
\g

	
	First it seem to work properly, but the problem is that when the
postmaster dies (a thing that happens frequently, we don't know the cause),
the counter seems to be reset, and it begins again with 1, generating duplicate
iid's !!!

	Does anybody know what is the problem?

	I read in the mail-list about problems implementing a counter, but
they seem to be problems with simultaneous access to the counter function.
	We didn't detect that problem with out counter.

	We thought about another solution to implement the unique number
generator: a C funtion wich returns the system clock time (in clock units from
a given specific date).

	The problem is that we need indexes on iid's and we don't know how
to implement the iid_ops needed for that index.
	Does anybody know how to do that? Where may I find documentation
about writing the operator to work with indexes on a user define type?

	Any idea would be welcome, as we are stuck now.
	Regards:
		Fernando,
	
-- 
Fernando Ariznavarreta Fernandez       E-Mail:fernando@etsimo.uniovi.es
Dpto. Explotacion y Prospeccion de Minas     Phone: +34 8 510 4267
School of Mines. University of Oviedo               +34 8 510 4336
Independencia, 13                            Fax:   +34 8 510 4242
Oviedo 33004, SPAIN                                 +34 8 510 4340