Skip site navigation (1) Skip section navigation (2)


From: Alex Perel <veers(at)webhosting(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: INSERT INTO ... SELECT problem
Date: 2000-12-04 21:49:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Hi everyone,

I've recently encountered a bizzare problem that manifests itself reliably
on my running copy of postgres. I have a system set up to track IPs. The 
arrangement uses two mutually-exclusive buckets, one for free IPs and
the other for used ones. There are rules set up on the used pool to
remove IPs from the free on insert, and re-add them on delete.

The structure of the tables is:

CREATE TABLE "ips_free" (
        "block_id" int4 NOT NULL,
        "ip" inet NOT NULL,
        "contact_id" int4,
        "alloc_type" int4,
        PRIMARY KEY ("block_id", "ip")

CREATE TABLE "ips_used" (
        "block_id" int4 NOT NULL,
        "ip" inet NOT NULL,
        "contact_id" int4,
        "alloc_type" int4,
        PRIMARY KEY ("block_id", "ip")

The applicable rule that acts on inset to ips_used is:

CREATE RULE ip_allocated_rule AS 
         ON INSERT 
         TO ips_used
         DO DELETE FROM ips_free
                  WHERE ips_free.block_id = NEW.block_id
                    AND ips_free.ip       = NEW.ip;

When I tried to minimize the total number of queries in a data load, I
tried to get the block ID (see above for the schema definition) using
INSERT INTO ... SELECT. A query like

INSERT INTO ips_used 
SELECT block_id
     , ip
     , '1000'
  FROM ips_free
 WHERE ip = ''

simply reutrns with "INSERT 0 0" and in fact removes the IP from the
free bucket without adding it to the USED bucket. I really can't
explain this behavior and I'm hoping someone can shed a little bit of
light on it. 

I am running PostgreSQL 7.0.0 on sparc-sun-solaris2.7, compiled by gcc 2.95.2



      Alex G. Perel  -=-  AP5081
veers(at)disturbed(dot)net  -=-  alex(dot)perel(at)inquent(dot)com
               play  -=-  work                      
Disturbed Networks - Powered exclusively by FreeBSD
== The Power to Serve -=-     


pgsql-hackers by date

Next:From: Tom LaneDate: 2000-12-04 21:51:52
Subject: Re: Wrong FOR UPDATE lock type
Previous:From: Thomas GoodDate: 2000-12-04 21:30:01
Subject: Debian build failing...

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group