Re: OPAQUE and 7.2-7.3 upgrade

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
> Sent: 11 September 2002 22:13
> To: Dave Page
> Cc: Oliver Elphick; pgsql-hackers(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(dot)Elphick(at)lfix(dot)co(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(dot)Elphick(at)lfix(dot)co(dot)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(at)lfix(dot)co(dot)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 |/

On Thu, 2002-09-12 at 15:54, Tom Lane wrote:
> Oliver Elphick <olly(at)lfix(dot)co(dot)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(dot)Elphick(at)lfix(dot)co(dot)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(dot)18149(dot)15(dot)camel(at)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(at)lfix(dot)co(dot)uk">&lt;olly(at)lfix(dot)co(dot)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(at)rhyme(dot)com(dot)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(at)lfix(dot)co(dot)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

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(at)rhyme(dot)com(dot)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(at)rhyme(dot)com(dot)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(at)rhyme(dot)com(dot)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 |/

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 |/