[SQL] Question on SQL and pg_-tables
Dear all,
after reading about the pg_* system tables, I made up a view to see the all
user columns, their type, default value, indices etc. at once (see example
below). Now my first question is:
- Is it possible to get the same result with a simpler / shorter SQL query
than shown below (I'm not so familiar with SQL yet, hopefully that query is
correct in the first place...)?
I was also trying to get the RI-Constraints out of the pg_* tables. I found
the two tables involved in a RI-Constraint in pg_trigger (tgrelid,
tgconstrrelid), but the affected columns are only(?) in the tgargs. To get
them out of tgargs, I need some (easy) string processing. My second question
is:
- Is it possible to get not only the two tables, but also their corresponding
two columns involved in a RI-Constraint out of the pg_* tables just with a
SQL query?
Thanks for any comments!
Tilo
Example and view definition for question one:
Example:
testobj=> select * from columns;
table| column | type | len | notnull | dims | default_value
|index | primary | unique
-+--+--+-+-+--+-+--+-+
bbox| box | box | 32 | f |0 |
| bbox_area| f | f
bbox| box | box | 32 | f |0 |
| bbox_box | f | f
bbox| box | box | 32 | f |0 |
| bbox_height | f | f
bbox| box | box | 32 | f |0 |
| bbox_width | f | f
bbox| id | int4 | 4 | t |0 |
nextval('"bbox_id_seq"'::text) | bbox_pkey| t | t
bbox| obj_id | int4 | 4 | f |0 |
| bbox_obj_id | f | f
label | descr| text | -1 | f |0 |
| | |
label | id | int4 | 4 | t |0 |
nextval('"label_id_seq"'::text) | label_pkey | t | t
obj | id | int4 | 4 | t |0 |
nextval('"obj_id_seq"'::text) | obj_pkey | t | t
obj_label_r | label_id | int4 | 4 | f |0 |
| obj_label_r_label_id | f | f
obj_label_r | obj_id | int4 | 4 | f |0 |
| obj_label_r_obj_id | f | f
test2 | a| int4 | 4 | f |0 |
| test2id | f | f
test2 | b| int4 | 4 | f |0 |
| | |
test2 | c| int4 | 4 | f |0 |
| test2id | f | f
test2 | d| int4 | 4 | f |0 |
| | |
test2 | e| int4 | 4 | f |0 |
| test2id | f | f
(16 rows)
View definition:
CREATE VIEW columns as
select
defj.relname as table,
defj.attname as column,
defj.typname as type,
defj.attlen as len,
defj.attnotnull as notnull,
defj.attndims as dims,
defj.adsrc as default_value,
indj.relname as index,
indj.indisprimary as primary,
indj.indisunique as unique
from
-- first get all user columns for all user tables
((select * from
pg_class,
pg_attribute,
pg_type
where
pg_class.oid = attrelid
and pg_type.oid = atttypid
and relname !~ 'pg_'
and relname !~ 'pga_'
and pg_class.relkind = 'r'
and pg_attribute.attnum > 0) as colj
-- then get possible default values
left outer join
pg_attrdef on attrelid = adrelid and attnum = adnum) as defj
-- then get possible indices
left outer join
(select * from
pg_class,
pg_index,
pg_attribute
where
pg_class.oid = indexrelid
and pg_class.oid = attrelid) as indj
on (defj.attrelid = indj.indrelid
and defj.attnum = indj.indkey[indj.attnum-1])
order by
1,
2,
index;
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Question on SQL and pg_-tables
Tilo Schwarz <[EMAIL PROTECTED]> writes: > - Is it possible to get not only the two tables, but also their corresponding > two columns involved in a RI-Constraint out of the pg_* tables just with a > SQL query? Not easily --- the column info is buried in the pg_trigger.tgargs entries for the RI triggers, which there is no good way to take apart in plain SQL. You might care to start experimenting with 7.3 instead; the new pg_constraint table makes this much easier. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] changing the size of a column without dump/restore
I've got a huge database table and I need to increase the size of a varchar from like 100 to 200 characters. As I recall the size is just a restriction and doesn't actually affect the format of the table file. Rather than dumping/restoring a 5Gb table with 20,000,000 rows which will take all day and night, is there anything I can twiddle in the system tables to change this size? I'd of course be backing up the data just in case! -Michael _ http://fastmail.ca/ - Fast Secure Web Email for Canadians ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] changing the size of a column without dump/restore
On Mon, Nov 25, 2002 at 01:57:28PM -0500, Michael Richards wrote: > I've got a huge database table and I need to increase the size of a > varchar from like 100 to 200 characters. As I recall the size is just > a restriction and doesn't actually affect the format of the table > file. > > Rather than dumping/restoring a 5Gb table with 20,000,000 rows which > will take all day and night, is there anything I can twiddle in the > system tables to change this size? I'd of course be backing up the > data just in case! PG doesn't have an 'alter table' to increase the column size of a varchar. But you can accomplish it by manipulating the system tables directly. The size of a varchar is stored in pg_attribute as the actual size + 4. For example to change a column "foo" in table "bar" to 200: update pg_attribute set atttypmod = 204 where attrelid = ( select oid from pg_class where relname = 'bar' ) and attname = 'foo'; -Roberto P.S: I don't know if this has any bad side effects. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + * JHM wonders what Joey did to earn "I'd just like to say, for the record, that Joey rules." -- Seen on #Debian ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] celko nested set functions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NotDashEscaped: You need GnuPG to verify this message Robert Treat and I came up with a better way to move nodes from one branch to another inside of a nested tree: CREATE or REPLACE FUNCTION move_tree (integer, integer) RETURNS text AS ' -- Moves part of a nested set tree to another part. -- Pass in the left of the child (from) and the left of the parent (to) DECLARE cleft INTEGER; cright INTEGER; pleft INTEGER; pright INTEGER; leftbound INTEGER; rightbound INTEGER; treeshift INTEGER; cwidth INTEGER; leftrange INTEGER; rightrange INTEGER; BEGIN -- Self-move makes no sense IF $1 = $2 THEN RETURN ''Cannot move: entries are identical''; END IF; SELECT lft, rgt FROM tree WHERE lft = $1 INTO cleft, cright; SELECT lft, rgt FROM tree WHERE lft = $2 INTO pleft, pright; -- Make sure the child exists IF cleft IS NULL THEN RETURN ''No entry found with an left of ''||$1; END IF; -- Make sure the parent exists IF pleft IS NULL THEN RETURN ''No entry found with a left of ''||$2; END IF; -- Parent cannot be underneath the child IF pleft BETWEEN cleft AND cright THEN RETURN ''Cannot move: first entry contains second''; END IF; -- Child may already be in the proper place IF cleft = pleft+1 THEN RETURN ''No changes need to be made''; END IF; IF cleft > pleft THEN treeshift := pleft - cleft + 1; leftbound := pleft+1; rightbound := cleft-1; cwidth := cright-cleft+1; leftrange := cright; rightrange := pleft; ELSE treeshift := pleft - cright; leftbound := cright + 1; rightbound := pleft; cwidth := cleft-cright-1; leftrange := pleft+1; rightrange := cleft; END IF; UPDATE tree SET lft = CASE WHEN lft BETWEEN leftbound AND rightbound THEN lft + cwidth WHEN lft BETWEEN cleft AND cright THEN lft + treeshift ELSE lft END, rgt = CASE WHEN rgt BETWEEN leftbound AND rightbound THEN rgt + cwidth WHEN rgt BETWEEN cleft AND cright THEN rgt + treeshift ELSE rgt END WHERE lft < leftrange OR rgt > rightrange; RETURN ''Tree has been moved''; END; ' LANGUAGE 'plpgsql'; Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200211251526 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE94ojRvJuQZxSWSsgRAkkUAJ0eX9VJtXYajAo60UeKYaXH1xxmkwCeJDtX qrX7tgXmUCJNd/fphjGi7tI= =+ADv -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] subselect instead of a view...
Create view WLE_pathnames as SELECT E.name, EP.pathname FROM element E, element_pathnames EP, watch_list_element WLE WHERE WLE.watch_list_id = 3724 and WLE.element_id= E.id and E.id = EP.id; name | pathname -+- euchre | /ports/games/euchre reallyslick | /ports/graphics/reallyslick The above query is pretty well optimized: Nested Loop (cost=0.00..647.08 rows=62 width=61) (actual time=0.99..1.19 rows=2 loops=1) -> Nested Loop (cost=0.00..437.06 rows=62 width=20) (actual time=0.66..0.78 rows=2 loops=1) -> Index Scan using watch_list_element_pkey on watch_list_element wle (cost=0.00..229.64 rows=62 width=4) (actual time=0.34..0.36 rows=2 loops=1) -> Index Scan using element_pkey on element e (cost=0.00..3.34 rows=1 width=16) (actual time=0.16..0.17 rows=1 loops=2) -> Index Scan using element_pathnames_pkey on element_pathnames ep (cost=0.00..3.38 rows=1 width=41) (actual time=0.16..0.17 rows=1 loops=2) Total runtime: 1.44 msec Now I want to get all the stuff from element_pathnames like pathname || '/%'. Does that make sense? Essentially, I want this using the above view: explain analyze SELECT E.id, CLE.commit_log_id, E.name, EP.pathname FROM element E, element_pathnames EP, commit_log_elements CLE, WLE_pathnames WLEP WHERE E.id = EP.id AND EP.pathname like WLEP.pathname || '/%' AND CLE.element_id= E.id ORDER BY EP.pathname; I know this can be done better, I just can't figure out how. I keep thinking of a subselect but I'm totally blocked. It must be bed time. Sort (cost=285579.85..285579.85 rows=67012 width=114) (actual time=9463.95..9464.01 rows=11 loops=1) -> Hash Join (cost=264060.42..272748.13 rows=67012 width=114) (actual time=9154.69..9463.55 rows=11 loops=1) -> Seq Scan on commit_log_elements cle (cost=0.00..3936.75 rows=216575 width=8) (actual time=0.18..1762.38 rows=216575 loops=1) -> Hash (cost=263370.92..263370.92 rows=36997 width=106) (actual time=5716.62..5716.62 rows=0 loops=1) -> Hash Join (cost=258032.99..263370.92 rows=36997 width=106) (actual time=5524.78..5695.47 rows=10 loops=1) -> Seq Scan on element e (cost=0.00..2286.70 rows=119570 width=16) (actual time=0.15..892.40 rows=119570 loops=1) -> Hash (cost=257416.50..257416.50 rows=36997 width=90) (actual time=3481.05..3481.05 rows=0 loops=1) -> Nested Loop (cost=0.00..257416.50 rows=36997 width=90) (actual time=1847.01..3465.54 rows=10 loops=1) -> Nested Loop (cost=0.00..647.08 rows=62 width=49) (actual time=0.96..1.50 rows=2 loops=1) -> Nested Loop (cost=0.00..437.06 rows=62 width=8) (actual time=0.64..0.94 rows=2 loops=1) -> Index Scan using watch_list_element_pkey on watch_list_element wle (cost=0.00..229.64 rows=62 width=4) (actual time=0.34..0.37 rows=2 loops=1) -> Index Scan using element_pkey on element e (cost=0.00..3.34 rows=1 width=4) (actual time=0.21..0.22 rows=1 loops=2) -> Index Scan using element_pathnames_pkey on element_pathnames ep (cost=0.00..3.38 rows=1 width=41) (actual time=0.21..0.23 rows=1 loops=2) -> Seq Scan on element_pathnames ep (cost=0.00..2355.70 rows=119570 width=41) (actual time=0.08..858.74 rows=119570 loops=2) Total runtime: 9464.51 msec Clues please? -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] subselect instead of a view...
On 25 Nov 2002 at 22:15, Dan Langille wrote: > I know this can be done better, I just can't figure out how. I keep > thinking of a subselect but I'm totally blocked. It must be bed time. It's odd what reading the paper, relaxing with a book, and then trying to sleep can generate. There I was, almost dropping off, when I realised I needed this: SELECT E.name, EP.pathname, E.id, EP2.pathname, CL.id FROM element E, element_pathnames EP, watch_list_element WLE, element_pathnames EP2, element E2, commit_log_elements CLE, commit_log CL WHERE WLE.watch_list_id = 3724 and WLE.element_id= E.id and E.id = EP.id and EP2.pathname like EP.pathname || '/%' AND EP2.id= E2.id AND E2.id = CLE.element_id AND CLE.commit_log_id = CL.id; I am still suspicous of that like. It seems to be the performance killer here. There is an index which can be used: # explain select * from element_pathnames WHERE pathname like 'abc%'; NOTICE: QUERY PLAN: Index Scan using element_pathnames_pathname on element_pathnames (cost=0.00..5.80 rows=1 width=41) But in the main query, it doesn't get picked up. The explain appears below (and at http://www.freshports.org/tmp/explain.txt which will be easier to read than this text-wrapped version). There are quite a few sequential scans there. I'm confused as to why the indexes are not being used. A "vacuum analyze" has been run. Thanks. Hash Join (cost=266574.28..279596.82 rows=67012 width=118) -> Hash Join (cost=263685.03..272372.74 rows=67012 width=114) -> Seq Scan on commit_log_elements cle (cost=0.00..3936.75 rows=216575 width=8) -> Hash (cost=262995.54..262995.54 rows=36997 width=106) -> Hash Join (cost=2994.62..262995.54 rows=36997 width=106) -> Nested Loop (cost=0.00..257416.50 rows=36997 width=102) -> Nested Loop (cost=0.00..647.08 rows=62 width=61) -> Nested Loop (cost=0.00..437.06 rows=62 width=20) -> Index Scan using watch_list_element_pkey on watch_list_element wle (cost=0.00..229.64 rows=62 width=4) -> Index Scan using element_pkey on element e (cost=0.00..3.34 rows=1 width=16) -> Index Scan using element_pathnames_pkey on element_pathnames ep (cost=0.00..3.38 rows=1 width=41) -> Seq Scan on element_pathnames ep2 (cost=0.00..2355.70 rows=119570 width=41) -> Hash (cost=2286.70..2286.70 rows=119570 width=4) -> Seq Scan on element e2 (cost=0.00..2286.70 rows=119570 width=4) -> Hash (cost=2543.20..2543.20 rows=58420 width=4) -> Seq Scan on commit_log cl (cost=0.00..2543.20 rows=58420 width=4) -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] celko nested set functions -- tree move
I'm not sure that keying off lft is safe in a multi-user environment. I opted to create and use an objid on the tree definition table, since its identity is static. I also found that when trees get active, allowing for tree IDs increased operation speed quite a bit (i actually push this to two levels--a 'universe id' and then a 'tree id'). Here's my version. Clearly not as elegantly written, but nothing's gone awry yet. -- --- --Title: trackmyproject_tree_move() -- Function: moves a tree branch in the hierarchy from one parent to -- another. --parms: srcobj the branch/object to be moved -- newparentthe new parent for the object to be moved -- Returns: zero -- --- CREATE FUNCTION trackmyproject_tree_move( INT4, INT4 ) RETURNS INT4 AS ' DECLARE t_srcobj ALIAS FOR $1; t_newparent ALIAS FOR $2; srcspan INT4; srclft INT4; srcrgt INT4; srcuid INT4; srctid INT4; newparentrgt INT4; newparentuid INT4; newparenttid INT4; moveoffset INT4; myrec RECORD; BEGIN -- get src span info (distance between lft and rgt plus one) SELECT ((rgt - lft) + 1) INTO srcspan FROM list_objects WHERE objid_auto=t_srcobj; LOCK TABLE list_objects; -- find out where the new parent currently ends SELECT rgt, universeid, treeid INTO myrec FROM list_objects WHERE objid_auto=t_newparent; newparentrgt := myrec.rgt; newparentuid := myrec.universeid; newparenttid := myrec.treeid; -- create the gap at the bottom of the hierarchy for the -- new parent big enuf for the source object and its tree UPDATE list_objects SET lft = CASE WHEN lft > newparentrgt THEN lft + srcspan ELSE lft END, rgt = CASE WHEN rgt >= newparentrgt THEN rgt + srcspan ELSE rgt END WHERE rgt >= newparentrgt AND universeid=newparentuid AND treeid=newparenttid; -- move the object tree in to the newly created gap -- (may seem like a repetative select, but the above UPDATE -- MAY have moved the source object) SELECT lft, rgt, universeid, treeid INTO myrec FROM list_objects WHERE objid_auto=t_srcobj; srclft := myrec.lft; srcrgt := myrec.rgt; srcuid := myrec.universeid; srctid := myrec.treeid; -- this works even if we are jumping trees or moving up or down within -- the same tree moveoffset := srclft - newparentrgt; UPDATE list_objects SET lft = lft - moveoffset, rgt = rgt - moveoffset, universeid = newparentuid, treeid = newparenttid WHERE lft >= srclft AND rgt <= srcrgt AND universeid=srcuid AND treeid=srctid; -- close the gap where the source object was UPDATE list_objects SET lft = CASE WHEN lft > srclft THEN lft - srcspan ELSE lft END, rgt = CASE WHEN rgt > srclft THEN rgt - srcspan ELSE rgt END WHERE rgt >= srclft AND universeid=srcuid AND treeid=srctid; RETURN 0; END; ' LANGUAGE 'plpgsql'; > Robert Treat and I came up with a better way to move > nodes from one branch to another inside of a nested tree: ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] subselect instead of a view...
"Dan Langille" <[EMAIL PROTECTED]> writes: > SELECT E.name, EP.pathname, E.id, EP2.pathname, CL.id > ... > and EP2.pathname like EP.pathname || '/%' > I am still suspicous of that like. It seems to be the performance > killer here. There is an index which can be used: It won't be, though. The LIKE-to-indexscan transformation happens at plan time, and that means it can only happen if the pattern is a constant. Which it surely will not be in your example. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
