Обсуждение: pg_dump/restore problem

I am trying to move some data from an 8.0.7 instance to a 7.4.8
instance. I did a pg_dump and then did a pg_restore.
For the most part the data transferred through to the older instance but
I had problems with the sequence updates.

 SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('nw_foo',
'foo_id'), 678, true);
ERROR:  function pg_catalog.pg_get_serial_sequence("unknown", "unknown")
does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.

I am looking for a an alternative to manually issuing alter sequence
commands.

Any help appreciated.

On Mon, Jun 12, 2006 at 04:51:18PM -0400, renneyt@yahoo.com wrote:
> I am trying to move some data from an 8.0.7 instance to a 7.4.8
> instance. I did a pg_dump and then did a pg_restore.
> For the most part the data transferred through to the older instance but
> I had problems with the sequence updates.
>
> SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('nw_foo',
> 'foo_id'), 678, true);
> ERROR:  function pg_catalog.pg_get_serial_sequence("unknown", "unknown")
> does not exist
> HINT:  No function matches the given name and argument types. You may
> need to add explicit type casts.
>
> I am looking for a an alternative to manually issuing alter sequence
> commands.

From http://www.postgresql.org/docs/8.1/interactive/release-8-0.html:

# Add pg_get_serial_sequence() to return a SERIAL column's sequence name
(Christopher)

This allows automated scripts to reliably find the SERIAL sequence name.

If you use a 7.x copy of pg_dump it might work, though there could be
catalog changes that make that a problem.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim C. Nasby wrote:

On Mon, Jun 12, 2006 at 04:51:18PM -0400, renneyt@yahoo.com wrote: 

I am trying to move some data from an 8.0.7 instance to a 7.4.8 
instance. I did a pg_dump and then did a pg_restore.
For the most part the data transferred through to the older instance but 
I had problems with the sequence updates.

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('nw_foo', 
'foo_id'), 678, true);
ERROR:  function pg_catalog.pg_get_serial_sequence("unknown", "unknown") 
does not exist
HINT:  No function matches the given name and argument types. You may 
need to add explicit type casts.

I am looking for a an alternative to manually issuing alter sequence 
commands.   

>From http://www.postgresql.org/docs/8.1/interactive/release-8-0.html:

# Add pg_get_serial_sequence() to return a SERIAL column's sequence name
(Christopher)
 

Does  pg_get_serial_sequence()  exist as plpgsql code? Where may I find it? I would like to retrofit it into a 7.4.8 PG database.

Thanks

This allows automated scripts to reliably find the SERIAL sequence name. 

If you use a 7.x copy of pg_dump it might work, though there could be
catalog changes that make that a problem. 

On Tue, Jun 13, 2006 at 04:28:47PM -0400, renneyt@yahoo.com wrote:
> Does  pg_get_serial_sequence()  exist as plpgsql code? Where may I find
> it? I would like to retrofit it into a 7.4.8 PG database.

No, but it shouldn't be terribly hard to do it in plpgsql; you just need
to build the right sequence name (assuming you haven't been messing with
the sequence names).
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Tue, Jun 13, 2006 at 04:28:47PM -0400, renneyt@yahoo.com wrote:
>> Does  pg_get_serial_sequence()  exist as plpgsql code? Where may I find
>> it? I would like to retrofit it into a 7.4.8 PG database.

> No, but it shouldn't be terribly hard to do it in plpgsql; you just need
> to build the right sequence name (assuming you haven't been messing with
> the sequence names).

You could actually do it 100% correctly in plpgsql; it's really just a
query into pg_depend to find the sequence dependent on the column.

But if your need is just to restore one specific dump, I'd be inclined
to edit the dump file.  Most of the time, pg_get_serial_sequence('foo',
'bar') just results in 'foo_bar_seq'.

            regards, tom lane

Jim C. Nasby wrote:

On Tue, Jun 13, 2006 at 04:28:47PM -0400, renneyt@yahoo.com wrote: 

Does  pg_get_serial_sequence()  exist as plpgsql code? Where may I find 
it? I would like to retrofit it into a 7.4.8 PG database.   

No, but it shouldn't be terribly hard to do it in plpgsql; you just need
to build the right sequence name (assuming you haven't been messing with
the sequence names). 

Because of time and my unfamiliarity with pg_depend, I did a quick and dirty hack that worked for me.

My schema is foo.

CREATE or REPLACE  FUNCTION foo.pg_get_serial_sequence(varchar(500), varchar(500)) RETURNS varchar(500) AS '
DECLARE
totcomments integer;
begin
  return $1||''_''||$2||''_seq'';
end;
' language 'plpgsql';

,
Because it is a hosted ISP version of PG and I do not have write access to pg_catalog I had to run the backup dump through sed to change the schema from pg_catalog to foo.


cat pgsql.dmp | sed 's/pg_catalog.pg_get_serial_sequence/foo\.pg_get_serial_sequence/g'
> pgsql.dmp2

Thanks to all for all the suggestions.