Subject: Managing key-value pairs

From: [email-masked] (Reinhard Dunkel)
Date: Sun, 21 Nov 1993 20:11:00 -0700

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           [email-masked]

---

From: [email-masked] (Jean-Paul Vetterli)
Date: Mon, 22 Nov 93 09:35:31 +0100

> From: [email-masked] (Reinhard Dunkel)
> Message-Id: <[email-masked]>
> 
> [...]
> [...]

One approach to enforce uniqueness is to use rules. 
Example: 

---- cut here ----
#  this is a spog script: %spog -f <thisfile>
#  it is idempotent 
#
#  create key-value table:
create kv (k = char16,\
	v = text)
#
#  uniqueness *and* integrity:
define rule kv_r is \
	on append to kv \
	where kv.k = new.k or new.k ISNULL \
	do instead nothing
#
#  data:
append kv (k = "K1", v = "K1's value")
append kv (k = "K2", v = "K2's value")
append kv (k = "K3", v = "K3's value")
append kv (k = "K4", v = "K4's value")
append kv (k = "K5", v = "etc.")
---- cut here ----

I hope this can help.

Jean-Paul Vetterli 		
Route des Pommiers 19		email:	[email-masked] 		
CH-1723 Marly			phone:	++77 34 38 27 		
Switzerland			fax:	++37 46 53 09