[GENERAL] problem to restore pg_dump --data-only

[simon litwan]

hello all

i've did pg_dump --data-only for quit a while and didn't have any
problem with restoring the resulting backup. 

but since the schema changed slightly the restore fails:
ERROR: insert or update in table "angebotsmuster" violates
foreignkey-constraints

it seems to me like the order of the inserts is not ok.

is there any solution for this problem?

any hint is appreciated.

simon



-- 
Simon Litwan   [EMAIL PROTECTED]
Wyona Inc.  -   Open Source Content Management   -   Apache Lenya
http://www.wyona.com  http://lenya.apache.org


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

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

Re: [GENERAL] Cutting the Gborg throat

[Jan Wieck]

On 8/28/2006 9:36 PM, Joshua D. Drake wrote:


Hello,

O.k. so how about a phased approach?

1. Contact maintainers to create their new projects on pgfoundry and 
begin moving tickets


2. Migrate CVS

3. Migrate mailing lists


Apparently something cut the throat first. GBorg is down since Sunday.


Jan

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

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

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

Re: [GENERAL] Cutting the Gborg throat

[Dave Page]

 

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Jan Wieck
> Sent: 31 August 2006 13:59
> To: Joshua D. Drake
> Cc: Dave Cramer; Greg Sabino Mullane; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Cutting the Gborg throat
> 
> On 8/28/2006 9:36 PM, Joshua D. Drake wrote:
> 
> > Hello,
> > 
> > O.k. so how about a phased approach?
> > 
> > 1. Contact maintainers to create their new projects on 
> pgfoundry and 
> > begin moving tickets
> > 
> > 2. Migrate CVS
> > 
> > 3. Migrate mailing lists
> 
> Apparently something cut the throat first. GBorg is down since Sunday.

Neptune lost 2 disks at once... Buts whats more interesting is that
yours is the first complaint I've seen.

Regards, Dave.

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

Re: [GENERAL] Dependency graph of all tuples relied upon in a query answer

[Merlin Moncure]

On 8/30/06, Randall Lucas <[EMAIL PROTECTED]> wrote:

I'm storing facts about an entity (e.g., "company") in a normalized
form with various repeating groups, link tables, etc.  My workflow
requires that after (or as part of) collecting these facts, I be able
to "sign off" as having verified all of the facts that pertain to a
given company.  I understand this as meaning I need to sign off on each
row that was used in answering the query "select * from company left
join ..."



An inverted way of thinking about the problem is the notion of getting a
source document (say, a "company registration form") and parsing and
storing it in a normalized format.  How do you "tag" the origin of each
and every row in every table that resulted from that source document?


your form should have a code, either entered by the customer or by the
preparer who enters it into the database, which becomes the key that
identifies the registration document.  Put that key into other tables.


It is possible to do so by associating an extra column with each
inserted or modified value (yuck).


be careful, you are flirting with EAV thinking.  I think EAV designs
are terrible.

> It seems to me that the elegant way to do this would be to get the

entire graph of dependencies for not only all tables that reference the
entity, but only those rows within those tables that refer specifically
to my entity.

The query engine should have a pretty good idea about which tables and
which rows would actually be used in forming the responses to a given
query.  How can I get this information?  Or am I off the deep end (-ency
graph) with this one?


I am not sure where you are going with this.  Maybe you should mock up
some simple tables and repost your question.

merlin

---(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] Cutting the Gborg throat

[Magnus Hagander]

> > > Hello,
> > >
> > > O.k. so how about a phased approach?
> > >
> > > 1. Contact maintainers to create their new projects on
> > pgfoundry and
> > > begin moving tickets
> > >
> > > 2. Migrate CVS
> > >
> > > 3. Migrate mailing lists
> >
> > Apparently something cut the throat first. GBorg is down since
> Sunday.
> 
> Neptune lost 2 disks at once... Buts whats more interesting is that
> yours is the first complaint I've seen.

You don't look carefully enough. There's been a couple of complaints
earlier. But no, not many.

//Magnus


---(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] Anonymous stored procedures

[Scott Marlowe]

On Tue, 2006-08-29 at 11:56, David Fetter wrote:
> On Tue, Aug 29, 2006 at 11:02:12AM -0500, Ron Johnson wrote:
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> > 
> > Hi,
> > 
> > Does PostgreSQL support them?
> 
> Not yet.

Note, however, that by using the dblink package you can get something
like them.  i.e. use dblink to connect back to the same (or another)
database and execute the user defined function there.  

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

Re: [GENERAL] Cutting the Gborg throat

[Stefan Kaltenbrunner]

Dave Page wrote:
 


-Original Message-
From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED] On Behalf Of Jan Wieck

Sent: 31 August 2006 13:59
To: Joshua D. Drake
Cc: Dave Cramer; Greg Sabino Mullane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Cutting the Gborg throat

On 8/28/2006 9:36 PM, Joshua D. Drake wrote:


Hello,

O.k. so how about a phased approach?

1. Contact maintainers to create their new projects on 
pgfoundry and 

begin moving tickets

2. Migrate CVS

3. Migrate mailing lists

Apparently something cut the throat first. GBorg is down since Sunday.


Neptune lost 2 disks at once... Buts whats more interesting is that
yours is the first complaint I've seen.


fwiw we had a number of requests on irc at least ...


Stefan

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

Re: [GENERAL] Cutting the Gborg throat

[Jan Wieck]

On 8/31/2006 9:10 AM, Dave Page wrote:
 


-Original Message-
From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED] On Behalf Of Jan Wieck

Sent: 31 August 2006 13:59
To: Joshua D. Drake
Cc: Dave Cramer; Greg Sabino Mullane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Cutting the Gborg throat

On 8/28/2006 9:36 PM, Joshua D. Drake wrote:

> Hello,
> 
> O.k. so how about a phased approach?
> 
> 1. Contact maintainers to create their new projects on 
pgfoundry and 
> begin moving tickets
> 
> 2. Migrate CVS
> 
> 3. Migrate mailing lists


Apparently something cut the throat first. GBorg is down since Sunday.


Neptune lost 2 disks at once... Buts whats more interesting is that
yours is the first complaint I've seen.


Meaning what?

A) Will be restored from backup
B) Data is lost finally and must be recovered from other sources


Jan

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

---(end of broadcast)---
TIP 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] Cutting the Gborg throat

[Dave Page]

 

> -Original Message-
> From: Jan Wieck [mailto:[EMAIL PROTECTED] 
> Sent: 31 August 2006 14:51
> To: Dave Page
> Cc: Joshua D. Drake; Dave Cramer; Greg Sabino Mullane; 
> pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Cutting the Gborg throat
> 
> > Neptune lost 2 disks at once... Buts whats more interesting is that
> > yours is the first complaint I've seen.
> 
> Meaning what?
> 
> A) Will be restored from backup
> B) Data is lost finally and must be recovered from other sources

Well A) afaik. I'm just telling you what happened though.

Regards, Dave.

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

   http://archives.postgresql.org

Re: [GENERAL] Cutting the Gborg throat

[Magnus Hagander]

> >> Apparently something cut the throat first. GBorg is down since
> Sunday.
> >
> > Neptune lost 2 disks at once... Buts whats more interesting is
> that
> > yours is the first complaint I've seen.
> 
> Meaning what?
> 
> A) Will be restored from backup
> B) Data is lost finally and must be recovered from other sources

The info I've seen from Marc says the data is available on backups and
will be restored, but the backup server is currently offline so it can't
be done right now. Don't know more details than that.

//Magnus

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

   http://archives.postgresql.org

[GENERAL] Fatal error while installing

[Gibson]

I seem to get this error "user postgres could not be created, user 
account already exists" when trying to install postgres. Anyone knows 
how to fix this?


Erik Jones wrote:

Erik Jones wrote:

Ok, consider the following table definition:

CREATE TABLE stats (
id SERIAL PRIMARY KEY,
hits bigint default 0,
clickthrus bigint default 0,
referrals bigint default 0);


Now, this table has a lot of rows that are constantly being updated 
by queries of the following form:


UPDATE stats
SET clickthrus = clickthrus + #
WHERE id = blah;  -- sub various values for # and blah

There can be, and often are,  multiple updates for the same row 
coming in at the same time,  but never for the same field.  My 
understanding of the locking involved is that updates take out 
row-exclusive locks to prevent other transactions from modifying the 
data and to serialize with other updates.  So, multiple update 
statements to the same row come in, the first to arrive is granted a 
row-exclusive lock and the other wait.  When the first is finished 
and commits, the second to have arrived get the lock, and so forth.  
Here is what I am seeing all through my logs:


2006-08-29 03:17:25 CDT 16074 192.168.1.173(35190):STATEMENT: ABORT
2006-08-29 03:17:25 CDT 8553 192.168.1.168(42707):ERROR: deadlock 
detected
2006-08-29 03:17:25 CDT 8553 192.168.1.168(42707):DETAIL: Process 
8553 waits for ShareLock on transaction 1548224183; blocked by 
process 5499.
Process 5499 waits for ShareLock on transaction 1548224182; blocked 
by process 8553.
2006-08-29 03:17:25 CDT 8553 192.168.1.168(42707):STATEMENT: UPDATE 
stats

SET hits = hits + 3
WHERE id = 271524;

or,

2006-08-29 08:47:31 CDT 12479 192.168.1.168(46125):ERROR: deadlock 
detected
2006-08-29 08:47:31 CDT 12479 192.168.1.168(46125):DETAIL: Process 
12479 waits for ExclusiveLock on tuple (3024,45) of relation 33942 of 
database 33325; blocked by process 12513

.
Process 12513 waits for ShareLock on transaction 1550567046; blocked 
by process 12495.
Process 12495 waits for ShareLock on transaction 1550569729; blocked 
by process 12479.
2006-08-29 08:47:31 CDT 12479 192.168.1.168(46125):STATEMENT: UPDATE 
stats

SET click_thrus = clickthrus + 1
WHERE id = 275359;

What's with ShareLock s on transactions?  Where do those come from?



I should also note that each of those updates occurs in it's own 
transactions, but that they do not attempt to modify any other rows in 
that table before commiting.  They do,  however, delete rows in 
another common table (where they pulled the stat counts from), but the 
rows they delete are disjunct.


The whole process/algorithm is such:

1.  Get rows matching X from temp table.
2.  Accumulate values from X and update row and field corresponding to 
X in stats table.

3.  Delete rows collected in step one.
4.  Commit.
5.  Repeat from step 1.

With multiple processes using the same algo and tables but for 
different values of X.





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

Re: [GENERAL] Listening on more than one port?

[Tom Lane]

"Michael Nolan" <[EMAIL PROTECTED]> writes:
> Situation in a nutshell:

> Production  and test databases are on two separate systems inside the
> firewall.  The web server is at an ISP, outside the firewall.

> The firewall sends all data coming from the ISP, port 5432 to a specific IP
> address inside the firewall: port 5432, which is the production serve.

> Test transactions need to go to the other server, which has to be the same
> IP address (the address of the firewall), so it needs to be a different
> port, but I'm not sure if the port translator in the firewall will do any
> better at getting a response  that than a port translator on the test server
> does.

What I'd do is configure the firewall to forward connections to 5432 to
the production machine (port 5432) and forward connections to 5431 to
5432 on the devel server.  There isn't any reason to have either
postmaster listening on multiple port numbers.

regards, tom lane

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

Re: [GENERAL] Cutting the Gborg throat

[David Fetter]

On Thu, Aug 31, 2006 at 03:27:13PM +0200, Magnus Hagander wrote:
> > > > Hello,
> > > >
> > > > O.k. so how about a phased approach?
> > > >
> > > > 1. Contact maintainers to create their new projects on
> > > pgfoundry and
> > > > begin moving tickets
> > > >
> > > > 2. Migrate CVS
> > > >
> > > > 3. Migrate mailing lists
> > >
> > > Apparently something cut the throat first. GBorg is down since
> > > Sunday.
> > 
> > Neptune lost 2 disks at once... Buts whats more interesting is
> > that yours is the first complaint I've seen.
> 
> You don't look carefully enough. There's been a couple of complaints
> earlier. But no, not many.

Perhaps it's because you responded with a message to the effect "we're
on it" when I asked, and people took that to mean that prompt action
was in the works :)

Cheers,
D
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(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] Cutting the Gborg throat

[Magnus Hagander]

> > You don't look carefully enough. There's been a couple of
> complaints
> > earlier. But no, not many.
> 
> Perhaps it's because you responded with a message to the effect
> "we're on it" when I asked, and people took that to mean that
> prompt action was in the works :)

Probably. Heck, that's what *I* thought at the time :)

//Magnus


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

Re: [GENERAL] UUID as primary key

[Vance Maverick]

Can you give a link to the PostgreSQL binding?  I haven't been able to
find it there.  All I see is C, Perl and PHP bindings for the generation
of UUIDs, and nothing about storage.

(For my application, as it happens, I don't need to generate UUIDs in
the database, but I recognize that would be useful.)

Vance 

> 
> Ralf Engelschall's OSSP uuid looks very good. Written in C with
> interfaces into PostgreSQL, PHP and C++ (classes wrapping the C
> structures and functions).
> 
> http://www.ossp.org/pkg/lib/uuid/
> 
> You should be able to e. g.
> 
> CREATE TABLE t (id UUID DEFAULT 'uuid(4)' PRIMARY KEY);
> 
> -- 
> How many Vietnam vets does it take to screw in a light bulb?
> You don't know, man.  You don't KNOW.
> Cause you weren't THERE. http://bash.org/?255991
> 

---(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] Cutting the Gborg throat

[Joshua D. Drake]

Jan Wieck wrote:

On 8/28/2006 9:36 PM, Joshua D. Drake wrote:


Hello,

O.k. so how about a phased approach?

1. Contact maintainers to create their new projects on pgfoundry and 
begin moving tickets


2. Migrate CVS

3. Migrate mailing lists


Apparently something cut the throat first. GBorg is down since Sunday.


It is a hardware failure apparently. My understanding is that Marc is 
working on it.


Joshua D. Drake




Jan




--

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



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

  http://archives.postgresql.org

Re: [GENERAL] Cutting the Gborg throat

[Joshua D. Drake]

grate mailing lists

Apparently something cut the throat first. GBorg is down since Sunday.


Neptune lost 2 disks at once... Buts whats more interesting is that
yours is the first complaint I've seen.


Fetter also brought it up, but it was on IRC :)



Regards, Dave.




--

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



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

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

Re: [GENERAL] Cutting the Gborg throat

[Dave Page]

 

> -Original Message-
> From: Joshua D. Drake [mailto:[EMAIL PROTECTED] 
> Sent: 31 August 2006 15:47
> To: Dave Page
> Cc: Jan Wieck; Dave Cramer; Greg Sabino Mullane; 
> pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Cutting the Gborg throat
> 
> grate mailing lists
> >> Apparently something cut the throat first. GBorg is down 
> since Sunday.
> > 
> > Neptune lost 2 disks at once... Buts whats more interesting is that
> > yours is the first complaint I've seen.
> 
> Fetter also brought it up, but it was on IRC :)

Perhaps we could get a new "rule" on IRC that when someone reports an
infrastructure issue, one of the regulars forwards it to -www ?

/D

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

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

[GENERAL] Strange error related to temporary tables

[Csaba Nagy]

Postgres version: 8.1.3

I have a code similar to this pseudo-code:

try {
  create temp table temp_report ...;
  ... do some processing using this table...
  drop table temp_report;
  commit;
} catch all errors {
  rollback;
}

This code occasionally triggered the following error (couldn't reliably
reproduce):

SQL state: 42710

ERROR: type "temp_report" already exists


The code is possible to be executed in parallel by multiple threads, on
different connections, or in sequence on the same connection. I would
expect it in both cases to work correctly... in manual tests I was able
to create in parallel temporary tables named identically in different
connections, and on the same connection after rolling back the
transaction which created the first table... so I don't know what to try
to trigger this.

Any ideas what's the problem ?

TIA,
Csaba.



---(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] Cutting the Gborg throat

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


>> Apparently something cut the throat first. GBorg is down since Sunday.
>
> Neptune lost 2 disks at once... Buts whats more interesting is that
> yours is the first complaint I've seen.

The squeaky wheel gets the grease? I'm certainly unhappy about the fact
that it is down, and I've griped on IRC about it, but I see little
point about complaining to the list when it is a known problem. I
assumed all along that it was being worked on, and further complaints
to the list would only annoy those who were trying their best to
resolve the problem.

All that aside, this seems to be a fairly long outage and an update
would be nice. Perhaps for something as important as this a daily
update could be a standard way of doing things moving forward?

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200608311059
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iD8DBQFE9vl8vJuQZxSWSsgRAkCeAKCs80RNZXOsDlsJg8vBcQGIDh0wdACdGELD
/VhJv99k06JSUK3/OD2imYA=
=aeuU
-END PGP SIGNATURE-



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

[GENERAL] Record creation date

[Rafael Barrera Oro]

Hello everyone! i am checking a database due to some inconsintencies and 
i was wandering if its possible to find out the creation date of a 
record (using the oid, whatever...). Maybe i am saying something stupid 
(since i am no DB expert) so if this is the case, please point it out 
gently.


Thanks a lot in advance!

Rafael

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

[GENERAL] number of elements in a multidimensional array

[SunWuKung]

>From this array how could I get back the fact that this array consists
of a two dimensional array with X elements?

Select array_dims('{{1,4,10,11},{1,5,4,5}}'::text [])

I would like to get back the number 4 here?

Thanks for the help.
Balázs


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

Re: [GENERAL] Fatal error while installing

[Joshua D. Drake]

Gibson wrote:
I seem to get this error "user postgres could not be created, user 
account already exists" when trying to install postgres. Anyone knows 
how to fix this?


Sounds to me like the user postgres on the OS already exists.

Joshua D. Drake




Erik Jones wrote:

Erik Jones wrote:

Ok, consider the following table definition:

CREATE TABLE stats (
id SERIAL PRIMARY KEY,
hits bigint default 0,
clickthrus bigint default 0,
referrals bigint default 0);


Now, this table has a lot of rows that are constantly being updated 
by queries of the following form:


UPDATE stats
SET clickthrus = clickthrus + #
WHERE id = blah;  -- sub various values for # and blah

There can be, and often are,  multiple updates for the same row 
coming in at the same time,  but never for the same field.  My 
understanding of the locking involved is that updates take out 
row-exclusive locks to prevent other transactions from modifying the 
data and to serialize with other updates.  So, multiple update 
statements to the same row come in, the first to arrive is granted a 
row-exclusive lock and the other wait.  When the first is finished 
and commits, the second to have arrived get the lock, and so forth.  
Here is what I am seeing all through my logs:


2006-08-29 03:17:25 CDT 16074 192.168.1.173(35190):STATEMENT: ABORT
2006-08-29 03:17:25 CDT 8553 192.168.1.168(42707):ERROR: deadlock 
detected
2006-08-29 03:17:25 CDT 8553 192.168.1.168(42707):DETAIL: Process 
8553 waits for ShareLock on transaction 1548224183; blocked by 
process 5499.
Process 5499 waits for ShareLock on transaction 1548224182; blocked 
by process 8553.
2006-08-29 03:17:25 CDT 8553 192.168.1.168(42707):STATEMENT: UPDATE 
stats

SET hits = hits + 3
WHERE id = 271524;

or,

2006-08-29 08:47:31 CDT 12479 192.168.1.168(46125):ERROR: deadlock 
detected
2006-08-29 08:47:31 CDT 12479 192.168.1.168(46125):DETAIL: Process 
12479 waits for ExclusiveLock on tuple (3024,45) of relation 33942 of 
database 33325; blocked by process 12513

.
Process 12513 waits for ShareLock on transaction 1550567046; blocked 
by process 12495.
Process 12495 waits for ShareLock on transaction 1550569729; blocked 
by process 12479.
2006-08-29 08:47:31 CDT 12479 192.168.1.168(46125):STATEMENT: UPDATE 
stats

SET click_thrus = clickthrus + 1
WHERE id = 275359;

What's with ShareLock s on transactions?  Where do those come from?



I should also note that each of those updates occurs in it's own 
transactions, but that they do not attempt to modify any other rows in 
that table before commiting.  They do,  however, delete rows in 
another common table (where they pulled the stat counts from), but the 
rows they delete are disjunct.


The whole process/algorithm is such:

1.  Get rows matching X from temp table.
2.  Accumulate values from X and update row and field corresponding to 
X in stats table.

3.  Delete rows collected in step one.
4.  Commit.
5.  Repeat from step 1.

With multiple processes using the same algo and tables but for 
different values of X.





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




--

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



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

Re: [GENERAL] Fatal error while installing

[Gibson]

How do i remove this user?I am running on Win XP here.

Joshua D. Drake wrote:

Gibson wrote:
I seem to get this error "user postgres could not be created, user 
account already exists" when trying to install postgres. Anyone knows 
how to fix this?


Sounds to me like the user postgres on the OS already exists.

Joshua D. Drake





---(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] Strange error related to temporary tables

[Alban Hertroys]

Csaba Nagy wrote:

The code is possible to be executed in parallel by multiple threads, on
different connections, or in sequence on the same connection. I would
expect it in both cases to work correctly... in manual tests I was able
to create in parallel temporary tables named identically in different
connections, and on the same connection after rolling back the
transaction which created the first table... so I don't know what to try
to trigger this.

Any ideas what's the problem ?


Are you sure that you're not re-using an existing connection from a pool?

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

// Integrate Your World //

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

Re: [GENERAL] Listening on more than one port?

[Shane Ambler]

Obviously the test section has separate configurations to the live section -
simply have 2 databases defined in the same server 'ourlivedatabase' and
'ourtestdatabase'. This still gives one small login/config  setting that
changes between live and dev.

On 31/8/2006 12:20, "Kelly Burkhart" <[EMAIL PROTECTED]> wrote:

> On 8/30/06, Michael Nolan <[EMAIL PROTECTED]> wrote:
>> Situation in a nutshell:
>> 
>> Production  and test databases are on two separate systems inside the
>> firewall.  The web server is at an ISP, outside the firewall.
>> 
>> The firewall sends all data coming from the ISP, port 5432 to a specific IP
>> address inside the firewall: port 5432, which is the production serve.
>> 
>> Test transactions need to go to the other server, which has to be the same
>> IP address (the address of the firewall), so it needs to be a different
>> port, but I'm not sure if the port translator in the firewall will do any
>> better at getting a response  that than a port translator on the test server
>> does.
>> 
>> However, I also need the test database responding to requests on port 5432.
> 
> Your firewall can probably redirect firewallhost:5431 to
> insidehost:5432.  If not, you could use something like simpleproxy on
> insidehost to do what you want.
> 
> 
> 
> -K
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 

-- 

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz



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

Re: [GENERAL] Strange error related to temporary tables

[Csaba Nagy]

On Thu, 2006-08-31 at 17:25, Alban Hertroys wrote:
> Csaba Nagy wrote:
> > The code is possible to be executed in parallel by multiple threads, on
> > different connections, or in sequence on the same connection. I would
> > expect it in both cases to work correctly... in manual tests I was able
> > to create in parallel temporary tables named identically in different
> > connections, and on the same connection after rolling back the
> > transaction which created the first table... so I don't know what to try
> > to trigger this.
> > 
> > Any ideas what's the problem ?
> 
> Are you sure that you're not re-using an existing connection from a pool?

Of course I'm re-using a connection from a pool... but if you look at
the pseudo-code from the OP, the temporary table is either dropped or
the transaction which creates it is rolled back, before the connection
is returned to the pool. When another thread is reusing the connection,
the table should be dropped either way, and manual tests show that it
works both ways, so I guess it is some race condition somewhere else.

In any case, the error message is strange in itself, as if I try to
create the temporary table when it exists, the error I get in manual
trial is:

ERROR:  relation "test_temp_table" already exists


compared to the error from the OP which I cite here for reference:

ERROR: type "temp_report" already exists
   

So it was complaining about an already existing type, not relation.

Any ideas ? I'm still clueless.

Cheers,
Csaba.



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

Re: [GENERAL] Strange error related to temporary tables

[Alban Hertroys]

Csaba Nagy wrote:

In any case, the error message is strange in itself, as if I try to
create the temporary table when it exists, the error I get in manual
trial is:

ERROR:  relation "test_temp_table" already exists


compared to the error from the OP which I cite here for reference:

ERROR: type "temp_report" already exists
   


That's strange indeed.

Some googling revealed this thread:
http://archives.postgresql.org/pgsql-general/2005-09/msg01100.php

Seems like around this time last year someone had the same problem. 
Unfortunately, the cause and it's possible solutions aren't mentioned 
(the OP found a workaround though). They may have never been found...


I suppose if you'd check pg_type there is a record containing 'temp_report'?

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

// Integrate Your World //

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

  http://archives.postgresql.org

Re: [GENERAL] Listening on more than one port?

[Michael Nolan]

I prefer to have my live and test databases on separate systems, if only because each one takes up well over 100 GB of disk space.  Our hardware firewall doesn't support port changes, but I was able to get the rinetd port redirector working to redirect 5431 to 5432 on the test system..  (And it works with psql now, too, I'm not sure why it didn't work before.)
So, I've got a solution that works for me.  Thanks, everyone.  That's '30' for this thread.  :-)--Mike NolanOn 8/31/06, Shane Ambler
 <[EMAIL PROTECTED]> wrote:
Obviously the test section has separate configurations to the live section -simply have 2 databases defined in the same server 'ourlivedatabase' and'ourtestdatabase'. This still gives one small login/config  setting that
changes between live and dev.On 31/8/2006 12:20, "Kelly Burkhart" <[EMAIL PROTECTED]> wrote:> On 8/30/06, Michael Nolan <
[EMAIL PROTECTED]> wrote:>> Situation in a nutshell: Production  and test databases are on two separate systems inside the>> firewall.  The web server is at an ISP, outside the firewall.
 The firewall sends all data coming from the ISP, port 5432 to a specific IP>> address inside the firewall: port 5432, which is the production serve. Test transactions need to go to the other server, which has to be the same
>> IP address (the address of the firewall), so it needs to be a different>> port, but I'm not sure if the port translator in the firewall will do any>> better at getting a response  that than a port translator on the test server
>> does. However, I also need the test database responding to requests on port 5432.>> Your firewall can probably redirect firewallhost:5431 to> insidehost:5432.  If not, you could use something like simpleproxy on
> insidehost to do what you want.>> >> -K>> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings>--Shane Ambler[EMAIL PROTECTED]Get Sheeky @ 
http://Sheeky.Biz---(end of broadcast)---TIP 6: explain analyze is your friend

Re: [GENERAL] Fatal error while installing

[Ardian Xharra]

Go to Start/ Control Panel / Administrative tools / Computer Management /
and you see Local Users and groups
Delete the user from there

- Original Message - 
From: "Gibson" <[EMAIL PROTECTED]>

Cc: "pgsql general" 
Sent: Friday, September 01, 2006 2:31 AM
Subject: Re: [GENERAL] Fatal error while installing



How do i remove this user?I am running on Win XP here.

Joshua D. Drake wrote:

Gibson wrote:
I seem to get this error "user postgres could not be created, user 
account already exists" when trying to install postgres. Anyone knows 
how to fix this?


Sounds to me like the user postgres on the OS already exists.

Joshua D. Drake





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



--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.7/434 - Release Date: 30/08/2006






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

[GENERAL] Thought provoking piece on NetBSD

[Joshua D. Drake]

I thought some people in this group may find this letter from one of
NetBSD's founders very interesting.

http://mail-index.netbsd.org/netbsd-users/2006/08/30/0016.html

It is current, to the point and has some direct correlations with our 
project that we may want to be aware of.


Sincerely,

Joshua D. Drake

--

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



---(end of broadcast)---
TIP 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] Strange error related to temporary tables

[Tom Lane]

Csaba Nagy <[EMAIL PROTECTED]> writes:
> In any case, the error message is strange in itself, as if I try to
> create the temporary table when it exists, the error I get in manual
> trial is:
> ERROR:  relation "test_temp_table" already exists
> 
> compared to the error from the OP which I cite here for reference:
> ERROR: type "temp_report" already exists
>

This is not too surprising given the way that heap_create_with_catalog
works --- it happens to be easier to insert the pg_type row before
the pg_class row, so if you have two sessions trying to create the same
table at about the same time, that's where the unique index constraint
will kick in.  The initial check for a duplicate pg_class row doesn't
catch the conflict because the guy who's just slightly ahead won't have
committed yet when the second guy looks.

The question is how is this scenario managing to occur, given that the
tables in question are temp tables?  It seems like this must indicate
two backends trying to use the same pg_temp_NNN schema; but that should
surely be impossible --- it's driven off MyBackendId, and if that's not
unique to a live session then we've got *major* problems.

IIRC we've seen prior reports of similar issues, so I believe there is
something there, but without a test case it's gonna be hard to track down.

regards, tom lane

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

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

[GENERAL] Query performance inconsistant.

[Matthew Schumacher]

I have been having performance problems with my DB so this morning I
added some config to log queries that take more than 250ms.  The result
is surprising because some queries will take as long as 10 seconds, but
then you do a explain analyze on them they show that indexes are being
used and they run very fast.  Here is an example:

2006-08-31 05:55:39.560 LOG:  duration: 3835.182 ms  statement: select
acctMessage(  )

But the same query returns this when I explain it:

> > explain analyze select acctMessage(  );
  QUERY PLAN
--
 Result  (cost=0.00..0.03 rows=1 width=0) (actual time=26.797..26.799
rows=1 loops=1)
 Total runtime: 36.838 ms

So the question is, why do some queries take a very long time?  Sure,
the obvious answer is the machine was busy doing something else, but I
can't find any evidence of that:

05:40:01 AM   CPU %user %nice   %system   %iowait%steal
%idle
05:54:01 AM   all  0.58  0.00  0.47  0.50  0.00
98.45
05:55:01 AM   all  0.46  0.00  0.31  3.45  0.00
95.79
05:56:01 AM   all  0.75  0.00  0.25  4.32  0.00
94.69

05:40:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached kbswpfree
kbswpused  %swpused  kbswpcad
05:54:01 AM 96376   3019292 96.91  6724   2789348   1004000
   20  0.0020
05:55:01 AM 92904   3022764 97.02  7844   2791424   1004000
   20  0.0020
05:56:01 AM 98840   3016828 96.83  9056   2784160   1004000
   20  0.0020

05:40:01 AM   tps  rtps  wtps   bread/s   bwrtn/s
05:54:01 AM 21.53  4.35 17.18 67.77344.84
05:55:01 AM 71.61 59.11 12.50   1202.79283.57
05:56:01 AM 29.22 13.94 15.29264.18316.59

Any thoughts on how to track this down?  I don't want to go buy a faster
server when I can't confirm that hardware performance is the problem.

Thanks,
schu




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

Re: [GENERAL] UUID as primary key

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2006-08-31 07:35:30 -0700:
> > Ralf Engelschall's OSSP uuid looks very good. Written in C with
> > interfaces into PostgreSQL, PHP and C++ (classes wrapping the C
> > structures and functions).
> > 
> > http://www.ossp.org/pkg/lib/uuid/
> 
> Can you give a link to the PostgreSQL binding?  I haven't been able to
> find it there.  All I see is C, Perl and PHP bindings for the generation
> of UUIDs, and nothing about storage.
 
The above page links to the source repository [1] which in turn
contains a "pgsql" directory[2].

[1] http://cvs.ossp.org/dir?d=ossp-pkg/uuid
[2] http://cvs.ossp.org/dir?d=ossp-pkg/uuid/pgsql

> > CREATE TABLE t (id UUID DEFAULT 'uuid(4)' PRIMARY KEY);

> (For my application, as it happens, I don't need to generate UUIDs in
> the database, but I recognize that would be useful.)

Actually, serverside generation of uuids is the least interesting
aspect of that snippet. Use of custom types in indexes requires
appropriate operator classes, and I was trying to give a hint that
the library's support for PostgreSQL is quite comprehensive.

Now, an operator class is no rocket science, but the ability to use
the type in any way without having to add missing features is nice
anyway.

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

---(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] Query performance inconsistant.

[Tom Lane]

Matthew Schumacher <[EMAIL PROTECTED]> writes:
> I have been having performance problems with my DB so this morning I
> added some config to log queries that take more than 250ms.  The result
> is surprising because some queries will take as long as 10 seconds, but
> then you do a explain analyze on them they show that indexes are being
> used and they run very fast.

Is it possible that it's not directly that query's fault?  For instance
it could be blocked by a lock held by some other transaction.  I can't
unfortunately think of any very nice way to deduce this from log entries
... you'd have to catch it in the act and look into pg_locks to find out
who's the perpetrator.

regards, tom lane

---(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] Thought provoking piece on NetBSD

[Martijn van Oosterhout]

On Thu, Aug 31, 2006 at 09:11:52AM -0700, Joshua D. Drake wrote:
> I thought some people in this group may find this letter from one of
> NetBSD's founders very interesting.
> 
> http://mail-index.netbsd.org/netbsd-users/2006/08/30/0016.html
> 
> It is current, to the point and has some direct correlations with our 
> project that we may want to be aware of.

Nice post, though I don't think PostgreSQL really has many of the
faults he lists. The only obvious one to me is the strong leadership
part, but that's not quite as necessary (I think) because the project
has a clear goal (to a certain extent): SQL compliance.

I think operating systems are a particularly hard area because of the
amount of evolution going on and the amount of work needed just to keep
working on newer machines. The field of databases and SQL is nowhere
near that difficult.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] Installation of Postgres 7.1

[Jonathan Chocolate]

Hi!I am unable to install postgres 7.1.2 on my Ubuntu Linux Box. I am installing postgres 7.1.2 from source. I stopped at the process of invoking the "make" command. I have a 7.1.2 database with me and plan to replicate it to another box. Pg_dump reports and error while I was dumping. 
My Linux Boxes are all Ubuntu.What can i do?

Re: [GENERAL] Installation of Postgres 7.1

[Jaime Casanova]

On 8/31/06, Jonathan Chocolate <[EMAIL PROTECTED]> wrote:

Hi!

I am unable to install postgres 7.1.2 on my Ubuntu Linux Box. I am
installing postgres 7.1.2 from source. I stopped at the process of invoking
the "make" command.

I have a 7.1.2 database with me and plan to replicate it to another box.
Pg_dump reports and error while I was dumping.

My Linux Boxes are all Ubuntu.

What can i do?



don't expect someone here help you to hang yourself, install a new
version. most recent one is 8.1.4

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
  Richard Cook

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

  http://archives.postgresql.org

Re: [GENERAL] Installation of Postgres 7.1

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jonathan Chocolate wrote:
> Hi!
> 
> I am unable to install postgres 7.1.2 on my Ubuntu Linux Box. I am
> installing postgres 7.1.2 from source. I stopped at the process of
> invoking the "make" command.

What error(s)?

> I have a 7.1.2 database with me and plan to replicate it to another box.
> Pg_dump reports and error while I was dumping.
> 
> My Linux Boxes are all Ubuntu.
> 
> What can i do?

Install v8.1?

Seriously, why install such an old version?  Some arcane data
requirement?

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE9yViS9HxQb37XmcRAgPcAKC63fHVt9BVwkV8Rhb2Ugh0qmt1YACfdGyR
kNnvE7Y8DI1wTg6teLP6ark=
=E9LX
-END PGP SIGNATURE-

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

Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on NetBSD

[Josh Berkus]

Josh,

> It is current, to the point and has some direct correlations with our
> project that we may want to be aware of.

Well, we're not in any danger of the board of a foundation taking over 
Postgres.  ;-)

The only part of this that I see as relevant to us is setting of 
development goals.  And we've already discussed this ad nauseum on the 
Hackers list and AFAIK have an initial plan (the enhanced TODO), lacking 
only the resources to implement it this month.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

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

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

Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on NetBSD

[Joshua D. Drake]

The only part of this that I see as relevant to us is setting of 
development goals.  And we've already discussed this ad nauseum on the 
Hackers list and AFAIK have an initial plan (the enhanced TODO), lacking 
only the resources to implement it this month.


Almost the whole thing is relevant :). Keep in mind that I am not saying 
that it is negative. For example the NetBSD core is obviously cranked, 
where our Core tends to stay out of the way. That is a positive.


On the other hand, we do suffer from the locked project problem (the 
recent recursive query debacle is a perfect example).


We do have portions of a meritocracy in place but we are by no means 
mature in that arena. Likely because of our lock problem ;)


We are also better at having cross over between sub projects so that 
many people who are the same people are part of many projects. This 
allows communication to flow between sub projects.


Not perfect of course :) but better then many I see.

Another odd issue, which may or may not be a positive is that we don't 
have a public leader. We have half a dozen people (less I think) that 
are very, very public (I am not talking mailing list public).


Anyway, the post as I said was for provoking thought, not for 
antagonistic measures. I saw good and bad and thought it would be good 
for everyone to review as we are as a project dealing with some of our 
own growth problems.


Sincerely,

Joshua D. Drake




--

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



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

Re: [GENERAL] Installation of Postgres 7.1

[Martijn van Oosterhout]

On Fri, Sep 01, 2006 at 01:57:03AM +0800, Jonathan Chocolate wrote:
> Hi!
> 
> I am unable to install postgres 7.1.2 on my Ubuntu Linux Box. I am
> installing postgres 7.1.2 from source. I stopped at the process of invoking
> the "make" command.

Dear gods, that's an ancient version. You're not going to get a lot of
help fixing something that old, nearly 6 major releases ago.

In any case, you're definitly not going to get any help if you don't
provide any actual error messages.

> I have a 7.1.2 database with me and plan to replicate it to another box.
> Pg_dump reports and error while I was dumping.

What kind of errors? That version had no protection against XID
wraparounnd... (which would be catastrophic).

> My Linux Boxes are all Ubuntu.
> 
> What can i do?

Install a newer version, say, 8.1.x?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Thought provoking piece on NetBSD

[Bruce Momjian]

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Thu, Aug 31, 2006 at 09:11:52AM -0700, Joshua D. Drake wrote:
> > I thought some people in this group may find this letter from one of
> > NetBSD's founders very interesting.
> > 
> > http://mail-index.netbsd.org/netbsd-users/2006/08/30/0016.html
> > 
> > It is current, to the point and has some direct correlations with our 
> > project that we may want to be aware of.
> 
> Nice post, though I don't think PostgreSQL really has many of the
> faults he lists. The only obvious one to me is the strong leadership
> part, but that's not quite as necessary (I think) because the project
> has a clear goal (to a certain extent): SQL compliance.

I think the issue is complacent leadership on the one hand, vs. a single
forceful leader on the other.  I think the best configuration somewhere
is in the middle, which is what we have.  I don't see how it is related
to the OS problem domain.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(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] postgres array quoting

[marc]

Hello -

I am attempting to find a way to make use of arrays of text, as 
demonstraited by the following:


CREATE TABLE messages (
  id SERIAL,
  format TEXT NOT NULL,
  arguments TEXT[]
);

Into that table will be values that you would associate with some form of 
printf. For example:


INSERT INTO messages (format,arguments)
  VALUES ('hello %s','{"world"}');
INSERT INTO messages (format,arguments)
  VALUES ('test 2 %s %s','{"one","two"}');
INSERT INTO messages (format,arguments)
  VALUES ('test 3 %s','{"abc","d,e,f","g,\\"h,i\'"}');

A select shows the following:

 id |format|arguments
+--+--
  1 | hello %s | {world}
  2 | test 2 %s %s | {one,two}
  3 | test 3 %s| {abc,"d,e,f","g,\"h,i'"}

The goal of this table is to extract each row and pass it to some 
variation of printf. My problem is that I can't figure out a good way to 
do this, and am hoping that someone has already found a way.


 * If I could figure a way to pass a variable number of arguments to a
   plperl function, or an array reference, I could use something like this
   function:

CREATE OR REPLACE FUNCTION audit_log_format(TEXT,...) RETURNS TEXT AS $$
  return sprintf shift,@_;
$$ LANGUAGE plperl;

 * If I could figure out a way to force select to always apply
   escaping/quoting logic to each of the elements in the arguments array,
   then I could probably find a way to do this. This split is non-trivial,
   but doable.

 * If I could perform a select within a plperl function and receive back
   a perl array for the arguments column, I could use a plperl function
   kinda like the one above, except taking an ID value as the argument.

Given the number of ways that things could be escaped in text stored in 
the arguments array, such as embedded quotes, commas, back slashes, etc, 
compounded by the lack of quotes in same cases but not others, parsing the 
output of a select is difficult at best. I suspect that it is sadly my 
only option.


Suggestions?

- Marc

---(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] [pgsql-advocacy] Thought provoking piece on NetBSD

[Josh Berkus]

Josh,

> On the other hand, we do suffer from the locked project problem (the
> recent recursive query debacle is a perfect example).

Yep, and that was immediately recognized as a problem in need of a 
solution.  In fact, some of the arguments againts the issue/feature 
tracker were that it would encourage the locked project issue.  So the 
NetBSD experience should inform our design of the future feature/bug 
tracker: it should be used to encourage new developers (by providing clear 
specs and status information) rather than locking in old ones.

> We do have portions of a meritocracy in place but we are by no means
> mature in that arena. Likely because of our lock problem ;)

What specific issues do you see?   We're pretty strongly merit-based -- the 
only reservation I see on that is a bias toward more eloquent writers 
having disproprotionate influence.  But I don't see any way to avoid that.

> Another odd issue, which may or may not be a positive is that we don't
> have a public leader. We have half a dozen people (less I think) that
> are very, very public (I am not talking mailing list public).

Actually, this issue is a complete red herring.   People like to point to 
Linux as successful because of Linus's benevolent dictatorship, but Linus 
is the exception rather than the rule.  Most of the very successful 
projects (Apache, Perl, MySQL, Debian, X.org, etc.) are led by councils or 
companies without a dictator.  I can name more than a few projects where 
the "charismatic leader" was the main thing preventing the project's 
success.

In general, I think that people who harp on PostgreSQL's lack of a 
benevolent dictator as an inhibitor to progress are people who are not 
comfortable with democracy and are looking for excuses why company X needs 
to "take over the project for its own good."

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---(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] [pgsql-advocacy] Thought provoking piece on NetBSD

[Joshua D. Drake]

In general, I think that people who harp on PostgreSQL's lack of a 
benevolent dictator as an inhibitor to progress are people who are not 
comfortable with democracy and are looking for excuses why company X needs 
to "take over the project for its own good."


Well I definitely don't think we need a benevolent dictator... however 
considering the relatively small number of people in the public eye, a 
definition of goals that we all speak too might be good :)


Joshua D. Drake




--

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



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

  http://archives.postgresql.org

Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on NetBSD

[Bruce Momjian]

Joshua D. Drake wrote:
> > 
> > The only part of this that I see as relevant to us is setting of 
> > development goals.  And we've already discussed this ad nauseum on the 
> > Hackers list and AFAIK have an initial plan (the enhanced TODO), lacking 
> > only the resources to implement it this month.
> 
> Almost the whole thing is relevant :). Keep in mind that I am not saying 

I totally agree!

> that it is negative. For example the NetBSD core is obviously cranked, 
> where our Core tends to stay out of the way. That is a positive.
> 
> On the other hand, we do suffer from the locked project problem (the 
> recent recursive query debacle is a perfect example).

Yep, but fortunately this problem doesn't happen to us often.

> Anyway, the post as I said was for provoking thought, not for 
> antagonistic measures. I saw good and bad and thought it would be good 
> for everyone to review as we are as a project dealing with some of our 
> own growth problems.

Yes.  There are lessons to be learned.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [GENERAL] Query performance inconsistant.

[Matthew Schumacher]

Tom Lane wrote:
> Matthew Schumacher <[EMAIL PROTECTED]> writes:
>> I have been having performance problems with my DB so this morning I
>> added some config to log queries that take more than 250ms.  The result
>> is surprising because some queries will take as long as 10 seconds, but
>> then you do a explain analyze on them they show that indexes are being
>> used and they run very fast.
> 
> Is it possible that it's not directly that query's fault?  For instance
> it could be blocked by a lock held by some other transaction.  I can't
> unfortunately think of any very nice way to deduce this from log entries
> ... you'd have to catch it in the act and look into pg_locks to find out
> who's the perpetrator.
> 
>   regards, tom lane

This does help me try to figure out where the problem is.  The proc in
question inserts in a very large table, and updates another large table.
 Since postgres puts each proc in it's own transaction I'm thinking the
problem may be the database locking these large tables while this proc
is called concurrently.

In order to understand this better I need to know how postgres locking
works and when locks are used.  Do you know of any documentation that I
can read that explains this?

Thanks,
schu

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

Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on NetBSD

[Martijn van Oosterhout]

On Thu, Aug 31, 2006 at 11:18:27AM -0700, Joshua D. Drake wrote:
> On the other hand, we do suffer from the locked project problem (the 
> recent recursive query debacle is a perfect example).

Maybe, but we don't have the extreme form. Patches have been submitted
by people other than the ones saying they'd do it, and no-one got their
head bitten off for it. Indeed, the original person was often grateful
that it wasn't their problem anymore.

One thing about the discussion about locking was where we wanted a more
formal locking strategy (keeping a list). I think this is the wrong
approach. If you want some feature that hasn't seen any recent
discussion, *do it*, don't wait around seeing if someone else will do
it. This was in the article also:

   ... there was no sense that anyone else "owned" a piece
   of Linux (although de facto "ownership" has happened in some parts);
   if you didn't produce, Linus would use someone else's code.  If you
   wanted people to use your stuff, you had to keep moving.

I really think that's a better idea than tracking who is doing what.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on NetBSD

[Joshua D. Drake]

On the other hand, we do suffer from the locked project problem (the 
recent recursive query debacle is a perfect example).


Yep, but fortunately this problem doesn't happen to us often.


I think this might happen more then you think. I ran into it with Alvaro 
just a couple of days ago. I brought up 3/4 items I thought he might be 
interested in working on for 8.3.


The immediate response was well that is such a person's or that a person's.

Now, all we have to do is actually communicate ;) to make sure that we 
move forward to eliminate the lock and we will. However it does point to 
the fact that not everyone is going to take that extra step, some are 
going to assume that it is being worked on.


Sincerely,

Joshua D. Drake





--

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



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

Re: [GENERAL] Installation of Postgres 7.1

[Tom Lane]

Martijn van Oosterhout  writes:
> On Fri, Sep 01, 2006 at 01:57:03AM +0800, Jonathan Chocolate wrote:
>> I am unable to install postgres 7.1.2 on my Ubuntu Linux Box.

> Dear gods, that's an ancient version. You're not going to get a lot of
> help fixing something that old, nearly 6 major releases ago.
> In any case, you're definitly not going to get any help if you don't
> provide any actual error messages.

I recall having to add some #include directives to get 7.1 to compile on
newer versions of Linux, and a troll through our CVS logs also finds
things like this:

2004-10-13 18:09  tgl

* src/interfaces/ecpg/preproc/preproc.y (REL7_1_STABLE): Remove
stray semicolons in old ecpg preproc grammar ... modern bison
versions won't compile it at all with those there.  Probably of
only academic interest now, but ...

You could no doubt get 7.1 to compile on modern tools with a bit of
work, but I think your time would be far more productively spent on
updating to a modern version of Postgres, instead.

regards, tom lane

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

Re: [GENERAL] postgres array quoting

[Alvaro Herrera]

[EMAIL PROTECTED] wrote:
> Hello -
> 
> I am attempting to find a way to make use of arrays of text, as 
> demonstraited by the following:

Interesting problem.  Apparently plperl is not cool about parsing arrays
in the arguments to Perl arrays -- at least I couldn't make it work, and
I don't find any mention in my (admittedly a bit outdated) local copy of
the docs.

I remember we did parse arrays in PL/php to native PHP arrays with which
you could do this, but that code was more than a bit buggy, so I'm wary
of recommending it.

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

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

Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on NetBSD

[Bill Moran]

In response to "Joshua D. Drake" <[EMAIL PROTECTED]>:

> >>
> >> On the other hand, we do suffer from the locked project problem (the 
> >> recent recursive query debacle is a perfect example).
> > 
> > Yep, but fortunately this problem doesn't happen to us often.
> 
> I think this might happen more then you think. I ran into it with Alvaro 
> just a couple of days ago. I brought up 3/4 items I thought he might be 
> interested in working on for 8.3.
> 
> The immediate response was well that is such a person's or that a person's.
> 
> Now, all we have to do is actually communicate ;) to make sure that we 
> move forward to eliminate the lock and we will. However it does point to 
> the fact that not everyone is going to take that extra step, some are 
> going to assume that it is being worked on.

In my experience, some of this is culture.  Some groups communicate more
easily than others.  When people don't communicate well, stuff has to
be done to encourage it.  At the extreme end, stuff has to be done to
enforce it.

I think it's best if it happens naturally, but you can't always count on
that.

-- 
Bill Moran
Collaborative Fusion Inc.

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

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

Re: [GENERAL] postgres array quoting

[Tom Lane]

[EMAIL PROTECTED] writes:
>   * If I could figure a way to pass a variable number of arguments to a
> plperl function, or an array reference, I could use something like this
> function:

Doesn't plperl already contain a feature to convert a Postgres array
into a Perl array?  It may be documented poorly or not at all, but I
definitely see code in there for going the other direction, and one
would think that whoever coded it would have catered for both
conversions.

regards, tom lane

---(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] Query performance inconsistant.

[Jeff Davis]

On Thu, 2006-08-31 at 11:04 -0800, Matthew Schumacher wrote:
> Tom Lane wrote:
> > Matthew Schumacher <[EMAIL PROTECTED]> writes:
> >> I have been having performance problems with my DB so this morning I
> >> added some config to log queries that take more than 250ms.  The result
> >> is surprising because some queries will take as long as 10 seconds, but
> >> then you do a explain analyze on them they show that indexes are being
> >> used and they run very fast.
> > 
> > Is it possible that it's not directly that query's fault?  For instance
> > it could be blocked by a lock held by some other transaction.  I can't
> > unfortunately think of any very nice way to deduce this from log entries
> > ... you'd have to catch it in the act and look into pg_locks to find out
> > who's the perpetrator.
> > 
> > regards, tom lane
> 
> This does help me try to figure out where the problem is.  The proc in
> question inserts in a very large table, and updates another large table.
>  Since postgres puts each proc in it's own transaction I'm thinking the
> problem may be the database locking these large tables while this proc
> is called concurrently.
> 
> In order to understand this better I need to know how postgres locking
> works and when locks are used.  Do you know of any documentation that I
> can read that explains this?

http://www.postgresql.org/docs/8.1/static/mvcc.html

In the "Explicit Locking" section it details the locks acquired by
UPDATE, etc.

>From what you described, I would not expect many locking problems. Are
there any other types of queries you run that may cause a lock? Do you
run periodic "VACUUM FULL" or something? "VACUUM FULL" causes a full
table lock, and is usually not necessary. If so, try running just
"VACUUM" without "FULL".

Regards,
Jeff Davis


---(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] [pgsql-advocacy] Thought provoking piece on NetBSD

[Tom Lane]

Josh Berkus  writes:
> In general, I think that people who harp on PostgreSQL's lack of a 
> benevolent dictator as an inhibitor to progress are people who are not 
> comfortable with democracy and are looking for excuses why company X needs 
> to "take over the project for its own good."

I don't recall having seen that idea being pushed for Postgres ... not
seriously anyway.  However, it's certainly true that historically we've
had effectively *no* project leadership, in the sense of anyone setting
feature goals for releases or creating a long-term roadmap.  Would we
be better off if we had done that?  I'm not sure.

It's pointless to suppose that individual developers would really be
answerable to any project-wide management, since that's not who they're
paid by.  So I tend to think that a project roadmap would be more of an
exercise in wishful thinking than a useful management tool.  OTOH it
*could* be useful, if there are any developers out there wondering what
they should work on next.  Are there any ... and would they listen to a
roadmap if they had one, rather than scratching their own itches?

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Query performance inconsistant.

[Matthew Schumacher]

Jeff Davis wrote:
> http://www.postgresql.org/docs/8.1/static/mvcc.html
> 
> In the "Explicit Locking" section it details the locks acquired by
> UPDATE, etc.
> 
>>From what you described, I would not expect many locking problems. Are
> there any other types of queries you run that may cause a lock? Do you
> run periodic "VACUUM FULL" or something? "VACUUM FULL" causes a full
> table lock, and is usually not necessary. If so, try running just
> "VACUUM" without "FULL".
> 
> Regards,
>   Jeff Davis
> 

Jeff,

I have "autovacuum = on" in the config file with a pretty frequent
autovacuum_naptime, but I'm unsure if that does a vacuum or vacuum full.

schu


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

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

Re: [GENERAL] Query performance inconsistant.

[Tom Lane]

Jeff Davis <[EMAIL PROTECTED]> writes:
> From what you described, I would not expect many locking problems. Are
> there any other types of queries you run that may cause a lock?

Row locks (SELECT FOR UPDATE/SHARE) are a possible problem, particularly
if this is a pre-8.1 Postgres where exclusive row locks were used for
foreign key constraints.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] postgres array quoting

[Joshua D. Drake]

Alvaro Herrera wrote:

[EMAIL PROTECTED] wrote:

Hello -

I am attempting to find a way to make use of arrays of text, as 
demonstraited by the following:


Interesting problem.  Apparently plperl is not cool about parsing arrays
in the arguments to Perl arrays -- at least I couldn't make it work, and
I don't find any mention in my (admittedly a bit outdated) local copy of
the docs.

I remember we did parse arrays in PL/php to native PHP arrays with which
you could do this, but that code was more than a bit buggy, so I'm wary
of recommending it.



Yeah I would second that... our array stuff needs some work (after 8.2).

Sincerely,

Joshua D. Drake




--

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



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

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

Re: [GENERAL] Installation of Postgres 7.1

[Chris Browne]

[EMAIL PROTECTED] ("Jonathan Chocolate") writes:
> I am unable to install postgres 7.1.2 on my Ubuntu Linux Box. I am installing 
> postgres 7.1.2 from source. I stopped at the
> process of invoking the "make" command.
> I have a 7.1.2 database with me and plan to replicate it to another box. 
> Pg_dump reports and error while I was dumping.
> My Linux Boxes are all Ubuntu.
> What can i do?

Consider installing a modern version of PostgreSQL?

7.1.2, which is now over five years old, isn't even the latest release
of 7.1; you should install 7.1.3, if it is 7.1 that you honestly need.
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://cbbrowne.com/info/emacs.html
"I once witnessed  a long-winded, month-long flamewar over  the use of
mice vs. trackballs...It was very silly." -- Matt Welsh

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

   http://archives.postgresql.org

Re: [GENERAL] Query performance inconsistant.

[Tom Lane]

Matthew Schumacher <[EMAIL PROTECTED]> writes:
> I have "autovacuum = on" in the config file with a pretty frequent
> autovacuum_naptime, but I'm unsure if that does a vacuum or vacuum full.

autovacuum *never* does a vacuum full, because that would lead to
unexpected blockages of foreground queries.  Still though, autovac could
be contributing to the problem indirectly.  I'm assuming that most of
your transactions on the problem table are short.  It's possible that
one or more clients are grabbing quasi-exclusive table locks, and
normally you don't notice because they are able to get the lock quickly,
do their work, and get out.  But if autovac is working on the table then
the requestor of the exclusive lock blocks ... and everyone else queues
up behind him, until the vacuum command finishes with the table.

regards, tom lane

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

Re: [GENERAL] Strange error related to temporary tables

[Tom Lane]

Csaba Nagy <[EMAIL PROTECTED]> writes:
>   create temp table temp_report ...;
>   ... do some processing using this table...
>   drop table temp_report;
>   commit;

> This code occasionally triggered the following error (couldn't reliably
> reproduce):
> ERROR: type "temp_report" already exists

BTW, when this happens, does the error persist?  If it's a race
condition you'd expect not (because the guy who successfully created the
temp table would soon drop it again).  When we saw this problem before,
we were speculating that a temp table's pg_type row had somehow not
gotten dropped during table drop, which'd lead to a persistent failure.

Note that a "persistent" failure could still only manifest occasionally,
if the unwanted pg_type row were in a high-numbered pg_temp_NNN schema
that doesn't get used often.  So i guess the correct thing to do is
"select oid, xmin from pg_type where typname = 'temp_report'" and see
if there are any long-lived entries (xmin far away from the others would
be a tipoff).

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on

[Bruce Momjian]

Tom Lane wrote:
> Josh Berkus  writes:
> > In general, I think that people who harp on PostgreSQL's lack of a 
> > benevolent dictator as an inhibitor to progress are people who are not 
> > comfortable with democracy and are looking for excuses why company X needs 
> > to "take over the project for its own good."
> 
> I don't recall having seen that idea being pushed for Postgres ... not
> seriously anyway.  However, it's certainly true that historically we've
> had effectively *no* project leadership, in the sense of anyone setting
> feature goals for releases or creating a long-term roadmap.  Would we
> be better off if we had done that?  I'm not sure.
> 
> It's pointless to suppose that individual developers would really be
> answerable to any project-wide management, since that's not who they're
> paid by.  So I tend to think that a project roadmap would be more of an
> exercise in wishful thinking than a useful management tool.  OTOH it
> *could* be useful, if there are any developers out there wondering what
> they should work on next.  Are there any ... and would they listen to a
> roadmap if they had one, rather than scratching their own itches?

I think the longer someone is with the project the more they start
working on what is good for the project, rather than what interests
them.  I think we have seen many cases of that.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [GENERAL] Dependency graph of all tuples relied upon in a query answer

[Randall Lucas]

On Thu, Aug 31, 2006 at 09:17:54AM -0400, Merlin Moncure wrote:
> On 8/30/06, Randall Lucas <[EMAIL PROTECTED]> wrote:
> 
> >An inverted way of thinking about the problem is the notion of getting a
> >source document (say, a "company registration form") and parsing and
> >storing it in a normalized format.  How do you "tag" the origin of each
> >and every row in every table that resulted from that source document?
> 
> your form should have a code, either entered by the customer or by the
> preparer who enters it into the database, which becomes the key that
> identifies the registration document.  Put that key into other tables.

Yes -- I have flirted with this idea for the case of importing a set of
facts from a single source.  But where it breaks down is in being able
to do the reverse -- ex post facto, to certify the results of a query
as being accurate, and thereby attesting to the underlying facts.

> be careful, you are flirting with EAV thinking.  I think EAV designs
> are terrible.

(Though religion is not at issue here, I am pretty devoutly relational.
I just want a good way to perform audits and other functions on my
relations.)

> > It seems to me that the elegant way to do this would be to get the
> >entire graph of dependencies for not only all tables that reference the
> >entity, but only those rows within those tables that refer specifically
> >to my entity.
> >
> >The query engine should have a pretty good idea about which tables and
> >which rows would actually be used in forming the responses to a given
> >query.  How can I get this information?  Or am I off the deep end (-ency
> >graph) with this one?
> 
> I am not sure where you are going with this.  Maybe you should mock up
> some simple tables and repost your question.

Imagine that I am the SEC.  I look for cases of insider trading, and
when I find a case, I have to go back and double-check all the pieces
of evidence I am using against them.

create table company (
id  serial primary key,
nametext not null
);

create table officer (
id  serial primary key,
company_id  int not null references company(id) on delete cascade,
nametext not null,
title   text not null
);

create table insider_trade (
id  serial primary key,
officer_id int not null references officer(id) on delete cascade,
shares_traded   numeric,
share_price numeric,
trade_date  timestamptz
);

insert into company (name) values ('goodco');
insert into company (name) values ('badco');
insert into officer (company_id, name, title) values (1, 'johnny b. good', 
'ceo');
insert into officer (company_id, name, title) values (1, 'mother teresa', 
'saint');
insert into officer (company_id, name, title) values (2, 'leroy brown', 
'ceo');
insert into insider_trade (officer_id, shares_traded, share_price, 
trade_date) values
(3, '5', '6.66', '2006-07-04 1:23 PM PST');

Now, my database looks like this:

select * from company left join officer on company.id=officer.company_id 
left join insider_trade on officer.id=insider_trade.officer_id;
 id |  name  | id | company_id |  name  | title | id | officer_id | 
shares_traded | share_price |   trade_date   

+++++---+++---+-+
  1 | goodco |  1 |  1 | johnny b. good | ceo   ||| 
  | | 
  1 | goodco |  2 |  1 | mother teresa  | saint ||| 
  | | 
  2 | badco  |  3 |  2 | leroy brown| ceo   |  1 |  3 | 
5 |6.66 | 2006-07-04 14:23:00-07
(3 rows)

If I want to know whom to investigate, I might do a query like this:

select * from company left join officer on company.id=officer.company_id 
left join insider_trade on officer.id=insider_trade.officer_id where 
insider_trade.id is not null;
 id | name  | id | company_id |name | title | id | officer_id | 
shares_traded | share_price |   trade_date   

+---+++-+---+++---+-+
  2 | badco |  3 |  2 | leroy brown | ceo   |  1 |  3 | 
5 |6.66 | 2006-07-04 14:23:00-07
(1 row)

Now that I have this query, in order to make my case, I need to "sign
off" on all of the individual data that went into it.  I would like to
do something like:

select last_query_shown_tuples();
 schema |  table_name   | pk_columns | pk_values 
+---++---
 public |   company |[id]|   [2] 
 public |   officer |[id]|   [3] 
 public | insider_trade |[id]|   [1] 
 (3 rows)

(I am aware that, at le

Re: [GENERAL] [pgsql-advocacy] Thought provoking piece on

[Alvaro Herrera]

Bruce Momjian wrote:
> Tom Lane wrote:

> > It's pointless to suppose that individual developers would really be
> > answerable to any project-wide management, since that's not who they're
> > paid by.  So I tend to think that a project roadmap would be more of an
> > exercise in wishful thinking than a useful management tool.  OTOH it
> > *could* be useful, if there are any developers out there wondering what
> > they should work on next.  Are there any ... and would they listen to a
> > roadmap if they had one, rather than scratching their own itches?

I would certainly listen to a roadmap if it talked to me ...

> I think the longer someone is with the project the more they start
> working on what is good for the project, rather than what interests
> them.  I think we have seen many cases of that.

On my particular case, I generally grab some problem that I perceive as
important and unhandled, and try to do something to remedy it.  This is
how I got here in the first place, by fixing some problems in the
CLUSTER implementation.  This is how I got to doing shared dependencies,
shared row locks and autovacuum -- neither of them were problems that
affected me in any way.  Savepoints were a different matter.  I chose to
work on them because Bruce and other people on this list suggested them
to me, back when I was looking for something to do my undergrad project
in.

So yes, I'd probably work on something "the community" considered
important.

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

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

[GENERAL] SPI_execute (result of a select statement)

[Jasbinder Bali]

Hi,While using SPI_execute for a select statement, how do we store the result returned by select statement (say a C variable)?SPI_execute returns different values for success or failure only as per the postgres manual.
Thanks,Jas

Re: [GENERAL] SPI_execute (result of a select statement)

[Martijn van Oosterhout]

On Fri, Sep 01, 2006 at 02:24:32AM -0400, Jasbinder Bali wrote:
> Hi,
> 
> While using SPI_execute for a select statement, how do we store the result
> returned by select statement (say a C variable)?
> SPI_execute returns different values for success or failure only as per the
> postgres manual.

Did you read the whole page?

http://www.postgresql.org/docs/8.1/interactive/spi-spi-execute.html

Especially the bit where it says:

  If the return value of the function is SPI_OK_SELECT then you may use
  the global pointer SPITupleTable *SPI_tuptable to access the result
  rows.

From there you can get the data. There's also the helper functions like
SPI_getvalue().

http://www.postgresql.org/docs/8.1/interactive/spi-interface-support.html

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] Trigger (Transaction related)

[Harpreet Dhaliwal]

Hi,I've written a trigger after insert on a table (parser) and the trigger calls a function that dynamically loads a shared object written in C.This shared object intends to use the newly inserted row in the table on which trigger is written and insert values in another table, something like this,
sprintf(buffer, "INSERT INTO headers (id, header_content) VALUES ( %d, SELECT contents FROM parser WHERE id = %d ), id, id);SPI_exec(buffer,0).Above line of code is not able to read the values of newly inserted code 
i.e SELECT contents FROM parser WHERE id = %d and throws an error.Is it because its still the same transaction going on and the insert fired on parser talbe won't be reflected in the select query in the shared object which is a part of the same transaction???
Regards,~Harpreet