BUG #6643: [PostgreSQL9.2beta1] COPY after changing fillfactor gets a PANIC.

The following bug has been logged on the website:

Bug reference: 6643
Logged by: Tomonari Katsumata
Email address: katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp
PostgreSQL version: Unsupported/Unknown
Operating system: RHEL 6.2 x86_64
Description:

Hi,

Now, I'm testing PostgreSQL 9.2 Beta 1.
And I have a problem.

Steps to procedure are bellow.

1. CREATE DATABASE
export LANG=C
initdb -D $PGDATA -E SQL_ASCII
pg_ctl start
createdb testdb

2. CREATE TABLE
psql -d testdb -f ./create_table_customer.sql

3. ALTER TABLE(fillfactor)
psql -d testdb -c "ALTER TABLE customer SET (fillfactor=90);"

4. LOAD DATA
(please set correct path to customer.data)
psql -d testdb -f ./customer.sql

Then, I have a PANIC error.
==============================
BEGIN
TRUNCATE TABLE
PANIC: failed to add tuple to page
CONTEXT: COPY customer, line 296:
"296>ALNGAT>alngat>EgBEEAyXVIAWBE>KCiDDFsqA8Kv>2586068>4067234479>ALNGAT(at)kuvkaEEyi>20100905>20101023>..."
STATEMENT: COPY customer FROM
'/home/katsumata/work/2012/20120516_PG92beta1_bug1/copy_panic_dbt1/copy_panic/customer.data'
WITH DELIMITER '>';
psql:./customer.sql:3: PANIC: failed to add tuple to page
CONTEXT: COPY customer, line 296:
"296>ALNGAT>alngat>EgBEEAyXVIAWBE>KCiDDFsqA8Kv>2586068>4067234479>ALNGAT(at)kuvkaEEyi>20100905>20101023>..."
PANIC: failed to add tuple to page
CONTEXT: COPY customer, line 296:
"296>ALNGAT>alngat>EgBEEAyXVIAWBE>KCiDDFsqA8Kv>2586068>4067234479>ALNGAT(at)kuvkaEEyi>20100905>20101023>..."
psql:./customer.sql:3: connection to server was lost
==============================

If I skip the 3rd step(ALTER TABLE(fillfactor)),
I don't have any ERROR.
And It's also OK on PostgreSQL 9.1.3.

Are there any changes about this behavior ?

On 16.05.2012 13:39, katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp wrote:
> Now, I'm testing PostgreSQL 9.2 Beta 1.
> And I have a problem.
>
> Steps to procedure are bellow.
>
> 1. CREATE DATABASE
> export LANG=C
> initdb -D $PGDATA -E SQL_ASCII
> pg_ctl start
> createdb testdb
>
> 2. CREATE TABLE
> psql -d testdb -f ./create_table_customer.sql
>
> 3. ALTER TABLE(fillfactor)
> psql -d testdb -c "ALTER TABLE customer SET (fillfactor=90);"
>
> 4. LOAD DATA
> (please set correct path to customer.data)
> psql -d testdb -f ./customer.sql
>
> Then, I have a PANIC error.
> ==============================
> BEGIN
> TRUNCATE TABLE
> PANIC: failed to add tuple to page
> CONTEXT: COPY customer, line 296:
> "296>ALNGAT>alngat>EgBEEAyXVIAWBE>KCiDDFsqA8Kv>2586068>4067234479>ALNGAT(at)kuvkaEEyi>20100905>20101023>..."
> STATEMENT: COPY customer FROM
> '/home/katsumata/work/2012/20120516_PG92beta1_bug1/copy_panic_dbt1/copy_panic/customer.data'
> WITH DELIMITER '>';
> psql:./customer.sql:3: PANIC: failed to add tuple to page
> CONTEXT: COPY customer, line 296:
> "296>ALNGAT>alngat>EgBEEAyXVIAWBE>KCiDDFsqA8Kv>2586068>4067234479>ALNGAT(at)kuvkaEEyi>20100905>20101023>..."
> PANIC: failed to add tuple to page
> CONTEXT: COPY customer, line 296:
> "296>ALNGAT>alngat>EgBEEAyXVIAWBE>KCiDDFsqA8Kv>2586068>4067234479>ALNGAT(at)kuvkaEEyi>20100905>20101023>..."
> psql:./customer.sql:3: connection to server was lost
> ==============================
>
> If I skip the 3rd step(ALTER TABLE(fillfactor)),
> I don't have any ERROR.
> And It's also OK on PostgreSQL 9.1.3.
>
> Are there any changes about this behavior ?

This sounds like a bug in the new page-at-a-time behavior in COPY. Can
you send me a self-contained test, including the test data?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On 16.05.2012 13:47, Heikki Linnakangas wrote:
> This sounds like a bug in the new page-at-a-time behavior in COPY. Can
> you send me a self-contained test, including the test data?

Never mind. After staring at the code for a while, I spotted the bug,
and was able to reproduce with a simpler case. It's quite easy to
reproduce when you set fillfactor even lower, like 10.

The problem is with this line in heap_multi_insert function:

if (PageGetHeapFreeSpace(page) - saveFreeSpace < MAXALIGN(heaptup->t_len))

That doesn't work as intended, because the return value of
PageGetHeapFreeSpace and saveFreeSpace are unsigned. When saveFreeSpace
is larger than the amount of free space on the page, the left hand side
of that comparison is supposed to go negative, but it wraps around to a
highly positive number because it's unsigned.

Fixed, thanks for the report!

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Hi, Heikki

I'm sorry, forgotten attach files.

I've tryed to send mail with files,
but I could not...
(I think this is my mail server problem.)

Thank you very much for fixing it!

(2012/05/16 20:14), Heikki Linnakangas wrote:
> On 16.05.2012 13:47, Heikki Linnakangas wrote:
>> This sounds like a bug in the new page-at-a-time behavior in COPY. Can
>> you send me a self-contained test, including the test data?
>
> Never mind. After staring at the code for a while, I spotted the bug,
> and was able to reproduce with a simpler case. It's quite easy to
> reproduce when you set fillfactor even lower, like 10.
>
> The problem is with this line in heap_multi_insert function:
>
> if (PageGetHeapFreeSpace(page) - saveFreeSpace <
> MAXALIGN(heaptup->t_len))
>
> That doesn't work as intended, because the return value of
> PageGetHeapFreeSpace and saveFreeSpace are unsigned. When
> saveFreeSpace is larger than the amount of free space on the page, the
> left hand side of that comparison is supposed to go negative, but it
> wraps around to a highly positive number because it's unsigned.
>
> Fixed, thanks for the report!