Re: Singleton table (was Re: How to don't update sequence on rollback of a transaction)

On 08/03/2012 12:07 PM, Chris Angelico wrote:
> On Fri, Aug 3, 2012 at 10:00 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
>> -- PostgreSQL specific hack you can use to make
>> -- really sure only one row ever exists
>> CREATE UNIQUE INDEX there_can_be_only_one
>> ON invoice_number( (1) );
> This will guarantee that there's only one row. Does Postgres then
> optimize anything based on this? I have a couple of cases where I'm
> using a table to store global configuration, and selecting/updating
> that table without a WHERE clause to manipulate it. Can PostgreSQL
> notice the "highlander" status of the table and know not to go looking
> for more?

As far as I know, no. If anything it could slow things down a tad. I
just like to be really sure it's a single row table.

An `ON INSERT OR DELETE` trigger that throws is probably a more sensible
option, really.

--
Craig Ringer