Use the PG_CATALOG.AI_GENERATE_TEXT(...) function and LLMs to implement text inference - AnalyticDB for PostgreSQL

If you want to perform text inference, classification, induction, and summarization, you can use the PG_CATALOG.AI_GENERATE_TEXT(...) function of AnalyticDB for PostgreSQL to interact with the large language models (LLMs) that are deployed in Elastic Algorithm Service (EAS) of Platform for AI (PAI).

Background information

Artificial Intelligence Generated Content (AIGC) is a new content creation method. AIGC can generate various types of content, such as text, images, and audio, based on machine learning and natural language processing (NLP) technologies.

AnalyticDB for PostgreSQL integrates data analysis with lightweight artificial intelligence (AI) to help most small and medium-sized enterprises meet the requirements for data analysis and AI applications within databases.

AnalyticDB for PostgreSQL uses warehouse intelligence to provide AI text generation. This helps you generate new text similar to the source text in style, tone, and content by using LLMs.

Prerequisites

An AnalyticDB for PostgreSQL instance that meets the following minor version requirements is created. For information about how to view the minor version of an instance, see View the minor engine version.
- AnalyticDB for PostgreSQL V6.0 instance in elastic storage mode: V6.6.1.0 or later.
- AnalyticDB for PostgreSQL V7.0 instance in elastic storage mode: V7.0.4.0 or later.
- AnalyticDB for PostgreSQL V7.0 instance in Serverless mode: cn.V2.1.1.5 or later.
PAI EAS is activated in the same region as the AnalyticDB for PostgreSQL instance. At least one LLM, such as Tongyi Qianwen, ChatGLM, and Llama 2, is deployed in PAI EAS. In this example, Tongyi Qianwen is deployed. For more information, see Quickly deploy Tongyi Qianwen in EAS.
Note
- If you want to perform text inference, induction, and summarization, we recommend that you select Tongyi Qianwen. Compared with other models, Tongyi Qianwen is more efficient in response time and content accuracy.
- For information about how to deploy Llama 2, see Deploy a Llama model as a web application in EAS.

`PG_CATALOG.AI_GENERATE_TEXT(...)` function

Syntax

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

Parameters

Parameter	Required	Description
input_endpoint	Yes	The endpoint that is used to access the LLM deployed in PAI EAS. You can perform the following steps to obtain the endpoint: Log on to the PAI console. In the left-side navigation pane, choose Model Deployment > Elastic Algorithm Service (EAS). On the page that appears, click a service ID. In the Basic Information section of the Service Details tab, click View Endpoint Information. View the endpoint on the VPC Endpoint tab of the Invocation Method dialog box.
input_token	Yes	The token that is used to access the LLM deployed in PAI EAS. You can perform the following steps to obtain the token: Log on to the PAI console. In the left-side navigation pane, choose Model Deployment > Elastic Algorithm Service (EAS). On the page that appears, click a service ID. In the Basic Information section of the Service Details tab, click View Endpoint Information. View the token on the VPC Endpoint tab of the Invocation Method dialog box.
input_prompt	Yes	The source text that is used for inference.
input_additional_parms	No	The additional parameter that you can specify. You can specify the `history` parameter. The `history` parameter provides a context for text inference. You can encapsulate the context that includes a question and an answer in the JSON format. For more information, see the "Best practices: Encapsulate the AI_GENERATE_TEXT function" section of this topic. Format: `{ "history": [ ["question_text",''answer_text"], ["question_text",''answer_text"] ... ] }`. The `history` parameter specifies a keyword to identify the context. The parameter includes two parts: `question_text`: the historical question. `answer_text`: the historical answer. The two parts are of the STRING or TEXT type. If you call the `PG_CATALOG.AI_GENERATE_TEXT(...)` function without specifying the history parameter, NULL is used.

Examples

Example 1: Use the input_prompt parameter to perform text inference.

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

Sample result:

-[RECOND 1]---------------
Answer | The capital of Zhejiang province is Hangzhou.

Example 2: Use the input_additional_parms parameter to provide a context for text inference.

The provided context can improve the accuracy of text inference. In this example, the context is related to provincial capitals. The answer to "How about Jiangsu?" can be inferred to "The capital of Jiangsu province is Nanjing."

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

Sample result:

-[RECOND 1]-------------
Answer | The capital of Jiangsu province is Nanjing.

Best practices: Encapsulate the AI_GENERATE_TEXT function

Each time you use the AI_GENERATE_TEXT function, you must specify the input_endpoint and input_token parameters. This is not concise enough and causes sensitive information explosion in the application. We recommend that you create an encapsulation function to encapsulate the AI_GENERATE_TEXT function. This method provides the following advantages:

Simplifies the use of the AI_GENERATE_TEXT function and masks sensitive information.
Allows secondary processing on the inference result.

Example:

-- Create an encapsulation 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 encapsulation function to simplify usage.
SELECT Wrapper_AI_GENERATE_TEXT('Where is the capital of Zhejiang province? ', null) AS Answer;

---------------------------
Answer | The capital of Zhejiang province is Hangzhou.

Use cases

Warehouse intelligence can summarize the evaluations that are published by consumers on shopping website products to determine whether the evaluations are positive or negative.

Execute the following statements to create a table named feedback_collect and insert product evaluations of consumers into the table:

-- Create a table. 
CREATE TABLE feedback_collect
(
    id int not null,
    name varchar(10),
    age int,
    feedback text,
    AI_evaluation text
) 
distributed by (id);

-- Insert consumer information and product evaluations into the table. 
INSERT INTO feedback_collect VALUES(1, 'John', 38, 'iPhone 15 is beautiful and lightweight and provides powerful capabilities. It features particular colors, characteristic materials, and clear screens. It provides telephoto lens to meet various requirements for taking pictures. The mobile phone provides a USB-C connector to support fast recharges and data transmission. iPhone 15 is easy to use and cost-efficient.');
INSERT INTO feedback_collect VALUES(2, 'Tony', 30, 'The Thinkpad P series laptop is fashionable and lightweight and provides powerful capabilities. It is equipped with a high-definition screen and a soft-touch keyboard. It can be connected over USB, HDMI, or Thunderbolt and supports fast recharges and data transmission. The Thinkpad P series laptop is easy to use and cost-efficient.');
INSERT INTO feedback_collect VALUES(3, 'Mike', 40, 'The mobile phone film is terrible. It is of poor quality and not sticky enough to protect the phone screen. It is an unsatisfactory product that is uncomfortable to use. To save money and time, I strongly recommend that you do not buy this film.');
INSERT INTO feedback_collect VALUES(4, 'Eric', 45, 'The electric toothbrush is really bad. Its battery life is very short. It lasts only for one or two days on each recharge. Its cleaning effect is not as good as expected, and many bacteria remain on the teeth. Most importantly, it is of poor quality with frequent glitches and malfunctions. I am very disappointed with this electric toothbrush. I recommend that you think carefully before making a purchase. ');

Case 1: Determine whether the product evaluations are positive or negative and store the results in the feedback_collect table.

-- Query the values of the AI_evaluation column. No values are displayed in the column. 
SELECT AI_evaluation FROM feedback_collect;

-- Determine whether the product evaluations are positive or negative and store the results in the feedback_collect table. 
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****',
                                                'Determine whether the values of the feedback column are positive or negative evaluations. ',
                                                '{
                                                    "history" : [
                                                      ["This camera takes poor-quality photos, and the battery life is not long enough.", "Negative evaluation."],
                                                      ["This refrigerator is energy-efficient, eco-friendly, and quiet.", " Positive evaluation."]
                                                    ]
                                                 }');
-- Query the AI_evaluation column. The following information is displayed: 
SELECT AI_evaluation FROM feedback_collect;

     ai_evaluation      
------------------------
 This evaluation is positive. 
 Positive evaluation. 
 Negative evaluation. 
 Negative evaluation. 
(4 rows)

Case 2: Summarize and return the negative product evaluations from consumers.

-- Summarize and return the negative product evaluations from consumers. 
SELECT feedback AS Evaluation,
       AI_evaluation Evaluation inference,
       PG_CATALOG.AI_GENERATE_TEXT('http://1648821****.vpc.cn-shanghai.pai-eas.aliyuncs.com/api/predict/test_pg',
                                    'OGZiOGVkNTcwNTRiNzA0ODM1MGY0MTZhZGIwNT****',
                                   'Summarize the values of the feedback column and return the results by using approximately 30 characters. ',
                           NULL) AS Summary       
FROM feedback_collect
WHERE AI_evaluation LIKE '%Negative%';

-- Sample result: 
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Evaluation     | The mobile phone film is terrible. It is of poor quality and not sticky enough to protect the phone screen. It is an unsatisfactory product that is uncomfortable to use. To save money and time, I strongly recommend that you do not buy this film.
Evaluation inference | This evaluation is negative. 
Summary       | This mobile phone film is of poor quality, is not sticky enough, and is uncomfortable to use. I recommend that you do not buy this film. 
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Evaluation     | The electric toothbrush is really bad. Its battery life is very short. It lasts only for one or two days on each recharge. Its cleaning effect is not as good as expected, and many bacteria remain on the teeth. Most importantly, it is of poor quality with frequent glitches and malfunctions. I am very disappointed with this electric toothbrush. I recommend that you think carefully before making a purchase. 
Evaluation inference | Negative evaluation. 
Summary       | This electric toothbrush has a short battery life, is of poor quality, and delivers bad cleaning effects. I recommend that you think carefully before making a purchase.

Important

The inference results of the preceding cases are for reference only. The actual inference results vary based on the LLM such as Tongyi Qianwen or ChatGLM. Even if you use the same LLM, the inference results from multiple executions of the same SQL statement may be different.

Common errors and troubleshooting

Error messages

The following error message is displayed: AI_GENERATE_TEXT error: HTTP request service failure or AI_GENERATE_TEXT error: Incorrect endpoint URL is specified.
Check whether the input_endpoint parameter is correctly specified when you call the PG_CATALOG.AI_GENERATE_TEXT function. You must specify a Virtual Private Cloud (VPC) endpoint. For information about how to obtain the VPC endpoint, see the "PG_CATALOG.AI_GENERATE_TEXT(...) function" section of this topic.
The following error message is displayed: AI_GENERATE_TEXT error: Incorrect endpoint URL is specified.
Check whether the input_token parameter is correctly specified when you call the PG_CATALOG.AI_GENERATE_TEXT function. For information about how to obtain the token, see the "PG_CATALOG.AI_GENERATE_TEXT(...) function" section of this topic.
The following error message is displayed: AI_GENERATE_TEXT error: Invalid JSON syntax against the 4th parameter within UDF.
Check whether the input_additional_parms parameter is correctly specified when you call the PG_CATALOG.AI_GENERATE_TEXT function. The error may be caused by the following reasons:
- The JSON format of the input_additional_parms parameter is invalid.
- The input_additional_parms parameter uses Chinese commas (,), single quotation marks ('), or double quotation marks ("). As a result, the system fails to recognize the JSON data.
The following error message is displayed: function AI_GENERATE_TEXT (unknown, unknown, unknown, unknown) does not exist.
- Check whether the AnalyticDB for PostgreSQL instance meets the minor version requirements. For information about how to view the minor version of an instance, see View the minor engine version.
- When you call the PG_CATALOG.AI_GENERATE_TEXT function, the PG_CATALOG schema is not specified. As a result, the system fails to recognize the function.

Extended response time for the AI_GENERATE_TEXT function

If no results are returned for the PG_CATALOG.AI_GENERATE_TEXT function after an extended period of time, you can perform the following operations to identify the cause:

Make sure that the AnalyticDB for PostgreSQL instance resides in the same region as PAI EAS in which the LLM is deployed.
Make sure that values of the input_endpoint and input_token parameters are the same as the deployment information of the LLM. For more information, see the "Parameters" section of this topic.
Check whether the LLM has performance issues. In most cases, LLMs respond to a request within 2 seconds to 3 seconds.
Note
The preceding value is for reference only. The actual response time is subject to the following elements: the length of the source text, length of the inference result, LLM performance, specifications of the instance on which the LLM is deployed, and number of instances on which the LLM is deployed.
For example, use the SELECT pg_catalog.ai_generate_text (...) where column >= ... statement to perform text inference. You can use the following methods to improve the speed of text inference:
- Increase the number of instances on which the LLM is deployed.
  1. Estimate the amount of time that is required for the LLM to perform text inference.
    Execute the SELECT COUNT(*) WHERE column >= ... statement to query the number of data records to be processed by the LLM. For example, the result is 1,000. Use several data records to calculate the amount of time that is required for the LLM to perform text reference. For example, execute the SELECT pg_catalog.ai_generate_text (...) LIMIT 100 statement to calculate the amount of time that is required for the LLM to process 100 data records. You can record the system timestamps before and after you execute the preceding statement in your application to calculate the execution duration. The result multiplied by 10 is the amount of time that is required for the LLM to process 1,000 data records.
  2. Increase the number of instances on which the LLM is deployed based on the estimated amount of time. For more information, see the "Appendix: Suggestions on the specifications of instances on which LLMs are deployed" section of this topic.
    You can use the following methods to increase the number of instances on which the LLM is deployed:
    - When you deploy the LLM, select multiple instances. For more information, see Model service deployment by using the PAI console.
    - After you deploy the LLM, use horizontal scaling to increase the number of instances. For more information, see Enable or disable the horizontal auto-scaling feature.
    - Enable the auto-scaling feature to dynamically increase the number of instances based on the system loads. For more information, see Enable or disable the scheduled auto-scaling feature.
- For long-running SQL statements, configure the following parameter settings to prevent statement timeout errors:
  - SET idle_in_transaction_session_timeout =5h;: sets the session timeout period to 5 hours. Specify an appropriate timeout period based on your business requirements.
  - SET statement_timeout = 0;: configures the statement to never time out. Perform this configuration based on your business requirements.

Appendix: Suggestions on the specifications of instances on which LLMs are deployed

GPU provides computing resources for LLMs. If you want the LLMs to process requests in a shorter amount of time, you can select higher GPU specifications.

For the same number of GPU cores, a higher number of instances delivers more improvements in the LLM processing performance.

For example, compared with selecting an instance that uses a four-core GPU, selecting four instances that use a single-core GPU improves more LLM processing performance at the same costs.

References

For more information about PAI EAS, see EAS overview.