[Question posted by a user on YugabyteDB Community Slack]
I am currently migrating all my data from PostgreSQL to YugabyteDB, and everything was going fine until I tried to migrate my views table but then I got this error:
ERROR: System column "ctid" is not supported yet and this was the command I used below
CREATE OR REPLACE VIEW public.catalog_entries_v
AS
SELECT e.branch_id,
e.path,
e.physical_address,
e.creation_date,
e.size,
e.checksum,
e.metadata,
e.min_commit,
e.max_commit,
e.min_commit < catalog_max_commit_id() AS is_committed,
e.max_commit < catalog_max_commit_id() AS is_deleted,
e.max_commit = 0 AS is_tombstone,
e.ctid AS entry_ctid
FROM catalog_entries e;
We use the PostgreSQL database for dealing with SQL, and separately store data in our distributed storage layer called DocDB.
That means that we first use PostgreSQL for all its SQL options and represent data in the PostgreSQL format, but when it needs persisting, we change it to our internal DocDB format, and send it to DocDB.
When requested, the data read from DocDB is transformed back to PostgreSQL format and presented to the database client requesting it.
That has some implications. Because a block is created on the fly, we don’t use PostgreSQL internal meta-columns such as CTID, because it’s meant to represent the physical location, which is meaningless for yugabytedb, because the block is created on the fly.
This also means it makes no sense to perform vacuuming in YugabyteDB: we do not store past version tuples in the PostgreSQL data presentation, because we always represent PostgreSQL data as of the requesting time.
YugabyteDB is an LSM-tree based database, which means we only ever add data; even a delete is an addition. There is an internal process called ‘compaction’ which finds deleted (“tombstoned”) rows and past version fields. If these are expired, which means older than the set retention time and not being used, these will be removed.