Thread: Odd messages on reloading DB table

Odd messages on reloading DB table

From

Steve Wampler

Date:

07 February 2019, 19:28:59

With PostgreSQL 9.5.15, I ran:

    pg_dump -t targets -d atst.experimentdb >nT.db

then I ran:

    psql -h langley atst.experimentdb <nT.db

and got:

    SET
    SET
    SET
    SET
     set_config
    ------------

    (1 row)

    SET
    SET
    SET
    SET
    SET
    ERROR:  relation "targets" already exists
    ALTER TABLE
    ERROR:  relation "targets" does not exist
    LINE 1: UPDATE targets SET             time_stamp=new.time_stamp,   ...
                ^
    QUERY:  UPDATE targets SET             time_stamp=new.time_stamp,            modified=new.modified, 
id=new.id,            targets=new.targets     WHERE (id=new.id)
    CONTEXT:  PL/pgSQL function public.targets_insert_or_update() line 1 at SQL statement
    COPY targets, line 1: "2017-10-31 09:37:28.798152    t    az_el_target     
{"name":"AZ_EL_Targets","priority":10,"tags":["{\\"Tag.tag..."
    ERROR:  relation "id_id_targets" already exists
    ERROR:  relation "time_stamp_id_targets" already exists
    ERROR:  trigger "targets_trigger_insert" for relation "targets" already exists
ERROR:  trigger "targets_trigger_update" for relation "targets" already exists
REVOKE
REVOKE
GRANT
GRANT

Eh?  It looks like it worked (maybe), but why:

    (1) the table already exist and the immediately doesn't exist?
    (2) report ERROR on UPDATE when there are no UPDATES in the input file?

I see similar errors when using -Fc on the dump and pg_restore to read it back in,
    except, I use "-a" on both pg_dump and pg_restore so the error about the
    table already exising goes away [no CREATE TABLE anymore, of course).

Can someone explain what happened and how it can be fixed?

For reference, here are the first few lines of nT.db:
=====================================================
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.15
-- Dumped by pg_dump version 9.5.15

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: targets; Type: TABLE; Schema: public; Owner: atst
--

CREATE TABLE public.targets (
     time_stamp timestamp without time zone NOT NULL,
     modified boolean DEFAULT false,
     id character varying(256) NOT NULL,
     targets text,
     marked boolean DEFAULT true,
     collectable boolean DEFAULT false,
     proposalid text
);


ALTER TABLE public.targets OWNER TO atst;

--
-- Data for Name: targets; Type: TABLE DATA; Schema: public; Owner: atst
--

COPY public.targets (time_stamp, modified, id, targets, marked, collectable, proposalid) FROM stdin;
=======================================================================
-- 
Steve Wampler -- swampler@nso.edu
The gods that smiled on your birth are now laughing out loud.

Re: Odd messages on reloading DB table

From

Steve Wampler

Date:

07 February 2019, 19:38:54

On 2/7/19 9:28 AM, Steve Wampler wrote:
> With PostgreSQL 9.5.15, I ran:
> 
>     pg_dump -t targets -d atst.experimentdb >nT.db
> 
> then I ran:
> 
>     psql -h langley atst.experimentdb <nT.db
> 
> and got:
...
>     ERROR:  relation "targets" already exists
>     ALTER TABLE
>     ERROR:  relation "targets" does not exist
>     LINE 1: UPDATE targets SET             time_stamp=new.time_stamp,   ...
>                 ^
>     QUERY:  UPDATE targets SET             time_stamp=new.time_stamp,            modified=new.modified, 
> id=new.id,            targets=new.targets     WHERE (id=new.id)
>     CONTEXT:  PL/pgSQL function public.targets_insert_or_update() line 1 at SQL statement
>     COPY targets, line 1: "2017-10-31 09:37:28.798152    t    az_el_target 
> {"name":"AZ_EL_Targets","priority":10,"tags":["{\\"Tag.tag..."
>     ERROR:  relation "id_id_targets" already exists
>     ERROR:  relation "time_stamp_id_targets" already exists
>     ERROR:  trigger "targets_trigger_insert" for relation "targets" already exists
> ERROR:  trigger "targets_trigger_update" for relation "targets" already exists
> REVOKE
> REVOKE
> GRANT
> GRANT
> 
> Eh?  It looks like it worked (maybe), but why:
> 
>     (1) the table already exist and the immediately doesn't exist?
>     (2) report ERROR on UPDATE when there are no UPDATES in the input file?

I take it back.  The content of the table was not restored to the values in nT.db afterall.
-- 
Steve Wampler -- swampler@nso.edu
The gods that smiled on your birth are now laughing out loud.

Re: Odd messages on reloading DB table

From

"David G. Johnston"

Date:

08 February 2019, 01:24:13

On Thursday, February 7, 2019, Steve Wampler <swampler@nso.edu> wrote:

(1) the table already exist and the immediately doesn't exist?
(2) report ERROR on UPDATE when there are no UPDATES in the input file

Most likely the first attempt was schema qualified and so found the existing targets table while the second attempt was not schema qualified and targets is not in the search path.

One guess I have is that triggers are involved here and those triggers need to be more resiliant in face of the recent search_path security update.

David J.

Re: Odd messages on reloading DB table

From

Steve Wampler

Date:

11 February 2019, 22:29:57

On 2/7/19 3:24 PM, David G. Johnston wrote:
> On Thursday, February 7, 2019, Steve Wampler <swampler@nso.edu <mailto:swampler@nso.edu>> wrote:
> 
>         (1) the table already exist and the immediately doesn't exist?
>         (2) report ERROR on UPDATE when there are no UPDATES in the input file
> 
> 
> 
> Most likely the first attempt was schema qualified and so found the existing targets table while the second attempt
was

> not schema qualified and targets is not in the search path.
> 
> One guess I have is that triggers are involved here and those triggers need to be more resiliant in face of the
recent

> search_path security update.

Thanks - but I thought the search_path update was a PG 10 change and so shouldn't reflect on 9.5.15 behavior.  Did it
get back-ported?

In any event I'm surprised that pg_dump for 9.5.15 can produce a dump that can't be restored by either pg_restore
(when -Fc is used on both ends) or with psql (without -Fc used on pg_dump).  I would have expected some message
from pg_dump if it ran into issues preventing this.

-- 
Steve Wampler -- swampler@nso.edu
The gods that smiled on your birth are now laughing out loud.

Re: Odd messages on reloading DB table

From

"David G. Johnston"

Date:

11 February 2019, 23:03:29

On Mon, Feb 11, 2019 at 12:29 PM Steve Wampler <swampler@nso.edu> wrote:
> Thanks - but I thought the search_path update was a PG 10 change and so shouldn't reflect on 9.5.15 behavior.  Did
it
> get back-ported?

Yes, it was deemed a security vulnerability and thus back-patched.
Release notes will indicate when that happened.

> In any event I'm surprised that pg_dump for 9.5.15 can produce a dump that can't be restored by either pg_restore
> (when -Fc is used on both ends) or with psql (without -Fc used on pg_dump).  I would have expected some message
> from pg_dump if it ran into issues preventing this.

pg_dump doesn't look into function bodies to make determinations - to
it those are just strings.  There are number of susceptible areas that
cannot be reliably detected during backup.  That is why it is
imperative that backups are restored before being considered valid (at
the least those backups that occur subsequent to a schema change).

David J.