A view just stopped working out of the blue...

From: Don Baccus <dhogaza(at)pacifier(dot)com>
To: pgsql-hackers(at)postgreSQL(dot)org (PostgreSQL HACKERS)
Subject: A view just stopped working out of the blue...
Date: 1999-12-06 19:24:43
Message-ID: 3.0.1.32.19991206112443.0100d9e0@mail.pacifier.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have the following table and view:

create table users (
user_id integer not null primary key,
first_names varchar(50) not null,
last_name varchar(50) not null,
password varchar(30) not null,
email varchar(50) not null unique,

census_rights_p boolean default 'f',
locale_rights_p boolean default 'f',
admin_rights_p boolean default 'f',

-- to suppress email alerts
on_vacation_until date,

-- set when user reappears at site
last_visit datetime,
-- this is what most pages query against (since the above column
-- will only be a few minutes old for most pages in a session)
second_to_last_visit datetime,

registration_date date,
registration_ip varchar(50),
user_state varchar(100) check(user_state is null or
user_state in ('need_email_verification_and_admin_approv', 'n
eed_admin_approv', 'need_email_verification', 'rejected', 'authorized',
'banned', 'deleted')
),
deleted_p boolean default 'f',
banned_p boolean default 'f',
-- who and why this person was banned
banning_user integer,
banning_note varchar(4000),
portrait_loaded boolean default 'f',
portrait_type varchar(10) default ''
);

-- Create an "alert table" view of just those users who should
-- be sent e-mail alerts.

create view users_alertable
as
select *
from users
where (on_vacation_until is null or
on_vacation_until < 'now'::date)
and (deleted_p = 'f');

This has been working for months, just fine. I've been porting over a bunch
more stuff from Oracle to this Postgres-based system, and bam! Now any
select from the view dies with:

unknown node tag 600 in apply_RIR_view

I've tried dropping and rebuilding the table and view in a test database
and the problem remains. I recall running into problems with other
operations many moons ago, where a particular node type wasn't being
handled by a particular operator (the ones I'd seen previously were
fixed by the excellent 6.5.* versions).

Is this a similar case? I may do a little digging myself tonight, but
thought I'd ask to see if this rings a bell with anyone. It's a bit
strange because this view's been working great on this table for so
long. I added a couple of extra columns to the table recently but
the view worked immediately afterwards. The stuff I've been porting
creates views willy-nilly and it's almost like there's an interaction
taking place, but that doesn't seem right.

It fails in the same manner if I simply declare the view as:

create view users_alertable as select * from users;

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jan Wieck 1999-12-06 19:34:23 Re: [HACKERS] A view just stopped working out of the blue...
Previous Message Jan Wieck 1999-12-06 18:20:10 FOREIGN KEY and shift/reduce