agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Ed Rahn <edsrahn@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: foregin table insert error
Date: Sun, 14 Dec 2014 11:49:55 -0500
Message-ID: <548DBFB3.1090304@gmail.com> (raw)
In-Reply-To: <548D9F19.9080405@aklaver.com>
References: <548D54C9.3040005@gmail.com>
	<548D9F19.9080405@aklaver.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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.... 
>
>
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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

view thread (5+ messages)  latest in thread

Message-ID: <548DBFB3.1090304@gmail.com>
Permalink:  ../548DBFB3.1090304@gmail.com/
Also on:    postgresql.org/message-id/548DBFB3.1090304@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: edsrahn@gmail.com
  Subject: Re: foregin table insert error
  In-Reply-To: <548DBFB3.1090304@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox