Thread:
Hello, Mr. Tom Lane I am a chinese student studied in Harbin institute of technology. I want join to PostgreSQL Global DevelopmentGroup and I want work on the planner/optimizer. I have been reading the source code for 2 months. There many datastrucutres I can't understand. Can you tell me what document I must read first. If you have documents about planner/optimizerof PostgreSQL, send me please. a doctor student English name is Mohan. Chinese name is Guo long jiang. Thank you very much! 04/09/2002 ______________________________________ =================================================================== �������ѵ������� (http://mail.sina.com.cn) ���˷�����Ϣ�������г���һ�ߣ��ó���ʱ�ͳ��֣� (http://classad.sina.com.cn/2shou/) �������ֻ�ͼƬ������������������ѡ��ÿ�춼�и��� (http://sms.sina.com.cn/cgi-bin/sms/smspic.cgi)
I assume you have read everything on the developers web page: http://developer.postgresql.org/index.php --------------------------------------------------------------------------- ljguo_1234 wrote: > Hello, Mr. Tom Lane > I am a chinese student studied in Harbin institute of technology. I want join to PostgreSQL Global Development Groupand I want work on the planner/optimizer. I have been reading the source code for 2 months. There many data strucutresI can't understand. Can you tell me what document I must read first. If you have documents about planner/optimizerof PostgreSQL, send me please. > a doctor student English name is Mohan. Chinese name is Guo long jiang. > Thank you very much! 04/09/2002 > ______________________________________ > > =================================================================== > �������ѵ������� (http://mail.sina.com.cn) > ���˷�����Ϣ�������г���һ�ߣ��ó���ʱ�ͳ��֣� (http://classad.sina.com.cn/2shou/) > �������ֻ�ͼƬ������������������ѡ��ÿ�춼�и��� (http://sms.sina.com.cn/cgi-bin/sms/smspic.cgi) > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- 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, Pennsylvania19073
> -----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.
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
surethat you use the "pg_dumpall" command from the version you are currently running. 7.2's "pg_dumpall" should
notbe 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
havethe 7.2 postmaster running and run the 7.3 pg_dumpall by using its full pathname. 7.2's pg_dumpall is
unsuitablebecause of the introduction of schemas in 7.3 which make it necessary to grant public access to
featuresthat 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
livethin me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me,
andgave 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, Pennsylvania19073
> -----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, Pennsylvania19073
> -----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, Pennsylvania19073
> -----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 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 livethin me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, andgave 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, Pennsylvania19073
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, Pennsylvania19073
> -----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; andlet 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 |
Argumentdata 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
withouttime 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,
Pennsylvania19073
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; andlet 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 wrote:<br /><blockquote cite="mid1031850517.18149.15.camel@linda" type="cite"><pre wrap="">On Thu, 2002-09-12at 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"><olly@lfix.co.uk></a> writes: </pre><blockquote type="cite"><prewrap="">On Thu, 2002-09-12 at 15:31, Tom Lane wrote: </pre><blockquote type="cite"><pre wrap="">Doesanyone 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 languagehandlers 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. </pre></blockquote><pre wrap="">Only if people dump their old databaseswith 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 >=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 existing7.2.x installation and not be linked against 7.3 libraries? Call it a migration agent if you will.<br /><br />A notice of somekind would help: Before upgrading, dump the database using this program.<br /><br />
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
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
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 withselect * from foo where numeric_col = 10.1
whereas 7.2 failed withERROR: 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, Pennsylvania19073
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, Pennsylvania19073
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 |/