Thread: Change the behaviour of the SERIAL "Type"

Change the behaviour of the SERIAL "Type"

From
Dani Oderbolz
Date:
Hi all,
I am struggling hard with a badly written piece of code.
It has such statements all over the place:

INSERT INTO TABLE A (NULL, Value1, Value2...).

It was written for MySQL, which can take NULL and then assign an 
auto_increment.
However, in PostgreSQL I am getting problems, because it would not let 
me insert NULL
into a NOT NULL column (which is perfectly sensible from my point of view).
But as the author has also left out the column list in the insert, its 
really tedious to change
the code.
Therefore I was thinking whether it was possible to change the meaning 
of SERIAL in my DB,
so that it would actually generate a Trigger on the table, which would 
then take care
of  the SERIAL value. (Getting nextval from the sequence and write all 
into the table)
This way, there would be no possibility to circumvent the Value which 
comes from the Sequence.
To me, this would make more sense anyway than just setting the DEFAULT 
of a coulmn
(and as far as I understood, this is what SERIAL does, right?)

Is there a way to change SERIAL this way?

Cheers, Dani



Re: Change the behaviour of the SERIAL "Type"

From
Bruno Wolff III
Date:
On Thu, Jun 26, 2003 at 14:31:34 +0200, Dani Oderbolz <oderbolz@ecologic.de> wrote:
> 
> It was written for MySQL, which can take NULL and then assign an 
> auto_increment.
> However, in PostgreSQL I am getting problems, because it would not let 
> me insert NULL
> into a NOT NULL column (which is perfectly sensible from my point of view).
> But as the author has also left out the column list in the insert, its 
> really tedious to change
> the code.

You can use the keyword DEFAULT instead of NULL and it will do what you
want.

> This way, there would be no possibility to circumvent the Value which 
> comes from the Sequence.

You can use a unique constraint to enforce uniqueness.

> Is there a way to change SERIAL this way?

Well you can certainly write your own trigger to do this.


Re: Change the behaviour of the SERIAL "Type"

From
Randall Lucas
Date:
Wow, I had never actually faced this problem (yet) but I spied it as a 
possible stumbling block for porting MySQL apps, for which the standard 
practice is inserting a NULL.  As I have made a fairly thorough reading 
of the docs (but may have not cross-correlated every piece of data yet, 
obviously), I was surprised to find I hadn't figured this out myself.  
It /seems/ obvious in retrospect, but it really baked my noodle when I 
first looked at some ugly MySQL queries.

Respectfully, then, I move that a sentence outlining this functionality 
be added to User Manual section 5.1.4, "The Serial Types."  
Furthermore, anyone who has written or is writing a MySQL porting guide 
should include this, if he hasn't.

Best,

Randall


On Thursday, June 26, 2003, at 08:49 AM, Bruno Wolff III wrote:

> On Thu, Jun 26, 2003 at 14:31:34 +0200,
>   Dani Oderbolz <oderbolz@ecologic.de> wrote:
>>
>> It was written for MySQL, which can take NULL and then assign an
>> auto_increment.
>> However, in PostgreSQL I am getting problems, because it would not let
>> me insert NULL
>> into a NOT NULL column (which is perfectly sensible from my point of 
>> view).
>> But as the author has also left out the column list in the insert, its
>> really tedious to change
>> the code.
>
> You can use the keyword DEFAULT instead of NULL and it will do what you
> want.
>
>> This way, there would be no possibility to circumvent the Value which
>> comes from the Sequence.
>
> You can use a unique constraint to enforce uniqueness.
>
>> Is there a way to change SERIAL this way?
>
> Well you can certainly write your own trigger to do this.
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>



Re: Change the behaviour of the SERIAL "Type"

From
Dani Oderbolz
Date:
Randall Lucas wrote:

>
> Wow, I had never actually faced this problem (yet) but I spied it as a 
> possible stumbling block for porting MySQL apps, for which the 
> standard practice is inserting a NULL.  As I have made a fairly 
> thorough reading of the docs (but may have not cross-correlated every 
> piece of data yet, obviously), I was surprised to find I hadn't 
> figured this out myself.  It /seems/ obvious in retrospect, but it 
> really baked my noodle when I first looked at some ugly MySQL queries.
>
> Respectfully, then, I move that a sentence outlining this 
> functionality be added to User Manual section 5.1.4, "The Serial 
> Types."  Furthermore, anyone who has written or is writing a MySQL 
> porting guide should include this, if he hasn't. 

Yea, fine, but I propose a different (deeper) approach.
Why does SERIAL only enforce a DEFAULT?
This is not an exact imitation of an autoincrement, as a DEFAULT can be 
overwritten.
In my oppinion, SERIAL should implicitly create a Trigger on the table, 
which then
handles this transparently.
Would that be difficult?
(I am already writing a Procedure which gets all the info needed out of 
the Catalog,
but my problem is that I need some dynamic statements in there...)

Cheers, Dani




Re: Change the behaviour of the SERIAL "Type"

From
Bruno Wolff III
Date:
On Fri, Jun 27, 2003 at 10:45:07 +0200, Dani Oderbolz <oderbolz@ecologic.de> wrote:
> 
> Yea, fine, but I propose a different (deeper) approach.
> Why does SERIAL only enforce a DEFAULT?

Because it is faster.

> This is not an exact imitation of an autoincrement, as a DEFAULT can be 
> overwritten.

There are probably other differences as well, since serial only provides
a way to get unique values. If you want more meaning than that you
have to be careful.

> In my oppinion, SERIAL should implicitly create a Trigger on the table, 
> which then
> handles this transparently.
> Would that be difficult?

It shouldn't be too difficult to write some triggers that make something
closer to autoincrement. It probably won't work very well if there are
lots of concurrent updates though. You can either lock the table with
the column exclusively and then find the largest value and then use
that value plus one. Don't use max for this. Make an index on the
autoincrement column and use order by and limit 1 to get the largest
value. The other option is to keep the sequence value in other table.
You can use select for update to update it. You will want to vacuum
this table often enough that it will stay on one page.

> (I am already writing a Procedure which gets all the info needed out of 
> the Catalog,
> but my problem is that I need some dynamic statements in there...)
> 
> Cheers, Dani
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


Re: Change the behaviour of the SERIAL "Type"

From
Dani Oderbolz
Date:
Bruno Wolff III wrote:

> ...
>
>
>It shouldn't be too difficult to write some triggers that make something
>closer to autoincrement. It probably won't work very well if there are
>lots of concurrent updates though. You can either lock the table with
>the column exclusively and then find the largest value and then use
>that value plus one. Don't use max for this. Make an index on the
>autoincrement column and use order by and limit 1 to get the largest
>value. The other option is to keep the sequence value in other table.
>You can use select for update to update it. You will want to vacuum
>this table often enough that it will stay on one page.
>
Well, why not just use the Sequence?
Is there really such a performance hit when calling a trigger?
In Oracle, one usually does such a thing, as there is no such nice 
workaround
as SERIAL.
Hmm, I am still thinking about a special kinf of SERIAL, maybe called
TRIGGERED_SERIAL which creates a trigger instead of a DEFAULT.

Cheers,
Dani




Re: Change the behaviour of the SERIAL "Type"

From
Rod Taylor
Date:
> Well, why not just use the Sequence?
> Is there really such a performance hit when calling a trigger?
> In Oracle, one usually does such a thing, as there is no such nice
> workaround
> as SERIAL.
> Hmm, I am still thinking about a special kinf of SERIAL, maybe called
> TRIGGERED_SERIAL which creates a trigger instead of a DEFAULT.

DB2, Firebird, MSSQL? and some others have what they call GENERATOR
support (IDENTITIES fall into this)-- which also happens to be in the
SQL 200N proposals.

Main Features (per proposed spec):
- Not strictly integers (any expression on any datatype)
- Optionally overridable or not -- which is what you're looking for
- Attribute of the column. Not a datatype. This is an alternative for
DEFAULT.


I'm hoping to add IDENTITIES / GENERATOR support along these lines in
7.5, but I've not looked at those other databases to see how close their
implementation matches spec -- whether it will make us compatible with
them or not.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: Change the behaviour of the SERIAL "Type"

From
Bruno Wolff III
Date:
On Fri, Jun 27, 2003 at 16:35:36 +0200, Dani Oderbolz <oderbolz@ecologic.de> wrote:
> Well, why not just use the Sequence?
> Is there really such a performance hit when calling a trigger?

I think the big issue is concurrency. Sequences are designed so that
conncurrent uses of the sequence don't block each other. In the trigger
based methods you have to lock the value against concurrent update and
this lock will be held to the end of the transaction. This has the
potential to really hurt your performance.

I may have been misunderstanding what you are trying to do though.
If your problem was that people could update or insert values into
the serial column that might cause other transactions to unexpected
fail (when they try to use a duplicate value - assuming you use a unique
index on the column) then you could use a trigger to prevent updates
on that column and force inserts to always use nextval. If you need
to reload the database at some point, you will need to do something
to keep the triggers from interfering with the reload. This should
be fairly efficient.

I had thought you were concerned about possible gaps in the sequence.