Re: foregin table insert error

On 12/14/2014 09:30 AM, Adrian Klaver wrote:
> On 12/14/2014 01:13 AM, Ed Rahn wrote:
>> Hi,
>> I have a foreign table that I'm getting an insert error on:
>>
>> horsedata=# insert into remote_cache (entry_id, name_id) values(2,1);
>> ERROR: null value in column "id" violates not-null constraint
>> DETAIL: Failing row contains (null, 1, 2, null).
>> CONTEXT: Remote SQL command: INSERT INTO public.cache(id, name_id,
>> entry_id, value) VALUES ($1, $2, $3, $4)
>>
>>
>> Here is the remote table client side:
>> horsedata=# \d remote_cache
>> Foreign table "public.remote_cache"
>> Column | Type | Modifiers | FDW Options
>> ----------+---------+-----------+-------------
>> id | integer | |
>> name_id | integer | |
>> entry_id | integer | |
>> value | integer | |
>> Server: home
>> FDW Options: (table_name 'cache')
>>
>>
>> And here's cache server side:
>> horsedata=# \d cache;
>> Table "public.cache"
>> Column | Type | Modifiers
>> ----------+------------------+----------------------------------------------------
>>
>>
>> id | integer | not null default
>> nextval('cache_id_seq'::regclass)
>> name_id | integer |
>> entry_id | integer |
>> value | double precision |
>> Indexes:
>> "cache_pkey" PRIMARY KEY, btree (id)
>> "cache_name_id_entry_id_key" UNIQUE CONSTRAINT, btree (name_id,
>> entry_id)
>> "ix_cache_entry_id" btree (entry_id)
>> "ix_cache_name_id" btree (name_id)
>>
>>
>> Any suggestions?
>
> Yes, see here:
>
> http://www.postgresql.org/message-id/CA+mi_8bfkaFPNPPx6_W_T_0J9OEMSfXQKCDZo=OMJpWWcCKtoA@mail.gmail.com
>
>
I tried something similar using -1 as well as the above:
On server I set:
create function inc_id_cache() returns trigger as $inc$
begin
if NEW.id = NULL then
NEW.id := nextval('cache_id_seq');
end if;
return NEW;
end;
$inc$ language plpgsql;

create trigger inc before insert on cache for each row execute procedure
inc_id_cache();

Now in both cases I get:
horsedata=# insert into remote_cache(name_id, entry_id) values (1, 8);
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, 1, 8, null).
CONTEXT: Remote SQL command: INSERT INTO public.cache(id, name_id,
entry_id, value) VALUES ($1, $2, $3, $4)

thanks
Ed