In-database AI (public preview)

Background information

Artificial Intelligence Generated Content (AIGC) is an emerging AI technology that automatically generates content such as text, images, and audio using machine learning and natural language processing.

AnalyticDB for PostgreSQL is an integrated platform for data analytics and lightweight AI. It helps most small and medium-sized users achieve their primary goal—data analytics—with AI applications as a supplement, all within the database, thereby enhancing data analytics with AI capabilities.

The AIGC in-database AI feature in AnalyticDB for PostgreSQL provides AI text generation services. It uses an LLM service to generate (or infer) new text that matches the style, tone, and content of the input data.

Prerequisites

• Your AnalyticDB for PostgreSQL instance must meet one of the following minor engine version requirements. To check your instance’s minor engine version, see View minor engine version.

  • For Storage Elastic Edition 6.0 instances, the minor engine version must be v6.6.1.0 or later.

  • For Storage Elastic Edition 7.0 instances, the minor engine version must be v7.0.4.0 or later.

  • For Serverless instances, the minor engine version must be cn.v2.1.1.5 or later.

• You must have activated PAI Elastic Algorithm Service (EAS) in the same region as your AnalyticDB for PostgreSQL instance and deployed at least one LLM service (such as Qwen, ChatGLM, or Llama2). This topic uses Qwen as an example. For instructions, see Deploy Qwen with one-click on EAS in 5 minutes.

  Note

  • When performing inference, summarization, or similar operations on Chinese text, use the Qwen model. In tests against other models, Qwen demonstrates strong performance in both response time and accuracy of generated content.

  • To deploy ChatGLM, see Deploy ChatGLM and LangChain applications with one-click on EAS in 5 minutes.

  • To deploy Llama2, see One-click deployment of Llama2-WebUI on EAS.

PG_CATALOG.AI_GENERATE_TEXT(...) function overview

Syntax

FUNCTION PG_CATALOG.AI_GENERATE_TEXT
(input_endpoint text,
input_token text,
input_prompt text,
input_additional_parms text)
RETURNS TEXT
......

Parameters

Examples

• Example 1: Perform language inference using only the input_prompt parameter.

  SELECT PG_CATALOG.AI_GENERATE_TEXT('http://1648821****.vpc.cn-shanghai.pai-eas.aliyuncs.com/api/predict/test_pg',
                                     'OGZiOGVkNTcwNTRiNzA0ODM1MGY0MTZhZGIwNT****',
                                     'What is the capital of Zhejiang Province?',
                                      NULL) AS reply;

  The result is:

  -[RECORD 1]---------------
  reply | The capital of Zhejiang Province is Hangzhou.

• Example 2: Provide context using the input_additional_parms parameter to improve inference accuracy.

  Adding relevant context improves inference accuracy. In this example, the context relates to provincial capitals, so the model correctly answers “The capital of Jiangsu Province is Nanjing” instead of providing unrelated information about Jiangsu.

  SELECT PG_CATALOG.AI_GENERATE_TEXT('http://1648821****.vpc.cn-shanghai.pai-eas.aliyuncs.com/api/predict/test_pg',
                                     'OGZiOGVkNTcwNTRiNzA0ODM1MGY0MTZhZGIwNT****',
                                     'What about Jiangsu?',
                                      '{ "history" : [
                                                        ["What is the capital of Zhejiang?", "The capital of Zhejiang is Hangzhou."],
                                                        ["What is the capital of Liaoning?", "The capital of Liaoning is Shenyang."]
                                                      ]
                                      }') as reply;   

  The result is:

  -[RECORD 1]-------------
  reply | The capital of Jiangsu Province is Nanjing.

Best practices: Encapsulating functionsAI_GENERATE_TEXT

Each call to AI_GENERATE_TEXT requires long values for input_endpoint and input_token. This makes usage cumbersome and exposes sensitive information in your application. Create a custom wrapper function to encapsulate AI_GENERATE_TEXT. This approach:

• Simplifies usage and hides sensitive information.

• Allows post-processing of generated content.

Encapsulated function AI_GENERATE_TEXT example:

-- Define the wrapper function:
CREATE OR REPLACE FUNCTION Wrapper_AI_GENERATE_TEXT(prompt text, context text) 
RETURNS TEXT AS
$$
DECLARE
   result text := ' ';
BEGIN
    SELECT PG_CATALOG.AI_GENERATE_TEXT ('<... use_your_endpoint ...>',
                                        '<... use_your_token ...>', 
                                        prompt, context) INTO result;
    IF result IS NOT NULL 
       AND result LIKE '%some_condition%' THEN 
      result := '<... your_expected_value ...> ';
    END IF; 
    RETURN result;
END;
$$ LANGUAGE plpgsql;
-- Use the wrapper function for simpler calls:
SELECT Wrapper_AI_GENERATE_TEXT('What is the capital of Zhejiang?', null) AS reply;
---------------------------
reply | The capital of Zhejiang Province is Hangzhou.

Use cases

Summarize customer reviews posted on an e-commerce site and classify them as positive or negative feedback.

Run the following statement to create a table named feedback_collect that stores customer product reviews.

-- Create the table.
CREATE TABLE feedback_collect
(
    id int not null,
    name varchar(10),
    age int,
    feedback text,
    AI_evaluation text
) 
distributed by (id);
-- Insert customer information and product reviews.
INSERT INTO feedback_collect VALUES(1, 'Zhang San', 38, 'The iPhone 15 has a beautiful design, is lightweight and portable, and offers powerful features. It comes in unique colors and materials, with a clear screen and smooth operation. The camera delivers excellent results with telephoto support, meeting diverse user needs. It also uses a USB-C port for fast charging and data transfer. Overall, the iPhone 15 is easy to use and offers great value.');
INSERT INTO feedback_collect VALUES(2, 'Li Si', 30, 'The ThinkPad P-series laptop has a stylish design, is lightweight and portable, and offers powerful performance. It features a high-definition screen, smooth operation, a comfortable keyboard, and excellent performance. It supports multiple connection options including USB, HDMI, and Thunderbolt, along with fast charging and data transfer. The ThinkPad P-series is easy to use and offers great value.');
INSERT INTO feedback_collect VALUES(3, 'Wang Wu', 40, 'This phone screen protector is terrible! First, the quality is poor and it feels very rough to use. Second, its adhesive is weak—it lost stickiness within days and no longer protects my screen. Most importantly, it negatively affects my user experience, leaving me very dissatisfied. I strongly advise against buying this protector to avoid wasting time and money.');
INSERT INTO feedback_collect VALUES(4, 'Sun Liu', 45, 'This electric toothbrush is awful! First, its battery life is extremely short—each charge lasts only a day or two. Second, its cleaning performance falls short of expectations, leaving significant plaque behind. Most importantly, its build quality is poor, causing frequent stuttering and malfunctions. I am very disappointed and recommend careful consideration before purchase.');

• Scenario 1: Automatically classify each review as positive or negative and store the result in the feedback_collect table.

  -- Check the AI_evaluation column—it is currently empty.
  SELECT AI_evaluation FROM feedback_collect;
  -- Classify each review and save the result.
  UPDATE feedback_collect
  SET AI_evaluation = PG_CATALOG.AI_GENERATE_TEXT('http://1648821****.vpc.cn-shanghai.pai-eas.aliyuncs.com/api/predict/test_pg',
                                                  'OGZiOGVkNTcwNTRiNzA0ODM1MGY0MTZhZGIwNT****',
                                                  'Classify the following text as positive or negative: “'|| feedback ||'”.',
                                                  '{
                                                      "history" : [
                                                        ["The camera takes poor photos and has bad battery life!", "Negative review."],
                                                        ["This refrigerator is energy-efficient, eco-friendly, and quiet.", "Positive review."]
                                                      ]
                                                   }');
  -- Check the AI_evaluation column again.
  SELECT AI_evaluation FROM feedback_collect;
       ai_evaluation      
  ------------------------
   This review is negative.
   Negative review.
   Positive review.
   Positive review.
  (4 rows)

• Scenario 2: Summarize negative reviews and display the results.

  -- Summarize negative reviews.
  SELECT feedback AS original_review,
         AI_evaluation AS original_classification,
         PG_CATALOG.AI_GENERATE_TEXT('http://1648821****.vpc.cn-shanghai.pai-eas.aliyuncs.com/api/predict/test_pg',
                                      'OGZiOGVkNTcwNTRiNzA0ODM1MGY0MTZhZGIwNT****',
                                     'Summarize the following text in about 30 words: “'|| feedback ||'”.',
                             NULL) AS summary       
  FROM feedback_collect
  WHERE AI_evaluation LIKE '%negative%';
  -- Results:
  -[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  original_review | This phone screen protector is terrible! First, the quality is poor and it feels very rough to use. Second, its adhesive is weak—it lost stickiness within days and no longer protects my screen. Most importantly, it negatively affects my user experience, leaving me very dissatisfied. I strongly advise against buying this protector to avoid wasting time and money.
  original_classification | This review is negative.
  summary       | This screen protector has poor quality, weak adhesion, and harms user experience—avoid purchasing.
  -[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  original_review | This electric toothbrush is awful! First, its battery life is extremely short—each charge lasts only a day or two. Second, its cleaning performance falls short of expectations, leaving significant plaque behind. Most importantly, its build quality is poor, causing frequent stuttering and malfunctions. I am very disappointed and recommend careful consideration before purchase.
  original_classification | Negative review.
  summary       | This toothbrush has short battery life, poor cleaning performance, and low build quality—consider carefully before buying.

FAQ and solutions

Error messages

• Error: AI_GENERATE_TEXT error: HTTP request service failure or AI_GENERATE_TEXT error: Incorrect endpoint URL is specified.

  Verify that the input_endpoint parameter in your PG_CATALOG.AI_GENERATE_TEXT call uses the correct VPC address. For how to obtain the endpoint, see PG_CATALOG.AI_GENERATE_TEXT(...) function overview.

• Error: AI_GENERATE_TEXT error: Incorrect endpoint URL is specified.

  Verify that the input_token parameter in your PG_CATALOG.AI_GENERATE_TEXT call is correct. For how to obtain the token, see PG_CATALOG.AI_GENERATE_TEXT(...) function overview.

• Error: AI_GENERATE_TEXT error: Invalid JSON syntax against the 4th parameter within UDF.

  Check the input_additional_parms parameter in your PG_CATALOG.AI_GENERATE_TEXT call. This error may occur because:

  • The JSON format of input_additional_parms is invalid.

  • input_additional_parms uses Chinese punctuation (such as commas, single quotes, or double quotes), which breaks JSON parsing.

• Error: function AI_GENERATE_TEXT (unknown, unknown, unknown, unknown) does not exist.

  • Verify that your AnalyticDB for PostgreSQL instance meets the minor engine version requirement. To check your version, see View minor engine version.

  • You did not specify the schema PG_CATALOG when calling PG_CATALOG.AI_GENERATE_TEXT, so the system cannot find the function.

No response after calling AI_GENERATE_TEXT

If PG_CATALOG.AI_GENERATE_TEXT takes too long to return a result, check the following:

• Ensure your AnalyticDB for PostgreSQL instance and your LLM service on PAI-EAS are in the same region.

• Ensure the input_endpoint and input_token match your LLM service deployment details. For details, see Parameters.

• Model performance issues. Typically, an LLM service processes a single request in about 2–3 seconds.

  Note

  This is only a reference. Response time depends on the length of input text, the length of generated output, the model’s inherent performance, the hardware specification of the deployed instance, and the number of deployed instances.

  For example, if you run SELECT pg_catalog.ai_generate_text (...) WHERE column >= ... to process many rows, try these methods to improve speed:

  • Increase the number of LLM service instances.

    1. First, estimate processing time.

       Calculate the total number of rows to process using SELECT COUNT(*) WHERE column >= ... . Suppose the result is 1000. Sample a few rows and measure LLM processing time—for example, run SELECT pg_catalog.ai_generate_text (...) LIMIT 100 and record timestamps before and after execution to measure duration. Multiply this time by 10 to estimate total processing time.

    2. Then, based on this estimate, increase the number of LLM service instances. For instance configuration recommendations, see Appendix: Recommended LLM service instance configurations.

       You can increase instance count in several ways:

       • During deployment, select multiple node instances. For instructions, see Custom deployment.

       • If already deployed, scale out to add more instances. For instructions, see Horizontal auto-scaling.

       • Enable auto-scaling to dynamically adjust instance count based on load. For instructions, see Scheduled auto-scaling.

  • For long-running SQL statements, set appropriate timeout parameters to prevent premature termination. Examples:

    • SET idle_in_transaction_session_timeout = 5h; sets session timeout to 5 hours (adjust as needed).

    • SET statement_timeout = 0; disables statement timeout (use with caution).

Appendix: Recommended LLM service instance configurations

LLM services rely primarily on GPU compute resources. If low response time is critical, choose higher-spec GPUs.

GPU core allocation: With the same total number of GPU cores, using more instances yields better performance than using fewer, larger instances.

For example, deploying four single-core GPU instances (e.g., ecs.gn6e-c12g1.3xlarge with 1 × NVIDIA V100 each) delivers better request processing performance than one four-core instance (ecs.gn6e-c12g1.12xlarge with 4 × NVIDIA V100), even though both configurations cost the same. Set the instance count to 4 and choose the single-GPU spec ecs.gn6e-c12g1.3xlarge for optimal throughput.

References

For more information about Alibaba Cloud PAI Elastic Algorithm Service (EAS), see EAS overview.