Re: Referencing serial col's sequence for insert

Thanks Olarte,
Exactly following your advice...this being the beauty of open source -you
can read the source code
​. It's that itch to drink deep from the fountain of knowledge.​

I really do like
​ ​
​Laurenz Albe's advice of using WITH() AS which seems to be the best
practice I can ask the developers to follow as it eliminates a lot of
uncertainties and db specific behavior - and seems like a best practice
even for other DBs.
​In fact I am sort of expanding that a bit to say wherever sequences need
to be used ​
​use the WITH() AS construct pattern.​

Thanks everyone for the inputs.

Regards
​,​

A
​nil​

On 24 Jul 2014 02:03, "Francisco Olarte" <folarte(at)peoplecall(dot)com> wrote:

> 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.
>