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@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026