Re: [GENERAL] why postgresql over other RDBMS

[PFC]

On Sat, 02 Jun 2007 00:14:28 +0200, Ron Johnson <[EMAIL PROTECTED]>  
wrote:



On 06/01/07 16:38, PFC wrote:


Will the synchronized seq scan patch be able to do this by  
issuing all  the CREATE INDEX commands at the same time from several  
different database  connections ?

 No, but it could someday.
 Actually I tested, it does it right now, albeit unconsciously (pg  
doesn't do anything to synchronize the scans, but if you launch the  
concurrent connections at the same time and issue all your "create  
index" at the same time, only 1 table scan is needed). Maybe if the  
tables were bigger, it would lose sync between the 3 concurrent scans  
and would end up going slower. That's why I spoke about the  
"synchronized scan" patch.


How much of this, though, is from the OS's disk cache?  Or are Seq Scans  
O_DIRECT and bypass the OS cache?



	Well, the file was larger than disk cache, and I checked in vmstat's  
number of actual bytes read from disks...

Three threads read the table once, One thread reads the table 3 times.

	So it works right now, except it doesn't have (yet) the infrastructure to  
keep the scans synchronized, and psql can't open several connections (yet).


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

Re: [GENERAL] multimaster

[PFC]

Have you looked at raidb?  http://c-jdbc.objectweb.org.  Narrow niche,  
but if it happens to be the one you are in, then it's an option.  I took  
a quick look at the user's page, and both of them were using PostgreSQL.


I just love those Java guys.
The world starts and ends with Java.
	How do you execute a psql script with that thing ? How do you actually  
use any non-java stuff with it ?
	The same features could be implemented in a connection pool like  
pgpool2...




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

Re: [GENERAL] multimaster

[Martijn van Oosterhout]

On Sat, Jun 02, 2007 at 02:27:06AM +0200, Alexander Staubo wrote:
> >What you are basically saying below is... web 2.0 developers such as
> >rails developers have so fundamentally broken the way it is supposed to
> >be done, we should too...
> 
> I don't know if I said that, but I would love to hear how they have
> broken it, and what you propose the solution to be.

I don't know if it's a general problem, but I've been involved in a
using rails and it appears to have it's own way of declaring the
database. It presumes to handle referential integrity and uniqueness in
the application code (!).

Lo and behold, there are now some uniqueness violations and no-one knows
why. There seems to be a general unwillingness to let the database
check this because it's "slow".

My proposal is: let databases do what they're good at: checking
uniqueness, referential integrity and constraints, and let the user
code deal with the actual work.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   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] warm standby server stops doingcheckpointsafterawhile

[Simon Riggs]

On Fri, 2007-06-01 at 23:14 +0200, Frank Wittig wrote:
> Teodor Sigaev schrieb:
> > Hope, attached patch fix that. Pls, test it.
> 
> It still happens.
> The log is full of incomplete split dumps:
> 
> <2007-06-01 23:00:00.001 CEST:%> LOG:  GIN incomplete splits=8
> <2007-06-01 23:00:00.001 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/28020F48; undo 0/0; tli 1; xid 0/36761651; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:00:00.001 CEST:%> LOG:  GIN incomplete split root:696
> l:93996 r:111778 at redo CF/4B669DF0
> <2007-06-01 23:00:00.001 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/28020F48; undo 0/0; tli 1; xid 0/36761651; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:00:00.001 CEST:%> LOG:  GIN incomplete split root:562
> l:4491 r:111780 at redo CF/9FEF39F0
> <2007-06-01 23:00:00.001 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/28020F48; undo 0/0; tli 1; xid 0/36761651; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:00:00.001 CEST:%> LOG:  GIN incomplete split root:200
> l:63015 r:111781 at redo CF/B45AC9B0
> <2007-06-01 23:00:00.001 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/28020F48; undo 0/0; tli 1; xid 0/36761651; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:00:00.001 CEST:%> LOG:  GIN incomplete split root:318
> l:322 r:111782 at redo CF/B9939B58
> <2007-06-01 23:00:00.001 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/28020F48; undo 0/0; tli 1; xid 0/36761651; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:00:00.001 CEST:%> LOG:  GIN incomplete split root:374
> l:1912 r:111783 at redo CF/B9B6CC00
> <2007-06-01 23:00:00.001 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/28020F48; undo 0/0; tli 1; xid 0/36761651; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:00:00.001 CEST:%> LOG:  GIN incomplete split root:1552
> l:1555 r:111784 at redo CF/F1C6D770
> <2007-06-01 23:00:00.001 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/28020F48; undo 0/0; tli 1; xid 0/36761651; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:00:00.001 CEST:%> LOG:  GIN incomplete split root:221
> l:222 r:111785 at redo D0/ED2F6F0
> <2007-06-01 23:00:00.001 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/28020F48; undo 0/0; tli 1; xid 0/36761651; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:00:00.001 CEST:%> LOG:  GIN incomplete split root:1147
> l:111771 r:111786 at redo D0/1DDE64C8
> <2007-06-01 23:00:00.001 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/28020F48; undo 0/0; tli 1; xid 0/36761651; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:00:00.814 CEST:%> LOG:  restored log file
> "000100D00029" from archive
> <2007-06-01 23:00:03.325 CEST:%> LOG:  restored log file
> "000100D0002A" from archive
> <2007-06-01 23:00:06.145 CEST:%> LOG:  restored log file
> "000100D0002B" from archive
> <2007-06-01 23:00:10.996 CEST:%> LOG:  restored log file
> "000100D0002C" from archive
> <2007-06-01 23:00:14.283 CEST:%> LOG:  restored log file
> "000100D0002D" from archive
> <2007-06-01 23:00:17.086 CEST:%> LOG:  restored log file
> "000100D0002E" from archive
> <2007-06-01 23:01:19.587 CEST:%> LOG:  restored log file
> "000100D0002F" from archive
> <2007-06-01 23:01:22.700 CEST:%> LOG:  restored log file
> "000100D00030" from archive
> <2007-06-01 23:01:24.038 CEST:%> LOG:  GIN incomplete splits=9
> <2007-06-01 23:01:24.038 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/30018F68; undo 0/0; tli 1; xid 0/36763650; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:01:24.038 CEST:%> LOG:  GIN incomplete split root:696
> l:93996 r:111778 at redo CF/4B669DF0
> <2007-06-01 23:01:24.038 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/30018F68; undo 0/0; tli 1; xid 0/36763650; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:01:24.038 CEST:%> LOG:  GIN incomplete split root:562
> l:4491 r:111780 at redo CF/9FEF39F0
> <2007-06-01 23:01:24.038 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/30018F68; undo 0/0; tli 1; xid 0/36763650; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:01:24.038 CEST:%> LOG:  GIN incomplete split root:200
> l:63015 r:111781 at redo CF/B45AC9B0
> <2007-06-01 23:01:24.038 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/30018F68; undo 0/0; tli 1; xid 0/36763650; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:01:24.038 CEST:%> LOG:  GIN incomplete split root:318
> l:322 r:111782 at redo CF/B9939B58
> <2007-06-01 23:01:24.038 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/30018F68; undo 0/0; tli 1; xid 0/36763650; oid 243915448; multi 8;
> offset 15; online
> <2007-06-01 23:01:24.038 CEST:%> LOG:  GIN incomplete split root:374
> l:1912 r:111783 at redo CF/B9B6CC00
> <2007-06-01 23:01:24.038 CEST:%> CONTEXT:  xlog redo checkpoint: redo
> D0/30018F68; undo 0/0; tli 1; xid 0/36763650; oid 243915448; multi 8;

Re: [GENERAL] multimaster

[Rodrigo Gonzalez]

Alexander Staubo wrote:

On 6/2/07, Guy Rouillier <[EMAIL PROTECTED]> wrote:

Have you looked at raidb?  http://c-jdbc.objectweb.org.  Narrow niche,
but if it happens to be the one you are in, then it's an option.  I took
a quick look at the user's page, and both of them were using PostgreSQL.


Looked at it briefly. C-JDBC is called Sequoia now. It's probably
pretty good, but it's Java, and using JDBC from Ruby is a bit awkward;
we're not really ready to use JRuby.

Alexander.

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



Check carob, it include and odbc driver to connect to sequoia

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

Re: [GENERAL] multimaster

[Alexander Staubo]

On 6/2/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:

I don't know if it's a general problem, but I've been involved in a
using rails and it appears to have it's own way of declaring the
database. It presumes to handle referential integrity and uniqueness in
the application code (!).


I think you've been misled. True, Rails/ActiveRecord does bear the
signs of having been designed for MySQL/MyISAM, which has neither
transactions nor referential integrity, but this does not mean that
Rails does not support these constructs, or that Rails users don't use
them. I value my data integrity, so all my relations have RI, unique
constraints, null constraints, etc. as in any well-designed schema.

Alexander.

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

[GENERAL] Transactional DDL

[Jasbinder Singh Bali]

Hi,

A few days back, it was commented by someone in the community that Postgres
has
this Transactional DDL feature.
What I understand about Transactional DDL is something like this:

begin
  --ddl 1
  --ddl 2
end;

I believe that if a database supports transactional ddl then ddl1 and ddl2
would commit together as a batch
And
If a Db doesn't support this transactional DDL feature then ddl1 executes
and commits without even caring about ddl2. Right?

~Jas

[GENERAL] Stored Procedures and Functions

[Harpreet Dhaliwal]

Hi,

Is it true that postgres doesn't have a notion of Stored Procedures and
functions is what it has instead?
RDBMS like Sql Server supports both stored procedures and functions.
So I was wondering what is the difference between a Stored Procedure and a
function.

Thanks,
~Harpreet

Re: [GENERAL] Transactional DDL

[Alexander Staubo]

On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:
[snip]

I believe that if a database supports transactional ddl then ddl1 and ddl2
would commit together as a batch
And
If a Db doesn't support this transactional DDL feature then ddl1 executes
and commits without even caring about ddl2. Right?


Exactly right -- Oracle, for example, implicitly commits the
transaction when you execute a DDL statement such as "create table".

Alexander.

---(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] Corruption of files in PostgreSQL

[Tom Lane]

"Paolo Bizzarri" <[EMAIL PROTECTED]> writes:
> On 6/2/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Please provide a reproducible test case ...

> as explained above, the problem seems quite random. So I need to
> understand what we have to check.

In this context "reproducible" means that the failure happens
eventually.  I don't care if the test program only fails once in
thousands of tries --- I just want a complete self-contained example
that produces a failure.  I don't have the time to try to
reverse-engineer a test case from your rather vague description, whereas
I suppose you can make one by stripping down code you've already got.

The sub-text here is that I don't really believe that lo_import and
lo_export in themselves are broken.  There must be some extra factor ---
something else you are doing, or something in your environment ---
contributing to the bug.  Thus, the odds of someone else building a
usable test case from scratch aren't that good, and being able to
reproduce the failure outside your environment is an essential step.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] Transactional DDL

[Jasbinder Singh Bali]

But its said that transactions in any RDBMS follow ACID properties.
So if i put a create table and an Insert statement in the same begin end
block as one single transactioin, won't both create and insert follow acid
property, being in one single trasaction, and either both get committed or
none, talking about oracle lets say

On 6/2/07, Alexander Staubo <[EMAIL PROTECTED]> wrote:


On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:
[snip]
> I believe that if a database supports transactional ddl then ddl1 and
ddl2
> would commit together as a batch
> And
> If a Db doesn't support this transactional DDL feature then ddl1
executes
> and commits without even caring about ddl2. Right?

Exactly right -- Oracle, for example, implicitly commits the
transaction when you execute a DDL statement such as "create table".

Alexander.

Re: [GENERAL] Transactional DDL

[Michael Glaesemann]

On Jun 2, 2007, at 10:12 , Jasbinder Singh Bali wrote:


But its said that transactions in any RDBMS follow ACID properties.
So if i put a create table and an Insert statement in the same  
begin end block as one single transactioin, won't both create and  
insert follow acid property, being in one single trasaction, and  
either both get committed or none, talking about oracle lets say


test=# \d
List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
(2 rows)

test=# begin;
BEGIN
test=# create table foo (a integer);
CREATE TABLE
test=# insert into foo (a) values (1);
INSERT 0 1
test=# commit;
COMMIT
test=# \d
List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
public | foo  | table | postgres
(3 rows)

test=# select * from foo;
a
---
1
(1 row)

test=# begin;
BEGIN
test=# create table bar (a integer);
CREATE TABLE
test=# insert into bar (a) values (1);
INSERT 0 1
test=# select * from bar;
a
---
1
(1 row)

test=# \d
List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
public | bar  | table | postgres
public | foo  | table | postgres
(4 rows)

test=# select 1/0;
ERROR:  division by zero
test=# commit;
ROLLBACK
test=# \d
List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
public | foo  | table | postgres
(3 rows)


Michael Glaesemann
grzm seespotcode net



---(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] Stored Procedures and Functions

[Leif B. Kristensen]

On Saturday 2. June 2007 16:47, Harpreet Dhaliwal wrote:
>Hi,
>
>Is it true that postgres doesn't have a notion of Stored Procedures
> and functions is what it has instead?
>RDBMS like Sql Server supports both stored procedures and functions.
>So I was wondering what is the difference between a Stored Procedure
> and a function.

Pascal has functions and procedures. C has only functions. That doesn't 
say anything about the relative usability of each language. Those are 
just names.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

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

Re: [GENERAL] Transactional DDL

[Harpreet Dhaliwal]

Whats so novel about postgresql here?
This would happen in any RDBMS. right?
You induced divide by zero exception that crashed the whole transaction and
it did not create the table bar?
I can't see any Transactional DDL philosophy here.
Could you please throw some more light on it to point out the transactional
DDL fundamental as
pointed out by Jas?

Thanks,
~Harpreet

On 6/2/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:



On Jun 2, 2007, at 10:12 , Jasbinder Singh Bali wrote:

> But its said that transactions in any RDBMS follow ACID properties.
> So if i put a create table and an Insert statement in the same
> begin end block as one single transactioin, won't both create and
> insert follow acid property, being in one single trasaction, and
> either both get committed or none, talking about oracle lets say

test=# \d
 List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
(2 rows)

test=# begin;
BEGIN
test=# create table foo (a integer);
CREATE TABLE
test=# insert into foo (a) values (1);
INSERT 0 1
test=# commit;
COMMIT
test=# \d
 List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
public | foo  | table | postgres
(3 rows)

test=# select * from foo;
a
---
1
(1 row)

test=# begin;
BEGIN
test=# create table bar (a integer);
CREATE TABLE
test=# insert into bar (a) values (1);
INSERT 0 1
test=# select * from bar;
a
---
1
(1 row)

test=# \d
 List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
public | bar  | table | postgres
public | foo  | table | postgres
(4 rows)

test=# select 1/0;
ERROR:  division by zero
test=# commit;
ROLLBACK
test=# \d
 List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
public | foo  | table | postgres
(3 rows)


Michael Glaesemann
grzm seespotcode net



---(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] Stored Procedures and Functions

[Pavel Stehule]

Hello



Is it true that postgres doesn't have a notion of Stored Procedures and
functions is what it has instead?
RDBMS like Sql Server supports both stored procedures and functions.
So I was wondering what is the difference between a Stored Procedure and a
function.



It's true. PostgreSQL knows only functions. Difference between
procedures and function are in calling context a possibilities, and
depends on database system. Functions are called from SELECT
statements, procedures are called via statement CALL. On some systems
functions has some limits (it's not true for PostgreSQL). On some
systems procedures allow transaction con troll, returning
multirecordset (unbind selects), and more.

PostgreSQL implementation is more practical than elegant. PostgreSQL
functions hasn't classic limits, but some design points are little bit
dirty. When you start to play with OUT variables, you will see.

For beginner, difference between function and procedure is less than
small in PostgreSQL. There are two families of stored procedures:
Oracle and Microsoft. Look to their documentation.

Regards
Pavel Stehule

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

Re: [GENERAL] Stored Procedures and Functions

[PFC]

MySQL supports procedures and functions.

Functions can return results but cannot update the database.
Procedures can update the database but cannot return results.

However :
- a function can call a procedure that updates the database !
- a procedure can return result through OUT parameters !

	It's a friggin mess. In pgsql, if you want, a STABLE or IMMUTABLE  
procedure is a function since it is repeatable : it will always return the  
same results with the same parameters, and has no side-effects. This is  
the definition of a function.


	It is better not to draw useless lines in the ground with huge "don't  
walk over this line" stickers. People will always find a way around.  
Better offer features that users need.




Is it true that postgres doesn't have a notion of Stored Procedures
and functions is what it has instead?
RDBMS like Sql Server supports both stored procedures and functions.
So I was wondering what is the difference between a Stored Procedure
and a function.


Pascal has functions and procedures. C has only functions. That doesn't
say anything about the relative usability of each language. Those are
just names.




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

Re: [GENERAL] Transactional DDL

[Dawid Kuroczko]

On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:

But its said that transactions in any RDBMS follow ACID properties.
So if i put a create table and an Insert statement in the same begin end
block as one single transactioin, won't both create and insert follow acid
property, being in one single trasaction, and either both get committed or
none, talking about oracle lets say


Actually, Oracle inserts implicit COMMIT after each DDL.

So, if you have:

BEGIN;
INSERT INTO foo (bar) VALUES (1);
CREATE INDEX foo_bar ON foo (bar);
-- Here Oracle will insert implicit COMMIT, thus your foo table will
have value 1 commited.
-- And here Oracle will BEGIN a new trasaction.
INSERT INTO foo (bar) VALUES (2);
ROLLBACK;
-- And you will rollback the insert of value 2.  Value 1 remains in the table,
-- because it is already committed.

  Regards,
  Dawid

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

  http://archives.postgresql.org/

Re: [GENERAL] There can be only one! How to avoid the "highlander-problem".

[Erwin Brandstetter]

On Jun 2, 2:43 am, [EMAIL PROTECTED] (Erwin Brandstetter) wrote:
>   raise warning '%', kings;

And remove this line of debug code.

/Erwin


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

Re: [GENERAL] Transactional DDL

[Harpreet Dhaliwal]

So, while writing any technical document, would it be wrong to mention
stored procedures in postgresql?
what is the general convention?

On 6/2/07, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:


On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:
> But its said that transactions in any RDBMS follow ACID properties.
> So if i put a create table and an Insert statement in the same begin end
> block as one single transactioin, won't both create and insert follow
acid
> property, being in one single trasaction, and either both get committed
or
> none, talking about oracle lets say

Actually, Oracle inserts implicit COMMIT after each DDL.

So, if you have:

BEGIN;
INSERT INTO foo (bar) VALUES (1);
CREATE INDEX foo_bar ON foo (bar);
-- Here Oracle will insert implicit COMMIT, thus your foo table will
have value 1 commited.
-- And here Oracle will BEGIN a new trasaction.
INSERT INTO foo (bar) VALUES (2);
ROLLBACK;
-- And you will rollback the insert of value 2.  Value 1 remains in the
table,
-- because it is already committed.

   Regards,
   Dawid

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

   http://archives.postgresql.org/

Re: [GENERAL] Transactional DDL

[Michael Glaesemann]

On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:


Whats so novel about postgresql here?
This would happen in any RDBMS. right?
You induced divide by zero exception that crashed the whole  
transaction and it did not create the table bar?


[Please don't top-post. It makes the discussion hard to follow.]

I used the divide by zero to raise an error to show that both the  
CREATE TABLE and the INSERT were rolled back when the transaction  
failed. If there's another definition of transactional DDL, I'd like  
to know what it is.


Michael Glaesemann
grzm seespotcode net



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

Re: [GENERAL] query log corrupted-looking entries

[George Pavlov]

From: Tom Lane [mailto:[EMAIL PROTECTED] 
> "George Pavlov" <[EMAIL PROTECTED]> writes:
> > ... Also redirect_stderr = on.
> 
> Hm.  Well, that's the bit that ought to get you into the PIPE_BUF
> exception.  There's been some speculation that a change like the
> attached would help.  I've found that it makes no difference with
> my libc, but maybe yours is different --- want to try it?

I will. I may need some time though, since I first need to find a way to
reproduce the problem reliably on my test environments and right now I
cannot seem to. I have seen the problem mostly under production loads
(also under certain kinds of stress-testing, but I forget exactly which
kind...)

In the meantime I went and looked at the logs in more detail and the
corrupted entries seem much more prevalent than what I originally
thought. Apart from the ones pgfouine complains about there are many
more such lines. For example out of a (average-load) day's log file with
17+ million lines pgfouine complains about 8 lines, but there are in
fact 1400+ lines with these kinds of entries.

George

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

Re: One last Slony question (was Re: [GENERAL] Slightly OT.)

[Andrew Sullivan]

On Fri, Jun 01, 2007 at 06:15:40PM -0500, Ron Johnson wrote:
> 
> Since DDL is infrequent, is that bottleneck an acceptable trade-off?

I don't know.  We'd have to do the analysis.  But it could be a
problem.  Look at it this way: if you have a replica that is, for
isntance, _always_ 30 minutes behind, as a sort of poor-person's
fast-recovery PITR, then you lose that functionality if you have to
perform DDL on the replica at the same time as on the origin, because
you have to catch up first.  

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler

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

Re: [GENERAL] Transactional DDL

[Jasbinder Singh Bali]

On 6/2/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:



On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:

> Whats so novel about postgresql here?
> This would happen in any RDBMS. right?
> You induced divide by zero exception that crashed the whole
> transaction and it did not create the table bar?

[Please don't top-post. It makes the discussion hard to follow.]

I used the divide by zero to raise an error to show that both the
CREATE TABLE and the INSERT were rolled back when the transaction
failed. If there's another definition of transactional DDL, I'd like
to know what it is.

Michael Glaesemann
grzm seespotcode net



This is what happens in every RDBMS. Whats so special about postgres then?

Re: [GENERAL] Transactional DDL

[Harpreet Dhaliwal]

On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:




On 6/2/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
>
>
> On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:
>
> > Whats so novel about postgresql here?
> > This would happen in any RDBMS. right?
> > You induced divide by zero exception that crashed the whole
> > transaction and it did not create the table bar?
>
> [Please don't top-post. It makes the discussion hard to follow.]
>
> I used the divide by zero to raise an error to show that both the
> CREATE TABLE and the INSERT were rolled back when the transaction
> failed. If there's another definition of transactional DDL, I'd like
> to know what it is.
>
> Michael Glaesemann
> grzm seespotcode net


This is what happens in every RDBMS. Whats so special about postgres then?






Exactly. this seems like proving the ACIC property of a database thats true
for every RDBMS.
Whats so different in postgresql then?

Re: [GENERAL] Slightly OT.

[Andrew Sullivan]

On Sat, Jun 02, 2007 at 01:30:53AM +0200, Alexander Staubo wrote:

> There needs to be a point of synchronization when a DDL transaction
> appears that blocks further write transactions from running. As far as
> I can tell, the slaves themselves can continue to receive pending
> events, but perhaps not.

In order to do it automatically, you have to lock everyone, get all
the events through, and then perform the DDL, and then come out of
lock.  Otherwise, what happens when you do DROP COLUMN?  If it goes
through ahead of data that ought to go into that column, you have
just broken your cluster.  I suppose you could figure out a way to
work around this, but pretty soon you are building an artificial
intelligence expert system with event-predicting capabilities.  Such
systems are not well known for their simplicity and ease of
maintenance.

> Last I checked, nobody was actually terribly *happy* about having to
> pipe schema changes through slonik. 

Nobody would suggest it's the friendliest arrangement.  But this is a
field where the details really count, and therefore proposals to make
it more friendly have to account for how that friendliness in a lot
of cases doesn't lead to complete breakage in others.  (I had to be
exposed to the multimaster MS SQL stuff, years ago, and I have to say
that it was great when it worked; but when things went south, boy did
your life suck.  Whether it is better now, I don't know.)

A
-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Unfortunately reformatting the Internet is a little more painful 
than reformatting your hard drive when it gets out of whack.
--Scott Morris

---(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] Transactional DDL

[Ron Johnson]

You were politely asked not to top-post.

On 06/02/07 11:46, Harpreet Dhaliwal wrote:

So, while writing any technical document, would it be wrong to mention
stored procedures in postgresql?
what is the general convention?


Did I miss something?  What does "stored procedures" have to do with 
 "Transactional DDL"?



On 6/2/07, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:


On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:
> But its said that transactions in any RDBMS follow ACID properties.
> So if i put a create table and an Insert statement in the same begin 
end

> block as one single transactioin, won't both create and insert follow
acid
> property, being in one single trasaction, and either both get committed
or
> none, talking about oracle lets say

Actually, Oracle inserts implicit COMMIT after each DDL.

So, if you have:

BEGIN;
INSERT INTO foo (bar) VALUES (1);
CREATE INDEX foo_bar ON foo (bar);
-- Here Oracle will insert implicit COMMIT, thus your foo table will
have value 1 commited.
-- And here Oracle will BEGIN a new trasaction.
INSERT INTO foo (bar) VALUES (2);
ROLLBACK;
-- And you will rollback the insert of value 2.  Value 1 remains in the
table,
-- because it is already committed.

   Regards,
   Dawid


--
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!


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

Re: [GENERAL] Transactional DDL

[Russ Brown]

Harpreet Dhaliwal wrote:
> 
> 
> On 6/2/07, *Jasbinder Singh Bali* <[EMAIL PROTECTED]
> > wrote:
> 
> 
> 
> On 6/2/07, *Michael Glaesemann* < [EMAIL PROTECTED]
> > wrote:
> 
> 
> On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:
> 
> > Whats so novel about postgresql here?
> > This would happen in any RDBMS. right?
> > You induced divide by zero exception that crashed the whole
> > transaction and it did not create the table bar?
> 
> [Please don't top-post. It makes the discussion hard to follow.]
> 
> I used the divide by zero to raise an error to show that both the
> CREATE TABLE and the INSERT were rolled back when the transaction
> failed. If there's another definition of transactional DDL, I'd like
> to know what it is.
> 
> Michael Glaesemann
> grzm seespotcode net
> 
> 
> This is what happens in every RDBMS. 

No, it doesn't

> Whats so special about postgres
> then?
>  
> 
> 
> 
> Exactly. this seems like proving the ACIC property of a database thats
> true for every RDBMS.
> Whats so different in postgresql then?

Try doing the same test in MySQL (using InnoDB so you get a supposedly
ACID compliant table type).

Or even in Oracle.

You'll find that the table create gets committed *implicitly*, and the
rollback will only rollback the insert, not the table create.

The point is that most RDBMS systems treat DDL a little differently and
force transaction commit when they are executed. Postgres does not.



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

Re: [GENERAL] Transactional DDL

[Ron Johnson]

On 06/02/07 13:35, Jasbinder Singh Bali wrote:

On 6/2/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:



On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:

> Whats so novel about postgresql here?
> This would happen in any RDBMS. right?
> You induced divide by zero exception that crashed the whole
> transaction and it did not create the table bar?

[Please don't top-post. It makes the discussion hard to follow.]

I used the divide by zero to raise an error to show that both the
CREATE TABLE and the INSERT were rolled back when the transaction
failed. If there's another definition of transactional DDL, I'd like
to know what it is.

Michael Glaesemann
grzm seespotcode net



This is what happens in every RDBMS. Whats so special about postgres then?


But it's NOT what happens in every RDBMS.  Oracle implicitly 
executes a COMMIT after every DDL statement.


--
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!


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

Re: [GENERAL] Corruption of files in PostgreSQL

[Paolo Bizzarri]

On 6/2/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Paolo Bizzarri" <[EMAIL PROTECTED]> writes:
> On 6/2/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Please provide a reproducible test case ...

> as explained above, the problem seems quite random. So I need to
> understand what we have to check.

In this context "reproducible" means that the failure happens
eventually.  I don't care if the test program only fails once in
thousands of tries --- I just want a complete self-contained example
that produces a failure.


As said above, our application is rather complex and involves several
different pieces of software, including Zope, OpenOffice both as
server and client, and PostgreSQL. We are absolutely NOT sure that the
problem is inside PostgreSQL.

What we are trying to understand is, first and foremost, if there are
known cases under which PostgreSQL can truncate a file.


I don't have the time to try to
reverse-engineer a test case from your rather vague description, whereas
I suppose you can make one by stripping down code you've already got.


I was not asking for a reverse engineering of a test case. I will try
to provide an example, but the problem is, without knowing what to
see, that I could omit fundamental details.


The sub-text here is that I don't really believe that lo_import and
lo_export in themselves are broken.  There must be some extra factor ---
something else you are doing, or something in your environment ---
contributing to the bug.


I certainly agree with you. I was asking what to see and what to check.


Thus, the odds of someone else building a
usable test case from scratch aren't that good, and being able to
reproduce the failure outside your environment is an essential step.


I agree with you. I was not hoping for this. At the same time, I was
asking an help for what to see, so that I can reproduce a test case.

As an alternate, I can suggest to download and install PAFlow, but I
understand it is a rather large application

Best regards.

Paolo Bizzarri
Icube S.r.l.

---(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] Transactional DDL

[PFC]

This is what happens in every RDBMS. Whats so special about postgres  
then?


mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE ble ( id INTEGER ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO ble VALUES (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM ble;
+--+
| id   |
+--+
|1 |
|2 |
|3 |
+--+
3 rows in set (0.00 sec)


psql=> BEGIN;
BEGIN
psql=> CREATE TABLE ble ( id INTEGER );
CREATE TABLE
psql=> INSERT INTO ble VALUES (1),(2),(3);
INSERT 0 3
psql=> ROLLBACK;
ROLLBACK
psql=> SELECT * FROM ble;
ERREUR:  la relation «ble» n'existe pas

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

Re: [GENERAL] Transactional DDL

[Tom Lane]

Russ Brown <[EMAIL PROTECTED]> writes:
> Harpreet Dhaliwal wrote:
>> Whats so different in postgresql then?

> Try doing the same test in MySQL (using InnoDB so you get a supposedly
> ACID compliant table type).

> Or even in Oracle.

Examples (using mysql 5.0.40, reasonably current):

mysql> create table t1 (f1 int) engine = innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+--+
| f1   |
+--+
|1 | 
+--+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+--+
| f1   |
+--+
|1 | 
|2 | 
+--+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+--+
| f1   |
+--+
|1 | 
+--+
1 row in set (0.00 sec)

[ okay, so we can roll back an INSERT properly ]

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

[ oops, DROP TABLE isn't transactional ]

mysql> create table t1 (f1 int) engine = innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> create table t2 (f2 int) engine = innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

[ so CREATE TABLE isn't transactional, and what's more, now
  the INSERT wasn't either: ]

mysql> select * from t1;
+--+
| f1   |
+--+
|1 | 
|2 | 
+--+
2 rows in set (0.00 sec)

So it appears that mysql works just like Oracle on this point:
a DDL operation forces an immediate COMMIT.

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] Transactional DDL

[Leif B. Kristensen]

On Saturday 2. June 2007 20:39, Ron Johnson wrote:
>You were politely asked not to top-post.
>
>On 06/02/07 11:46, Harpreet Dhaliwal wrote:
>> So, while writing any technical document, would it be wrong to
>> mention stored procedures in postgresql?
>> what is the general convention?
>
>Did I miss something?  What does "stored procedures" have to do with
>  "Transactional DDL"?

I believe that he posted this in reply to the "Stored procedures and 
functions" thread. It kind of fits in there.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

---(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] Corruption of files in PostgreSQL

[Michael Nolan]

On 6/2/07, Paolo Bizzarri <[EMAIL PROTECTED]> wrote:



What we are trying to understand is, first and foremost, if there are
known cases under which PostgreSQL can truncate a file.




I think it's somewhat more likely that whatever is sending the file to PG is
the cause, either in how it handles the file or due to communications
issues.

This sounds similar to a problem I experienced with an application I wrote
that takes files extracted from email (using MHonarc) and stores them in a
PG database so that I can render them using a web browser later on.  I wound
up having to store the files in BASE64 encoding to keep them from getting
corrupted.
--
Mike Nolan

Re: [GENERAL] why postgresql over other RDBMS

[Jeremy Harris]

On 06/01/07 11:22, Bruce Momjian wrote:

> PFC wrote:

>> On Thu, 31 May 2007 22:20:09 +0200, Vivek Khera <[EMAIL PROTECTED]> wrote:
>>

>>> On May 25, 2007, at 5:28 PM, Tom Lane wrote:
>>>

 That's true at the level of DDL operations, but AFAIK we could
 parallelize table-loading and index-creation steps pretty effectively
 --- and that's where all the time goes.
>>> I would be happy with parallel builds of the indexes of a given table.   
>>> That way you have just one scan of the whole table to build all its  
>>> indexes.
>> 	Will the synchronized seq scan patch be able to do this by issuing all  
>> the CREATE INDEX commands at the same time from several different database  
>> connections ?
> 
> No, but it could someday.


Would it be possible to track stats sufficient for a cost/benefit based
automatic recreate of all indices on a table whenever a full-table-scan
occurred, whether due to a commanded index rebuild or not?

Cheers,
  Jeremy Harris

---(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] Transactional DDL

[Harpreet Dhaliwal]

my bad.. i replied to that in a wrong thread. sorry

On 6/2/07, Leif B. Kristensen <[EMAIL PROTECTED]> wrote:


On Saturday 2. June 2007 20:39, Ron Johnson wrote:
>You were politely asked not to top-post.
>
>On 06/02/07 11:46, Harpreet Dhaliwal wrote:
>> So, while writing any technical document, would it be wrong to
>> mention stored procedures in postgresql?
>> what is the general convention?
>
>Did I miss something?  What does "stored procedures" have to do with
>  "Transactional DDL"?

I believe that he posted this in reply to the "Stored procedures and
functions" thread. It kind of fits in there.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

---(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] Need a wee bit more info on PostgreSQL's SSL security options

[Andreas]

Hi,

I've got it so far:
Server-OS: Debian 3.1 sarge
PostgreSQL: Debian's binary PG 8.1.8 (still the most recent version
available)

Following a tutorial (actually for OpenVPN as I didn't find any for PG
that goes beyond what is found in the main docu) I created a CA, server
and client certificate, updated postgresql.conf and pg_hba.conf, did a
restart of PG and connected from a windows box with pgAdmin.
NICE :)

Now as far as I see, even though I have my postgresql.crt+key in place,
I still have to provide username and password, right?

The server rejects my connection attempt if I move postgresql.crt+key
away. Thats to be expected.
Can I further check the security of the server? The aim will be to have
the port open to the Internet.

How can I check that PG accepts only keys produced by my CA?

What would be the correct  "Common Name"  of a client?

I read that the client can maintain a file  root.crt  to check the
identity of the db-server.
Is this the  root.crt  that sits in PG's data-directory or is it the
server.crt  ?

In the documentation there is a certificate-revocation-list-file mentioned.
I suspect this is to revoke a formerly granted key that got lost or is
owned by a person who shouldn't be allowed to access the dbms anymore.
How is this CRL file set up?


Is there a documentation, that covers those matters more deeply than
chapter 16.8 and 20.1 of PG's main documentation?
Especially the whole client-side topic is rather thin for a newbie.


Regards
Andreas



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

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

Re: [GENERAL] Transactional DDL

[Jaime Casanova]

On 6/2/07, PFC <[EMAIL PROTECTED]> wrote:


>> This is what happens in every RDBMS. Whats so special about postgres
>> then?

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE ble ( id INTEGER ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO ble VALUES (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM ble;
+--+
| id   |
+--+
|1 |
|2 |
|3 |
+--+
3 rows in set (0.00 sec)




Tom's example seems to show that mysql inserts a commit immidiatelly
after a DDL but this one example shows the thing is worse than that.
if that is the case this 3 rows should have been gone with the
rollback.

--
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 5: don't forget to increase your free space map settings

Re: [GENERAL] Transactional DDL

[Jaime Casanova]

> On 6/2/07, *Jasbinder Singh Bali* <[EMAIL PROTECTED]
> > wrote:
>
> On 6/2/07, *Michael Glaesemann* < [EMAIL PROTECTED]
> > wrote:
>
> On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:
>
> > Whats so novel about postgresql here?
> > This would happen in any RDBMS. right?
> > You induced divide by zero exception that crashed the whole
> > transaction and it did not create the table bar?
>

No, it doesn't



then informix is better than oracle in this point. last time i try
this on informix it did the right thing...

sadly enough, i don't have an informix database at hand to confirm if
my memory has no corrupted indexes ;)

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

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

Re: [GENERAL] High-availability

[Alexander Staubo]

On 6/3/07, Madison Kelly <[EMAIL PROTECTED]> wrote:

> Slony is indeed intended for near-real-time replication; it's
> asynchronous, so slaves always lag behind the master. The amount of
> discrepancy depends on a bunch of factors -- individual node
> performance, network performance, and system load.

That was *exactly* the kind of link I was trying to find.


You're welcome.

As a side-note, I sat up pgpool-II today, and was pleasantly surprised
about how easy it all was; within two minutes I had two databases in
perfect sync on my laptop. It has limitations (such as in its handling
of sequences), but compared to Slony it's like a breath of fresh
mountain air.

Pgpool-II also supports table partitioning, where you define each
database to have a subset of the data. Pgpool-II then intercepts every
SQL statement and routes it to the correct server. It doesn't work
with referential integrity, I think, which is a major limitation, but
it's the nature of the beast.

Alexander.

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

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

Re: [GENERAL] High-availability

[Alexander Staubo]

On 6/3/07, Alexander Staubo <[EMAIL PROTECTED]> wrote:

As a side-note, I sat up pgpool-II today, and was pleasantly surprised
about how easy it all was; within two minutes I had two databases in
perfect sync on my laptop. It has limitations (such as in its handling
of sequences), but compared to Slony it's like a breath of fresh
mountain air.


Err, the setup is, I mean. Once you have Slony up and running, it's
pretty smooth.

Alexander.

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

Re: [GENERAL] Transactional DDL

[Tom Lane]

"Jaime Casanova" <[EMAIL PROTECTED]> writes:
> Tom's example seems to show that mysql inserts a commit immidiatelly
> after a DDL but this one example shows the thing is worse than that.

Actually, I think their behavior is just "DDL issues a COMMIT", so that
after that you are out of the transaction and the INSERT commits
immediately.  Some experimentation shows that mysql doesn't issue a
warning for rollback-outside-a-transaction, so the lack of any complaint
at the rollback step is just standard mysql-ism.

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] High-availability

[Madison Kelly]

Alexander Staubo wrote:

On 6/1/07, Madison Kelly <[EMAIL PROTECTED]> wrote:

   After realizing that 'clustering' in the PgSQL docs means multiple
DBs behind one server, and NOT multple machines, I am back at square
one, feeling somewhat the fool. :P


I remember being similarly disappointed in this rampant co-opting of
the word "cluster" back in 7.4 or so. :) A gaggle of geese, a murder
of crows, a cluster of databases, I guess.


   Can anyone point me to docs/websites that discuss options on
replicating in (as close as possible to) realtime? Ideally with load
balancing while both/all servers are up, and failover/resyncing when a
member fails and is restored.


The PostgreSQL documentation gives a pretty good overview of the options:

 http://www.postgresql.org/docs/8.2/interactive/high-availability.html

That said, there is to my knowledge no single, integrated product that
will do all you ask. None are capable of anything near real-time,
automatic failover tends to be left as an exercise for the reader, and
there is a lot of work to get it up and running, and requires
particular care in maintenance and monitoring once it's up.

There are several commercial (Mammoth Replicator comes to mind) and
several open-source projects. Among the open-source ones (Slony-I,
pgpool, PGCluster), I believe Slony-I is the most mature. There are a
few in-progress attempts (pgpool-II, PGCluster 2, PostgreSQL-R) that
are not ready for prime time yet; of these, I believe pgpool-II is the
most promising.

As mentioned in a different thread today, work is being done to
implement WAL-based master-slave replication, which I think should
prove more scalable and more transparent than the current third-party
products:

 http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php


   I've looked at slony, but it looks more like a way to push occasional
copies to slaves, and isn't meant to be real time. Am I wrong by chance?


Slony is indeed intended for near-real-time replication; it's
asynchronous, so slaves always lag behind the master. The amount of
discrepancy depends on a bunch of factors -- individual node
performance, network performance, and system load.

Alexander.


That was *exactly* the kind of link I was trying to find.

Thank you!

Madi

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

Re: [GENERAL] multimaster

[Guy Rouillier]

PFC wrote:


Have you looked at raidb?  http://c-jdbc.objectweb.org.  Narrow niche, 
but if it happens to be the one you are in, then it's an option.  I 
took a quick look at the user's page, and both of them were using 
PostgreSQL.


I just love those Java guys.
The world starts and ends with Java.
How do you execute a psql script with that thing ? How do you 
actually use any non-java stuff with it ?


That's why I said "Narrow niche, but if it happens to be the one you're 
in ...".  I do find your response rather peculiar.  The objectweb guys 
saw a need and filled it in the domain they work in - Java.  Should the 
Java folks complain because Perl scripts on CPAN are not accessible from 
Java?


--
Guy Rouillier

---(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] PITR Base Backup on an idle 8.1 server

[Greg Smith]

On Fri, 1 Jun 2007, Marco Colombo wrote:

If you need *both* a "full backup" *and* PITR, just add a real cp to the 
archive_command above. The important part is to return failure during the 
backup process, I think.


You seem to have worked out a way for your application to do a base backup 
in a fashion that you're happy with.  The way you're grabbing files 
directly from the xlog directory only works because your commit workload 
is so trivial that you can get away with it, and because you haven't then 
tried to apply future archive logs.  In the general case, circumventing 
the archiving when the backup is going on won't guarantee everything is 
ordered just right for PITR to work correctly.  I consider what you're 
doing a bad idea that you happen to be comfortable with the ramifications 
of, and given the circumstances I understand how you have ended up with 
that solution.


I would highly recommend you consider switching at some point to the 
solution Simon threw out:



create table xlog_switch as
select '0123456789ABCDE' from generate_series(1,100);
drop table xlog_switch;


as the best way to solve this problem (thanks, Simon; that's what I was 
looking for but didn't quite have figured out yet).  With that as a 
working way to force a segment change on an idle server without actually 
impacting the data, you should reconsider doing your PITR backup 
properly--where you never touch anything in the xlog directory and instead 
only work with what the archive_command is told.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---(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] Turning on logging

[Owen Hartnett]

I know this is in the docs somewhere, and it's probably staring me in 
the face, but I haven't been able to find it:


I'm running 8.2.4 through npgsql - how do I log:

1) connections to the database

2) updates, deletes, adds


Is this set in the ./configuration?  Or in the startup command line? 
I'm running on Mac OS X server using the Ray Aspeitia/David Wheeler 
startup script with log rotation turned on (using 
/usr/sbin/rotatelogs the Apache log rotation utility).


Thanks for the help!

-Owen

---(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] Turning on logging

[Pavel Stehule]

2007/6/3, Owen Hartnett <[EMAIL PROTECTED]>:


I know this is in the docs somewhere, and it's probably staring me in
the face, but I haven't been able to find it:

I'm running 8.2.4 through npgsql - how do I log:

1) connections to the database

2) updates, deletes, adds




Hello,

look postgresql.conf in your data directory and set

log_connection = on
log_statement = 'mod'

Regards
Pavel

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