Thread

1. Re: Backend dies when overloading + operator for bool

   Robert B. Easter <reaster@comptechnews.com> — 2000-12-19T21:08:52Z

   On Tuesday 19 December 2000 22:11, Jeff Davis wrote:
   >
   > create function bool_to_int(bool) returns int as 'select 1 as result
   > where $1 union select 0 as result where not $1;' language 'sql';
   
   This is an alternative way to make the bool_to_int, but it doesn't solve your 
   problem unless the union was causing a bug:
   
   CREATE FUNCTION bool_to_int(BOOLEAN) RETURNS INTEGER AS '
   SELECT CASE WHEN $1 THEN 1 ELSE 0 END AS result;
   ' LANGUAGE 'SQL';
   
   
   

2. Backend dies when overloading + operator for bool

   Jeff Davis <jdavis@wasabimg.com> — 2000-12-20T03:11:31Z

   If PostgreSQL failed to compile on your computer or you found a bug that
   
   is likely to be specific to one platform then please fill out this form
   and e-mail it to pgsql-ports@postgresql.org.
   
   To report any other bug, fill out the form below and e-mail it to
   pgsql-bugs@postgresql.org.
   
   If you not only found the problem but solved it and generated a patch
   then e-mail it to pgsql-patches@postgresql.org instead.  Please use the
   command "diff -c" to generate the patch.
   
   You may also enter a bug report at http://www.postgresql.org/ instead of
   
   e-mail-ing this form.
   
   ============================================================================
   
                           POSTGRESQL BUG REPORT TEMPLATE
   ============================================================================
   
   
   
   Your name               : Jeff Davis
   Your email address      : jdavis@wasabimg.com
   
   
   System Configuration
   ---------------------
     Architecture (example: Intel Pentium)         : Intel Pentium
   
     Operating System (example: Linux 2.0.26 ELF)  : Linux 2.2.17 ELF
   
     PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.3
   
     Compiler used (example:  gcc 2.8.0)           : gcc 2.95.2
   
   
   Please enter a FULL description of your problem:
   ------------------------------------------------
   I wished to perform a query that would involve boolean value addition
   (i.e. true + false + true would equal 2 as an integer). The reason was
   to keep track of the number of matches (i.e. if conditions are seperated
   by "OR" and if row matches 3 conditions, it would have be moved higher
   in the order than a row matching only two conditions).
   
   I tried the procedure below to make the proper functions (as a user, not
   a superuser) and the backend died. A full error log is at the end of
   this email.
    Whenever I try to use my operator, it dies.
   
   
   
   Please describe a way to repeat the problem.   Please try to provide a
   concise reproducible example, if at all possible:
   ----------------------------------------------------------------------
   
   create function bool_to_int(bool) returns int as 'select 1 as result
   where $1 union select 0 as result where not $1;' language 'sql';
   
   create function bool_plus_bool (bool,bool) returns int as 'select
   bool_to_int($1) + bool_to_int($2) as result;' language 'sql';
   
   create function int_plus_bool (int,bool) returns int as 'select ($1) +
   bool_to_int($2) as result;' language 'sql';
   
   create function bool_plus_int (bool,int) returns int as 'select
   bool_to_int($1) + ($2) as result;' language 'sql';
   
   create operator +( procedure = bool_plus_bool, leftarg = bool, rightarg
   = bool);
   create operator +( procedure = bool_plus_int, leftarg = bool, rightarg =
   int);
   create operator +( procedure = int_plus_bool, leftarg = int, rightarg =
   bool);
   
   select key,a,b,((key=1)+(a='hi')+(b='bye')) as m from v1; #kills backend
   
   select (1=1)+(2=2) as a; #also kills backend
   
   If you know how this problem might be fixed, list the solution below:
   ---------------------------------------------------------------------
   
   Sorry, I am not yet familiar enough with the internals.
   
   
   
   
   
   
   ################### error log #########################
   
   FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
   binding ShmemCreate(key=52e2c1, size=1104896)
   DEBUG:  Data Base System is starting up at Tue Dec 19 10:19:01 2000
   DEBUG:  Data Base System was shut down at Tue Dec 19 10:18:52 2000
   DEBUG:  Data Base System is in production state at Tue Dec 19 10:19:01
   2000
   proc_exit(0)
   shmem_exit(0)
   exit(0)
   /usr/local/pgsql/bin/postmaster: reaping dead processes...
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 4
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 4
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling writing 4
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 5
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 5
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling writing 5
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 5
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 5
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling writing 5
   /usr/local/pgsql/bin/postmaster: BackendStartup: environ dump:
   -----------------------------------------
    PWD=/usr/local/pgsql
    HOSTNAME=dynworks1
    MANPATH=:/usr/local/pgsql/man
    PS1=\h:\w\$
    USER=jdavis
    MACHTYPE=i386-pc-linux-gnu
    MAIL=/var/mail/jdavis
    LOGNAME=jdavis
    SHLVL=3
    SHELL=/bin/sh
    HOSTTYPE=i386
    OSTYPE=linux-gnu
    HOME=/var/lib/postgres
    TERM=xterm
    PGDATA=/usr/local/pgsql/data
    PATH=/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games
    SSH_TTY=/dev/pts/0
    _=/usr/local/pgsql/bin/postmaster
    OLDPWD=/home/jdavis
    POSTPORT=5432
    POSTID=2147483646
    IPC_KEY=5432000
    PG_USER=jdavis
   -----------------------------------------
   /usr/local/pgsql/bin/postmaster: BackendStartup: pid 770 user jdavis db
   jdavis socket 5
   /usr/local/pgsql/bin/postmaster child[770]: starting with
   (/usr/local/pgsql/bin/postgres -d3 -v131072 -p jdavis )
   FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
   started: host=localhost user=jdavis database=jdavis
   InitPostgres
   StartTransactionCommand
   query: SELECT usesuper FROM pg_user WHERE usename = 'jdavis'
   parser outputs:
   
   { QUERY :command 1  :utility <> :resultRelation 0 :into <> :isPortal
   false :isBinary false :isTemp false :unionall false :distinctClause <>
   :sortClause <> :rtable ({ RTE :relname pg_user :ref { ATTR :relname
   pg_user :attrs <>} :relid 17280 :inh false :inFromCl true :inJoinSet
   true :skipAcl false}) :targetlist ({ TARGETENTRY :resdom { RESDOM :resno
   1 :restype 16 :restypmod -1 :resname usesuper :reskey 0 :reskeyop 0
   :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 5
   :vartype 16 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 5}})
   :qual { EXPR :typeOid 16  :opType op :oper { OPER :opno 93 :opid 0
   :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 19
   :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
   :consttype 19 :constlen 32 :constisnull false :constvalue  32 [ 106 100
   97 118 105 115 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]
   :constbyval false })} :groupClause <> :havingQual <> :hasAggs false
   :hasSubLinks false :unionClause <> :intersectClause <> :limitOffset <>
   :limitCount <> :rowMark <>}
   
   ProcessQuery
   CommitTransactionCommand
   StartTransactionCommand
   query: select * from v1;
   parser outputs:
   
   { QUERY :command 1  :utility <> :resultRelation 0 :into <> :isPortal
   false :isBinary false :isTemp false :unionall false :distinctClause <>
   :sortClause <> :rtable ({ RTE :relname v1 :ref { ATTR :relname v1 :attrs
   <>} :relid 19023 :inh false :inFromCl true :inJoinSet true :skipAcl
   false}) :targetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 20
   :restypmod -1 :resname key :reskey 0 :reskeyop 0 :ressortgroupref 0
   :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 20 :vartypmod
   -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom {
   RESDOM :resno 2 :restype 25 :restypmod -1 :resname a :reskey 0 :reskeyop
   0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2
   :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 2}} {
   TARGETENTRY :resdom { RESDOM :resno 3 :restype 25 :restypmod -1 :resname
   b :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
   :varno 1 :varattno 3 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold
   1 :varoattno 3}}) :qual <> :groupClause <> :havingQual <> :hasAggs false
   :hasSubLinks false :unionClause <> :intersectClause <> :limitOffset <>
   :limitCount <> :rowMark <>}
   
   ProcessQuery
   CommitTransactionCommand
   StartTransactionCommand
   query: select key,a,b,((key=1)+(a='hi')+(b='bye')) as m from v1;
   parser outputs:
   
   { QUERY :command 1  :utility <> :resultRelation 0 :into <> :isPortal
   false :isBinary false :isTemp false :unionall false :distinctClause <>
   :sortClause <> :rtable ({ RTE :relname v1 :ref { ATTR :relname v1 :attrs
   <>} :relid 19023 :inh false :inFromCl true :inJoinSet true :skipAcl
   false}) :targetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 20
   :restypmod -1 :resname key :reskey 0 :reskeyop 0 :ressortgroupref 0
   :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 20 :vartypmod
   -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom {
   RESDOM :resno 2 :restype 25 :restypmod -1 :resname a :reskey 0 :reskeyop
   0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2
   :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 2}} {
   TARGETENTRY :resdom { RESDOM :resno 3 :restype 25 :restypmod -1 :resname
   b :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
   :varno 1 :varattno 3 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold
   1 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 23
   :restypmod -1 :resname m :reskey 0 :reskeyop 0 :ressortgroupref 0
   :resjunk false } :expr { EXPR :typeOid 23  :opType op :oper { OPER :opno
   19050 :opid 0 :opresulttype 23 } :args ({ EXPR :typeOid 23  :opType op
   :oper { OPER :opno 19046 :opid 0 :opresulttype 23 } :args ({ EXPR
   :typeOid 16  :opType op :oper { OPER :opno 416 :opid 0 :opresulttype 16
   } :args ({ VAR :varno 1 :varattno 1 :vartype 20 :vartypmod -1
   :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 23 :constlen
   4 :constisnull false :constvalue  4 [ 1 0 0 0 ]  :constbyval true })} {
   EXPR :typeOid 16  :opType op :oper { OPER :opno 98 :opid 0 :opresulttype
   16 } :args ({ VAR :varno 1 :varattno 2 :vartype 25 :vartypmod -1
   :varlevelsup 0 :varnoold 1 :varoattno 2} { CONST :consttype 25 :constlen
   -1 :constisnull false :constvalue  6 [ 6 0 0 0 104 105 ]  :constbyval
   false })})} { EXPR :typeOid 16  :opType op :oper { OPER :opno 98 :opid 0
   :opresulttype 16 } :args ({ VAR :varno 1 :varattno 3 :vartype 25
   :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 3} { CONST
   :consttype 25 :constlen -1 :constisnull false :constvalue  7 [ 7 0 0 0
   98 121 101 ]  :constbyval false })})}}) :qual <> :groupClause <>
   :havingQual <> :hasAggs false :hasSubLinks false :unionClause <>
   :intersectClause <> :limitOffset <> :limitCount <> :rowMark <>}
   
   ProcessQuery
   /usr/local/pgsql/bin/postmaster: reaping dead processes...
   /usr/local/pgsql/bin/postmaster: CleanupProc: pid 770 exited with status
   11
   Server process (pid 770) exited with status 11 at Tue Dec 19 10:19:28
   2000
   Terminating any active server processes...
   Server processes were terminated at Tue Dec 19 10:19:28 2000
   Reinitializing shared memory and semaphores
   shmem_exit(0)
   binding ShmemCreate(key=52e325, size=1104896)
   DEBUG:  Data Base System is starting up at Tue Dec 19 10:19:28 2000
   DEBUG:  Data Base System was interrupted being in production at Tue Dec
   19 10:19:01 2000
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 5
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 5
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling writing 5
   DEBUG:  Data Base System is in production state at Tue Dec 19 10:19:28
   2000
   proc_exit(0)
   shmem_exit(0)
   exit(0)
   /usr/local/pgsql/bin/postmaster: reaping dead processes...
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 5
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 5
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling writing 5
   /usr/local/pgsql/bin/postmaster: BackendStartup: environ dump:
   -----------------------------------------
    PWD=/usr/local/pgsql
    HOSTNAME=dynworks1
    MANPATH=:/usr/local/pgsql/man
    PS1=\h:\w\$
    USER=jdavis
    MACHTYPE=i386-pc-linux-gnu
    MAIL=/var/mail/jdavis
    LOGNAME=jdavis
    SHLVL=3
    SHELL=/bin/sh
    HOSTTYPE=i386
    OSTYPE=linux-gnu
    HOME=/var/lib/postgres
    TERM=xterm
    PGDATA=/usr/local/pgsql/data
    PATH=/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games
    SSH_TTY=/dev/pts/0
    _=/usr/local/pgsql/bin/postmaster
    OLDPWD=/home/jdavis
    POSTPORT=5432
    POSTID=2147483644
    IPC_KEY=5432100
    PG_USER=jdavis
   -----------------------------------------
   /usr/local/pgsql/bin/postmaster: BackendStartup: pid 772 user jdavis db
   jdavis socket 5
   /usr/local/pgsql/bin/postmaster child[772]: starting with
   (/usr/local/pgsql/bin/postgres -d3 -v131072 -p jdavis )
   FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
   started: host=localhost user=jdavis database=jdavis
   InitPostgres
   /usr/local/pgsql/bin/postmaster: ServerLoop:  handling reading 4
   proc_exit(0)
   shmem_exit(0)
   exit(0)
   /usr/local/pgsql/bin/postmaster: reaping dead processes...
   /usr/local/pgsql/bin/postmaster: CleanupProc: pid 772 exited with status
   0
   pmdie 2
   Fast Shutdown request at Tue Dec 19 10:19:35 2000
   DEBUG:  Data Base System shutting down at Tue Dec 19 10:19:35 2000
   DEBUG:  Data Base System shut down at Tue Dec 19 10:19:35 2000
   proc_exit(0)
   shmem_exit(0)
   exit(0)
   /usr/local/pgsql/bin/postmaster: reaping dead processes...
   proc_exit(0)
   shmem_exit(0)
   exit(0)
   0)
   shmem_exit(0)
   exit(0)
   /usr/local/pgsql/bin/postmaster: reaping dead processes...
   /usr/local/pgsql/bin/postmaster: CleanupProc: pid 772 exited with status
   0
   pmdie 2
   Fast Shutdown request at Tue Dec 19 10:19:35 2000
   DEBUG:  Data Base System shutting down at Tue Dec 19 10:19:35 2000
   DEBUG:  Data Base System shut down at Tue Dec 19 10:19:35 2000
   proc_exit(0)
   shmem_exit(0)
   exit(0)
   /usr/local/pgsql/bin/postmaster: reaping dead processes...
   proc_exit(0)
   shmem_exit(0)
   exit(0)
   
   
   

3. Re: Backend dies when overloading + operator for bool

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-20T19:04:13Z

   7.0.* and prior releases don't support using SQL-language functions
   as operators.  7.1 will.  However, invoking three SQL queries to add
   two booleans sure seems like massive inefficiency...
   
   			regards, tom lane