Date: Tue, 10 Sep 2024 09:45:02 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Sanskar Agrawal <sanskar@flintk12.com>
Cc: pgsql-bugs mailing list <pgsql-bugs@postgresql.org>
Subject: Re: BUG #18607: UNION ALL discards all foreign key relations +
 indexes
Message-ID: <Zt_4_jOQNLSLVFtf@depesz.com>
Reply-To: depesz@depesz.com
References: <18607-651aa996c1e71d48@postgresql.org>
 <Zt_wyM5koKKmOmdn@depesz.com>
 <CAL9bbZquC=BZa61TSHgX7vXjMN7BWtEgOCuzM8gwbn0jD3uvLg@mail.gmail.com>
 <Zt_xfCSMgpVoxggF@depesz.com>
 <CAL9bbZrrBYN8BxW51ihEYFNxDL0BN8WvgSq5x5UzWMs0OKUC4w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: 
 <CAL9bbZrrBYN8BxW51ihEYFNxDL0BN8WvgSq5x5UzWMs0OKUC4w@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Zt_4_jOQNLSLVFtf%40depesz.com>
Precedence: bulk

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