Thread: 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