Ramesh T | 31 Jul 18:39 2015

postgres 9.3

I changed archive_command in postgresql.conf and restarted it.postgres 3 installed on linux.connected from putty.
after restarted it
getting message like

-bash-4.1$ service postgres-9.3 start
postgres-9.3: unrecognized service

but files available at server side..
any help..?

Alex Magnum | 31 Jul 14:07 2015

PLPerl Trigger to update text search


I am trying to update a tsvector field through a plperl trigger.

$_TD->{new}{text_search} = to_tsvector('pg_catalog.english', 'text1 text2');

but plperl does not seem to like that... 
ERROR:  Undefined subroutine &main::to_tsvector called 

anyone done that and could help me out if that is actually possible?

Thanks in advance
Andrey Lizenko | 31 Jul 12:52 2015

multiple postgres processes after establishing tcp connection

PostgreSQL 9.4.4 started in usual way
[postgres <at> ubuntu12-vm][20150731 06:26:06]:/db2/master$ pg_ctl start -D /db2/master/ -l serverlog
server starting

I can see root process for this then:
[postgres <at> ubuntu12-vm][20150731 06:38:36]:/db2/master$ ps -ef | grep "bin/postgres" | grep -v grep
postgres  5152     1  0 06:28 pts/0    00:00:00 /opt/postgres/9.4.4/bin/postgres -D /db2/master

If only simple telnet connection started:
[postgres <at> ubuntu12-vm][20150731 06:30:24]:/db2$ telnet 5551
Connected to
Escape character is '^]'.
new postgres process appeared as a child of the first one.
[postgres <at> ubuntu12-vm][20150731 06:38:42]:/db2/master$ ps -ef | grep "bin/postgres" | grep -v grep
postgres  5152     1  0 06:28 pts/0    00:00:00 /opt/postgres/9.4.4/bin/postgres -D /db2/master
postgres  6358  5152  0 06:39 ?        00:00:00 /opt/postgres/9.4.4/bin/postgres -D /db2/master

Is this an attempt to create new worker?
No records (presumably) in pg_stat_activity, only "invalid length of startup packet" after closing telnet session.

Regards, Andrey Lizenko
Merlin Moncure | 30 Jul 23:51 2015

PSA: linux kernel bug in TRIM support corrupts data with md raid levels 0 and 10

http://www.spinics.net/lists/raid/msg49452.html (via slashdot)



Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:

Melvin Davidson | 30 Jul 15:42 2015

user connection not recorded?

I have a puzzling question.

All through the error log, there are connections for [unknown] user.
2015-07-30 00:00:00 CDT [6577]: [1-1]: : [unknown]: LOG:  connection received: host= port=50687

The log_line_prefix   is  %t [%p]: [%l-1]: %h: %u:

I can understand that the host is not available in nslookup, but why is the user not being recorded?

Melvin Davidson
Rowan Collins | 30 Jul 14:35 2015

Exclusively locking parent tables while disinheriting children.


When working with partition sets, we're seeing occasional errors of 
"could not find inherited attribute..." in Select queries. This is 
apparently caused when an "ALTER TABLE ... NO INHERIT" runs concurrently 
with another transaction selecting from the relevant child table.

I found an old bug report filed against 8.3 back in 2008 [1] I can still 
reproduce the test case in that report on 9.1.11 and 9.3.5, and it seems 
to match what we're seeing in production.

Tom Lane said at the time that a lock would cause more problems than it 
solved [2], but when I add an explicit lock statement ("LOCK TABLE ONLY 
p_tbl, c1_tbl IN ACCESS EXCLUSIVE MODE;") between "BEGIN" and "ALTER 
TABLE", I get the behaviour I would expect - the SELECT blocks until the 
transaction is committed, then returns rows from the remaining child table.

So what I want to understand is what the risk of adding this lock are - 
under what circumstances would I expect to see dead locks if I manually 
added this lock to my partition maintenance functions?

If there aren't any, should the database itself acquire this lock during 
the ALTER TABLE process? There is mention in previous discussions of 
DROP TABLE also not taking a lock, but even if that case isn't fixable, 
fixing NO INHERIT would at least provide a documented (and quite 
intuitive) way to achieve this safely - always disinherit your children 
before dropping them.

[2] http://www.postgresql.org/message-id/19666.1213709303%40sss.pgh.pa.us

Rowan Collins


Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:

Renato Oliveira | 30 Jul 12:13 2015

How Many PG_Locks are considered too many



I have few questions, if anyone could help me, it will be very much appreciated.


We have a Nagios plugin, which monitors pg_locks and almost daily we see 3000 to 40000 pg_locks.


Can we just ignore them, can we let them grow without worrying?

How many pg_locks are considered unsafe for any given postgres server?


Thank you





Jan Keirse | 30 Jul 11:55 2015

Transaction ID Wraparound Monitoring


we have some very write heavy databases and I have our monitoring
system watch the transaction age of my databases to be alerted before
we get into problems in case autovacuum can't keep up to avoid
transaction ID wraparound.

The query I am executing is this:
SELECT max(age(pg_database.datfrozenxid)) / 2147483648.0 * 100.0 AS
"Percentage of transaction ID's used" FROM pg_database;

My believe was that if this reaches 100 the database will stop
accepting writes and one must vacuum. I have set alerts on 50 and 90,
the result is around 9 so my believe was autovacuum is working fine
for my workload.
I often see autovacuum kicking in to prevent XID Wraparround, I
thought that was just to be on the safe side and vacuum well before
it's too late.

However today I saw this post:

The following line has me worried:
... that database is going to reach a situation where the XID counter
has reached its maximum value. The absolute peak is something around 2
billion, but it can be far lower than that in some situations...

Could someone shed some light on this? Is my query insufficient? Can
the transaction wrapparound freeze problem indeed occur earlier? And
if so, could someone suggest a better query to monitor?

Kind Regards,

Jan Keirse


**** DISCLAIMER ****


"This message is delivered to all addressees subject to the conditions
set forth in the attached disclaimer, which is an integral part of this


Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:

Murali M | 30 Jul 00:42 2015

Question about copy from with timestamp format


I wanted to copy a file from local file system to postgres. I have timestamp value specified as:
YYYYMMDDHH24 format -- for example:
2015072913 -- is July 29, 2015 at 13:00 

how do I import this data into a timestamp field?

thanks, murali.

PS: I believe if I need the hour, I need to use timestamp (I do not want to put the hour as a separate column). I believe date datatype does not work, if I am right??
CS DBA | 29 Jul 21:13 2015

xmin horizon?


The documentation for pg_stat_activity lists this column:

backend_xmin     xid     The current backend's xmin horizon.

Can someone point me to a better understanding on "xmin horizon"?

Thanks in advance


Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:

Ramesh T | 29 Jul 18:33 2015

instr detail

Hi All,
           is instr available in postgres 9.3..?

in oracle instr('12.32.42','.',-1) ,any help appreciated