Re: [GENERAL] Memory error in user-defined aggregation function
Hi,
Finally got this running under the debugger and figured out what is
going on. I had been under the impression that
if (PG_ARGISNULL(0))
PG_RETURN_NULL();
state = (quartile_state *) PG_GETARG_POINTER(0);
would ensure that state was never a null pointer. However this is not
the case, and an additional check for state==0x0 solved the problem.
Somewhat unexpected, I have to say.
I would still be interested in any ways in which this implementation
could be improved. It would be good if there were some model
implementations for this type of thing - without orafce to guide me I
would have had a hard time figuring any of this out from the docs. I'd
gladly make the quartile implementation available for this purpose if
there is interest.
Adriaan
On 7 August 2012 15:04, Adriaan Joubert wrote:
> Hi,
>
> I've implemented an aggregation function to compute quartiles in C
> borrowing liberally from orafce code. I uses this code in a windowing
> context and it worked fine until today - and I'm not sure what
> changed. This is on 9.1.2 and I have also tried it on 9.1.4.
>
> What I have determined so far (by sprinkling a lot of elog's
> throughout the code) is that it does not seem to be data specific,
> although it seems to depend on the number of aggregations I do (up to
> about 1250 seems to be fine, beyond that it chokes). I also
> established that there does not seem to be a problem with the transfer
> function, and the data is accumulated without any issues. The error I
> see is in the call to first_quartile_final (listed below). The pointer
> to the transfer data structure is not null, but accessing the field
> mstate->nelems causes a segflt. So the transfer data structure pointer
> is bogus.
>
> I've recompiled postgres with debugging enabled and have connected to
> the backend with gdb, but haven't had any joy in persuading gdb to
> actually stop in the correct file so that I can step through. I'll
> keep on trying to make some headway with that.
>
> In the meantime I would appreciate any comments as to whether the
> approach taken is the right one, and whether additional checks can be
> inserted to avoid this segmentation faults.
>
> Many thanks,
>
> Adriaan
>
>
> My transfer data structure is
>
> typedef struct
> {
> int len; /* allocated length */
> int nextlen; /* next allocated length */
> int nelems; /* number of valid entries */
> float8 *values;
> } quartile_state;
>
> On the first call to the aggregate function this data structure is
> allocated as follows:
>
> static quartile_state *
> quartile_accummulate(quartile_state *mstate, float8 value,
> MemoryContext aggcontext)
> {
> MemoryContext oldcontext;
>
> if (mstate == NULL)
> {
> /* First call - initialize */
> oldcontext = MemoryContextSwitchTo(aggcontext);
> mstate = palloc(sizeof(quartile_state));
> mstate->len = 512;
> mstate->nextlen = 2 * 512;
> mstate->nelems = 0;
> mstate->values = palloc(mstate->len * sizeof(float8));
> MemoryContextSwitchTo(oldcontext);
> }
> else
> {
> if (mstate->nelems >= mstate->len)
> {
> int newlen = mstate->nextlen;
>
> oldcontext = MemoryContextSwitchTo(aggcontext);
> mstate->nextlen += mstate->len;
> mstate->len = newlen;
> mstate->values = repalloc(mstate->values, mstate->len
> * sizeof(float8));
> MemoryContextSwitchTo(oldcontext);
> }
> }
>
> mstate->values[mstate->nelems++] = value;
>
> return mstate;
> }
>
>
> And the transfer function itself is
>
> PG_FUNCTION_INFO_V1(quartile_transfer);
> Datum
> quartile_transfer(PG_FUNCTION_ARGS) {
> MemoryContext aggcontext;
> quartile_state *state = NULL;
> float8 elem;
>
> if (!AggCheckCallContext(fcinfo, &aggcontext))
> {
> elog(ERROR, "quartile_transform called in non-aggregate
> context");
> }
>
> state = PG_ARGISNULL(0) ? NULL : (quartile_state *)
> PG_GETARG_POINTER(0);
> if (PG_ARGISNULL(1))
> PG_RETURN_POINTER(state);
>
> elem = PG_GETARG_FLOAT8(1);
>
> state = quartile_accummulate(state, elem, aggcontext);
>
> PG_RETURN_POINTER(state);
> }
>
> The
[GENERAL] Memory error in user-defined aggregation function
Hi,
I've implemented an aggregation function to compute quartiles in C
borrowing liberally from orafce code. I uses this code in a windowing
context and it worked fine until today - and I'm not sure what
changed. This is on 9.1.2 and I have also tried it on 9.1.4.
What I have determined so far (by sprinkling a lot of elog's
throughout the code) is that it does not seem to be data specific,
although it seems to depend on the number of aggregations I do (up to
about 1250 seems to be fine, beyond that it chokes). I also
established that there does not seem to be a problem with the transfer
function, and the data is accumulated without any issues. The error I
see is in the call to first_quartile_final (listed below). The pointer
to the transfer data structure is not null, but accessing the field
mstate->nelems causes a segflt. So the transfer data structure pointer
is bogus.
I've recompiled postgres with debugging enabled and have connected to
the backend with gdb, but haven't had any joy in persuading gdb to
actually stop in the correct file so that I can step through. I'll
keep on trying to make some headway with that.
In the meantime I would appreciate any comments as to whether the
approach taken is the right one, and whether additional checks can be
inserted to avoid this segmentation faults.
Many thanks,
Adriaan
My transfer data structure is
typedef struct
{
int len; /* allocated length */
int nextlen; /* next allocated length */
int nelems; /* number of valid entries */
float8 *values;
} quartile_state;
On the first call to the aggregate function this data structure is
allocated as follows:
static quartile_state *
quartile_accummulate(quartile_state *mstate, float8 value,
MemoryContext aggcontext)
{
MemoryContext oldcontext;
if (mstate == NULL)
{
/* First call - initialize */
oldcontext = MemoryContextSwitchTo(aggcontext);
mstate = palloc(sizeof(quartile_state));
mstate->len = 512;
mstate->nextlen = 2 * 512;
mstate->nelems = 0;
mstate->values = palloc(mstate->len * sizeof(float8));
MemoryContextSwitchTo(oldcontext);
}
else
{
if (mstate->nelems >= mstate->len)
{
int newlen = mstate->nextlen;
oldcontext = MemoryContextSwitchTo(aggcontext);
mstate->nextlen += mstate->len;
mstate->len = newlen;
mstate->values = repalloc(mstate->values, mstate->len *
sizeof(float8));
MemoryContextSwitchTo(oldcontext);
}
}
mstate->values[mstate->nelems++] = value;
return mstate;
}
And the transfer function itself is
PG_FUNCTION_INFO_V1(quartile_transfer);
Datum
quartile_transfer(PG_FUNCTION_ARGS) {
MemoryContext aggcontext;
quartile_state *state = NULL;
float8 elem;
if (!AggCheckCallContext(fcinfo, &aggcontext))
{
elog(ERROR, "quartile_transform called in non-aggregate
context");
}
state = PG_ARGISNULL(0) ? NULL : (quartile_state *)
PG_GETARG_POINTER(0);
if (PG_ARGISNULL(1))
PG_RETURN_POINTER(state);
elem = PG_GETARG_FLOAT8(1);
state = quartile_accummulate(state, elem, aggcontext);
PG_RETURN_POINTER(state);
}
The final function for the computation of the first quartile is
PG_FUNCTION_INFO_V1(first_quartile_final);
Datum
first_quartile_final(PG_FUNCTION_ARGS) {
quartile_state *state = NULL;
float8 result;
if (PG_ARGISNULL(0))
PG_RETURN_NULL();
state = (quartile_state *) PG_GETARG_POINTER(0);
/** HERE state->nelems causes a segflt */
if (state->nelems<4)
PG_RETURN_NULL();
result = quartile_result(state, 0.25);
PG_RETURN_FLOAT8(result);
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What are your using it for?
> I'm working a piece on open-source databases for LinuxWorld magazine and > I'd like to know what people are actually using postgresql for. We are using it for financial applications. Both as store for trading data (in one database we have well over 2-million data points for daily trading data) and as back-end for our (private) web servers. These are both for our own use and increasingly for our customers. We run Digital Unix, and postgres runs on a 2-processor DS-20. For a small organisation Oracle is just too expensive on high-end hardware and postgres is pretty good and improving all the time. Show me another system where you can either fix bugs yourself or get a fix off the mailing list in a few hours! Adriaan -- --+------- Dr Adriaan Joubert | Phone: +357-2-750 652 APL Financial Services (Overseas) Ltd| Fax: +357-2-750 654 3 D. Vikella St | e-mail: [EMAIL PROTECTED] 1061 Nicosia, CYPRUS | --+---
Re: [GENERAL] And to make things even better...
Steve Wolfe wrote: > /var/lib/pgsql reports that we're running 6.3 - which I don't find on the > FTP site. Is that not a valid distribution number, or is the source not > available? Oh, I remember running 6.3. That was a looong time ago. Current version is 6.5.3 and 7.0 is about to be released. I would very much recommend an upgrade, although you may want to wait for 7.0 to be released. I've been migrating stuff accross to 7.0 RC1 (release candidate) and it is pretty solid and a vast improvement on 6.3! Adriaan
Re: [GENERAL] date expressions
surfer girl wrote: > I checked the manual on this but couldn't find a clear answer: > > I've got two dates in a database, a start date and and end date, which are type >"date." I want to compare these dates to today's date in the SQL statement, something >along the lines of "startdate < date < enddate". Can this be done in the SQL select >statement? SELECT .. FROM .. WHERE startdate < current_date AND enddate > current_date
Re: [GENERAL] please help me recover from duplicate key in unique index
> > Please help me recover our database from what I think > > is a duplicate key in unique index problem. > > This may not help, and forgive my asking the obvious, but have > you done a SELECT on the table and actually *seen* duplicate 'id' > values in the SERIAL column? That would surprise me because I > didn't think it was possible to create duplicates in the scenario > you describe. The SERIAL type is really just a sequence, and its > values are unique across all transactions (I hope!). IIRC there > is some opportunity for wierdness if the sequence cache setting > has been "adjusted" incorrectly > (http://www.postgresql.org/docs/postgres/sql-createsequence.htm). > > > When I try to vacuum, I get this: > > > > ERROR: Cannot insert a duplicate key into a unique > > index > > Try dropping all indexes on the table, do a vacuum (if it will let you). Dump the table out with pg_dump -t if it will let you, sort it in emacs or with perl. Easiest thing would then be to write a little perl script that puts all duplicate rows into a separate file. Dropt the table and re-create it. Load the first lot up (with given sequence number!), fix your sequences (drop, create ..start ) and then handle the duplicate rows (i.e. insert them with perl/DBI or something so that they get new sequence numbers assigned). I think you should be able to dump once you have dropped all indexes (probably one of them is _pkey). Your sequence may be called __seq if memeory serves me right. It is still a normal sequences and you can drop and recreate it safely. Good luck, Adriaan
Re: [GENERAL] Future of PostgreSQL
Hi, Yes, I think reliability needs more work. I've had quite a few problems with system indexes getting corrupted (number of tuples incorrect and some other bizarre problems). Very hard to pin down as I haven't been able to reproduce any of these cases. I've got the feeling that there may be problems when you have PL routines used to enforce consistency constraints between several tables and the database is being hit hard. On the whole we are very happy with postgres and it has recently moved from one of our development systems to a production system. I think there has been a similar development for quite a few other people and there are an increasing number of production Postgres systems out there. Several people have mentioned that they could make some money available for futher development of postgres. I also noticed that the common list of complaints (large tuples etc) have mostly moved from the to-do to the done list. I think there needs to be a new discussion on how best to make use of additional resources to do things that benefit postgres most. Perhaps it would be an idea to have the developers put together a list with tasks that are boring and that nobody wants to do, but that would be of great benefit to the system (for somebody who doesn't know the internals it is hard to see what may be important tasks). I would prefer to contribute time, but we are kind-of short of people, so that that is pretty hard to do. The next best thing then seems to be to contribute money in a way that benefits everybody. I'm thinking along the lines of: if a few companies could provide $500 or $1000 and this could free up some of a developers time to work on postgres rather than to go contracting and this time is spent on a part of postgres that is important for production use (Vadim's work on the transaction logs for example), then this is a good thing. Any such process should make use of an accumulation of small contributions, as it is amazingly difficult to explain to a finance director why you want to spend $1000 without getting anything solid in return (while they are often quite happy to shell out twice that for an Office licence) and many companies are small start-ups and perhaps not that flush with cash (which is probably why they are using postgres in the first place). And secondly it is very important for the developers to figure out how this is going to interact with the whole process of collaborative software development. The last thing we want is competition for funds to impact on a collaborative development process. I think a system like this can only operate if it is based on consensus between the main developers. Please feel free to flame if I'm talking bollox. In the mean-time: happy new year to everybody! Adriaan
Re: [GENERAL] Future of PostgreSQL
john huttley wrote: > > I believe we are adding Oracle compatibility as possible. We are > > working on write-ahead log, long tuples, foreign keys, and outer joins. > > Anything else? > > Yes, earlier in the year I was trying to migrate from Pervasive SQL to > posgtres and > came to a screaming halt when it wouldn't do a large view. Exceeded some > sort of internal buffer > or rule area. I dont recall the details, although the mail archive will have > it. This will be fixed by Jan's new compressed type and the long fields in a second table. So in about 6 months time. The one we still need is views on UNION's... Adriaan
Re: [GENERAL] get the previous assigned sequence value
> > With this second method, you'd probably need to beware race conditions. If > > another process inserts a record into mytable after you do but before you > > call currval(), then you'll get the wrong value. > > > > Not an issue if you've only got one process accessing the table - probably > > is one if you have two or more. > > I don't think that's true the currval belongs to the process (as it were) > so that what happens is that currval remains unchanged by inserts by other > processes. > > nextval however is 'affected' meaning that nextval won't just return > curval+1, it returns whatever the next sequence item is taking into > account the increasses caused by other processes. > > at least I seem to remember that from previous postings... any seconders? currval always gives you the most recent sequence value returned by your own back-end, so that it is not affected by waht other processes do. With nextval it also depends on what cache size you chose. If youchose 1, the default, nextval is directly affected by whether other processes have doena nextval. If the caches is larger this is not necessarily the case. Adriaan
Re: [GENERAL] ALTER FUNCTION
Just drop the function, drop all triggers that use the function, re-create the function and recreate all triggers. If the function is called by other PL functions, you need to drop and re-install those as well. If you keep them all in a big file, every one preceded by drop, you can just reload the file (with \i into psql) whenever you have changed something. No need to dump any data. Adriaan > > UPDATE pg_proc SET prosrc='SQL statement' WHERE proname LIKE > 'functionname'; > > seems to work for SQL queries at least. I doubt it's recommended, > though! > > > > > are there plans for an ALTER FUNCTION statement for Postgresql? I > > think functions are completely unuseable, when it is not possible to > > change the definition of a function. A bugfix in a function > requires > > the export of all data, a redefinition of the function and a > complete > > reimport. Or is there a simpler way?
Re: [GENERAL] Except operation
Satyajeet Seth wrote: > > Hi > The query: > select * from webdata except select * from webdata1; > takes abysmally long .How can I optimise it? > The particulars are: You could try select * from webdata w where not exists (select * from webdata1 w1 where w1.tid=w.tid ... ) If you have the correct indexes on webdata1 this can be quite fast. Adriaan
Re: [GENERAL] alpha and true64 port
Bruce Momjian wrote: > > hi, this is kind of emergent! > > > > we are in the process of decision making. Is there an true64 on > > alpha port? > > > > thanks in advance!! > > We have alpha/osf, but no tru64 that I know of. Tru64 is just a renaming of Digital Unix / OSF/1(Those Compaq people can't spell, where is good old digital with a line-long engineering number you can't remember?) Postgres runs fine on Alphas, but you need to use the cc compiler, i.e. compile with template alpha_cc. And you need to use 6.5.2. I think I sent in a couple of minor patches -- have a look at the mailing list archive. Adriaan
Re: [GENERAL] Perl - Apache / Postgress
> Erik Colson wrote:
>
> I'm using Apache with mod_perl to access a PostgresSQL database (6.5)
> .
>
> The script starts with connecting to the database... this means that
> the server is actually reconnecting everytime the script starts and
> disconnect when the HTML page is generated.
>
> I've read about a possibility to make a 'permanent' connection to the
> database ? Can anyone tell how ?
I've never used mod_perl, but I use FastCGI with CGI.pm, and that works
just great. In the CGI script you have a loop
while (my $q = new CGI::Fast) {
}
and this works just fine. The Apache fastCGI module is available from
www.fastcgi.com.
Adriaan
[GENERAL] new type: 1-byte bit mask type
Hi, I finally finished my 1-byte bitmask type. You can even use it in btree indexes now. It provides all the standard bit operations (& | ^ ~ << >>) and I hope someone will find it useful. If anybody has any suggestions for improvements or questions, please let me know. If it passes muster, perhaps it could go into the contrib area of one of the next releases. Oh, and you will note that the or-operator is defined as '||' instead of '|' -- this is because postgres would not accept a single '|' as an operator. This will hopefully change at some point in the future. Adriaan bit1.tar.gz
Re: [GENERAL] User-defined types and indices
> > I defined a new type, and it is essential that I am able to use it in > an index. This seems to require a bit more than just having the > comparison operators. On the create index page it seems that it is > necessary to define an *_ops class for the new type, but I have no idea > how I go about this. I have looked at the folowing system tables > > pg_am > pg_amop > pg_opclass > pg_operator > Aaah, grepping through the sgml doc stuff I finally found something that looks as if it explains what I want! Sorry for the bandwith. Adriaan
[GENERAL] User-defined types and indices
Hi, I defined a new type, and it is essential that I am able to use it in an index. This seems to require a bit more than just having the comparison operators. On the create index page it seems that it is necessary to define an *_ops class for the new type, but I have no idea how I go about this. I have looked at the folowing system tables pg_am pg_amop pg_opclass pg_operator but have no idea how I go about inserting the new type into these classes. I have operators for <, <=, =, <>, > and >= defined and they work fine, so I think it should be possible to define a btree. I did not define HASH or any of that stuff when defining the operators as I wasn't too sure what that implied. I'd really appreciate any hints on this one. Thanks in advance, Adriaan
Re: [GENERAL] PL Problems.
> tt=> create table test (a int4, b bit2); > CREATE > tt=> CREATE FUNCTION mytrig () RETURNS opaque AS > ' > tt-> ' > tt'> DECLARE > tt'> def_state CONSTANT BIT2 := 'b0001'::BIT2; > tt'> BEGIN > tt'> new.b = def_state; > tt'> RETURN new; > tt'> END; > tt'> ' LANGUAGE 'plpgsql'; > ERROR: parser: parse error at or near "b0001" > tt=> > Aaaah, Stupidity! I need double quotes around the strings, i.e. ''b0001'' Sorry for the bandwith. BTW, is their interest in having a 2 byte bit type as a contributed type along the lines of int8? I could package it up, and post it. It supports all the binary operations, i.e. & | ^ ~ << and >>. Only problem I would like to resolve is why I cannot define the or operator as |. Adriaan
[GENERAL] PL Problems.
I have my marvelous bit type working now, and now I find out I cannot use it in PL scripts. tt=> create table test (a int4, b bit2); CREATE tt=> CREATE FUNCTION mytrig () RETURNS opaque AS ' tt-> ' tt'> DECLARE tt'> def_state CONSTANT BIT2 := 'b0001'::BIT2; tt'> BEGIN tt'> new.b = def_state; tt'> RETURN new; tt'> END; tt'> ' LANGUAGE 'plpgsql'; ERROR: parser: parse error at or near "b0001" tt=> I've tried all combinations I could think of, always with the same result. Is this a restriction in PL? I would have thought that the types are simply looked up in the systems tables, or do I have to do something else? Any help greatly appreciated! Even if only to tell me that this doesn't work in which case I can convert back to using integers. Cheers, Adriaan
[GENERAL] Parser or documentation bug?
Hi, I'm trying to define a new bit type with a length of two bytes, and to define a set of operators on this type. I've hit the following problem: I cannot define a | operator, as the parser doesn't like it. tt=> drop operator | (Bit2,Bit2); ERROR: parser: parse error at or near "|" tt=> create operator | ( leftarg = Bit2, rightarg = Bit2, procedure = bit2or ); ERROR: parser: parse error at or near "|" If I use || it works. So either the man page or the parser are at fault here. I'm currently using the snapshot from last Friday. I can also do a create by enclosing the | in double-quotes, as in "|", but I cannot use it, and I cannot drop it. Cheers, Adriaan
[GENERAL] psql/backend error messages
I'm seeing this in psql. I think this is bad. What could cause this? It is a join between three largish tables. I get loads of these. Backend sent B message without prior T Backend sent B message without prior T Backend sent D message without prior T Backend sent B message without prior T Backend sent D message without prior T Adriaan
[GENERAL] Timing queries
Hi, I've got a large application with hundreds of different queries. I thought I had them all sorted out (i.e. determined the correct indices to make them quick), but now I see that, with 5 copies of the application running, I'm getting some serious contention on the database. Is there some way of switching on debugging, so that I can see the query that is executed and get an elapsed+system time for its execution (elapsed to figure out whether it may be hanging on locks, better still would be information on how long a query was locked out)? >From the debugging the framework for something like this seems to exist, and I found a section in chapter 62 of the developer's guide that says something about timing, but couldn't figure out exactly what. I'm using 4.2 and a mix of perl/DBI/DBD and C++/libpq. Thanks, Adriaan
Re: [GENERAL] Desperately Seeking Regular Expression
I solved something like this recently in perl. It's not terribly
efficient, but it is simple. I'm doing this from memory, so it may need
some debugging. Use something along the lines of
#!/usr/local/bin/perl
while (<>) {
@a = split /(\")/;
# This gives you a list with some of the items being double-quotes
# Now you need to figure out which ones were inside double quotes
my $b;
if ($a[0] eq '"') {
# we started with a double quoted string, so join th e 1st 3 fields
# and stick them on the new string
$b = join('',splice(@a,0,3))."\t";
}
while (@a) {
$b .= join("\t",split(' ',shift @a))."\t";
# if there is more then we have another double quoted string
$b = join('',splice(@a,0,3))."\t" if @a;
}
# Remove the last tab and replace with a newline
$b =~ s/\t$/\n/;
print $b;
}
Adriaan
Re: [GENERAL] advice on buying sun hardware to run postgres
Jim Jennis wrote: > A DEC (sorry Compaq) Alpha running Linux is a mean combination. Used > Alpha's can be had fairly cheap and they really scream. If cost is an > issue, I would look for an older one used e.g. a > > DEC 2100/A500MP (was marketed as a "Sable") > > Put Linux on the beast and watch the smoke roll. > Yep, and they do multiple processors (on linux as well, I couldn't find a link now, but I seem to remember a report of linux running on an 8-processor Alpha 8400. www.alphalinux.org is the place to look). We use Alpha with Digital Unix, because we do lots of number crunching (we need their compilers), and it's fast and rock-solid. And the new DS-20's have got a 5.2GB/s back-plane Intel eat your heart out. The Alphas won't necessarily blow your mind on integer applications though, but they will hold their own. We switched from Sun a long time ago, and never looked back. Adriaan
Re: [GENERAL] Trigger or Rule?
> > select count(*) into cnt from where new. = key; > if (cnt>0) then > delete from where key = new. > end if > Just looked at this, and this is not actually what you wanted. If you do not want to replace the old row, do something along the lines RAISE EXCEPTION ''Duplicate entry'' which will abort the insert. It's all in the manual. Adriaan
Re: [GENERAL] Trigger or Rule?
Andy Lewis wrote:
>
> I have a table that among other things has a name, address, city, state
> fields. When I insert into, I want to be able to make sure that there is
> no duplicate records or that a row is inserted that is already in the DB.
>
> Question number one is: Should I use a trigger or a rule?
>
> And request number two is perhaps a sample that could get me started.
>
> I've read thru the Documentation and Man pages and tried creating a rule
> but, had no luck.
>
I know this isn't exactly what you want. I had a unique trigger in C,
but doing it in PL is much easier. Here is an example of a singleton --
i.e. a trigger that allows only one row in a table.
DROP FUNCTION singleton();
CREATE FUNCTION singleton () RETURNS opaque AS
'
DECLARE
BEGIN
DELETE FROM daemon;
RETURN new;
END;' LANGUAGE 'plpgsql';
DROP TRIGGER daemon_singleton ON daemon;
CREATE TRIGGER daemon_singleton BEFORE INSERT ON daemon
FOR EACH ROW EXECUTE PROCEDURE singleton('daemon');
The new row is always available in the variable 'new', so that you could
do something along the lines of
select count(*) into cnt from where new. = key;
if (cnt>0) then
delete from where key = new.
end if
Remember to return new, leave spaces around the = comparisons, and
declare the variable cnt in the declare section (as int4 or something).
The documentation for PL is actually quite good, and you should also
have a look at the examples. You need to load PL as an interpreted
language, so you need something along the lines of
DROP FUNCTION plpgsql_call_handler();
CREATE FUNCTION plpgsql_call_handler() RETURNS opaque
AS '/lib/plpgsql.so'
LANGUAGE 'C';
DROP PROCEDURAL LANGUAGE 'plpgsql';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
first.
Good luck, Adriaan
Re: [GENERAL] The WWW of PostgreSQL
The Hermit Hacker wrote: > > > > I don't want to create a polemic, but is it possible to make a > > recovery of the previous WWW interfaces of the postgreSQL.org site? > > What does an elephant do on the home page? Probably you have been > > hacked :-) > > The elephant surrounded by the diamond has been adopted as our official > logo...we've really gotta come up with a short 'explanation' of the logo > though, too many ppl are confused. > Well, I like the new logo. I think the last logo looked too 70s/early-80s and this one is great. And I don't mind the new web-page design either. Just mentioning it, because the people who like something usually stay quietAnd it is good that the logo doesn't look like all the other rectangular box-logos that have become so common. Just my 2p Adriaan
[GENERAL] Use of index with oid's
As I cannot return a complete row of a record from a function (written in PL), I return an oid. So the function looks like this CREATE FUNCTION task_next (int4) RETURNS oid AS ' DECLARE ... END; ' LANGUAGE 'plpgsql'; This function can return null. I then select the next row with tt> select * from tasksids where oid=task_next(0); and this is very slow, especially if task_next(0) returns null. I thus defined an index on the oids: tt> create unique index tasksids_oid_idx on tasksids(oid); But get the following tt=> explain select * from tasksids where oid=''::oid; NOTICE: QUERY PLAN: Index Scan using tasksids_oid_idx on tasksids (cost=2.05 size=1 width=33) EXPLAIN tt=> explain select * from tasksids where oid=task_next(0)::oid; NOTICE: QUERY PLAN: Seq Scan on tasksids (cost=2.22 size=4 width=33) EXPLAIN So why doesn't the query use the index when the oid is returned from a function? And is there a better way of getting that row (or a null record) returned from the function? This seems kind-of clumsy. Cheers, Adriaan
[GENERAL] Error in querying view
Hi I don't know whether this has been fixed in 6.4 -- I still cannot get 6.4 to compile and run on Alpha -- but I have the following error when querying a view in 6.3.2: I have a table with jobs/tasks (one job can consist of several tasks) that need to be executed, and have created a relatively complicated view (tasks_todo) on this table, which gives me all jobs that are finished. When I then try to create the following very simple view, I get an error: cb=> create view jobs_done as select j.job from jobs j where not j.job in (select job from tasks_todo); CREATE cb=> select * from jobs_done; ERROR: ExecEvalExpr: unknown expression type 108 cb=> select * from tasks_todo; job|task|priority|joblink|state ---+++---+- 58| 58| 3| 0|4 67| 67| 3| 0|4 44| 44| 3| 0|4 61| 61| 3| 0|4 70| 70| 5| 0|4 88| 96| 3| 80|2 89| 97| 3| 76|0 90| 98| 3| 77|0 91| 99| 3| 78|0 92| 100| 3| 79|0 95| 103| 3| 82|0 93| 101| 3| 80|0 96| 104| 3| 80|2 100| 108| 3| 81|0 112| 120| 3|101|0 113| 121| 3| 73|0 111| 119| 3| 74|0 (17 rows) cb=> select j.job from jobs j where not j.job in (select job from tasks_todo); job --- 55 57 59 60 42 56 74 75 77 78 79 80 81 73 86 85 87 76 101 82 (20 rows) So, is there a problem with using NOT..IN.. in a view? Any help appreciated, Adriaan
[GENERAL] Postgres on Alpha?
Has anybody managed to get any of the postgres 6.4 versions to run correctly on Alpha? I have tried 6.4 through 6.4.2 and cannot get any of them to compile/run (I managed to get some versions to compile after some minor modifications, but then they did not run without crashing). I've tried both the native cc and egcs-2.91.60. The latter complained about the return types defined in s_lock.h, but I know nothing about alpha-assembler and have no idea whether the spin lock is implemented correctly. If there was a problem there it would explain why it isn't running properly. Adriaan
Re: [GENERAL] drop database failed
Fuad Abdallah wrote: I have just compiles postgres-6.4 under irix 6.5.1 with the SGI cc v7.2.1 > and everything seems to work fine - the regression test works with some > minor deviations - but i can not delete a database. > destroydb fails with the following error: > > ERROR: typeidTypeRelid: Invalid type - oid = 0 > I had exactly the same problem after compiling on Alpha (DEC Unix 4.0D) with cc -std1, so this isn't SGI specific. Adriaan
