Hi
PgQ might be the answer for you. Each transaction shouöd push event into
queue and then you can write conusmers that notify each of the applications
that need to react to this.
Extract from documentation:
PgQ is Postgres based event processing system. It is part of SkyTools
package
that
I need to generate a diff (or something similar) of a table, day by day.
What is the best way to tack insert/update/delete operations? I have two
ideas, and I'd like to hear your opinion:
1) pg_dump each day and run diff
2) modify some triggers we use and store the information in another table
I
On Wed, Sep 12, 2007 at 01:56:13PM -0500, Josh Trutwin wrote:
Or would you have to compare each field in OLD, NEW to see if
anything actually changed?
you dont have to compare all columns (at least not in 8.2 and newer).
please take a look at
Cultural Sublimation wrote:
[has a problem because a SETOF RECORD function can return NULLs in
record attributes]
The client software obtains the type
information by querying Postgresql, namely by checking the attnotnull
column in the pg_attribute catalog.
On Thu, Sep 13, 2007 at 09:59:30AM +0200, Ottavio Campana wrote:
1) pg_dump each day and run diff
it will become increasingly painful as the table size increases.
2) modify some triggers we use and store the information in another table
this is the best choice. you can use table_log extension
Hi
PgQ can be used this purpose. Idea is to have triggers on table that push
events into queue and then on that queue you can do whatever suits you best.
As we don't want to keep these logs online PgQ is most conenient as it
efficiently removes them as soon as they are handled.
PgQ -
Hi list,
Le jeudi 13 septembre 2007, Andrew Hammond a écrit :
Does anyone know where I could find a tool which allows importing schema
information from a postgres database into visio? The boss guys want some
pretty pictures...
Druid[1] is somewhat capable of delivering nice pictures out of an
Ottavio Campana [EMAIL PROTECTED] writes:
I need to generate a diff (or something similar) of a table, day by day.
What is the best way to tack insert/update/delete operations? I have two
ideas, and I'd like to hear your opinion:
1) pg_dump each day and run diff
You can't use pg_dump
hubert depesz lubaczewski ha scritto:
On Thu, Sep 13, 2007 at 09:59:30AM +0200, Ottavio Campana wrote:
1) pg_dump each day and run diff
it will become increasingly painful as the table size increases.
2) modify some triggers we use and store the information in another table
this is the
am Thu, dem 13.09.2007, um 10:44:41 +0200 mailte Ottavio Campana folgendes:
http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
since I already use triggers on that table, can I use table_log?
I mean, can I have two triggers for the same event on
Dear list,
on my system I have multiple user.
I want that the user xy (who is no superuser) can Update a systemtable with:
UPDATE pg_catalog.pg_class SET reltriggers = 0;
With psql I already wrote:
ALTER TABLE pg_catalog.pg_class OWNER TO xy;
and
GRANT ALL PRIVILEGES ON pg_catalog.pg_class
am Thu, dem 13.09.2007, um 11:25:39 +0200 mailte Kai Behncke folgendes:
But always if I sent as user xy the
UPDATE pg_catalog.pg_class SET reltriggers = 0;-command I get:
SQL error:
ERROR: permission denied for relation pg_class
Why is that? MUST I be a superuser for that?
Write a
On Thu, Sep 13, 2007 at 11:25:39AM +0200, Kai Behncke wrote:
I want that the user xy (who is no superuser) can Update a systemtable with:
UPDATE pg_catalog.pg_class SET reltriggers = 0;
why dont you simply alter table disable trigger?
depesz
--
quicksil1er: postgres is excellent, but like
Hidepesz,
Original-Nachricht
Datum: Thu, 13 Sep 2007 12:25:51 +0200
Von: hubert depesz lubaczewski [EMAIL PROTECTED]
An: Kai Behncke [EMAIL PROTECTED]
CC: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??
On Thu, Sep 13, 2007
On Thu, Sep 13, 2007 at 01:06:11PM +0200, Kai Behncke wrote:
Could you give me an example for that please?
Thank you very much :-), Kai
i think i gave.
ok. again:
alter table some_table disable trigger all;
depesz
--
quicksil1er: postgres is excellent, but like any DB it requires a
highly
am Thu, dem 13.09.2007, um 13:06:11 +0200 mailte Kai Behncke folgendes:
why dont you simply alter table disable trigger?
depesz
Could you give me an example for that please?
Thank you very much :-), Kai
Open psql and type: \h alter table
test=*# \h alter table
Command: ALTER
Unfortunately for you, they are not different types. If the OCaml
binding thinks they are, it's the binding's problem; especially since
the binding seems to be using a completely lame method of trying to tell
the difference.
Hi,
In OCaml and in other languages with strong type systems, int4
Hi,
The thing behind the RETURNS in a function is always a data type,
regardless if it is one that has been explicitly declared with
CREATE TYPE or implicitly by CREATE TABLE.
There are no NOT NULL conditions for data types.
NOT NULL only exists for table columns.
Thanks for the
$curr_yr = -1
$cols = array();
while (fetch rows) {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols) 0) {
On Thu, Sep 13, 2007 at 05:02:10AM -0700, Cultural Sublimation wrote:
In OCaml and in other languages with strong type systems, int4 never NULL
and int4 possibly NULL are definitely different types. I think the source
of the problem here is that SQL has a different philosophy, one where type
Stefan Schwarzer wrote:
$curr_yr = -1
$cols = array();
while (fetch rows) {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if
$curr_yr = -1
$cols = array();
while (fetch rows) {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols) 0) {
Stefan Schwarzer wrote:
$curr_yr = -1
$cols = array();
while (fetch rows) {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if
Umm - not sure what you're after. What's wrong with one of:
SELECT ... ORDER BY year, value
SELECT ... ORDER BY value, year
Or did you want a particular year pulled out of the general list,
in which case try something like:
SELECT ... ORDER BY (year = 1970), year, value
SELECT ...
Stefan Schwarzer wrote:
Umm - not sure what you're after. What's wrong with one of:
SELECT ... ORDER BY year, value
SELECT ... ORDER BY value, year
Or did you want a particular year pulled out of the general list, in
which case try something like:
SELECT ... ORDER BY (year = 1970),
Martijn van Oosterhout [EMAIL PROTECTED] writes:
Firstly, the output of most queries is of a type not represented
anywhere in the catalogs. It's mostly going to be an undeclared record
whose members are listed in pg_type. So using pg_attribute for anything
like this is probably completely
Uiuiui and it gets even worse... I want to implement the
possibility to calculate on-the-fly the per Capita values for the
selected data set. With the old table design it would be
something like this:
SELECT (fish_catch.y_1970 / pop_total.y_1970),
(fish_catch.y_1971 /
Stefan Schwarzer wrote:
Just for the completeness, I attach the final working SQL query:
SELECT
f.year,
f.id,
c.name,
(f.value / p.value) AS per_capita
FROM
fish_catch AS f
JOIN
pop_total AS p
USING
(year, id)
INNER JOIN
countries AS c ON f.id = c.id
ORDER BY
year
Make
Of course you should really have a data model that knows what it
wants to sort by and constructs the query appropriately. The table-
drawing code can then ask the data-model for heading-names and sort-
order details. It's more work up-front, but you only have to do it
once and then you can
hello
table is
+---+---+--+---+
| id | one | two | three |
+---+---+--+---+
| first | Jack | Jill | Mary |
| last | Ja | Ji | Ma |
+---+---+--+---+
result is
++---+---+
| id | one | two |
++---+---+
| first
Stefan Schwarzer wrote:
Of course you should really have a data model that knows what it wants
to sort by and constructs the query appropriately. The table-drawing
code can then ask the data-model for heading-names and sort-order
details. It's more work up-front, but you only have to do it
On 9/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
hello
table is
+---+---+--+---+
| id | one | two | three |
+---+---+--+---+
| first | Jack | Jill | Mary |
| last | Ja | Ji | Ma |
+---+---+--+---+
result is
On 9/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
query is??
http://www.w3schools.com/sql/default.asp
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL
hello
i add more column not row for new user. i want all last like 'J%'.
http://www.nabble.com/an-other-provokative-question---tf4394285.html
sincerely
siva
Original Message
Subject: Re: [GENERAL] query help
From: Alexander Staubo [EMAIL PROTECTED]
Date: Thu, September 13, 2007
On 9/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
i add more column not row for new user. i want all last like 'J%'.
http://www.nabble.com/an-other-provokative-question---tf4394285.html
Sorry, but the only difference between your table example and your
result example was the absence, in
[EMAIL PROTECTED] wrote:
hello
i add more column not row for new user. i want all last like 'J%'.
http://www.nabble.com/an-other-provokative-question---tf4394285.html
sincerely
siva
You add a new _column_ for each user?!? That is hideously broken in so
many ways. It makes the trivially easy
many apologees. right link
http://archives.postgresql.org/pgsql-general/2007-09/msg00607.php
i flip row to column if ok. but howto query??
sincerely
siva
Original Message
Subject: Re: [GENERAL] query help
From: Alexander Staubo [EMAIL PROTECTED]
Date: Thu, September 13, 2007
[EMAIL PROTECTED] wrote:
hello
i add more column not row for new user. i want all last like 'J%'.
I get the feeling that the result as you've laid it out is not what we
all think it is. For example:
table is
+---+---+--+---+
| id | one | two | three |
can u refer to row?? howto select * from table where row(#2) like 'J%'??
i wanted to test column storing but not ok as no row refer name/id.
many thank yous
sincerely
siva
Original Message
Subject: Re: [GENERAL] query help
From: [EMAIL PROTECTED]
Date: Thu, September 13, 2007
Depesz,
Thank you for the suggestion- I thought I had read up on that tool
earlier but had somehow managed to forget about it when starting this
phase of my investigation.
Needless to say, I can confirm the claims made on the project homepage
when using very large data sets.
- Loading
Cultural Sublimation wrote:
Unfortunately for you, they are not different types. If the OCaml
binding thinks they are, it's the binding's problem; especially since
the binding seems to be using a completely lame method of trying to tell
the difference.
Hi,
In OCaml and in other
Hey guys,
maybe anyone using tsearch2 could advise on this. With the default
installation, url, host and some other tokens are processed with the simple
dictionary. Thus term like mywebsite.com gets stored as 'mywebsite.com'. The
parser correctly assigns token id of type host to the term, but
I think it would be useful if pg_standby (in version 8.3 contrib) could
be observed in some way.
Right now I use my own standby script, because every time it runs, it
touches a file in a known location. That allows me to monitor that file,
and if it is too stale, I know something must have gone
Hi all,
does anybody know where to find a good list of PostgreSQL terms
(including both traditional terms and Postgres-specific ones)? At
least a simple list w/o descriptions...
Google didn't help me yet :-\
--
Best regards,
Nikolay
---(end of
On Thu, 13 Sep 2007, Laimonas Simutis wrote:
Hey guys,
maybe anyone using tsearch2 could advise on this. With the default
installation, url, host and some other tokens are processed with the simple
dictionary. Thus term like mywebsite.com gets stored as 'mywebsite.com'. The
parser correctly
On Sep 13, 2007, at 1:38 PM, Jeff Davis wrote:
I think it would be useful if pg_standby (in version 8.3 contrib)
could
be observed in some way.
Right now I use my own standby script, because every time it runs, it
touches a file in a known location. That allows me to monitor that
file,
and
On Thu, 2007-09-13 at 14:05 -0500, Erik Jones wrote:
If you include the -d option pg_standby will emit logging info on
stderr so you can tack on something like 2 logpath/standby.log.
What it is lacking, however, is timestamps in the output when it
successfully recovers a WAL file. Was
On Sep 13, 2007, at 3:02 PM, Jeff Davis wrote:
On Thu, 2007-09-13 at 14:05 -0500, Erik Jones wrote:
If you include the -d option pg_standby will emit logging info on
stderr so you can tack on something like 2 logpath/standby.log.
What it is lacking, however, is timestamps in the output when
Any way to install the dictionary without the make? As in is there binary
versions of it available? I am running postgresql on windows servers...
On 9/13/07, Oleg Bartunov [EMAIL PROTECTED] wrote:
On Thu, 13 Sep 2007, Laimonas Simutis wrote:
Hey guys,
maybe anyone using tsearch2 could
When restoring a dump, I get the following error:
ERROR: could not make operator class gin__int_ops be default for type
pg_catalog.int4[]
DETAIL: Operator class _int4_ops already is the default.
I believe the problem lies with:
CREATE OPERATOR CLASS gin__int_ops
DEFAULT FOR TYPE
Alvaro Herrera wrote:
Bruce Momjian wrote:
Is this item closed?
No, it isn't. Please add a TODO item about it:
* Prevent long-lived temp tables from causing frozen-Xid advancement
starvation
Sorry, I don't understand this. Can you give me more text? Thanks.
--
Bruce Momjian
Trevor Talbot wrote:
Unless psql is turning on keepalive or similar, or the OS is forcing
it on by default, there are no timeouts for idle TCP connections. If
the command was transported to the server successfully and psql was
just waiting for a result, the connection is idle and nothing
On 9/13/07, Bruce Momjian [EMAIL PROTECTED] wrote:
Alvaro Herrera wrote:
Bruce Momjian wrote:
Is this item closed?
No, it isn't. Please add a TODO item about it:
* Prevent long-lived temp tables from causing frozen-Xid advancement
starvation
Sorry, I don't understand this.
On 9/7/2007 11:45 AM, Tom Lane wrote:
Zoltan Boszormenyi [EMAIL PROTECTED] writes:
Tom Lane =EDrta:
Zoltan Boszormenyi [EMAIL PROTECTED] writes:
At the end of the day, the behaviour is the same, isn't it?
No, there's a difference in terms of the priority for pushing this
column out to toast
Carlo Stonebanks [EMAIL PROTECTED] writes:
When restoring a dump, I get the following error:
ERROR: could not make operator class gin__int_ops be default for type
pg_catalog.int4[]
DETAIL: Operator class _int4_ops already is the default.
This is a dup of bug #3048. I see that that still
This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---
Magnus Hagander wrote:
Shelby Cain wrote:
- Original Message From: Magnus Hagander
[EMAIL PROTECTED]
For recent postgres releases, is there any effective difference
(performance/memory/io) between:
create temp table foo as select * from bar where bar.date '2007-01-01';
copy foo to '/tmp/bar.out';
drop table temp;
and this:
copy ( select * from bar where bar.date '2007-01-01' ) to
Jason L. Buberel [EMAIL PROTECTED] writes:
For recent postgres releases, is there any effective difference
(performance/memory/io) between:
create temp table foo as select * from bar where bar.date '2007-01-01';
copy foo to '/tmp/bar.out';
drop table temp;
and this:
copy ( select *
58 matches
Mail list logo