Thread: [8.0.3] Not dumping all sequences ...
I had posted this earlier, but had insufficient info (or access) to
provide much detail ... now, I've got the access, and this really isn't
making much sense ...
Have databse that \ds shows several sequences, but one in partiicular, I
can't seem to 'dump' with pg_dump:
public | xa_url_id_seq | sequence | pareto_su
there is another sequence, of similiar name, in the database that I'm
showing here since it will show up in the grep of the dump:
public | tmp_xa_url_id_seq | sequence | pareto_su
Now, I'm doing a simple:
pg_dump -U pareto_su --schema-only pareto > schema.out
and:
$ grep -i xa_url_id_seq schema.out nextval('xa_url_id_seq'::text), nextval('xa_url_id_seq'::text),
url_idbigint DEFAULT nextval('tmp_xa_url_id_seq'::text) NOT NULL,
-- Name: tmp_xa_url_id_seq; Type: SEQUENCE; Schema: public; Owner: pareto_su
CREATE SEQUENCE tmp_xa_url_id_seq
ALTER TABLE public.tmp_xa_url_id_seq OWNER TO pareto_su;
-- Name: tmp_xa_url_id_seq; Type: ACL; Schema: public; Owner: pareto_su
REVOKE ALL ON TABLE tmp_xa_url_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE tmp_xa_url_id_seq FROM pareto_su;
GRANT ALL ON TABLE tmp_xa_url_id_seq TO pareto_su;
GRANT SELECT,UPDATE ON TABLE tmp_xa_url_id_seq TO GROUP pareto_app;
As you will see above, there is a CREATE SEQUENCE for the
tmp_xa_url_id_seq SEQUENCE ... but none for the xa_url_id_seq one ...
I'm not seeing any errors generated when I do the pg_dump itself, and the
database itself is owned by the user I'm doing the dump as, as are all the
SEQUENCES/TABLES ...
I've even checked \dp, to make sure there was no permissions issues, and
unless I' mmissing something, they look correct:
public | xa_url_id_seq | sequence | {pareto_su=arwdRxt/pareto_su,"group pareto_app=rw/pareto_su"}
So, unless I'm overlooking something, the system sees the SEQUENCE, but
pg_dump doesn't see it ... is there something else I should be looking at
/ verifying as to why it isn't dump?
Oh, and just in case, I've checked that the pg_dump version is correct as
well:
$ pg_dump --version
pg_dump (PostgreSQL) 8.0.3
uname of the system shows:
Linux pareto 2.6.10-5-amd64-generic #1 Tue Apr 5 12:21:57 UTC 2005 x86_64 GNU/Linux
So, we're on a 64bit system ... but can't see how that would make a
different for a dump ...
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> Have databse that \ds shows several sequences, but one in partiicular, I
> can't seem to 'dump' with pg_dump:
> public | xa_url_id_seq | sequence | pareto_su
Given the name, this could be a SERIAL column's sequence --- which is
not dumped as a separate object by pg_dump, since recreating the SERIAL
column ought to create it.
regards, tom lane
On Tue, 2 Aug 2005, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> Have databse that \ds shows several sequences, but one in partiicular, I
>> can't seem to 'dump' with pg_dump:
>
>> public | xa_url_id_seq | sequence | pareto_su
>
> Given the name, this could be a SERIAL column's sequence --- which is
> not dumped as a separate object by pg_dump, since recreating the SERIAL
> column ought to create it.
One of their functions does an insert into the table with
"nextval('xa_url_id_seq'::text)" as part of the INSERT itself, so they
aren't using that field as a SERIAL type ... just checked the CREATE TABLE
for teh table, and its defined as a "bigint NOT NULL" ... so we're not
dealing with a SERIAL field, as far as I can tell ... in fact, grep'ng
their whole schema, they don't use SERIAL fields at all ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Tue, 2 Aug 2005, Tom Lane wrote:
>> Given the name, this could be a SERIAL column's sequence --- which is
>> not dumped as a separate object by pg_dump, since recreating the SERIAL
>> column ought to create it.
> One of their functions does an insert into the table with
> "nextval('xa_url_id_seq'::text)" as part of the INSERT itself, so they
> aren't using that field as a SERIAL type ... just checked the CREATE TABLE
> for teh table, and its defined as a "bigint NOT NULL" ...
Hmm, odd. But maybe there are traces of a SERIAL linkage? What do
you get from
select * from pg_depend where objid = 'xa_url_id_seq'::regclass;
regards, tom lane
On Tue, 2 Aug 2005, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> On Tue, 2 Aug 2005, Tom Lane wrote:
>>> Given the name, this could be a SERIAL column's sequence --- which is
>>> not dumped as a separate object by pg_dump, since recreating the SERIAL
>>> column ought to create it.
>
>> One of their functions does an insert into the table with
>> "nextval('xa_url_id_seq'::text)" as part of the INSERT itself, so they
>> aren't using that field as a SERIAL type ... just checked the CREATE TABLE
>> for teh table, and its defined as a "bigint NOT NULL" ...
>
> Hmm, odd. But maybe there are traces of a SERIAL linkage? What do
> you get from
>
> select * from pg_depend where objid = 'xa_url_id_seq'::regclass;
# select * from pg_depend where objid = 'xa_url_id_seq'::regclass; classid | objid | objsubid | refclassid | refobjid
|refobjsubid | deptype
---------+--------+----------+------------+----------+-------------+--------- 1259 | 335539 | 0 | 16672
| 2200 | 0 | n 1259 | 335539 | 0 | 1259 | 335541 | 1 | i
(2 rows)
'k, checking the docs ... deptype == i is an INTERNAL, and refobjid is
what is referencing it (in this case, xa_url, as I'd expect) ... but,
looking at \d for xa_url, I'm not seeing anything there to cause it ... no
serial values ... the only 'default nextval()' I can find in the schema
is something totally unrelated ...
next? :)
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Tue, 2 Aug 2005, Tom Lane wrote:
>> Hmm, odd. But maybe there are traces of a SERIAL linkage? What do
>> you get from
>>
>> select * from pg_depend where objid = 'xa_url_id_seq'::regclass;
> # select * from pg_depend where objid = 'xa_url_id_seq'::regclass;
> classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
> ---------+--------+----------+------------+----------+-------------+---------
> 1259 | 335539 | 0 | 16672 | 2200 | 0 | n
> 1259 | 335539 | 0 | 1259 | 335541 | 1 | i
> (2 rows)
Well, that second line is *definitely* a SERIAL column linkage.
> 'k, checking the docs ... deptype == i is an INTERNAL, and refobjid is
> what is referencing it (in this case, xa_url, as I'd expect) ... but,
> looking at \d for xa_url, I'm not seeing anything there to cause it ... no
> serial values ... the only 'default nextval()' I can find in the schema
> is something totally unrelated ...
Is it possible they did "create table xa_url(id bigserial, ...)" and
then later changed the default expression for the column?
regards, tom lane
On Tue, 2 Aug 2005, Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >> On Tue, 2 Aug 2005, Tom Lane wrote: >>> Hmm, odd. But maybe there are traces of a SERIAL linkage? What do >>> you get from >>> >>> select * from pg_depend where objid = 'xa_url_id_seq'::regclass; > >> # select * from pg_depend where objid = 'xa_url_id_seq'::regclass; >> classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype >> ---------+--------+----------+------------+----------+-------------+--------- >> 1259 | 335539 | 0 | 16672 | 2200 | 0 | n >> 1259 | 335539 | 0 | 1259 | 335541 | 1 | i >> (2 rows) > > Well, that second line is *definitely* a SERIAL column linkage. > >> 'k, checking the docs ... deptype == i is an INTERNAL, and refobjid is >> what is referencing it (in this case, xa_url, as I'd expect) ... but, >> looking at \d for xa_url, I'm not seeing anything there to cause it ... no >> serial values ... the only 'default nextval()' I can find in the schema >> is something totally unrelated ... > > Is it possible they did "create table xa_url(id bigserial, ...)" and > then later changed the default expression for the column? 'k, am checking into this ... is it a simple matter of removing that second record above from pg_depend to "fix" the pg_dump issue, or something more involved then that? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Tue, 2 Aug 2005, Tom Lane wrote:
>> Well, that second line is *definitely* a SERIAL column linkage.
>>
>> Is it possible they did "create table xa_url(id bigserial, ...)" and
>> then later changed the default expression for the column?
> 'k, am checking into this ... is it a simple matter of removing that
> second record above from pg_depend to "fix" the pg_dump issue, or
> something more involved then that?
AFAIR, removing that pg_depend entry would be enough to decouple the
sequence so it appears as an independent object in the pg_dump output.
However, I'm still wondering exactly what is wrong, if anything ---
does the pg_dump output not reload correctly? If so, what happens
exactly when you try?
regards, tom lane
I should point out that the bug I reported about dependencies and changing the type of a serial column still exists. Once you change a serial column to something else, you cannot ever change the default IIRC... Chris Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: > >>On Tue, 2 Aug 2005, Tom Lane wrote: >> >>>Well, that second line is *definitely* a SERIAL column linkage. >>> >>>Is it possible they did "create table xa_url(id bigserial, ...)" and >>>then later changed the default expression for the column? > > >>'k, am checking into this ... is it a simple matter of removing that >>second record above from pg_depend to "fix" the pg_dump issue, or >>something more involved then that? > > > AFAIR, removing that pg_depend entry would be enough to decouple the > sequence so it appears as an independent object in the pg_dump output. > However, I'm still wondering exactly what is wrong, if anything --- > does the pg_dump output not reload correctly? If so, what happens > exactly when you try? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq