Thread: 9.1beta2 / UNLOGGED + CHECK + INHERITS

I wanted to see how much faster unlogged tables might be for an
app I have, so as a quick test I did:

s/CREATE TABLE/CREATE UNLOGGED TABLE/ to get some numbers.
Which lead to a crash.

Here is a trimmed down test case:
$ cat > unlog-test.sql
CREATE UNLOGGED TABLE leases (
    mac macaddr NOT NULL,
    ip inet NOT NULL,
    start_ts timestamp with time zone NOT NULL,
    end_ts timestamp with time zone NOT NULL,
    id bigint NOT NULL,
    truncated integer,
    router_ip inet,
    CONSTRAINT leases_check CHECK ((start_ts < end_ts))
);
CREATE UNLOGGED TABLE closed_leases (
)
INHERITS (leases);
CREATE UNLOGGED TABLE open_leases (
)
INHERITS (leases);
^D

$ psql91 -U postgres postgres -c '\i unlog-test.sql'
CREATE TABLE
psql91:unlog-test.sql:13: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
psql91:unlog-test.sql:13: connection to server was lost

The CHECK CONSTRAINT on leases is required to cause the crash on
creating closed_leases.

Garick

On Fri, Jun 17, 2011 at 1:01 PM, Garick Hamlin <ghamlin@isc.upenn.edu> wrote:
> I wanted to see how much faster unlogged tables might be for an
> app I have, so as a quick test I did:
>
> s/CREATE TABLE/CREATE UNLOGGED TABLE/ to get some numbers.
> Which lead to a crash.
>
> Here is a trimmed down test case:
> $ cat > unlog-test.sql
> CREATE UNLOGGED TABLE leases (
>    mac macaddr NOT NULL,
>    ip inet NOT NULL,
>    start_ts timestamp with time zone NOT NULL,
>    end_ts timestamp with time zone NOT NULL,
>    id bigint NOT NULL,
>    truncated integer,
>    router_ip inet,
>    CONSTRAINT leases_check CHECK ((start_ts < end_ts))
> );
> CREATE UNLOGGED TABLE closed_leases (
> )
> INHERITS (leases);
> CREATE UNLOGGED TABLE open_leases (
> )
> INHERITS (leases);
> ^D
>
> $ psql91 -U postgres postgres -c '\i unlog-test.sql'
> CREATE TABLE
> psql91:unlog-test.sql:13: server closed the connection unexpectedly
>        This probably means the server terminated abnormally
>        before or while processing the request.
> psql91:unlog-test.sql:13: connection to server was lost

Fixed, thanks for the report!

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company