Re: [GENERAL] warm standby - apply wal archives

[MirrorX]

The network bandwidth between the servers is definitely not an issue. What is
bothering me is the big size of the wal archives, which goes up to 200GB per
day and if the standby server will be able to replay all these files. The
argument that; since the master can do it and also do various other tasks at
the same time, and since the secondary is identical to the first, so he
should be able to do that seems valid, so i will give it a try and let you
know about the results. In the meantime if there are any other
ideas/suggestions etc please let me know. thx to all

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/warm-standby-apply-wal-archives-tp4770567p4773498.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] Query runs in 335ms; function in 100,239ms : date problem?

[Torsten Zühlsdorff]

Rory Campbell-Lange schrieb:


Try to run it as a prepared query - I guess you'll get about the same run
time as with the function (i.e. over 100 seconds).

The prepared query runs in almost exactly the same time as the function,
but thanks for the suggestion. A very useful aspect of it is that I was
able to get the EXPLAIN output which I guess gives a fairly good picture
of the plan used for the function.

The explain output is here:
http://campbell-lange.net/media/files/explain.txt.html

I'm inexperienced in reading EXPLAIN output, but it looks like the
Nested Loop Semi Join at line 72 is running very slowly.


I added in more filtering conditions to the clause at line 72 and the
prepared statement dropped in runtime to 24043.902 ms. Unfortunately the
function ran slower -- 47957.796 -- but even that is a 50% improvement.


Also declare your function as STABLE. This can trigger an speed-increase.

Greetings,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.


--
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] warm standby - apply wal archives

[Venkat Balaji]

Considering the size of WAL archives = 200GB

Compressing them using gzip (you can use this command in a shell script and
place it in archive_command as well) would possibly reduce the size to as
low as 10 - 20 GB.

Please let us know the results.

Thanks
Venkat

On Tue, Sep 6, 2011 at 1:03 PM, MirrorX  wrote:

> The network bandwidth between the servers is definitely not an issue. What
> is
> bothering me is the big size of the wal archives, which goes up to 200GB
> per
> day and if the standby server will be able to replay all these files. The
> argument that; since the master can do it and also do various other tasks
> at
> the same time, and since the secondary is identical to the first, so he
> should be able to do that seems valid, so i will give it a try and let you
> know about the results. In the meantime if there are any other
> ideas/suggestions etc please let me know. thx to all
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/warm-standby-apply-wal-archives-tp4770567p4773498.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] warm standby - apply wal archives

[MirrorX]

the network transfer does not bother me for now. i will first try to do the
whole procedure without compression, so as not to waste any cpu util and
time for compressing and decompressing. through the 4Gbps ethernet, the
200GB of the day can be transferred in a matter of minutes. so i will try it
and get back with the results. thx to all

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/warm-standby-apply-wal-archives-tp4770567p4773807.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] [pgadmin-support] Help for Migration

[Mamatha_Kagathi_Chan]

Hi,

I am trying to migrate a very small MS SQL Server Database (with 200 records 
max, 20 tables, 10 stored procedures) to PostgreSQL. I tried browsing through 
internet to find technical steps but I found some blogs with vague discussion 
for same. Kindly let me know where to look for the information or even better 
if I can get some document from the community for the same.

Thanks,
Mamatha

-- 
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] [pgadmin-support] Help for Migration

[David Fetter]

On Tue, Sep 06, 2011 at 12:17:28PM +, mamatha_kagathi_c...@dell.com wrote:
> Hi,
> 
> I am trying to migrate a very small MS SQL Server Database (with 200
> records max, 20 tables, 10 stored procedures) to PostgreSQL. I tried
> browsing through internet to find technical steps but I found some
> blogs with vague discussion for same. Kindly let me know where to
> look for the information or even better if I can get some document
> from the community for the same.

The table structures shouldn't be a problem to do fairly mechanically,
and at worst you can simply hand-type in the data.  The stored
procedures will be a problem at two levels:

1.  You'll have to translate them into a language PostgreSQL can use
from (I'm guessing here, but it's usually a good guess in these
situations) T-SQL.

2.  PostgreSQL functions, which are similar in many ways to stored
procedures, have a fundamental difference: they can't control
transactions.  Any stored procedures that have a COMMIT or ROLLBACK in
them will have to be re-architected in a fundamental way.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] UPDATE using query; per-row function calling problem

[David Johnston]

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rory Campbell-Lange
Sent: Monday, September 05, 2011 4:55 PM
To: David Johnston
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] UPDATE using query; per-row function calling problem

On 02/09/11, David Johnston (pol...@yahoo.com) wrote:
> > In my "-1" example, am I right in assuming that I created a 
> > correlated subquery rather than an correlated one? I'm confused 
> > about the difference.
> > 
> Correlated: has a where clause that references the outer query
> Un-correlated: not correlated
> 
> Because of the where clause a correlated sub-query will return a 
> different record for each row whereas an un-correlated sub-query will 
> return the same record for all rows since the where clause (if any) is 
> constant.

Hi David -- thanks for the clarification. However I'm still a little
confused. As I understand it the following is a un-correlated sub-query:

UPDATE
slots
SET
a = 'a'
,b = (SELECT uuid_generate_v1())
WHERE
c = TRUE;

and the following, without a 'WHERE', is a correlated sub-query:

UPDATE
slots
SET
a = 'a'
,b = uuid_generate_v1()
WHERE
c = TRUE;

Is the point that the lower is not a sub-query at all?


--

Correct, the second query uses a simple function call to set the value of
"b";  Using your example you would need to do something like:

UPDATE
  slots
SET
  a = 'a'
  ,b = (SELECT something FROM somewhere WHERE somewhere.a = slots.a)
WHERE
  c = TRUE;

to use a correlated sub-query.  Since "uuid_generate_v1()" doesn't naturally
link to slots (or anything else) there isn't any meaningful way to use a
correlated sub-query in this situation.  Since you are using a function (as
opposed to a direct TABLE/VIEW) the use of a sub-query is pointless and,
apparently, results in optimizations that are undesirable.

David J.



-- 
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] Query runs in 335ms; function in 100,239ms : date problem?

[Tom Lane]

"Tomas Vondra"  writes:
> On 6 Září 2011, 0:27, Rory Campbell-Lange wrote:
>> The prepared query runs in almost exactly the same time as the function,
>> but thanks for the suggestion. A very useful aspect of it is that I was
>> able to get the EXPLAIN output which I guess gives a fairly good picture
>> of the plan used for the function.

> Well, my point was that the queries wrapped in functions are executed just
> like prepared statements. And because prepared queries do not use
> parameter values to optimize the plan, the result may be worse compared to
> queries with literal parameters. So I was not expecting an improvement, I
> was merely trying to show the problem.

Right.  The actual fix is to use EXECUTE so you force a new plan to be
generated each time.  If you use EXECUTE USING to insert the parameter
values, you can avoid most of the notational mess this would otherwise
imply, as well as the risk of SQL-injection bugs from failing to quote
parameter values safely.

I'm hoping that 9.2 will be smart enough to not need such workarounds,
but that's where things stand at the moment.

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

[GENERAL] Demoting master to slave without an rsync...is it safe?

[Chris Redekop]

I have a master+slave set up using asynchronous streaming replication.  If I
do a graceful (-fast) shutdown of the master, and then promote the slave to
master, my understanding is that I should not have any data loss.  At that
point in order to bring the old master back up as a slave the docs say I
should do a base-backup etchowever I've found that simply
setting recovery_target_timeline='latest' does allow the old master to start
back up as a slave, and everything appears to be happy, all data seems to be
synced properly.  My question is, is this safe to do?  It's a very
attractive option as it removes a significant amount of load from the master
server for planned switch-overs (maintenance/upgrades/whatever).  Thanks!

[GENERAL] Backend process that won't die

[Susan Cassidy]

I have a couple of backend processes that are "stuck", and do not respond to a 
pg_cancel_backend.  This is PostgreSQL 8.3.5.  The pg_cancel_backend returns 
true, but the process keeps running.  I have also done a "kill 12345" from the 
command-line, with no effect.

The processes are running a "select function_x" statement that normally takes a 
fraction of a second to run.

No locks are shown when I do:
select relname,pg_locks.* from pg_class,pg_locks where relfilenode=relation and 
not granted;

We had a database crash last week, and had to reindex a bunch of tables, but 
this function has been working for several days on the same tables that should 
be being used by the function_x function.

Any ideas on how to get the processes to go away?

They are eating cpu cycles, for no good reason:
postgres 28396 85.0  1.4 4420768 242224 ?  Ss   Sep03 3193:40 postgres: 
userxx dbname1 172.27.43.9(1160) SELECT


Thanks,
Susan

Re: [GENERAL] Demoting master to slave without an rsync...is it safe?

[Alex Lai]

Chris Redekop wrote:
I have a master+slave set up using asynchronous streaming replication. 
 If I do a graceful (-fast) shutdown of the master, and then promote 
the slave to master, my understanding is that I should not have any 
data loss.  At that point in order to bring the old master back up as 
a slave the docs say I should do a base-backup etchowever I've 
found that simply setting recovery_target_timeline='latest' does allow 
the old master to start back up as a slave, and everything appears to 
be happy, all data seems to be synced properly.  My question is, is 
this safe to do?  It's a very attractive option as it removes a 
significant amount of load from the master server for planned 
switch-overs (maintenance/upgrades/whatever).  Thanks! 

Hi Chris,

I don't have an answer for you, but I am doing the kind of tests you are 
doing.

I tested the following cases:
case-1  Shutdown master, slave still available for read-only
  Then started master, both master and slave works properly
  
case-2  Shutdown slave while master still running and lots of inserts 
and updates
   After 10 minutes, started slave, and it catched up with the 
changes within minutes


case-3  Assume master is crash, shutdown master, slave in read-only and 
waiting for the WAL update.
   Then, I touch the recovery failover file indicated in 
recovery.conf.

   The slave changed into read-write mode.
   A lots of inserts and update to the slave now become 
master.  (I am not sure its a good idea, but in real case if master down 
I want the slave can act as master while waiting for the another slave 
available.)
   I am not sure the changes are updated to arch_replicate 
directory where  are the  old  Master write  to it.
   I real case I need to make sure no changes to the new master 
right after the fail over.
   Then copy the postgresql.conf and recovery.conf to the 
$PGDATA of the new master then restart  the database.
   Then resyn $PGDATA to another location of the old master 
host, then copy slave conf to the new $PGDATA.  I have about 1TB of 
database can take up to 30 minues.

   Then restart the new slave, every thing works.

I have two questions:
(1) Did you set recovery_target_timeline='latest' in both master and slave?
(2) Did you make any changes after promote the slave to be master?
  
--


Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc. 
7515 Mission Drive, 
Suite A100 Lanham, MD 20706 
301-352-4657 (phone) 
301-352-0437 (fax) 
a...@sesda2.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] Backend process that won't die

[Tom Lane]

Susan Cassidy  writes:
> I have a couple of backend processes that are "stuck", and do not respond to 
> a pg_cancel_backend.  This is PostgreSQL 8.3.5.  The pg_cancel_backend 
> returns true, but the process keeps running.  I have also done a "kill 12345" 
> from the command-line, with no effect.

> We had a database crash last week, and had to reindex a bunch of tables, but 
> this function has been working for several days on the same tables that 
> should be being used by the function_x function.

By "this function" you mean that the reindex is not finished, but
nonetheless you have got regular queries running with the corrupted
indexes?

> Any ideas on how to get the processes to go away?

It seems like a good bet that they're chasing circular links in the
corrupted indexes.  "kill -9" would get rid of them, but it would force
a database-wide restart, which would also take out your reindex process,
so maybe that wouldn't be a good idea.

If they're significantly interfering with the progress of the reindex
then maybe you should bite the bullet and kill them anyway.  Otherwise
I'd be inclined to let them go until you can afford a restart.

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] Backend process that won't die

[Susan Cassidy]

-Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
> Sent: Tuesday, September 06, 2011 9:57 AM
> To: Susan Cassidy
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Backend process that won't die 

> Susan Cassidy  writes:
>> I have a couple of backend processes that are "stuck", and do not respond to 
>> a pg_cancel_backend.  This is PostgreSQL 8.3.5.  The pg_cancel_backend 
>> returns true, but the process keeps running.  I have also done a "kill 
>> 12345" from the command-line, with no effect.

>> We had a database crash last week, and had to reindex a bunch of tables, but 
>> this function has been working for several days on the same tables that 
>> should be being used by the function_x function.

> By "this function" you mean that the reindex is not finished, but
nonetheless you have got regular queries running with the corrupted
indexes?

No, the reindexes that I knew were needed have already been done.

> Any ideas on how to get the processes to go away?

> It seems like a good bet that they're chasing circular links in the
corrupted indexes.  "kill -9" would get rid of them, but it would force
a database-wide restart, which would also take out your reindex process,
so maybe that wouldn't be a good idea.

> If they're significantly interfering with the progress of the reindex
then maybe you should bite the bullet and kill them anyway.  Otherwise
I'd be inclined to let them go until you can afford a restart.

>   regards, tom lane

Without any error messages about indexes, which I have not seen lately, I have 
no idea which indexes still might need rebuilding.

So, you think I should go ahead and kill -9 the "stuck" processes, and let the 
database restart?  It is a 2-system cluster, with failover, so I'll let the IT 
guy handle that, I guess.

Thanks,
Susan


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

[GENERAL] Deleting one of 2 identical records

[Gauthier, Dave]

Hi:

If I have a table that has 2 records which are identical with regard to all 
their column values, is there a way to delete one of them, leaving one 
remaining?  Is there some unique record_id key of some sort I can use for 
somethign like this?

Thanks in Advance!

Re: [GENERAL] Deleting one of 2 identical records

[Thom Brown]

On 6 September 2011 18:39, Gauthier, Dave  wrote:

> Hi:
>
> ** **
>
> If I have a table that has 2 records which are identical with regard to all
> their column values, is there a way to delete one of them, leaving one
> remaining?  Is there some unique record_id key of some sort I can use for
> somethign like this?
>
> ** **
>
> Thanks in Advance!
>

Yes, identify them by their ctid value.

So get the ctids by running:

SELECT ctid, *
FROM my_table
WHERE 

You will see entries which look like "(7296,11)".

You can then delete the row by referencing it in the DELETE statement.  For
example:

DELETE FROM my_table
WHERE ctid = '(7296,11)';

It's a shame we don't have a LIMIT on the DELETE clause (looks at hackers).

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [GENERAL] Deleting one of 2 identical records

[Andy Colson]

On 9/6/2011 12:39 PM, Gauthier, Dave wrote:

Hi:

If I have a table that has 2 records which are identical with regard to
all their column values, is there a way to delete one of them, leaving
one remaining? Is there some unique record_id key of some sort I can use
for somethign like this?

Thanks in Advance!



Not easily that I know of.  I have two thoughts:

1)
create table junk (like orig);
insert into junk select distinct from orig;
delete from orig where exists(select from junk);
insert into orig select * from junk;

2)
alter table orig add uid integer;
create sequence bob;
update orig set uid = nextval('bob');
drop sequence bob;
-- magic to delet using uid

Ah, Thom just answered.  I like his better, but I'll post this just for 
completeness...


-Andy

--
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] Deleting one of 2 identical records

[Andy Colson]

On 9/6/2011 12:44 PM, Thom Brown wrote:

On 6 September 2011 18:39, Gauthier, Dave mailto:dave.gauth...@intel.com>> wrote:

Hi:

__ __

If I have a table that has 2 records which are identical with regard
to all their column values, is there a way to delete one of them,
leaving one remaining?  Is there some unique record_id key of some
sort I can use for somethign like this?

__ __

Thanks in Advance!


Yes, identify them by their ctid value.

So get the ctids by running:

SELECT ctid, *
FROM my_table
WHERE 

You will see entries which look like "(7296,11)".

You can then delete the row by referencing it in the DELETE statement.
  For example:

DELETE FROM my_table
WHERE ctid = '(7296,11)';

It's a shame we don't have a LIMIT on the DELETE clause (looks at hackers).

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


I wonder.. using the new writeable cte's, could you:

with x (
  -- id = 5 has two identical rows, but limit 1
  select * from orig where id = 5 limit 1;
)
delete from x;

-Andy

--
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] Deleting one of 2 identical records

[Andreas Kretschmer]

Gauthier, Dave  wrote:

> Hi:
> 
>  
> 
> If I have a table that has 2 records which are identical with regard to all
> their column values, is there a way to delete one of them, leaving one
> remaining?  Is there some unique record_id key of some sort I can use for
> somethign like this?

Yes, use the CTID-column.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
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] Deleting one of 2 identical records

[Gauthier, Dave]

The identification and deleting of the records using ctids seems to have worked 
fine.  
Thanks !

-Original Message-
From: Andy Colson [mailto:a...@squeakycode.net] 
Sent: Tuesday, September 06, 2011 1:55 PM
To: Thom Brown
Cc: Gauthier, Dave; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Deleting one of 2 identical records

On 9/6/2011 12:44 PM, Thom Brown wrote:
> On 6 September 2011 18:39, Gauthier, Dave  > wrote:
>
> Hi:
>
> __ __
>
> If I have a table that has 2 records which are identical with regard
> to all their column values, is there a way to delete one of them,
> leaving one remaining?  Is there some unique record_id key of some
> sort I can use for somethign like this?
>
> __ __
>
> Thanks in Advance!
>
>
> Yes, identify them by their ctid value.
>
> So get the ctids by running:
>
> SELECT ctid, *
> FROM my_table
> WHERE 
>
> You will see entries which look like "(7296,11)".
>
> You can then delete the row by referencing it in the DELETE statement.
>   For example:
>
> DELETE FROM my_table
> WHERE ctid = '(7296,11)';
>
> It's a shame we don't have a LIMIT on the DELETE clause (looks at hackers).
>
> --
> Thom Brown
> Twitter: @darkixion
> IRC (freenode): dark_ixion
> Registered Linux user: #516935
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

I wonder.. using the new writeable cte's, could you:

with x (
   -- id = 5 has two identical rows, but limit 1
   select * from orig where id = 5 limit 1;
)
delete from x;

-Andy

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

[GENERAL] Which perl works with pg9.1

[pasman pasmański]

Hi.
I have installed activeperl 5.10. It works with pg8.4, but not with
9.1rc1 (win32 installer).
Do this version need perl 5.12?

-- 

pasman

-- 
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] Which perl works with pg9.1

[Bruce Momjian]

pasman pasma?ski wrote:
> Hi.
> I have installed activeperl 5.10. It works with pg8.4, but not with
> 9.1rc1 (win32 installer).
> Do this version need perl 5.12?

Odd, the source code only requires Perl 5.8.   Not sure why win32 would
need a later version.  What error are you seeing?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] conditional insert

[Lincoln Yeoh]

At 07:02 PM 9/5/2011, J. Hondius wrote:

I agree that there are better ways to do this.
But for me this works. (legacy driven situation)

INSERT INTO tbinitialisatie (col1, col2)
 SELECT 'x', 'y'
 FROM tbinitialisatie
 WHERE not exists (select * from tbinitialisatie where col1 = 'x' 
and col2 = 'y')

 LIMIT 1


Hi,

That does not work 100%. Try it with two psql instances.

Do:
*** psql #1
begin;
INSERT INTO tbinitialisatie (col1, col2)
 SELECT 'x', 'y'
 FROM tbinitialisatie
 WHERE not exists (select * from tbinitialisatie where col1 = 'x' 
and col2 = 'y')

 LIMIT 1 ;

*** psql #2
begin;
INSERT INTO tbinitialisatie (col1, col2)
 SELECT 'x', 'y'
 FROM tbinitialisatie
 WHERE not exists (select * from tbinitialisatie where col1 = 'x' 
and col2 = 'y')

 LIMIT 1 ;
commit;

*** psql #1
commit;

You should find duplicate inserts.

In most cases the "begin" and "commit" are very close together so you 
won't notice the problem. But one day you might get unlucky.


Your options are:
a) put a unique constraint and handle the insert errors when they occur
b) lock the entire table first (affects performance: blocks all 
selects on that table)
c) use a lock elsewhere (but this requires all applications using the 
database to cooperate and use the lock).
d) wait for SQL MERGE to be implemented ( but from what I see the 
current proposal seems to require a) or b) anyway: 
http://wiki.postgresql.org/wiki/SQL_MERGE )


You could do both a) and b) too. Or both a) and c) (if you don't want 
insert errors in the cooperating apps and want to allow other selects 
during the transaction).


Regards,
Link.


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

[GENERAL] Advice on HA option

[hyelluas]

Hello,

I need to implement HA for the appliance that is sold to the customer  - our
application has no DBA and must be very stable and self  recoverable.

We are running PostgreSQL 8.4. 

Is the warm standby a good solution for us?

Thank you for all the suggestions.

Helen  


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Advice-on-HA-option-tp4775605p4775605.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] conditional insert

[Merlin Moncure]

On Tue, Sep 6, 2011 at 1:50 PM, Lincoln Yeoh  wrote:
> At 07:02 PM 9/5/2011, J. Hondius wrote:
>>
>> I agree that there are better ways to do this.
>> But for me this works. (legacy driven situation)
>>
>> INSERT INTO tbinitialisatie (col1, col2)
>>  SELECT 'x', 'y'
>>  FROM tbinitialisatie
>>  WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2
>> = 'y')
>>  LIMIT 1
>
> Hi,
>
> That does not work 100%. Try it with two psql instances.
>
> Do:
> *** psql #1
> begin;
> INSERT INTO tbinitialisatie (col1, col2)
>  SELECT 'x', 'y'
>  FROM tbinitialisatie
>  WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2 =
> 'y')
>  LIMIT 1 ;
>
> *** psql #2
> begin;
> INSERT INTO tbinitialisatie (col1, col2)
>  SELECT 'x', 'y'
>  FROM tbinitialisatie
>  WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2 =
> 'y')
>  LIMIT 1 ;
> commit;
>
> *** psql #1
> commit;
>
> You should find duplicate inserts.
>
> In most cases the "begin" and "commit" are very close together so you won't
> notice the problem. But one day you might get unlucky.
>
> Your options are:
> a) put a unique constraint and handle the insert errors when they occur
> b) lock the entire table first (affects performance: blocks all selects on
> that table)
> c) use a lock elsewhere (but this requires all applications using the
> database to cooperate and use the lock).
> d) wait for SQL MERGE to be implemented ( but from what I see the current
> proposal seems to require a) or b) anyway:
> http://wiki.postgresql.org/wiki/SQL_MERGE )

b) doesn't block reads if you lock in EXCLUSIVE mode.  a) is the best
way to go if you prefer to handle errors on the client and/or
concurrency is important...c) otherwise.

merlin

-- 
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] Advice on HA option

[Merlin Moncure]

On Tue, Sep 6, 2011 at 2:06 PM, hyelluas  wrote:
> Hello,
>
> I need to implement HA for the appliance that is sold to the customer  - our
> application has no DBA and must be very stable and self  recoverable.
>
> We are running PostgreSQL 8.4.
>
> Is the warm standby a good solution for us?

It can work, but hands free administration will be a lot easier with
9.0 SR -- perhaps enough so to justify looking at an upgrade.  If you
are stuck on 8.4, take a look at some helper tools, such as skype's
walmgr.

merlin

-- 
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] Demoting master to slave without an rsync...is it safe?

[Chris Redekop]

>
>
> I have two questions:
> (1) Did you set recovery_target_timeline='**latest' in both master and
> slave?
>

Yesbut it's in recovery.conf so it only really applies to whichever
server is currently the slave...


> (2) Did you make any changes after promote the slave to be master?


Yes, somehowever I'm not sure I've done enough changes to have the slave
rotate+archive an xlog before bringing the old master back up as a
slaveI would assume that wouldn't make a difference but it's something
to test I guess

Re: [GENERAL] conditional insert

[Merlin Moncure]

On Tue, Sep 6, 2011 at 3:45 PM, Merlin Moncure  wrote:
> On Tue, Sep 6, 2011 at 1:50 PM, Lincoln Yeoh  wrote:
>> At 07:02 PM 9/5/2011, J. Hondius wrote:
>>>
>>> I agree that there are better ways to do this.
>>> But for me this works. (legacy driven situation)
>>>
>>> INSERT INTO tbinitialisatie (col1, col2)
>>>  SELECT 'x', 'y'
>>>  FROM tbinitialisatie
>>>  WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2
>>> = 'y')
>>>  LIMIT 1
>>
>> Hi,
>>
>> That does not work 100%. Try it with two psql instances.
>>
>> Do:
>> *** psql #1
>> begin;
>> INSERT INTO tbinitialisatie (col1, col2)
>>  SELECT 'x', 'y'
>>  FROM tbinitialisatie
>>  WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2 =
>> 'y')
>>  LIMIT 1 ;
>>
>> *** psql #2
>> begin;
>> INSERT INTO tbinitialisatie (col1, col2)
>>  SELECT 'x', 'y'
>>  FROM tbinitialisatie
>>  WHERE not exists (select * from tbinitialisatie where col1 = 'x' and col2 =
>> 'y')
>>  LIMIT 1 ;
>> commit;
>>
>> *** psql #1
>> commit;
>>
>> You should find duplicate inserts.
>>
>> In most cases the "begin" and "commit" are very close together so you won't
>> notice the problem. But one day you might get unlucky.
>>
>> Your options are:
>> a) put a unique constraint and handle the insert errors when they occur
>> b) lock the entire table first (affects performance: blocks all selects on
>> that table)
>> c) use a lock elsewhere (but this requires all applications using the
>> database to cooperate and use the lock).
>> d) wait for SQL MERGE to be implemented ( but from what I see the current
>> proposal seems to require a) or b) anyway:
>> http://wiki.postgresql.org/wiki/SQL_MERGE )
>
> b) doesn't block reads if you lock in EXCLUSIVE mode.  a) is the best
> way to go if you prefer to handle errors on the client and/or
> concurrency is important...c) otherwise.

whoops!  meant to say b) otherwise! As far as c) goes, that is
essentially an advisory lock for the purpose -- using advisory locks
in place of mvcc locks is pretty weak sauce -- they should be used
when what you are locking doesn't follow mvcc rules.

merlin

-- 
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] Deleting one of 2 identical records

[David Johnston]

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andy Colson
Sent: Tuesday, September 06, 2011 1:55 PM
To: Thom Brown
Cc: Gauthier, Dave; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Deleting one of 2 identical records


I wonder.. using the new writeable cte's, could you:

with x (
   -- id = 5 has two identical rows, but limit 1
   select * from orig where id = 5 limit 1;
)
delete from x;

-Andy


--

By my understanding it is a writeable CTE because the statement inside the
CTE can now be INSERT/UPDATE/DELETE (in addition to the SELECT - read only -
statement).

A CTE is, in some ways, like an immediately materialized view. Any reference
to it does not affect the source tables; thus your example likely would not
work.  It isn't a RULE where "x" is simply an alias for "orig".

The real problem is not the language but the table design.  The idea of
"true duplicates" is generally problematic but when it does occur it is
advisable to introduce some kind of artificial key/sequence to allow for
direct selection of the row without resorting to internals.  Then, it is
simply to use the full power of the SELECT statement (with Window functions
and CTEs) to identify the rows that are to be deleted and feed the PK from
those rows into the DELETE's WHERE clause using a sub-query.

David J.




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

[GENERAL] Complex query question

[Mike Orr]

I have a complex query question whose answer I think would help me to
understand subselects and aggregates better. I have a table with four
columns of interest:

id (int primary key), loc_title (varchar null), loc_value (float
null), loc_unit (varchar null)

I want the output columns to be:
(1) each distinct value of loc_title, sorted
(2) an id of a record containing that loc_title
(3) the loc_value for the record in column 2
(4) the loc_unit for the record in column 2

I don't care as much how the records for columns 2-4 are chosen. It
could be max(loc_value), min(id), or something else. I just need some
sample records to test my program against.

Is this something I should be able to do with a single query with a
subselect, or is it too much for one query? I tried a few ways and
none of them were syntactically valid.

-- 
Mike Orr 

-- 
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] Deleting one of 2 identical records

[Thom Brown]

On 6 September 2011 19:00, Gauthier, Dave  wrote:

> The identification and deleting of the records using ctids seems to have
> worked fine.
> Thanks !
>

Alternative you could do something like this:

WITH keep AS (
SELECT
my_table AS duplicate_row,
min(ctid) AS keep,
count(*)
FROM my_table
GROUP BY my_table
HAVING count(*) > 1
)
DELETE FROM my_table
USING keep
WHERE
my_table = keep.duplicate_row
AND
my_table.ctid != keep
RETURNING my_table.ctid, my_table.*;

This would delete all duplicate rows from the table and just keep whichever
row appears first in the table before its duplicates.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [GENERAL] Deleting one of 2 identical records

[salah jubeh]

Hello Thom.

what is the meaning of  

select table_name from table_name   
Also is this a common behavior of all Databases i.e. oracle , Microsoft ,...etc 
 . i.e is this is the standard behavior 

I think this is a good way to find duplicates in general, I will write a 
routine to compare all the columns by excluding the primary key which is serial

Thanks in advance 

Regards 


From: Thom Brown 
To: "Gauthier, Dave" 
Cc: Andy Colson ; "pgsql-general@postgresql.org" 

Sent: Wednesday, September 7, 2011 12:50 AM
Subject: Re: [GENERAL] Deleting one of 2 identical records


On 6 September 2011 19:00, Gauthier, Dave  wrote:

The identification and deleting of the records using ctids seems to have worked 
fine.
>Thanks !
>

Alternative you could do something like this:

WITH keep AS (
    SELECT
        my_table AS duplicate_row,
        min(ctid) AS keep,
        count(*)
    FROM my_table
    GROUP BY my_table
    HAVING count(*) > 1
)
DELETE FROM my_table
USING keep
WHERE
    my_table = keep.duplicate_row
AND
    my_table.ctid != keep
RETURNING my_table.ctid, my_table.*;

This would delete all duplicate rows from the table and just keep whichever row 
appears first in the table before its duplicates.
-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [GENERAL] Deleting one of 2 identical records

[Thom Brown]

On 7 September 2011 00:55, salah jubeh  wrote:

>
> Hello Thom.
>
> what is the meaning of
>
> select table_name from table_name
>
> Also is this a common behavior of all Databases i.e. oracle , Microsoft
> ,...etc  . i.e is this is the standard behavior
>
> I think this is a good way to find duplicates in general, I will write a
> routine to compare all the columns by excluding the primary key which is
> serial
>
> Thanks in advance
>

Well I just put table_name as a placeholder for the actual name of the table
you wish to remove duplicates from.

No, you can't run this query on other databases, particularly because ctids
are specific to PostgreSQL.  Other databases will use different methods to
this one, if they have any at all.  With Oracle you'd probably use ROWNUM
somehow, and SQL Server will likely use some awful multi-query technique
involving creating temporary tables, copying distinct rows from the
duplicate set to another table, deleting it from the original and copying
back.  Can't say for sure though since I haven't used it in quite a while.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [GENERAL] Which perl works with pg9.1

[Craig Ringer]

On 7/09/2011 3:00 AM, Bruce Momjian wrote:

pasman pasma�ski wrote:

Hi.
I have installed activeperl 5.10. It works with pg8.4, but not with
9.1rc1 (win32 installer).
Do this version need perl 5.12?

Odd, the source code only requires Perl 5.8.   Not sure why win32 would
need a later version.


The win32 packages require whatever version of Perl they were built 
against, AFAIK. Perl DLLs are only binary compatible within a major 
version, and I think the DLL naming changes too.


I thought the required version of Perl was mentioned by the installer or 
post-install notes somewhere, but can't immediately find the info.


--
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] Which perl works with pg9.1

[Bruce Momjian]

Craig Ringer wrote:
> On 7/09/2011 3:00 AM, Bruce Momjian wrote:
> > pasman pasma?ski wrote:
> >> Hi.
> >> I have installed activeperl 5.10. It works with pg8.4, but not with
> >> 9.1rc1 (win32 installer).
> >> Do this version need perl 5.12?
> > Odd, the source code only requires Perl 5.8.   Not sure why win32 would
> > need a later version.
> 
> The win32 packages require whatever version of Perl they were built 
> against, AFAIK. Perl DLLs are only binary compatible within a major 
> version, and I think the DLL naming changes too.
> 
> I thought the required version of Perl was mentioned by the installer or 
> post-install notes somewhere, but can't immediately find the info.

Oh, yes, that is right and isn't tested very well at runtime, as far as
I remember.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

[GENERAL] Disconnecting and cancelling a statement

[Jeff Davis]

I'm looking for a reliable way for a client to disconnect from a backend
such that any running query is terminated.

Right now, PostgreSQL doesn't seem to make an effort to detect a client
cancellation. For instance, if you do a "select pg_sleep(1000)" and then
kill -9 the client, the SELECT will remain running. That's not so much
of a problem for sleep, but if it's doing real work, then it's wasting a
lot of effort (and perhaps not terminating in any reasonable amount of
time).

And even if the client makes an effort to cancel and there are no major
network problems, then I still don't see a good method. Because the
cancellation request is sent out-of-band to the postmaster, then it's in
a race with the (asynchronous) query that you just sent. If the signal
reaches the backend before the query does, then the SIGINT becomes a
no-op (because it's still idle), and then the query arrives, and then
the client does PQfinish, the backend will still be alive doing a bunch
of needless work.

I have attached a simple C program that demonstrates the problem (must
be run from same host as PG because it uses SIGSTOP/SIGCONT to reproduce
race). After you run it, see how the "SELECT pg_sleep(1000)" is still
running, despite the client being disconnected.

There are two solutions that I see, neither of which look great:

1. Make a separate connection, and issue pg_terminate_backend() before
PQfinish. It works because a SIGTERM will not be a no-op on an idle
backend. This solution requires superuser privileges (not acceptable),
plus it's a little ugly.

2. Keep sending cancellation requests in a loop with a delay, consuming
input each time until PQisBusy() returns false. Obviously fairly ugly
and error prone, but is somewhat acceptable.

Any other ideas? There is no PQterminate, unforunately.
statement_timeout is not feasible, as the statement might legitimately
run for a very long time.

This is all compounded by the fact that terminating the backend directly
is no guarantee of proper shutdown, either:

http://archives.postgresql.org/pgsql-general/2009-03/msg00434.php

That means that there is no way to nicely and reliably shut down
postgresql from the client alone, nor from the server alone. The only
way is to send a SIGTERM to the backend *and* terminate the client
connection. Unless someone has a better idea?

Thoughts?

Regards,
Jeff Davis

/*
 * Only works if run on the same host as postgres; can't work over a
 * network because we need to be able to signal backend directly.
 */

#include 
#include 
#include 
#include 
#include 

#define ERRBUF_SIZE 256

int main(int argc, char *argv[])
{
	char		*conninfo;
	PGconn		*conn;
	PGcancel	*cancel;
	char		 errbuf[ERRBUF_SIZE];
	pid_t		 be_pid;

	if (argc < 2)
	{
		fprintf(stderr, "must supply connection string as argument");
		exit(1);
	}

	conninfo = argv[1];

	conn = PQconnectdb(conninfo);

	/* Check to see that the backend connection was successfully made */
	if (PQstatus(conn) != CONNECTION_OK)
	{
		fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
		PQfinish(conn);
		exit(1);
	}

	cancel = PQgetCancel(conn);

	be_pid = PQbackendPID(conn);

	printf("sending SIGSTOP to %d\n", be_pid);
	kill(be_pid, SIGSTOP);

	printf("sending query\n");
	PQsendQuery(conn, "select pg_sleep(1000)");

	printf("sending cancel\n");
	PQcancel(cancel, errbuf, ERRBUF_SIZE);

	printf("sending SIGCONT to %d\n", be_pid);
	kill(be_pid, SIGCONT);

	printf("disconnecting\n");
	PQfreeCancel(cancel);

	PQfinish(conn);

	return 0;
}

-- 
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] Which perl works with pg9.1

[pasman pasmański]

>> The win32 packages require whatever version of Perl they were built
>> against, AFAIK. Perl DLLs are only binary compatible within a major
>> version, and I think the DLL naming changes too.
>>


Looks like 9.1 binaries are linked with perl 5.14.
Thanks for answers


pasman

-- 
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] Disconnecting and cancelling a statement

[Craig Ringer]

On 7/09/2011 10:00 AM, Jeff Davis wrote:

I'm looking for a reliable way for a client to disconnect from a backend
such that any running query is terminated.

Right now, PostgreSQL doesn't seem to make an effort to detect a client
cancellation. For instance, if you do a "select pg_sleep(1000)" and then
kill -9 the client, the SELECT will remain running.


pg_sleep isn't a good test. In fact, Pg _does_ make an effort to detect 
when a client dies, and will try to terminate the query. It does this 
via explicit checks at various points, none of which are reached while 
Pg is idling in a sleep() syscall. During more typical query processing 
you'll usually find that a query gets terminated when the client dies.


Pg must find out when the client dies, though. If the client just goes 
away - such as with a laptop on wifi that wanders out of range - it 
won't know about it until it next attempts to send data to the client. 
To address this, if you want reliable client dropout detection, you need 
to enable tcp keepalives and set them to quite aggressive so the OS will 
periodically test the connection for aliveness.

And even if the client makes an effort to cancel and there are no major
network problems, then I still don't see a good method. Because the
cancellation request is sent out-of-band to the postmaster, then it's in
a race with the (asynchronous) query that you just sent.


Yeah, this bit frustrates me too. Not only is the request async, but it 
requires the establishment of a new full-featured database connection 
first. That's a mess with pooling and sharding/clustering setups where 
there's no guarantee the connection will go to the same host. It's also 
expensive in terms of round trips, setup work on the server, and sheer 
time taken. I found supporting query cancels to be a right PITA and was 
never satisifed with the solution I ended up with in my (Java/PgJDBC) app.


I'd love to see Pg accept OOB cancel requests done via lightweight 
connections that don't go through the whole setup process. If the server 
sent a statement "cookie" when executing a statement, the client could 
hang onto that and use it to issue a cancel for that statement and only 
that statement by establishing a new connection to the server and 
sending that cookie rather than the usual negotiation and auth process. 
There'd be no need to go through full auth or even bother with SSL, 
because it's a one-time random (or hash-based) code. Pooling systems 
could send this to _all_ servers, or it could be prefixed with a server 
identifier that helped poolers route it to the right server.


The same statement cookie could be used to support full connection-based 
cancellation by passing it to a "pg_cancel_statement('blahcookieblah');" 
function.


There's probably no safe way (short of a fully threaded backend - and I 
did say "safe") to avoid the need for a new control connection and use a 
single tcp connection for everything, though. Sending the cancel message 
in-band via the regular connection will work for non-SSL connections, 
since the server can periodically check for new input and process it 
without risking blocking. This cannot work with SSL though, as there's 
no way to tell if data waiting on the socket is a whole SSL message or 
only a partial one that'll block waiting for new input when read. Even 
were that problem worked around with a separate socket reader thread 
that _can_ block, the backend still has to do periodic checks for input, 
so it'd suffer from some of the same issues as the current approach.



Any other ideas? There is no PQterminate, unforunately.
statement_timeout is not feasible, as the statement might legitimately
run for a very long time.


A real fix requires backend enhancements like unique statement 
identifiers, IMO.


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