pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Undeliverable Message
5+ messages / 1 participants
[nested] [flat]

* Undeliverable Message
@ 1999-03-03 04:44  MAILER-DAEMON@mail1.sfr.fr
  0 siblings, 0 replies; 5+ messages in thread

From: MAILER-DAEMON@mail1.sfr.fr @ 1999-03-03 04:44 UTC (permalink / raw)
  To: pgsql-sql@hub.org

To:            ISMTP@SFRA0046@Servers[<pgsql-sql-digest@hub.org>]
Cc:            
Subject:       pgsql-sql-digest V1 #157

Message not delivered to recipients below.  Press F1 for help with VNM
error codes.               

	VNM3043:  DE_VOLDER Fabrice@ATR_EXPL_LYON1@SFR_DO_CNTR_EST



VNM3043 -- MAILBOX IS FULL

   The message cannot be delivered because the
   recipient's mailbox contains the maximum number of 
   messages, as set by the system administrator.  The
   recipient must delete some messages before any
   other messages can be delivered.
    The maximum message limit for a user's mailbox is 
   10,000.  The default message limit is 1000 messages.  
   Administrators can set message limits using the 
   Mailbox  Settings function available in the 
   Manage User menu  (MUSER). 

   When a user's mailbox reaches the limit, the 
   user must delete some of the messages before 
   the mailbox can accept any more incoming messages.

----------------------  Original Message Follows  ----------------------


pgsql-sql-digest        Tuesday, March 2 1999        Volume 01 : Number 157



Index:

Staus of Primary/Foreign key?
Semantics of LIKE
Persistent connection in PostGresSQL
FW: indexing functions
CREATE TABLE - DEFAULT 
Commit
Commit 
COMMIT
Re: [SQL] Staus of Primary/Foreign key?
RULE problem
question about pg_dump

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

Date: Mon, 1 Mar 1999 23:35:57 -0500 (EST)
From: "Stan Brown" <stanb@awod.com>
Subject: Staus of Primary/Foreign key?

	I have implemented referential integrity in older Postgres projects
	using functions. I am preparing to do a new project, aand was wondering
	what the status of Primary/Foreign key implementation was?

	I am starting with 6.4.2,

	Thanks.

- -- 
Stan Brown     stanb@netcom.com                                    843-745-3154
Westvaco
Charleston SC.
- -- 
Windows 98: n.
	useless extension to a minor patch release for 32-bit extensions and
	a graphical shell for a 16-bit patch to an 8-bit operating system
	originally coded for a 4-bit microprocessor, written by a 2-bit 
	company that can't stand for 1 bit of competition.
- -
(c) 1999 Stan Brown.  Redistribution via the Microsoft Network is prohibited.

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

Date: Mon, 01 Mar 1999 22:02:43 -0800
From: Craig Johannsen <cjohan@home.com>
Subject: Semantics of LIKE

How does the LIKE predicate operator treat trailing spaces
in a CHAR column.  For example,
create table test(c1 char(5));
insert into test('abc');
select c1 from test where c1 like 'abc%';
should retrieve one row.
select c1 from test where c1 like 'abc';
should retrieve zero rows.
select c1 from test where c1 like 'abc  ';
should retrieve one row.

Correct?  Does this conform exactly to the SQL 92 spec?

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

Date: Tue, 02 Mar 1999 12:11:10 +0200
From: CIMPOESU Teodor <teo@digiro.net>
Subject: Persistent connection in PostGresSQL

I have some questions for you all,
What is the difference between a persistent connection and a regular one
in postgres?
If I read /server-status after some queries with pg_pconnect() I see a
lot of active sessions there.
In which cases it is reasonable to use a persistent conection and in
which is not?
10x.

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

Date: Tue, 2 Mar 1999 12:36:07 +0100
From: Bas Peters <bpeters@idc.nl>
Subject: FW: indexing functions

I posted this message a while ago, but did not receive any response, so
I give it another try. Any pointer would help.

> Is it possible to manipulate the way an index is created by using
> functions? I would like to store SGML data in text fields that include
> a lot of entities (like &uuml;). To enable the user to search the
> &uuml; character as u I would like to index those entities as the
> character without the diacritical marks. Is this possible?
> 
> Thanks in advance,
> 
> Bas Peters
> bpeters@idc.nl

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

Date: Tue, 2 Mar 1999 06:51:12 -0500 (GMT+5)
From: Ingrith Andrea Correa Vargas <i-correa@uniandes.edu.co>
Subject: CREATE TABLE - DEFAULT 

I'll try sending my question again.
Can you help me?!

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

Hello again!

I have been receiving some mails asking my question about creating a table
with a default value as a user defined function, but I have a problem yet.

I hope somebody knows to solve my problem...
Thank you in advance! :)

//Ingrith

- ---||---

Te complete problem is:

- ---------------------------------------------------------
CREATE TABLE emp (code INT4
  , name VARCHAR(20)
  , salary INT4
  , 2salary INT4 default double_salary(emp)
);

CREATE FUNCTION double_salary(emp) RETURNS INT4
AS 'SELECT $1.salay * 2 AS result' LANGUAGE 'sql'
;

ERROR:  parser: syntax error at or near ")"
- ---------------------------------------------------------

Function double_salary receives an 'emp' , as you see, and there is the
error, because if I change parameter 'emp' by a number, the error doesn't
appear.

How can I do?!

The origin of this question is that I want to know if a subclass inherits
all atributtes from the parent, included functions like double_salary in
my example.

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

Date: Tue, 2 Mar 1999 16:55:39 -0300 (EST)
From: Daniel Facciolo Pires <dfpires@tekila.intermidia.icmc.sc.usp.br>
Subject: Commit

Hello,

I'm using Postgresql with Servlets. Servlets don't accept finalize a
section more than two times. I have to update a value (sequence) to take
the new value. So, i would like to know if have a way to invoke a command
like commit ... by programm and how can i do this.

Thanks a lot

- -----------------------------------------
Daniel Facciolo Pires
Mestrando em Ciencias da Computacao
ICMC-USP-Sao Carlos
Grupo: Hipermidia
dfpires@icmc.sc.usp.br
http://www.intermidia.icmc.sc.usp.br/~dfpires

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

Date: Tue, 2 Mar 1999 17:06:30 -0300 (EST)
From: Daniel Facciolo Pires <dfpires@tekila.intermidia.icmc.sc.usp.br>
Subject: Commit 

Hello,

I'm using Postgresql with Servlets. Servlets don't accept finalize a
section more than two times. I have to update a value (sequence) to take
the new value. So, i would like to know if have a way to invoke a command
like commit ... by programm and how can i do this.

Thanks a lot

- -----------------------------------------
Daniel Facciolo Pires
Mestrando em Ciencias da Computacao
ICMC-USP-Sao Carlos
Grupo: Hipermidia
dfpires@icmc.sc.usp.br
http://www.intermidia.icmc.sc.usp.br/~dfpires

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

Date: Tue, 2 Mar 1999 17:18:16 -0300 (EST)
From: Daniel Facciolo Pires <dfpires@tekila.intermidia.icmc.sc.usp.br>
Subject: COMMIT

Hello,

I'm using Postgresql with Servlets. Servlets don't accept finalize a
section more than two times. I have to update a value (sequence) to take
the new value. So, i would like to know if have a way to invoke a command
like commit ... by programm and how can i do this.

Thanks a lot 

- -----------------------------------------
Daniel Facciolo Pires
Mestrando em Ciencias da Computacao
ICMC-USP-Sao Carlos
Grupo: Hipermidia
dfpires@icmc.sc.usp.br
http://www.intermidia.icmc.sc.usp.br/~dfpires

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

Date: Tue, 2 Mar 1999 21:53:44 +0100 (MET)
From: jwieck@debis.com (Jan Wieck)
Subject: Re: [SQL] Staus of Primary/Foreign key?

>
>    I have implemented referential integrity in older Postgres projects
>    using functions. I am preparing to do a new project, aand was wondering
>    what the status of Primary/Foreign key implementation was?
>
>    I am starting with 6.4.2,

    I'm  planning  to implement most of the constraints currently
    missing (foreign key, cascaded deletes) over rules  -  or  at
    least making the rule system capable of supporting it.

    But  it  will  not  be  in  v6.5. The reason for that is that
    constraints need a special feature, the rule system currently
    cannot  support.  It  is  required  that  rule actions can be
    deferred until transaction commit time.   At  that  time  (or
    when  they  should  get  run  anyway), they need very special
    visibility of heap tuples on a per range table base. One  and
    the  same  table  might  have  to  be  scanned with different
    snapshot's in one query.

    That all will take a while.


Jan

- --

## It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#
------------------------------

Date: Tue, 2 Mar 1999 23:06:55 +0100 (MET)
From: mueller@pmsun20.dial.eunet.ch (Pascal Mueller)
Subject: RULE problem

Hi

I have a problem using rules and do not know if I am wrong or if there 
is a bug somewhere?
I have two tables A and B. I want to restrict deleting an instance of table 
A if there is a instance in B referencing A. 
I tried to set up a rule for this. Here's an example:

- -- START EXAMPLE

- -- **********************************************************************
- -- * rule_test.sql							*
- -- * A test case for rule usage						*
- -- *									*
- -- * Last modification: 03/02/1999				pm, SWS	*
- -- **********************************************************************

- -- Create table
CREATE TABLE a ( a INT PRIMARY KEY );
CREATE TABLE b ( b INT PRIMARY KEY, ref INT );


- -- RULEs ----------------------------------------------------------------
- --	Rules for database integrity.

- -- Restrict REMOVE on A if there is still an entry in B referencing A
CREATE RULE demo AS ON DELETE TO a 
	WHERE EXISTS (
	  SELECT * FROM b
	    WHERE b.ref 	)
	DO INSTEAD NOTHING
;

- -- FILL IN DATA ---------------------------------------------------------
INSERT INTO a VALUES ( '1' );
INSERT INTO a VALUES ( '2' );
INSERT INTO a VALUES ( '3' );
INSERT INTO b VALUES ( '1', '1' );
INSERT INTO b VALUES ( '2', '3' );
INSERT INTO b VALUES ( '3', '3' );

- -- TRY TO REMOVE --------------------------------------------------------
DELETE FROM a WHERE a DELETE FROM a WHERE a DELETE FROM a WHERE a 
- -- END EXAMPLE


The DELETE commands does not work and ends up in an ERROR in all 3 cases:

  DELETE FROM a WHERE a   ERROR:  ExecEvalExpr: unknown expression type 108
  DELETE FROM a WHERE a   ERROR:  ExecEvalExpr: unknown expression type 108
  DELETE FROM a WHERE a   ERROR:  ExecEvalExpr: unknown expression type 108

And trying to understand what's going on I issued en EXPLAIN command, 
crashing the backend:

  pascal  pqReadData() -- backend closed the channel unexpectedly.
	This probably means the backend terminated abnormally before or while 
	processing the request.
  We have lost the connection to the backend, so further processing is 
  impossible.  Terminating.


What I'm doing wrong here? Any hint? Thanks for any help.


Pascal Mueller

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

Date: Tue, 2 Mar 1999 22:01:56 -0500
From: Bill Brandt <brandtwr-pgsql@draaw.net>
Subject: question about pg_dump

I'm trying to upgrade from 6.3.  In doing this, I performed a pg_dump of
each database (pg_dump -o where the oid's matter).  I have one database
(which doesn't use oid info in the tables btw) that is around 40MB of
data.  The output file that created looks fine, but when I go to load the
data, it gets a portion of the way into loading and indicates that you
cannot exceed 20000 lines of input on a copy command.  Is there a way to
dump the database that it won't try more than 20000 lines at a time?  I
kept the 6.3 directory so dumping again is not a big deal.

Bill

- -- 
Bill Brandt                                   
brandtwr@draaw.net                              http://www.draaw.net/

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

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Undeliverable Message
@ 1999-03-25 04:57  MAILER-DAEMON@mail1.sfr.fr
  0 siblings, 0 replies; 5+ messages in thread

From: MAILER-DAEMON@mail1.sfr.fr @ 1999-03-25 04:57 UTC (permalink / raw)
  To: pgsql-sql@hub.org

To:            ISMTP@SFRA0046@Servers[<pgsql-sql-digest@hub.org>]
Cc:            
Subject:       pgsql-sql-digest V1 #178

Message not delivered to recipients below.  Press F1 for help with VNM
error codes.               

	VNM3043:  DE_VOLDER Fabrice@ATR_EXPL_LYON1@SFR_DO_CNTR_EST



VNM3043 -- MAILBOX IS FULL

   The message cannot be delivered because the
   recipient's mailbox contains the maximum number of 
   messages, as set by the system administrator.  The
   recipient must delete some messages before any
   other messages can be delivered.
    The maximum message limit for a user's mailbox is 
   10,000.  The default message limit is 1000 messages.  
   Administrators can set message limits using the 
   Mailbox  Settings function available in the 
   Manage User menu  (MUSER). 

   When a user's mailbox reaches the limit, the 
   user must delete some of the messages before 
   the mailbox can accept any more incoming messages.

----------------------  Original Message Follows  ----------------------


pgsql-sql-digest       Wednesday, March 24 1999       Volume 01 : Number 178



Index:

Re: [SQL] DEFAULT TEXT 'now'
Re: [SQL] DEFAULT TEXT 'now'
Re: [SQL] transaction-lock? 
Re: [SQL] DEFAULT TEXT 'now'
Comparing Dates to Current Time 
Comparing Dates to Current Time 
Use of 'default TEXT now()'
RE: [SQL] Comparing Dates to Current Time
RE: [SQL] Use of 'default TEXT now()'
RE: [SQL] Comparing Dates to Current Time
RE: [SQL] Use of 'default TEXT now()'
Re: [SQL] Use of 'default TEXT now()'
RE: [SQL] Use of 'default TEXT now()'
types and subtypes
RE: [SQL] types and subtypes
Help with query

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

Date: Wed, 24 Mar 1999 02:09:38 -0500 (EST)
From: Howie <caffeine@toodarkpark.org>
Subject: Re: [SQL] DEFAULT TEXT 'now'

On Tue, 23 Mar 1999, Roberto Joao Lopes Garcia wrote:

> Hi
> 
> I+m have a table like bellow:
> 
> CREATE TABLE conex (
>     id int4 PRIMARY KEY DEFAULT NEXTVAL('seq_conex'),
>  
>     inicio datetime DEFAULT TEXT 'now', 
>     ultimo datetime DEFAULT TEXT
> 'now',       
>                  .
>                  .
>                  .
> )
> 
> insert into conex (ip) values(+200.200.200.200+)
> 
> Will result every time the same data and time for +inicio+ and +ultimo+! 
> 
> I want to insert the current date and time. Is that code correct and the
> result a bug???
> Is there an way to make the DEFAULT work???

try:

...
inicido DATETIME DEFAULT NOW(),
ultimo  DATETIME DEFAULT NOW(),
...

- ---
Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org    
  "You can change the world with a bullet in the right place"

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

Date: 24 Mar 1999 10:10:42 +0300
From: tolik@icomm.ru (Anatoly K. Lasareff)
Subject: Re: [SQL] DEFAULT TEXT 'now'

>>>>> "RJLG" 
 RJLG> Hi
 RJLG> I+m have a table like bellow:

 RJLG> CREATE TABLE conex (
 RJLG> id int4 PRIMARY KEY DEFAULT NEXTVAL('seq_conex'),
 
 RJLG> inicio datetime DEFAULT TEXT 'now', 
 RJLG> ultimo datetime DEFAULT TEXT
 RJLG> 'now',       
 RJLG> .
 RJLG> .
 RJLG> .
 RJLG> )

 RJLG> insert into conex (ip) values(+200.200.200.200+)

 RJLG> Will result every time the same data and time for +inicio+ and +ultimo+! 

You must type

 
inicio datetime DEFAULT TEXT now(),
                             ^^^^^

This is the function which calls every time you insert new record. In
your case 'now' is the constant, which calculates once, when you
create the table.

- -- 
Anatoly K. Lasareff              Email:       tolik@icomm.ru 
Senior programmer

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

Date: Wed, 24 Mar 1999 14:42:30 +0200
From: Herouth Maoz <herouth@oumail.openu.ac.il>
Subject: Re: [SQL] transaction-lock? 

At 20:06 +0200 on 23/03/1999, Forgacs Tamas wrote:


>
> I am a novice pgsql user. I used Oracle, but it is unbelievable what
> bugs they have...
>
> So, is it possible, that if one transaction is pending on for eg. 'test'
> table in one psql session, then in another session this 'test' table is
> locked until the transaction is not finished on another????
>
> I have been tested a lot of ways, but the most easier problem is the
> next:
>     - there is a table named 'test', with only 1 field.
>     - opening a psql session on tty1, and on tty2 into the same database
>
>     - on tty1 'begin' a transaction
>     - on tty1 'insert' 1 row into the 'test' table
>     - on tty2 'select' from the 'test' table
>     > transaction not finished

Yes, that's the documented behaviour. Oracle has bugs, but Postgres will
have a better locking resolution only starting 6.5. At the moment it locks
on tables.

Herouth

- --
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

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

Date: Wed, 24 Mar 1999 10:26:55 -0600
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
Subject: Re: [SQL] DEFAULT TEXT 'now'

Anatoly K. Lasareff wrote:
> 
> >>>>> "RJLG" > 
>> <snipped problem with using default 'now'>

> <snipped description of using now() instead>

What version of postgresql are you all using? I tried RJLG's example
code with 6.4.2, and it worked - I got different times at each insert,
so the default 'now' was being calculated at insertion time, not table
creation time. Here's what it looks like:

test2
Table    +----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4 not null default nextval ( 
|     4 |
| inicio                           | datetime default text 'now'     
|     8 |
| ultimo                           | datetime default text 'now'     
|     8 |
| ip                               | inet                            
|   var |
+----------------------------------+----------------------------------+-------+
Index:    conex_pkey
test2id|inicio                      |ultimo                     
|             ip
- --+----------------------------+----------------------------+---------------
 1|Tue Mar 23 13:40:24 1999 CST|Tue Mar 23 13:40:24 1999
CST|200.200.200.200
 2|Tue Mar 23 13:40:28 1999 CST|Tue Mar 23 13:40:28 1999
CST|200.200.200.202
 3|Tue Mar 23 13:40:30 1999 CST|Tue Mar 23 13:40:30 1999
CST|200.200.200.203
 4|Tue Mar 23 13:41:06 1999 CST|Tue Mar 23 13:41:06 1999
CST|200.200.200.203
 5|Tue Mar 23 13:41:21 1999 CST|Tue Mar 23 13:41:21 1999
CST|200.200.200.205
(5 rows)

test2
???

Ross

- -- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

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

Date: Wed, 24 Mar 1999 14:49:37 -0400
From: Jenny DiBartolomeo <jdibartolomeo@agency.com>
Subject: Comparing Dates to Current Time 

OK... this is probably a trivial one.  In an Oracle query, I might do the
following select statement:

   SELECT title

     FROM articles

    WHERE expires > SYSDATE

How would I do the same thing in Postgres if the column "expires" is of the
data type "DATE"?  I've tried everything and read the man pages and can't
seem to get it working!  Any help would be VERY APPRECIATED!!!

Thanks sooooo much for any help you can give me!!!!  Please respond via
email to jdibartolomeo@agency.com.

- -Jenny

- ------------------------------
Jennifer DiBartolomeo
jdibartolomeo@agency.com
- ------------------------------

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

Date: Wed, 24 Mar 1999 15:08:48 -0400
From: Jenny DiBartolomeo <jdibartolomeo@agency.com>
Subject: Comparing Dates to Current Time 

OK... this is probably a trivial one.  In an Oracle query, I might do the
following select statement:

   SELECT title

     FROM articles

    WHERE expires > SYSDATE

How would I do the same thing in Postgres if the column "expires" is of the
data type "DATE"?  I've tried everything and read the man pages and can't
seem to get it working!  Any help would be VERY APPRECIATED!!!

Thanks sooooo much for any help you can give me!!!!  Please respond via
email to jdibartolomeo@agency.com.

- -Jenny

- ------------------------------
Jennifer DiBartolomeo
jdibartolomeo@agency.com
- ------------------------------

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

Date: Wed, 24 Mar 1999 14:19:59 -0600 (CST)
From: "Ing. Roberto Andrade" <randrade@campus.iztacala.unam.mx>
Subject: Use of 'default TEXT now()'

Hi:

I'm writing an application where I have to use a datetime field. In this
field we'll keep the date and time of the coments about some tasks.

I am not sure about the right use of 'default', should I use:

fecha_obs	datetime default TEXT now()

or

fecha_obs	datetime default now() ?

When I must use TEXT ?

I want to retrieve the fields in datetime order.

TIA

                       Roberto Andrade Fonseca
                Universidad Nacional Aut<noma de M0xico

                     Tel0fonos: 623-1154, 623-1119
               e-mail: randrade@campus.iztacala.unam.mx

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

Date: Wed, 24 Mar 1999 15:12:19 -0600
From: "Jackson, DeJuan" <djackson@cpsgroup.com>
Subject: RE: [SQL] Comparing Dates to Current Time

try:
 SELECT title
   FROM articles
  WHERE expires > now()::datetime::date
	-DEJ

> -----Original Message-----
> From: Jenny DiBartolomeo [mailto:jdibartolomeo@agency.com]
> Sent: Wednesday, March 24, 1999 1:09 PM
> To: pgsql-sql@postgreSQL.org
> Subject: [SQL] Comparing Dates to Current Time
> 
> 
> OK... this is probably a trivial one.  In an Oracle query, I 
> might do the
> following select statement:
> 
>    SELECT title
> 
>      FROM articles
> 
>     WHERE expires > SYSDATE
> 
> How would I do the same thing in Postgres if the column 
> "expires" is of the
> data type "DATE"?  I've tried everything and read the man 
> pages and can't
> seem to get it working!  Any help would be VERY APPRECIATED!!!
> 
> Thanks sooooo much for any help you can give me!!!!  Please 
> respond via
> email to jdibartolomeo@agency.com.
> 
> -Jenny
> 
> ------------------------------
> Jennifer DiBartolomeo
> jdibartolomeo@agency.com
> ------------------------------
> 

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

Date: Wed, 24 Mar 1999 15:14:55 -0600
From: "Jackson, DeJuan" <djackson@cpsgroup.com>
Subject: RE: [SQL] Use of 'default TEXT now()'

Use:
  fecha_obs	datetime default datetime(now())
The use of TEXT was for:
  fecha_obs datetime default text 'now'
which will also do what you want (I believe, haven't tried it).
	-DEJ

> -----Original Message-----
> From: Ing. Roberto Andrade [mailto:randrade@campus.iztacala.unam.mx]
> Sent: Wednesday, March 24, 1999 2:20 PM
> To: sql
> Subject: [SQL] Use of 'default TEXT now()'
> 
> 
> Hi:
> 
> I'm writing an application where I have to use a datetime 
> field. In this
> field we'll keep the date and time of the coments about some tasks.
> 
> I am not sure about the right use of 'default', should I use:
> 
> fecha_obs	datetime default TEXT now()
> 
> or
> 
> fecha_obs	datetime default now() ?
> 
> When I must use TEXT ?
> 
> I want to retrieve the fields in datetime order.
> 
> TIA
> 
>                        Roberto Andrade Fonseca
>                 Universidad Nacional Aut<noma de M0xico
> 
>                      Tel0fonos: 623-1154, 623-1119
>                e-mail: randrade@campus.iztacala.unam.mx
> 
> 

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

Date: Wed, 24 Mar 1999 15:18:16 -0600
From: Michael Davis <michael.davis@prevuenet.com>
Subject: RE: [SQL] Comparing Dates to Current Time

Try select title from articles where expires > now()::date;

	-----Original Message-----
	From:	Jenny DiBartolomeo [SMTP:jdibartolomeo@agency.com]
	Sent:	Wednesday, March 24, 1999 12:09 PM
	To:	pgsql-sql@postgreSQL.org
	Subject:	[SQL] Comparing Dates to Current Time

	OK... this is probably a trivial one.  In an Oracle query, I might
do the
	following select statement:

	   SELECT title

	     FROM articles

	    WHERE expires > SYSDATE

	How would I do the same thing in Postgres if the column "expires" is
of the
	data type "DATE"?  I've tried everything and read the man pages and
can't
	seem to get it working!  Any help would be VERY APPRECIATED!!!

	Thanks sooooo much for any help you can give me!!!!  Please respond
via
	email to jdibartolomeo@agency.com.

	-Jenny

	------------------------------
	Jennifer DiBartolomeo
	jdibartolomeo@agency.com
	------------------------------

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

Date: Wed, 24 Mar 1999 15:31:37 -0600 (CST)
From: "Ing. Roberto Andrade" <randrade@campus.iztacala.unam.mx>
Subject: RE: [SQL] Use of 'default TEXT now()'

Hi Jackson:

On Wed, 24 Mar 1999, Jackson, DeJuan wrote:

> Use:
>   fecha_obs	datetime default datetime(now())
> The use of TEXT was for:
>   fecha_obs datetime default text 'now'
> which will also do what you want (I believe, haven't tried it).
> 	-DEJ

Where can I get some help about this subject?

TIA,

Roberto Andrade Fonseca 

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

Date: Wed, 24 Mar 1999 05:55:37 -0800 (PST)
From: Rich Shepard <rshepard@appl-ecosys.com>
Subject: Re: [SQL] Use of 'default TEXT now()'

On Wed, 24 Mar 1999, Ing. Roberto Andrade wrote:

> I'm writing an application where I have to use a datetime field. In this
> field we'll keep the date and time of the coments about some tasks.
> 
> I am not sure about the right use of 'default', should I use:
> 
> fecha_obs	datetime default TEXT now()
> 
> or
> 
> fecha_obs	datetime default now() ?
> 
> When I must use TEXT ?

  Unless I've missed something important, you want to use the latter form.
Text is a field data type for a variable length field without limit. Like
the memo field in dBASE and Paradox.

  The variable length field with a limit to the number of characters it can
contain is varchar(n). The data type, char(n), holds a fixed length string,
padded with blanks, to the maximum of 'n'.

  These are most of the character data types. What you want is one of the
date/time data types.

Hope this helps,

Rich


Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc.
2404 SW 22nd Street
Troutdale, OR 97060-1247  U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax)
rshepard@appl-ecosys.com

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

Date: Wed, 24 Mar 1999 15:59:52 -0600
From: "Jackson, DeJuan" <djackson@cpsgroup.com>
Subject: RE: [SQL] Use of 'default TEXT now()'

You might take a look at the tutorial on the PostgreSQL web-site
(http://www.postgreSQL.ORG/docs/tutorial/index.html).
There are other references under the "Getting Help/Documentation" section in
the web-site as well

> -----Original Message-----
> From: Ing. Roberto Andrade [mailto:randrade@campus.iztacala.unam.mx]
> Sent: Wednesday, March 24, 1999 3:32 PM
> To: sql
> Subject: RE: [SQL] Use of 'default TEXT now()'
> 
> 
> Hi Jackson:
> 
> On Wed, 24 Mar 1999, Jackson, DeJuan wrote:
> 
> > Use:
> >   fecha_obs	datetime default datetime(now())
> > The use of TEXT was for:
> >   fecha_obs datetime default text 'now'
> > which will also do what you want (I believe, haven't tried it).
> > 	-DEJ
> 
> Where can I get some help about this subject?
> 
> TIA,
> 
> Roberto Andrade Fonseca 
> 

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

Date: Wed, 24 Mar 1999 16:59:23 -0500 (GMT+5)
From: Ingrith Andrea Correa Vargas <i-correa@uniandes.edu.co>
Subject: types and subtypes

hello all!

I want to simulate objects with Postgres and I would like to know if it's
possible to create a type (like address: t_address) and then to create
another type (like customer) with an attribute of type t_address.

And I have a second question:
Can I make a "reference" to an object of an existing type?!

These are features provided by the SQL3 standar, I want to know if it's
possible to implement it with Postgres.

Can somebody help to me?!

Thank you in advance,



//Ingrith Correa :)

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

Date: Wed, 24 Mar 1999 16:27:02 -0600
From: "Jackson, DeJuan" <djackson@cpsgroup.com>
Subject: RE: [SQL] types and subtypes

> hello all!
> 
> I want to simulate objects with Postgres and I would like to 
> know if it's
> possible to create a type (like address: t_address) and then to create
> another type (like customer) with an attribute of type t_address.
Yes, you can.  

> And I have a second question:
> Can I make a "reference" to an object of an existing type?!
If what you mean here is a c-style typedef no not really but you can use
inheritance.
If what you mean is another name by which to refer to a table or row then
the answer depend complete upon the context.  In rules tables are already
referenced as new, old, current.  In pl/pgsql you can create references
until your hearts content.

> These are features provided by the SQL3 standar, I want to 
> know if it's
> possible to implement it with Postgres.
PostgreSQL isn't fully SQL3 compliant, but it's getting there.  

> Can somebody help to me?!
That depends upon your problem, and you must remember that we are not
psychiatrist, plastic-surgeons, or magicians but we'll do our best.

> Thank you in advance,
> //Ingrith Correa :)
You're welcome in behind,
	-DEJ

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

Date: Thu, 25 Mar 1999 06:13:06 +0300 (MSK)
From: Oleg Bartunov <oleg@sai.msu.su>
Subject: Help with query

Hi,

I have query
 select source,max(up_date),count(*) from flats group by source
which gets me
source                    |       max|count
- --------------------------+----------+-----

where source is a company name, max(up_date) - the last update,
count  - total number of items for giver company

but I need  

source                    |       max| count_updates
- --------------------------+----------+-----

where count_updates is a number of *last updated* items for given company ?

How I can do this with SQL ?

	Best regards,

		Oleg

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

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

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Undeliverable Message
@ 1999-05-24 04:35  MAILER-DAEMON@mail1.sfr.fr
  0 siblings, 0 replies; 5+ messages in thread

From: MAILER-DAEMON@mail1.sfr.fr @ 1999-05-24 04:35 UTC (permalink / raw)
  To: pgsql-sql@hub.org

To:            ISMTP@SFRA0046@Servers[<pgsql-sql-digest@hub.org>]
Cc:            
Subject:       pgsql-sql-digest V1 #238

Message not delivered to recipients below.  Press F1 for help with VNM
error codes.               

	VNM3043:  DE_VOLDER Fabrice@ATR_EXPL_LYON1@SFR_DO_CNTR_EST



VNM3043 -- MAILBOX IS FULL

   The message cannot be delivered because the
   recipient's mailbox contains the maximum number of 
   messages, as set by the system administrator.  The
   recipient must delete some messages before any
   other messages can be delivered.
    The maximum message limit for a user's mailbox is 
   10,000.  The default message limit is 1000 messages.  
   Administrators can set message limits using the 
   Mailbox  Settings function available in the 
   Manage User menu  (MUSER). 

   When a user's mailbox reaches the limit, the 
   user must delete some of the messages before 
   the mailbox can accept any more incoming messages.

----------------------  Original Message Follows  ----------------------


pgsql-sql-digest         Sunday, May 23 1999         Volume 01 : Number 238



Index:

Re: [SQL] Problems with refint.so and 6.5 Beta 

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

Date: Sun, 23 May 1999 21:58:34 +0200
From: "Michael Scheuner" <scheuner@timeworld.com>
Subject: Re: [SQL] Problems with refint.so and 6.5 Beta 

Hi !



>"Michael Scheuner" <scheuner@timeworld.com> writes:
>> ERROR: check_primary_key: even number of arguments shoud be specified
>
>That was a known bug at the time the last beta release was made.
>It's fixed.  Grab current sources instead --- either the nightly
>snapshot tarfile, or get 'em directly from the CVS server.
>
> regards, tom lane
>

Thankyou, i installed an actual snapshot and now it works fine!

Michael Scheuner

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

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Undeliverable Message
@ 1999-05-25 07:21  MAILER-DAEMON@mail1.sfr.fr
  0 siblings, 0 replies; 5+ messages in thread

From: MAILER-DAEMON@mail1.sfr.fr @ 1999-05-25 07:21 UTC (permalink / raw)
  To: pgsql-sql@hub.org

To:            ISMTP@SFRA0046@Servers[<pgsql-sql-digest@hub.org>]
Cc:            
Subject:       pgsql-sql-digest V1 #239

Message not delivered to recipients below.  Press F1 for help with VNM
error codes.               

	VNM3043:  DE_VOLDER Fabrice@ATR_EXPL_LYON1@SFR_DO_CNTR_EST



VNM3043 -- MAILBOX IS FULL

   The message cannot be delivered because the
   recipient's mailbox contains the maximum number of 
   messages, as set by the system administrator.  The
   recipient must delete some messages before any
   other messages can be delivered.
    The maximum message limit for a user's mailbox is 
   10,000.  The default message limit is 1000 messages.  
   Administrators can set message limits using the 
   Mailbox  Settings function available in the 
   Manage User menu  (MUSER). 

   When a user's mailbox reaches the limit, the 
   user must delete some of the messages before 
   the mailbox can accept any more incoming messages.

----------------------  Original Message Follows  ----------------------


pgsql-sql-digest         Monday, May 24 1999         Volume 01 : Number 239



Index:

Undeliverable Message
[none]
RE: [SQL] Problems with refint.so and 6.5 Beta
Re: [SQL] Re: pgsql-sql-digest V1 #225
Update of two tables in a trigger
Fatal process interaction
Re: [SQL] Re: pgsql-sql-digest V1 #225
index on int8 in PG 6.4.2
Re: [SQL] Re: pgsql-sql-digest V1 #225 
Re: [SQL] Fatal process interaction 
Re: [SQL] index on int8 in PG 6.4.2 

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

Date: Mon, 24 May 99 5:35:26 +0100
From: <MAILER-DAEMON@mail1.sfr.fr>
Subject: Undeliverable Message

To:            ISMTP@SFRA0046@Servers[<pgsql-sql-digest@hub.org>]
Cc:            
Subject:       pgsql-sql-digest V1 #238

Message not delivered to recipients below.  Press F1 for help with VNM
error codes.               

	VNM3043:  DE_VOLDER Fabrice@ATR_EXPL_LYON1@SFR_DO_CNTR_EST



VNM3043 -- MAILBOX IS FULL

   The message cannot be delivered because the
   recipient's mailbox contains the maximum number of 
   messages, as set by the system administrator.  The
   recipient must delete some messages before any
   other messages can be delivered.
    The maximum message limit for a user's mailbox is 
   10,000.  The default message limit is 1000 messages.  
   Administrators can set message limits using the 
   Mailbox  Settings function available in the 
   Manage User menu  (MUSER). 

   When a user's mailbox reaches the limit, the 
   user must delete some of the messages before 
   the mailbox can accept any more incoming messages.

- ----------------------  Original Message Follows  ----------------------


pgsql-sql-digest         Sunday, May 23 1999         Volume 01 : Number 238



Index:

Re: [SQL] Problems with refint.so and 6.5 Beta 

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

Date: Sun, 23 May 1999 21:58:34 +0200
From: "Michael Scheuner" <scheuner@timeworld.com>
Subject: Re: [SQL] Problems with refint.so and 6.5 Beta 

Hi !



>"Michael Scheuner" <scheuner@timeworld.com> writes:
>> ERROR: check_primary_key: even number of arguments shoud be specified
>
>That was a known bug at the time the last beta release was made.
>It's fixed.  Grab current sources instead --- either the nightly
>snapshot tarfile, or get 'em directly from the CVS server.
>
> regards, tom lane
>

Thankyou, i installed an actual snapshot and now it works fine!

Michael Scheuner

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

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

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

Date: Mon, 24 May 1999 13:42:31 -0400 (EDT)
From: Subject: [none]

  I want to fill an array in my database with the content of a file. I
know the number of value I have to add (written in the begining of the
file), but I'd like not to load all the file in memory. So I can't put the
value within the INSERT command. Is it possible to insert an array with a
fixed size and to fill at after?

  I have tried things like :
CREATE TABLE timeseries (seriesid int4 PRIMARY KEY,
			 samples float8 []) ;
INSERT INTO timeseries (seriesid, samples[1000]) VALUES (1, '{}') ;
UPDATE timeseries SET samples[1]^RE seriesid
   -> ERROR : array_set: array bound exceeded

  Any help?

  Guillaume.

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

Date: Mon, 24 May 1999 13:53:17 -0500
From: "Jackson, DeJuan" <djackson@cpsgroup.com>
Subject: RE: [SQL] Problems with refint.so and 6.5 Beta

This has been fixed in the CVS.
	-DEJ

> -----Original Message-----
> Hi,
> 
> i just installed the 6.5 Beta (on a fresh Suse 6.0 System) and it works
> fine, but i don't get refint.so (from the contrib/spi-Diretory of
> 6.5-tar-gz) working with it. Running the refint.example, Postgres shows
> me:
> 
> ERROR: check_primary_key: even number of arguments shoud be specified
> 
> fort all the inserts : "INSERT INTO B VALUES (1)" thru the last "INSERT
> INTO
> C VALUES (30)"
> 
> Can somebody help me ?
> 
> Michael Scheuner
> 
> 

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

Date: Mon, 24 May 1999 14:04:58 -0500
From: "Steven M. Wheeler" <swheeler@sabre.com>
Subject: Re: [SQL] Re: pgsql-sql-digest V1 #225

Tom,

I have a little more information.

Version of Postgres: 6.4.2

Regarding your request for a backtrace, I recompiled with debugging and
profiling options on.  Subsequently I have attached the debugger and
interrupted the backend a number of times.  I keep coming up in mcount() and
a couple of hash functions.  BTW: I had let the query run for over 50+ hours
without it returning a value.  The offending SQL: select count(*) from
currnt;  Is there something more definitive you would like me to do?

Using the syntax you suggested, "statdate >works like a champ.  So does using "between" as suggested by another user.
However, issuing "select count(*) form currnt where statdate still returns an error telling me that I must use a cast since there is more
than one possible function.

A funny note.  Issuing the SQL: select count(*) from currnt where statdate
>00:07:30:81, nice and fast) and returns the count 2956630.  The laugh is
that this date range covers everything in the database, the same as the
above offending syntax should.

Thanks for the assist!
- --
Steven M. Wheeler
UNIX Engineering
The SABRE Group
(918) 292-4119
(918) 292-4165 FAX

Tom Lane wrote:

> "Steven M. Wheeler" <swheeler@sabre.com> writes:
> > First my configuration:
>
> Er, what version of Postgres?
>
> > Using psql in interactive mode, I issue "select count(*) from
> > currnt;".  The postmaster starts to read data in, as seen in vmstat by
> > the bi stat jumping sharply.  Very rapidly, CPU goes to 0% idle,
> > postmaster is using 99.4%, bi stat runs about 12K for 60-80 seconds,
> > and then goes to 0, and everything stays there.  I never get a return
> > and the system stays maxed.  When the row count in this table was
> > below 2M, I would get a return count.
>
> This is odd, all right.  Can you attach to the backend with a debugger
> and see where it is (get a backtrace)?  It sounds like the backend's
> going into an infinite loop, but I've never seen that sort of behavior
> on such a simple query...
>
> > Running psql in interactive mode, I issue "select * from currnt where
> > cast(statdate as text) like '03-%-1999);".  This runs for quite
> > awhile, eats into swap to the tune of 670MB+ and then bombs out with a
> > palloc error.
>
> This is a known problem that I hope to see fixed in 6.6, but it will
> not be fixed for 6.5.   An expression involving any datatype more
> complex than integers consumes temporary memory for each evaluation,
> and currently the backend doesn't try to recover that memory until
> end of statement.  So if you process enough tuples in one statement,
> you run out of memory :-(.  We know how to fix this but it's too large
> a change to make at this late stage of the 6.5 release cycle.
>
> It sounds like this expression is consuming several hundred bytes per
> iteration, which is more than I would've expected --- a couple dozen
> bytes for the temporary text value should've been enough.  It could be
> that the "like" operator itself is wasting memory internally, which
> might be fixable now; I'll look into it.
>
> In the meantime, you might be able to work around the problem by using
> a less memory-hungry form of the WHERE expression --- for example, the
> above query could probably be written without any text temporary as
>         statdate >> This way only requires boolean intermediate values, which don't require
> extra memory to be allocated.  (Haven't actually tried it, but I think
> it should work.)
>
> > Am I trying to run too large a DB?
>
> No; there are people running Postgres DBs with individual tables larger
> than 2Gb without trouble.  But it does depend on working around some of
> the known limitations :-(.  The developers plan to address these
> limitations in future releases, but there are only so many hours in the
> day...
>
>                         regards, tom lane

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

Date: Mon, 24 May 1999 20:59:56 +0200 (MEST)
From: Christian Ullrich <ChrUllrich@gmx.de>
Subject: Update of two tables in a trigger

Hello world,

I am currently playing with postgres to learn a bit about it. I
created a database to contain information about manpages i printed.
(I am going to print a lot of them, and I don't want to do it
twice).

For some reason, I split the output in two tables, one to contain
the command and the section number, one for the number of pages and
sheets of paper, and the date I printed it.

I connected these two tables by creating a sequence, using the
nextval() as default value for a field in the first table and the
currval() as default in the second.

I also created a view (named v_manpages) that joins these two tables
together, based on the mentioned fields.

After failing to get it to work with rules, I am now trying to use
triggers and PL/pgSQL-functions for updating the view, but I am
experiencing rather strange things. The inserting of rows works
fine, but deleting...

Here's my problem:

manpagecmd       |section|pages|sheets|      when
- ----------+-------+-----+------+----------
procmailsc|5      |    5|     3|1999-05-24
sox       |1      |   11|     6|1999-05-24
chgrp     |1      |    1|     1|1999-05-24
procmailrc|5      |   14|     7|1999-05-24  

manpageINSERT 20036 1   
manpageINSERT 20039 1

manpagecmd       |section|pages|sheets|      when
- ----------+-------+-----+------+----------
procmailsc|5      |    5|     3|1999-05-24
sox       |1      |   11|     6|1999-05-24
chgrp     |1      |    1|     1|1999-05-24
procmailrc|5      |   14|     7|1999-05-24      
update    |sql    |    1|     1|1999-05-24
test      |1      |    2|     1|1999-05-24     

manpageDELETE 1
manpagecmd       |section|pages|sheets|      when
- ----------+-------+-----+------+----------
procmailsc|5      |    5|     3|1999-05-24
sox       |1      |   11|     6|1999-05-24
chgrp     |1      |    1|     1|1999-05-24
procmailrc|5      |   14|     7|1999-05-24     

Two are gone, sox is still present.

manpageDELETE 0    

Now I can't delete anything more.

These are my tables, triggers and functions:

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

create table manpages (cmd text, section varchar(5), prnr int4
unique default nextval('seq_printnr'));

create table info (prnr int4 unique default currval('seq_printnr'),
pages int2, sheets int2, when date default current_date);

create view v_manpages as select cmd,section,pages,sheets,when from
manpages,info where manpages.prnr 
create function get_prnrfromcmd(text) returns int4 as '
select prnr from manpages where cmd ' language 'sql';

create function tp_v_manpages () returns opaque as '
declare
	printnr int4;
begin
	if TG_OP 		printnr 		if printnr ISNULL then
			return NULL;
		end if;
		
		delete from manpages where prnr 		delete from info where prnr 		return OLD;
	end if;
	
	if TG_OP 		insert into manpages values (NEW.cmd,
		NEW.section);

		printnr :		if printnr ISNULL then
			return NULL;
		end if;
	
		insert into info values (printnr, NEW.pages,
		NEW.sheets);
		return NEW;
	end if;
	return NULL;
end;
' language 'plpgsql';

create trigger t_v_manpages
before insert or delete on v_manpages for each row
execute procedure tp_v_manpages();

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

PostgreSQL version is 6.4.2.

What mistake did I make?

(If this mail is too long, or if there is information missing,
or if I am a complete fool, I'm very sorry. But please help me.)

- -- 
Christian Ullrich

(I am a student, I live in Germany)

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

Date: Mon, 24 May 1999 14:41:23 -0500
From: "Steven M. Wheeler" <swheeler@sabre.com>
Subject: Fatal process interaction

The situation:
In xterm #1: psql running, connected to database pssdb, executing an SQL select
statement.

In xterm #2: A shell script is run that pipes another select statement throug psql.

When the select statement in xterm #2 completes, the select statement being executed
in xterm #1 aborts and the following error message is output:

NOTICE:  Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend died abnormally and
possibly corrupted shared memory.
        I have rolled back the current transaction and am going to terminate your
database system connection and exit.
        Please reconnect to the database system and repeat your query.
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally before or while
processing the request.
We have lost the connection to the backend, so further processing is impossible.
Terminating.

This problem is very repeatable and does not seem to depend on which UID is running
which session.  They can both be postgres, or me (smw) or one of each.  The one
common factor appears to be that psql is involved in both.  If psql is run in
interactive mode in both windows, then \q is issued in one of the windows, both psql
sessions will terminate.

Any ideas?
- --
Steven M. Wheeler
UNIX Engineering
The SABRE Group
(918) 292-4119
(918) 292-4165 FAX

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

Date: Mon, 24 May 1999 17:25:33 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Subject: Re: [SQL] Re: pgsql-sql-digest V1 #225

> Tom,
> 
> I have a little more information.
> 
> Version of Postgres: 6.4.2
> 
> Regarding your request for a backtrace, I recompiled with debugging and
> profiling options on.  Subsequently I have attached the debugger and
> interrupted the backend a number of times.  I keep coming up in mcount() and
> a couple of hash functions.  BTW: I had let the query run for over 50+ hours
> without it returning a value.  The offending SQL: select count(*) from
> currnt;  Is there something more definitive you would like me to do?
> 
> Using the syntax you suggested, "statdate >> works like a champ.  So does using "between" as suggested by another user.
> However, issuing "select count(*) form currnt where statdate > still returns an error telling me that I must use a cast since there is more
> than one possible function.

I hate to say this but 6.5 is just about to be released, and has major
hashjoin fixes that may fix this problem.  Can you try the most recent
snapshot on ftp.postgresql.org.

- -- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

Date: Tue, 25 May 1999 01:46:29 +0200 (MET DST)
From: Mirek Budzanowski <mirekb@tcs.uni.wroc.pl>
Subject: index on int8 in PG 6.4.2

Hello,

I got a table createtd with command:
create table test (t int8);

When I try:
create index i_test on test(t);
I get error:
ERROR:  Can't find a default operator class for type 20.

How can I define a default operator for int8???
When I am doing select * from test where t > 4, etc.
it works. 

Anyone have any idea how to define a index ont int8 field?

Mirek

- --
Mirek Budzanowski; P.O. box 1739; 51-161 Wroclaw 8; Poland
Tel. +48 602 306674     http://www.tcs.uni.wroc.pl/~mirekb

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

Date: Mon, 24 May 1999 19:59:32 -0400
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] Re: pgsql-sql-digest V1 #225 

"Steven M. Wheeler" <swheeler@sabre.com> writes:
> Regarding your request for a backtrace, I recompiled with debugging and
> profiling options on.  Subsequently I have attached the debugger and
> interrupted the backend a number of times.  I keep coming up in mcount() and
> a couple of hash functions.  BTW: I had let the query run for over 50+ hours
> without it returning a value.  The offending SQL: select count(*) from
> currnt;  Is there something more definitive you would like me to do?

If you could interrupt the backend a few times and provide a full
backtrace (gdb "bt" command) each time, we could maybe form a picture of
what the heck it's doing.  This report does seem *very* odd, especially
your discovery that adding a "where" clause speeds it up.  (That'd be
fine if the where clause eliminated many rows, but since it doesn't...)

Also, it would be useful to know what "explain" says about how the query
will be executed.  I'd expect an index scan for the "select ... where"
case, and a plain sequential scan for the case without where; if it's
doing something else that would be important to know.

One more thing --- exactly what is the declaration of the currnt table,
and of its indexes if any?

BTW, I concur with Bruce's suggestion to try a recent 6.5 snapshot.
I don't see any hashjoin going on here, but it is true that we've
squashed a remarkable number of bugs between 6.4.* and 6.5.  Perhaps
you are hitting one of them.

			regards, tom lane

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

Date: Mon, 24 May 1999 20:33:56 -0400
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] Fatal process interaction 

"Steven M. Wheeler" <swheeler@sabre.com> writes:
> If psql is run in interactive mode in both windows, then \q is issued
> in one of the windows, both psql sessions will terminate.

Wow, that's pretty bizarre.  What platform are you on?  I think the FAQ
mentions that problems like this have been seen on systems where SysV
semaphore support wasn't configured or didn't work right ...

			regards, tom lane

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

Date: Mon, 24 May 1999 20:38:05 -0400
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] index on int8 in PG 6.4.2 

Mirek Budzanowski <mirekb@tcs.uni.wroc.pl> writes:
> Anyone have any idea how to define a index ont int8 field?

Postgres 6.4 doesn't have support for indexes on int8.

In theory you could add it for yourself (there is some documentation
about index operators in the Developer's Guide) but it's not a simple
job.  You would probably be better off to wait for 6.5, which does
have int8 index support...

			regards, tom lane

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

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Undeliverable Message
@ 1999-07-09 04:34  MAILER-DAEMON@mail1.sfr.fr
  0 siblings, 0 replies; 5+ messages in thread

From: MAILER-DAEMON@mail1.sfr.fr @ 1999-07-09 04:34 UTC (permalink / raw)
  To: pgsql-sql@hub.org

To:            ISMTP@SFRA0046@Servers[<pgsql-sql-digest@hub.org>]
Cc:            
Subject:       pgsql-sql-digest V1 #284

Message not delivered to recipients below.  Press F1 for help with VNM
error codes.               

	VNM3043:  DE_VOLDER Fabrice@ATR_EXPL_LYON1@SFR_DO_CNTR_EST



VNM3043 -- MAILBOX IS FULL

   The message cannot be delivered because the
   recipient's mailbox contains the maximum number of 
   messages, as set by the system administrator.  The
   recipient must delete some messages before any
   other messages can be delivered.
    The maximum message limit for a user's mailbox is 
   10,000.  The default message limit is 1000 messages.  
   Administrators can set message limits using the 
   Mailbox  Settings function available in the 
   Manage User menu  (MUSER). 

   When a user's mailbox reaches the limit, the 
   user must delete some of the messages before 
   the mailbox can accept any more incoming messages.

----------------------  Original Message Follows  ----------------------


pgsql-sql-digest        Thursday, July 8 1999        Volume 01 : Number 284



Index:

Re: [SQL] Index on Type Numeric
Re: [SQL] Good Optimization
concatenation varchar and float
Re: [SQL] Good Optimization
Re: [SQL] Good Optimization
Re: [SQL] Good Optimization
unsubscribe
Re: [SQL] Good Optimization 
Re: [SQL] Good Optimization
Re: [SQL] Good Optimization
RewriteDefine.c : Rule size
Re: [SQL] RewriteDefine.c : Rule size
uncorrelated subqueries
Re: [SQL] uncorrelated subqueries 
Referential Integrity
offtopic: odbc C frontend for linux?
Re: [SQL] Referential Integrity
Re: [SQL] offtopic: odbc C frontend for linux?
Re: [SQL] offtopic: odbc C frontend for linux?
subscribe

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

Date: Wed, 7 Jul 1999 23:04:00 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Subject: Re: [SQL] Index on Type Numeric

This will have to wait for 6.6.

> Using version 6.5, when I try to create an index on a table using columns that have data types of "numeric", I get an error message;
> can't find default operator class for type 1700
> 
> What do I do now?
> 
> Steven Pennie
> Application Programmer/Analyst III
> KDOT Materials & Research Center
> (785) 291-3863 - Fax (785) 296-2526
> 
> 
> 


- -- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

Date: Thu, 08 Jul 1999 10:09:39 +0200
From: Patrik Kudo <kudo@partitur.se>
Subject: Re: [SQL] Good Optimization

Bruce Momjian wrote:
> 
> Added to TODO:
> 
>         * In WHERE x:ND x
I don't know if I'm way off, but wouldn't removing "xperformance further?

- -- 
ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol
KEnns det oklart? Froga po!

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

Date: Thu, 08 Jul 1999 10:27:43 +0200
From: Eric BASIER <basier@ipgp.jussieu.fr>
Subject: concatenation varchar and float

Hi all,

Does anyone known fi it's possible to make a concatenation of varchar
and float.
I try the request bellow and I don't known how to correct.
seedending)) || ('" "' || latitude)  from station;
ERROR:  There is more than one possible operator '||' for types
'unknown' and 'float8'
        You will have to retype this query using an explicit cast
seedending)) || ('" "'::text || latitude)  from station;
ERROR:  There is more than one possible operator '||' for types 'text'
and 'float8'
        You will have to retype this query using an explicit cast
seedending)) || ('" "'::text || latitude::float8) from station;
ERROR:  There is more than one possible operator '||' for types 'text'
and 'float8'
        You will have to retype this query using an explicit cast
seed
Table    +----------------------------------+----------------------------------+-------+

|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+

| network                          | varchar() not null
|     2 |
| station                          | varchar() not null
|     5 |
| begining                         | datetime not null
|     8 |
| ending                           | datetime
|     8 |
| modified                         | datetime
|     8 |
| latitude                         | float8
|     8 |
| lat_prec                         | int4
|     4 |
| longitude                        | float8
|     8 |
| long_prec                        | int4
|     4 |
| coord_type                       | int4
|     4 |
| coord_map                        | varchar()
|   100 |
| elevation                        | float8
|     8 |

I work with postgres 6.4.2

- --
Basier Eric  e-mail : basier@ipgp.jussieu.fr
http://geoscope.ipgp.jussieu.fr
IPGP Observatoires Sismologie-Volcanologie
4,Place Jussieu 75005 Paris Tour 24-14 4eme Etage Tel 01 44 27 38 96

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

Date: Thu, 8 Jul 1999 11:04:26 +0200 (MET DST)
From: wieck@debis.com (Jan Wieck)
Subject: Re: [SQL] Good Optimization

>
> Bruce Momjian wrote:
> > 
> > Added to TODO:
> > 
> >         * In WHERE x=3 AND x=y, add y=3
>
> I don't know if I'm way off, but wouldn't removing "x=y" improve
> performance further?

    Maybe  in  the simple case above. But it will probably change
    the result set if someone issues

        SELECT a.x, b.y FROM a, b WHERE a.x >                                    AND a.x <                                    AND a.x 
    which should then get rewritten into

        SELECT a.x, b.y FROM a, b WHERE a.x >                                    AND a.x <                                    AND a.x                                     AND b.x >                                    AND b.x <
    This time the "a.x 
    IMHO we're improving optimization more and more on  the  cost
    of  query  parse/rewrite/optimize/plan time. Thus performance
    of statements that EXECUTE fast slows  down  more  and  more.
    Isn't   it   time   to   think   about  some  (maybe  shared)
    "parsetree->plan" cache that provides ready to use  plans  if
    only Const values have changed?


Jan

- --

## It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#
------------------------------

Date: Thu, 08 Jul 1999 11:23:14 +0200
From: Patrik Kudo <kudo@partitur.se>
Subject: Re: [SQL] Good Optimization

Jan Wieck wrote:
> 
> >
> > Bruce Momjian wrote:
> > > 
> > > Added to TODO:
> > > 
> > >         * In WHERE x=3 AND x=y, add y=3
> >
> > I don't know if I'm way off, but wouldn't removing "x=y" improve
> > performance further?
> 
>     Maybe  in  the simple case above. But it will probably change
>     the result set if someone issues
> 
>         SELECT a.x, b.y FROM a, b WHERE a.x >>                                     AND a.x <>                                     AND a.x > 
>     which should then get rewritten into
> 
>         SELECT a.x, b.y FROM a, b WHERE a.x >>                                     AND a.x <>                                     AND a.x >                                     AND b.x >>                                     AND b.x <> 
>     This time the "a.x 
Ouch... didn't think that far. However, if an cache is implemented
it might be worth the extra overhead to optimize special cases like
the one I commented on. Since most systems use a fairly limited set
of queries (I suppose...) most of the queries will be cached quite
fast.

BTW, I just love the work all of you are doing! Postgres is awsome!

/Kudo

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

Date: Thu, 8 Jul 1999 12:24:20 +0200 (MET DST)
From: wieck@debis.com (Jan Wieck)
Subject: Re: [SQL] Good Optimization

Patrik Kudo wrote:

> Jan Wieck wrote:
> >
> >     This time the "a.x >
> Ouch... didn't think that far. However, if an cache is implemented
> it might be worth the extra overhead to optimize special cases like
> the one I commented on. Since most systems use a fairly limited set
> of queries (I suppose...) most of the queries will be cached quite
> fast.

    Exactly  the  fact  that  most  system  use  a limited set of
    queries made me think that a shared query->plan  cache,  that
    in  the best of all worlds isn't lost on backend termination,
    is worth the efford. And it would lower the  need  of  client
    side prepared execution plans as discussed sometimes.


Jan

- --

## It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#
------------------------------

Date: Thu, 08 Jul 1999 13:07:19 +0200
From: Seb <unixseb@club-internet.fr>
Subject: unsubscribe

unsubscribe

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

Date: Thu, 08 Jul 1999 11:01:17 -0400
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] Good Optimization 

wieck@debis.com (Jan Wieck) writes:
>     IMHO we're improving optimization more and more on  the  cost
>     of  query  parse/rewrite/optimize/plan time. Thus performance
>     of statements that EXECUTE fast slows  down  more  and  more.
>     Isn't   it   time   to   think   about  some  (maybe  shared)
>     "parsetree->plan" cache that provides ready to use  plans  if
>     only Const values have changed?

Easier said than done, because the plan chosen by the optimizer may
well depend on the values of the constants.  For example, in

	SELECT * FROM t1, t2 WHERE t1.a 
the optimizer will attempt to estimate the fraction of t1's rows that
will pass the restriction "t1.b < 12", and then it will choose the
type of join depending on how many rows it thinks there will be.
If we require plans to be chosen without dependence on the values of
constants, we will have to give up a great deal of optimization.

I do not object to letting the user specifically say PREPARE xyz...
and then using that prepared plan; there are plenty of times when
trading off planning time against getting a narrowly-tailored plan
is a useful thing to do.  But we mustn't pre-empt the user's choice.
Note also that in a PREPARE context, it is known which items are
substitutable parameters and which are plain constants, so some
amount of optimization can still go on.

			regards, tom lane

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

Date: Thu, 8 Jul 1999 11:40:18 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Subject: Re: [SQL] Good Optimization

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Bruce Momjian wrote:
> > 
> > Added to TODO:
> > 
> >         * In WHERE x:ND x> 
> I don't know if I'm way off, but wouldn't removing "x> performance further?

Let me rewrite it.  TODO list updated:

	* In WHERE tab1.x:ND tab1.x
- -- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

Date: Thu, 8 Jul 1999 11:48:04 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Subject: Re: [SQL] Good Optimization

>     IMHO we're improving optimization more and more on  the  cost
>     of  query  parse/rewrite/optimize/plan time. Thus performance
>     of statements that EXECUTE fast slows  down  more  and  more.
>     Isn't   it   time   to   think   about  some  (maybe  shared)
>     "parsetree->plan" cache that provides ready to use  plans  if
>     only Const values have changed?

TODO list has:

	* Cache most recent query plan(s?)

- -- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

Date: Thu, 08 Jul 1999 18:15:07 +0200
From: Olivier Ertz <ertz@illite.u-strasbg.fr>
Subject: RewriteDefine.c : Rule size

Hello,

	I'm using Postgres 6.4.2.
I want to create a view, but a message appear :
 ERROR:  DefineQueryRewrite: rule plan string too big.
What could be the solution to avoid the problem? In fact, I have 31 
columns in a table, and i want to make a view on this table (with
a line restrict I watched the RewriteDefine.c code and saw : 
 #define RULE_PLAN_SIZE BLCKSZ
I thought changing the value of this size, but I don't find the
place to modify the BLCKSZ value.
But, before doing the modification and recompile, I would like to
have your opinion on the problem...perhaps is there an easier way to
resolve the problem.

	Thank you for answering
	
- -- 
ERTZ Olivier - ertz@illite.u-strasbg.fr - Tel 03 88 35 85 87
- ------------------------------------------------------------
  Etudiant DESS Informatique   |   Stagiaire EOST/ULP-CGS
   Universite Louis Pasteur    |  Ecole et Observatoire des 
     STRASBOURG - France       |    Sciences de la Terre
- ------------------------------------------------------------
   World Wide Web : http://dess-info.u-strasbg.fr/~ertz 
- ------------------------------------------------------------

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

Date: Thu, 8 Jul 1999 18:30:16 +0200 (MET DST)
From: wieck@debis.com (Jan Wieck)
Subject: Re: [SQL] RewriteDefine.c : Rule size

>
> Hello,
>
>    I'm using Postgres 6.4.2.
> I want to create a view, but a message appear :
>  ERROR:  DefineQueryRewrite: rule plan string too big.
> What could be the solution to avoid the problem? In fact, I have 31
> columns in a table, and i want to make a view on this table (with
> a line restrict 
    That's a well known, long standing problem which is still not
    fixed in v6.5.  I'm planning to fix it for v6.6 - though that
    doesn't help you.

> I watched the RewriteDefine.c code and saw :
>  #define RULE_PLAN_SIZE BLCKSZ
> I thought changing the value of this size, but I don't find the
> place to modify the BLCKSZ value.
> But, before doing the modification and recompile, I would like to
> have your opinion on the problem...perhaps is there an easier way to
> resolve the problem.

    Never  did it, but the BLCKSZ is defined in include/config.h.
    That is a file generated from  config.h.in,  so  be  sure  to
    change it there and reconfigure before recompiling.

    And  if you change the blocksize, be sure to run initdb after
    because your databases will  not  be  binary  compatible  any
    more!


Jan

- --

## It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#
------------------------------

Date: Fri, 09 Jul 1999 02:08:18 -0500
From: Bruce Lowery <bruce.lowery@edventions.com>
Subject: uncorrelated subqueries

Hello,

  In v6.4.2 do uncorrelated subqueries get run on each iteration of the
outer loop?  For instance,

SELECT a
FROM     table1
WHERE  table1.b     AND   table1.c  IN ( SELECT d  FROM  table2 WHERE table2.e;

Does the subquery get run for each row from table 1 where table1.b is true?

Bruce Lowery
edventions.com

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

Date: Thu, 08 Jul 1999 15:30:06 -0400
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] uncorrelated subqueries 

Bruce Lowery <bruce.lowery@edventions.com> writes:
>   In v6.4.2 do uncorrelated subqueries get run on each iteration of the
> outer loop?

It looks like the particular example you give is done that way, but the
system does know about uncorrelated subqueries.  For example, using 6.5:

explain SELECT a FROM table1 WHERE table1.b3 AND
table1.c  NOTICE:  QUERY PLAN:
Seq Scan on table1  (costC.00 rowsth
  InitPlan
    ->  Seq Scan on table2  (costC.00 rowsth

explain SELECT a FROM table1 WHERE table1.b3 AND
table1.c  NOTICE:  QUERY PLAN:
Seq Scan on table1  (costC.00 rowsth
  SubPlan
    ->  Seq Scan on table2  (costC.00 rowsth

You can see that we get an "InitPlan" (ie, run once) for an uncorrelated
subquery but a "SubPlan" (repeat each time) for a correlated one.
Unfortunately, the case you care about is:

explain SELECT a FROM table1 WHERE table1.b3 AND
table1.c  IN ( SELECT d  FROM  table2 WHERE table2.eD);
NOTICE:  QUERY PLAN:
Seq Scan on table1  (costC.00 rowsth
  SubPlan
    ->  Seq Scan on table2  (costC.00 rowsth

The main problem that would have to be solved to convert this to
an InitPlan is what to do if the subselect returns a huge number
of tuples ... with the current implementation, since we scan the
tuples one at a time, there's no problem, but if we try to store
all the tuples we could run out of memory.

			regards, tom lane

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

Date: Thu, 8 Jul 1999 15:41:56 -0300
From: "Gilson Costa" <gpcosta@ruralsp.com.br>
Subject: Referential Integrity

I am a novice in PostgreSQL.
How can I impose referential integrity to my tables?

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

Date: Thu, 8 Jul 1999 22:36:31 +0200 (CEST)
From: Engard Ferenc <fery@pons.sote.hu>
Subject: offtopic: odbc C frontend for linux?

Hi all,

I need to connect to odbc data sources with a linux C prg. Can I do
it? (And how? ;-)

Thx:
Circum

PS: There is a tclodbc package, did anybody use that? Experiences?

 __  @
/  \    _   _                                           Engard Ferenc
l    | ( \ /  | | (\/)                      mailto:s-fery@kkt.sote.hu
\__/ | |   \_ \_/ I  I                    http://pons.sote.hu/~s-fery

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

Date: Thu, 08 Jul 1999 13:38:26 -0700
From: Steven Bradley <sbradley@llnl.gov>
Subject: Re: [SQL] Referential Integrity

PostgreSQL does not implement declarative referential integrity.  It lets
you specify foreign key constraints in table definitions, but does nothing
with them. As such, you have to resort to writing triggers to implement the
traditional RI concerns (e.g.: mandatory parent, cascade delete, etc...).






At 03:41 PM 7/8/99 -0300, you wrote:
>I am a novice in PostgreSQL.
>How can I impose referential integrity to my tables?
>
>
>
>


Steven Bradley
Lawrence Livermore National Laboratory
PO Box 808
Livermore, California 94550
(925) 423-2101       sbradley@llnl.gov

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

Date: Thu, 8 Jul 1999 22:53:06 -0700
From: "Stephen Boyle" <yd80@dial.pipex.com>
Subject: Re: [SQL] offtopic: odbc C frontend for linux?

I haven't done it but have read how to do this in 'The Linux Database p323 -
p366' - Fred Butzen and Dorothy Forbes ISBN 1-55828-491-5.

'a fine reference manual for ODBC....Lauri Savioja' apparently is available
@ www.cs.hut.fi/~las

also www.openlinksw.com for odbc driver/client.


HIH

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

Date: Thu, 8 Jul 1999 23:52:39 +0200 (MET DST)
From: wieck@debis.com (Jan Wieck)
Subject: Re: [SQL] offtopic: odbc C frontend for linux?

>
> Hi all,
>
> I need to connect to odbc data sources with a linux C prg. Can I do
> it? (And how? ;-)

    Not that offtopic IMHO.

    Sybase  offers  an ODBC client library (ctlib). This is AFAIK
    freely available for Linux (binary only and other OS versions
    are told to cost money).

    I  never  played  around with it, but I've read that it could
    also contact other ODBC datasources like MS SQL-Server. Might
    be worth looking at it.


Jan

- --

## It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#
------------------------------

Date: Fri, 9 Jul 1999 11:51:46 +0900
From: Soon-Suk Kim <master@golden21.net>
Subject: subscribe



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

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~1999-07-09 04:34 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-03 04:44 Undeliverable Message MAILER-DAEMON@mail1.sfr.fr
1999-03-25 04:57 Undeliverable Message MAILER-DAEMON@mail1.sfr.fr
1999-05-24 04:35 Undeliverable Message MAILER-DAEMON@mail1.sfr.fr
1999-05-25 07:21 Undeliverable Message MAILER-DAEMON@mail1.sfr.fr
1999-07-09 04:34 Undeliverable Message MAILER-DAEMON@mail1.sfr.fr

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox