Re: [GENERAL] Autograph Annoucement (ERD Tool)

[A. Kretschmer]

am  Thu, dem 03.04.2008, um 15:40:41 -0700 mailte Colin Fox folgendes:
> Hello everyone.
> 
> There were a number of people asking about ERD tools here a while ago,
> so I decided to publish one that I've put together.
> 
> It's called Autograph, and you can find it on the pg foundry:
> 
> http://pgfoundry.org/projects/autograph/

I have added the url to the fine IRC-docbot:

09:47 < akretschmer> ??erd
09:47 < rtfm_please> For information about erd
09:47 < rtfm_please> see http://druid.sf.net/
09:47 < rtfm_please> or http://schemaspy.sourceforge.net/
09:47 < rtfm_please> or http://uml.sourceforge.net/index.php
09:47 < akretschmer> ?learn erd http://pgfoundry.org/projects/autograph/


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to insert values into complex type field

[windwxc]

hi all, i want to know how to insert values into the field which is a complex 
type. In fact it is a complex type which also include a complex type. The 
following is its definition:
create TYPE lifetime as( strattime date, endtime date);
create TYPE attributetype as( ID numeric, address character(50), periodspan 
lifetime);
create TABLE attribute2005( gid serial, allfield attributetype);
now i want to insert data into the table attribute2005 but always failure so 
wish someone can help.
my sql is following:
INSERT INTO attribute2005 VALUES(1,(23,'ee','ttt',('2005-01-01','2005-12-31')));
my postgresql is 8.2 and is installed in windows XP.
thank you!
 
 xiaochun wu

---
外企高薪职位急聘( 
http://d1.sina.com.cn/sina/limeng3/mail_zhuiyu/2008/mail_zhuiyu_20080331.html )

---
注册新浪2G免费邮箱（http://mail.sina.com.cn/）

Re: [GENERAL] Secure "where in(a,b,c)" clause.

[William Temperley]

Thanks for the replies,

"Rodrigo E. De León Plicet" <[EMAIL PROTECTED]> wrote:
>Use a prepared query and ANY, e.g.:
>select st_collect(the_geom) from tiles
>where tilename = any('{foo,bar,baz}');

Thanks, that's what I was looking for!
$sql = "select uid, accredited as acc, x(the_geom), y(the_geom) from clubs
where st_within(the_geom, (select st_collect(the_geom) from tiles
where tilename = any($1)))";

$result = pg_query_params($sql, array('{'.$tilearr.'}'));

Though a regex would do as well I guess.

Cheers

Will

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] is it helpful for the optimiser/planner to add LIMIT 1

[Gregory Stark]

"Ivan Sergio Borgonovo" <[EMAIL PROTECTED]> writes:

> Summarising it up: is it worth to add it here and there as an
> optimisation flag?

Probably not. Unless you're not planning on reading all the resulting records
anyways and want the planner to optimize with that assumption.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autograph Annoucement (ERD Tool)

[Colin Fox]

Colin Wetherbee wrote:
>
>>
>> Can you look at the PS file, and make sure that you're at least getting
>> a legitimate postscript diagram?
>
> It turned out I had incorrectly added my schema information.
>
> Works now, with NetPBM.
>
> Cool tool!
Glad you like it. I've found it personally indispensable. :)
>
> Thanks.
>
> Also, is there a way to forcibly remove tables and views from the
> output?  In all.xml, I only list the tables I want, but in addition to
> those, I also get my PostGIS tables and all the views I've created
> within the schema.
>
> Colin
If you look in the autograph.xsl stylesheet, you'll see excludetables &
excludeschemas. I started work on this, but got bogged down in exactly
what the xsl for that would be.

If you want to get it working and send me the patches, I'll be more than
happy to apply them and give you attribution. ;)


begin:vcard
fn:Colin Fox
n:Fox;Colin
org:CF Consulting Inc.
adr:;;#330-1152 Mainland St.;Vancouver;BC;V6B 4X2;Canada
email;internet:[EMAIL PROTECTED]
title:President
tel;work:(604) 681 5282
tel;cell:(778) 838 7887
x-mozilla-html:TRUE
url:http://cfconsulting.ca
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] simple update queries take a long time - postgres 8.3.1

[mark]

On Thu, Apr 3, 2008 at 10:02 PM, Greg Smith <[EMAIL PROTECTED]> wrote:

> On Wed, 2 Apr 2008, mark wrote:
>
> > with no clients connected to the database when I try to shutdown the
> > database [to apply new settings], it says database cant be shutdown.. for a
> > long time both in smart and normal mode... then i had to go to immediate
> > mode to shut down.. but then when i start it again.. it goes into recovery
> > mode and runs for a while..is this the long recover time you are talking
> > about?
> >
>
> If you couldn't shutdown using fast, that's something that deserves some
> investigation. That shouldn't happen unless there's a bad situation.


that is what I intended to write. I tried both smart and fast mode. and it
says 'database wont shutdown', and immediate is the only mode that shutsdown
the database.
 -> is shutting down in immediate mode a safe thing to do?
 -> how do i investigate why fast mode doesnt work?

stats with new settings are below..
  -> but even with this sometimes update queries take more than coupla
seconds sometimes...
  -> now i have a few ' in transaction' statements which I did not
have before.. is this OK?
  -> sometimes INSERT statements are also slow. is there any settings I can
tweak to make INSERT statements fast?

bgwriter_lru_maxpages = 200 # 0-1000 max buffers written/round
checkpoint_segments = 96# in logfile segments, min 1, 16MB
each
checkpoint_timeout = 20min  # range 30s-1h
checkpoint_completion_target = 0.8  # checkpoint target duration, 0.0 -
1.0

postgres=# select * from pg_stat_bgwriter;
 checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
maxwritten_clean | buffers_backend | buffers_alloc
---+-++---+--+-+---
67 |   0 |4012010 |330679
|  570 |  184569 |   5379667
(1 row)

[GENERAL] Conversion to 8.3

[Terry Lee Tucker]

Greetings:

I am converting our application from 7.4.19 to 8.3.1. In the old scheme of 
things, I was generating an interval between two timestamps and evaluating 
the interval string in another set of trigger code. I was doing the 
following:
IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN
.
.
END IF;
With the new casting rules, this doesn't work. How can I determine if this 
on-time value is "ago", that is, the shipment is late?

Thanks for any help you can give...
-- 
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
[EMAIL PROTECTED]
www.turbocorp.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how to insert values into complex type field

[Stephan Szabo]

On Fri, 4 Apr 2008 [EMAIL PROTECTED] wrote:

> hi all, i want to know how to insert values into the field which is a complex 
> type. In fact it is a complex type which also include a complex type. The 
> following is its definition:
> create TYPE lifetime as( strattime date, endtime date);

> create TYPE attributetype as( ID numeric, address character(50),
> periodspan lifetime);

> create TABLE attribute2005( gid serial, allfield attributetype);
> now i want to insert data into the table attribute2005 but always failure so 
> wish someone can help.
> my sql is following:

> INSERT INTO attribute2005
> VALUES(1,(23,'ee','ttt',('2005-01-01','2005-12-31')));

I'm running on 8.3, but in that version at least, it looks like you have
a few options for the values and one of these should hopefully work in
8.2.

VALUES (1, ROW(23, 'ee', ROW('2005-01-01', '2005-12-31')));
VALUES (1, '(23,"ee","(2005-01-01,2005-12-31)")')
There are other slight variations on this second one, you can remove the
double quotes around ee and it looks like you can add double quotes around
the dates, etc.

It also looks like your attibute type above only had 2 scalars and the
complex type rather than three, so I've dropped the 'ttt' for the examples
above.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection reset by peer / broken pipe

[Jeff Wigal (Referee Assistant)]

I'm in the process of tracking down the cause of this... Is there any way on
the server side of things to terminate a connection after "x" number of
minutes?  For what we're doing, there is no reason to have a connection open
after 10 minutes.

Thanks in advance--

On Tue, Apr 1, 2008 at 5:44 PM, Jeff Wigal (Referee Assistant) <
[EMAIL PROTECTED]> wrote:

> That's possible.  They are communicating with the server using MS Access,
> which is connecting to the server through the Postgres ODBC driver.
>
> On Tue, Apr 1, 2008 at 5:12 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> > "Jeff Wigal (Referee Assistant)" <[EMAIL PROTECTED]> writes:
> > > I am running Postgres 8.2.3 and am seeing the following error messages
> > in my
> > > logs:
> >
> > > LOG:  SSL SYSCALL error: Connection reset by peer
> > > LOG:  could not receive data from client: Connection reset by peer
> > > LOG:  unexpected EOF on client connection
> > > LOG:  could not send data to client: Broken pipe
> >
> > Do your client applications tend to leave an open connection sitting
> > idle for awhile?  If so you might be getting burnt by idle-connection
> > timeouts in intervening routers.  NAT-capable boxes in particular
> > will kill a connection that carries no data for "too long".  If you're
> > lucky the router will offer a way to adjust its timeout ...
> >
> >regards, tom lane
> >
>
>

[GENERAL] ERROR: XX000: cache lookup failed for relation

[Glyn Astill]

Hi chaps,

I've got a problem trying to drop a table, I get the error "cache lookup failed 
for relation"

SEE=# drop table replicated_users;
ERROR:  XX000: cache lookup failed for relation 30554884
LOCATION:  getRelationDescription, dependency.c:2021
Now this table is on a slony-I slave and was in replication when I tried to 
drop it - I presume this is a big mistake and I should never try to drop a 
table without first droping it from replication?

In addition I'd set up a trigger on the table "replicate_users".

If I do:

 select relname,oid from pg_class where relname = 'replicated_users';

-[ RECORD 1 ]-
relname | replicated_users
oid | 30554879

Thats not the same oid as the one it's complaining about.

Does anyone have any idea why this has happened or how I can fix it?

Cheers
Glyn






  ___ 
Yahoo! For Good helps you make a difference  

http://uk.promotions.yahoo.com/forgood/


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Secure "where in(a,b,c)" clause.

[Tino Wildenhain]

Steve Atkins wrote:
...

I count the number of values that I want to put in the IN () clause,
then create a query string with the right number of bind variables
in the in clause, then bind the values.

So for {1, 3, 5} I'd use "select * from foo where bar in (?, ?, ?)" and for
{1,5,7,9,11} I'd use "select * from foo where bar in (?, ?, ?, ?, ?)"

Then, in perl-speak, I prepare that string into a query, loop through
all my values and bind them one by one, then execute the query.


You mean something like:

items=(1,2,5,6,9)

cursor.execute("SELECT ... FROM foo where bar in (%s)" % 
','.join('?'*len(items)),items)


? :-)

Oh.. I forgot he said PHP...

SCNR
Tino

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autograph Annoucement (ERD Tool)

[Colin Fox]

Colin Wetherbee wrote:
> Colin Wetherbee wrote:
>> Also, is there a way to forcibly remove tables and views from the
>> output?  In all.xml, I only list the tables I want, but in addition
>> to those, I also get my PostGIS tables and all the views I've created
>> within the schema.
>
> And...
>
> If I set the tablemode to "detailed", all the lines terminate at
> primary keys, instead of terminating at a primary key and a foreign
> key, which, I assume, is what should happen.
>
Yes, this is odd. I don't actually use the detailed view myself. Once I
got it working, I never really found a need for it. So I haven't looked
at a detailed diagram for some time. However, I do remember it working,
and I know that I've put code in to connect the various fields, so I'm
not sure at what point it got broken.


> Here's the sample "detailed" output from one of my databases:
>
> http://colinwetherbee.com/data/js-20080403.png
>
> Thanks again for this neat, lean tool.
>
> Colin

begin:vcard
fn:Colin Fox
n:Fox;Colin
org:CF Consulting Inc.
adr:;;#330-1152 Mainland St.;Vancouver;BC;V6B 4X2;Canada
email;internet:[EMAIL PROTECTED]
title:President
tel;work:(604) 681 5282
tel;cell:(778) 838 7887
x-mozilla-html:TRUE
url:http://cfconsulting.ca
version:2.1
end:vcard


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Conversion to 8.3

[Craig Ringer]

Terry Lee Tucker wrote:

Greetings:

I am converting our application from 7.4.19 to 8.3.1. In the old scheme of 
things, I was generating an interval between two timestamps and evaluating 
the interval string in another set of trigger code. I was doing the 
following:

IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN
  
If I understand your question correctly, you're seeking to determine if 
new.ontime is "in the past". If so, compare with current_timestamp / 
current_date as appropriate, eg:


IF new.ontime IS NOT NULL AND new.ontime < current_timestamp THEN

(Note that current_timestamp and current_date are constant within a 
transaction, so they might not be suitable if you have really long 
running transactions).


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Upgrading from 8.2.0 to 8.3.1

[Bhat, Suma]

We are upgrading our system from postgresql-8.2.0 to postgresql-8.3.1.

 

With postgresql-8.2.0, 

 

SELECT c1.relname as PKTABLE_NAME,c2.relname as FKTABLE_NAME,
t1.tgconstrname,a.attnum as keyseq,ic.relname as
fkeyname,t1.tgdeferrable,t1.tginitdeferred,
t1.tgnargs,   t1.tgargs   FROM pg_catalog.pg_namespace n1
JOIN pg_catalog.pg_class c1 ON (c1.relnamespace = n1.oid) JOIN
pg_catalog.pg_index i ON (c1.oid = i.indrelid) JOIN
pg_catalog.pg_class ic ON (i.indexrelid = ic.oid) JOIN
pg_catalog.pg_attribute a ON (ic.oid = a.attrelid),
pg_catalog.pg_namespace n2 JOIN pg_catalog.pg_class c2 ON
(c2.relnamespace = n2.oid), pg_catalog.pg_trigger t1 JOIN
pg_catalog.pg_proc p1 ON (t1.tgfoid = p1.oid),
pg_catalog.pg_trigger t2 JOIN pg_catalog.pg_proc p2 ON (t2.tgfoid =
p2.oid) WHERE (t1.tgrelid=c1.oid   AND t1.tgisconstraint   AND
t1.tgconstrrelid=c2.oid   AND p1.proname LIKE 'RI\\_FKey\\_%\\_upd')
AND (t2.tgrelid=c1.oid   AND t2.tgisconstraint   AND
t2.tgconstrrelid=c2.oid   AND p2.proname LIKE 'RI\\_FKey\\_%\\_del')
AND i.indisprimary  AND n2.nspname = 'public' ORDER BY FKTABLE_NAME ,
tgconstrname, keyseq;

 

Would come back with values like:

ref_a_c_l_right_sets_a_c_l\000a_c_l_right_sets\000a_c_l\000UNSPECIFIED\0
00a_c_l_id\000a_c_l_id\000

 

for  t1.tgargs.

 

but with postgresql-8.3.1, t1.args values are empty.

 

Can someone please explain what changed from 8.2.0 to 8.3.1 and how I
need to update this query ?

 

Thanks much,

Suma

 

 

Re: [GENERAL] how to insert values into complex type field

[Stephane Bortzmeyer]

On Fri, Apr 04, 2008 at 04:32:36PM +0800,
 [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote 
 a message of 74 lines which said:

> INSERT INTO attribute2005 
> VALUES(1,(23,'ee','ttt',('2005-01-01','2005-12-31')));

And why did you not post the error message? Because it is very clear:

ERROR:  malformed record literal: "ttt"
DETAIL:  Missing left parenthesis.

Indeed, attributetype has no column for this 'ttt' value. The correct
INSERT is:

INSERT INTO attribute2005 VALUES(1,(23,'ee',('2005-01-01','2005-12-31')));

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading from 8.2.0 to 8.3.1

[Bhat, Suma]

but with postgresql-8.3.1, ***t1.tgargs***  values are empty - I meant

 

sorry about the typo.

 

-Suma

 

 



From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Bhat, Suma
Sent: Friday, April 04, 2008 3:07 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Upgrading from 8.2.0 to 8.3.1

 

We are upgrading our system from postgresql-8.2.0 to postgresql-8.3.1.

 

With postgresql-8.2.0, 

 

SELECT c1.relname as PKTABLE_NAME,c2.relname as FKTABLE_NAME,
t1.tgconstrname,a.attnum as keyseq,ic.relname as
fkeyname,t1.tgdeferrable,t1.tginitdeferred,
t1.tgnargs,   t1.tgargs   FROM pg_catalog.pg_namespace n1
JOIN pg_catalog.pg_class c1 ON (c1.relnamespace = n1.oid) JOIN
pg_catalog.pg_index i ON (c1.oid = i.indrelid) JOIN
pg_catalog.pg_class ic ON (i.indexrelid = ic.oid) JOIN
pg_catalog.pg_attribute a ON (ic.oid = a.attrelid),
pg_catalog.pg_namespace n2 JOIN pg_catalog.pg_class c2 ON
(c2.relnamespace = n2.oid), pg_catalog.pg_trigger t1 JOIN
pg_catalog.pg_proc p1 ON (t1.tgfoid = p1.oid),
pg_catalog.pg_trigger t2 JOIN pg_catalog.pg_proc p2 ON (t2.tgfoid =
p2.oid) WHERE (t1.tgrelid=c1.oid   AND t1.tgisconstraint   AND
t1.tgconstrrelid=c2.oid   AND p1.proname LIKE 'RI\\_FKey\\_%\\_upd')
AND (t2.tgrelid=c1.oid   AND t2.tgisconstraint   AND
t2.tgconstrrelid=c2.oid   AND p2.proname LIKE 'RI\\_FKey\\_%\\_del')
AND i.indisprimary  AND n2.nspname = 'public' ORDER BY FKTABLE_NAME ,
tgconstrname, keyseq;

 

Would come back with values like:

ref_a_c_l_right_sets_a_c_l\000a_c_l_right_sets\000a_c_l\000UNSPECIFIED\0
00a_c_l_id\000a_c_l_id\000

 

for  t1.tgargs.

 

but with postgresql-8.3.1, t1.args values are empty.

 

Can someone please explain what changed from 8.2.0 to 8.3.1 and how I
need to update this query ?

 

Thanks much,

Suma

 

 

[GENERAL] Question about pg_catalog.pg_trigger.

[Bhat, Suma]

We are upgrading our system from postgresql-8.2.0 to postgresql-8.3.1.

 

With postgresql-8.2.0, 

 

SELECT c1.relname as PKTABLE_NAME,c2.relname as FKTABLE_NAME,
t1.tgconstrname,a.attnum as keyseq,ic.relname as
fkeyname,t1.tgdeferrable,t1.tginitdeferred,
t1.tgnargs,   t1.tgargs   FROM pg_catalog.pg_namespace n1
JOIN pg_catalog.pg_class c1 ON (c1.relnamespace = n1.oid) JOIN
pg_catalog.pg_index i ON (c1.oid = i.indrelid) JOIN
pg_catalog.pg_class ic ON (i.indexrelid = ic.oid) JOIN
pg_catalog.pg_attribute a ON (ic.oid = a.attrelid),
pg_catalog.pg_namespace n2 JOIN pg_catalog.pg_class c2 ON
(c2.relnamespace = n2.oid), pg_catalog.pg_trigger t1 JOIN
pg_catalog.pg_proc p1 ON (t1.tgfoid = p1.oid),
pg_catalog.pg_trigger t2 JOIN pg_catalog.pg_proc p2 ON (t2.tgfoid =
p2.oid) WHERE (t1.tgrelid=c1.oid   AND t1.tgisconstraint   AND
t1.tgconstrrelid=c2.oid   AND p1.proname LIKE 'RI\\_FKey\\_%\\_upd')
AND (t2.tgrelid=c1.oid   AND t2.tgisconstraint   AND
t2.tgconstrrelid=c2.oid   AND p2.proname LIKE 'RI\\_FKey\\_%\\_del')
AND i.indisprimary  AND n2.nspname = 'public' ORDER BY FKTABLE_NAME ,
tgconstrname, keyseq;

 

Would come back with values like:

ref_a_c_l_right_sets_a_c_l\000a_c_l_right_sets\000a_c_l\000UNSPECIFIED\0
00a_c_l_id\000a_c_l_id\000

 

for  t1.tgargs.

 

but with postgresql-8.3.1, t1.args values are empty.

 

Can someone please explain what changed and how I need to update this
query ?

 

Thanks much,

Suma

 

 

 

 

[GENERAL] creating a temp table in a function

[Shahaf Abileah]

I have a function that creates a temp table and drops it on commit.  If
I run the function twice in the same psql interactive session, I get an
error.  If I run it twice in two different psql sessions (using the -c
flag), I get no error.  Is this expected behavior?  If so, why?

 

You are now connected to database "test".

test=# CREATE OR REPLACE FUNCTION test_function() RETURNS void AS $t$

test$# BEGIN

test$# create temp table my_temp_table(id bigint) on commit drop;

test$# insert into my_temp_table values(0);

test$# END;

test$# $t$ LANGUAGE plpgsql;

CREATE FUNCTION

test=# select test_function();

 test_function

---

 

(1 row)

 

test=# select test_function();

ERROR:  relation with OID 70828339 does not exist

CONTEXT:  SQL statement "INSERT INTO my_temp_table values(0)"

PL/pgSQL function "test_function" line 3 at SQL statement

test=# \q

[EMAIL PROTECTED] ~]$ psql -U postgres -d test -c "select
test_function()"

Password for user postgres:

 test_function

---

 

(1 row)

 

[EMAIL PROTECTED] ~]$ psql -U postgres -d test -c "select
test_function()"

Password for user postgres:

 test_function

---

 

(1 row)

 

 

 

Shahaf Abileah | Lead Software Developer - Data Team

[EMAIL PROTECTED]   | tel: 206.859.2869 |
cell: 206.331.2057 | www.redfin.com  

 

[GENERAL] Exception messages -> application?

[Frank Miles]

First of all, many thanks to all the developers for creating such a great DB.

I have a moderately DB-ignorant question: is there a "built-in" way for an
application to receive the message emitted by a RAISE  in a PgSQL function?

Context: I have a moderately complex application (in python, using psycopg2)
that we use to help guide our research support operations.  Occasionally 
the application doesn't allow the user to do something because of some 
trigger or other PgSQL function has raised an exception.  While sometimes the 
application can make a plausible conjecture as to the cause, other times

it is necessary to dig through the database logs to read the message emitted
by the server-side DB function.  This is frustrating for the users.

Presumably the function could do something like writing the message to 
a special-purpose table that the user application could read (but perhaps

only when not in a transaction); or perhaps some sort of notify/listen system
could be set up.  What method[s] are generally the best in these circumstances?
It would be best if it didn't require changing all of the PgSQL functions
(ouch!), though that is not impossible.

Thanks for your insightful ideas!

-f

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] creating a temp table in a function

[Ivan Sergio Borgonovo]

On Fri, 4 Apr 2008 09:13:18 -0700
"Shahaf Abileah" <[EMAIL PROTECTED]> wrote:

> I have a function that creates a temp table and drops it on
> commit.  If I run the function twice in the same psql interactive
> session, I get an error.  If I run it twice in two different psql
> sessions (using the -c flag), I get no error.  Is this expected
> behavior?  If so, why?

http://www.postgresql.org/docs/faqs.FAQ.html#item4.19

?


-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] creating a temp table in a function

[Andreas 'ads' Scherbaum]

On Fri, 4 Apr 2008 09:13:18 -0700 Shahaf Abileah wrote:

> I have a function that creates a temp table and drops it on commit.  If
> I run the function twice in the same psql interactive session, I get an
> error.  If I run it twice in two different psql sessions (using the -c
> flag), I get no error.  Is this expected behavior?  If so, why?

Create a query in a text string and run the query with EXECUTE.

In your case the OIDs are determined during parsing, but in the next
function call the temp table is gone away. So you have to make sure the
table is dynamically created.


Kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_dump and schemas

[Rusty Conover]

Hi All,

Is there a way to pass a parameter to pg_dump that would make the  
produced dump be loaded into a different schema rather then the one it  
is being dumped from?  Basically be able to say dump out of public,  
but write the dump so its restored to say "test1".


Thanks,

Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question about pg_catalog.pg_trigger.

[Tom Lane]

"Bhat, Suma" <[EMAIL PROTECTED]> writes:
> but with postgresql-8.3.1, t1.args values are empty.

Yes, the RI triggers no longer care about tgargs.  They look at the
FK constraint's pg_constraint entry, instead.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] creating a temp table in a function

[Raymond O'Donnell]

On 04/04/2008 20:47, Andreas 'ads' Scherbaum wrote:


I have a function that creates a temp table and drops it on commit.  If
I run the function twice in the same psql interactive session, I get an
error.  If I run it twice in two different psql sessions (using the -c
flag), I get no error.  Is this expected behavior?  If so, why?


Create a query in a text string and run the query with EXECUTE.


Wasn't this fixed in 8.3? - Is it not the case that you no longer have 
to handle temp tables in plpgsql functions via EXECUTE?


Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Out of memory

[Alex Adriaanse]

Tom Lane wrote:

Alex Adriaanse <[EMAIL PROTECTED]> writes:
  
Unfortunately, we do not have any core dumps from those.  Is there 
anything else I can provide to make tracing this easier?  Could we use 
the addresses mentioned in the segfault messages for anything useful?



Hmm, you could try attaching to a running Postgres process with gdb
and doing "x/i 0xHEXADDRESS" --- that should at least come up with a
routine name, unless your kernel is into address randomization ...
  
We weren't able to do this on the production server where PostgreSQL 
crashed, but we did try to recreate as close a setup as possible on 
another similar server with the same version of PostgreSQL 8.1.9.  It is 
possible that some of the libraries are different or that we're using 
different binaries of the same version of PostgreSQL.  Thus, it might be 
possible that these particular addresses have shifted due to the 
different environment and now point to irrelevant instructions.  But in 
case they haven't, here's the output I got:


(gdb) x/i 0x0049ea35
0x49ea35 :callq  0x562c00 

(gdb) x/i 0x0049ea00
0x49ea00 :   mov%rbx,0xffd0(%rsp)

(gdb) x/i 0x005e7b13
0x5e7b13 : mov0xfff0(%rdi),%rdi

I hope this helps.

Alex


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] creating a temp table in a function

[Alvaro Herrera]

Raymond O'Donnell wrote:
> On 04/04/2008 20:47, Andreas 'ads' Scherbaum wrote:
>
>>> I have a function that creates a temp table and drops it on commit.  If
>>> I run the function twice in the same psql interactive session, I get an
>>> error.  If I run it twice in two different psql sessions (using the -c
>>> flag), I get no error.  Is this expected behavior?  If so, why?
>>
>> Create a query in a text string and run the query with EXECUTE.
>
> Wasn't this fixed in 8.3? - Is it not the case that you no longer have  
> to handle temp tables in plpgsql functions via EXECUTE?

Yes, it should work on 8.3.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Conversion to 8.3

[Tom Lane]

Terry Lee Tucker <[EMAIL PROTECTED]> writes:
> I am converting our application from 7.4.19 to 8.3.1. In the old scheme of 
> things, I was generating an interval between two timestamps and evaluating 
> the interval string in another set of trigger code. I was doing the 
> following:
> IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN
> With the new casting rules, this doesn't work.

Well, you could force it to work by casting new.ontime to text
explicitly, but this is a pretty horrid way of testing for a negative
interval anyhow.  I'd be inclined to do something like
new.ontime < '0 seconds'

BTW, the IS NOT NULL test is redundant too, since the comparison
can't succeed for a null.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] creating a temp table in a function

[Andreas 'ads' Scherbaum]

On Fri, 04 Apr 2008 21:22:17 +0100 Raymond O'Donnell wrote:

> On 04/04/2008 20:47, Andreas 'ads' Scherbaum wrote:
> 
> >> I have a function that creates a temp table and drops it on commit.  If
> >> I run the function twice in the same psql interactive session, I get an
> >> error.  If I run it twice in two different psql sessions (using the -c
> >> flag), I get no error.  Is this expected behavior?  If so, why?
> > 
> > Create a query in a text string and run the query with EXECUTE.
> 
> Wasn't this fixed in 8.3? - Is it not the case that you no longer have 
> to handle temp tables in plpgsql functions via EXECUTE?

Uhm, every day you learn something new.

Thanks for updating me, guys.


Kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] psql: FATAL: role "xyz" is not permitted to log in

[Ralph Smith]

I just can't connect to a database, though I can to others as other  
users...


postgres=# CREATE ROLE xyz  WITH password 'abc' ;
CREATE ROLE

postgres=# CREATE DATABASE LabNews_dev WITH OWNER=xyz ENCODING='UTF8' ;
CREATE DATABASE

postgres=# grant create, connect on database LabNews_dev to xyz with  
grant option;

GRANT

In pg_hba.conf:
local   LabNews_dev
labnews  trust
hostLabNews_dev  labnews ###.###.###.###/32   trust  #  
crushinator (Taylor)

---
In postgresql.conf:
listen_addresses = '*'
#ssl = true # (change requires restart)
#password_encryption = on
--
I've reloaded (& restarted) the DB

===
WHAT DOESN'T WORK:

[EMAIL PROTECTED]:~/8.2/main$ psql -U xyz  LabNews_dev
psql: FATAL:  database "LabNews_dev" does not exist

okay...
[EMAIL PROTECTED]:~/8.2/main$ psql -U xyz  labnews_dev
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "labnews",  
database "labnews_dev", SSL off


AND REMOTELY:
[EMAIL PROTECTED]:~$ psql -h thathost -U xyz labnews_dev
Password for user labnews:
psql: FATAL:  password authentication failed for user "labnews"

And I have ALTER ROLE'd to assure I set the password right.

=
HOWEVER:

[EMAIL PROTECTED]:~/8.2/main$ psql -U otherdbuser   otherdb
Welcome to psql 8.2.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

airburst=#

and
[EMAIL PROTECTED]:~$ psql -h flexo -U otherdbuser   otherdb
Welcome to psql 8.2.7 (server 8.2.6), the PostgreSQL interactive  
terminal.


Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

airburst=# \q


What am I missing???

Thanks!


Ralph
=

Re: [GENERAL] psql: FATAL: role "xyz" is not permitted to log in

[Tom Lane]

Ralph Smith <[EMAIL PROTECTED]> writes:
> I just can't connect to a database, though I can to others as other  
> users...

You're confused about the rules for case sensitivity.

> postgres=# CREATE DATABASE LabNews_dev WITH OWNER=xyz ENCODING='UTF8' ;
> CREATE DATABASE

This got folded to lower case, since you didn't double-quote the
name.  However, most places where you use a database name outside
the context of SQL commands, no such folding occurs --- in particular
not on psql's command line, and IIRC not in pg_hba.conf either.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] psql: FATAL: role "xyz" is not permitted to log in

[Ralph Smith]

On Apr 4, 2008, at 2:11 PM, Tom Lane wrote:
---

Ralph Smith <[EMAIL PROTECTED]> writes:

I just can't connect to a database, though I can to others as other
users...


You're confused about the rules for case sensitivity.

postgres=# CREATE DATABASE LabNews_dev WITH OWNER=xyz  
ENCODING='UTF8' ;

CREATE DATABASE


This got folded to lower case, since you didn't double-quote the
name.  However, most places where you use a database name outside
the context of SQL commands, no such folding occurs --- in particular
not on psql's command line, and IIRC not in pg_hba.conf either.

regards, tom lane


However I'm now trying to access it using all lower case.
Are you saying that IT'S confused and I should go all lower or use  
quotes (") not (')?


Thanks again,
Ralph Smith


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Direct access to GIST structure

[Tim Keitt]

I was wondering if there is a way to get access to the tree structure
underlying at GIST index? What I would like to do is perform a
breadth-first search on the tree in order to find nearest neighbors
(using PostGIS data types). Nearest neighbor searchers are not very
efficient using only range queries as it is not known a priori how
large a range to sample. Searching the tree is very fast as the tree
encode spatial proximity. Is there an API (backend C-level is fine) to
access a GIST index?

THK

-- 
Timothy H. Keitt, Assistant Professor
University of Texas at Austin http://www.keittlab.org/
Contact info and schedule at http://www.keittlab.org/~tkeitt/
Please support open document formats http://opendocument.xml.org/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Out of memory

[Tom Lane]

Alex Adriaanse <[EMAIL PROTECTED]> writes:
> ... possible that these particular addresses have shifted due to the 
> different environment and now point to irrelevant instructions.  But in 
> case they haven't, here's the output I got:

> (gdb) x/i 0x0049ea35
> 0x49ea35 :callq  0x562c00 

> (gdb) x/i 0x0049ea00
> 0x49ea00 :   mov%rbx,0xffd0(%rsp)

> (gdb) x/i 0x005e7b13
> 0x5e7b13 : mov0xfff0(%rdi),%rdi

Hmm, not exactly what I was expecting to see.  The last one looks like
someone passed garbage (maybe a NULL) to pfree; which would be a bug but
it's not clear how memory pressure would result in that, and without
knowing where pfree was called from we're not going to be able to get
far investigating.

The first two both seem like they could only be explained by running out
of execution stack space.  8.1 takes the max_stack_depth setting you
tell it as gospel, so a core dump right there is possible if you set
max_stack_depth too large, but you didn't mention having changed it.
In any case it's not clear why you'd get a transient spate of problems
like that, unless the system was handlng especially (and unreasonably)
complicated queries for awhile.  Did you query the client about whether
their workload could have been unusual during this episode?

Of course this is all just speculation since we can't trust the
addresses completely.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] too many LWLocks taken

[Alex Vinogradovs]

Guys,


I've got a pretty large database, and since certain time
it started giving me "too many LWLocks taken" when running
some batch inserts... Any parameter can be ajusted ?


Thanks!

Best regards,
Alex Vinogradovs

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] psql: FATAL: role "xyz" is not permitted to log in

[Tom Lane]

Ralph Smith <[EMAIL PROTECTED]> writes:
> However I'm now trying to access it using all lower case.
> Are you saying that IT'S confused and I should go all lower or use  
> quotes (") not (')?

Well, you can do it either way.

If you want the database to be really truly named "LabNews_dev"
(mixed case) then you need to create it using the quotes, and then
you'll have to use quotes *every* time you name it in SQL commands,
and you'll have to be careful to use the mixed-case spelling in
other contexts such as pg_hba.conf and psql command arguments.

If you are okay with it really being named "labnews_dev" then you
won't need quotes in your SQL commands, but you'll have to spell
it in all lower case in pg_hba.conf and psql command arguments.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Direct access to GIST structure

[Teodor Sigaev]

encode spatial proximity. Is there an API (backend C-level is fine) to
access a GIST index?


The best way is to extend existing interface to GiST to support KNN-search. But 
you can see how to get access to index structure from module in gevel module 
(http://www.sigaev.ru/cvsweb/cvsweb.cgi/gevel/). GiST-related functions in this 
module is invented to help to developers, not for production use, so they 
acquire exclusive lock on index.


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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Exception messages -> application?

[Karsten Hilbert]

On Fri, Apr 04, 2008 at 12:02:20PM -0700, Frank Miles wrote:

> I have a moderately DB-ignorant question: is there a "built-in" way for an
> application to receive the message emitted by a RAISE  in a PgSQL 
> function?
>
> Context: I have a moderately complex application (in python, using psycopg2)

psycopg2 does a pretty good job of converting any PostgreSQL
side exceptions into Python exceptions which are then
getting raised in your Python code. Unless you explicitely
silence/catch PG exceptions inside your plpgsql code you
should be seeing them turn up in Python.

There's a bunch of arguments on the Python exception raised.

try:
...
except TheExceptionType, e:
print dir(e)

might help with that.

Also, you may need to fiddle with the PG client logging
settings (in postgresql.conf) which control what level of
detail of error information is getting sent to the client in
the first place.

BTW, are you talking about RAISE EXCEPTION or RAISE NOTICE ?

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] psql: FATAL: role "xyz" is not permitted to log in

[Ralph Smith]

Egg in the face time.  I knew it was going to be one of those...


CREATE ROLE  --DOES NOT EQUAL--  CREATE USER

Whereas the latter implies LOGIN priv, the former does not.
I'd read about LOGIN auto-implied some time ago, but I'd not noticed  
that is not so w/ a created ROLE.



Maybe you can smell the sulfur from where you are?


Ralph Smith

=



On Apr 4, 2008, at 2:57 PM, Tom Lane wrote:

Ralph Smith <[EMAIL PROTECTED]> writes:

However I'm now trying to access it using all lower case.
Are you saying that IT'S confused and I should go all lower or use
quotes (") not (')?


Well, you can do it either way.

If you want the database to be really truly named "LabNews_dev"
(mixed case) then you need to create it using the quotes, and then
you'll have to use quotes *every* time you name it in SQL commands,
and you'll have to be careful to use the mixed-case spelling in
other contexts such as pg_hba.conf and psql command arguments.

If you are okay with it really being named "labnews_dev" then you
won't need quotes in your SQL commands, but you'll have to spell
it in all lower case in pg_hba.conf and psql command arguments.

regards, tom lane



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Duplicate Key violation on dump&reload using pg_restore

[Markus Wollny]

Hello!
 
I'm currently trying to migrate one of our databases from PostgreSQL 8.2.4 to 
PostgreSQL 8.3.1. I have worked around the Tsearch2 migration (we used the 
contrib module) without too much hassle, but find myself stuck at an unexpected 
point - I get a duplicate key violation for the primary key on one of my tables:
 
pg_restore -U postgres -d community -a --disable-triggers -t ct_com_user -v 
ct_com_user.backup
pg_restore: connecting to database for restore
pg_restore: disabling triggers for ct_com_user
pg_restore: restoring data for table "ct_com_user"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4256; 0 106035908 TABLE DATA 
ct_com_user postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  duplicate key value violates 
unique constraint "pk_ct_com_user"
CONTEXT:  COPY ct_com_user, line 357214: "2463013   X   
5   \N  \N  0   \N  0   \N  0   \N  0   \N  
 1   \N  1   \N  1   \N  0   \N  0   \N 
 0   \N  0   [EMAIL PROTECTED]    0   
..."
pg_restore: *** aborted because of error
 
This is the table definition (I left out the non-relevant bits):
CREATE TABLE ct_com_user
(
  user_id integer NOT NULL,
  "login" text,
  "password" text,
  [...]
  CONSTRAINT pk_ct_com_user PRIMARY KEY (user_id)
)
WITH (OIDS=TRUE);

I did not change the table definition after the dump. I used pgdump of 8.3.1 to 
create a dump of schema and data separately like this:
 
/opt/pgsql/bin/pg_dump -h-U postgres -N tsearch2 -s community > 
community.schema.sql
/opt/pgsql/bin/pg_dump -h -U postgres -N tsearch2 -a community -Fc 
> community.data.pg
 
Then I created a new database (same encoding UTF-8, no issues there) on my 
8.3.1 machine and installed the 8.3.1-contrib-tsearch2-module for backwards 
compatibility. After that I fed the schema.sql into that new DB - no errors so 
far. Then I tried to restore the data using 
 
/opt/pgsql/bin/pg_restore --disable-triggers -v -U postgres -v -Fc -d community 
community.data.pg
 
During restore of that complete data dump, I get a warning like the one above:
 
pg_restore: restoring data for table "ct_com_user"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 9116; 0 106035908 TABLE DATA 
ct_com_user postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  duplicate key value violates 
unique constraint "pk_ct_com_user"
CONTEXT:  COPY ct_com_user, line 356811: "2463013   X   
5   \N  \N  0   \N  0   \N  0   \N  0   \N  
 1   \N  1   \N  1   \N  0   \N  0   \N 
 0   \N  0   [EMAIL PROTECTED]    0   
..."
pg_restore: enabling triggers for ct_com_user
[...]
WARNING: errors ignored on restore: 1
 
Checking the restored database, everything is where it should be (i.e. even the 
TSearch2-enabled tabled), with the exception of that ct_com_user-table, which 
remains empty. I therefore tried and dumped that table alone again and tried to 
restore - with the exact same result (see above). Before restoring again, I 
made sure that the target table doesn't contain any entries (count(*) still is 
0).
 
I'll try and delete that single line in the 8.2.1 production system now (this 
user has not logged in for nearly three months now, so not much loss there - 
but even if that happens to work out (not so sure if it will), I'd still like 
to know what's going on here. Any ideas?
 
Kind regards
 
   Markus
 
 
 
 
 


Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276

Re: [GENERAL] too many LWLocks taken

[Tom Lane]

Alex Vinogradovs <[EMAIL PROTECTED]> writes:
> I've got a pretty large database, and since certain time
> it started giving me "too many LWLocks taken" when running
> some batch inserts... Any parameter can be ajusted ?

Oh really?  That's a bug, not something you need to adjust a parameter
for.  Can you put together a self-contained test case?  What PG
version is this?

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Duplicate Key violation on dump&reload using pg_restore

[Tom Lane]

"Markus Wollny" <[EMAIL PROTECTED]> writes:
> I'm currently trying to migrate one of our databases from PostgreSQL 8.2.4 to 
> PostgreSQL 8.3.1. I have worked around the Tsearch2 migration (we used the 
> contrib module) without too much hassle, but find myself stuck at an 
> unexpected point - I get a duplicate key violation for the primary key on one 
> of my tables:

Maybe there actually is a duplicate key in the source DB --- have you
checked?  There were some bugs in early 8.2.x releases that could
possibly allow that to happen.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Duplicate Key violation on dump&reload using pg_restore

[Markus Wollny]

Quick update: Seems like removing that tuple has solved the issue, dump and 
import of that table went fine, everything is where is should be - but there 
shouldn't have been an issue there in the first place however, with the primary 
key constraint present in the source database. I'm still curious, even though 
I've now got less to worry about the upcoming migration :)


Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276

Re: [GENERAL] Duplicate Key violation on dump&reload using pg_restore

[Markus Wollny]

Tom Lane wrote:
> Maybe there actually is a duplicate key in the source DB --- have you
> checked?  There were some bugs in early 8.2.x releases that could
> possibly allow that to happen.

Thanks, I was hoping there would be an easy explanation like that. I guess I'll 
have to do a little reading up on the change logs of post-8.2.4-releases :)



Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276

Re: [GENERAL] too many LWLocks taken

[Alex Vinogradovs]

Version is 8.2.5 running on FreeBSD 6.2. I can try upgrading
to 8.2.7, if you think that would help.

I don't think I can make a test case. Database had been
up for a year now, with about 2 thousand inherited tables
(partitioning) and about 2B rows in all tables...


On Fri, 2008-04-04 at 18:58 -0400, Tom Lane wrote:
> Alex Vinogradovs <[EMAIL PROTECTED]> writes:
> > I've got a pretty large database, and since certain time
> > it started giving me "too many LWLocks taken" when running
> > some batch inserts... Any parameter can be ajusted ?
> 
> Oh really?  That's a bug, not something you need to adjust a parameter
> for.  Can you put together a self-contained test case?  What PG
> version is this?
> 
>   regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] too many LWLocks taken

[Tom Lane]

Alex Vinogradovs <[EMAIL PROTECTED]> writes:
> Version is 8.2.5 running on FreeBSD 6.2. I can try upgrading
> to 8.2.7, if you think that would help.

Well, an upgrade would be a good idea on general principles, but
I doubt it will fix a previously unknown bug.

Does the postmaster log show any other odd behavior around these
errors?  Which process is throwing the error anyway?  If it's
a regular backend, what query is it executing?

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Conversion to 8.3

[Terry Lee Tucker]

On Friday 04 April 2008 15:01, Craig Ringer wrote:
> Terry Lee Tucker wrote:
> > Greetings:
> >
> > I am converting our application from 7.4.19 to 8.3.1. In the old scheme
> > of things, I was generating an interval between two timestamps and
> > evaluating the interval string in another set of trigger code. I was
> > doing the following:
> > IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN
>
> If I understand your question correctly, you're seeking to determine if
> new.ontime is "in the past". If so, compare with current_timestamp /
> current_date as appropriate, eg:
>
> IF new.ontime IS NOT NULL AND new.ontime < current_timestamp THEN
>
> (Note that current_timestamp and current_date are constant within a
> transaction, so they might not be suitable if you have really long
> running transactions).
>
> --
> Craig Ringer

Craig,

Thanks for the response. I have failed to communicate the problem. The ontime 
value is calculated based on the arrival time versus the appointment time; 
therefore the current timestamp is not helpful. I don't have the appointment 
data in this trigger and thus I can't do a comparison there unless I go find 
the appointment data, which I could do, but was trying to prevent the 
overhead. My question is: Is there a way to look directly at the timestamp 
value and determine if it was "ago", that is, negative?

Thanks...
-- 
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
[EMAIL PROTECTED]
www.turbocorp.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Conversion to 8.3

[Terry Lee Tucker]

On Friday 04 April 2008 16:36, Tom Lane wrote:
> Terry Lee Tucker <[EMAIL PROTECTED]> writes:
> > I am converting our application from 7.4.19 to 8.3.1. In the old scheme
> > of things, I was generating an interval between two timestamps and
> > evaluating the interval string in another set of trigger code. I was
> > doing the following:
> > IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN
> > With the new casting rules, this doesn't work.
>
> Well, you could force it to work by casting new.ontime to text
> explicitly, but this is a pretty horrid way of testing for a negative
> interval anyhow.  I'd be inclined to do something like
>  new.ontime < '0 seconds'
>
> BTW, the IS NOT NULL test is redundant too, since the comparison
> can't succeed for a null.
>
>regards, tom lane

Thanks Tom. This is what I needed.
-- 
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
[EMAIL PROTECTED]
www.turbocorp.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] dump data into complex type table

[windwxc]

hi,everyone,first thank you help me relove the problem about complex type.
now i have other two question about complex type field.
firstly, i want to know if the complex type data can be dump into the database 
by command 'copy'?
secondly, i try it but failure. The following is detail about my operation. my 
data which need be dump into data is text file and the table accepting the data 
is include a complex type field.
format of text file is :
"dd",20,"1,"010301040213","石红","红河州弥勒县虹溪镇虹溪村委会东门村小组","K236","(2005-1-1,2005-12-31)""
"ww",2,"2,"010301010975","陈永华","红河州弥勒县虹溪镇虹溪村委会西门村小组","K236","(2005-1-1,2005-12-31)""
my table is:
CREATE TABLE test2
(
  address character varying(30),
  area1 numeric,
  att attritest
)
WITH (OIDS=FALSE);
ALTER TABLE test2 OWNER TO stuser;

attritest is complex type and define is:
CREATE TYPE attritest AS(ID integer, planter_no character(20).planter_name 
character(20),planter_address character varying(50),planter_type character(10), 
validtime lifetime);
lifetime also complex type and includes (starttime date,endtime date)
my COPY commamn is:
copy test2 from 'e:\\test.txt' with DELIMITE ',';
the error is:
ERROR: extra data afterlase expected column.
thank you!
 xiaochun wu

---
外企高薪职位急聘( 
http://d1.sina.com.cn/sina/limeng3/mail_zhuiyu/2008/mail_zhuiyu_20080331.html )

---
注册新浪2G免费邮箱（http://mail.sina.com.cn/）

Re: [GENERAL] Direct access to GIST structure

[Tim Keitt]

On Fri, Apr 4, 2008 at 4:58 PM, Teodor Sigaev <[EMAIL PROTECTED]> wrote:
>
>
> > encode spatial proximity. Is there an API (backend C-level is fine) to
> > access a GIST index?
> >
>
>  The best way is to extend existing interface to GiST to support KNN-search.
> But you can see how to get access to index structure from module in gevel
> module (http://www.sigaev.ru/cvsweb/cvsweb.cgi/gevel/). GiST-related
> functions in this module is invented to help to developers, not for
> production use, so they acquire exclusive lock on index.

Thanks that helps.

I just stumbled on http://www.cs.purdue.edu/spgist/ which seems like
exactly what I need.

THK

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



-- 
Timothy H. Keitt, Assistant Professor
University of Texas at Austin http://www.keittlab.org/
Contact info and schedule at http://www.keittlab.org/~tkeitt/
Please support open document formats http://opendocument.xml.org/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Conversion to 8.3

[Scott Marlowe]

On Fri, Apr 4, 2008 at 2:39 PM, Terry Lee Tucker <[EMAIL PROTECTED]> wrote:
> On Friday 04 April 2008 15:01, Craig Ringer wrote:
>
> > Terry Lee Tucker wrote:
>  > > Greetings:
>  > >
>  > > I am converting our application from 7.4.19 to 8.3.1. In the old scheme
>  > > of things, I was generating an interval between two timestamps and
>  > > evaluating the interval string in another set of trigger code. I was
>  > > doing the following:
>  > > IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN
>  >
>  > If I understand your question correctly, you're seeking to determine if
>  > new.ontime is "in the past". If so, compare with current_timestamp /
>  > current_date as appropriate, eg:
>  >
>  > IF new.ontime IS NOT NULL AND new.ontime < current_timestamp THEN
>  >
>  > (Note that current_timestamp and current_date are constant within a
>  > transaction, so they might not be suitable if you have really long
>  > running transactions).
>  >
>  > --
>  > Craig Ringer
>
>  Craig,
>
>  Thanks for the response. I have failed to communicate the problem. The ontime
>  value is calculated based on the arrival time versus the appointment time;
>  therefore the current timestamp is not helpful. I don't have the appointment
>  data in this trigger and thus I can't do a comparison there unless I go find
>  the appointment data, which I could do, but was trying to prevent the
>  overhead. My question is: Is there a way to look directly at the timestamp
>  value and determine if it was "ago", that is, negative?

what type is new.ontime ??   timestamp or interval.  I would expect it
to be an interval.  But intervals are either negative or positive, not
"ago" unless that's something peculiar to 7.4 that I've long since
forgotten.  So, if you generate this interval use a raise to show it
during the trigger, what does it look like in 8.3.1?  I'm guessing
something like this:

 -10 days -03:00:00

so then you can just compare it to something like:

if new.ontime < '0'::interval then it's negative, i.e in the past.

Maybe if you post a bit more of your data / schema etc... so we can
see what you were doing and what you're trying to do now would help,
but I'm guessing that you made the classic mistake of using string
comparison to do date math, when it is usually much better to let the
db do that kind of thing for you with proper interval / timestamp
stuff.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dump data into complex type table

[Craig Ringer]

[EMAIL PROTECTED] wrote:

hi,everyone,first thank you help me relove the problem about complex type.
now i have other two question about complex type field.
firstly, i want to know if the complex type data can be dump into the database 
by command 'copy'?
secondly, i try it but failure. The following is detail about my operation. my 
data which need be dump into data is text file and the table accepting the data 
is include a complex type field.
format of text file is :
"dd",20,"1,"010301040213","","??","K236","(2005-1-1,2005-12-31)""
"ww",2,"2,"010301010975","??","??","K236","(2005-1-1,2005-12-31)""
  


Try using tabs and omitting the double quotes.

If you still have problems, use pg_dump to dump a table containing your 
complex values and examine the dump file. That'll show you how it needs 
to be done.


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Direct access to GIST structure

[Teodor Sigaev]

I just stumbled on http://www.cs.purdue.edu/spgist/ which seems like
exactly what I need.


It doesn't work with 8.2 and up, because since 8.2 index should take care about 
concurrent access itself and that implementation doesn't do it.


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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Direct access to GIST structure

[Oleg Bartunov]

Tim,

we have this in our TODO
http://www.sai.msu.su/~megera/wiki/TODO
Current interface doesn't allow tree navigation.
We're looking for sponsorships of this project.

Oleg
On Fri, 4 Apr 2008, Tim Keitt wrote:


I was wondering if there is a way to get access to the tree structure
underlying at GIST index? What I would like to do is perform a
breadth-first search on the tree in order to find nearest neighbors
(using PostGIS data types). Nearest neighbor searchers are not very
efficient using only range queries as it is not known a priori how
large a range to sample. Searching the tree is very fast as the tree
encode spatial proximity. Is there an API (backend C-level is fine) to
access a GIST index?

THK




Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Word boundaries in regular expressions

[Troy Rasiah]

Apologies if this has been posted somewhere else but what is the 
equivalent of perl's \b in postgres regular expressions ?



--
Troy Rasiah




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump and schemas

[Brent Wood]

Hi Rusty,

Try passing the output through a utility like sed, already there under Linux , 
but versions that work under Windows are available (eg, cygwin)

eg, using a pipe:   pg_dump -d | sed 's/public/test1/g' > dump.sql

or converting a pg_dump output file:

pg_dump
cat dump.sql | sed 's/public/test1/g' > dump2.sql

With tools like these freely available, you don't really need to spend time 
reinventing them in your database applications. Of course. if you have the 
"public" schema name used elsewhere in your database, you may need to get a bit 
creative in your use of sed, but it can pretty well always be made to do this 
sort of operation. 

Cheers,

   Brent Wood



>>> Rusty Conover <[EMAIL PROTECTED]> 05/04/08 8:42 AM >>>
Hi All,

Is there a way to pass a parameter to pg_dump that would make the  
produced dump be loaded into a different schema rather then the one it  
is being dumped from?  Basically be able to say dump out of public,  
but write the dump so its restored to say "test1".

Thanks,

Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com







-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general