How to prevent inserts in the parent table?

Question

There is a table t which has inherited children. I want that only the children can receive inserts. What is the best way to force that the parent table rejects inserts?

create table t (c int);
create table t1 () inherits (t);

This should not be possible:

insert into t (c) values (1);

EDIT:

I found a model visible solution in addition to the one from @wildplasser:

create table tfk (c integer unique check(false));
create table t (c integer, foreign key (c) references tfk(c));

Now it is not possible to insert into t UNLESS it is a null value, and still possible to insert into its children. It can be a good solution if that columnn is already constrained as not null but not enough otherwise. Or does someone know a trick to make the above work for null values?

News:

I asked for a new syntax in the postgresql list and it was done for 9.2:

Allow CHECK constraints to be declared NO INHERIT (Nikhil Sontakke, Alex Hunsaker)

This makes them enforceable only on the parent table, not on child tables.

Anaplasty

Have you tried permissions? Seems like just not granting the relevant user permission should work. – Sideline

@Sideline Even if a privilege is revoked from a role that role could still retain it if, say, public or another role of which it is member still has it. What I'm looking for is a definitive and unequivocal action. – Anaplasty

This seems more a philosophical problem than a practical requirement. If you ensure that only the owner has the right to insert, yet believe that right could "accidentally" be handed out, then so too could the right to remove any constraint or trigger you use to stop it. I understand you're looking for the PostgreSQL equivalent to an "abstract class" but there just isn't one. Looking into it triggers will work as they don't apply to child tables (although I previously thought otherwise). This will at least prevent accidental insertion. – Sniggle

@couling Dropping a constraint or trigger is an exclusive privilege of the owner (or superuser) and is an intentional data definition act, not an accident. An insert yes, can be an accident. This question is a very practical requirement. Just yesterday I, when logged as the table owner, accidentally inserted in the parent table which caused me lots of work to fix. An abstract class is a good figure here and could be part of good answer from you. Please read the faq before downvoting a question (revenge is not a reason to). – Anaplasty

In general permissions are the way to prevent insert on a table. The two answers you previously gave for permissions not being what you need (to me and to Robert) made no sense. The down vote was for not stating your requirements clearly. But thanks, I didn't actually know how down voted my answer. And thanks for stating your requirement clearly now. Add them to your question and I'll up vote it. Regards – Sniggle

@derobert: as mentioned in my answer below permissions won't help it unfortunately: https://mcmap.net/q/933929/-how-to-prevent-inserts-in-the-parent-table – Acima

Answer 1

You could use a before insert trigger to raise an error or redirect to the correct table.

Fahland

Answer 2

create table t (c int, check (false) no inherit);

This will prevent inserts into table t. It adds a constraint that will never be true, so no data can be inserted. no inherit will prevent that constraint from affecting child tables.

Meneses

That was only introduced in 9.2 and I pointed it in an update to my question. – Anaplasty

alter table my_master_tab add constraint chk_empty check ( false ) no inherit as an alter statement with a name for the constraint – Acima

Answer 3

This is ugly, but it appears to work:

--SET search_path='tmp';

DROP TABLE dontinsert CASCADE;
CREATE TABLE dontinsert
        ( id INTEGER NOT NULL PRIMARY KEY
        );

DROP TABLE doinsert CASCADE;
CREATE TABLE doinsert ()
        INHERITS (dontinsert)
        ;

CREATE RULE dont_do_it AS
        ON INSERT TO dontinsert
        DO INSTEAD NOTHING
        ;

INSERT INTO dontinsert(id) VALUES( 13) ;

INSERT INTO doinsert(id) VALUES( 42) ;

SELECT id AS id_from_dont FROM dontinsert;

SELECT id AS id_from_do FROM doinsert;

Result:

SET
NOTICE:  drop cascades to table doinsert
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dontinsert_pkey" for table "dontinsert"
CREATE TABLE
ERROR:  table "doinsert" does not exist
CREATE TABLE
CREATE RULE
INSERT 0 0
INSERT 0 1
 id_from_dont 
--------------
           42
(1 row)

 id_from_do 
------------
         42
(1 row)

UPDATE: since the OP wants INSERTSs to fail with a lot of noise, I had to add a canary-table with an impossible constraint imposed on it:

DROP TABLE alwaysempty CASCADE;
CREATE TABLE alwaysempty
        ( id INTEGER NOT NULL
        );

ALTER TABLE alwaysempty
        ADD CONSTRAINT dont_insert_you_sucker CHECK (id > 0 AND id < 0)
        ;

CREATE RULE dont_do_it AS
        ON INSERT TO dontinsert
        DO INSTEAD -- NOTHING
        INSERT INTO alwaysempty (id)
        VALUES (NEW.id)
        ;

The new output:

SET
NOTICE:  drop cascades to table doinsert
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dontinsert_pkey" for table "dontinsert"
CREATE TABLE
ERROR:  table "doinsert" does not exist
CREATE TABLE
DROP TABLE
CREATE TABLE
ALTER TABLE
CREATE RULE
ERROR:  new row for relation "alwaysempty" violates check constraint "dont_insert_you_sucker"
INSERT 0 1
 id_from_dont 
--------------
           42
(1 row)

 id_from_do 
------------
         42
(1 row)

Next attempt: move (ONLY) the basetable into an unreacheable schema (since I really hate triggers) ...

SET search_path='tmp';

DROP SCHEMA hidden CASCADE;
CREATE SCHEMA hidden;
REVOKE ALL ON SCHEMA hidden FROM PUBLIC;

DROP TABLE dontinsert CASCADE;
CREATE TABLE dontinsert
        ( id INTEGER NOT NULL PRIMARY KEY
        );

DROP TABLE doinsert CASCADE;
CREATE TABLE doinsert ()
        INHERITS (dontinsert)
        ;

ALTER TABLE ONLY dontinsert SET SCHEMA hidden;

INSERT INTO alwaysempty (id) VALUES (NEW.id) ;

INSERT INTO dontinsert(id) VALUES( 13) ;

INSERT INTO doinsert(id) VALUES( 42) ;

SELECT id AS id_from_dont FROM hidden.dontinsert;

SELECT id AS id_from_do FROM doinsert;

Cerebrum

I don't think a rule is ugly. Indeed I see it as more elegant in its simplicity than a trigger. So I voted you answer up. But I want an exception to be raised on insert which is not possible for a rule. Or is it? – Anaplasty

Well, you can do almost everything with a rule. I'll look into it. – Cerebrum

Problem with a rule is it rewrites queries. Test it with copy from stdin, and it should then insert. A trigger is the answer. – Felicitous

Problem with copy is that it doesn't rewrite queries. Usage of COPY is more-or-less restricted to DBA's; which are supposed to know what they are doing. – Cerebrum

Not true. copy from stdin can be run by anyone with permission to insert on the table. – Felicitous

Thank you for your effort. It (and the comments from @Scott) really helps to highlight rules vs triggers. If I could upvote twice I would do it. but although your solutions work they got almost as complex as a trigger without the benefit of being the book solution. – Anaplasty

@Scott I clearly see your point about rules and it rules rules out for me :) since copy from should also be a Big No. It is just that your wording was not very right. I would rewrite it as "Problem with a rule is as it just rewrites queries it does not prevent copy from from doing the damage". – Anaplasty

The hidden schema trick is cleaner and does not need triggers. – Cerebrum

Your solution is based on revoking the rights from the schema hidden. How is that superior to revoking the rights from the table dontinsert? Same level of "security", isn't it? As an aside: simplify the "always FALSE"-constraint to ALTER TABLE t ADD CONSTRAINT dont_insert_you_random_insult CHECK (FALSE); – Structural

It appears that a revoke on the parent table is inherited by al its child tables (an ONLY clause is rejected), even is the constraint is added later. The SCHEMA hack appears to escape this scrutiny. – Cerebrum

Thing is, triggers only look complex and scary the first time or two. Honestly they're not that hard. Put the create or replace trigger / function stuff in a script and it's super easy to update / debug / build triggers for a purpose. Warning!!! Once you start writing triggers you may go through a period of "trigger-craziness" where you start writing triggers for all kinds of stuff. This is normal. :) – Felicitous

Been there. Actually, I am beyond triggers Triggers make your database act like a spreadsheet on steroids. If there is another way to reach your goal: avoid them. The real bad thing about triggers is that they cannot be imposed by the database model or schema. To the model, they are invisible. They are an extra hidden layer. – Cerebrum

Answer 4

update: the revoke update etc is actually bad, since it will make updates fail and not auto-direct them to the partitions :-( ... so the best way seems to be @JoeVanDyk s simple always-false check condition to me.

---

as @derobert already mentioned in a comment, it seems good to me, considering overhead, speed and practicability, to do this via rights:

revoke insert, update, references on  my_master_table  from  my_upd_usr

(references because it won't work as intended if used: inheritance caveats)

(Of course, giving some user the rights to insert into the master accidentially seems possible, e.g. by giving some the rights to do so for all tables, but I guess its most often unlikely to give somebody "accidentially" rights for DML operations. And even if this someone had the rights, why should he start inserting into an empty table seeing that it is not used this way and the data resides in partitions.)

Acima