pg_restore provided with Windows build not honoring check_function_bodies = FALSE? - Mailing list pgadmin-support

Users at work frequently had issues with missing triggers and functions, 
and I finally dug down and took a look. I've been able to confirm that 
8.4.4 pg_dump and pg_restore work as expected in Ubuntu 8.04 LTS. The 
server has the default (undefined) setting for check_function_bodies.

But when restoring any database dump in custom format in PGAdmin3 
1.10.5, check_function_bodies seems to be ignored and the functions are 
compiled during statement execution. I attempted to do some packet dumps 
to determine if it was being set again down the line, but couldn't find 
where it went wrong.

Here are the steps to duplicate:

postgres=# CREATE DATABASE function_test;
postgres=# \c function_test
psql (8.4.4)
You are now connected to database "function_test".
function_test=# CREATE TABLE foo ( bar varchar );
CREATE TABLE
function_test=# CREATE LANGUAGE plpgsql;
CREATE LANGUAGE
function_test=# CREATE OR REPLACE FUNCTION foobar () RETURNS VOID AS $$
DECLARE  barfoo foo%rowtype;
BEGIN  RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION
function_test=# SELECT foobar(); foobar
--------

(1 row)

function_test=# \q
postgres@ELY3:~$ pg_dump -Fc -f function_test.backup function_test
postgres@ELY3:~$ psql -c "CREATE DATABASE function_test_restore;"
CREATE DATABASE
postgres@ELY3:~$ pg_restore -v -Fc -d function_test_restore 
function_test.backup
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: creating FUNCTION foobar()
pg_restore: creating TABLE foo
pg_restore: restoring data for table "foo"
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for PROCEDURAL LANGUAGE plpgsql
pg_restore: setting owner and privileges for FUNCTION foobar()
pg_restore: setting owner and privileges for TABLE foo

postgres@ELY3:~$ pg_restore function_test.backup
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- Name: foobar(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION foobar() RETURNS void    LANGUAGE plpgsql IMMUTABLE    AS $$
DECLARE  barfoo foo%rowtype;
BEGIN  RETURN;
END;
$$;


ALTER FUNCTION public.foobar() OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: foo; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE foo (    bar character varying
);


ALTER TABLE public.foo OWNER TO postgres;

--
-- Data for Name: foo; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY foo (bar) FROM stdin;
\.


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--


* Back on the Windows machine.

C:\Program Files (x86)\pgAdmin III\1.10>pg_dump -v -h x.x.x.x -U lyadmin 
-Fc -f function_test.backup function_test
pg_dump: reading schemas
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 operator classes
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 user-defined operator families
pg_dump: reading user-defined conversions
pg_dump: reading user-defined tables
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: reading type casts
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "foo"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition
pg_dump: dumping contents of table foo



* On the Ubuntu box for a sec
postgres@ELY3:~$ psql -c "CREATE DATABASE function_test_restore2;"
CREATE DATABASE

* Back on Windows
C:\Program Files (x86)\pgAdmin III\1.10>pg_restore -v -h x.x.x.x -U 
lyadmin -Fc -d function_test_restore2 function_test.backup
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: creating FUNCTION foobar()
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 19; 1255 11517367 
FUNCTION foobar() postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
"foo" does not exist
CONTEXT:  compilation of PL/pgSQL function "foobar" near line 2    Command was: CREATE FUNCTION foobar() RETURNS void
LANGUAGE plpgsql IMMUTABLE    AS $$
 
DECLARE  barfoo foo%rowtype;
BEGIN  RETURN;...
pg_restore: [archiver (db)] could not execute query: ERROR:  function 
public.foobar() does not exist    Command was: ALTER FUNCTION public.foobar() OWNER TO postgres;
pg_restore: creating TABLE foo
pg_restore: restoring data for table "foo"
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for PROCEDURAL LANGUAGE plpgsql
pg_restore: setting owner and privileges for FUNCTION foobar()
pg_restore: setting owner and privileges for TABLE foo
WARNING: errors ignored on restore: 2