Re: [SQL] Porting from PL/SQL to PLPGSQL
Jomon, > 1)In Exceptions ORACLE have something called WHEN OTHERS THEN cluase.I > can't find a replacement for that in > PostGreSQL. PL/pgSQL currently does not handle exceptions at all. This is on the TODO list. > 2 Oracle have a function USERENV to get the user session > information.Is there any replacement available in PostGres. ? There are several variables and tools which provide current user and session information. See the "PostgreSQL Administration" section of the documentation. > 3 Is there any replace available for INSTEAD OF INSERT/DELETE/UPDATE > for triggers in PostGreSQL. In Postgres, this is generally done through the RULES system instead of triggers. Please lookup CREATE RULE in the online docs. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Order of triggers - totally lost
Dear Jonathan, Thanks for your will to help. It would be a bit difficult to clean up everything as much as possible (while keeping the problem) and send the skeleton. It looks like the A_AU trigger I mentioned solved the problem. Actually, the situation I painted is much much simplified compared to the real one (about 20 or more tables are accessed during that "simple 1-line update"). What I'd probably use best, are some generic guidelines: * what is sure about trigger execution order? (Cristoph Haller partially answered my question, quoting future plans) * are there generic recommendations what kind of things to put in before and after triggers? * how about FOR EACH STATEMENT triggers? (we only use FOR EACH ROW triggers) G. --- cut here --- - Original Message - From: "Jonathan Gardner" <[EMAIL PROTECTED]> Sent: Wednesday, August 13, 2003 4:54 PM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 13 August 2003 03:11, SZŰCS Gábor wrote: > > What may be wrong? Any ideas to re-organize parts of the triggers? > May putting the update to an A_AU trigger help? I tried it, still > have problems (not sure it's still the trigger order), but the > trigger order is still strange for me: I'd need some solid code to solve this. Can you send the create statements and the insert statement that started it all? I get the feeling that you may have more triggers than you really need. ---(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] How to check: is some key referenced from sometable
Hello, Bruno.
You wrote 9 08 2003, 18:08:09:
BWI> On Mon, Aug 04, 2003 at 11:17:56 +0300,
BWI> [EMAIL PROTECTED] wrote:
>> Please help.
>>
>> Need a boolean function which returns true if given key in table is
>> referensed from another table(s).
BWI> Use "exists" with a subselect. Something like:
BWI> select exists(select 1 from table where table.key = 'value');
Thanx for advice, but way I know and it can't be used.
That's why I've got too many and/or too big tables from which
such key is referenced. I mean (in some simplified way):
table A ( -- main table A
id SERIAL,
PRIMARY KEY(id)
);
tableBxx (-- a lot of tables which got a.id as FOREIGN KEY
... -- too much to check 'em all with EXISTS queries
a_id int4 REFERENCES a(id), -- without significant perfomance loss
...
);
So I need:
"silent delete" - i.e. when trying to DELETE row from A I'll not fall
out with "$1 referential integrity violation - key in A still
referenced from Bxx", but silently doesn't delete row ('cause run
it from function and need function to executes farther after delete);
"something to check reference" - system (potgresql) is very quickly
realizes that key is referenced from another table, maybe this
information may be accessed through some system relations, or so?
Thanx for attention.
--
Best regards,
eVl
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] How to check: is some key referenced from sometable
On Mon, Aug 04, 2003 at 11:17:56 +0300, [EMAIL PROTECTED] wrote: > Please help. > > Need a boolean function which returns true if given key in table is > referensed from another table(s). Use "exists" with a subselect. Something like: select exists(select 1 from table where table.key = 'value'); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] looking for empty fields
Title: Viorel Thank you for responding and sorry to waste your time. I just realized my stupid mistake after sending the message! It was an integer field. Jodi Viorel Dragomir wrote: - Original Message - From: JodiKanter To: Postgres SQL List Sent: Wednesday, August 06, 2003 5:07PM Subject: [SQL] looking for emptyfields We recently upgraded from version 7.2.3 to 7.3.3 and seem to have somecode that has broken. We were doing checks in various locations looking fornulls and/or empty fields. To search for empty fields we said something like select count(am_pk) from arraymeasurement where al_fk is null oral_fk=''; It's a string value that you're searching for? If not, don't use ''. Is this not allowed anymore? Allmy check with the double ticks are failing. I assume there is a difference inpostgres between an empty and null field. How can I check for both in7.3.3? Thanks Jodi -- ___ Jodi L Kanter BioInformatics DatabaseAdministrator University of Virginia (434) 924-2846 [EMAIL PROTECTED] -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [SQL] length of recordset read through a cursor
>> After declaring a cursor, one way of obtaining the length of the >resultset >> is to perform a "MOVE 0" and read the PQcmdStatus which returns a >"MOVE nn" >> where nn is the length of the resultset. (A negative MOVE can then be >used >> to allow starting to fetch records from the beginning of the >resultset.) >> >> Is there another, possibly faster way? >> >Looks like you're using libpq (because you mention PQcmdStatus), >then after declaring a cursor and FETCH ALL, try > >1.3.4. Retrieving SELECT Result Information > >PQntuples Returns the number of tuples (rows) in the query result. > >int PQntuples(const PGresult *res); > >I'm not exactly sure what you're trying to achieve or going to do, >so if I misunderstood you, ask again. > >Regards, Christoph Thanks for your reply. What I'm trying to do is the following: I want to browse through a view containing more than 1 records. To avoid slowing things down too much, I would like my client program to receive (through the network) only the records that are to be displayed on the screen. I believe I could do this by declaring a cursor and then fetching the parts of the resultset I need. It would be useful to know the size of the resultset immediately after the cursor has been declared. How do I get this information? I could of course fetch all of the resultset, but that is what I am trying to avoid. Shouldn't it be quicker to perform a move through the set than fetching it? I found that moving zero records results in a move to the end of the resultset, with a command status returning the number of records moved. Although I expected this method to take less time than a fetch (does it?), I was wondering if there might be another way to get the size of the resultset that can be fetched through the declared cursor. KP ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
we make a dump before ... and with a product pgmanager (ems tech) we do not show any foreign keys. But I think all of triggers of referenced table are not deleted. We recreate the schemas ... and now is running. How is it possible to identify Triggers with no referenced tables ? Stephan Szabo a écrit: On Mon, 11 Aug 2003, [ISO-8859-1] Beno?t Bournon wrote: No trigger Just triggers for foreign key in spectacle and none in spectacle_v How is it possible to verify triggers for foreign keys ? Generally a select on pg_trigger. Each foreign key should have 3 triggers, 1 on the referencing table and 2 on the referenced. You find the tables involved by crossreferencing tgrelid against the oid of the row in pg_class. Can you send the results of a pg_dump -s? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] (no subject)
We have been using 7.3.3 on several different applications and have discovered an anomaly. Backups are done nightly using "pg_dump --attribute-inserts -f att.dump.`dcode` spdb"; Dump files are used to restore and test. This test method has been used successfully on all previous versions. However, in 7.3.3, when the dump utility hits a carriage return imbeded with in a text field, the dump utility immeadiately jumps to the next record rendering the dump of successive records useless. i.e. INSERT INTO registry (rid, sid, aflag, fname, lname, mi, addr, city, state, zip, phone, email, weddate, regdate, items, \ notes, ref) VALUES (1148, 503, NULL, 'Jillian', 'Kooker', ' ', '', '', '', '', '610-649-3327', '', '2002-10-24', '2002-0\ 8-15', 'HP: 9120 Malis Henderson Veil: S2378 sparkle tulle and rat-tail edge', 'Used an 888 slip^M << What am I doing wrong?? thanks -- A. R. Van Hook Honeywell Federal Manufacturing & Technologies IT System Engineer City of Lake Lotawana MO Mayor pager(816)458-2585 cell (816)564-0769 [EMAIL PROTECTED] (816)997-3531 [EMAIL PROTECTED] (816)578-4704 [EMAIL PROTECTED] (816)578-4215 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Using rowtype as function argument
Martin Brommer <[EMAIL PROTECTED]> writes: > How do I call a function that takes a rowtype for an argument as in: > CREATE FUNCTION myfunc(mytablename) RETURNS INT AS ' SELECT myfunc(mytablename.*) FROM mytablename; This also works at the moment, but seems less clear to me: SELECT myfunc(mytablename) FROM mytablename; regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] User-defined SQL function has slower query on 7.3.3 than
Mr. Lane:
QUERY
-
SELECT ZIPCODE
FROM LOCATIONS
WHERE
COUNTRY = 'USA' AND ZIP_DIST_MI('07306', ZIPCODE) <= 25;
I found that the 7.1.3 server performed QUERY very slowly after a VACUUM
ANALYZE. (I can't just ANALYZE in this version, right?) It's performance
was comparable to the 7.3.3 server for awhile. Then, it improved.
I don't know how to prove that an SPI query uses an index. I do know that
this SQL: select latitude, longitude from geo_zipdata where zip = $1
uses the index through PSQL.
I use an elog(NOTICE, ...) to print when the SQL get prepared, and it is
just once. geo_zipdata is never changed for the life of the database.
db=> explain
db-> select latitude, longitude from geo_zipdata where zip = '07306';
QUERY PLAN
-
Index Scan using geo_zipdata_zip_idx on geo_zipdata (cost=0.00..17.07
rows=5 width=16)
Index Cond: (zip = '07306'::character varying)
(2 rows)
I expect QUERY to need a single full table scan for each ZIPCODE. I just
think that 7500 rows should never take over a minute. PG 7.3.3 takes 9
minutes (the one time we waited for it to finish).
How many data pages could 7500 rows need? With 2 or 3 page reads, it can't
take up much memory or I/O to do that.
- Andrew
On Wed, 6 Aug 2003, Tom Lane wrote:
> Andrew Droffner <[EMAIL PROTECTED]> writes:
> > I have this QUERY (below) that PostgreSQL 7.3.X servers run MUCH slower
> > than
> > the 7.1.3 server does.
>
> I know of no reason for that to happen. Have you vacuum analyzed the
> 7.3 database?
>
> > It finds the ZIPs locations with a prepared
> > (and saved) SPI query, which uses an index:
> > "select latitude, longitude from geo_zipdata where zip = $1"
>
> How do you know it's using the index?
>
> regards, tom lane
>
--
[ Andrew Droffner
[ Advance Publications Internet
[
[ [EMAIL PROTECTED]
---(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] Order of triggers - totally lost
G - > Am I reading you right, and statement triggers don't work in 7.3? or some > aspect of the order of statement- and row-level triggers? Correct, they don't work in 7.3. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sub-sel/group problem
> > select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as task > from rides r, loco_dets l where r.rlid = l.lid group by rtid; > Looks like another implicit FROM clause mystery. Try ... GROUP BY r.rtid ; The same goes for the JOIN. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] sub-sel/group problem
Hi folks,
I don;t know if it's cos it's Monday or what, but I can't see what's wrong
here.
I've got two tables, turns which holds a turn number, a task, and where
appropriate a link to a loco. The select below works but only shows those
tasks where a loco is involved.:
select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as task
from rides r, loco_dets l where r.rlid = l.lid group by rtid;
rtid | task
--+-
5 | G on 60007
6 | A on 75014, C on 75014, A on 75029, C on 75029
7 | C on 4277, A on 44767, C on 44767
8 | A on 30926, C on 30926, G on 60532
9 | A on 30926, C on 30926, A on 75014, C on 75014
10 | F on 2392, F on 75029, L on 75029
11 | A on 44767, C on 44767, A on 75029
However, when I tried to change this to using an outer join I'm getting stuck.
Can anyone see my stumbling point, which I think is to do with the condition
part of the case statement. Do I need to do that in a sub-select first or is
there an alternative?
=# select r.rtid,
-# case when r.rlid > 0 then
-#concat(r.rcid::text || ' on ' || l.lnumber::text)
-# else
-#r.rcid::text
-# end as task
-# from rides r
-# left outer join loco_dets l on r.rlid = l.lid
-# group by rtid
-# order by rtid
-# ;
ERROR: Attribute r.rlid must be GROUPed or used in an aggregate function
=#
Watch out.
When using outer joins you get NULL values.
some_text || NULL = NULL (always)
Try this:
select r.rtid,
concat(r.rcid::text || coalesce(' on ' || l.lnumber::text,'')
as task
from rides r
left outer join loco_dets l on r.rlid = l.lid
group by r.rtid
order by r.rtid
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Comparing arrays
Bertrand Petit wrote:
In two table I store "objects" and their attributes. The
attributes, which are not meant to be searched, are stored as unbound
arrays of varchars. I have a query that needs to use those attributes
on both sides of an EXCEPT statement:
SELECT left.id, left.attribs FROM left
EXCEPT SELECT right.id, right.attribs FROM right;
That query can't be planed because of the following error: "Unable to
identify an ordering operator '<' for type 'character varying[]'".
This should work on 7.4 beta.
create table myleft (id int, attribs varchar[]);
insert into myleft values(1,'{a,b}');
insert into myleft values(2,'{c,d}');
insert into myleft values(3,'{e,f}');
create table myright (id int, attribs varchar[]);
insert into myright values(2,'{c,d}');
regression=# SELECT myleft.id, myleft.attribs FROM myleft EXCEPT SELECT
myright.id, myright.attribs FROM myright;
id | attribs
+-
1 | {a,b}
3 | {e,f}
(2 rows)
I thought that I could build such an operator using PL/pgSQL,
unfortunately this language can't receive arguments of type ANYARRAY.
This also works on 7.4 beta.
So this led me to the creation of a new ATTRIBUTES data type, the
should be acceptable as an argument to a PL/pgSQL procedure.
Why do that -- I thought your data was in varchar[]? PL/pgSQL can have
varchar[] as an argument in 7.3.
test=# create or replace function testplpgsql(varchar[], int) returns
varchar as 'begin return $1[$2]; end;' language plpgsql;
CREATE FUNCTION
test=# select testplpgsql('{a}'::varchar[],1);
testplpgsql
-
a
(1 row)
test=# select testplpgsql('{a,b}'::varchar[],2);
testplpgsql
-
b
(1 row)
test=# select version();
version
-
PostgreSQL 7.3.3 on i686-redhat-linux-gnu, compiled by GCC 2.96
(1 row)
In any case, 7.3 and before have many issues with effective use of
arrays. For instance, even if you got all this working, you'd find
working with arrays in PL/pgSQL painful if not impossible.
If you really depend on arrays, I'd strongly suggest testing on 7.4 beta
and moving to 7.4 as soon as it is released.
HTH
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] (no subject)
"A. Van Hook" <[EMAIL PROTECTED]> writes: > Dump files are used to restore and test. This test method has been used > successfully on > all previous versions. However, in 7.3.3, when the dump utility hits a > carriage return imbeded with in a text field, the > dump utility immeadiately jumps to the next record rendering the dump of > successive records useless. AFAICT, pg_dump with -D in 7.3 and current dumps embedded carriage returns and newlines literally, same as it always has. I suspect your problems are not actually with Postgres, but with some other tool that is misreading or altering the dump file. Personally, I would use pg_dump without -D. Recent versions give a fairly nice behavior for embedded control characters: COPY foo (f1) FROM stdin; aaa\rbbb\r\nccc aaa\rbbb\r\nccc \. regards, tom lane ---(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] [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
On Mon, 11 Aug 2003, BenLaKnet wrote: > we make a dump before ... and with a product pgmanager (ems tech) we do > not show any foreign keys. That's possible, I'd have guessed that it should be dumping the triggers as CREATE CONSTRAINT TRIGGER commands in the dump. > But I think all of triggers of referenced table are not deleted. If you can make a repeatable test case (a simpler one than your full data set) that'd be helpful to look at. One possibility is that older versions (7.1 and earlier) had a bug that would cause the constraint triggers to be reloaded without the information of which table it was associated with, but I don't see how that'd apply here. > We recreate the schemas ... and now it's running. > > How is it possible to identify Triggers with no referenced tables ? Generally you'd need to look through pg_trigger manually looking for something that doesn't match up correctly against the constraints you expect to have. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] converting interval to timestamp
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 31 July 2003 09:05, teknokrat wrote: > The difference of two dates/timestamps always gives an interval. is > there a way to convert this interval into number such as number of > milliseconds or number of days? > Take a look at the User's Guide. There is great documentation on the available functions to manipulate and extract useful information from dates and timestamps. I think the function you are looking for is "extract". > Also does anyone know what field type an interval would map to in jdbc? > Post this question it the interfaces list. I am sure they would be more helpful. - -- Jonathan Gardner <[EMAIL PROTECTED]> Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/M84pWgwF3QvpWNwRAnOiAKC9t3rZNve8KyOgXyxMG/p75OxkTwCeP+Tv ZYuvijykpHJ3GZiwD6hIvnI= =onBr -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] possible?
If I don't get it worng, you wanna know how many threads you have for each forum, and how many posts you have for each thread... don't you? maybe something like SELECT F.id AS forumId, ( SELECT count(id) FROM thread WHERE forumId=F.id ) AS threadCount, T.id AS threadId, ( SELECT count(id) FROM posts WHERE threadId=T.id ) AS postCount FROM Forum F INNER JOIN thread T ON (T.forumId=F.id) or perhaps SELECT F.id AS forumId, T.id AS threadId, coalesce(TC.threadCount, 0) AS threadCount, coalesce(PC.postCount, 0) AS postCount FROM forum F INNER JOIN thread T ON (T.forumId=F.id) LEFT JOIN ( SELECT T.forumId, count(T.id) AS threadCount FROM thread T GROUP BY T.forumId ) TC ON (TC.forumId=F.id) LEFT JOIN ( SELECT P.threadID, count(P.id) AS postCount FROM posts P GROUP BY P.threadId ) PC ON (PC.threadId=T.id) should work. On Mon, 2003-08-11 at 16:41, Fejes Jozsef wrote: Hi! I'd like to make a bulletin board, and came upon a strange question: can the forum listing be done only with one single SELECT? My data structure is like this. Table "forum" holds the main categories, each with an "id" field. Table "thread" holds the threads, each has it's own "id" too, and a "forumid" field that specifies it's parent caregory. Table "post" holds the posts, with "id", and with a "threadid" field, that specifies which thread this post belongs to, and also each post has a timestamp. First the user should see a listing of the "forum" table. Then he selects a forum, and he should see the a listing of the "thread" table with the appropriate "forumid". It's not too serious so far. Here are my problems: - order the listings by the time the last post was made - display how many posts that "forum" or "thread" contains - for forums, display how many threads are in it I checked out some forum implementations so far, and found nothing helpful. They use too many queries, or don't order by time (like PHPBB). On the main page, I can list the forums, and then make a query for each of them to tell how many threads and posts are in it, but I just don't like it. By creating views, I was even able to order by time, but couldn't tell the number of threads. Also, after creating a new thread, it's empty, so "WHERE forum.id = thread.forumid AND thread.id = post.threadid" doesn't return empty threads, so noone can post to it. What I'd really love to see is a single SELECT statement for each of the two listings that display everything I want. With data views, embedded selects, or anything. So, is it possible? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org signature.asc Description: This is a digitally signed message part
Re: [SQL] How to optimize this query ?
On Wed, 13 Aug 2003, ProgHome wrote: > I tried with some LEFT JOINS, which give me the possibility to keep > the information of the right table. > > I have now the following query, which is 10 times faster !!! (from 16s > to 1.6s) > But I'd like to remove the last subquery, to see if it faster ;) > > > - > > SELECT lead. * > FROM lead > LEFT JOIN purchase ON ( lead.id = purchase.lead_id ) > LEFT JOIN affiliate_lockout ON ( lead.affiliate_id = > affiliate_lockout.affiliate_locked_id ) > WHERE ( > exclusive IS NULL OR ( > exclusive = 0 AND nb_purchases < 3 > ) > ) AND id NOT > IN ( > > SELECT lead_id > FROM purchase > INNER JOIN member_exclusion > WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND > purchase.member_id = 21101 > ) AND ( > affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS > NULL > ) AND purchase.member_id <> 21101 > GROUP BY lead.id As I replied to Franco for his query below, I believe this query is not equivalent to your original query for a few cases, but those might not come up. If you had a row in lead like id = 2, affiliate_id = 2 And rows in affiliate_lockout like: affiliate_locked_id=2, member_id=21101 affiliate_locked_id=2, member_id=31101 should this row in lead be shown or not? In the original query I think it would not (because lead.affiliate_id was IN the affiliate_lockout table where member_id=21101). In the above query I think it will, because one of the joined tables will have the lead information and a member_id that is not equal to 21101. > -Original Message- > From: Stephan Szabo [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 13, 2003 1:10 PM > To: Franco Bruno Borghesi > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [SQL] How to optimize this query ? > > On 13 Aug 2003, Franco Bruno Borghesi wrote: > > > Maybe its better now. Anyway, what I think is that joining will > perform > > better than using IN. Am I wrong? > > Generally that's true (for 7.3 and earlier). For 7.4 IN has gotten much > better, and you probably want to retry with IN. However, it's possible > that NOT EXISTS will work better than left joins even in 7.3 and > earlier, > I'm not sure, I think it's probably situational. > > I think that you're still going to have a problem in the below if there > are purchase rows with member_id 21101 and some other value that both > match. I think you need to do something like the subselect on > affiliate_lockout in the from on purchase as well. > > > SELECT > >L.* > > FROM > > lead L > > LEFT JOIN purchase P ON (L.id=P.lead_id) > > LEFT JOIN member_exclusion M ON > > (P.member_id=M.member_id_to_exclude) > > LEFT JOIN ( > >SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout > WHERE > > member_id=21101 > > ) A ON (L.affiliate_id=A.affiliated_locled_id) > > WHERE > > L.exclusive IS NULL OR > > ( > > L.exclusive=0 AND > > L.nb_purchases<3 > > ) AND > > (P.lead_id IS NULL OR P.lead_id<>21101) AND > > [I think this was meant to be member_id from the original query] > > > (M.member_id IS NULL) AND > > (A.member_id IS NULL) > > > > > ---(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] How to optimize this query ?
Maybe its better now. Anyway, what I think is that joining will perform better than using IN. Am I wrong? SELECT L.* FROM lead L LEFT JOIN purchase P ON (L.id=P.lead_id) LEFT JOIN member_exclusion M ON (P.member_id=M.member_id_to_exclude) LEFT JOIN ( SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout WHERE member_id=21101 ) A ON (L.affiliate_id=A.affiliated_locled_id) WHERE L.exclusive IS NULL OR ( L.exclusive=0 AND L.nb_purchases<3 ) AND (P.lead_id IS NULL OR P.lead_id<>21101) AND (M.member_id IS NULL) AND (A.member_id IS NULL) On Tue, 2003-08-12 at 22:29, Stephan Szabo wrote: On Tue, 12 Aug 2003, Franco Bruno Borghesi wrote: > I *guess* this query does the same as yours (please verify). It does not in general unfortunately. :( I see two possible problems. The first is that by using an inner join you're going to lose any rows where there is no match on the right hand table which the original doesn't. The second is that if there were two rows in affiliate_lockout with the different member_ids but the same affiliate_locked_id say, (21101, 10) and (21201, 10) and you were matching a lead row with a affiliate_id of 10, the second row would get past the condition since it has a member_id!=21101, but the original would drop the row because there existed some matching affiliate_lockout row where the member_id was 21101. > SELECT > L.* > FROM > lead L > INNER JOIN purchase P ON (L.id=P.lead_id) > INNER JOIN affiliate_lockout A ON > (L.affiliate_id=A.affiliate_locked_id) > INNER JOIN member_exclusion M ON > (P.member_id=M.member_id_to_exclude) > WHERE > L.exclusive IS NULL OR > ( > L.exclusive=0 AND > L.nb_purchases<3 > ) AND > P.lead_id<>21101 AND > A.member_id<>21011 > > > Hi all > > > > I have to optmize this query, because it takes a while to run (about > > 30s) > > > > Here are the tables (with the keys): > > affiliate_lockout (6 rows) (member_id, affiliate_id) > > lead (4490 rows) (id, ...) > > member (6 rows) (id, ...) > > member_exclusion (3 rows)(member_id, member_id_to_exclude) > > purchase (1 rows)(lead_id, member_id, ...) > > > > > > Here is the query: > > SELECT * > > FROM lead > > WHERE > > (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND > > > > id NOT IN ( > > > > SELECT lead_id > > FROM purchase > > WHERE member_id = 21101 > > ) AND affiliate_id NOT > > IN ( > > > > SELECT affiliate_locked_id > > FROM affiliate_lockout > > WHERE member_id = 21101 > > ) AND id NOT > > IN ( > > > > SELECT lead_id > > FROM purchase > > INNER JOIN member_exclusion > > WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND > > purchase.member_id = 21101 > > ) > > > > > > I wonder the problem is with the subqueries (which are apparently very > > slow to run, according to what I read), but I can't figure how to > > rewrite this query without any subquery ... > > > > Maybe the problem comes from the index ... How would you create your > > indexes to optimize this query ? > > > > Could somebody help me ? > > Thanks > > krysto > > > > ---(end of > > broadcast)--- TIP 5: Have you checked our > > extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > signature.asc Description: This is a digitally signed message part
Re: [SQL] RI_ConstraintTrigger_11264756
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 31 July 2003 12:18, Charles Hauser wrote: > All, > > I have inherited a table (below) with a RI trigger. > I believe this table was created where both clone_id & blastx_id are FKs > w/ stipulation 'ON DELETE CASCADE'. > > My question is, is this specified in the trigger: > RI_ConstraintTrigger_11264756? > > If so how would I learn the content of the trigger knowing just the > trigger id (11264756)? > > I have not seen a 'decoding' of triggers using pgsql. > Look at the pg_trigger table. There is some documentation on what the various fields mean. Normally, I see these triggers when I have declared a table to have a foreign key, or I created a table that has a foreign key that references this table. I can usually decode who is referencing what from the tgargs column. Example: \000order_materials\000order_shipping\000UNSPECIFIED\000order_ship_id\000order_ship_id\000 Translates to: order_materials.order_ship_id references order_shipping.order_ship_id I have found a ton of these as I have upgraded from 7.2 to 7.3. I have dropped most of the triggers and altered the table to have the foreign keys to remedy this. - -- Jonathan Gardner <[EMAIL PROTECTED]> Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/M9BAWgwF3QvpWNwRAmrKAKDrkW914nrOqiUm4D9JCYsmD982awCfYh3T pvdmcoJfo0f/bIFubyMUfbs= =yzi0 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to optimize this query ?
On Tue, 12 Aug 2003, Franco Bruno Borghesi wrote: > I *guess* this query does the same as yours (please verify). It does not in general unfortunately. :( I see two possible problems. The first is that by using an inner join you're going to lose any rows where there is no match on the right hand table which the original doesn't. The second is that if there were two rows in affiliate_lockout with the different member_ids but the same affiliate_locked_id say, (21101, 10) and (21201, 10) and you were matching a lead row with a affiliate_id of 10, the second row would get past the condition since it has a member_id!=21101, but the original would drop the row because there existed some matching affiliate_lockout row where the member_id was 21101. > SELECT > L.* > FROM > lead L > INNER JOIN purchase P ON (L.id=P.lead_id) > INNER JOIN affiliate_lockout A ON > (L.affiliate_id=A.affiliate_locked_id) > INNER JOIN member_exclusion M ON > (P.member_id=M.member_id_to_exclude) > WHERE > L.exclusive IS NULL OR > ( > L.exclusive=0 AND > L.nb_purchases<3 > ) AND > P.lead_id<>21101 AND > A.member_id<>21011 > > > Hi all > > > > I have to optmize this query, because it takes a while to run (about > > 30s) > > > > Here are the tables (with the keys): > > affiliate_lockout (6 rows) (member_id, affiliate_id) > > lead (4490 rows) (id, ...) > > member (6 rows) (id, ...) > > member_exclusion (3 rows)(member_id, member_id_to_exclude) > > purchase (1 rows)(lead_id, member_id, ...) > > > > > > Here is the query: > > SELECT * > > FROM lead > > WHERE > > (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND > > > > id NOT IN ( > > > > SELECT lead_id > > FROM purchase > > WHERE member_id = 21101 > > ) AND affiliate_id NOT > > IN ( > > > > SELECT affiliate_locked_id > > FROM affiliate_lockout > > WHERE member_id = 21101 > > ) AND id NOT > > IN ( > > > > SELECT lead_id > > FROM purchase > > INNER JOIN member_exclusion > > WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND > > purchase.member_id = 21101 > > ) > > > > > > I wonder the problem is with the subqueries (which are apparently very > > slow to run, according to what I read), but I can't figure how to > > rewrite this query without any subquery ... > > > > Maybe the problem comes from the index ... How would you create your > > indexes to optimize this query ? > > > > Could somebody help me ? > > Thanks > > krysto > > > > ---(end of > > broadcast)--- TIP 5: Have you checked our > > extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Order of triggers - totally lost
Dear Jonathan, thanks for your POV. - Original Message - From: "Jonathan Gardner" <[EMAIL PROTECTED]> Sent: Wednesday, August 13, 2003 6:20 PM > I try to avoid triggers unless it is completely obvious what they are > doing and why I should use them. This tends to minimize the number of > them hanging around. I am a programmer - python, perl mostly - so the > logic flow of triggers isn't something I can keep a firm handle on all > the time. We prefer using triggers to protect the server as well as client logic and even wizards (some of them have more than 100 graph vertices and edges) to help the users avoid illegal operations. In short: double protection. > My tables only end up with a couple of triggers, if any. I have a ton of A couple of triggers for most tables, yes. But for over 100 tables, this is a couple hundred triggers total. Is that what you meant? > stored procedures lying around, however -- pretty much one for each > "action" a user would take to modify the database. If a trigger > triggers another trigger, they aren't dependant on the order thereof, > or even the existance of the other trigger. > > You may also want to examine PostgreSQL's RULE system (CREATE RULE). I > think some of your triggers may be interchangeable with rules. You have a point there... not sure I'll have the time to experiment (we are ordered to make only small changes to be easily commitable to the in-production version) G. --- cut here --- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Insert a description while creating a table
> > I want to insert descriptions at the columns of my tables but
without
> > using the command COMMENT ON. I want to do it together with the
table
> > creation. Is that possible?
> >
> > I wanna do something like this:
> >
> > create table test (
> > id serial 'Descripitions about ID',
> > name varchar(50) 'Descriptions about NAME'
> > );
>
> Probably not going to happen in the backend.
>
> However, you should be able to accomplish that with a little bit of
Perl
> to pre-process the SQL.
>
That perl script comes to my mind too.
The reason why Luiz doesn't like it, may be because you can't
see these descriptions within psql using \dd test
I did
the create table, then
COMMENT ON COLUMN test.id is 'Descripitions about ID';
COMMENT ON COLUMN test.name is 'Descriptions about NAME';
\dd test shows
Object descriptions
Schema | Name | Object | Description
+--++-
(0 rows)
This is odd. OK, I know the doc says
\dd [ pattern ]
Shows the descriptions of objects matching the pattern, or of all
visible objects if no argument is given. But in either case,
only objects that have a description are listed. ("Object" covers
aggregates, functions, operators, types, relations (tables, views,
indexes, sequences, large objects), rules, and triggers.) For
example:
Nothing about columns.
But what is the purpose of comments on columns if you can only get them
via
select * from pg_description where
objoid = (select typrelid from pg_type where typname='test')
order by objsubid ;
objoid | classoid | objsubid | description
+--+--+-
17326 | 1259 |1 | Descripitions about ID
17326 | 1259 |2 | Descriptions about NAME
(2 rows)
which you'll have to find out on your own.
Regards, Christoph
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] optimisation of a code
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 11 August 2003 15:19, krysto wrote: > Hi all > > I wrote a script in PHP with mysql (hum ... to be honnest, I wrote > this script under PostGreSQL but we recently migrated to mysql, so I > had to adapt my code to mysql ... sorry about that ... anyway, it is > the same kind of query, with subqueries !) but this is a very very > long script ... > > I was wondering if there is a way to optimize this one, by doing some > JOIN (because the subqueries don't seem to be very powerful ...) > Take this to the postgresql-performance list. They love taking things apart and getting it running faster. They'll want: - - The tables - - The indexes - - The queries - - The results of EXPLAIN and EXPLAIN ANALYZE on the queries - - What you have already tried and what seems to work and what doesn't. - -- Jonathan Gardner <[EMAIL PROTECTED]> Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/ORjgWgwF3QvpWNwRAszNAKDuIybxFQuXa9IwrqW0UQf+Iqyb9gCgxqaK s/MPbNjorsXVMutSAiVEAk4= =tiHM -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
On Mon, 11 Aug 2003, [ISO-8859-1] Benoît Bournon wrote: > No trigger > > Just triggers for foreign key in spectacle and none in spectacle_v > > How is it possible to verify triggers for foreign keys ? Generally a select on pg_trigger. Each foreign key should have 3 triggers, 1 on the referencing table and 2 on the referenced. You find the tables involved by crossreferencing tgrelid against the oid of the row in pg_class. Can you send the results of a pg_dump -s? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to check: is some key referenced from sometable
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Saturday 09 August 2003 14:44, eVl One wrote:
> So I need:
>"silent delete" - i.e. when trying to DELETE row from A I'll not
> fall out with "$1 referential integrity violation - key in A still
> referenced from Bxx", but silently doesn't delete row ('cause run it
> from function and need function to executes farther after delete);
> "something to check reference" - system (potgresql) is very quickly
> realizes that key is referenced from another table, maybe this
> information may be accessed through some system relations, or so?
> Thanx for attention.
You may want to check out the "ON DELETE" and "ON UPDATE" clauses of the
FOREIGN KEY syntax. This will help you keep you data consistent.
Otherwise, if you want to know what is referencing something, you'll
have to query all of those tables. Your database structure should be
well-defined and thought out, so you should be able to figure out what
all is referencing it. If you need it for day-to-day queries, you may
want to rethink your design.
If you like, take a look at the pg_trigger table. The way PostgreSQL
manages the foreign key references is through triggers. You may be able
to identify which triggers are which, and from there, determine which
tables are referencing which tables.
- --
Jonathan Gardner <[EMAIL PROTECTED]>
Live Free, Use Linux!
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/ORn/WgwF3QvpWNwRAsJ5AJ9iGmopncQsyizb6yIh2CvCQsSmwQCg0UZ/
xImGLW35bdAJSuzCWc7yxBU=
=eQGH
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] How to optimize this query ?
OK, here is the final query without any subquery ... -- SELECT L. * FROM lead L LEFT JOIN purchase P ON ( L.id = P.lead_id ) LEFT JOIN member_exclusion M ON ( P.member_id = M.member_id_to_exclude ) LEFT JOIN ( SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout WHERE member_id = 21101 ) AS A ON ( L.affiliate_id = A.affiliate_locked_id ) WHERE L.exclusive IS NULL OR ( L.exclusive = 0 AND L.nb_purchases < 3 ) AND ( P.lead_id IS NULL OR P.lead_id <> 21101 ) AND ( M.member_id IS NULL ) GROUP BY L.id --- I've got the same result as before, so it should be correct ;) By the way, the time seems to be the same (1.41s for the last form, and 1.44s now) but I think it's because I don't have much stuff in Member_exclusion (6 rows) so I will keep this query without subquery ... Thanks, Franco ! PS : definitively, try to avoid the subqueries ! It's easy to program, but very slow to execute ! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] How to speeed up the query performance
How do I speed up the quey performance if I've a query like this : Does 'not in' command will affected the performance?. select ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber from transportsetup ts where ts.bizid = 'B126' and ts.deletestatus = 0 and ts.transportid not in ( select t.transportid f rom transportsetup t,servicedetail s,logisticservice l where t.bizid=l.bizid and l.serviceid=s.serviceid and t.transportid=s.transportid and t.bizid = 'B126' and l.status='Pending' or t.bizid=l.bizid and l.serviceid=s.serviceid and t.transportid=s.transportid and t.bizid = 'B126' and l.status='Reserved' ) order by ts.transporttype; Any help pretty much appreciated. Thanks
Re: [SQL] Insert a description while creating a table
On Wed, 2003-08-13 at 07:56, [EMAIL PROTECTED] wrote: > Hi, > > I want to insert descriptions at the columns of my tables but without > using the command COMMENT ON. I want to do it together with the table > creation. Is that possible? > > I wanna do something like this: > > create table test ( > id serial 'Descripitions about ID', > name varchar(50) 'Descriptions about NAME' > ); Probably not going to happen in the backend. However, you should be able to accomplish that with a little bit of Perl to pre-process the SQL. signature.asc Description: This is a digitally signed message part
Re: [SQL] [GENERAL] Postgresql slow on XEON 2.4ghz/1gb ram
On Wed, Aug 06, 2003 at 10:50:38AM -0300, Wilson A. Galafassi Jr. wrote: > Hello. > I have this problem: i'm running the postgre 7.3 on a windows 2000 server > with P3 1GHZ DUAL/1gb ram with good performance. For best performance i > have change the server for a XEON 2.4/1gb ram and for my suprise the > performance decrease 80%. anybody have a similar experience? does exist > any special configuration to postgre running on a Xeon processor? Any have > any idea to help-me? Excuse-me my bad english. I assume you've done the vacuums, analyze, configured the wal and shmem option appropriate for that size machine. But in any case, without specific examples about what you're seeing we can't help you. -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato pgp0.pgp Description: PGP signature
Re: [SQL] Retrieving tuple data on insert
> you can retrieve it using the currentVal function on the sequence used to > generate that id. Thank you. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
we make a dump before ... and with a product pgmanager (ems tech) we do not show any foreign keys. But I think all of triggers of referenced table are not deleted. We recreate the schemas ... and now it's running. How is it possible to identify Triggers with no referenced tables ? Stephan Szabo a écrit: On Mon, 11 Aug 2003, BenLaKnet wrote: How could I do a pg_dump -s ?? on local server ?? Yes. I figured you wouldn't want to send your backups with data (I assume you have pg_dump generated backups ;) ). You can use pg_dump locally or remotely (using -h and -p options). Locally, log in as the postgres user and run pg_dump -s , remotely do pg_dump -s -h -p I believe.
Re: [SQL] [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
No trigger Just triggers for foreign key in spectacle and none in spectacle_v How is it possible to verify triggers for foreign keys ? Stephan Szabo a écrit: On Fri, 8 Aug 2003, BenLaKnet wrote: When I duplicate this code in an other table named spectacle_v without Foreygn key ... all is running. But when I try to delete a spectacle_membre, linked value in spectacle are correctly deleted, but I have an error for spectacle_v which is not linked : (ERROR: referential integrity violation - key in membre_adherent still referenced from spectacle_v ) What triggers are defined on membre_adherent? ---(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] PostgreSQL and Journaled File Systems
Roberto Mello <[EMAIL PROTECTED]> writes: > What is the word about using PG with journaled file systems? I have heard > that it's best to not have the PG journal in a journaled system to avoid > double journaling. Journaling metadata is good. Journaling file contents is redundant --- turn that off if you can. (Of course, if there is anything other than Postgres files on the same device, you may not want to turn off contents journalling...) regards, tom lane ---(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] Error message with a SQL function
[EMAIL PROTECTED] (krysto) writes: > CREATE FUNCTION update_affiliate(int,double precision,double > precision) RETURNS void AS ' UPDATE affiliate SET balance=balance + > $2, balance_in_points=balance_in_points + ( $2 / $3 ) WHERE id = $1; ' > LANGUAGE 'sql'; I don't believe there was a type "void" in PG 7.1. IIRC, back then the custom was to say "RETURNS opaque" in this situation. It's probably time for you to update to something newer ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Insert a description while creating a table
> On Wed, Aug 13, 2003 at 02:54:59PM +0200, Christoph Haller wrote:
> > \dd test shows
> >
> > Object descriptions
> > Schema | Name | Object | Description
> > +--++-
> > (0 rows)
> >
> > This is odd. OK, I know the doc says
> > \dd [ pattern ]
> >
> > Shows the descriptions of objects matching the pattern, or of
all
> > visible objects if no argument is given. But in either case,
> > only objects that have a description are listed. ("Object"
covers
> > aggregates, functions, operators, types, relations (tables, views,
> > indexes, sequences, large objects), rules, and triggers.) For
> > example:
> >
> > Nothing about columns.
> > But what is the purpose of comments on columns if you can only get
them
> > via
> > select * from pg_description where
>
> Have you tried \d+ tablename? (sorry for the long lines ";-)
>
I apologize for complaining, missed that completely.
Thanks to Reinoud for pointing this out.
Regards, Christoph
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Insert a description while creating a table
Hi, I want to insert descriptions at the columns of my tables but without using the command COMMENT ON. I want to do it together with the table creation. Is that possible? I wanna do something like this: create table test ( id serial 'Descripitions about ID', name varchar(50) 'Descriptions about NAME' ); Thanks in advance Luiz. -- * * Luiz Fernando Pinto * * -*- * * Klais Desenvolvimento * * [EMAIL PROTECTED] * * ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Testing gateway
In theory, the news2mail gateway is back in place ... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Order of triggers - totally lost
Dear Josh, thanks for the guidelines. - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> Sent: Wednesday, August 13, 2003 5:55 PM > My perspective: multiple triggers of the same type on the same table are a > really bad idea if execution order matters. Any setup like this is > automatically maintenence hell, even when we add some sort of "trigger > priority" feature. No such thing here. At least, no such that may affect the same tuple (i.e. AI and AU are separate triggers, but I won't gain anything with a single AIU trigger that runs twice, in unpredictable order) > before triggers: re-formating input and inserting complex defaults before > saving to table; historical archiving of old data; data validation. > > after triggers: updating related tables; chronological logging of activity. just about the same as we use it, but just hours ago, I had to put some of the after stuff (updating related tables) to the before trigger, because it seemed to be much more simple. > > * how about FOR EACH STATEMENT triggers? > >(we only use FOR EACH ROW triggers) > > These will not work until 7.4, and then there will be some limitations (which > will hopefully go away in 7.5). Am I reading you right, and statement triggers don't work in 7.3? or some aspect of the order of statement- and row-level triggers? G. --- cut here --- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Order of triggers - totally lost
> The docs say in trigger-datachanges.html: > > "Changes made by query Q are visible by queries that are started after query > Q, no matter whether they are started inside Q (during the execution of Q) > or after Q is done. > > This is true for triggers as well ..." > > Trying to understand that, I have a feeling that the update in A_BIUD should > already see the results of the preceding insert, including the results of > triggers activated by that insert. I've seen that PostgreSQL Weekly News - July 25th 2003 Even though we are nearing release, it doesn't hurt to look ahead, as evidenced by several additions to the TODO list this week: delay resolution of array expression type to assignment coercion can be performed on empty array expressions, have AFTER triggers execute after the appropriate SQL statement in a function, not at the end of the function, more details on possible tablespace definitions were added, add PL/PHP, allow PL/pgSQL to name columns by ordinal position (e.g. rec.(3)), and prevent COMMENT ON DATABASE from using a database name. Some of these already have folks working on them, however if you are interested in lending a hand please don't be afraid to speak up. Looks like this passage about AFTER triggers is what you're expecting, but is not implemented yet. > > What may be wrong? Any ideas to re-organize parts of the triggers? May > putting the update to an A_AU trigger help? I tried it, still have problems > (not sure it's still the trigger order), but the trigger order is still > strange for me: > No ideas, sorry. Maybe move over to the [HACKERS] list for more professional support. Regards, Christoph ---(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] How to speeed up the query performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 12 August 2003 20:20, Abdul Wahab Dahalan wrote: > How do I speed up the quey performance if I've a query like this : > Does 'not in' command will affected the performance?. > Yes. Severely. See the responses to the "How to optimize this query ?" thread. If you want more details, check the pgsql-performance archives. - -- Jonathan Gardner <[EMAIL PROTECTED]> Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/OlBBWgwF3QvpWNwRAuzJAJ99iMmMbU/tiJhi077+8WCmAId76ACffL+5 biOZSLPbuhWZBL6MNlZE3V0= =Sg0n -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Analyze makes queries slow...
On Fri, 8 Aug 2003 09:24:48 -0700 Jonathan Gardner <[EMAIL PROTECTED]> wrote: => Try the performance list. Thanks for the tip Stef ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Insert a description while creating a table
Hi Christoph,
I'm thinking that the best solution is create a script in perl or
python that executes de COMMENT command to me. My initial idea would be
comment my columns in a standard way and then run the script.
My comment that will turn into description will start with
"/*$" instead of the simple "/*", for instance.
Regards,
Luiz.
On Wed, 13 Aug 2003, Christoph Haller wrote:
> > > I want to insert descriptions at the columns of my tables but
> without
> > > using the command COMMENT ON. I want to do it together with the
> table
> > > creation. Is that possible?
> > >
> > > I wanna do something like this:
> > >
> > > create table test (
> > > id serial 'Descripitions about ID',
> > > name varchar(50) 'Descriptions about NAME'
> > > );
> >
> > Probably not going to happen in the backend.
> >
> > However, you should be able to accomplish that with a little bit of
> Perl
> > to pre-process the SQL.
> >
> That perl script comes to my mind too.
> The reason why Luiz doesn't like it, may be because you can't
> see these descriptions within psql using \dd test
>
> I did
> the create table, then
> COMMENT ON COLUMN test.id is 'Descripitions about ID';
> COMMENT ON COLUMN test.name is 'Descriptions about NAME';
> \dd test shows
>
> Object descriptions
> Schema | Name | Object | Description
> +--++-
> (0 rows)
>
> This is odd. OK, I know the doc says
> \dd [ pattern ]
>
> Shows the descriptions of objects matching the pattern, or of all
> visible objects if no argument is given. But in either case,
> only objects that have a description are listed. ("Object" covers
> aggregates, functions, operators, types, relations (tables, views,
> indexes, sequences, large objects), rules, and triggers.) For
> example:
>
> Nothing about columns.
> But what is the purpose of comments on columns if you can only get them
> via
> select * from pg_description where
> objoid = (select typrelid from pg_type where typname='test')
> order by objsubid ;
> objoid | classoid | objsubid | description
> +--+--+-
> 17326 | 1259 |1 | Descripitions about ID
> 17326 | 1259 |2 | Descriptions about NAME
> (2 rows)
>
> which you'll have to find out on your own.
>
> Regards, Christoph
>
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
>
--
*
* Luiz Fernando Pinto *
* -*- *
* Klais Desenvolvimento *
* [EMAIL PROTECTED] *
*
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Porting from PL/SQL to PLPGSQL
> we are facing a few problems with PL/SQL Code.. > > 1)In Exceptions ORACLE have something called WHEN OTHERS THEN cluase.I > can't find a replacement for that in > PostGreSQL. What does it do? > 2 Oracle have a function USERENV to get the user session > information.Is there any replacement available in PostGres. ? CURRENT_USER (I believe this is per spec) > 3 Is there any replace available for INSTEAD OF INSERT/DELETE/UPDATE > for triggers in PostGreSQL. You might have some luck with Rules (CREATE RULE) if you are writing data to a different table. Otherwise you're stuck with somewhat more complicated triggers. signature.asc Description: This is a digitally signed message part
Re: [SQL] INSERT INTO ... SELECT
Thanks, Christoph for your thought. An alternative solution I have is to fetch the user table first and act according with the retured value. It doesn't seem to have a single query solution. v. -- - Original Message - DATE: Wed, 13 Aug 2003 13:40:53 From: Christoph Haller <[EMAIL PROTECTED]> To: [EMAIL PROTECTED], [EMAIL PROTECTED] Cc: >> >> Coincidentally, I have a very similar case using some twists. >> >> The table I want to insert data is something like >> >> table A ( >> user01 int, >> user02 int, >> ... >> primary key (user01, user02), >> CHECK ( user01 < user02 ) >> ); >> >> And the user table is: >> >> tabe user_table ( >> user int constraint pk_user primary key UNIQUE, --PK >> ..., >> email varchar(25) not null, >> ... >> }; >> >> In the insertion statement, the user02 is obtained by a subselect >statement: "select user from user where email=?". >> >> There may not exist the email in the user table. And if yes, the check >condition may not be met. If the condition is n >ot met, the two user IDs need to be switched. >> >> How the query shall be construted? >> >> Thanks for any suggestions. >> > >I'd say this conditional insert into user02 column can be done by a >PL/pgSQL function >combined by CASE statements. >I'm thinking of something like (untested of course) >INSERT INTO ... >SELECT >CASE WHEN user01 < get_user02(email=?) >THEN user01 ELSE get_user02(email=?) END, >CASE WHEN user01 < get_user02(email=?) >THEN get_user02(email=?) ELSE user01 END, >... >FROM table A ... ; > >You might think calling the function four times is a waste, >but as far as I understand it PL/pgSQL functions are run via >prepared execution plans (see PL/pgSQL - SQL Procedural Language - >Overview), >so it shouldn't be too bad. >Don't know what to do about >> There may not exist the email in the user table. >Insert a NULL? > >HTH > >Regards, Christoph > > > Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] INSERT INTO ... SELECT
Hi, I would like to insert into a table values from a table and user defined ones. Here is the example: I found this statement to insert values from another table: INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER; But the test_table has another column, which should have the same value for all the customers. Is there something like INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, name from CUSTOMER: and if so, what ist the correct statement? If not, what is an alternative to insert a single row at a time? Thanks in advance Silke ---(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] How to speeed up the query performance
> > How do I speed up the quey performance if I've a query like this : > Does 'not in' command will affected the performance?. Yes, it's well known to be slow in 7.3 and lower, should be fixed in 7.4 AFAIK. > > select > ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber > from transportsetup ts > where ts.bizid = 'B126' > and ts.deletestatus = 0 > and ts.transportid not in (select t.transportid > from transportsetup t,servicedetail s,logisticservice l > where t.bizid=l.bizid > and l.serviceid=s.serviceid > and t.transportid=s.transportid > and t.bizid = 'B126' > and l.status='Pending' > or t.bizid=l.bizid > and l.serviceid=s.serviceid > and t.transportid=s.transportid > and t.bizid = 'B126' and l.status='Reserved' ) > order by ts.transporttype; > As recently mentioned by Stephan Szabo on '[SQL] How to optimize this query ?' NOT EXISTS performs much better. Try: select ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber fromtransportsetup ts where ts.bizid = 'B126' and ts.deletestatus = 0 and NOT EXISTS (select t.transportid from transportsetup t,servicedetail s,logisticservice l where ts.transportid = t.transportid and t.bizid=l.bizid and l.serviceid=s.serviceid and t.transportid=s.transportid and t.bizid = 'B126' and l.status='Pending' or t.bizid=l.bizid and l.serviceid=s.serviceid and t.transportid=s.transportid and t.bizid = 'B126' and l.status='Reserved' ) order by ts.transporttype; Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How to speeed up the query performance
Hai Chris! Thanks for the solution but seem it doesnt work. (0 rows) returned when I used NOT EXITS but (4 rows) returned when NOT IN is used... FYI I used 7.2 Christoph Haller wrote: How do I speed up the quey performance if I've a query like this : Does 'not in' command will affected the performance?. Yes, it's well known to be slow in 7.3 and lower, should be fixed in 7.4 AFAIK. select ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber from transportsetup ts where ts.bizid = 'B126' and ts.deletestatus = 0 and ts.transportid not in (select t.transportid from transportsetup t,servicedetail s,logisticservice l where t.bizid=l.bizid and l.serviceid=s.serviceid and t.transportid=s.transportid and t.bizid = 'B126' and l.status='Pending' or t.bizid=l.bizid and l.serviceid=s.serviceid and t.transportid=s.transportid and t.bizid = 'B126' and l.status='Reserved' ) order by ts.transporttype; As recently mentioned by Stephan Szabo on '[SQL] How to optimize this query ?' NOT EXISTS performs much better. Try: select ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber fromtransportsetup ts where ts.bizid = 'B126' and ts.deletestatus = 0 and NOT EXISTS (select t.transportid from transportsetup t,servicedetail s,logisticservice l where ts.transportid = t.transportid and t.bizid=l.bizid and l.serviceid=s.serviceid and t.transportid=s.transportid and t.bizid = 'B126' and l.status='Pending' or t.bizid=l.bizid and l.serviceid=s.serviceid and t.transportid=s.transportid and t.bizid = 'B126' and l.status='Reserved' ) order by ts.transporttype; Regards, Christoph
[SQL] Error message with a SQL function
Hi I try to create the following SQL function: CREATE FUNCTION update_affiliate(int,double precision,double precision) RETURNS void AS ' UPDATE affiliate SET balance=balance + $2, balance_in_points=balance_in_points + ( $2 / $3 ) WHERE id = $1; ' LANGUAGE 'sql'; but when I submit this query directly with PostgreSQL, I have the following error message: PostgreSQL said: ERROR: Unable to locate type oid 325664 in catalog I think the problem comes from the first parameter ($1) but I don't understand because affiliate.id is indeed the primary key of the table affiliate. Has anybody an idea ? Thanks Krysto ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to optimize this query ?
Sorry, I posted the following message on the newsgroups, but it seems that you didn't see it ... I tried with some LEFT JOINS, which give me the possibility to keep the information of the right table. I have now the following query, which is 10 times faster !!! (from 16s to 1.6s) But I'd like to remove the last subquery, to see if it faster ;) - SELECT lead. * FROM lead LEFT JOIN purchase ON ( lead.id = purchase.lead_id ) LEFT JOIN affiliate_lockout ON ( lead.affiliate_id = affiliate_lockout.affiliate_locked_id ) WHERE ( exclusive IS NULL OR ( exclusive = 0 AND nb_purchases < 3 ) ) AND id NOT IN ( SELECT lead_id FROM purchase INNER JOIN member_exclusion WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND purchase.member_id = 21101 ) AND ( affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS NULL ) AND purchase.member_id <> 21101 GROUP BY lead.id -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 1:10 PM To: Franco Bruno Borghesi Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [SQL] How to optimize this query ? On 13 Aug 2003, Franco Bruno Borghesi wrote: > Maybe its better now. Anyway, what I think is that joining will perform > better than using IN. Am I wrong? Generally that's true (for 7.3 and earlier). For 7.4 IN has gotten much better, and you probably want to retry with IN. However, it's possible that NOT EXISTS will work better than left joins even in 7.3 and earlier, I'm not sure, I think it's probably situational. I think that you're still going to have a problem in the below if there are purchase rows with member_id 21101 and some other value that both match. I think you need to do something like the subselect on affiliate_lockout in the from on purchase as well. > SELECT >L.* > FROM > lead L > LEFT JOIN purchase P ON (L.id=P.lead_id) > LEFT JOIN member_exclusion M ON > (P.member_id=M.member_id_to_exclude) > LEFT JOIN ( >SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout WHERE > member_id=21101 > ) A ON (L.affiliate_id=A.affiliated_locled_id) > WHERE > L.exclusive IS NULL OR > ( > L.exclusive=0 AND > L.nb_purchases<3 > ) AND > (P.lead_id IS NULL OR P.lead_id<>21101) AND [I think this was meant to be member_id from the original query] > (M.member_id IS NULL) AND > (A.member_id IS NULL) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to optimize this query ?
On 13 Aug 2003, Franco Bruno Borghesi wrote: > Maybe its better now. Anyway, what I think is that joining will perform > better than using IN. Am I wrong? Generally that's true (for 7.3 and earlier). For 7.4 IN has gotten much better, and you probably want to retry with IN. However, it's possible that NOT EXISTS will work better than left joins even in 7.3 and earlier, I'm not sure, I think it's probably situational. I think that you're still going to have a problem in the below if there are purchase rows with member_id 21101 and some other value that both match. I think you need to do something like the subselect on affiliate_lockout in the from on purchase as well. > SELECT >L.* > FROM > lead L > LEFT JOIN purchase P ON (L.id=P.lead_id) > LEFT JOIN member_exclusion M ON > (P.member_id=M.member_id_to_exclude) > LEFT JOIN ( >SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout WHERE > member_id=21101 > ) A ON (L.affiliate_id=A.affiliated_locled_id) > WHERE > L.exclusive IS NULL OR > ( > L.exclusive=0 AND > L.nb_purchases<3 > ) AND > (P.lead_id IS NULL OR P.lead_id<>21101) AND [I think this was meant to be member_id from the original query] > (M.member_id IS NULL) AND > (A.member_id IS NULL) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] How to optimize this query ?
Hi all I have to optmize this query, because it takes a while to run (about 30s) Here are the tables (with the keys): affiliate_lockout (6 rows) (member_id, affiliate_id) lead (4490 rows) (id, ...) member (6 rows) (id, ...) member_exclusion (3 rows)(member_id, member_id_to_exclude) purchase (1 rows)(lead_id, member_id, ...) Here is the query: SELECT * FROM lead WHERE (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND id NOT IN ( SELECT lead_id FROM purchase WHERE member_id = 21101 ) AND affiliate_id NOT IN ( SELECT affiliate_locked_id FROM affiliate_lockout WHERE member_id = 21101 ) AND id NOT IN ( SELECT lead_id FROM purchase INNER JOIN member_exclusion WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND purchase.member_id = 21101 ) I wonder the problem is with the subqueries (which are apparently very slow to run, according to what I read), but I can't figure how to rewrite this query without any subquery ... Maybe the problem comes from the index ... How would you create your indexes to optimize this query ? Could somebody help me ? Thanks krysto ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Query suddenly taking longer....
Hi all,
I have the following query on postgresql 7.3.2 on RedHat 7.
select *, (select count(*) from xrefmembergroup where membergroupid =
m.id) as numberingroup from membergroup m;
(basically- try to get a list of 'groups' and the number of members in
each group)
The xrefmembergroup table has about 120,000 rows, membergroup has 90.
This query has been running very quickly, but has suddenly started
taking a LONG LONG time. Nothing has else has really changed in the
system, this morning it just started taking too long (went from .5
seconds to > 5 minutes).
Now, when I do run this query my postmaster process spikes from around
10Megs (normal size) to around 250Megs and just kinda sits there until
it eventually returns 5 minutes later.
I get the feeling that the xrefmembergroup table has crossed some bounds
(disk/memory) that is causing it to be super slow, but I don't know
which one. I have b-tree indexes on all the fields in xrefmembergroup.
Here's the table definition:
Column | Type | Modifiers
---+--+
id| integer | not null default
nextval('"xrefmembergroup_id_seq"'::text)
membergroupid | integer | not null default 0
memberid | integer | not null default 0
timestamp | timestamp with time zone | default
"timestamp"('now'::text)
Indexes: xrefmembergroup_pkey primary key btree (id),
membergroupid_xrefmembergroup_key btree (membergroupid),
memberid_xrefmembergroup_key btree (memberid)
At one point, I did an EXPLAIN ANALYZE on the query and it seemed to be
using sequential scans. I can't run this query anymore because it nukes
my production server, so I'm limited in how much I can debug this right
now. I have a similar system (7.3.2 on Debian) that does not exhibit
this problem running on the same database. Don't know why its not using
the indexes. Any thoughts?
/kurt
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Changing data type must recreate all views?
I need to change column data type from integer to float8 That mean to droping table and recreate a new one and can lost the original object id. Do i need to recreate all views and triggers that relate to that table? if that so, is there anyway to do that without touching views and triggers? Thanks, yudie ---(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] How to speeed up the query performance
On Thu, 14 Aug 2003, Abdul Wahab Dahalan wrote: > Hai Chris! > Thanks for the solution but seem it doesnt work. > (0 rows) returned when I used NOT EXITS but (4 rows) returned > when NOT IN is used... Maybe you need a set of parenthesis around the old conditions because of the or. > >Try: > >select > >ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber > > > >fromtransportsetup ts > >where ts.bizid = 'B126' > >and ts.deletestatus = 0 > >and NOT EXISTS (select t.transportid > >from transportsetup t,servicedetail s,logisticservice l > >where ts.transportid = t.transportid > >and t.bizid=l.bizid Replace the above with: and (t.bizid=l.bizid > >and l.serviceid=s.serviceid > >and t.transportid=s.transportid > >and t.bizid = 'B126' > >and l.status='Pending' > >or t.bizid=l.bizid > >and l.serviceid=s.serviceid > >and t.transportid=s.transportid > >and t.bizid = 'B126' and l.status='Reserved' ) Add: ) > >order by ts.transporttype; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Timezone troubles
Jesse Scott <[EMAIL PROTECTED]> writes: >> Ok, I was actually dyslexic when I read the version number, I have >> 7.3.2 not 7.2.3, I'll investigate the timezone status though. In that case I think you are looking for this 7.3.3 fix: 2003-02-27 16:37 tgl * src/backend/utils/adt/timestamp.c (REL7_3_STABLE): Change EXTRACT(EPOCH FROM timestamp) so that a timestamp without time zone is assumed to be in local time, not GMT. This improves consistency with other operations, which all assume local timezone when it matters. Per bug #897. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] INSERT INTO ... SELECT
> > Coincidentally, I have a very similar case using some twists. > > The table I want to insert data is something like > > table A ( > user01 int, > user02 int, > ... > primary key (user01, user02), > CHECK ( user01 < user02 ) > ); > > And the user table is: > > tabe user_table ( > user int constraint pk_user primary key UNIQUE, --PK > ..., > email varchar(25) not null, > ... > }; > > In the insertion statement, the user02 is obtained by a subselect statement: "select user from user where email=?". > > There may not exist the email in the user table. And if yes, the check condition may not be met. If the condition is n ot met, the two user IDs need to be switched. > > How the query shall be construted? > > Thanks for any suggestions. > I'd say this conditional insert into user02 column can be done by a PL/pgSQL function combined by CASE statements. I'm thinking of something like (untested of course) INSERT INTO ... SELECT CASE WHEN user01 < get_user02(email=?) THEN user01 ELSE get_user02(email=?) END, CASE WHEN user01 < get_user02(email=?) THEN get_user02(email=?) ELSE user01 END, ... FROM table A ... ; You might think calling the function four times is a waste, but as far as I understand it PL/pgSQL functions are run via prepared execution plans (see PL/pgSQL - SQL Procedural Language - Overview), so it shouldn't be too bad. Don't know what to do about > There may not exist the email in the user table. Insert a NULL? HTH Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How to optimize this query ?
I tried with some LEFT JOINS, which give me the possibility to keep the information of the right table. I have now the following query, which is 10 times faster !!! (from 16s to 1.6s) But I's like to remove the last subquery, to see if it faster ;) Can somebody help me ? - SELECT lead. * FROM lead LEFT JOIN purchase ON ( lead.id = purchase.lead_id ) LEFT JOIN affiliate_lockout ON ( lead.affiliate_id = affiliate_lockout.affiliate_locked_id ) WHERE ( exclusive IS NULL OR ( exclusive = 0 AND nb_purchases < 3 ) ) AND id NOT IN ( SELECT lead_id FROM purchase INNER JOIN member_exclusion WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND purchase.member_id = 21101 ) AND ( affiliate_lockout.member_id <> 21101 OR affiliate_lockout.member_id IS NULL ) AND purchase.member_id <> 21101 GROUP BY lead.id ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to optimize this query ?
On 12 Aug 2003, krysto wrote: > Hi all > > I have to optmize this query, because it takes a while to run (about > 30s) > > Here are the tables (with the keys): > affiliate_lockout (6 rows) (member_id, affiliate_id) > lead (4490 rows) (id, ...) > member (6 rows) (id, ...) > member_exclusion (3 rows)(member_id, member_id_to_exclude) > purchase (1 rows)(lead_id, member_id, ...) > I think you can possibly get better results in 7.3 and earlier by using NOT EXISTS. > Here is the query: > SELECT * > FROM lead > WHERE > (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND > NOT EXISTS ( select lead_id from purchase where lead_id=lead.id and member_id=21101) > id NOT IN ( > SELECT lead_id > FROM purchase > WHERE member_id = 21101 AND NOT EXISTS ( select * from affiliate_lockout WHERE member_id=21101 and affiliate_locked_id=lead.affiliate_id) > ) AND affiliate_id NOT > IN ( > > SELECT affiliate_locked_id > FROM affiliate_lockout > WHERE member_id = 21101 > ) AND id NOT > IN ( > > SELECT lead_id > FROM purchase > INNER JOIN member_exclusion > WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND > purchase.member_id = 21101 Doesn't this condition end up giving you a subset of the rows in the first one? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Porting from PL/SQL to PLPGSQL
Hi, We are in the process of migrating of our application from Oracle to PostGreSQL. we are facing a few problems with PL/SQL Code.. 1) In Exceptions ORACLE have something called WHEN OTHERS THEN cluase.I can't find a replacement for that in PostGreSQL. 2 Oracle have a function USERENV to get the user session information.Is there any replacement available in PostGres. ? 3 Is there any replace available for INSTEAD OF INSERT/DELETE/UPDATE for triggers in PostGreSQL. Regards Jomon Skariah. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Order of triggers - totally lost
G: > * what is sure about trigger execution order? >(Cristoph Haller partially answered my question, quoting future plans) My perspective: multiple triggers of the same type on the same table are a really bad idea if execution order matters. Any setup like this is automatically maintenence hell, even when we add some sort of "trigger priority" feature. If your table needs multiple BEFORE operations, you should put the operations into one large procedure with branching logic. > * are there generic recommendations what kind of things to put in >before and after triggers? before triggers: re-formating input and inserting complex defaults before saving to table; historical archiving of old data; data validation. after triggers: updating related tables; chronological logging of activity. > * how about FOR EACH STATEMENT triggers? >(we only use FOR EACH ROW triggers) These will not work until 7.4, and then there will be some limitations (which will hopefully go away in 7.5). -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Insert a description while creating a table
comment on table "test" is 'fziofeiozfiezojfezjfeziojfeziojf ezfjioezifj ezio' ; Rod Taylor wrote: On Wed, 2003-08-13 at 07:56, [EMAIL PROTECTED] wrote: Hi, I want to insert descriptions at the columns of my tables but without using the command COMMENT ON. I want to do it together with the table creation. Is that possible? I wanna do something like this: create table test ( id serial 'Descripitions about ID', name varchar(50) 'Descriptions about NAME' ); Probably not going to happen in the backend. However, you should be able to accomplish that with a little bit of Perl to pre-process the SQL.
Re: [SQL] Insert a description while creating a table
On Wed, Aug 13, 2003 at 02:54:59PM +0200, Christoph Haller wrote:
> \dd test shows
>
> Object descriptions
> Schema | Name | Object | Description
> +--++-
> (0 rows)
>
> This is odd. OK, I know the doc says
> \dd [ pattern ]
>
> Shows the descriptions of objects matching the pattern, or of all
> visible objects if no argument is given. But in either case,
> only objects that have a description are listed. ("Object" covers
> aggregates, functions, operators, types, relations (tables, views,
> indexes, sequences, large objects), rules, and triggers.) For
> example:
>
> Nothing about columns.
> But what is the purpose of comments on columns if you can only get them
> via
> select * from pg_description where
Have you tried \d+ tablename? (sorry for the long lines ";-)
drbob=# \dd object
Object descriptions
Schema | Name | Object | Description
+++-
public | object | table | The table that holds all instances of objects.
(1 row)
drbob=# \d+ object
Table "public.object"
Column |Type |
Modifiers |
Description
--+-+--+-
id | integer | not null default
nextval('shared_ids'::text) | (global) unique ID for this table
object_def_id| integer | not null
| foreign key to object_def.id
name | character varying(60) |
| natural name of this object
superobject_id | integer |
| foreign key to object.id. This field is only filled in when the this
object is a subobject of another object (EG a port in a router)
subobject_def_id | integer |
| foreign key to subobject_def.id. This field is only filled in when this
object is a subobject of another object (EG a port in a router)
has_subobject| boolean | not null default 'FALSE'
| boolean indicating that this object instance has subobjects (EG router
that has ports)
date_added | timestamp without time zone |
| date this record was added to the database
user_added | character varying(20) |
| username that added this record to the database
date_changed | timestamp without time zone |
| date this record was last changed
user_changed | character varying(20) |
| username that made the last change to this record
Indexes: pk_object primary key btree (id),
object_pk unique btree (id),
object_object_def_fk btree (object_def_id),
object_object_fk btree (superobject_id),
object_subobject_def_fk btree (subobject_def_id)
Triggers: tdb_object,
tiua_object,
tiub_object
--
__
"Nothing is as subjective as reality"
Reinoud van Leeuwen[EMAIL PROTECTED]
http://www.xs4all.nl/~reinoud
__
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] How to optimize this query ?
Title: RE: [SQL] How to optimize this query ? Actually, I have got another query where I need to remove the subqueries ... It is almost the same query but this time, I don't search in the table LEAD but in the table MEMBER … HERE are the tables: affiliate_lockout (6 rows) (member_id, affiliate_id) automated (4 rows) (member_id, …) lead (4490 rows) (id, ...) member (6 rows) (id, ...) member_exclusion (3 rows) (member_id, member_id_to_exclude) purchase (1 rows) (lead_id, member_id, ...) select member.id, automated.delivery, member.email from (automated INNER JOIN member ON member.id = automated.member_id) where activated=1 and website='$SITE_NAME' and (select count(*) from trans_member where (unix_timestamp(now())-unix_timestamp(date)) < (unix_timestamp(now())-'$today_midnight') and type='purchase' and comment LIKE '%automated%' and member_id=member.id and comment LIKE '%$type%') < max_$field and balance_in_points > $price and credit_ratings_t$n LIKE '%$lead[borrower_credit_rating]%' and states LIKE '%$lead[prop_state]%' and ltv_t$n/100 >= (cast($lead[loan_amount] as unsigned) / cast($lead[current_value] as unsigned)) and amount_t$n < $lead[loan_amount] AND $id NOT IN (select lead_id from purchase where member_id=member.id) AND $aff_id NOT IN (select affiliate_locked_id from affiliate_lockout where member_id=member.id) AND $id NOT IN (select lead_id from purchase where member_id IN (select member_id_to_exclude from member_exclusion where member_id=member.id)) ORDER BY balance_in_points DESC For this one, I really don’t know how to remove the 3 subqueries at the end because the $id and $aff_id are values external to the query, and there is no table to join … I tried to remove the subqueries and to rewrite them to 3 small external queries that I run for each result given by this first query, but I guess this will be much longer if the tables are big … What do you think about ?
Re: [SQL] Order of triggers - totally lost
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 13 August 2003 08:40, SZŰCS Gábor wrote: > Actually, the situation I painted is much much simplified compared to > the real one (about 20 or more tables are accessed during that > "simple 1-line update"). What I'd probably use best, are some generic > guidelines: > I try to avoid triggers unless it is completely obvious what they are doing and why I should use them. This tends to minimize the number of them hanging around. I am a programmer - python, perl mostly - so the logic flow of triggers isn't something I can keep a firm handle on all the time. Here are some examples of when I use triggers: - One column is completely dependent on one or more other columns (e.g., total_revenue, total_cost, profit). A 'before' insert/update trigger works here. That one trigger can do all of the calculations for the row. - A row is summary of several other rows in other tables. This is useful for collecting real-time stats, but is difficult to get right. Here, I use 'after' triggers. I also copiously document how it works, because there are always nasty bugs waiting to bite. - A particularly nasty constraint, that isn't as simple as "not null". If you noticed, the foreign key constraints are implemented with three triggers - one on the referencing table, and two on the referenced table. There are some other situations where you may want constraints that aren't as clear-cut as a foreign key that will require multiple 'before' triggers on multiple tables. When I handle a complicated procedure that involves inserting multiple rows into multiple tables, I tend to put those into plpgsql procedures. That way, I can keep control of everything and keep it clean. For instance, placing an order with several items. My tables only end up with a couple of triggers, if any. I have a ton of stored procedures lying around, however -- pretty much one for each "action" a user would take to modify the database. If a trigger triggers another trigger, they aren't dependant on the order thereof, or even the existance of the other trigger. You may also want to examine PostgreSQL's RULE system (CREATE RULE). I think some of your triggers may be interchangeable with rules. - -- Jonathan Gardner <[EMAIL PROTECTED]> Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/OmU2WgwF3QvpWNwRAklXAJ4hv+2Fx5jZXG6ykpOMMNLvG655owCdFtEo +eV+ZcrItpOerAPySiSPe2g= =e1Ao -END PGP SIGNATURE- ---(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] Insert a description while creating a table
create table test ( id serial , name varchar(50) ); COMMENT ON TABLE "test" IS 'MySQL table id_training_form ... id_form_unique'; comment on COLUMN "test"."id" is 'greigjreoigjreigjore iojrjiogre' ; Doc postgresql comment Rod Taylor wrote: On Wed, 2003-08-13 at 07:56, [EMAIL PROTECTED] wrote: Hi, I want to insert descriptions at the columns of my tables but without using the command COMMENT ON. I want to do it together with the table creation. Is that possible? I wanna do something like this: create table test ( id serial 'Descripitions about ID', name varchar(50) 'Descriptions about NAME' ); Probably not going to happen in the backend. However, you should be able to accomplish that with a little bit of Perl to pre-process the SQL.
Re: [SQL] Error message with a SQL function
Thanks You were right, it was because of the return void But I think in the documentation, I read somewhere that this kind of return was allowed ... I must have mistaken ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to optimize this query ?
On Wed, 13 Aug 2003, ProgHome wrote: > select member.id, automated.delivery, member.email > > from (automated INNER JOIN member ON member.id = automated.member_id) > > where activated=1 > and website='$SITE_NAME' > and (select count(*) from trans_member where > (unix_timestamp(now())-unix_timestamp(date)) < > (unix_timestamp(now())-'$today_midnight') and type='purchase' and > comment LIKE '%automated%' > and member_id=member.id and comment LIKE '%$type%') < max_$field > and balance_in_points > $price > and credit_ratings_t$n LIKE '%$lead[borrower_credit_rating]%' > and states LIKE '%$lead[prop_state]%' > and ltv_t$n/100 >= (cast($lead[loan_amount] as unsigned) / > cast($lead[current_value] as unsigned)) > and amount_t$n < $lead[loan_amount] > > AND $id NOT IN (select lead_id from purchase where > member_id=member.id) > AND $aff_id NOT IN (select affiliate_locked_id from affiliate_lockout > where member_id=member.id) > AND $id NOT IN (select lead_id from purchase where member_id IN > (select member_id_to_exclude from member_exclusion where > member_id=member.id)) In these cases I'd suggest trying a NOT EXISTS, maybe something like (for the first one): AND NOT EXISTS (select 1 from purchase where member_id=member.id AND lead_id=$id) With a two column index on purchase(member_id,lead_id) [or lead_id, member_id maybe] you might be able to do okay. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Order of triggers - totally lost
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 13 August 2003 03:11, SZŰCS Gábor wrote: > > What may be wrong? Any ideas to re-organize parts of the triggers? > May putting the update to an A_AU trigger help? I tried it, still > have problems (not sure it's still the trigger order), but the > trigger order is still strange for me: > I'd need some solid code to solve this. Can you send the create statements and the insert statement that started it all? I get the feeling that you may have more triggers than you really need. - -- Jonathan Gardner <[EMAIL PROTECTED]> Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/OlEmWgwF3QvpWNwRAnPdAKDEVKuZb+hRuF0VT2XvE2jf/NNlGwCfUnl9 FP6qKYkr8zAokDNaK4CI6rE= =0kMs -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] lower/upper functions and strings in searches
I am obviuously doing some newbie trick, and I ordinarily would spend time browing the
archives, but the archives.postgresql.org site seems to be absurdly slow.
This is 7.3.3 on a linux box.
I have a bunch of data with state, city, county and country names. When our
application does a search for an exact match:
select * from gx_geotowns where l_state = 'NM';
I get back a lot of rows of cities in New Mexico, as expected.
If I try:
select * from gx_geotowns where upper(l_state) = upper('nm');
I get back:
-+---+--++--+--
(0 rows)
I've used other databases in which a similar statement worked as exepected:
select * from clients
where upper(client_name) = upper("Some client or otheR");
And it finds the row(s) in question ...
I just know I'm overlooking some real obvious thing but for some reason this eludes
me. I could see if the search was very slow (the function returns type "text" and the
indexed columns are of type CHAR().
If someone could offer help I would appreciate it,
Thanks,
Greg Williamson
DBA GlobeXplorer LLC
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Order of triggers - totally lost
Dear Gurus, Please help to solve this chaos: I have a simple query that triggers several plpgsql triggers on several tables. Table A has a BIUD (Before Insert or Update or Delete) trigger (henceforth A_BIUD and analogues). Table B has triggers BIUD, AI and AU. Table B1 has triggers BIUD and AD. Table B2 has triggers BIUD and AIUD. Consider the following, my preferred trigger order for a specific UPDATE on table A: A_BIUD, update: insert into B (foobar); -- inserting a single tuple B_BIUD, insert -- irrelevant B_AI, insert: insert into B1 (foo2); -- one or more tuples B1.BIUD, insert: if B is done then raise exception;-- "done" is false by default. insert into B2 (foo2); -- the same one or more tuples B2.BIUD, insert -- irrelevant update B set done=true WHERE foobar; -- updating the same single tuple B_BIUD, update -- irrelevant B_AU, update: update B2 set done=true WHERE foo2; -- the same one or more tuples B2.BIUD, update -- irrelevant In short: A_BIUD, update B_BIUD, insert B_AI, insert B1_BIUD, insert B2_BIUD, insert B_BIUD, update B_AU, update However: the triggers run in the following order, until the abovementioned exception aborts the transaction: A_BIUD, update B_BIUD, insert B_BIUD, update A_BIUD, update ends here B_AI, insert B1_BIUD, insert: exception. The docs say in trigger-datachanges.html: "Changes made by query Q are visible by queries that are started after query Q, no matter whether they are started inside Q (during the execution of Q) or after Q is done. This is true for triggers as well ..." Trying to understand that, I have a feeling that the update in A_BIUD should already see the results of the preceding insert, including the results of triggers activated by that insert. What may be wrong? Any ideas to re-organize parts of the triggers? May putting the update to an A_AU trigger help? I tried it, still have problems (not sure it's still the trigger order), but the trigger order is still strange for me: A_BIUD B_BIUD, insert B_AI B1_BIUD, insert A_AU B2_AIUD, insert (!) B2_BIUD, insert (???) B_AU G. --- cut here --- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] lower/upper functions and strings in searches
On Thu, 14 Aug 2003, Gregory S. Williamson wrote:
> I am obviuously doing some newbie trick, and I ordinarily would spend time browing
> the archives, but the archives.postgresql.org site seems to be absurdly slow.
>
> This is 7.3.3 on a linux box.
>
> I have a bunch of data with state, city, county and country names. When our
> application does a search for an exact match:
>
> select * from gx_geotowns where l_state = 'NM';
> I get back a lot of rows of cities in New Mexico, as expected.
>
> If I try:
>
> select * from gx_geotowns where upper(l_state) = upper('nm');
You say the column is of type CHAR(), but CHAR(2) or something else?
For 7.3 and earlier, you're going to get a text comparison which means
that trailing spaces are significant (it's effectively no pad in text vs
pad space in char).
---(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] Query suddenly taking longer....
Tom, Thanks for the reply. I agree that the query seemed inefficient, but it ran so quickly I thought it was okay. The only difference between the two servers was that the fast one used an Index Scan while the other (the now-slow one) would use a sequential scan. The query as you re-wrote it seems to work great though. Thank you. /kurt Tom Lane wrote: Kurt Overberg <[EMAIL PROTECTED]> writes: I have the following query on postgresql 7.3.2 on RedHat 7. select *, (select count(*) from xrefmembergroup where membergroupid = m.id) as numberingroup from membergroup m; The xrefmembergroup table has about 120,000 rows, membergroup has 90. This query has been running very quickly, but has suddenly started taking a LONG LONG time. Presumably the plan changed, but without any reasonable way to tell what the old plan was, there's no way to be sure. (Possibly comparing explain plans from both servers would be useful, though.) Now, when I do run this query my postmaster process spikes from around 10Megs (normal size) to around 250Megs and just kinda sits there until it eventually returns 5 minutes later. What was the new plan, exactly? I don't see any reason for this query to chew a lot of memory. I think that the query is inherently inefficient as written, since it forces a separate scan of xrefmembergroup for every membergroup row. I don't really see how it could ever have been done in subsecond time, unless perhaps a large fraction of the xrefmembergroup entries did not match any membergroup row, which seems unlikely. I'd suggest doing something that will allow the counts to be accumulated in just one xrefmembergroup scan, with GROUP BY. A straightforward way is select m.*, numberingroup from membergroup m, (select membergroupid, count(*) as numberingroup from xrefmembergroup group by membergroupid) as c where m.id = c.membergroupid; I'm not convinced this will actually be much of a win in 7.3 unfortunately ... but it should fly in 7.4, because of the new hash aggregation code. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] INSERT INTO ... SELECT
Coincidentally, I have a very similar case using some twists. The table I want to insert data is something like table A ( user01 int, user02 int, ... primary key (user01, user02), CHECK ( user01 < user02 ) ); And the user table is: tabe user_table ( user int constraint pk_user primary key UNIQUE, --PK ..., email varchar(25) not null, ... }; In the insertion statement, the user02 is obtained by a subselect statement: "select user from user where email=?". There may not exist the email in the user table. And if yes, the check condition may not be met. If the condition is not met, the two user IDs need to be switched. How the query shall be construted? Thanks for any suggestions. v. -- - Original Message - DATE: Mon, 11 Aug 2003 21:57:05 From: Christopher Browne <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: >Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Silke Trissl) would write: >> I would like to insert into a table values from a table and user >> defined ones. Here is the example: >> >> I found this statement to insert values from another table: >> >> INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER; >> >> But the test_table has another column, which should have the same >> value for all the customers. >> >> Is there something like >> >> INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, >> name from CUSTOMER: >> >> and if so, what ist the correct statement? If not, what is an >> alternative to insert a single row at a time? > >You're close. > >The constant term needs to be inside the SELECT. > >Try: > insert into test_table (int_id, cust_id, cust_name) > select '1', id, name from customer; >-- >select 'cbbrowne' || '@' || 'acm.org'; >http://www.ntlug.org/~cbbrowne/sap.html >(eq? 'truth 'beauty) ; to avoid unassigned-var error, since compiled code > ; will pick up previous value to var set!-ed, > ; the unassigned object. >-- from BBN-CL's cl-parser.scm > >---(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster > Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] INSERT INTO ... SELECT
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Silke Trissl) would write: > I would like to insert into a table values from a table and user > defined ones. Here is the example: > > I found this statement to insert values from another table: > > INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER; > > But the test_table has another column, which should have the same > value for all the customers. > > Is there something like > > INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, > name from CUSTOMER: > > and if so, what ist the correct statement? If not, what is an > alternative to insert a single row at a time? You're close. The constant term needs to be inside the SELECT. Try: insert into test_table (int_id, cust_id, cust_name) select '1', id, name from customer; -- select 'cbbrowne' || '@' || 'acm.org'; http://www.ntlug.org/~cbbrowne/sap.html (eq? 'truth 'beauty) ; to avoid unassigned-var error, since compiled code ; will pick up previous value to var set!-ed, ; the unassigned object. -- from BBN-CL's cl-parser.scm ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] lower/upper functions and strings in searches
Gregory, > I just know I'm overlooking some real obvious thing but for some reason this eludes me. I could see if the search was very slow (the function returns type "text" and the indexed columns are of type CHAR(). Char(what?) ? if it's, say CHAR(4) that could be your problem; 'NM'::undefined == 'NM '::CHAR but 'NM'::TEXT != 'NM '::CHAR so casting everything to the desired type should fix the problem. and why are you using CHAR, anyway? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] sub-sel/group problem
On Mon, 11 Aug 2003, Gary Stainburn wrote: > I don;t know if it's cos it's Monday or what, but I can't see what's wrong > here. > > I've got two tables, turns which holds a turn number, a task, and where > appropriate a link to a loco. The select below works but only shows those > tasks where a loco is involved.: > > select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as task > from rides r, loco_dets l where r.rlid = l.lid group by rtid; I assume concat is an aggregate function? > > rtid | task > --+- > 5 | G on 60007 > 6 | A on 75014, C on 75014, A on 75029, C on 75029 > 7 | C on 4277, A on 44767, C on 44767 > 8 | A on 30926, C on 30926, G on 60532 > 9 | A on 30926, C on 30926, A on 75014, C on 75014 >10 | F on 2392, F on 75029, L on 75029 >11 | A on 44767, C on 44767, A on 75029 > > However, when I tried to change this to using an outer join I'm getting stuck. > Can anyone see my stumbling point, which I think is to do with the condition > part of the case statement. Do I need to do that in a sub-select first or is > there an alternative? > > =# select r.rtid, > -# case when r.rlid > 0 then > -#concat(r.rcid::text || ' on ' || l.lnumber::text) > -# else > -#r.rcid::text > -# end as task > -# from rides r > -# left outer join loco_dets l on r.rlid = l.lid > -# group by rtid > -# order by rtid > -# ; > ERROR: Attribute r.rlid must be GROUPed or used in an aggregate function Something like this might work: select r.rtid, concat(r.rcid::text || coalesce( ' on ' || l.lnumber::text,'')) as task from rides r left outer join loco_dets l on r.rlid=l.lid group by rtid order by rtid; IIRC, SQL99 has a complicated way of defining dependant columns for group by but SQL92 only allows you to use the columns that are grouped outside of an aggregate. In general, if r.rlid is >0 in some cases and <0 in others for a particular group, what would the query do? You might be able to show that it doesn't happen, but the system isn't quite that bright. ;) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to optimize this query ?
I *guess* this query does the same as yours (please verify). SELECT L.* FROM lead L INNER JOIN purchase P ON (L.id=P.lead_id) INNER JOIN affiliate_lockout A ON (L.affiliate_id=A.affiliate_locked_id) INNER JOIN member_exclusion M ON (P.member_id=M.member_id_to_exclude) WHERE L.exclusive IS NULL OR ( L.exclusive=0 AND L.nb_purchases<3 ) AND P.lead_id<>21101 AND A.member_id<>21011 Hope it performs better. > Hi all > > I have to optmize this query, because it takes a while to run (about > 30s) > > Here are the tables (with the keys): > affiliate_lockout (6 rows) (member_id, affiliate_id) > lead (4490 rows) (id, ...) > member (6 rows) (id, ...) > member_exclusion (3 rows)(member_id, member_id_to_exclude) > purchase (1 rows)(lead_id, member_id, ...) > > > Here is the query: > SELECT * > FROM lead > WHERE > (exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND > > id NOT IN ( > > SELECT lead_id > FROM purchase > WHERE member_id = 21101 > ) AND affiliate_id NOT > IN ( > > SELECT affiliate_locked_id > FROM affiliate_lockout > WHERE member_id = 21101 > ) AND id NOT > IN ( > > SELECT lead_id > FROM purchase > INNER JOIN member_exclusion > WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND > purchase.member_id = 21101 > ) > > > I wonder the problem is with the subqueries (which are apparently very > slow to run, according to what I read), but I can't figure how to > rewrite this query without any subquery ... > > Maybe the problem comes from the index ... How would you create your > indexes to optimize this query ? > > Could somebody help me ? > Thanks > krysto > > ---(end of > broadcast)--- TIP 5: Have you checked our > extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] INSERT INTO ... SELECT
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 11 August 2003 03:21, Silke Trissl wrote: > > Is there something like > > INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, > name from CUSTOMER: > > and if so, what ist the correct statement? If not, what is an > alternative to insert a single row at a time? > I'm not too sure on what you want, but I'll guess. INSERT INTO test_table (int_id, cust_id, cust_name) SELECT 1, id, name FROM customer; Does this do what you want? - -- Jonathan Gardner <[EMAIL PROTECTED]> Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/ORhhWgwF3QvpWNwRAsoTAJ9Wq+iZ3/JbapXeS1wrv9VgR1gtAgCfVItU ZsBeXCPBuLuOgBTWn3vu2Gc= =PJDU -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] sub-sel/group problem
Hi folks, I don;t know if it's cos it's Monday or what, but I can't see what's wrong here. I've got two tables, turns which holds a turn number, a task, and where appropriate a link to a loco. The select below works but only shows those tasks where a loco is involved.: select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as task from rides r, loco_dets l where r.rlid = l.lid group by rtid; rtid | task --+- 5 | G on 60007 6 | A on 75014, C on 75014, A on 75029, C on 75029 7 | C on 4277, A on 44767, C on 44767 8 | A on 30926, C on 30926, G on 60532 9 | A on 30926, C on 30926, A on 75014, C on 75014 10 | F on 2392, F on 75029, L on 75029 11 | A on 44767, C on 44767, A on 75029 However, when I tried to change this to using an outer join I'm getting stuck. Can anyone see my stumbling point, which I think is to do with the condition part of the case statement. Do I need to do that in a sub-select first or is there an alternative? =# select r.rtid, -# case when r.rlid > 0 then -#concat(r.rcid::text || ' on ' || l.lnumber::text) -# else -#r.rcid::text -# end as task -# from rides r -# left outer join loco_dets l on r.rlid = l.lid -# group by rtid -# order by rtid -# ; ERROR: Attribute r.rlid must be GROUPed or used in an aggregate function =# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Query suddenly taking longer....
Kurt Overberg <[EMAIL PROTECTED]> writes: > I have the following query on postgresql 7.3.2 on RedHat 7. > select *, (select count(*) from xrefmembergroup where membergroupid = > m.id) as numberingroup from membergroup m; > The xrefmembergroup table has about 120,000 rows, membergroup has 90. > This query has been running very quickly, but has suddenly started > taking a LONG LONG time. Presumably the plan changed, but without any reasonable way to tell what the old plan was, there's no way to be sure. (Possibly comparing explain plans from both servers would be useful, though.) > Now, when I do run this query my postmaster process spikes from around > 10Megs (normal size) to around 250Megs and just kinda sits there until > it eventually returns 5 minutes later. What was the new plan, exactly? I don't see any reason for this query to chew a lot of memory. I think that the query is inherently inefficient as written, since it forces a separate scan of xrefmembergroup for every membergroup row. I don't really see how it could ever have been done in subsecond time, unless perhaps a large fraction of the xrefmembergroup entries did not match any membergroup row, which seems unlikely. I'd suggest doing something that will allow the counts to be accumulated in just one xrefmembergroup scan, with GROUP BY. A straightforward way is select m.*, numberingroup from membergroup m, (select membergroupid, count(*) as numberingroup from xrefmembergroup group by membergroupid) as c where m.id = c.membergroupid; I'm not convinced this will actually be much of a win in 7.3 unfortunately ... but it should fly in 7.4, because of the new hash aggregation code. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] OFF-TOPIC: Richard Huxton, Please Contact Us!
Richard: Ned Lily and I have been sending you e-mails for 3 weeks, and you have not responded. Since you've posted to the SQL list in that time, I can only think that you're not getting our e-mails. I'm hoping that by sending you via the SQL list, you'll receive the e-mail. Please contact us. cc: to me at josh at postgresql.org just in case. Thanks! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Comparing arrays
Bertrand Petit <[EMAIL PROTECTED]> writes: > That query can't be planed because of the following error: "Unable to > identify an ordering operator '<' for type 'character varying[]'". This shortcoming is (at long last) repaired for 7.4. There is no simple solution in earlier releases, I fear. > I'm signaled that the array_out procedure is not defined: "ERROR: > TypeCreate: function array_out(attributes) does not exist". That error > sounds strange as the CREATE TYPE manual describes uniform array type > creation as illustrated above and that array_out() seems to exist as > shown bellow. CREATE TYPE wants an *exact* match of the argument/result datatypes. You could fake it out by creating another pg_proc row pointing at the same internal procedure. However, I think creating a private datatype is the hard way to go about this. A better short-run solution is just to create = and < operators for varchar[]. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Porting from PL/SQL to PLPGSQL
> Hi, > > We are in the process of migrating of our application from Oracle to > PostGreSQL. > > > we are facing a few problems with PL/SQL Code.. > > 1)Can we perform DML statements on VIEWS in PostGreSQL as we do in > ORACLE? > Do I need to create some RULES for that ?? > > 2 In Oracle sqlplus we can run sql script files as @script_name; > How do we do the same in PostGres. > Also is there any replacement for "&&" in PostGres ? > > > Eg: > CREATE USER CATALOG > IDENTIFIED BY &ORA_PASSWORD > DEFAULT TABLESPACE &DFLT_TABLESPACE > TEMPORARY TABLESPACE &TEMP_TABLESPACE > > > Regards > > Jomon Skariah. > ---(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
[SQL] Abort Transaction DP PK (again)
Hi, I know that this topic was discuted before but I like an alternative for my high load INSERT query. Why Postgres abort a transaction when find a duplicate PK ?!?! A simple Fuction test: (...) SELECT a,b from foo where a=1 and b=1 IF NOT FOUND INSERT INTO foo VALUES(1,1) ELSE UPDATE (...) Is, obviously, very very much slower than this (hipotetic) function: (...) INSERT INTO foo IF "ERROR DP PK" UPDATE (...) In both cases, INSERT will test if values violate the PK... but in first example the extra SELECT cause an unnecessary extreame work... Thanks, Alexandre ---(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] How to optimize this query ?
For the following query, I have a little problem ... First, I have to rewrite several times the same query because the alias are not recognised in the same query ( I got an error when I try to reuse the alias "nb_bogus_leads", for instance). Do you have a way to avoid this ? Because If I do so, the same query is calculated twice ... Second problem, the most important : The A.id should be for each result returned in A.*, and there should be a join to calculate the query "nb_bogus_leads" (for instance) about the A.id currently processed by the query. But it seems that this join doesn't work, because I have the same "nb_bogus_leads" and same "nb_leads_submitted" for each A.id returned (they should be different !) How can you make this query work ? Thanks SELECT A. * , ( SELECT CAST( count( * ) AS UNSIGNED ) FROM request INNER JOIN lead ON ( lead_id = lead.id ) WHERE allowed = 1 AND lead.affiliate_id = A.id ) AS nb_bogus_leads, ( SELECT CAST( count( * ) AS UNSIGNED ) FROM lead WHERE affiliate_id = A.id ) AS nb_leads_submitted, ( CASE WHEN ( SELECT CAST( count( * ) AS UNSIGNED ) FROM lead WHERE affiliate_id = A.id ) <> 0 THEN ( SELECT CAST( count( * ) AS UNSIGNED ) FROM request INNER JOIN lead ON ( lead_id = lead.id ) WHERE allowed = 1 AND lead.affiliate_id = A.id ) / ( SELECT CAST( count( * ) AS UNSIGNED ) FROM lead WHERE affiliate_id = A.id ) * 100 WHEN ( SELECT CAST( count( * ) AS UNSIGNED ) FROM lead WHERE affiliate_id = A.id ) = 0 THEN 0 END ) AS percentage_bogus_leads FROM affiliate A WHERE website = 'dev' ---(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] Abort Transaction DP PK (again)
Alexandre, > I know that this topic was discuted before but I like an alternative for > my high load INSERT query. > Why Postgres abort a transaction when find a duplicate PK ?!?! Postgres currently aborts a transaction upon *any* error condition, except warnings. We do not, currently, have a exception-handling model for procedure code. It's on the TODO list. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Optional join
Hi, I trying to write a query that will join 2 tables. Here's the concept: Table 1: table1, primary key pk1 Table 2: table2, primary key pk2 One of the fields (f2) in table2 contains either the primary key of table1 or a NULL value. So normally a pretty basic query: SELECT table1.*, table2.pk2 FROM table1, table2 WHERE table2.f2 = table1.pk1; BUT what I would like to do is show all records of Table 1 even if there is no match in Table 2. Meaning that the reults could be table1... table2.pk2 table1... NULL Doable? Thanks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Porting from PL/SQL to PLPGSQL
Hi Josh,
Thanks for your reply.
In our PL/SQL code we have used userenv('LANG') in some of the INSERT
statements.
Do we have a replacement for this..
Regards
Jomon
-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 14, 2003 1:05 AM
To: Jomon Skariah; [EMAIL PROTECTED]
Subject: Re: [SQL] Porting from PL/SQL to PLPGSQL
Jomon,
> 1)In Exceptions ORACLE have something called WHEN OTHERS THEN cluase.I
> can't find a replacement for that in
> PostGreSQL.
PL/pgSQL currently does not handle exceptions at all. This is on the TODO
list.
> 2 Oracle have a function USERENV to get the user session
> information.Is there any replacement available in PostGres. ?
There are several variables and tools which provide current user and session
information. See the "PostgreSQL Administration" section of the
documentation.
> 3 Is there any replace available for INSTEAD OF INSERT/DELETE/UPDATE
> for triggers in PostGreSQL.
In Postgres, this is generally done through the RULES system instead of
triggers. Please lookup CREATE RULE in the online docs.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] JDBC encoding problem
I'm having a rather strange problem that I'm hoping someone can help me
with. I'm using Struts 1.0/jsp on Debian linux under Tomcat 4.1.x and
the blackdown JVM . I'm attempting to convert my current SQL_ASCII
database to UNICODE. I'm new to this, so am most likely making a few
mistakes. Here's what I've done so far:
o Converted database encoding to be UNICODE. I'm pretty sure this part
worked okay. (did a pg_dump, then iconv -f 8859_1 -t UTF-8, then
created new db with encoding UNICODE and reloaded- no errors upon reload)
sparky:~$ psql -l
List of databases
Name| Owner | Encoding
---+--+---
unitest | kurt | UNICODE
template1 | postgres | SQL_ASCII
(2 rows)
o set client_encoding to 'UTF8';
o In my JSP files, I set the following at the top of each:
<%@ page lanuage="java" pageEncoding="UTF-8" %>
Now, to test this, I go to a japanese page, copy some text, then paste
it into a form, that gets submitted to the server and saved into the DB.
Then I try to display what I got back from the database. It comes out
garbled. HOWEVER- if I leave the 'pageEncoding' out of my display .jsp
file it still comes out garbled, UNTIL I set UTF-8 manually in my
browsers Character Encoding settings (both mozilla and IE). Then the
japanese characters render fine (just like I entered them).
Very strange. What's confusing is that when I set the pageEncoding to
'UTF-8', the characters don't render properly, and as far as I can tell,
thats the same as manually setting the browser manually. I must be
doing something wrong because I get the same results in IE and mozilla
(recent build).
What may be the problem- I don't do anything differently when getting
the data out of the database, just standard
resultset.getString("column"); Do I need to change that call, to handle
the potentially UTF-8 encoded strings? I can't find anything on that at
all with google/usenet.
Any and all help, suggestions or pointers would be greatly appreciated.
Thanks!
/kurt
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] User-defined SQL function has slower query on 7.3.3 than 7.1.3
Andrew Droffner <[EMAIL PROTECTED]> writes: > I have this QUERY (below) that PostgreSQL 7.3.X servers run MUCH slower > than > the 7.1.3 server does. I know of no reason for that to happen. Have you vacuum analyzed the 7.3 database? > It finds the ZIPs locations with a prepared > (and saved) SPI query, which uses an index: > "select latitude, longitude from geo_zipdata where zip = $1" How do you know it's using the index? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Timestamp in PG - 7.1 & 7.2
"Anagha Joshi" <[EMAIL PROTECTED]> writes: > Here is output from PostgreSQL 7.2.4: > =20 > trapdb=3D# select cast(datetime(1057637700) as timestamp); > timestamp > --- > 2003-07-07 22:45:00+05:30 > (1 row) > =20 > I've migrated from PG - 7.1.2 to 7.2.4. Not sure why 7.2 is misbehaving here --- it looks to me like it's adding instead of subtracting the timezone offset, but I don't see any mention of having fixed such a bug in the CVS logs. I'd suggest changing your code to abstime(n) instead of datetime(n) --- you will have to do that when you update to 7.3 anyway, and it gets the right answer on 7.2. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Postgresql slow on XEON 2.4ghz/1gb ram
Hello. I have this problem: i'm running the postgre 7.3 on a windows 2000 server with P3 1GHZ DUAL/1gb ram with good performance. For best performance i have change the server for a XEON 2.4/1gb ram and for my suprise the performance decrease 80%. anybody have a similar experience? does exist any special configuration to postgre running on a Xeon processor? Any have any idea to help-me? Excuse-me my bad english. Very Thanks Wilson icq 77032308 msn [EMAIL PROTECTED]
[SQL] User-defined SQL function has slower query on 7.3.3 than 7.1.3
I have this QUERY (below) that PostgreSQL 7.3.X servers run MUCH slower
than
the 7.1.3 server does. It makes sense that both servers have to do a
sequential scan over the ZIPCODE column. There are over 7,500 rows in the
LOCATIONS table.
Does anyone know what changed in the planner or optimizer? Can I change
the
postgresql.conf file to improve 7.3.3 performance?
Situation
-
Here is the situation...
PG 7.1.3 returns QUERY in about 4 seconds. The EXPLAIN plan says it uses
the index on country.
PG 7.3.3 simply does not return QUERY. I've waited over 3 minutes. With
the extra condition WHERE STATE = 'NJ' it takes almost 5 seconds. Other
states are much worse.
QUERY
-
SELECT ZIPCODE
FROM LOCATIONS
WHERE
COUNTRY = 'USA' AND ZIP_DIST_MI('07306', ZIPCODE) <= 25;
The function is written in C, using SPI. Given two US ZIP codes, it
returns the distance in miles. For example, it is 78 miles from Jersey
City to Philadelphia:
db=> select ZIP_DIST_MI('07306', '19130');
zip_dist_mi
-
78.801595557406
(1 row)
ZIP_DIST_MI() uses a geo_zipdata table to get the latitude and longitude.
Using those, it can calculate the "great circle distance" between ZIPs
with C double arithmetic. It finds the ZIPs locations with a prepared
(and saved) SPI query, which uses an index:
"select latitude, longitude from geo_zipdata where zip = $1"
FUNCTION
CREATE FUNCTION ZIP_DIST_MI(TEXT, TEXT)
RETURNS DOUBLE PRECISION...
ZIP DATA TABLE
--
CREATE TABLE GEO_ZIPDATA (
ZIP VARCHAR(5) NOT NULL,
STATEVARCHAR(2) NOT NULL,
CITY VARCHAR(64) NOT NULL,
COUNTY VARCHAR(64) NOT NULL,
LATITUDE FLOAT NOT NULL,
LONGITUDE FLOAT NOT NULL,
FIPS NUMERIC(10) NOT NULL
);
CREATE INDEX GEO_ZIPDATA_ZIP_IDX ON GEO_ZIPDATA (ZIP);
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Optional join
On Thu, Aug 14, 2003 at 10:40:02 -0400, Slawek Jarosz <[EMAIL PROTECTED]> wrote: > Hi, > > I trying to write a query that will join 2 tables. Here's the concept: > Table 1: table1, primary key pk1 > Table 2: table2, primary key pk2 > > One of the fields (f2) in table2 contains either the primary key of table1 or a NULL > value. So normally a pretty basic query: > > SELECT table1.*, table2.pk2 FROM table1, table2 WHERE table2.f2 = table1.pk1; > > BUT what I would like to do is show all records of Table 1 even if there is no match > in Table 2. Meaning that the reults could be > table1... table2.pk2 > table1... NULL > > Doable? You use outer joins to do this. Something like: SELECT table1.*, table2.pk2 FROM table1 left join table2 on table2.f2 = table1.pk1; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Optional join
On Thu, 14 Aug 2003, Slawek Jarosz wrote: > Hi, > > I trying to write a query that will join 2 tables. Here's the concept: > Table 1: table1, primary key pk1 Table 2: table2, primary key pk2 > > One of the fields (f2) in table2 contains either the primary key of > table1 or a NULL value. So normally a pretty basic query: > > SELECT table1.*, table2.pk2 FROM table1, table2 WHERE table2.f2 = > table1.pk1; > > BUT what I would like to do is show all records of Table 1 even if > there is no match in Table 2. Meaning that the reults could be > table1... table2.pk2 > table1... NULL See outer joins. Something like: SELECT table1.*,table2.pk FROM table1 LEFT OUTER JOIN table on (table2.f2=table1.pk1); ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Why table has drop, but the foreign key still there?
Here are the simple things I did
create table state (
state_code char(2) not null,
state varchar(15) not null,
primary key (state_code)
);
create table whitepage (
user_id char(8) not null,
email varchar(50),
telephone char(16) not null,
contact_namevarchar(30) not null,
cityvarchar(20),
state_code char(2),
primary key (user_id),
foreign key (state_code) references state (state_code)
);
insert into state (state_code,state) values ('GU','Guam');
drop table whitepage;
delete from state where state_code = 'GU';
ERROR: Relation "whitepage" does not exist
Why I got this error message??!!
The whitepage table already drop. When the table drop, will the
foreign key constraint also drop??
Now, order for me delete a row from state table, I HAVE TO
re-create whitepage table. That's silly!!!
What shall I do? How do I delete a row in state without
re-create the whitepage table???
Thank you very much in advance!
---(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] [ADMIN] Why table has drop, but the foreign key still there?
On Wed, 13 Aug 2003, Raymond Chui wrote:
>
> Here are the simple things I did
>
> create table state (
> state_code char(2) not null,
> state varchar(15) not null,
> primary key (state_code)
> );
>
> create table whitepage (
> user_id char(8) not null,
> email varchar(50),
> telephone char(16) not null,
> contact_namevarchar(30) not null,
> cityvarchar(20),
> state_code char(2),
> primary key (user_id),
> foreign key (state_code) references state (state_code)
> );
>
>
> insert into state (state_code,state) values ('GU','Guam');
> drop table whitepage;
> delete from state where state_code = 'GU';
> ERROR: Relation "whitepage" does not exist
What version are you using? I can't seem to replicate this given the
above on 7.2, 7.3 or 7.4.
If these tables were preexisting and had gone through a dump cycle
from 7.0 or 7.1, there was a bug in pg_dump for those versions that
would lose the connection between the triggers and the other table
of the constraint.
In any case, you'll need to find and drop the two orphaned triggers on
state (see techdocs for information on how to find them).
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] update system table?
how could I fix this problem: ERROR: unexpected chunk number 8 (expected 0) for toast value 6935693 It appear to be simple: update chunk_seq from 8 to 0. how to do it? in the stand alone postgres, could I copy a entire table? how to do it? tnx Eric
