Subject: some questions about postgres v3r1

From: [email-masked] (Juergen Schoenwaelder)
Date: Tue, 28 Jan 92 11:07:45 MET

Hi!

We are looking at postgres for some time and we have some questions
about it. (When I refer to postgres I mean postgres v3r1 on a sparc
under sunos 4.1.)

1) How can we retrieve attributes with a NULL value?

	   create foo(a1=int4, a2=int4)
	   append foo(a1=7)
	   append foo(a1=9,a2=0)

	   retrieve (foo.all) where foo.a2 = NULL

   This query just dumps core (in function make_op in file 
   $POSTGRESHOME/src/parser). But a replace operations works:

	   replace foo(a1=NULL) where foo.a1=9

	   retrieve (foo.all) where foo.a2=0

   This last retrieve returns both tuples which I think is not correct.

2) How can we return an error code when parsing of an adt fails?

   For example, we would like to add an adt for an ip address.
   Our input function parses a string of the form "a.b.c.d",
   where a,b,c and are in the range from 0...255. If someone
   inputs "nase" to our input function we would like to get
   postgres to return a type conflict.

3) We are using the mechanism for accessing time varying data. We have some
   problems when some data is stored in the current relation and some have 
   moved to the archive. At the end of this mail I have put a shell script 
   that fills a relation foo, moves some tuples to the archive and fills foo 
   again. The retrieve queries at the end of this script return unexpected
   results:

   foo.count: 

	---------------
	| 20          |
	---------------
	| 10          |
	---------------

   foo.dump contains 30 tuples and foo.dump.sorted contains 21 tuples.

4) The last question deals with our plan of storing long term statistical
   data in a postgres db. Since we are using the time varying data feature
   we would like to know if we could expect the disk format of our data 
   base to be compatible with future postgres releases. And if the format
   will change, how can we port time varying data to a new storing
   format (without loosing our tmin, tmax settings)?

5) When will recursive queries work (retrieve, addattr)? Which release
   will do the job and when will it be available?

Thanks in advance for answering my questions,
						Juergen

---
Juergen Schoenwaelder   ([email-masked])
Technische Universitaet Braunschweig, Inst. f. Betriebssysteme & Rechnerverbund
Bueltenweg 74/75,  3300 Braunschweig, Germany, Tel. +49 531 / 391-3249

#! /bin/sh
#
# create and fill a foo relation
#

DBNAME=nunix

# check if there is already a relation foo
QUERY='retrieve (a.relname) from a in pg_class where a.relname = "foo"'
answer=`monitor -N -c "$QUERY" $DBNAME | awk -F\| '/^\|/ {print }'`

if [ "$answer" = "" ] ; then
	# create relation foo
	QUERY="create foo(name=char16,val=int4) archive = heavy"
	monitor -N -c "$QUERY" $DBNAME

fi

# append one tuple
QUERY="append foo(name=\"number\",val=0)"
monitor -N -c "$QUERY" $DBNAME

# fill relation foo
n=20
while [ $n -gt 0 ] ; do
 	QUERY="replace foo(val=$n) where foo.name = \"number\""
  	monitor -N -c "$QUERY" $DBNAME
  	n=`expr $n - 1`
done

# now move tuples to archive
vacuum $DBNAME

# and fill foo again
n=9
while [ $n -gt 0 ] ; do
        QUERY="replace foo(val=$n) where foo.name = \"number\""
        monitor -N -c "$QUERY" $DBNAME
        n=`expr $n - 1`
done

QUERY='retrieve (f.val) from f in foo[,]'
monitor -N -c "$QUERY" $DBNAME > foo.dump

QUERY='retrieve (f.val) from f in foo[,] sort by val'
monitor -N -c "$QUERY" $DBNAME > foo.dump.sorted

QUERY='retrieve (x=count{f.val}) from f in foo[,]'
monitor -N -c "$QUERY" $DBNAME > foo.count

# end of foo

---

From: postarch (Postgres Mailing Archive)
Date: Tue, 28 Jan 92 12:12:28 PST

>   This last retrieve returns both tuples which I think is not correct.

There is no easy way to do this as Postgres does not know enough about
null attributes.  The necessary keywords are reserved in the parser, but
the executor needs some serious work in this area.  As of now null attrs
appear as 0 for pass by value attributes and null pointers for pass by
reference attributes (the latter characteristic can cause a core dump in
routines that manipulate the data if they do not check for null first).

>   postgres to return a type conflict.

elog(WARN, "input string %s for type 'der_mund' is wrong", inputstr);

This will print the string (with printf semantics) and abort the current
transaction.

>   again. The retrieve queries at the end of this script return unexpected

Will look into this.

>   format (without loosing our tmin, tmax settings)?

Being a research group and having our product so competitively priced means
that we don't have the motivation or man power to provide many commercial
niceties (e.g. backward compatibility).  To move from one Postgres version 
to the next we usually recommend that you copy your databases out to UNIX 
files and back in once the new release is installed.  This of course plays 
havoc with dependencies on system defined attributes such as tmin, tmax 
and oid.  We have had to ask users to live with these deficiencies.  If the
tuple layout does not change from one release to the next then backward
compatibility *should* come for free.  But again, we don't verify or ensure
this.

>   will do the job and when will it be available?

Recursive query handling was added to the system a few of releases ago,
but problems with the implementation caused it to be phased out
completely since then.  There are currently no plans to implement them
again. i.e. not for version 4 due out in March.  Beyond that I cannot
say.

Jeff Meredith
[email-masked]