Re: [SQL] Issue with UPDATE statement on v8
On Thu, Sep 22, 2005 at 11:22:22AM -0500, Kenneth Hutchinson wrote: > UPDATE t_summary > SETavailability = 7 > WHERE oid = 28245084 > > When this query is executed (within a function or without) the database > will simply hang. If the UPDATE is turned into a SELECT, the query > works just fine. One possibility is that another transaction has updated this row or done a SELECT FOR UPDATE and hasn't committed yet. Does just this one record cause the update to hang or do all updates to the table hang? Have you queried pg_locks in another session while the update is hung? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Where are user defined functions stored?
If I write a rule, I can easily see it by doing select * from pg_rules; and I also get to see all the rules I have created for my database. How can I easily get to see the definition of a user defined function please? I've searched through the online manual but failed to find the answer. TAI Hilary Hilary Forbes The DMR Information and Technology Group (www.dmr.co.uk) Direct tel 01689 889950 Fax 01689 860330 DMR is a UK registered trade mark of DMR Limited ** ---(end of broadcast)--- TIP 1: 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] Difficulties with a master-detail query
On Mon, Sep 19, 2005 at 20:29:40 +0300, "Milen A. Radev" <[EMAIL PROTECTED]> wrote: > > I would like to get all employees, who speak two specified languages > (say german and french). The following query gives me that, bu I don't > like it (see for yourself): > > > SELECT > ?.employee_id, > ?.name, > COUNT(s.skill_id) > FROM > employee AS e > INNER JOIN > employee_skill AS es > ON > e.employee_id=es.employee_id > INNER JOIN > skill AS s > ON > s.skill_id=es.skill_id AND s.skill_id IN (1, 2) > GROUP BY > e.employee_id, > e.name > HAVING > COUNT(s.skill_id)>=2; > > > Here "(1, 2)" are the IDs for those predefined two languages, got from > the "skill" table. ?nd that two in "COUNT(s.skill_id)>=2" is there > because the count of the languages. > > > Any ideas for simpler and more universal query? That seems like a reasonable way to do things. The other way to do this is to join the employee_skills table once for each skill. However, I expect the above approach to be faster. If you want to use skill names you can join against the skill table. I don't think is an automatic way to tie the count() comparison to the number of different skills being checked. But if you are doing this through an app it already has to generate the IN list, so it shouldn't be hard to make it figure out what to use to campare the count to. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Where are user defined functions stored?
Hilary Forbes <[EMAIL PROTECTED]> writes: > How can I easily get to see the definition of a user defined function > please? Look in pg_proc. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [ADMIN] COPY TO / COPY FROM
> I kown I can use pg_dump to perform that "back up" but I wanted to > test performences. pg_dump does the same.. if you same custom it makes binary (WITH BINARY) but with compression. Performance? 1. TOP: inserte into select 2. TOP: copy / pg_restore with uncompressed binary 3. TOP: copy / pg_restore with uncompressed text 4. TOP: pg_restore with compressed binary (custom format in pg_dump) 5. TOP: normal inserts... This is my experience. > PATH = /home/postgres//carax_exec.out Be aware to give postgres write/read access to this file/dir - i think the double "//" is typee Cédric Buschini wrote: > Aldor wrote: > >> What is the name of the table you want to dump? >> >> > I kown I can use pg_dump to perform that "back up" but I wanted to test > performences. > > The table is carax_exec > >> What is the exact path you try to dump it to? >> >> > PATH = /home/postgres//carax_exec.out > >> What version of PostgreSQL do you have? >> >> > uhmm 8.0 > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Where are user defined functions stored?
Tom Many thanks. Suppose I now want to know if there are any user defined functions set up in my database? I was rather hoping I could do something simple like \df to get a list of **user** defined functions rather as \dt gives me a list of my tables and not the complete list of all the system tables as well. Is there a way of achieving this? Hilary At 10:40 23/09/2005 -0400, you wrote: >Hilary Forbes <[EMAIL PROTECTED]> writes: >> How can I easily get to see the definition of a user defined function >> please? > >Look in pg_proc. > >regards, tom lane > >---(end of broadcast)--- >TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq Hilary Forbes The DMR Information and Technology Group (www.dmr.co.uk) Direct tel 01689 889950 Fax 01689 860330 DMR is a UK registered trade mark of DMR Limited ** ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Where are user defined functions stored?
Hilary Forbes <[EMAIL PROTECTED]> writes: > Many thanks. Suppose I now want to know if there are any user defined > functions set up in my database? I was rather hoping I could do something > simple like > \df > to get a list of **user** defined functions rather as \dt gives me a list of > my tables and not the complete list of all the system tables as well. Is > there a way of achieving this? You could do something like \df public.* although if your functions are scattered through a bunch of different schemas it'd get a bit tedious. Or try looking at the catalog for yourself, eg select oid::regprocedure, prosrc from pg_proc where pronamespace != (select oid from pg_namespace where nspname = 'pg_catalog'); This essentially implements the rule "system functions are those in pg_catalog". You might want to exclude stuff in information_schema as well. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Updating cidr column with network operator
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 22.09.2005 um 22:26 schrieb Daryl Richter: Axel Rau wrote: Thank you for responding, Daryl, Am 22.09.2005 um 16:45 schrieb Daryl Richter: Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, "it belongs to": CREATE TABLE network ( id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address' ) CREATE TABLE address ( id inet PRIMARY KEY , -- 'PK of IPv4/6 host address' network cidr NOT NULL-- 'FK to Network table' REFERENCES network ON DELETE CASCADE ON UPDATE CASCADE ) I tried (using the WHERE clause to eliminate the addresses were no corresponding net exists): But you can't insert a row in address w/o a valid network.id? That's what the fk ensures. Perhaps you could elaborate more? Are you trying to *put* on the fk and you currently have bad data? The fk requires a corresponding row in network. But my update tries to reference the right network, that one where the ip address belongs to. I'm still not understanding what you're trying to do, perhaps its a language issue. :) Let me try again. I built your schema and inserted some rows: insert into network( id ) values( '10.1' ); insert into address( id, network ) values( '10.1.0.1', '10.1' ); insert into address( id, network ) values( '10.1.0.2', '10.1' ); insert into address( id, network ) values( '10.1.0.3', '10.1' ); I then select from network: id --- 10.1.0.0/16 and from address: idnetwork --- 10.1.0.1 10.1.0.0/16 10.1.0.2 10.1.0.0/16 10.1.0.3 10.1.0.0/16 Why do you now want to update address.network? They are already pointing to the right network, aren't they? Networks change during time, being diveded or aggregated or you just enter wrong data during insert. With the UPDATE below, I want to correct the addresses to again point at the right net. While writing this, I learn that because of the pk in network, UPDATEs will be difficult to accomplish (you may need a temporary net to park all addresses of a network to be divided, make the change in network and use the UPDATE below to adjust A.network. I use net '0.0.0.0/32' as 'UNKNOWN' net or for parking. I think if you provide some sample data we can figure this out. Yes, this a goof idea. Playing with small tables let you find quickly the right query. Lets start over with a slightly bigger collection of data: insert into network( id ) values( '10.1/16' ); insert into network( id ) values( '10.2/16' ); insert into network( id ) values( '10.3/16' ); insert into address( id, network ) values( '10.1.0.1', '10.1/16' ); insert into address( id, network ) values( '10.1.0.2', '10.1/16' ); insert into address( id, network ) values( '10.1.0.3', '10.1/16' ); insert into address( id, network ) values( '10.1.0.4', '10.2/16' ); insert into address( id, network ) values( '10.1.0.5', '10.2/16' ); insert into address( id, network ) values( '10.1.0.6', '10.3/16' ); insert into address( id, network ) values( '10.200.0.6', '10.3/16' ); -- address not in network insert into address( id, network ) values( '10.200.0.7', '10.3/16' ); -- address not in network During the UPDATE I must avoid NULLs in A.network. This SELECT SELECT A.id, A.network, N.id FROM address A, network N WHERE EXISTS (SELECT A.id WHERE A.id << N.id); does it right: id| network | id - --+-+- 10.1.0.1 | 10.1.0.0/16 | 10.1.0.0/16 10.1.0.2 | 10.1.0.0/16 | 10.1.0.0/16 10.1.0.3 | 10.1.0.0/16 | 10.1.0.0/16 10.1.0.4 | 10.2.0.0/16 | 10.1.0.0/16 10.1.0.5 | 10.2.0.0/16 | 10.1.0.0/16 10.1.0.6 | 10.3.0.0/16 | 10.1.0.0/16 NOT the UPDATE (meanwhile improved from below): UPDATE address SET network = (SELECT N.id WHERE A.id << N.id) FROM address A, network N, network N1 WHERE EXISTS (SELECT A.id WHERE A.id << N1.id); I get: 'ERROR: null value in column "network" violates not-null constraint'. So my problem reduces to some (basic) SQL question: "How do I avoid the NULLs with EXISTS () or IN () in the WHERE clause of an UPDATE.?" Apparently in this situation the WHERE acts not as an inner join. The problem seems to be the N.id in the SET expression, as shows by this SELECT: SELECT A.id, A.network, (SELECT N.id WHERE A.id << N.id) FROM address A, network N, network N1 WHERE EXISTS (SELECT A.id WHERE A.id << N1.id); which gives: id| network | ?column? - --+-+- 10.1.0.1 | 10.1.0.0/16 | 10.1.0.0/16 10.1.0.1 | 10.1.0.0/16 | 10.1.0.1 | 10.1.0.0/16 | 10.1.0.2 | 10.1.0.0/16 | 10.1.0.0/16 10.1.0.2 | 10.1.0.0/16 | 10.1.0.2 | 10.1.0.0/16 | 10.1.0.3 | 10.1.0.0/16 | 10.1.0.0/16 1
Re: [SQL] delete item[5] from varchar[] array???
--- Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Wed, Sep 21, 2005 at 06:56:36PM -0700, Matthew
> Peter wrote:
> > How is it possible to delete an item from a single
> > dimension varchar[] array? Lets say it has the
> values
> > {1,2,3,4,5,6}... how do i delete at position [4]?
>
> I'm not sure if there's a better way, but you could
> concatenate the
> slices adjacent to the position you want to delete:
>
> test=> SELECT a FROM foo;
>a
> ---
> {1,2,3,4,5,6}
> (1 row)
>
> test=> UPDATE foo SET a = a[1:3] || a[5:6];
> UPDATE 1
> test=> SELECT a FROM foo;
> a
> -
> {1,2,3,5,6}
> (1 row)
>
> --
> Michael Fuhr
>
I sure hope there is a better way :) There must be an
easy, native way to interface with arrays. We all know
programming languages have ways to delete items in an
array natively, it just needs to be exposed.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 1: 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] Updating cidr column with network operator
On Fri, Sep 23, 2005 at 06:31:17PM +0200, Axel Rau wrote: > Networks change during time, being diveded or aggregated or you just > enter wrong data during insert. Have you considered using a CHECK constraint and/or a trigger to ensure that the network in the network column contains the address in the id column? If you have and rejected the idea, what were the reasons? > With the UPDATE below, I want to correct the addresses to again point > at the right net. Does the following statement do what you want? It shouldn't touch the records with no matching network -- what do you want to happen in those cases? This update also might not give the results you want if more than one network matches. UPDATE address SET network = n.id FROM network n WHERE address.id << n.id; > While writing this, I learn that because of the pk in network, > UPDATEs will be difficult to accomplish (you may need a temporary > net to park all addresses of a network to be divided, make the > change in network and use the UPDATE below to adjust A.network. I > use net '0.0.0.0/32' as 'UNKNOWN' net or for parking. I'm not sure I understand what you're saying, but if you're concerned about foreign key violations then making the foreign key constraints deferrable and deferring them during certain updates might remove the need for a temporary "parking" network. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Updating cidr column with network operator
Axel Rau wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 22.09.2005 um 22:26 schrieb Daryl Richter: Axel Rau wrote: Thank you for responding, Daryl, Am 22.09.2005 um 16:45 schrieb Daryl Richter: Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, "it belongs to": CREATE TABLE network ( id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address' ) CREATE TABLE address ( id inet PRIMARY KEY , -- 'PK of IPv4/6 host address' network cidr NOT NULL-- 'FK to Network table' REFERENCES network ON DELETE CASCADE ON UPDATE CASCADE ) I tried (using the WHERE clause to eliminate the addresses were no corresponding net exists): [snip] Networks change during time, being diveded or aggregated or you just enter wrong data during insert. With the UPDATE below, I want to correct the addresses to again point at the right net. While writing this, I learn that because of the pk in network, UPDATEs will be difficult to accomplish (you may need a temporary net to park all addresses of a network to be divided, make the change in network and use the UPDATE below to adjust A.network. I use net '0.0.0.0/32' as 'UNKNOWN' net or for parking. I think if you provide some sample data we can figure this out. Yes, this a goof idea. Playing with small tables let you find quickly the right query. Lets start over with a slightly bigger collection of data: insert into network( id ) values( '10.1/16' ); insert into network( id ) values( '10.2/16' ); insert into network( id ) values( '10.3/16' ); insert into address( id, network ) values( '10.1.0.1', '10.1/16' ); insert into address( id, network ) values( '10.1.0.2', '10.1/16' ); insert into address( id, network ) values( '10.1.0.3', '10.1/16' ); insert into address( id, network ) values( '10.1.0.4', '10.2/16' ); insert into address( id, network ) values( '10.1.0.5', '10.2/16' ); insert into address( id, network ) values( '10.1.0.6', '10.3/16' ); insert into address( id, network ) values( '10.200.0.6', '10.3/16' ); -- address not in network insert into address( id, network ) values( '10.200.0.7', '10.3/16' ); -- address not in network But those are bad inserts, right? I think that I now see what the problem is --> Why do you have a network table at all? It's redundant. If you just insert the ids into your address table, don't the new PostgreSQL operators give you all the information you need? So, for example, if I inserted the data above and then want to answer the question, "What are all my 16-bit subnets?" select distinct network( set_masklen( id, 16 ) ) from address; network - 10.1.0.0/16 10.2.0.0/16 10.200.0.0/16 [rest snipped] -- Daryl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] delete item[5] from varchar[] array???
On Fri, Sep 23, 2005 at 10:02:44AM -0700, Matthew Peter wrote: > --- Michael Fuhr <[EMAIL PROTECTED]> wrote: > > test=> UPDATE foo SET a = a[1:3] || a[5:6]; > > I sure hope there is a better way :) There must be an > easy, native way to interface with arrays. Hoping a thing is true doesn't mean it must be true :-) > We all know programming languages have ways to delete items in an > array natively, it just needs to be exposed. No, we don't all know that because it isn't generally true, or else you're using a loose definition of "natively." Languages differ in what capabilities they offer "natively." contrib/intarray implements an intarray_del_elem() function, presumably because the core product doesn't offer that capability, or at least didn't when the module was written. If you think it's there just waiting to be exposed, then dig into the source code and expose it :-) -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Updating cidr column with network operator
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 23.09.2005 um 19:32 schrieb Michael Fuhr: On Fri, Sep 23, 2005 at 06:31:17PM +0200, Axel Rau wrote: Networks change during time, being diveded or aggregated or you just enter wrong data during insert. Have you considered using a CHECK constraint and/or a trigger to ensure that the network in the network column contains the address in the id column? If you have and rejected the idea, what were the reasons? I'm sure this would be the cleanest solution but remember networks change. This constraind would have to update all details (addresses) of a 10/8 being splitted in a 10/9 and a 10.128/9. If this can be done with pg, it is above my current knowledge level. (But feel free to send a suggestion). The other point is performance. Inserting new addresses is a realtime job while correcting network changes is a daily maintenance job. With the UPDATE below, I want to correct the addresses to again point at the right net. Does the following statement do what you want? Yes. Thank you. This was the 1st answer I'm looking for. Just too simple. It shouldn't touch the records with no matching network -- what do you want to happen in those cases? They will be updated to reference '0.0.0.0/32' (the UNKNOWN net). Will this work: UPDATE address SET network = '0.0.0.0/32' WHERE NOT EXISTS (SELECT address.id << network.id); ? This update also might not give the results you want if more than one network matches. This is not possible, because the pk of network is the net cidr. UPDATE address SET network = n.id FROM network n WHERE address.id << n.id; While writing this, I learn that because of the pk in network, UPDATEs will be difficult to accomplish (you may need a temporary net to park all addresses of a network to be divided, make the change in network and use the UPDATE below to adjust A.network. I use net '0.0.0.0/32' as 'UNKNOWN' net or for parking. I'm not sure I understand what you're saying, but if you're concerned about foreign key violations then making the foreign key constraints deferrable and deferring them during certain updates might remove the need for a temporary "parking" network. I see. But I have to sort in the orphaned addresses anyway if I delete a net for splitting or aggregating or even the net may be abandoned so its children will have to be moved to the UNKNOWN net. Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (Darwin) iQEVAwUBQzRVS8Fz9+6bacTRAQKIlwgAn6G8mXkT+vODCW+n9/dUmOB/NYOJVfZL T7/oiYpSVWz1ApcIbcQii+RvhpEZXvgpHif8i5Nd0yeV2347PKwflttGSiWJxVPt mVUrYxjfIjAKmYhbOP25aHK/AGqgjgQRrCOosz3Kbzr5OY4kpNhF67oosGDpIVq+ DcC7nx6+QoHkFByBqL7xTlHDNBS98baVCeGDTIeaJOFEsU1u6t+29ORHloicBo6n 3QZz2qLTMVNzcX/mfS6BqV4POOMSza9zMyRApTwM5lwM+HBAOXvMJ0INiGA0hLE0 o+kVa0I0JTBD4RByxt9c66qFtFN5Y6oZFonm+pBA6nRliBIpt2/8ZA== =fhnD -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Updating cidr column with network operator
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 23.09.2005 um 20:43 schrieb Daryl Richter: [snip] I think that I now see what the problem is --> Why do you have a network table at all? It's redundant. There are more attributes and fks, I didn't mention to simplify. The schema is events -> addresses -> autnums -> organisations. Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (Darwin) iQEVAwUBQzRWjsFz9+6bacTRAQIT4Af/fYbPJtgi9sDcWTm84hbtPl9BUS6Rt0Wo 728oZO7/iEXNSCoaZCqUymK2sKzsE5XV1EZL7xeBCoDZmj+i/3LS0cEAq3Q4corU HEXBvRQ1HcEbvaRioefo5pk6iKvgBxbz9guKawcPwgHJMdvAFSlJpDB1lXOkZgTJ P/8goloEygGFFTEEsquP+4Us0sdBDNe9g0TLapNwI8A9VQ/10tqKF29hEa+tUUun DjkdWAWndAiZvz5zoTfyBTFFKNu9e4dHYNSRMtmz16d5oMYwhIePuHT4lPw03hBW 0FOq4CnkF5YUIrC5XoEta6J1Zxf7FGY1ba+xHBUZjviKCtjtmPS34A== =XBqf -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] delete item[5] from varchar[] array???
--- Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Fri, Sep 23, 2005 at 10:02:44AM -0700, Matthew > Peter wrote: > > --- Michael Fuhr <[EMAIL PROTECTED]> wrote: > > > test=> UPDATE foo SET a = a[1:3] || a[5:6]; > > > > I sure hope there is a better way :) There must be > an > > easy, native way to interface with arrays. > > Hoping a thing is true doesn't mean it must be true > :-) > > > We all know programming languages have ways to > delete items in an > > array natively, it just needs to be exposed. > > No, we don't all know that because it isn't > generally true, or else > you're using a loose definition of "natively." > Languages differ > in what capabilities they offer "natively." > > contrib/intarray implements an intarray_del_elem() > function, > presumably because the core product doesn't offer > that capability, > or at least didn't when the module was written. If > you think it's > there just waiting to be exposed, then dig into the > source code and > expose it :-) > > -- > Michael Fuhr > Yes. I should've said "most" and some thanks to libs. I'm busy in other source which is why I hoped those who were already engulfed in pg's source would have a solution... Thought this functionality may be around... somewhere which is why I asked. Took a brief look at intarray_del_elem() function you mentioned. This may be perfect, and then some. I'll play around with it. Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] VACUUM FULL vs dump & restore
Greetings, What advantages I lose, when using dump-truncate-restore (table or whole DB) instead of performing VACUUM FULL ? In both cases I have no access to data, but first is much faster (by subjective estimate). Thank you, Ilya A. Kovalenko (mailto:[EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
