This is a multi-part message in MIME format.
--------------040002090700010604000202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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:*.xs lt:*.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:his tory: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



--------------040002090700010604000202
Content-Type: text/plain;
name="testDB.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="testDB.sql"

--
-- 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_sequenc e('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
--


--------------040002090700010604000202
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--------------040002090700010604000202--

--LZvS9be/3tNcYl/X
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Nov 15, 2005 at 11:39:37AM +0200, Sarunas Krisciukaitis wrote:

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

--LZvS9be/3tNcYl/X
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFDesPEBcgs9XrR2kYRAouAAJ9vuZKCipeg0tb53GLzkZ oPNWenfwCeOm+/
/e1g2rcs4hRQdUV20+6dKlk=
=hGmJ
-----END PGP SIGNATURE-----

--LZvS9be/3tNcYl/X--

On Wed, Nov 16, 2005 at 06:29:40AM +0100, Tomas Zerolo wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--gatW/ieO32f1wygP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Nov 15, 2005 at 10:51:10PM -0700, Michael Fuhr wrote:
[...]

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

thanks
-- tomas

--gatW/ieO32f1wygP
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFDestzBcgs9XrR2kYRAtY1AJ9x/aRTQX45TmAjNp4QPXUl4ZU7JACfRIRz
ini/DA7p/oBfKXFC4Xg0b0o=
=C8ew
-----END PGP SIGNATURE-----

--gatW/ieO32f1wygP--

On Wed, Nov 16, 2005 at 10:31:10AM +0200, Sarunas Krisciukaitis wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match