[SQL] foreign key, on delete cascade...
Hi
I found in an old pgsql dump something like :
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'TABLE NAME';
inserts
BEGIN TRANSACTION;
CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C,
"pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* 'TABLE NAME'
GROUP BY 1;
UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP
WHERE "pg_class"."relname" = TMP."tmp_relname";
DROP TABLE "tr";
COMMIT TRANSACTION;
Which was supposed to disable triggers on this table during the inserts. It
was on 7.0.x I believe, It still work with 7.2.3, but I was wondering if it
was the right way to do.
--
Mathieu Arnold
---(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] Problems Formulating a SELECT
Richard, Thanks again. > My bad, I didn't test it - for the last one you'll want > > SELECT contig_x_vw.contig_id FROM contig_x_vw ... > > It doesn't matter which id you use (since you want the value in each) but you > will need to tell PG which one you want. No problem, after I sent the email I caught the source of the ' "contig_id" is ambiguous' ERROR. > > It's checking the "contig_id"s are the same (from clone_contig) - A contig_id match is what I wanted, but also a requirement that clone.project,plate,col & row also match for cloneA and cloneB. I added what I think gives me the correct match params (see below) and a quick survey of the results looks to be ok. Is there a method to remove duplicate results? For instance the query below in part yields : chlamy_est-> ; contig_id --- 27170 27173 27173 27179 27179 27179 27179 27179 The repetitive occurrence of the same contig_id (27179) occurs as a result of multiple pairs of clones matching the criteria for a given contig_id. So for contig_id 27179 there are 5 pairs which match: cloneA cloneBcontig_id 894018D05.x1<-> 894018D05.y127179 894080H12.x1<-> 894080H12.y127179 894094E04.x2<-> 894094E04.y227179 894095H03.x1<-> 894095H03.y227179 963037B05.x2<-> 963037B05.y127179 CREATE VIEW contig_x_vw AS SELECT contig_id,project,plate,row,col FROM clone_contig, clone WHERE clone_contig.clone_id = clone.clone_id AND read='x'; CREATE VIEW contig_y_vw AS SELECT contig_id,project,plate,row,col FROM clone_contig, clone WHERE clone_contig.clone_id = clone.clone_id AND read='y'; SELECT contig_x_vw.contig_id FROM contig_x_vw, contig_y_vw WHERE contig_x_vw.project = contig_y_vw.project AND contig_x_vw.plate = contig_y_vw.plate AND contig_x_vw.col = contig_y_vw.col AND contig_x_vw.row = contig_y_vw.row AND contig_x_vw.contig_id = contig_y_vw.contig_id ; > > cloneA.project=cloneB.project > > cloneA.plate=cloneB.plate > > cloneA.row=cloneB.row > > cloneA.col=cloneB.col > > > > > > TABLE clone 'A''B' > > > > clone_id12018 12019 > > project 894 894 > > plate 27 27 > > row G G > > col 9 9 > > readx y > > Does that get you any further? If not, I'm back at my desk tomorrow 9am GMT > > - Richard Huxton > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Temporary tables and indexes
Hi : Are the indices of a temporary table automatically "dropped" together its corresponding temporary table after a database session? ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problems Formulating a SELECT
On Monday 07 Oct 2002 8:24 pm, Charles Hauser wrote: > I am trying to formulate a SELECT and could use some suggestions. > > From the TABLES below I would like to find ALL contigs which contain the > same clones except that one (or more) has read='y' and the other(s) have > read='x'. > Or stated another way: find all contigs composed of (at least) both (x > and y) reads from the same clone. > > For example: > > In the data below, the contig '20020630.488.1'(contig_id:13805) is > composed of 2 clones (clone_id='12018' and '12019') which are > 894027G09.x and 894027G09.y, respectively. > > TABLE clone'A''B' > > clone_id 12018 12019 > project 894 894 > plate 27 27 > row G G > col 9 9 > read x y > > Table clone_contig: > > clone_idcontig_id > 12018 13805 > 12019 13805 How about something like: CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_config, clone WHERE clone_config.clone_id = clone.clone_id AND read='x'; CREATE VIEW contig_y_vw AS [same but for y] SELECT config_id FROM contig_x_vw, contig_y_vw WHERE contig_x_vw.clone_id = contig_y_vw.clone.id; You don't need the views, but they make the example easier. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Probs "compiling" a function
On Tuesday 08 Oct 2002 3:14 am, [EMAIL PROTECTED] wrote: > Greetings, Having probs just "compiling" this fn and it comes back with a > very stark error msg which is below. I'm using version 7.1.3 > > I run the script as: > \i procedures/zff > > and get the error msg: > psql:procedures/zff:51: ERROR: parser: parse error at or near "b" > > Line 51 is the line that starts LANGUAGE ... > VALUES(upper($1),$2,upper($3),upper($4),$5,$6,$7,$8,$9,upper($10),$11, > $12,$13,$14,/'B/',t_class_sun,$16); I think the 'B' here might be the problem - did you mean \'B\' or ''B''? It probably doesn't spot it until the end of the definition when it hits another quote on the "language" line. - Richard Huxton ---(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] foreign key, on delete cascade...
On Tue, 8 Oct 2002, Mathieu Arnold wrote:
> I found in an old pgsql dump something like :
>
> UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'TABLE NAME';
>
> inserts
>
> BEGIN TRANSACTION;
> CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
> INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C,
> "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* 'TABLE NAME'
> GROUP BY 1;
> UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP
> WHERE "pg_class"."relname" = TMP."tmp_relname";
> DROP TABLE "tr";
> COMMIT TRANSACTION;
>
> Which was supposed to disable triggers on this table during the inserts. It
> was on 7.0.x I believe, It still work with 7.2.3, but I was wondering if it
> was the right way to do.
For right now, I believe so as long as you don't want the inserted rows
to be checked at all.
---(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] Problems Formulating a SELECT
Richard, Thanks, a followup. I believe this will not work (novice, so take w/ grain of salt). I tried the following: chlamy_est=> CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_contig, clone WHERE chlamy_est-> clone_contig.clone_id = clone.clone_id AND read='x'; CREATE chlamy_est=> CREATE VIEW contig_y_vw AS SELECT contig_id FROM clone_contig, clone WHERE chlamy_est-> clone_contig.clone_id = clone.clone_id AND read='y'; CREATE chlamy_est=> SELECT contig_id FROM contig_x_vw, contig_y_vw WHERE contig_x_vw.clone_id = chlamy_est-> contig_y_vw.clone.id; ERROR: Column reference "contig_id" is ambiguous If I understand the logic, you SELECT: all contig_ids where where clone.read = 'x' (VIEW contig_x_vw) all contig_ids where where clone.read = 'y' (VIEW contig_y_vw) find isect of these two wehere their clone_ids are same However, their clone_ids will never be the same as in the example. cloneA.project=cloneB.project cloneA.plate=cloneB.plate cloneA.row=cloneB.row cloneA.col=cloneB.col TABLE clone 'A''B' clone_id12018 12019 project 894 894 plate 27 27 row G G col 9 9 readx y ?? Charles > > > > In the data below, the contig '20020630.488.1'(contig_id:13805) is > > composed of 2 clones (clone_id='12018' and '12019') which are > > 894027G09.x and 894027G09.y, respectively. > > > > TABLE clone 'A''B' > > > > clone_id12018 12019 > > project 894 894 > > plate 27 27 > > row G G > > col 9 9 > > readx y > > > > Table clone_contig: > > > > clone_idcontig_id > > 12018 13805 > > 12019 13805 > > How about something like: > > CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_config, clone WHERE > clone_config.clone_id = clone.clone_id AND read='x'; > CREATE VIEW contig_y_vw AS [same but for y] > > SELECT config_id FROM contig_x_vw, contig_y_vw WHERE contig_x_vw.clone_id = > contig_y_vw.clone.id; > > You don't need the views, but they make the example easier. > > - Richard Huxton > > ---(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] Temporary tables and indexes
Ludwig, > Are the indices of a temporary table automatically > "dropped" together its corresponding temporary table > after a database session? I'm kind of surprised that it's possible to index a temporary table. There's not much point in doing so. Yes, the indexes would be dropped as well. -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [NOVICE] update question
David, I'm sorry, you just don't seem to be at the "self-help" stage. I strongly reccommend that you hire a database consultant to help you. -Josh Berkus DAVID KUCHARSKI <[EMAIL PROTECTED]> wrote: > the result set of the view is 3628 lines. It would be > nice if postgres could do this update instead of our > data entry person. > > > Josh Berkus wrote: > > > > David, > > > > > > > > 1) Is there any way you can easily identify the relevant > rows in the > > > > legacy > > > > > > database and export only those? > > > > > > I guess I'm unclear as to which table you are > > > referring to as the LEGACY table. either way i > > > imagine that by looking at a table or VIEW of the pik > > > numbers common to both tables is what i need, so yes I > > > can get that. in fact i have now. but how can i use > > > that table to update the price fields in inventory > > > > No, I'm talking about the database system that is exporting to > text. Can you > > tell it to export only the rows you need? > > > > -- > > -Josh Berkus > > Aglio Database Solutions > > San Francisco > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How slow is distinct - 2nd
Bruno Wolff III schrieb: > > On Tue, Oct 01, 2002 at 14:18:50 +0200, > Michael Contzen <[EMAIL PROTECTED]> wrote: > > Here the table: > > > > mc=# \d egal > > Table "public.egal" > > Column | Type | Modifiers > > +-+--- > > i | integer | > > > > mc=# select count(*) from egal; > > count > > - > > 7227744 > > (1 row) > > > > mc=# select count(distinct i) from egal; > > count > > --- > > 67 > > (1 row) > > This suggests that the best way to do this is with a hash instead of a sort. > > If you have lots of memory you might try increasing the sort memory size. Hello, ok, sort_mem was still set to the default (=1024). I've increased it to sort_mem=10240 which results to: (same machine, same data, etc.) time echo "select distinct i from egal;"|psql mc >/dev/null real2m30.667s user0m0.000s sys 0m0.010s If I set sort_mem=1024000: time echo "select distinct i from egal;"|psql mc >/dev/null real0m52.274s user0m0.020s sys 0m0.000s wow, in comparison to nearly 5 minutes before this is quite good speedup. But: All the work could be done in memory as the processor load shows (output of top, which shows the following output during all the time) PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 8310 postgres 17 0 528M 528M 2712 R99.9 13.5 0:11 postmaster Even it nearly performs 5 times faster than before with 1M memory, postgres is still 8 times slower than oracle. Further increasing of sort_mem to 4096000 doesn't reduce the time, as the cpu load cannot increased any more :-) But increasing the memory in that way is not realy a solution: Normaly not all the data fits into memory. In our application I guess 10%. Oracle has even less memory: PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 8466 oracle14 0 11052 10M 10440 R99.9 0.2 0:01 oracle (this 10M session memory plus 32M shared memory pool not shown here). This shows to me, that oracle uses a quite different algorithm for this task. May be it uses some hashing-like algorithm first without sorting before. I don't know oracle enough, perhaps this is that "sort unique" step in the planners output. I think, first Postgres sorts all the data, which results to temporary data of the same size than before and which needs to be written to disk at least once, and after that postgres does the unique operation, right? If I can do any more tests to oracle or postgres, let me know. Kind regards, Michael Contzen ---(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] Temporary tables and indexes
Sure there is! There are queries that benefit from having a temporary table created for a subquery and the temporary table indexed before the join. Since we can't easily return result sets from functions yet, it's not probably used that much, but from within a function, I can see why you might want to do this from time to time. Ian A. Harding Programmer/Analyst II Tacoma-Pierce County Health Department (253) 798-3549 [EMAIL PROTECTED] WWSD - What Would Scooby Doo? >>> "Josh Berkus" <[EMAIL PROTECTED]> 10/08/02 08:36AM >>> Ludwig, > Are the indices of a temporary table automatically > "dropped" together its corresponding temporary table > after a database session? I'm kind of surprised that it's possible to index a temporary table. There's not much point in doing so. Yes, the indexes would be dropped as well. -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problems Formulating a SELECT
On Tuesday 08 Oct 2002 4:04 pm, Charles Hauser wrote: > Richard, > > Thanks, a followup. > > I believe this will not work (novice, so take w/ grain of salt). > > I tried the following: > > chlamy_est=> CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_contig, > clone WHERE chlamy_est-> clone_contig.clone_id = clone.clone_id AND > read='x'; > CREATE > chlamy_est=> CREATE VIEW contig_y_vw AS SELECT contig_id FROM clone_contig, > clone WHERE chlamy_est-> clone_contig.clone_id = clone.clone_id AND > read='y'; > CREATE > chlamy_est=> SELECT contig_id FROM contig_x_vw, contig_y_vw WHERE > contig_x_vw.clone_id = chlamy_est-> contig_y_vw.clone.id; > ERROR: Column reference "contig_id" is ambiguous My bad, I didn't test it - for the last one you'll want SELECT contig_x_vw.contig_id FROM contig_x_vw ... It doesn't matter which id you use (since you want the value in each) but you will need to tell PG which one you want. > If I understand the logic, you SELECT: > all contig_ids where where clone.read = 'x' (VIEW contig_x_vw) > all contig_ids where where clone.read = 'y' (VIEW contig_y_vw) > find isect of these two wehere their clone_ids are same > > > However, their clone_ids will never be the same as in the example. It's checking the "contig_id"s are the same (from clone_contig) - if that isn't what's wanted you can check the columns below match. Otherwise, you're quite right, that's what it's doing. > cloneA.project=cloneB.project > cloneA.plate=cloneB.plate > cloneA.row=cloneB.row > cloneA.col=cloneB.col > > > TABLE clone 'A''B' > > clone_id12018 12019 > project 894 894 > plate 27 27 > row G G > col 9 9 > readx y Does that get you any further? If not, I'm back at my desk tomorrow 9am GMT - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Table Rule
Hi,
I have a Postgresql Rule question I'd love some help with thanks.
I have a table, sysmessages, I'd like to keep a journal of.
So I create a rule that says on insert or update to this table do insert
or update
into my sysmessges_log table.
My problem is this:
sysmessages table has it's own primary key.
When inserting into the sysmessages_log table the key inserted from the
sysmessages
table is incremented. I don't want the incremented id but the same id in
the sysmessages table.
Here's and example:
sysmessages row:
iduser_iddate prioritymessagestatus
1933952002-10-103test messageA
What's inserted in the the sysmessages table is
log_ididuser_iddate prioritymessagestatus
12933952002-10-103test messageA
See how the id field is 2 in the sysmessages table not 1 like in
sysmessages.
I want the id field to be 1 not 2.
I've include the sql below. It's all nice and tidy so if your keen you can
insert it and test out my rule.
Thanks very much for your time.
sql:
-- DROP RULE sysmessages_insert_rule;
-- DROP RULE sysmessages_update_rule;
-- DROP SEQUENCE sysmessages_id_seq;
-- DROP TABLE sysmessages;
-- DROP SEQUENCE sysmessages_log_log_id_seq;
-- DROP TABLE sysmessages_log;
create table sysmessages
(
id serial PRIMARY KEY,
user_id integer NOT NULL, -- ref. integrity removed for this example
sql code.
message_date date DEFAULT now() NOT NULL,
message_priority char(1) CHECK( message_priority IN
(1,2,3,4,5,6,7,8,9) ) NOT NULL,
message text NOT NULL,
status char(1) CHECK( status IN ('A','P','N') ) NOT NULL
);
create table sysmessages_log
(
log_id serial PRIMARY KEY,
id integer NOT NULL, -- no ref. integrity. we keep all records
user_id integer NOT NULL, -- no need to use ref. integrity. allow
ex-users to be in this table.
message_date date DEFAULT now() NOT NULL,
message_priority char(1) CHECK( message_priority IN
(1,2,3,4,5,6,7,8,9) ) NOT NULL,
message text NOT NULL,
status char(1) CHECK( status IN ('A','P','N') ) NOT NULL
);
CREATE RULEsysmessages_insert_rule AS
ON INSERT TOsysmessages
DO INSERT INTOsysmessages_log
(id,user_id,message_date,message_priority,message,status) VALUES
(new.id,new.user_id,new.message_date,new.message_priority,new.message,new.status);
CREATE RULEsysmessages_update_rule AS
ON UPDATE TOsysmessages
DO INSERT INTOsysmessages_log
(id,user_id,message_date,message_priority,message,status) VALUES
(new.id,new.user_id,new.message_date,new.message_priority,new.message,new.status);
---(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] IN, EXISTS or ANY?
Josh Berkus wrote: >>But EXISTS is an entirely different animal which is often faster >>... isn't that in the FAQ? There is no reference to EXISTS in the SELECT documentation?? Is this explained somewhere else? -- Keith Gray Technical Services Manager Heart Consulting Services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] IN, EXISTS or ANY?
Keith, > >>But EXISTS is an entirely different animal which is often faster > >>... isn't that in the FAQ? > > > There is no reference to EXISTS in the SELECT documentation?? > > Is this explained somewhere else? Hmmm found this using the Index for the online docs: http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/functions-subquery.html Have fun. -Josh ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Temporary tables and indexes
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I'm kind of surprised that it's possible to index a temporary table. > There's not much point in doing so. Why not? You seem to be equating "temporary" with "small", but I don't see why that must be so. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Temporary tables and indexes
Tom, > > I'm kind of surprised that it's possible to index a temporary > table. > > There's not much point in doing so. > > Why not? You seem to be equating "temporary" with "small", but I > don't > see why that must be so. Nah. I'm equating "temporary" with "query twice and throw away", which isn't necessarily what everyone else does. BTW, Ludwig, in postgresql versions < 7.2.0, I noticed that sometimes indexes didn't seem to completely go away (i.e. I would get a name conflict if I tried to drop & re-create in the same transaction) without a VACUUM. If you're using 7.1.3, you should upgrade for this reason. -Josh Berkus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
