Re: [GENERAL] Preferred usage for 'copy to' for a subset of data

2007-09-13 Thread Tom Lane
"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 ( s

[GENERAL] Preferred usage for 'copy to' for a subset of data

2007-09-13 Thread Jason L. Buberel
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 '/tmp/ba

Re: [GENERAL] FATAL: could not reattach to shared memory (Win32)

2007-09-13 Thread Bruce Momjian
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 PROTE

Re: [GENERAL] 8.2.4 error restoring dump because of gin__int_ops

2007-09-13 Thread Tom Lane
"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 t

Re: [GENERAL] What's the difference between SET STORAGE MAIN and EXTENDED?

2007-09-13 Thread Jan Wieck
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 to

Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question

2007-09-13 Thread Andrew Hammond
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 d

Re: [GENERAL] psql hanging

2007-09-13 Thread Steve Crawford
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

Re: [GENERAL] AutoVacuum Behaviour Question

2007-09-13 Thread Bruce Momjian
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

[GENERAL] 8.2.4 error restoring dump because of gin__int_ops

2007-09-13 Thread Carlo Stonebanks
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 inte

Re: [GENERAL] processing urls with tsearch2

2007-09-13 Thread Laimonas Simutis
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 tsearch

Re: [GENERAL] pg_standby observation

2007-09-13 Thread Erik Jones
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

Re: [GENERAL] pg_standby observation

2007-09-13 Thread Jeff Davis
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.

Re: [GENERAL] pg_standby observation

2007-09-13 Thread Erik Jones
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

Re: [GENERAL] processing urls with tsearch2

2007-09-13 Thread Oleg Bartunov
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 ass

[GENERAL] PostgreSQL Glossary?

2007-09-13 Thread Nikolay Samokhvalov
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 broadcast)

[GENERAL] pg_standby observation

2007-09-13 Thread Jeff Davis
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 wr

[GENERAL] processing urls with tsearch2

2007-09-13 Thread Laimonas Simutis
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 then

Re: [GENERAL] Cannot declare record members NOT NULL

2007-09-13 Thread Marco Colombo
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 oth

Re: [GENERAL] Alternative to drop index, load data, recreate index?

2007-09-13 Thread Jason L. Buberel
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 1.2M

Re: [GENERAL] query help

2007-09-13 Thread volunteer
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 11:

Re: [GENERAL] query help

2007-09-13 Thread brian
[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 | >>+---+-

Re: [GENERAL] query help

2007-09-13 Thread volunteer
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

Re: [GENERAL] query help

2007-09-13 Thread Steve Crawford
[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

Re: [GENERAL] query help

2007-09-13 Thread Alexander Staubo
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,

Re: [GENERAL] query help

2007-09-13 Thread volunteer
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,

Re: [GENERAL] query help

2007-09-13 Thread Rodrigo De León
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 PR

Re: [GENERAL] query help

2007-09-13 Thread Alexander Staubo
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 > +

Re: [GENERAL] Data Model - Linking to PHP Code - Literature

2007-09-13 Thread Richard Huxton
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

[GENERAL] query help

2007-09-13 Thread volunteer
hello table is +---+---+--+---+ | id | one | two | three | +---+---+--+---+ | first | Jack | Jill | Mary | | last | Ja | Ji | Ma | +---+---+--+---+ result is ++---+---+ | id | one | two | ++---+---+ | first

[GENERAL] Data Model - Linking to PHP Code - Literature

2007-09-13 Thread Stefan Schwarzer
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

Re: [GENERAL] Database/Table Design for Global Country Statistics

2007-09-13 Thread Richard Huxton
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 sur

Re: [GENERAL] Database/Table Design for Global Country Statistics

2007-09-13 Thread Stefan Schwarzer
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 / pop_tota

Re: [GENERAL] Cannot declare record members NOT NULL

2007-09-13 Thread Tom Lane
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

Re: [GENERAL] Database/Table Design for Global Country Statistics

2007-09-13 Thread Richard Huxton
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), yea

Re: [GENERAL] Database/Table Design for Global Country Statistics

2007-09-13 Thread Stefan Schwarzer
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 ... OR

Re: [GENERAL] Database/Table Design for Global Country Statistics

2007-09-13 Thread Richard Huxton
Stefan Schwarzer wrote: $curr_yr = -1 $cols = array(); while () { 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

Re: [GENERAL] Database/Table Design for Global Country Statistics

2007-09-13 Thread Stefan Schwarzer
$curr_yr = -1 $cols = array(); while () { 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) { display_table_ro

Re: [GENERAL] Database/Table Design for Global Country Statistics

2007-09-13 Thread Richard Huxton
Stefan Schwarzer wrote: $curr_yr = -1 $cols = array(); while () { 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)

Re: [GENERAL] Cannot declare record members NOT NULL

2007-09-13 Thread Martijn van Oosterhout
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

Re: [GENERAL] Database/Table Design for Global Country Statistics

2007-09-13 Thread Stefan Schwarzer
$curr_yr = -1 $cols = array(); while () { 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) { display_table_row(

Re: [GENERAL] Cannot declare record members NOT NULL

2007-09-13 Thread Cultural Sublimation
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 i

Re: [GENERAL] Cannot declare record members NOT NULL

2007-09-13 Thread Cultural Sublimation
> 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, "

Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??

2007-09-13 Thread A. Kretschmer
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:

Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??

2007-09-13 Thread hubert depesz lubaczewski
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

Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??

2007-09-13 Thread Kai Behncke
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

Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??

2007-09-13 Thread hubert depesz lubaczewski
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

Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??

2007-09-13 Thread A. Kretschmer
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?

[GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??

2007-09-13 Thread Kai Behncke
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 T

Re: [GENERAL] get a list of table modifications in a day?

2007-09-13 Thread A. Kretschmer
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 even

Re: [GENERAL] get a list of table modifications in a day?

2007-09-13 Thread Ottavio Campana
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 > > thi

Re: [GENERAL] get a list of table modifications in a day?

2007-09-13 Thread Gregory Stark
"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_du

Re: [GENERAL] importing pgsql schema into visio (for diagramming)

2007-09-13 Thread Dimitri Fontaine
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

Re: [GENERAL] get a list of table modifications in a day?

2007-09-13 Thread Asko Oja
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 - table_dispat

Re: [GENERAL] Cannot declare record members NOT NULL

2007-09-13 Thread Albe Laurenz
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. Theref

Re: [GENERAL] get a list of table modifications in a day?

2007-09-13 Thread hubert depesz lubaczewski
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 extensio

Re: [GENERAL] ON UPDATE trigger question

2007-09-13 Thread hubert depesz lubaczewski
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 http://www.depesz.com/index.php/2007/09/08/avoiding

[GENERAL] get a list of table modifications in a day?

2007-09-13 Thread Ottavio Campana
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