Re: Single Row Table?

Hi,

I have got a table which is supposed to contain only one row. It does
not have any primary keys defined.
So, essentially, when a new insert happens in that table, I would like
it (the insert) to fail if there is already a row existing in that
table.
How can I do that? Can I add any constraints? Or do I need to write a
separate trigger for the same?

Thanks and regards,

Yateen V. Joshi

On Fri, Aug 27, 2004 at 13:32:07 +0530,
Yateen Joshi <yjoshi(at)starentnetworks(dot)com> wrote:
> Hi,
>
> I have got a table which is supposed to contain only one row. It does
> not have any primary keys defined.
> So, essentially, when a new insert happens in that table, I would like
> it (the insert) to fail if there is already a row existing in that
> table.
> How can I do that? Can I add any constraints? Or do I need to write a
> separate trigger for the same?

A simple way to force this is to add a primary key and a constraint
that forces the primary key to be a particular value.

On Sun, 2004-08-29 at 15:30, Bruno Wolff III wrote:
> On Fri, Aug 27, 2004 at 13:32:07 +0530,
> Yateen Joshi <yjoshi(at)starentnetworks(dot)com> wrote:
> > Hi,
> >
> > I have got a table which is supposed to contain only one row. It does
> > not have any primary keys defined.
> > So, essentially, when a new insert happens in that table, I would like
> > it (the insert) to fail if there is already a row existing in that
> > table.
> > How can I do that? Can I add any constraints? Or do I need to write a
> > separate trigger for the same?
>
> A simple way to force this is to add a primary key and a constraint
> that forces the primary key to be a particular value.

Is it reasonable / possible to add a check constraint something like
select count(*) from table <=1?

On Sun, Aug 29, 2004 at 15:38:45 -0600,
Scott Marlowe <smarlowe(at)qwest(dot)net> wrote:
> On Sun, 2004-08-29 at 15:30, Bruno Wolff III wrote:
> > On Fri, Aug 27, 2004 at 13:32:07 +0530,
> > Yateen Joshi <yjoshi(at)starentnetworks(dot)com> wrote:
> > > Hi,
> > >
> > > I have got a table which is supposed to contain only one row. It does
> > > not have any primary keys defined.
> > > So, essentially, when a new insert happens in that table, I would like
> > > it (the insert) to fail if there is already a row existing in that
> > > table.
> > > How can I do that? Can I add any constraints? Or do I need to write a
> > > separate trigger for the same?
> >
> > A simple way to force this is to add a primary key and a constraint
> > that forces the primary key to be a particular value.
>
> Is it reasonable / possible to add a check constraint something like
> select count(*) from table <=1?

It should be possible to do that with an after trigger, but it is a bit
more work to set up. As long as the table gets vacuumed, either way
is probably reasonably fast.

Yateen Joshi wrote:

> Hi,
>
>
>
> I have got a table which is supposed to contain only one row. It does
> not have any primary keys defined.
>
> So, essentially, when a new insert happens in that table, I would like
> it (the insert) to fail if there is already a row existing in that table.
>
> How can I do that? Can I add any constraints? Or do I need to write a
> separate trigger for the same?
>
>
>
> Thanks and regards,
>
>
>
> Yateen V. Joshi
>
>
>
You could try:

id INT PRIMARY KEY NOT NULL DEFAULT(1) CHECK (id = 1),

Tim

--- Scott Marlowe <smarlowe(at)qwest(dot)net> wrote:

> On Sun, 2004-08-29 at 15:30, Bruno Wolff III wrote:
> > On Fri, Aug 27, 2004 at 13:32:07 +0530,
> > Yateen Joshi <yjoshi(at)starentnetworks(dot)com> wrote:
> > > Hi,
> > >
> > > I have got a table which is supposed to contain
> only one row. It does
> > > not have any primary keys defined.
> > > So, essentially, when a new insert happens in
> that table, I would like
> > > it (the insert) to fail if there is already a
> row existing in that
> > > table.
> > > How can I do that? Can I add any constraints? Or
> do I need to write a
> > > separate trigger for the same?
> >
> > A simple way to force this is to add a primary key
> and a constraint
> > that forces the primary key to be a particular
> value.
>
> Is it reasonable / possible to add a check
> constraint something like
> select count(*) from table <=1?

ISTM most natural to do this with a rule, e.g.:

CREATE RULE my_insert_rule AS ON INSERT TO my_table DO
INSTEAD NOTHING;

Which will cause all inserts to be silently dropped.
If you want to return a message to the application,
you could use a statement trigger, which I believe we
don't have yet, or you could use a rule like:

CREATE RULE my_insert_rule AS ON INSERT TO my_table DO
INSTEAD SELECT 'Inserts to my_table not allowed!';

Although the application may not be expecting a return
message, and might not handle it.

>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
> joining column's datatypes do not match
>

_______________________________
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
http://promotions.yahoo.com/goldrush

> ISTM most natural to do this with a rule, e.g.:
>
> CREATE RULE my_insert_rule AS ON INSERT TO my_table DO
> INSTEAD NOTHING;
>
> Which will cause all inserts to be silently dropped.

This strikes me as bad programming practice. Errors should be reported,
not silently ignored. If the application is doing an insert when it doesn't
need to, then the application is flawed as well.
--
Mike Nolan

Tim Penhey wrote on 30.08.2004 23:12:

>> I have got a table which is supposed to contain only one row. It does
>> not have any primary keys defined.
>>
>> So, essentially, when a new insert happens in that table, I would like
>> it (the insert) to fail if there is already a row existing in that table.
>>
>>
> You could try:
>
> id INT PRIMARY KEY NOT NULL DEFAULT(1) CHECK (id = 1),
>

I like that approach :)

But should you also prevent DELETE's from that table? Otherwise you could
wind up with no rows at all. I guess that would have to be done using a rule...

Thomas

> But should you also prevent DELETE's from that table? Otherwise you could
> wind up with no rows at all. I guess that would have to be done using a rule...

Why not just revoke the delete privilege?
--
Mike Nolan

Mike Nolan wrote on 31.08.2004 21:46:

>>But should you also prevent DELETE's from that table? Otherwise you could
>>wind up with no rows at all. I guess that would have to be done using a rule...
>
>
> Why not just revoke the delete privilege?

That was one of my first guesses as well, but then I'm not sure if you can
revoke DELETE and INSERT privilege from the owner of the table...

Thomas

Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
> That was one of my first guesses as well, but then I'm not sure if you can
> revoke DELETE and INSERT privilege from the owner of the table...

In Postgres you can do this, although I think it's contrary to the
restrictions of the SQL spec, so it might not work in other DBs.

The reason is that Postgres treats the owner's ordinary privileges
as having been granted by the owner to himself; therefore he can revoke
'em too. The SQL spec treats the owner's privileges as having been
granted by the mystical entity "_SYSTEM"; not being "_SYSTEM", the owner
cannot revoke them.

Since it's obviously useful to be able to make a read-only table,
I think that the spec has missed a bet here.

regards, tom lane

Tom Lane wrote on 31.08.2004 22:59:

> In Postgres you can do this, although I think it's contrary to the
> restrictions of the SQL spec, so it might not work in other DBs.
>
> The reason is that Postgres treats the owner's ordinary privileges
> as having been granted by the owner to himself; therefore he can revoke
> 'em too. The SQL spec treats the owner's privileges as having been
> granted by the mystical entity "_SYSTEM"; not being "_SYSTEM", the owner
> cannot revoke them.
>
> Since it's obviously useful to be able to make a read-only table,
> I think that the spec has missed a bet here.
>

Thanks for the explanation. And yes I agree with you that making a table
read only by revoking the grants is something *very* useful.

Cheers
Thomas

> > Why not just revoke the delete privilege?
>
> That was one of my first guesses as well, but then I'm not sure if you can
> revoke DELETE and INSERT privilege from the owner of the table...

I just did a quick test on 7.4.5. Yes the table owner can revoke (and
re-grant) delete privileges on a table he owns, but of course I was not
able to revoke delete privileges from a superuser, since by definition
a superuser bypasses all access controls restrictions.

I assume the rule approach would apply to the superuser as well as to
other users. That makes it better able to handle this situation, whether
or not that approach has downsides is an internals question I can't answer.
--
Mike Nolan

> Thanks for the explanation. And yes I agree with you that making a table
> read only by revoking the grants is something *very* useful.

IIRC, Oracle supports read-only tablespaces. The reason for doing it at
the tablespace level probably has to with the overall concept of tablespaces
being linked to physical storage issues. Thus a read-only tablespace
could be stored on CD-ROM or some other non-writeable medium.
--
Mike Nolan