Re: [GENERAL] Log-based repliaction?

[Richard Broersma Jr]

> For your immediate needs I'd recommend slony.
> It's a quite reliable replication solution that works quite nicely.
> It will sync the replica in nearly real-time and you won't have any 
> transactional problems either.

I seems that slony will pretty much do everything mentioned.  But can it do 
point-in-time
recovery?

Regards,

Richard Broersma Jr.

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

   http://archives.postgresql.org/

Re: [GENERAL] Log-based repliaction?

[Uwe C. Schroeder]

For your immediate needs I'd recommend slony.
It's a quite reliable replication solution that works quite nicely.
It will sync the replica in nearly real-time and you won't have any 
transactional problems either.

Uwe


On Friday 20 October 2006 21:22, Tobias Brox wrote:
> I would eventually like to:
>
>  1) find a way to (stress) test a mockup database server.
>
>  2) fix a near-realtime replication of our production database server
> for pulling out statistics and reports.  Minor bugs would be
> permitable in such a setup.  We don't need a full-fledged data
> warehouse solution just yet.
>
>  3) set up a failover solution *)
>
> Now, why not just get the production database to log all the queries,
> and feed them into a replication database?  I guess this solution
> could be used for archieving any of the three things above.  This idea
> is very trivial, but I never found any pointers while googling, so I
> assume there are some problems with this approach?
>
> Here is some things I can think of at the moment:
>
>  - logging all queries at the production database will slow it down
>considerably (haven't done much testing here)
>
>  - transactional model can easily be broken (postgres can log
>transaction starts, commits and rollbacks, and the transactions are
>also supposed to be serializable ... so I don't see the issue?)
>
>  - disregarded due to the resource consumption on the replica server.
>
>  - some of the transactions aren't really serializable, or relies on
>the exact timestamp for the operation. **)
>
>  - unlike the wal, the log file doesn't get synced for every
>transaction, and this will cause delays and potentially data loss.
>
>  ...anything else?
>
> The simplest thing would be to have one connection open towards the
> replica for every connection made towards the production database, run
> every query in order, and hope the best - should work good for problem
> 1 and problem 2 above.
>
> Still, maybe better (for 2) to filter out only queries altering the
> table and transactions ending with a commit - and do the transactions
> one by one, ordered by commit time.  Would save quite some CPU cycles
> on the replica database compared to the suggestion above.
>
> I was administring a mysql database quite some years ago ... well, it
> was a simple hobby project and we didn't even need transactional
> operations.  However, we did need point-in-time-recovery.  The mysql
> way of fixinge this was to write a separate log containing only the
> queries involving writing to the database.  This log was really nice
> to work with, and it could easily be used for replication as well.
>
>
>
> *) boss has decided that a failover solution is important and should
> be prioritied in 2007 ... even to the point where he's willing to of
> throw money at it.  If anyone have experiences with failover-solutions
> built over a SAN, and can do consultancy services for us, please send
> me a private email ;-)
>
> **) We have had problems with transactions doing "if not exists in
> database then insert".  Two such transactions can be run synchronously
> and result in duplicates.
>
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings

-- 
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 3: Have you checked our extensive FAQ?

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

[GENERAL] Log-based repliaction?

[Tobias Brox]

I would eventually like to: 

 1) find a way to (stress) test a mockup database server.
 
 2) fix a near-realtime replication of our production database server
for pulling out statistics and reports.  Minor bugs would be
permitable in such a setup.  We don't need a full-fledged data
warehouse solution just yet.

 3) set up a failover solution *) 

Now, why not just get the production database to log all the queries,
and feed them into a replication database?  I guess this solution
could be used for archieving any of the three things above.  This idea
is very trivial, but I never found any pointers while googling, so I
assume there are some problems with this approach?

Here is some things I can think of at the moment:

 - logging all queries at the production database will slow it down
   considerably (haven't done much testing here)

 - transactional model can easily be broken (postgres can log
   transaction starts, commits and rollbacks, and the transactions are
   also supposed to be serializable ... so I don't see the issue?)

 - disregarded due to the resource consumption on the replica server.

 - some of the transactions aren't really serializable, or relies on
   the exact timestamp for the operation. **)

 - unlike the wal, the log file doesn't get synced for every
   transaction, and this will cause delays and potentially data loss.

 ...anything else?

The simplest thing would be to have one connection open towards the
replica for every connection made towards the production database, run
every query in order, and hope the best - should work good for problem
1 and problem 2 above.

Still, maybe better (for 2) to filter out only queries altering the
table and transactions ending with a commit - and do the transactions
one by one, ordered by commit time.  Would save quite some CPU cycles
on the replica database compared to the suggestion above.

I was administring a mysql database quite some years ago ... well, it
was a simple hobby project and we didn't even need transactional
operations.  However, we did need point-in-time-recovery.  The mysql
way of fixinge this was to write a separate log containing only the
queries involving writing to the database.  This log was really nice
to work with, and it could easily be used for replication as well.



*) boss has decided that a failover solution is important and should
be prioritied in 2007 ... even to the point where he's willing to of
throw money at it.  If anyone have experiences with failover-solutions
built over a SAN, and can do consultancy services for us, please send
me a private email ;-)

**) We have had problems with transactions doing "if not exists in
database then insert".  Two such transactions can be run synchronously
and result in duplicates.


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

Re: [GENERAL] old duplicate emails

[Gregory S. Williamson]

I've seeing a few ... one just now dated 10/17 on conversion and Oracle ...
G

-Original Message-
From:   [EMAIL PROTECTED] on behalf of Richard Broersma Jr
Sent:   Fri 10/20/2006 8:43 PM
To: General PostgreSQL List
Cc: 
Subject:[GENERAL] old duplicate emails

It is just me, or is everyone getting duplicated old emails from a couple of 
day ago?

Regards,

Richard Broersma Jr.

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


---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=453996c7253166672479766&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:453996c7253166672479766!
---






---(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] why not kill -9 postmaster

[Tom Lane]

"Ian Harding" <[EMAIL PROTECTED]> writes:
> On 10/20/06, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Personally I think the TIP that's really needed is "never remove
>> postmaster.pid by hand".

> When the machine crashes, don't you have to remove the pid file by
> hand to get the Postgres to start?  I seem to remember having to do
> that

Given a properly written startup script and a reasonably recent
postmaster, that shouldn't be necessary.  In any case, retrying the
startup script is a *far* safer habit to develop than manually removing
the pidfile (and putting an "rm" into the script itself is folly of the
first magnitude).

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] Overload after some minutes, please help!

[Tom Lane]

"Peter Bauer" <[EMAIL PROTECTED]> writes:
> we have a theory for the root of all evil which causes a reproducable
> deadlock which is not detected by Postgre:

> The DELETE statement contains a select which waits for a sharelock
> (according to pg_locks and pg_stat_activity) on rows locked by the
> UPDATE statement. The UPDATE itself waits to get a lock for some rows
> which are exclusively locked by the DELETE statement (got from its
> sub-SELECT).

> What do you think about this theory?

Not much.  It's been years since anyone found a bug in the deadlock
detector; if you want us to believe you have an undetected deadlock,
you'll need more evidence than an unsupported assertion.

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

[GENERAL] old duplicate emails

[Richard Broersma Jr]

It is just me, or is everyone getting duplicated old emails from a couple of 
day ago?

Regards,

Richard Broersma Jr.

---(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] Custom types and array equality

[Tom Lane]

Morgan Kita <[EMAIL PROTECTED]> writes:
> This should be a relatively simple question... I just made a custom type
> in C, which I will use to build an array in one of my tables. ...

> However, should array_eq be called for unique constraints? What am I
> missing here to get this system working?

array_eq couldn't care less whether you have an operator named '='.
What it wants to find is a default btree index opclass for your datatype.
That represents an actual assertion that the operators behave in a
certain way, whereas an operator merely named '=' might do anything.

There's a fair amount of other infrastructure that also relies on
btree opclasses to tell it how operators behave, so it's worth your
trouble to jump through this hoop.

regards, tom lane

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

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

Re: [GENERAL] c (lowercase) privilege

[Alvaro Herrera]

Javier Carlos wrote:
>Hi,
> 
>Does anybody know what's the meaning of the c (lowercase) privilege in
> PostgreSQL 8.2 Beta?

"connect"



-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(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] Custom types and array equality

[Morgan Kita]

Hi,

This should be a relatively simple question... I just made a custom type
in C, which I will use to build an array in one of my tables. I defined
an equality function in C, and declared an operator = on my custom type
that calls my equality operator.

Along the lines of:
CREATE OPERATOR = (
   leftarg = mytype, rightarg = mytype, procedure = mytype_equal,
   commutator = = 
);

However, I cant seem to add a useful unique constraint on the table that
includes a column of type mytype[]. When I insert a new record it
complains that it can't find a comparison operator for the type. I did a
bit of digging and found in array.c that error message is produced when
calling array_cmp and the type has no defined comparison operator...
However, should array_eq be called for unique constraints? What am I
missing here to get this system working? The operator itself works when
I call it explicitly on two arrays of mytype.

Thanks for any help ahead of time!


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

Re: [GENERAL] Question with tsearch2 (or it might be a general one too)

[Gregory S. Williamson]

Ritesh --

You are correct in thinking that "@@" is a special operator defined for 
tesearch2; it uses the GIST indexes to do a search, but more than that I can't 
say, since I am not really familiar with tsearch2. (In the postGIS world there 
is a vaguely equivalent operator, "&&", again using postgres' ability to define 
ones own data types and functions.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC


-Original Message-
From:   [EMAIL PROTECTED] on behalf of Ritesh Nadhani
Sent:   Thu 10/19/2006 11:38 AM
To: pgsql-general@postgresql.org
Cc: 
Subject:[GENERAL] Question with tsearch2 (or it might be a general one 
too)

Hello

A newbie to PostgreSQL from MySQL and just trying to learn tsearch2. In 
one of the examples at:

http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html

the query given is:

SELECT intindex, strTopic FROM tblmessages
 WHERE idxfti @@ to_tsquery('default', 'gettysburg & 
address')
 AND strMessage ~* '.*men are created equal.*';

What does the '@@' in the query means?

I did a search at:

http://search.postgresql.org/www.search?ul=http%3A%2F%2Fwww.postgresql.org%2Fdocs%2F8.1%2Finteractive%2F%25&fm=on&cs=utf-8&q=%40%40

an it dosnt return any result.

Is this specific to tsearch2? What does that mean?

Ritesh

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

   http://archives.postgresql.org/


---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=453969c4232531465134470&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:453969c4232531465134470!
---






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

[GENERAL] Runaway functions killed all active connections

[Jeff Davis]

I got the following error (v8.1.4):

PANIC:  ERRORDATA_STACK_SIZE exceeded

There was a discussion about this same error on the list 3 days ago.
However, it looks like I'm having a different problem. 

I have an ON INSERT rule that executes 3 functions similar to the
following function:

CREATE OR REPLACE FUNCTION get_text_id(TEXT) RETURNS INT
  VOLATILE LANGUAGE plpgsql AS
$get_text_id$
DECLARE
  text_id INT;
BEGIN
  LOOP
SELECT INTO text_id id FROM textid WHERE name=$1;
IF FOUND THEN
  RETURN text_id;
END IF;
BEGIN
  INSERT INTO textid(name) VALUES($1);
  RETURN CURRVAL('textid_id_seq');
EXCEPTION WHEN unique_violation THEN
END;
  END LOOP;
END;
$get_text_id$;

And I received the error when I inserted about 2M records. I assume it's
this function. Would this be expected to cause that problem? Is there an
easier way to do what I need? How can I try to be safe from this problem
in the future (at least so it doesn't kill other active connections)?

I have another simpler function which I don't think would be the
problem:

CREATE OR REPLACE FUNCTION value(TEXT) RETURNS INT
  STABLE LANGUAGE plpgsql AS
$$
DECLARE
  value INT;
BEGIN
  SELECT INTO value my_value FROM my_table
  WHERE name = $1;
  IF FOUND THEN
RETURN value;
  ELSE
RAISE EXCEPTION 'Invalid name';
  END IF;
END;
$$;

Also, I am inserting the records from a temporary table that was created
in the same transaction, if that makes any difference.

Regards,
Jeff Davis


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

   http://archives.postgresql.org/

[GENERAL] unstable postgres on freebsd

[Wes Sheldahl]

Hi,I seem to have an unstable/unreliable installation of postgresql, and I'm not sure how to troubleshoot it.Symptoms:$ psql postgrespsql: FATAL:  semctl(851974, 3, SETVAL, 0) failed: Invalid argument
(restart postgresql)$ psql postgresWelcome to psql 8.1.4, the PostgreSQL interactive terminal.(rest of welcome message and psql prompt)It will work fine for a while, but later it will stop accepting connections with the above error, until I restart postgresql again. It also happens when I call a ruby script that connects to postgres: fails with same error message sometimes, but works after restarting postgresql.
Versions/Environment:Postgresql 8.1.4 (installed from ports on FreeBSD 6.1)I've already tried reinstalling the postgresql81-server and postgresql81-client ports and their dependencies via portupgrade, but the symptoms persist. What else should I try?
-- Wes Sheldahl[EMAIL PROTECTED]

Re: [GENERAL] PostgreSQL, LGPL and GPL.

[Alexander Terekhov]

Merijn de Weerd wrote:
[...]
> If you distribute the PostgreSQL server software linked with
> the PostGIS software, then you have to comply with the GPL
> for both parts of that derivative work.
> 
> If you don't distribute any server software, you do not have
> to worry about what the GPL requires.

Yeah. A+B is GNU derivative. Go to doctor, schizophrenic de Weerd. 

regards,
alexander.

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

Re: [GENERAL] PostgreSQL, LGPL and GPL.

[Merijn de Weerd]

On 2006-10-20, Karen Hill <[EMAIL PROTECTED]> wrote:
> If you make create a PostgreSQL database that uses PostGIS and you
> distribute that database, than your database (tables, stored
> procedures, views, etc) are GPL?  

No, because those tables, stored procedures etc. are not
derivative works of the PostGIS code. 

> Like wise if you create a client that
> connects to that database, do they also become GPL?  Does PostgreSQL in
> effect become GPL when using PostGIS because PostGIS accesses parts of
> PostgreSQL?

You have to distinguish the server software from what you store
in the database. Your data does not "become GPL" because it's stored
in a database managed by a GPL-licensed server. 

If you distribute the PostgreSQL server software linked with
the PostGIS software, then you have to comply with the GPL
for both parts of that derivative work.

If you don't distribute any server software, you do not have
to worry about what the GPL requires.

The MySQL people claim that connecting to their database server
means your client has to be made GPL, but they're pretty much
the only ones saying that.

> Npgsql is LGPL.  It means you must release the source of Npgsql when
> distributing it, and if you modify Npgsql, but not have to release the
> source under the (L)GPL of the software that calls Npgsql functions?

Yes.

> If you provide the source on a CD and the (GPL/LGPL) license as a text
> file on that CD if you distribute, then are your obligations met under
> the GPL/LGPL?

Yes.

>  What if those you distribute to lose the source code CD,
> can they then come after you X number of years later demanding the
> source?

No. They got the source, they were careless, that's too bad
for them. If and only if you told them "instead of giving you
the source right now, you (and anyone who wants) can write to 
me any time in the next three years and I'll give the source
to whoever writes to me", then people can demand the source.

> For the developers of LGPL/GPL like Npgsql, why do you not dual
> license?  Have a model like MySQL where one can purchase a BSD licensed
> version or use the GPL/LGPL one.

Dual licensing is basically trading on ignorance of what
the GPL requires and how to work with that. It's not a viable
long-term business model. 

And besides, it's much harder to get contributions from third
party if you want to hold that amount of control over the source.

Merijn

-- 
Remove +nospam to reply

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

[GENERAL] strange encoding behavior

[Jeff Davis]

The following encoding behavior seems strange to me (v8.1.4). I have
read the docs, but I am still confused.

I have a UTF8 encoded database. I can do

=> SELECT '\xb9'::text;

But that seems to be the only way to get an invalid utf8 byte sequence
into a text type.

Even if I do PQexecParams and send the data as binary format (and type
text), I get a conversion error.

If I send the invalid character in a raw PQexec query, I assume that
postgres tries to convert it to cstring first, causing the conversion
error. That means it's impossible to send any character that's an
invalid UTF8 sequence in a raw query (as a value, anyway), as far as I
can tell. What motivates this question is that I have an application
inserting these invalid characters (using them in the raw query), and I
am finding it difficult to migrate to the UTF8-encoded database.

It seems strange that it's possible to put invalid utf8 byte sequences
in a text field, but only by using the E''-style escape sequences. The
only way I have found to do it using PQexecParams with the binary data
is something like:

=> SELECT textin(byteaout($1)); -- $1 is binary format, type bytea

So, if I were to sum this up in a single question, why does cstring not
accept invalid utf8 sequences? And if it doesn't, why are they allowed
in any text type?

Regards,
Jeff Davis

PS: I posted a similar question yesterday that included a lot of useless
information. I'm not trying to repost, I'm trying to focus my question a
little more.

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

Re: [GENERAL] why not kill -9 postmaster

[Ian Harding]

On 10/20/06, Tom Lane <[EMAIL PROTECTED]> wrote:

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Shane Ambler wrote:
>> The one thing worse than kill -9 the postmaster is pulling the power
>> cord out of the server. Which is what makes UPS's so good.
>>
>> If your server is changing the data file on disk and you pull the power
>> cord, what chance do you expect of reading that data file again?

> 1.  That's what we have WAL for.  The only thing that can really kill
> you is the use of non-battery-backed write cache.

The important distinction here is "will you lose data" vs "can you start
a new server without tedious manual intervention" (ipcrm etc).  kill -9
won't lose data, but you may have to clean up after it.  And, as Andreas
already noted, some people have been seen to mess up the manual
intervention part badly enough to cause data loss by themselves.
Personally I think the TIP that's really needed is "never remove
postmaster.pid by hand".



When the machine crashes, don't you have to remove the pid file by
hand to get the Postgres to start?  I seem to remember having to do
that

- Ian Never-Say-Never Harding

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

Re: [GENERAL] Overload after some minutes, please help!

[Peter Bauer]

Hi all,

we have a theory for the root of all evil which causes a reproducable
deadlock which is not detected by Postgre:

The DELETE statement contains a select which waits for a sharelock
(according to pg_locks and pg_stat_activity) on rows locked by the
UPDATE statement. The UPDATE itself waits to get a lock for some rows
which are exclusively locked by the DELETE statement (got from its
sub-SELECT).

What do you think about this theory?

thx,
Peter

2006/10/19, Peter Bauer <[EMAIL PROTECTED]>:

thank you very much, we will test it

br,
Peter

2006/10/19, Jim C. Nasby <[EMAIL PROTECTED]>:
> On Thu, Oct 19, 2006 at 01:57:56PM +0200, Peter Bauer wrote:
> In the update statement, don't wrap the ID values in quotes. At best
> it's extra work; at worse it will fool the planner into not using the
> index.
>
> > shared_buffers = 1000   # min 16 or max_connections*2, 8KB each
>
> This is *way* too small for what you're trying to do. Try a minimum of
> 10% of memory, and 50% of memory may be a better idea.
>
> > #temp_buffers = 1000# min 100, 8KB each
> > #max_prepared_transactions = 5  # can be 0 or more
> > # note: increasing max_prepared_transactions costs ~600 bytes of shared 
memory
> > # per transaction slot, plus lock space (see max_locks_per_transaction).
> > work_mem = 20480# min 64, size in KB,
>
> Making that active might help a lot, but beware of running the machine
> out of memory...
>
> > #max_fsm_pages = 2  # min max_fsm_relations*16, 6 bytes each
>
> Probably needs to get increased.
>
> > #bgwriter_delay = 200   # 10-1 milliseconds between rounds
> > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
> > #bgwriter_lru_maxpages = 5  # 0-1000 buffers max written/round
> > #bgwriter_all_percent = 0.333   # 0-100% of all buffers scanned/round
> > #bgwriter_all_maxpages = 5  # 0-1000 buffers max written/round
>
> The background writer might need to be tuned more aggressively.
>
> > #checkpoint_warning = 30# in seconds, 0 is off
>
> I'd set that closer to 300 to make sure you're not checkpointing a lot,
> though keep in mind that will impact failover time.
>
> > effective_cache_size = 44800# typically 8KB each
>
> The machine only has 1/2G of memory?
>
> > #autovacuum_naptime = 60# time between autovacuum runs, in secs
>
> I'd drop that to 30.
>
> > #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
> > # vacuum
> > #autovacuum_analyze_threshold = 500 # min # of tuple updates before
> > # analyze
> > #autovacuum_vacuum_scale_factor = 0.4   # fraction of rel size before
> > # vacuum
> > #autovacuum_analyze_scale_factor = 0.2  # fraction of rel size before
> > # analyze
>
> I'd cut the above 4 in half.
>
> --
> Jim Nasby[EMAIL PROTECTED]
> EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)
>



---(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] SQL injection in a ~ or LIKE statement

[hefferon9]

Hello,

I'm concerned about whether the usual parameter escaping mechanism is enough in 
a LIKE or regular expression search.

I run a recent Postgres version and use the Python connector psycopg2 for a web 
application.  I understand that if I always escape as in 

  dBres=dBcsr.execute('SELECT docText FROM documents WHERE 
name=%(storyName)s',{'storyName':storyName})
 
then I am doing the right thing.  Suppose now that I want to search the text of 
those documents?  I have been unable to find if I need to anything more for a 
LIKE or regex search, and also unable to find any assurance that it is enough.  
(No doubt I've not looked in the right place; sorry.)

I plan to add full text searching also; is the escaping mechanism enough there?

Thank you for your help,
Jim

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

[GENERAL] Question with tsearch2 (or it might be a general one too)

[Ritesh Nadhani]

Hello

A newbie to PostgreSQL from MySQL and just trying to learn tsearch2. In 
one of the examples at:


http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html

the query given is:

SELECT intindex, strTopic FROM tblmessages
WHERE idxfti @@ to_tsquery('default', 'gettysburg & 
address')

AND strMessage ~* '.*men are created equal.*';

What does the '@@' in the query means?

I did a search at:

http://search.postgresql.org/www.search?ul=http%3A%2F%2Fwww.postgresql.org%2Fdocs%2F8.1%2Finteractive%2F%25&fm=on&cs=utf-8&q=%40%40

an it dosnt return any result.

Is this specific to tsearch2? What does that mean?

Ritesh

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

  http://archives.postgresql.org/

Re: [GENERAL] c (lowercase) privilege

[Stephan Szabo]

On Fri, 20 Oct 2006, Javier Carlos wrote:

>Does anybody know what's the meaning of the c (lowercase) privilege in
> PostgreSQL 8.2 Beta?

That should be for CONNECT privilege.

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

[GENERAL] c (lowercase) privilege

[Javier Carlos]

   Hi,

   Does anybody know what's the meaning of the c (lowercase) privilege in
PostgreSQL 8.2 Beta?

bd_test=# SELECT datacl FROM pg_catalog.pg_database WHERE datname='bd_test';
datacl
--
 {=CTc/user1,user1=CTc/user1}


   In 8.1 documentation is not listed:

  r -- SELECT ("read")
  w -- UPDATE ("write")
  a -- INSERT ("append")
  d -- DELETE
  R -- RULE
  x -- REFERENCES
  t -- TRIGGER
  X -- EXECUTE
  U -- USAGE
  C -- CREATE
  T -- TEMPORARY
arwdRxt -- ALL PRIVILEGES (for tables)
  * -- grant option for preceding privilege


   Thanks in advance,

   Javier


-
nediam.com.mx/en/


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

Re: [GENERAL] Upgrade 7.4 to 8.1 or 8.2?

[John Sidney-Woollett]

Thanks for the info. I think that we'll move to 8.1.5 first, and then 
8.2 when it's stable. We have to keep our test and production systems in 
sync (version-wise).


John

Merlin Moncure wrote:

I've been developing against 8.2 for months without a single 8.2
specific problem. I run both linux and windows in high load
environments (not so much windows these days tho).  I'm going to go
against the grain here and say go for it: yes, it is faster.  It's up
to you to determine a test period long enough to build up a trust
level.

possible downsides:
* catalog changes during beta period. (dump/reload to update to 8.2
proper, etc).
* last minute feature change.  note recent small change in insert
returning as an example.
* risk of undiscovered bug

ideally, i'd develop/test vs. 8.2 beta, and switch production system
at 8.2 release.

merlin


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

Re: [GENERAL] PostgreSQL, LGPL and GPL.

[Martijn van Oosterhout]

On Fri, Oct 20, 2006 at 03:35:34PM -0500, Ron Johnson wrote:
> Exactly.  The "Linus View" is that dynamic linking and "socket
> conversations" are *not* linking in the GPL2 meaning, but the FSF &
> RMS think differently.  The GPL3 seems to codify that strictness.

Dynamic linking may be an issue, but talking over a socket doesn't
create any kind of dependancy at all. I don't think anyone has ever
tried to claim that talking to a GPL server requires your code to be
GPL also. The existing counterexamples alone...

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] PostgreSQL, LGPL and GPL.

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/20/06 13:49, Joshua D. Drake wrote:
> Karen Hill wrote:
>> I was looking through the various contrib packages and pgfoundry
>> projects.  I noticed that many of them are GPL like PostGIS or LGPL
>> like Npgsql.   I have questions.
>>
>> If you make create a PostgreSQL database that uses PostGIS and you
>> distribute that database, than your database (tables, stored
>> procedures, views, etc) are GPL?  Like wise if you create a client that
>> connects to that database, do they also become GPL?  Does PostgreSQL in
>> effect become GPL when using PostGIS because PostGIS accesses parts of
>> PostgreSQL?
> 
> O.k. first, nobody here is a lawyer. You should be asking them. However
> in my experience:
> 
> PostgreSQL + LGPL is fine
> PostgreSQL + GPL it depends.
> 
> For example, if Slony was GPL and you used Slony + PostgreSQL with your
> web application to distribute load, it is questionable if you would be
> able to keep your sources to yourself as the GPL becomes a distributed
> and required component of the application.
> 
> But it all depends on a ton of components.
> 
> In short, don't ask geeks legal questions, they don't know even if they
> think they do. You need to ask an attorney.
> 
> Remember that the law is all about interpretation.

Exactly.  The "Linus View" is that dynamic linking and "socket
conversations" are *not* linking in the GPL2 meaning, but the FSF &
RMS think differently.  The GPL3 seems to codify that strictness.

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

iD8DBQFFOTMWS9HxQb37XmcRAmu8AKC0P8/Eq+ISD88aJBYvjGY9NaeJDwCfeyVU
QJ224doTckpNTczIDcXTr9E=
=rP+O
-END PGP SIGNATURE-

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

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

Re: [GENERAL] PostgreSQL, LGPL and GPL.

[Joshua D. Drake]

Karen Hill wrote:
> John Hasler wrote:
> 
>>> Npgsql is LGPL.  It means you must release the source of Npgsql when
>>> distributing it, and if you modify Npgsql, but not have to release the
>>> source under the (L)GPL of the software that calls Npgsql functions?
>> Pretty much, but you must provide your software in a form that can be
>> relinked.
>>
> 
> What does that mean?  In .NET, you add a reference to the Npgsql dll,
> and then add "Using Npgsql;" to the top of the class you want to use
> Npgsql in.  So if you wrote something like this it would have to go
> under the LGPL?:

What he means is if you modified npgsql.dll itself you would have to
provide those modifications back. Not your code, just the code that
modifies npgsql.

J



> 
> Using Npgsql;
> namespace foo{
> class foobar
> {
>   foobar()
> {
>String connectionString = "";
>NpgsqlConnection conn = new NpgsqlConnection(connectionString);
> }
> 
> }
> 
> }
> 
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org/
> 


-- 

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

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


---(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] PostgreSQL, LGPL and GPL.

[Peter Eisentraut]

Karen Hill wrote:
> If you make create a PostgreSQL database that uses PostGIS and you
> distribute that database, than your database (tables, stored
> procedures, views, etc) are GPL?

Nothing ever becomes GPL automatically.  You may wish to distribute your 
own work under the GPL, but you don't have to.

> Like wise if you create a client 
> that connects to that database, do they also become GPL?

Likewise.

> Does 
> PostgreSQL in effect become GPL when using PostGIS because PostGIS
> accesses parts of PostgreSQL?

Likewise.

> Npgsql is LGPL.  It means you must release the source of Npgsql when
> distributing it, and if you modify Npgsql, but not have to release  
> the source under the (L)GPL of the software that calls Npgsql
> functions?

Correct.

> If you provide the source on a CD and the (GPL/LGPL) license as a
> text file on that CD if you distribute, then are your obligations met
> under the GPL/LGPL?

That depends on the distribution methods of the non-source.

> What if those you distribute to lose the source 
> code CD, can they then come after you X number of years later
> demanding the source?

No.

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

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

Re: [GENERAL] PostgreSQL, LGPL and GPL.

[Karen Hill]

John Hasler wrote:

> > Npgsql is LGPL.  It means you must release the source of Npgsql when
> > distributing it, and if you modify Npgsql, but not have to release the
> > source under the (L)GPL of the software that calls Npgsql functions?
>
> Pretty much, but you must provide your software in a form that can be
> relinked.
>

What does that mean?  In .NET, you add a reference to the Npgsql dll,
and then add "Using Npgsql;" to the top of the class you want to use
Npgsql in.  So if you wrote something like this it would have to go
under the LGPL?:

Using Npgsql;
namespace foo{
class foobar
{
  foobar()
{
   String connectionString = "";
   NpgsqlConnection conn = new NpgsqlConnection(connectionString);
}

}

}


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

   http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL, LGPL and GPL.

[Joshua D. Drake]

Karen Hill wrote:
> I was looking through the various contrib packages and pgfoundry
> projects.  I noticed that many of them are GPL like PostGIS or LGPL
> like Npgsql.   I have questions.
> 
> If you make create a PostgreSQL database that uses PostGIS and you
> distribute that database, than your database (tables, stored
> procedures, views, etc) are GPL?  Like wise if you create a client that
> connects to that database, do they also become GPL?  Does PostgreSQL in
> effect become GPL when using PostGIS because PostGIS accesses parts of
> PostgreSQL?

O.k. first, nobody here is a lawyer. You should be asking them. However
in my experience:

PostgreSQL + LGPL is fine
PostgreSQL + GPL it depends.

For example, if Slony was GPL and you used Slony + PostgreSQL with your
web application to distribute load, it is questionable if you would be
able to keep your sources to yourself as the GPL becomes a distributed
and required component of the application.

But it all depends on a ton of components.

In short, don't ask geeks legal questions, they don't know even if they
think they do. You need to ask an attorney.

Remember that the law is all about interpretation.

You killed that man? Yes I did -- but did I murder him? Or was it
involuntary manslaughter? .. Neither; it was self defense. No it was not
self defense, you were driving your car. Oh your right, it was a crime
of passion -- let's make a plea deal.

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/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


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

   http://archives.postgresql.org/

Re: [GENERAL] Overload after some minutes, please help!

[Peter Bauer]

Hi all,

for further investigation we seperated the sub-SELECT from the DELETE
statement and it looks like the SELECT is usually finished in some 100
milliseconds but after some minutes it suddenly takes some minutes.

Peter

2006/10/20, Peter Bauer <[EMAIL PROTECTED]>:

Hi all,

we have a theory for the root of all evil which causes a reproducable
deadlock which is not detected by Postgre:

The DELETE statement contains a select which waits for a sharelock
(according to pg_locks and pg_stat_activity) on rows locked by the
UPDATE statement. The UPDATE itself waits to get a lock for some rows
which are exclusively locked by the DELETE statement (got from its
sub-SELECT).

What do you think about this theory?

thx,
Peter

2006/10/19, Peter Bauer <[EMAIL PROTECTED]>:
> thank you very much, we will test it
>
> br,
> Peter
>
> 2006/10/19, Jim C. Nasby <[EMAIL PROTECTED]>:
> > On Thu, Oct 19, 2006 at 01:57:56PM +0200, Peter Bauer wrote:
> > In the update statement, don't wrap the ID values in quotes. At best
> > it's extra work; at worse it will fool the planner into not using the
> > index.
> >
> > > shared_buffers = 1000   # min 16 or max_connections*2, 8KB each
> >
> > This is *way* too small for what you're trying to do. Try a minimum of
> > 10% of memory, and 50% of memory may be a better idea.
> >
> > > #temp_buffers = 1000# min 100, 8KB each
> > > #max_prepared_transactions = 5  # can be 0 or more
> > > # note: increasing max_prepared_transactions costs ~600 bytes of shared 
memory
> > > # per transaction slot, plus lock space (see max_locks_per_transaction).
> > > work_mem = 20480# min 64, size in KB,
> >
> > Making that active might help a lot, but beware of running the machine
> > out of memory...
> >
> > > #max_fsm_pages = 2  # min max_fsm_relations*16, 6 bytes each
> >
> > Probably needs to get increased.
> >
> > > #bgwriter_delay = 200   # 10-1 milliseconds between rounds
> > > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
> > > #bgwriter_lru_maxpages = 5  # 0-1000 buffers max written/round
> > > #bgwriter_all_percent = 0.333   # 0-100% of all buffers scanned/round
> > > #bgwriter_all_maxpages = 5  # 0-1000 buffers max written/round
> >
> > The background writer might need to be tuned more aggressively.
> >
> > > #checkpoint_warning = 30# in seconds, 0 is off
> >
> > I'd set that closer to 300 to make sure you're not checkpointing a lot,
> > though keep in mind that will impact failover time.
> >
> > > effective_cache_size = 44800# typically 8KB each
> >
> > The machine only has 1/2G of memory?
> >
> > > #autovacuum_naptime = 60# time between autovacuum runs, in secs
> >
> > I'd drop that to 30.
> >
> > > #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
> > > # vacuum
> > > #autovacuum_analyze_threshold = 500 # min # of tuple updates before
> > > # analyze
> > > #autovacuum_vacuum_scale_factor = 0.4   # fraction of rel size before
> > > # vacuum
> > > #autovacuum_analyze_scale_factor = 0.2  # fraction of rel size before
> > > # analyze
> >
> > I'd cut the above 4 in half.
> >
> > --
> > Jim Nasby[EMAIL PROTECTED]
> > EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)
> >
>



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

  http://archives.postgresql.org/

Re: [GENERAL] Upgrade 7.4 to 8.1 or 8.2?

[Merlin Moncure]

On 10/20/06, John Sidney-Woollett <[EMAIL PROTECTED]> wrote:

We're looking to upgrade from 7.4 - should we go to 8.1.5 or 8.2?

We have two databases; 7.4.6 and 7.4.11 in a master slave config using
Slony. Both databases use the C locale with UTF-8 encoding on unix.

We've dumped and loaded the data into an 8.1.4 database and have seen no
problems with invalid UTF-8 sequences. So we're fairly happy that we can
upgrade to 8.1.5 pretty easily using Slony.

We're really looking for some extra performance right now.

Are the differences between 8.1.5 and 8.2 significant? Is 8.2 more about
speed or new features?


I've been developing against 8.2 for months without a single 8.2
specific problem. I run both linux and windows in high load
environments (not so much windows these days tho).  I'm going to go
against the grain here and say go for it: yes, it is faster.  It's up
to you to determine a test period long enough to build up a trust
level.

possible downsides:
* catalog changes during beta period. (dump/reload to update to 8.2
proper, etc).
* last minute feature change.  note recent small change in insert
returning as an example.
* risk of undiscovered bug

ideally, i'd develop/test vs. 8.2 beta, and switch production system
at 8.2 release.

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

[GENERAL] PostgreSQL, LGPL and GPL.

[Karen Hill]

I was looking through the various contrib packages and pgfoundry
projects.  I noticed that many of them are GPL like PostGIS or LGPL
like Npgsql.   I have questions.

If you make create a PostgreSQL database that uses PostGIS and you
distribute that database, than your database (tables, stored
procedures, views, etc) are GPL?  Like wise if you create a client that
connects to that database, do they also become GPL?  Does PostgreSQL in
effect become GPL when using PostGIS because PostGIS accesses parts of
PostgreSQL?

Npgsql is LGPL.  It means you must release the source of Npgsql when
distributing it, and if you modify Npgsql, but not have to release the
source under the (L)GPL of the software that calls Npgsql functions?

If you provide the source on a CD and the (GPL/LGPL) license as a text
file on that CD if you distribute, then are your obligations met under
the GPL/LGPL?  What if those you distribute to lose the source code CD,
can they then come after you X number of years later demanding the
source?

For the developers of LGPL/GPL like Npgsql, why do you not dual
license?  Have a model like MySQL where one can purchase a BSD licensed
version or use the GPL/LGPL one.


regards,
Karen


---(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] Upgrade 7.4 to 8.1 or 8.2?

[Brandon Aiken]

From what I've seen 8.2 is going to offer several nice new features, but
I would move to 8.1.5 for now.

Honestly, I would probably wait until 8.2.1 is available before moving
to that subversion.  No offense against the PG team, but I've been
burned by zeroes once too many times to go live with 8.2.0.

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of John
Sidney-Woollett
Sent: Friday, October 20, 2006 11:10 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Upgrade 7.4 to 8.1 or 8.2?

We're looking to upgrade from 7.4 - should we go to 8.1.5 or 8.2?

We have two databases; 7.4.6 and 7.4.11 in a master slave config using 
Slony. Both databases use the C locale with UTF-8 encoding on unix.

We've dumped and loaded the data into an 8.1.4 database and have seen no

problems with invalid UTF-8 sequences. So we're fairly happy that we can

upgrade to 8.1.5 pretty easily using Slony.

We're really looking for some extra performance right now.

Are the differences between 8.1.5 and 8.2 significant? Is 8.2 more about

speed or new features?

John

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

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

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

Re: [GENERAL] Upgrade 7.4 to 8.1 or 8.2?

[Jeff Davis]

On Fri, 2006-10-20 at 16:09 +0100, John Sidney-Woollett wrote:
> We're looking to upgrade from 7.4 - should we go to 8.1.5 or 8.2?
> 

I think the biggest question is whether your deployment can wait for the
stable release of 8.2, including possible delays.

Also, of course, check out the release notes to see if there's anything
you're interested in. If not, probably 8.1 is the way to go because it
will be more stable for some time.

Either way, try out your application on the 8.2 betas. Testing the beta
helps everyone.

Regards,
Jeff Davis


---(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] hardware failure - data recovery

[Jerry Sievers]

Rick Gigger <[EMAIL PROTECTED]> writes:

> To make a long story short lets just say that I had a bit of a
> hardware failure recently.
> 
> If I got an error like this when trying to dump a db from the mangled
> data directory is it safe to say it's totally hosed or is there some
> chance of recovery?

Why don't you try dumping just the critical tables using pg_dump -t?

Also perhaps use psql and try looking up in pg_class for the damaged
tables by OID.  You may be able to drop just a few tables and then
dump the DB normally.  This assumes the damaged table(s) are
non-critical...

I suggest you stop Pg first, take an FS backup of the entire cluster
before this so you cahn try various approaches if needed.

> pg_dump: ERROR:  could not open relation 1663/18392/18400: No such
> file or directory
> pg_dump: SQL command to dump the contents of table "file" failed:
> PQendcopy() failed.
> pg_dump: Error message from server: ERROR:  could not open relation
> 1663/18392/18400: No such file or directory
> pg_dump: The command was: COPY public.file (vfs_id, vfs_type,
> vfs_path, vfs_name, vfs_modified, vfs_owner, vfs_data) TO stdout;
> 
> Thanks,
> 
> Rick
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 

-- 
---
Jerry Sievers   305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobilehttp://www.JerrySievers.com/

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

Re: [GENERAL] why not kill -9 postmaster

[Harpreet Dhaliwal]

After all that discussion that took place while i was sleeping, I have a few more questions simply haunting me.Someitmes, rather most of the times, when I start postgres using pg_ctl, it says antoher postmaster is running. Being a total naive about the hazzards of kill -9 postmaster, i simply used to kill -9 all postmaster related process IDs. 
Now, what should i do to get rid of the postmaster that is already running from a safe perspective.Also, even though it says, postmaster is still running, i can't start my pgadmin because it starts crying over the fact that postgres server is not running.
Another thing that worries me is the importance of postmaster.pid.What happens if I simply do rm postmaster.pid after killing all the postmaster processes.How big a pain in the neck is that going to be?
Thanks,~HarpreetOn 10/20/06, Tom Lane <[EMAIL PROTECTED]> wrote:
Martijn van Oosterhout  writes:> Well, if you kill -9 the postmaster all the connections stay alive and> stay processing tuples and writing to disk, except the coordination is
> gone.The postmaster isn't involved in any critical inter-backend coordination.If you kill -9 the postmaster *and then kill or wait out all thebackends*, you won't lose data.  This is not a desirable long-term
operating mode, because it cripples autovacuum and some other things,but it's not dangerous.The only really serious risk I'm aware of in this scenario is:1. DBA does "kill -9" postmaster, but some backends are still alive and
processing.2. DBA tries to start new postmaster, gets message about "shared memorysegment still in use".3. DBA does "rm postmaster.pid" (this is the step that qualifies himas an idiot).
4. DBA starts new postmaster.  Since the interlock file is gone, itstarts up without any awareness that there are old backends still alive.At this point, you have two separate sets of backends that are not
communicating (they're using two different shared memory segments)but they are munging the same data files.  It will not take longto turn the data files into irrecoverable hash --- for just onereason, transaction numbering will diverge between the two sets of
backends.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] Upgrade 7.4 to 8.1 or 8.2?

[John Sidney-Woollett]

Thanks for the link - that was very useful.

John

Alvaro Herrera wrote:

John Sidney-Woollett wrote:
Is there a page that describes the new features/improvements between 
8.1.5 and 8.2? I couldn't find one on the postgres website.


If there aren't *big* performance gains between 8.1.5 and 8.2 then we'll 
go with 8.1.5 and leave the 8.2 upgrade till sometime next summer.


There are a lot of performance improvements.

The release notes are here:

http://momjian.us/main/writings/pgsql/sgml/release-8-2.html



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

Re: [GENERAL] Upgrade 7.4 to 8.1 or 8.2?

[Alvaro Herrera]

John Sidney-Woollett wrote:
> Is there a page that describes the new features/improvements between 
> 8.1.5 and 8.2? I couldn't find one on the postgres website.
> 
> If there aren't *big* performance gains between 8.1.5 and 8.2 then we'll 
> go with 8.1.5 and leave the 8.2 upgrade till sometime next summer.

There are a lot of performance improvements.

The release notes are here:

http://momjian.us/main/writings/pgsql/sgml/release-8-2.html

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(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] Upgrade 7.4 to 8.1 or 8.2?

[Harald Armin Massa]

John,No I cannot risk live data...please read closely: Ron wrot "Can you risk your app on beta software?" ..
There is allways a risk included in basing an application on beta software, as programming interfaces may change.BUT:My experience is: you can trust your data to PostgreSQL. The elephant never forgets. I started developing an application using some obscure FTP-Download of some obscure 
7.x Windows Port. I roled out to pilot users using PostgreSQL 8.0 beta on Windows. That 8.0 BETA was used by salesmen on laptops. And not a single byte was lost by PostreSQL.NO, I do not recommend rolling out with 
8.2beta; your way is perfectly sensible:I guess going with 8.1.5 is what we should do.
Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.

Re: [GENERAL] Upgrade 7.4 to 8.1 or 8.2?

[John Sidney-Woollett]

Is there a page that describes the new features/improvements between 
8.1.5 and 8.2? I couldn't find one on the postgres website.


If there aren't *big* performance gains between 8.1.5 and 8.2 then we'll 
go with 8.1.5 and leave the 8.2 upgrade till sometime next summer.


John

Tom Lane wrote:

John Sidney-Woollett <[EMAIL PROTECTED]> writes:
Are the differences between 8.1.5 and 8.2 significant? Is 8.2 more about 
speed or new features?


8.2 is still all about beta testing.

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


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

  http://archives.postgresql.org/

Re: [GENERAL] Upgrade 7.4 to 8.1 or 8.2?

[John Sidney-Woollett]

No I cannot risk live data...

But I (think I) read that 8.2 was expected to go final in November/December.

So my question was is it worth waiting for 8.2 final or to go with 8.1.5 
now. I guess going with 8.1.5 is what we should do.


Thanks

John

Ron Johnson wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/20/06 10:09, John Sidney-Woollett wrote:

We're looking to upgrade from 7.4 - should we go to 8.1.5 or 8.2?

We have two databases; 7.4.6 and 7.4.11 in a master slave config using
Slony. Both databases use the C locale with UTF-8 encoding on unix.

We've dumped and loaded the data into an 8.1.4 database and have seen no
problems with invalid UTF-8 sequences. So we're fairly happy that we can
upgrade to 8.1.5 pretty easily using Slony.

We're really looking for some extra performance right now.

Are the differences between 8.1.5 and 8.2 significant? Is 8.2 more about
speed or new features?


8.2 is not released yet.  Can you risk your app on beta software?

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

iD8DBQFFOOk3S9HxQb37XmcRAmznAKCUDHP7Vh+RKOr8VLwfi/38ceucqwCfXW2M
J1DNE9Ph7hgyBDWjjJUPWLI=
=g5EN
-END PGP SIGNATURE-

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

   http://archives.postgresql.org/


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

Re: [GENERAL] skip duplicate key error during inserts

[Merlin Moncure]

On 10/20/06, Jean-Christophe Roux <[EMAIL PROTECTED]> wrote:

Hello,
I have a table like this:
create table dummy (value integer primary key);
and I insert a row like this
insert into dummy values(0);
then I want to insert three rows:
insert into dummy values(0);
insert into dummy values(1);
insert into dummy values(2);



none of them will be inserted because the first insert is a primary key
violation. How can I have postgreSQL not mind about the error and proceed to
the next insert. I could send the inserts one at a time but bundling them
speeds up the process.
Thanks


if this is bulk insert,
1. insert into scratch table:
create temp table scratch_foo as select * from foo limit 0;
2. insert into scratch, etc.
3. insert into foo select distinct on * from scratch_foo <-- season to taste

merlin

---(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] why not kill -9 postmaster

[Tom Lane]

Martijn van Oosterhout  writes:
> Well, if you kill -9 the postmaster all the connections stay alive and
> stay processing tuples and writing to disk, except the coordination is
> gone.

The postmaster isn't involved in any critical inter-backend coordination.
If you kill -9 the postmaster *and then kill or wait out all the
backends*, you won't lose data.  This is not a desirable long-term
operating mode, because it cripples autovacuum and some other things,
but it's not dangerous.

The only really serious risk I'm aware of in this scenario is:

1. DBA does "kill -9" postmaster, but some backends are still alive and
processing.

2. DBA tries to start new postmaster, gets message about "shared memory
segment still in use".

3. DBA does "rm postmaster.pid" (this is the step that qualifies him
as an idiot).

4. DBA starts new postmaster.  Since the interlock file is gone, it
starts up without any awareness that there are old backends still alive.

At this point, you have two separate sets of backends that are not
communicating (they're using two different shared memory segments)
but they are munging the same data files.  It will not take long
to turn the data files into irrecoverable hash --- for just one
reason, transaction numbering will diverge between the two sets of
backends.

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] Upgrade 7.4 to 8.1 or 8.2?

[Tom Lane]

John Sidney-Woollett <[EMAIL PROTECTED]> writes:
> Are the differences between 8.1.5 and 8.2 significant? Is 8.2 more about 
> speed or new features?

8.2 is still all about beta testing.

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] why not kill -9 postmaster

[Martijn van Oosterhout]

On Sat, Oct 21, 2006 at 12:20:35AM +0930, Shane Ambler wrote:
> If you kill -9 the postmaster the system can still finish sending 
> changes to disk and close the file but pulling the power cord can stop a 
> write in the middle of a block giving you half new data and half old 
> data in the one file.

Well, if you kill -9 the postmaster all the connections stay alive and
stay processing tuples and writing to disk, except the coordination is
gone. Some queues won't be processed, some signals will be ignored, if
the postmaster pid gets reused you'll have some fun.

In particular, the sinval-queue processing would break, which could
lead to some interesting issues. But I expect any number of issues to
start occurring.

A half-written disk blocks is a solved problem, postgresql will recover
from that without blinking.

> It's all a matter of timing.

Pulling the plug is *way* safer, it's a known quantity. As Tom said,
killing the postmaster needs cleanup, and some people screwup the
cleanup enough to corrupt their own data.

Now: killall -9 postgres (kill the parents, all the clients,
autovacuum, bgwriter, etc) all in one go is much more like a crash. But
that's not what's being discussed here.

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] Upgrade 7.4 to 8.1 or 8.2?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/20/06 10:09, John Sidney-Woollett wrote:
> We're looking to upgrade from 7.4 - should we go to 8.1.5 or 8.2?
> 
> We have two databases; 7.4.6 and 7.4.11 in a master slave config using
> Slony. Both databases use the C locale with UTF-8 encoding on unix.
> 
> We've dumped and loaded the data into an 8.1.4 database and have seen no
> problems with invalid UTF-8 sequences. So we're fairly happy that we can
> upgrade to 8.1.5 pretty easily using Slony.
> 
> We're really looking for some extra performance right now.
> 
> Are the differences between 8.1.5 and 8.2 significant? Is 8.2 more about
> speed or new features?

8.2 is not released yet.  Can you risk your app on beta software?

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

iD8DBQFFOOk3S9HxQb37XmcRAmznAKCUDHP7Vh+RKOr8VLwfi/38ceucqwCfXW2M
J1DNE9Ph7hgyBDWjjJUPWLI=
=g5EN
-END PGP SIGNATURE-

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

   http://archives.postgresql.org/

Re: [GENERAL] why not kill -9 postmaster

[Alvaro Herrera]

Shane Ambler wrote:
> Dawid Kuroczko wrote:
> >On 10/20/06, Shane Ambler <[EMAIL PROTECTED]> wrote:
> 
> >>The one thing worse than kill -9 the postmaster is pulling the power
> >>cord out of the server. Which is what makes UPS's so good.
> >
> >
> >Well, I think that pulling the power cord is much safer than killing -9
> >the postmaster.  If you pull the plug, then during bootup postgresql
> >will just replay every COMMITed transaction, so there won't be any
> >dataloss or downtime.
> 
> If you kill -9 the postmaster the system can still finish sending 
> changes to disk and close the file but pulling the power cord can stop a 
> write in the middle of a block giving you half new data and half old 
> data in the one file.

That case is protected against in the WAL code.  That's what we save
whole page images for.

The only difference between kill -9 postmaster and abrupt shutdown, is
that on the former case there may be backends that continue to run and
commit transactions.  Those will still be WAL-logged though.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

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

[GENERAL] Upgrade 7.4 to 8.1 or 8.2?

[John Sidney-Woollett]

We're looking to upgrade from 7.4 - should we go to 8.1.5 or 8.2?

We have two databases; 7.4.6 and 7.4.11 in a master slave config using 
Slony. Both databases use the C locale with UTF-8 encoding on unix.


We've dumped and loaded the data into an 8.1.4 database and have seen no 
problems with invalid UTF-8 sequences. So we're fairly happy that we can 
upgrade to 8.1.5 pretty easily using Slony.


We're really looking for some extra performance right now.

Are the differences between 8.1.5 and 8.2 significant? Is 8.2 more about 
speed or new features?


John

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

[GENERAL] skip duplicate key error during inserts

[Jean-Christophe Roux]

Hello,I have a table like this:create table dummy (value integer primary key);and I insert a row like thisinsert into dummy values(0);then I want to insert three rows:insert into dummy values(0);insert into dummy values(1);insert into dummy values(2);none of them will be inserted because the first insert is a primary key violation. How can I have postgreSQL not mind about the error and proceed to the next insert. I could send the inserts one at a time but bundling them speeds up the process.Thanks

Re: [GENERAL] why not kill -9 postmaster

[Shane Ambler]

Dawid Kuroczko wrote:

On 10/20/06, Shane Ambler <[EMAIL PROTECTED]> wrote:



The one thing worse than kill -9 the postmaster is pulling the power
cord out of the server. Which is what makes UPS's so good.



Well, I think that pulling the power cord is much safer than killing -9
the postmaster.  If you pull the plug, then during bootup postgresql
will just replay every COMMITed transaction, so there won't be any
dataloss or downtime.


If you kill -9 the postmaster the system can still finish sending 
changes to disk and close the file but pulling the power cord can stop a 
write in the middle of a block giving you half new data and half old 
data in the one file.


It's all a matter of timing.


--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

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

Re: [GENERAL] why not kill -9 postmaster

[Dawid Kuroczko]

On 10/20/06, Shane Ambler <[EMAIL PROTECTED]> wrote:
>> After all, that's what a system crash is, right?>> A system crash is safer in that it won't leave orphaned child
> processes or IPC/synchronization resources around, making it more> comparable to a SIGQUIT than a SIGKILL.>The one thing worse than kill -9 the postmaster is pulling the powercord out of the server. Which is what makes UPS's so good.

 
Well, I think that pulling the power cord is much safer than killing -9
the postmaster.  If you pull the plug, then during bootup postgresql
will just replay every COMMITed transaction, so there won't be any
dataloss or downtime.
 
If you kill -9 postmaster... well, it's messy. ;-)))  I feel safer when
everything goes down at the same time. ;) 
If your server is changing the data file on disk and you pull the powercord, what chance do you expect of reading that data file again?

 
With PostgreSQL? I expect to read all commited transactions. And
those not commited... well, they weren't commited in the first place,
so I won't see them anyway.
 
This is all in assumption that you are running your DB with fsync on,
on a reliable filesystem, and your hardware doesn't lie to you about
fsyncing data (and it's best if you have a battery for controller's cache).
 
Regards,
   Dawid

Re: [GENERAL] c function returning high resolution timestamp

[Ron Peterson]

On Fri, Oct 20, 2006 at 03:32:05PM +0200, Andreas Seltenreich wrote:
> Ron Peterson writes:
> 
> > On Wed, Oct 18, 2006 at 04:43:40PM -0400, Ron Peterson wrote:
> > I'm pretty close, but I'm still not understanding something about
> > PostgreSQL's internal timestamp representation.  If I do 'select
> > now();', I get a return value with microsecond resolution, which would
> > seem to indicate that internally, PostgreSQL is using an INT64 value
> > rather than a float to hold the timestamp.
> 
> Floating point timestamps /do/ have microsecond resolution over a
> limited range:
>
> ,[  ]
> | Microsecond precision is achieved for dates within a few years of
> | 2000-01-01, but the precision degrades for dates further away. When
> | timestamp values are stored as eight-byte integers (a compile-time
> | option), microsecond precision is available over the full range of
> | values.
> `

Ahah!  Pghghtht, I've read that page many times, but never looking for
programming information.  Not a problem with the way the docs are
organized, just a problem with the way my brain is organized.  Thanks
for taking the time to help a slow learner.

Working code is posted here:

http://www.yellowbank.com/code/PostgreSQL/y_uuid/

-- 
Ron Peterson
https://www.yellowbank.com/

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

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

Re: [GENERAL] why not kill -9 postmaster

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Shane Ambler wrote:
>> The one thing worse than kill -9 the postmaster is pulling the power 
>> cord out of the server. Which is what makes UPS's so good.
>> 
>> If your server is changing the data file on disk and you pull the power 
>> cord, what chance do you expect of reading that data file again?

> 1.  That's what we have WAL for.  The only thing that can really kill
> you is the use of non-battery-backed write cache.

The important distinction here is "will you lose data" vs "can you start
a new server without tedious manual intervention" (ipcrm etc).  kill -9
won't lose data, but you may have to clean up after it.  And, as Andreas
already noted, some people have been seen to mess up the manual
intervention part badly enough to cause data loss by themselves.
Personally I think the TIP that's really needed is "never remove
postmaster.pid by hand".

regards, tom lane

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

Re: [professionel] Re: [GENERAL] division by zero error in a request

[Oisin Glynn]

Bernard Grosperrin wrote:

Oisin

SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) /
(sold_parts_amount_dly + sold_labor_amount_dly) from sales where 
(sold_parts_amount_dly + sold_labor_amount_dly)>0

Thanks for your answer.

The real request would be something like this:

SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) - 
cost_amount_dly /
(sold_parts_amount_dly + sold_labor_amount_dly) from sales where 
(sold_parts_amount_dly + sold_labor_amount_dly)>0


My problem is that in fact I want to SUM those amounts, with a GROUP 
BY per location. But by doing so, any location where one row has where 
= 0 is eliminated. So, is there a way to SUM inside a subset returned by


SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) - 
cost_amount_dly /
(sold_parts_amount_dly + sold_labor_amount_dly) from sales where 
(sold_parts_amount_dly + sold_labor_amount_dly)>0




Should I select into a temporary table, then SUM that table?

Thanks,
Bernard


Please always copy the list on your responses as others may be interested.
Something like this should work. I am not sure how inefficient it is.

select location_id, SUM((sold_parts_amount_dly + sold_labor_amount_dly) 
- cost_amount_dly) /
SUM(sold_parts_amount_dly + sold_labor_amount_dly) from sales s1  where 
(select  SUM(sold_parts_amount_dly + sold_labor_amount_dly) from sales  
s2 where s2.location_id = s1.location_id) > 0 group by location_id;




smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] why not kill -9 postmaster

[Ray Stell]

On Fri, Oct 20, 2006 at 10:56:09PM +0930, Shane Ambler wrote:

Someone in the thread mentioned having to clean up shared mem.  I've had
to do this often with oracle:

root# ipcs
-- Shared Memory Segments 
keyshmid  owner  perms  bytes  nattch status
0xe97c83ac 5505024oracle640807403520  10
0x0052f649 3538945postgresql60010461184   2

-- Semaphore Arrays 
keysemid  owner  perms  nsems
0xfb5e028c 25690112   oracle640154
0x0052f649 17629185   postgresq 60017
0x0052f64a 17661954   postgresq 60017
0x0052f64b 17694723   postgresq 60017
0x0052f64c 17727492   postgresq 60017
0x0052f64d 17760261   postgresq 60017
0x0052f64e 17793030   postgresq 60017
0x0052f64f 17825799   postgresq 60017

-- Message Queues 
keymsqid  owner  perms  used-bytes   messages

$ ipcrm shm 2588672
resource(s) deleted

this remove example was not from the above shared mem report.

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

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

Re: [GENERAL] why not kill -9 postmaster

[Harald Armin Massa]

>> If your server is changing the data file on disk and you pull the power
> cord, what chance do you expect of reading that data file again?1.  That's what we have WAL for.  The only thing that can really killyou is the use of non-battery-backed write cache.
Just for information: I had to suffer numerous BOS (blue screen of death) on an W2k3 Server running PostgreSQL 8.0 and 8.1 for Windows.Every time the database restarted without data loss and without operator invention. 
Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.

Re: [GENERAL] c function returning high resolution timestamp

[Andreas Seltenreich]

Ron Peterson writes:

> On Wed, Oct 18, 2006 at 04:43:40PM -0400, Ron Peterson wrote:
> I'm pretty close, but I'm still not understanding something about
> PostgreSQL's internal timestamp representation.  If I do 'select
> now();', I get a return value with microsecond resolution, which would
> seem to indicate that internally, PostgreSQL is using an INT64 value
> rather than a float to hold the timestamp.

Floating point timestamps /do/ have microsecond resolution over a
limited range:

,[  ]
| Microsecond precision is achieved for dates within a few years of
| 2000-01-01, but the precision degrades for dates further away. When
| timestamp values are stored as eight-byte integers (a compile-time
| option), microsecond precision is available over the full range of
| values.
`

> My function below, however, always takes the float path through the
> ifdef.  If I force the int64 path, I just get a garbage timestamp
> which still only has a seconds resolution.

The proper way to enable the int64 path is enabling integer timestamps
during build-time:

,[ ./configure --help|grep time ]
|   --enable-integer-datetimes  enable 64-bit integer date/time support
`

regards,
andreas

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

Re: [GENERAL] why not kill -9 postmaster

[Alvaro Herrera]

Shane Ambler wrote:

> The one thing worse than kill -9 the postmaster is pulling the power 
> cord out of the server. Which is what makes UPS's so good.
> 
> If your server is changing the data file on disk and you pull the power 
> cord, what chance do you expect of reading that data file again?

1.  That's what we have WAL for.  The only thing that can really kill
you is the use of non-battery-backed write cache.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

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

Re: [GENERAL] why not kill -9 postmaster

[Shane Ambler]

Andreas Seltenreich wrote:

Ron Johnson writes:


On 10/20/06 05:27, Andreas Seltenreich wrote:

,[ 
 ]
| It is best not to use SIGKILL to shut down the server. Doing so will
| prevent the server from releasing shared memory and semaphores,
| which may then have to be done manually before a new server can be
| started. Furthermore, SIGKILL kills the postmaster process without
| letting it relay the signal to its subprocesses, so it will be
| necessary to kill the individual subprocesses by hand as well.
`

But it can't be fatal, can it?


While it could be fixed by hand, the list archives tell that it was
fatal enough for some to shoot themselves in their feet.


After all, that's what a system crash is, right?


A system crash is safer in that it won't leave orphaned child
processes or IPC/synchronization resources around, making it more
comparable to a SIGQUIT than a SIGKILL.



The one thing worse than kill -9 the postmaster is pulling the power 
cord out of the server. Which is what makes UPS's so good.


If your server is changing the data file on disk and you pull the power 
cord, what chance do you expect of reading that data file again?


While every attempt is made to make the server as reliable as possible 
and to be able to recover as much as possible when things go wrong, 
abrupt stops (whether from kill -9 or other) at the worst time will make 
you dig out your backup copies or spend hours or days manually fixing 
what is left to get as much data as you can.


If you are testing and developing that probably won't matter, but what 
would it cost you or your company if you lost all the data in your 
database? What about lost productivity during the time spent recovering? 
Is it worth risking all that?


--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

Re: [GENERAL] why not kill -9 postmaster

[Peter Eisentraut]

Am Freitag, 20. Oktober 2006 13:12 schrieb Ron Johnson:
> But it can't be fatal, can it?  After all, that's what a system
> crash is, right?

Perhaps we should add another tip not to crash the system.

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

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

Re: [GENERAL] Link error: LNK2019: unresolved external symbol _pg_detoast_datum

[Taras Kopets]

Hi,Ale Raza wrote:
>> Ok, it means I
can not use MS VC complier/linker, only choice is Mingw or Cygwin. I think you can use MS VC, you can try to find some info from here:
http://www.postgresql.org/docs/8.0/interactive/xfunc-c.htmlRead user comments at the bottom.Ale Raza wrote:
>> Wondering if
you know which process (Postmaster.exe or Postgres.exe) >> I have to attach in
order to debug this type of functions. >> I have more complex functions where I
need debugger.In documentation it is said: "The first time a user-defined function in a particular loadable object file is 
called in a session, the dynamic loader loads that object file into memory so 
that the function can be called", so I think you should try to attach to postgres.exe (I'm not sure).Have a nice day,Taras Kopets

[GENERAL] c function returning high resolution timestamp

[Ron Peterson]

On Wed, Oct 18, 2006 at 04:43:40PM -0400, Ron Peterson wrote:
> On Wed, Oct 18, 2006 at 04:31:57PM -0400, Ron Peterson wrote:
> 
> > I'm having a hard time finding any examples of functions returning
> > timestamps I can study to see how they are handled internally.  I'm sure
> > it's only a line or two of code.
> 
> ...I just found date.c

I'm pretty close, but I'm still not understanding something about
PostgreSQL's internal timestamp representation.  If I do 'select
now();', I get a return value with microsecond resolution, which would
seem to indicate that internally, PostgreSQL is using an INT64 value
rather than a float to hold the timestamp.  My function below, however,
always takes the float path through the ifdef.  If I force the int64
path, I just get a garbage timestamp which still only has a seconds
resolution.

What do I need to do to generate a high resolution timestamp?

TIA.

Ron Peterson
https://www.yellowbank.com/


#include "postgres.h"
#include "fmgr.h"
#include "utils/datetime.h"
#include 
#include 
#include 
#include 

PG_FUNCTION_INFO_V1( y_uuid_time );
Datum
y_uuid_time( PG_FUNCTION_ARGS )
{
if( PG_ARGISNULL(0) ) {
PG_RETURN_NULL();
}
bytea* uuid = PG_GETARG_BYTEA_P(0);

typedef unsigned int uint;

uuid_t uu;
struct timeval tv;
time_t t;
Timestamp ts;
uint epoch_offset;

epoch_offset = (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY;

memcpy( uu, VARDATA( uuid ), 16 );

t = uuid_time( uu, &tv );

#ifdef HAVE_INT64_TIMESTAMP
ts = (tv.tv_sec - epoch_offset) * 100 + tv.tv_usec;
#else
ts = (double)(tv.tv_sec - epoch_offset);
#endif

PG_RETURN_TIMESTAMP( ts );
}



---(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] why not kill -9 postmaster

[Andreas Seltenreich]

Ron Johnson writes:

> On 10/20/06 05:27, Andreas Seltenreich wrote:
>> ,[ 
>> 
>>  ]
>> | It is best not to use SIGKILL to shut down the server. Doing so will
>> | prevent the server from releasing shared memory and semaphores,
>> | which may then have to be done manually before a new server can be
>> | started. Furthermore, SIGKILL kills the postmaster process without
>> | letting it relay the signal to its subprocesses, so it will be
>> | necessary to kill the individual subprocesses by hand as well.
>> `
>
> But it can't be fatal, can it?

While it could be fixed by hand, the list archives tell that it was
fatal enough for some to shoot themselves in their feet.

> After all, that's what a system crash is, right?

A system crash is safer in that it won't leave orphaned child
processes or IPC/synchronization resources around, making it more
comparable to a SIGQUIT than a SIGKILL.

regards,
andreas


pgpSDTv4JvBEC.pgp
Description: PGP signature

[GENERAL] Where is the pg_terminate_backend() function? Way to execute shell cmd

[Uyelik]

Hi,
1. On Postgresql 8.1.4, i can't use pg_terminate_backend(int) function.
How can i use this function? 
2. How can i execute a shell command?
I need to terminate a client connection(some times active, some times
idle one) by a query. I can undertake the risc for it. :)

Regards,
Mustafa Yorukoglu

Re: [GENERAL] why not kill -9 postmaster

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/20/06 05:27, Andreas Seltenreich wrote:
> Harpreet Dhaliwal writes:
> 
>> Its always said that don't kill -9 postmaster.
>> Whats the reason not to do it. Why is it so strictly  prohibited?
> 
> ,[ 
>  
> ]
> | It is best not to use SIGKILL to shut down the server. Doing so will
> | prevent the server from releasing shared memory and semaphores,
> | which may then have to be done manually before a new server can be
> | started. Furthermore, SIGKILL kills the postmaster process without
> | letting it relay the signal to its subprocesses, so it will be
> | necessary to kill the individual subprocesses by hand as well.
> `

But it can't be fatal, can it?  After all, that's what a system
crash is, right?

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

iD8DBQFFOK8XS9HxQb37XmcRAsUMAKCptETkgCvdbhaxyvqhCryYAo3GtgCfUmqt
J41C6cs+rk7+h993Qh0pUMI=
=OJsz
-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] why not kill -9 postmaster

[Andreas Seltenreich]

Harpreet Dhaliwal writes:

> Its always said that don't kill -9 postmaster.
> Whats the reason not to do it. Why is it so strictly  prohibited?

,[ 
 ]
| It is best not to use SIGKILL to shut down the server. Doing so will
| prevent the server from releasing shared memory and semaphores,
| which may then have to be done manually before a new server can be
| started. Furthermore, SIGKILL kills the postmaster process without
| letting it relay the signal to its subprocesses, so it will be
| necessary to kill the individual subprocesses by hand as well.
`

regards,
andreas

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

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

[GENERAL] Performance: fsync vs. commit delay

[Ilja Golshtein]

Hello!

I run an [almost] OLTP system with PG as backend on Windows and on Linux.

The idea is obvious - increase performance as much as possible keeping 
reliability in mind and I'd highly appreciate any hints.

Some questions about performance/reliability trade.

My first idea was to increase commit_delay having fsync on, since I believed 
commit_delay is rather conservative thing. Reading docs I realized it is 
actually not, because it prevents server from immediate writes. It means if I 
disable fsync only OS/hardware crashes are dangerous, while after setting 
nonzero commit_delay PG server crashes are harmful as well.

Is my understanding correct?

What I want is to do write always but do fsync, say, every 100 ms. Is it 
possible to control fsyncs and writes separately, e.g. have write for every 
commit and fsync as often as specified time range?

-- 
Best regards
Ilja Golshtein

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

Re: [GENERAL] plpgsql FOUND bug when SELECT INTO assigns a NULL

[Karl O. Pinc]

On 10/19/2006 10:13:46 PM, Tom Lane wrote:

"Karl O. Pinc" <[EMAIL PROTECTED]> writes:
> I would expect that because a row exists, even
> though the value assigned is NULL, FOUND would
> be TRUE.  Are my expectations wrong?

No, but I think your code is.  Please provide a test case
demonstrating
this behavior.


Thanks for the reply.  I suspect you're right.

After lots of investigation what seems to be happening
is that I'm putting data in in the wrong order

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


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

Re: Can't get ECPG to connect (was Re: [GENERAL] Urgen help required)

[Harpreet Dhaliwal]

Jas, you might need to check the uplink and downlink time of your network router.
Also,check the postgresql.conf file to see various timeouts there. Comment all the timeouts to set them to infinity so that your Unix tools server keeps  trying connecting to the Postgres server. 
Also, check if you closed the client socket in ur postgres database. This might be one of the reasons of the whole fuss.
 
Also, your  "Urgent help required" subject line is more than enough to catch an eye. I don't think it needs any amendment. 
Anyways, do let me know if you need any further help. I'll be glad to assist you with this.
You are working on the side effects of  postgres and its quite obvious that you'll face all such problems. Keep your cools.
 
Regards,
~Harpreet. 
On 10/18/06, Bill Moran <[EMAIL PROTECTED]> wrote:
In response to "J S B" <[EMAIL PROTECTED]>:> Hi,
> I have the following scenario:->> 1. There's a function in the postgres datbase that when executed calls a> shared object (dynamically loaded).> 2. The shared object is a Client (made using socket library) which connects
> to a server (coded again using socket library) hosted on some other machine.> 3. This server when invoked through the postgres function described above> has an ECPG code in it that gets executed after a successful write back to
> the client, which inserts a record in a table in the postgres database.>> I use ECPGdebug in the begening of the server code file to see the state of> affairs with the ECPG code and it always raises -402 sqlcode which means the
> connection attempt to the database did not succeed.>> All firewalls in the datbase server and this machine wherein my socket> server is running are disabled.> Other function calls from this machine to my database machine are working
> fine.>> Also, my postgres function is able to establish connection with the this> outside deamon (that i refer to as server coded using socket library).> Just the ECPG calls from this deamon back to postgres database are not
> successful.>> Any kind of help would be highly appreciated.How about help posting a better question.First off, use a descriptive subject.  Notice how I've improved this foryou.
Secondly, always cut/paste exact error messages.  There are some peoplewho will quickly know exactly what is wrong if they see the whole errormessage.Thirdly, have a look in the PostgreSQL server logs.  If there's nothing
telling there, bump up the authentication/connection debugging inpostgresql.conf and try again.  If you get messages but aren't sure whatthey mean, post them to the list.Fourthly, be more specific.  It would be worthwhile to show the snippit
of code that connects, so people know whether you're using IP sockets areunix domain sockets.Fifthly, are you using unix domain sockets or IP sockets?  And are theappropriate permissions in pg_hba.conf?
--Bill MoranCollaborative Fusion Inc.---(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] why not kill -9 postmaster

[Harpreet Dhaliwal]

Its always said that don't kill -9 postmaster.
Whats the reason not to do it. Why is it so strictly  prohibited?
 
Thanks,
~Harpreet.

Re: [GENERAL] Ghost open transaction

[Uwe C. Schroeder]

do a "ps -ef | grep transact"
and look for "idle in transaction" postmaster processes.
If you're certain that nobody else is running that transaction (i.e. nobody on 
the system or the process with the idle transaction has been sitting there 
for a while and normally the application doesn't have long running 
transactions) then just kill the process in question (don't kill -9 it!)

That will roll the transaction back and close it.

Hope that helps

Uwe



On Friday 20 October 2006 00:04, Naz Gassiep wrote:
> I was performing a bunch of INSERTs into a table, users, that has a
> unique index on username. During the transaction, my internet connection
> dropped. The INSERTs were being done inside a transaction.
>
> Once I had manhandled my DSL router back online, I went back into a
> console to redo the inserts. I found that after I did the first insert,
> it appeared to freeze. I thought that my net had dropped out again, but
> I was able to Ctrl+C the command and rollback and do it again, with the
> same result. The previous connection is obviously still active, locking
> the transaction until the fate of the previous insert with that username
> is known, i.e., the ghost connection rolls back or commits.
>
> How do I determine which connection is the ghost connection, and how do
> I tell PG to kill it? Also, is it an OS setting to determine the timeout
> on open but inactive connections, or a PG setting?
>
> - Naz.
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster

-- 
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 2: Don't 'kill -9' the postmaster

[GENERAL] Ghost open transaction

[Naz Gassiep]

I was performing a bunch of INSERTs into a table, users, that has a 
unique index on username. During the transaction, my internet connection 
dropped. The INSERTs were being done inside a transaction.


Once I had manhandled my DSL router back online, I went back into a 
console to redo the inserts. I found that after I did the first insert, 
it appeared to freeze. I thought that my net had dropped out again, but 
I was able to Ctrl+C the command and rollback and do it again, with the 
same result. The previous connection is obviously still active, locking 
the transaction until the fate of the previous insert with that username 
is known, i.e., the ghost connection rolls back or commits.


How do I determine which connection is the ghost connection, and how do 
I tell PG to kill it? Also, is it an OS setting to determine the timeout 
on open but inactive connections, or a PG setting?


- Naz.

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

[GENERAL] verifying data integrity after migrating for postgres 7.3 to postgres 8.1

[Najib Abi Fadel]

Hi,

i migrated my database from postgres 7.3 to postgres
8.1.

I was wondering if there is a way to verify that data
has been well copied betweeen the 2 versions !

I thought about writing a script that will verify that
we have the same tables and the same number of lines
per table between the 2 databases. 

Has anyone done that before ?

Is there any other way for verification ?

Thanks in advance for any help.

Najib.

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

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