Re: Referencing serial col's sequence for insert

Hi Anil:

On Tue, Jul 22, 2014 at 6:46 PM, Anil Menon <gakmenon(at)gmail(dot)com> wrote:
> Am a bit confused -which one comes first?
> 1) the 'data'||currval('id01_col1_seq') is parsed first : which means it
....
>or
> 1) an insert is attempted which causes a sequence.nextval to be performed
...
> I observe the latter on my single session notebook instance of postgres.

Don't be confused, you have experimental evidence which beats theories
hand down, it's either the later or a superset of it ( i.e., second
for single sessions only, or second on notebook sessions, but it seems
unlikely ).

Also note the 1st one cannot be unless you are not using a fresh
session ( i.e., the insert is the first command typed, which if it is
not signals you are testing badly ), since currval is documented as
failing in this case.

Anyway, you aproach is risky. You've been told a lot of alternatives
which have predictable behaviour ( here is another one, start work,
select and ignore nextval, then use currval for BOTH values ), so why
not use one of them? Bear in mind that the second alternative maybe
working due to undefined behaviour which may change in a future
release, or when using multiple rows ( or when using an even number of
sessions, although, as before, I find that one unlikely ). ( Or, if
you really want to know for knowledges sake which is the behaviour,
download the sources, procure yourself a tank of your favorite
caffeinated potion and hack on. )

Regards.

Francisco Olarte.