Remove - Mailing list pgsql-sql

Remove

-----Original Message-----
From: owner-pgsql-sql-digest@hub.org
[mailto:owner-pgsql-sql-digest@hub.org]
Sent: Wednesday, November 03, 1999 9:01 PM
To: pgsql-sql-digest@hub.org
Subject: pgsql-sql-digest V1 #402



pgsql-sql-digest      Wednesday, November 3 1999      Volume 01 : Number 402



Index:

Re: [SQL] why don't this create table work?
Re: [SQL] why don't this create table work?
Re: [SQL] why don't this create table work? 
Re: [SQL] timestamps 
Re: [SQL] why don't this create table work?
unsubscribe
Optimizing a query through its syntax and indices
Re: [SQL] Optimizing a query through its syntax and indices

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

Date: Wed, 3 Nov 1999 05:00:26 +0100
From: Mathijs Brands <mathijs@ilse.nl>
Subject: Re: [SQL] why don't this create table work?

On Tue, Nov 02, 1999 at 05:59:15PM -0500, User & allegedly wrote:
> 
> here is the sql:
> 
> nnm=> create table nnm_event_limits (
>     nnm-> nodename varchar(256) not null,
>     nnm-> event_oid  varchar(256) not null,
>     nnm-> always_never varchar(1) null,                                ^^^^^^^^^^
There is a 'not' missing...
>     nnm-> limit int4);
> ERROR:  parser: parse error at or near "null"
> 
> This is converted from openviews table scheema.  here it is without the
> trailing null on always_never:
> nnm=> create table nnm_event_limits (
>     nnm-> nodename varchar(256) not null,
>     nnm-> event_oid  varchar(256) not null,
>     nnm-> always_never varchar(1) ,
>     nnm-> limit int4);
> ERROR:  parser: parse error at or near "limit"
> 
> limit is not a reserved word as far as I can tell, any ideas?

Actually, it is. You can do something like the following:

select username from users limit 10;

> I am useing 6.5, got it from PG_VERSION file.

You're probably running 6.5.1 or 6.5.2. You can easily check this
by starting psql and checking the first few lines. It will tell
you the exact versionnumber.

> ps would useing text instead of varchar be a good thing to do?

That is my understanding, but I'm not completely sure.

Mathijs

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

Date: Wed, 3 Nov 1999 00:04:11 -0500
From: User & <marc@oscar.noc.cv.net>
Subject: Re: [SQL] why don't this create table work?

On Wed, Nov 03, 1999 at 05:00:26AM +0100, Mathijs Brands wrote:
> On Tue, Nov 02, 1999 at 05:59:15PM -0500, User & allegedly wrote:
> > 
> > here is the sql:
> > 
> > nnm=> create table nnm_event_limits (
> >     nnm-> nodename varchar(256) not null,
> >     nnm-> event_oid  varchar(256) not null,
> >     nnm-> always_never varchar(1) null,
>                                  ^^^^^^^^^^
> There is a 'not' missing...

Not from my reading, null is the assumed default it does not have to be
put in but it can be if you choose to.

> >     nnm-> limit int4);
> > ERROR:  parser: parse error at or near "null"
> > 
> > This is converted from openviews table scheema.  here it is without the
> > trailing null on always_never:
> > nnm=> create table nnm_event_limits (
> >     nnm-> nodename varchar(256) not null,
> >     nnm-> event_oid  varchar(256) not null,
> >     nnm-> always_never varchar(1) ,
> >     nnm-> limit int4);
> > ERROR:  parser: parse error at or near "limit"
> > 
> > limit is not a reserved word as far as I can tell, any ideas?
> 
> Actually, it is. You can do something like the following:
> 
> select username from users limit 10;

This will not help me create the table, selecting is not the problem
the table will not get created so I cannot select on it.

> 
> > I am useing 6.5, got it from PG_VERSION file.
> 
> You're probably running 6.5.1 or 6.5.2. You can easily check this
> by starting psql and checking the first few lines. It will tell
> you the exact versionnumber.

6.5.2 from digging around in /usr/ports


marc


> 
> > ps would useing text instead of varchar be a good thing to do?
> 
> That is my understanding, but I'm not completely sure.
> 
> Mathijs
> 
> ************
> 

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

Date: Wed, 03 Nov 1999 00:35:52 -0500
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] why don't this create table work? 

User & <marc@oscar.noc.cv.net> writes:
> nnm=> create table nnm_event_limits (
> nnm-> nodename varchar(256) not null,
> nnm-> event_oid  varchar(256) not null,
> nnm-> always_never varchar(1) null,
> nnm-> limit int4);
> ERROR:  parser: parse error at or near "null"

> This is converted from openviews table scheema.

Openviews is allowing stuff that is not in the SQL92 spec --- AFAICS
there is nothing in the spec about a "NULL" column qualification.
You can say "NOT NULL" or you can leave it out.

> limit is not a reserved word as far as I can tell, any ideas?

Yes it is.  Probably we could allow it as a column name anyway,
but it's not listed as a "safe" column ID in the 6.5 grammar.
If you're determined to use it as a column name even though it's
reserved, put double quotes around it, eg "limit" int4.  But you'll
have to do that every time you refer to it in a query, so choosing
another name is probably the path of least resistance.

> ps would useing text instead of varchar be a good thing to do?

Use varchar if you have an application-defined reason to want to
enforce a specific upper limit on the length of the string in
a column.  If you don't have any particular upper limit in mind,
use text --- it's the same thing as varchar except for the limit.

In the above example, I'll bet a nickel that you have no clear reason
for specifying an upper limit of 256 on nodename and event_oid, so they
should probably be text.  If always_never can legitimately be either 0
or 1 chars long, but never more, then varchar(1) is the right
declaration.  (Perhaps it should always be 1 char long --- in that case
you should've said char(1).  Note that NULL is by no means the same
thing as a zero-character string.)
        regards, tom lane

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

Date: Wed, 03 Nov 1999 00:48:26 -0500
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] timestamps 

David Rugge <davidrugge@mindspring.com> writes:
> I created a table using this statement:
> create table timestamptest (creation_date timestamp default text 'now')
> But I get the creation date of the table instead of the date of the
> transaction. This is the format suggested in the manual to workaround the
> default value being set at table creation, but it doesn't work!

Try "default now()".

The documentation recommending "default text 'now'" is bogus, IMHO.
That method depends on a particular set of interacting behaviors in the
parser and the table-default-creation code, and at least some of those
behaviors were arguably bugs.  They were also data-type-dependent ---
I believe "default text 'now'" does work in 6.5.* for a DATETIME column,
but not for a TIMESTAMP column, because of slight differences in the
sets of available operators for the two datatypes.

FYI, any of these variants should give the result you want in future
releases.  But now() is the only one that I think can be counted on
to work in current and obsolete Postgres versions too.
        regards, tom lane

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

Date: Wed, 3 Nov 1999 02:31:12 -0500
From: User & <marc@oscar.noc.cv.net>
Subject: Re: [SQL] why don't this create table work?

On Wed, Nov 03, 1999 at 12:35:52AM -0500, Tom Lane wrote:
> User & <marc@oscar.noc.cv.net> writes:
> > nnm=> create table nnm_event_limits (
> > nnm-> nodename varchar(256) not null,
> > nnm-> event_oid  varchar(256) not null,
> > nnm-> always_never varchar(1) null,
> > nnm-> limit int4);
> > ERROR:  parser: parse error at or near "null"
> 
> > This is converted from openviews table scheema.
> 
> Openviews is allowing stuff that is not in the SQL92 spec --- AFAICS
> there is nothing in the spec about a "NULL" column qualification.
> You can say "NOT NULL" or you can leave it out.

I am sorry I should have said the oracle table scheema for the 
openview data wharehouse.  

>
> > limit is not a reserved word as far as I can tell, any ideas?
> 
> Yes it is.  Probably we could allow it as a column name anyway,
> but it's not listed as a "safe" column ID in the 6.5 grammar.
> If you're determined to use it as a column name even though it's
> reserved, put double quotes around it, eg "limit" int4.  But you'll
> have to do that every time you refer to it in a query, so choosing
> another name is probably the path of least resistance.

Thanks it worked.  The purpose for this exersize is to get a demo server up
for web based reporting on the openview datawharehouse we are collecting
here.  The final version will connect over odbc to solid or oracle, probably
solid, to get live data and do reports on it.  Since HP has already fixed
the
colum names I am just going to have to deal with it.  
> 
> > ps would useing text instead of varchar be a good thing to do?
> 
> Use varchar if you have an application-defined reason to want to
> enforce a specific upper limit on the length of the string in
> a column.  If you don't have any particular upper limit in mind,
> use text --- it's the same thing as varchar except for the limit.
> 
> In the above example, I'll bet a nickel that you have no clear reason
> for specifying an upper limit of 256 on nodename and event_oid, so they

If I was to take that bet you would owe me a shinny new nickle, my reason
for the use of varchar instead of text is very good: I don't realy know
what I am doing so while I am learning I change as little as possable
and I have not figured out why they did what they did so I will not
'improve' it.  And this has to be good enough to get me the go ahead
to get the production version started and no better, that last part 
is very important to me as features tend to stop creaping and start 
running into my code when I don't watch out.  

<some what off topic>  The webserver I am useing is roxen (www.roxen.com)
it is open source and very nice, and comes with postgres support out of 
the box.  It has a tag based language called RXML for doing neat stuff
and it runs as a non forking deamon which makes it very nice for an
embeded type server, we have had problems recently with netscape proxy
killing some very important boxes here recently, and a real convenient 
admin gui.  All in all I am very impressed with the product, just like
postgres very very nice job and much thanks to the developers.

<off topic/>

Thanks Marc


> should probably be text.  If always_never can legitimately be either 0
> or 1 chars long, but never more, then varchar(1) is the right
> declaration.  (Perhaps it should always be 1 char long --- in that case
> you should've said char(1).  Note that NULL is by no means the same
> thing as a zero-character string.)
> 
>             regards, tom lane

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

Date: Wed, 3 Nov 1999 07:24:01 -0600
From: "Joel Fischer" <joelf@min.ascend.com>
Subject: unsubscribe

> -----Original Message-----
> From: owner-pgsql-sql-digest@hub.org
> [mailto:owner-pgsql-sql-digest@hub.org]
> Sent: Tuesday, November 02, 1999 10:00 PM
> To: pgsql-sql-digest@hub.org
> Subject: pgsql-sql-digest V1 #401
> 
> 
> 
> pgsql-sql-digest       Tuesday, November 2 1999       Volume 01 : 
> Number 401
> 
> 
> 
> Index:
> 
> Antw: [SQL] query with subquery abnormally slow?
> ERROR:    btree: lost page
> Re: Antw: [SQL] query with subquery abnormally slow?
> Re: [SQL] query with subquery abnormally slow?
> Redhat 6.0 Link Error: Undefined Reference to crypt
> Re: [SQL] Redhat 6.0 Link Error: Undefined Reference to crypt 
> why don't this create table work?
> timestamps
> 
> ----------------------------------------------------------------------
> 
> Date: Tue, 02 Nov 1999 07:45:31 +0100
> From: "Gerhard Dieringer" <DieringG@eba-haus.de>
> Subject: Antw: [SQL] query with subquery abnormally slow?
> 
> >>> Oskar Liljeblad <osk@hem.passagen.se> 01.11.1999  19.47 Uhr >>>
> >>I'm doing a SELECT query with a subquery on a table with 12K rows
> >>but it is very slow (10 seconds+). The query looks like this:
> >>
> >>  select *
> >>   from items
> >>   where package in
> >>      (select package
> >>         from items
> >>         where ...blah...
> >>         group by package)
> >> .....
> 
> I am sorry  if I am still sleeping and don't see the problem, but 
> what is the difference between this querry and the following:
> 
> select *
>    from items
>        where ...blah...
> 
> Gerhard
> 
> ------------------------------
> 
> Date: Tue, 2 Nov 1999 10:51:17 +0330 (IRT)
> From: Roomi <roomi@RASANEH.safineh.net>
> Subject: ERROR:    btree: lost page
> 
>   Hi,
> i have a huge DB on the web and manage it with some perl scripts.
> recently i underestood that some fields of one of th tables make the
> postgres not to work proparely.
> 
> i found out that in the table X, and row for user Y, the field Z have
> problem. when i  type : "update X set Z='something' where user='Y' ; " in
> psql intractive environment, this error message occures :
> 
>  "FATAL 1:
> btree: lost page in the chain of duplicates"
> 
> then the psql don't accept any request and queries!!!
> 
> when i delete this user from table X, and re-add him with a new 
> user name, 
> the problem doesn't appear. also i can't add a user with his last user
> naem. !!!
> 
> i am really confused . may be this is a bug of postgres?!
> 
> plz send ur comments to me 
> 
> sincerely
> mehdi roomi
> roomi@safineh.net
> 
> ------------------------------
> 
> Date: Tue, 02 Nov 1999 00:42:30 PST
> From: "omid omoomi" <oomoomi@hotmail.com>
> Subject: Re: Antw: [SQL] query with subquery abnormally slow?
> 
> Hello ,,,
> don't you think that you should use a multiple row function ( 
> such as sum() 
> or ... ) along with 'group by' clause in that query?
> 
> regards ,
> omid omoomi
> 
> >From: "Gerhard Dieringer" <DieringG@eba-haus.de>
> >To: <pgsql-sql@postgreSQL.org>
> >Subject: Antw: [SQL] query with subquery abnormally slow?
> >Date: Tue, 02 Nov 1999 07:45:31 +0100
> >
> >
> >
> > >>> Oskar Liljeblad <osk@hem.passagen.se> 01.11.1999  19.47 Uhr >>>
> > >>I'm doing a SELECT query with a subquery on a table with 12K rows
> > >>but it is very slow (10 seconds+). The query looks like this:
> > >>
> > >>  select *
> > >>   from items
> > >>   where package in
> > >>      (select package
> > >>         from items
> > >>         where ...blah...
> > >>         group by package)
> > >> .....
> >
> >I am sorry  if I am still sleeping and don't see the problem, 
> but what is 
> >the difference between this querry and the following:
> >
> >select *
> >    from items
> >        where ...blah...
> >
> >Gerhard
> >
> >
> >
> >************
> >
> >
> 
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
> 
> ------------------------------
> 
> Date: Tue, 2 Nov 1999 00:42:41 -0800 (PST)
> From: Zalman Stern <zalman@netcom.com>
> Subject: Re: [SQL] query with subquery abnormally slow?
> 
> I inadvertently deleted Oskar's message where he described what he is
> trying to do at a higher level, but I don't think I'm completely missing
> the point with the following:
> 
> The table looks like so:
> 
> select * from test;
> [
> ssmldb=> select * from test;
> package               |artist             |song
> - ----------------------+-------------------+-----------------
> Surf Comp             |Mermen             |Pulpin
> Surf Comp             |Bambi Molesters    |Tremor
> Surf Comp             |The Squares        |Squaranoid
> Dumb Loud Hollow Twang|Bambi Molesters    |Point Break
> Dumb Loud Hollow Twang|Bambi Molesters    |Glider
> Songs of the Cows     |Mermen             |Songs of the Cows
> Surfmania             |The Aqua Velvets   |Surfmania
> Surf Comp 2           |Mermen             |Slo Mo H50
> Surf Comp 2           |Los Straightjackets|Caveman
> ]
> 
> select t2.* from test t1, test t2
>     where t1.package = t2.package and
>       t1.artist = 'Mermen' and t2.artist != 'Mermen';
> [
> package    |artist             |song
> - -----------+-------------------+----------
> Surf Comp  |Bambi Molesters    |Tremor
> Surf Comp  |The Squares        |Squaranoid
> Surf Comp 2|Los Straightjackets|Caveman
> (3 rows)
> ]
> 
> The query above shows all songs by a different artist that share an album
> with a song by the artist in question. It however omits the songs by the
> artist in question. To get those back, you could try:
> 
> select distinct t1.* from test t1, test t2
>     where t1.package = t2.package and
>       ((t1.artist = 'Mermen' and t2.artist != 'Mermen') or
>        (t1.artist != 'Mermen' and t2.artist = 'Mermen'));
> 
> or use a union clause:
> select t1.* from test t1, test t2
>     where t1.package = t2.package and
>       (t2.artist = 'Mermen' and t1.artist != 'Mermen')
>     union select t3.* from test t3, test t4
>           where t3.package = t4.package and
>             (t3.artist = 'Mermen' and t4.artist != 'Mermen') ;
> 
> I don't know how these do for speed as I don't care to create a big table
> and indices and all that, but they do not use EXITS and it seems with
> suitable indices they should be fairly fast.
> 
> - -Z-
> 
> ------------------------------
> 
> Date: Tue, 2 Nov 1999 10:34:55 -0500 
> From: "Klein, Robert" <rvklein@ober.com>
> Subject: Redhat 6.0 Link Error: Undefined Reference to crypt
> 
> I'm trying to compile a program on Redhat 6.0 with the Postgres 
> 6.4 the came
> with it.  
> I get the following link error:
> /usr/lib/libpq.so: Undefined reference to "crypt"
> Does postgres need a difference libc or glibc?  Redhat comes with:
> libc 5.3.13
> glibc 2.1.1
> 
> Thanks!
> 
> Rob Klein
> System Administrator
> Ober, Kaler, Grimes and Shriver
> 120 East Baltimore St
> Baltimore, MD 21202
> 
> ------------------------------
> 
> Date: Tue, 02 Nov 1999 14:09:58 -0500
> From: Tom Lane <tgl@sss.pgh.pa.us> 
> Subject: Re: [SQL] Redhat 6.0 Link Error: Undefined Reference to crypt 
> 
> "Klein, Robert" <rvklein@ober.com> writes:
> > I'm trying to compile a program on Redhat 6.0 with the Postgres 
> 6.4 the came
> > with it.  
> > I get the following link error:
> > /usr/lib/libpq.so: Undefined reference to "crypt"
> 
> You need to add -lcrypt to your link command, probably.  On some
> platforms crypt() is part of regular libc, on some it comes in
> a separate libcrypt library...
> 
>             regards, tom lane
> 
> ------------------------------
> 
> Date: Tue, 2 Nov 1999 17:59:15 -0500
> From: User & <marc@oscar.noc.cv.net>
> Subject: why don't this create table work?
> 
> here is the sql:
> 
> nnm=> create table nnm_event_limits (
>     nnm-> nodename varchar(256) not null,
>     nnm-> event_oid  varchar(256) not null,
>     nnm-> always_never varchar(1) null,
>     nnm-> limit int4);
> ERROR:  parser: parse error at or near "null"
> 
> This is converted from openviews table scheema.  here it is without the
> trailing null on always_never:
> nnm=> create table nnm_event_limits (
>     nnm-> nodename varchar(256) not null,
>     nnm-> event_oid  varchar(256) not null,
>     nnm-> always_never varchar(1) ,
>     nnm-> limit int4);
> ERROR:  parser: parse error at or near "limit"
> 
> limit is not a reserved word as far as I can tell, any ideas?
> 
> I am useing 6.5, got it from PG_VERSION file.
> 
> I am new to DB programming in general and Postgres in particular.
> 
> Thanks Marc
> 
> ps would useing text instead of varchar be a good thing to do?
> 
> Marc
> 
> ------------------------------
> 
> Date: Tue, 2 Nov 1999 21:51:52 -0500
> From: David Rugge <davidrugge@mindspring.com>
> Subject: timestamps
> 
> I created a table using this statement:
> 
> create table timestamptest (creation_date timestamp default text 'now')
> 
> But I get the creation date of the table instead of the date of the
> transaction. This is the format suggested in the manual to workaround the
> default value being set at table creation, but it doesn't work!
> 
> What can I do to make a timestamp that is the date the row was 
> inserted into
> the table?
> 
> - --
> David Rugge
> 
http://www.mindspring.com/~davidrugge/index.html

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

End of pgsql-sql-digest V1 #401
*******************************


************

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

Date: Wed, 03 Nov 1999 17:45:44 +0200
From: Stoyan Genov <genov@digsys.bg> 
Subject: Optimizing a query through its syntax and indices

Hello,

I talk PostgreSQL 6.5.2 ...

Suppose we have some tables we join using some of their fields.
Suppose there are "restrictions" for the values of some (or all) of the
tables'
fields of this kind: tableM.fieldN [ NOT ] IN (constA,constB,constC),tableP.fieldQ [ NOT ] IN
(constD,constE,constF),etc...
These restrictions can occur for the fields by which we join the tables, as
well
as for fields of tables we do not use for joins.

Are there (can there be) general ( or not so general :-) ) rules for
optimizing
the query ( and the performance and the speed ) through the particular order
we
put the join statements in the WHERE clause, trough mixing/ordering the join
parts and the restrictions ( in the means defined above ) in the values of
the
fields in the WHERE clause, through changing the syntax ( for example, using
UNION or EXCEPT clauses ), or through using one- or more-than-one-field
indices, or through doubling some of the restrictions if they refer to a
field we join by ( for example, "... WHERE tableM.fieldN = tableP.fieldQ and
tableM.fieldN in (constA,constB)" complemented by " and tableP.fieldQ in
(constA
,constB)" ).

I hope I was clear enough for my question to be understood.

Any help will be appretiated.

Regards,Stoyan Genov

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

Date: Wed, 3 Nov 1999 18:16:33 +0100 (MET)
From: wieck@debis.com (Jan Wieck)
Subject: Re: [SQL] Optimizing a query through its syntax and indices

>
>
> Hello,
>
> I talk PostgreSQL 6.5.2 ...
>
> Suppose we have some tables we join using some of their fields.
> Suppose there are "restrictions" for the values of some (or all) of the
tables'
> fields of this kind:
>    tableM.fieldN [ NOT ] IN (constA,constB,constC),
>    tableP.fieldQ [ NOT ] IN (constD,constE,constF),
>    etc...
> These restrictions can occur for the fields by which we join the tables,
as well
> as for fields of tables we do not use for joins.
>
> Are there (can there be) general ( or not so general :-) ) rules for
optimizing
> the query ( and the performance and the speed ) through the particular
order we
> put the join statements in the WHERE clause, trough mixing/ordering the
join
> parts and the restrictions ( in the means defined above ) in the values of
the
> fields in the WHERE clause, through changing the syntax ( for example,
using
> UNION or EXCEPT clauses ), or through using one- or more-than-one-field
> indices, or through doubling some of the restrictions if they refer to a
> field we join by ( for example, "... WHERE tableM.fieldN = tableP.fieldQ
and
> tableM.fieldN in (constA,constB)" complemented by " and tableP.fieldQ in
(constA
> ,constB)" ).
>
   The  last part, complementing the qualifications, is known to   speedup  the  join  significantly.  At   least   for
 simple   expressions  that  can be put down into the scan itself. This   is because it reduces the amount  of  data
before the  join   already.  Don't  know if this is true for IN expressions too,   so you might give it a try (and
reportthe result back to us,   please).
 
   There  had  been  the idea to do this automatically in a step   between rewriting and planning. Unfortunately noone
seems to   have the time to tackle it.
 


Jan

- --

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

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

End of pgsql-sql-digest V1 #402
*******************************


************