Thread: Backend dies when overloading + operator for bool

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)

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';

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