Re: [SQL] Rule causes baffling error
Ken Winter wrote:
Richard ~
Let me zoom out for a moment, for the bigger picture.
As you have inferred, what I'm trying to do is develop a history-preserving
table ("my_data" in the example that started this thread). *Most* user
programs would see and manipulate this table as if it contained only the
current rows (marked by effective_date_and_time <= 'now' and
expiration_date_and_time = 'infinity').
When these programs do an INSERT, I need automatic actions that set the
expiration and date timestamps to 'now' and 'infinity'; when they do an
UPDATE, I need automatic actions that save the old data in a history record
and expire it as of 'now' and the new data in a record that's effective
'now' and expires at 'infinity'; when they do a DELETE, I need an automatic
action to expire the target record as of 'now' rather than actually deleting
it.
Oh - while I think of it, be VERY VERY careful that your system clock
doesn't get put back. I've done this sort of thing and been bitten by it.
However, I also need certain maintenance programs, designed to enable
certain users to correct inaccurately entered data. These need to be able
to "rewrite history" by doing actions against "my_data" without these
automatic actions occurring. It may prove advisable to provide some
automatic actions for these programs too, but they definitely won't be the
actions described above. If the above actions were implemented as triggers,
all the ways I could think of to conditionally disable them (and possibly
replace them with other actions) seemed architecturally very klunky. That's
when I decided I needed the "my_data_now" view, and from that I inferred
(apparently correctly) that the actions would have to be implemented as
rewrite rules.
The "standard" approach in so far as there is one would be to have a
first line IF CURRENT_USER = 'MAINTENANCE' THEN RETURN ... or perhaps a
boolean stored in a system-settings table to turn them on or off in
en-masse. In your case the user-test seems better.
The cascading problem was solkable. But the solution was a bit hard to
reach because the user-invoked UPDATE action triggered both an INSERT and an
UPDATE on the same table (and user DELETE triggered an UPDATE), and so one
had to take into account that all of these triggered actions would cause
their triggers to fire again. Not a deal-killer, but the solution felt
brittle.
Yes, I did consider having a "live" table and a separate "history" table.
The killer of that idea was my inability to find a way to implement foreign
keys that could refer to both tables and that could follow a record when it
was moved from "live" to "history". Much of the history I'm trying to
preserve is not in the "my_data" table; it's in related tables that refer to
it. I presumably could do this by not declaring the FKs to PostgreSQL, and
implementing the necessary referential integrity with triggers, but - well,
in a word, yuck.
If you're going to do this with multiple tables you actually need (at
least) three. For example, if you had different versions of e.g.
documents being stored you would want:
document - invariants: the id, perhaps document-type.
FKeys link to this.
A row is only deleted from here if all live+history
is also deleted.
document_live - the one that gets edited.
1:1 relationship with document if still live
document_hist - with timestamps. N:1 with document
Have a google for Temporal Databases too - there's a lot of thinking
been done about this.
As it happens, I have found a rewrite of my UPDATE rule that works, so my
immediate need is past. FYI, the old update rule was:
[snip]
The relevant change is that I'm now expiring the record with the old data
and inserting the one with the new data, rather than vice versa. I still
don't know why the old rule didn't work and this one does, but hey,
whatever. Another advantage of the new one is that I don't have to re-point
foreign keys that were already pointed to the record containing the old
data, because that record stays in place.
(The other change, adding the lines
AND effective_date_and_time <= CURRENT_TIMESTAMP
AND expiration_date_and_time >= CURRENT_TIMESTAMP;
to the UPDATE, was necessary to keep updates to the "my_data_now" from
updating the expired rows as well.)
Make sure you test it with inserts/updates of multiple rows too.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] Querying date_time for date only ?
Hello List, I have a time stamp without time zone field, -MM-DD hh:mm:ss, in my table. I want to also find something just for a particular day regardless of the time. (Pg)SQL way to do this ? TIA, Aarni -- -- This is a bugfree broadcast to you from **Kmail** on **Fedora Core** linux system -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Querying date_time for date only ?
On December 20, 2005 08:59 am, Aarni Ruuhimäki wrote: > Hello List, > > I have a time stamp without time zone field, -MM-DD hh:mm:ss, in my > table. I want to also find something just for a particular day regardless > of the time. > > (Pg)SQL way to do this ? You can try, SELECT field::date FROM mytable; to select only the date part. Likewise, you can use field::time if you want to disregard the date. > TIA, > > Aarni HTH. Mike. -- Michael Burke [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: 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] Querying date_time for date only ?
On Tuesday 20 December 2005 15:19, Michael Burke wrote:
> On December 20, 2005 08:59 am, Aarni Ruuhimäki wrote:
> > Hello List,
> >
> > I have a time stamp without time zone field, -MM-DD hh:mm:ss, in my
> > table. I want to also find something just for a particular day regardless
> > of the time.
> >
> > (Pg)SQL way to do this ?
>
> You can try,
>
> SELECT field::date FROM mytable;
>
> to select only the date part. Likewise, you can use field::time if you
> want to disregard the date.
>
That's neat, thanks.
I was just getting there with
WHERE ... AND EXTRACT('day' FROM res_date_time) = $day
AND EXTRACT('month' FROM res_date_time) = $month
AND ...,
which may be useful elsewhere.
> > TIA,
> >
> > Aarni
>
> HTH.
> Mike.
Merry Christmas to everyone,
Aarni
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] Sub-query as function argument
Is it possible to execute a SELECT query as an argument to a function?
Example:
SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio);
In my particular case, my_func inserts columns into another table; I wish to
use values from another table as the arguments. The interior SELECT will
return only a single value; perhaps it needs to be wrapped in another
function?
TIA.
Mike.
--
Michael Burke
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Sub-query as function argument
On 12/20/05, Michael Burke <[EMAIL PROTECTED]> wrote: > Is it possible to execute a SELECT query as an argument to a function? > have you tried? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Sub-query as function argument
On December 20, 2005 10:52 am, Jaime Casanova wrote:
> have you tried?
Yes:
=> SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio);
ERROR: syntax error at or near "SELECT" at character 32
PostgreSQL 7.4.9, myfunc is pl/pgsql.
Just found a working method, though:
=> SELECT my_func('Sample', NULL, MIN(year)) FROM audio [ WHERE ... ];
--
Michael Burke
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Querying date_time for date only ?
Michael Burke <[EMAIL PROTECTED]> writes: > On December 20, 2005 08:59 am, Aarni Ruuhimäki wrote: >> I have a time stamp without time zone field, -MM-DD hh:mm:ss, in my >> table. I want to also find something just for a particular day regardless >> of the time. > You can try, > SELECT field::date FROM mytable; The date_trunc() function can also be useful for this sort of thing, particularly if you need to round off to something finer or coarser than days. http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Sub-query as function argument
Michael Burke <[EMAIL PROTECTED]> writes:
> Is it possible to execute a SELECT query as an argument to a function?
> SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio);
You need parentheses around the sub-SELECT.
SELECT my_func('Sample', NULL, (SELECT MIN(year) FROM audio));
This is generally true everywhere in expressions, not just in
function arguments. Without the parens, it's often ambiguous
what's subselect and what's outer query.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Does VACUUM reorder tables on clustered indices
On Sun, Dec 18, 2005 at 07:01:39PM -0300, Alvaro Herrera wrote: > Martin Marques escribi?: > > On Sun, 18 Dec 2005, frank church wrote: > > > > > > > >Does VACUUMing reorder tables on clustered indices or is it only the > > >CLUSTER > > >command that can do that? > > > > Cluster does that. Vacuum only cleans dead tuples from the tables. > > Note that while reordering, CLUSTER also gets rid of dead tuples, so if > you cluster you don't need to vacuum. It also does a REINDEX... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Performance of a view
John McCawley <[EMAIL PROTECTED]> writes: > I have a view which is defined as follows: > SELECT tbl_claim.claim_id, count(tbl_invoice.invoice_id) AS count, > min(tbl_invoice.invoicedate) AS invoicedate > FROM tbl_claim > LEFT JOIN tbl_invoice ON tbl_claim.claim_id = tbl_invoice.claim_id AND > tbl_invoice.active = 1 > GROUP BY tbl_claim.claim_id; > If I run: > EXPLAIN ANALYZE SELECT > tbl_claim.claim_id FROM tbl_claim INNER JOIN vw_claiminvoicecount ON > tbl_claim.claim_id = vw_claiminvoicecount.claim_id WHERE > tbl_claim.claim_id = 217778; > [ it's fast ] > However, if I run: > EXPLAIN ANALYZE SELECT > tbl_claim.claim_id FROM tbl_claim INNER JOIN vw_claiminvoicecount ON > tbl_claim.claim_id = vw_claiminvoicecount.claim_id WHERE > tbl_claim.claimnum = 'L1J8823'; > [ it's not ] I finally got around to looking at this. The reason the first case is fast is that the planner is able to deduce the extra condition vw_claiminvoicecount.claim_id = 217778, and then push that down into the view, so that the LEFT JOIN only need be performed for the single tbl_claim row with that claim_id. In the second case this is not possible --- the restriction on claimnum doesn't have any connection to the view that the planner can see. My advice is to extend the view to show claimnum as well, and then you can forget about the extra join of tbl_claim and just do SELECT * FROM vw_claiminvoicecount WHERE claimnum = 'L1J8823'; regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Does VACUUM reorder tables on clustered indices
Hi, Utilize CLUSTER; (after vacuum) to reorder the data. Regards - Original Message - From: "Jim C. Nasby" <[EMAIL PROTECTED]> To: "Martin Marques" ; "frank church" <[EMAIL PROTECTED]>; Sent: Tuesday, December 20, 2005 10:41 PM Subject: Re: [SQL] Does VACUUM reorder tables on clustered indices On Sun, Dec 18, 2005 at 07:01:39PM -0300, Alvaro Herrera wrote: Martin Marques escribi?: > On Sun, 18 Dec 2005, frank church wrote: > > > > >Does VACUUMing reorder tables on clustered indices or is it only the > >CLUSTER > >command that can do that? > > Cluster does that. Vacuum only cleans dead tuples from the tables. Note that while reordering, CLUSTER also gets rid of dead tuples, so if you cluster you don't need to vacuum. It also does a REINDEX... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Does VACUUM reorder tables on clustered indices
On Wed, Dec 21, 2005 at 12:34:12AM +0100, [EMAIL PROTECTED] wrote: > Hi, > > Utilize CLUSTER; (after vacuum) to reorder the data. Why would you vacuum when cluster is just going to wipe out the dead tuples anyway? > >>Note that while reordering, CLUSTER also gets rid of dead tuples, so if > >>you cluster you don't need to vacuum. > > > >It also does a REINDEX... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Help me do a LOOP
I need to test each record of a cursor for some requirements, something like thisDECLARE xpvnr varchar(50); xdata date; xcod int; xagentd varchar(3); xid bigint; xserie varchar(4); xnr varchar(7); xsocasig varchar(3); DECLARE myCursor CURSOR FOR select cod,pvnr,data,agentd,id,serie,nr,socasig from pvmst, pvdtl where pvmst.cod=pvdtl.cod order by data; BEGIN delete from stoccalc_temp; OPEN myCursor; FETCH NEXT FROM myCursor INTO xcod,xpvnr,xdata,xagentd,xid,xserie,xnr, xsocasig; WH
ILE
(still in the cursor) BEGIN DECLARE xxcod varchar(21); xxcod2 varchar(21); xxagent varchar(3); xxid bigint; DECLARE my2cursor CURSOR FOR select cod,agent,id from stoccalc_temp where nr=xnr and serie=xserie and socasig=xsocasig; OPEN my2cursor; FETCH NEXT FROM my2cursor INTO xxcod,xxagent,xxid;
xxcod2:=TRIM('2005'+TRIM(xsocasig)+TRIM(xxagent)+TRIM(xserie)+TRIM(xnr)); IF xxcod <> '' THEN -- inseamna ca este in stoccalc_temp polita update stoccalc set cod=xxcod2, agent=xxagent, stare='0' where id=xid; END IF; CLOSE my2cursor; FETCH NEXT FROM myCursor INTO xcod,xpvnr,xdata,xagentd,xid,xserie,xnr,xsocasig; END;CLOSE myCursor;END;-- the problem is that I do not know how to make the while loop execute until the last record "WHILE (still in the cursor)"... So I need something like for i:=1 to lastrecord_of_cursor do begin ... end; Can anyone help me with this? It
really
seems silly... but I cannot get to solve this, I do not know where to look for syntax like this or tutorials like this. Help
Yahoo! Shopping
Find Great Deals on Holiday Gifts at Yahoo! Shopping
[SQL] Commiting after certain no of rows have been deleted
Hi, In my application I m deleteing large no of rows from table based on certain condition. This takes lot of time and if sometimes my application fails it starts all over again...since the coomit is done at the end of transactions. Is there a way i can do commit when certain no of rows have been deleted? For eg if i need to delete 2 million rows i should be able to commit after say 10,000 rows. Regards smita
[SQL] Problem obtaining MAX values FROM TABLE
I am having a problem with a query, I have a view which produces something like this: b_idcompany_name product_count product_type 29"company 1" 1 "a" 29"company 2" 1 "b" 29"company 3" 3 "a" 27"company 4" 1 "c" 27"company 4" 4 "d" 24"company 5" 3 "a" 24"company 5" 5 "c" 24"company 5" 2 "d" - I need to write a query which returns each company together with the highest product_count and its associated product type so the result should look like this: b_idcompany_name product_count product_type 29"company 1" 1 "a" 29"company 2" 1 "b" 29"company 3" 3 "a" 27"company 4" 4 "d" 24"company 5" 5 "c" - I have tried the following query: SELECT company, MAX(type_count), product_type FROM buyer_product_frequencies GROUP BY company, product_type But in this case it just produces the same results as are in the first table. If I drop product_type from the query I get the right result but I don't have the product type which I need. This problem is driving me mad! so any assistance would be greatly appreciated. Many thanks, Mike Farewell ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [GENERAL] Question on indexes
CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html vishOn 12/19/05, Emil Rachovsky <[EMAIL PROTECTED]> wrote: Hi,Can anyone show me a simple way of creating an indexin PostGre like that:create index indName on someTable(someIntColumn DESC)?Thanks In Advance,Emil__ Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection aroundhttp://mail.yahoo.com---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Help on a complex query (avg data for day of the week)
Hello,
I have a table containing a timestamp and data usage fields (among others).
This table stores amounts of data usage and the times then the data was used,
eg:
time | data
+--
2005-03-26 09:32:43+11 | 162
I want to form a query that returns the average total usage for each day of
the week, eg:
day|avg_usage
+-
0 | 35684624.0
1 | 103344529.0
2 | 105899406.0
3 | 21994539.0
4 | 113045173.0
5 | 110675115.0
6 | 8791397.00
(7 rows)
To get this info, I am using the following query:
select dow as day, sum(sum_data)/count(dow) as avg_usage from
(select extract('dow' from date_trunc('day', time)) as dow, sum(data) as
sum_data
from datalog where time >= '2005-09-11' and time <= '2005-09-25' group by dow)
as avg_data_per_day group by day;
This works well, assuming that there is at least one entry in the table for
each day in the time period.
The problem comes when there are days where no data is logged. In my example,
the total data for each day of the week is divided by the number of the days
found. As there are exactly 2 of each day of the week between 2005-09-11 and
2005-09-25, we should hope to divide each total by 2. but if there is no data
logged for the 14th, then the total for wednesdays would be divided by 1. I
want it to be the sum of the 2 days divided by 2: (101994539 + 0)/2.
Is there a better way to do this? Or does anyone have any suggestions on the
best way to insert the missing dates into my query?
Any help would be great!
Thanks,
Matthew Smith
---(end of broadcast)---
TIP 6: explain analyze is your friend
