Feasibility of Converting RTE to PNSI
Tags: postgres, database, apacheage, opensource
Published: 2023-06-13 15:33:46

## Introduction

In this post we will be exploring about a change we are looking to implement at AGE.

Before going further into details of what RTE and PNSI is, let me first explain what is the problem statement and what we are looking for.


## Problem Statement

PostgreSQL version 13(pg 13) uses PNSI instead of RTE in many functions. PNSI also exists in some of functions in PG11 and PG12 along with RTE as well. 

## What we are looking for? 
Find a way or research about converting RTE to PNSI in PG11 and PG12 version. If we are successful, multiple version of PostgreSQL can be closer to each other. Which can help us in development of the further versions.

Now lets see what actually is RTE and PNSI.

## RTE
RTE stands for Range Table Entry, in the source code it is a structure RangeTblEntry. This plays an important role in the query processing of the PostgreSQL. RTE basically represents tables, subqueries in a postgresql query. It refers not only to tables or subqueries but also some results of joins and records that SQL statement operates on. It keeps data of these tables, subqueries etc. 

This RTE structure has many fields. Here I'm explaining some important fields from the RTE struct.

One of them is `subquery` pointer of type `Query` which is used to hold subquery. other is jointype of type `JoinType`which is used to hold the information about join and some other boolean flags to keep the record of different boolean values. Which is used is further processing of query.

## PNSI
PNSI stand for "ParseNameSpaceItem", which is a struct in parse_node.h file. Which represents an element of a namespace  list. It has various fields, various boolean flags and important thing is that it has a field for RTE as well, to hold the data that we used to store in RTE. And other fields include `p_nscolumns` of type ParseNameSpaceColumn which is an array containing information about how to construct vars referencing corresponding element of the RTE's colnames list.

And there are various flags like `p_rel_visible` and `p_cols_visible` that define which RTEs are accessible by 
qualified and unqualified names respectively. While processing the FROM clause various flags like 
`p_lateral_only` and `p_lateral_ok` they serve the purpose of  visibility to LATERAL subexpressions and errors respectively.

## Relationship between RTE and PNSI

RTE is used in older versions of PostgreSQL like pg11 and pg12. PNSI is used in PostgreSQL version 13. It 
also has a field for RTE to refer to “RangeTblEntry”. So, we can say that PNSI is an updated version of RTE 
as it provides some extra values along with RTE field. Whenever a subquery is created within a query, an 
RTE is formed to represents the information of that subquery. And in version pg13 this RTE is then 
included in the PNSI structure along with other information.

## Conclusion/feasibility 
According to our current research status we are unable to find a way to convert RTE to PNSI. The reason is absence of some information that is required to convert but is not available. So, for now its not feaible to convert RTE to PNSI for some versions of PG.  


## References

[https://github.com/apache/age](https://github.com/apache/age)
[https://github.com/postgres/postgres/](https://github.com/postgres/postgres/)