Re: BUG #18607: UNION ALL discards all foreign key relations + indexes

On Tue, Sep 10, 2024 at 12:52:21PM +0530, Sanskar Agrawal wrote:
> Please find the below DDLS:
>
> CREATE TABLE test_users (
> user_id SERIAL PRIMARY KEY,
> username VARCHAR(50) NOT NULL,
> email VARCHAR(100) NOT NULL,
> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
> );
> -- Add an index on the user_id column
> CREATE INDEX idx_user_id ON test_users (user_id);
> -- Add an index on the email column
> CREATE INDEX idx_email ON test_users (email);
> INSERT INTO test_users (username, email) VALUES
> ('user1', 'user1(at)example(dot)com'),
> ('user2', 'user2(at)example(dot)com');
> SELECT * FROM temp_users WHERE email = 'user1(at)example(dot)com'
> UNION ALL
> SELECT * FROM temp_users WHERE email = 'user2(at)example(dot)com';

First, please note that your queries run with error:

#v+
=$ psql -aX -f z.sql
CREATE TABLE test_users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
-- Add an index on the user_id column
CREATE INDEX idx_user_id ON test_users (user_id);
CREATE INDEX
-- Add an index on the email column
CREATE INDEX idx_email ON test_users (email);
CREATE INDEX
INSERT INTO test_users (username, email) VALUES
('user1', 'user1(at)example(dot)com'),
('user2', 'user2(at)example(dot)com');
INSERT 0 2
SELECT * FROM temp_users WHERE email = 'user1(at)example(dot)com'
UNION ALL
SELECT * FROM temp_users WHERE email = 'user2(at)example(dot)com';
psql:z.sql:16: ERROR: relation "temp_users" does not exist
LINE 1: SELECT * FROM temp_users WHERE email = 'user1(at)example(dot)com'
^
#v-

You meant test_users, but had temp_users. Fixed that, re-ran:

#v+
=$ psql -aX -f z.sql
CREATE TABLE test_users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
-- Add an index on the user_id column
CREATE INDEX idx_user_id ON test_users (user_id);
CREATE INDEX
-- Add an index on the email column
CREATE INDEX idx_email ON test_users (email);
CREATE INDEX
INSERT INTO test_users (username, email) VALUES
('user1', 'user1(at)example(dot)com'),
('user2', 'user2(at)example(dot)com');
INSERT 0 2
SELECT * FROM test_users WHERE email = 'user1(at)example(dot)com'
UNION ALL
SELECT * FROM test_users WHERE email = 'user2(at)example(dot)com';
user_id | username | email | created_at
---------+----------+-------------------+---------------------------
1 | user1 | user1(at)example(dot)com | 2024-09-10 09:43:05.72212
2 | user2 | user2(at)example(dot)com | 2024-09-10 09:43:05.72212
(2 rows)
#v-

So, the queries work.

Now, let's see the table:

#v+
=$ psql -aX <<< '\d test_users'
\d test_users
Table "public.test_users"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------------------------------------------
user_id | integer | | not null | nextval('test_users_user_id_seq'::regclass)
username | character varying(50) | | not null |
email | character varying(100) | | not null |
created_at | timestamp without time zone | | | CURRENT_TIMESTAMP
Indexes:
"test_users_pkey" PRIMARY KEY, btree (user_id)
"idx_email" btree (email)
"idx_user_id" btree (user_id)
#v-

The index is there. So nothing got dropped.

Can you explain what you're seeing, preferably using tested queries that
show the problem?

Best regards,

depesz