where's the reference to a view, here?

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