Re: [HACKERS] Enhancing PGSQL to be compatible with Informix SQL - Mailing list pgsql-hackers

On Thu, 6 Jan 2000, Bruce Momjian wrote:

[snip]

> The move to make MONEY use decimal would add precision.
> 
> > > >     5/    serial data type
> > > >         o    Serial type must return inserted key value
> > > 
> > > How does Informix return the value?
> > > 
> > 
> > >From a user standpoint it mystically appears in sqlca just after the
> > insert statement is executed.  Actually the informix engine recognises
> > it's just done a serial insert, and sends it back in addition to the
> > standard status packets.
> 
> 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.

The above example is a relatively simple one to parse and analyze.  A more
complicated case that we'd also probably have to recognise would be
something like

select x,y,z,p+1 from base_table insert into mytable

short of having an SQL parser how are you supposed to determine the
required behaviour?

There are other issues with serial which suggest that better processing is
probably required; they are currently completely useful in the context of
temporary tables, since the underlying sequence is never dropped.

> 
> > I can understand the situation here (one of the main reasons I raised the
> > thread in the first place).  Above all else the difficulty I have with
> > serial at the moment is the impossibility of differentiating a serial with
> > an int4 after creation (after all the database treats them identically).
> > The catalog tables don't contain any information.  The only way you can
> > work out you created a serial column is by looking for an appropriately
> > named sequence in the database on every int4 column that exists (or am I
> > wrong?). This is not exactly something that appeals to me
> 
> 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.


.............................Rod

+-----------------------------------------------------------------------------+
| Rod Chamberlin              |  rod@querix.com   Tel +44 1703 232345         |
| Software Engineer           |                   Mob +44 7803 295406         |
| QueriX                      |                   Fax +44 1703 399685         |
+-----------------------------------------------------------------------------+
| The views expressed in this document do not necessarily represent those of  |
|                    the management of QueriX (UK) Ltd.                       |
+-----------------------------------------------------------------------------+