Thread: pg_dump/restore syntax checking bug?

Hello,

In testing some pg_restore functionality I found the following:

postgres@jd-laptop:~$ pg_dump -U postgres -Fc -s --file=foo.sqlc
postgres@jd-laptop:~$ dropdb test;
postgres@jd-laptop:~$ createdb test;
postgres@jd-laptop:~$ pg_restore -d test -P 'by()' foo.sqlc
postgres@jd-laptop:~$ psql -U postgres test
psql (9.1.8)
Type "help" for help.

test=# select by(); by
---- by
(1 row)

test=# select hello();
ERROR:  function hello() does not exist
LINE 1: select hello();               ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.

----> The above is as expected.


test=# \q
postgres@jd-laptop:~$ pg_restore -d test -P 'by(),hello()' foo.sqlc
postgres@jd-laptop:~$ psql -U postgres test;
psql (9.1.8)
Type "help" for help.

test=# select hello();
ERROR:  function hello() does not exist
LINE 1: select hello();

----> This is where I am confused. It didn't restore hello() and it also 
didn't error that the syntax of the restore command was invalid.               ^
test=# drop function by();
DROP FUNCTION
test=#
test=# q
test-# \q
postgres@jd-laptop:~$ pg_restore -d test -P 'by(),hello()' foo.sqlc
postgres@jd-laptop:~$ psql -U postgres test;
psql (9.1.8)
Type "help" for help.

test=# drop function by();
ERROR:  function by() does not exist

----> by() not restored by above command


test=# \q
postgres@jd-laptop:~$ pg_restore -d test -P 'by()','hello()' foo.sqlc
postgres@jd-laptop:~$ psql -U postgres test;
psql (9.1.8)
Type "help" for help.

test=# drop function by();
ERROR:  function by() does not exist
test=# select hello();
ERROR:  function hello() does not exist
LINE 1: select hello();               ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.

----> by() and hello() also not restored


test=# \q
postgres@jd-laptop:~$ pg_restore -d test -P 'by()' -P'hello()' foo.sqlc
postgres@jd-laptop:~$ psql -U postgres test;
psql (9.1.8)
Type "help" for help.

test=# select hello(); hello
------- hello
(1 row)

test=# select by();
ERROR:  function by() does not exist
LINE 1: select by();

----> hello() restored but by() was not.


It appears we need better syntax checking.

Sincerely,

JD

On Fri, Mar 22, 2013 at 9:35 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

> postgres@jd-laptop:~$ pg_restore -d test -P 'by(),hello()' foo.sqlc

Note, the pg_restore doc makes no mention of trying to squeeze
multiple function prototypes in a single argument you've done here, or
of using multiple -P flags.

> It appears we need better syntax checking.

Can't really argue with this. But if you think these pg_restore
examples are bad, try this gem: reindexdb --table='foo; ALTER ROLE limited WITH superuser'

Josh

On 03/22/2013 10:13 PM, Josh Kupershmidt wrote:
>
> On Fri, Mar 22, 2013 at 9:35 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
>> postgres@jd-laptop:~$ pg_restore -d test -P 'by(),hello()' foo.sqlc
>
> Note, the pg_restore doc makes no mention of trying to squeeze
> multiple function prototypes in a single argument you've done here, or
> of using multiple -P flags.
>
>> It appears we need better syntax checking.
>
> Can't really argue with this. But if you think these pg_restore
> examples are bad, try this gem:
>    reindexdb --table='foo; ALTER ROLE limited WITH superuser'

That is HORRIBLE! Looks like our base utilities need some attention.

jD

>
> Josh
>
>