Re: [GENERAL] Database Comparison tool?

[Devrim GUNDUZ]

Hi,

On Wed, 2006-02-08 at 18:22 +, Nicholas Walker wrote:
> Are there any tools that can compare a database schema, and produce sql 
> of the changes from one version to the next.

http://www.sqlmanager.net/en/products/postgresql/dbcomparer
http://www.sqlmanager.net/en/products/postgresql/datacomparer

I did not try them (I don't have Windows) but ISTM these will help you.

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 6: explain analyze is your friend

Re: [GENERAL] Database Comparison tool?

[Roger Hand]

I have a script I've been using that does a db comparison, and it works very 
well.

In order to ensure things are in the right order, I have to ...

- query for table and view names (FROM pg_tables WHERE schemaname = 'public' 
...), with an ORDER BY clause, natch.
- create a batch command file with one line for each table and view. This 
command is a pg_dump of the schema, which is appended to an output file

I also output function definitions, as well as the actual data (not just the 
schema) of some tables that basically have static or lookup data.

There's a little more to it to suit my particular needs, but the general 
approach works well. Oh, one other thing I sometimes need to do is to delete 
lines with db owner if the two db's have different owners.

-Roger

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Rick Gigger
Sent: Thursday, February 09, 2006 11:09 PM
To: Philippe Ferreira
Cc: Nicholas Walker; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Database Comparison tool?


Is the ordering guaranteed to be the same on both boxes if you do this?

Rick

On Feb 9, 2006, at 1:03 PM, Philippe Ferreira wrote:

>
>> Are there any tools that can compare a database schema, and  
>> produce sql of the changes from one version to the next.
>>
>> We have a development server, and it would be great to be able to  
>> just run a tool, where we could produce the changes, review it,  
>> and then commit to production.
>
> Hi,
>
> Do a "pgdump" of both databases, and use the "diff" tool to compare  
> the two generated files !
>
> (But I hope your databases are not too big...)
>
> Philippe Ferreira.
>
> ---(end of  
> broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>   choose an index scan if your joining column's datatypes do not
>   match
>


---(end of broadcast)---
TIP 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

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

Re: [GENERAL] Database Comparison tool?

[Rick Gigger]

Is the ordering guaranteed to be the same on both boxes if you do this?

Rick

On Feb 9, 2006, at 1:03 PM, Philippe Ferreira wrote:



Are there any tools that can compare a database schema, and  
produce sql of the changes from one version to the next.


We have a development server, and it would be great to be able to  
just run a tool, where we could produce the changes, review it,  
and then commit to production.


Hi,

Do a "pgdump" of both databases, and use the "diff" tool to compare  
the two generated files !


(But I hope your databases are not too big...)

Philippe Ferreira.

---(end of  
broadcast)---

TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match




---(end of broadcast)---
TIP 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] Database Comparison tool?

[Philippe Ferreira]

Are there any tools that can compare a database schema, and produce 
sql of the changes from one version to the next.


We have a development server, and it would be great to be able to just 
run a tool, where we could produce the changes, review it, and then 
commit to production.


Hi,

Do a "pgdump" of both databases, and use the "diff" tool to compare the 
two generated files !


(But I hope your databases are not too big...)

Philippe Ferreira.

---(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] Sequences/defaults and pg_dump

[Bruno Wolff III]

On Tue, Feb 07, 2006 at 15:28:31 +0300,
  Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote:
> The real situation would be as the following.
> I want to use some algorithm to hide real number of registered users
> in my table user. So, I don't want to use simple sequence, when every
> new registered user in my system can guess what is the number of
> registered users simply observing his ID. So, I use following
> algorithm:
> (nextval('...name of the sequnence...') * N) mod % M,
> where N and M are quite big numbers that have no common multiples
> besides 1 (sorry, do not remember the English term for those numbers 
> ;-) ).

(N and M are said to be "relatively prime".)

The above method isn't very secure. You might be better off using a block
cipher in counter mode, depending on how badly you want to keep the number
of users secret. Even that won't be foolproof as the users might cooperate
with each other to estimate how many of them there are.

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

Re: [GENERAL] Is there a way to limit CPU usage per user

[Michael Fuhr]

On Fri, Feb 10, 2006 at 11:30:04AM +0700, Luki Rustianto wrote:
> So how can we terminate such a long running query ?
> 
> The idea is to make a crontab to periodicaly do a job to search a
> typical "SELECT * FROM bigtable" query who has run for some hours then
> to terminate them...

Are you familiar with the statement_timeout setting?

test=> SET statement_timeout TO 1000; -- milliseconds
SET
test=> SELECT ;
ERROR:  canceling statement due to statement timeout

If that won't work then please explain in general terms what problem
you're trying to solve, not how you're trying to solve it.

-- 
Michael Fuhr

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

Re: [GENERAL] Return more then one value using PL

[Michael Fuhr]

On Thu, Feb 09, 2006 at 11:23:55AM -0800, Benjamin Arai wrote:
> What languages allow you to return more than one value using PL?  For
> example, I read that PL/Python only supports returning a single value.

What do you mean by "more than one value"?  Multiple columns
(composite type), multiple rows (set), or both?  In any case, the
documentation for each language describes its capabilities.

http://www.postgresql.org/docs/8.1/interactive/server-programming.html

Of the standard languages in PostgreSQL 8.1 you can return sets,
composite types, and sets of composite types with C, SQL, PL/pgSQL,
and PL/Perl but not with PL/Tcl or PL/Python.  Third-party languages
with support for sets and composite types include PL/Ruby, PL/php,
PL/R, PL/Java, and possibly others.

-- 
Michael Fuhr

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

   http://archives.postgresql.org

Re: [GENERAL] Is there a way to limit CPU usage per user

[Luki Rustianto]

So how can we terminate such a long running query ?

The idea is to make a crontab to periodicaly do a job to search a
typical "SELECT * FROM bigtable" query who has run for some hours then
to terminate them...



On 2/9/06, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> > Is there a way to limit user's CPU resource specially on "SELECT" query ?
> >
> > I hava a table with a lot of rows inside, if one sloopy DB users do a
> > "SELECT * FROM bigtable"
> > then CPU resource will go near 99% and this action will surely affect
> > the other database performance ...
> >
> Yes, the answer is not to write the query in the first place :-).  you
> can implement cursors, do client side browsing, or other techiniques
> to handle this problem more elegantly.
>
> try to follow rule of thumb to return minimal amount of data necessary
> to the client.
>
> Merlin
>

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

Re: [GENERAL] r trim of characters other than space

[surabhi.ahuja]

Title: Re: [GENERAL] r trim of characters other than space 






but how should i do it within 
a stored procedure
something like:
 
CREATE OR REPLACE FUNCTION 
insert(varchar(65),varchar(65),date,varchar(256)) RETURNS retval 
AS'DECLAREpatName 
text;BEGIN    
patName := trim($1);    select 
trim(trailing `^` from patName) INTO 
patName;    
trim(patName);
 
this seems to be giving syntax error
 
thanks,
regards
Surabhi


From: Tom Lane 
[mailto:[EMAIL PROTECTED]Sent: Thu 2/9/2006 12:49 PMTo: 
surabhi.ahujaCc: pgsql-general@postgresql.orgSubject: Re: 
[GENERAL] r trim of characters other than space 

***Your mail has been scanned by iiitb 
VirusWall.***-***"surabhi.ahuja" 
<[EMAIL PROTECTED]> writes:> i want to make the following 
check,> if it is having carets in the end, then those carets be 
removed.> so if i get a string like abc def> i should be able 
to get abc defPer SQL spec:regression=# select trim(trailing '^' 
from 'abc def');  rtrim - abc def(1 
row)    
    
    regards, tom 
lane

Re: [GENERAL] distinct not working in a multiple join

[Stephen Frost]

* David Rio Deiros ([EMAIL PROTECTED]) wrote:
> Now I have to redefine my query because I want to get the second 
> output but keeping the group_id. Ideas and suggestions are welcome.

You might want to look at 'distinct on'.

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Request to have VACUUM ignore cost based limits

[Karl O. Pinc]

On 02/09/2006 12:19:39 AM, Tom Lane wrote:

"Karl O. Pinc" <[EMAIL PROTECTED]> writes:
> But isn't SET server wide?

No.  Perhaps you need to read
http://www.postgresql.org/docs/8.1/static/runtime-config.html#CONFIG-SETTING


Yes, I do.  Thanks.  (Probably a sign it's time to read
the whole manual again.)

Karl <[EMAIL PROTECTED]>
Free Software:  "You don't pay back, you pay forward."
 -- Robert A. Heinlein


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

Re: [GENERAL] distinct not working in a multiple join

[David Rio Deiros]

On Thu, Feb 09, 2006 at 07:20:19PM -0500, Tom Lane wrote:
> David Rio Deiros <[EMAIL PROTECTED]> writes:
> > I have some issues with the query attached at the end of this email.
> > If I run that query I got this output ( I have removed some of the 
> > fields) despite the distinct clause:
> 
> > QC Q&A  | www.xxx.com | 44281
> > QC Q&A  | www.xxx.com | 44281
> > WhyMAX? | | 44285
> 
> Since you removed some fields, no one can tell if this output
> is wrong or not.

Tom, 

Thanks for the answer and apologizes for the lack of information. 
I think you found the problem already (see bellow). In anycase, just 
to clarify:

This is the information about the machine/OS/Psql version:

1. Psql 8.0.4 - Linux 2.6.13

Now, This is the first query I tried:

SELECT
distinct ar.title,
ar.raw_data,
ar.upload_dt,
ar.artifact_id,
g.group_id,
acl.read
FROM
artifact_acl acl,
artifacts a,
artifact_revisions ar,
revisions_to_types rt,
artifact_types at,
groups g
WHERE
a.expire_dt > NOW() and
acl.artifact_id = a.artifact_id and
a.published_revision = ar.revision_id and
ar.revision_id = rt.revision_id and
rt.type_id = at.type_id and
acl.group_id = g.group_id and
a.suppress = false and
at.is_resource = true and
(
acl.group_id = (
select
group_id
from
groups
where
group_nm = 'Marketing'
   )
   OR
acl.group_id = (
select
group_id
from
groups
where
group_nm = 'Communicators'
   )
) and
acl.group_id IN (4,17,54,2,1,123) and
acl.read = true and
((g.back_or_front = 'front') or
(g.group_nm = 'PR Admin'))
ORDER BY
ar.upload_dt DESC
LIMIT 3;

and here you have the output:

-[ RECORD 1 ]---
title   | QC Q&A
raw_data| www.com
upload_dt   | 2006-02-09 11:15:04.724525
artifact_id | 44281
group_id| 2
read| t
-[ RECORD 2 ]---
title   | QC Q&A
raw_data| www.com
upload_dt   | 2006-02-09 11:15:04.724525
artifact_id | 44281
group_id| 54
read| t
-[ RECORD 3 ]---
title   | WhyMAX?
raw_data| 
upload_dt   | 2006-02-09 09:25:27.717663
artifact_id | 44061
group_id| 2
read| t


Now, I tried this query, which is the first one but removing the g.group_id
from the select clause. This is the output:

-[ RECORD 1 ]-
title   | QC Q&A
raw_data| www.com
upload_dt   | 2006-02-09 11:15:04.724525
artifact_id | 44281
read| t
-[ RECORD 2 ]
title   | WhyMAX?
raw_data| 
upload_dt   | 2006-02-09 09:25:27.717663
artifact_id | 44061
read| t
-[ RECORD 3 ]---
title   | Business Assets-test
raw_data| corpcomm.com/
upload_dt   | 2006-02-08 15:58:06.81578
artifact_id | 44280
read| t

Which is the desired output.

> > SELECT  
> > distinct ar.title,
> > ar.raw_data,
> > ar.upload_dt,
> > ar.artifact_id,
> > g.group_id,
> > acl.read
> > FROM
> 
> The way you formatted that makes me wonder if you think that the
> DISTINCT applies only to the first column.  It does not, it applies
> to all the columns together --- that is, it only removes rows that
> are identical in all columns to some other row.  So if there were
> some rows that were identical except for group_id, you'd get the
> behavior you described.

Yes, that was the reason, I thought that distinct was only applying
to the first column.

> BTW, I'm not sure I believe this is actually the same query you
> ran.  The presence of the "GROUP BY ar.title" clause should have
> provoked errors about ungrouped columns.  If this is an exact
> copy of what you did, what Postgres version is this?

It wasn't the same query. Forget about the first email, in this email
you have the queries I launched (sorry again about that).

Now I have to redefine my query because I want to get the second 
output but keeping the group_id. Ideas and su

Re: [GENERAL] Insert more than one t-uple in a single sql

[David Fetter]

On Thu, Feb 09, 2006 at 07:12:45PM -0500, Tom Lane wrote:
> >> You can't do that in postgres, sorry. That's a mysql-ism.
> >> 
> >> Gonzalo Villegas wrote:
> >> 
> >>> It must be something like
> >>> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
> >>> (c1,c2,...)
> 
> Actually, that's not a mysql-ism, it's SQL-spec syntax.  We haven't
> got round to implementing it, partly because the SELECT ... UNION
> ALL ...  syntax provides a perfectly good substitute.  It is on the
> TODO list though.

Another way it's different from COPY is that the VALUES can take
expressions.

What all would need to change in order to implement this?  There
appear to be things in src/backend/parser and src/bin/psql that bear
on this.  Would libpq and ecpg need to change?

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778

Remember to vote!

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

Re: [GENERAL] distinct not working in a multiple join

[Tom Lane]

David Rio Deiros <[EMAIL PROTECTED]> writes:
> I have some issues with the query attached at the end of this email.
> If I run that query I got this output ( I have removed some of the 
> fields) despite the distinct clause:

> QC Q&A  | www.xxx.com | 44281
> QC Q&A  | www.xxx.com | 44281
> WhyMAX? | | 44285

Since you removed some fields, no one can tell if this output
is wrong or not.

> SELECT  
> distinct ar.title,
> ar.raw_data,
> ar.upload_dt,
> ar.artifact_id,
> g.group_id,
> acl.read
> FROM

The way you formatted that makes me wonder if you think that the
DISTINCT applies only to the first column.  It does not, it applies
to all the columns together --- that is, it only removes rows that
are identical in all columns to some other row.  So if there were
some rows that were identical except for group_id, you'd get the
behavior you described.

BTW, I'm not sure I believe this is actually the same query you
ran.  The presence of the "GROUP BY ar.title" clause should have
provoked errors about ungrouped columns.  If this is an exact
copy of what you did, what Postgres version is this?

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] Insert more than one t-uple in a single sql

[Tom Lane]

>> You can't do that in postgres, sorry. That's a mysql-ism.
>> 
>> Gonzalo Villegas wrote:
>> 
>>> It must be something like
>>> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
>>> (c1,c2,...)

Actually, that's not a mysql-ism, it's SQL-spec syntax.  We haven't got
round to implementing it, partly because the SELECT ... UNION ALL ...
syntax provides a perfectly good substitute.  It is on the TODO list
though.

I wouldn't recommend trying to insert more than a few dozen rows with
the UNION ALL approach, else the planner overhead might swamp any
savings.  If you want to insert thousands of rows at once, you almost
certainly want to find a way to use COPY.

regards, tom lane

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

[GENERAL] distinct not working in a multiple join

[David Rio Deiros]

Hi there,

I have some issues with the query attached at the end of this email.
If I run that query I got this output ( I have removed some of the 
fields) despite the distinct clause:

QC Q&A  | www.xxx.com | 44281
QC Q&A  | www.xxx.com | 44281
WhyMAX? | | 44285

But, and here is the weird thing, if I remove g.group_id from the
selection list then I got what I expected:

QC Q&A  | www.xxx.com | 44281
WhyMAX? | | 44285
toto| rufus   | 44286

Can someone explain me what is going on here? What am I missing?

Thanks for you help in advance,

David


SELECT  
distinct ar.title,
ar.raw_data,
ar.upload_dt,
ar.artifact_id,
g.group_id,
acl.read
FROM
artifact_acl acl,
artifacts a,
artifact_revisions ar,
revisions_to_types rt,
artifact_types at,
groups g
WHERE
a.expire_dt > NOW() and
acl.artifact_id = a.artifact_id and
a.published_revision = ar.revision_id and
ar.revision_id = rt.revision_id and
rt.type_id = at.type_id and
acl.group_id = g.group_id and
a.suppress = false and
at.is_resource = true and
(
acl.group_id = (
select
group_id
from
groups
where
group_nm = 'ting'
   )
   OR
acl.group_id = (
select
group_id
from
groups
where
group_nm = 'tors'
   )
) and
acl.group_id IN (4,17,54,2,1,123) and
acl.read = true and
((g.back_or_front = 'front') or
 (g.group_nm = 'PR Admin'))
GROUP BY
   ar.title
ORDER BY
   ar.upload_dt DESC
LIMIT 3;

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

Re: [GENERAL] Insert more than one t-uple in a single sql

[Rick Gigger]

There is a little trick you can do though, it goes something like this:

insert into table (field1, field2, field3) select v1, v2, v3 union  
b1, b2, b3 union select c1, c2, c3


I originally did this because it was significantly faster on SQL  
Server 2000 than doing the inserts individually.  Usually I did it  
with up to maybe 20 rows at a time that were all grouped to some kind  
of common parent.


Some version of postgres a long time ago broke my code because it did  
some stricter type checking and so I had to make sure that I was  
never putting single quotes around int and that date fields were  
strictly typecasted so that it wouldn't think they were strings.  It  
does work now though as long as I do that and I use it all the time.   
I don't know if it gets the same sort of speed boost in postgres as  
it did in sql server.  As long as they are all done within a single  
transaction in postgres it may not matter whether you do them  
individually or batched like that.


If you really have a lot of data you want to insert at once why not  
just use COPY?


Rick


On Feb 9, 2006, at 4:13 PM, Chris wrote:


Hi,

You can't do that in postgres, sorry. That's a mysql-ism.

Gonzalo Villegas wrote:


It must be something like
insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
(c1,c2,...)


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




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

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

Re: [GENERAL] Insert more than one t-uple in a single sql

[Klint Gore]

On Thu, 9 Feb 2006 17:57:03 -0500, "Gonzalo Villegas" <[EMAIL PROTECTED]> wrote:
>  
> 
> Hi all,
> 
> I'm trying to insert more than one t-uple in a single sql query. Just like
> 
> copy table (field1,field2,...) from 
> 
> It must be something like
> 
> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
> (c1,c2,...)

insert into table (field1,field2)
select v1,v2
union all
select b1,b2
union all
select c1,c2

klint.

+---+-+
: Klint Gore: "Non rhyming:
: EMail   : [EMAIL PROTECTED]   :  slang - the:
: Snail   : A.B.R.I.:  possibilities  :
: Mail  University of New England   :  are useless"   :
:   Armidale NSW 2351 Australia : L.J.J.  :
: Fax : +61 2 6772 5376 : :
+---+-+

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

Re: [GENERAL] Insert more than one t-uple in a single sql

[Chris]

Hi,

You can't do that in postgres, sorry. That's a mysql-ism.

Gonzalo Villegas wrote:


It must be something like

insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
(c1,c2,...)


---(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] Insert more than one t-uple in a single sql

[Gonzalo Villegas]

 

Hi all,

I'm trying to insert more than one t-uple in a single sql query. Just like

copy table (field1,field2,...) from 

It must be something like

insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
(c1,c2,...)



Thanks in advance!

Gonzalo A. Villegas

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

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

Re: [GENERAL] referential integrity without trigger

[Tom Lane]

Alexander Presber <[EMAIL PROTECTED]> writes:
> Is there a clever, general scheme to "recheck"  and enforce foreign  
> key contraints, after the responsible triggers have been disabled and  
> reenabled?

Drop the constraint (keep your fingers off the trigger, thank you ;-)).
Modify the master table.  Re-create the constraint before committing.
ALTER TABLE ADD FOREIGN KEY does this about as fast as any ad-hoc idea
you might come up with.

regards, tom lane

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

Re: [GENERAL] Update table with data from another table

[Chandra Sekhar Surapaneni]

UPDATE schema1.A
SET col = z.col
FROM schema2.A z
WHERE z.match = schema1.A.match 

This should always work. Observe schema1.A.match in the last line of the
query. If this does not work please post the error message you are
getting when you tried this. 

-Chandra Sekhar Surapaneni

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mike G.
Sent: Thursday, February 09, 2006 2:21 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Update table with data from another table

Hi,

I was hoping to update the results of one table with data from another
table.  I have done this many times before using UPDATE X SET Z FROM Y.

The catch this time is the tables involved both have the same column
names, same table names but reside in different schemas.

UPDATE schema1.A
SET col = z.col
FROM schema2.A z
WHERE z.match = match;

I can't get postgres to accept the above or any variation such as adding
the full schema and table name to each column.  I also can't seem to
declare an alias for the destination table (UPDATE schema1.A w SET w.col
= z.col).

As a workaround I will rename one of the tables temporarily.  If someone
has been able to get this to work I would appreciate any tips to
reference for next time.

Mike

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



---(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] PQputline error with pg_restore

[Tom Lane]

Jennifer I Drake/O/VCU <[EMAIL PROTECTED]> writes:
> ERROR: copy: line 178286, overflow on numeric ABS(value) >= 10^3 for field 
> with precision 5 scale 3

It would seem that you've got an incorrect (too large) value in a
numeric field in the dumped data.

It's not clear how you got into this state.  If you haven't mucked
with the dump then the value was presumably too large in the source
database, which would imply some bug in Postgres that had let it
escape range checking when it was stored into the table originally.
7.3.2 is so far back that this wouldn't surprise me a whole lot.
If you can reproduce such a problem in a more current version, we'd
like to see the details.

The easiest way to fix things is probably to make pg_restore generate
a SQL script file, edit the script, then load it.  You can either change
the data value if you think it's wrong, or widen the field precision if
you want to keep the data as-is.

regards, tom lane

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

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

Re: [GENERAL] PQputline error with pg_restore

[Jennifer I Drake/O/VCU]

The postmaster stderr was being sent to /dev/null, so I changed
that and was able to generate a log file.  Unfortunately, I'm not
sure what the output in the log file means - I don't have a lot of
experience with this sort of thing and would greatly appreciate any
help.  Here's the info from the log:

postmaster successfully started
LOG:  database system was shut down at 2006-02-09 15:37:30 EST
LOG:  checkpoint record is at 1/2C118B80
LOG:  redo record is at 1/2C118B80; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 6659; next oid: 19331162
LOG:  database system is ready
WARNING:  Attribute "piece" has an unknown type
    Proceeding with relation creation anyway
WARNING:  Attribute "piece" has an unknown type
    Proceeding with relation creation anyway
LOG:  recycled transaction log file 0001002B
LOG:  recycled transaction log file 0001002C
LOG:  recycled transaction log file 0001002D
LOG:  recycled transaction log file 0001002E
LOG:  recycled transaction log file 0001002F
LOG:  recycled transaction log file 00010030
LOG:  recycled transaction log file 00010031
LOG:  recycled transaction log file 00010032
LOG:  recycled transaction log file 00010033
LOG:  recycled transaction log file 00010034
ERROR:  copy: line 178286, overflow on numeric ABS(value) >= 10^3 for field with precision 5 scale 3
FATAL:  Socket command type 8 unknown
LOG:  recycled transaction log file 00010036
LOG:  recycled transaction log file 00010037
LOG:  recycled transaction log file 00010035

Thanks,
Jennifer
[EMAIL PROTECTED] wrote: -
You should really update to something more current than 7.3.2 :-(The first thing to do is get more info about the error, butunfortunately that release of pg_restore isn't going to tell you whatthe error message from PQputline is.  So you'll have to try to findout from the postmaster's log.You'll need to look at the startup script Mandrake uses for postgresto see where it sends the postmaster's stderr, but I wouldn't besurprised to find that it sends to /dev/null :-(.  You can change thescript to redirect to some real file and then restart the postmaster andthen try the restore again.  Or reconfigure things so that thepostmaster sends its log messages to syslog --- though this may takesome fooling with syslog's configuration as well as with postgresql.conf.(Syslog is probably a better choice for production purposes --- if youredirect to a file, that file will continue to grow as long as thepostmaster runs.)Once you've managed to see the underlying error message, if it doesn'tmake things clear then pass the info along and we'll try to help.BTW, another thing you could try is having pg_restore just generatea SQL script, and then feed the SQL script to psql.  psql will probablybe more cooperative about showing the underlying message.	regards, tom lane

Re: [GENERAL] PgAdmin3 for Suse AMD64

[Chandra Sekhar Surapaneni]

select groname, 
grosysid, usename from pg_user right outer join pg_group on usesysid = 
any(grolist);
 
This should do the job for 1).
 
- Chandra Sekhar Surapaneni


From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED] On Behalf Of Hrishikesh 
DeshmukhSent: Thursday, February 09, 2006 12:52 PMTo: 
pgsql-general@postgresql.orgSubject: [GENERAL] PgAdmin3 for Suse 
AMD64
Dear All,I have two questions:1) One can used select 
* from pg_group to get list of groups BUT how does one get group membership say 
for this example find users who are under the group sales?!booktown=# SELECT * FROM pg_group;  groname   | grosysid |   grolist+--+- sales  |1 | {7017,7016} accounting |2 | marketing  |3 |
(3 rows)2) Where can i find Pgadmin3 rpms for Suse 9.3 for AMD64 
Opterons (dual core)?Thank youHrishi

[GENERAL] Update table with data from another table

[Mike G.]

Hi,

I was hoping to update the results of one table with data from another table.  
I have done this many times before using UPDATE X SET Z FROM Y.

The catch this time is the tables involved both have the same column names, 
same table names but reside in different schemas.

UPDATE schema1.A
SET col = z.col
FROM schema2.A z
WHERE z.match = match;

I can't get postgres to accept the above or any variation such as adding the 
full schema and table name to each column.  I also can't seem to declare an 
alias for the destination table (UPDATE schema1.A w SET w.col = z.col).

As a workaround I will rename one of the tables temporarily.  If someone has 
been able to get this to work I would appreciate any tips to reference for next 
time.

Mike

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

Re: [GENERAL] What's faster?

[Martijn van Oosterhout]

On Thu, Feb 09, 2006 at 10:52:03AM -0800, Uwe C. Schroeder wrote:
> Depending on your keys neither.
> Rather let the DB handle the resultset. count(*) is quite slow.
> 
> How about something like
> 
> select blablabla from _complex_query order by _key_ (optional DESC or ASC) 
> OFFSET xxx LIMIT 15
> 
> where your offset would be a parameter from the php side and is basically the 
> page number of the number of pages you want to display.
> The only drawback of that is that you will never see the total number of hits.
> So maybe you do a count(*) ONCE and then use the above query to loop over the 
> resultset - or you don't show the number of pages and just have a "next 
> results" and "previous results" button that adjusts the offset parameter.

Another possibility is to put a limit of 151. If you get 151 rows you
print 1 2 3 .. 8 9 10 More. If you get less you know how many pages. As
you get to page 5 you can limit to 225+1.

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

[GENERAL] Return more then one value using PL

[Benjamin Arai]

smime.p7m
Description: S/MIME encrypted message

Re: [GENERAL] Debian Packages For PostgreSQL

[Stephen Frost]

* Tyler MacDonald ([EMAIL PROTECTED]) wrote:
> Stephen Frost <[EMAIL PROTECTED]> wrote:
> > > Speaking of Debian, is there some list to discuss Debian-specific
> > > packaging issues, e.g. how to create a Debian package which installs
> > > some stored procedures written in C?
> > 
> > Sure:
> > http://lists.alioth.debian.org/mailman/listinfo/pkg-postgresql-public
> 
>   Hmm, looks like a large noise:signal ratio there:
> 
> http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/2005-November/thread.html
> 
>   In fact, I'd go as far as to say that list would make an excellent
> spam honeypot. ;-)

eh, you're taking much too short a look at the list.  Consider a bit
further back.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Debian Packages For PostgreSQL

[Tyler MacDonald]

Stephen Frost <[EMAIL PROTECTED]> wrote:
> > Speaking of Debian, is there some list to discuss Debian-specific
> > packaging issues, e.g. how to create a Debian package which installs
> > some stored procedures written in C?
> 
> Sure:
> http://lists.alioth.debian.org/mailman/listinfo/pkg-postgresql-public

Hmm, looks like a large noise:signal ratio there:

http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/2005-November/thread.html

In fact, I'd go as far as to say that list would make an excellent
spam honeypot. ;-)

- Tyler


---(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] Debian Packages For PostgreSQL

[Stephen Frost]

* Florian Weimer ([EMAIL PROTECTED]) wrote:
> * Redefined Horizons:
> 
> > It looks like the packages.debian.org site is down. Is there another
> > place where I can download a .deb for the latest stable version of
> > PostgreSQL. (I don't have a direct link to the internet on my Linux
> > box, so I can't use APT.)
> 
> ; the packages
> have "sarge" in their names.
> 
> Speaking of Debian, is there some list to discuss Debian-specific
> packaging issues, e.g. how to create a Debian package which installs
> some stored procedures written in C?

Sure:
http://lists.alioth.debian.org/mailman/listinfo/pkg-postgresql-public

Thanks,

Stephen


signature.asc
Description: Digital signature

[GENERAL] PgAdmin3 for Suse AMD64

[Hrishikesh Deshmukh]

Dear All,I have two questions:1) One can used select * from pg_group to get list of groups BUT how does one get group membership say for this example find users who are under the group sales?!
booktown=# SELECT * FROM pg_group;  groname   | grosysid |   grolist+--+- sales  |1 | {7017,7016} accounting |2 | marketing  |3 |
(3 rows)2) Where can i find Pgadmin3 rpms for Suse 9.3 for AMD64 Opterons (dual core)?Thank youHrishi

Re: [GENERAL] What's faster?

[Uwe C. Schroeder]

Depending on your keys neither.
Rather let the DB handle the resultset. count(*) is quite slow.

How about something like

select blablabla from _complex_query order by _key_ (optional DESC or ASC) 
OFFSET xxx LIMIT 15

where your offset would be a parameter from the php side and is basically the 
page number of the number of pages you want to display.
The only drawback of that is that you will never see the total number of hits.
So maybe you do a count(*) ONCE and then use the above query to loop over the 
resultset - or you don't show the number of pages and just have a "next 
results" and "previous results" button that adjusts the offset parameter.



On Wednesday 08 February 2006 19:45, Silas Justiniano wrote:
> Hello all!
>
> I'm performing a query that returns me hundreds of records... but I
> need cut them in pages that have 15 items! (using PHP)
>
> So, is it faster:
>
>   select blablabal from _complex_query
>   if (count($result) > 15) show_pages;
>   show_only_15_rows($result);
>
> or:
>
>   select count(*) from _complex_query
>   if ($result1 > 15) show_pages;
>   select blablabal from _complex_query LIMIT ... (see the LIMIT!)
>   show $result
>
> On the first, I can use pg_num_rows instead of count(), too.
>
> what do you think?
>
> Thank you!
>
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq

-- 
UC

--
Open Source Solutions 4U, LLC   1618 Kelly St
Phone:  +1 707 568 3056 Santa Rosa, CA 95401
Cell:   +1 650 302 2405 United States
Fax:+1 707 568 6416

---(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] Debian Packages For PostgreSQL

[Florian Weimer]

* Redefined Horizons:

> It looks like the packages.debian.org site is down. Is there another
> place where I can download a .deb for the latest stable version of
> PostgreSQL. (I don't have a direct link to the internet on my Linux
> box, so I can't use APT.)

; the packages
have "sarge" in their names.

Speaking of Debian, is there some list to discuss Debian-specific
packaging issues, e.g. how to create a Debian package which installs
some stored procedures written in C?

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

   http://archives.postgresql.org

Re: [GENERAL] referential integrity without trigger

[Stephan Szabo]

On Thu, 9 Feb 2006, Alexander Presber wrote:

> Hello everybody,
>
> Assuming I want to empty and refill table A (with roughly the same
> content, preferrably in one transaction) and don't want to completely
> empty a dependent table B but still keep referential integrity after
> the commit.
>
> Without disabling A's on-delete-trigger B will be be emptied on
> commit, even when I inserted exactly the same data into A that I
> deleted an instant before. That is because the trigger gets called on
> commit, no matter if the deleted rows have "reappeared".
>
> If I disable the trigger, My referential integrity is most likely
> corrupted.
> Is there a clever, general scheme to "recheck"  and enforce foreign
> key contraints, after the responsible triggers have been disabled and
> reenabled?

Probably the easiest way to do these things is to drop the constraint
before, do stuff and re-add the constraint since that will check the
constraint at the add constraint time.

---(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] referential integrity without trigger

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Alexander Presber <[EMAIL PROTECTED]> writes:

> Hello everybody,
> Assuming I want to empty and refill table A (with roughly the same
> content, preferrably in one transaction) and don't want to completely
> empty a dependent table B but still keep referential integrity after
> the commit.

> Without disabling A's on-delete-trigger B will be be emptied on
> commit, even when I inserted exactly the same data into A that I
> deleted an instant before. That is because the trigger gets called on
> commit, no matter if the deleted rows have "reappeared".

> If I disable the trigger, My referential integrity is most likely
> corrupted.
> Is there a clever, general scheme to "recheck"  and enforce foreign
> key contraints, after the responsible triggers have been disabled and
> reenabled?

> I hope this makes sense to you.

Not quite?  Why do you use an explicit trigger for checking
referential integrity?  Can't you just use a foreign key with "ON
DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED"?


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

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

Re: [GENERAL] Debian Packages For PostgreSQL

[Shelby Cain]

--- Redefined Horizons <[EMAIL PROTECTED]> wrote:

> It looks like the packages.debian.org site is down. Is there another
> place where I can download a .deb for the latest stable version of
> PostgreSQL. (I don't have a direct link to the internet on my Linux
> box, so I can't use APT.)
> 

Sorry... http://pdo.debian.net/


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

Re: [GENERAL] Debian Packages For PostgreSQL

[Shelby Cain]

--- Redefined Horizons <[EMAIL PROTECTED]> wrote:

> It looks like the packages.debian.org site is down. Is there another
> place where I can download a .deb for the latest stable version of
> PostgreSQL. (I don't have a direct link to the internet on my Linux
> box, so I can't use APT.)
> 

Try http://pdo.debian.org/ instead for the time being.

Regards,

Shelby Cain



__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.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] Debian Packages For PostgreSQL

[Redefined Horizons]

It looks like the packages.debian.org site is down. Is there another
place where I can download a .deb for the latest stable version of
PostgreSQL. (I don't have a direct link to the internet on my Linux
box, so I can't use APT.)

Thanks,

Scott Huey

---(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] PQputline error with pg_restore

[Tom Lane]

Jennifer Drake <[EMAIL PROTECTED]> writes:
> I am running PostgreSQL 7.3.2 on Mandrake 9.1

You should really update to something more current than 7.3.2 :-(

> pg_restore: [archiver(db)] error returned by PQputline
> pg_restore: *** aborted because of error

The first thing to do is get more info about the error, but
unfortunately that release of pg_restore isn't going to tell you what
the error message from PQputline is.  So you'll have to try to find
out from the postmaster's log.

> I tried checking the log for the postmaster 
> (I assumed to be in /var/log), but no such file existed.

You'll need to look at the startup script Mandrake uses for postgres
to see where it sends the postmaster's stderr, but I wouldn't be
surprised to find that it sends to /dev/null :-(.  You can change the
script to redirect to some real file and then restart the postmaster and
then try the restore again.  Or reconfigure things so that the
postmaster sends its log messages to syslog --- though this may take
some fooling with syslog's configuration as well as with postgresql.conf.
(Syslog is probably a better choice for production purposes --- if you
redirect to a file, that file will continue to grow as long as the
postmaster runs.)

Once you've managed to see the underlying error message, if it doesn't
make things clear then pass the info along and we'll try to help.

BTW, another thing you could try is having pg_restore just generate
a SQL script, and then feed the SQL script to psql.  psql will probably
be more cooperative about showing the underlying message.

regards, tom lane

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

Re: [GENERAL] Is there a way to limit CPU usage per user

[Scott Marlowe]

On Wed, 2006-02-08 at 15:42, Merlin Moncure wrote:
> > Is there a way to limit user's CPU resource specially on "SELECT" query ?
> >
> > I hava a table with a lot of rows inside, if one sloopy DB users do a
> > "SELECT * FROM bigtable"
> > then CPU resource will go near 99% and this action will surely affect
> > the other database performance ...
> >
> Yes, the answer is not to write the query in the first place :-).  you
> can implement cursors, do client side browsing, or other techiniques
> to handle this problem more elegantly.
> 
> try to follow rule of thumb to return minimal amount of data necessary
> to the client.

Note that another useful tip here is to use slony to produce as many
replicants as needed to handle that kind of thing.

We have our production pgsql machines in a slony setup, with pg01 being
accessible by the application that inserts and updates the data, and all
reporting apps hit pg02 and up to do selects and such.

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

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

Re: [GENERAL] [ODBC] Problem using ODBC from .NET framework

[Shelby Cain]

--- Ludek Finstrle <[EMAIL PROTECTED]> wrote:

> Feel free to re-post the message if 08.01.0200 is still boken. I'll
> appreciate if you include mylog output (from 08.01.0200 driver).
> 

Using 08.01.0200, I'm still receiving the same error.  However, I've
managed to narrow the case when it happens down a bit.  I only receive
the "The connection is dead" error when an error occurs after I've
explicitly defined a transaction via ADO.Net's
OdbcConnection.BeginTransaction() function.

I'd be happy to send you a small C# test case in if you wish.

Regards,

Shelby Cain

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.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] PQputline error with pg_restore

[Jennifer Drake]

Hello,

I am running PostgreSQL 7.3.2 on Mandrake 9.1  I created a database 
archive using pg_dump (pg_dump -Ft -v -R oral > dbarchive200106.tar).  
When I try to restore this database with pg_restore on a new system 
(pg_restore -d oral -R -v dbarchive200106.tar), I get the following error:


pg_restore: [archiver(db)] error returned by PQputline
pg_restore: *** aborted because of error

The size of the uncompressed tar file is 1.34 GB.  Based on other 
informtion I've found here, I tried checking the log for the postmaster 
(I assumed to be in /var/log), but no such file existed.  The memory 
settings on the machine are:


sort_mem = 1
vacuum_mem = 1

Are these memory settings still too high?  Is there another 
configuration I should check?  I would appreciate any suggestions on how 
to fix this problem.


Thanks,
Jennifer

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

  http://archives.postgresql.org

Re: [GENERAL] What's faster?

[Alban Hertroys]

Silas Justiniano wrote:

Hello all!

I'm performing a query that returns me hundreds of records... but I
need cut them in pages that have 15 items! (using PHP)

So, is it faster:

  select blablabal from _complex_query
  if (count($result) > 15) show_pages;
  show_only_15_rows($result);

or:

  select count(*) from _complex_query
  if ($result1 > 15) show_pages;
  select blablabal from _complex_query LIMIT ... (see the LIMIT!)
  show $result


If you don't care about how many pages you'll get, try:

select blablabal from _complex_query LIMIT (15+1);
if (count($result) > 15) show_pages;
show_only_15_rows($result);

--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

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

//Showing your Vision to the World//

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

Re: [GENERAL] Create a new database from JDBC?

[Dan Armbrust]

Dave Page wrote:



On 8/2/06 17:26, "Joshua D. Drake" <[EMAIL PROTECTED]> wrote:


Certainly not what I want, since that database doesn't exist.  Is
there a system database I could always count on being available that I
could connect to?

template1 but only if you allow it from pg_hba.conf.


The postgres database would be more appropriate on 8.1+ (it is specifically
inteded for that kind of thing), however like template1, it can be dropped,
though that isn't advisable without good reason.

Regards, Dave.




So there is no guaranteed way to do this, unless there is a known, 
existing database?  Seems like a shortcoming, to me.  Would this be 
considered as a feature request, or does the current design make it to 
difficult to implement?


Dan

--

Daniel Armbrust
Biomedical Informatics
Mayo Clinic Rochester
daniel.armbrust(at)mayo.edu
http://informatics.mayo.edu/

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

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

Re: [GENERAL] [ODBC] Problem using ODBC from .NET framework

[Shelby Cain]

--- Ludek Finstrle <[EMAIL PROTECTED]> wrote:

> > Hi all.  I having an issue with the 8.01.01.02 ODBC driver that is
> > installed via the 8.1.x Windows installer.
> 
> ...
> 
> > Anyone have any ideas whats going on or how I can get back to the
> 8.0.x
> > behavior?
> 
> There is newer 08.01 psqlODBC version. Please try the 08.01.0200
> version.
> We provide a lot of changes (including error reporting).
> 
> Feel free to re-post the message if 08.01.0200 is still boken. I'll
> appreciate if you include mylog output (from 08.01.0200 driver).
> 
> Regards,
> 
> Luf
> 

Thanks.  I'll try the new version out and let you know.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.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] Database Comparison tool?

[Nicholas Walker]

Are there any tools that can compare a database schema, and produce sql 
of the changes from one version to the next.


We have a development server, and it would be great to be able to just 
run a tool, where we could produce the changes, review it, and then 
commit to production.


Or is there a system table that will give me the last modification date 
of a table/view/procedure, etc, etc...


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

  http://archives.postgresql.org

Re: [GENERAL] About CASE Studio - generate SQL output (how to link

[Emi Lu]

I saw the online doc mentioned that it supports tablespace for oracle 
and db2, but does it supports tablespace for postgresql 8.0 as well?


http://www.casestudio.com/enu/ver219.aspx

- Emi



Does anybody have the experiences of using "CASE Studio" ?

I used CASE Studio 2.2.1,and it helps me get a global picture of all 
objects we have. I tried to generate SQL outputs for tables' 
definitions. However, I cannot figure out how to specify tablespaces 
for indexes. For example, " ... primary key (***) using tablespace 
fis_index" ,  "unique constraint using tablespace fis_index", etc.


Also, please clue me in how feasible the SQL outputs generated by CASE 
Studio? I mean do you think it can generate what you expect from your 
experiences?


Thanks a lot,
Emi




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

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




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

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

[GENERAL] About CASE Studio - generate SQL output (how to link tablespace to table definition)

[Emi Lu]

Hello,

Does anybody have the experiences of using "CASE Studio" ?

I used CASE Studio 2.2.1,and it helps me get a global picture of all 
objects we have. I tried to generate SQL outputs for tables' 
definitions. However, I cannot figure out how to specify tablespaces for 
indexes. For example, " ... primary key (***) using tablespace 
fis_index" ,  "unique constraint using tablespace fis_index", etc.


Also, please clue me in how feasible the SQL outputs generated by CASE 
Studio? I mean do you think it can generate what you expect from your 
experiences?


Thanks a lot,
Emi




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

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

Re: [GENERAL] Confirming the autovacuum daemon is running

[Jim Buttafuoco]

I would be "nice" if vacuum/analyze recorded in a table pg_vacuum (for example) 
the last time it ran and some stats for
each table

Jim

-- Original Message ---
From: "Karl O. Pinc" <[EMAIL PROTECTED]>
To: Tom Lane <[EMAIL PROTECTED]>
Cc: pgsql-general@postgresql.org
Sent: Thu, 09 Feb 2006 00:31:57 +
Subject: Re: [GENERAL] Confirming the autovacuum daemon is running

> On 02/08/2006 06:06:25 PM, Tom Lane wrote:
> > "Karl O. Pinc" <[EMAIL PROTECTED]> writes:
> > > What is the proper way to confirm that the autovacuum daemon
> > > is running?
> > 
> > The autovac process doesn't run continuously, so you wouldn't
> > necessarily see it in ps.
> 
> Thanks.
> 
> I suppose then if I really wanted to I could look at frozen
> transaction ids or something.
> 
> Considering how important vacuuming is, it might be nice to
> have an entry in the postmaster startup log or something
> that confirms vacuuming is actually happening.   Better yet
> something that says that it is _not_ turned
> on, because if it is turned on and things go bad
> I presume that then they'll be an indication in the
> logs.  Done that way, when there are vacuum problems,
> regardless of why, there'll always be something in
> the log.
> 
> Karl <[EMAIL PROTECTED]>
> Free Software:  "You don't pay back, you pay forward."
>   -- Robert A. Heinlein
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
--- End of Original Message ---


---(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] Situation with delphi7 x postgresql 8.1.2

[Marcio]

 Hi, I develop under Delphi7(Build 8.1) pgexpress 4.01 and 
postgreSQL 8.1.2 OS is windows XP Professional or Windows 2003 
Server. In my project i use dbexpress components TSQLConnection, 
TSQLQuery -> TDataSetProvider -> TClientDataSet. In some 
situations when the TClientDataSet excute action "TClientDataSet.Open" 
this freeze the aplication and CPU Usage is over 95%. This situation 
happen only when the server is native windows. If necessary i have one 
project and database for example.
 
Thank´s
 
Márcio

 

Re: [GENERAL] loading pg_description ... FATAL: duplicate key violates unique constraint "pg_description_o_c_o_index"

[Martijn van Oosterhout]

On Thu, Feb 09, 2006 at 10:24:38AM +0530, surabhi.ahuja wrote:
> thank u so much for the help.
> ok i ll tell u how i arrived at this problem.
>  
> this i was using just for testing purpose on my local m/c
>  
> i had deleted rows in a table, and had searched  and found the following:



> and did gmake install
>  
> it got installed properly 
>  
> but when i did /usr/local/pgsql/bin/initdb -D /homes/surabi/data
>  
> it gave me this error:
> loading pg_description ... FATAL:  duplicate key violates unique constraint 
> "pg_description_o_c_o_index"
> child process exited with exit code 1
> initdb: removing contents of data directory "/homes/surabi/data"
> 
> please tell me how to proceed. I just want to try this experiment for 
> knowledge sake.

I think you're missing the problems with this patch. This can't be used
to do any real work because you can never delete rows and when updating
you'll get a copy of the old and the new. So all uniques indexes will
instantly break.

The purpose of this patch is not to install it and create a database
with it, but to temporarily use it to retreive your data. You can't
initdb with it. Even then if you have any deleted rows in the system
catalog, it won't work.

Basically, you run it over your existing installation, do the dump and
then go back to the normal installation. But you're playing with fire,
seriously!

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

[GENERAL] referential integrity without trigger

[Alexander Presber]

Hello everybody,

Assuming I want to empty and refill table A (with roughly the same  
content, preferrably in one transaction) and don't want to completely  
empty a dependent table B but still keep referential integrity after  
the commit.


Without disabling A's on-delete-trigger B will be be emptied on  
commit, even when I inserted exactly the same data into A that I  
deleted an instant before. That is because the trigger gets called on  
commit, no matter if the deleted rows have "reappeared".


If I disable the trigger, My referential integrity is most likely  
corrupted.
Is there a clever, general scheme to "recheck"  and enforce foreign  
key contraints, after the responsible triggers have been disabled and  
reenabled?


I hope this makes sense to you.

Alexander Presber

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