[GENERAL] Re: Is there a way to ask PostgreSQL for the name of the computer it's running on?

[aasat]

CREATE OR REPLACE FUNCTION get_hostname()
  RETURNS text AS
$BODY$use Sys::Hostname;
return hostname;$BODY$
  LANGUAGE plperlu VOLATILE;


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Is-there-a-way-to-ask-PostgreSQL-for-the-name-of-the-computer-it-s-running-on-tp5712539p5713452.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Simon Riggs]

On 19 June 2012 22:07, Tom Lane  wrote:
> Rafal Pietrak  writes:
>> The point is, that SQL syntax errors are so obviusly different from
>> execution errors, that noting this distinction should not raise any
>> ambiguity.
>
> I beg to disagree.  Typos can manifest themselves as execution errors
> just as well as syntax errors.

The arguments for the current behaviour are clear and rational. I see
no challenge possible on that basis.

However, PostgreSQL is one of the only databases to behave in this
way. This causes some database applications to have subtle problems
when we migrate/port them to work with us. Some, though few, programs
actually rely on run-time errors in order to execute correctly. I
don't condone or encourage that but I do recognise that there is
substantial legacy code out there, and much of that needs to run on
multiple DBMS.

So it would be useful to have a non-default option of statement-level
abort for those cases, as an ease of use feature.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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

Re: [GENERAL] How to get no. of commits/rollbacks by application on the database?

[Craig Ringer]

On 06/19/2012 09:08 PM, Raghavendra wrote:

> How can we figure out no. of commit/rollback happend on the database
> by application ?
>

If am a DBA, how to know exact application or user commits/rollbacks
happened on the database without enabling logs.


This sounds like one of those "what are you trying to achieve" 
questions, where you need to step back and ask why you're trying to 
collect that info and what you want it for.


First, how do you define "application"? Any client that connects to a 
given database? Any client that sets the application_name GUC to a 
particular value? Any client from a given host? etc. "Application" can 
mean a lot of different things. It sounds like you might simply mean 
work done on a connection that isn't internal to the database system's 
bookkeeping, which is a bit easier, but I'm not sure.


Second, why? What does the transaction count tell you? How will you 
account for work done by PgAgent (if used), via dblink, etc?


I guess I'm unsure what you're trying to accomplish.

--
Craig Ringer

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Edson Richter]

Em 19/06/2012 22:26, Scott Marlowe escreveu:

On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter  wrote:

According to documentation,

"TRUNCATE is transaction-safe with respect to the data in the tables: the
truncation will be safely rolled back if the surrounding transaction does
not commit."

You will find this description at following page:

http://www.postgresql.org/docs/9.0/static/sql-truncate.html

So, when you have the "syntax error" on second line, then transaction is
rolled back (cannot proceed: and that's why Syntax Errors should be treated
as any other error) and your data is safe.

Yes but the discussion was that the syntax error SHOULDN'T cause a
roll back, and I was giving an example of when a transaction should
have rolled back but wouldn't have if syntax errors didn't cause
rollback.

In a different vein, the issue of "interactive" versus "scripted" is
something I don't want to take chances on getting wrong.  If I'm in
the psql terminal and type \i /tmp/somesqlile.sql is that interactive
or scripted?  How can psql know?  Should it know?  Can I trust it to
make the right decision of interactive versus scripted each time?

I generally put more than two lines of sql in a text file, edit it,
and throw at begin; on it.  run it with \i and then commit or rollback
as needed.  It documents what you did so you can check it in
somewhere, and makes it repeatable.
AFAIK, psql open one connection to database - and the transaction is 
connection related (two different connections does not share a 
transaction). I really mean AFAIK. At this point, someone else with more 
internals knowledge can give some light here.


My argument was pro "syntax error should rollback" to make things 
safe... :-). Assuming psql is working with only one connection, even in 
interactive mode, the transaction should remains valid.


Regards,


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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Scott Marlowe]

On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter  wrote:
> According to documentation,
>
> "TRUNCATE is transaction-safe with respect to the data in the tables: the
> truncation will be safely rolled back if the surrounding transaction does
> not commit."
>
> You will find this description at following page:
>
> http://www.postgresql.org/docs/9.0/static/sql-truncate.html
>
> So, when you have the "syntax error" on second line, then transaction is
> rolled back (cannot proceed: and that's why Syntax Errors should be treated
> as any other error) and your data is safe.

Yes but the discussion was that the syntax error SHOULDN'T cause a
roll back, and I was giving an example of when a transaction should
have rolled back but wouldn't have if syntax errors didn't cause
rollback.

In a different vein, the issue of "interactive" versus "scripted" is
something I don't want to take chances on getting wrong.  If I'm in
the psql terminal and type \i /tmp/somesqlile.sql is that interactive
or scripted?  How can psql know?  Should it know?  Can I trust it to
make the right decision of interactive versus scripted each time?

I generally put more than two lines of sql in a text file, edit it,
and throw at begin; on it.  run it with \i and then commit or rollback
as needed.  It documents what you did so you can check it in
somewhere, and makes it repeatable.

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Edson Richter]

According to documentation,

"TRUNCATEis transaction-safe with respect to the data in the tables: the 
truncation will be safely rolled back if the surrounding transaction 
does not commit."


You will find this description at following page:

http://www.postgresql.org/docs/9.0/static/sql-truncate.html

So, when you have the "syntax error" on second line, then transaction is 
rolled back (cannot proceed: and that's why Syntax Errors should be 
treated as any other error) and your data is safe.


Regards,

Edson Richter.


Em 19/06/2012 18:58, Scott Marlowe escreveu:

But that data was supposed to get transferred into another table
first!  Data shouldn't just disappear like that. If you want that kind
of behaviour use a different db that likes to throw your data away
when it shouldn't.

On Tue, Jun 19, 2012 at 1:09 PM, Edson Richter  wrote:

And I will be pleased that data is gone! I really did not expect anything but 
this.
If I need such tolerant behavior, then this shall be a feature of my special 
app, not a feature of the database... If the developer does not know how to 
write sql, then is time to learn. If the problem is the dynamic generated Sql, 
then I must write more test cases to cover these new scenarios. But IMHO, 
database must fail always (syntax or not...).

Regards,

Edson

Scott Marlowe  escreveu:


On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter  wrote:

There is also the case of dynamically generated sql statements based on user 
selection... being syntax or not, I would never want half job done. Thia is the 
purpose of transactions: or all or nothing...

This this this, and again, this.  Imagine:

begin;
insert into tableb selcet * from tableb;
truncate tableb;
commit;

What should happen when we get to the error on the second line?  Keep
going?  Boom, data gone because of a syntax error.

Re: [GENERAL] pgstat wait timeout : permission denied

[Dickson S. Guedes]

2012/6/19 Alban Hertroys :
>> 2012-06-19 09:33:38 CESTWARNING:  01000: pgstat wait timeout
>> 2012-06-19 09:33:38 CESTLOCATION:  backend_read_statsfile,
>> .\src\backend\postmaster\pgstat.c:3807
>> 2012-06-19 09:33:41 CESTLOG:  42501: could not rename temporary statistics
>> file "pg_stat_tmp/pgstat.tmp" to "pg_stat_tmp/pgstat.stat": Permission
>> denied
>
> That looks like anti-virus software getting in the way.


Or the Windows Firewall.


regards
-- 
Dickson S. Guedes
mail/xmpp: gue...@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Gavin Flower]

On 20/06/12 01:35, Rafal Pietrak wrote:

On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote:

On 06/19/2012 02:20 PM, Tom Lane wrote:

So you're suggesting that "SELECT 1/0;" should terminate a transaction,
but "SELECT 1//0;" should not?  How about "ROLBACK;"?  It gets pretty
squishy pretty fast when you try to decide which sorts of errors are
more important than others.


When put that way, it seems blindingly obvious. You have a talent for
making a devastating point very succinctly.

I'd humbly disagree.

Not to drag this discussiong any further, just to make a point that the
other approach is also "blindingly obvious". Only the other way around.

The point is, that SQL syntax errors are so obviusly different from
execution errors, that noting this distinction should not raise any
ambiguity. In Tom's example "ROLBACK":
1. should not break the transaction
2. should only raise NOTICE: "syntax error"
2.1. in case this was issued from command line - user can always
ROL to see what's next.
2.2. in case of a compiled program sending a "ROLBACK" to the
backend  hack, the programmer should know better.
3. and BTW: what about rolling back a tediously cooked sequence of
statements finished by "COMINT"?

Things are not so obvious. And frankly, if not for the "" I'd have
case (3) so often, that it would have driven me crasy.


-R


--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/




I would be be extremely concerned about any move to allow syntax errors 
not to abort a transaction.


Even minor syntax errors may indicate that something much more serious 
is wrong.


PL/1 was designed to tolerate various errors and guess what the 
programmer intended, it would make assumptions and act on them – a good 
way to hide serious programming errors.


A language that is too forgiving encourages sloppy thinking.

A bit like in chess, if you don't follow the dictum of 'touch a piece 
move it' in social play (it is the rule in match and tournament play), 
then your level of skill in Chess is unlikely to improve much. I coach 
Chess at my son's school and I used to be Director-of-Play for Chess 
tournaments.


I remember learning C many years ago, very unforgiving. However, the 
discipline imposed was very beneficial to improving my programming skills.


I would far rather a compiler pull me up for minor violations, than an 
obvious error not picked up until I came to test the program. The 
compiler is not perfect and some errors will slip through. However, the 
sooner errors are detected, the less likely an error will cause bad 
problems in production.


The greater the size and complexity of code, the more important this all 
becomes. Mind you, even very simple SQL SELECT's might have results used 
to make critical business decisions - so even then, sloppy habits should 
be discouraged.


I would be very reluctant to hire any developer who had the mind set of 
seriously wanting something like psql to be forgiving of any kind of 
error - as it suggests that they are more careless than normal, and lack 
the attitude to be reliably rigorous.

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Darren Duncan]

I like the current behavior.  Having been pleasantly surprised that this is how 
Pg operates, it is very helpful when I'm working on scripts or batches such as 
for creating or populating schemas.  If it dies part way through, I know I can 
just fix the problem and rerun the whole thing, without having to first undo or 
skip the earlier portions.  Also, rollback for everything is much more 
deterministic. -- Darren Duncan


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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Scott Marlowe]

But that data was supposed to get transferred into another table
first!  Data shouldn't just disappear like that. If you want that kind
of behaviour use a different db that likes to throw your data away
when it shouldn't.

On Tue, Jun 19, 2012 at 1:09 PM, Edson Richter  wrote:
> And I will be pleased that data is gone! I really did not expect anything but 
> this.
> If I need such tolerant behavior, then this shall be a feature of my special 
> app, not a feature of the database... If the developer does not know how to 
> write sql, then is time to learn. If the problem is the dynamic generated 
> Sql, then I must write more test cases to cover these new scenarios. But 
> IMHO, database must fail always (syntax or not...).
>
> Regards,
>
> Edson
>
> Scott Marlowe  escreveu:
>
>>On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter  
>>wrote:
>>> There is also the case of dynamically generated sql statements based on 
>>> user selection... being syntax or not, I would never want half job done. 
>>> Thia is the purpose of transactions: or all or nothing...
>>
>>This this this, and again, this.  Imagine:
>>
>>begin;
>>insert into tableb selcet * from tableb;
>>truncate tableb;
>>commit;
>>
>>What should happen when we get to the error on the second line?  Keep
>>going?  Boom, data gone because of a syntax error.
>>



-- 
To understand recursion, one must first understand recursion.

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Rafal Pietrak]

On Wed, 2012-06-20 at 08:27 +1200, Gavin Flower wrote:
[]
> > > 
> > 
> > 
> I would be be extremely concerned about any move to allow syntax
> errors not to abort a transaction. 

Me too. But it's a nuicence for interractive session when  transactions
breakes due to syntax error - still, may be as Rainer Pruy said earlier,
this may be a suggestion to maintainers of interactive tools. 
> 
> Even minor syntax errors may indicate that something much more serious
> is wrong.

No. We are talking about an interactive session - someone just have
misstyped something; it's a one time event.
> 
> PL/1 was designed to tolerate various errors and guess what the
> programmer intended, it would make assumptions and act on them – a
> good way to hide serious programming errors.
> 
> A language that is too forgiving encourages sloppy thinking.

This is "dangerous grounds" :) - without going too far I'd say, there is
also ADA (rigorious) and perl (sloopy). "statistically", anything I
installed, that's written in perl is ways more stable, then enything
else. 

But I'd also say, that I prefere tools (programming languages, operating
systems, IDE, etc), that help me from makeing errors.
> 
[---]
> 
> I would far rather a compiler pull me up for minor violations, than an
> obvious error not picked up until I came to test the program. The
> compiler is not perfect and some errors will slip through. However,
> the sooner errors are detected, the less likely an error will cause
> bad problems in production.

On the other hand I find it more tedious then it pays off, when current
CC force me to explicitly typecast every pointer I write, because: "type
don't match". But that's not the point here.

The point is, that sometimes we need regorious, and sometimes we need
sloopy. Like, when we start a project, we need to "scetch", then we need
to "tighten the shoelaces". At least for me it works that way.

And we are talking about interractive psql breaking transaction because
of syntax error - almost always this is a one time typo. I'd prefere it
to be a bit more "sloopy", then deployed SQL application (e.g.
non-interactive session).
> 
> The greater the size and complexity of code, the more important this
> all becomes. Mind you, even very simple SQL SELECT's might have
> results used to make critical business decisions - so even then,
> sloppy habits should be discouraged.

Hmmm, years ago I has told, that UNIX is sloopy (does not guarantee
anything to a process: neither time to dysk when writing, nor CPU time,
nor even IRQ response time), so it will not prevail. It did. And it runs
critical systems.

As postgres is my favourite database for its ease of use (to the point
where I dont try applications which only run on its closest
free-couterpart: mysql :), there is always room for improvements (my
personal wishlist for postgres is currently 11 points long and keeping
transaction on syntax errors is even beyond that list).

-R
> 



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

Re: [GENERAL] Special ORDER BY

[Hellmuth Vargas]

Helo

This is provided that the list is constant

SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo)
 JOIN (values(245,1),(130,2), (240,3), (243,4), (246,5), (490,6),
(630,7), (730,8), (740,9)) as d(dato,orden) on (c.etiqueta =d.dato)
 ORDRE BY d.orden



On Tue, Jun 19, 2012 at 2:31 PM, Martín Marqués wrote:

> How can I order a result set by a special order of a column. For
> example, let's say I have this query
>
>
> SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo)
>  WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740)
>
> Now I want to order the result set using c.etiqueta but with the order
> of the values as you see in the IN clause.
>
> How can I do that?
>
> --
> Martín Marqués
> select 'martin.marques' || '@' || 'gmail.com'
> DBA, Programador, Administrador
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate

Re: [GENERAL] Special ORDER BY

[Tulio]

  
  
You can use something like "CASE WHEN" .. and use a number to order
your data...
like this
SELECT *, CASE WHEN c.etiqueta = 245 THEN 1 
		WHEN c.etiqueta = 130 THEN 2 
		WHEN c.etiqueta = 240 THEN 3 
		WHEN c.etiqueta = 243 THEN 4 
		WHEN c.etiqueta = 246 THEN 5 
		WHEN c.etiqueta = 490 THEN 6 
		WHEN c.etiqueta = 630 THEN 7
		WHEN c.etiqueta = 730 THEN 8
		WHEN c.etiqueta = 740 THEN 9
  end as newcolum FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo)
WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740)
ORDER BY newcolum


Em 19/06/2012 16:31, Martín Marqués escreveu:

  How can I order a result set by a special order of a column. For
example, let's say I have this query


SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo)
  WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740)

Now I want to order the result set using c.etiqueta but with the order
of the values as you see in the IN clause.

How can I do that?





  

  

[GENERAL] Special ORDER BY

[Martín Marqués]

How can I order a result set by a special order of a column. For
example, let's say I have this query


SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo)
  WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740)

Now I want to order the result set using c.etiqueta but with the order
of the values as you see in the IN clause.

How can I do that?

-- 
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Edson Richter]

And I will be pleased that data is gone! I really did not expect anything but 
this.
If I need such tolerant behavior, then this shall be a feature of my special 
app, not a feature of the database... If the developer does not know how to 
write sql, then is time to learn. If the problem is the dynamic generated Sql, 
then I must write more test cases to cover these new scenarios. But IMHO, 
database must fail always (syntax or not...).

Regards,

Edson 

Scott Marlowe  escreveu:

>On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter  
>wrote:
>> There is also the case of dynamically generated sql statements based on user 
>> selection... being syntax or not, I would never want half job done. Thia is 
>> the purpose of transactions: or all or nothing...
>
>This this this, and again, this.  Imagine:
>
>begin;
>insert into tableb selcet * from tableb;
>truncate tableb;
>commit;
>
>What should happen when we get to the error on the second line?  Keep
>going?  Boom, data gone because of a syntax error.
>

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

Re: [GENERAL] bytea insert difference between 8.3 and 9.x

[McKay]

Merlin Moncure-2 wrote
> 
> m_connection.prepare("INSERT INTO foo(x) VALUES($1)") ("bytea",
> pqxx::prepare::treat_binary); 
> 

I have the same problem. I can't get accsess to pqxx::prepare::treat_binary.
And m_connection.prepare(...) two arguments only. What i mist?

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/bytea-insert-difference-between-8-3-and-9-x-tp4840946p5713320.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] db server processes hanging around

[Steve Crawford]

On 06/19/2012 09:29 AM, Mark Rostron wrote:

hi

we are running out of database connections.

we are using pg 9.0.6 on linux centos 5.7 64bit.
we are not using any go-between connection pools such as pgbouncer or 
pgpool - connections occur directly from client to database.
the connection setup on the client (java) is default, only providing 
(user,password,dbhost,dbname).


we have about 10 developers developing java thru IDEA who start/stop 
the local tomcat server frequently.
i have observed that tomcat doesn't disconnect from pg cleanly when 
they cycle, and the server processes persist for a long time.
I have had them reduce their local connection factory pool size to 1 
(this helped) and increased our max_connection value to 1000.

yet the problem persists.

I have noticed that the server processes do die "after some time" - 
due to inactivity?
we are looking for a way to control server processes better than we 
are doing now.


thnx for your time.
mr


I am unaware of any system setting like max_connection_idle_time (though 
it might be a useful addition). I have not had to mess with 
tcp_keepalive settings but you might be able to alter those (perhaps at 
the OS instead of PostgreSQL) to reduce the delay before the backend 
terminates. But this won't work for socket connections.


You could hack together a tailored solution by having cron run a script 
that would query pg_stat_activity for queries equal to "" and with 
a backend_start age greater than whatever you find reasonable and then 
execute pg_terminate_backend() on those PIDs. You could even have a 
table of developer IP addresses and only terminate those processes. 
Alternately, if Tomcat connected to a different port you could only kill 
those.


Cheers,
Steve

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Scott Marlowe]

On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter  wrote:
> There is also the case of dynamically generated sql statements based on user 
> selection... being syntax or not, I would never want half job done. Thia is 
> the purpose of transactions: or all or nothing...

This this this, and again, this.  Imagine:

begin;
insert into tableb selcet * from tableb;
truncate tableb;
commit;

What should happen when we get to the error on the second line?  Keep
going?  Boom, data gone because of a syntax error.

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

Re: [GENERAL] Fine-grained replication?

[Paul Jones]

I should have been more specific about why I'm interested in this.

The idea is not so much the replication but being able to use the
replication information to make business decisions based on what is
observed happening to the data, or being able to selectively repair
data if there are problems.

XReader certainly shows a lot of promise, but it's not quite here yet.

Specific answers to respondents below...

> From: Sergey Konoplev 
> Date: Tue, 19 Jun 2012 13:00:49 +0400

> On Tue, Jun 19, 2012 at 1:49 AM, Paul Jones  wrote:
>> Is anyone aware of other non-trigger-based, fine-grained replication tools 
>> for PostgreSQL along the lines of the XReader 
>>  or pgreplay 
>> ?

> Binary Replication? 
> http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
> Sergey Konoplev

Thanks!  This is good info.

On Tue, Jun 19, 2012 at 12:34:02PM +0200, Albe Laurenz wrote:

> I'm not sure what you want, because "pgreplay" is certainly not a
> replication
> tool, unless "replication" means something quite different to you than
> it
> does to me.
> 
> Can you be more specific?

Yes, as explained above.  So pgreplay in this context would be more likely
to be used to repair something.

> Date: Tue, 19 Jun 2012 12:09:28 +0200
> From: Guillaume Lelarge 
> 
> If you want fine grained replication, it means you won't be able to use
> log shipping and streaming replication. If you don't want trigger based
> replication, you won't have many options still available. pgPool comes to
> mind but, AFAIK, it isn't fine grained (and, to be honest, I would say it
> isn't really replication).
> 
> So, nope, sorry.
> 
> BTW, what's the issue with trigger-based replication? it really helps a
> lot in many cases.
> 

The worry with trigger-based replication is that it could become a
performance bottleneck for heavy loads.  We tried out Slony just to see
how it worked and it did its job just fine for what we did with it.

Paul Jones

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

Re: [GENERAL] Smaller multiple tables or one large table?

[Benedict Holland]

Hi all,

I am curious if there is a significant speed up with doing this if most of
the queries run against it are going to be table wide. I won't drop the
data and the data won't really grow. Do I get better speedup with one large
table and large indexes or many small tables with many small indexes?

Thanks,
~Ben

On Sat, Jun 16, 2012 at 2:13 AM, Gabriele Bartolini <
gabriele.bartol...@2ndquadrant.it> wrote:

> Hi Benedict,
>
> Il 15/06/12 20:58, Benedict Holland ha scritto:
>
>  The tables would have to be specified with a table pk constraint falling
>> between two ranges. A view would then be created to manage all of the small
>> tables with triggers handling insert and update operations. Select would
>> have to be view specific but that is really cheap compared to updates. That
>> should have the additional benefit of only hitting a specific table(s) with
>> an update.
>>
>> Basically, I don't see how this particular configuration breaks and if
>> PostgreSQL already has the ability to do this as it seems very useful to
>> manage very large data sets.
>>
>
> What you are looking for is called 'partitioning' (horizontal
> partitioning). I suggest that you read this chapter:
> http://www.postgresql.org/**docs/9.1/static/ddl-**partitioning.html
>
> Cheers,
> Gabriele
>
> --
>  Gabriele Bartolini - 2ndQuadrant Italia
>  PostgreSQL Training, Services and Support
>  gabriele.bartolini@**2ndQuadrant.it | www.2ndQuadrant.it
>
>

[GENERAL] db server processes hanging around

[Mark Rostron]

hi

we are running out of database connections.

we are using pg 9.0.6 on linux centos 5.7 64bit.
we are not using any go-between connection pools such as pgbouncer or
pgpool - connections occur directly from client to database.
the connection setup on the client (java) is default, only providing
(user,password,dbhost,dbname).

we have about 10 developers developing java thru IDEA who start/stop the
local tomcat server frequently.
i have observed that tomcat doesn't disconnect from pg cleanly when they
cycle, and the server processes persist for a long time.
I have had them reduce their local connection factory pool size to 1 (this
helped) and increased our max_connection value to 1000.
yet the problem persists.

I have noticed that the server processes do die "after some time" - due to
inactivity?
we are looking for a way to control server processes better than we are
doing now.

thnx for your time.
mr

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


...
> ON_ERROR_ROLLBACK=interactive provides a helper for that in psql.
>
> Savepoints are overhead, though, and I don't understand why they're 
> required for statements that don't even parse.

Other have handled the latter part of the above already (short version: 
error is the only sane response to a non-parsing statement), but as 
to the first part, the overhead is really not that high. Yes, psql 
will create and remove a savepoint around each statement, but this is 
a very lightweight action, especially if you are using psql in 
interactive mode. In other words, we already have an elegant and 
lightweight approach to the described problem.


- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201206191146
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk/gn30ACgkQvJuQZxSWSsgekgCfcoBq2VjCitjrpK9CrSMFob0Y
YF8An3Qp/OQjAcRsEBahE5OIbFzEEZX/
=hHAn
-END PGP SIGNATURE-



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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Edson Richter]

There is also the case of dynamically generated sql statements based on user 
selection... being syntax or not, I would never want half job done. Thia is the 
purpose of transactions: or all or nothing...

Tom Lane  escreveu:

>Rafal Pietrak  writes:
>> The point is, that SQL syntax errors are so obviusly different from
>> execution errors, that noting this distinction should not raise any
>> ambiguity.
>
>I beg to disagree.  Typos can manifest themselves as execution errors
>just as well as syntax errors.
>
>You are probably thinking that we could behave differently if the error
>was detected by the lexer, or perhaps the lexer + grammar, rather than
>later on.  But those boundaries are purely implementation artifacts,
>and the division of labor isn't always obvious, especially to people not
>steeped in the innards of PG.  Users are going to be confused (and
>unhappy) if some errors roll back their transaction while other
>not-obviously-different ones don't.
>
>As an example, suppose you fat-finger '-' for '=' in UPDATE:
>
>   UPDATE tab SET col - 42 WHERE ...
>
>This is going to draw a grammar error.  But make the same mistake
>a few tokens later:
>
>   UPDATE tab SET col = 42 WHERE key - 42;
>
>and now you will get a pretty late-stage parse analysis failure,
>since it'll bleat that the argument of WHERE isn't boolean.  Users
>are definitely not going to understand why the former doesn't kill
>their transaction but the latter does.  Or, if we solve that problem
>by saying that no parse-analysis failure kills the transaction,
>where does that stop?  The boundaries between parse analysis, planning,
>and execution are even squishier and more arbitrary (from a naive user's
>standpoint) than the ones earlier in the process.
>
>   regards, tom lane
>
>-- 
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general
>

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Rainer Pruy]

Hm, sorry but I still can not get into that argument.

Take your example 3 (COMINT in place of COMMIT)

How should the DB know that (and how) to safely recover from such error?
You need to tell - and there are tools to do so right available.

In an interactive session:
- use "autocommit=on" to indicate that any statement surely will not
invalidate any previous one
  Then the "problem" is non-existent

- if you need transactional grouping of statements:
  you may envelope each statement with transactional sub structure (e.g.
SAVEPOINTRELEASE)
  to indicate to the DB that only the inner most level of transaction is
at stake and
  the "environment" outside that statement may cope with errors.

  Agreed, this is "unexpected" if coming from a DB that treats syntax
errors differently.
  (May be sometimes there will be a mode with interactive tools that
provide such enveloping implicitly (if requested by user))

In a non-interactive session it is more obvious.
What should happen when after the failed "COMMIT" above the session is
to be terminated?
The pending transaction is to be terminated anyway.
Moreover, of a syntax error happens with a statement (e.g. some update)
and a later statement is assuming it had succeeded  and will ruin your
data if not,
would you still appreciate the DB to simply ignore the error (logging a
message of course) and
later on happily commit inconsistent data?
I'm sure, there will be loud outcry if such would be possible by mere
syntax error handling.

If your application is prepared to handle syntax errors during run, then
use available tools, if not
(and most application likely will not provide such logic), accept the
need for testing your applications.


Any reaction for a transactional system has to guarantee consistency
even for the price of convenience. Thus, convenience may cost some extra
effort.

At the end, I read the complaint as a suggestion to maintainers of
interactive tools
to build such interactive convenience into their tools.
But do not detect evidence for this to be a "feature" of the DB in the
first place.

Rainer
On 19.06.2012 15:35, Rafal Pietrak wrote:
> On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote:
>> On 06/19/2012 02:20 PM, Tom Lane wrote:
>>> So you're suggesting that "SELECT 1/0;" should terminate a transaction,
>>> but "SELECT 1//0;" should not?  How about "ROLBACK;"?  It gets pretty
>>> squishy pretty fast when you try to decide which sorts of errors are
>>> more important than others.
>>>
>> When put that way, it seems blindingly obvious. You have a talent for 
>> making a devastating point very succinctly.
> I'd humbly disagree.
>
> Not to drag this discussiong any further, just to make a point that the
> other approach is also "blindingly obvious". Only the other way around.
>
> The point is, that SQL syntax errors are so obviusly different from
> execution errors, that noting this distinction should not raise any
> ambiguity. In Tom's example "ROLBACK":
> 1. should not break the transaction
> 2. should only raise NOTICE: "syntax error"
> 2.1. in case this was issued from command line - user can always
> ROL to see what's next.
> 2.2. in case of a compiled program sending a "ROLBACK" to the
> backend  hack, the programmer should know better.
> 3. and BTW: what about rolling back a tediously cooked sequence of
> statements finished by "COMINT"?
>
> Things are not so obvious. And frankly, if not for the "" I'd have
> case (3) so often, that it would have driven me crasy.
>
>
> -R
>
>> --
>> Craig Ringer
>>
>> POST Newspapers
>> 276 Onslow Rd, Shenton Park
>> Ph: 08 9381 3088 Fax: 08 9388 2258
>> ABN: 50 008 917 717
>> http://www.postnewspapers.com.au/
>>
>
>

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Tom Lane]

Rafal Pietrak  writes:
> The point is, that SQL syntax errors are so obviusly different from
> execution errors, that noting this distinction should not raise any
> ambiguity.

I beg to disagree.  Typos can manifest themselves as execution errors
just as well as syntax errors.

You are probably thinking that we could behave differently if the error
was detected by the lexer, or perhaps the lexer + grammar, rather than
later on.  But those boundaries are purely implementation artifacts,
and the division of labor isn't always obvious, especially to people not
steeped in the innards of PG.  Users are going to be confused (and
unhappy) if some errors roll back their transaction while other
not-obviously-different ones don't.

As an example, suppose you fat-finger '-' for '=' in UPDATE:

UPDATE tab SET col - 42 WHERE ...

This is going to draw a grammar error.  But make the same mistake
a few tokens later:

UPDATE tab SET col = 42 WHERE key - 42;

and now you will get a pretty late-stage parse analysis failure,
since it'll bleat that the argument of WHERE isn't boolean.  Users
are definitely not going to understand why the former doesn't kill
their transaction but the latter does.  Or, if we solve that problem
by saying that no parse-analysis failure kills the transaction,
where does that stop?  The boundaries between parse analysis, planning,
and execution are even squishier and more arbitrary (from a naive user's
standpoint) than the ones earlier in the process.

regards, tom lane

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Andrew Sullivan]

On Tue, Jun 19, 2012 at 03:35:19PM +0200, Rafal Pietrak wrote:
> 
> The point is, that SQL syntax errors are so obviusly different from
> execution errors, that noting this distinction should not raise any
> ambiguity.

Good.  One looks forward to your fully-worked-out AI/ESP patch that gets
this right every time.  While you're at it, I suggest fixing these
"obvious" mistakes:

SELECT SELECT 'text';
SELECT 'text;
SELECT INSERT 'text' INTO column;
INSERT 'text' INTO 'column';

And so on.  Every one of these is a boiled down example of a stupid
think-o I have made more than once.  This is what the command buffer
is for.

If you really want your input system to provide fairly complete syntax
checking for you, however, I will point out that psql's \e command
will happily drop you into the editor of your choice.  If you want an
editor that knows more about what you want than you do, I think you
will find it is spelled "emacs".

Best,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

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

Re: [GENERAL] Error message "psql: could not connect to server: No such file or directory"

[Adrian Klaver]

On 06/19/2012 01:32 AM, Stefan Schwarzer wrote:



I need unfortunately to come back to this issue. I (again) re-installed Lion 
from scratch, and finally got everything working. The Postgres was running, I 
uploaded a couple of dumped SQL files. And then re-started the machine for 
another reason. And suddenly it says again the it can't connect to the server.

I tried
/usr/local/pgsql-9.1/bin/initdb -U postgres -D /usr/local/pgsql-9.1/data 
--encoding=UTF8 --locale=en_US

and
/usr/local/pgsql-9.1/bin/pg_ctl -D /usr/local/pgsql-9.1/data/ -l logfile start

and
sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist

… but nothing happens. I don't have the feeling that it's a problem with the "postgres" 
and "_postgres" users (as it has worked before already).

Can anyone give me a hint how I can figure out where the problem lies and what 
I can do about it? Why did it work before I restarted the computer and why does 
it not work anymore?

Thanks a lot for any hints!



When troubleshooting I am firm believer in working from the known to the 
unknown. In that vein:


1) Is there /usr/local/pgsql-9.1/data/?

   a) If so are there subdirectories in it indicating cluster was 
created? i.e. base/ global/ pg_clog/, etc

   b) If not is there a Postgres data directory anywhere on machine?

2) When you say nothing happens does that mean nothing is written to log 
file and/or terminal? No process shows up in process list?


3) If something does happen in, terms of error messages, what are they?

4) Have you looked at system (not Postgres specific) logs to see if this 
a system issue?


5) What user are you running the above commands as?
  a) What user is associated with the Postgres data directory(if it 
exists)?


--
Adrian Klaver
adrian.kla...@gmail.com



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

Re: [GENERAL] SELECT INTO create new table, how to turn off this feature?

[aasat]

Sorry, my mistake, Postgres 9.0 for unexists variables throwing error
"myvariable" is not a known variable

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/SELECT-INTO-create-new-table-how-to-turn-off-this-feature-tp5713267p5713279.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Rafal Pietrak]

On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote:
> On 06/19/2012 02:20 PM, Tom Lane wrote:
> > So you're suggesting that "SELECT 1/0;" should terminate a transaction,
> > but "SELECT 1//0;" should not?  How about "ROLBACK;"?  It gets pretty
> > squishy pretty fast when you try to decide which sorts of errors are
> > more important than others.
> >
> When put that way, it seems blindingly obvious. You have a talent for 
> making a devastating point very succinctly.

I'd humbly disagree.

Not to drag this discussiong any further, just to make a point that the
other approach is also "blindingly obvious". Only the other way around.

The point is, that SQL syntax errors are so obviusly different from
execution errors, that noting this distinction should not raise any
ambiguity. In Tom's example "ROLBACK":
1. should not break the transaction
2. should only raise NOTICE: "syntax error"
2.1. in case this was issued from command line - user can always
ROL to see what's next.
2.2. in case of a compiled program sending a "ROLBACK" to the
backend  hack, the programmer should know better.
3. and BTW: what about rolling back a tediously cooked sequence of
statements finished by "COMINT"?

Things are not so obvious. And frankly, if not for the "" I'd have
case (3) so often, that it would have driven me crasy.


-R

> 
> --
> Craig Ringer
> 
> POST Newspapers
> 276 Onslow Rd, Shenton Park
> Ph: 08 9381 3088 Fax: 08 9388 2258
> ABN: 50 008 917 717
> http://www.postnewspapers.com.au/
> 



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

Re: [GENERAL] SELECT INTO create new table, how to turn off this feature?

[Raymond O'Donnell]

On 19/06/2012 14:05, aasat wrote:
> Hi,
> 
> Is it possible to turn off future "SELECT INTO" that create new table?
> I use it on pl/pgsql function, and I have mamy mistakes with variable name
> in SQL 

No, it's not possible.

What problems are you running into?

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie



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

[GENERAL] How to get no. of commits/rollbacks by application on the database?

[Raghavendra]

Hi All,

How can we figure out no. of commit/rollback happend on the database by
application ?

As per my testing what I observed, even if your database is idle than
in pg_stat_database
(xact_commit/xact_rollback) columns keep increasing. This means
autovacuum/autoanalyze count also included in these column.

If am a DBA, how to know exact application or user commits/rollbacks
happened on the database without enabling logs.

Please advice.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

[GENERAL] SELECT INTO create new table, how to turn off this feature?

[aasat]

Hi,

Is it possible to turn off future "SELECT INTO" that create new table?
I use it on pl/pgsql function, and I have mamy mistakes with variable name
in SQL 

thanks
Wojtek

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/SELECT-INTO-create-new-table-how-to-turn-off-this-feature-tp5713267.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Fine-grained replication?

[Achilleas Mantzios]

On Τρι 19 Ιουν 2012 12:00:49 Sergey Konoplev wrote:
> On Tue, Jun 19, 2012 at 1:49 AM, Paul Jones  wrote:
> > Is anyone aware of other non-trigger-based, fine-grained replication
> > tools for PostgreSQL along the lines of the XReader
> >  or pgreplay
> > ?
> 
> Binary Replication?
> http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
> 

Alright, but the OP wrote about "fine-grained". IMHO log-based and fine-grained
seem a liitle but mutually conflicting.

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

-
Achilleas Mantzios
IT DEPT

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

Re: [GENERAL] Fine-grained replication?

[Achilleas Mantzios]

On Τρι 19 Ιουν 2012 13:09:28 Guillaume Lelarge wrote:
> On Mon, 18 Jun 2012 14:49:32 -0700 (PDT), Paul Jones 
> 
> wrote:
> > Is anyone aware of other non-trigger-based, fine-grained replication
> 
> tools
> 
> > for PostgreSQL along the lines of the XReader
> >  or pgreplay
> > ?
> 
> If you want fine grained replication, it means you won't be able to use
> log shipping and streaming replication. If you don't want trigger based
> replication, you won't have many options still available. pgPool comes to
> mind but, AFAIK, it isn't fine grained (and, to be honest, I would say it
> isn't really replication).
> 
> So, nope, sorry.
> 
> BTW, what's the issue with trigger-based replication? it really helps a
> lot in many cases.

Totally agree, for us, a heavily modified version of (what initially was) 
DBmirror 
has made the job more than well.
Also, our replication is the most "fine-grained" situation i can think of. 
(even rows being lazily replicated based on the FK dependency graph, and many 
other nice tricks)

-
Achilleas Mantzios
IT DEPT

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Craig Ringer]

On 06/19/2012 02:20 PM, Tom Lane wrote:

So you're suggesting that "SELECT 1/0;" should terminate a transaction,
but "SELECT 1//0;" should not?  How about "ROLBACK;"?  It gets pretty
squishy pretty fast when you try to decide which sorts of errors are
more important than others.

When put that way, it seems blindingly obvious. You have a talent for 
making a devastating point very succinctly.


--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

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

Re: [GENERAL] Fine-grained replication?

[Albe Laurenz]

Paul Jones wrote:
> Is anyone aware of other non-trigger-based, fine-grained replication
tools for PostgreSQL along the
> lines of the XReader  or
pgreplay
> ?

I'm not sure what you want, because "pgreplay" is certainly not a
replication
tool, unless "replication" means something quite different to you than
it
does to me.

Can you be more specific?

Yours,
Laurenz Albe

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

Re: [GENERAL] pgstat wait timeout : permission denied

[pandorino]

hmm, interesting idea.
I'll check, it will be a bit difficult because it is centrally managed by
administrators, but I will see how to temporary disable it.
Thanks


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-permission-denied-tp5713236p5713255.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Fine-grained replication?

[Guillaume Lelarge]

On Mon, 18 Jun 2012 14:49:32 -0700 (PDT), Paul Jones 
wrote:
> Is anyone aware of other non-trigger-based, fine-grained replication
tools
> for PostgreSQL along the lines of the XReader
>  or pgreplay
> ?
> 

If you want fine grained replication, it means you won't be able to use
log shipping and streaming replication. If you don't want trigger based
replication, you won't have many options still available. pgPool comes to
mind but, AFAIK, it isn't fine grained (and, to be honest, I would say it
isn't really replication).

So, nope, sorry.

BTW, what's the issue with trigger-based replication? it really helps a
lot in many cases.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

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

[GENERAL] Please make it easy to drop a database that is in use

[Evan Martin]

Hi All,

When I'm developing against a PostgreSQL database I often drop and 
re-create it and I often find that the drop fails, because it's "in use 
by other users". This is really annoying, especially when I know full 
well there are no other users - it's just me.


I'm aware of the workaround: use *pg_terminate_backend* to kill existing 
connections, but there are two problems with that. Firstly, it's just a 
hassle, for something that should be a very simple operation. (I'm not 
even writing SQL for it normally, just pressing Delete in pgAdmin.) 
Secondly, pg_terminate_backend requires superuser rights. If I'm not a 
superuser, but I am the owner of the database, it doesn't seem right 
that another user should be able to prevent me from dropping /my/ database.


I'd really like to see PostgreSQL directly support dropping a database, 
regardless of who is using it - something like "DROP DATABASE ... 
CASCADE". (Although "CASCADE" wouldn't be the appropriate word here. 
Maybe "DROP DATABASE ... TO_HELL_WITH_USERS"?)


Evan

Re: [GENERAL] pgstat wait timeout : permission denied

[Alban Hertroys]

> 2012-06-19 09:33:38 CESTWARNING:  01000: pgstat wait timeout
> 2012-06-19 09:33:38 CESTLOCATION:  backend_read_statsfile,
> .\src\backend\postmaster\pgstat.c:3807
> 2012-06-19 09:33:41 CESTLOG:  42501: could not rename temporary statistics
> file "pg_stat_tmp/pgstat.tmp" to "pg_stat_tmp/pgstat.stat": Permission
> denied

That looks like anti-virus software getting in the way.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

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

Re: [GENERAL] Fine-grained replication?

[Sergey Konoplev]

On Tue, Jun 19, 2012 at 1:49 AM, Paul Jones  wrote:
> Is anyone aware of other non-trigger-based, fine-grained replication tools 
> for PostgreSQL along the lines of the XReader 
>  or pgreplay 
> ?

Binary Replication? http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

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



-- 
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204

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

Re: [GENERAL] Error message "psql: could not connect to server: No such file or directory"

[Stefan Schwarzer]

>>> I guess this is a typical user error. I searched around to find the 
>>> solution, but in vain.
>>> 
>>> I just upgraded to Lion, and used Kyngchaos libraries for installation of 
>>> Postgres.
>>> 
>>> Try to get running postgres, and I get this:
>>> 
>>>  /usr/local/pgsql-9.1/bin/psql -U postgres
>>>  psql: could not connect to server: No such file or directory
>>> Is the server running locally and accepting
>>> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
>>> 
>>> 
>>> In my limited understanding, it seems that Postgres is not running. When I 
>>> use:
>>> 
>>>   ps auxw | grep post
>>> 
>>> out comes only the "grep post", but no postgres process.
>>> 
>>> When I use:
>>> 
>>>   sudo launchctl load 
>>> /Library/LaunchDaemons/org.postgresql.postgres.plist
>>> 
>>> it says:
>>> 
>>>   org.postgresql.postgres: Already loaded
>>> 
>>> and displays in the "ps" list then:
>>> 
>>>   user935   0.0  0.1  2493888   7296   ??  Ss3:24PM   
>>> 0:00.12 
>>> /System/Library/PrivateFrameworks/DiskImages.framework/Resources/diskimages-helper
>>>  -uuid 9EFB9424-7971-4A8B-9D73-B93BD7F1DB5F -post-exec 4
>>> 
>>> (which looks a bit strange to me (DiskImages ??))
>>> 
>>> 
>>> I am somewhat lost (and worse is that it worked the other day, before 
>>> re-installing Lion anew.).
>> 

I need unfortunately to come back to this issue. I (again) re-installed Lion 
from scratch, and finally got everything working. The Postgres was running, I 
uploaded a couple of dumped SQL files. And then re-started the machine for 
another reason. And suddenly it says again the it can't connect to the server.

I tried 
/usr/local/pgsql-9.1/bin/initdb -U postgres -D /usr/local/pgsql-9.1/data 
--encoding=UTF8 --locale=en_US

and
/usr/local/pgsql-9.1/bin/pg_ctl -D /usr/local/pgsql-9.1/data/ -l logfile start

and
sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist

… but nothing happens. I don't have the feeling that it's a problem with the 
"postgres" and "_postgres" users (as it has worked before already).

Can anyone give me a hint how I can figure out where the problem lies and what 
I can do about it? Why did it work before I restarted the computer and why does 
it not work anymore?

Thanks a lot for any hints!
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgstat wait timeout : permission denied

[pandorino]

Hi All
my configuration: 
(-) postgres 9.1
(-) windows 2003
(-) 4 Xeon CPUs
(-) 16GB of RAM

I'm getting a lot of errors in my postgres log file:
---
2012-06-19 09:31:06 CESTLOCATION:  backend_read_statsfile,
.\src\backend\postmaster\pgstat.c:3807
2012-06-19 09:31:38 CESTWARNING:  01000: pgstat wait timeout
2012-06-19 09:31:38 CESTLOCATION:  backend_read_statsfile,
.\src\backend\postmaster\pgstat.c:3807
2012-06-19 09:31:58 CESTWARNING:  01000: pgstat wait timeout
2012-06-19 09:31:58 CESTLOCATION:  backend_read_statsfile,
.\src\backend\postmaster\pgstat.c:3807
2012-06-19 09:32:58 CESTWARNING:  01000: pgstat wait timeout
2012-06-19 09:32:58 CESTLOCATION:  backend_read_statsfile,
.\src\backend\postmaster\pgstat.c:3807
2012-06-19 09:33:38 CESTWARNING:  01000: pgstat wait timeout
2012-06-19 09:33:38 CESTLOCATION:  backend_read_statsfile,
.\src\backend\postmaster\pgstat.c:3807
2012-06-19 09:33:41 CESTLOG:  42501: could not rename temporary statistics
file "pg_stat_tmp/pgstat.tmp" to "pg_stat_tmp/pgstat.stat": Permission
denied
---
and that's really odd: I mean, when postgres starts, it creates the file
pg_stat_tmp/pgstat.stat, so it means that it has all the permissions to
access to that folder.  But, nevertheless, after some time it shows the
error above.
And these are repeadtly shown in the postgres log file.

Tried to change the folder/file permissions to "everyone full control", but
nothing changes.

Do you have some hints/idea?
Thanks in advance



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-permission-denied-tp5713236.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] How to include Tablefunc as an extension

[Hitoshi Harada]

On Mon, Jun 18, 2012 at 11:34 PM, Stefan Schwarzer
 wrote:
>
>>> I read through the Postgres doc and many Google results, but it seems
>>> still unclear to me how to include additional packages into my postgres
>>> database. I see that there are a few installed under
>>> "/usr/local/pgsql-9.1/share/extension/" (I am on Lion and installed the
>>> Kyngchaos libs). But as I want to install crosstab now, I downloaded the
>>> source code for postgres, run a make/install - and now wonder what to do
>>> with it. I could eventually load the .sql file as usual; but it seems this
>>> should be now avoided, and loaded as an extension. I tried to copy three
>>> files (--1.0.sql, --unpackaged.sql and the normal .sql) to the folder and
>>> then load it from PgAdmin, but this results in an error message ("could
>>> not access file "$libdir/tablefunc"").
>>
>> This should do the trick:
>>
>> CREATE EXTENSION crosstab;
>
> Thanks a lot. Yes, that's the command I used when it launches the above error 
> message. The question is more about: "where do I need to place the tablefunc 
> files (and which ones) in order to execute successfully that command"?

make install should copy files including $libdir/tablefunc.  If not,
you should take a look at the output of the make command.  Make sure
your PATH points to pg_config that your database is running with.

Thanks,
-- 
Hitoshi Harada

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

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

[Peter Bex]

On Tue, Jun 19, 2012 at 02:20:57AM -0400, Tom Lane wrote:
> Craig Ringer  writes:
> > I've been working in psql a lot recently, and have started to wonder why 
> > statements with syntax errors or other problems that render them 
> > unexecutable terminate the transaction.
> 
> Well, the obvious reason is that it's hard to tell what the user meant,
> so bailing is the safest response.
> 
> > I understand why statements that raise errors during their execution 
> > terminate a transaction,
> 
> So you're suggesting that "SELECT 1/0;" should terminate a transaction,
> but "SELECT 1//0;" should not?  How about "ROLBACK;"?  It gets pretty
> squishy pretty fast when you try to decide which sorts of errors are
> more important than others.

+1.  I hate tools that try to read your mind.  They invariably fail
at that.  The current behaviour is 100% correct and unambiguous.

Cheers,
Peter
-- 
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
-- Donald Knuth

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