[SQL] trigger function building
What's the proper way to include the suport files for building 'C' trigger functions in to the normal PostgreSQL install? I.E. / "make install "does not include internal/postgres_fe.h, executor/spi.h nor commands/trigger.h in to /usr/local/pgsql/include. ---(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] Multitable uniqueness ?
On Wed, May 26, 2004 at 05:13:14 +0200, Andreas <[EMAIL PROTECTED]> wrote: > > Is there a way to have something like this : UNIQUE (table_1.id, > table_2.xxx) Postgres doesn't support database constraints at this time which is what you would need to do this simply. You can enforce this constraint by creating a third table that has the ids, id type and a separate id type field for each possible type that will be null except for the type field that matches the actual type. You also need to add a type field to the original two tables. Then you set up a composite foreign key from the new table to each of the per type tables using the id and the matching id type fields. Each per type table should have a foriegn key refernce for id to the combined table. This will enforce a 1-1 relationship between the combined table and the union of the per type tables. It is probably possible to get rid of the redundant copies of the field type using triggers, but I don't know that there is much of a benefit to doing so. The redundant values will all be kept in sync with constraints, so there isn't a data consistancy problem with doing it that way. This method is going to be more portable than using triggers. This method will probably be within a constant factor as efficient as anything you do with triggers. ---(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] Multitable uniqueness ?
Bruno Wolff III wrote: On Wed, May 26, 2004 at 05:13:14 +0200, Andreas <[EMAIL PROTECTED]> wrote: Is there a way to have something like this : UNIQUE (table_1.id, table_2.xxx) Postgres doesn't support database constraints at this time which is what you would need to do this simply. You can enforce this constraint by creating a third table that has the ids, id type and a separate id type field for each possible type that will be null except for the type field that matches the actual type. You also need to add a type field to the original two tables. Then you set up a composite foreign key from the new table to each of the per type tables using the id and the matching id type fields. Each per type table should have a foriegn key refernce for id to the combined table. This will enforce a 1-1 relationship between the combined table and the union of the per type tables. I think a third table with just the unique id plus custom triggers on both tables that insert/update/delete the id into/from the third table are totally enough. Jan It is probably possible to get rid of the redundant copies of the field type using triggers, but I don't know that there is much of a benefit to doing so. The redundant values will all be kept in sync with constraints, so there isn't a data consistancy problem with doing it that way. This method is going to be more portable than using triggers. This method will probably be within a constant factor as efficient as anything you do with triggers. ---(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 -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [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] Multitable uniqueness ?
Do you really need MANY-TO-MANY between customers and projects? I can see customers owning many projects, but do you really have projects belonging to many customers? If not, fold cust_proj into projects. Otherwise, UNIQUE (cp_id, stall_no) on stalls should be enough. Andreas wrote: Hi folks, Is there a way to have something like this : UNIQUE (table_1.id, table_2.xxx) I got some tables that have a couple of foreign keys. Now I try to minimize those relationships to clean up the mess. :-} We do business fairs. (???) Like c-bit only a few magnitudes smaller. So we have projects and rent stalls to customers. customers (c_id, ...) projects (p_id,...) there is an relationcust_proj (cp_id, c_fk, p_fk, status_fk) with a UNIQUE constraint (c_fk, p_fk) A customer can have several orders, contacts, ... tied to a project. Those look like this stalls (stall_id, cp_id, stall_no, ...) o_idPRIMARY cp_fk FOREIGN KEY that ties to custmer and project stall_no is a varchar It should be unique within a project. Will I have to integrate the project.id into the stalls-table ? ---(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] trigger function building
hook <[EMAIL PROTECTED]> writes: > What's the proper way to include the suport files for building 'C' trigger > functions in to the normal PostgreSQL install? > > > I.E. / > "make install "does not include internal/postgres_fe.h, executor/spi.h > nor commands/trigger.h in to /usr/local/pgsql/include. make install-all-headers should do the trick. Regards, Manuel. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Memory usage on subselect
On 24 May 2004, at 14:37, Tom Lane wrote: Dan Field <[EMAIL PROTECTED]> writes: I have a similar problem with just one of my queries (although it isn't a sub select): You really ought to vacuum and/or analyze occasionally. The EXPLAIN results show that the planner hasn't got any non-default statistics for any of these tables. Wow, thanks for that. I'd been pulling my hair out for a couple of days wondering where I was going wrong. I went from 45 second queries down to sub second query lengths after a simple vacuum full analyze. I've now added nightly and monthly cron jobs to do this for me in future. Out of curiosity, why is this deemed a DBA task rather than an automated postgres task? Once again, many thanks. -- Dan Field <[EMAIL PROTECTED]> - Support Programmer: Cymru ar y we cy_GB: http://www.cymruarywe.org en_GB: http://www.walesontheweb.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Memory usage on subselect
Dan Field wrote: On 24 May 2004, at 14:37, Tom Lane wrote: Dan Field <[EMAIL PROTECTED]> writes: I have a similar problem with just one of my queries (although it isn't a sub select): You really ought to vacuum and/or analyze occasionally. The EXPLAIN results show that the planner hasn't got any non-default statistics for any of these tables. Wow, thanks for that. I'd been pulling my hair out for a couple of days wondering where I was going wrong. I went from 45 second queries down to sub second query lengths after a simple vacuum full analyze. I've now added nightly and monthly cron jobs to do this for me in future. Out of curiosity, why is this deemed a DBA task rather than an automated postgres task? Once again, many thanks. You have to use the pg_autovacuum demon. Run the vacuum full and the reindex once in a week. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] The PostgreSQL
Dear developers, many tricks were discussed here, and many feature requests generated. Every discussed problem ALREADY have solution in the current version of the SQL. Most of the problems are forced by incorrect design of a database ! I ask you: do not burden the PostgreSQL with features !!! Please !!! So well known Oracle is now sinking in the ocean of own features. Thnx. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
