Обсуждение: foregin table insert error
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 nulldefault 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? Thanks Ed
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 > > Thanks > Ed > > -- Adrian Klaver adrian.klaver@aklaver.com
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
On 12/14/2014 08:49 AM, Ed Rahn wrote: > 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) Hmm, the NOT NULL constraint is being checked before the trigger is run. Maybe change the function to look for a dummy value, say -1 and then use that in your INSERT: insert into remote_cache (entry_id, name_id) values(-1, 2,1); > > thanks > Ed > > -- Adrian Klaver adrian.klaver@aklaver.com
On 12/14/2014 08:49 AM, Ed Rahn wrote: > 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) Just realized that deals with remote side, but not with getting the value back to the local side. That stirred a memory which led me to this blog post: http://michael.otacoo.com/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/ FYI if I follow correctly this: =# CREATE FOREIGN TABLE foreign_seq_table (a bigint) -# SERVER postgres_server OPTIONS (table_name 'seq_table') should be: =# CREATE FOREIGN TABLE foreign_seq_table (a bigint) -# SERVER postgres_server OPTIONS (table_name 'seq_view') > > thanks > Ed > > -- Adrian Klaver adrian.klaver@aklaver.com