Re: [SQL] SQL problem: bank account
On Monday 02 June 2003 00:49, listrec wrote: > How about: > > select max(transaction_id) from bank_account group by customer_id > And if you want the rest of the data in the rows: SELECT b.* FROM bank_account b JOIN ( SELECT max(transaction_id) AS transaction_id FROM bank_account GROUP BY customer_id ) AS subselect ON subselect.transaction_id=b.transaction_id ; I use a similar query here at work to find the first time a guy has visited our site through an advertiser. -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL problem: bank account
On Monday 02 June 2003 00:49, listrec wrote: > How about: > > select max(transaction_id) from bank_account group by customer_id > And if you want the rest of the data in the rows: SELECT b.* FROM bank_account b JOIN ( SELECT max(transaction_id) AS transaction_id FROM bank_account GROUP BY customer_id ) AS subselect ON subselect.transaction_id=b.transaction_id ; I use a similar query here at work to find the first time a guy has visited our site through an advertiser. -- Jonathan M. Gardner Smooth Corporation - Perl Programmer [EMAIL PROTECTED] - (425) 460-4780 Live Free, Use Linux! ---(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] Blobs with perl
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 17 June 2003 02:00, cristi wrote: > I want to insert a picture in a table from an internet browser using a > script made in perl. > Has somebody a code example with this kind a problem (I need only a code > fragment)? > Despite PostgreSQL's powerful BLOB features, I would strongly suggest against storing these kind of things in the database. It is better to have it in a local file for several reasons. 1) Apache can server up local images lightning fast 2) You can edit local images with your favorite image editor (ie, Gimp, Photoshop) 3) You can ftp, scp, sftp the image around without a problem. 4) You can tar it up and archive it. 5) You can move it off of your burdened PostgreSQL database server machine and on to its own image server when your site becomes popular. While all of the above are certainly possible with PostgreSQL, it is a bit more complicated. And remember, while your database server is small now, it will grow, and grow, and grow, and grow. It will one day become the bottleneck in your operations. That is an inevitable fact of any dynamic website. - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+7yYTWgwF3QvpWNwRAv3WAKDFrjfQUpQFmZFvVMismUeoxABoDQCfY/F7 LajclhvacQOgsn+6qnLEEwQ= =k0vW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [ADMIN] Notification
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 17 June 2003 05:13, Anagha Joshi wrote: > Hi All, > I'm new to Postgres. I'm using Postgres-7.2.4 > Can anybody guide me in detail how 'NOTIFY-LISTEN' can be implemented > progmatically bet'n frontend and backend? > Might want to move this to the SQL list. The idea is that a process will LISTEN for some notification. Another process will NOTIFY that notification, and the one listening will get a message. This is useful for a couple of things. If you have an app that shows something like customer data, then you might want to update the customer info when it changes. Here's the table: CREATE TABLE customer ( customer_id SERIAL PRIMARY KEY , first_name VARCHAR(20) NOT NULL , last_name VARCHAR(20) NOT NULL ); When displaying the customer info for customer_id 19291, you could do something like: LISTEN customer_19291; Now when someone else goes in and changes the customer info in the database, you can have a trigger set up that will NOTIFY. CREATE OR REPLACE FUNCTION notify_on_update() RETURNS TRIGGER AS ' BEGIN EXECUTE ''NOTIFY ''||TG_RELNAME||''_''||NEW.customer_id; RETURN NEW; END ' LANGUAGE 'plpgsql'; CREATE TRIGGER notify_on_update AFTER update ON customer FOR EACH ROW EXECUTE PROCEDURE notify_on_update(); Now you set up your application to watch for the notification. When it receives the notification, if can update the displayed information. Is this a good starting place? - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+73tPWgwF3QvpWNwRAqSQAKC/IsVy45bg4FAy4vwOK0PvBcfbjACg2XWT D98xYLKLXVuQPkUGTiCVHVE= =kpg2 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [ADMIN] Latest transcation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 19 June 2003 04:41, Anagha Joshi wrote: > My client C++ front end is multi-threaded. The above 'transcation' block > is in thread -y . > I want to know the info. abt' last row inserted into table in this > transcation block. > Info contains the details like time of insertion of a row and data in > that row. > > I think this sufficely explains what I want. > There are some things you can do at the application level to record what was done in a seperate thread or even process. With threads, you can use shared variables and semaphores. With processes, you can use some form of IPC or shared memory. If you decide to go at an application layer, it is beyond the scope of the ADMIN list, and better suited for a discussion with your peers in that language and environment. If you go for a server-side solution (because the application doesn't know what was last inserted and when), then you'll want to use triggers as I described in a previous posting. - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+8cOoWgwF3QvpWNwRAmGkAJ4pE8Eb9V/kiyBFqLCqr/2nNqA3HwCg0PE2 2TCK7YF50MKLwbUurS1aqlY= =hvKY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [ADMIN] Latest transcation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 20 June 2003 01:27, Anagha Joshi wrote: > Yes...I'm aware of that and have tried also by maintaining extra table. > But how to to the following: > 1.If insertion takes place, I want to return to the client the > values (with field names of course) which are inserted into the > tables. In each case table might be different. > When you insert a single row, PostgreSQL returns the OID of that row. Just follow up with a select (SELECT * FROM WHERE OID=) and you'll get all the info you need. > 2.If modification takes places, I want to know the values which > are modified with field names and return them to the client. > You can do that by checking what is different between the data you inserted and the data you get from the select statement. > More precisely, > My C++ client -- > ' > ' > " > Transcation begin > insert/update query to the backend is fired. > //control is tranferred to the trigger > //After trigger procedure is executed, I want at this > point the values inserted/modified so //as to pass them back > Transcation end > ' > ' > I don't think it can work the way you would like it. Try this algorithm. For insert: 1. Insert query is run. (Trigger, other table inserts/updates performed) 2. With the OID from the insert, select the data. For Update: 1. Update query is run. (Triggers, etc, are run as well). 2. With the OID (or Primary Key), select the data you just updated. You can turn these two procedures into a stored procedure pretty easily. This way, all you do is call a stored procedure, and it will pass back all the data you need. You get a free transaction block inside the stored procedure as well. - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+8w95WgwF3QvpWNwRAsVgAKCu48FN0VkRvXc9a2d9Qc91YU6jaQCdG4h4 kH42h4oWrsh1f1Splm0KNkA= =zOMj -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] Database design - AGAIN
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 23 June 2003 22:19, Rudi Starcevic wrote: > I"m not sure whether to have on larger table or 2 smaller one with a > join table. Theory says you are better off with the join tables. Practical reality agrees with this. It takes a bit more work to write your select statements, but the data is better for at least these two reasons: 1) You won't have a lot of empty columns. 2) You will be able to add, rename, and remove features pretty easily. There are more reasons, but they are not immediately obvious to the untrained DBA. (Words like "data integrity" and "data consistency" start to mean things when you become trained.) With that said, it sounds like you are really intermediate with database design. If you were a beginner, and if you barely understood what a table was, (and if you were the guy who would be using it all the time,) I would probably suggest you use the former method. It will work pretty well until you want to add more features or until you start worrying about your database size. At that point, I would explain how and why to use joins, and how to convert your data over to the new design. - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE++HBcWgwF3QvpWNwRAgjPAJ48syjGQahHvU4zi38WVheFbVFC5ACfQw5S 0qO67ZB2ToO4zFJKoh5GtrU= =PnFr -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] problem with temporary table.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 10 July 2003 03:03, Vijay Kumar wrote: > Hi, > We are using postgresql7.3.3, we are encountering some problems by > using temporary tables. > > Actually our requirement was, > 1. create temporary table. > 2. insert some values on that table by using some quries. > 3. select the inserted values from the temporary table. > < snip> > > The above function is working fine for the first call, from next > call onwards it is throwing the below error. Error: relation 'temp_table' > already exists. > So drop the table when you are done with it. You may also want to investigate returning a table of data, rather than a cursor or a single row. I am not too sure on how this would work exactly because I have never done it. Someone else may be willing to fill in the details. - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/FVY4WgwF3QvpWNwRAjIXAJ47L28D29zv91JGXQnA1rQ79wqRlwCeLB56 /+FhmG0tosyNXyH61po2myY= =UWtn -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] parse error for function def
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Thursday 17 July 2003 03:46, Terence Kearns wrote:
> CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS
> 'DECLARE
> BEGIN
>RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool;
> END;'
> LANGUAGE 'sql';
>
> produces this error
> ERROR: parser: parse error at or near "RETURN" at character 20
>
> I'm trying to create a function to use on a trigger to check reference
> to views since pg does not support foreign keys referencing views.
If you want to write triggers, there is great documentation on that in the
PL/pgSQL documentation.
I'll give this a shot:
CREATE FUNCTION base.fn_fkey_check(name, name, name, name) RETURNS TRIGGER AS
'
BEGIN
" $1 column1 (the table that has the foreign key column)
" $2 - table2 $3 - column2 (the table/column that is the primary key being
reference)
EXECUTE ''SELECT $3 FROM $2 WHERE $3=NEW.$1'';
IF FOUND
THEN RETURN NEW
RAISE ERROR ''Foreign key violation.'';
RETURN;
END'
LANGUAGE 'plpgsql';
- --
Jonathan Gardner <[EMAIL PROTECTED]>
(was [EMAIL PROTECTED])
Live Free, Use Linux!
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/Fxb9WgwF3QvpWNwRAh6tAJ9TxkqmKd8NrsQSwadV9FQ8PuSFIACg2sg9
6KPuw+msH/faa8F0xR+FSTI=
=Yo2a
-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] Analyze makes queries slow...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 06 August 2003 08:18, Stef wrote: > > Can anybody help, or give some links to good > help resources? > Try the performance list. Attach the create statements used to create the tables, the query you are running, and the different explain plans that were generated. If you know what it is, you may also want to attach the relevant statistics from the pg_stats table. They will be most helpful. - -- Jonathan Gardner <[EMAIL PROTECTED]> Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/M87QWgwF3QvpWNwRAgMfAJ9c6O9EyVbJYiguv/b2wtf/NsLZfACgq3lW YjpMSEMZ4gfyGaGoJcOjpSo= =4/hA -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] duplicate dates
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Friday 01 August 2003 08:56, Jodi Kanter wrote:
> I have one table that has a date/time field in it. I'd like to identify
> the records in the database where the date/time fields are the same. How
> can I do this? Do I need to create a view or temp table? Is there a way
> to run through one table multiple times.
SELECT date_trunc('month', date_column), ...
GROUP BY date_trunc('month', date_column)
HAVING count(primary_key) > 1;
Replace date_trunc('month', date_column) with whatever you want to group it
by. Note that date_part can give you interesting groupings (all the
Decembers, all the 1st of the months, all the Fridays, etc...)
If you want to run through it several times, you can have a sub-select in the
from clause. You could also save the results in a temp table.
- --
Jonathan Gardner <[EMAIL PROTECTED]>
Live Free, Use Linux!
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/M8HgWgwF3QvpWNwRAsUNAKCLnB6vajJ8fuS7IRgp0pYxp6YaxgCg2qbk
juL5a4tM1la0zmP81PdxS/c=
=N8Q/
-END PGP SIGNATURE-
---(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] 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] 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] 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] 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 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] 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] 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
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
Re: [HACKERS] [SQL] Materialized View Summary
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 24 February 2004 01:48 pm, Robert Treat wrote: > On Tue, 2004-02-24 at 12:11, Richard Huxton wrote: > > On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote: > > > I've written a summary of my findings on implementing and using > > > materialized views in PostgreSQL. I've already deployed eagerly > > > updating materialized views on several views in a production > > > environment for a company called RedWeek: http://redweek.com/. As a > > > result, some queries that were taking longer than 30 seconds to run > > > now run in a fraction of a millisecond. > > > > > > You can view my summary at > > > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.htm > > >l > > have you done much concurrency testing on your snapshot views? I > implemented a similar scheme in one of my databases but found problems > when I had concurrent "refresh attempts". I ended up serializing the > calls view LOCKing, which was ok for my needs, but I thought potentially > problematic in other cases. > I don't actually use snapshot views in production. I would imagine that if you had two seperate processes trying to update the views simultaneously, that would be a problem. All I can say is "don't do that". I think you'd want to lock the table before we go and start messing with it on that scale. We are running into some deadlock issues and some other problems with eager mvs, but they are very rare and hard to reproduce. I think we are going to start locking the row before updating it and see if that solves it. We also just discovered the "debug_deadlock" feature. I'll post my findings and summaries of the information I am getting here soon. I'm interested in whatever you've been working on WRT materialized views. What cases do you think will be problematic? Do you have ideas on how to work around them? Are there issues that I'm not addressing but should be? > > Interesting (and well written) summary. Even if not a "built in" > > feature, I'm sure that plenty of people will find this useful. Make > > sure it gets linked to from techdocs. > > Done. :-) > *blush* > > If you could identify candidate keys on a view, you could conceivably > > automate the process even more. That's got to be possible in some > > cases, but I'm not sure how difficult it is to do in all cases. > > it seems somewhere between Joe Conways work work arrays and polymorphic > functions in 7.4 this should be feasible. > I'll have to look at what he is doing in more detail. - -- Jonathan M. Gardner Web Developer, Amazon.com [EMAIL PROTECTED] - (206) 266-2906 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAO837BFeYcclU5Q0RAhonAKDBY7Svz9/vxmerS+y/h2mLgV1ZZQCdFlnd 7aMPFvRx4O8qg+sJfWkaBh8= =zdhL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Changing primary keys
On Wednesday 10 March 2004 09:17 am, David wrote: > Is it possible to change the primary key of a relation? I want to add an > attribute, that i already have in the realtion, to the primary key (yes i > realise i designed my model pretty badly) > It sure is. First, ensure that the values are indeed not NULL and unique. Next, alter the table to drop the primary key. (See ALTER TABLE). Then, alter the table to add the new primary key. If you have other tables that have a foreign key references to this table, they may have to change. I don't need to tell you to think really hard about schema changes and the impact it will have on the application before you do stuff. And be sure you are not doing this on the production database without testing it first! My personal preference would be to add the attribute, and then make a new primary key column. I really don't like multi-column primary keys as they are a bit more difficult to use. Even if the boss wants to use the old-pk + attribute as the pk, you will use the new pk as the actual pk for joins and such. You may put a not null unique constraint on the old-pk + attribute combination so that you are guaranteed that the data won't violate the boss's idea of what the pk should be. -- Jonathan Gardner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Break a report in Run Time
On Wednesday 10 March 2004 10:23 am, Jander wrote: >I have a application with a lof of reports. I need to > break a report in Run Time. How can I do this? > Could you clarify what you mean by "break a report in run time"? -- Jonathan Gardner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Break a report in Run Time
On Thursday 11 March 2004 10:08 am, Jander wrote: > I need to abort the SQL process inside my application developed in Kylix. > Go read the documentation for Kylix. I don't know the environment at all. I do know that if you are calling the query in a blocking manner then the only way to stop it is to kill the entire process, or set a timeout or something. > - Original Message - > From: "Jonathan M. Gardner" <[EMAIL PROTECTED]> > To: "Jander" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Thursday, March 11, 2004 12:22 PM > Subject: Re: [SQL] Break a report in Run Time > > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On Thursday 11 March 2004 9:11 am, Jander wrote: > > > I need to abort the SQL process running in DB Server. > > > > If you are in psql, you can cancel any query with CTRL-C. Otherwise, > > you can kill the process running the query using the "kill" command. If > > that doesn't work you can kill (but not -9!) the backend process > > handling your query. > > > > > - Original Message - > > > From: "Jonathan Gardner" <[EMAIL PROTECTED]> > > > To: "Jander" <[EMAIL PROTECTED]> > > > Cc: <[EMAIL PROTECTED]> > > > Sent: Wednesday, March 10, 2004 6:09 PM > > > Subject: Re: [SQL] Break a report in Run Time > > > > > > > On Wednesday 10 March 2004 10:23 am, Jander wrote: > > > > >I have a application with a lof of reports. I need > > > > > to break a report in Run Time. How can I do this? > > > > > > > > Could you clarify what you mean by "break a report in run time"? > > > > > > > > -- > > > > Jonathan Gardner > > > > [EMAIL PROTECTED] > > > > > > > > -------(end of > > > > broadcast)--- TIP 4: Don't 'kill -9' the > > > > postmaster > > > > - -- > > Jonathan Gardner > > [EMAIL PROTECTED] > > -BEGIN PGP SIGNATURE- > > Version: GnuPG v1.2.3 (GNU/Linux) > > > > iD8DBQFAUJIpqp6r/MVGlwwRAi8eAJ4q9UHzimQtlR3I+XdGICvfF8ZfbgCdHU6q > > 77RNJ+5WBwAwrZ1pbD+7meg= > > =BsCQ > > -END PGP SIGNATURE- -- Jonathan Gardner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
