Re: Add XMLNamespaces to XMLElement

2025-03-15 Thread newtglobal postgresql_contributors
The following review has been posted through the commitfest application:
make installcheck-world:  not tested
Implements feature:   not tested
Spec compliant:   not tested
Documentation:not tested

Hi Pavel,

I have tested this patch, and it proves to be highly useful when handling 
XMLNAMESPACES() with both DEFAULT and NO DEFAULT options. The following test 
cases confirm its correctness:


SELECT xmlelement(
NAME "foo",
XMLNAMESPACES('http://x.y' AS xy, 'http://a.b' AS ab, DEFAULT 'http://d.e'),
xmlelement(NAME "foot", 
xmlelement(NAME "xy:shoe"), 
xmlelement(NAME "ab:lace")
)
);

SELECT xmlelement(
NAME "foo",
XMLNAMESPACES('http://x.y' AS xy, 'http://a.b' AS ab, NO DEFAULT),
xmlelement(NAME "foot", 
xmlelement(NAME "xy:shoe"), 
xmlelement(NAME "ab:lace")
)
);
Additionally, I verified that the patch correctly supports multiple namespaces 
when using both DEFAULT and NO DEFAULT, ensuring expected behavior across 
different use cases.

Great work on this improvement!

Re: speedup COPY TO for partitioned table.

2025-03-17 Thread newtglobal postgresql_contributors
Hi Jian,
Tested this patch with COPY sales TO STDOUT; ~ 1.909ms, improving performance 
over the older COPY (SELECT * FROM sales) TO STDOUT; ~ 3.80ms method. This 
eliminates query planning overhead and significantly speeds up data export from 
partitioned tables. 
Our test setup involved creating a partitioned table(sales), inserted 500 
records, and comparing execution times.

-- Step 1: Create Partitioned Parent Table
CREATE TABLE sales (
id SERIAL NOT NULL,
sale_date DATE NOT NULL,
region TEXT NOT NULL,
amount NUMERIC(10,2) NOT NULL,
category TEXT NOT NULL,
PRIMARY KEY (id, sale_date,region)
) PARTITION BY RANGE (sale_date);

-- Step 2: Create Range Partitions (2023 & 2024)
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
PARTITION BY HASH (region);

CREATE TABLE sales_2024 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
PARTITION BY HASH (region);

-- Step 3: Create Hash Partitions for sales_2023
CREATE TABLE sales_2023_part1 PARTITION OF sales_2023
FOR VALUES WITH (MODULUS 2, REMAINDER 0);

CREATE TABLE sales_2023_part2 PARTITION OF sales_2023
FOR VALUES WITH (MODULUS 2, REMAINDER 1);

-- Step 4: Create Hash Partitions for sales_2024
CREATE TABLE sales_2024_part1 PARTITION OF sales_2024
FOR VALUES WITH (MODULUS 2, REMAINDER 0);

CREATE TABLE sales_2024_part2 PARTITION OF sales_2024
FOR VALUES WITH (MODULUS 2, REMAINDER 1);

-- Step 5: Insert Data **AFTER** Creating Partitions
INSERT INTO sales (sale_date, region, amount, category)
SELECT 
('2023-01-01'::DATE + (random() * 730)::int) AS sale_date,  -- Random date 
in 2023-2024 range
CASE WHEN random() > 0.5 THEN 'North' ELSE 'South' END AS region,  -- 
Random region
(random() * 1000)::NUMERIC(10,2) AS amount,  -- Random amount (0 to 1000)
CASE WHEN random() > 0.5 THEN 'Electronics' ELSE 'Furniture' END AS 
category  -- Random category
FROM generate_series(1, 500);

COPY (SELECT * FROM SALES) TO STDOUT;  ~ 1.909ms

COPY SALES TO STDOUT; ~ 3.80ms

This change is recommended for better performance in PostgreSQL partitioned 
tables.

Re: [PoC] Reducing planning time when tables have many partitions

2025-03-12 Thread newtglobal postgresql_contributors
The following review has been posted through the commitfest application:
make installcheck-world:  tested, failed
Implements feature:   tested, failed
Spec compliant:   tested, failed
Documentation:tested, failed

Hi Yuya,
Tested this patch and noted that this patch significantly improves query 
planning time, especially as the number of partitions increases. While the 
impact is minimal for small partition counts (2–8), the improvement becomes 
substantial from 16 partitions onward, reaching up to ~86.6% reduction at 768 
partitions. Larger partitions (512–1024) see a dramatic speedup, cutting 
planning time by over 2.7 seconds. The results confirm that the patch optimizes 
partitioned query execution efficiently. This enhancement is crucial for 
databases handling large partitioned tables, leading to better performance and 
scalability.
Regards,
NewtGlobal PostgreSQL contributors

Re: [PoC] Add CANONICAL option to xmlserialize

2025-03-12 Thread newtglobal postgresql_contributors
The following review has been posted through the commitfest application:
make installcheck-world:  tested, failed
Implements feature:   tested, failed
Spec compliant:   tested, failed
Documentation:tested, failed

Hi Jim,
I tested the function with dynamically generated XML using `XMLELEMENT`, 
`XMLATTRIBUTES`, and `XMLCOMMENT`.

Example:

SELECT XMLCANONICALIZE(XMLELEMENT(
NAME employee,
XMLCOMMENT('Employee details start'),
XMLELEMENT(NAME id, 101),
XMLELEMENT(NAME name, 'Mahesh'),
XMLELEMENT(NAME department, 'Engineering'),
XMLCOMMENT('Employee details end')
), true);

The function correctly handled comments and structured XML elements as expected.

Testing with a Table Containing Employee Data
Created a table and inserted sample employee records to verify XML generation.

Table Creation and Data Insertion:

CREATE TABLE employees (id INT, name TEXT, department TEXT);
INSERT INTO employees VALUES (1, 'Alice', 'HR'), (2, 'Bob', 'IT');


Canonicalizing Employee XML Data:

SELECT XMLCANONICALIZE(XMLELEMENT(
NAME employee,
XMLATTRIBUTES(id AS id),
XMLCOMMENT('Employee details start'),
XMLELEMENT(NAME name, name),
XMLELEMENT(NAME department, department),
XMLCOMMENT('Employee details end')
), true)
FROM employees;

The patch correctly processes XML elements and attributes from database records.

Testing with Dynamic Comments
Added a column to store dynamic comments and verified if `XMLCANONICALIZE` 
handles them properly.

Modifications:
ALTER TABLE employees ADD COLUMN comment TEXT;
UPDATE employees SET comment = 'Employee details for ' || name;


Verification Query:

SELECT XMLCANONICALIZE(XMLELEMENT(
NAME employee,
XMLATTRIBUTES(id AS id),
XMLCOMMENT(comment),
XMLELEMENT(NAME name, name),
XMLELEMENT(NAME department, department)
), true)
FROM employees;

Dynamic comments were correctly included in the XML output.

Testing with Manual Newlines
Inserted manual newlines to check the function's behavior with formatted XML.


SELECT XMLCANONICALIZE(
XMLELEMENT(
NAME employee,
XMLATTRIBUTES(id AS id),
XMLCOMMENT(comment),
E'\n  ',
XMLELEMENT(NAME name, name),
E'\n  ',
XMLELEMENT(NAME department, department),
E'\n'
),
true
)
FROM employees;

Whitespace and newlines were correctly handled in XML output.

After testing various scenarios, I found that `XMLCANONICALIZE` is working as 
expected. It:
- Removes unnecessary whitespace and newlines.
- Sorts attributes in a consistent order.
- Converts empty elements to start-end tag pairs.
- Preserves or removes comments based on the flag.
- Works well with table data and dynamically generated XML.

Regards,
NewtGlobal PostgreSQL contributors

Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).

2025-03-12 Thread newtglobal postgresql_contributors
The following review has been posted through the commitfest application:
make installcheck-world:  not tested
Implements feature:   not tested
Spec compliant:   not tested
Documentation:not tested

Hi,
Tested the latest patch that allows direct `COPY` operations on Materialized 
Views, removing the need for `COPY (SELECT ...)`. This enhancement reduces 
query overhead, improving performance by **4–5%**.  
Example:  
Previous approach: 
COPY (SELECT * FROM staff_summary) TO STDOUT WITH CSV HEADER;
Optimized approach:  
COPY staff_summary TO STDOUT WITH CSV HEADER;
Performance tests were conducted using a Materialized View containing around 
80,000 records, confirming that the new approach is faster and more efficient 
for exporting data.

Regards,
Newt Global PostgreSQL Contributors

Re: pg_dump, pg_dumpall, pg_restore: Add --no-policies option

2025-03-12 Thread newtglobal postgresql_contributors
The following review has been posted through the commitfest application:
make installcheck-world:  not tested
Implements feature:   not tested
Spec compliant:   not tested
Documentation:not tested

Hi,
Tested this patch with `--no-policies` option works as expected by ensuring 
that policy definitions are not included in database backups. Successfully 
tested using `pg_dump`, `pg_dumpall`, and `pg_restore`, confirming that 
policies are excluded upon restoration. The `admin_full_access` policy was 
correctly applied, granting full access to the `admin` role for the `users` 
table. Additionally, the `read_only_access` policy was verified to restrict the 
`readonly` role to only performing `SELECT` operations.

Regards,
Newt Global PostgreSQL Contributors

Re: Replace IN VALUES with ANY in WHERE clauses during optimization

2025-02-27 Thread newtglobal postgresql_contributors
The following review has been posted through the commitfest application:
make installcheck-world:  tested, failed
Implements feature:   tested, failed
Spec compliant:   tested, failed
Documentation:tested, failed

Hi Ivan Kush
I tested the patch with `commands.sql` and observed noticeable improvements in 
planning and execution time, especially with multiple tables. Even single-table 
queries show small time reductions (0.02–0.04 ms). The patch optimizes `IN` 
clauses effectively, particularly with `VALUES`. For example, `col IN (VALUES 
('a'), ('b'), ('c'))` now behaves similarly to `col IN ('a', 'b', 'c')`, 
leading to faster execution and reduced planning overhead. 
Regards, 
Postgresql Contributors - NewtGlobal

Re: New "raw" COPY format

2025-02-27 Thread newtglobal postgresql_contributors
The following review has been posted through the commitfest application:
make installcheck-world:  tested, failed
Implements feature:   tested, failed
Spec compliant:   tested, failed
Documentation:tested, failed

Hi Joel,
After testing the patch, I observed that for single-column tables, the format 
evolved from SINGLE to RAW and finally to LIST to handle diverse data more 
flexibly. For example, the command: \COPY test.foo2 FROM 
'/home/newtdba/postgres-cf-5300/testfiles/testname.txt' WITH (FORMAT LIST); 
works with CSV, TXT, and RAW files without specifying column names. This LIST 
format is effective for copying data to/from single-column tables but requires 
specifying the correct format.

The new status of this patch is: Needs review