Re: index question - Mailing list pgsql-general

CREATE TABLE gorfs.inode_segments
(
  st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key to ensure uniqueness per relevant scope
  segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details. The meaning of this column varies based on the host inode type:...
  st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in the directory)
  full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful only for directory inode segments (objects in the directory)
  segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
  CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
  CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
  CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
)

CREATE TABLE gorfs.inodes
(
  st_dev "gorfs"."dev_t" DEFAULT NULL::bigint, -- ID of device containing file. Meaningless in this implementation....
  st_ino "gorfs"."ino_t" NOT NULL DEFAULT "nextval"('"gorfs"."inodes_st_ino_idseq"'::"regclass"), -- Inode number....
  st_mode "gorfs"."mode_t" NOT NULL, -- File type/mode bits....
  st_nlink "gorfs"."nlink_t" NOT NULL, -- Number of hard links (directory segments) pointing to this inode. See stat(2) manual page for details (man 2 stat)
  st_uid "gorfs"."uid_t" NOT NULL, -- User ID that owns the file. See stat(2) manual page for details (man 2 stat)
  st_gid "gorfs"."gid_t" NOT NULL, -- Group ID that owns the file.See stat(2) manual page for details (man 2 stat)
  st_rdev "gorfs"."dev_t", -- Device number (currently we don't support device files).  See stat(2) manual page for details (man 2 stat)
  st_size "gorfs"."off_t", -- File size, if applicable. See stat(2) manual page for details (man 2 stat)
  st_blksize "gorfs"."blksize_t", -- Block size for I/O. Meaningless here, hard coded to 512. See stat(2) manual page for details (man 2 stat)
  st_blocks "gorfs"."blkcnt_t", -- Number of allocated blocks. Meaningless here, but calculated from block size. See stat(2) manual page for details (man 2 stat)
  st_atime "gorfs"."time_t" NOT NULL, -- Timestamp of last access. Stored as a timestamp as opposed to unix TS. See stat(2) manual page for details (man 2 stat)
  st_mtime "gorfs"."time_t" NOT NULL, -- Timestamp of last modification. Stored as a timestamp as opposed to unix TS. See stat(2) manual page for details (man 2 stat)
  st_ctime "gorfs"."time_t" NOT NULL, -- Timestamp of last change. Stored as a timestamp as opposed to unix TS. See stat(2) manual page for details (man 2 stat)
  checksum_md5 "md5_hash", -- MD5 checksum of the file. Supplied by the application as the DB might not even see the payload
  media_subtype_id integer, -- Reference to MIME type (see FK constraint). We can't support all media types but unknow types can be stored as application/octet-stream
  external_size "gorfs"."off_t", -- For symlinks only. Meaningful for fat links only: total size of the fat link target. Null for normal symlinks
  CONSTRAINT pk_inodes PRIMARY KEY ("st_ino"),
  CONSTRAINT fk_media_subtype_must_exist FOREIGN KEY (media_subtype_id)
      REFERENCES public.media_subtypes (media_subtype_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT cc_mount_devices_not_supported CHECK ("st_dev" IS NULL)
)