SELECT name, default_version, comment
FROM pg_available_extensions
WHERE name IN ('tencentdb_ai', 'pgvector', 'pgmq', 'vector');

SHOW shared_preload_libraries;

SELECT name, setting, context
FROM pg_settings
WHERE name LIKE 'tencentdb_ai.autoembedding%'
ORDER BY name;

                       name                       | setting  | context
--------------------------------------------------+----------+---------
 tencentdb_ai.autoembedding_batch_size            | 32       | sighup
 tencentdb_ai.autoembedding_database              | postgres | sighup
 tencentdb_ai.autoembedding_max_input_bytes       | 65536    | sighup
 tencentdb_ai.autoembedding_max_retry             | 5        | sighup
 tencentdb_ai.autoembedding_naptime_ms            | 5000     | sighup
 tencentdb_ai.autoembedding_retry_base_ms         | 1000     | sighup
 tencentdb_ai.autoembedding_task_launch_jitter_ms | 10       | sighup
 tencentdb_ai.autoembedding_worker                | on       | sighup
(8 rows)

-- pgcrypto, vector, and pgmq are installed automatically.
CREATE EXTENSION IF NOT EXISTS tencentdb_ai CASCADE;

SELECT * FROM pgmq.list_queues();

             queue_name              | is_partitioned | is_unlogged |          created_at
-------------------------------------+----------------+-------------+-------------------------------
 tencentdb_ai_autoembedding_incr     | f              | f           | 2026-08-05 22:36:41.689961+08
 tencentdb_ai_autoembedding_backfill | f              | f           | 2026-08-05 22:36:41.689961+08
(2 rows)

-- View the structure of the model_list table.
SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema = 'tencentdb_ai' AND table_name = 'model_list'
ORDER BY ordinal_position;

-- View existing models.
SELECT * FROM tencentdb_ai.model_list;

-- Register a model. (The last parameter, backend_type, has a default value of 'hunyuan' and can be omitted.)
SELECT tencentdb_ai.add_model('hunyuan-embedding', '2023-09-01', NULL, NULL);

-- [Enter the SecretId here]
SELECT tencentdb_ai.update_model_attr(
    'hunyuan-embedding',
    'SecretId',
    '<your_SecretId>'  -- Replace with your SecretId
);

-- [Enter the SecretKey here]
SELECT tencentdb_ai.update_model_attr(
    'hunyuan-embedding',
    'SecretKey',
    '<your_SecretKey>'     -- Replace with your SecretKey
);

-- Set the embedding vector dimension.
UPDATE tencentdb_ai.model_list
SET embedding_dim = 1024
WHERE model_name = 'hunyuan-embedding';

-- Verify the model configuration.
SELECT model_name, backend_type, embedding_dim
FROM tencentdb_ai.model_list
WHERE model_name = 'hunyuan-embedding';

 add_model
-----------

(1 row)

 update_model_attr
-------------------

(1 row)

 update_model_attr
-------------------

(1 row)

UPDATE 1

    model_name     | backend_type | embedding_dim
-------------------+--------------+---------------
 hunyuan-embedding | hunyuan      |          1024
(1 row)

NOTICE:  table "kb_articles" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 3
       ?column?
-----------------------
 Test data inserted: 3
(1 row)

-- 5.1 Create an incremental task.
-- Automatically create a content_embedding vector(1024) column.
-- Automatically create INSERT AFTER + UPDATE BEFORE triggers.
SELECT tencentdb_ai.add_incr_autoembedding_task(
    'public', 'kb_articles',
    ARRAY['content'],
    'hunyuan-embedding'
) AS incr_task_id;

-- 5.2 Create a stock task (backfill historical data).
SELECT tencentdb_ai.add_backfill_autoembedding_task(
    'public', 'kb_articles',
    ARRAY['content'],
    'hunyuan-embedding'
) AS backfill_task_id;

 incr_task_id
--------------
            1
(1 row)

 backfill_task_id
------------------
                1
(1 row)

\\d kb_articles

                                    Table "public.kb_articles"
      Column       |     Type     | Collation | Nullable |                 Default
-------------------+--------------+-----------+----------+-----------------------------------------
 id                | bigint       |           | not null | nextval('kb_articles_id_seq'::regclass)
 title             | text         |           |          |
 content           | text         |           |          |
 content_embedding | vector(1024) |           |          |
Indexes:
    "kb_articles_pkey" PRIMARY KEY, btree (id)
Triggers:
    tencentdb_ai_autoemb_ins_1 AFTER INSERT ON kb_articles FOR EACH ROW
        EXECUTE FUNCTION tencentdb_ai._autoembedding_enqueue_trigger(...)
    tencentdb_ai_autoemb_upd_1 BEFORE UPDATE OF content ON kb_articles FOR EACH ROW
        EXECUTE FUNCTION tencentdb_ai._autoembedding_enqueue_trigger(...)

SELECT trigger_name, event_manipulation, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'kb_articles';

SELECT task_kind, task_id, table_name, target_column, status, backfill_state, pending, failed_count
FROM tencentdb_ai.autoembedding_status
ORDER BY task_id;

 task_kind | task_id | table_name  |   target_column   | status  | backfill_state | pending | failed_count
-----------+---------+-------------+-------------------+---------+----------------+---------+--------------
 incr      |       1 | kb_articles | content_embedding | enabled |                |       0 |            0
 backfill  |       1 | kb_articles | content_embedding |         | not_started    |       0 |            0
(2 rows)

SELECT table_name, relid, status FROM tencentdb_ai.autoembedding_incr_task;

 table_name  | relid | status
-------------+-------+---------
 kb_articles | 17136 | enabled
(1 row)

-- Insert new data (insert after the incremental task is created).
INSERT INTO kb_articles (title, content) VALUES
    ('Embedding Model', 'An embedding model is a machine learning model that converts text into vector representations. Common embedding models include BERT, GPT, and Hunyuan Embedding, among others.');

INSERT 0 1

-- Check the pending status immediately. (You should see pending = 1, indicating that it has been enqueued.)
SELECT task_kind, pending FROM tencentdb_ai.autoembedding_status;

 task_kind | pending
-----------+---------
 incr      |       1
 backfill  |       0
(2 rows)

-- Wait for the background worker to process.
SELECT pg_sleep(5);

 pg_sleep
----------

(1 row)

-- Check whether vectors have been generated. (Note: The first 3 rows existed before the task was created and will not be backfilled automatically.)
SELECT id, title,
       content_embedding IS NOT NULL AS has_embedding,
       CASE WHEN content_embedding IS NOT NULL
            THEN vector_dims(content_embedding) ELSE NULL END AS dims
FROM kb_articles
ORDER BY id;

 id |      title      | has_embedding | dims
----+-----------------+---------------+------
  1 | PostgreSQL Introduction | f             |
  2 | Tencent Cloud VectorDB      | f             |
  3 | RAG Technology        | f             |
  4 | Embedding Model        | t             |
(4 rows)

INSERT INTO kb_articles (title, content) VALUES
    ('Hunyuan Large Model', 'Tencent Hunyuan is a general-purpose large language model self-developed by Tencent, featuring powerful natural language understanding and generation capabilities.');

INSERT 0 1

-- Check the pending status immediately.
SELECT task_kind, pending FROM tencentdb_ai.autoembedding_status;

 task_kind | pending
-----------+---------
 incr      |       1
 backfill  |       0
(2 rows)

SELECT pg_sleep(5);

SELECT task_kind, pending, failed_count FROM tencentdb_ai.autoembedding_status;

SELECT id, title, content_embedding IS NOT NULL AS has_embedding
FROM kb_articles ORDER BY id;

-- Error Table
SELECT * FROM tencentdb_ai.autoembedding_error ORDER BY created_at DESC LIMIT 5;

 pg_sleep
----------

(1 row)

 task_kind | pending | failed_count
-----------+---------+--------------
 incr      |       0 |            0
 backfill  |       0 |            0
(2 rows)

 id |      title      | has_embedding
----+-----------------+---------------
  1 | PostgreSQL Introduction | f
  2 | Tencent Cloud VectorDB      | f
  3 | RAG Technology        | f
  4 | Embedding Model        | t
  5 | Hunyuan Large Model      | t
(5 rows)

 error_id | msg_id | task_kind | task_id | row_id | error_code | error_message | detail | created_at
----------+--------+-----------+---------+--------+------------+---------------+--------+------------
(0 rows)

SELECT id, title,
       vector_dims(content_embedding) AS dims,
       length(content_embedding::text) AS text_len
FROM kb_articles
WHERE content_embedding IS NOT NULL;

 id |   title    | dims | text_len
----+------------+------+----------
  4 | Embedding Model   | 1024 |    12727
  5 | Hunyuan Large Model | 1024 |    12779
(2 rows)

-- Updating existing rows triggers the UPDATE trigger even if the content remains unchanged.
UPDATE kb_articles SET content = content WHERE id IN (1,2,3);

UPDATE 3

-- Check the pending status immediately (should be 3).
SELECT task_kind, pending FROM tencentdb_ai.autoembedding_status;

 task_kind | pending
-----------+---------
 incr      |       3
 backfill  |       0
(2 rows)

SELECT pg_sleep(5);

-- Perform a full validation.
SELECT id, title, content_embedding IS NOT NULL AS has_embedding
FROM kb_articles ORDER BY id;

 pg_sleep
----------

(1 row)

 id |      title      | has_embedding
----+-----------------+---------------
  1 | PostgreSQL Introduction | t
  2 | Tencent Cloud VectorDB      | t
  3 | RAG Technology        | t
  4 | Embedding Model        | t
  5 | Hunyuan Large Model      | t
(5 rows)

SELECT id, title,
       content_embedding <=> (
           SELECT content_embedding FROM kb_articles WHERE id = 4
       ) AS distance
FROM kb_articles
WHERE content_embedding IS NOT NULL
ORDER BY distance;

 id |   title    |      distance
----+------------+---------------------
  4 | Embedding Model   |                   0
  5 | Hunyuan Large Model | 0.28689392595353047
(2 rows)

SELECT id, title,
       content_embedding <=> (
           SELECT content_embedding FROM kb_articles WHERE id = 1
       ) AS distance
FROM kb_articles
WHERE content_embedding IS NOT NULL
ORDER BY distance;

 id |      title      |      distance
----+-----------------+---------------------
  1 | PostgreSQL Introduction |                   0
  2 | Tencent Cloud VectorDB      |  0.2789466068930575
  3 | RAG Technology        |  0.3563481692751316
  4 | Embedding Model        |  0.3840361738420558
  5 | Hunyuan Large Model      | 0.40611747180705204
(5 rows)

SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema = 'tencentdb_ai' AND table_name = 'model_list'
ORDER BY ordinal_position;