No title

On Mon, 9 Sep 2002, Laurette Cisneros wrote:

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler() does not return type language_handler

I sounds like there's a language installed on your 7.2.2 server that your
7.3 server doesn't have installed.

On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler() does not return type language_handler

Any ideas?

At the moment, you have to edit the dump. Where the language handler
function is declared, change "RETURNS opaque" to "RETURNS
language_handler".

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Submit yourselves therefore to God. Resist the devil,
and he will flee from you." James 4:7

Thanks!

On 9 Sep 2002, Oliver Elphick wrote:

On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler() does not return type language_handler

Any ideas?

At the moment, you have to edit the dump. Where the language handler
function is declared, change "RETURNS opaque" to "RETURNS
language_handler".

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

Ok, am I missing somethig here?

In 7.3, the -Fp option has been removed which leaves the -Fc (which we use
in our 7.2 dumps) or -Ft.

How does one edit a compressed or tar file?

Also, is this problem going to be fixed in a later beta or regular release
of 7.3? This could pose a problem to restore full database dumps.

Thanks,

L.
On 9 Sep 2002, Oliver Elphick wrote:

On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler() does not return type language_handler

Any ideas?

At the moment, you have to edit the dump. Where the language handler
function is declared, change "RETURNS opaque" to "RETURNS
language_handler".

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

Ok, I made the changes in the compressed pg_dump file. Now pg_restore crashes:

pg_restore: [archiver] out of memory

*sigh*

L.
On 9 Sep 2002, Oliver Elphick wrote:

On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler() does not return type language_handler

Any ideas?

At the moment, you have to edit the dump. Where the language handler
function is declared, change "RETURNS opaque" to "RETURNS
language_handler".

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

At 03:54 PM 9/09/2002 -0700, Laurette Cisneros wrote:

Ok, I made the changes in the compressed pg_dump file.

That's probably a very bad idea.

It's a little more long-winded, but try:

pg_restore -l dumpfile > dump1.lis

copy dump1.lis to dump2.lis

delete everything from dump1.lis at and after the definition that causes
the problem.

delete everything from dump2.lis at and before the definition that causes
the problem.

pg_restore -L dump1.lis

manually define the language

pg_restore -L dump2.lis

ALTERNATIVELY, define the language in template1, then just edit dump1.lis
to remove the line for the language definition, and run pg_restore -L
dump1.lis.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

On Tue, 2002-09-10 at 00:50, Philip Warner wrote:

ALTERNATIVELY, define the language in template1, then just edit dump1.lis
to remove the line for the language definition, and run pg_restore -L
dump1.lis.

That doesn't work for a dump and reload, because 7.3's pg_dumpall writes
a script to create the databases from template0 rather than template1.

The 7.3 documentation for pg_dump says:

Notes

If your installation has any local additions to the template1
database, be careful to restore the output of pg_dump into a truly
empty database; otherwise you are likely to get errors due to
duplicate definitions of the added objects. To make an empty
database without any local additions, copy from template0 not
template1, for example:

CREATE DATABASE foo WITH TEMPLATE = template0;

but this seems to be out of date. pg_dumpall actually uses template0
itself.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

I am confused. This wording seems fine to me.

---------------------------------------------------------------------------

Oliver Elphick wrote:

On Tue, 2002-09-10 at 00:50, Philip Warner wrote:

ALTERNATIVELY, define the language in template1, then just edit dump1.lis
to remove the line for the language definition, and run pg_restore -L
dump1.lis.

That doesn't work for a dump and reload, because 7.3's pg_dumpall writes
a script to create the databases from template0 rather than template1.

The 7.3 documentation for pg_dump says:

Notes

If your installation has any local additions to the template1
database, be careful to restore the output of pg_dump into a truly
empty database; otherwise you are likely to get errors due to
duplicate definitions of the added objects. To make an empty
database without any local additions, copy from template0 not
template1, for example:

CREATE DATABASE foo WITH TEMPLATE = template0;

but this seems to be out of date. pg_dumpall actually uses template0
itself.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

I do this to begin with (createdb -T template0 db).

FYI: Here's what I've determined is the best thing to do:

1. create the database from template0
2. create the needed languages (plpgsql, plperl, plpython) in the database
3. create the needed tables, functions, types, etc. from script files.
4. restore only the data from the dump.

Seems to be the "easiest" and safest way to convert the database(s) to
7.3b1 (we have a mirad of databases for different needs each having their
own set of types, functions and languages that they use). I'll let you
know if I run into problems with this - as this, in my opinion, should not!

Thanks to all for the help,

L.
On Tue, 10 Sep 2002, Bruce Momjian wrote:

I am confused. This wording seems fine to me.

---------------------------------------------------------------------------

Oliver Elphick wrote:

On Tue, 2002-09-10 at 00:50, Philip Warner wrote:

ALTERNATIVELY, define the language in template1, then just edit dump1.lis
to remove the line for the language definition, and run pg_restore -L
dump1.lis.

That doesn't work for a dump and reload, because 7.3's pg_dumpall writes
a script to create the databases from template0 rather than template1.

The 7.3 documentation for pg_dump says:

Notes

If your installation has any local additions to the template1
database, be careful to restore the output of pg_dump into a truly
empty database; otherwise you are likely to get errors due to
duplicate definitions of the added objects. To make an empty
database without any local additions, copy from template0 not
template1, for example:

CREATE DATABASE foo WITH TEMPLATE = template0;

but this seems to be out of date. pg_dumpall actually uses template0
itself.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

On Tue, 2002-09-10 at 18:38, Bruce Momjian wrote:

I am confused. This wording seems fine to me.

The confusion was mine. Of course, pg_dump doesn't create the
database. I was mixing it up with pg_dumpall.

However, there is a problem in that recent changes have made it quite
likely that an upgrade will fail and will requre the dump script to be
edited. There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

Oliver Elphick wrote:

On Tue, 2002-09-10 at 18:38, Bruce Momjian wrote:

I am confused. This wording seems fine to me.

The confusion was mine. Of course, pg_dump doesn't create the
database. I was mixing it up with pg_dumpall.

However, there is a problem in that recent changes have made it quite
likely that an upgrade will fail and will requre the dump script to be
edited. There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

OK, can you specifically list them?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Tue, 2002-09-10 at 23:09, Bruce Momjian wrote:

Oliver Elphick wrote:

edited. There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

OK, can you specifically list them?

Message yesterday to pgsql-hackers

Subject: [HACKERS] pg_dump problems in upgrading
Date: 09 Sep 2002 12:31:39 +0100
Message-Id: <1031571099.24419.199.camel@linda>

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

Oliver Elphick <olly@lfix.co.uk> writes:

However, there is a problem in that recent changes have made it quite
likely that an upgrade will fail and will requre the dump script to be
edited. There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

AFAIK, we did what we could on that front in 7.2.1. If you have ideas
on how we can retroactively make things better, I'm all ears ...

regards, tom lane

On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote:

Oliver Elphick <olly@lfix.co.uk> writes:

However, there is a problem in that recent changes have made it quite
likely that an upgrade will fail and will requre the dump script to be
edited. There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

AFAIK, we did what we could on that front in 7.2.1. If you have ideas
on how we can retroactively make things better, I'm all ears ...

So this release is going to be the royal pain release to upgrade to? Not
good. People may just not upgrade at all in that case.

My datasets aren't complicated enough to trigger some of these problems;
people who have complex datasets need to report all failures so that we can
at least write a sed/perl/awk script to massage the things that need
massaging, if it can be done that easily.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Lamar Owen <lamar.owen@wgcr.org> writes:

On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote:

AFAIK, we did what we could on that front in 7.2.1. If you have ideas
on how we can retroactively make things better, I'm all ears ...

So this release is going to be the royal pain release to upgrade to?

pg_dumpall from a 7.2 db, and reload into 7.2, is broken if you have
mixed-case DB names. AFAIK it's okay if you use a later-than-7.2
pg_dumpall, or reload with a later-than-7.2 psql. If Oliver's got
info to the contrary then he'd better be more specific about what
he thinks should be fixed for 7.3. Griping about the fact that 7.2.0
is broken is spectacularly unproductive at this point.

regards, tom lane

On Wed, 2002-09-11 at 05:20, Tom Lane wrote:

Lamar Owen <lamar.owen@wgcr.org> writes:

On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote:

AFAIK, we did what we could on that front in 7.2.1. If you have ideas
on how we can retroactively make things better, I'm all ears ...

So this release is going to be the royal pain release to upgrade to?

pg_dumpall from a 7.2 db, and reload into 7.2, is broken if you have
mixed-case DB names. AFAIK it's okay if you use a later-than-7.2
pg_dumpall, or reload with a later-than-7.2 psql. If Oliver's got
info to the contrary then he'd better be more specific about what
he thinks should be fixed for 7.3. Griping about the fact that 7.2.0
is broken is spectacularly unproductive at this point.

I ran pg_dumpall from 7.3 on the 7.2 database. So I am talking about
the pg_dump that is now being beta-tested. Because of the major changes
in 7.3, the 7.2 dump is not very useful. I am *not* complaining about
7.2's pg_dump!

Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:

1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.

2. The dump produced:
CREATE TABLE cust_alloc_history (
...
"year" integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column "year" is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression

3. A view was created before one of the tables to which it referred.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me."
Galatians 2:20

-----Original Message-----
From: Oliver Elphick [mailto:olly@lfix.co.uk]
Sent: 11 September 2002 07:29
To: Tom Lane
Cc: Lamar Owen; Bruce Momjian; Philip Warner; Laurette
Cisneros; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS]

Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:

I wonder how many people would do something more like:

pg_dumpall > db.sql
make install
psql -e template1 < db.sql

rather than manually installing pg_dumpall from 7.3 first?

Regards, Dave.

Oliver Elphick <olly@lfix.co.uk> writes:

Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:

1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.

Okay, we need to do something about that, though I'm not sure I see
a clean solution offhand.

2. The dump produced:
CREATE TABLE cust_alloc_history (
...
"year" integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column "year" is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression

Hmm ... what was the original coding of the default?

3. A view was created before one of the tables to which it referred.

This has been a problem all along and will continue to be a problem
for awhile longer. Sorry.

regards, tom lane

On Wed, 2002-09-11 at 14:59, Tom Lane wrote:

Oliver Elphick <olly@lfix.co.uk> writes:

Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:

1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.

Okay, we need to do something about that, though I'm not sure I see
a clean solution offhand.

In 7.2, this will identify the functions that need to be dumped as
language handlers:

junk=# SELECT p.proname
junk-# FROM pg_proc AS p, pg_language AS l
junk-# WHERE l.lanplcallfoid = p.oid AND l.lanplcallfoid != 0;
proname
----------------------
plperl_call_handler
plpgsql_call_handler
pltcl_call_handler
(3 rows)

2. The dump produced:
CREATE TABLE cust_alloc_history (
...
"year" integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column "year" is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression

Hmm ... what was the original coding of the default?

year INTEGER DEFAULT date_part('year',CURRENT_TIMESTAMP)

3. A view was created before one of the tables to which it referred.

This has been a problem all along and will continue to be a problem
for awhile longer. Sorry.

Is it not enough to defer all views until the end? Why would they be
needed any sooner?

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me."
Galatians 2:20

On Wed, 2002-09-11 at 08:20, Dave Page wrote:

-----Original Message-----
From: Oliver Elphick [mailto:olly@lfix.co.uk]
Sent: 11 September 2002 07:29
To: Tom Lane
Cc: Lamar Owen; Bruce Momjian; Philip Warner; Laurette
Cisneros; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS]

Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:

I wonder how many people would do something more like:

pg_dumpall > db.sql
make install
psql -e template1 < db.sql

rather than manually installing pg_dumpall from 7.3 first?

I suppose that what people will do unless told otherwise, but the
introduction of schemas means that it is much better to use 7.3's dump,
otherwise, for example, all functions will be private rather than
public.

Perhaps a note should be added to INSTALL. At the moment it says:

2. To dump your database installation, type:

pg_dumpall > outputfile

...

Make sure that you use the "pg_dumpall" command from the version
you are currently running. 7.2's "pg_dumpall" should not be used
on older databases.

But now we should be telling people to use 7.3's pg_dumpall, at least
for 7.2 data. (How far back can it go?)

Make sure you use pg_dumpall from the new 7.3 software to dump
your data from 7.2. To do this, you must have the 7.2
postmaster running and run the 7.3 pg_dumpall by using its full
pathname. 7.2's pg_dumpall is unsuitable because of the
introduction of schemas in 7.3 which make it necessary to grant
public access to features that will, if created from a 7.2 dump,
be given access by their owner only.

(Have I got that right?)

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me."
Galatians 2:20

Oliver Elphick wrote:

But now we should be telling people to use 7.3's pg_dumpall, at least
for 7.2 data. (How far back can it go?)

Make sure you use pg_dumpall from the new 7.3 software to dump
your data from 7.2. To do this, you must have the 7.2
postmaster running and run the 7.3 pg_dumpall by using its full
pathname. 7.2's pg_dumpall is unsuitable because of the
introduction of schemas in 7.3 which make it necessary to grant
public access to features that will, if created from a 7.2 dump,
be given access by their owner only.

That's a pretty big hurtle. I think we are better off giving them an
SQL UPDATE to run.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Is it not enough to defer all views until the end? Why would they be
needed any sooner?

I would think that views of views, or permissions on views, or prepared
statements might need the right view to be declared first. There may be other
examples as well.

Your solution might be better than the current situation, however.

Regards,
Jeff

yes, deferring views to the end will also break if you have
SQL functions defined that use views. The dependencies
is (are?) a really hard problem.

elein

At 12:41 PM 9/11/02, Jeff Davis wrote:

Is it not enough to defer all views until the end? Why would they be
needed any sooner?

I would think that views of views, or permissions on views, or prepared
statements might need the right view to be declared first. There may be other
examples as well.

Your solution might be better than the current situation, however.

Regards,
Jeff

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
elein@norcov.com (510)543-6079
"Taking a Trip. Not taking a Trip." --anonymous
:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:

Oliver Elphick <olly@lfix.co.uk> writes:

ERROR: Column "year" is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression

Hmm ... what was the original coding of the default?

year INTEGER DEFAULT date_part('year',CURRENT_TIMESTAMP)

Well, date_part has always yielded double, so what we are really looking
at here is a side-effect of the tightening of implicit casting in 7.3.
It wants you to cast down to integer explicitly.

There was some discussion of allowing "implicit explicit casting" of
INSERT and UPDATE values to the target column's datatype, ie, allow a
cast path to be used even if it is not marked as implicitly castable.
If we did that then it's be reasonable to do it for default values as
well, and that would allow this coding to keep working. But we did not
have a consensus to do it AFAIR.

3. A view was created before one of the tables to which it referred.

This has been a problem all along and will continue to be a problem
for awhile longer. Sorry.

Is it not enough to defer all views until the end? Why would they be
needed any sooner?

Well, one counterexample is where the view is being used as a substitute
for a standalone composite type: there might be a function somewhere
that uses the view's rowtype as an input or result datatype. I recall
seeing exactly that coding technique in some of Joe Conway's contrib
stuff (though it's now been superseded by use of standalone types).
In any case, such a rule won't ensure getting cross-references between
views to work.

The only real solution to pg_dump's ordering woes is to examine the
database dependency graph and do a topological sort to determine a
safe dump order. As of 7.3 we have the raw materials to do this (in
the form of the pg_depend system table), but making pg_dump actually
do it is a major rewrite that didn't get done, and IMHO shouldn't be
tackled during beta. (I sure want to see it for 7.4 though.)

In the meantime, I think that we shouldn't mess with pg_dump's basically
OID-order-driven dump ordering. It works in normal cases, and adding
arbitrary rules to it to fix one corner case is likely to accomplish
little except breaking other corner cases.

regards, tom lane

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 11 September 2002 18:21
To: Oliver Elphick
Cc: Dave Page; Tom Lane; Lamar Owen; Philip Warner; Laurette
Cisneros; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS]

Oliver Elphick wrote:

But now we should be telling people to use 7.3's

pg_dumpall, at least

for 7.2 data. (How far back can it go?)

Make sure you use pg_dumpall from the new 7.3

software to dump

your data from 7.2. To do this, you must have the 7.2
postmaster running and run the 7.3 pg_dumpall by

using its full

pathname. 7.2's pg_dumpall is unsuitable because of the
introduction of schemas in 7.3 which make it

necessary to grant

public access to features that will, if created

from a 7.2 dump,

be given access by their owner only.

That's a pretty big hurtle. I think we are better off giving
them an SQL UPDATE to run.

How would that massage a dump file though? I can't think of any SQL that
might make 7.2 output 'language_handler' correctly, and we already know
7.3 will barf on opaque.

Regards, Dave.

Dave Page wrote:

That's a pretty big hurtle. I think we are better off giving
them an SQL UPDATE to run.

How would that massage a dump file though? I can't think of any SQL that
might make 7.2 output 'language_handler' correctly, and we already know
7.3 will barf on opaque.

Oh, I thought it was just the permissions that were the problem. Can we
give them a sed script?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 11 September 2002 22:13
To: Dave Page
Cc: Oliver Elphick; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS]

Dave Page wrote:

That's a pretty big hurtle. I think we are better off giving
them an SQL UPDATE to run.

How would that massage a dump file though? I can't think of any SQL
that might make 7.2 output 'language_handler' correctly, and we
already know 7.3 will barf on opaque.

Oh, I thought it was just the permissions that were the
problem. Can we give them a sed script?

I guess so. It seems to me that upgrading to 7.3 is going to be the
stuff of nightmares, so my first thought is to try to avoid getting
people to run a 7.3 utility on their 7.x database. It would be nice to
see such a script run on old version dump files - but what else will
break? Oliver has found a couple of things, and I wouldn't be surprised
if my main installation falls over as well. If I get a chance I'll try
it tomorrow.

Regards, Dave.

Dave Page wrote:

Oh, I thought it was just the permissions that were the
problem. Can we give them a sed script?

I guess so. It seems to me that upgrading to 7.3 is going to be the
stuff of nightmares, so my first thought is to try to avoid getting
people to run a 7.3 utility on their 7.x database. It would be nice to
see such a script run on old version dump files - but what else will
break? Oliver has found a couple of things, and I wouldn't be surprised
if my main installation falls over as well. If I get a chance I'll try
it tomorrow.

Why can't we do the remapping in the SQL grammar and remove the
remapping in 7.4?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 11 September 2002 22:28
To: Dave Page
Cc: Oliver Elphick; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS]

Why can't we do the remapping in the SQL grammar and remove
the remapping in 7.4?

I can see that working for the opaque/language_handler thing, but
would/should it work for tweaking casts that are no longer implicit?

Regards, Dave.

On Wed, 2002-09-11 at 21:19, Tom Lane wrote:

In the meantime, I think that we shouldn't mess with pg_dump's basically
OID-order-driven dump ordering. It works in normal cases, and adding
arbitrary rules to it to fix one corner case is likely to accomplish
little except breaking other corner cases.

I can see that Lamar and I are going to have major problems dealing with
users who fall over these problems. There are some things that simply
cannot be handled automatically, such as user-written functions that
return opaque. Then there are issues of ordering; and finally the fact
that we need to use the new pg_dump with the old binaries to get a
useful dump.

It seems to me that I shall have to make the new package such that it
can exist alongside the old one for a time, or else possibly separate
7.3 pg_dump and pg_dumpall into a separate package. It is going to be a
total pain!

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me."
Galatians 2:20

On Wed, 2002-09-11 at 22:27, Bruce Momjian wrote:

Dave Page wrote:

Oh, I thought it was just the permissions that were the
problem. Can we give them a sed script?

I guess so. It seems to me that upgrading to 7.3 is going to be the
stuff of nightmares, so my first thought is to try to avoid getting
people to run a 7.3 utility on their 7.x database. It would be nice to
see such a script run on old version dump files - but what else will
break? Oliver has found a couple of things, and I wouldn't be surprised
if my main installation falls over as well. If I get a chance I'll try
it tomorrow.

Why can't we do the remapping in the SQL grammar and remove the
remapping in 7.4?

Surely you will have to leave the remapping in for the benefit of anyone
who jumps from <= 7.2 to >= 7.4

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me."
Galatians 2:20

Oliver Elphick wrote:

On Wed, 2002-09-11 at 22:27, Bruce Momjian wrote:

Dave Page wrote:

Oh, I thought it was just the permissions that were the
problem. Can we give them a sed script?

I guess so. It seems to me that upgrading to 7.3 is going to be the
stuff of nightmares, so my first thought is to try to avoid getting
people to run a 7.3 utility on their 7.x database. It would be nice to
see such a script run on old version dump files - but what else will
break? Oliver has found a couple of things, and I wouldn't be surprised
if my main installation falls over as well. If I get a chance I'll try
it tomorrow.

Why can't we do the remapping in the SQL grammar and remove the
remapping in 7.4?

Surely you will have to leave the remapping in for the benefit of anyone
who jumps from <= 7.2 to >= 7.4

Well, our whole goal was to get rid of the opaque thing entirely so I am
not sure if we want to keep that going. In fact, I am not sure it is
even possible to remap opaque because it now is represented by so many
other values.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Dave Page wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 11 September 2002 22:28
To: Dave Page
Cc: Oliver Elphick; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS]

Why can't we do the remapping in the SQL grammar and remove
the remapping in 7.4?

I can see that working for the opaque/language_handler thing, but
would/should it work for tweaking casts that are no longer implicit?

OK, I am going to add these items to the open items list because I am
having trouble keeping track of all the compatibility changes for
pg_dump.

I have:

Loading 7.2 pg_dumps
opaque language handler no longer recognized

What else is there?

Do cast problems related to pg_dump loading or to working with the data
after the load? Is it casts in user functions?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Wednesday 11 September 2002 05:40 pm, Oliver Elphick wrote:

On Wed, 2002-09-11 at 21:19, Tom Lane wrote:

In the meantime, I think that we shouldn't mess with pg_dump's basically
OID-order-driven dump ordering. It works in normal cases, and adding
arbitrary rules to it to fix one corner case is likely to accomplish
little except breaking other corner cases.

I can see that Lamar and I are going to have major problems dealing with
users who fall over these problems.

Yes, we are. Thankfully, with RPM dependencies I can prevent blind upgrades.
But that doe not help the data migration issue this release is going to be.
Guys, migration that is this shabby is, well, shabby. This _has_ to be fixed
where a dump of 7.2.2 data (not 7.2.0, Tom) can be cleanly restored into 7.3.
That is, after all, our only migration path.

I think this upgrade/migration nightmare scenario warrants upping the version
to 8.0 to draw attention to the potential problems.

It seems to me that I shall have to make the new package such that it
can exist alongside the old one for a time, or else possibly separate
7.3 pg_dump and pg_dumpall into a separate package. It is going to be a
total pain!

I had planned on making just such a 'pg_dump' package -- but if the 7.3
pg_dump isn't going to produce useful output, it seems like a waste of time.

However, the jury is still out -- what sort of percentages are involved? That
is, how likely are problems going to happen?

Bruce, I mentioned a sed/perl/awk script already to massage the dump into a
7.3-friendly form -- but we need to gather the cases that are involved.
Methinks every single OpenACS installation will hit this issue.

How big is the problem? It's looking bigger with each passing day, ISTM.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Lamar Owen wrote:

Bruce, I mentioned a sed/perl/awk script already to massage the dump into a
7.3-friendly form -- but we need to gather the cases that are involved.
Methinks every single OpenACS installation will hit this issue.

How big is the problem? It's looking bigger with each passing day, ISTM.

That is exactly what I want to know and document on the open items page.
I am having trouble understanding some of the failures because no one is
showing the failure messages/statements, just describing them.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Wednesday 11 September 2002 09:44 pm, Bruce Momjian wrote:

Lamar Owen wrote:

Bruce, I mentioned a sed/perl/awk script already to massage the dump into
a 7.3-friendly form -- but we need to gather the cases that are involved.
Methinks every single OpenACS installation will hit this issue.

How big is the problem? It's looking bigger with each passing day, ISTM.

That is exactly what I want to know and document on the open items page.
I am having trouble understanding some of the failures because no one is
showing the failure messages/statements, just describing them.

Well, I am going to _try_ to lay aside an hour or two tomorrow or Friday and
try to import a 7.2.2 OpenACS dump into a 7.3 installation. I'll try to get
very verbose with the errors... :-)
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 12 September 2002 00:53
To: Dave Page
Cc: Oliver Elphick; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS]

OK, I am going to add these items to the open items list
because I am having trouble keeping track of all the
compatibility changes for pg_dump.

I have:

Loading 7.2 pg_dumps
opaque language handler no longer recognized

What else is there?

Do cast problems related to pg_dump loading or to working
with the data after the load? Is it casts in user functions?

Oliver reported:

2. The dump produced:
CREATE TABLE cust_alloc_history (
...
"year" integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column "year" is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression

For an original definition of:

year INTEGER DEFAULT
date_part('year',CURRENT_TIMESTAMP)

Regards, Dave.

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Well, our whole goal was to get rid of the opaque thing entirely so I am
not sure if we want to keep that going. In fact, I am not sure it is
even possible to remap opaque because it now is represented by so many
other values.

We do still allow OPAQUE for triggers and datatype I/O functions, though
I would like to take that out by and by.

The only case where OPAQUE is rejected now but was allowed before is PL
language handlers. We could weaken that --- but since there are no
user-defined PL handlers in the wild (AFAIK anyway), I'd prefer not to.

My original thought about this was that people should run 7.3's
createlang script to load proper 7.3 language definitions into their 7.3
database. (This would not only fix the OPAQUE business but also replace
any remaining absolute paths for language handlers with the $libdir
form, which is an important 7.2 change that doesn't seem to have
propagated very well because people are just doing dumps and reloads.)

But I now see that this answer doesn't work for pg_dumpall scripts.

Does anyone see a cleaner answer than re-allowing OPAQUE for PL
handlers?

regards, tom lane

On Thu, 2002-09-12 at 15:31, Tom Lane wrote:

Does anyone see a cleaner answer than re-allowing OPAQUE for PL
handlers?

Can't you just special case the language handlers when dumping <7.3 and
change 'RETURNS opaque' to 'RETURNS language_handler'? That's all that
is needed to let them be restored OK into 7.3.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Let the wicked forsake his way, and the unrighteous
man his thoughts; and let him return unto the LORD,
and He will have mercy upon him; and to our God, for
he will abundantly pardon." Isaiah 55:7

Oliver Elphick <olly@lfix.co.uk> writes:

On Thu, 2002-09-12 at 15:31, Tom Lane wrote:

Does anyone see a cleaner answer than re-allowing OPAQUE for PL
handlers?

Can't you just special case the language handlers when dumping <7.3 and
change 'RETURNS opaque' to 'RETURNS language_handler'? That's all that
is needed to let them be restored OK into 7.3.

Only if people dump their old databases with 7.3 pg_dump; which is an
assumption I'd rather not make if we can avoid it.

OTOH, if we did do such a thing we could probably fix OPAQUE triggers
and datatype I/O ops too ...

regards, tom lane

At 10:31 AM 12/09/2002 -0400, Tom Lane wrote:

Does anyone see a cleaner answer than re-allowing OPAQUE for PL
handlers?

What about extending the function manager macros to know about return types
(at least for builtin types)?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Dave Page wrote:

Oliver reported:

2. The dump produced:
CREATE TABLE cust_alloc_history (
...
"year" integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column "year" is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression

For an original definition of:

year INTEGER DEFAULT
date_part('year',CURRENT_TIMESTAMP)

Wow. That is clear. Why are we returning "year" as a double? Yes, I
see now:

test=> \df date_part
List of functions
Result data type | Schema | Name | Argument data types
------------------+------------+-----------+-----------------------------------
double precision | pg_catalog | date_part | text, abstime
double precision | pg_catalog | date_part | text, date
double precision | pg_catalog | date_part | text, interval
double precision | pg_catalog | date_part | text, reltime
double precision | pg_catalog | date_part | text, time with time zone
double precision | pg_catalog | date_part | text, time without time zone
double precision | pg_catalog | date_part | text, timestamp with time zone
double precision | pg_catalog | date_part | text, timestamp without time zone

I would love to say that this is related to change in casts, but that
isn't the case. It is the new double-precision handling of dates; and
I see no easy way to fix this, and you can't fix this after the data
load because the table wasn't created. Yuck.

I have to ask, why are we using a double here rather than a 64-bit
value, if available?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Thu, 2002-09-12 at 15:54, Tom Lane wrote:

Oliver Elphick <olly@lfix.co.uk> writes:

On Thu, 2002-09-12 at 15:31, Tom Lane wrote:

Does anyone see a cleaner answer than re-allowing OPAQUE for PL
handlers?

Can't you just special case the language handlers when dumping <7.3 and
change 'RETURNS opaque' to 'RETURNS language_handler'? That's all that
is needed to let them be restored OK into 7.3.

Only if people dump their old databases with 7.3 pg_dump; which is an
assumption I'd rather not make if we can avoid it.

I don't understand.

The only pg_dump we can fix is 7.3. You can't backport such a change
into 7.2 or it won't work for 7.2 restore. If you are using 7.3 pg_dump
it isn't an assumption but a certainty that it is being used.

If someone restores into 7.3 with a 7.2 dump they are going to have
other problems, such as turning all their functions private. Since they
are going to need to edit the dump anyway, they might as well edit this
bit too. Surely we should be advising them to use 7.3's pg_dump to do
the upgrade.

The alternative approach is to build a set of kludges into >=7.3 to
change opague to language_handler when a language function is
installed. That doesn't sound like a good idea.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Let the wicked forsake his way, and the unrighteous
man his thoughts; and let him return unto the LORD,
and He will have mercy upon him; and to our God, for
he will abundantly pardon." Isaiah 55:7

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body>
Oliver Elphick wrote:<br>
<blockquote type="cite" cite="mid1031850517.18149.15.camel@linda">
<pre wrap="">On Thu, 2002-09-12 at 15:54, Tom Lane wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Oliver Elphick <a class="moz-txt-link-rfc2396E" href="mailto:olly@lfix.co.uk">&lt;olly@lfix.co.uk&gt;</a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">On Thu, 2002-09-12 at 15:31, Tom Lane wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Does anyone see a cleaner answer than re-allowing OPAQUE for PL
handlers?
</pre>
</blockquote>
</blockquote>
<blockquote type="cite">
<pre wrap="">Can't you just special case the language handlers when dumping &lt;7.3 and
change 'RETURNS opaque' to 'RETURNS language_handler'? That's all that
is needed to let them be restored OK into 7.3.
</pre>
</blockquote>
<pre wrap="">Only if people dump their old databases with 7.3 pg_dump; which is an
assumption I'd rather not make if we can avoid it.
</pre>
</blockquote>
<pre wrap=""><!---->
I don't understand.

The only pg_dump we can fix is 7.3. You can't backport such a change
into 7.2 or it won't work for 7.2 restore. If you are using 7.3 pg_dump
it isn't an assumption but a certainty that it is being used.

If someone restores into 7.3 with a 7.2 dump they are going to have
other problems, such as turning all their functions private. Since they
are going to need to edit the dump anyway, they might as well edit this
bit too. Surely we should be advising them to use 7.3's pg_dump to do
the upgrade.

The alternative approach is to build a set of kludges into &gt;=7.3 to
change opague to language_handler when a language function is
installed. That doesn't sound like a good idea.

</pre>
</blockquote>
Is it possible to build a standalone 7.3 dump/dump_all program that can be
run on a server with an existing 7.2.x installation and not be linked against
7.3 libraries? &nbsp; Call it a migration agent if you will.<br>
<br>
A notice of somekind would help: &nbsp; Before upgrading, dump the database using
this program.<br>
<br>
</body>
</html>

Philip Warner <pjw@rhyme.com.au> writes:

At 10:31 AM 12/09/2002 -0400, Tom Lane wrote:

Does anyone see a cleaner answer than re-allowing OPAQUE for PL
handlers?

What about extending the function manager macros to know about return types
(at least for builtin types)?

Er ... what has that got to do with this? And what sort of extension
do you think we need? We already have the RETURN_foo() macros.

regards, tom lane

Oliver Elphick <olly@lfix.co.uk> writes:

On Thu, 2002-09-12 at 15:54, Tom Lane wrote:

Only if people dump their old databases with 7.3 pg_dump; which is an
assumption I'd rather not make if we can avoid it.

I don't understand.

The only pg_dump we can fix is 7.3.

Certainly. But if we hack the backend so it still accepts OPAQUE, then
we can still load 7.2 dump files.

If someone restores into 7.3 with a 7.2 dump they are going to have
other problems, such as turning all their functions private.

True, but they can fix that after-the-fact. Not sure if there is any
good workaround for the PL-handler problem in a 7.2 pg_dumpall script.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I would love to say that this is related to change in casts, but that
isn't the case. It is the new double-precision handling of dates;

You've got that exactly backwards: date_part has always returned double.

regards, tom lane

Bruce Momjian writes:

Wow. That is clear. Why are we returning "year" as a double?

Because we've been doing that for many years.

I would love to say that this is related to change in casts, but that
isn't the case.

Sure it is. The float=>int casts need to be made implicit, or we'll have
tons of problems like this.

--
Peter Eisentraut peter_e@gmx.net

At 01:37 PM 12/09/2002 -0400, Tom Lane wrote:

What about extending the function manager macros to know about return

types

(at least for builtin types)?

Er ... what has that got to do with this?

When a user issues a 'CREATE FUNCTION' call, the fmgr can check the return
type, and create it with the correct return type (with warning). We just
need to make sure that the language handlers are listed as returning the
correct type.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner <pjw@rhyme.com.au> writes:

At 01:37 PM 12/09/2002 -0400, Tom Lane wrote:

Er ... what has that got to do with this?

When a user issues a 'CREATE FUNCTION' call, the fmgr can check the return
type, and create it with the correct return type (with warning). We just
need to make sure that the language handlers are listed as returning the
correct type.

You mean hardwire the names "plpgsql_language_handler", etc, as being
ones that should return such-and-such instead of OPAQUE?

I suppose that's a possible approach, but it strikes me as mighty
ugly.

If we were going to do such a thing, I'd also want to see it force
the shlib path to "$libdir". Does that strike you as impossibly
crocky, or a reasonable workaround for our past sins?

regards, tom lane

At 11:27 PM 12/09/2002 -0400, Tom Lane wrote:

You mean hardwire the names "plpgsql_language_handler", etc, as being
ones that should return such-and-such instead of OPAQUE?

No; I actually mean modifying the function definition macros
(PG_FUNCTION_INFO etc) to allow function definitions to (optionally)
include return type (at least for builtin types with fixed IDs) - they
already define the invocation method etc, so it does not seem a big stretch
to add a return type ID.

Not all functions would need to use these, but when a user defines a
function they could be checked. And in the case of the plpgsql handlers,
they would of course be defined.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

At 01:42 PM 13/09/2002 +1000, Philip Warner wrote:

Not all functions would need to use these, but when a user defines a
function they could be checked. And in the case of the plpgsql handlers,
they would of course be defined.

ISTM that this problem comes about because we allow an external function to
be defined incorrectly (ie. the db says it returns type A, the function
really returns type B) - and we should be addressing that problem.

As I said in an earlier post, it might be good in the future to apply this
to function args as well.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner <pjw@rhyme.com.au> writes:

At 11:27 PM 12/09/2002 -0400, Tom Lane wrote:

You mean hardwire the names "plpgsql_language_handler", etc, as being
ones that should return such-and-such instead of OPAQUE?

No; I actually mean modifying the function definition macros
(PG_FUNCTION_INFO etc) to allow function definitions to (optionally)
include return type (at least for builtin types with fixed IDs) - they
already define the invocation method etc, so it does not seem a big stretch
to add a return type ID.

That cannot work for user-defined functions, wherein the datatype OID is
not frozen at the time the code is compiled. In any case, it surely
does not help for our current problem, which is forward-compatibility
of dumps from 7.2 databases...

regards, tom lane

Philip Warner <pjw@rhyme.com.au> writes:

ISTM that this problem comes about because we allow an external function to
be defined incorrectly (ie. the db says it returns type A, the function
really returns type B) - and we should be addressing that problem.

Well, yeah. 7.3 is trying to tighten up on exactly that point. And our
current problem arises precisely because dumps from older database
versions will fail to meet the tighter rules. How can we accommodate
those old dumps without abandoning the attempt to be tighter about
datatypes?

regards, tom lane

At 12:11 AM 13/09/2002 -0400, Tom Lane wrote:

How can we accommodate
those old dumps without abandoning the attempt to be tighter about
datatypes?

Maybe I'm missing something, but:

1. Dump from 7.2 has 'Create Function....OPAQUE'

2. 7.3 installation has plpgsql library with new function info macro that
defines the builtin return type correctly

3. Script runs 'Create Function....OPAQUE'; the backend enquires about the
function in the 'plpgsql.so' library, notes that it really returns
'language_handler', issues a NOTICE and modifies the definition
appropriately before adding it to the database.

I'm not sure it's all that valuable, but if we wanted to allow for function
to return user-defined types, then the function manager macros would have
to include a return type name, not number.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Peter Eisentraut <peter_e@gmx.net> writes:

Bruce Momjian writes:

I would love to say that this is related to change in casts, but that
isn't the case.

Sure it is. The float=>int casts need to be made implicit, or we'll have
tons of problems like this.

Well, yeah. That did not seem to bother anyone last spring, when we
were discussing tightening the implicit-casting rules. Shall we
abandon all that work and go back to "any available cast can be applied
implicitly"?

My vote is "tough, time to fix your SQL code".

regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:

Bruce Momjian writes:

I would love to say that this is related to change in casts, but that
isn't the case.

Sure it is. The float=>int casts need to be made implicit, or

we'll have

tons of problems like this.

Well, yeah. That did not seem to bother anyone last spring, when we
were discussing tightening the implicit-casting rules. Shall we
abandon all that work and go back to "any available cast can be applied
implicitly"?

My vote is "tough, time to fix your SQL code".

Wasn't the resolution back then to "wait until beta and see who complains"?

Chris

En Fri, 13 Sep 2002 00:46:00 -0400
Tom Lane <tgl@sss.pgh.pa.us> escribi�:

Peter Eisentraut <peter_e@gmx.net> writes:

Sure it is. The float=>int casts need to be made implicit, or we'll have
tons of problems like this.

Well, yeah. That did not seem to bother anyone last spring, when we
were discussing tightening the implicit-casting rules. Shall we
abandon all that work and go back to "any available cast can be applied
implicitly"?

Implicit float to int loses precision, so it shouldn't be implicit,
should it?

Maybe the solution is to make 7.3 pg_dump smart enough to add explicit
casts where default values demand them... Is this possible? Are there
other cases where tightening implicit casts is going to bit users?

--
Alvaro Herrera (<alvherre[a]atentus.com>)
El sentido de las cosas no viene de las cosas, sino de
las inteligencias que las aplican a sus problemas diarios
en busca del progreso. (Ernesto Hern�ndez-Novich)

My vote is "tough, time to fix your SQL code".

Sounds good to me, but please document it in the "migration" notes. No need
for a surprise.

Regards,
Jeff

Sure it is. The float=>int casts need to be made implicit, or we'll have
tons of problems like this.

Well, yeah. That did not seem to bother anyone last spring, when we
were discussing tightening the implicit-casting rules. Shall we
abandon all that work and go back to "any available cast can
be applied implicitly"?

My vote is "tough, time to fix your SQL code".

I personally don't think that is good. SQL users are used to using implicit casts.
Other db's do handle them whereever possible. It is imho no answer to drop so many
implicit casts only because of the corner cases where it does not work.

What we imho really need is a runtime check that checks whether an implicit cast
caused a loss of precision and abort in that case only. That is what other db's do.

I thought that I voiced my opinion strong enough on this before, but I'll do it again,
I think we should allow a lot more implicit casts than are now in beta.
Especially in the numeric area.

I don't have any strong arguments (other than other db's can do it), but this is
my opinion.

Andreas

Tom Lane writes:

Shall we abandon all that work and go back to "any available cast can be
applied implicitly"?

My vote is "tough, time to fix your SQL code".

That would be a OK if the current behavior conformed to the SQL standard,
which it doesn't. The standard says that all numerical types are mutually
assignable, which in my mind translates directly as implicitly castable.
Additionally, your stance breaks the following SQL compatible and probably
quite common code:

create table test ( a int extract(year from current_date) );

We aren't abandoning "all that work". Plenty of casts should not be
implicit because they are structurally guaranteed to lose information. But
for casts between numerical types it depends on the content at run time.
Therefore the SQL standard says that the check needs to be at run time.
We do that already, so I don't see a reason to be more strict here.

--
Peter Eisentraut peter_e@gmx.net

At 12:11 AM 13/09/2002 -0400, Tom Lane wrote:

Well, yeah. 7.3 is trying to tighten up on exactly that point.

The problem is that as implemented you have only half of the solution; you
also need a way for postgresql to determine the 'real' arguments and return
type of a function. If the building blocks for pseudo-RTTI can be put in
place, then I think that would be a great step forward, *and* solve the
current problem.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane writes:

Shall we abandon all that work and go back to "any available cast can be
applied implicitly"?

My vote is "tough, time to fix your SQL code".

That would be a OK if the current behavior conformed to the SQL standard,
which it doesn't. The standard says that all numerical types are mutually
assignable, which in my mind translates directly as implicitly castable.

If we take that stance then we will never make any progress at all on
fixing our problems with poor choices of numeric operators and inability
to choose an appropriate operator. We can *not* adopt the attitude that
all numeric casts are equal; some have got to be more equal than others,
or the parser will be unable to choose desirable interpretations over
undesirable ones.

As an example, current code does the right thing with
select * from foo where numeric_col = 10.1
whereas 7.2 failed with
ERROR: Unable to identify an operator '=' for types 'numeric' and 'double precision'
This improvement comes precisely because the numeric->float8 cast
pathway is not treated on an even footing with the other direction.

Additionally, your stance breaks the following SQL compatible and probably
quite common code:

create table test ( a int extract(year from current_date) );

I previously suggested that it might be okay to allow non-implicit casts
to be used when assigning a value to a target column in INSERT and
UPDATE (including the case where the value is a default value). If we
do that, then the above will work, and we haven't abandoned all hope of
choosing sensible cast pathways within expressions.

Alternatively we could think about a three-level scheme where pg_cast
can declare different "strengths" of implicit castability for a cast
pathway; then it'd be possible to allow or disallow implicit coercion
to a target column type on a cast-by-cast basis. Dunno if we need that
much complexity here...

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I would love to say that this is related to change in casts, but that
isn't the case. It is the new double-precision handling of dates;

You've got that exactly backwards: date_part has always returned double.

Well, at least I was _exact_ about something. :-)

(I am back from the retreat. I actually was back Saturday afternoon but
my connection to my ISP was down until today.)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Can someone remind me why date_part() returns a double rather than an
int4? It is just for partial seconds?

---------------------------------------------------------------------------

Peter Eisentraut wrote:

Tom Lane writes:

Shall we abandon all that work and go back to "any available cast can be
applied implicitly"?

My vote is "tough, time to fix your SQL code".

That would be a OK if the current behavior conformed to the SQL standard,
which it doesn't. The standard says that all numerical types are mutually
assignable, which in my mind translates directly as implicitly castable.
Additionally, your stance breaks the following SQL compatible and probably
quite common code:

create table test ( a int extract(year from current_date) );

We aren't abandoning "all that work". Plenty of casts should not be
implicit because they are structurally guaranteed to lose information. But
for casts between numerical types it depends on the content at run time.
Therefore the SQL standard says that the check needs to be at run time.
We do that already, so I don't see a reason to be more strict here.

--
Peter Eisentraut peter_e@gmx.net

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073