Ramesh T | 31 Jul 18:39 2015
Picon

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
Picon

PLPerl Trigger to update text search

Hi,

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
Alex
Andrey Lizenko | 31 Jul 12:52 2015
Picon

multiple postgres processes after establishing tcp connection

Hello,
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 127.0.0.1 5551
Trying 127.0.0.1...
Connected to 127.0.0.1.
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
Picon

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)

merlin

--

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

Melvin Davidson | 30 Jul 15:42 2015
Picon

user connection not recorded?

I have a puzzling question.

All through the error log, there are connections for [unknown] user.
EG:
2015-07-30 00:00:00 CDT [6577]: [1-1]: : [unknown]: LOG:  connection received: host=173.239.101.98 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
Picon

Exclusively locking parent tables while disinheriting children.

Hi,

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.

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

Regards,
-- 
Rowan Collins
[IMSoP]

--

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

Renato Oliveira | 30 Jul 12:13 2015

How Many PG_Locks are considered too many

Hi

 

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

 

Renato

 

 

Jan Keirse | 30 Jul 11:55 2015

Transaction ID Wraparound Monitoring

Hello,

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:
http://blog.getsentry.com/2015/07/23/transaction-id-wraparound-in-postgres.html

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

http://www.tvh.com/glob/en/email-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
message."

--

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

Murali M | 30 Jul 00:42 2015
Picon

Question about copy from with timestamp format

Hi,

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?

All;

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:
http://www.postgresql.org/mailpref/pgsql-general

Ramesh T | 29 Jul 18:33 2015
Picon

instr detail

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

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

Gmane