Horton
Menu

Thread

  1. create type and backend terminate

    And. Andruikhanov <andy@euinf.dp.ua> — 2001-02-27T15:54:39Z

    Good day.

System Configuration
---------------------
  Architecture: Intel Pentium
  Operating System: FreeBSD 3.5-STABLE
  PostgreSQL version:   PostgreSQL-7.0.3
  Compiler used:  gcc 2.7.2.3

Description of problem
----------------------

  I have some problem with creating new type in pgsql. Server was crashed (core
dumped by signal 11). I not sure that my action was correct, but may be 
following situations can help to make "defence" of pgsql more effective.

  For example, I try to create unsigned long type (4 bytes):

#include <stdio.h>
#include <stdlib.h>
#include <limits.h>
#include "/usr/local/pgsql/include/postgres.h"

typedef int32 unsl4;

/* Function for convert external to internal representation */
unsl4 unsl4_in(char *in_s0) {
	unsl4 res=0;
	if(in_s0 == NULL) {
	  elog(NOTICE, "null ptr as argument in unsl4_in");
	  return res;
	}
	res = (unsl4)strtoul(in_s0, NULL, 10);
	return res;
}

/* Reverse transformation of new type */
char *unsl4_out(unsl4 in_d1) {
	char *res1;
	res1 = (char *)palloc(60);
	sprintf(res1, "%lu", in_d1);
	return res1;
}

Save above code to unsl4.c and compile to shared object:
gcc -O2 -shared unsl4.c -o unsl4.so

--Create function in pgsql:

create function unsl4_in(opaque) returns unsl4 as
'some_path/unsl4.so' language 'c';

create function unsl4_out(opaque) returns opaque as
'some_path/unsl4.so' language 'c';

--Then create new type:
create type unsl4(input=unsl4_in, output=unsl4_out, internallength=4,
externallength=variable);

--Try to test our new type:

sample_0=# select '-1'::unsl4;
  ?column? 
  ------------
   4294967295
   (1 row)
   
sample_0=# select 00003123456001::unsl4;
  ?column?  
  ------------
   3123456001
   (1 row)

--I think, it was all OK.

/* Then create function for operate with type 'unsl4' */

/* First example */
#include <stdio.h>
#include <stdlib.h>
#include <limits.h>
#include "/usr/local/pgsql/include/postgres.h"

typedef int32 unsl4;
unsl4 unsl4_add(unsl4 in_d1, unsl4 in_d2) {
	return in_d1+in_d2;
}

Save and compile to another shared object (unsl4_1.so).

--Create this function in pgsql:
create function unsl4_add(unsl4, unsl4) returns unsl4 as
'some_path/unsl4_1.so' language 'c';

--Then test it:

sample_0=# select unsl4_add('0003123456004', '058');
 unsl4_add  
 ------------
  3123456062
  (1 row)

sample_0=# select unsl4_add(-1::unsl4, 10::unsl4);
 unsl4_add 
 -----------
  9
  (1 row)

sample_0=# select unsl4_add(013::unsl4, 200::unsl4);
 unsl4_add 
 -----------
  213
  (1 row)
  
--All result is correct.

Then I create some error situations, I think: 
1. Drop function 'unsl4_add' in pgsql. And create it again, but with input
arguments as 'opaque' type.
2. Or create function 'unsl4_add', which take input arguments by reference:

/* Second example */
unsl4 *unsl4_add(unsl4 *in_d1, unsl4 *in_d2) {
	unsl4 *res;
	if(in_d1==NULL || in_d2==NULL)
	  return NULL;
	res = (unsl4 *)palloc(sizeof(unsl4));
	*res = (*in_d1)+(*in_d2);
	return res;
}

  After each situations (and like them) postmaster was crashed:

sample_0=# select unsl4_add('3123567101', '10');
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
		before or while processing the request.
		The connection to the server was lost. Attempting reset: Failed.

  I droped "bad" function 'unsl4_add' and recreated it again from "first
example". But postmaster crashed too. All previous examples, which works
correctly (tests of type 'unsl4') makes the crash situation also.

  Actions of full recreate 'unsl4' type (drop type 'unsl4', functions
'unsl4_*' and create them again) doesn't help in this problem (reboot of
postmaster, 'vacuum analyze' of database also). New type 'unsl4' doesn't work.

  If I create 'unsl4' by functions which takes input arguments by reference
(not fixed length), then postmaster works with new type again. If I try to
create functions for operate with 'unsl4' by several ways - some combinations
of receiving input arguments (references, fixed length), then postmaster crash
again.

  Some fragment of postmaster logfile, when it was crashed:

== begin file ==
010227.14:38:29.050 [14200] ProcessQuery
010227.14:38:29.050 [14200] CommitTransactionCommand
010227.14:38:33.999 [14200] StartTransactionCommand
010227.14:38:33.999 [14200] query: select unsl4_add('3123567101', '10');
010227.14:38:34.000 [14200] parser outputs:

{ QUERY :command 1  :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :unionall false :distinctClause <> :sortClause <> :rtable <> :targetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 49088 :restypmod -1 :resnam
e unsl4_add :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 49088  :opType func :oper { FUNC :funcid 49094 :functype 49088 :funcisindex false :funcsize 0  :func_fcache @ 0x0 :func_tlist ({ TARGETENTRY :resdom { RESDOM :resn
o 1 :restype 49088 :restypmod -1 :resname \<noname> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno -1 :varattno 1 :vartype 49088 :vartypmod -1  :varlevelsup 0 :varnoold -1 :varoattno 1}}) :func_planlist <>} :args ({ CONST :co
nsttype 49088 :constlen 4 :constisnull false :constvalue  4 [ -3 -39 45 -70 ]  :constbyval false } { CONST :consttype 49088 :constlen 4 :constisnull false :constvalue  4 [ 10 0 0 0 ]  :constbyval false })}}) :qual <> :groupClause <> :havingQual <> :hasAgg
s false :hasSubLinks false :unionClause <> :intersectClause <> :limitOffset <> :limitCount <> :rowMark <>}

010227.14:38:34.001 [14200] ProcessQuery
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 14200 exited with status 139
Server process (pid 14200) exited with status 139 at Tue Feb 27 14:38:34 2001
Terminating any active server processes...
Server processes were terminated at Tue Feb 27 14:38:34 2001
Reinitializing shared memory and semaphores
010227.14:38:34.060 [14076] shmem_exit(0)
binding ShmemCreate(key=52e4b5, size=1104896)
010227.14:38:34.064 [14201] DEBUG:  Data Base System is starting up at Tue Feb 27 14:38:34 2001
010227.14:38:34.065 [14201] DEBUG:  Data Base System was interrupted being in production at Tue Feb 27 14:37:56 2001
/usr/local/pgsql/bin/postmaster: ServerLoop:		handling reading 7
/usr/local/pgsql/bin/postmaster: ServerLoop:		handling reading 7
/usr/local/pgsql/bin/postmaster: ServerLoop:		handling writing 7
The Data Base System is starting up
/usr/local/pgsql/bin/postmaster: ServerLoop:		handling writing 7
010227.14:38:34.339 [14201] DEBUG:  Data Base System is in production state at Tue Feb 27 14:38:34 2001
010227.14:38:34.339 [14201] proc_exit(0)
010227.14:38:34.340 [14201] shmem_exit(0)
010227.14:38:34.340 [14201] exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
== end of file ==


  Best regards..
--

  2. Re: create type and backend terminate

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-27T19:40:39Z

    "And. Andruikhanov" <andy@euinf.dp.ua> writes:

> create type unsl4(input=unsl4_in, output=unsl4_out, internallength=4,
> externallength=variable);

This will not work at all, since you did not specify PASSEDBYVALUE
but your functions assume the type is pass-by-value.  An explicit
ALIGNMENT=int4 might be a good idea too, although I think that's the
default.

			regards, tom lane

  3. temp table in pl/pgsql language

    And. Andruikhanov <andy@euinf.dp.ua> — 2001-05-21T15:47:01Z

     Good day.
 
 System Configuration
 ---------------------
   Architecture: Intel Pentium
   Operating System: FreeBSD 4.2-STABLE
   PostgreSQL version:   PostgreSQL-7.1RC1
   Compiler used:  gcc 2.95.2
 
 Description of problem
 ----------------------

It's may be postgreSQL limitation, but I found nothing information in TODO list
about this subject.

 If I use CREATE TEMP TABLE name(col1..) as SELECT query 
in pl/pgsql functions - it's all OK. At the end of transaction server droped
this table automatically.
  But I can't use this possibility in TRIGGER's functions. Postgres take this
table as "permanent" (not temporary), I think. And on the second transaction
client received message as "ERROR, Relation name already exists". If I drope
this temp table in trigger's function, then error message like this: "ERROR:
Relation 32122 does not exist".


   Best regards..
 --

  4. error in expr. with array or wrong in documentation ?

    And. Andruikhanov <andy@euinf.dp.ua> — 2001-05-21T16:06:06Z

      Good day.
  
  System Configuration
  ---------------------
    Architecture: Intel Pentium
    Operating System: FreeBSD 4.2-STABLE
    PostgreSQL version:   PostgreSQL-7.1RC1 or 7.1.1
    Compiler used:  gcc 2.95.2
  
  Description of problem
  ----------------------
Chapter 6. Arrays (In Documentation). It say:
   To search the entire array (not just specified columns), you could
   use:                 
SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000;

But, If client try to execute query, for example:
select * from pg_group where grolist *= 1003::integer;

Then it received message: ERROR:  Unable to identify an operator '*=' for types
'_int4' and 'int4'
You will have to retype this query using an explicit cast


    Best regards..
  --

  5. Re: error in expr. with array or wrong in documentation ?

    Peter Eisentraut <peter_e@gmx.net> — 2001-05-21T16:45:03Z

    And. Andruikhanov writes:

> Chapter 6. Arrays (In Documentation). It say:
>    To search the entire array (not just specified columns), you could
>    use:
> SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000;
>
> But, If client try to execute query, for example:
> select * from pg_group where grolist *= 1003::integer;
>
> Then it received message: ERROR:  Unable to identify an operator '*=' for types
> '_int4' and 'int4'
> You will have to retype this query using an explicit cast

The documentation also says:

: To install this optional module, look in the contrib/array directory of
: the PostgreSQL source distribution.

Did you do that?

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

  6. timestamp (minor bug)

    And. Andruikhanov <andy@euinf.dp.ua> — 2001-07-22T11:20:59Z

      Good day.

  System Configuration
  ---------------------
    Architecture: Intel Pentium
    Operating System: FreeBSD 4.2-STABLE
    PostgreSQL version:   PostgreSQL-7.1.2
    Compiler used:  gcc 2.95.2

  Description of problem
  ----------------------

Wrong result in timestamp value between "winter" and "summer" time:

select '2001-03-24 03:00'::timestamp;
        ?column?
------------------------
 2001-03-24 03:00:00+02
(1 row)

It's OK, but another example will demonstrate error:
select '2001-03-25 03:00'::timestamp;
        ?column?
------------------------
 2035-05-22 09:58:04+03
(1 row)

NOTICE:  DateStyle is ISO with European conventions
LC_ALL=ru_RU.KOI8-R


  Best regards..
 --

  7. Re: timestamp (minor bug)

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-07-22T15:51:05Z

    "And. Andruikhanov" <andy@euinf.dp.ua> writes:
> Wrong result in timestamp value between "winter" and "summer" time:

Could you trace through the timestamp conversion routines and determine
exactly what's going wrong?  The problem is most likely in or near
DetermineLocalTimeZone() in src/backend/utils/adt/datetime.c.  We've
heard some reports of misbehavior of the C library's mktime() routine
on certain platforms, when given a time near a DST boundary in certain
timezones (what timezone do you use, anyway?).  DetermineLocalTimeZone
attempts to defend itself against this, but perhaps is not completely
successful.  Unfortunately, none of the developers have a machine on
which mktime() misbehaves, so it's hard for us to figure out much more
about the issue...

			regards, tom lane

  8. Re: timestamp (minor bug)

    And. Andruikhanov <andy@euinf.dp.ua> — 2001-07-25T11:21:05Z

    On Sun, 22 Jul 2001, Tom Lane wrote:

> "And. Andruikhanov" <andy@euinf.dp.ua> writes:
> > Wrong result in timestamp value between "winter" and "summer" time:
>
> Could you trace through the timestamp conversion routines and determine
> exactly what's going wrong?  The problem is most likely in or near
> DetermineLocalTimeZone() in src/backend/utils/adt/datetime.c.  We've

This function returns 0, when
tm->tm_hour = 3;
tm->tm_min = 0;
tm->tm_sec = 0;
tm->tm_year=2001;
tm->tm_mon=3;
tm->tm_mday=25;

otherwise it returns correctly tm_gmtoff. (in config.h HAVE_TM_ZONE defined
as 1)

> heard some reports of misbehavior of the C library's mktime() routine
> on certain platforms, when given a time near a DST boundary in certain
> timezones (what timezone do you use, anyway?).

I set TZ (or TimeZone in psql) to undefined or to my local timezone, but it
does not influences on result.
in /etc/ I have symlink "localtime" to /usr/share/zoneinfo/...


 Good Luck.
--

  9. Re: timestamp (minor bug)

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-07-29T19:46:54Z

    "And. Andruikhanov" <andy@euinf.dp.ua> writes:
> On Sun, 22 Jul 2001, Tom Lane wrote:
>> "And. Andruikhanov" <andy@euinf.dp.ua> writes:
> Wrong result in timestamp value between "winter" and "summer" time:
>> 
>> Could you trace through the timestamp conversion routines and determine
>> exactly what's going wrong?  The problem is most likely in or near
>> DetermineLocalTimeZone() in src/backend/utils/adt/datetime.c.  We've

> This function returns 0, when
> tm->tm_hour = 3;
> tm->tm_min = 0;
> tm->tm_sec = 0;
> tm->tm_year=2001;
> tm->tm_mon=3;
> tm->tm_mday=25;

> otherwise it returns correctly tm_gmtoff. (in config.h HAVE_TM_ZONE defined
> as 1)

That's not the whole story, however.  Returning zero would cause the
time to be interpreted as GMT rather than your local time, which would
make for an error of a couple of hours, not thirty-plus years.  It looks
to me like a garbage value must have been added to the time value
somewhere.  Could you dig further and determine where?

			regards, tom lane