Re: [GENERAL] trigger question

2000-06-27 Thread Tom Lane

mikeo <[EMAIL PROTECTED]> writes:
> in oracle, the triggers were smart enough to know not to reference
> an old value on insert in an "insert or update" trigger procedure,
> apparently.

> this is the original oracle trigger that works fine 
> with the same insert statement:

> CREATE OR REPLACE TRIGGER rates_hist_trigger
> before insert or update on rates
> for each row
>  WHEN (old.rt_valid <> 'P' or new.rt_valid not in ('Y','N'))

Hmm.  It sounds to me like Oracle treats the OLD fields as being NULL
if the context is INSERT, which is something we could certainly do at
the price of losing some error detection capability --- ie, if that
really had been a typo as I first thought, the system wouldn't flag it
for you.

Not sure which way is better.  Comments anyone?

regards, tom lane



Re: [GENERAL] trigger question

2000-06-27 Thread mikeo

At 10:33 AM 6/27/00 -0400, Tom Lane wrote:
>mikeo <[EMAIL PROTECTED]> writes:
>> CREATE function rates_hist_function()
>> returns opaque
>> as 'BEGIN
>>if ( old.rt_valid <> ''P'' or new.rt_valid not in
(''Y'',''N''))
>  
>
>> i get this error:
>> ERROR:  record old is unassigned yet
>
>> since this trigger is for both insert or update, why does it expect
>> the "OLD" value to already exist, as it would not for insert?
>
>Because you referenced it in the function code.  Am I missing something?
>
>   regards, tom lane
>

maybe.
in oracle, the triggers were smart enough to know not to reference
an old value on insert in an "insert or update" trigger procedure,
apparently.

this is the original oracle trigger that works fine 
with the same insert statement:

CREATE OR REPLACE TRIGGER rates_hist_trigger
before insert or update on rates
for each row
 WHEN (old.rt_valid <> 'P' or new.rt_valid not in ('Y','N'))
begin
insert into rates_hist
values
(:new.cut_id,:new.ct_key,:new.rtm_id,:new.rt_sell_factor,

:new.rt_sell_msg_cost,:new.rt_sell_init_sec,:new.rt_sell_init_cost,

:new.rt_sell_addl_sec,:new.rt_sell_addl_cost,:new.rt_buy_factor,

:new.rt_buy_msg_cost,:new.rt_buy_init_sec,:new.rt_buy_init_cost,

:new.rt_buy_addl_sec,:new.rt_buy_addl_cost,:new.rt_valid,:new.rse_id,
:new.wu_id, sysdate, :new.rt_usoc_def_factor
   );
end;
/

i can easily get around this using rules.  my main objective is to not have to
change too much code as we migrate over to postgres from oracle and that is
not
too much of a change.   

thanks,
   mikeo




Re: [GENERAL] Conversion from MS Access to Postgresql

2000-06-27 Thread davidb

Hi Mihai,

Microsoft products store false as (0) and true as
(-1) (Why? I don't know!).  Apparently storing a
(-1) requires more than bool provides.  So, you
can either edit all of your Access code so that it
interprets (1) as true (practically, I don't
recommend this), or you can migrate your yes/no
fields to int2.

David Boerwinkle

-Original Message-
From: Mihai Gheorghiu <[EMAIL PROTECTED]>
To: Stephen Davies <[EMAIL PROTECTED]>; G.L.Lim
<[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED]
<[EMAIL PROTECTED]>
Date: Tuesday, June 27, 2000 9:07 AM
Subject: Re: [GENERAL] Conversion from MS Access
to Postgresql


>I tried to export an Access Yes/No field to pgsql
boolean and got an error
>message.
>By default, Access (and the ODBC driver) exports
Yes/No to bpchar. However,
>I want to use bool.
>Any suggestions?
>Thanks,
>
>Mihai
>
>
>-Original Message-
>From: Stephen Davies <[EMAIL PROTECTED]>
>To: G.L.Lim <[EMAIL PROTECTED]>
>Cc: [EMAIL PROTECTED]
<[EMAIL PROTECTED]>
>Date: Monday, June 26, 2000 7:54 PM
>Subject: Re: [GENERAL] Conversion from MS Access
to Postgresql
>
>
>>G'day.
>>
>>Having just done it, I can confirm that
converting an Access database
>>to PostgreSQL is very easy.
>>
>>All I did was as follows:
>>
>>1. create an empty Postgresql database,
>>2. cretae an ODBC DSN on the Access machine
pointing at the empty
>>database,
>>3, fire up Access and Save/As/Export all tables
to the ODBC target.
>>
>>Job done:-))
>>
>>In fact, I had to do some massage to the results
as the export process
>>does not create indexes nor sequences and I
decidesd to change some of
>>the generated data types.
>>
>>This was also easy using the pgdump utility to
dump the data and the
>>definition for editing.
>>
>>Be aware that any column names that include
upper case letters in
>>Access will keep their capitalisation and
require quotes in Postgresql.
>>
>>HTH,
>>Stephen.
>>"G.L.Lim" <[EMAIL PROTECTED]>  wrote:
>>> Hi,
>>>
>>> I am curently using MS Access and would like
to convert my existing
>Access
>>> database to Postgresql database (maybe into
Postgresql dump file first if
>>> neccessary). Is there any utility or program
that can do that? Please
>>> advise.
>>>
>>> Thank you.
>>>
>>> Regards,
>>> Geok Leng
>>
>>
>>
>>
>>

>>Stephen Davies Consulting
[EMAIL PROTECTED]
>>Adelaide, South Australia.   Voice:
08-8177 1595
>>Computing & Network solutions.   Fax:
08-8177 0133
>>
>>
>




Re: [GENERAL] FATAL 1: Database dbname=template1 does not exist in pg_database

2000-06-27 Thread Carsten Huettl

Tom Lane schrieb:

> Read it again: the error message is complaining because you tried to
> access a database named "dbname=template1".  Better check the syntax
> on the connection subroutine you are using --- looks like a confusion
> between positional parameters and keyword-style connect strings...

In the meantime I figured out, was was going wrong with my
postgresql installation.
I made a big mistake configuring the config.inc.php3 file
There I had wrong entries for user and stduser. After correcting this
phpPGadmin work quite well.

C.



Re: [GENERAL] puzzled by the docs

2000-06-27 Thread Tom Lane

Hernan Gonzalez <[EMAIL PROTECTED]> writes:
> The PostgreSQL Administrator's Guide which appears on the web
> http://www.postgresql.org/docs/admin/index.html
> is fairly different from the one which is packed with
> the 7.0.2 distribution.

> Which is the good one?

The files appearing under http://www.postgresql.org/docs/ are a
snapshot of current development, not the docs that go with the
most recent release.  Any changes you see are work that will be
in the next major release (ie, 7.1).

We have been planning to rearrange the website so that the main
docs page shows the most recent release, and the development
snapshot appears someplace else, but I guess Vince hasn't got
round to it yet ...

regards, tom lane



Re: [GENERAL] How to dump from Postgre

2000-06-27 Thread Dale Anderson

The BitBucket on unix is /DEV/NULL, or the place where things will disappear.

>>> Herbert Liechti <[EMAIL PROTECTED]> 06/27/00 09:35AM >>>
Thomas Lockhart wrote:

> > Yeah, it just vanished. Not my fault. The bitbucket ate it.

And what is a bitbucket? My dictioniary knows nothing
about this word. Just for all the people who's  mother
tongue is not english. :-)

- Herbie






Re: [GENERAL] How to dump from Postgre

2000-06-27 Thread Herbert Liechti

Thomas Lockhart wrote:

> > Yeah, it just vanished. Not my fault. The bitbucket ate it.

And what is a bitbucket? My dictioniary knows nothing
about this word. Just for all the people who's  mother
tongue is not english. :-)

- Herbie





Re: [GENERAL] trigger question

2000-06-27 Thread Tom Lane

mikeo <[EMAIL PROTECTED]> writes:
> CREATE function rates_hist_function()
> returns opaque
> as 'BEGIN
>if ( old.rt_valid <> ''P'' or new.rt_valid not in (''Y'',''N''))
  

> i get this error:
> ERROR:  record old is unassigned yet

> since this trigger is for both insert or update, why does it expect
> the "OLD" value to already exist, as it would not for insert?

Because you referenced it in the function code.  Am I missing something?

regards, tom lane



[GENERAL] puzzled by the docs

2000-06-27 Thread Hernan Gonzalez

The PostgreSQL Administrator's Guide which appears on the web
http://www.postgresql.org/docs/admin/index.html
is fairly different from the one which is packed with
the 7.0.2 distribution.

Which is the good one?

I'm puzzled because the web version mentions the release 7.0.2
(the other one, doesn't). 
And the section 'Logging and Debuging' (in chap. 7 from the web version),  
mentions a 'postgres.conf'  configuration file, some settings that are
of interest for me (as 'LOG_PID' and 'LOG_TIMESTAMP') 
but the distributed doc, instead, speaks of the 'pg_option' file, 
and doesn't mention those settings...

Help! Am I missing something stupid?

Hernan Gonzalez
Buenos Aires, Argentina



Re: [GENERAL] Why isn't that null

2000-06-27 Thread Matthias Teege

Tom Lane <[EMAIL PROTECTED]> writes:

[...]

> > If I change the function statement as follows: 
> 
> > IF NEW.vkp = 0 THEN
> >... 
> > END IF;
> 
> > it works
> 
> Shouldn't that give a type error of some sort?  You didn't say
> what type vkp is, but if it's a string type then comparing it
> against a numeric shouldn't work IMHO.

no it isn't. It is a float8 type. I changed my program in
that way that the query includes NULL instead of ''.

Bis dann
Matthias


-- 
Matthias Teege -- [EMAIL PROTECTED] -- http://emugs.de
make world not war
PGP-Key auf Anfrage



Re: [GENERAL] Limit for an transaction

2000-06-27 Thread Martijn van Oosterhout

Matthias Teege wrote:
> 
> Moin,
> 
> ist there any limit for the length of an transaction? I'am
> using postgreSQL 6.5.1 with PHP and the apache log says:
> 
> NOTICE:  (transaction aborted): queries ignored until END

Usually that appears right after one of the queries you
did failed for some reason (usually a typo in the query).
Since the query didn't complete sucessfully it must be aborted,
and this it ignores all queries until END.
-- 
Martijn van Oosterhout <[EMAIL PROTECTED]>
http://cupid.suninternet.com/~kleptog/



Re: [GENERAL] Conversion from MS Access to Postgresql

2000-06-27 Thread Mihai Gheorghiu

I tried to export an Access Yes/No field to pgsql boolean and got an error
message.
By default, Access (and the ODBC driver) exports Yes/No to bpchar. However,
I want to use bool.
Any suggestions?
Thanks,

Mihai


-Original Message-
From: Stephen Davies <[EMAIL PROTECTED]>
To: G.L.Lim <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Monday, June 26, 2000 7:54 PM
Subject: Re: [GENERAL] Conversion from MS Access to Postgresql


>G'day.
>
>Having just done it, I can confirm that converting an Access database
>to PostgreSQL is very easy.
>
>All I did was as follows:
>
>1. create an empty Postgresql database,
>2. cretae an ODBC DSN on the Access machine pointing at the empty
>database,
>3, fire up Access and Save/As/Export all tables to the ODBC target.
>
>Job done:-))
>
>In fact, I had to do some massage to the results as the export process
>does not create indexes nor sequences and I decidesd to change some of
>the generated data types.
>
>This was also easy using the pgdump utility to dump the data and the
>definition for editing.
>
>Be aware that any column names that include upper case letters in
>Access will keep their capitalisation and require quotes in Postgresql.
>
>HTH,
>Stephen.
>"G.L.Lim" <[EMAIL PROTECTED]>  wrote:
>> Hi,
>>
>> I am curently using MS Access and would like to convert my existing
Access
>> database to Postgresql database (maybe into Postgresql dump file first if
>> neccessary). Is there any utility or program that can do that? Please
>> advise.
>>
>> Thank you.
>>
>> Regards,
>> Geok Leng
>
>
>
>
>
>Stephen Davies Consulting   [EMAIL PROTECTED]
>Adelaide, South Australia.   Voice: 08-8177 1595
>Computing & Network solutions.   Fax: 08-8177 0133
>
>




Re: [GENERAL] How to dump from Postgre

2000-06-27 Thread Thomas Lockhart

> Yeah, it just vanished. Not my fault. The bitbucket ate it.

Ah, so sorry. Use pg_dump.

Hope you find your docs soon ;)

 - Thomas



Re: [GENERAL] How to dump from Postgre

2000-06-27 Thread Poul L. Christiansen

Generally a lot of the questions here on the mailing lists are answered
in the documentation. So read the docs first ;-)

It's: "pg_dump databaseName > someFile.sql"

"Morten W. Petersen" wrote:

> >  The PostgreSQL documentation is invisible?
>
> Yeah, it just vanished. Not my fault. The bitbucket ate it.
>
> -Morten =)




Re: [GENERAL] DateTime fields

2000-06-27 Thread Karel Zak

 */

On Tue, 27 Jun 2000, Dale Anderson wrote:

> Here is am example of what I am doing.
> 
> danderso=# \d test
>Table "test"
>  Attribute |   Type| Modifier
> ---+---+--
>  time  | timestamp |
> 
> danderso=# insert into test values (to_timestamp('1200 PM JUN 27 2000','HHMI PM MON 
>DD '));
> INSERT 22825 1
> danderso=# select * from test;
>   time
> 
>  2000-06-28 00:00:00-05
> (1 row)
> 

And for me:

test=# select to_timestamp('1200 PM JUN 27 2000', 'HHMI PM MON DD ');
  to_timestamp

 2000-06-27 12:00:00+02

but don't worry, you are right, I have NEW FIXED version. I commit it 
tomorrow. 

(Tom wanted show you that standard date/time routines are right only.)

Karel




[GENERAL] BLOB DBI func() interface under postgres

2000-06-27 Thread Louis-David Mitterrand

Hello,

In DBD::Pg one can read (line 134):

$lobj_fd = $dbh->func($lobjId, $mode, 'lo_open');

But how is the LOB retrieved in the first place? If I pass the OID of an
existing LOB instance from a table the returned $lobj_fd is null. What
kind of $lobjId is one supposed to pass to this function to open a LOB?

The aim is to be able to read a LOB from a postgres DB without having to
lo_export the object to a file first. Can that be done with the
$dbh->func() interface? (using lo_open, lo_read, etc ..)

TIA

-- 
Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr

Hi, I am an alien .sig, and at the moment I am having sex to your
mind, by looking at your smile I can see that you like it.



Re: [GENERAL] DateTime fields

2000-06-27 Thread Dale Anderson

Here is am example of what I am doing.

danderso=# \d test
   Table "test"
 Attribute |   Type| Modifier
---+---+--
 time  | timestamp |

danderso=# insert into test values (to_timestamp('1200 PM JUN 27 2000','HHMI PM MON DD 
'));
INSERT 22825 1
danderso=# select * from test;
  time

 2000-06-28 00:00:00-05
(1 row)

danderso=#

Thanks,
Dale.

>>> Tom Lane <[EMAIL PROTECTED]> 06/26/00 05:41PM >>>
"Dale Anderson" <[EMAIL PROTECTED]> writes:
> I noticed that the DateTime fields and the to_timestamp function
> interpret 12 PM as 00:00 and 12 AM as 12:00.

Huh?

regression=# select '2000-06-12 12:00 am' ::datetime;
?column?

 2000-06-12 00:00:00-04
(1 row)

regression=# select '2000-06-12 12:00 pm' ::datetime;
?column?

 2000-06-12 12:00:00-04
(1 row)

Looks fine to me.
Could you be more precise about what you're trying to do?

regards, tom lane




[GENERAL] trigger question

2000-06-27 Thread mikeo

hi,
  i've created a function as follows:

drop function rates_hist_function();
CREATE function rates_hist_function()
returns opaque
as 'BEGIN
   if ( old.rt_valid <> ''P'' or new.rt_valid not in
(''Y'',''N''))
then
  new.rt_timestamp = now();
  insert into rates_hist values (
new.cut_id,new.ct_key,new.rtm_id,new.rt_sell_factor,

new.rt_sell_msg_cost,new.rt_sell_init_sec,new.rt_sell_init_cost,

new.rt_sell_addl_sec,new.rt_sell_addl_cost,new.rt_buy_factor,

new.rt_buy_msg_cost,new.rt_buy_init_sec,new.rt_buy_init_cost,

new.rt_buy_addl_sec,new.rt_buy_addl_cost,new.rt_valid,new.rse_id,
new.wu_id,new.rt_timestamp, new.rt_usoc_def_factor
);
   end if;
   return new;
END;'
language 'plpgsql';

and i call it from this trigger:

drop TRIGGER rates_hist_trigger on rates;
CREATE TRIGGER rates_hist_trigger
after insert or update on rates
for each row
execute procedure rates_hist_function();

when i attempt to insert a row into the rates table using this statement:

insert into rates(cut_id,ct_key,rt_valid,...,rt_timestamp,rt_usoc_def_factor) 
  values ('mikeo',123456,'x',...,now(),1.35);

i get this error:
ERROR:  record old is unassigned yet

since this trigger is for both insert or update, why does it expect the "OLD"
value to already exist, as it would not for insert?  second, is there a way 
to tell a function that it's inserting or updating, as in oracle's 
"if updating..."?


thanks,
mikeo



Re: [GENERAL] How to dump from Postgre

2000-06-27 Thread Morten W. Petersen

>  The PostgreSQL documentation is invisible?

Yeah, it just vanished. Not my fault. The bitbucket ate it.

-Morten =)




Re: [GENERAL] Transactions and web applications

2000-06-27 Thread brianb-pggeneral


Lincoln Yeoh writes:

> At 10:01 PM 26-06-2000 -0400, Michael Mayo wrote:
> >- Original Message -
> >From: "Lincoln Yeoh" <[EMAIL PROTECTED]>
> >> What are the recommended ways to do transactions in web applications?
> Let me illustrate the problem with an example:
> 
> page 1
> user logs in
> page 2
> user does A
> page 3
> user does B
> page 4
> user does C
> page 5
> user says, oops forget the whole thing, please rollback. Or user just says
> forget it and closes browser. Or browser crashes at page 4.

Typically, web applications do not need to store the intermediate results
in the database. You can store them as hidden form variables on the
in-between pages or as cookies on the browser. Only when all the data is
ready to be passed to the database do you need to worry about the
transaction, and then it's only done when the webserver handles that
particular page. 

This way, you limit your transaction to the time it takes to handle the
final "Submit" page.

If you're doing serious web/db stuff, you may want to look at the numerous
resources available at http://www.arsdigita.com/asj (formerly
http://photo.net/wtr/).

> Do those Java object thingies help for these problems?   

:-)

Brian
--
Brian Baquiran <[EMAIL PROTECTED]>
http://www.baquiran.com/ AIM: bbaquiran 
Work: (632)718   Home: (632)9227123



Re: [GENERAL] How to dump from Postgre

2000-06-27 Thread Karel Zak

On Tue, 27 Jun 2000, Morten W. Petersen wrote:

> How do you dump from the Postgre database?
> (i.e. as with MySQL, where you have mysqldump)

 The PostgreSQL documentation is invisible?

Karel




[GENERAL] How to dump from Postgre

2000-06-27 Thread Morten W. Petersen

How do you dump from the Postgre database?
(i.e. as with MySQL, where you have mysqldump)

-Morten




Re: [GENERAL] Why isn't that null

2000-06-27 Thread Tom Lane

Matthias Teege <[EMAIL PROTECTED]> writes:
> I have al small problem with ISNULL in a trigger
> function. My definition looks like: 

> IF NEW.vkp ISNULL THEN
>...

> The following SQL query didnt cause this if statement: 

> insert into aufpos(auftrag, artikel, best_menge, vkp,
> lieferwoche, cuser, ctime, uuser, utime) values
> ('175','8501900','12','','3500','matthias',
> now (), 'matthias',now ())

This seems correct to me --- an empty string '' is certainly not
the same thing as NULL.

> If I change the function statement as follows: 

> IF NEW.vkp = 0 THEN
>... 
> END IF;

> it works

Shouldn't that give a type error of some sort?  You didn't say
what type vkp is, but if it's a string type then comparing it
against a numeric shouldn't work IMHO.

regards, tom lane



Re: [GENERAL] Transactions and web applications

2000-06-27 Thread Lincoln Yeoh

At 10:01 PM 26-06-2000 -0400, Michael Mayo wrote:
>- Original Message -
>From: "Lincoln Yeoh" <[EMAIL PROTECTED]>
>> What are the recommended ways to do transactions in web applications?
>>
>> Right now I have persistent database connections, but currently they are
>> shared by multiple sessions. So I make sure that database level
>> transactions are closed after each page/http request.
>>
>> In theory I could tie database connections to sessions, but then if I have
>> 1000 outstanding (not necessarily active) sessions that would mean 1000
>> database connections, and 1000 postgres backend processes right? Ouch!

>However, are you sure that you're not overestimating your requirements?
>Assuming the average database connection takes 2 seconds to complete, it
>would require a load of 500 hits/sec to reach that 1000 figure you
>state...only the largest of sites get that kind of traffic.  If your site
>does get that popular, it is likely you will have the $$ to invest in enough
>hardware to support it.

Let me illustrate the problem with an example:

page 1
user logs in
page 2
user does A
page 3
user does B
page 4
user does C
page 5
user says, oops forget the whole thing, please rollback. Or user just says
forget it and closes browser. Or browser crashes at page 4.

What you suggest (database connection takes 2 second) seems to be CGI
style: for each webpage, connect to database, do stuff then disconnect. You
will not be able to take full advantage of database level transactions with
this approach- because with most databases when you disconnect there'll be
a rollback or commit.

Whereas if I try to use database level transactions to handle actual
transactions (reservations, rollbacks etc) for a webapp, if a user visits
the site, does something and then quits halfway, there's no way to know
about it until the timeout.

If the timeout value is 15 minutes, that's 900 seconds of waiting before
rollback/continuing the database transaction.

If I get 2 connections per second and people don't log out I hit that 1000
figure in 500 seconds. For postgres, each connection will involve a
separate backend taking say about 1MB. Bad news if:
database_connection_limit < timeout x peak_connections_per_second.

So this approach is not suitable for serving the whole world, might work
for a controlled number of internal users (tellers, helpdesks etc).

Right now I use application level transactions, with shared persistent
database connections for performance. 

What I want to know is if I'm missing something? Is there another way to do
things?

>> Because of this it looks like I may actually have to do application level
>> transactions instead of being able to rely solely on database level
>> transactions.
>
>I fail to see how application-level tranactions will help you here.
>Even if you do app-level transactions, you will still need a database engine
>to hold and query whatever data you have.  Maybe you are suggesting building
>your own database engine built into your app.  IMHO, this would be a rather

The application still uses the database. What I mean by application level
transactions is that the rollback/commit stuff, transactional isolation
will be handled by the application (at the application layer). 

For example if there's a commit, I then update all relevant records to
indicate they're committed. 
i.e. update tablex set committed=true where transactionid=14452;

And that's not what I call "help", just something I had to put up with :).. 

For MySQL I had to do things differently: no database level transactions so
can't update X tables atomically. Every row had a transactionid but no
committed flag. Had a transaction table keeping track of which transactions
are valid. This allowed me to mark rows in X tables as committed, just by
updating a row in the transaction table. But it meant that every select has
to be joined with the transaction table.

Fortunately I didn't have to deal with uniqueness problems and all that,
that would have been more painful. Also not all tables needed transactions.
And I didn't have to write my own WAL :).

Do those Java object thingies help for these problems?   

Cheers,
Link.






Re: [GENERAL] Conversion from MS Access to Postgresql

2000-06-27 Thread Tom Lane

"Len Morgan" <[EMAIL PROTECTED]> writes:
> ...  I cannot seem to make Postgres join two
> tables when the type of one is char(9) and the other is character
> varying(9).  The machine seems to go into an endless loop.

What?  Specific example, please.

> A similar problem I have is with fix precision fields.  I want to
> export a numeric(10,2) number from Access but they always end up
> numeric(30,6).

I don't think our 6.5.* ODBC driver knows anything about numeric,
so you're probably going to get default numeric precision if you
go through it.  You might have better luck with 7.0.

regards, tom lane



Re: [GENERAL] DateTime fields

2000-06-27 Thread Karel Zak


On Mon, 26 Jun 2000, Tom Lane wrote:

> "Dale Anderson" <[EMAIL PROTECTED]> writes:
> > I noticed that the DateTime fields and the to_timestamp function
> > interpret 12 PM as 00:00 and 12 AM as 12:00.
> 
> Huh?
> 
> Looks fine to me.

No, you overlook "to_timestamp()" in query. This routine really has bug in
PM/AM interpretation. 

Karel




Re: [GENERAL] Why isn't that null

2000-06-27 Thread NRonayette

Hi,

May be this insert will work with your test in your function

insert into aufpos(auftrag, artikel, best_menge, vkp,
 lieferwoche, cuser, ctime, uuser, utime) values
('175','8501900','12',NULL,'3500','matthias',
 now (), 'matthias',now ())

Nicolas

Matthias Teege a écrit :
> 
> Moin,
> 
> I have al small problem with ISNULL in a trigger
> function. My definition looks like:
> 
> IF NEW.vkp ISNULL THEN
>select t1.las into i_kd
>from auftrag t1, aufpos t2 where
>t2.auftrag = t1.id;
>select get_vkp(i_kd, NEW.artikel) into f_vkp;
>NEW.vkp:=f_vkp;
> END IF;
> 
> The following SQL query didnt cause this if statement:
> 
> insert into aufpos(auftrag, artikel, best_menge, vkp,
> lieferwoche, cuser, ctime, uuser, utime) values
> ('175','8501900','12','','3500','matthias',
> now (), 'matthias',now ())
> 
> If I change the function statement as follows:
> 
> IF NEW.vkp = 0 THEN
>...
> END IF;
> 
> it works but '0' is a leagal value but not '' (empty). The
> function should only be called if NEW.vkp ist empty.
> 
> Many thanks
> Matthias



[GENERAL] Why isn't that null

2000-06-27 Thread Matthias Teege


Moin,

I have al small problem with ISNULL in a trigger
function. My definition looks like: 

IF NEW.vkp ISNULL THEN
   select t1.las into i_kd 
   from auftrag t1, aufpos t2 where 
   t2.auftrag = t1.id;
   select get_vkp(i_kd, NEW.artikel) into f_vkp;
   NEW.vkp:=f_vkp;
END IF;

The following SQL query didnt cause this if statement: 

insert into aufpos(auftrag, artikel, best_menge, vkp,
lieferwoche, cuser, ctime, uuser, utime) values
('175','8501900','12','','3500','matthias',
now (), 'matthias',now ())

If I change the function statement as follows: 

IF NEW.vkp = 0 THEN
   ... 
END IF;

it works but '0' is a leagal value but not '' (empty). The
function should only be called if NEW.vkp ist empty.

Many thanks
Matthias





[GENERAL] Plan question..

2000-06-27 Thread Mitch Vincent

databasename=# explain select DISTINCT (case when resubmitted > created then
resubmitted else created end),a.app_id, a.appcode, a.firstname,
a.middlename, a.lastname, a.state, a.degree1, a.d1date, a.degree2, a.d2date,
a.salary, a.skill1, a.skill2, a.skill3, a.objective, a.employer, a.sic1,
a.sic2, a.sic3, a.prefs1, a.prefs2, a.sells from applicants as
a,applicants_resumes as ar,resumes_fti as rf where (a.created::date >
'01-06-2000' or a.resubmitted::date > '01-06-2000') and (rf.string
~'^test' ) and ar.app_id=a.app_id and rf.id=ar.oid order by (case when
resubmitted > created then resubmitted else created end) desc limit 10
offset 0;

NOTICE:  QUERY PLAN:

Unique  (cost=3981.58..4396.74 rows=722 width=220)
  ->  Sort  (cost=3981.58..3981.58 rows=7220 width=220)
->  Hash Join  (cost=1751.00..3518.84 rows=7220 width=220)
  ->  Hash Join  (cost=1665.98..2634.96 rows=6132 width=216)
->  Seq Scan on applicants a  (cost=0.00..585.74
rows=6132 width=208)
->  Hash  (cost=1638.38..1638.38 rows=11038 width=8)
  ->  Seq Scan on applicants_resumes ar
(cost=0.00..1638.38 rows=11038 width=8)
  ->  Hash  (cost=4.97..4.97 rows=32024 width=4)
->  Index Scan using resumes_fti_index on resumes_fti rf
(cost=0.00..4.97 rows=32024 width=4)

EXPLAIN

Both the app_id and the OID columns are indexed and I have done a VACUUM
ANALYZE in the applicants_resumes table yet I still get a seq scan on
them I'm trying to understand how this works so I can index the right
columns -- could someone please school me? :-)

When I enable likeplanning (in contrib) I get a totally differen plan but
over all it's slower.

Thanks!

-Mitch




[GENERAL] connection time out

2000-06-27 Thread jprem

hello ,
   is there any connection time out parameter to be set while
starting the
postmaster.the connection to postgresql are timed out after some 5
minutes.
i am running postgresql 6.5.3 on freeBSD 3.4 and accessing it using
jdbc.
why i get this problem ? can anyone help me out ???!!!