pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17008: pg_dump doesn't dump a sequence with bigint type
9+ messages / 5 participants
[nested] [flat]
* BUG #17008: pg_dump doesn't dump a sequence with bigint type
@ 2021-05-13 15:45 PG Bug reporting form <noreply@postgresql.org>
2021-05-13 16:31 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type David G. Johnston <david.g.johnston@gmail.com>
2021-05-13 16:52 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 9+ messages in thread
From: PG Bug reporting form @ 2021-05-13 15:45 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jacevedo@we-techs.com
The following bug has been logged on the website:
Bug reference: 17008
Logged by: Jaime Acevedo
Email address: jacevedo@we-techs.com
PostgreSQL version: 12.6
Operating system: ubuntu 20.04
Description:
I'm using pg_dump to dump my database version 12.5 (RDS), pg_dump version
12.6 but I have a the following table
CREATE TABLE public.projects_historydata
(
id bigint NOT NULL DEFAULT
nextval('projects_historydata_id_seq'::regclass),
value double precision NOT NULL,
value_at timestamp with time zone NOT NULL,
var_id integer NOT NULL,
created timestamp with time zone NOT NULL,
CONSTRAINT projects_historydata_pkey PRIMARY KEY (id),
CONSTRAINT projects_historydata_var_id_21f99477_fk_projects_var_id
FOREIGN KEY (var_id)
REFERENCES public.projects_var (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED
)
WITH (
autovacuum_enabled = TRUE
)
TABLESPACE pg_default;
ALTER TABLE public.projects_historydata
OWNER to wedbmaster;
GRANT SELECT ON TABLE public.projects_historydata TO read_only_user;
GRANT SELECT ON TABLE public.projects_historydata TO readaccess;
GRANT ALL ON TABLE public.projects_historydata TO wedbmaster;
-- Index: projects_historydata_db9d0193
-- DROP INDEX public.projects_historydata_db9d0193;
CREATE INDEX projects_historydata_db9d0193
ON public.projects_historydata USING btree
(var_id ASC NULLS LAST)
TABLESPACE pg_default;
-- Index: projects_historydata_var_id_value_at_idx
-- DROP INDEX public.projects_historydata_var_id_value_at_idx;
CREATE INDEX projects_historydata_var_id_value_at_idx
ON public.projects_historydata USING btree
(var_id ASC NULLS LAST, value_at ASC NULLS LAST)
TABLESPACE pg_default;
-- Trigger: after_insert_projects_historydata_trigger
-- DROP TRIGGER after_insert_projects_historydata_trigger ON
public.projects_historydata;
CREATE TRIGGER after_insert_projects_historydata_trigger
AFTER INSERT
ON public.projects_historydata
FOR EACH ROW
EXECUTE FUNCTION public.projects_historydata_delete_master();
-- Trigger: before_insert_projects_historydata_trigger
-- DROP TRIGGER before_insert_projects_historydata_trigger ON
public.projects_historydata;
CREATE TRIGGER before_insert_projects_historydata_trigger
BEFORE INSERT
ON public.projects_historydata
FOR EACH ROW
EXECUTE FUNCTION public.projects_historydata_insert_child();
this table make references to the sequence projects_historydata_id_seq
-- SEQUENCE: public.projects_project_id_seq
-- DROP SEQUENCE public.projects_project_id_seq;
CREATE SEQUENCE public.projects_project_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
ALTER SEQUENCE public.projects_project_id_seq
OWNER TO wedbmaster;
GRANT SELECT ON SEQUENCE public.projects_project_id_seq TO read_only_user;
GRANT ALL ON SEQUENCE public.projects_project_id_seq TO wedbmaster;
At the moment to do a pg_dump the pg_dump doesn't make the script for the
sequence, then the restore fail to create the table.
Please see that the id value for the table (the value handle by the
sequences is not an integer is biginteger)
Regards,
Jaime Acevedo E.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type
2021-05-13 15:45 BUG #17008: pg_dump doesn't dump a sequence with bigint type PG Bug reporting form <noreply@postgresql.org>
@ 2021-05-13 16:31 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2021-05-13 16:31 UTC (permalink / raw)
To: jacevedo@we-techs.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Thu, May 13, 2021 at 9:04 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17008
> Logged by: Jaime Acevedo
> Email address: jacevedo@we-techs.com
> PostgreSQL version: 12.6
> Operating system: ubuntu 20.04
> Description:
>
> I'm using pg_dump to dump my database version 12.5 (RDS), pg_dump version
> 12.6 but I have a the following table
>
> CREATE TABLE public.projects_historydata
> (
> id bigint NOT NULL DEFAULT
> nextval('projects_historydata_id_seq'::regclass),
> [...]
> this table make references to the sequence projects_historydata_id_seq
> -- SEQUENCE: public.projects_project_id_seq
>
> -- DROP SEQUENCE public.projects_project_id_seq;
>
> CREATE SEQUENCE public.projects_project_id_seq
> INCREMENT 1
> START 1
> MINVALUE 1
> MAXVALUE 9223372036854775807
> CACHE 1;
> [...]
>
> At the moment to do a pg_dump the pg_dump doesn't make the script for the
> sequence, then the restore fail to create the table.
>
> Please see that the id value for the table (the value handle by the
> sequences is not an integer is biginteger)
>
>
I'm confused regarding the complaint. I see that the sequence used by the
history table is different from the single sequence shown in the email. Is
the script in the email the entire dump script? What dump command did you
use?
The sequence you show is a bigint sequence since that is the default and
there is nothing overriding it. Though since you seem to be showing
mis-matched information that observation is only a general one.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type
2021-05-13 15:45 BUG #17008: pg_dump doesn't dump a sequence with bigint type PG Bug reporting form <noreply@postgresql.org>
@ 2021-05-13 16:52 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-05-13 18:11 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2021-05-13 16:52 UTC (permalink / raw)
To: jacevedo@we-techs.com; pgsql-bugs@lists.postgresql.org
On 2021-May-13, PG Bug reporting form wrote:
> I'm using pg_dump to dump my database version 12.5 (RDS), pg_dump version
> 12.6 but I have a the following table
>
> CREATE TABLE public.projects_historydata
> (
> id bigint NOT NULL DEFAULT
> nextval('projects_historydata_id_seq'::regclass),
> value double precision NOT NULL,
> value_at timestamp with time zone NOT NULL,
> var_id integer NOT NULL,
> created timestamp with time zone NOT NULL,
> CONSTRAINT projects_historydata_pkey PRIMARY KEY (id),
> CONSTRAINT projects_historydata_var_id_21f99477_fk_projects_var_id
> FOREIGN KEY (var_id)
> REFERENCES public.projects_var (id) MATCH SIMPLE
> ON UPDATE NO ACTION
> ON DELETE NO ACTION
> DEFERRABLE INITIALLY DEFERRED
> )
>
> WITH (
> autovacuum_enabled = TRUE
> )
> TABLESPACE pg_default;
Hello Jaime
How did you get this table definition?
I suggest to run this:
ALTER SEQUENCE projects_historydata_id_seq OWNED BY public.projects_historydata.id
and then things will work better.
Cheers
--
Álvaro Herrera Valdivia, Chile
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type
2021-05-13 15:45 BUG #17008: pg_dump doesn't dump a sequence with bigint type PG Bug reporting form <noreply@postgresql.org>
2021-05-13 16:52 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2021-05-13 18:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-13 18:20 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Jaime Acevedo <jacevedo@we-techs.com>
2021-05-13 18:21 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2021-05-13 18:11 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: jacevedo@we-techs.com; pgsql-bugs@lists.postgresql.org
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2021-May-13, PG Bug reporting form wrote:
>> I'm using pg_dump to dump my database version 12.5 (RDS), pg_dump version
>> 12.6 but I have a the following table
> How did you get this table definition?
Indeed. It seems unlikely to me that this is pg_dump's fault;
I think it's just reporting the way things are in the source
database. If projects_historydata_id_seq is the wrong width,
that's probably because it was manually created that way and
then manually attached to projects_historydata.id.
> I suggest to run this:
> ALTER SEQUENCE projects_historydata_id_seq OWNED BY public.projects_historydata.id
> and then things will work better.
This won't in itself fix the sequence's width; you'd need
to do some additional ALTERs if that's wrong.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type
2021-05-13 15:45 BUG #17008: pg_dump doesn't dump a sequence with bigint type PG Bug reporting form <noreply@postgresql.org>
2021-05-13 16:52 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-05-13 18:11 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-05-13 18:20 ` Jaime Acevedo <jacevedo@we-techs.com>
2021-05-13 18:29 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 1 reply; 9+ messages in thread
From: Jaime Acevedo @ 2021-05-13 18:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-bugs@lists.postgresql.org
Sorry, I made a mistake at the moment to copy & paste the sequences.
But this finally the problems was the filter to do dump, when I use
--exclude-table option with pattern projects_historydata_* for some reason
the sequences was excluded finally I used the pattern
projects_historydata_[0-9]* to exclude only the tables that I want to
exclude.
I don´t know if that behavior is right.
Regards,
Jaime Acevedo E.
El jue, 13 may 2021 a las 14:11, Tom Lane (<tgl@sss.pgh.pa.us>) escribió:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > On 2021-May-13, PG Bug reporting form wrote:
> >> I'm using pg_dump to dump my database version 12.5 (RDS), pg_dump
> version
> >> 12.6 but I have a the following table
>
> > How did you get this table definition?
>
> Indeed. It seems unlikely to me that this is pg_dump's fault;
> I think it's just reporting the way things are in the source
> database. If projects_historydata_id_seq is the wrong width,
> that's probably because it was manually created that way and
> then manually attached to projects_historydata.id.
>
> > I suggest to run this:
> > ALTER SEQUENCE projects_historydata_id_seq OWNED BY
> public.projects_historydata.id
> > and then things will work better.
>
> This won't in itself fix the sequence's width; you'd need
> to do some additional ALTERs if that's wrong.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type
2021-05-13 15:45 BUG #17008: pg_dump doesn't dump a sequence with bigint type PG Bug reporting form <noreply@postgresql.org>
2021-05-13 16:52 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-05-13 18:11 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Tom Lane <tgl@sss.pgh.pa.us>
2021-05-13 18:20 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Jaime Acevedo <jacevedo@we-techs.com>
@ 2021-05-13 18:29 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-05-13 18:34 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2021-05-13 18:29 UTC (permalink / raw)
To: Jaime Acevedo <jacevedo@we-techs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
On 2021-May-13, Jaime Acevedo wrote:
> Sorry, I made a mistake at the moment to copy & paste the sequences.
>
> But this finally the problems was the filter to do dump, when I use
> --exclude-table option with pattern projects_historydata_* for some reason
> the sequences was excluded finally I used the pattern
> projects_historydata_[0-9]* to exclude only the tables that I want to
> exclude.
Ah, that makes sense.
> I don´t know if that behavior is right.
Hmm, I think --exclude-tables should exclude tables only, not sequences.
And it does behave like that for me, in a quick test with version 10.
What version of Postgres are you using?
--
Álvaro Herrera 39°49'30"S 73°17'W
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type
2021-05-13 15:45 BUG #17008: pg_dump doesn't dump a sequence with bigint type PG Bug reporting form <noreply@postgresql.org>
2021-05-13 16:52 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-05-13 18:11 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Tom Lane <tgl@sss.pgh.pa.us>
2021-05-13 18:20 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Jaime Acevedo <jacevedo@we-techs.com>
2021-05-13 18:29 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2021-05-13 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-13 18:52 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Jaime Acevedo <jacevedo@we-techs.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2021-05-13 18:34 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Jaime Acevedo <jacevedo@we-techs.com>; pgsql-bugs@lists.postgresql.org
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Hmm, I think --exclude-tables should exclude tables only, not sequences.
> And it does behave like that for me, in a quick test with version 10.
Probably your original thought is right: the sequence isn't properly owned
by the table column, so it doesn't get dumped in a selective dump.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type
2021-05-13 15:45 BUG #17008: pg_dump doesn't dump a sequence with bigint type PG Bug reporting form <noreply@postgresql.org>
2021-05-13 16:52 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-05-13 18:11 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Tom Lane <tgl@sss.pgh.pa.us>
2021-05-13 18:20 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Jaime Acevedo <jacevedo@we-techs.com>
2021-05-13 18:29 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-05-13 18:34 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-05-13 18:52 ` Jaime Acevedo <jacevedo@we-techs.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jaime Acevedo @ 2021-05-13 18:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-bugs@lists.postgresql.org
Ok, thanks.
El jue, 13 may 2021 a las 14:34, Tom Lane (<tgl@sss.pgh.pa.us>) escribió:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Hmm, I think --exclude-tables should exclude tables only, not sequences.
> > And it does behave like that for me, in a quick test with version 10.
>
> Probably your original thought is right: the sequence isn't properly owned
> by the table column, so it doesn't get dumped in a selective dump.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type
2021-05-13 15:45 BUG #17008: pg_dump doesn't dump a sequence with bigint type PG Bug reporting form <noreply@postgresql.org>
2021-05-13 16:52 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-05-13 18:11 ` Re: BUG #17008: pg_dump doesn't dump a sequence with bigint type Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-05-13 18:21 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 0 replies; 9+ messages in thread
From: Alvaro Herrera @ 2021-05-13 18:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jacevedo@we-techs.com; pgsql-bugs@lists.postgresql.org
On 2021-May-13, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > I suggest to run this:
> > ALTER SEQUENCE projects_historydata_id_seq OWNED BY public.projects_historydata.id
> > and then things will work better.
>
> This won't in itself fix the sequence's width; you'd need
> to do some additional ALTERs if that's wrong.
True.
What I'm thinking is that Jaime misunderstood what the actual problem
is; note that he posted the CREATE SEQUENCE statement for an unrelated
sequence, and didn't post any statement for the correct one. He thinks
the problem is related to the sequence being bigint, but I doubt it
really is.
--
Álvaro Herrera Valdivia, Chile
"No tengo por qué estar de acuerdo con lo que pienso"
(Carlos Caszeli)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2021-05-13 18:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-13 15:45 BUG #17008: pg_dump doesn't dump a sequence with bigint type PG Bug reporting form <noreply@postgresql.org>
2021-05-13 16:31 ` David G. Johnston <david.g.johnston@gmail.com>
2021-05-13 16:52 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-05-13 18:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-13 18:20 ` Jaime Acevedo <jacevedo@we-techs.com>
2021-05-13 18:29 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-05-13 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-13 18:52 ` Jaime Acevedo <jacevedo@we-techs.com>
2021-05-13 18:21 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox