Thread: Best way to restrict detail rows?

I have a "master-detail" kind of situation, as illustrated here:

CREATE TABLE master(id SERIAL PRIMARY KEY,foo TEXT
);

CREATE TABLE detail(id SERIAL PRIMARY KEYmaster BIGINT NOT NULL REFERENCES master(id),bar TEXT
);

(this is a simplification, of course)

I would like a way to restrict the addition of new detail records, but  
only after the initial detail records have been inserted into the  
system.  Ideally, I'd like to start a transaction, enter the master  
record and its detail records, then commit the transaction, which  
would then lock these records so no more detail records can ever be  
added.  As a concrete example, you might think of the master record as  
an Order, while the details are Line Items for an order.  When  
initially creating the Order, I'd like to add as many Line Items as  
necessary, but once the Order is entered, I don't want anybody to be  
able to add more Line Items.

Is there a way to do this straightforwardly?  It seems like I might be  
able to use a BEFORE INSERT ROW trigger on the detail table to see if  
there are already any detail records for the master row.  If there are  
no detail records, then I can go ahead and add.  If there are detail  
records, then maybe I could use a pl/perl hash to carry information  
around as to whether or not those records were added in the current  
transaction, and then decide whether to insert based on that.  That  
seems rather cumbersome and baroque, though.

Thanks in advance for any suggestions,

Chris

On Mon, Dec 8, 2008 at 1:56 PM, Christopher Maier <maier@med.unc.edu> wrote:
> I have a "master-detail" kind of situation, as illustrated here:
>
> CREATE TABLE master(
>        id SERIAL PRIMARY KEY,
>        foo TEXT
> );
>
> CREATE TABLE detail(
>        id SERIAL PRIMARY KEY
>        master BIGINT NOT NULL REFERENCES master(id),
>        bar TEXT
> );
>
> (this is a simplification, of course)
>
> I would like a way to restrict the addition of new detail records, but only
> after the initial detail records have been inserted into the system.

After you create the table do something like this:

create rule detail_no_insert as on insert to detail do nothing;
create rule detail_no_update as on update to detail do nothing;

poof.  no more updates or inserts work.  Note that copy will still
work, as it doesn't fire rules.  So, you can update the data with
copy, and otherwise not touch it.

On Mon, Dec 8, 2008 at 2:28 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Dec 8, 2008 at 1:56 PM, Christopher Maier <maier@med.unc.edu> wrote:
>> I have a "master-detail" kind of situation, as illustrated here:
>>
>> CREATE TABLE master(
>>        id SERIAL PRIMARY KEY,
>>        foo TEXT
>> );
>>
>> CREATE TABLE detail(
>>        id SERIAL PRIMARY KEY
>>        master BIGINT NOT NULL REFERENCES master(id),
>>        bar TEXT
>> );
>>
>> (this is a simplification, of course)
>>
>> I would like a way to restrict the addition of new detail records, but only
>> after the initial detail records have been inserted into the system.
>
> After you create the table do something like this:
>
> create rule detail_no_insert as on insert to detail do nothing;
> create rule detail_no_update as on update to detail do nothing;
>
> poof.  no more updates or inserts work.  Note that copy will still
> work, as it doesn't fire rules.  So, you can update the data with
> copy, and otherwise not touch it.
>

whoops!  do INSTEAD nothing.

-- 
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis

On Mon, Dec 8, 2008 at 1:28 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Dec 8, 2008 at 1:56 PM, Christopher Maier <maier@med.unc.edu> wrote:
>> I have a "master-detail" kind of situation, as illustrated here:
>>
>> CREATE TABLE master(
>>        id SERIAL PRIMARY KEY,
>>        foo TEXT
>> );
>>
>> CREATE TABLE detail(
>>        id SERIAL PRIMARY KEY
>>        master BIGINT NOT NULL REFERENCES master(id),
>>        bar TEXT
>> );
>>
>> (this is a simplification, of course)
>>
>> I would like a way to restrict the addition of new detail records, but only
>> after the initial detail records have been inserted into the system.
>
> After you create the table do something like this:
>
> create rule detail_no_insert as on insert to detail do nothing;
> create rule detail_no_update as on update to detail do nothing;
>
> poof.  no more updates or inserts work.  Note that copy will still
> work, as it doesn't fire rules.  So, you can update the data with
> copy, and otherwise not touch it.


One Idea that popped into my head that may-or-may-not work would be to
add a constraint trigger that checks if all of the detail records have
the same xmin as the order table record.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Mon, Dec 8, 2008 at 2:31 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> One Idea that popped into my head that may-or-may-not work would be to
> add a constraint trigger that checks if all of the detail records have
> the same xmin as the order table record.

Yes, it's not as simple as I first thought when I read it.

I'd look at using a udf that used a sec definer that only it had to do
the row adds and do everything at once, inserting to both tables at
the time of the creation of the order.