Re: Fwd: [SQL] Start up question about triggers

[Andrew Sullivan]

On Fri, Jun 23, 2006 at 10:29:33PM -0500, Aaron Bono wrote:
> 
> What I really cannot find is a way to _dynamically_ in the trigger ask what
> COLUMNS are in OLD and NEW.  If we had:

All of the columns, of course.  When the tuple is UPDATEd, even if
you did not mention some columns in your UPDATE, there is an OLD
value and a NEW value.  They're the same.

> I wish I had more experience with stored procedures - I know what I would
> try to do, just not if it is possible or how to implement it.

I think what Tom (but I don't want to put words in his mouth) and,
certainly, I have been saying is that your plan to get the list of
transform commands is fundamentally misguided.  If you want to track
changes, then that's what you should do: track what changed.

Note that there is a project that already, in fact, does this for you
as part of how it works: Slony-I.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
When my information changes, I alter my conclusions.  What do you do sir?
--attr. John Maynard Keynes

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: Fwd: [SQL] Start up question about triggers

[Forums @ Existanze]

Hello again,

The problem is not tracking WHAT changed, this can be done, as we have
discussed in this thread, the problem is how to replicate the necessary
commands that will alter a mirror database to reflect what has been changed,
sequencially and in the order that it has occurred.

I you can find my first question (I can repost), you will see that this
problem has arisen due of lack of proper design. This is a requirement that
has just come into the picture and we have to find a solution for it.

My initial question was intended to figure out a way to track these changes
AND create the necessary INSERT,DELETE and UPDATE statements for each of the
changes that occurr in the entire database. I wanted to avoid having to
create an audit table for each of the tables in the database. At the moment
we are counting 82, this will mean another 82 tables, along with 82 triggers
and so on. 

I personally don't believe that what I am trying to do is "fundamentally
misguided", it may not be possible, but my theoretical suggestion a couple
of posts back could be a very simple and fast way of doing this, given the
necessary functionallity. ie. Recording the statement that we ran on the
database.

Once again, I am amassed at this mailing list for the wonderfull support,
and lack of hesitasion in answering a lost soul's question, I just wished
the rest of the mailing list could be like this. I am really gratefull for
all your comments and suggestions. 

Best Regards,
Fotis
 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan
> Sent: 26 June 2006 13:43
> To: pgsql-sql@postgresql.org
> Subject: Re: Fwd: [SQL] Start up question about triggers
> 
> On Fri, Jun 23, 2006 at 10:29:33PM -0500, Aaron Bono wrote:
> > 
> > What I really cannot find is a way to _dynamically_ in the 
> trigger ask 
> > what COLUMNS are in OLD and NEW.  If we had:
> 
> All of the columns, of course.  When the tuple is UPDATEd, 
> even if you did not mention some columns in your UPDATE, 
> there is an OLD value and a NEW value.  They're the same.
> 
> > I wish I had more experience with stored procedures - I know what I 
> > would try to do, just not if it is possible or how to implement it.
> 
> I think what Tom (but I don't want to put words in his mouth) 
> and, certainly, I have been saying is that your plan to get 
> the list of transform commands is fundamentally misguided.  
> If you want to track changes, then that's what you should do: 
> track what changed.
> 
> Note that there is a project that already, in fact, does this 
> for you as part of how it works: Slony-I.
> 
> A
> 
> --
> Andrew Sullivan  | [EMAIL PROTECTED]
> When my information changes, I alter my conclusions.  What do 
> you do sir?
>   --attr. John Maynard Keynes
> 
> ---(end of 
> broadcast)---
> TIP 6: explain analyze is your friend
> 


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: Fwd: [SQL] Start up question about triggers

[Richard Broersma Jr]

> The problem is not tracking WHAT changed, this can be done, as we have
> discussed in this thread, the problem is how to replicate the necessary
> commands that will alter a mirror database to reflect what has been changed,
> sequencially and in the order that it has occurred.

If your finial goal is just to achieve db server replication, wouldn't slony 
achieve what you
want?

Regards,

Richard Broersma Jr.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: Fwd: [SQL] Start up question about triggers

[Andrew Sullivan]

On Mon, Jun 26, 2006 at 04:59:26PM +0300, Forums @ Existanze wrote:
> Hello again,
> 
> The problem is not tracking WHAT changed, this can be done, as we have
> discussed in this thread, the problem is how to replicate the necessary
> commands that will alter a mirror database to reflect what has been changed,
> sequencially and in the order that it has occurred.
> 
> I you can find my first question (I can repost), you will see that this
> problem has arisen due of lack of proper design. This is a requirement that
> has just come into the picture and we have to find a solution for it.

Ah.  That was not, in fact, clear to me.  What is it about Slony that
doesn't solve this problem?  You've checked it out, right? 
 will get you there.

A


-- 
Andrew Sullivan  | [EMAIL PROTECTED]
If they don't do anything, we don't need their acronym.
--Josh Hamilton, on the US FEMA

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: Fwd: [SQL] Start up question about triggers

[Scott Marlowe]

On Mon, 2006-06-26 at 08:59, Forums @ Existanze wrote:
> Hello again,
> 
> The problem is not tracking WHAT changed, this can be done, as we have
> discussed in this thread, the problem is how to replicate the necessary
> commands that will alter a mirror database to reflect what has been changed,
> sequencially and in the order that it has occurred.
> 
> I you can find my first question (I can repost), you will see that this
> problem has arisen due of lack of proper design. This is a requirement that
> has just come into the picture and we have to find a solution for it.
> 
> My initial question was intended to figure out a way to track these changes
> AND create the necessary INSERT,DELETE and UPDATE statements for each of the
> changes that occurr in the entire database. I wanted to avoid having to
> create an audit table for each of the tables in the database. At the moment
> we are counting 82, this will mean another 82 tables, along with 82 triggers
> and so on. 

I'm having a mad scientist moment.

I bet it would be possible to hack pgpool to do this.  Just have it
shoot all the queries that come in to it at the normal database, AND at
a text file or something like that.

Not sure that's any better than using pgsql logging to do the same
thing, but it certainly doesn't seem like it would be that hard to do.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: Fwd: [SQL] Start up question about triggers

[Andrew Sullivan]

On Mon, Jun 26, 2006 at 10:38:06AM -0500, Scott Marlowe wrote:
> I bet it would be possible to hack pgpool to do this.  Just have it
> shoot all the queries that come in to it at the normal database, AND at
> a text file or something like that.

Now you're back to the same problem: it doesn't tell you what the
database looks like.  It tells you what commands were sent.  What
about triggers?  Rules?  For that matter, what about 

SELECT now()

or 

SELECT nextval()

?

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
--Dennis Ritchie

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] avg(interval)

[Jeremiah Elliott]

I am getting some really strange query output. I am trying to average an
interval. The problem is that the interval average is sometimes
returning more than 24 hours for the average.

query:
select avg(scan_date -  backflush_date) as time_diff,
substring(pt_article, 1, 5) as clan, sum(qty) as units
from carton_master
join outbound on master_number = master_id
join pt_mstr on product = pt_part  where
scan_date::date  between '2006-06-01'::date and '2006-07-01'::date
and  substring(pt_article, 1, 5) = '*2420'
group by clan

output:
4 days 33:48:13.994333   *2420   25



however if i don't average them here is what i get:
query:
select (scan_date -  backflush_date) as time_diff,
substring(pt_article, 1, 5) as clan
from carton_master
join outbound on master_number = master_id
join pt_mstr on product = pt_part  where
scan_date::date  between '2006-06-01'::date and '2006-07-01'::date
and  substring(pt_article, 1, 5) = '*2420'

output:
"7 days 22:24:50.62311";"*2420"
"9 days 22:21:02.683393";"*2420"
"23:21:35.458459";"*2420"
"4 days 22:47:41.749756";"*2420"
"3 days 06:05:59.456947";"*2420"



which should average to just over nine days -
Assuming I am not missing something huge here. I tend to think its a bug
in avg(interval) postgres function.

I am running postgres 8.1.3 on 32bit suse

Thanks
Jeremiah Elliott

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] avg(interval)

[Tom Lane]

"Jeremiah Elliott" <[EMAIL PROTECTED]> writes:
> however if i don't average them here is what i get:
> "7 days 22:24:50.62311";"*2420"
> "9 days 22:21:02.683393";"*2420"
> "23:21:35.458459";"*2420"
> "4 days 22:47:41.749756";"*2420"
> "3 days 06:05:59.456947";"*2420"

> which should average to just over nine days -

Uh ... how do you arrive at that conclusion?  I haven't done the math,
but by eyeball an average of four-something days doesn't look out of
line for those values.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] avg(interval)

[Joe]

Tom Lane wrote:

"Jeremiah Elliott" <[EMAIL PROTECTED]> writes:

however if i don't average them here is what i get:
"7 days 22:24:50.62311";"*2420"
"9 days 22:21:02.683393";"*2420"
"23:21:35.458459";"*2420"
"4 days 22:47:41.749756";"*2420"
"3 days 06:05:59.456947";"*2420"



which should average to just over nine days -


Uh ... how do you arrive at that conclusion?  I haven't done the math,
but by eyeball an average of four-something days doesn't look out of
line for those values.


It seems he's calculating (7 + 9 + 23 + 4 + 3) / 5 ...

Joe

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[SQL] Problem w/ Join... Using (...) Syntax

[operationsengineer1]

how does one apply the JOIN... USING (...) syntax when
the primary key and foreign key have different names?

JOIN t_employee USING (employee_id)

works perfect when the primary and foreign key are
both named employee_id.

however, it one is named inspector_id and the other
employee_id, it doesn't work - or at least i haven't
been able to find the appropriate syntax through
searching and trial and error.

tia...

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Problem w/ Join... Using (...) Syntax

[Alvaro Herrera]

[EMAIL PROTECTED] wrote:
> how does one apply the JOIN... USING (...) syntax when
> the primary key and foreign key have different names?
> 
> JOIN t_employee USING (employee_id)
> 
> works perfect when the primary and foreign key are
> both named employee_id.

t_inspection JOIN t_employee
ON (t_employee.employee_id = t_inspection.inspector_id)


-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[SQL] permissions from system catalogs

[Kyle Bateman]

Can anyone point me where to look in the system catalogs (or information 
schema) to determine in advance whether a given user (or the current 
user) will have select permission to a given table or view?


Kyle


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] avg(interval)

[Aaron Bono]

Right, the 23 is just less than 1 day, not 23 days.The good news: your query is working!-AaronOn 6/26/06, Joe <
[EMAIL PROTECTED]> wrote:Tom Lane wrote:> "Jeremiah Elliott" <
[EMAIL PROTECTED]> writes:>> however if i don't average them here is what i get:>> "7 days 22:24:50.62311";"*2420">> "9 days 22:21:
02.683393";"*2420">> "23:21:35.458459";"*2420">> "4 days 22:47:41.749756";"*2420">> "3 days 06:05:59.456947";"*2420"
>>> which should average to just over nine days ->> Uh ... how do you arrive at that conclusion?  I haven't done the math,> but by eyeball an average of four-something days doesn't look out of
> line for those values.It seems he's calculating (7 + 9 + 23 + 4 + 3) / 5 ...Joe

Re: [SQL] permissions from system catalogs

[Tom Lane]

Kyle Bateman <[EMAIL PROTECTED]> writes:
> Can anyone point me where to look in the system catalogs (or information 
> schema) to determine in advance whether a given user (or the current 
> user) will have select permission to a given table or view?

Try has_table_privilege()

http://www.postgresql.org/docs/8.1/static/functions-info.html

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] permissions from system catalogs

[Michael Fuhr]

On Mon, Jun 26, 2006 at 05:30:12PM -0600, Kyle Bateman wrote:
> Can anyone point me where to look in the system catalogs (or information 
> schema) to determine in advance whether a given user (or the current 
> user) will have select permission to a given table or view?

An easy way is to use the has_table_privilege() function.  See also
information schema views whose names contain the word "grants" or
"privileges."

http://www.postgresql.org/docs/8.1/interactive/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE
http://www.postgresql.org/docs/8.1/interactive/information-schema.html

(Adjust the version number in the above links if you're using a
version other than 8.1)

-- 
Michael Fuhr

---(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] avg(interval)

[Erik Jones]

Well, the query is working ok numerically, but should the answer really 
be reported as 4 days and 33 hours?


Erik

Aaron Bono wrote:

Right, the 23 is just less than 1 day, not 23 days.

The good news: your query is working!

-Aaron

On 6/26/06, *Joe* < [EMAIL PROTECTED] 
> wrote:


Tom Lane wrote:
> "Jeremiah Elliott" < [EMAIL PROTECTED] >
writes:
>> however if i don't average them here is what i get:
>> "7 days 22:24:50.62311";"*2420"
>> "9 days 22:21: 02.683393";"*2420"
>> "23:21:35.458459";"*2420"
>> "4 days 22:47:41.749756";"*2420"
>> "3 days 06:05:59.456947";"*2420"
>
>> which should average to just over nine days -
>
> Uh ... how do you arrive at that conclusion?  I haven't done the
math,
> but by eyeball an average of four-something days doesn't look
out of
> line for those values.

It seems he's calculating (7 + 9 + 23 + 4 + 3) / 5 ...

Joe




--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] avg(interval)

[Joe]

Erik Jones wrote:
Well, the query is working ok numerically, but should the answer really 
be reported as 4 days and 33 hours?


Well, the original poster didn't provide the table schema or PG version, 
but on PG 8.0.3 both with intervals or with differences between 
timestamps, the query appears to work OK:


test=> select * from x;
t
-
 7 days 22:24:00
 9 days 22:21:00
 23:21:00
 4 days 22:47:00
 3 days 06:05:00
(5 rows)

test=> select avg(t) from x;
   avg
-
 5 days 09:47:36
(1 row)

test=> select * from x2;
  t
-
 2006-06-07 22:24:00
 2006-06-09 22:21:00
 2006-05-31 23:21:00
 2006-06-04 22:47:00
 2006-06-03 06:05:00
(5 rows)

test=> select avg(t - '2006-5-31 0:0'::timestamp) from x2;
   avg
-
 5 days 09:47:36
(1 row)

Joe

---(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