Horton

Thread

  1. network_ops in 7.0 and pg_dump question

    Oleg Bartunov <oleg@sai.msu.su> — 2000-02-07T20:31:22Z

    Hi,

what's happen with network_ops in current CVS ?
I just synced sources and couldn't load dump from 6.5.3 - 
problem occures on 
CREATE  INDEX "face_key" on "face" using btree ( "eid" "int4_ops", "ip" "network_ops" );

The message I got:
CREATE
ERROR:  DefineIndex: network_ops class not found


Table face:
election=# \d face
           Table "face"
 Attribute |    Type    | Modifier 
-----------+------------+----------
 eid       | integer    | 
 ip        | inet       | 
 vdate     | datetime   | 
 ftrs      | smallint[] | 



Also, does new pg_dump is aware about order of defining of function 
and tables, when function is used in CREATE TABLE, for example:
CREATE TABLE "applicant" (
        "candx" int2 DEFAULT next_applicant ( ) NOT NULL,
        "candidate" text, 
        "candt" int2,
        "img" text);
but function next_applicant() is dumped in 6.5.3 after  CREATE TABLE
and this cause an error. I had manually edit dump file to reverse order :-)



	Regards,

		Oleg

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

  2. Re: [HACKERS] network_ops in 7.0 and pg_dump question

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-02-07T20:52:01Z

    > Hi,
> 
> what's happen with network_ops in current CVS ?
> I just synced sources and couldn't load dump from 6.5.3 - 
> problem occures on 
> CREATE  INDEX "face_key" on "face" using btree ( "eid" "int4_ops", "ip" "network_ops" );
> 
> The message I got:
> CREATE
> ERROR:  DefineIndex: network_ops class not found
> 

Oops, my fault.  There was some confusing links in the catalog for the
ip/cidr types.  They pointed to the same *ops, which made the table
non-unique, so the cache would grab a random matching entry.  The new
system has separate *ops for each type.  We were basically using the
cache on a non-unique entry.  We would grab the first match.  The new
code uses the same underlying functions, but moves the duplication down
one level.

Now, how to convert these?  Not supplying the ops works fine, but
pg_dump supplies the ops.  Maybe in gram.y, if they supply network_ops,
we should just remove that from being passed to the backend for a few
releases.  Comments?



-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  3. Re: [HACKERS] network_ops in 7.0 and pg_dump question

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-07T23:30:51Z

    Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Now, how to convert these?  Not supplying the ops works fine, but
> pg_dump supplies the ops.  Maybe in gram.y, if they supply network_ops,
> we should just remove that from being passed to the backend for a few
> releases.  Comments?

Ugly, but probably the best stopgap for backwards compatibility ...
at least I can't think of a better answer, since we have no way to
change what 6.5 pg_dump will dump.

You're only going to suppress "network_ops" if it appears in the
ops position of a CREATE INDEX, right?  Don't want to stop people
from using the name for fields and so on.

			regards, tom lane

  4. Re: [HACKERS] network_ops in 7.0 and pg_dump question

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-07T23:36:53Z

    Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Now, how to convert these?  Not supplying the ops works fine, but
> pg_dump supplies the ops.  Maybe in gram.y, if they supply network_ops,
> we should just remove that from being passed to the backend for a few
> releases.  Comments?

Actually, rather than hacking gram.y, it seems like it would be cleaner
to put the kluge in whatever part of the parser looks up the ops name.

Of course a kluge is a kluge no matter what...

			regards, tom lane

  5. Re: [HACKERS] network_ops in 7.0 and pg_dump question

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-02-08T00:03:29Z

    > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Now, how to convert these?  Not supplying the ops works fine, but
> > pg_dump supplies the ops.  Maybe in gram.y, if they supply network_ops,
> > we should just remove that from being passed to the backend for a few
> > releases.  Comments?
> 
> Ugly, but probably the best stopgap for backwards compatibility ...
> at least I can't think of a better answer, since we have no way to
> change what 6.5 pg_dump will dump.
> 
> You're only going to suppress "network_ops" if it appears in the
> ops position of a CREATE INDEX, right?  Don't want to stop people
> from using the name for fields and so on.

No, just at that part in the grammar.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  6. Re: [HACKERS] network_ops in 7.0 and pg_dump question

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-02-08T00:05:33Z

    > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Now, how to convert these?  Not supplying the ops works fine, but
> > pg_dump supplies the ops.  Maybe in gram.y, if they supply network_ops,
> > we should just remove that from being passed to the backend for a few
> > releases.  Comments?
> 
> Actually, rather than hacking gram.y, it seems like it would be cleaner
> to put the kluge in whatever part of the parser looks up the ops name.
> 
> Of course a kluge is a kluge no matter what...

I like it in gram.y because it is more visible there and easier to
remove later.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  7. Re: [HACKERS] network_ops in 7.0 and pg_dump question

    Oleg Bartunov <oleg@sai.msu.su> — 2000-02-08T10:42:57Z

    Thanks,
creation of index works now. But what about pg_dump ?
I still have to edit manually dump file.
look to excerption from dump file:
CREATE TABLE "applicant" (
        "candx" int2 DEFAULT next_applicant() NOT NULL,
        "candidate" text,
        "candt" int2,
        "img" text
);

This fails because function next_applicant dumps later !

Here is a psql output:
You are now connected as new user megera.
ERROR:  Relation 'applicant' does not exist
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \.
ERROR:  parser: parse error at or near "2"
invalid command \.
ERROR:  parser: parse error at or near "1"
invalid command \.
ERROR:  parser: parse error at or near "1"
invalid command \.
ERROR:  parser: parse error at or near "1"
invalid command \.
ERROR:  parser: parse error at or near "24"
invalid command \.
ERROR:  parser: parse error at or near "24"
CREATE
CREATE

Hmm, error diagnostics still not very informative :-)

	Regards,

		Oleg

On Mon, 7 Feb 2000, Bruce Momjian wrote:

> Date: Mon, 7 Feb 2000 19:03:29 -0500 (EST)
> From: Bruce Momjian <pgman@candle.pha.pa.us>
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: Oleg Bartunov <oleg@sai.msu.su>, hackers@postgreSQL.org
> Subject: Re: [HACKERS] network_ops in 7.0 and pg_dump question
> 
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Now, how to convert these?  Not supplying the ops works fine, but
> > > pg_dump supplies the ops.  Maybe in gram.y, if they supply network_ops,
> > > we should just remove that from being passed to the backend for a few
> > > releases.  Comments?
> > 
> > Ugly, but probably the best stopgap for backwards compatibility ...
> > at least I can't think of a better answer, since we have no way to
> > change what 6.5 pg_dump will dump.
> > 
> > You're only going to suppress "network_ops" if it appears in the
> > ops position of a CREATE INDEX, right?  Don't want to stop people
> > from using the name for fields and so on.
> 
> No, just at that part in the grammar.
> 
> -- 
>   Bruce Momjian                        |  http://www.op.net/~candle
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 
> ************
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

  8. Ordering of pg_dump output

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-08T16:14:46Z

    Oleg Bartunov <oleg@sai.msu.su> writes:
> creation of index works now. But what about pg_dump ?
> I still have to edit manually dump file.
> look to excerption from dump file:
> CREATE TABLE "applicant" (
>         "candx" int2 DEFAULT next_applicant() NOT NULL,
>         "candidate" text,
>         "candt" int2,
>         "img" text
> );
> This fails because function next_applicant dumps later !

Yeah, it's a known bug.  We can't just dump the functions first,
though, can we?  I'm not sure how carefully function definitions
get examined by CREATE FUNCTION.

The simplest real solution I've heard so far is to dump database objects
in order by OID rather than doing it strictly by type.

Is anyone working on this, or does anyone want to?  I haven't looked at
pg_dump in a while, but I know some other folks have been hacking it
recently.

			regards, tom lane

  9. Re: [HACKERS] Ordering of pg_dump output

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-02-08T16:38:02Z

    > Yeah, it's a known bug.  We can't just dump the functions first,
> though, can we?  I'm not sure how carefully function definitions
> get examined by CREATE FUNCTION.
> 
> The simplest real solution I've heard so far is to dump database objects
> in order by OID rather than doing it strictly by type.
> 
> Is anyone working on this, or does anyone want to?  I haven't looked at
> pg_dump in a while, but I know some other folks have been hacking it
> recently.

I thought Peter E. was thinking about it.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  10. Re: [HACKERS] Ordering of pg_dump output

    Mark Hollomon <mhh@nortelnetworks.com> — 2000-02-08T19:01:26Z

    Tom Lane wrote:
> 
> The simplest real solution I've heard so far is to dump database objects
> in order by OID rather than doing it strictly by type.
> 
> Is anyone working on this, or does anyone want to?  I haven't looked at
> pg_dump in a while, but I know some other folks have been hacking it
> recently.

I'll take a stab at it, if Peter E. isn't already doing it.

-- 

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

  11. Re: [HACKERS] Ordering of pg_dump output

    Jan Wieck <wieck@debis.com> — 2000-02-08T19:24:21Z

    > The simplest real solution I've heard so far is to dump database objects
> in order by OID rather than doing it strictly by type.
>
> Is anyone working on this, or does anyone want to?  I haven't looked at
> pg_dump in a while, but I know some other folks have been hacking it
> recently.

    Dumping  by  Oid  or building up a framework of dependencies,
    these where the options. Don't forget, SQL language functions
    are  (in  contrast to procedural ones) parsed at CREATE time.
    So any operator, aggregate  or  table  you  use  inside  must
    exist.  And  they  can  be used in turn in many places, so it
    isn't simple at all.

    I think finally pg_dump  must  scan  the  entire  schema  two
    times,  first  to  get  all the Oid's, second to dump all the
    objects.

    AFAIK, nobody is working on it. And starting on it right  now
    seems a little late to make it until BETA.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

  12. Re: [HACKERS] Ordering of pg_dump output

    Don Baccus <dhogaza@pacifier.com> — 2000-02-08T19:26:16Z

    At 02:01 PM 2/8/00 -0500, Mark Hollomon wrote:
>Tom Lane wrote:
>> 
>> The simplest real solution I've heard so far is to dump database objects
>> in order by OID rather than doing it strictly by type.
>> 
>> Is anyone working on this, or does anyone want to?  I haven't looked at
>> pg_dump in a while, but I know some other folks have been hacking it
>> recently.
>
>I'll take a stab at it, if Peter E. isn't already doing it.

You might want to e-mail Jan and/or Steve Szabo, who've been working
on dumping referential integrity stuff.  Because tables can mutally
refer to each other, constraint dumping won't be done until data is
dumped, so the data will be loaded first when someone recreates the
database from the dump.

I was busy over the weekend working the MATCH <unspecified> and the
semantics of referential integrity actions so mostly ignored the
e-mails they traded on the subject - you'll need to get details
from them.

You need to make sure whatever you do doesn't break whatever they've
done or are doing...



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

  13. Re: [HACKERS] Ordering of pg_dump output

    Chris <chrisb@nimrod.itg.telstra.com.au> — 2000-02-08T22:50:03Z

    > Tom Lane wrote:
> >
> > The simplest real solution I've heard so far is to dump database objects
> > in order by OID rather than doing it strictly by type.

Hmm. Now if my OO stuff was working I guess pg_dump could be implemented
as...

List<PGObject*> dblist = pgselect("SELECT ** from object order by oid");
while (dblist.begin(); !dblist.atEnd(); dblist++) {
	dblist.obj().dump();

  14. Re: [HACKERS] Ordering of pg_dump output

    Peter Eisentraut <peter_e@gmx.net> — 2000-02-09T00:09:53Z

    On 2000-02-08, Tom Lane mentioned:

> The simplest real solution I've heard so far is to dump database objects
> in order by OID rather than doing it strictly by type.

AFAIR, it was your idea ... ;)

> 
> Is anyone working on this, or does anyone want to?  I haven't looked at
> pg_dump in a while, but I know some other folks have been hacking it
> recently.

I might have been putting out remarks to that end once in a while, and I'm
still interested in it, but it would be a more extensive project, like the
psql revision, because pg_dump needs a lot of love as it stands. (I think
there are some parts still in it that allow you to dump PostQUEL.)

The problem with a pure oid-based ordering concept is that (as you
yourself pointed out) it won't work if you alter some object in question
after creation. The obvious case would be an alter function (to be
implemented), but another case is (probably) alter column set default (is
implemented).

What I'd like to do first is to draw up some (semi-)formal
(dependency-based) concept on paper and either verify it or come to the
conclusion that it will never work and then give up in disgust. ;) No,
seriously, I suppose I'll bring this up again in a couple of months when
we're ready for it.

Any collaborators are welcome of course.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

  15. Re: [HACKERS] Ordering of pg_dump output

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-09T00:21:08Z

    Peter Eisentraut <peter_e@gmx.net> writes:
> The problem with a pure oid-based ordering concept is that (as you
> yourself pointed out) it won't work if you alter some object in question
> after creation. The obvious case would be an alter function (to be
> implemented), but another case is (probably) alter column set default (is
> implemented).

Right; a genuine dependency analysis would be better.  Also a lot more
painful to implement.

As you say, pg_dump could do with a wholesale rewrite, and maybe that
would be a good time to look at the dependency-based approach.  In the
meantime, I think dumping in OID order would fix 90% of the problem for
10% of the work...

			regards, tom lane