Re: [GENERAL] ERROR: cache lookup failed for type 0

[Greg Stark]

Tom Lane <[EMAIL PROTECTED]> writes:

> Tzahi Fadida <[EMAIL PROTECTED]> writes:
> > well, I tried the heap_deformtuple and I am getting now:
> > select testgetrows();
> > server closed the connection unexpectedly
> 
> You didn't palloc the values array.  Any reasonable compiler would have
> warned you about that BTW.  If you don't have compiler warnings enabled,
> learn to use them.

I think with gcc this type of warning is only enabled when you're compiling
with optimizations. Most people don't compile with optimizations enabled when
developing.

-- 
greg


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] MS-SQL to PostgreSQL

[Alam Surya]

Hi Craig

try with this

Declare v_maxval integer; v_minval integer;
Begin
   Select max(value),min(value) into v_maxval,v_minval from ABC;
   
   
   
end;

or see SELECT INTO from postgresql documentation

regards,

Alam Surya

- Original Message - 
From: "Craig Bryden" <[EMAIL PROTECTED]>
Subject: [GENERAL] MS-SQL to PostgreSQL


> Hi
> 
> Below is a snippet of MS-SQL code. Please can someone translate this to 
> plpgsql for me.
> 
> **
> DECLARE @MaxVal int, @MinVal int
> 
> SELECT @MaxVal = MAX(Value), @MinVal = MIN(Value)
> FROM ABC
> **
> 
> The variables would then be used for purther processing.
> I know that the variables would be declared like
> 
> ***
> DECLARE
> MaxVal int;
> MinVal int;
> BEGIN
>???
> END;
> ***
> 
> I am not sure of the query part and in particular the assignment of the 
> values to the variables
> 
> Thank you
> Craig




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Problem creating trigger-function with arguments (8.0rc4)

[Michael Fuhr]

On Fri, Jan 07, 2005 at 03:57:44PM -0700, Michael Fuhr wrote:

> By accessing TG_ARGV (not TGARGS) in the function.

Tom was probably thinking in C when he said TGARGS.  The Trigger
type (struct Trigger) has a tgargs member.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

   http://archives.postgresql.org

Re: [GENERAL] Problem creating trigger-function with arguments (8.0rc4)

[Michael Fuhr]

On Fri, Jan 07, 2005 at 02:00:07PM -0800, David Fetter wrote:
> On Fri, Jan 07, 2005 at 03:52:15PM -0500, Tom Lane wrote:
> > 
> > The CREATE TRIGGER parameter comes to the trigger function via
> > TGARGS, not as a regular parameter.
> 
> Um, so how would one write a trigger that takes arguments?

By accessing TG_ARGV (not TGARGS) in the function.  See the "Trigger
Procedures" documentation.

CREATE TABLE foo (x INTEGER);
 
CREATE FUNCTION trigfunc() RETURNS TRIGGER AS $$
BEGIN
RAISE INFO 'trigger argument = %', TG_ARGV[0];
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trig_insert BEFORE INSERT ON foo
  FOR EACH ROW EXECUTE PROCEDURE trigfunc('insert argument');

CREATE TRIGGER trig_update BEFORE UPDATE ON foo
  FOR EACH ROW EXECUTE PROCEDURE trigfunc('update argument');


test=> INSERT INTO foo VALUES (123);
INFO:  trigger argument = insert argument
INSERT 0 1

test=> UPDATE foo SET x = 456;
INFO:  trigger argument = update argument
UPDATE 1

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

returning a setof tuples like a subquery was(RE: [GENERAL] ERROR: cache lookup failed for type 0 )

[Tzahi Fadida]

yes you were right it works now. 
10x, sorry for the rookie mistake I jumped the gun on the last one.
If you will i have another question on the same subject. 
I want to return a setof tuples without a predetermined return type
like a subquery with a join, I don't want to specify a type.
for example, new_join_alg_func(table1,table2).
when I use SETOF RECORD I have to specify a type, same goes
for anyelement.

Regards,
tzahi.

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Saturday, January 08, 2005 12:24 AM
> To: Tzahi Fadida
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] ERROR: cache lookup failed for type 0 
> 
> 
> Tzahi Fadida <[EMAIL PROTECTED]> writes:
> > well, I tried the heap_deformtuple and I am getting now: select 
> > testgetrows(); server closed the connection unexpectedly
> 
> You didn't palloc the values array.  Any reasonable compiler 
> would have warned you about that BTW.  If you don't have 
> compiler warnings enabled, learn to use them.
> 
> Also, I'd recommend using the tupledesc from the just-opened 
> lRel; fetching it via an independent path is just asking for trouble.
> 
>   regards, tom lane
> 
> ---(end of 
> broadcast)---
> TIP 7: don't forget to increase your free space map settings
> 
> 



---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] ERROR: cache lookup failed for type 0

[Tom Lane]

Tzahi Fadida <[EMAIL PROTECTED]> writes:
> well, I tried the heap_deformtuple and I am getting now:
> select testgetrows();
> server closed the connection unexpectedly

You didn't palloc the values array.  Any reasonable compiler would have
warned you about that BTW.  If you don't have compiler warnings enabled,
learn to use them.

Also, I'd recommend using the tupledesc from the just-opened lRel;
fetching it via an independent path is just asking for trouble.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] bgwriter

[Jeff Davis]

I understand that the bgwriter code will not be changed until 8.1 (I'm
referring to this issue:
 ).

After reading through the thread, it's my impression that there exists
some performance problem with the current method of using a percent of
dirty buffers as a GUC parameter under some (but not all) situations.

So, what situations should I watch out for to avoid the performance
problem with 8.0? Is it actually a performance regression from 7.4, or
just not as good as we hoped? Is it a relatively minor issue, or can it
really bog down the server in some situations?

Regards,
Jeff Davis


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] ERROR: cache lookup failed for type 0

[Tzahi Fadida]

well, I tried the heap_deformtuple and I am getting now:
select testgetrows();
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> 

btw, as can be seen below I tried two kinds of tupledesc just in case
with the same results.
/*funcctx->tuple_desc= BlessTupleDesc(fctx->lRel->rd_att);*/
funcctx->tuple_desc=BlessTupleDesc(RelationNameGetTupleDesc("my_first_ta
ble"));

#include "postgres.h"
#include 
#include 
#include "fmgr.h"
#include "funcapi.h"
#include "access/heapam.h" 

typedef struct
{
  HeapScanDesc scan;
  Relation  lRel; 
} testgetrows_fctx;

PG_FUNCTION_INFO_V1(testgetrows);

Datum
testgetrows(PG_FUNCTION_ARGS)
{
 FuncCallContext *funcctx;
 testgetrows_fctx *fctx; 
 if (SRF_IS_FIRSTCALL())
 {
MemoryContext   oldcontext;
funcctx = SRF_FIRSTCALL_INIT();

oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
fctx = (testgetrows_fctx *) palloc(sizeof(testgetrows_fctx)); 
fctx->lRel = heap_open(17236, AccessShareLock);
fctx->scan = heap_beginscan(fctx->lRel, SnapshotNow, 0, NULL);

/*funcctx->tuple_desc= BlessTupleDesc(fctx->lRel->rd_att);*/
 
funcctx->tuple_desc=BlessTupleDesc(RelationNameGetTupleDesc("my_first_ta
ble"));

funcctx->user_fctx = fctx; 
MemoryContextSwitchTo(oldcontext);
}

funcctx = SRF_PERCALL_SETUP();
fctx = funcctx->user_fctx;

HeapTupletuple;
tuple = heap_getnext(fctx->scan, ForwardScanDirection);
if (HeapTupleIsValid(tuple))
{ 
  
  Datumresult;
  Datum*values;
  HeapTupletupleCopy;
  HeapTupletupleCopy2;
  char *nulls = (char *)palloc(funcctx->tuple_desc->natts *
sizeof(char));
  
  tupleCopy = heap_copytuple(tuple);
  heap_deformtuple(tuple,funcctx->tuple_desc,values,nulls);
  tupleCopy2 = heap_formtuple(funcctx->tuple_desc,values,nulls);
  result = HeapTupleGetDatum(tupleCopy2);
  
  SRF_RETURN_NEXT(funcctx, result);

}
else/* do when there is no more left */
{
  heap_endscan(fctx->scan);
  heap_close(fctx->lRel, AccessShareLock);
  
  SRF_RETURN_DONE(funcctx);
}
}
Regards,
tzahi.

> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED] 
> Sent: Friday, January 07, 2005 10:31 PM
> To: Tzahi Fadida
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] ERROR: cache lookup failed for type 0 
> 
> 
> Tzahi Fadida <[EMAIL PROTECTED]> writes:
> > It still doesn't work. btw, I am using 8rc2.
> 
> Um.  The "clean" way to do this is to use BlessTupleDesc and 
> then heap_formtuple.  That requires you to break down the 
> original tuple into fields (see heap_deformtuple).  
> Alternatively you could poke the datatype ID fields directly 
> into the copied tuple.
> 
>   regards, tom lane
> 
> 



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

Re: [GENERAL] Problem creating trigger-function with arguments (8.0rc4)

[David Fetter]

On Fri, Jan 07, 2005 at 03:52:15PM -0500, Tom Lane wrote:
> "Florian G. Pflug" <[EMAIL PROTECTED]> writes:
> > This is what I tried:
> 
> > CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text)
> > RETURNS "trigger" AS $$
> 
> There should probably be a specific error check telling you that a
> trigger function can't take any explicit arguments.  But there isn't
> (and it's too late for 8.0 because we froze error message strings
> long since :-().
> 
> The CREATE TRIGGER parameter comes to the trigger function via
> TGARGS, not as a regular parameter.

Um, so how would one write a trigger that takes arguments?  I stubbed
my toe on this in re: dbi-link, and would like to be able to write a
trigger with arguments :)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

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

   http://archives.postgresql.org

[GENERAL] unsubscribe

[Astha Raj]

What do I do to unsubscribe from this mailing list?



-- 
Internal Virus Database is out-of-date.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.0 - Release Date: 12/9/2004
 


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Books for experienced developers

[Shawn Harrison]

Craig Bryden wrote [01/07/05 3:46 AM]:
Hi
I have vast experience working with MS-SQL. Which books would be good 
for me to use in order to teach myself PostgreSQL? I need to migrate a 
MS-SQL Db to PostgreSQL. It contains tablers,views,stored procs, and 
user defined functions.
With no other RDBMS experience, I learned PostgreSQL using the online 
documentation. I'm using tables, views, rules, triggers, and stored 
procedures -- everything you've mentioned. Everything you need is 
explained very well in the docs.

Thanks
Craig
--

[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] need help Connect failure in an applet

[Ragnar Hafstað]

On Fri, 2005-01-07 at 13:03 -0500, John Doggett wrote:

>   When I did that, I now get a different error, that the postmaster is
> refusingthe connection. The solution to this problem is supposed to be
> adding  tcpip_socket = true to the postgresql.conf file and restarting the
> postgresql service. When I did that, I got an error when restarting the
> service.

If you are using 8.0, you should instead look at the listen_addresses
configuration. (I understand that tcpip_socket is not used any more)

Note that I have not tried 8.0 yet myself, so this could just be
hallucinations on my part.

gnari



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] PostgreSQL users on webhosting

[Jeff Davis]

That's an interesting idea. First, you can't (as far as I know) do it
with just schemas to seperate the users. There is no default tablespace
for an object created inside a given schema.

However, there is a default tablespace for a given database. You can (as
superuser) create a tablespace and permit only a specific user to use
it, and then create a database within that tablespace (so that objects
created in that database use only a specific tablespace). Users can't
create their own tablespace, so they can't create objects out of that
tablespace unless the superuser creates a new tablespace and gives them
permission.

That seems like it would work quite effectively, except that you need a
bunch of size-limited areas to point the tablespaces at. It would
probably be inconvenient to have many partitions. Although you could,
like you said, put all the "cheap" accounts on one partition, and the
expensive guys on their own disk. Then again, if you're going to single
out accounts, why not just give the special hosting account their own
instance?

There's no really easy answer. It would be nice if postgres had a "max
size" parameter for tablespaces, and then you could achieve reasoanble
seperation between databases quite easily (while still sharing the
buffers). I'm not sure what the overhead on a feature like that would
be.

Regards,
Jeff Davis

On Fri, 2005-01-07 at 10:38 +0100, Csaba Nagy wrote:
> On Wed, 2005-01-05 at 21:34, Jeff Davis wrote:
> > Benefits of multiple instances:
> > (1) Let's say you're using the one-instance method and one of your web
> > users is a less-than-talented developer, and makes an infinite loop that
> > fills the database with garbage. Not only will that hurt performance,
> > but if it fills the disk than no other users can even commit a
> > transaction! If you seperate the instances, you can run each as its own
> > uid and control each with quotas, etc.
> 
> I wonder if this could not be achieved at least partially by using
> schemas and set each user's schema to different tablespaces with
> different space available on them ? Say, the bulk of the low paying
> customers on a bulk partition, and the important customer on it's own
> partition ? I actually would like to know if this is feasable...
> 
> Cheers,
> Csaba.
> 
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Problem creating trigger-function with arguments (8.0rc4)

[Tom Lane]

"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
> This is what I tried:

> CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text)
> RETURNS "trigger" AS $$

There should probably be a specific error check telling you that a
trigger function can't take any explicit arguments.  But there isn't
(and it's too late for 8.0 because we froze error message strings
long since :-().

The CREATE TRIGGER parameter comes to the trigger function via TGARGS,
not as a regular parameter.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Problem creating trigger-function with arguments (8.0rc4)

[Michael Fuhr]

On Fri, Jan 07, 2005 at 09:00:12PM +0100, Florian G. Pflug wrote:

> CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text)
> RETURNS "trigger" AS $$

See the "Trigger Procedures" section of the PL/pgSQL documentation.
The first paragraph contains this:

Note that the function must be declared with no arguments even if
it expects to receive arguments specified in CREATE TRIGGER ---
trigger arguments are passed via TG_ARGV, as described below.

> If I replace RETURNS "trigger" with e.g. RETURNS "int8" it works...
> Is this is a bug, or has something regarding triggerfunctions and
> parameters changed in 8.0

Changed since when?  Are you saying this worked in an older version
of PostgreSQL?  If so, what version?  The paragraph I quoted above
goes back to at least 7.2.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] MS-SQL to PostgreSQL

[Michael Fuhr]

On Fri, Jan 07, 2005 at 10:00:47PM +0200, Craig Bryden wrote:

> I am not sure of the query part and in particular the assignment of the 
> values to the variables

See SELECT INTO in the PL/pgSQL documentation.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

   http://archives.postgresql.org

Re: [GENERAL] ERROR: cache lookup failed for type 0

[Tom Lane]

Tzahi Fadida <[EMAIL PROTECTED]> writes:
> It still doesn't work. btw, I am using 8rc2.

Um.  The "clean" way to do this is to use BlessTupleDesc and then
heap_formtuple.  That requires you to break down the original tuple
into fields (see heap_deformtuple).  Alternatively you could poke
the datatype ID fields directly into the copied tuple.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] Problem creating trigger-function with arguments (8.0rc4)

[Florian G. Pflug]

Hi
I want to create a simple trigger that denies inserts into a particular 
table - but, since I want meaningfull error-messages, but don't
want to create a function for each table, I figured I could pass
the error-message to the trigger-function.
This is what I tried:

CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text)
RETURNS "trigger" AS $$
begin
raise exception '%', v_message ;
end ;
$$ LANGUAGE 'plpgsql' VOLATILE STRICT;
This results in a parse error at "v_message" (in the 4th line).
If I replace RETURNS "trigger" with e.g. RETURNS "int8" it works...
Is this is a bug, or has something regarding triggerfunctions and
parameters changed in 8.0 (I also try omiting the parameter name
in the function declartion, and using "$1" directly, but then
the error-message says "Unknown parameter $1").
greetings, Florian Pflug


smime.p7s
Description: S/MIME Cryptographic Signature

[GENERAL] MS-SQL to PostgreSQL

[Craig Bryden]

Hi
Below is a snippet of MS-SQL code. Please can someone translate this to 
plpgsql for me.

**
DECLARE @MaxVal int, @MinVal int
SELECT @MaxVal = MAX(Value), @MinVal = MIN(Value)
FROM ABC
**
The variables would then be used for purther processing.
I know that the variables would be declared like
***
DECLARE
   MaxVal int;
   MinVal int;
BEGIN
  ???
END;
***
I am not sure of the query part and in particular the assignment of the 
values to the variables

Thank you
Craig
_
Research SA schools and varsities on MSN Search. http://search.msn.co.za
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] ERROR: cache lookup failed for type 0

[Tzahi Fadida]

It still doesn't work. btw, I am using 8rc2.
changed it to:
Datumresult; 
  HeapTupletupleCopy;
  tupleCopy = heap_copytuple(tuple);
  result = HeapTupleGetDatum(tupleCopy);
  SRF_RETURN_NEXT(funcctx, result);

its probably something with the column description. the rows are
returned ok with select testgetrows();
but not with select * from testgetrows();

Regards,
tzahi.

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Friday, January 07, 2005 8:59 PM
> To: Tzahi Fadida
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] ERROR: cache lookup failed for type 0 
> 
> 
> Tzahi Fadida <[EMAIL PROTECTED]> writes:
> > Hi, I am learning how to use the c functions and my function below 
> > works when I do: select testgetrows();
> > but when I do select * from testgetrows(); I am getting:
> > "ERROR:  cache lookup failed for type 0"
> > Whats's the problem?
> 
> I don't think it's safe to do HeapTupleGetDatum() directly on 
> a tuple obtained from heap_getnext.  You need to copy it.
> 
>   regards, tom lane
> 
> ---(end of 
> broadcast)---
> TIP 1: subscribe and unsubscribe commands go to 
> [EMAIL PROTECTED]
> 
> 



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] ERROR: cache lookup failed for type 0

[Tom Lane]

Tzahi Fadida <[EMAIL PROTECTED]> writes:
> Hi, I am learning how to use the c functions and my function below works
> when I do:
> select testgetrows();
> but when I do select * from testgetrows(); I am getting:
> "ERROR:  cache lookup failed for type 0"
> Whats's the problem?

I don't think it's safe to do HeapTupleGetDatum() directly on a tuple
obtained from heap_getnext.  You need to copy it.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.0.0 Release Candidate 4

[David Fetter]

On Fri, Jan 07, 2005 at 12:04:00PM -0400, Marc G. Fournier wrote:
> 
> Welcome to the New Year all, and the final days of testing for 8.0.0.
> 
> This will hopefully be our final Release Candidate, with a Full 
> Release happening late next week.  There are several Windows related
> fixes in this Release Candidate, with a full list of changes viewable
> in the Changelog available on the FTP server.
> 
> A current list of *known* supported platforms can be found at:
> 
>   http://developer.postgresql.org/supported-platforms.html
> 
> We're always looking to improve that list, so we encourage anyone that is 
> running a platform not listed to please report on any success or failures 
> with Release Candidate 4.
> 
> Baring *any* coding changes (documentation != code) over the next week or 
> so, we *hope* that this will the final Release Candidate before Full 
> Release, with that being aimed for the 15th (or earlier).
> 
> As always, this release is available on all mirrors, as listed at:
> 
>   http://wwwmaster.postgresql.org/download/mirrors-ftp
> 
> For those using Bittorrent, David Fetter hasn't had a chance to update the 
> .torrents, but should be available later today from:
> 
>   http://bt.postgresql.org

They're up. :)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

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

[GENERAL] ERROR: cache lookup failed for type 0

[Tzahi Fadida]

Hi, I am learning how to use the c functions and my function below works
when I do:
select testgetrows();
but when I do select * from testgetrows(); I am getting:
"ERROR:  cache lookup failed for type 0"
Whats's the problem?
10x.

drop function testgetrows();
CREATE OR REPLACE FUNCTION testgetrows() RETURNS SETOF my_first_table
AS 'foo6', 'testgetrows'
LANGUAGE C IMMUTABLE STRICT;

#include "postgres.h"
#include 
#include 
#include "fmgr.h"
#include "funcapi.h"
#include "access/heapam.h" 

typedef struct
{
  HeapScanDesc scan;
  Relation  lRel; 
} testgetrows_fctx;

PG_FUNCTION_INFO_V1(testgetrows);

Datum
testgetrows(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
testgetrows_fctx *fctx; 
if (SRF_IS_FIRSTCALL())
 {
MemoryContext   oldcontext;

funcctx = SRF_FIRSTCALL_INIT();
oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
fctx = (testgetrows_fctx *) palloc(sizeof(testgetrows_fctx)); 

fctx->lRel = heap_open(17236, AccessShareLock);
fctx->scan = heap_beginscan(fctx->lRel, SnapshotNow, 0, NULL);
funcctx->user_fctx = fctx; 
MemoryContextSwitchTo(oldcontext);
}

funcctx = SRF_PERCALL_SETUP();
fctx = funcctx->user_fctx;
HeapTupletuple;
tuple = heap_getnext(fctx->scan, ForwardScanDirection);
if (HeapTupleIsValid(tuple))
{ 
  Datumresult; 
  result = HeapTupleGetDatum(tuple);
  SRF_RETURN_NEXT(funcctx, result);
}
else/* do when there is no more left */
{
  heap_endscan(fctx->scan);
  heap_close(fctx->lRel, AccessShareLock);
  SRF_RETURN_DONE(funcctx);
}
}

Regards,
tzahi.




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

   http://archives.postgresql.org

Re: [GENERAL] Books for experienced DB developer

[Michael Fuhr]

On Fri, Jan 07, 2005 at 12:20:13PM +0100, Tino Wildenhain wrote:

> Nevertheless you should be able to return 3 cursors you
> define in your stored function and use them afterwards.

A function can also return SETOF RECORD.  However, a query calling
such a function would need to provide a column definition list, so
the query must know in advance what record type the function will
return.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] PostgreSQL Deployment

[Steve Atkins]

On Fri, Jan 07, 2005 at 06:31:35AM -0800, Hong Ji wrote:
> Hi
> I am new to PostgreSQL and interested in using PostgreSQL in our
> application. 
> But I have questions regarding what our users have to do to install the
> software.
> 
> I can not find documentation on how to deploy application which uses
> PostgreSQL.
> 
> Or does the user have to run separate setup (beside our application's setup
> program) to just install PostgreSQL as server?

We bundle an installation of PostgreSQL in the same tarball as our
application (installed within our applications directory structure and
built to run on a different port by default, so that it doesn't clash
with a native postgresql, if any). As part of our install script we
run initdb, createuser etc., exactly as explained in the standard
postgresql install docs.

That works for us, as the application is usually running on a machine
that's mostly dedicated to it. More usually as a user I'd usually
prefer the option of using an already installed postgresql instance if
one were available.

Cheers,
  Steve


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] does "select count(*) from mytable" always do a seq

[Scott Ribe]

> I guess what I"m really asking is why can't you run aggregates over an index?

It's got to do with MVCC and transaction consistency. Running count(*) or an
aggregate function on an index could include records that should not be
visible to your current transaction.


-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice



---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] PL/Perl

[Joshua D. Drake]

with me it doesn't work and returns error message
"Query failed: ERROR: creation of function failed: 'require' trapped by
operation mask at (eval 2) line 2. in ..."
You need to use plperlu.
Sincerely,
Joshua D. Drake

Thanx
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
begin:vcard
fn:Joshua D. Drake
n:Drake;Joshua D.
org:Command Prompt, Inc.
adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We  provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored  the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl.
x-mozilla-html:FALSE
url:http://www.commandprompt.com/
version:2.1
end:vcard


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] need help Connect failure in an applet

[John Doggett]

Alex,

Immediately after sending the inquiry I discovered that default applet
behavior is as you desribe and that one must change the java.policy file to
allow the connection.

When I did that, I now get a different error, that the postmaster is
refusingthe connection. The solution to this problem is supposed to be
adding  tcpip_socket = true to the postgresql.conf file and restarting the
postgresql service. When I did that, I got an error when restarting the
service.

Now I am rebooting to see if that helps.

Jay

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Alex Turner
Sent: Friday, January 07, 2005 12:04 PM
To: [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] need help Connect failure in an applet


An applet is only permitted to connect back to the originating server
IP that served the HTTP request.  If you try to connect to a different
address, it will fail with a permissions exception.  127.0.0.1 is the
localhost loopback address, and would not work from a remote host
either.

Alex Turner
NetEconomist

On Fri, 7 Jan 2005 11:23:46 -0500, John Doggett <[EMAIL PROTECTED]> wrote:
>
>
> Does anyone know why a connect call will fail with an
> AccessControlException:access denied(java.net.SocketPermission
> 127.0.0.1:5432 connect, resolve) from a browser delivered applet?
>
> The applet works fine in the debugger.
>
> I have PostgreSQL running in windows.
>
> Jay
>
> This e-mail message and any attachments are confidential and may be
> privileged.  If you are not the intended recipient, please notify MARKEM
> Corporation immediately -- by replying to this message or by sending an
> e-mail to [EMAIL PROTECTED] -- and destroy all copies of this message
> and any attachments. Thank you.
>
> Nothing contained within this e-mail, including attachments, is intended
to
> include or constitute an "electronic signature" as defined in 15 U.S.C. §
> 7006(5).
>

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html


-
This message has been scanned for malicious code and inappropriate content.
If you feel this message should have been blocked, please forward to
[EMAIL PROTECTED]



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

Re: [GENERAL] does "select count(*) from mytable" always do a seq scan?

[Alex Turner]

This is interesting... Perhaps a more knowledgable person for pgsql
could help us here...

I seem to remember something to do with the fact that You can't use
aggregate functions over an index...  I'm not sure why though.

You can do:
create index foo on my_table (lower(my_column))

but not
create index foo on my_table(min(my_column)) - I guess it wouldn't be
much of an index - it would be a single value.
You could reproduce that functionality with a trigger that updated a
table that contained the value of (min(my_column)), and I guess you
could do the same fo count(*) too.

I guess what I"m really asking is why can't you run aggregates over an index?

Alex Turner
NetEconomist

On Fri, 7 Jan 2005 09:09:49 -0800, Culley Harrelson <[EMAIL PROTECTED]> wrote:
> On Fri, 07 Jan 2005 16:17:16 +0100, Tino Wildenhain <[EMAIL PROTECTED]> wrote:
> >
> > How do you think an index would help if you do an unconditional
> > count(*)?
> 
> I really don't know .  I don't know the inner workings of
> database internals but I would guess that there would be some
> optimized way of counting the nodes in an index tree that would be
> faster than sequentially going through a table I suppose there is
> no free lunch.
> 
> One row, two rows, three rows, four rows, five rows 
> 
> culley
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Transaction size

[Tom Lane]

Alban Hertroys <[EMAIL PROTECTED]> writes:
> Is it possible that there is some limitation to the number of statements 
> in a single transaction?

2^32, and if you'd exceeded it, you'd get a very specific error message
saying so.

> As they're inserts, and therefore not even touching the same data, I'm 
> quite certain it's not some kind of row locking issue (does that even 
> happen at all with MVCC?).

I'm not.  In particular this could be a foreign key locking issue ---
does the target table have foreign keys, and if so could inserts from
different transactions be referencing the same master row?

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] does "select count(*) from mytable" always do a seq scan?

[Culley Harrelson]

On Fri, 07 Jan 2005 16:17:16 +0100, Tino Wildenhain <[EMAIL PROTECTED]> wrote:
> 
> How do you think an index would help if you do an unconditional
> count(*)?

I really don't know .  I don't know the inner workings of
database internals but I would guess that there would be some
optimized way of counting the nodes in an index tree that would be
faster than sequentially going through a table I suppose there is
no free lunch.

One row, two rows, three rows, four rows, five rows 

culley

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Transaction size

[Alban Hertroys]

Is it possible that there is some limitation to the number of statements 
in a single transaction? I'm trying to debug a problem where a program 
locks up during an insert[*].

Actually, there are 3 simultaneous transactions going on, all doing 
inserts of about 3000 records each. The problem occurs when I'm about 
400 records from the end of the transaction.
As they're inserts, and therefore not even touching the same data, I'm 
quite certain it's not some kind of row locking issue (does that even 
happen at all with MVCC?).

I'm just fishing around, it may just as well turn out to be a threading 
problem in my program, but I've been staring at it for days now and it 
doesn't seem to make more sense then it did at the begin...

Any insights much appreciated.
[*]: It prints debug output before the query is executed, and it should 
after.

BTW: My thanks to whoever changed the color of the links in the docs to 
the current color. The contrast/readability is much better now (compared 
to last week).

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] need help Connect failure in an applet

[Alex Turner]

An applet is only permitted to connect back to the originating server
IP that served the HTTP request.  If you try to connect to a different
address, it will fail with a permissions exception.  127.0.0.1 is the
localhost loopback address, and would not work from a remote host
either.

Alex Turner
NetEconomist

On Fri, 7 Jan 2005 11:23:46 -0500, John Doggett <[EMAIL PROTECTED]> wrote:
>  
>  
> Does anyone know why a connect call will fail with an 
> AccessControlException:access denied(java.net.SocketPermission
> 127.0.0.1:5432 connect, resolve) from a browser delivered applet? 
>   
> The applet works fine in the debugger. 
>   
> I have PostgreSQL running in windows. 
>   
> Jay 
> 
> This e-mail message and any attachments are confidential and may be
> privileged.  If you are not the intended recipient, please notify MARKEM
> Corporation immediately -- by replying to this message or by sending an
> e-mail to [EMAIL PROTECTED] -- and destroy all copies of this message
> and any attachments. Thank you. 
>   
> Nothing contained within this e-mail, including attachments, is intended to
> include or constitute an "electronic signature" as defined in 15 U.S.C. §
> 7006(5). 
>

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] unsubscribe

[Xinshu Piao]

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Astha Raj
Sent: Friday, January 07, 2005 8:32 AM
To: [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED]
Subject: [GENERAL] unsubscribe


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Books for experienced DB developer

[Richard_D_Levine]

I installed pgadminIII and really enjoy their built in documentation reader
for PostgreSQL.  The documentation that came with pgadminIII had been
updated for the new version 8 features.

Rick



 
  Geoffrey  
 
  <[EMAIL PROTECTED]>To:   
pgsql-general@postgresql.org  
  Sent by:   cc:
 
  [EMAIL PROTECTED]Subject:  Re: [GENERAL] Books 
for experienced DB developer  
  tgresql.org   
 

 

 
  01/07/2005 08:58 AM   
 

 

 




Craig Bryden wrote:
> Hi
>
> I am a very experienced MS-SQL developer. I am looking to port a
> database to PostgreSQL. Which books that are available would be ideal
> for someone who is already an experienced DB developer?

If you are experienced, I'd say you'd save yourself some cash and be
perfectly fine using the online docs.  I'll assume you'll be on a wintel
arch, based on your experience.  When you install Postgresql on a Linux
box, you get all the docs installed as well.  I don't know about the
windows install.

--
Until later, Geoffrey

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster




---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] unsubscribe

[Astha Raj]

> Unsubscribe pgsql-general-owner and pgsql-general --
> 
> 
> 
-- 
Internal Virus Database is out-of-date.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.0 - Release Date: 12/9/2004
 
<>
---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] PL/Perl

[Pavel Stehule]

Hello, 
you have to use plperlu, untrusted plperl

regards
Pavel Stehule

On Fri, 7 Jan 2005, ON.KG wrote:

> Hi!
> 
> Could I use "use", "require" functions in plperl?
> 
> for example,
> 
> CREATE OR REPLACE FUNCTION perl_func (text)
> RETURNS real
> AS '
>   use HTTP::Request;
>   use HTTP::Headers;
>   
>   return $value;
> '
> LANGUAGE 'plperl';
> 
> 
> with me it doesn't work and returns error message
> "Query failed: ERROR: creation of function failed: 'require' trapped by
> operation mask at (eval 2) line 2. in ..."
> 
> Thanx
> 
> 
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match
> 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] unsubscribe

[Astha Raj]

Unsubscribe


-- 
Internal Virus Database is out-of-date.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.0 - Release Date: 12/9/2004
 
<>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] need help Connect failure in an applet

[John Doggett]

Does anyone 
know why a connect call will fail with an AccessControlException:access 
denied(java.net.SocketPermission 127.0.0.1:5432 connect, 
resolve) from a browser delivered applet? 

 
The applet 
works fine in the debugger. 

 
I have PostgreSQL running in 
windows.
 
Jay
This e-mail message and any attachments are confidential and may 
beprivileged.  If you are not the intended recipient, please notify 
MARKEMCorporation immediately -- by replying to this message or by sending 
ane-mail to [EMAIL PROTECTED] -- 
and destroy all copies of this messageand any attachments. Thank you.
 
Nothing contained within this e-mail, including attachments, is intended 
toinclude or constitute an "electronic signature" as defined in 15 U.S.C. 
§7006(5).
 

[GENERAL] PL/Perl

[ON.KG]

Hi!

Could I use "use", "require" functions in plperl?

for example,

CREATE OR REPLACE FUNCTION perl_func (text)
RETURNS real
AS '
  use HTTP::Request;
  use HTTP::Headers;
  
  return $value;
'
LANGUAGE 'plperl';


with me it doesn't work and returns error message
"Query failed: ERROR: creation of function failed: 'require' trapped by
operation mask at (eval 2) line 2. in ..."

Thanx


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] how to migrate a complete win/interbase6 DB

[Alexis Vasquez]

very new to postgres.

Hi all, I'd just created database in
linux/postgres7.4, so it's empty. later with a program
that comes with delphi5 (datapump) via odbc try'd to
pass metadata and data.  but just metadata is passed.
so only create tables without data...so I would
like to know if could be a permission fact. or else.. 
  
or how could be the better way to do this?

thanks in advance

Alexis




__ 
Renovamos el Correo Yahoo!: ¡250 MB GRATIS! 
Nuevos servicios, más seguridad 
http://correo.yahoo.es

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] PostgreSQL 8.0.0 Release Candidate 4

[Marc G. Fournier]

Welcome to the New Year all, and the final days of testing for 8.0.0.
This will hopefully be our final Release Candidate, with a Full 
Release happening late next week.  There are several Windows related
fixes in this Release Candidate, with a full list of changes viewable
in the Changelog available on the FTP server.

A current list of *known* supported platforms can be found at:
http://developer.postgresql.org/supported-platforms.html
We're always looking to improve that list, so we encourage anyone that is 
running a platform not listed to please report on any success or failures 
with Release Candidate 4.

Baring *any* coding changes (documentation != code) over the next week or so, 
we *hope* that this will the final Release Candidate before Full Release, 
with that being aimed for the 15th (or earlier).

As always, this release is available on all mirrors, as listed at:
http://wwwmaster.postgresql.org/download/mirrors-ftp
For those using Bittorrent, David Fetter hasn't had a chance to update the 
.torrents, but should be available later today from:

http://bt.postgresql.org
Please report any bug reports with this Release Candidate to:
pgsql-bugs@postgresql.org

Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] does "select count(*) from mytable" always do a seq

[Bruno Wolff III]

On Fri, Jan 07, 2005 at 16:17:16 +0100,
  Tino Wildenhain <[EMAIL PROTECTED]> wrote:
> Am Freitag, den 07.01.2005, 06:45 -0800 schrieb Culley Harrelson:
> > Hi,
> > 
> > I am using Postgresql 7.4.  I have a table with 1.5 million rows.  It
> > has a primary key. VACUUM FULL ANALYZE is run every night.  There are
> > 2000-5000 inserts on this table every day but very few updates and
> > deletes.  When I select count(*) from this table it is using a
> > sequence scan.  Is this just life or is there some way to get this to
> > do an index scan?
> 
> How do you think an index would help if you do an unconditional 
> count(*)?

Some systems can just run through the index without having to access the
tuples. This can result in you having to read significantly fewer disk blocks
to get the count. Unfortunately, postgres still needs to check visibility
for each tuple and so an using index scan for count will be slower than
a sequential scan if a significant fraction of the table is being counted.

If an approximate answer is OK there is some information calculated when
you vacuum a table and you could query this value in the pg catalog.
I don't remember the name of what you want, but this should be in the
archives.

Another solution is to use a trigger to keep a count in another table.
from what you say above, this might be a practical solution for you.
Doing this has also been discussed in the archives.

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

   http://archives.postgresql.org

[GENERAL] J2SE 1.5 Cache Rowset(JSR 114 )

[Brian Maguire]

Just as an FYI.  Sun's Cache rowset which is new and part of the J2SE 1.5 is 
not compatable with Postgres.  We have been working with Sun's JAVA development 
team to resolve the issues through our test cases and debugging.  We hope that 
in a future patch update that the issues will be resolved.  We'll keep you up 
to date.

Brian


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] does "select count(*) from mytable" always do a seq

[Tino Wildenhain]

Am Freitag, den 07.01.2005, 06:45 -0800 schrieb Culley Harrelson:
> Hi,
> 
> I am using Postgresql 7.4.  I have a table with 1.5 million rows.  It
> has a primary key. VACUUM FULL ANALYZE is run every night.  There are
> 2000-5000 inserts on this table every day but very few updates and
> deletes.  When I select count(*) from this table it is using a
> sequence scan.  Is this just life or is there some way to get this to
> do an index scan?

How do you think an index would help if you do an unconditional 
count(*)?

Regards
Tino


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] does "select count(*) from mytable" always do a seq scan?

[Culley Harrelson]

Hi,

I am using Postgresql 7.4.  I have a table with 1.5 million rows.  It
has a primary key. VACUUM FULL ANALYZE is run every night.  There are
2000-5000 inserts on this table every day but very few updates and
deletes.  When I select count(*) from this table it is using a
sequence scan.  Is this just life or is there some way to get this to
do an index scan?

culley

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] PostgreSQL Deployment

[Lonni J Friedman]

Which OS are you planning to use?  There are pre-built RPMs for Linux,
and pre-compiled binaries for Windows.


On Fri, 7 Jan 2005 06:31:35 -0800, Hong Ji <[EMAIL PROTECTED]> wrote:
> Hi
> I am new to PostgreSQL and interested in using PostgreSQL in our
> application.
> But I have questions regarding what our users have to do to install the
> software.
> 
> I can not find documentation on how to deploy application which uses
> PostgreSQL.
> 
> Or does the user have to run separate setup (beside our application's setup
> program) to just install PostgreSQL as server?
> 
> Thanks.
> 
> Hong
> 
> ---(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
> 


-- 
~
L. Friedman[EMAIL PROTECTED]
LlamaLand   http://netllama.linux-sxs.org

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

   http://archives.postgresql.org

[GENERAL] PostgreSQL Deployment

[Hong Ji]

Hi
I am new to PostgreSQL and interested in using PostgreSQL in our
application. 
But I have questions regarding what our users have to do to install the
software.

I can not find documentation on how to deploy application which uses
PostgreSQL.

Or does the user have to run separate setup (beside our application's setup
program) to just install PostgreSQL as server?

Thanks.

Hong



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

Re: [GENERAL] Books for experienced DB developer

[Joshua D. Drake]

Craig Bryden wrote:
Hi Michael
I am having some problems porting my Stored Procedures. I am using the 
pl/pgsql language. Instead of irritating/spamming everyone on this 
mailing list, I was hoping that there would be a comprehensive book 
that focusses on how to do DB things in PostgreSQL, but that does not 
spend too much time explaining RDBMS basics.

Although I "really", "really" do appreciate what you say above these
lists are specifically for helping people with the problems they are having.
It is great that you are willing to read the docs first
(more people should) but as you an experienced developed I would assume
you are going to ask questions that make sense ;).
So please, feel free and take your best shot :)
On a general note it may be productive for you to look at the other
procedural languages as well.
Sincerely,
Joshua D. Drake


Thanks
Craig
From: Michael Fuhr <[EMAIL PROTECTED]>
To: Craig Bryden <[EMAIL PROTECTED]>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Books for experienced DB developer
Date: Fri, 7 Jan 2005 02:58:29 -0700
On Fri, Jan 07, 2005 at 11:33:52AM +0200, Craig Bryden wrote:
> I am a very experienced MS-SQL developer. I am looking to port a 
database
> to PostgreSQL. Which books that are available would be ideal for 
someone
> who is already an experienced DB developer?

PostgreSQL has good documentation so I'd suggest starting there.
If the documentation doesn't cover something you want to know then
please provide more info about what you're looking for.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

_
Research SA schools and varsities on MSN Search. http://search.msn.co.za
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard


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

Re: [GENERAL] Books for experienced DB developer

[Geoffrey]

Craig Bryden wrote:
Hi
I am a very experienced MS-SQL developer. I am looking to port a 
database to PostgreSQL. Which books that are available would be ideal 
for someone who is already an experienced DB developer?
If you are experienced, I'd say you'd save yourself some cash and be 
perfectly fine using the online docs.  I'll assume you'll be on a wintel 
arch, based on your experience.  When you install Postgresql on a Linux 
box, you get all the docs installed as well.  I don't know about the 
windows install.

--
Until later, Geoffrey
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Global/persistent variables

[Ronnie Meier Ramos]

Thanks for all answers!
Unfortunatly, faking them with some C functions would be very complex 
because in this application's case most of this variables are record 
types and some are arrays (pl/tables)... :-(

It seems that creating temp tables (and some api functions) would be a 
little bit easier to implement. One concern would be about preformance: 
I guess that temp tables are written to disk and fetched as any other 
table but discarded in the end of the session.

Any suggestion or comments?
Thanks again,
Ronnie
Joe Conway escreveu  em 6/1/2005 21:57:
Ronnie Meier Ramos wrote:
AFAIK PG doesn't have packages - this is not a problem since I can 
handle it with different schemas or some naming convention, but is 
there a way to declare persistent variables (that would be "visible" 
to any function up to the end of the session) ?

You might be able to fake it with some C functions. See:
http://www.joeconway.com/myfunc.tgz
Look at myfunc_setvar(), myfunc_getvar(), and myfunc_rmvar().
HTH,
Joe
---(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

begin:vcard
fn:Ronnie Meier Ramos
n:Ramos;Ronnie
org;quoted-printable:Viler Cal=C3=A7ados Ltda.
adr;quoted-printable:;;RS 239, n=C2=BA500;Novo Hamburgo;RS;93352-000;Brasil
email;internet:[EMAIL PROTECTED]
title;quoted-printable:Gerente de Inform=C3=A1tica
tel;work:+55 (51) 2129-3800
tel;fax:+55 (51) 2129-3801
x-mozilla-html:TRUE
version:2.1
end:vcard


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

Re: [GENERAL] Books for experienced DB developer

[Tino Wildenhain]

On Fri, 2005-01-07 at 12:17 +0100, Patrick FICHE wrote:
> No, this is only one result-set.
> MS SQL Server is able to return multiple result-set.
> 
> For example, one SQL Server function can return the result of the following
> queries :
> SELECT * FROM Table1
> SELECT * FROM Table2
> SELECT * FROM Table3
> 
> with Table1, Table2 and Table3 having different structures...
> 
> For example, you can navigate in the result-sets in ODBC using the
> SQLMoreResults function...

I wonder how this fits into the SQL standard by any way ;)
Nevertheless you should be able to return 3 cursors you
define in your stored function and use them afterwards.

Regards
Tino


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Books for experienced DB developer

[Adam Witney]

It has been able to do this for some time now... Take a look

33.4.4. SQL Functions Returning Sets

http://www.postgresql.org/docs/7.4/static/xfunc-sql.html



> I'm afraid this is still a problem.
> From my knowledge, Postgres function is able to return a single result-set
> not multiple.
> I may have missed some facility...
> 
> Regards,
> Patrick
> 
> --- 
> Patrick Fiche 
> email : [EMAIL PROTECTED]
> tél : 01 69 29 36 18
> 
> --- 
> 
> 
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Tino Wildenhain
> Sent: vendredi 7 janvier 2005 11:45
> To: Patrick FICHE
> Cc: Craig Bryden; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Books for experienced DB developer
> 
> 
> On Fri, 2005-01-07 at 11:25 +0100, Patrick FICHE wrote:
>> Hi Craig,
>> 
>> 2 years ago, I had to do some porting from MS SQL to Postgres.
>> All the application logic was coded in stored procedures...
>> 
>> The major problem I was faced to, was to port procedures returning
> multiple
>> result-sets...
> 
> At least, this isnt a problem anymore :-)
> 
> Regards
> Tino
> 
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
>   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Books for experienced DB developer

[Patrick FICHE]

No, this is only one result-set.
MS SQL Server is able to return multiple result-set.

For example, one SQL Server function can return the result of the following
queries :
SELECT * FROM Table1
SELECT * FROM Table2
SELECT * FROM Table3

with Table1, Table2 and Table3 having different structures...

For example, you can navigate in the result-sets in ODBC using the
SQLMoreResults function...

That's probably not a very usual way of programming but we did it and
experienced problems for porting

Regards,
Patrick


--- 
Patrick Fiche 
email : [EMAIL PROTECTED] 
tél : 01 69 29 36 18 

--- 




-Original Message-
From: Tino Wildenhain [mailto:[EMAIL PROTECTED]
Sent: vendredi 7 janvier 2005 12:08
To: Patrick FICHE
Cc: Craig Bryden; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Books for experienced DB developer


On Fri, 2005-01-07 at 11:56 +0100, Patrick FICHE wrote:
> I'm afraid this is still a problem.
> From my knowledge, Postgres function is able to return a single result-set
> not multiple.
> I may have missed some facility...

 see
http://techdocs.postgresql.org/guides/SetReturningFunctions
 or
http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835

(fresh from freenode irc #postgresql channel)

Or is it not what you mean?

Regards
Tino


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Books for experienced DB developer

[Tino Wildenhain]

On Fri, 2005-01-07 at 11:56 +0100, Patrick FICHE wrote:
> I'm afraid this is still a problem.
> From my knowledge, Postgres function is able to return a single result-set
> not multiple.
> I may have missed some facility...

 see
http://techdocs.postgresql.org/guides/SetReturningFunctions
 or
http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835

(fresh from freenode irc #postgresql channel)

Or is it not what you mean?

Regards
Tino


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] functions in postgresql

[Pavel Stehule]

Hello, 
You can use pgjobs-1.00.133-src.tar.gz from gbrog 
http://gborg.postgresql.org/ - really confortable scheduler for 
postgresql, but in C++. 

Regards 
Pavel

On Thu, 6 Jan 2005 [EMAIL PROTECTED] wrote:

> Is there a way to write a function in postgresql and have the database run
> it on a schedule?
> I would like the database to run a function on every Friday.
> 
> 
> Thanks
> 
> 
> 
> Robert Stewart
> Network Eng
> Governor's Office of Technology
> 101 Cold Harbor
> Work # 502 564 9696
> Cell # 502 330 5991
> Email [EMAIL PROTECTED]
> 
> 


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

Re: [GENERAL] Books for experienced DB developer

[Patrick FICHE]

I'm afraid this is still a problem.
>From my knowledge, Postgres function is able to return a single result-set
not multiple.
I may have missed some facility...

Regards,
Patrick

--- 
Patrick Fiche 
email : [EMAIL PROTECTED] 
tél : 01 69 29 36 18 

--- 




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Tino Wildenhain
Sent: vendredi 7 janvier 2005 11:45
To: Patrick FICHE
Cc: Craig Bryden; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Books for experienced DB developer


On Fri, 2005-01-07 at 11:25 +0100, Patrick FICHE wrote:
> Hi Craig,
> 
> 2 years ago, I had to do some porting from MS SQL to Postgres.
> All the application logic was coded in stored procedures...
> 
> The major problem I was faced to, was to port procedures returning
multiple
> result-sets...

At least, this isnt a problem anymore :-)

Regards
Tino


---(end of broadcast)---
TIP 8: explain analyze is your friend

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

Re: [GENERAL] Books for experienced DB developer

[Tino Wildenhain]

On Fri, 2005-01-07 at 11:25 +0100, Patrick FICHE wrote:
> Hi Craig,
> 
> 2 years ago, I had to do some porting from MS SQL to Postgres.
> All the application logic was coded in stored procedures...
> 
> The major problem I was faced to, was to port procedures returning multiple
> result-sets...

At least, this isnt a problem anymore :-)

Regards
Tino


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Books for experienced DB developer

[Patrick FICHE]

Hi Craig,

2 years ago, I had to do some porting from MS SQL to Postgres.
All the application logic was coded in stored procedures...

The major problem I was faced to, was to port procedures returning multiple
result-sets...
Another problem was that there is no default value for stored procedures in
PostgreSQL, so you have to set all parameters and modify calling
applications to do so...

I have written some tips for porting but in French.
If you face to special problems, don't hesitate to ask for and if I perhaps
encountered the same problem...

Patrick


--- 
Patrick Fiche 
email : [EMAIL PROTECTED] 
tél : 01 69 29 36 18 

--- 




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Craig Bryden
Sent: vendredi 7 janvier 2005 11:04
To: [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Books for experienced DB developer


Hi Michael

I am having some problems porting my Stored Procedures. I am using the 
pl/pgsql language. Instead of irritating/spamming everyone on this mailing 
list, I was hoping that there would be a comprehensive book that focusses on

how to do DB things in PostgreSQL, but that does not spend too much time 
explaining RDBMS basics.

Thanks
Craig
>From: Michael Fuhr <[EMAIL PROTECTED]>
>To: Craig Bryden <[EMAIL PROTECTED]>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Books for experienced DB developer
>Date: Fri, 7 Jan 2005 02:58:29 -0700
>
>On Fri, Jan 07, 2005 at 11:33:52AM +0200, Craig Bryden wrote:
>
> > I am a very experienced MS-SQL developer. I am looking to port a 
>database
> > to PostgreSQL. Which books that are available would be ideal for someone
> > who is already an experienced DB developer?
>
>PostgreSQL has good documentation so I'd suggest starting there.
>If the documentation doesn't cover something you want to know then
>please provide more info about what you're looking for.
>
>--
>Michael Fuhr
>http://www.fuhr.org/~mfuhr/

_
Research SA schools and varsities on MSN Search. http://search.msn.co.za


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Books for experienced DB developer

[Craig Bryden]

Hi Michael
I am having some problems porting my Stored Procedures. I am using the 
pl/pgsql language. Instead of irritating/spamming everyone on this mailing 
list, I was hoping that there would be a comprehensive book that focusses on 
how to do DB things in PostgreSQL, but that does not spend too much time 
explaining RDBMS basics.

Thanks
Craig
From: Michael Fuhr <[EMAIL PROTECTED]>
To: Craig Bryden <[EMAIL PROTECTED]>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Books for experienced DB developer
Date: Fri, 7 Jan 2005 02:58:29 -0700
On Fri, Jan 07, 2005 at 11:33:52AM +0200, Craig Bryden wrote:
> I am a very experienced MS-SQL developer. I am looking to port a 
database
> to PostgreSQL. Which books that are available would be ideal for someone
> who is already an experienced DB developer?

PostgreSQL has good documentation so I'd suggest starting there.
If the documentation doesn't cover something you want to know then
please provide more info about what you're looking for.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
_
Research SA schools and varsities on MSN Search. http://search.msn.co.za
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Books for experienced DB developer

[Michael Fuhr]

On Fri, Jan 07, 2005 at 11:33:52AM +0200, Craig Bryden wrote:

> I am a very experienced MS-SQL developer. I am looking to port a database 
> to PostgreSQL. Which books that are available would be ideal for someone 
> who is already an experienced DB developer?

PostgreSQL has good documentation so I'd suggest starting there.
If the documentation doesn't cover something you want to know then
please provide more info about what you're looking for.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

[GENERAL] Books for experienced developers

[Craig Bryden]

Hi
I have vast experience working with MS-SQL. Which books would be good for me 
to use in order to teach myself PostgreSQL? I need to migrate a MS-SQL Db to 
PostgreSQL. It contains tablers,views,stored procs, and user defined 
functions.

Thanks
Craig
_
Search even faster with MSN Toolbar! 
http://toolbar.msn.co.za?DI=1054&XAPID=2083

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] PostgreSQL users on webhosting

[Csaba Nagy]

On Wed, 2005-01-05 at 21:34, Jeff Davis wrote:
> Benefits of multiple instances:
> (1) Let's say you're using the one-instance method and one of your web
> users is a less-than-talented developer, and makes an infinite loop that
> fills the database with garbage. Not only will that hurt performance,
> but if it fills the disk than no other users can even commit a
> transaction! If you seperate the instances, you can run each as its own
> uid and control each with quotas, etc.

I wonder if this could not be achieved at least partially by using
schemas and set each user's schema to different tablespaces with
different space available on them ? Say, the bulk of the low paying
customers on a bulk partition, and the important customer on it's own
partition ? I actually would like to know if this is feasable...

Cheers,
Csaba.





---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] Books for experienced DB developer

[Craig Bryden]

Hi
I am a very experienced MS-SQL developer. I am looking to port a database to 
PostgreSQL. Which books that are available would be ideal for someone who is 
already an experienced DB developer?

Thanks
Craig
_
Pop-up ads giving you a hard time? Block them with MSN Toolbar. 
http://toolbar.msn.co.za?DI=1054&XAPID=2083

---(end of broadcast)---
TIP 8: explain analyze is your friend