pg_restore provided with Windows build not honoring check_function_bodies = FALSE?

From: Derek Arnold <derek(dot)arnold(at)dealerbuilt(dot)com>
To: pgadmin-support(at)postgresql(dot)org
Subject: pg_restore provided with Windows build not honoring check_function_bodies = FALSE?
Date: 2010-09-17 21:07:17
Message-ID: 4C93D885.6030701@dealerbuilt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(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

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Guillaume Lelarge 2010-09-18 20:09:13 Re: pg_restore provided with Windows build not honoring check_function_bodies = FALSE?
Previous Message Guillaume Lelarge 2010-09-16 22:21:21 Re: Column width