Return-Path: mao
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA25070; Tue, 25 Feb 92 11:25:19 -0800
Message-Id: <9202251925.AA25070@postgres.Berkeley.EDU>
From: mao@postgres.Berkeley.EDU (Mike Olson)
Subject: Re: Simple oid question
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
In-Reply-To: Your message of Tue, 25 Feb 92 10:54:41 PST.
             <9202251856.AA24511@postgres.Berkeley.EDU> 
Date: Tue, 25 Feb 92 11:27:05 PST

just fyi:

> Oid's are unique across the entire database.

in fact, we have a couple of duplicates in the system catalogs, but not
in places where it matters.  they arose over the years, from people not
being careful about values they chose for new oids.  once you install
the system, no new oid will ever be assigned to more than one object in
the database.

> > Question about indices:
> > 
> > 	I'm running version 3.0 currently and according to the docs only
> > 	one key is allowed for a given index.  Is this restriction
> > 	lifted in the upcoming 4.0 release?
> 
> No, sorry.

it's possible to define a functional index on two or more attributes,
and get this to work okay.  it's a little convoluted, but here is what
i do in the inversion file system:

	define a new data type, 'oidseq', that consists of an oid and
	    an int4 (sequence number):

		typedef struct oidseq {
		    oid   os_oid;
		    int4  os_seqno;
		} oidseq;

	    and use 'define type' with input and output functions as
	    appropriate;

	define a function mkoidseq() that takes an oid and an int4 and
	    returns an instance of oidseq;

	define a new operator class for btrees called 'oidseq_ops' that
	    handles ordering on oidseqs --  major key is the oid, minor
	    key is the int4;

	finally, define a functional index:

	    define index mao_index on mao_files using btree
		(mkoidseq(foid, fseqno) oidseq_ops)

so with a little trouble, you can get multi-key indices.  the problem
is that the optimizer won't consider them for scans on only one of the
two keys; you need to query explicitly mkoidseq() of an oid,seqno pair.
however, if you plan to run queries of the form

	retrieve mao_files.fdata
	    where mkoidseq(mao_files.foid, mao_files.seqno) >= "10,0"::oidseq
	      and mkoidseq(mao_files.foid, mao_files.seqno) < "11,0"::oidseq

the optimizer does the right thing.  i use these indices for fast multi-key
lookup on data blocks for inversion files.

					mike