Thread: atttypmod now 32 bits, interface change
I was wrong. atttypmod was passed as int16 to the clients. attypmod is now passed as int32. I have modified libpq to fix this. I think only odbc needs to be changed for this. I know odbc is not maintained here, but is uploaded from somewhere else. The maintainer needs to change this. The other interfaces look OK. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Not that we have been sitting on our hands, but we have been waiting for the FE/BE protocol to stabilize before updating the ODBC driver to the 6.4 specs. Have we reached this point? Bruce Momjian wrote: > I was wrong. > > atttypmod was passed as int16 to the clients. attypmod is now passed as > int32. I have modified libpq to fix this. I think only odbc needs to > be changed for this. I know odbc is not maintained here, but is > uploaded from somewhere else. The maintainer needs to change this. The > other interfaces look OK. > > -- > Bruce Momjian | 830 Blythe Avenue > maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 > + If your life is a hard drive, | (610) 353-9879(w) > + Christ can be your backup. | (610) 853-3000(h)
David Hartwig <daveh@insightdist.com> writes:
> Not that we have been sitting on our hands, but we have been waiting for the
> FE/BE protocol to stabilize before updating the ODBC driver to the 6.4
> specs. Have we reached this point?
The cancel changeover and this atttypmod width business were the only
open issues I know about. I'm prepared to declare the protocol frozen
for 6.4 ... are there any objections?
regards, tom lane
> Not that we have been sitting on our hands, but we have been waiting for the > FE/BE protocol to stabilize before updating the ODBC driver to the 6.4 > specs. Have we reached this point? Of course, beta does not start until Sep 1, so it is possible to wait some more to see of other things change before updating things, but currently, there are no open items I know about. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> Not that we have been sitting on our hands, but we have been waiting for the > FE/BE protocol to stabilize before updating the ODBC driver to the 6.4 > specs. Have we reached this point? Good point. I totally agree. I think we have finally stabalized the protocol, with the CANCEL completed last week by Tom Lane. As far as I know, the libpq and sgml docs are updated, so you can use them to see the changes. If you need details, I have kept some of Tom Lane's postings. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> The cancel changeover and this atttypmod width business were the only
> open issues I know about. I'm prepared to declare the protocol frozen
> for 6.4 ... are there any objections?
Sounds good. Should we ask Tatsuo to do some mixed-endian tests, or is
that area completely unchanged from v6.3?
- Tom
> David Hartwig <daveh@insightdist.com> writes: > > Not that we have been sitting on our hands, but we have been waiting for the > > FE/BE protocol to stabilize before updating the ODBC driver to the 6.4 > > specs. Have we reached this point? > > The cancel changeover and this atttypmod width business were the only > open issues I know about. I'm prepared to declare the protocol frozen > for 6.4 ... are there any objections? I agree. We are done. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> > The cancel changeover and this atttypmod width business were the only > > open issues I know about. I'm prepared to declare the protocol frozen > > for 6.4 ... are there any objections? > > Sounds good. Should we ask Tatsuo to do some mixed-endian tests, or is > that area completely unchanged from v6.3? > > - Tom > Unchanged, I think. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
"Thomas G. Lockhart" <lockhart@alumnus.caltech.edu> writes:
> Should we ask Tatsuo to do some mixed-endian tests, or is
> that area completely unchanged from v6.3?
I don't think I broke anything in that regard ... but more testing is
always a good thing. If Tatsuo-san can spare the time, it would be
appreciated.
regards, tom lane
At 11:32 AM 98.7.13 -0400, Tom Lane wrote: >"Thomas G. Lockhart" <lockhart@alumnus.caltech.edu> writes: >> Should we ask Tatsuo to do some mixed-endian tests, or is >> that area completely unchanged from v6.3? > >I don't think I broke anything in that regard ... but more testing is >always a good thing. If Tatsuo-san can spare the time, it would be >appreciated. Ok, I think I can start the testing next week. This week I'm too busy because I have to finish writing an article on PostgreSQL for a Japanese magazine! By the way what are the visible changes of 6.4? I know now we can cancel a query. Could you tell me any other thing so that I could refer to them in the article? -- Tatsuo Ishii t-ishii@sra.co.jp
At 11:32 AM 98.7.13 -0400, Tom Lane wrote: >"Thomas G. Lockhart" <lockhart@alumnus.caltech.edu> writes: >> Should we ask Tatsuo to do some mixed-endian tests, or is >> that area completely unchanged from v6.3? > >I don't think I broke anything in that regard ... but more testing is >always a good thing. If Tatsuo-san can spare the time, it would be >appreciated. >Ok, I think I can start the testing next week. >This week I'm too busy because I have to finish writing an article >on PostgreSQL for a Japanese magazine! >By the way what are the visible changes of 6.4? >I know now we can cancel a query. Could you tell me any other >thing so that I could refer to them in the article? --------------------------------------------------------------------------- Nothing big that I can think of. Lots of cleanup/improvements to existing areas. Vadim has some big items (as usual), but I don't think we want to mention them publically yet. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> >This week I'm too busy because I have to finish writing an article
> >on PostgreSQL for a Japanese magazine!
> >By the way what are the visible changes of 6.4?
> >I know now we can cancel a query. Could you tell me any other
> >thing so that I could refer to them in the article?
> Nothing big that I can think of. Lots of cleanup/improvements to
> existing areas.
Now Bruce! The automatic type coersion features are a pretty big change,
especially for the casual user; the columns in queries get matched up
and converted without any explicit work from the user. I can give Tatsuo
some examples if he would like. I'll bet there are a few other changes
which would give readers a good idea about the ongoing support and
improvements to Postgres...
Speaking of docs, we'll have SQL and utility commands in an
html/hardcopy reference manual. Hmm, may not be as exciting for Japanese
readers, but... :)
I've been updating the old release notes in the sgml sources, and have
that completed. Perhaps we can start the v6.4 release notes now? With
the sgml sources we can have more summary verbiage to help users get
introduced to new features, and then roll it out into a text file if
necessary.
- Tom
> > >This week I'm too busy because I have to finish writing an article > > >on PostgreSQL for a Japanese magazine! > > >By the way what are the visible changes of 6.4? > > >I know now we can cancel a query. Could you tell me any other > > >thing so that I could refer to them in the article? > > Nothing big that I can think of. Lots of cleanup/improvements to > > existing areas. > > Now Bruce! The automatic type coersion features are a pretty big change, > especially for the casual user; the columns in queries get matched up > and converted without any explicit work from the user. I can give Tatsuo > some examples if he would like. I'll bet there are a few other changes > which would give readers a good idea about the ongoing support and > improvements to Postgres... > > Speaking of docs, we'll have SQL and utility commands in an > html/hardcopy reference manual. Hmm, may not be as exciting for Japanese > readers, but... :) > > I've been updating the old release notes in the sgml sources, and have > that completed. Perhaps we can start the v6.4 release notes now? With > the sgml sources we can have more summary verbiage to help users get > introduced to new features, and then roll it out into a text file if > necessary. I was afraid I was going to insult someone by saying what I did. I MEANT that there are no features being added that a non-postgresql user would be interested in. subselects was one feature that non-postgresql users would understand. Most of our stuff now is cleanup/extension of 6.3 features, many of which would be uninteresting to potential users. I suggest we focus on telling them about 6.3, which is ready NOW, and has many nice features. In fact, since we started two years ago, every release has gotten much better than the previous, so we are now at a point where we are adding 'nifty' features like 'cancel' and atttypmod and stuff like that. The days where every release fixed server crashes, or added a feature that users were 'screaming for' may be a thing of the past. We are nearing a maturity stage, where we can focus on performance, documenation, features, and cleanup. The days when we have a 'major' feature may be fewer, because we have added 'most' of the major features people have been asking for. Our user base is growing, and the number of sophisticated developers is growing too, so we are getting major patches to improve lots of existing functionality. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Bruce Momjian wrote:
>
>
> I was afraid I was going to insult someone by saying what I did.
>
> I MEANT that there are no features being added that a non-postgresql
> user would be interested in. subselects was one feature that
> non-postgresql users would understand. Most of our stuff now is
> cleanup/extension of 6.3 features, many of which would be uninteresting
> to potential users.
Not requiring the column to sort on in target list ia also quite
important.
As are the (still elementary) constraints, still elementary becuse
there is no way to use functions or "is null" in check constraint,
and constraints can be used only when defining tables, not in
"alter table" construct.
> The days where every release fixed server crashes, or added a feature
> that users were 'screaming for' may be a thing of the past.
Is anyone working on fixing the exploding optimisations for many OR-s,
at least the canonic case used by access?
My impression is that this has fallen somewhere between
insightdist and Vadim.
> We are nearing a maturity stage, where we can focus on performance,
> documenation, features, and cleanup. The days when we have a 'major'
> feature may be fewer, because we have added 'most' of the major features
> people have been asking for.
Expect them asking more soon ;)
I'm sure that soon being just basic ANSI SQL compliant is not enough;
people will want (in no particular order ;):
* ANSI CLI,
* updatable cursors,
* foreign key constraints,
* distributed databases,
* row level locking,
* better inheritance,
* domains,
* isolation levels,
* PL/SQL,
* better optimisation for special cases,
* temporary tables (both global and session level),
* more SQL3 constructs,
* unlisten command, maybe an argument to listen command,
* better support for installing your own access methods,
* separating the methods typinput/typoutput (native binary)
and typreceive/typsend (wire binary), they are in pg_type
* implementing a new fe/be protocol that is easier to implement
(does not mix zero terminated, and count-prefixed chunks),
preferrably modelled after X-Window protocol.
* getting rid of the 8k limitations, both in fe/be protocol and
in disk storage.
I know that some of these things are being worked on, but I've lost
track which are expected for 6.4, which for 6.5 and which I should
not expect before 8.0 ;)
> Our user base is growing, and the number of sophisticated developers is
> growing too, so we are getting major patches to improve lots of existing
> functionality.
Yep. Great future is awaiting PostgreSQL.
I'm really looking forward to a time when I can find some time to
contribute some actual code.
Hannu
> Bruce Momjian wrote: > > > > > > I was afraid I was going to insult someone by saying what I did. > > > > I MEANT that there are no features being added that a non-postgresql > > user would be interested in. subselects was one feature that > > non-postgresql users would understand. Most of our stuff now is > > cleanup/extension of 6.3 features, many of which would be uninteresting > > to potential users. > > Not requiring the column to sort on in target list ia also quite > important. > > As are the (still elementary) constraints, still elementary becuse > there is no way to use functions or "is null" in check constraint, > and constraints can be used only when defining tables, not in > "alter table" construct. > > > The days where every release fixed server crashes, or added a feature > > that users were 'screaming for' may be a thing of the past. > > Is anyone working on fixing the exploding optimisations for many OR-s, > at least the canonic case used by access? > > My impression is that this has fallen somewhere between > insightdist and Vadim. > > > We are nearing a maturity stage, where we can focus on performance, > > documenation, features, and cleanup. The days when we have a 'major' > > feature may be fewer, because we have added 'most' of the major features > > people have been asking for. > > Expect them asking more soon ;) > > I'm sure that soon being just basic ANSI SQL compliant is not enough; > people will want (in no particular order ;): > * ANSI CLI, > * updatable cursors, > * foreign key constraints, > * distributed databases, > * row level locking, > * better inheritance, > * domains, > * isolation levels, > * PL/SQL, > * better optimisation for special cases, > * temporary tables (both global and session level), > * more SQL3 constructs, > * unlisten command, maybe an argument to listen command, > * better support for installing your own access methods, > * separating the methods typinput/typoutput (native binary) > and typreceive/typsend (wire binary), they are in pg_type > * implementing a new fe/be protocol that is easier to implement > (does not mix zero terminated, and count-prefixed chunks), > preferrably modelled after X-Window protocol. > * getting rid of the 8k limitations, both in fe/be protocol and > in disk storage. > > I know that some of these things are being worked on, but I've lost > track which are expected for 6.4, which for 6.5 and which I should > not expect before 8.0 ;) > > > Our user base is growing, and the number of sophisticated developers is > > growing too, so we are getting major patches to improve lots of existing > > functionality. > > Yep. Great future is awaiting PostgreSQL. > > I'm really looking forward to a time when I can find some time to > contribute some actual code. > > Hannu > Hard to argue with this. There are more MAJOR things that I had forgotten. Still, I will say that the things we are working on now are more 'extras', than the stuff we were working on a year ago, which were 'usablility' issues. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Hannu Krosing wrote: > > Not requiring the column to sort on in target list ia also quite > important. I'm not sure but isn't this already in 6.4-current ? > > As are the (still elementary) constraints, still elementary becuse > there is no way to use functions or "is null" in check constraint, ispas=> create table t (x int, check (x is null or x = 5)); CREATE ispas=> insert into t values (1); ERROR: ExecAppend: rejected due to CHECK constraint $1 ispas=> insert into t values (null); INSERT 168212 1 ispas=> insert into t values (5); INSERT 168213 1 And I'm sure that functions are supported too. This is 6.3.2 > and constraints can be used only when defining tables, not in > "alter table" construct. I hadn't time to do this when implementing and have no plans to do this. In "near" future :) > > > The days where every release fixed server crashes, or added a feature > > that users were 'screaming for' may be a thing of the past. > > Is anyone working on fixing the exploding optimisations for many OR-s, > at least the canonic case used by access? > > My impression is that this has fallen somewhere between > insightdist and Vadim. I'm not working... Vadim
> > > The days where every release fixed server crashes, or added a feature > > > that users were 'screaming for' may be a thing of the past. > > > > Is anyone working on fixing the exploding optimisations for many OR-s, > > at least the canonic case used by access? > > > > My impression is that this has fallen somewhere between > > insightdist and Vadim. > > I'm not working... > Not sure anyone has an idea how to fix this. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Hannu Krosing wrote: > Bruce Momjian wrote: > > > > > > I was afraid I was going to insult someone by saying what I did. > > > > I MEANT that there are no features being added that a non-postgresql > > user would be interested in. subselects was one feature that > > non-postgresql users would understand. Most of our stuff now is > > cleanup/extension of 6.3 features, many of which would be uninteresting > > to potential users. > > Not requiring the column to sort on in target list ia also quite > important. > Along these lines - I heard someone grumbling a while back about not being able to use a function in the ORDER/GROUP BY clauses. (i.e. SELECT bar FROM foo ORDER BY LCASE(alpha);) I believe it is on the TODO list. Bruce, I will claim this item unless someone else already has.
> Along these lines - I heard someone grumbling a while back about not being > able to use a function in the ORDER/GROUP BY clauses. (i.e. SELECT bar FROM > foo ORDER BY LCASE(alpha);) I believe it is on the TODO list. Bruce, I > will claim this item unless someone else already has. > > Done. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Hannu Krosing wrote:
> > The days where every release fixed server crashes, or added a feature
> > that users were 'screaming for' may be a thing of the past.
>
> Is anyone working on fixing the exploding optimisations for many OR-s,
> at least the canonic case used by access?
>
> My impression is that this has fallen somewhere between
> insightdist and Vadim.
This is really big for the ODBCers. (And I suspect for JDBCers too.) Many
desktop libraries and end-user tools depend on this "record set" strategy to
operate effectively.
I have put together a workable hack that runs just before cnfify(). The
option is activated through the SET command. Once activated, it identifies
queries with this particular multi-OR pattern generated by these RECORD SET
strategies. Qualified query trees are rewritten as multiple UNIONs. (One
for each OR grouping).
The results are profound. Queries that used to scan tables because of the
ORs, now make use of any indexes. Thus, the size of the table has virtually
no effect on performance. Furthermore, queries that used to crash the
backend, now run in under a second.
Currently the down sides are:
1. If there is no usable index, performance is significantly worse. The
patch does not check to make sure that there is a usable index. I could use
some pointers on this.
2. Small tables are actually a bit slower than without the patch.
3. Not very elegant. I am looking for a more generalized solution.
I have lots of ideas, but I would need to know the backend much better before
attempting any of them. My favorite idea is before cnfify(), to factor the
OR terms and pull out the constants into a virtual (temporary) table spaces.
Then rewrite the query as a join. The optimizer will (should) treat the new
query accordingly. This assumes that an efficient factoring algorithm exists
and that temporary tables can exist in the heap.
Illustration:
SELECT ... FROM tab WHERE
(var1 = const1 AND var2 = const2) OR
(var1 = const3 AND var2 = const4) OR
(var1 = const5 AND var2 = const6)
SELECT ... FROM tab, tmp WHERE
(var1 = var_x AND var2 = var_y)
tmp
var_x | var_y
--------------
const1|const2
const3|const4
const5|const6
Comments?
> The results are profound. Queries that used to scan tables because of the
> ORs, now make use of any indexes. Thus, the size of the table has virtually
> no effect on performance. Furthermore, queries that used to crash the
> backend, now run in under a second.
>
> Currently the down sides are:
> 1. If there is no usable index, performance is significantly worse. The
> patch does not check to make sure that there is a usable index. I could use
> some pointers on this.
>
> 2. Small tables are actually a bit slower than without the patch.
>
> 3. Not very elegant. I am looking for a more generalized solution.
> I have lots of ideas, but I would need to know the backend much better before
> attempting any of them. My favorite idea is before cnfify(), to factor the
> OR terms and pull out the constants into a virtual (temporary) table spaces.
> Then rewrite the query as a join. The optimizer will (should) treat the new
> query accordingly. This assumes that an efficient factoring algorithm exists
> and that temporary tables can exist in the heap.
OK, I have an idea. Just today, we allow:
select *
from tab1
where val in (
select x from tab2
union
select y from tab3
)
How about if instead of doing:
select * from tab1 where val = 3
union
select * from tab1 where val = 4
...
you change it to:
select * from tab1 where val in (
select 3
union
select 4
)
This may be a big win. You aren't running the same query over and over
again, with the same joins, and just a different constant.
Let me know.
If it fails for some reason, it is possible my subselect union code has
a bug, so let me know.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
> > The results are profound. Queries that used to scan tables because of the
> OK, I have an idea. Just today, we allow:
>
> select *
> from tab1
> where val in (
> select x from tab2
> union
> select y from tab3
> )
>
> How about if instead of doing:
>
> select * from tab1 where val = 3
> union
> select * from tab1 where val = 4
> ...
>
> you change it to:
>
> select * from tab1 where val in (
> select 3
> union
> select 4
> )
OK, I just ran some test, and it does not look good:
---------------------------------------------------------------------------
son_db=> explain select mmatter from matter where mmatter = 'A01-001';
NOTICE: QUERY PLAN:
Index Scan using i_matt2 on matter (cost=2.05 size=1 width=12)
EXPLAIN
son_db=> explain select mmatter from matter where mmatter in (select 'A01-001');
NOTICE: QUERY PLAN:
Seq Scan on matter (cost=512.20 size=1001 width=12)
SubPlan
-> Result (cost=0.00 size=0 width=0)
EXPLAIN
---------------------------------------------------------------------------
Turns out indexes are not used in outer queries of subselects. Not sure
why. Vadim?
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
On Wed, 15 Jul 1998, Bruce Momjian wrote: > > > > The days where every release fixed server crashes, or added a feature > > > > that users were 'screaming for' may be a thing of the past. > > > > > > Is anyone working on fixing the exploding optimisations for many OR-s, > > > at least the canonic case used by access? > > > > > > My impression is that this has fallen somewhere between > > > insightdist and Vadim. > > > > I'm not working... > > > > Not sure anyone has an idea how to fix this. What? How to get Vadim back to work? ;) Maarten _____________________________________________________________________________ | TU Delft, The Netherlands, Faculty of Information Technology and Systems | | Department of Electrical Engineering | | Computer Architecture and Digital Technique section | | M.Boekhold@et.tudelft.nl | -----------------------------------------------------------------------------
Vadim Mikheev wrote: > > Hannu Krosing wrote: > > > > Not requiring the column to sort on in target list ia also quite > > important. > > I'm not sure but isn't this already in 6.4-current ? > > > > > As are the (still elementary) constraints, still elementary becuse > > there is no way to use functions or "is null" in check constraint, > > ispas=> create table t (x int, check (x is null or x = 5)); > CREATE > ispas=> insert into t values (1); > ERROR: ExecAppend: rejected due to CHECK constraint $1 > ispas=> insert into t values (null); > INSERT 168212 1 > ispas=> insert into t values (5); > INSERT 168213 1 > > And I'm sure that functions are supported too. This is 6.3.2 Sorry, i tried the wrong syntax (without IS ) ;( but functions still dont work: hannu=> create table test1 (a text, b text, hannu-> check (trim(a) <> '' or trim(b) <> '')); ERROR: parser: parse error at or near "trim" If I use a non-existing function, I get a different answer hannu=> create table test1 (a text, b text, hannu-> check (strip(a) <> '' or strip(b) <> '')); ERROR: function strip(text) does not exist So it cant't be just "parser" error > > and constraints can be used only when defining tables, not in > > "alter table" construct. > > I hadn't time to do this when implementing and have no plans > to do this. In "near" future :) > > > > > > The days where every release fixed server crashes, or added a feature > > > that users were 'screaming for' may be a thing of the past. > > > > Is anyone working on fixing the exploding optimisations for many OR-s, > > at least the canonic case used by access? > > > > My impression is that this has fallen somewhere between > > insightdist and Vadim. > > I'm not working... Are you after some general solution, or are you first implementing the 'rewrite to union' way ? Hannu
Bruce Momjian wrote: > > > The results are profound. Queries that used to scan tables because of the > > How about if instead of doing: > > select * from tab1 where val = 3 > union > select * from tab1 where val = 4 > ... > > you change it to: > > select * from tab1 where val in ( > select 3 > union > select 4 > ) > the explosion happens for ORs of multiple ANDs that get rewritten to: select * from tabl wehere val1=1 and val2=1 and val3=1 union select * from tabl wehere val1=1 and val2=1 and val3=2 union ... And there is no way of doing (at least presently): select * from table where (val1,val2,val3) in (select 1,1,1 union select 1,1,2); Hannu
Hannu Krosing wrote: > > but functions still dont work: > > hannu=> create table test1 (a text, b text, > hannu-> check (trim(a) <> '' or trim(b) <> '')); > ERROR: parser: parse error at or near "trim" TRIM is keyword, not a function... We have to copy some lines in gram.y Real functions are working... Vadim
hi, guys! It seems to me that two or three weeks ago there were some messages about porting libpq for Win32 platform. I think it is very imporant feature and it should be mentioned with no doubts in all reviews about PostgreSQL 'cause it moved PostgreSQL far beyond any other free DB engeens in the world of Windowz Al.
Vadim Mikheev wrote:
>
> Hannu Krosing wrote:
> >
> > but functions still dont work:
> >
> > hannu=> create table test1 (a text, b text,
> > hannu-> check (trim(a) <> '' or trim(b) <> ''));
> > ERROR: parser: parse error at or near "trim"
>
> TRIM is keyword, not a function...
> We have to copy some lines in gram.y
Wow! is this standard ?
I found the function trim by doing 'select oprname from pg_oper'
and tested it as follows:
hannu=> select trim(' x ');
btrim
-----
x
(1 row)
why is the column called btrim ?
some rewrite magic in parser ?
If it must stay a keyword, then perhaps we should remove the proc ?
> Real functions are working...
yep! Thanks:
create table test2(a text,b text, check (btrim(a) <> '' or btrim(b) <>
''));
does work ;)
Hannu
> hi, guys! > > > It seems to me that two or three weeks ago there were some messages about > porting libpq for Win32 platform. I think it is very imporant feature and > it should be mentioned with no doubts in all reviews about PostgreSQL > 'cause it moved PostgreSQL far beyond any other free DB engeens in the Already done in the current snapshot on ftp.postgresql.org. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> > > hannu-> check (trim(a) <> '' or trim(b) <> '')); > > > ERROR: parser: parse error at or near "trim" > > > > TRIM is keyword, not a function... > > We have to copy some lines in gram.y I think that having trim as a keyword is a problem. The primary virtue of postgres is that everything is either a function or a type and as such is definable by the user and resolved at runtime. Making a keyword out of a function spoils that capability. -dg David Gould dg@illustra.com 510.628.3783 or 510.305.9468 Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612 - If simplicity worked, the world would be overrun with insects. -
> > > > hannu-> check (trim(a) <> '' or trim(b) <> '')); > > > > ERROR: parser: parse error at or near "trim" > > > > > > TRIM is keyword, not a function... > > > We have to copy some lines in gram.y > > I think that having trim as a keyword is a problem. The primary virtue of > postgres is that everything is either a function or a type and as such is > definable by the user and resolved at runtime. Making a keyword out of a > function spoils that capability. Problem was that SQL standard syntax (or Oracle) did not allow it to be a function. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
On Thu, 16 Jul 1998, Aleksey Dashevsky wrote: > It seems to me that two or three weeks ago there were some messages about > porting libpq for Win32 platform. I think it is very imporant feature and > it should be mentioned with no doubts in all reviews about PostgreSQL > 'cause it moved PostgreSQL far beyond any other free DB engeens in the > world of Windowz I'd thought that the ODBC driver would have more of an impact with Win32 than porting libpq, especially with existing applications. -- Peter T Mount peter@retep.org.uk or petermount@earthling.net Main Homepage: http://www.retep.org.uk ************ Someday I may rebuild this signature completely ;-) ************ Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk
On Wed, 15 Jul 1998, David Hartwig wrote: > This is really big for the ODBCers. (And I suspect for JDBCers too.) Many > desktop libraries and end-user tools depend on this "record set" strategy to > operate effectively. Although I haven't seen what they produce, it is possible that JBuilder and others do have this affect with JDBC. However, not all JDBC applications have this problem. Infact the majority I've seen only produce much simpler queries. -- Peter T Mount peter@retep.org.uk or petermount@earthling.net Main Homepage: http://www.retep.org.uk ************ Someday I may rebuild this signature completely ;-) ************ Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk
>At 11:32 AM 98.7.13 -0400, Tom Lane wrote:
>>"Thomas G. Lockhart" <lockhart@alumnus.caltech.edu> writes:
>>> Should we ask Tatsuo to do some mixed-endian tests, or is
>>> that area completely unchanged from v6.3?
>>
>>I don't think I broke anything in that regard ... but more testing is
>>always a good thing. If Tatsuo-san can spare the time, it would be
>>appreciated.
>
>Ok, I think I can start the testing next week.
I did some cross-platform testing today against 7/18 snapshot. The
platforms tested are:
1. Sparc/Solaris 2.6
2. PowerPC/Linux
3. x86/FreeBSD
4. x86/Linux
They workd fine!
P.S.
I noticed that 6.4 client did not talk to 6.3.2 server.
[srapc451.sra.co.jp]t-ishii{157}
Connection to database 'test' failed.
Unsupported frontend protocol.[srapc451.sra.co.jp]t-ishii{158}
I thought that we have kept the "backward compatibility" since we
introduced "protocol version" in libpq?
> >At 11:32 AM 98.7.13 -0400, Tom Lane wrote:
> >>"Thomas G. Lockhart" <lockhart@alumnus.caltech.edu> writes:
> >>> Should we ask Tatsuo to do some mixed-endian tests, or is
> >>> that area completely unchanged from v6.3?
> >>
> >>I don't think I broke anything in that regard ... but more testing is
> >>always a good thing. If Tatsuo-san can spare the time, it would be
> >>appreciated.
> >
> >Ok, I think I can start the testing next week.
>
> I did some cross-platform testing today against 7/18 snapshot. The
> platforms tested are:
>
> 1. Sparc/Solaris 2.6
> 2. PowerPC/Linux
> 3. x86/FreeBSD
> 4. x86/Linux
>
> They workd fine!
>
> P.S.
> I noticed that 6.4 client did not talk to 6.3.2 server.
>
> [srapc451.sra.co.jp]t-ishii{157}
>
>
>
> Connection to database 'test' failed.
> Unsupported frontend protocol.[srapc451.sra.co.jp]t-ishii{158}
>
> I thought that we have kept the "backward compatibility" since we
> introduced "protocol version" in libpq?
Might be my atttypmod changes. I did not make those version-sensitive.
I will do that now.
However, the protocol version number thing looks like something more
fundamental.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
Re: [INTERFACES] Re: [HACKERS] atttypmod now 32 bits, interface change
From
"Thomas G. Lockhart"
Date:
> I did some cross-platform testing today against 7/18 snapshot.
> They workd fine!
Great. Thanks Tatsuo.
- Tom
t-ishii@sra.co.jp writes:
>> I noticed that 6.4 client did not talk to 6.3.2 server.
>> Connection to database 'test' failed.
>> Unsupported frontend protocol.
>>
>> I thought that we have kept the "backward compatibility" since we
>> introduced "protocol version" in libpq?
Backwards compatibility yes: a 6.4 server should be able to talk to
an old client. You're asking about cross-version compatibility in the
other direction, which is something we don't have. The connection
protocol is designed to let the server accommodate to the client, not
vice versa --- the client tells the server its version, but not vice
versa. I suppose the client might check for that particular error
message after a connect failure and then try again with a lower version
number ... but that's pretty messy.
On a practical level, the new libpq is not capable of talking to an old
server anyway --- some of the cleanups I made are critically dependent
on new protocol features, such as the 'Z' (ReadyForQuery) message.
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Might be my atttypmod changes. I did not make those version-sensitive.
> I will do that now.
Yes, if we want to have backward compatibility as I just defined it,
then the backend will have to send atttypmod as either 2 or 4 bytes
depending on ProtocolVersion. Shouldn't be too hard. But I'm concerned
that you and I both missed that initially. We had better actually test
that the current backend sources will work with a 6.3.2-release frontend.
regards, tom lane
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Might be my atttypmod changes. I did not make those version-sensitive.
> > I will do that now.
>
> Yes, if we want to have backward compatibility as I just defined it,
> then the backend will have to send atttypmod as either 2 or 4 bytes
> depending on ProtocolVersion. Shouldn't be too hard. But I'm concerned
> that you and I both missed that initially. We had better actually test
> that the current backend sources will work with a 6.3.2-release frontend.
Already done. We never passed atttypmod to the backend before 6.4, so
the change it just to pass it or not pass it, and Tom already did that.
The fact that the internal length was 2 and is not 4 is not relevant
because we never passed it to the frontend in the past.
if (PG_PROTOCOL_MAJOR(FrontendProtocol) >= 2)
pq_putint(attrs[i]->atttypmod, sizeof(attrs[i]->atttypmod...
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)