Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. pg_dump: Fix array literals in fetchAttributeStats().

  1. BUG #18923: pg_dump 18beta1 fails to process complex table names

    PG Bug reporting form <noreply@postgresql.org> — 2025-05-12T06:57:45Z

    The following bug has been logged on the website:

Bug reference:      18923
Logged by:          Philippe BEAUDOIN
Email address:      phb.emaj@free.fr
PostgreSQL version: 18beta1
Operating system:   Linux
Description:        

I have just run the E-Maj project regression test suite with PG18 beta1 and
found a pg_dump abort when trying to dump a schema prefixed table having a
complex name including single quote, double quote and space.
Here is a test case to reproduce it :
#!/bin/sh
PG18_BIN=/home/postgres/pg/pg18/bin
PG18_PORT=5418
echo "------ Setup a new database with a single schema and table"
$PG18_BIN/dropdb -h localhost -p $PG18_PORT --if-exists bug
$PG18_BIN/createdb -h localhost -p $PG18_PORT bug
$PG18_BIN/psql -h localhost -p $PG18_PORT bug <<EOF
select version();
SET client_min_messages TO WARNING;
CREATE SCHEMA "phil's schema""3";
SET search_path="phil's schema""3";
DROP TABLE IF EXISTS "phil's tbl1";
CREATE TABLE "phil's tbl1" (
  "phil's col11"   INT NOT NULL PRIMARY KEY,
  "phil's col12"   TEXT
);
EOF
echo "------ Dump the database"
$PG18_BIN/pg_dump --version
$PG18_BIN/pg_dump -h localhost -p $PG18_PORT --verbose bug >/dev/null
And here is what I get :
------ Setup a new database with a single schema and table
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 18beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
(1 row)
SET
CREATE SCHEMA
SET
DROP TABLE
CREATE TABLE
------ Dump the database
pg_dump (PostgreSQL) 18beta1
pg_dump: executing SELECT pg_catalog.set_config('search_path', '', false);
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading subscription membership of tables
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: creating SCHEMA "phil's schema"3"
pg_dump: creating TABLE "phil's schema"3.phil's tbl1"
pg_dump: processing data for table "phil's schema"3.phil's tbl1"
pg_dump: dumping contents of table "phil's schema"3.phil's tbl1"
pg_dump: error: query failed: ERROR:  syntax error at or near "s"
LINE 1: EXECUTE getAttributeStats('{"phil's schema""3","phil's schem...
                                          ^
pg_dump: detail: Query was: EXECUTE getAttributeStats('{"phil's
schema""3","phil's schema""3"}'::pg_catalog.name[],'{"phil's tbl1","phil's
tbl1_pkey"}'::pg_catalog.name[])

  2. Re: BUG #18923: pg_dump 18beta1 fails to process complex table names

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-12T13:51:39Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> I have just run the E-Maj project regression test suite with PG18 beta1 and
> found a pg_dump abort when trying to dump a schema prefixed table having a
> complex name including single quote, double quote and space.

Thanks for the report!  Looks like fetchAttributeStats() is completely
misguided about the appropriate quoting rules for array elements.

			regards, tom lane

  3. Re: BUG #18923: pg_dump 18beta1 fails to process complex table names

    jian he <jian.universality@gmail.com> — 2025-05-13T11:34:51Z

    On Mon, May 12, 2025 at 9:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > I have just run the E-Maj project regression test suite with PG18 beta1 and
> > found a pg_dump abort when trying to dump a schema prefixed table having a
> > complex name including single quote, double quote and space.
>
> Thanks for the report!  Looks like fetchAttributeStats() is completely
> misguided about the appropriate quoting rules for array elements.
>

re-read ReadArrayToken.
In this context, we need to care about single-quote, double-quotes.
for backslash, we skip it.

appendPGArray can help us handle double quotes,
now we just need to make it also handle single quotes.

  4. Re: BUG #18923: pg_dump 18beta1 fails to process complex table names

    Nathan Bossart <nathandbossart@gmail.com> — 2025-05-15T18:12:41Z

    On Tue, May 13, 2025 at 07:34:51PM +0800, jian he wrote:
> On Mon, May 12, 2025 at 9:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> PG Bug reporting form <noreply@postgresql.org> writes:
>> > I have just run the E-Maj project regression test suite with PG18 beta1 and
>> > found a pg_dump abort when trying to dump a schema prefixed table having a
>> > complex name including single quote, double quote and space.
>>
>> Thanks for the report!  Looks like fetchAttributeStats() is completely
>> misguided about the appropriate quoting rules for array elements.

D'oh, my bad.

> re-read ReadArrayToken.
> In this context, we need to care about single-quote, double-quotes.
> for backslash, we skip it.
> 
> appendPGArray can help us handle double quotes,
> now we just need to make it also handle single quotes.

I don't think we want to teach appendPGArray() to handle single quotes.
That's meant for building a text representation of a 1-dimensional Postgres
array, which IIUC doesn't need to escape single quotes.  Take the following
example:

	postgres=# select array['nathan''s element'];
	        array
	----------------------
	 {"nathan's element"}
	(1 row)

My first instinct is that we need to build the array with appendPGArray()
and then append it to the query using appendStringLiteralAH(), as done in
the attached patch.

-- 
nathan

  5. Re: BUG #18923: pg_dump 18beta1 fails to process complex table names

    Corey Huinker <corey.huinker@gmail.com> — 2025-05-15T19:03:11Z

    >
>
> My first instinct is that we need to build the array with appendPGArray()
> and then append it to the query using appendStringLiteralAH(), as done in
> the attached patch.
>

This patch looks good to me, though I'm wondering if we should add a test
case.

  6. Re: BUG #18923: pg_dump 18beta1 fails to process complex table names

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-15T19:03:21Z

    Nathan Bossart <nathandbossart@gmail.com> writes:
> I don't think we want to teach appendPGArray() to handle single quotes.

No, that seems quite wrong.  array_in won't de-dup single quotes.

> My first instinct is that we need to build the array with appendPGArray()
> and then append it to the query using appendStringLiteralAH(), as done in
> the attached patch.

Yeah, I think so.  I was confused for a bit because the one extant
user of appendPGArray is getNamespaces which does an additional layer
of quote-doubling via quoteAclUserName.  However, that seems to be because
it's trying to build aclitem[] arrays whose elements will be read by
aclitemin, and that de-dups double quotes.

Wouldn't be a bad idea to add a test case.

			regards, tom lane

  7. Re: BUG #18923: pg_dump 18beta1 fails to process complex table names

    jian he <jian.universality@gmail.com> — 2025-05-16T14:50:42Z

    On Fri, May 16, 2025 at 3:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Yeah, I think so.  I was confused for a bit because the one extant
> user of appendPGArray is getNamespaces which does an additional layer
> of quote-doubling via quoteAclUserName.  However, that seems to be because
> it's trying to build aclitem[] arrays whose elements will be read by
> aclitemin, and that de-dups double quotes.
>

getNamespaces use appendPGArray for object's initial ACL string

getNamespaces
``
nsinfo[i].dacl.initprivs = pstrdup(aclarray->data);
``

and we seems *only* use it dumpACL->buildACLCommands
``
    if (!parsePGArray(baseacls, &baseitems, &nbaseitems))
    {
        free(aclitems);
        free(baseitems);
        return false;
    }
``
parsePGArray didn't handle single-quotes.

I think the above is the reason single-quotes within array elements didn't cause
trouble.

  8. Re: BUG #18923: pg_dump 18beta1 fails to process complex table names

    Stepan Neretin <slpmcf@gmail.com> — 2025-05-19T07:24:47Z

    On Fri, May 16, 2025 at 9:51 PM jian he <jian.universality@gmail.com> wrote:

> On Fri, May 16, 2025 at 3:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Yeah, I think so.  I was confused for a bit because the one extant
> > user of appendPGArray is getNamespaces which does an additional layer
> > of quote-doubling via quoteAclUserName.  However, that seems to be
> because
> > it's trying to build aclitem[] arrays whose elements will be read by
> > aclitemin, and that de-dups double quotes.
> >
>
> getNamespaces use appendPGArray for object's initial ACL string
>
> getNamespaces
> ``
> nsinfo[i].dacl.initprivs = pstrdup(aclarray->data);
> ``
>
> and we seems *only* use it dumpACL->buildACLCommands
> ``
>     if (!parsePGArray(baseacls, &baseitems, &nbaseitems))
>     {
>         free(aclitems);
>         free(baseitems);
>         return false;
>     }
> ``
> parsePGArray didn't handle single-quotes.
>
> I think the above is the reason single-quotes within array elements didn't
> cause
> trouble.
>
>
>
Hi,

I’ve added a regression test covering problem

This patch combines Nathan Bossart’s original fix with suggestions from Tom
Lane and Jian He. It uses appendPGArray() to build the arrays and
appendStringLiteralAH() to quote them correctly in the EXECUTE statement.

Patch attached. Looking forward to your feedback.

Best regards,

Stepan Neretin

  9. Re: BUG #18923: pg_dump 18beta1 fails to process complex table names

    Nathan Bossart <nathandbossart@gmail.com> — 2025-05-19T20:30:29Z

    On Mon, May 19, 2025 at 02:24:47PM +0700, Stepan Neretin wrote:
> +	'CREATE SCHEMA "phil\'s schema\"3"' => {
> +		create_sql => q{
> +			CREATE SCHEMA "phil's schema""3";
> +			SET search_path = "phil's schema""3";
> +			DROP TABLE IF EXISTS "phil's tbl1";
> +			CREATE TABLE "phil's tbl1" (
> +				"phil's col11" serial PRIMARY KEY,
> +				"phil's col12" text
> +			);
> +		},
> +		regexp => qr/^
> +		CREATE\ SCHEMA\ "phil's\ schema""3";\n
> +		SET\ search_path\ =\ "phil's\ schema""3";\n
> +		DROP\ TABLE\ IF\ EXISTS\ "phil's\ tbl1";\n
> +		CREATE\ TABLE\ "phil's\ tbl1"\ \(\n
> +		\s+"phil's\ col11"\ integer\ NOT\ NULL\ DEFAULT\ nextval\('[^']+'\)::regclass,\n
> +		\s+"phil's\ col12"\ text\n
> +		\s+CONSTRAINT\ "[^"]+"\ PRIMARY\ KEY\ \("phil's\ col11"\)\n
> +		\);/xm,
> +		like => {},
> +	},

I don't think this is quite right.  IIUC this test runs some commands and
makes sure that a slightly adjusted version of those commands never appears
in the dump output.  This is presumably enough to reach the reported
problem, but AFAICT most of the test content is unnecessary.  I think we
should simply modify an existing stats test so that we also verify the
dumped stats commands, as done in the attached patch.

-- 
nathan

  10. Re: BUG #18923: pg_dump 18beta1 fails to process complex table names

    Nathan Bossart <nathandbossart@gmail.com> — 2025-05-20T21:33:03Z

    Committed.

-- 
nathan