Обсуждение: Compatibility of future releases
Hi all,
I would like to know if between Pg 7.2.3 and next releases there are (or
will be) differences as SQL or system tables structures. I explain better:
I use table inheritance so when I I made porting of tables from Pg 7.0.3 to
7.1.3 I had to add the SQL keyword ONLY in SELECT statements; when I made
porting of my databases from Pg 7.1.3 to 7.2.3 I had to remove any
reference to some system tables OIDs. Well, the next releases (7.3.x by the
first) will require again some changes of this type? If yes, what?
Thanks in advance,
Gianfranco Masia
P.S. sorry for my poor english :(
Gianfranco Masia - Eprom s.r.l. wrote:
> Hi all,
> I would like to know if between Pg 7.2.3 and next releases there are (or
> will be) differences as SQL or system tables structures. I explain better:
> I use table inheritance so when I I made porting of tables from Pg 7.0.3 to
> 7.1.3 I had to add the SQL keyword ONLY in SELECT statements; when I made
> porting of my databases from Pg 7.1.3 to 7.2.3 I had to remove any
> reference to some system tables OIDs. Well, the next releases (7.3.x by the
> first) will require again some changes of this type? If yes, what?
Good question. I would look at the release notes for such info:
http://developer.postgresql.org/docs/postgres/release.html#RELEASE-7-3
--
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
Any idea why when I call this function the record are not processed in
the order requested?
JLL
P.S.
It would be nice if the syntax would allow me to write something like >>
cur.seqno = seq
and have the underlying record updated.
declare
cur record;
seq int;
exchangeno text;
routeno text;
begin
exchangeno := '';
routeno := '';
for cur in
select oid, * from r order by exchangeno, routeno, street,
municipality, parity desc, fromno for update
loop
if cur.exchangeno != exchangeno or cur.routeno != routeno
then
seq := 1;
exchangeno := cur.exchangeno;
routeno := cur.routeno;
end if;
update r set seqno = seq, route = routeno || trim( lpad( seq, 4, '0'))
where oid = cur.oid;
seq := seq + 1;
end loop;
return 0;
end;
Well, I think I found why. Because OID is included in the selected fields list, the order by fields number are off by one. I rewrote the query using the field numbers instead of field names and the function ran as expected. It is a work around, but any ALTER to the table will force me to rewrite the field numbers. Someone should look into this. I think it is a bug. JLL Jean-Luc Lachance wrote: > > Any idea why when I call this function the record are not processed in > the order requested? > > JLL > > P.S. > > It would be nice if the syntax would allow me to write something like >> > cur.seqno = seq > and have the underlying record updated. > > declare > > cur record; > seq int; > exchangeno text; > routeno text; > > begin > > exchangeno := ''; > routeno := ''; > > for cur in > select oid, * from r order by exchangeno, routeno, street, > municipality, parity desc, fromno for update > loop > if cur.exchangeno != exchangeno or cur.routeno != routeno > then > seq := 1; > exchangeno := cur.exchangeno; > routeno := cur.routeno; > end if; > update r set seqno = seq, route = routeno || trim( lpad( seq, 4, '0')) > where oid = cur.oid; > seq := seq + 1; > end loop; > > return 0; > end; > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Hmmm...I would have said using the field numbers was the danger. I'd be interested to hear if anyone else has experienced field names not being matched to the correct columns. I'd also say your problem was probably more due to how you are initialising exchangeno and routeno variables to empty strings and then using those values in the order by clause of the select. However, having never used that FOR construct before I wouldn't want to swear to it not behaving as you seem to be expecting. -- Nigel J. Andrews On Tue, 26 Nov 2002, Jean-Luc Lachance wrote: > Well, I think I found why. > > Because OID is included in the selected fields list, the order by fields > number are off by one. > I rewrote the query using the field numbers instead of field names and > the function ran as expected. > It is a work around, but any ALTER to the table will force me to rewrite > the field numbers. > > Someone should look into this. I think it is a bug. > > JLL > > > Jean-Luc Lachance wrote: > > > > Any idea why when I call this function the record are not processed in > > the order requested? > > > > JLL > > > > P.S. > > > > It would be nice if the syntax would allow me to write something like >> > > cur.seqno = seq > > and have the underlying record updated. > > > > declare > > > > cur record; > > seq int; > > exchangeno text; > > routeno text; > > > > begin > > > > exchangeno := ''; > > routeno := ''; > > > > for cur in > > select oid, * from r order by exchangeno, routeno, street, > > municipality, parity desc, fromno for update > > loop > > if cur.exchangeno != exchangeno or cur.routeno != routeno > > then > > seq := 1; > > exchangeno := cur.exchangeno; > > routeno := cur.routeno; > > end if; > > update r set seqno = seq, route = routeno || trim( lpad( seq, 4, '0')) > > where oid = cur.oid; > > seq := seq + 1; > > end loop; > > > > return 0; > > end; > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> I'd also say your problem was probably more due to how you are initialising
> exchangeno and routeno variables to empty strings and then using those values
> in the order by clause of the select.
Yes, this is surely the issue: the 'exchangeno' and 'routeno' names in
the ORDER BY clause are being replaced by the plpgsql vars of the same
names.
It's a bad idea to use plpgsql variable names that are the same as
column names of tables that you use in the function...
regards, tom lane
Tom, At first I was about to tell you that it did not make sense to use local variable in the order by or group by clauses, but since they (order by - group by) can take numerical arguments... I understand perfectly now. Thanks for the help. JLL Tom Lane wrote: > > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > > I'd also say your problem was probably more due to how you are initialising > > exchangeno and routeno variables to empty strings and then using those values > > in the order by clause of the select. > > Yes, this is surely the issue: the 'exchangeno' and 'routeno' names in > the ORDER BY clause are being replaced by the plpgsql vars of the same > names. > > It's a bad idea to use plpgsql variable names that are the same as > column names of tables that you use in the function... > > regards, tom lane
On Tue, 26 Nov 2002 15:15:16 -0500, Bruce Momjian wrote: > Gianfranco Masia - Eprom s.r.l. wrote: >> Hi all, >> I would like to know if between Pg 7.2.3 and next releases there are >> (or >> will be) differences as SQL or system tables structures. > > Good question. I would look at the release notes for such info: > > http://developer.postgresql.org/docs/postgres/release.html#RELEASE-7-3 While the release notes are extensive, they are certainly not complete. I have already learned to my dismay (reported elsewhere) that the system table 'pg_relcheck' is gone, and 'pg_constraint' has taken over its function. That information is not covered at the URL listed above. -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
Jeff Boes wrote: > On Tue, 26 Nov 2002 15:15:16 -0500, Bruce Momjian wrote: > > > Gianfranco Masia - Eprom s.r.l. wrote: > >> Hi all, > >> I would like to know if between Pg 7.2.3 and next releases there are > >> (or > >> will be) differences as SQL or system tables structures. > > > > Good question. I would look at the release notes for such info: > > > > http://developer.postgresql.org/docs/postgres/release.html#RELEASE-7-3 > > > While the release notes are extensive, they are certainly not complete. > I have already learned to my dismay (reported elsewhere) that the system > table 'pg_relcheck' is gone, and 'pg_constraint' has taken over its > function. That information is not covered at the URL listed above. To be fair, we didn't expect many folks to care about pg_relcheck, and we assumed those that did were involved in the beta. Sorry. -- 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 Fri, 06 Dec 2002 16:39:09 -0500, Bruce Momjian wrote: > Jeff Boes wrote: >> While the release notes are extensive, they are certainly not complete. >> I have already learned to my dismay (reported elsewhere) that the >> system table 'pg_relcheck' is gone, and 'pg_constraint' has taken over >> its function. That information is not covered at the URL listed above. > > To be fair, we didn't expect many folks to care about pg_relcheck, and > we assumed those that did were involved in the beta. Sorry. "True dat." It would appear that the DBI and DBD::Pg developer(s) (Perl interface layer, for those outside) were unaware. Not only that, but there are apparently no plans to fix this in the near future, so if you plan to install PostgreSQL 7.3, plan on using something other than Perl to get at it. -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
Jeff Boes wrote: > On Fri, 06 Dec 2002 16:39:09 -0500, Bruce Momjian wrote: > > > Jeff Boes wrote: > >> While the release notes are extensive, they are certainly not complete. > >> I have already learned to my dismay (reported elsewhere) that the > >> system table 'pg_relcheck' is gone, and 'pg_constraint' has taken over > >> its function. That information is not covered at the URL listed above. > > > > To be fair, we didn't expect many folks to care about pg_relcheck, and > > we assumed those that did were involved in the beta. Sorry. > > > "True dat." It would appear that the DBI and DBD::Pg developer(s) (Perl > interface layer, for those outside) were unaware. > > Not only that, but there are apparently no plans to fix this in the near > future, so if you plan to install PostgreSQL 7.3, plan on using something > other than Perl to get at it. The bad news is that I am one of the DBD:pg maintainers. We just recently got the code under our control and David Wheeler and I are banging it around, with some help from others on gborg. We haven't figured out how to do the per-version tests needed, but we are working on it. It is a fairly new project for us. -- 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