On 2012-11-16, LEA KANG wrote:
> Hi,
>
> I have a table with several lines as following;
>
> - Create table mytable (type number , values integer [2]) ;
>
> - Insert into mytable values (1, ‘{ 10, 0 }’ );
> - Insert into mytable values (1, ‘{ 20, 30 }’ );
> - Insert into mytab
On Tue, Nov 20, 2012 at 12:30 PM, Craig Ringer wrote:
> C++ exception handling and the PostgreSQL backend's longjmp() based
> error handling will interact in exciting and interesting ways.
Define "interesting"? You mean in Wash's sense of "Oh God, oh God,
we're going to receive signal 9"?
Not a
Harry wrote:
> I need help to know how to get below things in Postgresql :-
> 1) No. of active connections?
Can be selected from pg_stat_activity. The exact query depends on
PostgreSQL version and on what you consider "active" to mean.
> 2) No. of non-responding connections?
> 3) Queries which a
Craig Ringer wrote:
> If at all possible, isolate your C++ code from the PostgreSQL
> aggregate implementation. Pass the C++ code pre-allocated buffers
> to work with if you can, and manage the allocations in the Pg C
> code. Turn your C++ code into library that presents only `extern
> "C"` interf
Thomas wrote:
> have tested further combinations - without success - any other
> idea?
> [attempts to use Java parameter list for PostgreSQL OUT parameters]
My guess is that for a single PostgreSQL OUT parameter you should
make your Java function return a value of that type and omit the
paramete
On 21/11/12 11:41, Shaun Thomas wrote:
On 11/20/2012 04:35 PM, Jeff Janes wrote:
Atomic update commit failure in the meatware :)
Ha.
What's actually funny is that one of the affected machines started
*swapping* earlier today. With 15GB free, and 12GB of inactive cache,
and vm.swappiness se
On 11/20/2012 04:35 PM, Jeff Janes wrote:
Atomic update commit failure in the meatware :)
Ha.
What's actually funny is that one of the affected machines started
*swapping* earlier today. With 15GB free, and 12GB of inactive cache,
and vm.swappiness set to 0, it somehow decided there was eno
On Tue, Nov 20, 2012 at 2:26 PM, Shaun Thomas wrote:
> On 11/20/2012 04:08 PM, Jeff Janes wrote:
>
>> Shaun Thomas reports one that is (I assume) not read intensive, but
>> his diagnosis is that this is a kernel bug where a larger
>> shared_buffers for no good reason causes the kernel to kill off
On 11/20/2012 04:08 PM, Jeff Janes wrote:
Shaun Thomas reports one that is (I assume) not read intensive, but
his diagnosis is that this is a kernel bug where a larger
shared_buffers for no good reason causes the kernel to kill off its
page cache.
We're actually very read intensive. According
Adrian Klaver wrote:
> I am hoping to hear more from people who have running 9.2 systems
> w/ between 100m and 1b records, w/ streaming replication and heavy
> data mining on the slaves (5-50m records read per hour by multiple
> parallel processes), while from time to time (2-3 times/week)
> betwe
On 11/20/2012 01:34 PM, Rhys A.D. Stewart wrote:
bummer.
No, not dynamic queries just wanted to have the name of the columns
along with the column data. Literally just like xmlforest, but without
the xml.
Well in pl/pythonu you can, if you use the plpy module:
http://www.postgresql.org/docs/9
On 11/20/2012 01:48 PM, Anibal David Acosta wrote:
> This query returns false
>
> select ('2012-11-20 17:00:00-02:00'::timestamp with time zone) =
> ('2012-11-20 18:00:00-03:00'::timestamp with time zone)
>
> Why?
>
> Does postgres convert the datetime or just show/hide time zone?
>
Try:
test
Gary Chambers writes:
>> This query returns false
>> select ('2012-11-20 17:00:00-02:00'::timestamp with time zone) =
>> ('2012-11-20 18:00:00-03:00'::timestamp with time zone)
> It's false for me, too. Change '2012-11-20 18:00:00-03:00' to the correct
> '2012-11-20
> 16:00:00-03:00' and it wi
On 11/20/2012 02:03 PM, Adrian Klaver wrote:
On 11/20/2012 12:51 PM, Rob Sargent wrote:
On 11/20/2012 01:35 PM, Adrian Klaver wrote:
On 11/20/2012 11:36 AM, Rhys A.D. Stewart wrote:
Greetings,
I'm looking to obtain the name of a column that is passed to a function,
similar to xmlforest. Sugge
This query returns false
select ('2012-11-20 17:00:00-02:00'::timestamp with time zone) = ('2012-11-20
18:00:00-03:00'::timestamp with time zone)
It's false for me, too. Change '2012-11-20 18:00:00-03:00' to the correct
'2012-11-20
16:00:00-03:00' and it will work as you expect.
--
G.
--
S
This query returns false
select ('2012-11-20 17:00:00-02:00'::timestamp with time zone) =
('2012-11-20 18:00:00-03:00'::timestamp with time zone)
Why?
Does postgres convert the datetime or just show/hide time zone?
On 11/20/2012 12:51 PM, Rob Sargent wrote:
> On 11/20/2012 01:35 PM, Adrian Klaver wrote:
>> On 11/20/2012 11:36 AM, Rhys A.D. Stewart wrote:
>>> Greetings,
>>>
>>> I'm looking to obtain the name of a column that is passed to a function,
>>> similar to xmlforest. Suggestions?
>>
>> This is going to
Hello
2012/11/20 Rhys A.D. Stewart :
> Greetings,
>
> I'm looking to obtain the name of a column that is passed to a function,
> similar to xmlforest. Suggestions?
>
It is not possible :(
you cannot to do it without postgres's parser hacking
Regards
Pavel Stehule
> Regards,
>
>
> Rhys
--
S
On 11/20/2012 01:35 PM, Adrian Klaver wrote:
On 11/20/2012 11:36 AM, Rhys A.D. Stewart wrote:
Greetings,
I'm looking to obtain the name of a column that is passed to a function,
similar to xmlforest. Suggestions?
This is going to require some more information.
1) What language is the functio
On 11/20/2012 11:36 AM, Rhys A.D. Stewart wrote:
Greetings,
I'm looking to obtain the name of a column that is passed to a function,
similar to xmlforest. Suggestions?
This is going to require some more information.
1) What language is the function written in?
2) Trigger function or not?
3)
On Tue, Nov 20, 2012 at 1:25 PM, Dmitry Koterov wrote:
> Hello.
>
> Sometimes I see a strange slowdown on my PG 9.1 server: it looks like the
> simplest queries which typically take 1ms or less (e.g. selection of a row
> by its primary key) take 300ms or even more. It is related to all queries
> w
Greetings,
I'm looking to obtain the name of a column that is passed to a function,
similar to xmlforest. Suggestions?
Regards,
Rhys
Hello.
Sometimes I see a strange slowdown on my PG 9.1 server: it looks like the
simplest queries which typically take 1ms or less (e.g. selection of a row
by its primary key) take 300ms or even more. It is related to all queries
within the connection, not the single one: once upon a time all fast
Matthew Vernon writes:
> naiively, you might try:
> \set pwd '\'' `pwd` '\''
> COPY table FROM :pwd || '/relative/path/to/data' ;
so I could do:
\set path '\'' `pwd` '/path/to/data1' '\''
COPY table1 FROM :path;
\set path '\'' `pwd` '/path/to/data2' '\''
COPY table2 FROM :path;
...but surely
have tested further combinations - without success - any other idea?
1st attempt (note: this implementation works on Apache Derby!)
===
CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" integer, IN
"vcIDName" character varying)
RETURNS integer LANGUAGE JAVA
EXTERNAL SE
Matthew Vernon writes:
> naiively, you might try:
> \set pwd '\'' `pwd` '\''
> COPY table FROM :pwd || '/relative/path/to/data' ;
Umm ... why don't you just use a relative path as-is, with \copy
instead of COPY?
\copy table from 'relative/path/to/data'
The server-side COPY is likely to
On 11/20/2012 11:47 AM, Matthew Vernon wrote:
Matthew Vernon writes:
naiively, you might try:
\set pwd '\'' `pwd` '\''
COPY table FROM :pwd || '/relative/path/to/data' ;
I should also note that I want to run a series of these commands, hence
setting pwd once and then wanting to use it multip
Matthew Vernon writes:
> naiively, you might try:
> \set pwd '\'' `pwd` '\''
> COPY table FROM :pwd || '/relative/path/to/data' ;
I should also note that I want to run a series of these commands, hence
setting pwd once and then wanting to use it multiple times.
Matthew
--
Matthew Vernon
Quant
Hi,
suppose for a moment I want to write a psql script that loads some data
into a database. I don't want to write an absolute path into my script,
but merely know where the data file will be relative to my script
location.
naiively, you might try:
\set pwd '\'' `pwd` '\''
COPY table FROM :pwd ||
what would pgbouncer do in my case? Number of connections will decrease,
but number of active clients won't be smaller. As I understand the latter
ones are that important.
-- Vlad
On Fri, Nov 16, 2012 at 2:31 PM, Merlin Moncure wrote:
>
> first thoughts:
> no single thing really stands out --
On Tue, Nov 20, 2012 at 10:50 AM, Jeff Janes wrote:
> On Tue, Nov 20, 2012 at 8:03 AM, Merlin Moncure wrote:
>> On Tue, Nov 20, 2012 at 9:02 AM, Shaun Thomas
>> wrote:
>>> On 11/16/2012 02:31 PM, Merlin Moncure wrote:
>>>
no single thing really stands out -- contention is all over the plac
On Tue, Nov 20, 2012 at 8:03 AM, Merlin Moncure wrote:
> On Tue, Nov 20, 2012 at 9:02 AM, Shaun Thomas
> wrote:
>> On 11/16/2012 02:31 PM, Merlin Moncure wrote:
>>
>>> no single thing really stands out -- contention is all over the place.
>>> lwlock, pinbuffer, dynahash (especially). I am again
Hello All,
I need help to know how to get below things in Postgresql :-
1) No. of active connections?
2) No. of non-responding connections?
3) Queries which are running beyond time(i.e. time which 'll set in database
query)?
5) Dead Locked Queries?
4) Process ID's of dead-locked queries?
5) No. of
On Thu, Nov 15, 2012 at 4:29 PM, Alvaro Herrera
wrote:
> Merlin Moncure escribió:
>
>> ok, excellent. reviewing the log, this immediately caught my eye:
>>
>> recvfrom(8, "\27\3\1\0@", 5, 0, NULL, NULL) = 5
>> recvfrom(8,
>> "\327\327\nl\231LD\211\346\243@WW\254\244\363C\326\247\341\177\255\263
Ok, that was a stupid question to ask, what comes from falling in panic and
suspending thinking before talking :-(
Solution in case anybody else panics:
a) install the *postgresql-devel* package from yum
b) add the include directory *'/usr/include/pgsql/server'* in your IDE. In
Netbeans 7.3b2 that
On 11/20/2012 10:13 AM, Merlin Moncure wrote:
have you ruled out numa issues?
(http://frosty-postgres.blogspot.com/2012/08/postgresql-numa-and-zone-reclaim-mode.html)
Haha. Yeah. Our zone reclaim mode off, and node distance is 10 or 20.
ZCM is only enabled by default if distance is > 20, unle
On Tue, Nov 20, 2012 at 10:12 AM, Shaun Thomas wrote:
> On 11/20/2012 10:03 AM, Merlin Moncure wrote:
>
>> Shared buffer manipulation changing contention is suggesting you're
>> running into free list lock issues. How many active backends/cores?
>
>
> Oh, the reason I wanted to point it out was t
On 11/20/2012 10:03 AM, Merlin Moncure wrote:
Shared buffer manipulation changing contention is suggesting you're
running into free list lock issues. How many active backends/cores?
Oh, the reason I wanted to point it out was that we see a lot more than
CPU contention with higher shared_buff
On Tue, Nov 20, 2012 at 9:02 AM, Shaun Thomas wrote:
> On 11/16/2012 02:31 PM, Merlin Moncure wrote:
>
>> no single thing really stands out -- contention is all over the place.
>> lwlock, pinbuffer, dynahash (especially). I am again suspicious of
>> bad scheduler interaction. any chance we can f
On Fri, Nov 16, 2012 at 12:13 PM, Vlad wrote:
> ok, I've applied that patch and ran. The stall started around 13:50:45...50
> and lasted until the end
>
> https://dl.dropbox.com/u/109778/postgresql-2012-11-16_134904-stripped.log
That isn't as much log as I expected. But I guess only the tip of t
On 11/16/2012 02:31 PM, Merlin Moncure wrote:
no single thing really stands out -- contention is all over the place.
lwlock, pinbuffer, dynahash (especially). I am again suspicious of
bad scheduler interaction. any chance we can fire up pgbouncer?
Just want to throw it out there, but we've b
Hi,
I want to make an extension but don't understand the source setup well.
a) Do I need the full source of postgres locally?
b) Where must the source be? The project dir is ~/nbprojects/my_pg_ext
and I am using the code of the fuzzystrmatch contrib as my "template"
from where to start
c) My inst
On Fri, Nov 16, 2012 at 2:13 PM, Vlad wrote:
> ok, I've applied that patch and ran. The stall started around 13:50:45...50
> and lasted until the end
>
> https://dl.dropbox.com/u/109778/postgresql-2012-11-16_134904-stripped.log
>
> the actual log has more data (including statement following each '
On 11/20/2012 06:09 PM, 张柏年 wrote:
> Hi All,
>
> If there is command to flush the internal buffer within
> Postgresql server of specific database after all connections has been
> disconnected to that database?
>
Why?
It isn't clear what problem you are trying to solve.
The write buffers
Hi,
Is it possible to get information about SQL query for given session?
The view pg_stats_activity shows only "FETCH ALL IN "
Thanks
Regards
aasat
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/fetch-from-cursor-in-pg-stats-activity-and-related-SQL-query-tp5732901
On Mon, Nov 19, 2012 at 12:02 PM, Vlad wrote:
>
> Some additional observation and food for thoughts. Our app uses connection
> caching (Apache::DBI). By disabling Apache::DBI and forcing client
> re-connection for every (http) request processed I eliminated the stall. The
> user cpu usage jumped (
ok, I've applied that patch and ran. The stall started around 13:50:45...50
and lasted until the end
https://dl.dropbox.com/u/109778/postgresql-2012-11-16_134904-stripped.log
the actual log has more data (including statement following each 'spin
delay' record), but there is some sensitive info in
Hello
postgres=# select pg_get_functiondef('fx'::regproc);
pg_get_functiondef
--
CREATE OR REPLACE FUNCTION public.fx(_m integer)+
RETURNS void +
LANGUAGE plpgsql +
On Fri, Nov 16, 2012 at 3:21 PM, Vlad wrote:
> what would pgbouncer do in my case? Number of connections will decrease, but
> number of active clients won't be smaller. As I understand the latter ones
> are that important.
Well, one thing that struck me was how little spinlock contention
there ac
Hi All,
If there is command to flush the internal buffer within Postgresql
server of specific database after all connections has been disconnected to
that database?
Regards,
Emmett
flush buffer after connection disallowed
Some additional observation and food for thoughts. Our app uses connection
caching (Apache::DBI). By disabling Apache::DBI and forcing
client re-connection for every (http) request processed I eliminated the
stall. The user cpu usage jumped (mostly cause prepared sql queries are no
longer available
51 matches
Mail list logo
