Search Postgresql Archives

Re: Oddity that I don't understand

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I see why…  The select of the view is picking d.ino which is null because there is no match in the dirents table.

Thanks guys!

On Aug 11, 2022, at 08:23, Perry Smith <pedz@xxxxxxxxxxxxxxxx> wrote:

I’m tempted to ask “Is this a bug” but I predict there is an explanation.

I have a view:
find_dups=# \sv+ dateien
1       CREATE OR REPLACE VIEW public.dateien AS
2        SELECT d.id,
3           d.basename,
4           d.parent_id,
5           d.ino,
6           d.ext,
7           i.ftype,
8           i.uid,
9           i.gid,
10          i.mode,
11          i.mtime,
12          i.nlink,
13          i.size,
14          i.sha1,
15          i.file_type
16         FROM dirents d
17           FULL JOIN inodes i USING (ino)

find_dups=# \d inodes
                            Table "public.inodes"
   Column   |              Type              | Collation | Nullable | Default 
------------+--------------------------------+-----------+----------+---------
 ino        | bigint                         |           | not null | 
 ftype      | character varying              |           | not null | 
 uid        | bigint                         |           | not null | 
 gid        | bigint                         |           | not null | 
 mode       | bigint                         |           | not null | 
 mtime      | timestamp without time zone    |           | not null | 
 nlink      | bigint                         |           | not null | 
 size       | bigint                         |           | not null | 
 sha1       | character varying              |           |          | 
 created_at | timestamp(6) without time zone |           | not null | 
 updated_at | timestamp(6) without time zone |           | not null | 
 file_type  | character varying              |           |          | 
Indexes:
    "inodes_pkey" PRIMARY KEY, btree (ino)
    "index_inodes_on_ftype_and_size_and_file_type_and_sha1_and_nlink" btree (ftype, size, file_type, sha1, nlink)
Referenced by:
    TABLE "dirents" CONSTRAINT "fk_rails_f076303053" FOREIGN KEY (ino) REFERENCES inodes(ino)

find_dups=# \d dirents
                                          Table "public.dirents"
   Column   |              Type              | Collation | Nullable |               Default               
------------+--------------------------------+-----------+----------+-------------------------------------
 id         | bigint                         |           | not null | nextval('dirents_id_seq'::regclass)
 basename   | character varying              |           | not null | 
 parent_id  | bigint                         |           |          | 
 ino        | bigint                         |           | not null | 
 created_at | timestamp(6) without time zone |           | not null | 
 updated_at | timestamp(6) without time zone |           | not null | 
 ext        | character varying              |           |          | 
Indexes:
    "dirents_pkey" PRIMARY KEY, btree (id)
    "index_dirents_on_basename" btree (basename)
    "index_dirents_on_ext" btree (ext)
    "index_dirents_on_ino" btree (ino)
    "index_dirents_on_parent_id_and_basename" UNIQUE, btree (parent_id, basename)
Foreign-key constraints:
    "fk_rails_4dfefe0fc0" FOREIGN KEY (parent_id) REFERENCES dirents(id) ON DELETE CASCADE
    "fk_rails_f076303053" FOREIGN KEY (ino) REFERENCES inodes(ino)
Referenced by:
    TABLE "dirents" CONSTRAINT "fk_rails_4dfefe0fc0" FOREIGN KEY (parent_id) REFERENCES dirents(id) ON DELETE CASCADE

 I do a select and I get:

find_dups=# select id, basename, ext, parent_id, ino, sha1 from dateien where sha1 = '36f53d60353e0de6ed55d9da70a36b17559039f3' order by parent_id;
    id    |   basename   | ext | parent_id |   ino    |                   sha1                   
----------+--------------+-----+-----------+----------+------------------------------------------
 85276821 | VC5Y8191.CR2 | CR2 |  85273064 | 70163023 | 36f53d60353e0de6ed55d9da70a36b17559039f3
 85829158 | VC5Y8191.CR2 | CR2 |  85827904 |    79366 | 36f53d60353e0de6ed55d9da70a36b17559039f3
          |              |     |           |          | 36f53d60353e0de6ed55d9da70a36b17559039f3
(3 rows)

How can the third line exist?  Or, perhaps I should ask, what is the third line telling me?

Thank you,
Perry




Attachment: signature.asc
Description: Message signed with OpenPGP


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux