DATABASE LINK

Description

In the local database, a database link is used to connect to and access the remote database.

A database link can be public or private. A private database link can be accessed only by the creator, while a public database link can be accessed by all users.

All created database link information is stored in the system view gs_db_links of the local database.

Precautions

• The database link feature can be used only in A-compatible versions.
• The remote database connected to database link supports only 503.1 and later versions.
• Ensure that the values of the compatibility parameter DBCOMPATIBILITY and GUC parameters behavior_compat_options, a_format_dev_version, and a_format_version of the local and remote databases are the same.
• When a session is enabled for a database link connection, the following GUC parameters are set:

  set search_path=pg_catalog, '$user', 'public';
  set datestyle=ISO;
  set intervalstyle=postgres;
  set extra_float_digits=3; 

  Other parameters are set at the remote end. If the remote parameters are different from the local parameters, the data display formats may be different. Therefore, ensure that the remote parameters are the same as the local parameters.

• Preparations: Use gs_guc to add a whitelist to the pg_hba.conf file to allow client connections.

  Example: gs_guc reload -I all -N all -Z datanode -h "host all all 192.168.11.11/32 sha256"

  For details about the parameters, see the description of gs_guc client authentication policy settings.

• The permission to create a database link needs to be granted using the GRANT syntax. By default, a new user does not have the permission, but the system administrator has the permission. For details, see GRANT description.
• When a database link is used to perform operations on a remote table, a schema corresponding to the remote table is created locally. If the metadata of the table does not exist locally, the metadata is written to the local system catalog. In this case, a level-7 lock is used to ensure write consistency until the transaction ends. When a database link is deleted, the corresponding metadata is also deleted.
• If a long transaction uses the dblink to operate a remote object for the first time, the lock is held until the transaction ends. Other transactions that use the dblink for the first time are blocked. To avoid this problem, run a quick statement, for example, "select * from t1@dblink where 1=2;", to query the remote object to be used and flush its metadata to disks. In addition, similar problem also occurs when the structure of the remote table changes and the stored metadata is updated locally.
• If the local and remote character sets are different, an error indicating that the conversion fails may be reported. The error information is that the remote end returns an error. If the character encoding of the local database is GB18030_2022, the character encoding sent to the remote database is converted to GB18030. Therefore, if the character set of the local database is GB18030_2022, the character set of the remote database can only be GB18030 or GB18030_2022.
• When a schema corresponding to the remote end is created locally, "USERNAME (available only for private database link) #remote schema@DBLINK" is used as the schema name. The maximum length of the schema name is 63 characters.
  

  When the permission to create a database link is granted to a user, the user can remotely access a database by using the IP address of the remote database. Exercise caution when granting this permission to users.

Syntax

• Create a database link.

  CREATE [ PUBLIC ] DATABASE LINK dblink
    [ CONNECT TO { CURRENT_USER | user IDENTIFIED BY password } ] [ USING ( option 'value' [...]) ];

• Modify the database link information.

  ALTER [ PUBLIC ] DATABASE LINK dblink
    { CONNECT TO user IDENTIFIED BY password };

• Delete a specified database link.

  DROP [ PUBLIC ] DATABASE LINK dblink ;

  

  • PUBLIC: creates a public database link visible to all users. If this clause is omitted, the database link is private and available only to the current user. The data that can be accessed on the remote database depends on the identity used by the database link during connection.
  • If CONNECT TO user IDENTIFIED BY password is specified, the database link makes a connection as a user with specified password.
  • If CONNECT TO CURRENT_USER is specified, the database link connects to the remote database as the local initial user.
  • If the preceding two clauses are omitted, the database link connects to the remote database as a local initial user.
  • dblink: indicates the name of the database link to be created.
  • user: indicates the username used by the created database link.
  • password: indicates the password of the username.
  • USING ( option 'value' [, ... ] )

    Specifies parameters such as the IP address, port number, and remote database name of the database to be connected. The supported options are as follows:

    • host: specifies the IP addresses to be connected. IPv6 addresses are not supported. Multiple IP addresses can be specified using character strings separated by commas (,). Currently, SSL settings, encrypted databases, and certificate authentication are not supported. If no IP address is specified, this parameter is left empty by default.
    • port: specifies the port number for connection. If this parameter is not specified, the default value 5432 is used.
    • dbname: specifies the name of the database to be connected. If this parameter is not specified, the username used for connecting to the remote end is used by default.
    • fetch_size: specifies the amount of data obtained from the remote end each time. The value of fetch_size ranges from 0 to 2147483647. The default value is 100.
    Notes:

    • You can write only part of the preceding options in the brackets after USING.
    • If the keyword USING is not written, the content in the brackets is not written as well.
    • When a database link is created, the system does not check whether the connection is successful. If related keywords are missing, an error may be reported.
• Perform the SELECT operation through a database link.
  

  The syntax for accessing a remote database object by using a created database link is basically the same as that for accessing a local object. The difference is that @dblink is added to the end of the remote object descriptor. For details about restrictions on SQL statements, see Table 1.

  [ WITH [ RECURSIVE ] with_query [, ...] ]
   SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
   { * | {expression [ [ AS ] output_name ]} [, ...] }
   [ FROM from_item [, ...] ]
   [ WHERE condition ]
   [ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
   [ GROUP BY grouping_element [, ...] ]
   [ HAVING condition [, ...] ]
   [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
   [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
   [ LIMIT { [offset,] count | ALL } ]
   [ OFFSET start [ ROW | ROWS ] ]
   [ {FOR { UPDATE | SHARE} [ OF table_name [, ...] ] } [...] ];
   {[ ONLY ] table_name [ * ] [ partition_clause ] @ dblink  [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    |( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    |[function_name] ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
    |[function_name] ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]};

• Perform the INSERT operation through a database link.

  [ WITH [ RECURSIVE ] with_query [, ...] ]
    INSERT [/*+ plan_hint */] INTO table_name [ partition_clause ] @ dblink [ ( column_name [, ...] ) ]
       { DEFAULT VALUES
       | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...] 
       | query }
       [ RETURNING { {output_expression [ [ AS ] output_name ] }[, ...]} ];

• Perform the UPDATE operation through a database link.

  UPDATE [/*+ plan_hint */] [ ONLY ] table_name [ partition_clause ] @ dblink  [ [ AS ] alias ]
   SET {column_name = { expression | DEFAULT } 
      |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...]
      [ FROM from_list ] [ WHERE condition ] 
      [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
      [ LIMIT { [offset,] count | ALL } ]
      [ RETURNING { {output_expression [ [ AS ] output_name ]} [, ...] }];
   where sub_query can be:
   SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
   { * | {expression [ [ AS ] output_name ]} [, ...] }
   [ FROM from_item [, ...] ]
   [ WHERE condition ]
   [ GEOUP BY grouping_element [, ...] ]
   [ HAVING condition [, ...] ];

• Perform the DELETE operation through a database link.

   [ WITH [ RECURSIVE ] with_query [, ...] ]
   DELETE [/*+ plan_hint */] FROM [ ONLY ] table_name [ partition_clause ] @ dblink  [ [ AS ] alias ]
      [ USING using_list ]
      [ WHERE condition]
      [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
      [ LIMIT { [offset,] count | ALL } ]
      [ RETURNING { * | { output_expr [ [ AS ] output_name ] } [, ...] } ];

• Perform the LOCK TABLE operation through a database link.

   LOCK [ TABLE ] {[ ONLY ] name @ dblink  [, ...]}
    
      [ IN {ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE} MODE ]
      [ NOWAIT ];

• Call a stored procedure or function of the remote database.

  1	CALL | SELECT [ schema. ] { func_name@dblink | procedure_name@dblink } ( param_expr );

  

  • When a database link calls a remote function or stored procedure, the user-defined types, out output parameters, aggregate functions, window functions, and return set functions are not supported. SELECT * FROM func@dblink() cannot be used to call the function or stored procedure.
  • When a database link calls a remote function or stored procedure, functions under PUBLIC are called by default if no schema is specified.
  • When the database link calls a remote function or stored procedure, param_expr does not support the use of ":=" or "=>" to separate parameter names and parameter values.

• The meanings of the parameters irrelevant to database link in the preceding SQL statements are the same as those in the original SQL statements.
• When specifying a column name, you can add "@dblink" to the end of the column name to specify the column of the table to which the corresponding database link points.

Restrictions

• Transaction

  When a database link is used, the relationship between local and remote transactions is as follows:

  1. A local transaction synchronously controls the commit/rollback status of a remote transaction.
  2. The relationship between isolation levels is as follows.

     Local Isolation Level	Remote Isolation Level
     Read Uncommitted	Repeatable Read
     Read Committed	Repeatable Read
     Repeatable Read	Repeatable Read
     Serializable	Serializable

     

     If you commit a local transaction, a transaction commit request is sent to the remote end. If the local transaction fails to be committed due to an exception (such as a connection exception or a local cluster instance exception) after the remote transaction is successfully committed, the committed remote transaction cannot be withdrawn. As a result, the local transaction may be inconsistent with the remote transaction.

• Permissions of local users to use database links
  1. If the keyword public is used, the link is a public database link and can be used by all users or schemas.
  2. If the keyword public is not used, the link is a private database link and can be used only by the current user or schema. (User sys cannot use database links across schemas.)
• Permission to access remote database objects through database links

  The permissions to access remote database objects are the same as those of the remote connection user bound to the database link.

• Supported SQL statements
  • For details about the statements supported by database links, see Table 1.
  • For details about the table types supported by database links, see Table 2.
• Function call using a database link
  • When a database link calls a remote function, the user-defined types, OUT/INOUT parameters, PACKAGE inner functions, aggregate functions, window functions, and return set functions are not supported.
  • When a database link calls a stored procedure or function of a remote database in the PLSQL_BODY, the user-defined types, OUT/INOUT parameters, PACKAGE inner functions, overloaded functions, aggregate functions, window functions, and return set functions are not supported.
  

  • You can use the following syntax to call stored procedures or functions of a remote database in the PLSQL_BODY: [CALL | SELECT] [ schema. ] { func_name@dblink | procedure_name@dblink } ( param_expr ).
  • You can use the following syntax to call parameterless stored procedures or functions of a remote database in the PLSQL_BODY: [CALL | SELECT] [ schema. ] { func_name@dblink | procedure_name@dblink } ( ).
• Synonyms
  • The database link name cannot be created as a synonym.
  • Synonyms that point to a database link object in a remote database cannot be called through database link. Example:
    • Step 1: Create table TABLE1 on DB1.
    • Step 2: Create DBLINK1 on DB2 for connecting to DB1. Create the synonym "CREATE SYNONYM T1 FOR TABLE1@DBLINK1".
    • Step 3: Create DBLINK2 on DB3 for connecting to DB2. Call the synonym T1 "SELECT * FROM T1@DBLINK2" on DB2 through DBLINK2.
• Table type constraints
  • HASHBUCKET: The query or DML operation cannot be performed on the remote hash bucket table through database link.
  • SLICE: The query or DML operation cannot be performed on the remote slice table through database link.
  • Replication table: The query or DML operation cannot be performed on the remote replication table through database link.
  • TEMPORARY: The query or DML operation cannot be performed on the remote temporary table through database link.
• Views
  • Currently, you can create a view for a remote table of a database link. However, when the structure of the remote table changes, an exception may occur when you use the view. Example:
    • Step 1: Create table TABLE1 on DB1.
    • Step 2: Create DBLINK on DB2 for connecting to DB1. Create the view "CREATE VIEW V1 AS SELECT * FROM TABLE1@DBLINK".
    • Step 3: Delete a column from TABLE1 on DB1. An error is reported when you query the view on DB2.
• Other scenarios
  • The database link table does not support triggers, including the scenarios where a database link is used in the function called by a trigger, the function called by a trigger is a database link function, and the trigger is defined on a database link.
  • The UPSERT and MERGE syntaxes are not supported.
  • The CURRENT CURSOR syntax is not supported.
  • Hidden columns in a table cannot be queried.

• Dump and backup

  Database objects related to database links cannot be dumped. The standby node cannot be called or connected by database links.

• Predicate pushdown constraints

  Only the data types, operators, and functions used in the WHERE clause are built in, and the used functions are of the IMMUTABLE type.

• Aggregate function pushdown constraints

  Only single tables that do not contain GROUP, ORDER BY, HAVING, and LIMIT clauses in the SELECT statement are supported. Window functions are not supported.

• Hint pushdown

  Only hints in scan mode can be pushed down based on the hint conditions of database link table objects. The syntax format is as follows:

  [no] tablescan|indexscan|indexonlyscan(table [index])

  The table name or table alias in a query block must be unique.

Examples

-- DDL statements
CREATE USER user2 WITH PASSWORD '********'; -- Create a common user.
GRANT CREATE PUBLIC DATABASE LINK TO user2; -- Grant the permission to create database links to a user.
GRANT DROP PUBLIC DATABASE LINK TO user2;  -- Grant the permission to drop database links to a user.
GRANT ALTER PUBLIC DATABASE LINK TO user2;  -- Grant the permission to modify database links to a user.
REVOKE CREATE PUBLIC DATABASE LINK FROM user2; -- Revoke the permission to create database links to a user.
REVOKE DROP PUBLIC DATABASE LINK FROM user2; -- Revoke the permission to drop database links to a user.
REVOKE ALTER PUBLIC DATABASE LINK FROM user2;   -- Revoke the permission to modify database links to a user.
CREATE PUBLIC DATABASE LINK dblink CONNECT TO 'user1' IDENTIFIED BY '********' USING (HOST '192.168.11.11', PORT '5432', DBNAME 'db1'); -- Create a database link object
ALTER PUBLIC DATABASE LINK dblink CONNECT TO 'user1' IDENTIFIED BY '********'; -- Modify database link information.
DROP PUBLIC DATABASE LINK dblink; -- Delete a database link object.

-- Database link statements
-- Preparations
CREATE USER user1 WITH SYSADMIN PASSWORD '********';
CREATE USER user2 WITH SYSADMIN PASSWORD '********';
CREATE DATABASE db1; -- Remote database
CREATE DATABASE db2; -- Database for testing the database link.
\c db1 user1
-- Create an ordinary table.
db1=> CREATE TABLE remote_tb(f1 int, f2 text, f3 text[]);
db1=> INSERT INTO remote_tb VALUES (0,'a','{"a0","b0","c0"}');
db1=> INSERT INTO remote_tb VALUES (1,'bb','{"a1","b1","c1"}');
db1=> INSERT INTO remote_tb VALUES (2,'cc','{"a2","b2","c2"}');
-- Create a function.
CREATE OR REPLACE FUNCTION f(a in int, b in int)
return int AS
    tmp int :=  a + b;
    begin
        return tmp;
    end;
/
CREATE FUNCTION
-- Create a synonym.
db1=> CREATE SYNONYM remote_sy FOR remote_tb;

\c db2 user2
db2=> CREATE TABLE local_tb(f1 int, f2 text, f3 text[]);
db2=> INSERT INTO local_tb VALUES (2,'c','{"a2","b2","c2"}');
db2=> CREATE PUBLIC DATABASE LINK dblink CONNECT TO 'user1' IDENTIFIED BY '********' USING (HOST '192.168.11.11', PORT '5432', DBNAME 'db1'); -- Set host and port based on actual situation.
db2=> SELECT * FROM remote_tb@dblink; -- Query the remote table.
 f1 | f2 |     f3     
----+----+------------
  1 | bb | {a1,b1,c1}
  2 | cc | {a2,b2,c2}
  0 | a  | {a0,b0,c0}
(3 rows)
db2=> INSERT INTO remote_tb@dblink VALUES (4,'d','{"a1","b2","c3"}'); -- Insert data into a remote table.
INSERT 0 1
db2=> UPDATE remote_tb@dblink SET f2 = 'aa' WHERE f1 = 0; -- Update the remote table.
UPDATE 1
db2=> DELETE remote_tb@dblink WHERE f1 = 1; -- Delete data from a remote table.
DELETE 1
db2=> SELECT * FROM remote_tb@dblink JOIN local_tb ON local_tb.f1 = remote_tb.f1@dblink; -- Join a local table to a remote table.
 f1 | f2 |     f3     | f1 | f2 |     f3     
----+----+------------+----+----+------------
  2 | cc | {a2,b2,c2} |  2 | c  | {a2,b2,c2}
(1 row)

db2=> SELECT count(*) FROM remote_tb@dblink;
 count 
-------
     3
(1 row)
db2=> 
db2=> SELECT f@dblink(1,2); -- Access the remote function.
 f 
---
 3
(1 row)
CREATE OR REPLACE FUNCTION call_f(a in int, b in int) -- Access remote functions in PLSQL_BODY.
RETURN int AS
    tmp int;
    begin
        tmp := f@dblink(a, b);
        RETURN tmp;
    end;
/
CREATE FUNCTION
db2=> SELECT call_f(1, 2);
 call_f 
--------
      3
(1 row)
db2=> CREATE SYNONYM local_sy FOR remote_tb@dblink; -- Create a synonym for a database link object.
CREATE SYNONYM
db2=> SELECT * FROM local_sy;
 f1 | f2 |     f3     
----+----+------------
  1 | bb  | {a1,b1,c1}
  2 | cc | {a2,b2,c2}
  0 | a  | {a0,b0,c0}
(3 rows)
db2=> SELECT * FROM remote_sy@dblink; -- Access the remote database synonym.
 f1 | f2 |     f3     
----+----+------------
  1 | bb | {a1,b1,c1}
 
  2 | cc | {a2,b2,c2}
  0 | a  | {a0,b0,c0}
(3 rows)
db2=> EXPLAIN VERBOSE SELECT /*+ tablescan(remote_sy) */ * FROM remote_sy@dblink; -- Partial hint pushdown supported by the database link.
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Foreign Scan on public.remote_tb remote_sy  (cost=100.00..100.03 rows=1 width=68)
   Output: f1, f2, f3
   Remote SQL: SELECT /*+  tablescan(remote_sy) */ f1, f2, f3 FROM public.remote_tb
(3 rows)

db2=> SELECT * FROM gs_database_link; -- View the database link system catalog.
db2=> START TRANSACTION;
START TRANSACTION
db2=> SELECT * FROM remote_sy@dblink; 
 f1 | f2 |     f3     
----+----+------------
  1 | bb | {a1,b1,c1}
  2 | cc | {a2,b2,c2}
  0 | a  | {a0,b0,c0}
(3 rows)

db2=> SELECT intransaction FROM gs_db_links; -- Check the database link system view.
 intransaction 
---------------
 t
(1 row)
db2=> END;
COMMIT
db2=> ALTER PUBLIC DATABASE LINK dblink CONNECT TO 'user1' IDENTIFIED BY '********'; -- Modify database link information.
db2=> DROP PUBLIC DATABASE LINK dblink; -- Delete a database link object.

Helpful Links

CREATE DATABASE LINK, GS_DATABASE_LINK, and GS_DB_LINKS