[GENERAL] Error 42704 - does mean what?
[Cross post from -SQL]
Hi,
I am trying to model a macro system where I have simple things, and more complex thing consisting of simple things. To do that I have
invented this table definition:
CREATE TABLE params
(
param_id serial NOT NULL,
name text NOT NULL,
unit text,
real_param_id integer[],
CONSTRAINT params_pkey PRIMARY KEY (param_id),
CONSTRAINT params_name_key UNIQUE (name)
);
with a complex and 2 simple things:
INSERT INTO params VALUES (1, 'a', NULL, '{1,2}');
INSERT INTO params VALUES (2, 'a1', '1', NULL);
INSERT INTO params VALUES (3, 'a2', '2', NULL);
So I want to get a listing of things, both simple and complex
col1 col2
-+
a1{{a1, 1}}
a2{{a2, 2}}
a {{a1, 1},{a2,2}}
with this SQL:
select name, array[array[name::text,unit::text]]::text[][]
from params
where real_param_id is null
union
select name, array(select cast('{'||a.name||','||a.unit||'}' as text[])
from params a,
(select c.param_id, unnest(real_param_id)
from params c
where c.param_id=b.param_id) as j
where a.param_id = j.unnest)
from params b
where b.real_param_id is not null
order by name
But I am getting this error which I do not find very informative, as I know i
can have arrays of text and arrays of those, so what is up?
ERROR: could not find array type for data type text[]
SQL state: 42704
Suggestions as to what to do to circumvent this error, and also to maybe more elegant ways to solve the fundamental problem will be received
with pleasure.
This is tested on both PostgreSQL 9.2.1 and a 9.1.*
Thanks in advance!
Regards,
Kim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory tuning for linux (Suffering CRS...)
Try this: http://www.powerpostgresql.com/Downloads/annotated_conf_80.html Peter L. Berghold wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Folks, I remember seeing somewhere a document that outlined how to tune memory for optimal operation of a postgres server on Linux. I can't seem to find that document again. I did fine one for the 7.x family but not 8.x and I'm currently running 8.x and noticing some heavy system load doing so. Any hints/tips appreciated on either where to look or how to tune this thing. Thanks, - -- Peter L. Berghold [EMAIL PROTECTED] Those who fail to learn from history are condemned to repeat it. AIM: redcowdawgYahoo IM: blue_cowdawg ICQ: 11455958 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCwrFqUM9/01RIhaARAowwAJ0WGG3iRjFn0AmaviMRgvjJpGMc5wCfUXlk 22RUTPMGOS8VLUl4nRZz7z0= =FKH1 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Propogating conditions into a query
Tom Lane wrote: Kim Bisgaard [EMAIL PROTECTED] writes: The reason the first query is not performing is because the query optimizer does not push the conditions down into the sub-queries - right?? Well, it's not the same condition: the WHERE clause is constraining the output variable of the FULL JOIN, which is logically and implementationally distinct from either input table's variable. I suppose it is arguable that we could transform the WHERE clause into constraints on the input variables --- but we'd have to think carefully about the conditions under which such a transformation is valid. Offhand it seems like it might work for strict non-volatile constraint expressions that refer only to output variables of the JOIN; if they refer to other tables too then I'm not sure. I am shure you know what you are talking about - I am just showing another case where I think the above transformations are worthwhile (pushing my problem :-) ) This is by no means a bug fix, but if I have time over the next week I'll see whether it is feasible to write for 8.1. Hey thanks a lot - tell me if there is anything I can do (beer next time in Copenhagen, work, ..) Best regards, Kim. -- Computer Department Phone: +45 3915 7562 (direct) Danish Meteorological Institute Fax: +45 3915 7460 (division) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Propogating conditions into a query
Hi Tom, I have now completed the move to PG8.0.3, and feel that I have confirmed that this problem is related to the problem I'm having: Formulated like this, it is not performing: SELECT station_id, timeobs,temp_grass, temp_dry_at_2m FROM temp_dry_at_2m a FULL OUTER JOIN temp_grass b USING (station_id, timeobs) WHERE station_id = 52981 AND timeobs = '2004-1-1 0:0:0'; Merge Full Join (cost=1598312.83..11032924.48 rows=6956994 width=32) (actual time=119061.098..133314.306 rows=1 loops=1) Merge Cond: ((outer.timeobs = inner.timeobs) AND (outer.station_id = inner.station_id)) Filter: ((COALESCE(inner.station_id, outer.station_id) = 52981) AND (COALESCE(inner.timeobs, outer.timeobs) = '2004-01-01 00:00:00'::timestamp without time zone)) - Sort (cost=346429.38..352445.11 rows=2406292 width=16) (actual time=20315.241..23850.529 rows=2406292 loops=1) Sort Key: b.timeobs, b.station_id - Seq Scan on temp_grass b (cost=0.00..41756.92 rows=2406292 width=16) (actual time=10.517..7003.468 rows=2406292 loops=1) - Sort (cost=1251883.44..1269275.93 rows=6956994 width=16) (actual time=82122.354..92027.850 rows=6956994 loops=1) Sort Key: a.timeobs, a.station_id - Seq Scan on temp_dry_at_2m a (cost=0.00..117549.94 rows=6956994 width=16) (actual time=23.759..39930.741 rows=6956994 loops=1) Total runtime: 133623.422 ms But Postgresql can do the work, if it is reformulated into: SELECT station_id, timeobs, temp_grass, temp_dry_at_2m FROM (SELECT station_id, timeobs, temp_dry_at_2m FROM temp_dry_at_2m WHERE station_id = 52981 AND timeobs = '2004-1-1 0:0:0') a FULL OUTER JOIN (SELECT station_id, timeobs, temp_grass FROM temp_grass WHERE station_id = 52981 AND timeobs = '2004-1-1 0:0:0') b USING (station_id, timeobs) Merge Full Join (cost=0.00..43023.64 rows=10614 width=32) (actual time=0.056..0.064 rows=1 loops=1) - Index Scan using temp_grass_idx on temp_grass (cost=0.00..246.55 rows=61 width=16) (actual time=0.029..0.031 rows=1 loops=1) Index Cond: ((timeobs = '2004-01-01 00:00:00'::timestamp without time zone) AND (station_id = 52981)) - Index Scan using temp_dry_at_2m_idx on temp_dry_at_2m (cost=0.00..699.52 rows=174 width=16) (actual time=0.017..0.020 rows=1 loops=1) Index Cond: ((timeobs = '2004-01-01 00:00:00'::timestamp without time zone) AND (station_id = 52981)) Total runtime: 0.163 ms The reason the first query is not performing is because the query optimizer does not push the conditions down into the sub-queries - right?? The reason that I do not just use the reformulated query, is that e.g. the station_id comes from another table (and there can be more of them), so it is bloody inconvenient to first select them, and then repeat them a number of time in the above transformation (I need to outer join more than two tables) Best regards, Kim Bisgaard wrote: Hi Tom, This sounds like the same problem which prevented PG from using the indices, and thus giving abyssmal performance in this other thread: I have two BIG tables (virtually identical) with 3 NOT NULL columns Station_id, TimeObs, Temp_, with unique indexes on (Station_id, TimeObs) and valid ANALYSE (set statistics=100). I want to join the two tables with a FULL OUTER JOIN. When I specify the query as: SELECT station_id, timeobs,temp_grass, temp_dry_at_2m FROM temp_dry_at_2m a FULL OUTER JOIN temp_grass bUSING (station_id, timeobs) WHERE station_id = 52981 AND timeobs = '2004-1-1 0:0:0' Then I would also vote for improving the inteligence of the optimizer! :-) Regards, Kim. Tom Lane wrote: Phil Endecott [EMAIL PROTECTED] writes: I don't see anything in there about LEFT OUTER JOIN though. Any ideas? Oh, I missed that part of your message. Hmm, I think the issue is that in D join (M join G on (M.g=G.id)) on (D.id=M.b) where D.id=nnn the planner deduces M.b=nnn by transitivity, but when the join is an outer join it can't make the same deduction. [ thinks some more... ] If we distinguished conditions that hold below the join from those that hold above it, we could deduce that M.b=nnn can be enforced below the join even though it might not be true above it. There's no such mechanism in existence now, though. A possible workaround is to generate your query like D left join (M join G on (M.g=G.id)) on (D.id=M.b AND M.b=nnn) where D.id=nnn but I don't know how practical that is for you. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast
Re: [GENERAL] Propogating conditions into a query
Hi Tom, This sounds like the same problem which prevented PG from using the indices, and thus giving abyssmal performance in this other thread: I have two BIG tables (virtually identical) with 3 NOT NULL columns Station_id, TimeObs, Temp_, with unique indexes on (Station_id, TimeObs) and valid ANALYSE (set statistics=100). I want to join the two tables with a FULL OUTER JOIN. When I specify the query as: SELECT station_id, timeobs,temp_grass, temp_dry_at_2m FROM temp_dry_at_2m a FULL OUTER JOIN temp_grass bUSING (station_id, timeobs) WHERE station_id = 52981 AND timeobs = '2004-1-1 0:0:0' Then I would also vote for improving the inteligence of the optimizer! :-) Regards, Kim. Tom Lane wrote: Phil Endecott [EMAIL PROTECTED] writes: I don't see anything in there about LEFT OUTER JOIN though. Any ideas? Oh, I missed that part of your message. Hmm, I think the issue is that in D join (M join G on (M.g=G.id)) on (D.id=M.b) where D.id=nnn the planner deduces M.b=nnn by transitivity, but when the join is an outer join it can't make the same deduction. [ thinks some more... ] If we distinguished conditions that hold below the join from those that hold above it, we could deduce that M.b=nnn can be enforced below the join even though it might not be true above it. There's no such mechanism in existence now, though. A possible workaround is to generate your query like D left join (M join G on (M.g=G.id)) on (D.id=M.b AND M.b=nnn) where D.id=nnn but I don't know how practical that is for you. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
