Guidance Needed for Testing PostgreSQL Patch (CF-5044)
Hi, I recently installed and tested the CF-5044 patch using the query provided on the PostgreSQL community page but could not confirm its functionality. I followed the standard process: 1. Applied the patch using git. 2. Rebuilt PostgreSQL from source (./configure, make, make install). 3. Tested with the suggested query after restarting PostgreSQL. I would appreciate your guidance on verifying patch functionality and ensuring compliance with community testing standards. If there are additional steps or tools I should use, please let me know. Thank you for your support as I represent Newt Global in contributing to the PostgreSQL community. Best regards, Team from Newt Global
Guidance Needed for Testing PostgreSQL Patch (CF-5044)
Hi pg_hackers,
I hope you are doing well. I am working on reviewing PostgreSQL community
tickets and started with the patch cf/5044.
This patch is in the branch cf/5044, and I pulled the code to test it.
Here’s what I have done so far:
*
Pulled the code from the branch cf/5044.
*
Compiled the code.
*
Set up the database as required.
Tested the query for this patch mentioned in the community chat
(https://commitfest.postgresql.org/50/5044/) .
However, I didn’t get the expected output mentioned in the ticket (below
mentioned example).
CREATE TABLE foo3(id serial PRIMARY key, txt text);
INSERT INTO foo3 (txt) VALUES ('aaa'),('bbb');
DO $$
DECLARE
l_cnt int;
BEGIN
l_cnt := 1
DELETE FROM foo3 WHERE id=1;
END; $$
-- without reaction - just don't work
(2024-06-16 16:05:55) postgres=# set plpgsql.extra_errors to
'strict_expr_check';
SET
(2024-06-16 16:06:43) postgres=# DO $$
DECLARE
l_cnt int;
BEGIN
l_cnt := 1
DELETE FROM foo3 WHERE id=1;
END; $$;
ERROR: syntax error at or near "DELETE"
LINE 11: DELETE FROM foo3 WHERE id=1;
^
Please provide guidance on this patch.
Thanks and Regards,
Postgresql Contributors - Newt Global
[cid:6feaaeb6-762b-426b-9ada-c03243768529]
Newt Global India Pvt. Ltd.
SSPDL Alpha City , Gamma Block,
25, Rajiv Gandhi Salai (OMR),
Navalur, Chennai - 600130 .
RE: Replace IN VALUES with ANY in WHERE clauses during optimization
Hi Ivan,
I tested the patch using the input provided in commands.sql and observed
improvements in both planning and execution time. These optimizations are
especially noticeable when working with a mulitple tables. Even when querying
just a single table, there is a small improvement in planning and execution
time, with differences ranging from 0.02 to 0.04 ms.
CREATE TABLE test_table (
_id SERIAL PRIMARY KEY,
_val TEXT NOT NULL
);
INSERT INTO test_table (_val) VALUES
('a'),
('b'),
('c'),
('d'),
('e');
EXPLAIN ANALYZE
SELECT *
FROM test_table
WHERE _val IN ('b', 'd', 'e');
EXPLAIN ANALYZE
SELECT *
FROM test_table
WHERE _val IN (VALUES ('b'), ('d'), ('e'));
The patch optimizes IN clauses effectively, especially when the syntax uses
VALUES.
When writing queries like:
col IN (VALUES ('a'), ('b'), ('c'))
the optimization makes it behave similarly to:
col IN ('a', 'b', 'c')
This leads to faster query execution times and reduced planning overhead.
Best Regards,
PostgreSQL Contributiors - Newt Global
[cid:55b37106-ab02-4cf9-b703-3cf4294d4600]
Newt Global India Pvt. Ltd.
SSPDL Alpha City , Gamma Block,
25, Rajiv Gandhi Salai (OMR),
Navalur, Chennai - 600130 .
Re: Replace IN VALUES with ANY in WHERE clauses during optimization
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
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
Re: [PoC] Reducing planning time when tables have many partitions
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
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).
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
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: Add XMLNamespaces to XMLElement
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.
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.
