PolarDB

https://help.aliyun.com/zh/polardb
Admin
PolarDB is Alibaba's self-developed cloud-native database, offering highly elastic,...
Tokens:3,161,733
Snippets:15,051
Trust Score:10
Update:1 day ago
Show doc for...
Tokens:
Raw
### Example: Adjust active connection threshold and verify

Source: https://help.aliyun.com/zh/polardb/polardb-for-postgresql/garbage-collection-during-maintainable-window

Example output showing how to get the default threshold, check the actual active connection count, set a new threshold, verify it, and reset to default. The output shows the NOTICE messages and result values.

```sql
-- 获取实例默认活跃连接数阈值，该实例阈值为5（不同实例的阈值可能不同，具体与CPU核数有关）
postgres=# SELECT polar_advisor.get_active_user_conn_num_limit();
NOTICE:  get active user conn limit by CPU cores number
 get_active_user_conn_num_limit
--------------------------------
               5
(1 row)

-- 获取当前实际活跃连接数，结果为8，大于上面获取的阈值5，因此系统会认为活跃连接数较多，不能在业务低谷期时间内执行垃圾回收
postgres=# SELECT COUNT(*) FROM pg_catalog.pg_stat_activity sa JOIN pg_catalog.pg_user u ON sa.usename = u.usename
postgres-# WHERE sa.state = 'active' AND sa.backend_type = 'client backend' AND NOT u.usesuper;
 count
-------
    8
(1 row)

-- 将活跃连接数阈值设为10，大于实际的活跃连接数8，系统会认为实际的活跃连接数8没有超过阈值10，可以执行垃圾回收
postgres=# SELECT polar_advisor.set_active_user_conn_num_limit(10);
 set_active_user_conn_num_limit
--------------------------------

(1 row)

-- 查看活跃连接数阈值，显示为10，就是上一步手动设置的值
postgres=# SELECT polar_advisor.get_active_user_conn_num_limit();
NOTICE:  get active user conn limit from table
  get_active_user_conn_num_limit
--------------------------------
               10
(1 row)

-- 取消设置活跃连接数阈值
postgres=# SELECT polar_advisor.unset_active_user_conn_num_limit();
 unset_active_user_conn_num_limit
----------------------------------

(1 row)

-- 取消设置以后，活跃连接数阈值恢复到默认值5
postgres=# SELECT polar_advisor.get_active_user_conn_num_limit();
NOTICE:  get active user conn limit by CPU cores number
 get_active_user_conn_num_limit
--------------------------------
               5
(1 row)
```

--------------------------------

### Install lib/pq driver for Go

Source: https://help.aliyun.com/zh/polardb/polardb-for-oracle/connect-to-polardb

Install the lib/pq PostgreSQL driver for Go using go get. This is a prerequisite for the Go connection example.

```bash
go get -u github.com/lib/pq
```

--------------------------------

### Prepare test data for OUTLINE example

Source: https://help.aliyun.com/zh/polardb/polardb-for-postgresql/statement-outline

Creates a table with a primary key and an index, inserts 100,000 rows, and runs ANALYZE to prepare statistics for the OUTLINE example.

```sql
CREATE TABLE t(a int,b int,PRIMARY KEY(a));
CREATE INDEX ON t(b);
INSERT INTO t SELECT i,i FROM generate_series(1,100000)i;
ANALYZE t;
```

--------------------------------

### Example: Modify Starting Value

Source: https://help.aliyun.com/zh/polardb/polardb-for-xscale/alter-sequence

Modifies the starting value of sequence seq1 to 1000000.

```plaintext
ALTER SEQUENCE seq1 START WITH 1000000;
```

--------------------------------

### Install psycopg2-binary for Python

Source: https://help.aliyun.com/zh/polardb/polardb-for-oracle/connect-to-polardb

Install the psycopg2 library using pip3. This is a prerequisite for the Python connection example.

```bash
pip3 install psycopg2-binary
```

--------------------------------

### Example output for disabling and enabling advisor window

Source: https://help.aliyun.com/zh/polardb/polardb-for-postgresql/garbage-collection-during-maintainable-window

Sample output showing the window status before and after disabling and enabling, demonstrating the expected results.

```sql
-- 窗口已开启
postgres=# SELECT polar_advisor.is_advisor_window_enabled();
 is_advisor_window_enabled
---------------------------
    t
(1 row)

-- 关闭窗口
postgres=# SELECT polar_advisor.disable_advisor_window();
 disable_advisor_window
------------------------

(1 row)

-- 窗口已关闭
postgres=# SELECT polar_advisor.is_advisor_window_enabled();
 is_advisor_window_enabled
---------------------------
    f
(1 row)

-- 重新开启窗口
postgres=# SELECT polar_advisor.enable_advisor_window();
 enable_advisor_window
-----------------------

(1 row)

-- 窗口已开启
postgres=# SELECT polar_advisor.is_advisor_window_enabled();
 is_advisor_window_enabled
---------------------------
    t
(1 row)
```

--------------------------------

### Example output for viewing advisor window

Source: https://help.aliyun.com/zh/polardb/polardb-for-postgresql/garbage-collection-during-maintainable-window

Sample output from the view commands, showing the window details, length in hours, whether current time is in the window, and seconds to next start/end.

```sql
-- 查看业务低谷期详情
postgres=# SELECT * FROM polar_advisor.get_advisor_window();
 start_time  |  end_time   | enabled | last_error_time | last_error_detail | others
-------------+-------------+---------+-----------------+-------------------+--------
 23:00:00+08 | 02:00:00+08 | t       |                 |                   |
(1 row)

-- 查看业务低谷期时间长度
postgres=# SELECT polar_advisor.get_advisor_window_length() / 3600.0 AS "窗口长度/h";
     窗口长度/h
--------------------
 3.0000000000000000
(1 row)

-- 查看当前时间是否在窗口时间内
postgres=# SELECT now(), * FROM polar_advisor.is_in_advisor_window();
              now              | is_in_advisor_window
-------------------------------+----------------------
 2024-04-01 07:40:37.733911+00 | f
(1 row)

-- 查看当前时间距离下一次窗口开始时间的时长
postgres=# SELECT * FROM polar_advisor.get_secs_to_advisor_window_start();
 secs_to_window_start |      time_now      | window_start | window_end
----------------------+--------------------+--------------+-------------
         26362.265179 | 07:40:37.734821+00 | 15:00:00+00  | 18:00:00+00
(1 row)

-- 查看当前时间距离下一次窗口结束时间的时长
postgres=# SELECT * FROM polar_advisor.get_secs_to_advisor_window_end();
 secs_to_window_end |      time_now      | window_start | window_end
--------------------+--------------------+--------------+-------------
       36561.870337 | 07:40:38.129663+00 | 15:00:00+00  | 18:00:00+00
(1 row)
```

--------------------------------

### dblink_error_message example

Source: https://help.aliyun.com/zh/polardb/polardb-for-postgresql/dblink-plugin-1693982715325

Example query that calls dblink_error_message on connection 'dtest1' to get the last error message.

```sql
SELECT dblink_error_message('dtest1');
```

--------------------------------

### RANGE partition table creation and query example

Source: https://help.aliyun.com/zh/polardb/polardb-for-mysql/optimizer-based-query-conversion-in-mysql-8

Shows the SQL to create a RANGE partitioned table R2 and a sample query that will be pruned based on the range condition.

```sql
CREATE TABLE R2  (
    a INT,
    d INT
) PARTITION BY RANGE(a) (
        PARTITION p20 VALUES LESS THAN (20),
        PARTITION p40 VALUES LESS THAN (40),
        PARTITION p60 VALUES LESS THAN (60),
        PARTITION p80 VALUES LESS THAN (80),
        PARTITION p100 VALUES LESS THAN MAXVALUE
);
```

```sql
Select * From R2 where a > 40 and a < 80;
```

--------------------------------

### ST_intersection example query

Source: https://help.aliyun.com/zh/polardb/polardb-for-postgresql/st-intersection-2

Example SQL query that calls ST_intersection with a trajectory column, a start and end timestamp, and a LINESTRING geometry, selecting from the traj_table.

```plaintext
Select ST_intersection(traj, '2010-1-1 13:00:00', '2010-1-1 14:00:00', 'LINESTRING(0 0, 5 5, 9 9)'::geometry) from  traj_table;
```

--------------------------------

### Example psql command with actual values

Source: https://help.aliyun.com/zh/polardb/polardb-for-postgresql/migrate-data-from-a-user-created-postgresql-database-to-polardb-for-postgresql

Example of the psql command with sample values for username, hostname, port, database name, and role info file.

```plaintext
psql -U gctest -h pc-xxxxxxxx.pg.polardb.cn-qd-pldb1.rds.aliyuncs.com -d testdb -p 1921 -f roleinfo.sql
```

--------------------------------

### Example: Convert to Group Sequence

Source: https://help.aliyun.com/zh/polardb/polardb-for-xscale/alter-sequence

Converts sequence seq2 to a Group Sequence with a starting value of 2000000.

```plaintext
ALTER SEQUENCE seq2 CHANGE TO GROUP START WITH 2000000;
```

--------------------------------

### ST_MakeTrajectory with start and end timestamps

Source: https://help.aliyun.com/zh/polardb/polardb-for-postgresql/st-maketrajectory-2

Creates a trajectory using separate start and end timestamp arguments ('2010-01-01 14:30'::timestamp and '2010-01-01 15:30'::timestamp) instead of a tsrange. The output is identical to the timestamp range example.

```sql
-- (2) ST_MakeTrajectory with start timestamp and end timestamp
select ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326), '2010-01-01 14:30'::timestamp, '2010-01-01 15:30'::timestamp, '{"leafcount":3,"attributes":{"velocity": {"type": "integer", "length": 2,"nullable" : true,"value": [120, 130, 140]}, "accuracy": {"type": "float", "length": 4, "nullable" : false,"value": [120, 130, 140]}, "bearing": {"type": "float", "length": 8, "nullable" : false,"value": [120, 130, 140]}, "vesname": {"type": "string", "length": 20, "nullable" : true,"value": ["adsf", "sdf", "sdfff"]}, "active": {"type": "timestamp", "nullable" : false,"value": ["Fri Jan 01 14:30:00 2010", "Fri Jan 01 15:00:00 2010", "Fri Jan 01 15:30:00 2010"]}}, "events": [{"1" : "Fri Jan 01 14:30:00 2010"}, {"2" : "Fri Jan 01 15:00:00 2010"}, {"3" : "Fri Jan 01 15:30:00 2010"}]}');                        st_maketrajectory         
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 {"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2010-01-01 14:30:00","end_time":"2010-01-01 15:30:00","spatial":"SRID=4326;LINESTRING(114 35,115 36,116 37)","timeline":["2010-01-01 14:30:00","2010-01-01 15:00:00","2010-01-01 15:30:00"],"attributes":{"leafcount":3,"velocity":{"type":"integer","length":2,"nullable":true,"value":[120,130,140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120.0,130.0,140.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120.0,130.0,140.0]},"vesname":{"type":"string","length":20,"nullable":true,"value":["adsf","sdf","sdfff"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["2010-01-01 14:30:00","2010-01-01 15:00:00","2010-01-01 15:30:00"]}},"events":[{"1":"2010-01-01 14:30:00"},{"2":"2010-01-01 15:00:00"},{"3":"2010-01-01 15:30:00"}]}}
(1 row)
```

--------------------------------

### Complete example: archive and delete OSS data

Source: https://help.aliyun.com/zh/polardb/polardb-for-mysql/user-guide/archive-data-in-ibd-format

This example shows the full workflow: create a database, create a table, create a stored procedure to populate data, archive to OSS, query, drop the table and database, and finally delete the OSS file. Execute in a test database.

```sql
CREATE DATABASE oss;
```

```sql
USE oss;
```

```sql
CREATE TABLE `toss1`(
  `uid` int NOT NULL AUTO_INCREMENT,
  `text_data` text NOT NULL,
  `save_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(`uid`)
) ENGINE = InnoDB;
```

```sql
DELIMITER |
CREATE PROCEDURE populate(IN `cnt` INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE (i <= cnt) DO
    INSERT INTO `toss1` (`text_data`) VALUES (REPEAT("#", 6333));
    SET i = i + 1;
  END WHILE;
END |
DELIMITER ;
```

```sql
CALL populate(5000);
```

```sql
ALTER TABLE `toss1` STORAGE_TYPE OSS;
```

```sql
SELECT COUNT(*) FROM `toss1`;
```

```sql
DROP TABLE `toss1`;
```

```sql
DROP DATABASE oss;
```

```sql
CALL dbms_oss.delete_table_file('oss', 'toss1');
```

--------------------------------

### Example: IN subquery converted to semi-join with execution plan

Source: https://help.aliyun.com/zh/polardb/polardb-for-mysql/optimizer-based-query-conversion-in-mysql-8

Shows an example of converting an IN subquery into a semi-join, including the rewritten query and its execution plan. The plan shows the semi-join execution with 'Start temporary' and 'End temporary' markers.

```plaintext
SELECT * FROM t1 WHERE t1.a in (SELECT t2.c1 FROM t2 where t2.c1 > 0);
=>
/* select#1 */
SELECT `t1`.`a` AS `a`
FROM `t1`
SEMI JOIN (`t2`)
WHERE ((`t1`.`a` = `t2`.`c1`) and (`t2`.`c1` > 0))
执行计划如下：
explain SELECT * FROM t1 WHERE t1.a in (SELECT t2.c1 FROM t2 where t2.c1 > 0);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                     |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Start temporary                              |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; End temporary; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+

```

--------------------------------

### Prepare test data for pg_bigm recheck example

Source: https://help.aliyun.com/zh/polardb/polardb-for-oracle/pg-bigm-for-oracle

Creates a table and inserts sample data, then creates a GIN index with gin_bigm_ops for full-text search.

```sql
CREATE TABLE tbl (doc text);

INSERT INTO tbl VALUES('He is awaiting trial');
INSERT INTO tbl VALUES('It was a trivial mistake');

CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);
```

--------------------------------

### Example: Convert to Time-based Sequence

Source: https://help.aliyun.com/zh/polardb/polardb-for-xscale/alter-sequence

Converts sequence seq3 to a Time-based Sequence. Note that specifying a starting value is meaningless for Time-based Sequence.

```plaintext
ALTER SEQUENCE seq3 CHANGE TO TIME;
```
Tokens:
Raw