Re: [GENERAL] Trigger on Postgres for tables syncronization
On Tue, 2004-07-27 at 09:55, Prabu Subroto wrote:
> PS>and if the view changes, does it also change the
> related record in table "appointment0" and "appointment1"?
Yes, because a view is pretty much just a stored query (Pg does't have
materialised views).
CREATE VIEW apointment0 AS
SELECT *
FROM appointment
WHERE done = 'Y';
You can then do further queries on that view in the same way as a table.
If you want to treat the view as a table for DML as well -- or to see
how views work internally -- see the "Rules" section of the Pg
documentation.
P.S. Consider using "done BOOLEAN NOT NULL DEFAULT FALSE" (the boolean
is what really matters) instead of "done CHAR(1) NOT NULL CHECK(done
IN('Y', 'N'))".. You might also consider some sort of index that
includes "done" depending on your data and usage.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] 2 different versions of postgres on the same system
On Tue, 2003-07-01 at 01:12, Madhavi Daroor wrote: > Hi all, > I have installed postgres 7.3 in my Red hat linux 7.2 system. I want to > also install postgres 7.2.3 in the same system. Is it possible?? Can > somebody please tell me how I can do this...ASAP!!! Easy, just change the PREFIX to a different location when you compile and initdb in a different dir than the first. If you plan to use TCP/IP you'll probably want to specify a different default port as well. If you don't create/user a seperate user than your main Pg(for the PGDATA, etc. env vars) just make sure to call /full/path/to/pg_ctl and specify where it's data resides with the -D flag. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Seemingly identical queries run at different speeds
On Tue, 2003-07-22 at 19:27, VanL wrote: > I have three queries that are essentially identical. Two of them run in > fractions of a second; one of them takes longer than 15 minutes to > return. (Not sure how long it totally takes, that has been the limit of > my patience.) > > The only difference between these queries is the use of table aliases in > the sql query. What is happening in postgres that this makes such a > difference? You're getting bit by a 'feature' that is supposed to make life easier. PostgreSQL automatically adds tables you reference to the FROM clause if they're not listed there already, it then sends a NOTICE saying it did so. This is supposed to be helpful, here it certainly is not. What's happening in the below is you reference mm_batch, mm_domain, etc. which are _different_ than B, D, etc. because aliasing changes what query processor refers to them as (allowing stuff like self-joins). So Pg is appending them to the FROM clause, which now looks like this: FROM mm_batch B, mm_domain D, mm_management_unit M, mm_customer C, mm_legacy_account LA, mm_target_account TA, mm_batch, mm_domain, mm_management_unit, mm_customer, mm_legacy_account, mm_target_account Yikes. It's now generating an absolutely huge cartesian product between the result set you want and all those extra tables. I believe in the upcoming 7.4.x release this won't happen unless you enable ADD_MISSING_FROM in your conf. Something I certainly won't be enabling due to this type of situation. > SLOW ( > 15 minutes): > select > mm_batch.name as batch_name, > mm_domain.name as domain_name, > mm_management_unit.name as management_unit_name, > mm_customer.firstname as customer_name, > mm_legacy_account.username as old_username, > mm_target_account.username as new_username > from > mm_batch B, > mm_domain D, > mm_management_unit M, > mm_customer C, > mm_legacy_account LA, > mm_target_account TA > where > mm_domain.bid = mm_batch.id > and mm_domain.mid = mm_management_unit.id > and mm_domain.cid = mm_customer.id > and mm_domain.lid = mm_legacy_account.id > and mm_domain.tid = mm_target_account.id > and mm_domain.name = 'example.com'; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Functional index performance question
On Tue, 2003-09-30 at 07:06, Mike Mascari wrote: [snip] > CREATE INDEX i_employees ON employees(lower(name)); > > Let's also assume that the lower() function is computationally > expensive. Now if I have a query like: > > SELECT lower(name) > FROM employees > WHERE lower(name) = 'mike' > > will PostgreSQL re-evaluate lower(name)? Is it necessary? No, it won't re-evaluate. Which is why functional indexes work and why you can only declare a functional index on a referentially transparent function (see IMMUTABLE flag in CREATE FUNCTION). See also: http://developer.postgresql.org/docs/postgres/indexes-expressional.html http://developer.postgresql.org/docs/postgres/sql-createfunction.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Functional index performance question
On Tue, 2003-09-30 at 09:54, Mike Mascari wrote: > Arguile wrote: > > > On Tue, 2003-09-30 at 07:06, Mike Mascari wrote: > > > >>CREATE INDEX i_employees ON employees(lower(name)); > >> > >>Let's also assume that the lower() function is computationally > >>expensive. Now if I have a query like: > >> > >>SELECT lower(name) > >>FROM employees > >>WHERE lower(name) = 'mike' > >> > >>will PostgreSQL re-evaluate lower(name)? Is it necessary? > > > > No, it won't re-evaluate. > > I think it will. You're correct, I misunderstood to which clause you were referring to: I thought you were wondering about the lower(name) in the where clause. Sorry for the confusion. ---(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
