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.
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: 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