Re: [GENERAL] A "cascade on delete" constraints deletes AFTER the source is gone??
It's a bit complex. I have two tables. "Books" and "Book_Authors" (which links between book_id and author_id). Book_authors has a foreign key on book_id to the Books table. On key violation it is set to delete the rows (if a book is deleted, it should't be linked to any authors). In Book_Authors I also have a trigger on DELETE. When a book is unlinked from an author, then the author vote_count should be reduced (as the author vote_count is the sum of all votes of his books). The problem is that when a book is deleted and then the trigger tried to get the number of it votes, it returns NULL, as the book is already gone and so its data. If it was a simple matter of triggers I could play with BEFORE/AFTER, but since it is constraints issue, it seems to be all happening AFTER the deletion. On Sun, 19 Dec 2004 12:23:09 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Sun, Dec 19, 2004 at 03:20:19PM +0200, Vitaly Belman wrote: > > > I noticed that when you do a constraint and tell it "cascade on > > delete", it will do so only AFTER that the source is deleted. Can I > > tell it somehow to cascade BEFORE the source is gone? > > What problem are you trying to solve? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > -- ICQ: 1912453 AIM: VitalyB1984 MSN: [EMAIL PROTECTED] Yahoo!: VitalyBe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] pgjob registered on pgFoundary
I've registered pgjob on pgFoundary. The idea for this is to periodically run SQL inside the database without the need for external utilities. Initially we'll probably have to settle for using some kind of external program to run this stuff, but eventually I'd like to see enough support in the backend so that users will only need to enable this. I've setup a mailing list for development discussions. I think the first order of business is to decide on the user API, but anyone who's interested in this should join the list and put your $0.02 in. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Scheduler in Postgres
On Sat, Dec 18, 2004 at 10:22:44PM -0500, Tom Lane wrote:
> Christopher Browne <[EMAIL PROTECTED]> writes:
> > Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Jim C. Nasby")
> > would write:
> >> In PostgreSQL, there's currently no way to assume the identity of
> >> another user.
>
> > I'm confused at that...
>
> > There seem to be ways at time of connection establishment, whether via
> > the psql "-U" option, or the PGconnect "dbuser=foo" option, or during
> > a psql session via "\c - newuser".
>
> Not to mention SET SESSION AUTHORIZATION.
Doh! Completely forgot about that.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] UNION with more restrictive DISTINCT
Oops. That statement will "prefer" the t1.name instead of t2.name. It should be COALESCE(t2.name,t1.name) Another option is: SELECT t2.id , t2.name FROM t2 UNION SELECT t1.id , t1.name FROM t2 WHERE NOT EXISTS (SELECT NULL FROM t1 JOIN t2 ON t1.id = t2.id ) - Original Message - From: "Vincent Hikida" <[EMAIL PROTECTED]> To: "peter pilsl" <[EMAIL PROTECTED]>; "PostgreSQL List" <[EMAIL PROTECTED]> Sent: Saturday, December 18, 2004 12:40 AM Subject: Re: [GENERAL] UNION with more restrictive DISTINCT One solution is SELECT COALESCE(t1.id,t2.id) , COALESCE(t1.name,t2.name) FROM t1 FULL JOIN t2 ON t1.id = t2.id - Original Message - From: "peter pilsl" <[EMAIL PROTECTED]> To: "PostgreSQL List" <[EMAIL PROTECTED]> Sent: Wednesday, December 15, 2004 1:03 PM Subject: [GENERAL] UNION with more restrictive DISTINCT I'd like to UNION two queries but the distinct-criteria for UNION should not be all columns in the queries, but only one. example. two tables: test=# select id,name from t1; id | name +-- 1 | bob 2 | mike (2 rows) test=# select id,name from t2; id | name +- 1 | bob 2 | mike j. (2 rows) # select id,name from t1 union select id,name from t2; id | name +- 1 | bob 2 | mike 2 | mike j. (3 rows) now I want a construct that returns me only one row for each id. If there are different names for that id's in the different tables, the name of t2 should be chosen. like: # select id,name from t1 UNION ON (id) AND SUPERMAGICPREFER (t2.name) select id,name from t2; id | name +- 1 | bob 2 | mike j. (2 rows) What is an appropriate approach to this? If I use my UNION-query as subquery for a SELECT DISTINCT ID, I loose the name, which is important. thnx. peter -- mag. peter pilsl goldfisch.at IT-management tel +43 699 1 3574035 fax +43 699 4 3574035 [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 ---(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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Multi row sequence?
On Sun, 2004-12-19 at 22:43 +0200, Ciprian Popovici wrote: > On Sun, 19 Dec 2004 13:51:39 + Ragnar Hafstað <[EMAIL PROTECTED]> wrote: > > On Sun, 2004-12-19 at 15:02 +0200, Ciprian Popovici wrote: > > > On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III <[EMAIL PROTECTED]> > wrote: > > > > On Fri, Dec 17, 2004 at 11:10:12 -, > > > > Filip Wuytack <[EMAIL PROTECTED]> wrote: [question about mysql's special AUTO_INCREMENT on a secondary column in a multiple-column index] > > > > > > Here's a case where what he said would come in handy: arranging a > > > particular display order within the individual groups. You have the > unique > > > key for the entire table, but you need something like a serial > restricted > > > to just a group of rows. > > > > would a normal sequence not do if that was the only purpose? > > Not if you need the main key values to stay put. I am afraid I do not follow you. gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Multi row sequence?
On Sun, 19 Dec 2004 13:51:39 + Ragnar Hafstaà <[EMAIL PROTECTED]> wrote: > On Sun, 2004-12-19 at 15:02 +0200, Ciprian Popovici wrote: > > On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III <[EMAIL PROTECTED]> wrote: > > > On Fri, Dec 17, 2004 at 11:10:12 -, > > > Filip Wuytack <[EMAIL PROTECTED]> wrote: > > > > Is it possible to have a sequence (as a multirow prim key), where > > > > sequence (id) only increase per group of data (grp). > > > > > > Why do you want to do this? It would be a lot simpler to generate unique > > > values over the table and that will work just fine if all you need > > > is uniqueness. > > > > Here's a case where what he said would come in handy: arranging a > > particular display order within the individual groups. You have the unique > > key for the entire table, but you need something like a serial restricted > > to just a group of rows. > > would a normal sequence not do if that was the only purpose? Not if you need the main key values to stay put. -- Ciprian Popovici ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] A "cascade on delete" constraints deletes AFTER the source is gone??
On Sun, Dec 19, 2004 at 03:20:19PM +0200, Vitaly Belman wrote: > I noticed that when you do a constraint and tell it "cascade on > delete", it will do so only AFTER that the source is deleted. Can I > tell it somehow to cascade BEFORE the source is gone? What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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: [GENERAL] Multi row sequence?
On Sun, Dec 19, 2004 at 15:02:27 +0200, Ciprian Popovici <[EMAIL PROTECTED]> wrote: > On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III <[EMAIL PROTECTED]> wrote: > > On Fri, Dec 17, 2004 at 11:10:12 -, > > Filip Wuytack <[EMAIL PROTECTED]> wrote: > > > Is it possible to have a sequence (as a multirow prim key), where > > > sequence (id) only increase per group of data (grp). > > > > Why do you want to do this? It would be a lot simpler to generate unique > > values over the table and that will work just fine if all you need > > is uniqueness. > > Here's a case where what he said would come in handy: arranging a > particular display order within the individual groups. You have the unique > key for the entire table, but you need something like a serial restricted > to just a group of rows. You wouldn't want to use a serial type for that. Inserts would become a big pain. You can use numeric or text so that it is easy to do inserts. ---(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: [GENERAL] Scheduler in Postgres
On Sat, Dec 18, 2004 at 15:00:17 -0600, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > > In oracle, jobs are fired off by the database engine. If the database is > up and job support is enabled, your jobs will run. Without some kind of > support for the database to fire up connections and execute some kind of > code we would have to rely on some external means to do so. This is less > robust, isn't cross-platform, and requires more work of the end-users. In my experience, cron is never down, but databases are taken down on a regular basis. I don't think the reliability of cron is a problem. > The other feature is that the connection running jobs in oracle has the > ability to re-create the connection environment used to submit the job. > This means jobs are run as the same user who submitted the job, and > certain session settings are also duplicated. In PostgreSQL, there's > currently no way to assume the identity of another user. cron also runs jobs as the user that created them. There is an issue when you want to let people who don't have shell access to the database server run scheduled jobs. I am not sure how the identity issue applies here. If you are running a cron script you can connect as whoever you are authorized to and can open a separate session for each job. > Even with lack of support for these two features, I still think it would > be very usefull to create a generic job system, probably as a pgFoundary > project. Enough people have asked about it that I'm sure there's plenty > of re-invented code out there. If we have a solid framework that people > are using, we'll have a much stronger case for getting the two features > I mentioned added to the back-end. That seems like the way to start. If people think they need something better than cron, you should get some help. There is a lot you could do with logging and job dependencies that are not built in to cron. ---(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: [GENERAL] PG8 final when
--- Együd Csaba <[EMAIL PROTECTED]> escreveu: > Dear Developers, > when do you plan to announce the final version of 8.0.0? As far as I can > remember it was promised by 15. dec. Could you please point to where was it promised? Regards, Clodoaldo Pinto ___ Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis ---(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: [GENERAL] Multi row sequence?
On Sun, 2004-12-19 at 15:02 +0200, Ciprian Popovici wrote: > On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III <[EMAIL PROTECTED]> wrote: > > On Fri, Dec 17, 2004 at 11:10:12 -, > > Filip Wuytack <[EMAIL PROTECTED]> wrote: > > > Is it possible to have a sequence (as a multirow prim key), where > > > sequence (id) only increase per group of data (grp). > > > > Why do you want to do this? It would be a lot simpler to generate unique > > values over the table and that will work just fine if all you need > > is uniqueness. > > Here's a case where what he said would come in handy: arranging a > particular display order within the individual groups. You have the unique > key for the entire table, but you need something like a serial restricted > to just a group of rows. would a normal sequence not do if that was the only purpose? gnari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PG8 final when
Együd Csaba wrote: > Dear Developers, > when do you plan to announce the final version of 8.0.0? As far as I can > remember it was promised by 15. dec. > No hurrying, just would like to know. I should install it on the site and > thougt I wait for the final version. My guess is 2-3 weeks from now. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] A "cascade on delete" constraints deletes AFTER the source is gone??
I noticed that when you do a constraint and tell it "cascade on delete", it will do so only AFTER that the source is deleted. Can I tell it somehow to cascade BEFORE the source is gone? -- ICQ: 1912453 AIM: VitalyB1984 MSN: [EMAIL PROTECTED] Yahoo!: VitalyBe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Multi row sequence?
On Sat, 18 Dec 2004 11:07:37 -0600 Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Fri, Dec 17, 2004 at 11:10:12 -, > Filip Wuytack <[EMAIL PROTECTED]> wrote: > > Is it possible to have a sequence (as a multirow prim key), where > > sequence (id) only increase per group of data (grp). > > Why do you want to do this? It would be a lot simpler to generate unique > values over the table and that will work just fine if all you need > is uniqueness. Here's a case where what he said would come in handy: arranging a particular display order within the individual groups. You have the unique key for the entire table, but you need something like a serial restricted to just a group of rows. -- Ciprian Popovici ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PG8 final when
Dear Developers, when do you plan to announce the final version of 8.0.0? As far as I can remember it was promised by 15. dec. No hurrying, just would like to know. I should install it on the site and thougt I wait for the final version. many thaks, -- Csaba --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.818 / Virus Database: 556 - Release Date: 2004.12.17. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
