Re: BUG #18607: UNION ALL discards all foreign key relations + indexes - Mailing list pgsql-bugs

From hubert depesz lubaczewski
Subject Re: BUG #18607: UNION ALL discards all foreign key relations + indexes
Date
Msg-id Zt_4_jOQNLSLVFtf@depesz.com
Whole thread Raw
In response to BUG #18607: UNION ALL discards all foreign key relations + indexes  (PG Bug reporting form <noreply@postgresql.org>)
List pgsql-bugs
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@example.com'),
> ('user2', 'user2@example.com');
> SELECT * FROM temp_users WHERE email = 'user1@example.com'
> UNION ALL
> SELECT * FROM temp_users WHERE email = 'user2@example.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@example.com'),
('user2', 'user2@example.com');
INSERT 0 2
SELECT * FROM temp_users WHERE email = 'user1@example.com'
UNION ALL
SELECT * FROM temp_users WHERE email = 'user2@example.com';
psql:z.sql:16: ERROR:  relation "temp_users" does not exist
LINE 1: SELECT * FROM temp_users WHERE email = 'user1@example.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@example.com'),
('user2', 'user2@example.com');
INSERT 0 2
SELECT * FROM test_users WHERE email = 'user1@example.com'
UNION ALL
SELECT * FROM test_users WHERE email = 'user2@example.com';
 user_id | username |       email       |        created_at
---------+----------+-------------------+---------------------------
       1 | user1    | user1@example.com | 2024-09-10 09:43:05.72212
       2 | user2    | user2@example.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




pgsql-bugs by date:

Previous
From: hubert depesz lubaczewski
Date:
Subject: Re: BUG #18607: UNION ALL discards all foreign key relations + indexes
Next
From: hubert depesz lubaczewski
Date:
Subject: Re: BUG #18607: UNION ALL discards all foreign key relations + indexes