Re: [BUGS] BUG #6363: pgp_sym_encrypt() broken between 8.4 and 9.1
Excerpts from dean.w.schulze's message of mié dic 28 20:21:08 -0300 2011:
> The following worked in 8.4, but fails in 9.1. (pgpcrypto is installed.)
>
> In 9.1 it looks like it doesn't recognize strings.
>
> insert into credentials values('demo', pgp_sym_encrypt('password',
> 'longpassword'));
>
>
> ERROR: function pgp_sym_encrypt(unknown, unknown) does not exist
It works fine for me here. Is pgp_sym_encrypt listed in this output?
\dx+ pgcrypto
--
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6363: pgp_sym_encrypt() broken between 8.4 and 9.1
On Thu, Dec 29, 2011 at 14:14, Alvaro Herrera
wrote:
>
> Excerpts from dean.w.schulze's message of mié dic 28 20:21:08 -0300 2011:
>
>> The following worked in 8.4, but fails in 9.1. (pgpcrypto is installed.)
>>
>> In 9.1 it looks like it doesn't recognize strings.
>>
>> insert into credentials values('demo', pgp_sym_encrypt('password',
>> 'longpassword'));
>>
>>
>> ERROR: function pgp_sym_encrypt(unknown, unknown) does not exist
>
> It works fine for me here. Is pgp_sym_encrypt listed in this output?
>
> \dx+ pgcrypto
My guess is that the OP has installed the extension in one database
(maybe "postgres") and tries to use it in another. That's a really
easy mistake to make...
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6365: Memory leak in insert and update
The following bug has been logged on the website: Bug reference: 6365 Logged by: Otto Havasvölgyi Email address: havasvolgyi.o...@gmail.com PostgreSQL version: 9.1.2 Operating system: Win XP SP2 x86; Linux Debian 2.6.32 kernel x64 Description: The bug can be reproduced with pgbench: Insert script: \set nbranches 1*:scale \set ntellers 10*:scale \set naccounts 10*:scale \setrandom aid 1 :naccounts \setrandom bid 1 :nbranches \setrandom tid 1 :ntellers \setrandom delta -5000 5000 insert into pgbench_history (tid, bid, aid, delta, mtime) values (:tid, :bid, :aid, :delta, current_timestamp); Update script: \set nbranches 1*:scale \set ntellers 10*:scale \set naccounts 10*:scale \setrandom aid 1 :naccounts \setrandom bid 1 :nbranches \setrandom tid 1 :ntellers \setrandom delta -5000 5000 update pgbench_accounts set abalance = abalance + :delta where aid = :aid; Steps: ./pgbench -i -Uotto test ./pgbench -c1 -j1 -T200 -Msimple -N -r -v -f insert.sql -Uotto testdb ./pgbench -c1 -j1 -T200 -Msimple -N -r -v -f update.sql -Uotto testdb During this test a continuous increase of the backend memory comsumption can be observed. During the insert test the increase is quite bigger than during update. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6365: Memory leak in insert and update
havasvolgyi.o...@gmail.com writes:
> The following bug has been logged on the website:
> Bug reference: 6365
> Logged by: Otto Havasvölgyi
> Email address: havasvolgyi.o...@gmail.com
> PostgreSQL version: 9.1.2
> Operating system: Win XP SP2 x86; Linux Debian 2.6.32 kernel x64
> Description:
> The bug can be reproduced with pgbench:
I see no memory leak with this example.
I suspect you are being fooled by tools that report shared memory as
being used by a process only after it first touches a given page of
shared memory ("top" on Linux does that, for example). This will cause
the apparent memory consumption of any long-lived backend to increase
until it has touched every available shared buffer. But that's not a
leak, just an artifact of the reporting tool. You can confirm for
yourself that that's what's happening by reducing shared_buffers to
a few megabytes and observing that reported memory usage increases up
to that much and then stops growing.
On Linux, I find that watching the "VIRT" column of top output is a
far more reliable guide to whether a memory leak is actually occuring.
Can't offer any suggestions as to what to use on Windows.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6365: Memory leak in insert and update
On Thu, Dec 29, 2011 at 2:10 PM, Tom Lane wrote:
> havasvolgyi.o...@gmail.com writes:
>> The following bug has been logged on the website:
>> Bug reference: 6365
>> Logged by: Otto Havasvölgyi
>> Email address: havasvolgyi.o...@gmail.com
>> PostgreSQL version: 9.1.2
>> Operating system: Win XP SP2 x86; Linux Debian 2.6.32 kernel x64
>> Description:
>
>> The bug can be reproduced with pgbench:
>
> I see no memory leak with this example.
>
> I suspect you are being fooled by tools that report shared memory as
> being used by a process only after it first touches a given page of
> shared memory ("top" on Linux does that, for example). This will cause
> the apparent memory consumption of any long-lived backend to increase
> until it has touched every available shared buffer. But that's not a
> leak, just an artifact of the reporting tool. You can confirm for
> yourself that that's what's happening by reducing shared_buffers to
> a few megabytes and observing that reported memory usage increases up
> to that much and then stops growing.
>
> On Linux, I find that watching the "VIRT" column of top output is a
> far more reliable guide to whether a memory leak is actually occuring.
> Can't offer any suggestions as to what to use on Windows.
This is by the way a FAQ:
http://wiki.postgresql.org/wiki/FAQ#Why_does_PostgreSQL_use_so_much_memory.3F
merlin
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] with hold cursor, cause function execute twice and wrong result
2011/12/27 wcting163 : > postgres=# select version(); > version > > --- > PostgreSQL 9.0alpha5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) > 4.1.2 20080704 (Asianux 3.0 4.1.2-44), 64-b > it > > create table test_execute(id int,name varchar(40)); > insert into test_execute values(1,'jack'); > create or replace function p_test_execute() returns void > as > $$ > begin > raise notice 'hello world'; > update test_execute set id=id*2; > end; > $$ LANGUAGE plpgsql; > > begin; > declare JDBC_CURS_1 cursor with hold for select p_test_execute() from > test_execute; > fetch 50 from JDBC_CURS_1; > NOTICE: hello world > end; > NOTICE: hello world > COMMIT > select * from test_execute; > id | name > +-- > 4 | jack > > I expect id = 2, but it is **4** instead, > > The reason is that the function p_test_execute is executed twice, when > *fetch*, it is first executed, and when transaction commit, because the > cursor is a *holdable* cursor, it is executed again. > > I read the code, for holdable cursor, when commit, following call will > execute: > CommitHoldablePortals-->PersistHoldablePortal-->ExecutorRewind. > > Is *ExecutorRewind* necessary, is it the root of this bug? > Does *ExecutorRewind* cause plan re-execute? while the current behavior isn't great, is this in fact a bug? there is no guarantee that functions in the select list are executed once per returned row anywhere else in the system. note, the current best way to isolate yourself from this behavior, stuffing the function call in a CTE, works they way you are intending: declare JDBC_CURS_1 cursor with hold for with foo as (select p_test_execute() from test_execute) select * from foo; merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6365: Memory leak in insert and update
Merlin Moncure writes: > On Thu, Dec 29, 2011 at 2:10 PM, Tom Lane wrote: >> I see no memory leak with this example. > This is by the way a FAQ: > http://wiki.postgresql.org/wiki/FAQ#Why_does_PostgreSQL_use_so_much_memory.3F Well, to be fair, the FAQ entry didn't mention this behavior of reported usage increasing over time. But it seems like a good place to document that, so I just added a paragraph about it. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6365: Memory leak in insert and update
Thanks for the quick response. Linux's top fooled me quite a bit.
Excuse me for the false report.
Best regards,
Otto
2011/12/29 Tom Lane
> havasvolgyi.o...@gmail.com writes:
> > The following bug has been logged on the website:
> > Bug reference: 6365
> > Logged by: Otto Havasvölgyi
> > Email address: havasvolgyi.o...@gmail.com
> > PostgreSQL version: 9.1.2
> > Operating system: Win XP SP2 x86; Linux Debian 2.6.32 kernel x64
> > Description:
>
> > The bug can be reproduced with pgbench:
>
> I see no memory leak with this example.
>
> I suspect you are being fooled by tools that report shared memory as
> being used by a process only after it first touches a given page of
> shared memory ("top" on Linux does that, for example). This will cause
> the apparent memory consumption of any long-lived backend to increase
> until it has touched every available shared buffer. But that's not a
> leak, just an artifact of the reporting tool. You can confirm for
> yourself that that's what's happening by reducing shared_buffers to
> a few megabytes and observing that reported memory usage increases up
> to that much and then stops growing.
>
> On Linux, I find that watching the "VIRT" column of top output is a
> far more reliable guide to whether a memory leak is actually occuring.
> Can't offer any suggestions as to what to use on Windows.
>
>regards, tom lane
>
