Thread: Double sequence increase on single insert with RULE on targeted table

Dear All,

A program produces the wrong output for any given input.
Here comes bug report:
1. Database dump is attached.
2. Input: "BEGIN; INSERT INTO test1(some_text) VALUES ('test1'); SELECT
lastval() as id; END;"
3. Output:
INSERT 0 1
id
----
  3
(1 row)
4. Exspected Output:
 id
----
  2
(1 row)
5. Environment:
ANT_HOME=/usr/share/ant-core
BASH=/bin/bash
BASH_ARGC=()
BASH_ARGV=()
BASH_LINENO=()
BASH_SOURCE=()
BASH_VERSINFO=([0]="3" [1]="00" [2]="16" [3]="2" [4]="release"
[5]="i686-pc-linux-gnu")
BASH_VERSION='3.00.16(2)-release'
CLASSPATH=.
COLORTERM=gnome-terminal
COLUMNS=124
CONFIG_PROTECT='/usr/lib/mozilla/defaults/pref /usr/lib/X11/xkb
/usr/kde/3.4/share/config /usr/kde/3.4/env /usr/kde/3.4/shutdown
/usr/share/config'
CONFIG_PROTECT_MASK='/etc/gconf /etc/terminfo /etc/splash'
CVS_RSH=ssh
DCCC_PATH=/usr/lib/distcc/bin
DESKTOP_SESSION=default
DESKTOP_STARTUP_ID=
DIRSTACK=()
DISPLAY=:0.0
DISTCC_LOG=
DISTCC_VERBOSE=0
DM_CONTROL=/var/run/xdmctl
EDITOR=/bin/nano
EUID=1000
FLTK_DOCDIR=/usr/share/doc/fltk-1.1.6/html
GCC_SPECS=
GDK_USE_XFT=1
GROUPS=()
GS_LIB=/home/sarunas/.fonts
GTK2_RC_FILES=/etc/gtk-2.0/gtkrc:/home/sarunas/.gtkrc-2.0:/home/sarunas/.kde3.4/share/config/gtkrc
GTK_RC_FILES=/etc/gtk/gtkrc:/home/sarunas/.gtkrc:/home/sarunas/.kde3.4/share/config/gtkrc
G_BROKEN_FILENAMES=1
HISTCONTROL=ignorespace
HISTFILE=/home/sarunas/.bash_history
HISTFILESIZE=500
HISTSIZE=500
HOME=/home/sarunas
HOSTNAME=sarunas
HOSTTYPE=i686
IFS=$' \t\n'

INFOPATH=/usr/share/info:/usr/share/binutils-data/i686-pc-linux-gnu/2.15.92.0.2/info:/usr/share/gcc-data/i686-pc-linux-gnu/3.4.4/info
JAVAC=/opt/sun-jdk-1.5.0.05/bin/javac
JAVA_HOME=/opt/sun-jdk-1.5.0.05
JDK_HOME=/opt/sun-jdk-1.5.0.05
KDEDIRS=/usr
KDE_FULL_SESSION=true
KDE_MULTIHEAD=false
KDE_NO_IPV6=1
LESS=-R
LESSOPEN='|lesspipe.sh %s'
LINES=45
LOGNAME=sarunas
MACHTYPE=i686-pc-linux-gnu
MAILCHECK=60

MANPATH=/usr/local/share/man:/usr/share/man:/usr/share/binutils-data/i686-pc-linux-gnu/2.15.92.0.2/man:/usr/share/gcc-data/i686-pc-linux-gnu/3.4.4/man::/opt/sun-jdk-1.5.0.05/man:/usr/qt/3/doc/man
MC_TMPDIR=/tmp/mc-sarunas
MOZILLA_FIVE_HOME=/usr/lib/mozilla
OLDPWD=/home/sarunas
OPENGL_PROFILE=xorg-x11
OPTERR=1
OPTIND=1
OSTYPE=linux-gnu
PAGER=/usr/bin/less

PATH=/usr/local/bin:/usr/bin:/bin:/opt/bin:/usr/i686-pc-linux-gnu/gcc-bin/3.4.4:/opt/sun-jdk-1.5.0.05/bin:/opt/sun-jdk-1.5.0.05/jre/bin:/usr/qt/3/bin:/usr/kde/3.4/bin:/usr/games/bin
PERLIO=stdio
PIPESTATUS=([0]="0")
PPID=9438

PRELINK_PATH_MASK='/usr/lib/gstreamer-0.8:/opt:/lib/modules:/usr/lib/locale:/usr/lib/wine:/usr/lib/valgrind:/usr/bin/mencoder:*.la:*.png:*.py:*.pl:*.pm:*.sh:*.xml:*.xslt:*.a:*.js'
PROMPT_COMMAND='pwd>&8;kill -STOP $$'
PS1='\[\033[01;32m\]\u@\h \[\033[01;34m\]\w \$ \[\033[00m\]'
PS2='> '
PS4='+ '
PWD=/home/sarunas/Cargo2Net
PYTHONPATH=/usr/lib/portage/pym
QMAKESPEC=linux-g++
QTDIR=/usr/qt/3
SESSION_MANAGER=local/sarunas:/tmp/.ICE-unix/8550
SHELL=/bin/bash
SHELLOPTS=braceexpand:emacs:hashall:histexpand:history:interactive-comments:monitor
SHLVL=3
SSH_AGENT_PID=8504
SSH_ASKPASS=/usr/bin/gtk2-ssh-askpass
SSH_AUTH_SOCK=/tmp/ssh-SGMtSU8503/agent.8503
TERM=xterm
UID=1000
USER=sarunas
WINDOWID=29373840
XCURSOR_THEME=gentoo-silver
XDG_CONFIG_DIRS=/usr/kde/3.4/etc/xdg
XDG_DATA_DIRS=/usr/kde/3.4/share:/usr/share
XDM_MANAGED=/var/run/xdmctl/xdmctl-:0,maysd,mayfn,sched,rsvd,method=classic
XINITRC=/etc/X11/xinit/xinitrc
_=/etc/profile
safe_term=xterm
use_color=true
6. Postmaster startup options:
 su - postgres -c '/usr/bin/pg_ctl start -D
'\''/var/lib/postgresql/data'\'' -s -l
'\''/var/lib/postgresql/data/postgresql.log'\'' -o '\'''\'''
7. PostgreSQL version:
PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.4.4 (Gentoo 3.4.4-r1, ssp-3.4.4-1.0,
pie-8.7.8)
8. Platform information:
    8.1. Kernel: 2.6.12-gentoo-r10
    8.2. GlibC: GNU C Library stable release version 2.3.5, by Roland
McGrath et al.
Copyright (C) 2005 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE.
Compiled by GNU CC version 3.4.4 (Gentoo 3.4.4-r1, ssp-3.4.4-1.0,
pie-8.7.8).
Compiled on a Linux 2.6.11 system on 2005-10-17.
Available extensions:
        GNU libio by Per Bothner
        crypt add-on version 2.1 by Michael Glad and others
        Native POSIX Threads Library by Ulrich Drepper et al
        The C stubs add-on version 2.1.2.
        GNU Libidn by Simon Josefsson
        BIND-8.2.3-T5B
        NIS(YP)/NIS+ NSS modules 0.19 by Thorsten Kukuk
Thread-local storage support included.
For bug reporting instructions, please see:
<http://www.gnu.org/software/libc/bugs.html>.
    8.3. Processor: Intel(R) Pentium(R) 4 CPU 2.80GHz
    8.4. Memmory: 1024 GB
    8.5. Linux ver: Gentoo 2005.1

Sincerely,
Sarunas


--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: testdb; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE testdb WITH TEMPLATE = template0 ENCODING = 'UTF8';


ALTER DATABASE testdb OWNER TO postgres;

\connect testdb

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test1; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE test1 (
    id bigserial NOT NULL,
    some_text text NOT NULL
);


ALTER TABLE public.test1 OWNER TO postgres;

--
-- Name: TABLE test1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE test1 IS 'Test table with Rules';


--
-- Name: COLUMN test1.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN test1.id IS 'ID';


--
-- Name: test1_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('test1', 'id'), 1, true);


--
-- Name: test_log1; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE test_log1 (
    qid bigint NOT NULL,
    when_happened timestamp without time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.test_log1 OWNER TO postgres;

--
-- Name: TABLE test_log1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE test_log1 IS 'Table were we log inserts to test1';


--
-- Data for Name: test1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY test1 (id, some_text) FROM stdin;
\.


--
-- Data for Name: test_log1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY test_log1 (qid, when_happened) FROM stdin;
\.


--
-- Name: test1_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
--

ALTER TABLE ONLY test1
    ADD CONSTRAINT test1_pkey PRIMARY KEY (id);


--
-- Name: test_log1_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
--

ALTER TABLE ONLY test_log1
    ADD CONSTRAINT test_log1_pkey PRIMARY KEY (qid);


--
-- Name: test1_on_insert; Type: RULE; Schema: public; Owner: postgres
--

CREATE RULE test1_on_insert AS ON INSERT TO test1 DO INSERT INTO test_log1 (qid) VALUES (new.id);


--
-- Name: RULE test1_on_insert ON test1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON RULE test1_on_insert ON test1 IS 'on insert update test_log1';


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

On Tue, Nov 15, 2005 at 11:39:37AM +0200, Sarunas Krisciukaitis wrote:
> Dear All,
>=20
> A program produces the wrong output for any given input.
> Here comes bug report:
> 1. Database dump is attached.
> 2. Input: "BEGIN; INSERT INTO test1(some_text) VALUES ('test1'); SELECT=
=20
> lastval() as id; END;"
> 3. Output:
> INSERT 0 1
> id
> ----
>  3
> (1 row)
> 4. Exspected Output:
> id
> ----
>  2
> (1 row)
[snip]

AFAIK, serials are not guaranteed to produce sequential values; tehy
will produce unique values. That means that they can (and sometimes
will) jump.

Think about it: how could a database achieve that when the rollback of
an ongoing transaction is always possible?

Regards
-- tomas

On Wed, Nov 16, 2005 at 06:29:40AM +0100, Tomas Zerolo wrote:
> AFAIK, serials are not guaranteed to produce sequential values; tehy
> will produce unique values. That means that they can (and sometimes
> will) jump.

In this particular case, however, the behavior is due to the rule
on test1:

CREATE RULE test1_on_insert AS ON INSERT TO test1
  DO INSERT INTO test_log1 (qid) VALUES (new.id);

This "gotcha" comes up occasionally; it's due to the fact that rules
are macros.  Search the archives for past discussion.

--
Michael Fuhr

On Tue, Nov 15, 2005 at 10:51:10PM -0700, Michael Fuhr wrote:
> On Wed, Nov 16, 2005 at 06:29:40AM +0100, Tomas Zerolo wrote:
> > AFAIK, serials are not guaranteed to produce sequential values; tehy
> > will produce unique values. That means that they can (and sometimes
> > will) jump.
>=20
> In this particular case, however, the behavior is due to the rule
> on test1:
>=20
> CREATE RULE test1_on_insert AS ON INSERT TO test1
>   DO INSERT INTO test_log1 (qid) VALUES (new.id);

[...]

Oops, I didn't see that. Your eyes are sharper than mine ;-)

thanks
-- tomas

I understand that RULES are like macros.
Strangest thing here is that INSERT to test1 will touch only one
sequence: test1_id_seq.
And it increments test1_id_seq twice during insert with RULE.
Then all sequence procedures like lastval() and currval() will return
number (as stated in report),
which is biger than actualy one inserted into the database.
When after insert:
BEGIN; INSERT INTO test1(some_text) VALUES ('test1'); SELECT lastval()
as id; END;
you make a select on test1 and test_log1 tables you see such a view:
testdb=# select * from test1;
 id | some_text
----+-----------
  2 | test1
(1 row)
testdb=# select * from test_log1;
 qid |       when_happened
-----+----------------------------
   3 | 2005-11-16 10:27:33.100913
(1 row)

Sarunas

Tomas Zerolo wrote:

>On Tue, Nov 15, 2005 at 10:51:10PM -0700, Michael Fuhr wrote:
>
>
>>On Wed, Nov 16, 2005 at 06:29:40AM +0100, Tomas Zerolo wrote:
>>
>>
>>>AFAIK, serials are not guaranteed to produce sequential values; tehy
>>>will produce unique values. That means that they can (and sometimes
>>>will) jump.
>>>
>>>
>>In this particular case, however, the behavior is due to the rule
>>on test1:
>>
>>CREATE RULE test1_on_insert AS ON INSERT TO test1
>>  DO INSERT INTO test_log1 (qid) VALUES (new.id);
>>
>>
>
>[...]
>
>Oops, I didn't see that. Your eyes are sharper than mine ;-)
>
>thanks
>-- tomas
>
>

On Wed, Nov 16, 2005 at 10:31:10AM +0200, Sarunas Krisciukaitis wrote:
> I understand that RULES are like macros.
> Strangest thing here is that INSERT to test1 will touch only one
> sequence: test1_id_seq.
> And it increments test1_id_seq twice during insert with RULE.

Yes, that's a well-known effect of rewriting a query that includes
a call to nextval().  NEW.id in the rule doesn't refer to the value
that's inserted, but rather to the expression that's evaluated to
get that value.  Since you didn't provide a value for id it gets
the default: nextval('test1_id_seq').  That expression is used in
both inserts, so the sequence gets incremented twice.  See the
archives for numerous past discussions of this behavior.

--
Michael Fuhr

Ok :) Then I found the solution in this partical case:
CREATE RULE test1_on_insert AS ON INSERT TO test1 DO INSERT INTO
test_log1 (qid) VALUES ( (SELECT lastval()) );
With this rule all inserts are working as expected :)

Thank you for you advise :)

Sarunas

Michael Fuhr wrote:

>On Wed, Nov 16, 2005 at 10:31:10AM +0200, Sarunas Krisciukaitis wrote:
>
>
>>I understand that RULES are like macros.
>>Strangest thing here is that INSERT to test1 will touch only one
>>sequence: test1_id_seq.
>>And it increments test1_id_seq twice during insert with RULE.
>>
>>
>
>Yes, that's a well-known effect of rewriting a query that includes
>a call to nextval().  NEW.id in the rule doesn't refer to the value
>that's inserted, but rather to the expression that's evaluated to
>get that value.  Since you didn't provide a value for id it gets
>the default: nextval('test1_id_seq').  That expression is used in
>both inserts, so the sequence gets incremented twice.  See the
>archives for numerous past discussions of this behavior.
>
>
>

Is that safe or could lastval return the value of a previous insert if
an insert fails? Though I suppose if the insert fails then the rule
shouldn't get fired...

On Thu, Nov 17, 2005 at 10:11:29AM +0200, Sarunas Krisciukaitis wrote:
> Ok :) Then I found the solution in this partical case:
> CREATE RULE test1_on_insert AS ON INSERT TO test1 DO INSERT INTO
> test_log1 (qid) VALUES ( (SELECT lastval()) );
> With this rule all inserts are working as expected :)
>
> Thank you for you advise :)
>
> Sarunas
>
> Michael Fuhr wrote:
>
> >On Wed, Nov 16, 2005 at 10:31:10AM +0200, Sarunas Krisciukaitis wrote:
> >
> >
> >>I understand that RULES are like macros.
> >>Strangest thing here is that INSERT to test1 will touch only one
> >>sequence: test1_id_seq.
> >>And it increments test1_id_seq twice during insert with RULE.
> >>
> >>
> >
> >Yes, that's a well-known effect of rewriting a query that includes
> >a call to nextval().  NEW.id in the rule doesn't refer to the value
> >that's inserted, but rather to the expression that's evaluated to
> >get that value.  Since you didn't provide a value for id it gets
> >the default: nextval('test1_id_seq').  That expression is used in
> >both inserts, so the sequence gets incremented twice.  See the
> >archives for numerous past discussions of this behavior.
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461