Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199311220308.TAA16223@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Date: Sun, 21 Nov 1993 20:11:00 -0700
From: dunkel@spin.chem.utah.edu (Reinhard Dunkel)
Message-Id: <9311220311.AA21809@spin.chem.utah.edu>
To: postgres@postgres.Berkeley.EDU
Cc: dunkel@spin.chem.utah.edu
Subject: Managing key-value pairs 
Resent-To: postgres-dist@postgres.Berkeley.EDU
Resent-Date: Sun, 21 Nov 93 19:08:29 -0800
Resent-XMts: smtp

I am trying to use Postgres 4.1 (RS/6000-370, AIX 3.2.4) to manage
key-value pairs for a graphical user interface. I am embarrassed to
admit that I have problems to make this easiest of all database
applications work smoothly. I would greatly appreciate any help on the
following problems:

PROBLEM 1: I have to enforce that my "param" attribute values (keys)
are unique in a database relation.  However, the command "create foo
(param=char16, value=text) key (param)\g" is documented in the
reference manual but the message "WARN:Nov 21 18:10:13:RelationCreate:
KEY not yet supported" sounds discouraging. So before writing a
key-value pair I query the database to see if the key is known and
then either replace or append the tupel.  This approach takes two
database accesses (one second overhead with libpq each). Is there a
better way to do this?

PROBLEM 2: The purge command in Postgres 4.1 is broken. How can I delete 
from time to time the history information of a relation? Something along 
the lines of:
     retrieve into tmp unique (foo.all)\g
     destroy foo\g
     rename tmp to foo\g
is slow and I am concerned that a system crash during this sequence
might leave the database in an undefined state. (I had problems with
bracketing these statements with "begin" and "end".) 

PROBLEM 3: With 'copy foo to "file.ASCII"' the contents of the
database can be written to a flat ASCII file. But how do I merge
key-value pairs from an ASCII file back into a relation so that (1)
new key-value pairs are appended to the relation, (2) pairs involving
known keys are used to replace key-value pairs in the relation, and
(3) the tupel history stays correct. Is there an easy way to do this -
if at all possible without using a temporary relation?

Any help would be greatly appreciated! Thanks.
   __                                      _
  |  )      o       |              |      | \            |        |
  | /    _          |_   _    _   _|      |  |           |     _  |
  |/\   /_) |  ^ ^  | |  _)  |   / |      |  | | |  ^ ^  |_)  /_) |
  |  \_/\___|_| | |_| |_(_|__|___\_|      |_/__|_|_| | |_| \_/\___|

  Department of Chemistry            Office: (801) 581-7351         
  2020 Henry Eyring Building b113    Home:   (801) 582-7516         
  University of Utah                 FAX:    (801) 581-8433         
  Salt Lake City, UT 84112           dunkel@chemistry.chem.utah.edu