Shulou(Shulou.com)06/02 Report--

This article mainly explains "PostgreSQL how to use FDW to achieve simple dblink", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "PostgreSQL how to use FDW to achieve simple dblink" bar!

1. Introduction

Oracle DATABASE LINK can access objects from other databases (instances). It is easy to use and easy to use, and many existing applications will involve it.

2. Common usage

Remote list

SELECT col_list FROM table_remote@dblink; program column

Remote sequence

SELECT seq_remote.nextval@dblink FROM dual

If it is used in a lot of applications, or faced with a lot of such applications, and we want to make as few changes to the application as possible, here is a simple way to modify PG to simplify the table.

3. Remote table syntax table_remote@dblink

There is an obvious problem with this format: @ is a common operator (or part) in PG. Search for it:

SELECT oprname FROM pg_operator WHERE oprname like'% @%'

How many? 60, there are as many as 17 directly @ itself.

4. Table access FROM clause

Let's take a look at the FROM clause syntax (excerpt from gram.y)

From_clause: FROM from_list {$$= $2;} | / * EMPTY*/ {$$= NIL;}; from_list: table_ref {$$= list_make1 ($1);} | from_list', 'table_ref {$$= lappend ($1, $3);}; / * * table_ref is where an alias clause can be attached. * / table_ref: relation_expr opt_alias_clause {$1-> alias = $2; $= (Node *) $1;}. Relation_expr: qualified_name {/ * inheritance query, implicitly * / $$= $1; $$- > inh = true; $$- > alias = NULL;}

The syntax here also affects INSERT, CURSOR, and so on, because just for demonstration, we don't evaluate the impact on them.

5. Create FDWCREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', dbname' foodb', port '5432'); CREATE SCHEMA myserver;CREATE FOREIGN TABLE myserver.films (code char (5) NOT NULL, title varchar (40) NOT NULL, did integer NOT NULL, date_prod date, kind varchar (10), len interval hour to minute) SERVER myserver

Here is just an example, in fact, what kind of naming rules can be chosen at will, as long as it does not conflict with the data structure required by the current application.

6. Map syntax to external table access relation_expr: qualified_name {/ * inheritance query, implicitly * / $$= $1; $$- > inh = true; $$- > alias = NULL;} | qualified_name Op IDENT {/ * inheritance query, implicitly * / $$= $1; $- > inh = true $$- > schemaname = $3; $$- > alias = NULL;}

You should check here to see if Op is @, otherwise any operator will do.

7. Visit SELECT * FROM films@myserver

In fact, it is equivalent to:

SELECT * FROM myserver.films

Such a small change may reduce some of the pain of modifying the code for application developers.

8. Other

The trouble with changing the syntax is that we need to confirm its scope of influence, and the simple way is to regression testing, which, if it passes, at least proves that we are not affecting the original functionality of PG. We mentioned the remote sequence above, which is more troublesome because it is only a demonstration and not one by one. And we're just thinking about the implementation in the FROM clause. What about the rest?

In addition, it is obvious that there will be more maintenance work for DBA.

9. Enhancement

This approach is certainly acceptable if only some tables need to be accessed remotely, and if a system needs to access hundreds of remote tables, it is obviously not a wonderful thing even if it can be created automatically by script. What if the table structure is changed? So automatic creation here is a good choice. If each visit is created automatically, you will face the problem of system space expansion (the same as the temporary table). Defining a refresh policy can be alleviated. The fundamental solution is to use temporary data sources, which is an interesting change.

Thank you for your reading, the above is the content of "how PostgreSQL uses FDW to achieve simple dblink". After the study of this article, I believe you have a deeper understanding of how PostgreSQL uses FDW to achieve simple dblink, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.