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@peoplecall.com> wrote:
Hi Anil:
On Tue, Jul 22, 2014 at 6:46 PM, Anil Menon <gakmenon@gmail.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.
