Re: pg_restore provided with Windows build not honoring check_function_bodies = FALSE?

Le 17/09/2010 23:07, Derek Arnold a écrit :
> 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.
>

pg_dump adds a SET line to set the value of check_function_bodies to false.

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

pgAdmin3 doesn't do anything with check_function_bodies.

> 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(at)ELY3:~$ pg_dump -Fc -f function_test.backup function_test
> postgres(at)ELY3:~$ psql -c "CREATE DATABASE function_test_restore;"
> CREATE DATABASE
> postgres(at)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(at)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(at)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
>

Can you do a pg_restore without -d, so that we can see the SET SQL
commands it executs. I read the part of the code where pg_dump adds the
check_function_bodies command, and I don't see anything specific to the
Windows platform. I'll try on my Windows box but it won't be before
tomorrow evening.

--
Guillaume
http://www.postgresql.fr
http://dalibo.com