Thread

escape string syntax and pg_dumpall

Patrick Welche <prlw1@newn.cam.ac.uk> — 2005-07-18T16:54:50Z

I just ran pg_dumpall from today's CVS against a 14 April server - I got:

pg_dumpall: query failed: ERROR:  type "e" does not exist
pg_dumpall: query was: SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation, spcacl FROM pg_catalog.pg_tablespace WHERE spcname NOT LIKE E'pg\_%'

That comes from v1.62 of pg_dumpall.c :

    res = executeQuery(conn, "SELECT spcname, "
                     "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
                       "spclocation, spcacl "
                       "FROM pg_catalog.pg_tablespace "
                       "WHERE spcname NOT LIKE E'pg\\_%'");

----------------------------
revision 1.62
date: 2005/06/26 03:03:48;  author: momjian;  state: Exp;  lines: +2 -2
Add E'' syntax so eventually normal strings can treat backslashes
literally.

Add GUC variables:

        "escape_string_warning" - warn about backslashes in non-E strings
        "escape_string_syntax" - supports E'' syntax?
        "standard_compliant_strings" - treats backslashes literally in ''

Update code to use E'' when escapes are used.
----------------------------


My version of the server predates those GUC settings - could the code
be assuming that my server can cope with something it can't?
(Normally I would just upgrade everything - and I bet it will work -
but, I think we still recommend that people use new pg_dumpall to
dump their old server when upgrading..)

Cheers,

Patrick

Re: escape string syntax and pg_dumpall

Tom Lane <tgl@sss.pgh.pa.us> — 2005-07-18T17:25:28Z

Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> I just ran pg_dumpall from today's CVS against a 14 April server - I got:
> pg_dumpall: query failed: ERROR:  type "e" does not exist
> pg_dumpall: query was: SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation, spcacl FROM pg_catalog.pg_tablespace WHERE spcname NOT LIKE E'pg\_%'

> That comes from v1.62 of pg_dumpall.c :

>     res = executeQuery(conn, "SELECT spcname, "
>                      "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
>                        "spclocation, spcacl "
>                        "FROM pg_catalog.pg_tablespace "
>                        "WHERE spcname NOT LIKE E'pg\\_%'");

This query needs to be version-dependent, Bruce ...

			regards, tom lane

Re: escape string syntax and pg_dumpall

Andrew - Supernews <andrew+nonews@supernews.com> — 2005-07-18T18:13:16Z

On 2005-07-18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> That comes from v1.62 of pg_dumpall.c :
>
>>     res = executeQuery(conn, "SELECT spcname, "
>>                      "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
>>                        "spclocation, spcacl "
>>                        "FROM pg_catalog.pg_tablespace "
>>                        "WHERE spcname NOT LIKE E'pg\\_%'");
>
> This query needs to be version-dependent, Bruce ...

It's not even correct as it stands - if you want to match a literal _
using LIKE then you would need E'pg\\\\_%' there.

Would  NOT LIKE 'pg!_%' ESCAPE '!'  be better?

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Re: escape string syntax and pg_dumpall

Tom Lane <tgl@sss.pgh.pa.us> — 2005-07-18T19:01:31Z

Andrew - Supernews <andrew+nonews@supernews.com> writes:
>>> "WHERE spcname NOT LIKE E'pg\\_%'");

> It's not even correct as it stands - if you want to match a literal _
> using LIKE then you would need E'pg\\\\_%' there.

Good point!

> Would  NOT LIKE 'pg!_%' ESCAPE '!'  be better?

Seems like a fine solution ---  I was about to object that ESCAPE
doesn't go back as far as PG 7.0, but neither does pg_tablespace,
so that seems OK.

Or perhaps better, use a regular regex: spcname !~ '^pg_'.  The
majority of the comparable cases in psql's describe.c do it this
way, and they seem more readable to me ...

			regards, tom lane

Re: escape string syntax and pg_dumpall

Patrick Welche <prlw1@newn.cam.ac.uk> — 2005-07-19T15:35:03Z

On Mon, Jul 18, 2005 at 03:01:31PM -0400, Tom Lane wrote:
> Andrew - Supernews <andrew+nonews@supernews.com> writes:
> >>> "WHERE spcname NOT LIKE E'pg\\_%'");
> 
> > It's not even correct as it stands - if you want to match a literal _
> > using LIKE then you would need E'pg\\\\_%' there.
> 
> Good point!
> 
> > Would  NOT LIKE 'pg!_%' ESCAPE '!'  be better?
> 
> Seems like a fine solution ---  I was about to object that ESCAPE
> doesn't go back as far as PG 7.0, but neither does pg_tablespace,
> so that seems OK.

The ESCAPE version which you commited works, thanks!

> Or perhaps better, use a regular regex: spcname !~ '^pg_'.  The
> majority of the comparable cases in psql's describe.c do it this
> way, and they seem more readable to me ...

Likewise.. then again your fix already works..

Cheers,

Patrick