[SQL] Update instead rules on Views
Hello, It seems that I have a fundamental misunderstanding how views work. See the following example: --- snip snap --- CREATE TABLE realdata (index int, data varchar, obsolete int); COPY realdata FROM STDIN; 1 hallo \N 2 test\N 3 blubb \N \. -- The obsolete row is a row that is now to be calculated on the -- fly. In our real data, this is to be a nested select, but random() -- works for this example as well. CREATE VIEW testview AS SELECT index, data, (random()*99)::int from realdata; -- But to remain compatibility with old apps, we also need to manage -- updates to the view, which are to be rewritten as follows: CREATE RULE testview_update_rule AS ON UPDATE TO testview DO INSTEAD UPDATE realdata SET index = NEW.index, data = NEW.data, obsolete=NULL ; --- snip snap --- But now, when we issue an UPDATE testview SET data='nono' WHERE index=1; we get the result UPDATE 3 So it updated _all_ of the rows instead of the qualified rows (WHERE index=1). SELECT * FROM realdata; index | data | obsolete ---+--+-- 1 | nono | 1 | nono | 1 | nono | But the documentation states: (rules-update.html): | No qualification but INSTEAD | | the query tree from the rule action with the original query | tree's qualification added I read this that the original qualification (WHERE index=1) is applied to the rule, resulting in a transformed query equivalent to: UPDATE realdata SET data='nono' WHERE index=1; which works as expected. Can anyone enlighten me? Thanks, Markus PS: My server version is PostgreSQL 7.4.5 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-9) -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 'show databases' in psql way?
\z will list the tables in the dbase \d tablename will list the columns in the table - Erik Wasser wrote: Hi list, how can I list the databases in a postgresish way? I know about the '-l' switch of 'psql' but is there a DBI/SQL-query way? I don't want to call an external program only to list the databases. I've googled about this problem but I only found the '-l'-way to this this. Ideas? Solutions? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] 'show databases' in psql way?
O lorid έγραψε στις Nov 2, 2004 : > \z will list the tables in the dbase > \d tablename will list the columns in the table > or foodb=# SELECT oid,* from pg_database ; > - > Erik Wasser wrote: > > >Hi list, > > > >how can I list the databases in a postgresish way? I know about the '-l' > >switch of 'psql' but is there a DBI/SQL-query way? I don't want to call > >an external program only to list the databases. I've googled about this > >problem but I only found the '-l'-way to this this. > > > >Ideas? Solutions? > > > > > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Update instead rules on Views
Helo, On Tue, 2 Nov 2004 13:05:07 +0100 Markus Schaber <[EMAIL PROTECTED]> wrote: > -- But to remain compatibility with old apps, we also need to manage > -- updates to the view, which are to be rewritten as follows: > CREATE RULE testview_update_rule > AS ON UPDATE TO testview DO INSTEAD > UPDATE realdata SET > index = NEW.index, > data = NEW.data, > obsolete=NULL > ; I now got it to work with adding a "WHERE index=NEW.index" to the view. Am I correct in my assumption that this means that this only works when I have a primary key (or at least unique) row in my dataset? Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 'show databases' in psql way?
in command line: psql template1 pgsql -l in PSQL: foodb=#> select datname from pg_database; - Arash >>> Achilleus Mantzios <[EMAIL PROTECTED]> 11/2/2004 10:21:06 AM >>> O lorid ** Nov 2, 2004 : > \z will list the tables in the dbase > \d tablename will list the columns in the table > or foodb=# SELECT oid,* from pg_database ; > - > Erik Wasser wrote: > > >Hi list, > > > >how can I list the databases in a postgresish way? I know about the '-l' > >switch of 'psql' but is there a DBI/SQL-query way? I don't want to call > >an external program only to list the databases. I've googled about this > >problem but I only found the '-l'-way to this this. > > > >Ideas? Solutions? > > > > > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Update instead rules on Views
Hello, On Tue, 2 Nov 2004 16:20:37 +0100 Markus Schaber <[EMAIL PROTECTED]> wrote: > > -- But to remain compatibility with old apps, we also need to manage > > -- updates to the view, which are to be rewritten as follows: > > CREATE RULE testview_update_rule > > AS ON UPDATE TO testview DO INSTEAD > > UPDATE realdata SET > > index = NEW.index, > > data = NEW.data, > > obsolete=NULL > > ; > > I now got it to work with adding a "WHERE index=NEW.index" to the view. This seems only to work when I update on the INDEX row. I now modified the rule to look like: CREATE RULE testview_update_rule AS ON UPDATE TO testview DO INSTEAD UPDATE realdata SET index = NEW.index, data = NEW.data, obsolete=NULL WHERE index = OLD.index; This seems to work now for arbitrary columns, provided that index is an unique row. When I have a table that looks like index | data | obsolete ---+---+-- 2 | test | 3 | blubb | 1 | nono | 3 | hallo | and I issue viewtest=# update testview set data='blob' where data='hallo'; I get: UPDATE 2 and it really updated 2 rows. As far as I understand now, I need a primary key in the underyling table as the qualification from the original statemet is applied to the view results, and not propagated to the underlying table. Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] JOIN not being calculated correctly
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Posting the EXPLAIN is a good, an EXPLAIN ANALYZE would be better
(assuming your dataset is small enough for it to complete in this
lifetime). You also need to include the following information:
1) The schema involved, including information about indexes being used.
2) Have you vacuumed / analyzed the tables involved recently?
3) Have you modified the stats on any of the tables / columns involve or
are you using defaults?
Drew
Scott Pederick wrote:
| Hi all!
|
| I'm having a problem with Postgresql 8.0.0-beta4 on a Win2K machine and a
| particular join.
|
| I've got two tables - a list of customers and jobs they've had. A customer
| can have multiple jobs.
|
| The query always scans the entire jobs table for each customer - I need it
| the other way around so I can get a list of the customers who have at
least
| one job.
|
| The EXPLAIN shows the jobs table is being scanned for some reason:
|
| Quarry=# EXPLAIN SELECT Customers.CustomerId, Jobs.JobId FROM Customers
| INNER JOIN Jobs USING (CustomerId);
|QUERY PLAN
| -
| Hash Join (cost=78.54..4908.71 rows=70727 width=8)
|Hash Cond: ("outer".customerid = "inner".customerid)
|-> Seq Scan on jobs (cost=0.00..3769.27 rows=70727 width=8)
|-> Hash (cost=76.03..76.03 rows=1003 width=4)
| -> Seq Scan on customers (cost=0.00..76.03 rows=1003 width=4)
| (5 rows)
|
|
|
| Even if I reverse the JOIN I get the exact same result:
|
| Quarry=# EXPLAIN SELECT Customers.CustomerId, Jobs.JobId FROM Jobs INNER
| JOIN Customers USING (CustomerId);
|QUERY PLAN
| -
| Hash Join (cost=78.54..4908.71 rows=70727 width=8)
|Hash Cond: ("outer".customerid = "inner".customerid)
|-> Seq Scan on jobs (cost=0.00..3769.27 rows=70727 width=8)
|-> Hash (cost=76.03..76.03 rows=1003 width=4)
| -> Seq Scan on customers (cost=0.00..76.03 rows=1003 width=4)
| (5 rows)
|
|
| How can I force it to operate as I need it to? It seems the query
engine is
| a little smarter than it needs to be.
|
| If anyone can shed some light on this problem, it would be greatly
| appreciated. I've taken it as far as I can and don't really know where to
| move from here.
- --
Andrew Hammond416-673-4138[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (GNU/Linux)
iD8DBQFBh9YQgfzn5SevSpoRAg0LAKCg5K7IccFIOvdTc8DEl2YaUMcUCgCfRt2Q
CI1Vo6yxHkrWcoTQMQ/EvOw=
=m15B
-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
[SQL] vacuum analyze slows sql query
Greetings pgsql-sql,
I have a very strange problem. Our production database is a fair
sized db, structure wise, and quite huge data wise. We have a web/php
based UI for our customer to manage the data in our application db.
The customer complains that the UI is sluggish accessing certain
pages and completely times-out on certain other pages.
We have a nightly "garbage collection" process that runs and purges
any old data. After this process a 'vacuum analyze' is kicked off
(regardless of whether or not any data was actually purged).
At this point I should mention that our customer sites are running
PostgreSQL 7.1.3; however, I am able to reproduce the issue on 7.4.2.
If it at all matters, customer is running the db on a Dell PowerEdge
2550 equiped with 1gig of ram. My personal test box is a 700Mhz Intel
with 512mb ram.
I have in the past made modifications to our SQL statements to make
queries more efficient. At this point I have given up and set out
to strip down our database and data enough to be able to post to
the list and ask for help from more qualified SQL experts.
In the process of "stripping down" our database I noticed some very
strange behavior which I could not explain. I started to reformulate
my original to-be post to to the list to ask assistence in explaining
this strange behavior I was observing. Next I noticed yet another
strange issue with PostgreSQL.
I noticed that a freshly created db with freshly inserted data (from
a previous pg_dump) would result in quite fast results. However,
after running 'vacuum analyze' the very same query slowed down about
1250x (Time: 1080688.921 ms vs Time: 864.522 ms).
Following is a paste from a psql shell after a dropdb, createdb
and populate db. The query is fast. I next run 'explain' and 'explain
verbose' on the query. Then you see a 'vacuum analyze' followed by
the the 'explain', 'explain verbose' and lastly the query again which
is now extremely slow!
-- begin
orig=# select offer_id, pkk_offer_has_pending_purch( offer_id ) from pkk_offer
;
(618 rows)
Time: 864.522 ms
orig=# explain select offer_id, pkk_offer_has_pending_purch( offer_id ) from
pkk_offer ;
QUERY PLAN
-
Seq Scan on pkk_offer (cost=0.00..22.50 rows=1000 width=4)
(1 row)
Time: 24.251 ms
orig=# explain verbose select offer_id, pkk_offer_has_pending_purch( offer_id )
from pkk_offer ;
QUERY PLAN
-
{SEQSCAN
:startup_cost 0.00
:total_cost 22.50
:plan_rows 1000
:plan_width 4
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname offer_id
:ressortgroupref 0
:resorigtbl 34965071
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 16
:restypmod -1
:resname pkk_offer_has_pending_purch
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
:expr
{FUNCEXPR
:funcid 34965096
:funcresulttype 16
:funcretset false
:funcformat 0
:args (
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
)
}
}
)
:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam ()
:allParam ()
:nParamExec 0
:scanrelid 1
}
Seq Scan on pkk_offer (cost=0.00..22.50 rows=1000 width=4)
(78 rows)
Time: 10.915 ms
orig=# vacuum analyze ;
VACUUM
Time: 504701.795 ms
orig=# explain select offer_id, pkk_offer_has_pending_purch( offer_id ) from
pkk_offer ;
QUERY PLAN
Seq Scan on pkk_offer (cost=0.00..13.72 rows=618 width=4)
(1 row)
Time: 96.903 ms
orig=# explain verbose select offer_id, pkk_offer_has_pending_purch( offer_id )
from pkk_offer ;
QUERY PLAN
{SEQSCAN
:startup_cost 0.00
:total_cost 13.72
:plan_rows 618
:plan_width 4
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname offer_id
:ressortgroupref 0
:resorigtbl 34965071
[SQL] Insert/Update Perl Function involving two tables needing to by 'in sync'
I am trying to write a Perl Function for one of the databases I'm building a web application for. This function is triggered to occur BEFORE INSERT OR UPDATE. This function is complex in that it involves fields in two different tables which need to be updated, where the updates one receives depend on and must actually also reflect the same data entered into one of the tables explicitly by the user. I basically want to make sure that certain Same fields in two tables are automatically updated with the exact Same information. This is redundant but it is the design our engineers came up with and management is forcing me to work with this redundant design. I've been trying to I've been trying to tweak this function using "new." & "old." prefixes for the fields that I'm manipulating but it doesn't work. I've attached my function below. Any input on how this logic is wrong is appreciated. See the code below. CREATE FUNCTION classdata_scheduleid_sync() -- the purpose of this function is to make sure that when a schedule_id -- is updated in the registration_and_attendance table, that the -- class_id start_date, & end date fields in this table are -- automatically updated with the corresponding class_id, start_date, -- end_date from the schedules table RETURNS trigger AS 'DECLARE schedule_info RECORD; BEGIN IF length(new.schedule_id) = 0 -- IS THIS AN INSERT OR UPDATE? THEN -- IS AN UPDATE FOR existing schedule_id IS NULL SELECT INTO schedule_info * FROM schedules WHERE id = old.schedule_id; UPDATE registration_and_attendance SET class_id = schedule_info.class_id, start_date = schedule_info.start_date, end_date = schedule_info.end_date WHERE id = old.id; ELSE-- must have been an INSERT SELECT INTO schedule_info * FROM schedules WHERE id = new.schedule_id; INSERT INTO registration_and_attendance(class_id, start_date, end_date) VALUES (schedule_info.class_id, schedule_info.start_date, schedule_info.end_date) WHERE schedule_id = new.schedule_id; END IF; RETURN new; END;' LANGUAGE 'plpgsql'; ---(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] 'show databases' in psql way?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 To be pedantic about it, use: SELECT pg_catalog.quote_ident(datname) AS database FROM pg_catalog.pg_database ORDER BY 1; or if using version 8 or higher: SELECT pg_catalog.quote_ident(datname) AS database, pg_catalog.quote_ident(spcname) AS tablespace FROM pg_catalog.pg_database JOIN pg_catalog.pg_tablespace t ON (dattablespace=t.oid) ORDER BY 1; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200411022120 -BEGIN PGP SIGNATURE- iD8DBQFBiFTSvJuQZxSWSsgRApflAJ0RiVndbc6u//cXX/S7uM8K91lWbgCfYVbC MsRNWlP446AcvHm0UaGgEVo= =S8Ft -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
