Thread: How to find out about the functions supported in Postgres 8

How to find out about the functions supported in Postgres 8

From
laurie.burrow@powerconv.alstom.com
Date:
In another thread (Re: [GENERAL] Return value of 'serial' column on insert)
Bruno Wolff wrote

...
> You can use the currval function to get the id. Normally the sequence
> will have a name like table_column_seq. In 8.0, there is a function
> you can use to get the sequence name, which is a bit more robust than
> hard coding it.

This sounds well useful. How do I find out what the functions available in
Postgres 8 are,  and what their usage  and calling parameters are?
Thanks
Laurie


:.________________
CONFIDENTIALITY : This  e-mail  and  any attachments are confidential and
may be privileged. If  you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.



Re: How to find out about the functions supported in Postgres

From
Richard Huxton
Date:
laurie.burrow@powerconv.alstom.com wrote:
> In another thread (Re: [GENERAL] Return value of 'serial' column on insert)
> Bruno Wolff wrote
>
> ...
>
>>You can use the currval function to get the id. Normally the sequence
>>will have a name like table_column_seq. In 8.0, there is a function
>>you can use to get the sequence name, which is a bit more robust than
>>hard coding it.
>
>
> This sounds well useful. How do I find out what the functions available in
> Postgres 8 are,  and what their usage  and calling parameters are?

The manuals are online at http://www.postgresql.org/ and come with most
installations.

As a quick reminder, you can do "\df foo*" in psql to check functions
that start with "foo".

--
   Richard Huxton
   Archonet Ltd

My postmaster just crashed !

From
PFC
Date:
    It seems that contrib/intagg crashes my server :
-------------------------------------------------------------------------
select int_agg_final_array(1);
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.
-------------------------------------------------------------------------
    Here's the logfile :

TRACE:  le systeme de bases de donnees est pret
TRACE:  processus serveur (PID 26882) a ete arrete par le signal 11
TRACE:  Arret des autres processus serveur actifs
FATAL:  Le systeme de bases de donnees est en cours de restauration
TRACE:  Tous les processus serveur se sont arretes, reinitialisation
TRACE:  le systeme de bases de donnees a ete interrompu a 2005-01-27
17:22:48 CET
TRACE:  l'enregistrement du point de verification est a 1/F3854A94
TRACE:  re-execution de l'enregistrement a 1/F3854A94 ; l'annulation de
l'enregistrement est a 0/0 ; arret TRUE
TRACE:  prochain identifiant de transaction : 5800 ; prochain OID : 4533584
TRACE:  le systeme de bases de donnees n'a pas ete arrete proprement ;
restauration automatique en cours
TRACE:  enregistrement de longueur nulle sur 1/F3854AD0
TRACE:  la re-execution n'est pas requise
TRACE:  le systeme de bases de donnees est pret


    What do you think ?

Re: My postmaster just crashed !

From
Michael Fuhr
Date:
[I've Cc'ed pgsql-bugs and set the Reply-To header to that list.]

On Thu, Jan 27, 2005 at 05:26:26PM +0100, PFC wrote:
>
>     It seems that contrib/intagg crashes my server :
> -------------------------------------------------------------------------
> select int_agg_final_array(1);
> server closed the connection unexpectedly

I see the same thing with PostgreSQL 8.0.0 (REL8_0_STABLE) on Solaris 9
and FreeBSD 4.11.  Here's part of the backtrace from the core dump on
FreeBSD:

(gdb) bt
#0  0x285077f4 in ShrinkPGArray (p=0x1) at int_aggregate.c:130
#1  0x28507929 in int_agg_final_array (fcinfo=0xbfbfeb90) at int_aggregate.c:177
#2  0x8106db8 in ExecMakeFunctionResult (fcache=0x83410a0, econtext=0x83411e8,
    isNull=0xbfbfecef "\b0í¿¿¯K\024\b¸z3\bï\003", isDone=0x0) at execQual.c:1042
#3  0x8107583 in ExecEvalFunc (fcache=0x83410a0, econtext=0x83411e8,
    isNull=0xbfbfecef "\b0í¿¿¯K\024\b¸z3\bï\003", isDone=0x0) at execQual.c:1459
#4  0x8108beb in ExecEvalExprSwitchContext (expression=0x83410a0, econtext=0x83411e8,
    isNull=0xbfbfecef "\b0í¿¿¯K\024\b¸z3\bï\003", isDone=0x0) at execQual.c:2781
#5  0x8145353 in evaluate_expr (expr=0x8337ab8, result_type=1007) at clauses.c:2399

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

Re: My postmaster just crashed !

From
PFC
Date:
    I'm sorry, I forgot to say I had the bug with 8rc3, then installed 8.0.0
and it is still crashing.

On Thu, 27 Jan 2005 10:52:37 -0700, Michael Fuhr <mike@fuhr.org> wrote:

> [I've Cc'ed pgsql-bugs and set the Reply-To header to that list.]
>
> On Thu, Jan 27, 2005 at 05:26:26PM +0100, PFC wrote:
>>
>>     It seems that contrib/intagg crashes my server :
>> -------------------------------------------------------------------------
>> select int_agg_final_array(1);
>> server closed the connection unexpectedly
>
> I see the same thing with PostgreSQL 8.0.0 (REL8_0_STABLE) on Solaris 9
> and FreeBSD 4.11.  Here's part of the backtrace from the core dump on
> FreeBSD:
>
> (gdb) bt
> #0  0x285077f4 in ShrinkPGArray (p=0x1) at int_aggregate.c:130
> #1  0x28507929 in int_agg_final_array (fcinfo=0xbfbfeb90) at
> int_aggregate.c:177
> #2  0x8106db8 in ExecMakeFunctionResult (fcache=0x83410a0,
> econtext=0x83411e8,
>     isNull=0xbfbfecef "\b0í¿¿¯K\024\b¸z3\bï\003", isDone=0x0) at
> execQual.c:1042
> #3  0x8107583 in ExecEvalFunc (fcache=0x83410a0, econtext=0x83411e8,
>     isNull=0xbfbfecef "\b0í¿¿¯K\024\b¸z3\bï\003", isDone=0x0) at
> execQual.c:1459
> #4  0x8108beb in ExecEvalExprSwitchContext (expression=0x83410a0,
> econtext=0x83411e8,
>     isNull=0xbfbfecef "\b0í¿¿¯K\024\b¸z3\bï\003", isDone=0x0) at
> execQual.c:2781
> #5  0x8145353 in evaluate_expr (expr=0x8337ab8, result_type=1007) at
> clauses.c:2399
>



Re: [BUGS] My postmaster just crashed !

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> On Thu, Jan 27, 2005 at 05:26:26PM +0100, PFC wrote:
>> It seems that contrib/intagg crashes my server :

> I see the same thing with PostgreSQL 8.0.0 (REL8_0_STABLE) on Solaris 9
> and FreeBSD 4.11.

The intagg source code says

    NOTE: This module requires sizeof(void *) to be the same as sizeof(int)

which in English means "this ain't gonna work on 64-bit machines".  I've
never bothered to look at it more closely than that ...

            regards, tom lane

Re: [BUGS] My postmaster just crashed !

From
Michael Fuhr
Date:
On Thu, Jan 27, 2005 at 02:22:36PM -0500, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > On Thu, Jan 27, 2005 at 05:26:26PM +0100, PFC wrote:
> >> It seems that contrib/intagg crashes my server :
>
> > I see the same thing with PostgreSQL 8.0.0 (REL8_0_STABLE) on Solaris 9
> > and FreeBSD 4.11.
>
> The intagg source code says
>
>     NOTE: This module requires sizeof(void *) to be the same as sizeof(int)
>
> which in English means "this ain't gonna work on 64-bit machines".  I've
> never bothered to look at it more closely than that ...

Hmmm...the PostgreSQL binaries on my Solaris/sparc box are 32-bit
and the FreeBSD box is a 32-bit i386, yet both are susceptible to
the crash.  On both boxes, a 32-bit program shows both sizeof(void *)
and sizeof(int) to be 4, so the problem doesn't appear to be due
to that necessary condition not being satisfied.

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

Re: [BUGS] My postmaster just crashed !

From
"Frank D. Engel, Jr."
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I think it is an internal thing with gcc that the size of a pointer and
sizeof(int) are always the same, regardless of machine word size...
with a 64-bit binary, sizeof(int) and sizeof(void *) should both be 8,
which still causes them to be equal.

On Jan 27, 2005, at 2:38 PM, Michael Fuhr wrote:

> On Thu, Jan 27, 2005 at 02:22:36PM -0500, Tom Lane wrote:
>> Michael Fuhr <mike@fuhr.org> writes:
>>> On Thu, Jan 27, 2005 at 05:26:26PM +0100, PFC wrote:
>>>> It seems that contrib/intagg crashes my server :
>>
>>> I see the same thing with PostgreSQL 8.0.0 (REL8_0_STABLE) on
>>> Solaris 9
>>> and FreeBSD 4.11.
>>
>> The intagg source code says
>>
>>     NOTE: This module requires sizeof(void *) to be the same as
>> sizeof(int)
>>
>> which in English means "this ain't gonna work on 64-bit machines".
>> I've
>> never bothered to look at it more closely than that ...
>
> Hmmm...the PostgreSQL binaries on my Solaris/sparc box are 32-bit
> and the FreeBSD box is a 32-bit i386, yet both are susceptible to
> the crash.  On both boxes, a 32-bit program shows both sizeof(void *)
> and sizeof(int) to be 4, so the problem doesn't appear to be due
> to that necessary condition not being satisfied.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB+UhH7aqtWrR9cZoRAnAFAJ43uG1sfDKrorDo17SoBW1+H8k7EQCfRvfH
7yQBTSKBiqkIdQ5Oh8tL2js=
=90y9
-----END PGP SIGNATURE-----



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com


Re: [BUGS] My postmaster just crashed !

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> Hmmm...the PostgreSQL binaries on my Solaris/sparc box are 32-bit
> and the FreeBSD box is a 32-bit i386, yet both are susceptible to
> the crash.

On looking at it, the problem is that the functions are defined in such
a way that you can pass any random integer value to int_agg_final_array(),
and it'll try to interpret that as a pointer.  So (a) it definitely
cannot work on 64-bit-pointer machines, and (b) it's trivial to crash it
by passing a number that's not a pointer.

The code should be rewritten by someone who has the skill to program
their way out of a paper bag :-( but I don't think there is time for a
proper fix right now.  What I'm inclined to do as a stopgap is just to
revoke all privileges from public on the two component functions of the
aggregate, since there is no particularly good reason to allow them to
be called directly anyway.

            regards, tom lane

Re: [BUGS] My postmaster just crashed !

From
Michael Fuhr
Date:
On Thu, Jan 27, 2005 at 03:00:06PM -0500, Frank D. Engel, Jr. wrote:

> I think it is an internal thing with gcc that the size of a pointer and
> sizeof(int) are always the same, regardless of machine word size...
> with a 64-bit binary, sizeof(int) and sizeof(void *) should both be 8,
> which still causes them to be equal.

Not with gcc 3.4.2 on Solaris 9/sparc -- maybe you're thinking of
sizeof(long).

% cat foo.c
#include <stdio.h>

int
main(void)
{
    printf("sizeof(void *) = %d\n", sizeof(void *));
    printf("sizeof(int)    = %d\n", sizeof(int));
    printf("sizeof(long)   = %d\n", sizeof(long));
    return 0;
}

% gcc -m32 -o foo foo.c
% ./foo
sizeof(void *) = 4
sizeof(int)    = 4
sizeof(long)   = 4

% gcc -m64 -o foo foo.c
% ./foo
sizeof(void *) = 8
sizeof(int)    = 4
sizeof(long)   = 8

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

Re: [BUGS] My postmaster just crashed !

From
Tom Lane
Date:
"Frank D. Engel, Jr." <fde101@fjrhome.net> writes:
> I think it is an internal thing with gcc that the size of a pointer and
> sizeof(int) are always the same, regardless of machine word size...
> with a 64-bit binary, sizeof(int) and sizeof(void *) should both be 8,
> which still causes them to be equal.

The above statement might be true with respect to long, but not int.

            regards, tom lane