Re: [SQL] TRIGGERS and FUNCTIONS
--- ashok raj <[EMAIL PROTECTED]> wrote: > Hello All , > >I am having a table named "test" with a trigger " tri_test > " which triggers the function " replicate() " on insert, update and > delete . >Can I able to get the SQL STATEMENT which triggers the > function replicate ( ) , inside the function definition replicate ( ) . >So that I could execute that statement in the remote > postgreSQL server You probably can get the statement. However, your solution sounds a lot like an existing replication solution, "Slony". It will do exactly what you want, and is already stable for production systems. http://pgfoundry.org/projects/slony1/ Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] When is a shared library unloaded?
Here is a little more information about my problem. There is a library( that has stored procedures) for processing trigger events. The library is loaded on first access( i.e when there is a trigger event to be sent). Normally, it gets unloaded during a postgres server shutdown but the library seems to be getting unloaded occasionally.We have logs in the _init( ) and _fini( ) of the library and we see that there are times when _fini( ) is getting called when you wouldn't expect it to be. Has anyone else had issues similar to this? Thanks, Jon. On 8/15/07, Jon Horsman <[EMAIL PROTECTED]> wrote: > > "Jon Horsman" <[EMAIL PROTECTED]> writes: > > > I'm randomly having issues with my triggers not firing (it turns out > > > they are getting removed) and when i turned INFO logs on i saw the > > > following > > > > > 2007-08-14 13:41:44.740305500 LOCATION: _fini, medbevent_init.c:129 > > > 2007-08-14 13:41:45.790850500 INFO: 0: medbeventlib -Unloading > > > library _fini() is called > > > > > Could someone please explain when a shared library gets > > > loaded/unloaded and how this all works? > > > > Are you sure the above doesn't just occur during server process > > termination? AFAIK Postgres never unloads a library, except when you > > specifically command it to load an updated version via the LOAD command. > > > > regards, tom lane > > Yup, the server process is still up and going, i have other clients > connected to postgres that still function when this happens. I'm not > using the LOAD command anywhere so thats not the problem. One of my > colleagues was doing some reading and said he found something that > stating that the fini() function is "unreliable", i'm not sure where > he read that though... > > Jon. > ---(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] Join query help
Try 2: Here are my DDLs & DMLs
-- Start
CREATE TABLE record
(
record_id integer PRIMARY KEY,
record_date timestamp with time zone NOT NULL
);
INSERT INTO record(record_id, record_date) VALUES ('1', '2007-07-23 11:30:37');
INSERT INTO record(record_id, record_date) VALUES ('2', '2007-07-27 11:30:14');
INSERT INTO record(record_id, record_date) VALUES ('3', '2007-07-17 13:15:03');
CREATE TABLE score
(
score_id integer PRIMARY KEY,
score_description character(7) NOT NULL
);
INSERT INTO score(score_id, score_description) VALUES ('0', 'NA');
INSERT INTO score(score_id, score_description) VALUES ('1', 'SAFE');
INSERT INTO score(score_id, score_description) VALUES ('2', 'AT RISK');
CREATE TABLE observation
(
observation_id integer PRIMARY KEY,
record_id integer REFERENCES record (record_id),
score_id integer REFERENCES score (score_id)
);
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3240', '1', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3239', '1', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3238', '1', '2');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3237', '1', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('2872', '2', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('2869', '2', '2');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('2870', '2', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('2871', '2', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3218', '3', '2');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3217', '3', '1');
-- End
By executing
SELECT week,
COUNT(record) as records,
SUM(inspection) as inspections
FROM
(
SELECT TO_CHAR(record.record_date, ', WW') as week,
RECORD.RECORD_ID AS RECORD,
COUNT(OBSERVATION_ID) AS INSPECTION
FROM OBSERVATION
LEFT JOIN record ON record.record_id = observation.record_id
GROUP BY RECORD, WEEK
) A
GROUP BY WEEK;
I get
week | records | inspections
--+-+--
2007, 30 | 2 |8
2007, 29 | 1 |2
(2 rows)
I like to query for a result set that will also have the sum(score_id)
where score_id = '1' like the following
week | records | inspections | score
--+-+-+--
2007, 30 | 2 | 8 |6
2007, 29 | 1 | 2 |1
(2 rows)
This will help identify that there were 6 SAFE observations found from
the 8 inspections on week 30.
I hope this is not too confusing that I have to re-design the whole schema
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Join query help
On Aug 18, 2007, at 0:35 , novice wrote:
What query do I write to generate the following?
week_no | count(record_id | count(observation_id) | sum(score_id)
where = '1'
2007, 30 | 2 | 8 | 6
2007, 29 | 1 | 2 | 1
Okay: let's take a look at what you're trying to get:
first column is year and week of year (which might be better labeled
"week" rather than "week_no")
Checking the available date/time functions, I see extract can get at
both of these attributes of a date value. That could be helpful.
However, this splits the week and year into two separate values,
which are two things to keep track of. As well, those values are now
integers, which have little to do with dates. date_trunc allows you
to truncate timestamps to the week, returning a timestamp value. This
mean's we can look at timestamps with week-precision: and they're
still timestamps, which means we can rely on them to act as dates in
terms of ordering (Some would go ahead and format the year, week
column as text right a way using TO_CHAR and rely on string
comparisons for grouping and ordering, but there's no reason to do
this, and you can no longer handle the value easily as a datetime
value. If you need a specific output format, do it at the end or in
your middleware.)
If you're going to group records by week (rather than timestamp)
often, I'd go ahead and create a VIEW:
CREATE VIEW record_with_week AS
SELECT record_id
, date_trunc('week', record_date) AS record_week
FROM record;
SELECT *, extract(week from record_week) as week_number
test-# FROM record_with_week;
record_id | record_week | week_number
---++-
1 | 2007-07-16 00:00:00-05 | 29
2 | 2007-07-23 00:00:00-05 | 30
3 | 2007-07-16 00:00:00-05 | 29
You're also looking for the count of distinct records and
observations per week, so let's do that:
SELECT record_week
, count(DISTINCT record_id) AS record_count
, count(DISTINCT observation_id) AS observation_count
FROM record_with_week
NATURAL JOIN observation
GROUP BY record_week;
record_week | record_count | observation_count
+--+---
2007-07-16 00:00:00-05 |2 | 6
2007-07-23 00:00:00-05 |1 | 4
(2 rows)
Now here's where I started having trouble. I can't figure out how to
get 2 observations for week 29 (record_id 1 & 3) and 8 for week 30
(record_id 2). Assuming the data is wrong (which is admittedly a poor
assumption), I moved ahead.
I'm interpreting "sum(score_id) where = '1'" as the sum of scores for
observations of that week where score_description is 'SAFE' (note
that '1' is text, and it appears that your score_id column is an
integer). So, let's get the observations that were safe:
SELECT record_week, sum(score_id) as safe_score_sum
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN score
WHERE score_description = 'SAFE'
GROUP BY record_week;
record_week | safe_score_sum
+
2007-07-23 00:00:00-05 | 3
2007-07-16 00:00:00-05 | 4
(2 rows)
Note I'm using score_description = 'SAFE' rather than score_id = 1,
as this is much more descriptive of what you're actually doing. It
makes the query easier to read as well.
Again, I can't get my numbers to agree with yours, so I'm probably
misinterpreting something, but I can't figure out another way to
interpret what you've described.
So, let's join this to the query:
SELECT record_week
, count(DISTINCT record_id) AS record_count
, count(DISTINCT observation_id) AS observation_count
, safe_score_sum
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN (
SELECT record_week, sum(score_id) as safe_score_sum
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN score
WHERE score_description = 'SAFE'
GROUP BY record_week
) safe_observation
GROUP BY record_week, safe_score_sum;
record_week | record_count | observation_count |
safe_score_sum
+--+---
+
2007-07-16 00:00:00-05 |2 | 6
| 4
2007-07-23 00:00:00-05 |1 | 4
| 3
(2 rows)
One thing that struck me as odd is that you're summing an ID column.
I'm guessing you're doing this as an attempt to count the total safe
observations, taking advantage of the coincidence that the score_id
is 1, so sum of observations with score_id = 1 is the same as the
count of observations where score_id is 1. If this is indeed what's
happening, I think I've got a better way to do it:
SELECT record_week
, count(DISTINCT record_id) AS record_count
, count(DISTINCT observation_id) AS observation_count
, count(DISTINCT safe_o
Re: [SQL] Join query help
On 21/08/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: > Now here's where I started having trouble. I can't figure out how to > get 2 observations for week 29 (record_id 1 & 3) and 8 for week 30 > (record_id 2). Assuming the data is wrong (which is admittedly a poor > assumption), I moved ahead. > > week_no | count(record_id | count(observation_id) | sum(score_id) > > where = '1' > > 2007, 30 | 2 | 8 | 6 > > 2007, 29 | 1 | 2 | 1 For week 29, there were 1 SAFE observation out of the 2 observations and for week 30, there were 6 SAFE obseravations out of the 8 observations. Hope this helps. Thanks! ---(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] [GENERAL] Join query help
On Aug 20, 2007, at 19:52 , novice wrote:
Try 2: Here are my DDLs & DMLs
Thanks for the data. It looks like the data you provided in the first
set was a little different, and the queries I supplied in my previous
message give you the results you want.
CREATE TABLE record
(
record_id integer PRIMARY KEY,
record_date timestamp with time zone NOT NULL
);
INSERT INTO record(record_id, record_date) VALUES ('1', '2007-07-23
11:30:37');
Note: record_id is in integer, yet you're quoting the value ('1').
This causes the server to cast the text value to an integer. Here
it's not going to cause much of a problem, just a couple CPU cycles.
In table definitions (and possibly queries?) it can cause the server
to ignore otherwise usable indexes when planning queries. (I see
below your other inserts also quote integer values: you should drop
the quotes there as well.)
CREATE TABLE score
(
score_id integer PRIMARY KEY,
score_description character(7) NOT NULL
);
Unless you have a strict business rule that score_description can
have no more than 7 characters, you should just use text instead of
character(7). It provides you more freedom (for example, you don't
have to change the column type if you ever want to use longer
descriptions). character(7) does not gain you anything in terms of
performance.
I'd also recommend adding a UNIQUE constraint to score_description.
It appears your score_id has no meaning other than use as a surrogate
key. There's nothing to prevent INSERT INTO score (score_id,
score_description) VALUES (5, 'SAFE') which would be quite confusing,
I should think. And if you don't have any particular reason to use a
surrogate key, you could just use score_description as the primary
key of the table, dropping score_id altogether. it would reduce the
number of joins you needed to do to have easily interpretable query
results. (This all goes for your record table as well.)
I like to query for a result set that will also have the sum(score_id)
where score_id = '1' like the following
week | records | inspections | score
--+-+-+--
2007, 30 | 2 | 8 |6
2007, 29 | 1 | 2 |1
(2 rows)
(repeating from earlier post)
SELECT to_char(record_week, ', IW') as formatted_record_week
, count(DISTINCT record_id) AS record_count
, count(DISTINCT observation_id) AS observation_count
, safe_score_sum
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN (
SELECT record_week, sum(score_id) as safe_score_sum
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN score
WHERE score_description = 'SAFE'
GROUP BY record_week
) safe_observation
GROUP BY record_week, safe_score_sum;
formatted_record_week | record_count | observation_count |
safe_score_sum
---+--+---
+
2007, 29 |1 | 2
| 1
2007, 30 |2 | 8
| 6
(2 rows)
This will help identify that there were 6 SAFE observations found from
the 8 inspections on week 30.
Yeah, I thought so: you're actually looking for the *count* of SAFE
observations, not the sum of the score_id for 'SAFE'. So what you
really want is:
SELECT TO_CHAR(record_week, ', IW') AS formatted_record_week
, count(DISTINCT record_id) AS record_count
, count(DISTINCT observation_id) AS observation_count
, count(DISTINCT safe_observation_id) as safe_observation_count
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN (
SELECT record_week
, observation_id as safe_observation_id
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN score
WHERE score_description = 'SAFE'
) safe_observation
GROUP BY record_week;
formatted_record_week | record_count | observation_count |
safe_observation_count
---+--+---
+
2007, 29 |1 | 2
| 1
2007, 30 |2 | 8
| 6
(2 rows)
Michael Glaesemann
grzm seespotcode net
---(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] Join query help
Many many thanks for all the advice =)
On 21/08/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
>
> On Aug 20, 2007, at 19:52 , novice wrote:
>
> > Try 2: Here are my DDLs & DMLs
>
> Thanks for the data. It looks like the data you provided in the first
> set was a little different, and the queries I supplied in my previous
> message give you the results you want.
>
> > CREATE TABLE record
> > (
> > record_id integer PRIMARY KEY,
> > record_date timestamp with time zone NOT NULL
> > );
> >
> > INSERT INTO record(record_id, record_date) VALUES ('1', '2007-07-23
> > 11:30:37');
>
> Note: record_id is in integer, yet you're quoting the value ('1').
> This causes the server to cast the text value to an integer. Here
> it's not going to cause much of a problem, just a couple CPU cycles.
> In table definitions (and possibly queries?) it can cause the server
> to ignore otherwise usable indexes when planning queries. (I see
> below your other inserts also quote integer values: you should drop
> the quotes there as well.)
>
> > CREATE TABLE score
> > (
> > score_id integer PRIMARY KEY,
> > score_description character(7) NOT NULL
> >
> > );
>
> Unless you have a strict business rule that score_description can
> have no more than 7 characters, you should just use text instead of
> character(7). It provides you more freedom (for example, you don't
> have to change the column type if you ever want to use longer
> descriptions). character(7) does not gain you anything in terms of
> performance.
>
> I'd also recommend adding a UNIQUE constraint to score_description.
> It appears your score_id has no meaning other than use as a surrogate
> key. There's nothing to prevent INSERT INTO score (score_id,
> score_description) VALUES (5, 'SAFE') which would be quite confusing,
> I should think. And if you don't have any particular reason to use a
> surrogate key, you could just use score_description as the primary
> key of the table, dropping score_id altogether. it would reduce the
> number of joins you needed to do to have easily interpretable query
> results. (This all goes for your record table as well.)
>
> > I like to query for a result set that will also have the sum(score_id)
> > where score_id = '1' like the following
> >
> >week | records | inspections | score
> > --+-+-+--
> > 2007, 30 | 2 | 8 |6
> > 2007, 29 | 1 | 2 |1
> > (2 rows)
> >
>
> (repeating from earlier post)
>
> SELECT to_char(record_week, ', IW') as formatted_record_week
> , count(DISTINCT record_id) AS record_count
> , count(DISTINCT observation_id) AS observation_count
> , safe_score_sum
> FROM record_with_week
> NATURAL JOIN observation
> NATURAL JOIN (
> SELECT record_week, sum(score_id) as safe_score_sum
> FROM record_with_week
> NATURAL JOIN observation
> NATURAL JOIN score
> WHERE score_description = 'SAFE'
> GROUP BY record_week
> ) safe_observation
> GROUP BY record_week, safe_score_sum;
> formatted_record_week | record_count | observation_count |
> safe_score_sum
> ---+--+---
> +
> 2007, 29 |1 | 2
> | 1
> 2007, 30 |2 | 8
> | 6
> (2 rows)
>
>
> > This will help identify that there were 6 SAFE observations found from
> > the 8 inspections on week 30.
>
> Yeah, I thought so: you're actually looking for the *count* of SAFE
> observations, not the sum of the score_id for 'SAFE'. So what you
> really want is:
>
> SELECT TO_CHAR(record_week, ', IW') AS formatted_record_week
> , count(DISTINCT record_id) AS record_count
> , count(DISTINCT observation_id) AS observation_count
> , count(DISTINCT safe_observation_id) as safe_observation_count
> FROM record_with_week
> NATURAL JOIN observation
> NATURAL JOIN (
> SELECT record_week
> , observation_id as safe_observation_id
> FROM record_with_week
> NATURAL JOIN observation
> NATURAL JOIN score
> WHERE score_description = 'SAFE'
> ) safe_observation
> GROUP BY record_week;
> formatted_record_week | record_count | observation_count |
> safe_observation_count
> ---+--+---
> +
> 2007, 29 |1 | 2
> | 1
> 2007, 30 |2 | 8
> | 6
> (2 rows)
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
>
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] Join query help
On Aug 20, 2007, at 20:27 , Michael Glaesemann wrote:
Note: record_id is in integer, yet you're quoting the value ('1').
This causes the server to cast the text value to an integer. Here
it's not going to cause much of a problem, just a couple CPU
cycles. In table definitions (and possibly queries?) it can cause
the server to ignore otherwise usable indexes when planning
queries. (I see below your other inserts also quote integer values:
you should drop the quotes there as well.)
Some clarification: what I meant by "table definitions" is if you're
actually defining text columns that reference integer columns. For
example:
CREATE TABLE foos (foo_id INTEGER PRIMARY KEY, foo text NOT NULL
UNIQUE);
CREATE TABLE bars (bar text PRIMARY KEY, foo_id text NOT NULL
REFERENCES foos (foo_id));
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [GENERAL] Join query help
On Aug 20, 2007, at 20:33 , novice wrote: Many many thanks for all the advice =) Glad to help. Good luck! Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
