Re: [GENERAL] Delete Question

[A.j. Langereis]

Postgresql supports records in the where clause i.e. you can compare
multiple columns simultaneously:

> test=# delete from change where id || ':' || datum not in (select id ||
':' || max(datum) from change group by id order by 1);

could therefore be rewritten to:

delete from change where (id, datum) in (select id, max(datum) from change
group by id);

Yours,

Aarjan Langereis

- Original Message -
From: "A. Kretschmer" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, December 07, 2005 8:47 AM
Subject: Re: [GENERAL] Delete Question


> am  07.12.2005, um 18:21:25 +1100 mailte Alex folgendes:
> > Hi,
> >
> > I have a table where I store changes made to an order. The looks like
> > ProdID, ChangeDate, Change1, Change2, ... etc.
> > Some ProdIDs have multiple records.
> >
> > Is there an easy way to delete all records of a ProdID except the most
> > recent (ChangeDate is timestamp) one? Preferably in one SQL statement?
>
> test=# select * from change ;
>  id |   datum|   text
> ++--
>   1 | 2005-12-07 08:28:28.939312 | foo
>   1 | 2005-12-07 08:28:34.695091 | foo2
>   1 | 2005-12-07 08:28:37.150354 | foo3
>   1 | 2005-12-07 08:28:43.263171 | foo_last
>   2 | 2005-12-07 08:28:48.419252 | foo
>   2 | 2005-12-07 08:28:55.819969 | foo_last
> (6 rows)
>
> test=# begin;
> BEGIN
> test=# delete from change where id || ':' || datum not in (select id ||
':' || max(datum) from change group by id order by 1);
> DELETE 4
> test=# select * from change ;
>  id |   datum|   text
> ++--
>   1 | 2005-12-07 08:28:43.263171 | foo_last
>   2 | 2005-12-07 08:28:55.819969 | foo_last
> (2 rows)
>
>
> But i'm not sure if this works correctly for you.
>
>
>
> HTH, Andreas
> --
> Andreas Kretschmer(Kontakt: siehe Header)
> Heynitz:  035242/47212,  D1: 0160/7141639
> GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
>  ===Schollglas Unternehmensgruppe===
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
>



---(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: [GENERAL] Delete Question

[Pandurangan R S]

DELECT FROM table
WHERE (ProdID,ChangeDate) not in
(SELECT ProdID,MAX(ChangeDate) FROM table
 GROUP BY ProdID)

I hope this works.

On 12/7/05, Alex <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have a table where I store changes made to an order. The looks like
> ProdID, ChangeDate, Change1, Change2, ... etc.
> Some ProdIDs have multiple records.
>
> Is there an easy way to delete all records of a ProdID except the most
> recent (ChangeDate is timestamp) one? Preferably in one SQL statement?
>
> Thanks for any advise
>
> Alex
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>


--
Regards
Pandu

---(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: [GENERAL] Delete Question

[Alex]

Thanks ,
this one works beautifully.

Alex

PS: also thanks to the other suggestions, have a look at this one


hubert depesz lubaczewski wrote:

On 12/7/05, *Alex* <[EMAIL PROTECTED] 
> wrote:


I have a table where I store changes made to an order. The looks like
ProdID, ChangeDate, Change1, Change2, ... etc.
Some ProdIDs have multiple records.
Is there an easy way to delete all records of a ProdID except the
most
recent (ChangeDate is timestamp) one? Preferably in one SQL statement?


delete from table_name where exists (select * from table_name x where 
x.prodid = table_name.prodid and x.changedate > table_name.changedate);


this should work.

depesz



---(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: [GENERAL] Delete Question

[A. Kretschmer]

am  07.12.2005, um 18:21:25 +1100 mailte Alex folgendes:
> Hi,
> 
> I have a table where I store changes made to an order. The looks like
> ProdID, ChangeDate, Change1, Change2, ... etc.
> Some ProdIDs have multiple records.
> 
> Is there an easy way to delete all records of a ProdID except the most 
> recent (ChangeDate is timestamp) one? Preferably in one SQL statement?

test=# select * from change ;
 id |   datum|   text
++--
  1 | 2005-12-07 08:28:28.939312 | foo
  1 | 2005-12-07 08:28:34.695091 | foo2
  1 | 2005-12-07 08:28:37.150354 | foo3
  1 | 2005-12-07 08:28:43.263171 | foo_last
  2 | 2005-12-07 08:28:48.419252 | foo
  2 | 2005-12-07 08:28:55.819969 | foo_last
(6 rows)

test=# begin;
BEGIN
test=# delete from change where id || ':' || datum not in (select id || ':' || 
max(datum) from change group by id order by 1);
DELETE 4
test=# select * from change ;
 id |   datum|   text
++--
  1 | 2005-12-07 08:28:43.263171 | foo_last
  2 | 2005-12-07 08:28:55.819969 | foo_last
(2 rows)


But i'm not sure if this works correctly for you.



HTH, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47212,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

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

[GENERAL] tables with lots of columns - what alternative from performance point of view?

[hubert depesz lubaczewski]

hi
jus recently there were some thread on postgresql list with people asying : i have 700 columns, i have 1000 columns and so on.
some people, imediatelly responded: change your schema.
this is what forced to me ask:

i have a situation where i ahve to store a number of "objects" in database.
all objects have 3 specific attributes (which go into objects table), and may have a lot of "custom fields".
basically - lsit of accessible custom fields for object depends on which object-category this object belongs to.
now.
i know, i could have written it in this way:

create table object_custom_fields (id serial primary key, object_id int8, field_id int8, field_value text);
but:
this approach has two very big drawbacks (for me):
1. the table cannot differentiate between custom fields of type "date", "number" and so on. - everything is stored as text.
2. it is rather slow. i have to do a non-unique index scan over
object_custom_fields, get all records, and pivot it (on the client side
of curse) to make it usable.

i did it differently, definitelly not nicely, but i dont see any other
way to get this performance with unknown list of custom fields:
1. create table cf_types (id serial, codename text, representation text);
2. create table cf_definitions (id serial, category_id int8, type_id int8, field-number int4);
3. create table cf_values (id serial, object_id int8 (unique), ...);

where
cf_types store information like this:
 id |  codename  | representation 
++
  1 | bool   | boolean
  2 | integer    | integer
  3 | number | number
  4 | text   | text
  5 | note   | text
  6 | date   | date
...
basically - there might be many "types" with the same representation.
then
cf_values have a lot of (128 at the moment) fields for all possible representations.
basically it looks like:
id, object_id, boolean_1 ... boolean_128, integer_1..integer_128, ...
the datatypes of this fields relate to their content (integer_* fields have datatype int8, and so on).

now.
in cf_definitions i specify, category, field_type_id, and a field-number - which relates to _NUMBER in fields in cf_values.

what i did achive is *very* fast retrieval of data for any given object.
the schema of cf_values table is absolutelly awful, and i will never say differently.
my point is - if somebody (tom lane for example) says - redesign your
schema - whenever he reads about table with 700 column (i have more :)
- then i must have missed something absolutelyl simple, fast and
elegant. what is this?

depesz

Re: [SQL] [GENERAL] lost in system tables

[Emil Rachovsky]

> Those parameters are specified when you declare the
> foreign key.  Look here, 
> in the section describing "references":
>
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
> 
> Luca

Thank you,Luca
I know that these parameters are specified when you
declare the foreign key, but i need to check them out
later from the system tables. The first one must be
something like the 'deferrable' column in
pg_constaint, but the second is still in vague.

Emil



__ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 


---(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: [GENERAL] Delete Question

[Michael Glaesemann]

On Dec 7, 2005, at 16:21 , Alex wrote:

Is there an easy way to delete all records of a ProdID except the  
most recent (ChangeDate is timestamp) one? Preferably in one SQL  
statement?


Here's one way to do it, though not it one SQL statement:

create table copy_of_original_table as
select distinct on ("ProdID") "ProdID", ...
from original_table
order by "ChangeDate" desc;

truncate original_table;

insert into original_table ("ProdID", ... )
select "ProdID", ...
from copy_of_original_table;

Note you need to quote the column names if case is important.

Michael Glaesemann
grzm myrealbox com




---(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: [GENERAL] Delete Question

[hubert depesz lubaczewski]

On 12/7/05, Alex <[EMAIL PROTECTED]> wrote:
I have a table where I store changes made to an order. The looks likeProdID, ChangeDate, Change1, Change2, ... etc.Some ProdIDs have multiple records.Is there an easy way to delete all records of a ProdID except the most
recent (ChangeDate is timestamp) one? Preferably in one SQL statement?
delete from table_name where exists (select * from table_name x where
x.prodid = table_name.prodid and x.changedate >
table_name.changedate);

this should work.

depesz

Re: [GENERAL] [SQL] lost in system tables

[Emil Rachovsky]

 Thank you,Tom,
As for the description of 'nulls' I have taken it as
it is from the Sybase help file :) 





__ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 


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

[GENERAL] Delete Question

[Alex]

Hi,

I have a table where I store changes made to an order. The looks like
ProdID, ChangeDate, Change1, Change2, ... etc.
Some ProdIDs have multiple records.

Is there an easy way to delete all records of a ProdID except the most 
recent (ChangeDate is timestamp) one? Preferably in one SQL statement?


Thanks for any advise

Alex

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

Re: [GENERAL] Letting a function return multiple columns instead of a single complex one

[A.j. Langereis]

> test=# select c,(ff).* from (select bar.*,get_a_foo(c) as ff from bar) b;
>  c | a | b
> ---+---+---
>  1 | 1 | 2
> (1 row)
>

Tanks! that works great! It managed to get it even a bit more simplified:
select bar.*, (get_a_foo(c)).* from bar;

> Not amazingly elegant, but it works.  Note that you need to beware of
> the possibility that the subselect will get flattened, leading to
> multiple evaluations of your function.  This doesn't happen in this
> particular case because you declared the function as returning set,
> but if you don't then you'll need additional countermeasures.
>
> In general I'd suggest that this style of programming is forcing SQL to
> do something SQL doesn't do very well, ie, emulate a functional
> language.  It's likely to end up both notationally ugly and very
> inefficient.  You should think hard about whether you can't express your
> problem with views and joins instead.
>

The reason that I need this is because of my other question (is there in pg
a function like oracle's rownum?). The function get_a_foo looks in reality a
bit more like the next:

create type foo_extended as (a int, b int, rowno int);

create or replace function get_a_foo_func(int)
 returns setof foo_extended as
'
declare
tmp_row foo_extended%rowtype;
i int;
begin
i := 1;

for tmp_row in (select * from foo where a = $1) loop
 tmp_row.rowno := i;
 return next tmp_row;
 i := i + 1;
end loop;

end;
'
language plpgsql volatile;

create or replace function get_a_foo(int)
 returns setof foo_extended as
'
select * from get_a_foo_func($1);
'
language sql volatile;

The function get_a_foo_func runs a query and adds to each row of the result
a rownum like number. The other, wrapper, function is to make it possible to
give a set as an imput parameter: unfortunately this is something that
doesn't seem to be supported by pl/pgsql.

Yours,

Aarjan Langereis



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

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

Re: [GENERAL] ltree patch is available

[Bruce Momjian]

I assume Teodor just applied this patch to the proper branches.

---

Oleg Bartunov wrote:
> Hi there,
> 
> Patch for ltree (all releases since 7.3) is available 
> http://www.sai.msu.su/~megera/postgres/gist/patches/_ltree.patch
> and from CVS. It fixes problem with unoptimal tree construction and big size
> of index. Thanks Lexa Tutubalin for test suite and persistency.
> 
>   Regards,
>   Oleg
> _
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [GENERAL] Letting a function return multiple columns instead of a single complex one

[Tom Lane]

"A.j. Langereis" <[EMAIL PROTECTED]> writes:
> The problem I am facing is that I will execute this function as part of =
> another query where the parameter will be one of the columns of another =
> table. Something like: "select bar.*, get_a_foo(c) from bar". I need the =
> result set to be like a table, because I'll have to use it later in =
> another query.

Try something like

test=# select c,(ff).* from (select bar.*,get_a_foo(c) as ff from bar) b;
 c | a | b
---+---+---
 1 | 1 | 2
(1 row)

Not amazingly elegant, but it works.  Note that you need to beware of
the possibility that the subselect will get flattened, leading to
multiple evaluations of your function.  This doesn't happen in this
particular case because you declared the function as returning set,
but if you don't then you'll need additional countermeasures.

In general I'd suggest that this style of programming is forcing SQL to
do something SQL doesn't do very well, ie, emulate a functional
language.  It's likely to end up both notationally ugly and very
inefficient.  You should think hard about whether you can't express your
problem with views and joins instead.

regards, tom lane

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

[GENERAL] Letting a function return multiple columns instead of a single complex one

[A.j. Langereis]

Dear all,
 
I have two questions: fist of all, is there any 
function in pg like oracle's rownum?
 
secondly, I wonder how it is possible to let a 
function return a dataset with different columns instead of a single, 
complex, one.
 
create table foo (a int, b int);
 
insert into foo (a,b) values (1,2);insert into foo (a,b) values 
(2,3);
 
create or replace function get_a_foo(a int) returns setof foo 
as$$select * from foo where a = $1;$$language sql 
volatile;
 
something like "select get_a_foo(1);" would return:
 get_a_foo--- (1,2)(1 row)
 
whereas "select * from get_a_foo(1);" will retunr:
 a | b---+--- 1 | 2(1 row)
The problem I am facing is that I will execute this function as part of 
another query where the parameter will be one of the columns of another table. 
Something like: "select bar.*, get_a_foo(c) from bar". I need the result set to 
be like a table, because I'll have to use it later in another query.
The whole construction works fine if there would be only one column in the 
resultset of the query, something that is not the case here.
 
Anyone any suggestion?
 
Yours Aarjan

Re: [GENERAL] deadlock on the same relation

[Jim C. Nasby]

On Fri, Dec 02, 2005 at 10:15:04AM -0500, Tom Lane wrote:
> "Francesco Formenti - TVBLOB S.r.l." <[EMAIL PROTECTED]> writes:
> > I have a problem about deadlock. I have several stored procedures; only 
> > one of them uses ACCESS EXCLUSIVE LOCK on a table; however, all the 
> > stored procedures can access to that table, using SELECT, INSERT or UPDATE.
> > The stored procedures are called by different processes of an external 
> > application.
> 
> > In a non-predictable way, I obtain error messages like this one:
> 
> > 2005-11-29 18:23:06 [12771] ERROR:  deadlock detected
> > DETAIL:  Process 12771 waits for AccessExclusiveLock on relation 26052 
> > of database 17142; blocked by process 12773.
> > Process 12773 waits for AccessExclusiveLock on relation 26052 of 
> > database 17142; blocked by process 12771.
> > CONTEXT:  PL/pgSQL function "set_session_box_status" line 7 at SQL statement
> 
> Probably you have been careless about avoiding "lock upgrade"
> situations.  If you are going to take an exclusive lock on a relation,
> it is dangerous to already hold a non-exclusive lock on the same
> relation, because that prevents anyone else from getting an exclusive
> lock; thus if another process is doing the exact same thing you are in
> a deadlock situation.
> 
> Since SELECT/INSERT/UPDATE take non-exclusive locks, you can't do one of
> those and later ask for exclusive lock within the same transaction.
> The general rule is "get the strongest lock you will need first".

And better yet, don't grab an exclusive lock...

I'm curious; what are you doing that requires one?
-- 
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 3: Have you checked our extensive FAQ?

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

Re: [GENERAL] Graphical modelling tool

[Carlos Correia]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thomas Kellerer wrote:
| Carlos Correia wrote on 06.12.2005 21:13:
|
|> -BEGIN PGP SIGNED MESSAGE-
|> Hash: SHA1
|>
|> Thomas Hallgren wrote:
|> | Hi,
|> | I'm about to start a new project where the first task is to design a
|> | database. I'm looking for some tool that will allow me to model the
|> | tables and relationships graphically, UML or similar, and then let me
|> | generate the SQL with PostgreSQL flavor. What's the best tools out
|> | there? Are there any open source alternatives?
|> |
|> | Kind regards,
|> | Thomas Hallgren
|> |
|> |
|> | ---(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
|> |
|>
|> Hi,
|>
|> Perhaps, you'll like this one:
|> http://www.memoriapersistente.pt/en/opensource/gaudi/index.html
|>
|> Regards,
|>
|
| I tried to run this (under Windows) but after selecting the database I
| always get the following exception:
|
| Exception in thread "main" java.lang.ExceptionInInitializerError
| at
| com.m16e.free.tools.xgui.XGuiFactory.readLayout(XGuiFactory.java:1152)
| at com.m16e.dbed.DbEditor.showFrame(DbEditor.java:254)
| at com.m16e.dbed.DbEditor.main(DbEditor.java:400)
| Caused by: java.util.MissingResourceException: Can't find bundle for
| base name i18n/xgui-locale, locale de_DE
| at
|
java.util.ResourceBundle.throwMissingResourceException(ResourceBundle.java:839)

|
| at java.util.ResourceBundle.getBundleImpl(ResourceBundle.java:808)
| at java.util.ResourceBundle.getBundle(ResourceBundle.java:552)
| at
| com.m16e.free.tools.i18n.MpBundle.setBundleFile(MpBundle.java:35)
| at com.m16e.free.tools.xgui.XGuiBundle.(XGuiBundle.java:62)
| at
com.m16e.free.tools.xgui.XGuiBundle.(XGuiBundle.java:57)
| ... 3 more
|
|
| I fiddled around with the locale settings in dbeditor.properties but to
| no avail. I even created a file i18n/xgui-locale.properties (and one
| xgui-locale_de.properties) but to no avail.
|
| What am I missing here?
|
| Regards
| Thomas
|
|
| ---(end of broadcast)---
| TIP 2: Don't 'kill -9' the postmaster
|

It is really missing that file (i18n/xgui-locale.properties) from the
tarball, my apologies for that.

Just create one with the following 2 lines:

msg-null-file-layout=Unknown layout file: {0}\!
msg-file-not-found=Error opening file: {0}\!

Most probably, next you will get another error, stating that tha program
could not find a layout file, because of CLASSPATH issues.

If that's the case, I suggest you send a post to Gaudí's mailing list
(http://lists.sourceforge.net/lists/listinfo/gaudi-users) and continue
the thread in there.

Just one tip: it seems you have not installed the application in the
default directory, if that's the case you should look in the manual for
configuration setup.

Regards,

Carlos
- --
MEMÓRIA PERSISTENTE, Lda.
Tel.: 219 291 591 - GSM:  967 511 762
e-mail: [EMAIL PROTECTED] - URL: http://www.m16e.com
AIM: m16e - ICQ: 257488263 - Jabber: [EMAIL PROTECTED]
Skype.com username (VoIP): m16e.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDlhZj90uzwjA1SJURAiHQAJ41IfxNGxi4vtn12C60qjfT+uD1IQCgk1OI
JjkjnA9gND/rJjELq4PL3Qc=
=XYZt
-END PGP SIGNATURE-

---(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: [GENERAL] Graphical modelling tool

[Thomas Kellerer]

Carlos Correia wrote on 06.12.2005 21:13:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thomas Hallgren wrote:
| Hi,
| I'm about to start a new project where the first task is to design a
| database. I'm looking for some tool that will allow me to model the
| tables and relationships graphically, UML or similar, and then let me
| generate the SQL with PostgreSQL flavor. What's the best tools out
| there? Are there any open source alternatives?
|
| Kind regards,
| Thomas Hallgren
|
|
| ---(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
|

Hi,

Perhaps, you'll like this one:
http://www.memoriapersistente.pt/en/opensource/gaudi/index.html

Regards,



I tried to run this (under Windows) but after selecting the database I 
always get the following exception:


Exception in thread "main" java.lang.ExceptionInInitializerError
at 
com.m16e.free.tools.xgui.XGuiFactory.readLayout(XGuiFactory.java:1152)

at com.m16e.dbed.DbEditor.showFrame(DbEditor.java:254)
at com.m16e.dbed.DbEditor.main(DbEditor.java:400)
Caused by: java.util.MissingResourceException: Can't find bundle for 
base name i18n/xgui-locale, locale de_DE
at 
java.util.ResourceBundle.throwMissingResourceException(ResourceBundle.java:839)

at java.util.ResourceBundle.getBundleImpl(ResourceBundle.java:808)
at java.util.ResourceBundle.getBundle(ResourceBundle.java:552)
at 
com.m16e.free.tools.i18n.MpBundle.setBundleFile(MpBundle.java:35)

at com.m16e.free.tools.xgui.XGuiBundle.(XGuiBundle.java:62)
at com.m16e.free.tools.xgui.XGuiBundle.(XGuiBundle.java:57)
... 3 more


I fiddled around with the locale settings in dbeditor.properties but to 
no avail. I even created a file i18n/xgui-locale.properties (and one 
xgui-locale_de.properties) but to no avail.


What am I missing here?

Regards
Thomas


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

Re: [GENERAL] Graphical modelling tool

[Luca Pireddu]

On Tuesday 06 December 2005 12:58, Thomas Hallgren wrote:
> Hi,
> I'm about to start a new project where the first task is to design a
> database. I'm looking for some tool that will allow me to model the
> tables and relationships graphically, UML or similar, and then let me
> generate the SQL with PostgreSQL flavor. What's the best tools out
> there? Are there any open source alternatives?
>
> Kind regards,
> Thomas Hallgren
>
You can try Dia with tedia2sql

Luca

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

Re: [GENERAL] Graphical modelling tool

[Mikael Carneholm]

I recommend Clay (http://www.azzurri.jp/en/software/clay/index.jsp).

It requires a JRE + Eclipse (as it is an eclipse plugin), but it is really nice 
to work with and has built-in support for PostgreSQL DDL generation.

/Mikael


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

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

Re: [GENERAL] insert deadlock

[Greg Stark]

Brian Cox <[EMAIL PROTECTED]> writes:

> Thread 1 does 1+ inserts into Table A and 1 update of Table B in a single
> transaction.
> This thread runs on demand.
> 
> Thread 2 does 1+ inserts into Table C in a separate transaction.  This thread
> runs once an hour.

I suspect you'll have to include more information than this to get an answer.
At least the \d output for tables A B and C including the foreign keys and
triggers.

Note that you need to worry not just about foreign key columns in tables A, B,
and C, but also any other table that has a foreign key column that refers to
these tables.

-- 
greg


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

Re: [GENERAL] Graphical modelling tool

[Carlos Correia]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thomas Hallgren wrote:
| Hi,
| I'm about to start a new project where the first task is to design a
| database. I'm looking for some tool that will allow me to model the
| tables and relationships graphically, UML or similar, and then let me
| generate the SQL with PostgreSQL flavor. What's the best tools out
| there? Are there any open source alternatives?
|
| Kind regards,
| Thomas Hallgren
|
|
| ---(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
|

Hi,

Perhaps, you'll like this one:
http://www.memoriapersistente.pt/en/opensource/gaudi/index.html

Regards,

Carlos
- --
MEMÓRIA PERSISTENTE, Lda.
Tel.: 219 291 591 - GSM:  967 511 762
e-mail: [EMAIL PROTECTED] - URL: http://www.m16e.com
AIM: m16e - ICQ: 257488263 - Jabber: [EMAIL PROTECTED]
Skype.com username (VoIP): m16e.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDlfDR90uzwjA1SJURAkLAAJ4519ZfgUS1GSXSxZIbiwQd+uYS2QCg0FHt
0es2I1VGBk96I0REcP2iEAw=
=ovOS
-END PGP SIGNATURE-

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

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

Re: [GENERAL] Graphical modelling tool

[Rich Shepard]

On Tue, 6 Dec 2005, Thomas Hallgren wrote:


I'm about to start a new project where the first task is to design a
database. I'm looking for some tool that will allow me to model the tables
and relationships graphically, UML or similar, and then let me generate the
SQL with PostgreSQL flavor. What's the best tools out there? Are there any
open source alternatives?


Thomas,

  This will do some of what you want:

   

Rich

--
Richard B. Shepard, Ph.D.   |   Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM)   |  Impact Assessments Using Fuzzy Logic"
 Voice: 503-667-4517 Fax: 503-667-8863

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

Re: [GENERAL] insert deadlock

[Tom Lane]

Brian Cox <[EMAIL PROTECTED]> writes:
> There are no foreign key constraints from/to A or B to/from C.

How sure are you of that?  FK conflicts are much the most common reason
for unexpected deadlocks in pre-8.1 PG releases.

> 2005-12-02 15:04:46,773 [TP-Processor8] INFO 
> [com.timestock.tess.services.processors.DefectProcessor] - 
> java.sql.SQLException: ERROR: deadlock detected

Java is doing you no favors here by suppressing the detail message that
would have told you exactly who blocked on what.  Perhaps you can get
the detail by looking in the postmaster log, though.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Graphical modelling tool

[John McCawley]

I wrote a little app that does basically what you're looking for:

http://www.hardgeus.com/projects/pgdesigner/

It's not professional quality, and is a little flaky, but it gets the 
job done.  It has a wxGTK dependency, and of course postgres.  Your best 
bet for running it is to pull it from my CVS server (instructions on 
page) rather than trying the binaries etc.


Thomas Hallgren wrote:


Hi,
I'm about to start a new project where the first task is to design a 
database. I'm looking for some tool that will allow me to model the 
tables and relationships graphically, UML or similar, and then let me 
generate the SQL with PostgreSQL flavor. What's the best tools out 
there? Are there any open source alternatives?


Kind regards,
Thomas Hallgren


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



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

[GENERAL] insert deadlock

[Brian Cox]

Thread 1 does 1+ inserts into Table A and 1 update of Table B in a 
single transaction.

This thread runs on demand.

Thread 2 does 1+ inserts into Table C in a separate transaction.  This 
thread runs once an hour.


There are no foreign key constraints from/to A or B to/from C. The 
default isolation level is used.


Thread 1 runs fine except when Thread 2 is running; in this case, about 
30% of the time, it gets a deadlock:


2005-12-02 15:04:46,771 [TP-Processor8] INFO 
[com.timestock.tess.services.processors.DefectProcessor] - 
java.sql.BatchUpdateException: Batch entry 0 insert into ts_defects 
(version_info, ts_tran_type, ts_transet_id, ts_tranunit_id, 
ts_trancomp_id, ts_user_id, ts_defect_def_id, ts_biz_event_id, 
ts_monitor_id, ts_defect_number, ts_occur_date, ts_defect_value, 
ts_observed_value, ts_http_status_code, ts_transaction_size, 
ts_transaction_time, ts_business_impact, ts_defect_type, 
ts_user_importance, ts_defect_importance, ts_tran_importance, 
ts_server_ip_address, ts_server_mac_address, ts_server_port, 
ts_client_ip_address, ts_missing_id, ts_missing_name, ts_content_error, 
ts_data_type, ts_soft_delete, ts_id) values (0, 3, 632465709984376570, 
NULL, NULL, 632471964872343772, 632465709984376573, 61, 
632465709984375001, 1133564681282, 2005-12-02 15:04:38.00-0800, 280, 
NULL, 200, 87120, 167, 64, 2, 4, 4, 4, 3232238338, 00:11:21:DF:5E:00, 
80, 3232235928, 0, NULL, NULL, 1, 0, 6001844673) was aborted.  
Call getNextException to see the cause.


2005-12-02 15:04:46,773 [TP-Processor8] INFO 
[com.timestock.tess.services.processors.DefectProcessor] - 

java.sql.SQLException: ERROR: deadlock detected

Rolling back the deadlocked transaction and retrying it works. What I 
don't understand is why the deadlock occurs as tables A/B seem to have 
no connection with C; can anyone explain this?


This is using PG version 8.0.3.

Thanks,
Brian Cox



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

  http://archives.postgresql.org

[GENERAL] Graphical modelling tool

[Thomas Hallgren]

Hi,
I'm about to start a new project where the first task is to design a 
database. I'm looking for some tool that will allow me to model the 
tables and relationships graphically, UML or similar, and then let me 
generate the SQL with PostgreSQL flavor. What's the best tools out 
there? Are there any open source alternatives?


Kind regards,
Thomas Hallgren


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

[GENERAL] ltree patch is available

[Oleg Bartunov]

Hi there,

Patch for ltree (all releases since 7.3) is available 
http://www.sai.msu.su/~megera/postgres/gist/patches/_ltree.patch

and from CVS. It fixes problem with unoptimal tree construction and big size
of index. Thanks Lexa Tutubalin for test suite and persistency.

Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

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

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

Re: [GENERAL] Should I fix something after disk full error

[Tom Lane]

"Andrus" <[EMAIL PROTECTED]> writes:
> I'm using 8.1 on XP.
> Server disk becomes full during running ANALYSE command.
> ...
> Should I fix something or can I continue to use this cluster normally ?

Everything should be OK once you clean off some disk space.
Let us know if you see any evidence that it's not.

regards, tom lane

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

[GENERAL] Should I fix something after disk full error

[Andrus]

I'm using 8.1 on XP.
Server disk becomes full during running ANALYSE command.

The following lines are in log file

2005-12-06 18:29:10 ERROR:  duplicate key violates unique constraint 
"pg_statistic_relid_att_index"
2005-12-06 18:30:23 LOG:  autovacuum: processing database "postgres"
2005-12-06 18:31:18 ERROR:  could not extend relation 1663/74856/2696: No 
space left on device
2005-12-06 18:31:18 HINT:  Check free disk space.
2005-12-06 18:31:29 WARNING:  corrupted pgstat.stat file
2005-12-06 18:32:37 LOG:  autovacuum: processing database "template1"
2005-12-06 18:32:39 LOG:  could not close temporary statistics file 
"global/pgstat.tmp": No space left on device
2005-12-06 18:32:40 LOG:  could not close temporary statistics file 
"global/pgstat.tmp": No space left on device
2005-12-06 18:33:42 LOG:  autovacuum: processing database "template1"
2005-12-06 18:34:42 LOG:  autovacuum: processing database "foo"
2005-12-06 18:35:53 LOG:  autovacuum: processing database "bar"
2005-12-06 18:37:12 LOG:  autovacuum: processing database "postgres"
2005-12-06 18:38:13 LOG:  autovacuum: processing database "template1"

etc.

Should I fix something or can I continue to use this cluster normally ?

Andrus. 



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

   http://archives.postgresql.org

Re: [GENERAL] [SQL] lost in system tables

[Tom Lane]

Emil Rachovsky <[EMAIL PROTECTED]> writes:
>  I am trying to find the equivalent of these two
> Sybase system columns :

> check_on_commit (Y/N)  -  Indicates whether INSERT and
> UPDATE commands should wait until the next COMMIT
> command to check if foreign keys are valid.

I think you are looking for the DEFERRABLE/DEFERRED option of foreign
key constraints.

> nulls (Y/N)  -  Indicates whether the columns in the
> foreign key are allowed to contain the NULL value.
> Note that this setting is independent of the nulls
> setting in the columns contained in the foreign key.

No such animal in the SQL standard --- though perhaps MATCH FULL
is approximately what you are looking for?  Your description as
stated makes no sense at all; either the columns are allowed to
be null, or they're not.

regards, tom lane

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

Re: [GENERAL] lost in system tables

[Luca Pireddu]

On Tuesday 06 December 2005 08:47, Emil Rachovsky wrote:
>  I am trying to find the equivalent of these two
> Sybase system columns :
>
> check_on_commit (Y/N)  -  Indicates whether INSERT and
> UPDATE commands should wait until the next COMMIT
> command to check if foreign keys are valid. A foreign
> key is valid if, for each row in the foreign table,
> the values in the columns of the foreign key either
> contain the NULL value or match the primary key values
> in some row of the primary table.
>
> nulls (Y/N)  -  Indicates whether the columns in the
> foreign key are allowed to contain the NULL value.
> Note that this setting is independent of the nulls
> setting in the columns contained in the foreign key.
>
>  Maybe pg_constaint.condeferrable is appropriate for
> check_on_commit? I'll appreciate any help
>
> Thanks in advance,
> Emil

Those parameters are specified when you declare the foreign key.  Look here, 
in the section describing "references":
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html

Luca

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

Re: [GENERAL] Temporary disable autovacuum in pgsql 8.1.0

[Richard van den Berg]

Tom Lane <[EMAIL PROTECTED]> wrote on 06-12-2005
16:07:41:
> That documentation is exactly correct.  Note that it doesn't
say "can
> only be set at server start" full stop.

That's a very suttle difference, but you are right
of couse. It's the word 'only' that threw me off. In my mind, it should
read:

This option can be set at server start or in the postgresql.conf
file during runtime.

Sincerely,

Richard van den Berg, CISSP
---
Trust Factory B.V. |     www.dna-portal.net
Bazarstraat 44a    |  www.trust-factory.com
2518AK The Hague   |  Phone: +31 70 3620684
The Netherlands    |  Fax  : +31 70 3603009
---

Re: [GENERAL] is there any way of specifying "i want x GB of space to be avaialble for my database"

[Chris Browne]

[EMAIL PROTECTED] ("surabhi.ahuja") writes:
> is there any way by which i can specify : to reserve x GB amount of
> space to this database (this x includes all the space which the
> database will require space for data files, indexes or any other
> resources).

I believe that is part of the intent of the TABLESPACE functionality
that was recently added to PostgreSQL.


-- 
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/linux.html
Rules of the  Evil Overlord #118. "If I  have equipment which performs
an  important function,  it  will not  be  activated by  a lever  that
someone  could  trigger  by   accidentally  falling  on  when  fatally
wounded." 

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

[GENERAL] lost in system tables

[Emil Rachovsky]

 I am trying to find the equivalent of these two
Sybase system columns :

check_on_commit (Y/N)  -  Indicates whether INSERT and
UPDATE commands should wait until the next COMMIT
command to check if foreign keys are valid. A foreign
key is valid if, for each row in the foreign table,
the values in the columns of the foreign key either
contain the NULL value or match the primary key values
in some row of the primary table.

nulls (Y/N)  -  Indicates whether the columns in the
foreign key are allowed to contain the NULL value.
Note that this setting is independent of the nulls
setting in the columns contained in the foreign key.

 Maybe pg_constaint.condeferrable is appropriate for
check_on_commit? I'll appreciate any help

Thanks in advance,
Emil





__ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 


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

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

Re: [GENERAL] error in creating database

[Markus Wollny]

Hi! 

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Karthik.S
> Gesendet: Dienstag, 6. Dezember 2005 13:26
> An: pgsql-general@postgresql.org
> Betreff: [GENERAL] error in creating database
> 
> Dear all,
> 
> I am using postgres version: 7.1.3 in Red hat linux : 7.2.

You should really consider upgrading. There has been a lot of development, both 
feature-wise and concerning bug-squashing, since the 7.1-days.
 
> Sometimes (nearly 50% of the times) the database creation 
> fails by saying
> "ERROR:  CREATE DATABASE: source database "template1" is 
> being accessed by other users"

8.1 has introduced the concept of a "maintenance-database", but you can easily 
do something similar by either creating your db's with "CREATE DATABASE foo 
WITH TEMPLATE = template0;" (but anything you have changed in template1 after 
PostgreSQL-installation won't be in your new db then) or simply create another 
template-database, say template2 for this purpose.

Kind regards

   Markus

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

Re: [GENERAL] error in creating database

[A. Kretschmer]

am  06.12.2005, um 17:56:20 +0530 mailte Karthik.S folgendes:
> Dear all,
> 
> I am using postgres version: 7.1.3 in Red hat linux : 7.2.

Uuhhh, 7.1 is out of lifetime. You should update!



> 
> I am having a script (uses psql) which drops and creates a database by 
> logging
> into 'template1'. This will be invoked from crontab.

Why drop and create the database?


> 
> Sometimes (nearly 50% of the times) the database creation fails by saying
> "ERROR:  CREATE DATABASE: source database "template1" is being
> accessed by other users"
> 
> But I am sure that none of my scripts access 'template1' at that time.

Can you split the script into 2 parts (drop and create) an run
separately this scripts?

But, i think:
- drop/create the same database via cron is silly
- you should update to 8.1, in versions prior 7.4 there was any bugs.



HTH, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47212,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

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

[GENERAL] error in creating database

[Karthik.S]

Dear all,

   I am using postgres version: 7.1.3 in Red hat linux : 7.2.

I am having a script (uses psql) which drops and creates a database by 
logging

into 'template1'. This will be invoked from crontab.

Sometimes (nearly 50% of the times) the database creation fails by saying
"ERROR:  CREATE DATABASE: source database "template1" is being
accessed by other users"

But I am sure that none of my scripts access 'template1' at that time.
I have also checked the 'ps -afx' output (logged at the time of failing),
but no suspects found.

Kindly give me a solution to overcome this problem.
Also kindly tell me a way to find, which process is accessing the
template1.


Thanks in advance.

regards
KArthik.S


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

Re: [GENERAL] Temporary disable autovacuum in pgsql 8.1.0

[Tom Lane]

Richard van den Berg <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote on 06-12-2005 15:49:28:
>> Couldn't you have just set autovacuum = off in postgresql.conf?  (Unlike
>> some other things, this setting can be changed after postmaster start.)

> If that is true, the documentation at 
> http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html 
> should be updated:

> autovacuum (boolean)
> Controls whether the server should start the autovacuum subprocess. This 
> is off by default. stats_start_collector and stats_row_level must also be 
> on for this to start. This option can only be set at server start or in 
> the postgresql.conf file. 

That documentation is exactly correct.  Note that it doesn't say "can
only be set at server start" full stop.

regards, tom lane

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

Re: [GENERAL] Temporary disable autovacuum in pgsql 8.1.0

[Richard van den Berg]

Tom Lane <[EMAIL PROTECTED]> wrote on 06-12-2005
15:49:28:
> Couldn't you have just set autovacuum = off in postgresql.conf?  (Unlike
> some other things, this setting can be changed after postmaster start.)

If that is true, the documentation at http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html
should be updated:

autovacuum (boolean)
Controls whether the server should start the autovacuum
subprocess. This is off by default. stats_start_collector
and stats_row_level must
also be on for this to start. This option can only be set at server start
or in the postgresql.conf
file. 

Richard van den Berg, CISSP
---
Trust Factory B.V. |     www.dna-portal.net
Bazarstraat 44a    |  www.trust-factory.com
2518AK The Hague   |  Phone: +31 70 3620684
The Netherlands    |  Fax  : +31 70 3603009
---

Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1

[Markus Wollny]

Hi! 

> -Ursprüngliche Nachricht-
> Von: Howard Cole [mailto:[EMAIL PROTECTED] 
> Gesendet: Dienstag, 6. Dezember 2005 15:38
> An: Markus Wollny
> Cc: PgSql General
> Betreff: Re: [GENERAL] Unicode Corruption and upgrading to 
> 8.0.4. to 8.1

> I am avoiding this solution at the moment since the database 
> contains binary (ByteA) fields aswell as text fields and I am 
> unsure what iconv would do to this data. 

Bytea-data in a plain text dump should be quite safe from iconv, as all the 
problematic characters (decimal value <32 or >126) in the binary string are 
represented as SQL escaped octets like \###. 

Kind regards

   Markus

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

Re: [GENERAL] Temporary disable autovacuum in pgsql 8.1.0

[Tom Lane]

Richard van den Berg <[EMAIL PROTECTED]> writes:
> We are in the process of upgrading our 400GB database from PostgreSQL
> 7.4.7 to 8.1.0. During the pg_restore I noticed that the autovacuum
> process was slowing things down significantly. I should have disabled it
> in postgresql.conf before starting the restore (duh).
> Not wanting to restart the restore, I searched for a method of disabling
> autovacuum on the fly. I found the solution based on the hints I found
> at http://archives.postgresql.org/pgsql-patches/2004-06/msg00414.php

Couldn't you have just set autovacuum = off in postgresql.conf?  (Unlike
some other things, this setting can be changed after postmaster start.)

regards, tom lane

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

Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1

[Howard Cole]

Thanks Markus,

I am avoiding this solution at the moment since the database contains 
binary (ByteA) fields aswell as text fields and I am unsure what iconv 
would do to this data. If Zlatko's method does not work then I shall see 
if I can programmatically use libiconv for all the relevant data.


Regards,

Howard Cole
Markus Wollny wrote:


message on this issue

http://archives.postgresql.org/pgsql-general/2005-11/msg00799.php

On top of that you'd be well advised to try dumping using pg_dump of postgresql 
8.1.

 



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

Re: [GENERAL] Inheritance Algebra

[Karsten Hilbert]

Trent,

although I cannot contribute much of anything to your line
of thought I'd encourage you to keep on with it as it'd be
highly desirable (for GNUmed at least) to have a stronger/
more encompassing inheritance solution in PostgreSQL.

Karsten,
GNUmed developer
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

Re: [GENERAL] massive performance hit when using "Limit 1"

[Rich Doughty]

Rich Doughty wrote:

Richard Huxton wrote:


Rich Doughty wrote:


[snip]

Try the same query but with a low retailer_id (100 or something) and 
see if it goes a lot quicker. If that is what the problem is, try 
changing the ORDER BY to something like "_s.retailer_id, _t.value, 
_t.number" and see if that gives the planner a nudge in the right 
direction.



the retailer_id would make no difference as thee are only 4000-ish rows in
ta_tokens_stock and they all (for now) have the same retailer_id.


ooops. i (sort of) spoke too soon. i didn't read the second half of the
comment properly. changing the ORDER BY clause does force a more sensible
query plan.

many thanks. so that's one way to give the planner hints...


Failing that, a change to your indexes will almost certainly help.



i'm not sure that's the case. the exact same query, but limited to >2 rows
is fine.

I found this in the 8.0.4 relnotes. i reckon its a good guess that's 
what the

problem is:

* Fix mis-planning of queries with small LIMIT values due to poorly thought
  out "fuzzy" cost comparison



--

  - Rich Doughty

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

Re: [GENERAL] massive performance hit when using "Limit 1"

[Rich Doughty]

Richard Huxton wrote:

Rich Doughty wrote:



This one goes nuts and doesn't return. is there any way i can
force a query plan similar to the one above?

  EXPLAIN SELECT _t.* FROM
   tokens.ta_tokens   _t INNER JOIN
   tokens.ta_tokens_stock _s ON _t.token_id = _s.token_id
  WHERE
   _s.retailer_id = '96599' AND
   _t.value   = '10'
  ORDER BY
   _t.number ASC
  LIMIT '1';
QUERY PLAN
--- 


 Limit  (cost=0.00..14967.39 rows=1 width=27)
   ->  Nested Loop  (cost=0.00..22316378.56 rows=1491 width=27)
 ->  Index Scan using ta_tokens_number_key on ta_tokens _t  
(cost=0.00..15519868.33 rows=1488768 width=27)

   Filter: ((value)::numeric = 10::numeric)
 ->  Index Scan using ta_tokens_stock_pkey on ta_tokens_stock 
_s  (cost=0.00..4.55 rows=1 width=4)
   Index Cond: (("outer".token_id)::integer = 
(_s.token_id)::integer)

   Filter: ((retailer_id)::integer = 96599)



I *think* what's happening here is that PG thinks it will use the index 
on _t.number (since you are going to sort by that anyway) and pretty 
soon find a row that will:

  1. have value=10
  2. join to a row in _s with the right retailer_id
It turns out that isn't the case, and so the query takes forever. 
Without knowing what "value" and "number" mean it's difficult to be 
sure, but I'd guess it's the "token_id" join part that's the problem, 
since at a guess a high-numbered retailer will have tokens with 
high-numbered "retailer_id".


If you'd posted EXPLAIN ANALYSE then we'd be able to see what actually 
did happen.


no chance. it takes far too long to return (days...).

Try the same query but with a low retailer_id (100 or something) and see 
if it goes a lot quicker. If that is what the problem is, try changing 
the ORDER BY to something like "_s.retailer_id, _t.value, _t.number" and 
see if that gives the planner a nudge in the right direction.


the retailer_id would make no difference as thee are only 4000-ish rows in
ta_tokens_stock and they all (for now) have the same retailer_id.


Failing that, a change to your indexes will almost certainly help.


i'm not sure that's the case. the exact same query, but limited to >2 rows
is fine.

I found this in the 8.0.4 relnotes. i reckon its a good guess that's what the
problem is:

* Fix mis-planning of queries with small LIMIT values due to poorly thought
  out "fuzzy" cost comparison



--

  - Rich Doughty

---(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: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1

[Markus Wollny]

Hello!

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Howard Cole
> Gesendet: Dienstag, 6. Dezember 2005 13:41
> An: 'PgSql General'
> Betreff: Re: [GENERAL] Unicode Corruption and upgrading to 
> 8.0.4. to 8.1

> >> Hi everyone, I have a problem with corrupt UTF-8 sequences in my
> >> 8.0.4 dump which is preventing me from upgrading to 8.1 - 
> which spots 
> >> the errors and refuses to import the data. Is there some 
> SQL command 
> >> that I can use to fix or cauterise the sequences in the 8.0.4 
> >> database before dumping to 8.1?
> >>
> >> I think the problem arose using invalid client encodings - 
> which were 
> >> not rejected prior to 8.1.


We experienced the exact same problems. You may solve the problem by feeding 
the dump through iconv. See my earlier message on this issue

http://archives.postgresql.org/pgsql-general/2005-11/msg00799.php

On top of that you'd be well advised to try dumping using pg_dump of postgresql 
8.1.

Kind regards

   Markus 

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

Re: [GENERAL] massive performance hit when using "Limit 1"

[Richard Huxton]

Rich Doughty wrote:


This one goes nuts and doesn't return. is there any way i can
force a query plan similar to the one above?

  EXPLAIN SELECT _t.* FROM
   tokens.ta_tokens   _t INNER JOIN
   tokens.ta_tokens_stock _s ON _t.token_id = _s.token_id
  WHERE
   _s.retailer_id = '96599' AND
   _t.value   = '10'
  ORDER BY
   _t.number ASC
  LIMIT '1';
QUERY PLAN
--- 


 Limit  (cost=0.00..14967.39 rows=1 width=27)
   ->  Nested Loop  (cost=0.00..22316378.56 rows=1491 width=27)
 ->  Index Scan using ta_tokens_number_key on ta_tokens _t  
(cost=0.00..15519868.33 rows=1488768 width=27)

   Filter: ((value)::numeric = 10::numeric)
 ->  Index Scan using ta_tokens_stock_pkey on ta_tokens_stock 
_s  (cost=0.00..4.55 rows=1 width=4)
   Index Cond: (("outer".token_id)::integer = 
(_s.token_id)::integer)

   Filter: ((retailer_id)::integer = 96599)


I *think* what's happening here is that PG thinks it will use the index 
on _t.number (since you are going to sort by that anyway) and pretty 
soon find a row that will:

  1. have value=10
  2. join to a row in _s with the right retailer_id
It turns out that isn't the case, and so the query takes forever. 
Without knowing what "value" and "number" mean it's difficult to be 
sure, but I'd guess it's the "token_id" join part that's the problem, 
since at a guess a high-numbered retailer will have tokens with 
high-numbered "retailer_id".


If you'd posted EXPLAIN ANALYSE then we'd be able to see what actually 
did happen.


Try the same query but with a low retailer_id (100 or something) and see 
if it goes a lot quicker. If that is what the problem is, try changing 
the ORDER BY to something like "_s.retailer_id, _t.value, _t.number" and 
see if that gives the planner a nudge in the right direction.


Failing that, a change to your indexes will almost certainly help.
--
  Richard Huxton
  Archonet Ltd

---(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: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1

[Howard Cole]

Hi Zlatko,

I shall give this a try later and let you know how I get on. Thank you 
for responding.


Howard.

Zlatko Matic wrote:


Have you tried to restore just schema first, then data?
Greetings,

Zlatko

Hi everyone, I have a problem with corrupt UTF-8 sequences in my 
8.0.4 dump which is preventing me from upgrading to 8.1 - which spots 
the errors and refuses to import the data. Is there some SQL command 
that I can use to fix or cauterise the sequences in the 8.0.4 
database before dumping to 8.1?


I think the problem arose using invalid client encodings - which were 
not rejected prior to 8.1.





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

Re: [GENERAL] fts, compond words?

[Marcus Engene]

Oleg Bartunov wrote:

On Mon, 5 Dec 2005, Marcus Engene wrote:

I realized from the documentation that I'm not looking for
compound words after all, I meant "exact phrase".

I can't see how to make rank tell me which results has an
exact phrase? Like "there must be a occurence of 'new' before
'york'"  (stemmed not really exact phrase)?


http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes

Phrase search
This tip is by Mike Rylander

To do phrase searching just add an additional WHERE clause to your query:

SELECT id FROM tab WHERE ts_idx_col @@ to_tsquery('history&lesson')
AND text_col ~* '.*history\\s+lesson.*';

The full-text index will still be used, and the regex will be used to
prune the results afterwards.


Hi,

Thanks for the answer, Oleg and Mike.

This, I guess, will be problematic in a query like
 A & (B | C)
or a more complex expression.

say C is "New York" and that tsearch receives

 A & (B | (new & york))

I cannot just add the regexp afterwards. What if B is true?
What would be nice to have, given ofcourse the index isn't stripped
is something like

 A & (B | (New OperatorTheNextWordMustFollow York))

Would something like that be doable? Right now, intuitively, it would be 
two trees in the where clause:

 tsearch(A & B) OR
 (tsearch (A & C) AND regexpmatch(C))
..and a nightmare in complex queries.

Best regards,
Marcus

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

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

Re: [GENERAL] need help

[Alban Hertroys]

Jenny wrote:

I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been
dealing with Psql for over than 2 years now, but I've never had this case
before.



Then I try to run the query from the psql shell. For example, the table has
obat_id : A, B, C, D.
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
( nothing happens.. I press the Ctrl-C to stop it. This is what comes out
:)
Cancel request sent
ERROR: canceling query due to user request

(If I try another obat_id)
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B';
(Less than a second, this is what comes out :)
UPDATE 1


It could well be another client has a lock on that record, for example 
by doing a SELECT FOR UPDATE w/o a NOWAIT.


You can verify by querying pg_locks. IIRC you can also see what query 
caused the lock by joining against some other system table, but the 
details escape me atm (check the archives, I learned that by following 
this list).


If it's indeed a locked record, the process causing the lock is listed. 
Either kill it or call it's owner back from his/her coffee break ;)


I doubt it's anything serious.

--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

//Showing your Vision to the World//

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

[GENERAL] Temporary disable autovacuum in pgsql 8.1.0

[Richard van den Berg]

We are in the process of upgrading our 400GB database from PostgreSQL
7.4.7 to 8.1.0. During the pg_restore I noticed that the autovacuum
process was slowing things down significantly. I should have disabled it
in postgresql.conf before starting the restore (duh).
Not wanting to restart the restore, I searched for a method of disabling
autovacuum on the fly. I found the solution based on the hints I found
at http://archives.postgresql.org/pgsql-patches/2004-06/msg00414.php

This works because pg_restore will create all tables before it starts
filling them. Resetting the pg_autovacuum table is a simple truncate.
Perhaps it will help someone with the same problem.

insert into pg_autovacuum (
vacrelid,
enabled,
vac_base_thresh,
vac_scale_factor,
anl_base_thresh,
anl_scale_factor,
vac_cost_delay,
vac_cost_limit)
select a.oid,
   false,
   current_setting('autovacuum_vacuum_threshold')::int,
   current_setting('autovacuum_vacuum_scale_factor')::real,
   current_setting('autovacuum_analyze_threshold')::int,
   current_setting('autovacuum_analyze_scale_factor')::real,
   current_setting('autovacuum_vacuum_cost_delay')::int,
   current_setting('autovacuum_vacuum_cost_limit')::int
frompg_class a
inner join  pg_stat_all_tables b
on  a.oid=b.relid
left outer join pg_autovacuum c
on  a.oid = c.vacrelid
where   a.relkind = 'r'
and schemaname not like 'pg_temp_%%'
and a.oid not in (select distinct vacrelid from
  pg_autovacuum);

update pg_autovacuum
setenabled=false
where  enabled;

-- 
Richard van den Berg, CISSP
---
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a|  www.trust-factory.com
2518AK The Hague   |  Phone: +31 70 3620684
The Netherlands|  Fax  : +31 70 3603009
---

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

[GENERAL] UNSUBSCRIBE

[Vinay Jain]

Please Unsubscribe.

Re: [GENERAL] massive performance hit when using "Limit 1"

[Rich Doughty]

Rich Doughty wrote:

can anyone explain the reason for the difference in the
following 2 query plans, or offer any advice? the two queries
are identical apart from the limit clause.


[snip]

fwiw, join order makes no difference here either. i get a slightly
different plan, but with LIMIT 1 postgres make a really strange
planner choice.

As before LIMIT > 1 the choice is logical and performance fine.



  - Rich Doughty

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

Re: [GENERAL] Using a 250GB RAID10 server for postgres

[Bernhard Weisshuhn]

On Tue, Dec 06, 2005 at 09:41:55AM +, Rory Campbell-Lange <[EMAIL 
PROTECTED]> wrote:

> Is it OK to use logical volume management to run an xfs partition
> hosting postgres data?

We use just that and it works splendid. It's very nice for adding space
and all that.

But I must admit that I haven't had any comments from the gurus wether
this is a wise choice performance wise.

Keep in mind that you might want devide your discs into two groups with
different physical discs so that you can make use of tablespaces. We use
them to seperate data and indexspace. Seperating the wal makes sense
only if there is nothing else accessing that disc.

just my two cents,
bkw

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

Re: [GENERAL] Using a 250GB RAID10 server for postgres

[Rory Campbell-Lange]

In the absence of replies (and sorry to bombard the list), I should
clarify my question:

Is it OK to use logical volume management to run an xfs partition
hosting postgres data?

(The server specs are below.)

Thanks for any replies.

Rory

On 05/12/05, Rory Campbell-Lange ([EMAIL PROTECTED]) wrote:
> [Didn't get any replies on the Perform list -- hoping someone can help
> me here]
> 
> Hi. We have a server provided for a test of a web application with the
> following specifications:
> 
> 1 Dual core 1.8GHz Opteron chip
> 6 GB RAM
> approx 250GB of RAID10 storage (LSI card + BBU, 4 x 15000 RPM,16MB
> Cache SCSI disks)
> 
> The database itself is very unlikely to use up more than 50GB of storage
> -- however we are going to be storing pictures and movies etc etc on the
> server.
> 
> I understand that it is better to run pg_xlog on a separate spindle but
> we don't have that option available at present.
> 
> Normally I use ext3. I wondered if I should make my normal partitions
> and then make a +/- 200GB LVM VG and then slice that initially into a
> 100GB ext3 data directory and a 50GB xfs postgres data area, giving
> 100GB to use between these as they grow. I haven't used LVM with xfs
> before, however.
> 
> Advice gratefully received.
> Rory
> 
> 

-- 
Rory Campbell-Lange 
<[EMAIL PROTECTED]>


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

[GENERAL] need help (not anymore)

[Jenny]

I run the VACUUM as you suggested, but still no response from the server. So, I
decided to DROP the database. I got a message that the database is being used.
I closed every application that accessing it. But, the message remains.

I checked the server processes (ps -ax). There were lots of  'UPDATE is waiting
...' on the list. I killed them all. I backuped current database and DROP the
database, restore to the backup file I just made. 

Don't really know why this happened, but thankfully now, everything's normal.
Thank you, guys.

Regards,
Jenny Tania



__ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 


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

Re: [GENERAL] need help

[Tino Wildenhain]

Jenny schrieb:

I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been
dealing with Psql for over than 2 years now, but I've never had this case
before.

I have a table that has about 20 rows in it.

   Table "public.s_apotik"
Column |  Type| Modifiers
---+--+--
obat_id| character varying(10)| not null
stock  | numeric  | not null
s_min  | numeric  | not null
s_jual | numeric  | 
s_r_jual   | numeric  | 
s_order| numeric  | 
s_r_order  | numeric  | 
s_bs   | numeric  | 
last_receive   | timestamp without time zone  |

Indexes:
   "s_apotik_pkey" PRIMARY KEY, btree(obat_id)
   
When I try to UPDATE one of the row, nothing happens for a very long time.

First, I run it on PgAdminIII, I can see the miliseconds are growing as I
waited. Then I stop the query, because the time needed for it is unbelievably
wrong.

Then I try to run the query from the psql shell. For example, the table has
obat_id : A, B, C, D.
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
( nothing happens.. I press the Ctrl-C to stop it. This is what comes out
:)
Cancel request sent
ERROR: canceling query due to user request

(If I try another obat_id)
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B';
(Less than a second, this is what comes out :)
UPDATE 1

I can't do anything to that row. I can't DELETE it. Can't DROP the table. 
I want this data out of my database.

What should I do? It's like there's a falsely pointed index here.
Any help would be very much appreciated.



1) lets hope you do regulary backups - and actually tested restore.
1a) if not, do it right now
2) reindex the table
3) try again to modify

Q: are there any foreign keys involved? If so, reindex those
tables too, just in case.

did you vacuum regulary?

HTH
Tino

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

[GENERAL] need help

[Jenny]

I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been
dealing with Psql for over than 2 years now, but I've never had this case
before.

I have a table that has about 20 rows in it.

   Table "public.s_apotik"
Column |  Type| Modifiers
---+--+--
obat_id| character varying(10)| not null
stock  | numeric  | not null
s_min  | numeric  | not null
s_jual | numeric  | 
s_r_jual   | numeric  | 
s_order| numeric  | 
s_r_order  | numeric  | 
s_bs   | numeric  | 
last_receive   | timestamp without time zone  |
Indexes:
   "s_apotik_pkey" PRIMARY KEY, btree(obat_id)
   
When I try to UPDATE one of the row, nothing happens for a very long time.
First, I run it on PgAdminIII, I can see the miliseconds are growing as I
waited. Then I stop the query, because the time needed for it is unbelievably
wrong.

Then I try to run the query from the psql shell. For example, the table has
obat_id : A, B, C, D.
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A';
( nothing happens.. I press the Ctrl-C to stop it. This is what comes out
:)
Cancel request sent
ERROR: canceling query due to user request

(If I try another obat_id)
db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B';
(Less than a second, this is what comes out :)
UPDATE 1

I can't do anything to that row. I can't DELETE it. Can't DROP the table. 
I want this data out of my database.
What should I do? It's like there's a falsely pointed index here.
Any help would be very much appreciated.


Regards,
Jenny Tania



__ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 


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