Description and Bug Reproduction SQL code In PostgreSQL

luckyhillockΔιαχείριση Δεδομένων

29 Νοε 2012 (πριν από 4 χρόνια και 9 μήνες)

284 εμφανίσεις



C2
-
1






Bug ID

Date
reported

Description and Bug Reproduction SQL code In PostgreSQL

Comment

3

11 May
2000

CLUSTER bug.

CLUSTER fails and appears to corrupt database.


Create a database, create a table. insert some data. create index on one
of fields. CLUS
TER indexname ON tablename;


With a large number of rows (so I can see it happening), I see the table
being copied to a temp, and the renamed back to its original name. the
problem is with the index :


DWS=# CREATE INDEX TESTIND ON ACCESSLOG
(ALCLIENT);


D
WS=# CLUSTER TESTIND ON ACCESSLOG


ERROR: temp_674e00 is an index relation



temp_674e00 appears to be a copy of the index file.


psql now tells me the relation accesslog no longer exists


the same error also occurs on small tables.


If you know how this

problem might be fixed, list the solution below

The bug has already been reported earlier in PostgreSQL Bug ID’s
1 and 2 in Appendix C1.

6

22 May
2000

Pg_dump crashes trying to dump database containing index on oid.


CREATEDB SNAFU


PSQL
-
D SNAFU

A pg_dump bug therefore not included in the experiment. In any
case, I ran the script and pg_dump
did not crash. Dump was
carried out successfully. Possible Heisen Bug
.


A
A
P
P
P
P
E
E
N
N
D
D
I
I
X
X


C
C
2
2
.
.


P
P
O
O
S
S
T
T
G
G
R
R
E
E
S
S
Q
Q
L
L


B
B
U
U
G
G
S
S


N
N
O
O
T
T


I
I
N
N
C
C
L
L
U
U
D
D
E
E
D
D


I
I
N
N


T
T
H
H
E
E


E
E
X
X
P
P
E
E
R
R
I
I
M
M
E
E
N
N
T
T




C2
-
2


CREAT
E TABLE TEST (TEST TEXT);


CREATE INDEX IDX_TEST_OID ON TEST (OID);


\
Q


PG_DUMP SNAFU



7

24 May
2000

Bug in the jdbc driver for postgress 7.0 (ResultSet.java).


According to Sun's documentation for the ResultSet

interface, the rows in a result set should be numbered starting with 1.
However, the ResultSet.java's first() routine set's c
urrent_row to 0. The
odd thing is that beforeFirst() also sets current_row to 0, it just does not
load this_row. The bug manifests itself as such:

given ResultSet rs containing two rows:


rs.last();

System.out.println("Last id is: "+rs.getRow());

rs.first
();

System.out.println("First id is: "+rs.getRow());


produces the output:


Last id is: 1

First id is: 0


JDBC bugs were not included in this experiment.

8

25 May
2000

dS and
\
df <pattern> crashing psql.


To replicate the problem, you need to do the foll
owing. All actions are
performed by postgres, the db superuser account:


Install postgres 7.0 with all three of
--
enable
-
locale,
--
enable
-
recode,

and
--
enable
-
multibyte specified. Set the user postgres's
LC_COLLATE env var to any of the en_* locales avai
lable on your
Client application (psql) error, not a ba
ckend bug, therefore not
included in the experiment.



C2
-
3

machine /except/

en_US.UTF
-
8, which doesn't seem to cause problems. The other locale
vars appear to be irrelevant; LC_COLLATE alone will do for
replication. These

were my settings:


> locale

LANG=

LC_CTYPE="C"

LC_NUMERIC="C"

LC_TIME="C"

LC_
COLLATE=en_US

LC_MONETARY="C"

LC_MESSAGES="C"

LC_ALL=


What follows are the operations I performed to get psql to crash:


> createdb
-
E LATIN1 foo

CREATE DATABASE

> psql foo

Welcome to psql, the PostgreSQL interactive terminal.

<snip>

foo=# create table Te
nChrName ( somelongname varchar (100)
unique);

NOTICE: CREATE TABLE/UNIQUE will create implicit index

'tenchrname_somelongname_key' for table 'tenchrname'

CREATE

foo=# vacuum analyze;

VACUUM

foo=#
\
dS

The connection to the server was lost. Attempting res
et: Failed.

!#
\
q

> kill `cat postmaster.pid`

> gdb postgres

<snip>

(gdb) run foo



C2
-
4


/* note: the following query is the smallest part of
\
dS's expansion


* that is sufficient for a crash


*/

backend> select * from pg_class where relname ~ '^n';

ERROR: exp
ression_tree_walker: Unexpected node type 0

ERROR: expression_tree_walker: Unexpected node type 0

backend> select * from pg_class where relname ~ '^n';

NOTICE: PortalHeapMemoryFree: 0x51c330 not in alloc set!

NOTICE: PortalHeapMemoryFree: 0x51c330 not
in alloc set!


Program received signal SIGBUS, Bus error.

0x21ddf4 in AllocSetAlloc (set=0x500ff8, size=12) at aset.c:233

233 if (chunk
-
>size >= size)

(gdb) bt

#0 0x21ddf4 in AllocSetAlloc (set=0x500ff8, size=12) at aset.c:233

#1 0x21
f8a0 in PortalHeapMemoryAlloc (this=0x2bddc0, size=12)


at portalmem.c:253

#2 0x21ed20 in MemoryContextAlloc (context=0x2bddc0, size=12) at

mcxt.c:224

#3 0x126e84 in newNode (size=12, tag=T_List) at nodes.c:38

#4 0x127180 in lcons (obj=0x51a240, list
=0x0) at list.c:112

#5 0x127220 in lappend (list=0x0, obj=0x51a240) at list.c:144

#6 0x14e6f8 in get_actual_clauses (restrictinfo_list=0x51a298)


at restrictinfo.c:55

#7 0x144b80 in create_scan_node (root=0x5134f8,
best_path=0x51be80,


tlist=0x51
b0b0) at createplan.c:152

#8 0x144ab0 in create_plan (root=0x5134f8, best_path=0x51be80)


at createplan.c:103

#9 0x147698 in subplanner (root=0x5134f8, flat_tlist=0x51a4a0,


qual=0x51a280, tuple_fraction=0) at planmain.c:288

#10 0x14740c in query_
planner (root=0x5134f8, tlist=0x519b08,

qual=0x51a280,


tuple_fraction=0) at planmain.c:128

#11 0x14817c in union_planner (parse=0x5134f8, tuple_fraction=0)


at planner.c:530



C2
-
5

#12 0x147b38 in subquery_planner (parse=0x5134f8, tuple_fraction=
-
1)


a
t planner.c:202

#13 0x147810 in planner (parse=0x5134f8) at planner.c:67

#14 0x1977c0 in pg_plan_query (querytree=0x5134f8) at
postgres.c:512

#15 0x197a9c in pg_exec_query_dest (


query_string=0x2ba070 "select * from pg_class where relname ~
'^n';

\
n",


dest=Debug, aclOverride=0 '
\
000') at postgres.c:646

#16 0x1978e4 in pg_exec_query (


query_string=0x2ba070 "select * from pg_class where relname ~
'^n';

\
n")


at postgres.c:562

#17 0x1996f4 in PostgresMain (argc=2, argv=0xeffffa64, real_argc=2,


real_argv=0xeffffa64) at postgres.c:1590

#18 0x1026d0 in main (argc=2, argv=0xeffffa64) at main.c:103


If you actually care to go through the steps above, don't leave anything
out. The vacuum analyze serves no useful purpose, but you

won't get a cras
h if you omit it. The table indentifiers really do need to
be around 10 chars long. The regexp needs to match the front of a
string, so use '^foo'
--

I couldn't get a crash with other types of regexps
but then I didn't try too many.


With the local set
tings described above, a query on pg_proc of the type
"select * from pg_proc where proname ~ '^n';" will /always/ produce
the

following kind of error: "NOTICE: PortalHeapMemoryFree: <addr>
not in

alloc set!" before printing the result (it never causes a c
rash, AFAICT,

and always does produce a correct result). You can get this behaviour
just by connecting to template1; perhaps other tables with bytea fields

may also do this, but pg_proc does it every single time.




C2
-
6

10

29 May
2000


Bug in the large object interface provided by libpq.


The code below will reproduce it. Basically it creates a large object,
writes six 'a' characters to it, then closes it. Then,
in another
transaction, it opens the object, seeks to

position 1 from the start, writes a 'b', then seeks to position 3 from the
start and writes another 'b'. Then it closes the object and COMMITs the
transaction. Finally, in a further separate transacti
on, it calls lo_export
to write out the resulting object to a file testloseek.c.lobj I find this file,
instead of containing the string 'ababaa' as expected, contains

'^@b^@baa' where ^@ is ASCII NUL.


Compile with something like



gcc
-
o testlosee
k testloseek.c
-
lpq


The program sets the PQtrace to STDOUT and writes messages to
STDERR, so run

it with STDOUT redirected to a log file.


This is a C version of a basic regression test of guile
-
pg:


#include <stdio.h>

#include "libpq
-
fe.h"

#include "lib
pq/libpq
-
fs.h"


void exec_cmd(PGconn *conn, char *str);


main (int argc, char *argv[])

{


PGconn *conn;


int lobj_fd;


char buf[256];


int ret, i;


Oid lobj_id;



conn = PQconnectdb("dbname=test");

Uses a C program. Due to the time constraints of the project, this
bug was given lower priority, since it is more time consuming to
reproduce. Left as further work for future developers of the
project.



C2
-
7


if (PQstatus(conn) != CONNECTION_OK) {



fprintf(stderr, "Can't connect to backend.
\
n");


fprintf(stderr, "ERROR: %s
\
n", PQerrorMessage(conn));


exit(1);


}


exec_cmd(conn, "BEGIN TRANSACTION");


PQtrace (conn, stdout);


if ((lobj_id = lo_creat(conn, INV_READ | INV_WRITE)) < 0
) {


fprintf(stderr, "Can't create lobj.
\
n");


fprintf(stderr, "ERROR: %s
\
n", PQerrorMessage(conn));


exit(1);


}


fprintf(stderr, "lo_creat() returned OID %ld.
\
n", lobj_id);


if ((lobj_fd = lo_open(conn, lobj_id, INV_READ | INV_WRITE)
) <
0) {


fprintf(stderr, "Can't open lobj.
\
n");


fprintf(stderr, "ERROR: %s
\
n", PQerrorMessage(conn));


exit(1);


}


fprintf(stderr, "lo_open returned fd = %d.
\
n", lobj_fd);


if ((ret = lo_write(conn, lobj_fd, "aaaaaa", 6)) != 6) {



fprintf(stderr, "Can't write lobj.
\
n");


fprintf(stderr, "ERROR: %s
\
n", PQerrorMessage(conn));


exit(1);


}


ret = lo_close(conn, lobj_fd);


printf("lo_close returned %d.
\
n", ret);


exec_cmd(conn, "END TRANSACTION");



exec_cmd(conn
, "BEGIN TRANSACTION");


if ((lobj_fd = lo_open(conn, lobj_id, INV_READ | INV_WRITE)) <
0) {


fprintf(stderr, "Can't open lobj.
\
n");


fprintf(stderr, "ERROR: %s
\
n", PQerrorMessage(conn));


exit(1);


}


fprintf(stderr, "lo_open returned

fd = %d.
\
n", lobj_fd);



C2
-
8


if (ret)


fprintf(stderr, "Error message: %s
\
n", PQerrorMessage(conn));


if ((ret = lo_lseek(conn, lobj_fd, 1, 0)) != 1) {


fprintf(stderr, "error (%d) lseeking in large object.
\
n", ret);


fprintf(stderr, "ERROR:

%s
\
n", PQerrorMessage(conn));


exit(1);


}


if ((ret = lo_write(conn, lobj_fd, "b", 1)) != 1) {


fprintf(stderr, "Can't write lobj.
\
n");


fprintf(stderr, "ERROR: %s
\
n", PQerrorMessage(conn));


exit(1);


}


if ((ret = lo_lseek(c
onn, lobj_fd, 3, 0)) != 3) {


fprintf(stderr, "error (%d) lseeking in large object.
\
n", ret);


fprintf(stderr, "ERROR: %s
\
n", PQerrorMessage(conn));


exit(1);


}


if ((ret = lo_write(conn, lobj_fd, "b", 1)) != 1) {


fprintf(stderr,
"Can't write lobj.
\
n");


fprintf(stderr, "ERROR: %s
\
n", PQerrorMessage(conn));


exit(1);


}


ret = lo_close(conn, lobj_fd);


printf("lo_close returned %d.
\
n", ret);


if (ret)


fprintf(stderr, "Error message: %s
\
n", PQerrorMessage(con
n));


PQuntrace(conn);


exec_cmd(conn, "END TRANSACTION");



exec_cmd(conn, "BEGIN TRANSACTION");


ret = lo_export(conn, lobj_id, "testloseek.c.lobj");


printf("lo_export returned %d.
\
n", ret);


if (ret != 1)


fprintf(stderr, "Error messag
e: %s
\
n", PQerrorMessage(conn));


exec_cmd(conn, "END TRANSACTION");


exit(0);

}



C2
-
9


void exec_cmd(PGconn *conn, char *str)

{


PGresult *res;



if ((res = PQexec(conn, str)) == NULL) {


fprintf(stderr, "Error executing %s.
\
n", str);


fprintf
(stderr, "Error message: %s
\
n", PQerrorMessage(conn));


exit(1);


}


if (PQresultStatus(res) != PGRES_COMMAND_OK) {


fprintf(stderr, "Error executing %s.
\
n", str);


fprintf(stderr, "Error message: %s
\
n", PQerrorMessage(conn));


PQcl
ear(res);


exit(1);


}


PQclear(res);

}


11

29 May
2000

pg_dumpall's output not totally usable.



I downloaded PostgreSQL V7.0, compiled and installed it on another
location than 6.5.

I want to save the database used in the 6.5 version to put it on the

7.0 version. On 6.5, I typed "pg_dumpall >/tmp
/db.out" . Then, on 7.0,
I typed "psql
-
d template1
-
f /tmp/db.out" (I followed the INSTALL
file).

The database is not very big, the output is 2.5 MB.

The problem is that the output of pg_dumpall doesn't totally work, and I
found that some statements are n
ot in the proper order.


I can give the original scripts used to create the database, they are

quite small :


-
rw
-
r
--
r
--

6795 May 15 10:07 STAR_createTables.sql

-
rw
-
r
--
r
--

1500 May 15 10:07 STAR_createIndexes.sql

-
rw
-
r
--
r
--

75
86 May 22 17:55 STAR_fillTables.sql

A backup bug, not a c
ore engine bug, therefore not included in the
experiment.



C2
-
10

-
rw
-
r
--
r
--

2611 May 15 12:27 SERPME_createTables.sql

-
rw
-
r
--
r
--

1188 May 15 12:31 SERPME_createIndexes.sql

-
rw
-
r
--
r
--

755 May 15 12:33
SERPME_createTransacRecords.sql


14

7 Jun 2000

LIKE bug


Steps to involve a bug result:


1. Compilation


./configure
--
enable
-
locale

# not needed for RPMS precompiled
binaries


2. Starting postmaster


export LC_CTYPE=cs_CZ



export LC_COLLATE=cs_CZ


# this setting is important
for the

bug result


postmaster
-
S
-
D /home/pgsql/data
-
o '
-
Fe'



3. SQL steps


create table test (name text);


insert into test values ('á');

# the first char is E1 from LATIN 2

coding


insert i
nto test values ('áb');


create index test_index on test (name);


set cpu_tuple_cost=1;


# force backend to use
index

scanning


select * from test where name like 'á%';


BUG: Only 1 line is selected with 'á' only instead of both lines.


Lower priorit
y. Left for the end of the experiment. Due time
constraints did not have time to reproduce.

18

14 Jun
2000

Problems with inet type
.


Table "IPADDRESSES"


A backup/restore bug, not a core engine bug therefore not included
in the experiment
.



C2
-
11


Attribute | Type | Modifier

--------------
+
---------
+
---------------------
-------


sysname | text | not null


index | integer | not null


ip_addr | inet | not null


Indices: ipaddresses_ip_addr,


ipaddresses_pkey


(sysname, ip_addr)
-

PRIMARY KEY



View "IPADDRESSES_VIEW"


Attribute | Type

| Modifier

--------------
+
---------
+
----------


sysname | text |


index | integer |


ip_addr | inet |


ip_netmask | inet |


View definition:

SELECT IPADDRESSES.SYSNAME, IPADDRESSES."INDEX",

IPV4_HOST(IPADDRESSES.IP_ADDR
) AS IP_ADDR,

IPV4_NETMASK(IPADDRESSES.IP_ADDR) AS IP_NETMASK
FROM IPADDRESSES;




ipv4_host and ipv4_netmask like original host and netmask but return
inet type ( need for ORDER )


CREATE FUNCTION ipv4_host(inet) RETURNS inet AS '

BEGIN

RETURN host($1);

E
ND;

' LANGUAGE 'plpgsql';


CREATE FUNCTION ipv4_netmask(inet) RETURNS inet AS '



C2
-
12

BEGIN

RETURN netmask($1);

END;

' LANGUAGE 'plpgsql';



**************************** Problem
************************


SELECT * FROM IPADDRESSES WHERE SYSNAME =
'SWITCH01.TOR';



sysname | index | ip_addr

--------------
+
-------
+
------------------


switch01.tor | 1 | 127.0/8


switch01.tor | 2 | 127.0/8


switch01.tor | 3 | 209.250.155.8/27

(2 rows)


but (sysname, ip_addr
)
-

PRIMARY KEY


127.0/8
-

is not correct output ( real 127.0.0.2/8 and 127.0.0.3/8)


SELECT * FROM IPADDRESSES_VIEW WHERE SYSNAME =
'SWITCH01.TOR';



sysname | index | ip_addr | ip_netmask

--------------
+
-------
+
---------------
+
---------
--------


switch01.tor | 1 | 127.0.0.2 | 255.0.0.0


switch01.tor | 2 | 127.0.0.3 | 255.0.0.0


switch01.tor | 3 | 209.250.155.8 | 255.255.255.224

(2 rows)



127.0.0.2 | 255.0.0.0 and 127.0.0.3 | 255.0.0.0
-

is c
orrect output


And of course after pg_dump and restore correct value 127.0.0.2/8 and


C2
-
13

127.0.0.3/8 will be lost and will have problem with PRIMARY KEY
-

(sysname, ip_addr).


25

7 Jul 2000

exp(x) vs :


SELECT : 1;
--

returns e

Works, but gives a NOTICE that the : operator is depreciated and that
exp(x) should be used instead.


SELECT exp(1);

Gets ERROR: exp(INT4) does not exist.


SELECT exp(1.0);

Works fine of course.


Not
SQL server related. Can be implemented by the clients,
therefore not included in the experiment. Since it was easy to
reproduce, out of curiosity I ran the script on PostgreSQL and they
were giving the problems reported. Not reproducible in Interbase
since

the exp(x) and : oprators are not supported.

26

7 July
2000

Factorial operator gets parser error.


SELECT 3 !;

ERROR: parser error at or near ""


SELECT 3 !

;

Works ok.


Parser error occurs if the ending ; is on the same line.


Not SQL server related. C
an be implemented by the clients,
therefore not included in the experiment. Since it was easy to
reproduce, out of curiosity I ran the script on PostgreSQL and they
were giving the problems reported. Not reproducible in Interbase
since the factorial operat
or is not supported.

33

6 Aug
2000

Database in recovery mode


The database state before running the query.

year | manufacturer | model | stocknumber | quantity |

realprice | province | country | id | pricecurrency

------
+
--------------
+
-----
----------
+
-------------
+
----------
+
-------

----
+
----------
+
---------
+
-------
+
---------------


1998 | Kenworth | T2000 | | 1

| | Manitoba | Canada | 31990 | CAD


1999 | Kenworth | W900B | |

1

| | Manitoba | Canada | 31991 | CAD

Incomplete bug report. The schema definition is missing. When
tried to reverse reproduce, got an error that attributes cat1 and
cat2 were missing.



C2
-
14


1998 | Kenworth | W
-
900 Damaged | | 1

| | Missouri | USA | 16535 | USD


1998 | Kenworth | W900L | 755934 | 1

| | Indiana |
USA | 16496 | USD


1997 | Kenworth | W900L | 742184 | 1

| | Indiana | USA | 23936 | USD


1997 | Kenworth | T600 | | 1

| | Manitoba | Canada | 31989 | CAD


1997 | Kenwor
th | T600 | #050520
-
97 | 1 |

47500.00 | Quebec | Canada | 32675 | USD


1999 | Kenworth | T600 | 99130 | 1 |

79500.00 | Ontario | Canada | 32829 | CAD


1998 | Kenworth | W900L | 758000

| 1

| | Indiana | USA | 22370 | USD


1997 | Kenworth | W900L | #170420 | 3

| | Quebec | Canada | 32215 | USD


1997 | Kenworth | T800 | #01502
-
15 | 5 |

34500.00 | Quebec
| Canada | 24482 | USD


1997 | Kenworth | W900L | 2
-
00
-
54/56 | 2 |

51950.00 | Ontario | Canada | 32620 | USD


1997 | Kenworth | W900L | fmdc | 1

| | Ontario | Canada | 31932 | CAD


2000 | Kenw
orth | T800 | | 1

| | Texas | USA | 33111 | USD


The bug reproduction steps:


/usr/local/pgsql/bin/psql
-
U equipment


Type:
\
copyright for distribution terms


\
h for help with SQL commands


\
?
for help on internal slash commands


\
g or terminate with semicolon to execute query


\
q to quit


equipment=> SELECT year, manufacturer, model, stocknumber,
quantity, realprice, province, country, id,pricecurrency FROM


C2
-
15

ad_trucks AS AD WHERE act
ive='t' cat1=9 AND cat2=4576 AND
UPPER(manufacturer) LIKE '%KENWORTH%' AND year

BETWEEN 1997 AND 2000;


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.



34

16 Aug
2000

Serial field dump bug.


$

psql mdb

...

mdb=# create table "Qq"("Id" serial, "Name" text);

NOTICE: CREATE TABLE will create implicit sequence 'Qq_Id_seq'
for SERIAL column 'Qq.Id'

NOTICE: CREATE TABLE/UNIQUE will create implicit index
'Qq_Id_key' for table 'Qq'

CREATE

mdb=#
\
q

$
pg_dump
-
c
-
t qq
-
f qq_dump mdb

$ psql
-
e mdb <qq_dump


You are now connected as new user postgres.


DROP TABLE "qq";

DROP


CREATE TABLE "qq" (

"Id" int4 DEFAULT nextval('qq_Id_seq'::text) NOT NULL,

"Name" text

);

CREATE


COPY "qq" FROM stdin;

DROP INDEX "
qq_Id_key";

Pg_dump b
ug rather then core engine bug therefore not included
as part of this experiment.



C2
-
16


ERROR: index "qq_Id_key" nonexistent


CREATE UNIQUE INDEX "qq_Id_key" on "qq" using btree ( "Id"
"int4_ops" );

CREATE


$ psql mdb

...


mdb=# insert into qq ("Name") values ('q1');


ERROR: Relation 'qq_id_seq' does not exist

mdb=#


35

23 Aug
2000

Not performing index scan for 64bit primary.


When performing a query against a table that has a 64bit (int8) primary
key a sequential scan always takes place
. Changing the key to a 32bit
(int4) one will result in the _pkey index being used (after a vacuum
-
analyze is ran). This results in a huge performance hit when using 64bit
foreign keys and referential integrity checks when the data volumes are
large (>1M
rows).


#!/bin/sh


mydb="testdb"


tmp="/tmp/$$.sql"


# create some tables

cat > ${tmp} <<!!

drop table with32bitkey;

drop table with64bitkey;

create table with32bitkey(mykey int4 primary key, stuff
varchar(8000));

create table with64bitkey(mykey int8 prima
ry key, stuff
Requires a large database to fully test. Due to timing constraints
there was no time to populate a database with >1M rows. Left as
further work fo
r future developers.



C2
-
17

varchar(8000));

begin;

!!


# put some rows in the tables

for i in `seq 99`

do

cat >> ${tmp} <<!!

insert into with32bitkey(mykey,stuff) values (1000${i},'MyStuff');

insert into with64bitkey(mykey,stuff) values (1000${i},'MyStuff');

insert into

with32bitkey(mykey,stuff) values (2000${i},'MyStuff');

insert into with64bitkey(mykey,stuff) values (2000${i},'MyStuff');

insert into with32bitkey(mykey,stuff) values (3000${i},'MyStuff');

insert into with64bitkey(mykey,stuff) values (3000${i},'MyStuff');

insert into with32bitkey(mykey,stuff) values (4000${i},'MyStuff');

insert into with64bitkey(mykey,stuff) values (4000${i},'MyStuff');

insert into with32bitkey(mykey,stuff) values (5000${i},'MyStuff');

insert into with64bitkey(mykey,stuff) values (5000${i}
,'MyStuff');

insert into with32bitkey(mykey,stuff) values (6000${i},'MyStuff');

insert into with64bitkey(mykey,stuff) values (6000${i},'MyStuff');

insert into with32bitkey(mykey,stuff) values (7000${i},'MyStuff');

insert into with64bitkey(mykey,stuff) valu
es (7000${i},'MyStuff');

insert into with32bitkey(mykey,stuff) values (8000${i},'MyStuff');

insert into with64bitkey(mykey,stuff) values (8000${i},'MyStuff');

insert into with32bitkey(mykey,stuff) values (9000${i},'MyStuff');

insert into with64bitkey(mykey
,stuff) values (9000${i},'MyStuff');

!!

done


# build some stats and then run the queries

cat >> ${tmp} <<!!

end;

vacuum analyze with32bitkey;

vacuum analyze with64bitkey;

explain select * from with32bitkey where mykey=5;

explain select * from with64bitkey

where mykey=5;

drop table with32bitkey;



C2
-
18

drop table with64bitkey;

!!


psql
-
d ${mydb} < ${tmp}


rm
-
f ${tmp}


36

23 Aug
2000

Foreign keys referencing read
-
only tables fail.


CREATE TABLE BAR(ID INT PRIMARY KEY, STR TEXT);

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit
index 'bar_pkey' for table 'bar'

CREATE


INSERT INTO BAR VALUES(0
, 'ZERO');

INSERT 139693 1


INSERT INTO BAR VALUES(1, 'ONE');

INSERT 139694 1


INSERT INTO BAR VALUES(2, 'TWO');

INSERT 139695 1


REVOKE ALL ON BAR FROM PUBLIC;

CHANGE


GRANT SELECT ON BAR TO NAGYA;

CHANGE


SELECT * FROM BAR;


id | str

----
+
------


0 |
zero


1 | one


2 | two

(3 rows)


Same bug reported on PostgreSQL Bug ID 73 in
Appendix C1
.



C2
-
19

CREATE TABLE FOO (BARID INT REFERENCES BAR(ID));

NOTICE: CREATE TABLE will create implicit trigger(s) for
FOREIGN KEY check(s)

CREATE


GRANT ALL ON FOO TO NAGYA;


connect to the database as user nagya and perform;


INSE
RT INTO FOO VALUES(0);

ERROR: bar: Permission denied.

INSERT INTO FOO VALUES(5);

ERROR: bar: Permission denied.


38

25 Aug
2000

Wrong permissions for triggers invoked by rewrite rules.


Rewritte
n queries are normally executed with the permissions of the
creator of the rewrite rule. However, triggers invoked by the rewritten
query are not executed with the new permissions.


--

as user 'user1':

create table table1(id int);

create table table2(id in
t);

create function foo() returns opaque as 'begin


insert into table2 values(new.id);


return new;

end;' language 'plpgsql';


create trigger bar before insert on table2 for each row execute
procedure foo();


create view view1 as select * from table1;

cr
eate rule view1_ins as on insert to view1 do instead insert into table1
values(new.id);


grant insert, select on view1 to user2;


Uses language ‘plpgsql’,
therefore not included in this experiment.



C2
-
20

--

as user 'user2':

insert into view1 values(1);


--

fails with: ERROR: table2: Permission denied.


41

31 Aug
2000

Re:


CREATE TABLE "USERS" (


NAME VARCHAR(10) PRIMARY KEY

);

CREATE TABLE "STAT" (

PEERNAME VARCHAR(10) REFERENCES USERS(NAME)

);


REVOKE ALL ON STAT,USERS FROM PUBLIC;

GRANT INSERT, SELECT ON S
TAT TO STATIST;

GRANT SELECT ON USERS TO STATIST;

GRANT ALL ON STAT TO BASEMASTER;


INSERT INTO USERS VALUES('NIVA');

INSERT INTO STAT VALUES
('NIVA',ABSTIME('NOW'),TIMESPAN(0),8607272,1829436);

---------------------

After that make the connection as the
user statis and execute the
command:

-----------------------

insert into stat

values('niva',abstime('now'),timespan(abstime('now')
-
(select
max(timewrite)

from stat)),50,50);";

--------------------------

Message:Access denied on users...


Already reported o
n postgreSQL Bug ID’s 36 and 40 in
Appendix
C1
.

44

9 Sep 2000

Backend crash trying to delete rows.




create table master(


id_master serial,

Unclear bug report. Followed the steps exactly as per bug report,
but could not reproduce, since attribute sl
ave in table slave is not
defined. I tried with id_slave, still wrong since id_slave is of type
serial while the value ‘Rod’ which is trying to be inserted is of type


C2
-
21


name varchar,


primary key(id_master)

);


create table slave(


id_slave serial,


id_master int4,


primary

key(id_slave),


foreign key(id_master) references master(id_master) on delete cascade

);


insert into master(name) values('Andrew');

insert into slave(id_master,slave) values(1,'Rod');


#this one works fine

delete from master;


alter table slave rename t
o slave2;


insert into master(name) values('Andrew');

insert into slave(id_master,slave) values(2,'Rod');


#this one doesn’t!

delete from master;


char.

49

6 Oct 2000

Bug with permissions.


INSERT INTO pg_group (groname, grosysid, gro
list) values

('confUser',100,'{}');


ALTER GROUP "confUser" Add User "conf
-
user";


CREATE TABLE Object_Type (




Primary_Key serial ,


PRIMARY KEY (Primary_Key),


Name char (20) NOT NULL

)

Already reported on P
ostgreSQL Bug ID’s 36 and 40 in

Appendix
C1
.



C2
-
22

;


GRANT select ON Object_Type TO GROUP "confUser";


CREATE TABLE O
bjects (


Primary_Key serial,


PRIMARY KEY (Primary_Key),


Object_Type_Index int,


FOREIGN KEY (Object_Type_Index) REFERENCES
Object_Type



ON DELETE CASCADE,


Object_Instance int ,


Name char (50) NOT NULL,


Tag char (50),


UNIQUE (Name,Object_Type_In
dex)

)

;


GRANT select ON Objects TO GROUP "confUser";


CREATE TABLE Contact (


Primary_Key serial ,


PRIMARY KEY (Primary_Key),


User_Index int,


Greeting_Index int REFERENCES Objects,


First_Name char (12) ,


Last_Name char (12) ,


Company_Name char (3
0) ,


Title char (30) ,


Company_Type int REFERENCES Objects,


Assistant_Name char (30) ,


On_Comments varchar (512) ,


Off_Comments varchar (512) ,


Not_Current CHAR DEFAULT 0 NOT NULL

)

;




C2
-
23

GRANT select, insert, update ON Contact TO GROUP "confUser";


idg
-
wendel=> insert into contact (user_index, greeting_index,
first_name,last_name, company_name, title, company_type) values (1,
3, 'Wendel', 'Leibe','Team', 'Software Engineer', 3);

ERROR: objects: Permission denied.

idg
-
wendel=>

54

20 Oct
2000

ESQL
-
C INSERT Problem.


An embedded SQL/C program INSERT operation fails, if a string value
submitted through a host variable ends with a backslash character. This
bug is not triggered if the off
ending variable is the last variable in the
INSERT statement.


CREATE TABLE t1 ( f1 CHAR(10), f2 CHAR(10) );


ESQL/C Code:


--
BEGIN
-
FILE
--

#include <stdio.h>

#include <stdlib.h>

#include <string.h>


EXEC SQL INCLUDE sqlca;


int main(void)

{


EXEC SQL BEGI
N DECLARE SECTION;


char f1[11];


char f2[11];


EXEC SQL END DECLARE SECTION;



EXEC SQL WHENEVER SQLERROR GOTO sql_error;


EXEC SQL CONNECT TO testdb;


strcpy(f1, "something
\
\
");


strcpy(f2, "FOO");


EXEC SQL INSERT INTO t1 ( f1, f2 )

Uses a C program. Due to time constraints on the project did not
manage to compile and run. Left as further work for future
developers.



C2
-
24


VALUES ( :
f1, :f2 );


EXEC SQL COMMIT WORK;


EXEC SQL DISCONNECT;


return 0;

sql_error:


EXEC SQL WHENEVER SQLERROR STOP;


fprintf(stderr, "internal error: SQL error: %s",


sqlca.sqlerrm.sqlerrml


? sqlca.sqlerrm.sqlerrmc : "unknown error");


EXEC SQ
L ROLLBACK;


EXEC SQL DISCONNECT;


return
-
1;

}

--
END
-
FILE
--


56

25 Oct
2000

Bug in views/aggregates


CREATE TABLE m
aster (


id int4 not null,


no int4 check (no >= 0) default 0,


primary key (id, no),


started date check ((not started is null) or (not closed)),


received date,


starter int4 not null,


description text,


closed bool default 'f',


date_of_closing timestamp,


closed_by int4);


CREATE TABLE detail (


id int4 not null,


no_ int4 not null,


primary key (id, no_, modification, archive),


ordering int4 not null,


object int4 not null,


ordered_by int4,


quantit
y numeric(14,4) not null,

Already reported on PostgreSQL Bug ID 39 in
Appendix C1
.



C2
-
25


quality int4 not null default 1,


archive bool default 'f',


starting int4,


modification int4 not null check (modification >= 0),


foreign key (id,modification) references


master(id,no));


CREATE VIEW buggy_view

AS

SELECT de.id, de.no_, de.ordering, de.object,

de.ordered_by, de.quantity, de.quality, ma.no FROM

detail de, master ma WHERE

((((ma.no >= de.starting) AND (ma.no < de.modification)) AND
de.archive)

OR ((ma.no >= de.modification) AND (NOT de.archive)
)) GROUP BY

de.id, de.no_, de.ordering, de.object,

de.ordered_by, de.quantity, de.quality, ma.no;


INSERT INTO master VALUES (1,0,now(),now(),1,'','f',now(),1);

INSERT INTO detail VALUES (1,1,1,100,1,1000,1,'f',1,0);

INSERT INTO detail VALUES (1,2,2,101,1
,2000,1,'f',1,0);


SELECT count(*) FROM buggy_view;
--

I can see two rows of result!


66

06 Jan
2001

boolean bugs


CREATE TABLE nulltest (nullfield);

INSERT INTO nulltest VALUES (null);


select (nu
llfield = 'willbenull') is null from nulltest;


?column?

----------

t

(1 row)



Unclear bug report. Could not create the tables or run the queries
with the scripts provided.



C2
-
26

select (nullfield = 'willbenull') is true from nulltest;


?column?

----------



(1 row)


select (nullfield = 'willbenull') is false from nulltest;


?column?

----------



(1 ro
w)



The IS operator is supposed to return only TRUE or FALSE, never
NULL. See ISO/IEC 9075
-
2:1999 6.30 <boolean value expression>.


71

14 Jan
2001

Strange r
esults with interval types computations

ez_money=> select '10/01/2000'::date+'1 month'::interval;

?column?

----------------------

2000
-
10
-
31 23:00:00+01

(1 row)



ez_money=> select '09/01/2000'::date+'1 month'::interval;

?column?

----------------------

200
0
-
10
-
01 00:00:00+02

(1 row)


Strange that 10/01/2000 + 1 month does not give 11/01/2000 !


Sample Code



pgsql
-
bugs@postgresql.org writes:

> ez_money=> select '10/01/2000'::date+'1 month'::interval;

Already reported on PostgreSQL Bug ID 69 in
Appendix C1
.



C2
-
27

> ?column?

>
--------------------
--

> 2000
-
10
-
31 23:00:00+01

> (1 row)


75

23 Jan
2001

inability to restore infinity values via psql or pg_restore


The exact sequence of steps required to produce the error:


postgres]$ createdb foo

postgres]$ psql foo

foo=# create table foo ( foo interval );

foo=# insert into foo( foo ) values( timestamp 'infinity'
-

timestamp '
-
infinity' );

foo=#
\
q

postgres]$ pg_dump
-
t foo foo >foo.sql

postgres]$ psql
-
c 'drop table foo' foo

postgres]$ psql
-
f fo
o.sql foo


Client and restore bug not a core engine bug, therefore not
included in the experiment.

78

26 Jan
2001

Bug with foreign keys and importing from a pg_dump file?


If you pg_dump, dropdb, createdb, and import the dump file, then the
system tables
are reporting the foreign key differently.


createdb tst

psql tst


CREATE TABLE genericfield

(


GenericID int4 PRIMARY KEY,


GenericName varchar(32) NOT NULL,


Note text

);

Pg_dump bug
, not a core engine bug, therefore not included in the
experiment. However on a more closer investigation it is worth
repeating the example on Interbase, since the example describes a
realistic scenario. Due to time constraints on the project did not
have
time to do it. Left as further work for future developers.



C2
-
28


CREATE TABLE membergenericfield

(


MemberGe
nericID int4 PRIMARY KEY,


GenericID int4 NOT NULL,


Note text,


CONSTRAINT MemberGenericFieldGenericID_fk


FOREIGN KEY (GenericID) REFERENCES
GenericField(GenericID)

);


select t.tgconstrname, c1.relname as TableName,
c2.relname as
ForiegnTable


FROM pg_trigger t


left join pg_class c1 on t.tgrelid = c1.relfilenode


left join pg_class c2 on t.tgconstrrelid = c2.relfilenode


where substr(t.tgname, 1, 3) = 'RI_' order by t.tgconstrname;



tgco
nstrname | tablename | foriegntable

--------------------------------
+
--------------------
+
--------------------


membergenericfieldgenericid_fk | membergenericfield | genericfield


membergenericfieldgenericid_fk | genericfield |
m
embergenericfield


membergenericfieldgenericid_fk | genericfield |
membergenericfield

(3 rows)


\
q


pg_dump tst > tst.dmp

dropdb tst

createdb tst

psql
-
a tst postgres < tst.dmp


psql tst postgres


select t.tgconstrname, c1.relname as TableName, c2.re
lname as


C2
-
29

ForiegnTable


FROM pg_trigger t


left join pg_class c1 on t.tgrelid = c1.relfilenode


left join pg_class c2 on t.tgconstrrelid = c2.relfilenode


where substr(t.tgname, 1, 3) = 'RI_' order by t.tgconstrname;



tgconstrn
ame | tablename | foriegntable

--------------------------------
+
--------------------
+
--------------


membergenericfieldgenericid_fk | membergenericfield | pg_xactlock


membergenericfieldgenericid_fk | genericfield | pg_xactlock


mem
bergenericfieldgenericid_fk | genericfield | pg_xactlock

(3 rows)


\
q


It looks like pg_dump neglects to emit a "FROM pktable" clause in its

CREATE CONSTRAINT TRIGGER commands, and so the
tgconstrrelid field of

pg_trigger is not restored.