Re: [ADMIN] Is there any eqvivalent or alternative to UPDATE(column)orCOLUMNS_UPDATED() of SQLServer2000

2002-12-27 Thread Klaus Sonnenleiter 
Sreedhar,

From looking at the documentation at postgresql.org, it seems like this syntax 
is not supported. The compatibility description in the create trigger docs 
indicates that BEFORE UPDATE OF col_name is not supported either which 
probably means that Postgres has no knowledge of the statement that fired the 
trigger.

Klaus

On Friday 27 December 2002 12:53, shreedhar wrote:
 Dear Momjian,

 I need solution for the problem stated in subject. (i.e.) any method to
 know whether a record is Previously updated  or not ?

 Sreedhar
 - Original Message -
 From: Bruce Momjian [EMAIL PROTECTED]
 To: shreedhar [EMAIL PROTECTED]
 Sent: Friday, December 27, 2002 10:41 AM
 Subject: Re: [ADMIN] Is there any eqvivalent or alternative to
 UPDATE(column)orCOLUMNS_UPDATED() of SQLServer2000

  Sure. As root, use the 'date' command to fix it.
 
  -
 -

 -

  shreedhar wrote:
   Sorry,
  
   Because it is some problem in my system. So, it has shown Fri, 15 Mar

 2002

   17:27:30 +0530.
  
   Kindly any body give solution to my problem.
  
   Sreedhar.
   - Original Message -
   From: Bruce Momjian [EMAIL PROTECTED]
   To: shreedhar [EMAIL PROTECTED]
   Sent: Thursday, December 26, 2002 11:03 PM
   Subject: Re: [ADMIN] Is there any eqvivalent or alternative to
   UPDATE(column)or COLUMNS_UPDATED() of SQLServer2000
  
Your email is dated:
   
Date: Fri, 15 Mar 2002 17:27:30 +0530
 
  -
 -
 
   -
  
shreedhar wrote:
 Is there any eqvivalent or alternative to the following IF
  
   UPDATE(column) or
  
 IF(COLUMNS_UPDATED()) of SQLServer2000.

 IF UPDATE (column)

 Tests for an INSERT or UPDATE action to a specified column and is

 not

   used
  
 with DELETE operations. More than one column can be specified.

 Because

   the
  
 table name is specified in the ON clause, do not include the table

 name

 before the column name in an IF UPDATE clause. To test for an
 INSERT

 or

 UPDATE action for more than one column, specify a separate
  
   UPDATE(column)
  
 clause following the first one. IF UPDATE will return the TRUE
 value

 in

 INSERT actions because the columns have either explicit values or
  
   implicit
  
 (NULL) values inserted.

 IF (COLUMNS_UPDATED())

 Tests, in an INSERT or UPDATE trigger only, whether the mentioned

 column

   or
  
 columns were inserted or updated. COLUMNS_UPDATED returns a

 varbinary

   bit
  
 pattern that indicates which columns in the table were inserted or
  
   updated.
  
 Thanks And Regards,

 Sreedhar





 Faith, faith, faith in ourselves, faith, faith in God, this is the
  
   secret
  
 of greatness.
 If you have faith in all the three hundred and thirty millions of

 your

 mythological gods,
 and in all the gods which foreigners have now and again introduced

 into

   your
  
 midst,
 and still have no faith in yourselves, there is no salvation for

 you. 

 (III. 190)



 ---(end of

 broadcast)---

 TIP 5: Have you checked our extensive FAQ?

 http://www.postgresql.org/users-lounge/docs/faq.html
   
--
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square,
Pennsylvania
  
   19073
 
  --
Bruce Momjian|  http://candle.pha.pa.us
[EMAIL PROTECTED]   |  (610) 359-1001
+  If your life is a hard drive, |  13 Roberts Road
+  Christ can be your backup.|  Newtown Square, Pennsylvania

 19073



 ---(end of broadcast)---
 TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[ADMIN] dbsize

2002-12-27 Thread Andreas Schmitz 

Hello *,

I don't know if dbsize a part of the admin business here. I couln't find any 
contact infos in the README or the source. I have a 7.3 on solaris 8. When I 
try to execute SELECT database_size('newsdb'); I get the following error 
message:

ERROR:  MemoryContextAlloc: invalid request size 1684144132

This started to happen when I increased memory sizes in the postgresql.conf.

regards

-andreas


-- 
Andreas Schmitz - Phone +49 201 8501 318
Cityweb-Technik-Service-Gesellschaft mbH
Friedrichstr. 12 - Fax +49 201 8501 104
45128 Essen - email [EMAIL PROTECTED]


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[ADMIN] now() and ::date

2002-12-27 Thread Ray Ontko 
Howdy,

I'm having a little trouble understanding the query optimizer
related to a timestamp with time zone column.  I have a table
called event with a not null column called event_date_time 
of type timestamp with time zone that has been recently analyzed.
There are about 500,000 rows in the table and about 10,000 distinct
values.

My basic questions are: 

  Why does now() disqualify use of the index?
  Why does ::date disqualify use of the index?

1) This works:

explain select count(*)
from event
where event_date_time = '2002-12-25'::timestamp with time zone - '1 month'::interval
and event_date_time  '2002-12-25'::timestamp with time zone
;

NOTICE:  QUERY PLAN:

Aggregate  (cost=4647.02..4647.02 rows=1 width=0)
  -  Index Scan using event_date_time on event  (cost=0.00..4643.95
rows=1227 w
idth=0)

EXPLAIN

2) This fails to use the index when I cast the literals as date.  Why?

explain select count(*)
from event
where event_date_time = '2002-12-25'::date - '1 month'::interval
and event_date_time  '2002-12-25'::date
;

NOTICE:  QUERY PLAN:

Aggregate  (cost=21479.33..21479.33 rows=1 width=0)
  -  Seq Scan on event  (cost=0.00..21337.66 rows=56665 width=0)

EXPLAIN

3) This fails to use the index when I try to use now() instead of
a literal date.  Why?

explain select count(*)
from event
where event_date_time = now()::timestamp with time zone - '1 month'::interval
and event_date_time  now()::timestamp with time zone
;

NOTICE:  QUERY PLAN:

Aggregate  (cost=21479.33..21479.33 rows=1 width=0)
  -  Seq Scan on event  (cost=0.00..21337.66 rows=56665 width=0)

EXPLAIN

Ray
--
Ray Ontko   [EMAIL PROTECTED]   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko  Co.   Software Consulting Services   http://www.ontko.com/

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])