Re: [HACKERS] Enhancing PGSQL to be compatible with Informix SQL

> > Yes, we have currval() which allows such retrieval _inside_ the
> > database, as well as in the application.
> >
>
> Yes, but the interface cannot tell what it's operating on, so it doesn't
> know to fetch curval; consider the following statement:
>
> insert into mytable values('Hello',0,0,23,17.0,0.0);
>
> Are any of the inserted values insert into serial columns?
>
> You have no way of knowing. In fact any one of the last 5 columsn could
> potentially be serial values being inserted (although if it's the third
> or forth column we don't need to do any extra processing (*)). In the same
> way the interface layer can see the SQL statement and not know if it has
> to do any extra work for informix compatibility in terms of fetching the
> extra values back from the sequence which Postgres has created for us.
>
> (*) Actually we probably do, since we need to ensure that the sequence
> value has passed the inserted value if we do a non-null insert on a serial
> column, otherwise we may later regenerate the same serial number.

Yes, I see your point, and the fault is that Informix is doing some
special things when 0 is inserted into the SERIAL column type. By doing
defaults and using that, we are being more constent. With the Informix
solution, we are losing information.

It is probably a good argument _not_ to implement the informix
slight-of-hand.

However, I also see your huge problem because we don't document the
SERIAL, and we don't allow zero to trigger a nextval(). Very tough.

> > Yes, the SERIAL gets lost once it is created. This can cause confusion
> > because doing a \dt on the table shows it as an INT4 with DEFAULT, and
> > not a serial. This can confuse people. I remember someone saying we
> > would need to keep the SERIAL understanding around so we would use it
> > for pg_dump, but I don't remember why we needed to do that.
> >
>
> This is odd actually. I can't see why you'd need to do it either, since
> you must already have the information you need to recreate the thing.
>
> The confusion though is not that I can't work out it's a serial, but
> that a program can't work out it's a serial.

SERIAL was implemented as a nice workaround to prevent people from
defining a sequance and defining a default nextval(). I think I may
have suggested it because of my Informix background.

The issue is that SERIAL is just a shortcut. It doesn't have any
internal representation. It would need one only for pg_dump and for
your use, and I am not sure that is warranted. Other people would have
to agree that keeping the SERIAL as its own type is good.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026