Subject: Postgres Gets Confused!

From: [email-masked]
Date: Tue, 25 Oct 1994 02:27:22 -0400 (EDT)

We are using Postgres to store the data for our information modeling
program.  In doing this, each user has his own PGDATA directory which is
served by his own postmaster (obviously running on different ports).  

The problem we keep seeing is that, after using the database
successfully for a period of time, Postgres seems to get confused and do
one of the following: 

1.) Claims the user is not a valid user even though they have been using
the system not 5 minutes before. 

2.) Claims the database does not exist even though they had been using
it not 5 minutes earlier and it is still listed under the base
subdirectory. 

Most of the time, I can cure this problem by logging in removing the
user and re-adding them.  Unfortunately, this does not always work and
we have had to destroy the PGDATA directory and recreate it from
scratch.  Which is definitely not desirable since it's not too useful
for a user to store their information in our system just to have it
destroyed because Postgres gets confused. 

Also, there seems to be a bug in the parser/planner.  First, it tells me
that I must use an explicit cast (see below), which makes perfect sense.
 Then, I use an explicit cast and it tells me that I cannot do cast this
expression (huh?).  Then, I rewrite the query to include another field
in the where clause, along with the EXACT same field that I tried to
cast in the previous query (which Postgres said I couldn't do) and it
works as expected. 

 paneer> monitor 
Welcome to the POSTGRES terminal monitor 

Go 
* retrieve (pg_operator.oprname) where pg_operator.oprleft = 16\g 

Query sent to backend is "retrieve (pg_operator.oprname) where
pg_operator.oprleft = 16" 
NOTICE:Oct 25 02:09:05:there is no operator = for types oid and int4 
NOTICE:Oct 25 02:09:05:You will either have to retype this query using an 
NOTICE:Oct 25 02:09:05:explicit cast, or you will have to define the operator 
WARN:Oct 25 02:09:05:= for oid and int4 using DEFINE OPERATOR 

Go 
* retrieve (pg_operator.oprname) where pg_operator.oprleft = 16::oid\g 

Query sent to backend is "retrieve (pg_operator.oprname) where
pg_operator.oprleft = 16::oid" 
WARN:Oct 25 02:09:17:parser_typecast: cannot cast this expression to
type "oid" 

Go 
* retrieve (pg_operator.oprname) where pg_operator.oprname = "<" and
pg_operator.oprleft = 16::oid\g 

Query sent to backend is "retrieve (pg_operator.oprname) where
pg_operator.oprname = "<" and pg_operator.oprleft = 16::oid" 
--------------- 
| oprname     | 
--------------- 
| <           | 
--------------- 

Go 
* 

Looks like a bug(s) to me, don't you think? 
Robert 

P.S.  I'm running Version 4.2 with all of the latest patches on OSF/1
1.3, OSF/1 2.0, HP-UX 9.03, SunOS 4.13, and Ultrix 4.2a.   All platforms
experience the same problems! 

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================

---

From: [email-masked] (Paul M. Aoki)
Date: Tue, 25 Oct 94 00:46:05 -0700

[email-masked] writes:
> user and re-adding them.

most of the time when i used to see this it was the funny PGUSER
environment variable corruption thing.  if you already patched around
that one then i'm not sure what it is.  try killing the postmaster and
restarting it (without blowing away the old database) and see if that
clears up the problem temporarily.  (there was a problem with the fact
that when you destroydb then createdb you sometimes leave the shared
memory buffer pool in a funny state.  i don't think this was ever
resolved.)

> expression (huh?).

shameful to say,
   retrieve (pg_operator.oprname) where pg_operator.oprleft = "16"::oid\g
works..
--
  Paul M. Aoki          |  University of California at Berkeley
  [email-masked]  |  Dept. of EECS, Computer Science Division (#1776) 
                        |  Berkeley, CA 94720-1776