Hi,

I need to include a column in a table that *references*
another table. I.e., the type of the column would be
"name of table".

Furthermore, I want to constrain the value in that column
to ensure that it represents an *existing* table. And,
ensure that the referenced table is not removed while
it is thusly referenced.

I *think* creating a column of type "name" and tying it
to pg_tables.tablename satisfies the first criteria.
But, how do I ensure the RDBMS prevents the deletion
of the referenced table while any such reference exists?
(short of manually writing TRIGGERs for anything that
could cause a DROP)

Thanks!
--don

On 2011-01-20, D Yuniskis <not.going.to.be (AT) seen (DOT) com> wrote:
the postgres type 'name' seems suitable here

That's not going to happen, Tom Lane (who is spokesman if not leader
of the development team) has repeatedly said there is no support for
triggers on relations in the information or pg_catalog schemas, nor
are there future plans to support that.

a references constraint would normally block the changing of that column
or removal of that row, and thus block deleting the table, but

you're not allowd to create references constraints to or triggers on
system catalogs.

jasen=# create table loki(t name,ns oid,foreign key (t,ns) references
pg_catalog.pg_class(relname,relnamespace));
ERROR: permission denied: "pg_class" is a system catalog

this means you'll need to handle the deletions after the fact, or
constrain the ordinary users into only deleting tables using a
certain method you define.

--
⚂⚃ 100% natural

Hi Jasen,

On 1/23/2011 4:28 AM, Jasen Betts wrote:
Sorry, I was perhaps not precise enough in my question.

I realize there are no EXISTING mechanisms to do what *I*
want. But, note that what *I* want isn't as "general"
as the above statement requires.

Since I have a *specific* requirement, I am looking to
identify any issues that would prevent me from *imposing*
this behavior on a PG deployment. E.g., it need not
look like a generic PG deployment (it is for use in a
set of embedded systems so *I* control all access to
the PG running therein).

For example, I could impose a front end that intercepts
{CREATE,DROP,ALTER} TABLE requests and rewrites them
prior to passing them on to PG. That front end could
be free to use whatever resources it needs to impose
this extra constraint (i.e., it's own idea of "system
catalogs") as the performance hit would be justifiable.

Since all I am doing is adding one capability/restriction
(and not arbitrarily allowing "any" trigger or relation
involving system tables), the problem's scope is considerably
less than expecting/requiring PG to handle system catalogs
*identically* to "regular" tables.

To that end, I am trying to identify any subtleties that could
*unintentionally* circumvent this sort of approach. (remember,
*I* control access to the RDBMS so all I have to do is "protect
me from myself")

"After the fact" isn't an option. If a table thusly referenced is
deleted, the device crashes. Sort of like letting something erase
the VTOC on a drive and trying to "clean up" later (i.e., "hoping"
all the other applications referencing that drive will be able to,
somehow, compensate.)

Are there other RDBMS offerings that might have this ability?
(I should probably look at Oracle to see what they have but
licensing negotiations would eat up *way* too much time. And,
porting to my environment would add even more snags! :< )
If push comes to shove, I can fall back to something "crude"
(e.g., dbm) and track all the relations "manually" :-/

Apologies, in advance, if I am slow to respond as I will be
traveling...

--don

On 2011-01-24, D Yuniskis <not.going.to.be (AT) seen (DOT) com> wrote:
rewriting of requests is what RULES do in PG

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

The event is one of SELECT, INSERT, UPDATE, or DELETE.

I don't know how much work would need to be done on the source (which
is freely available) to get DROP and ALTER added to that list.

trying to go that way is dependant on the system tables being
sufficiently like ordinary tables. and the code being organised in
such a way that supporting triggers on a system table doesn't
immediately lead to infinite recursion.

So instead constrain the ordinary users into only
deleting tables using a certain method you define.

that's easy. if the user of the normal database connection doesn't
match the owner of the table (or schema or database) they can't drop
the table, using the "DROP TABLE" command. but if you provide a
function with "SECURITY DEFINER" owned by rhe owner of the table, and
this fnction does the "DROP TABLE", then in this function you can do
the extra checks needed before dropping the table.

you also need to constrain them from creating tables that they own
(easy: don't grant them create table permission)

and if necessary provide a similar interface to create and alter
tables

I only know postgres in sufficient detail to offer useful comment on
it..

--
⚂⚃ 100% natural

Hi Jasen,

On 1/27/2011 3:06 AM, Jasen Betts wrote:
I don't think doing this *in* PG itself would be a practical
option. I think the problem is more complex than "simply"
rewriting the request.

E.g., you would have to intercept table create/alter commands
(or actively snoop the system catalogs looking for instances
of a special "name of table" type) to determine where the
references are (and, to what they point). Along with updates
on those columns, etc.

Then, trap drops to be sure the referenced tables aren't
dropped, etc.

I.e., you would build an agent that embodied that
information in much the same way you would *hope* a
"compliant" user would behave.

Yes. An agent would have to watch for circular references,
etc. But, that is a manageable task.

DROP CASCADE is a hassle as you can't casually determine
from the request whether or not it will (eventually) drag
one of these tables/references into the mess.

There are two different classes of use (abuse?) that have to
be handled. It is relatively easy to prevent someone from
dropping something that they don't "own". The tougher
problem is to prevent/discourage the owner of a resource
from dropping it while others might be referencing it.

Yet, an outright prohibition on that sort of thing is
equally "wrong".

Any data that an "agent" maintains can be useful in allowing
(ahem) "users" to determine the potential consequences of
these sorts of actions and modify or *enforce* those actions
accordingly.

Understandable. I'll see what I can come up with for an
algorithm to implement this in an external agent. Then, I
can get a better feel for the costs associated with it.

Thanks for your comments!