unsubscribe - Mailing list pgsql-sql

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


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