Re: [GENERAL] Lock Management: Waiting on locks

[Bill Moran]

On Wed, 26 Nov 2014 02:37:51 +0530
Dev Kumkar  wrote:

> Am debugging a race condition scenario where multiple transaction are
> running in parallel and there are insert/update these transactions are
> performing.
> 
> I was able to identify the blocking query and blocked query using following
> SQL.
> 
> However observed the blocking query is holding the locks on the tuple in
> minutes thereby making the other transaction query to wait on locks and
> slowness.
> 
> Can I get more insight why the blocking query is not releasing locks?

In addition to what you're getting from that query, include the xact_start and
state_change columns from pg_stat_activity. My guess is that your code is
starting a transaction, then running a query, then processing the query
results before committing the transaction. Since query locks are held for
the duration of the transaction, this will cause the locks to be held for a
long time if the processing step takes a while.

That's quite a wild guess for me to make: you haven't really provided enough
information for anyone to to make an intelligent response. However, I've
seen the problem I just described often enough that I figured I'd suggest it
as a guess. You should be able to determine if that's what's happening with
the additional columns as well as a look at whatever code is running the
queries.

If that turns out not to be the problem, then you'll probably need to provide
a bit more detail before anyone will be able to provide a better answer. I
mean, I'm even guessing that it's an app making the queries.

> SELECT
> kl.pid as blocking_pid,
> ka.usename as blocking_user,
> ka.query as blocking_query,
> bl.pid as blocked_pid,
> a.usename as blocked_user,
> a.query as blocked_query,
> to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
> FROM pg_catalog.pg_locks bl
> JOIN pg_catalog.pg_stat_activity a
> ON bl.pid = a.pid
> JOIN pg_catalog.pg_locks kl
> ON bl.locktype = kl.locktype
> and bl.database is not distinct from kl.database
> and bl.relation is not distinct from kl.relation
> and bl.page is not distinct from kl.page
> and bl.tuple is not distinct from kl.tuple
> and bl.virtualxid is not distinct from kl.virtualxid
> and bl.transactionid is not distinct from kl.transactionid
> and bl.classid is not distinct from kl.classid
> and bl.objid is not distinct from kl.objid
> and bl.objsubid is not distinct from kl.objsubid
> and bl.pid <> kl.pid
> JOIN pg_catalog.pg_stat_activity ka
> ON kl.pid = ka.pid
> WHERE kl.granted and not bl.granted
> ORDER BY a.query_start;
> 
> Regards...


-- 
Bill Moran
I need your help to succeed:
http://gamesbybill.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] is there a warm standby sync trigger?

[Sameer Kumar]

On Fri, Oct 24, 2014 at 8:01 PM, Michael Paquier 
wrote:

> On Fri, Oct 24, 2014 at 12:30 AM, John Smith 
> wrote:
> >
> > i want to setup a warm standby that listens 24/7 but only syncs when
> > told to (ie only when i am ok with the database updates, will i
> > trigger the sync).
> > can i?
> >
> > i don't want to manually backup and restore like i do now.
>
> That's what pause_at_recovery_target is aimed for:
> http://www.postgresql.org/docs/devel/static/recovery-target-settings.html
> Simply set up the recovery target you want to check, and use
> pause_at_recovery_target to put the standby in a latent state you can
> check. If the state of your server does not satisfy your needs,
> shutdown the server and change the target. Note that operations are
> not backward btw.


If you were using hot-standby, you could have used* pg_pause_recovery() /
pg_resume_recovery()*​

​To pause when needed and continue later. e.g. when you are doing bulk
loads or during a window when you see network congestion. ​


Best Regards,

*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: *+65 8110 0350 <%2B65%208110%200350>*  T: +65 6438 3504 | www.ashnik.com

*[image: icons]*



[image: Email patch] 



This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).

Re: [GENERAL] Estimating WAL usage during pg_basebackup

[Sameer Kumar]

On Fri, Oct 31, 2014 at 2:52 AM, Mike Blackwell 
wrote:

> I need to get an idea of how much WAL space will be required during a long
> (many hours) pg_basebackup over a relatively slow network connection.  This
> is for a server that's not yet running PITR / streaming.
>
> Any thoughts?
>

​You may want to explore
--xlog-method=method

This will enable you to 'fetch' wals at end of backup (which means you
should guess wal_keep_segment properly)​


Or you can 'stream' them which means WALs will be backed up in parallel.
This may introduce contention on network and hence may slow it down but you
will not have to guess wal_keep_segment​. See if this helps you.

Re: [GENERAL] Avoiding deadlocks when performing bulk update and delete operations

[Bill Moran]

On Wed, 26 Nov 2014 10:41:56 +1100
Sanjaya Vithanagama  wrote:
> 
> > * How frequently do deadlocks occur?
> 
> We are seeing deadlocks about 2-3 times per day in the production server.
> To reproduce the problem easily we've written a simple Java class with
> multiple threads calling to the stored procedures running the above queries
> inside a loop. This way we can easily recreate a scenario that happens in
> the production.

Don't overcomplicate your solution. Adjust your code to detect the deadlock
and replay the transaction when it happens. At 2-3 deadlocks per day, it's
difficult to justify any other solution (as any other solution would be
more time-consuming to implement, AND would interfere with performance).

I've worked with a number of write-heavy applications that experienced
deadlocks, some of them on the order of hundreds of deadlocks per day.
In some cases, you can adjust the queries to reduce the incidence of
deadlocks, or eliminate the possibility of deadlocks completely.  The
situation that you describe is not one of those cases, as the planner
can choose to lock rows in whatever order it thinks it most efficient
and you don't have direct control over that.

The performance hit you'll take 2-3 times a day when a statement has to
be replayed due to deadlock will hardly be noticed (although a statement
that takes 50 seconds will cause raised eyebrows if it runs 2x) but that
will only happen 2-3 times a day, and the solution I'm proposing won't
have any performance impact on the other 1300 queries per day that
don't deadlock.

2-3 deadlocks per day is normal operation for a heavily contented table,
in my experience.

-- 
Bill Moran
I need your help to succeed:
http://gamesbybill.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] Avoiding deadlocks when performing bulk update and delete operations

[Sanjaya Vithanagama]

On Tue, Nov 25, 2014 at 4:42 AM, Bill Moran 
wrote:

> On Mon, 24 Nov 2014 14:51:42 +1100
> Sanjaya Vithanagama  wrote:
>
> > Hi All,
> >
> > We have a single table which does not have any foreign key references.
> >
> > id_A (bigint)
> > id_B (bigint)
> > val_1 (varchar)
> > val_2 (varchar)
> >
> > The primary key of the table is a composite of id_A and id_B.
> >
> > Reads and writes of this table are highly concurrent and the table has
> > millions of rows. We have several stored procedures which do mass updates
> > and deletes. Those stored procedures are being called concurrently mainly
> > by triggers and application code.
> >
> > The operations usually look like the following where it could match
> > thousands of records to update or delete:
> >
> > DELETE FROM table_name t
> > USING (
> >SELECT id_A, id_B
> >FROM   table_name
> >WHERE  id_A = ANY(array_of_id_A)
> >ANDid_B = ANY(array_of_id_B)
> >ORDER  BY id_A, id_B
> >FORUPDATE
> >) del
> > WHERE  t.id_A = del.id_A
> > ANDt.id_B = del.id_B;
> >
> >
> > UPDATE table_name t
> > SETval_1 = 'some value'
> >  , val_2 = 'some value'
> > FROM (
> >SELECT id_A, id_B
> >FROM   table_name
> >WHERE  id_A = ANY(array_of_id_A)
> >ANDid_B = ANY(array_of_id_B)
> >ORDER  BY id_A, id_B
> >FORUPDATE
> >) upd
> > WHERE  t.id_A = upd.id_A
> > ANDt.id_B = upd.id_B;
> >
> > We are experiencing deadlocks and all our attempts to perform operations
> > with locks (row level using SELECT FOR UPDATE as used in the above
> queries
> > and table level locks) do not seem to solve these deadlock issues. (Note
> > that we cannot in any way use access exclusive locking on this table
> > because of the performance impact)
> >
> > Is there another way that we could try to solve these deadlock
> situations?
> > The reference manual says ? "The best defense against deadlocks is
> > generally to avoid them by being certain that all applications using a
> > database acquire locks on multiple objects in a consistent order."
> >
> > Is there a guaranteed way to do bulk update/delete operations in a
> > particular order so that we can ensure deadlocks won't occur? Or are
> there
> > any other tricks to avoid deadlocks in this situation?
>
> Lots of stuff to say about this ...
>
> First off, Igor has a valid point that the subselects are not helping any
> and may be making the situation slightly worse. I can't see any reason not
> to simiplify the queries as he suggested.
>

We used sub-selects in the delete queries was with the hope that we could
lock all the rows that will be deleted before the actual deletion happens.
(So that another transaction won't grab the lock for a row which will be
deleted).


>
> Secondly, a lot of your comments are too vague for me to understand what's
> happening, so I'm going to ask a bunch of questions to clarify:
>
> * How many UPDATE/INSERT queries are you running per second?
>

In peak hours it could be anywhere from 100-250. The problem occurs when
two triggers happen to update/delete the same rows at once.


> * How many in parallel on average?
> * What's the typical execution time for an UPDATE/INSERT that might cause
>   this problem?
>

The updates are the most problematic with the execution time being in the
rage of 5-50 seconds.


> * How frequently do deadlocks occur?
>

We are seeing deadlocks about 2-3 times per day in the production server.
To reproduce the problem easily we've written a simple Java class with
multiple threads calling to the stored procedures running the above queries
inside a loop. This way we can easily recreate a scenario that happens in
the production.


> * Are there other tables involved in the transactions ... i.e., have you
>   confirmed that these are the _only_ tables causing the deadlock?
>

Yes, there are no other tables involved with the quires so we can eliminate
any deadlock issues related to foreign key references.


>
> Since you didn't include any log output, I'm fuzzy on some of those things
> above ... but I'm assuming that you're unable to post specific details of
> the precise problem.
>

The log output looks like the following: (I have abstracted away the
function names are real queries but this represents the actual issue)

ERROR:  deadlock detected
DETAIL:  Process 54624 waits for ShareLock on transaction 14164828; blocked
by process 54605.
Process 54605 waits for ShareLock on transaction 14164827; blocked by
process 54624.
Process 54624: SELECT 1 FROM proc_delete()
Process 54605: SELECT 1 FROM proc_update()
HINT:  See server log for query details.
CONTEXT:  SQL statement "UPDATE table_name t
SETval_1 = 'some value'
  , val_2 = 'some value'
FROM (
SELECT id_A, id_B
FROM   table_name
WHERE  id_A = ANY(array_of_id_A)
ANDid_B = ANY(array_of_id_B)
ORDER  BY id_A, id_B
FORUPDATE
) upd
WHERE  t.id_A = upd.id_A
ANDt.id_B = upd.id_B"
PL/pgSQL function proc_delete() line 22 at S

Re: [GENERAL] SQL functions and triggers?

[Alban Hertroys]

> On 25 Nov 2014, at 22:24, Tom Lane  wrote:
> 
> Alban Hertroys  writes:
>> In the past, when writing trigger functions, I’ve always used pl/pgsql 
>> without giving it a second thought. Today I was modifying a database 
>> creation script that was originally intended for Firebird to work with 
>> Postgres and the example trigger procedures in there were very close to pure 
>> SQL.
> 
>> Hence, I started rewriting them as SQL functions, but is that really
>> possible?
> 
> No, nobody's ever tried to make that work.  It could probably be done
> with sufficiently many round tuits, but it's not clear that there's
> any benefit that would justify the work.  Surely dropping some SQL
> commands into plpgsql isn't very hard …

It isn’t. I was just wondering whether I was missing something obvious to make 
an SQL function return a trigger type value. I didn’t think there was, but it 
never hurts to ask ;)

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find 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] returning only part of a rule set

[Dave Potts]

On 25/11/14 22:24, David G Johnston wrote:

Thanks List,  I think this is the right way to go.


Adrian Klaver-4 wrote

If I am only interested in get 2 entries per result set,  I would expect
to see

Actually <=2.
How do you determine which rows to keep, by id or something else?


1 xx,yy
2 xx,yy


1,dd,zz
2,dd,zz

1, ee,ff

Using LIMIT only gives
1 xx,yy
2 xx,yy

Sorry for not explaining it very,  I want to limit the size of an
inviduail set of records which is part of set of records.

It would help if we could see the actual query you are using to get the
result sets, suitably anonymized if needed.

The general answer is that you use a window clause and a row_number()
function over an appropriate partiton.  Put that in a subquery then in the
outer query add a where clause for row_number <= 2.

David J.






--
View this message in context: 
http://postgresql.nabble.com/returning-only-part-of-a-rule-set-tp5828217p5828226.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] returning only part of a rule set

[David G Johnston]

Adrian Klaver-4 wrote
>>
>> If I am only interested in get 2 entries per result set,  I would expect
>> to see
> 
> Actually <=2.
> How do you determine which rows to keep, by id or something else?
> 
>>
>> 1 xx,yy
>> 2 xx,yy
>>
>>
>> 1,dd,zz
>> 2,dd,zz
>>
>> 1, ee,ff
>>
>> Using LIMIT only gives
>> 1 xx,yy
>> 2 xx,yy
>>
>> Sorry for not explaining it very,  I want to limit the size of an
>> inviduail set of records which is part of set of records.
> 
> It would help if we could see the actual query you are using to get the 
> result sets, suitably anonymized if needed.

The general answer is that you use a window clause and a row_number()
function over an appropriate partiton.  Put that in a subquery then in the
outer query add a where clause for row_number <= 2.

David J.






--
View this message in context: 
http://postgresql.nabble.com/returning-only-part-of-a-rule-set-tp5828217p5828226.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] returning only part of a rule set

[Adrian Klaver]

On 11/25/2014 01:56 PM, Dave Potts wrote:

On 25/11/14 21:36, Adrian Klaver wrote:

On 11/25/2014 01:27 PM, Dave Potts wrote:

I have a psql function that make a general sql query, returns a set of
results


Q.  Is there anyway that I can limit the size of the result sets ?


Put a LIMIT on the query.

If that is not what you want then we will need to see the code and/or
get a more detailed explanation of where you want the limiting to occur.

I tried that Limit applies to the  total number of results from a
query,  what I want to do is limit the total number of returns per
result set,

For example  if my query returns a set of items such as   id, foo,bar

Normally the result set is 3 set of records one with 3 entries, one with
2 entires and one with entry, the results would be.

1 xx,yy
2 xx,yy
3 xx,yy

1,dd,zz
2,dd,zz

1, ee,ff


So you are selecting on foo and bar?



If I am only interested in get 2 entries per result set,  I would expect
to see


Actually <=2.
How do you determine which rows to keep, by id or something else?



1 xx,yy
2 xx,yy


1,dd,zz
2,dd,zz

1, ee,ff

Using LIMIT only gives
1 xx,yy
2 xx,yy

Sorry for not explaining it very,  I want to limit the size of an
inviduail set of records which is part of set of records.


It would help if we could see the actual query you are using to get the 
result sets, suitably anonymized if needed.











Dave.

















--
Adrian Klaver
adrian.kla...@aklaver.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] returning only part of a rule set

[Dave Potts]

On 25/11/14 21:36, Adrian Klaver wrote:

On 11/25/2014 01:27 PM, Dave Potts wrote:

I have a psql function that make a general sql query, returns a set of
results


Q.  Is there anyway that I can limit the size of the result sets ?


Put a LIMIT on the query.

If that is not what you want then we will need to see the code and/or 
get a more detailed explanation of where you want the limiting to occur.
I tried that Limit applies to the  total number of results from a 
query,  what I want to do is limit the total number of returns per 
result set,


For example  if my query returns a set of items such as   id, foo,bar

Normally the result set is 3 set of records one with 3 entries, one with 
2 entires and one with entry, the results would be.


1 xx,yy
2 xx,yy
3 xx,yy

1,dd,zz
2,dd,zz

1, ee,ff

If I am only interested in get 2 entries per result set,  I would expect 
to see


1 xx,yy
2 xx,yy


1,dd,zz
2,dd,zz

1, ee,ff

Using LIMIT only gives
1 xx,yy
2 xx,yy

Sorry for not explaining it very,  I want to limit the size of an 
inviduail set of records which is part of set of records.









Dave.














--
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] returning only part of a rule set

[Adrian Klaver]

On 11/25/2014 01:27 PM, Dave Potts wrote:

I have a psql function that make a general sql query, returns a set of
results


Q.  Is there anyway that I can limit the size of the result sets ?


Put a LIMIT on the query.

If that is not what you want then we will need to see the code and/or 
get a more detailed explanation of where you want the limiting to occur.




Dave.










--
Adrian Klaver
adrian.kla...@aklaver.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] returning only part of a rule set

[David G Johnston]

Dave Potts wrote
> I have a psql function that make a general sql query, returns a set of 
> results
> Q.  Is there anyway that I can limit the size of the result sets ?

SELECT *
FROM somewhere
LIMIT 50; <---

David J.

btw: what is a "psql function"?





--
View this message in context: 
http://postgresql.nabble.com/returning-only-part-of-a-rule-set-tp5828217p5828218.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

[GENERAL] returning only part of a rule set

[Dave Potts]

I have a psql function that make a general sql query, returns a set of 
results



Q.  Is there anyway that I can limit the size of the result sets ?

Dave.







--
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] SQL functions and triggers?

[Tom Lane]

Alban Hertroys  writes:
> In the past, when writing trigger functions, I’ve always used pl/pgsql 
> without giving it a second thought. Today I was modifying a database creation 
> script that was originally intended for Firebird to work with Postgres and 
> the example trigger procedures in there were very close to pure SQL.

> Hence, I started rewriting them as SQL functions, but is that really
> possible?

No, nobody's ever tried to make that work.  It could probably be done
with sufficiently many round tuits, but it's not clear that there's
any benefit that would justify the work.  Surely dropping some SQL
commands into plpgsql isn't very hard ...

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] Best filesystem for a high load db

[Andy Colson]

On 11/25/2014 9:43 AM, Maila Fatticcioni wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello,
I need to install a Postgresql 9.4 cluster with two servers at first
instance. The database will be used in an enviroment with high load, a
lot number of clients will do queries that require fast replies.
I have to install Ubuntu 14.04 as OS and I would like to know which
filesystem you would suggest me to use. I have read that ZFS with ZIL
is now mature for a production server: do you think it is that true or
that it would be better to stay safer with a common EXT4?

Thanks in advance.

Maila Fatticcioni
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlR0o68ACgkQi2q3wPb3FcM0/ACgwWEPZ+8dhvJPcjvoZq0GSfDJ
1p8AnjNFd2Mb9Nms4jTfRywn0MBmtVAW
=wvKu
-END PGP SIGNATURE-




A long while back I benched Ext4 vs xfs, and xfs won.

I've been using xfs for a long while now and love it.

You don't need to guess with mount options anymore.  The defaults are best.

-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] Lock Management: Waiting on locks

[Dev Kumkar]

Am debugging a race condition scenario where multiple transaction are
running in parallel and there are insert/update these transactions are
performing.

I was able to identify the blocking query and blocked query using following
SQL.

However observed the blocking query is holding the locks on the tuple in
minutes thereby making the other transaction query to wait on locks and
slowness.

Can I get more insight why the blocking query is not releasing locks?

SELECT
kl.pid as blocking_pid,
ka.usename as blocking_user,
ka.query as blocking_query,
bl.pid as blocked_pid,
a.usename as blocked_user,
a.query as blocked_query,
to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.pid
JOIN pg_catalog.pg_locks kl
ON bl.locktype = kl.locktype
and bl.database is not distinct from kl.database
and bl.relation is not distinct from kl.relation
and bl.page is not distinct from kl.page
and bl.tuple is not distinct from kl.tuple
and bl.virtualxid is not distinct from kl.virtualxid
and bl.transactionid is not distinct from kl.transactionid
and bl.classid is not distinct from kl.classid
and bl.objid is not distinct from kl.objid
and bl.objsubid is not distinct from kl.objsubid
and bl.pid <> kl.pid
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.pid
WHERE kl.granted and not bl.granted
ORDER BY a.query_start;

Regards...

Re: [GENERAL] SQL functions and triggers?

[Bill Moran]

On Tue, 25 Nov 2014 21:41:12 +0100
Alban Hertroys  wrote:

> Hi all,
> 
> In the past, when writing trigger functions, I?ve always used pl/pgsql 
> without giving it a second thought. Today I was modifying a database creation 
> script that was originally intended for Firebird to work with Postgres and 
> the example trigger procedures in there were very close to pure SQL.
> 
> Hence, I started rewriting them as SQL functions, but is that really 
> possible? The return-type for trigger functions is usually ?returns trigger?, 
> which I don?t know how to translate to SQL. Next to that, what should the SQL 
> statement inside the function be returning?

Last I checked, triggers had to be written in a language that could "return
trigger".  A look at the 9.4 documentation seems to suggest that this is
still a requirement, although I didn't find a definitive statement to that
effect.

Since stored procedures written in SQL are unable to return the trigger type,
it's not currently possible to write triggers in SQL.

-- 
Bill Moran
I need your help to succeed:
http://gamesbybill.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] Transactions to create pg_multixact members and offsets

[Dev Kumkar]

On Fri, Nov 21, 2014 at 1:14 AM, Alvaro Herrera 
wrote:

>
> If there are foreign keys on the tables, the system internally runs some
> SELECT FOR KEY SHARE queries on the referenced tables (the ones
> containing the primary or unique keys).  You can get some multixacts
> that way too.
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

Thanks !

If I recollect correct, then is the cleanup of pg_multixact/members fixed
in 9.3.5 release?

Regards...

[GENERAL] SQL functions and triggers?

[Alban Hertroys]

Hi all,

In the past, when writing trigger functions, I’ve always used pl/pgsql without 
giving it a second thought. Today I was modifying a database creation script 
that was originally intended for Firebird to work with Postgres and the example 
trigger procedures in there were very close to pure SQL.

Hence, I started rewriting them as SQL functions, but is that really possible? 
The return-type for trigger functions is usually ‘returns trigger’, which I 
don’t know how to translate to SQL. Next to that, what should the SQL statement 
inside the function be returning?

This got a bit puzzling and now I wonder whether this is possible at all? And 
if so, what’s the correct syntax?

Say I have the below, how to actually return a value of type trigger?:

create or replace function pUpdateFoo()
returns trigger
as $$
update  Bar
set baz = baz + NEW.feep - OLD.feep
where  foo = NEW.foo;
$$;

create trigger tUpdateFoo
after update on Foo
for each row
execute procedure pUpdateFoo();

Glad to finally have a proper opportunity to apply PG again - it’s been too 
long!

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find 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

[GENERAL] users per database

[Willy-Bas Loos]

Hi,

I'm moving a database out of an existing cluster, and was wondering which
users i need to take with it.
So i made 2 little queries that show the users that have rights in the
database, maybe they wil come in handy for someone else too.

--show owners of objects in this database
select pg_get_userbyid(c.relowner), count(*)
from pg_class c
group by 1
order by 2 desc;

--show all users that have rights in this db (except column rights):
with a as (
select unnest(c.relacl)::text as priv
from pg_catalog.pg_class c
where relacl notnull
)
select substring(priv, 1, position('=' in priv)-1), count(*)
from a
where substring(priv, 1, position('=' in priv)-1) != ''
group by 1
order by 2 desc;

Cheers,
-- 
Willy-Bas Loos

Re: [GENERAL] Best filesystem for a high load db

[Bill Moran]

On Tue, 25 Nov 2014 17:27:18 +0100
Christoph Berg  wrote:

> Re: Bill Moran 2014-11-25 
> <20141125111630.d05d58a9eb083c7cf80ed...@potentialtech.com>
> > Anything with a journal is a performance problem. PostgreSQL effectivly
> > does its own journalling with the WAL logs. That's not to say that there's
> > no value to crash recovery to having a journalling filesystem, but it's
> > just to say that our experience showed journaling filesystems to be slower.
> > That rules out ext4, unless you disable the journal. I seem to remember
> > ext4 with journalling disabled being one of the faster filesystems, but I
> > could be remembering wrong.
> 
> If you are using a non-journalling FS, you'll be waiting for a full
> fsck after a system crash. Not sure that's an improvement.

It's an improvement if:
a) You're investing in high-quality hardware, so the chance of a system crash
   is very low.
b) The database is replicated, so your plan in the event of a primary crash is
   to fail over to the backup anyway.

If both of those are in place (as they were at my previous job) then the time
it takes to fsck isn't an issue, and taking action that causes the database to
run faster when nothing is wrong can be considered.

Obviously, the OP needs to assess the specific needs of the product in question.
Your point is very valid, and I'm glad you brought it up (as a lot of people
forget about it) but sometimes it's not the most important factor.

-- 
Bill Moran
I need your help to succeed:
http://gamesbybill.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] Best filesystem for a high load db

[Christoph Berg]

Re: Bill Moran 2014-11-25 
<20141125111630.d05d58a9eb083c7cf80ed...@potentialtech.com>
> Anything with a journal is a performance problem. PostgreSQL effectivly
> does its own journalling with the WAL logs. That's not to say that there's
> no value to crash recovery to having a journalling filesystem, but it's
> just to say that our experience showed journaling filesystems to be slower.
> That rules out ext4, unless you disable the journal. I seem to remember
> ext4 with journalling disabled being one of the faster filesystems, but I
> could be remembering wrong.

If you are using a non-journalling FS, you'll be waiting for a full
fsck after a system crash. Not sure that's an improvement.

Christoph
-- 
c...@df7cb.de | http://www.df7cb.de/


-- 
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] Best filesystem for a high load db

[Bill Moran]

On Tue, 25 Nov 2014 16:43:49 +0100
Maila Fatticcioni  wrote:
> Hello,
> I need to install a Postgresql 9.4 cluster with two servers at first
> instance. The database will be used in an enviroment with high load, a
> lot number of clients will do queries that require fast replies.
> I have to install Ubuntu 14.04 as OS and I would like to know which
> filesystem you would suggest me to use. I have read that ZFS with ZIL
> is now mature for a production server: do you think it is that true or
> that it would be better to stay safer with a common EXT4?

We did a lot of research into this at my last employer. Unfortunatley, I
don't remember most of it, but I'll relay what I do remember.

Anything with a journal is a performance problem. PostgreSQL effectivly
does its own journalling with the WAL logs. That's not to say that there's
no value to crash recovery to having a journalling filesystem, but it's
just to say that our experience showed journaling filesystems to be slower.
That rules out ext4, unless you disable the journal. I seem to remember
ext4 with journalling disabled being one of the faster filesystems, but I
could be remembering wrong.

At the time we tested it, ZFS performance was terrible for databases. ZFS
continues to evolve, so that information might not be accurate any more.

Now ... if I remember correctly, we decided that XFS was the fastest of the
ones we tested. If I'm remembering correctly, the performance of XFS was
only a little bit better than ext4 with journalling disabled and there was
considerable debate as to whether it was worth going with something less
widely used and supported (XFS) to gain only a tiny % of improvement over
something heavily used and tested (ext4). Sorry I can't be more definite, but
I don't have access to the information any more.

On a related note, I remember that the sysops guys had a list of mount options
that they used to improve performance ... noatime being one that I remember,
but whatever filesystem you use, be sure to research and tweak the mount
options.

-- 
Bill Moran
I need your help to succeed:
http://gamesbybill.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] Best filesystem for a high load db

[Maila Fatticcioni]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello,
I need to install a Postgresql 9.4 cluster with two servers at first
instance. The database will be used in an enviroment with high load, a
lot number of clients will do queries that require fast replies.
I have to install Ubuntu 14.04 as OS and I would like to know which
filesystem you would suggest me to use. I have read that ZFS with ZIL
is now mature for a production server: do you think it is that true or
that it would be better to stay safer with a common EXT4?

Thanks in advance.

Maila Fatticcioni
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlR0o68ACgkQi2q3wPb3FcM0/ACgwWEPZ+8dhvJPcjvoZq0GSfDJ
1p8AnjNFd2Mb9Nms4jTfRywn0MBmtVAW
=wvKu
-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

[GENERAL] Serialization exception : Who else was involved?

[Olivier MATROT]

I'm using PostgreSQL 9.2.8 on Windows x64.

 

When a transaction as SERIALIZABLE isolation level is failing, is there
a way to know which connection(s), thus transaction(s), were involved in
the overall process ?

 

I'm working on an accounting database. I know that we should retry the
transaction, this is what we do. I've encountered an issue where a
transaction has to be run 10 times to succeed on a busy system.

 

There should be a serious problem here. I'm wondering if there is a
configuration parameter that would allow the system to tell in the
exception detail the other connections involved in the failure.

 

The Postgresql wiki on SSI has no such information.

 

Thanks is advance.

 

Olivier.