Обсуждение: where's the reference to a view, here?
because of general weirdness, we translated all "--" in our original data to "==" so postgres wouldn't drop it off as a comment (still haven't figured that one out)... to restore it to its original glory, we tried inv=# update _invitem set descr= inv-# substring(descr from 1 for position('==' in descr)-1)||'--'||substring(descr from position('==' in descr)+2) inv-# where descr like '%==%'; ERROR: system column oid not available - inv is a view inv=# \d _invitem Table "_invitem" Attribute | Type | Modifier -----------+--------------+---------- id | integer | item | smallint | hrs | numeric(4,1) | rate | numeric(6,2) | other | numeric(7,2) | descr | varchar(80) | inv=# select count(*) from _invitem where descr like '%==%'; count ------- 45 there is a view named "inv" -- but how's that related to this straght table update? inv=# \d List of relations Name | Type | Owner ----------+-------+------- _charge | table | will _client | table | will _inv | table | will _invitem | table | will _job | table | will _work | table | will inv | view | will (7 rows) inv=# \d inv View "inv" Attribute | Type | Modifier -----------+----------------------+---------- client | varchar(8) | id | integer | code | varchar(20) | rundate | date | job | varchar(6) | invdate | date | costs | boolean | total | numeric(7,2) | item | smallint | hrs | numeric(4,1) | rate | numeric(6,2) | other | numeric(7,2) | descr | varchar(80) | amt | numeric(65535,65531) | View definition: SELECT b.client, b.id, b.code, b.rundate, b.job, b.invdate, b.costs, b.total, l.item, l.hrs, l.rate, l.other,l.descr, CASE WHEN (l.rate ISNULL) THEN l.other ELSE (l.rate * l.hrs) END AS amt FROM "_inv" b, "_invitem" l WHERE(b.id = l.id); -- I figure: if a man's gonna gamble, may as well do it without plowing. -- Bama Dillert, "Some Came Running" will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
From: "will trillich" <will@serensoft.com> > inv=# update _invitem set descr= > inv-# substring(descr from 1 for position('==' in descr)-1)||'--'||substring(descr from position('==' in descr)+2) > inv-# where descr like '%==%'; > ERROR: system column oid not available - inv is a view This query isn't in a function is it? I've seen all sorts of weirdness if I drop & recreate tables/views that compiled functions depend on. [snip] > inv=# \d inv > View "inv" > Attribute | Type | Modifier > -----------+----------------------+---------- [snip] > amt | numeric(65535,65531) | Is this large a numeric deliberate, or has something got mangled here? - Richard Huxton
will trillich <will@serensoft.com> writes: > there is a view named "inv" -- but how's that related to this > straght table update? You wouldn't have any ON UPDATE rules or triggers attached to this table, would you? regards, tom lane
On Fri, Jun 22, 2001 at 09:36:19AM -0400, Tom Lane wrote: > will trillich <will@serensoft.com> writes: > > there is a view named "inv" -- but how's that related to this > > straght table update? > > You wouldn't have any ON UPDATE rules or triggers attached to this > table, would you? > > regards, tom lane did, in fact, even before i'd "alter table inv rename to _inv"... wherein lies the culprit. (maybe 'alter table...' needs to root around some more on those renames to make sure all references are properly updated... as if i have a clue how hard that would be...) -- I figure: if a man's gonna gamble, may as well do it without plowing. -- Bama Dillert, "Some Came Running" will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
On Fri, Jun 22, 2001 at 09:30:05AM +0100, Richard Huxton wrote: > From: "will trillich" <will@serensoft.com> > > inv=# \d inv > > View "inv" > > Attribute | Type | Modifier > > -----------+----------------------+---------- > [snip] > > amt | numeric(65535,65531) | > > Is this large a numeric deliberate, or has something got mangled here? i noticed that, too. (wasn't me, wasn't me!) simple view created thus: CREATE VIEW inv AS SELECT b.client, b.id, b.code, b.rundate, b.job, b.invdate, b.costs, b.total, l.item, l.hrs, l.rate, l.other, l.descr, CASE WHEN (l.rate ISNULL) THEN l.other ELSE (l.rate * l.hrs) END AS amt FROM "_inv" b, "_invitem" l WHERE (b.id = l.id); notice how 'pg_dump' shows the phantom table behind the view: CREATE TABLE "inv" ( "client" character varying(8), "id" int4, "code" character varying(20), "rundate" date, "job" character varying(6), "invdate" date, "costs" bool, "total" numeric(7,2), "item" int2, "hrs" numeric(4,1), "rate" numeric(6,2), "other" numeric(7,2), "descr" character varying(80), "amt" numeric -- <<=== no big whoop, there ); but "\d inv" shows the oddness on the calculated field: View "inv" Attribute | Type | Modifier -----------+----------------------+---------- client | varchar(8) | id | integer | code | varchar(20) | rundate | date | job | varchar(6) | invdate | date | costs | boolean | total | numeric(7,2) | item | smallint | hrs | numeric(4,1) | rate | numeric(6,2) | other | numeric(7,2) | descr | varchar(80) | amt | numeric(65535,65531) | <<=== say what? <<=== View definition: SELECT b.client, b.id, b.code, b.rundate, b.job, b.invdate, b.costs, b.total, l.item, l.hrs, l.rate,l.other, l.descr, CASE WHEN (l.rate ISNULL) THEN l.other ELSE (l.rate * l.hrs) END AS amt FROM "_inv" b, "_invitem"l WHERE (b.id = l.id); curiouser and curiouser. (this probably reflects an internal-type flag situation, i'd bet. but it's still spooky to look at. just think, 65500 digits of precision eating away at my hard disk... not :) the rest is working much better, now that i've done a dump/reload of the schema and data. -- I figure: if a man's gonna gamble, may as well do it without plowing. -- Bama Dillert, "Some Came Running" will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
will trillich <will@serensoft.com> writes: > amt | numeric(65535,65531) | >> >> Is this large a numeric deliberate, or has something got mangled here? > i noticed that, too. (wasn't me, wasn't me!) This bit looks like a known-and-fixed-in-7.1 problem with pg_dump's handling of calculated NUMERIC columns in views. regards, tom lane