Thread: FW: help with serial

<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10.0pt;font-family:Arial;color:navy">H</span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">iall,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">There’s a behaviour I can’t understand!!!!</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">There is table TABLE_A with 3 columns (uid, id, session) where uid is the pk and the data type is
serial.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">There is table TABLE_B with 3 columns (uid, ts_col, col) where uid is the pk.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">On table TABLE_A I have a rule on insert like this:</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE OR REPLACE RULE r1 AS</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">    </span>ON INSERT TO TABLE_A DO<span style="mso-spacerun:yes"> 
</span>INSERTINTO TABLE_B (uid) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">  </span>VALUES ((new.uid));</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Till now… ok…</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">When I do an insert </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">insert into TABLE_A ("id", "session") values (7, 'aaaaaaaaaaaaa');</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have this row,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">In table TABLE_A</span></font><p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial;font-weight:bold; 
mso-bidi-font-weight:normal">37</span></font></b><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">;7; "aaaaaaaaaaaaa"</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">while in table TABLE_B</span></font><p class="MsoNormal"><b
style="mso-bidi-font-weight:normal"><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial;font-weight:bold;
mso-bidi-font-weight:normal">38</span></font></b><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">;""; ""</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">how is it possible, why in a table 37 and in the other 37 + 1?</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thank you,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Luca</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font></div>

Luca Rasconi wrote:
>  
> CREATE OR REPLACE RULE r1 AS
>     ON INSERT TO TABLE_A DO  INSERT INTO TABLE_B (uid) 
>   VALUES ((new.uid));

> how is it possible, why in a table 37 and in the other 37 + 1?

This is almost certainly the "nextval() evaluated twice" issue that 
catches everyone out from time to time. Check the mailing-list archives 
for discussion - it's because the rule acts as a macro, duplicating the 
expression.

--  Richard Huxton  Archonet Ltd