Re: [GENERAL] Postgresql and Creator2 commitChanges()

[Kris Jurka]

On Tue, 18 Apr 2006, lash wrote:


Sun Java Studio Creator 2 and PostgreSQL 8.1 database
using 8.1-405 jdbc3.

But "someDataProvider.commitChanges()" works only first time.
Then the only message i get after "someDataProvider.commitChanges()" is
"can't change isolation level in the middle of transaction".


If you are going to post to multiple lists, please cross-post instead of 
sending two separate messages.  This is a JDBC question, so I'll follow up 
there.


Kris Jurka


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

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

[GENERAL] Postgresql and Creator2 commitChanges()

[lash]

Hi.

Sun Java Studio Creator 2 and PostgreSQL 8.1 database
using 8.1-405 jdbc3.

Connectin,fetching,... works.

But "someDataProvider.commitChanges()" works only first time.
Then the only message i get after "someDataProvider.commitChanges()" is
"can't change isolation level in the middle of transaction".
I must use stored procedures for update and insert records. It works
fine.

When i change the data provider to MySQL
all works fine (i mean "someDataProvider.commitChanges()").

Can anybody comment ?.

Thanks.



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Vacuuming of indexes on tables.

[elein]

Running version 8.1.2, 1-2G RAM. Configuration
set up to use available RAM.  Running autovacuum.

I have a table with 850 rows.  The table
gets only inserts (constantly).  Once a day
a range of the rows is deleted and an explicit
vacuum analyze of the table is done.

The table has 4 indexes.

Much later in the day, a vacuum analyze of the
db showed that all of the indexes for that table
required significant vacuuming, although the
table did not.  

A bit later, further vacuuming showed more indexes
required vacuuming and again the table did not.  

In what situation would these indexes require the
further vacuuming?  Due to the insert only activity
on the table (and the db, mostly), I cannot understand
why the indexes require further vacuuming.

Ideas?

--elein
[EMAIL PROTECTED]

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

Re: [GENERAL] plpgsql replication stored procedure

[Peter Wilson]

[EMAIL PROTECTED] wrote:

Hi,

I'm trying to write a stored procedure that can capture all the changes
to a table and record the changes based on the table's primary key.

I can almost get there but the sticking point is being able to access
the primary key field of the NEW/OLD record in the trigger stored
procedure without knowing it's name.

The stored procedure is below and what I'm trying to do is find a way
to get the column from the NEW record that has the name
constraintColName. If I could do that I would replace NEW.oid with the
equivalent of NEW[constraintColName] and remove the need for oid's on
the table.

create or replace function replicate() returns trigger as
$$

 declare
  constraintName varchar;
  constraintColName varchar;
  keyId varchar;
  slaves record;

 begin
select into constraintName constraint_name from
information_schema.table_constraints where table_name = TG_RELNAME and
constraint_type = 'PRIMARY KEY';
select into constraintColName column_name from
information_schema.key_column_usage where constraint_name =
constraintName;
-- execute 'select ' || constraintColName || ' from ' || NEW into
keyId;

for slaves in
 select slaveid from replicationslaves
loop
 insert into replicationentries values(default, slaves.slaveid,
TG_OP, TG_RELNAME , NEW.oid, default);
end loop;
return NULL;
end;$$
language 'plpgsql';

Aaron

Why not use or adapt the 'C' function in the dbmirror implementation shipped 
with Postgres? The Perl script to replicate to the slave database is very 
inefficient but the trigger function itself is very fast.


I've also got a C++ implementation of the dbmirror replication perl script as 
well if it's any use


Pete
--
www.whitebeam.org
www.yellowhawk.co.uk
---


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

  http://archives.postgresql.org

Re: [GENERAL] catch SQLException, error code for Foeign key violation,

[Kris Jurka]

On Mon, 17 Apr 2006, surabhi.ahuja wrote:

The question is that is from this SQLException can i detect if it is a 
foreign key violation,




You should check the value of SQLException.getSQLState() against this 
table:

http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html

Kris Jurka

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

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

Re: [GENERAL] tsearch partial word

[Teodor Sigaev]

Tsearch doesn't support such scan. You can add your own operation or modify 
existing, but in any case index will not support it.


I don't known efficient index structure for queries you need...

Yudie Pg wrote:

Is it possible to search partial word as *like '%...%'* in tsearch?
 
Yudie


--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

Re: [GENERAL] Query runs fast or slow

[felix]

On Sun, Apr 16, 2006 at 04:32:25PM -0400, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > EXPLAIN ANALYZE doesn't show the slow timing
> > because it requires values, not $n placeholders,
> 
> To analyze the plan used for a parameterized query, try
> 
>   PREPARE foo(...) AS SELECT ... $n ...
> 
>   EXPLAIN ANALYZE EXECUTE foo(...)
> 
> But I already know what you're going to find: the planner's estimates
> for the range query are not going to be very good when it has no idea
> what the range bounds are.  This is a situation where it may be best
> to absorb the hit of re-planning each time instead of using a generic
> parameterized plan.

OK, here is the new explain analyze.  I eliminated cache effects by
dumping the tables and picking random values with an editor.

felix=> PREPARE foo(TEXT, INT, INT) AS SELECT s.data, g.key, g.val, g.sid FROM 
key k, val_int v, sid s, glue_int g WHERE (k.data = $1 AND k.id = g.key) AND 
(v.data >= $2 AND v.data <= $3) AND v.id = g.val AND g.sid = s.id;
PREPARE
felix=> explain analyze execute foo('mthNQFrmVs3Q4bVruCxIAGy', 1973028023, 
1973028223);
 
QUERY PLAN  
   

 Nested Loop  (cost=1380.11..404223.36 rows=499 width=60) (actual 
time=5785.012..77823.688 rows=1 loops=1)
   ->  Hash Join  (cost=1380.11..402713.38 rows=499 width=16) (actual 
time=5766.308..77804.969 rows=1 loops=1)
 Hash Cond: ("outer".val = "inner".id)
 ->  Nested Loop  (cost=0.00..400829.78 rows=99701 width=16) (actual 
time=115.154..77401.159 rows=10 loops=1)
   ->  Index Scan using key_data_key on "key" k  (cost=0.00..5.82 
rows=1 width=4) (actual time=0.125..0.132 rows=1 loops=1)
 Index Cond: (data = $1)
   ->  Index Scan using glue_int_key_idx on glue_int g  
(cost=0.00..399577.70 rows=99701 width=16) (actual time=115.011..76570.366 
rows=10 loops=1)
 Index Cond: ("outer".id = g."key")
 ->  Hash  (cost=1378.86..1378.86 rows=500 width=4) (actual 
time=11.580..11.580 rows=0 loops=1)
   ->  Index Scan using val_int_data_key on val_int v  
(cost=0.00..1378.86 rows=500 width=4) (actual time=11.556..11.561 rows=1 
loops=1)
 Index Cond: ((data >= $2) AND (data <= $3))
   ->  Index Scan using sid_pkey on sid s  (cost=0.00..3.01 rows=1 width=52) 
(actual time=18.682..18.687 rows=1 loops=1)
 Index Cond: ("outer".sid = s.id)
 Total runtime: 77823.897 ms
(14 rows)

A repeat shows it faster, from 77 seconds to 3.

felix=> explain analyze execute foo('mthNQFrmVs3Q4bVruCxIAGy', 1973028023, 
1973028223);
   
QUERY PLAN  
  
-
 Nested Loop  (cost=1380.11..404223.36 rows=499 width=60) (actual 
time=205.137..2931.899 rows=1 loops=1)
   ->  Hash Join  (cost=1380.11..402713.38 rows=499 width=16) (actual 
time=205.056..2931.803 rows=1 loops=1)
 Hash Cond: ("outer".val = "inner".id)
 ->  Nested Loop  (cost=0.00..400829.78 rows=99701 width=16) (actual 
time=0.148..2564.255 rows=10 loops=1)
   ->  Index Scan using key_data_key on "key" k  (cost=0.00..5.82 
rows=1 width=4) (actual time=0.031..0.039 rows=1 loops=1)
 Index Cond: (data = $1)
   ->  Index Scan using glue_int_key_idx on glue_int g  
(cost=0.00..399577.70 rows=99701 width=16) (actual time=0.105..1808.068 
rows=10 loops=1)
 Index Cond: ("outer".id = g."key")
 ->  Hash  (cost=1378.86..1378.86 rows=500 width=4) (actual 
time=0.090..0.090 rows=0 loops=1)
   ->  Index Scan using val_int_data_key on val_int v  
(cost=0.00..1378.86 rows=500 width=4) (actual time=0.074..0.080 rows=1 loops=1)
 Index Cond: ((data >= $2) AND (data <= $3))
   ->  Index Scan using sid_pkey on sid s  (cost=0.00..3.01 rows=1 width=52) 
(actual time=0.061..0.066 rows=1 loops=1)
 Index Cond: ("outer".sid = s.id)
 Total runtime: 2932.013 ms
(14 rows)

And running it as a simple query shows it much faster, 72 ms.

felix=> EXPLAIN ANALYZE SELECT s.data, g.key, g.val, g.sid FROM key k, val_int 
v, sid s, glue_int g WHERE (k.data = 'mthNQFrmVs3Q4bVruCxIAGy' AND k.id = 
g.key) AND (v.data >= 1973028023 AND v.data <= 1973028223) AND v.id = g.val AND 
g.sid = s.id;
QUERY 
PLAN 
--

Re: [GENERAL] 21 bit number for sequence

[Dawid Kuroczko]

On 4/15/06, Shoaib Mir <[EMAIL PROTECTED]> wrote:
Actually that is the application requirment to use 21 bit numbers as porting it from Oracle where it used to work. Yeah
now i have decided to use a numeric data type in a table and use that
to write my own nextval and currval functions for that purpose.


Something like:
CREATE SEQUENCE some_id_seq;
CREATE TABLE foo (
    some_id bit(21) DEFAULT nextval('some_id_seq')::bit(21),
    t text
);
insert into foo(t) values ('a');
insert into foo(t) values ('bar');
insert into foo(t) values ('baz');
insert into foo(some_id,t) values ('10010', 'uhh');
SELECT * FROM foo;
   some_id t 
- ---
1 a
00010 bar
00011 baz
10010 uhh
(4 rows)

HTH, HAND. :)
   Regards,
 Dawid

Re: [GENERAL] plpgsql replication stored procedure

[William Leite Araújo]

On 4/17/06, Tom Lane <[EMAIL PROTECTED]> wrote:
[EMAIL PROTECTED] writes:> I'm trying to write a stored procedure that can capture all the changes> to a table and record the changes based on the table's primary key.
> I can almost get there but the sticking point is being able to access> the primary key field of the NEW/OLD record in the trigger stored> procedure without knowing it's name.
    My database has a table that store all changes mades on all tables. The insert is made by onde single trigger, but I create a function that build functions to convert an generic record on a text value.    If this is usefull, mail-me    ;-)
    Dynamic record name cannot be made on a plpgsql function. This topic was discursed some months ago.-- William Leite AraújoEspecialista em Geoprocessamento- UFMGBacharel em Ciêncida da Computação - UFMG
MSN:  [EMAIL PROTECTED]ICQ:222159351GTalk: [EMAIL PROTECTED]Yahoo: [EMAIL PROTECTED]
Skype: william.bh

Re: [GENERAL] tsearch partial word

[Joshua D. Drake]

On Mon, 2006-04-17 at 14:48 -0500, Yudie Pg wrote:
> Is it possible to search partial word as like '%...%' in tsearch?

Well at that point you are just going to seqscan anyway... so why not
just to a standard like search?

The could see that you might get a benefit from using the '%...%' on the
vectors columns though, as you may be scanning less data.

Joshua D. Drake


>  
> Yudie
-- 

=== The PostgreSQL Company: Command Prompt, Inc. ===
  Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
  Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/





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

Re: [GENERAL] the integer type

[Tony Caduto]

Peter Eisentraut wrote:

Dave Page wrote:
  

It's not a error, pgAdmin III simply does not display the
word integer in it's drop down comboboxes, it uses all the
internal representation of types not the SQL standard aliases.
  

Which allows you to use any custom datatype or domain that you like.



That is completely unrelated.  If pgadmin (or any tool) passed the data 
types it presumably fetches from pg_type or thereabouts through the 
format_type function it could present the user with a full list of 
actually available data types but in their preferred spellings.


  
Yes, PG Lightning Admin uses the format_type function, though I modify 
some of the returned pretty names.

I change "character varying" back to varchar etc.

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

Re: [GENERAL] the integer type

[Dave Page]

 

> -Original Message-
> From: Peter Eisentraut [mailto:[EMAIL PROTECTED] 
> Sent: 17 April 2006 20:16
> To: pgsql-general@postgresql.org
> Cc: Dave Page; Tony Caduto; chris smith; Zahir Lalani
> Subject: Re: [GENERAL] the integer type
> 
> Dave Page wrote:
> > > It's not a error, pgAdmin III simply does not display the word 
> > > integer in it's drop down comboboxes, it uses all the internal 
> > > representation of types not the SQL standard aliases.
> >
> > Which allows you to use any custom datatype or domain that you like.
> 
> That is completely unrelated. If pgadmin (or any tool) 
> passed the data types it presumably fetches from pg_type or 
> thereabouts through the format_type function it could present 
> the user with a full list of actually available data types 
> but in their preferred spellings.

It's not at all unrelated - it just means that none of us were aware of
the existance of format_type.

Regards, Dave.

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

   http://archives.postgresql.org

[GENERAL] tsearch partial word

[Yudie Pg]

Is it possible to search partial word as like '%...%' in tsearch?
 
Yudie

Re: [GENERAL] plpgsql replication stored procedure

[Tom Lane]

[EMAIL PROTECTED] writes:
> I'm trying to write a stored procedure that can capture all the changes
> to a table and record the changes based on the table's primary key.
> I can almost get there but the sticking point is being able to access
> the primary key field of the NEW/OLD record in the trigger stored
> procedure without knowing it's name.

There is no way to write such a generic trigger in plpgsql (and even if
you could, its performance would suck :-().  You could do it in C if
you're sufficiently determined.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] the integer type

[Peter Eisentraut]

Dave Page wrote:
> > It's not a error, pgAdmin III simply does not display the
> > word integer in it's drop down comboboxes, it uses all the
> > internal representation of types not the SQL standard aliases.
>
> Which allows you to use any custom datatype or domain that you like.

That is completely unrelated.  If pgadmin (or any tool) passed the data 
types it presumably fetches from pg_type or thereabouts through the 
format_type function it could present the user with a full list of 
actually available data types but in their preferred spellings.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

[GENERAL] plpgsql replication stored procedure

[aaron . clauson]

Hi,

I'm trying to write a stored procedure that can capture all the changes
to a table and record the changes based on the table's primary key.

I can almost get there but the sticking point is being able to access
the primary key field of the NEW/OLD record in the trigger stored
procedure without knowing it's name.

The stored procedure is below and what I'm trying to do is find a way
to get the column from the NEW record that has the name
constraintColName. If I could do that I would replace NEW.oid with the
equivalent of NEW[constraintColName] and remove the need for oid's on
the table.

create or replace function replicate() returns trigger as
$$

 declare
  constraintName varchar;
  constraintColName varchar;
  keyId varchar;
  slaves record;

 begin
select into constraintName constraint_name from
information_schema.table_constraints where table_name = TG_RELNAME and
constraint_type = 'PRIMARY KEY';
select into constraintColName column_name from
information_schema.key_column_usage where constraint_name =
constraintName;
-- execute 'select ' || constraintColName || ' from ' || NEW into
keyId;

for slaves in
 select slaveid from replicationslaves
loop
 insert into replicationentries values(default, slaves.slaveid,
TG_OP, TG_RELNAME , NEW.oid, default);
end loop;
return NULL;
end;$$
language 'plpgsql';

Aaron


---(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] Syntax Help Requested

[Rich Shepard]

On Mon, 17 Apr 2006, Tom Lane wrote:


That last is a SQL command, not a shell command, and anyway it would
default to assuming you were trying to grant privileges on a table named
"contacts" not a database named contacts.


Tom,

  I should have been clearer, that I was logged in to psql when I issued the
latter command.

  An off-list suggestion to use the syntax shown in psql's '\h grant' seems
to have worked:
 grant all priviledges on database contacts to xrms;
returned no errors.


You didn't show us any attempt to create anything in contacts ...


  The install script does this.


What exactly happens when you try to run the script?


  I get a bunch of error messages. I've passed those on to the xrms
developers.


With the default setup it shouldn't really matter whether xrms is the owner
of the contacts database or not.


  I didn't think so.

Thanks,

Rich

--
Richard B. Shepard, Ph.D. |  Quantifying subjectivity for the
Applied Ecosystem Services, Inc.(TM)  |  benefit of business and society.
 Voice: 503-667-4517  Fax: 503-667-8863

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

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

Re: [GENERAL] Will changing the server date/time cause problems?

[Tom Lane]

Justin Pasher <[EMAIL PROTECTED]> writes:
> A client of ours has requested that we change the time on the server to 
> match their time zone (one hour ahead of us). Are there any know issues 
> or gotchas to look out for when bumping the server time forward an hour? 
> I couldn't imagine any issues occurring, since it's really just like the 
> server when 1 hour without performing any activity. Obviously any fields 
> in the database stored with a time without a timezone would be off 
> afterwards, but that's not a huge concern.

> Another client has requested the same thing, but their time is two hours 
> behind us. This is the change that I'm more worried about, since you are 
> going to have some data/time overlap on the server after the change is 
> made. Are there any known issues with this situation in Postgres? Should 
> everything be shutdown prior to the change, then restarted again?

There shouldn't be any need to change the server's clock at all, at
least not if you are running a sane operating system that keeps the
underlying time in GMT.  What you should be doing is setting the default
value of PG's TimeZone parameter to match what these clients want.
Or suggest to them that they set it themselves on a per-session or
per-user basis.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Syntax Help Requested

[Joshua D. Drake]

>When I request a list of databases (psql -l), the one named contacts is
> there. However, when I open the database (psql contacts), there's no one
> home; that is, 'psql -d' returns 'no relations found.'
> 
>I need a clue on how to let user 'xrms' access the database 'contacts' so
> the install script runs correctly.

You can't grant all on multiple objects. You have to grant on each
object explicitly.

Joshua D. Drake



> 
> TIA,
> 
> Rich
> 
-- 

=== The PostgreSQL Company: Command Prompt, Inc. ===
  Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
  Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.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] Syntax Help Requested

[Tom Lane]

Rich Shepard <[EMAIL PROTECTED]> writes:
> postgres (from my user account):

> createdb contacts
> createuser xrms # This creates the ROLE xrms
> grant all on contacts to xrms; # This generates an error at 'to'.

That last is a SQL command, not a shell command, and anyway it would
default to assuming you were trying to grant privileges on a table
named "contacts" not a database named contacts.

What you probably really should do is

createuser xrms
createdb --owner=xrms contacts

and go from there.

>When I request a list of databases (psql -l), the one named contacts is
> there. However, when I open the database (psql contacts), there's no one
> home; that is, 'psql -d' returns 'no relations found.'

You didn't show us any attempt to create anything in contacts ...

>I need a clue on how to let user 'xrms' access the database 'contacts' so
> the install script runs correctly.

What exactly happens when you try to run the script?  With the default
setup it shouldn't really matter whether xrms is the owner of the
contacts database or not.

regards, tom lane

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

[GENERAL] Will changing the server date/time cause problems?

[Justin Pasher]

A client of ours has requested that we change the time on the server to 
match their time zone (one hour ahead of us). Are there any know issues 
or gotchas to look out for when bumping the server time forward an hour? 
I couldn't imagine any issues occurring, since it's really just like the 
server when 1 hour without performing any activity. Obviously any fields 
in the database stored with a time without a timezone would be off 
afterwards, but that's not a huge concern.


Another client has requested the same thing, but their time is two hours 
behind us. This is the change that I'm more worried about, since you are 
going to have some data/time overlap on the server after the change is 
made. Are there any known issues with this situation in Postgres? Should 
everything be shutdown prior to the change, then restarted again?


Thanks.

Justin Pasher

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

[GENERAL] COMMENT and inheritance

[Kaloyan Iliev]

Hello All,

I want to ask is there a way to inherit table column comments. I have a 
table that is base for almost all other tables in my database (they 
inherit it).
So the new tables receive their fields from the base table. But if I 
want to put comments on this fields I must write COMMENT for every table 
and columnt(nevertheless that they are all the same). So shouldn't it be 
good if there is no comment for the column in the child table but there 
is comment in the parent the comment to apear in the \d+ for the child 
table. I write comments to use postgresql_autodoc and this blank columns 
really makes me trouble.


Thanks for the answers in advance.

Kaloyan Iliev


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

[GENERAL] Syntax Help Requested

[Rich Shepard]

  I'm working with the XRMS Contact Management System developers to get the
application working with postgres as well as the original MySQL. My role is
trying what they've produced and reporting errors.

  The application runs on a httpd server (apache-1.3.34 here) with any
browser; similar to SQL-Ledger, but xrms is written in php rather than perl.

  I've not yet been able to get last Friday's CVS version to install properly
after I dropped the old database and created a new one. I'm still missing
something on the proper syntax to grant priviledges to these tables to the
application. I'd appreciate some help translating from mysql to postgres
here.

  Following are what the INSTALL file provides for MySQL, and what I've done
in postgresql-8.1.2.

mysql:

Create a MySQL database to hold the XRMS tables, and pick a valid MySQL
account for XRMS to use.

Example:
   from a mysql prompt
   create database xrms;
   grant all privileges on xrms.* to [EMAIL PROTECTED] identified by
'yourpasswordhere';

postgres (from my user account):

createdb contacts
createuser xrms # This creates the ROLE xrms
grant all on contacts to xrms; # This generates an error at 'to'.

  When I request a list of databases (psql -l), the one named contacts is
there. However, when I open the database (psql contacts), there's no one
home; that is, 'psql -d' returns 'no relations found.'

  I need a clue on how to let user 'xrms' access the database 'contacts' so
the install script runs correctly.

TIA,

Rich

--
Richard B. Shepard, Ph.D. |  Quantifying subjectivity for the
Applied Ecosystem Services, Inc.(TM)  |  benefit of business and society.
 Voice: 503-667-4517  Fax: 503-667-8863

---(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: [Slony1-general] [GENERAL] Is a high tab_reloid worrying?

[Tom Lane]

Jan Wieck <[EMAIL PROTECTED]> writes:
> The other thing that is eating OID's are temporary objects. I personally 
> consider the implementation of temp tables broken for precisely that 
> matter. If your application uses temp tables, sooner or later it will 
> cause an OID counter wrap around and then you run the risk of random 
> transaction failures due to duplicate key errors on CREATE TEMP TABLE.

Not as of 8.1.

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

Re: [Slony1-general] [GENERAL] Is a high tab_reloid worrying?

[Jan Wieck]

On 4/13/2006 6:19 AM, John Sidney-Woollett wrote:


My tables are defined "WITHOUT OID" - does that make a difference?


That's good so far.

The other thing that is eating OID's are temporary objects. I personally 
consider the implementation of temp tables broken for precisely that 
matter. If your application uses temp tables, sooner or later it will 
cause an OID counter wrap around and then you run the risk of random 
transaction failures due to duplicate key errors on CREATE TEMP TABLE.



Jan



John
Hannu Krosing wrote:> Ühel kenal päeval, N, 2006-04-13 kell 10:06, kirjutas John> Sidney-Woollett:> >>I just added a new table to a slony relication set. 
The new table seems >>to have a really high tab_reloid value of 94,198,669> > ...> >>Is this something I should be worried about? Can I find out 
where all >>the intermediate OIDs have gone?> > > probably to data rows, unless you have all your tables defined using> WITHOUT OID. OIDs are assigned 
from a global "sequence".> > > Hannu> > > ---(end of broadcast)---> TIP 4: Have you 
searched our list archives?> >http://archives.postgresql.org___Slony1-general mailing [EMAIL 
PROTECTED]://gborg.postgresql.org/mailman/listinfo/slony1-general



--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

  http://archives.postgresql.org

Re: [GENERAL] enforce naming convention

[Martijn van Oosterhout]

On Mon, Apr 17, 2006 at 07:26:24AM -0700, SunWuKung wrote:
> This may be totally wronb, but what about writing a Before Insert
> trigger on the pg_catalog.pg_class table checking if there is already a
> table with that name and if yes prevent the insert?
> Is there something against doing so?

Yes, triggers don't work on system catalogs.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [GENERAL] enforce naming convention

[SunWuKung]

I know that one of the main reasons why schemas are there is to make it
possible to use identical table names, but this time they want to use
it to separate tables purely because they feel they would find their
ways better that way. On the other hand I feel that this separation
will be somewhat arbitrary and may change in the future - to make sure
that tables will be freely movable between schemas I want to make sure
that there will be no identical names.

This may be totally wronb, but what about writing a Before Insert
trigger on the pg_catalog.pg_class table checking if there is already a
table with that name and if yes prevent the insert?
Is there something against doing so?

Thanks for the help.
SWK


---(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] hard shutdown of system

[Tom Lane]

"surabhi.ahuja" <[EMAIL PROTECTED]> writes:
> the user tries to do kill -9 -1 and log in again

The *first* thing you gotta do is retrain your user.  kill -9
is never the appropriate way to shut down the postmaster.

The script mods you describe seem to be oriented at forcing the
postmaster to restart when there are still live child processes
of the old postmaster.  That is a REALLY BAD IDEA.  It will lead
to unrecoverable corruption of your database.  The safety checks
that are in the postmaster are there to keep you from destroying
your database --- overriding them by removing the pid file is
not safe, recommended, or supported.

The last question is why your user wants to shut down the postmaster
so often?  There shouldn't be any need for that in ordinary scenarios.

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

Re: [GENERAL] Question about partitioning

[Devrim GUNDUZ]

Hi,

On Mon, 2006-04-17 at 15:43 +0300, Devrim GUNDUZ wrote:

> I have a question that I could not find the answer of.

Next time I'll scroll down a bit to find my answer. Sorry for the noise.

Regards,
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PL/php, plPerlNG - http://www.commandprompt.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

[GENERAL] Question about partitioning

[Devrim GUNDUZ]

Hi,

I am writing a tutorial (in Turkish) on partitioning and I have a
question that I could not find the answer of.

http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html

This doc suggests to write a rule in order to be sure that data is
entered to the latest partition. Also it says that we need to redefine
this rule every month (based on the example there).

So the question is: What if we forget to redefine this? Data will not be
entered to the table defined in the rule because the check constraint
will not allow this.

So, is there a better solution than running a cron job (which redefines
this rule) at the first second of each month?

Regards,
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/



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

   http://archives.postgresql.org

[GENERAL] catch SQLException, error code for Foeign key violation,

[surabhi.ahuja]

i am working with PostgreSQL 8.0.0
 
and the programming lang used is java
 
i have a stored procedure which inserts a row in a 
table
 
to this stored procedure i pass the values which 
have to be inserted in that row.
 
now, i execute this query(which is a call to stored 
procedure)
 
this execute query is in a try block
 
and i catch SQL exception for it.
 
The question is that is from this SQLException can 
i detect if it is a foreign key violation,
 
i saw there is a method on SQLException, 

e.getErrorCode, is ther specific error code for 
Foreign Key violation, 
 
thanks,
regards
Surabhi

Re: [GENERAL] hard shutdown of system

[surabhi.ahuja]

Title: Re: [GENERAL] hard shutdown of system






hi,
 
yah i took this script only and built upon 
that
 
the problem is that the 
statement
"pidof 
postmaster"
 
will return some valu even if 
 
ps -aef | grep postmaster does not return 
anything
 
if i do kill -9 
 
and i do pidof postmaster,
some machines it will show 
nothing
 
and on some machines it still shows some 
value, althou ps -aef | grep postmaster does not display anything.
 
so in such machines i ll have to check for 
both, pidof as well as ps -aef | grep postmaster.
 
is this ok?


From: chris smith 
[mailto:[EMAIL PROTECTED]Sent: Mon 4/17/2006 1:14 PMTo: 
surabhi.ahujaCc: pgsql-general@postgresql.orgSubject: Re: 
[GENERAL] hard shutdown of system

***Your mail has been scanned by 
InterScan VirusWall.***-***On 4/17/06, 
surabhi.ahuja <[EMAIL PROTECTED]> wrote:>> the user 
tries to do kill -9 -1 and log in again>> in the startup script i 
do the following>> /sbin/pidof -s postmaster>> and 
it still displays some value,>> however ps -aef | grep postmaster 
does not display anything>> is it ok if i do the 
following>  pid1=`/sbin/pidof -s postmaster`>  pid2=`ps 
-eaf | grep postmaster | grep -v grep | tail -1 | awk '{print> 
$2}'`>> if ($pid1 and $pid2)> => postmaster is already 
running>> otherwise>> i check if postmaster.pid 
exists> if it does, i delete it> and then start postmaster by 
doing $PGCTL -l $POSTGRES_LOG -D $PGDATA -p> $POSTMASTER -o '-p 
${PGPORT}' start  > /dev/null 2>&1Check out the startup 
script. Depending on what system you arerunning, this might already all be 
taken care of.Here's a mandrake example (I think the redhat version is 
pretty similar).http://techdocs.postgresql.org/scripts/mandrake72-startup--Postgresql 
& php tutorialshttp://www.designmagick.com/

Re: [GENERAL] Comparing text field

[Nik]

It is possible that one had /r/n and the other just /n. I was doing the
comparison using psql.

The issue was not using LIKE and %.

I resolved the problem by removing chr(13) from both sides. So my
working query was had a where clause like this:

WHERE REPLACE(message, chr(13), '') = REPLACE('', chr(13),
'')


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

   http://archives.postgresql.org

[GENERAL] tsearch2 installation problem

[Danish]

Hi Everyone,
I have installed postgresql 8.1.3.tar.gz. After compiling and
installing the postgresql is running fine but Im not able to install
tsearch2. I went under the postgresql source directory
/usr/local/postgresql8.1.3/contrib and then did a
#gmake all
#gmake install

After issuing these commands, a contrib directory was created under
/usr/local/pgsql/share

Going under the /usr/local/pgsql/share/contrib directory i ran the
command as user postgres

$psql -d dbname -f tsearch2.sql
SET
BEGIN
psql:tsearch2.sql:13: NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_dict_pkey" for table "pg_ts_dict"
CREATE TABLE
psql:tsearch2.sql:20: ERROR:  could not access file "$libdir/tsearch2":
No suchfile or directory
psql:tsearch2.sql:26: ERROR:  current transaction is aborted, commands
ignored until end of transaction block

then i got the above errors..
Can anyone please help me out as to what is going wrong
Thanks
Danish


---(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] Replacing MD5 hash in pg_auth...

[Andrew - Supernews]

On 2006-04-15, "Peter van der Maas" <[EMAIL PROTECTED]> wrote:
> Hello,
>
> Is it correct to assume that if a user has write permission to
> \data\global\pg_auth on a Win32 machine, the superuser's MD5 hash can be
> replaced with one of a known origin in order to own the DB?

It's worse than that. If you can _read_ pg_auth, then you can log in as
any user who has an MD5 password provided that pg_hba.conf allows md5
auth - the values stored in pg_auth (and pg_shadow) are password equivalents
for the purposes of md5 auth.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

---(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] Google Summer of Code (Fix aggregate operators & Implement features such as Data Cubes and Skyline)

[Robert Treat]

On Saturday 15 April 2006 22:33, Benjamin Arai wrote:
> Hi,
>
> Myself and a friend are PhD students at the University of California,
> Riverside.  We would be interested in such a project if it were available.
> We are both experienced developers previously interning at EA Games and
> current system administrators for the CS department.
>

I agree with Tom that this seems ambitious, however if you started looking at 
the code now (say for adding the simplest of the new functions you are 
thinking about) and submitted a full proposal, we could probably drum up a 
mentor to help push the project along. 

Robert Treat

> > -Original Message-
> > From: Tom Lane [mailto:[EMAIL PROTECTED]
> > Sent: Saturday, April 15, 2006 7:14 PM
> > To: Benjamin Arai
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Google Summer of Code (Fix aggregate
> > operators & Implement features such as Data Cubes and Skyline)
> >
> > "Benjamin Arai" <[EMAIL PROTECTED]> writes:
> > > A good project for the Google summer of code would be to solve the
> > > aggregate problems in PostgreSQL.  In addition, add some of
> >
> > the more
> >
> > > complex operators such as rollup and etc.
> >
> > That seems a tad ambitious, unless the SOCcer has already
> > done some nontrivial backend work ...
> >
> > 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

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---(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] Select first ten of each category?

[Dawid Kuroczko]

On 4/13/06, Benjamin Smith <[EMAIL PROTECTED]> wrote:
I'm stumped on this one...I have a table defined thusly:create table items (id serial,category integer not null references category(id),name varchar not null,price real,unique(category, name));
It has a LARGE number of entries. I'd like to grab the 10 most expensive itemsfrom each category in a single query. How can this be done? Something likeSelect items.*FROM itemswhere id IN (
select firstTen(id) FROM itemsgroup by categoryORDER BY price DESC)ORDER BY price desc;But I've not found any incantation to make this idea work...

I came up with something like this:

SELECT i.* FROM items i JOIN (
    SELECT category, (
    SELECT price FROM items ii
    WHERE ii.category=io.category
    ORDER BY price DESC OFFSET 9 LIMIT 1
    ) AS date FROM items io GROUP BY category) AS sel
    ON (i.category=sel.category AND i.price >= sel.price);

I,e.  First do a select which will return ninth price of each
category (two inner selects) and then JOIN it with a whole
table, where category and price match they way you want.

Keep in mind, if you happen to have same prices at position
near 10th, you'll end up having more than 10 returns per
given category.  Either filter it at application level, or embed
some additional key inside the join condition (like
ON (i.category=sel.category AND (i.price > sel.price OR
(i.price=sel.price AND i.id=sel.last_id)));

  Regards,
    Dawid

Re: [GENERAL] hard shutdown of system

[chris smith]

On 4/17/06, surabhi.ahuja <[EMAIL PROTECTED]> wrote:
>
> the user tries to do kill -9 -1 and log in again
>
> in the startup script i do the following
>
> /sbin/pidof -s postmaster
>
> and it still displays some value,
>
> however ps -aef | grep postmaster does not display anything
>
> is it ok if i do the following
>  pid1=`/sbin/pidof -s postmaster`
>  pid2=`ps -eaf | grep postmaster | grep -v grep | tail -1 | awk '{print
> $2}'`
>
> if ($pid1 and $pid2)
> => postmaster is already running
>
> otherwise
>
> i check if postmaster.pid exists
> if it does, i delete it
> and then start postmaster by doing $PGCTL -l $POSTGRES_LOG -D $PGDATA -p
> $POSTMASTER -o '-p ${PGPORT}' start  > /dev/null 2>&1

Check out the startup script. Depending on what system you are
running, this might already all be taken care of.

Here's a mandrake example (I think the redhat version is pretty similar).

http://techdocs.postgresql.org/scripts/mandrake72-startup

--
Postgresql & php tutorials
http://www.designmagick.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