Odd messages on reloading DB table

From: Steve Wampler <swampler(at)nso(dot)edu>
To: Postgres-General <pgsql-general(at)postgresql(dot)org>
Subject: Odd messages on reloading DB table
Date: 2019-02-07 16:28:59
Message-ID: 862e9109-6544-8d87-31fd-5fe9b88819dc@nso.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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(at)nso(dot)edu
The gods that smiled on your birth are now laughing out loud.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Wampler 2019-02-07 16:38:54 Re: Odd messages on reloading DB table
Previous Message Pavel Stehule 2019-02-07 11:02:16 Re: How to add a new psql command ?