Thread

1. BUG #19023: Table DDL default column expression depending on temp objects

   PG Bug reporting form <noreply@postgresql.org> — 2025-08-18T13:32:24Z

   The following bug has been logged on the website:
   
   Bug reference:      19023
   Logged by:          Kirill Reshke
   Email address:      reshkekirill@gmail.com
   PostgreSQL version: 18beta3
   Operating system:   any
   Description:        
   
   I have found two cases when user can define a relation, which has temp
   objects in its DEFAULT expression.
   First example is:
   ```
   reshke=# create temp sequence s1;
   CREATE SEQUENCE
   reshke=# create table tt(i int default nextval('s1'));
   CREATE TABLE
   ```
   I think it is a bug that PostgreSQL allows this DDL to successfully
   complete. There is a validation for this type of DDL already, example:
   ```
   reshke=# create table ttx(i int default nextval('s3'));
   ERROR:  relation "s3" does not exist
   LINE 1: create table ttx(i int default nextval('s3'));
                                                  ^
   
   ```
   
   Since PostgreSQL already checks for its nextval() input, we can teach server
   to also validate relation relpersistence, arent we?
   
   Second example is with tables:
   ```
   reshke=# create temp table  x();
   CREATE TABLE
   reshke=# create table y (i int default nextval('x'));
   CREATE TABLE
   reshke=# insert into y default values ;
   ERROR:  cannot open relation "x"
   DETAIL:  This operation is not supported for tables.
   
   ```
   
   WDYT? Is this indeed a bug?
   
   

2. Re: BUG #19023: Table DDL default column expression depending on temp objects

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-18T14:05:55Z

   PG Bug reporting form <noreply@postgresql.org> writes:
   > I have found two cases when user can define a relation, which has temp
   > objects in its DEFAULT expression.
   > First example is:
   > ```
   > reshke=# create temp sequence s1;
   > CREATE SEQUENCE
   > reshke=# create table tt(i int default nextval('s1'));
   > CREATE TABLE
   > ```
   
   Appears to me to operate as intended: the DEFAULT will be dropped
   at session exit, just as if you'd done DROP SEQUENCE s1 CASCADE.
   
   > I think it is a bug that PostgreSQL allows this DDL to successfully
   > complete.
   
   Why?
   
   > reshke=# create temp table  x();
   > CREATE TABLE
   > reshke=# create table y (i int default nextval('x'));
   > CREATE TABLE
   > reshke=# insert into y default values ;
   > ERROR:  cannot open relation "x"
   > DETAIL:  This operation is not supported for tables.
   
   This is not about whether x is temp or not, it's about whether
   it's a sequence or not.  The argument of nextval() is declared
   as regclass, so we can verify that the constant is the name
   of a relation; but the parser has no way to know that it ought
   to be a sequence in particular.
   
   > WDYT? Is this indeed a bug?
   
   No.  Sure, in a perfect world we could detect "it's not a sequence" at
   parse time, but I can't see inventing "regsequence" just to improve
   that.
   
   			regards, tom lane