pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bradley Kieser <brad@kieser.net>
To: PgSQL ADMIN <pgsql-admin@postgresql.org>
Subject: (resend) NULL constrains inheritance to views
Date: Thu, 06 Jan 2005 17:05:42 +0000
Message-ID: <41DD6FE6.1090306@kieser.net> (raw)

(resend as my other mail seems to not have gotten through)

I have created a schema with views on top of tables. We use JDBC to 
connect to the schema and we use JBuilderX to develop our app.

Every table as an "id" column of type serial. I.e. It is not null with 
default nextval(...).
However, it seems that when a column from a table is used in a view, the 
not null part (as well as the default values) seems to be dropped.
Serials become integers.

I can use "alter table" to restore default values on the views, but 
"alter table" to make a view column not null results in an error saying 
that the viewname is not a table! I.e. I don't seem to be able to make 
the column not null.

I think that this is throwing JBuilder's resolver into chaos because it 
doesn't use the column that I have specified as ROWID (column "id" for 
each view) to resolve update rows back to the view, it tries to use 
every single column, a sure sign that it thinks that the column I have 
told it is a rowid isn't up to the job. I am pretty certain that it's 
because it thinks that column can be null.

Unfortunately I have to use views and cannot use tables because of 
security in the app. The views have rules set on them as well as 
permissions to specific users. So the answer isn't to open up the 
database and use the underlying tables instead. That would open a 
security hole in the app. Moreover, I use views to do complex processing 
in PG (e.g. multi-table joins or rules and triggers on updates to ensure 
data integrity and implement business logic). JBuilder, being a RAD is 
great at building apps from simple datasets (i.e. single views and 
tables), but it quickly starts to wilt under complex parametised queries!

So for these reasons I need to find a way to tell JBuilder (through 
JDBC) that the ID columns are not nullable. But how? Can someone please 
help me?

Thanks,

Brad



view thread (2+ messages)  latest in thread

Message-ID: <41DD6FE6.1090306@kieser.net>
Permalink:  ../41DD6FE6.1090306@kieser.net/
Also on:    postgresql.org/message-id/41DD6FE6.1090306@kieser.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: brad@kieser.net
  Subject: Re: (resend) NULL constrains inheritance to views
  In-Reply-To: <41DD6FE6.1090306@kieser.net>
  References: <41DD6FE6.1090306@kieser.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox