Consider the following:

$ psql test
test=# create temp sequence ts;
CREATE SEQUENCE
test=# create table tt1 (id int not null default nextval ('ts'), str varchar(255) not null);
CREATE TABLE
test=# insert into tt1 (str) values ('str1');                                   
INSERT 45510657 1
test=# select * from tt1;
 id | str  
----+------
  1 | str1
(1 row)

test=# \q
$ pg_dump -s -t tt1 test
--
-- PostgreSQL database dump
--

\connect - hf

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 45510654)
-- Name: tt1; Type: TABLE; Schema: public; Owner: hf
--

CREATE TABLE tt1 (
    id integer DEFAULT nextval('ts'::text) NOT NULL,
    str character varying(255) NOT NULL
);
$ psql test
test=# insert into tt1 (str) values ('str2');
ERROR:  Relation "ts" does not exist
test=# \q
$

Although what PostgreSQL (7.3.4) does is perfectly reasonable, I find
it somewhat unclean.  Since we now disallow FOREIGN KEYs between temp
and normal tables, we might also disallow using temp sequences with
normal tables.

Opinions?


02 Oct 2003 14:19:08 +0200
Harald Fuchs <nospam@sap.com>
Strange behavior regarding temporary sequences
Harald Fuchs <nospam@sap.com> writes:
> test=# create temp sequence ts;
> CREATE SEQUENCE
> test=# create table tt1 (id int not null default nextval ('ts'), str varchar(255) not null);
> CREATE TABLE

> Although what PostgreSQL (7.3.4) does is perfectly reasonable, I find
> it somewhat unclean.  Since we now disallow FOREIGN KEYs between temp
> and normal tables, we might also disallow using temp sequences with
> normal tables.

That's fairly impractical given that PG doesn't know that nextval('ts')
represents a sequence reference at all.  (The nextval() function knows
it, but I'd strongly resist any attempt to hard-wire assumptions about
nextval() into the rest of the system.)

There has been some talk of supporting the Oracle sequence syntax
ts.nextval, which would expose the sequence reference in a form the
system could recognize.  In the present state of the system, that would
cause your DEFAULT expression to get dropped when the temp sequence
went away (same result as DROP ... CASCADE issued manually).

			regards, tom lane

Fri, 03 Oct 2003 16:36:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Strange behavior regarding temporary sequences 