Re: [GENERAL] conn = PQconnectdb(conninfo);
Hi Laurenz, I'm not using ssl at the moment. The thing is when the reachable leak is there is just grows. It is not constant. And at a given point the program will break. I've ran the program with: valgrind --leak-check=full --show-reachable=yes --log-file="_pg_test_debug.log" ./_pg_test_debug View logfile here: http://pastebin.com/7rjBRbkD SSL is mentioned in combination with pg objects Your suggestion: sslmode=disable seems to have fix my issue.. Thanks! I have to further investigate why PG is trying to use SSL when no option is given to use it. I have a vanilla install of the db and libpq. Best, Peter 2015-07-31 12:05 GMT+02:00 Albe Laurenz : > Peter Kroon wrote: > > I've found perhaps a bug. > > I've narrowed down my code and the problem is indeed at: conn = > PQconnectdb(conninfo); > > > > My connection string: host=192.168.178.12 dbname=DATABASE user=foo > password=bar > > > > When I remove key/value host=xxx then everything is OK. Valgrind > mentions: no leaks are possible. > > > > When key/value host=xxx is added, not everything is freed and there are > tons of bytes still reachable. > > > > > > ==9195== > > ==9195== HEAP SUMMARY: > > ==9195== in use at exit: 450,080 bytes in 2,829 blocks > > ==9195== total heap usage: 9,476 allocs, 6,647 frees, 7,810,733 bytes > allocated > > ==9195== > > ==9195== LEAK SUMMARY: > > ==9195==definitely lost: 0 bytes in 0 blocks > > ==9195==indirectly lost: 0 bytes in 0 blocks > > ==9195== possibly lost: 0 bytes in 0 blocks > > ==9195==still reachable: 450,080 bytes in 2,829 blocks > > ==9195== suppressed: 0 bytes in 0 blocks > > ==9195== Rerun with --leak-check=full to see details of leaked memory > > ==9195== > > ==9195== For counts of detected and suppressed errors, rerun with: -v > > ==9195== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 14 from 6) > > > > > > > > The network address is the machine's address where I was testing on. So > I could also have used > > localhost or 127.0.0.1 but this gave me the same result when using the > network address. > > > > > > Played with hostaddr as well and gave me the same result. > > > > > > > http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PQCONNECTDB > > > http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS > > "PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian > 4.7.2-5) 4.7.2, 64-bit" > > > > > > The attachment is the program I've used for testing. > > You should run valgrind with --leak-check=full to see details. > > I tried your program and I get "still reachable" only when SSL is enabled; > all the memory is in > OpenSSL. Dou you use SSL? > > Without SSL (sslmode=disable) I get no "still reachable" memory. > > I don't know of reachable memory is a problem, I'd suspect not. > > Yours, > Laurenz Albe >
Re: [GENERAL] conn = PQconnectdb(conninfo);
I've found perhaps a bug.
I've narrowed down my code and the problem is indeed at: conn =
PQconnectdb(conninfo);
My connection string: host=192.168.178.12 dbname=DATABASE user=foo
password=bar
When I remove key/value host=xxx then everything is OK. Valgrind mentions:
no leaks are possible.
When key/value host=xxx is added, not everything is freed and there are
tons of bytes still reachable.
==9195==
==9195== HEAP SUMMARY:
==9195== in use at exit: 450,080 bytes in 2,829 blocks
==9195== total heap usage: 9,476 allocs, 6,647 frees, 7,810,733 bytes
allocated
==9195==
==9195== LEAK SUMMARY:
==9195==definitely lost: 0 bytes in 0 blocks
==9195==indirectly lost: 0 bytes in 0 blocks
==9195== possibly lost: 0 bytes in 0 blocks
==9195==still reachable: 450,080 bytes in 2,829 blocks
==9195== suppressed: 0 bytes in 0 blocks
==9195== Rerun with --leak-check=full to see details of leaked memory
==9195==
==9195== For counts of detected and suppressed errors, rerun with: -v
==9195== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 14 from 6)
The network address is the machine's address where I was testing on. So I
could also have used localhost or 127.0.0.1 but this gave me the same
result when using the network address.
Played with hostaddr as well and gave me the same result.
http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PQCONNECTDB
http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS
"PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.7.2-5) 4.7.2, 64-bit"
The attachment is the program I've used for testing.
2015-07-18 0:15 GMT+02:00 Tom Lane :
> Peter Kroon writes:
> > Every now and then my program will abort.
> > IAnd this is because: conn = PQconnectdb(conninfo);
> > The error given:
> > *** Error in `./server_prog': malloc(): smallbin double linked list
> > corrupted: 0x092c10a0 ***
>
> This looks like malloc() complaining because something has corrupted its
> bookkeeping data, which generally means that something previously wrote
> past the end of a malloc'd data chunk, or tried to write into an
> already-freed chunk. The odds are very high that the bug is in your
> program rather than libpq, though, because no such problems have been
> found within libpq recently.
>
> valgrind is a fairly useful tool for tracking down such issues.
>
> regards, tom lane
>
#include
#include
#include
#include "/usr/include/postgresql/libpq-fe.h"
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
void *print_message_function( void *ptr );
main()
{
int MAX = 10;
pthread_t thread[MAX];
const char *message = "Hello Wordl!";
int iret[MAX];
/* Create independent threads each of which will execute function */
int i;
for ( i = 0; i < MAX; i++ )
{
iret[i] = pthread_create( &thread[i], NULL, print_message_function, (void*) message);
if(iret[i])
{
fprintf(stderr,"Error, return code: %d\n", iret[i]);
exit(EXIT_FAILURE);
}
}
for ( i = 0; i < MAX; i++ )
printf("pthread_create(), thread %d returns: %d\n", i, iret[i]);
for ( i = 0; i < MAX; i++ )
pthread_join( thread[i], NULL);
exit(EXIT_SUCCESS);
}
void *print_message_function( void *ptr )
{
char *message;
message = (char *) ptr;
printf("%s \n", message);
// database variable
const char *conninfo;
PGconn *conn;
PGresult *res;
int nFields,
nRows;
int i,
j;
int result_ok = 0;
conninfo = "host=127.0.0.1 dbname=postgres user=postgres password=XXX port=5432";
/* Make a connection to the database */
conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
/* Start a transaction block */
res = PQexec(conn, "BEGIN");
PQclear(res);
/* disable notice */
res = PQexec(conn, "SET client_min_messages TO NOTICE;");
PQclear(res);
/* end the transaction */
res = PQexec(conn, "END");
PQclear(res);
/* close the connection to the database and clean-up */
PQfinish(conn);
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] conn = PQconnectdb(conninfo);
Hi, Every now and then my program will abort. IAnd this is because: conn = PQconnectdb(conninfo); The error given: *** Error in `./server_prog': malloc(): smallbin double linked list corrupted: 0x092c10a0 *** Any thoughts on why I'm getting this message? Regards, Peter
Re: [GENERAL] dblink max per function
A restart of my system solved the matter. 2015-07-05 20:54 GMT+02:00 Peter Kroon : > Hi, > > I've got a function with 5 dblink select statement all to my local server > with the same connection string. > When one more dblink select statement is added the query fails. > Is there some kind of limit that I can configure? If so, which one an > where? > > Thanks, > Peter > >
[GENERAL] Fwd: dblink max per function
Hi, I've got a function with 5 dblink select statement all to my local server with the same connection string. When one more dblink select statement is added the query fails. Is there some kind of limit that I can configure? If so, which one an where? Thanks, Peter
[GENERAL] valgrind
Hi list, I've Installed postgresql-9.4 using apt-get as instructed here: http://www.postgresql.org/download/linux/debian/ Also installed libpq-dev with the suggested dependencies using apt-get. And created a small program in c. You can find it here: http://pastebin.com/bRHw3Wud When I run the progam against valgrind I get: ==3814== Memcheck, a memory error detector ==3814== Copyright (C) 2002-2013, and GNU GPL'd, by Julian Seward et al. ==3814== Using Valgrind-3.10.1 and LibVEX; rerun with -h for copyright info ==3814== Command: ./pgsql_check ==3814== ==3814== ==3814== HEAP SUMMARY: ==3814== in use at exit: 47,288 bytes in 2,864 blocks ==3814== total heap usage: 5,671 allocs, 2,807 frees, 331,460 bytes allocated ==3814== ==3814== LEAK SUMMARY: ==3814==definitely lost: 0 bytes in 0 blocks ==3814==indirectly lost: 0 bytes in 0 blocks ==3814== possibly lost: 0 bytes in 0 blocks ==3814==still reachable: 47,288 bytes in 2,864 blocks ==3814== suppressed: 0 bytes in 0 blocks ==3814== Rerun with --leak-check=full to see details of leaked memory ==3814== ==3814== For counts of detected and suppressed errors, rerun with: -v ==3814== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 0 from 0) There is still reachable data. Is this a bug or have I forgotten to free something? Best, Peter
Re: [GENERAL] validate synatax
Perhaps creating a temporary table would be more efficient. Then the rollback isn't necessary. 2013/12/11 Jov > you can use the transition. > eg: > begin; > creat table(...); > catch error if the statement not validated. > rollback; > > jov > 在 2013-12-11 上午5:43，"Peter Kroon" 写道： > > Hi, >> >> How can I validate any query on PostgreSQL without executing the sql. >> I was able with EXPLAIN to find some errors. However this only worked >> with a SELECT statement. When i tried to create a TABLE it would not run. >> I do not wish to install external packages. Preferably use only default >> present features. >> >> >> http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT#PostgreSQL_EXPLAIN >> >> Any suggestions are welcome. >> >> Best, >> Peter >> >
Re: [GENERAL] validate synatax
>Why do you want to do that? I want to validate the SQL syntax and preferably in the browser using some kind of linter. >You can always run it inside transaction and rollback at the end. Sounds dangerous and will make the server very active because it is executing the SQL. Yes, the SQL should be tested even if the syntax is correct. So 2 things to look for: - Syntax validator - Check query logic, like does the table exist Best, Peter 2013/12/10 Szymon Guz > > > > On 10 December 2013 22:40, Peter Kroon wrote: > >> Hi, >> >> How can I validate any query on PostgreSQL without executing the sql. >> I was able with EXPLAIN to find some errors. However this only worked >> with a SELECT statement. When i tried to create a TABLE it would not run. >> I do not wish to install external packages. Preferably use only default >> present features. >> >> >> http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT#PostgreSQL_EXPLAIN >> >> Any suggestions are welcome. >> >> Best, >> Peter >> > > Why do you want to do that? Maybe just test it, there can be many more > problems then just not executing query, the query logic could be bad, even > if the syntax is correct. You can always run it inside transaction and > rollback at the end. > > regards, > Szymon >
[GENERAL] validate synatax
Hi, How can I validate any query on PostgreSQL without executing the sql. I was able with EXPLAIN to find some errors. However this only worked with a SELECT statement. When i tried to create a TABLE it would not run. I do not wish to install external packages. Preferably use only default present features. http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT#PostgreSQL_EXPLAIN Any suggestions are welcome. Best, Peter
[GENERAL] add parameter to existing function
Hi, I can use: create or replace function etc to alter a function. However, this will create a new function instead of replacing the old one when adding a parameter. Is this possible without the use of DROP and CASCADE? The function that I'm trying to alter is on some tables a default value. Best, Peter
Re: [GENERAL] pgadmin III query
Hi Dinesh, SELECT pg_reload_conf(); Did not do the job, I had to restart the server. I managed to collect the queries and there are a lot of them to show the SQL that is needed to create the given table. Does postrgesql have any plan on making their own function for this? Best, Peter 2013/12/9 Dinesh Kumar > Hi Peter, > > On Mon, Dec 9, 2013 at 7:03 PM, Peter Kroon wrote: > >> Hi Dinesh, >> >> >> >Get all the queries what it has performed. >> >> How and where? >> When I run "select * from pg_stat_activity" I get the same result with >> and without "log_minduration_statement=0" >> >> > By setting this parameter log_min_duration_statement to 0, postgres will > log all the queries, in the pg_log file. > > Hope the following steps helps you on this, and make sure you have enabled > the logging_collector. > > 1. Modify the above parameter on the required postgres cluster. > > 2. Do SELECT pg_reload_conf(); on the same machine. > > 3. And go to pg_log file location, and do tail -f current pg_log file. > > 4. Go to pgadmin, and refresh on any table. > > 5. Check the tail -f file output. There you will find all the sql > queries, which have been executed from pgAdmin. > > 6. Collect those queries, and make your own custom function with pl/pgsql > language. > > Regards, > Dinesh > > >> Could you provide a more detailed step by step guide? >> >> Best, >> Peter >> >> >> >> 2013/12/9 Dinesh Kumar >> >>> Hi, >>> >>> On Fri, Dec 6, 2013 at 4:34 PM, Peter Kroon wrote: >>> >>>> Thanks, but i need a non command line option. >>>> >>>> >>> We can do this with a function which is having the sql queries of >>> pgAdmin raised against the database. >>> >>> => Log all the queries by enabling "log_minduration_statement=0". >>> => Do SELECT pg_reload_conf(); >>> => Do a refresh on a table of pgAdmin's browser. >>> => Get all the queries what it has performed. >>> => Create a custom function with those queries. >>> >>> Regards, >>> Dinesh >>> >>> >>>> >>>> 2013/12/6 Ian Lawrence Barwick >>>> >>>>> 2013/12/6 Peter Kroon : >>>>> > When you click on a table in the "Object browser" you'll see in the >>>>> "SQL >>>>> > pane" the sql that is needed to create that table. >>>>> > >>>>> > Which function can I call to get that SQL? >>>>> >>>>> You can use the pg_dump command line function for this: >>>>> >>>>> pg_dump -s -t name_of_table name_of_database >>>>> >>>>> Regards >>>>> >>>>> Ian Barwick >>>>> >>>> >>>> >>> >> >
Re: [GENERAL] pgadmin III query
Thanks, but i need a non command line option. 2013/12/6 Ian Lawrence Barwick > 2013/12/6 Peter Kroon : > > When you click on a table in the "Object browser" you'll see in the "SQL > > pane" the sql that is needed to create that table. > > > > Which function can I call to get that SQL? > > You can use the pg_dump command line function for this: > > pg_dump -s -t name_of_table name_of_database > > Regards > > Ian Barwick >
[GENERAL] pgadmin III query
When you click on a table in the "Object browser" you'll see in the "SQL pane" the sql that is needed to create that table. Which function can I call to get that SQL? Best, Peter
Re: [GENERAL] xmlagg doesn't honor LIMIT?
This is how I solved it: SELECT xmlagg( xmlconcat( xmlelement(name test_element, xmlforest( ff.d AS a )--xmlforest ) )--xmlconcat )--xmlagg FROM ( SELECT --xmlagg( xmlconcat( xmlelement(name test_element, xmlattributes( 0 AS m ), xmlforest( dh.id AS i ,dh.some_value AS sv )--xmlforest )--test_element ) AS d --) FROM __pg_test_table AS dh WHERE dh.__rel=5 LIMIT 2 --OFFSET 10; ) AS ff; 2013/11/26 Albe Laurenz > Peter Kroon wrote: > > Is anyone able to reproduce? > > When I run the query below all 5 rows are returned instead of 2. > > Or is this the default behaviour.. > > > SELECT > > xmlagg( > [...] > > )--xmlagg > > FROM __pg_test_table AS dh > > WHERE dh.__rel=5 LIMIT 2 --OFFSET 10; > > According to the documentation, that query should return > exactly one row since xmlagg is an aggregate. > > So the LIMIT 2 won't do anything to the result. > > You can wrap your query in a > SELECT count(*) FROM (SELECT ...) AS dummy; > to see how many rows you got. > > Yours, > Laurenz Albe >
[GENERAL] xmlagg doesn't honor LIMIT?
Is anyone able to reproduce? When I run the query below all 5 rows are returned instead of 2. Or is this the default behaviour.. "PostgreSQL 9.2.4 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 32-bit" DROP TABLE IF EXISTS __pg_test_table CASCADE; CREATE TABLE __pg_test_table( id serial, some_value int, __rel int ); INSERT INTO __pg_test_table(some_value,__rel)VALUES(1,5); INSERT INTO __pg_test_table(some_value,__rel)VALUES(2,5); INSERT INTO __pg_test_table(some_value,__rel)VALUES(3,5); INSERT INTO __pg_test_table(some_value,__rel)VALUES(4,5); INSERT INTO __pg_test_table(some_value,__rel)VALUES(5,5); INSERT INTO __pg_test_table(some_value,__rel)VALUES(6,6); INSERT INTO __pg_test_table(some_value,__rel)VALUES(7,6); INSERT INTO __pg_test_table(some_value,__rel)VALUES(8,6); INSERT INTO __pg_test_table(some_value,__rel)VALUES(9,6); INSERT INTO __pg_test_table(some_value,__rel)VALUES(10,6); SELECT xmlagg( xmlconcat( xmlelement(name test_element, xmlattributes( 0 AS m ), xmlforest( dh.id AS i ,dh.some_value AS sv )--xmlforest )--test_element )--xmlconcat ORDER BY id DESC )--xmlagg FROM __pg_test_table AS dh WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;
[GENERAL] pg 9.2.4 dblink
Hi, I want to talk to multiple db's in one session. Is dblink the best solution or is there another way without installing dblink? Best, Peter
[GENERAL] Identifier gets truncated
When an identifier has more then 63 chars it gets truncated. This is fine. However PostgreSQL fails to notice that it might truncate to an identifier which already exist. Does PostgreSQL got a setting that will never create a duplicate identifier? really_long_name_table_A -> really_long_na really_long_name_table_B -> really_long_na Peter
Re: [GENERAL] execute if statement
To give a better idea: DO $$ DECLARE v_string text = 'raise notice ''%'', ''this could be sql with if statement...'';'; BEGIN if 1=1 then raise notice '%', ' first notice has been raised...'; end if; EXECUTE v_string;--this fails END; $$ LANGUAGE plpgsql; I do not wish to create a function for each query I have. 2012/12/1 Peter Kroon > M... > > How do I execute dynamic sql that starts with an if statement. > I'm converting mssql code to pgsql. > > > 2012/12/1 Raymond O'Donnell > >> On 01/12/2012 19:11, Peter Kroon wrote: >> > How can I achieve this? >> > >> > EXECUTE ' >> > >> > if 1=1 then >> > raise notice ''%'', ''notice has been raised...''; >> > end if; >> > >> > '; >> >> I think EXECUTE used like this is available only in a function: >> >> create or replace function my_function() >> returns void >> as >> $$ >> begin >> execute >> end; >> $$ >> language plpgsql; >> >> In "ordinary" SQL, EXECUTE executes a prepared statement. >> >> Ray. >> >> >> -- >> Raymond O'Donnell :: Galway :: Ireland >> r...@iol.ie >> > >
Re: [GENERAL] execute if statement
M... How do I execute dynamic sql that starts with an if statement. I'm converting mssql code to pgsql. 2012/12/1 Raymond O'Donnell > On 01/12/2012 19:11, Peter Kroon wrote: > > How can I achieve this? > > > > EXECUTE ' > > > > if 1=1 then > > raise notice ''%'', ''notice has been raised...''; > > end if; > > > > '; > > I think EXECUTE used like this is available only in a function: > > create or replace function my_function() > returns void > as > $$ > begin > execute > end; > $$ > language plpgsql; > > In "ordinary" SQL, EXECUTE executes a prepared statement. > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie >
[GENERAL] execute if statement
How can I achieve this? EXECUTE ' if 1=1 then raise notice ''%'', ''notice has been raised...''; end if; ';
Re: [GENERAL] set value var via execute
Go it: execute 'select id from '||v_table||' order by random() limit 1' into v_holder; 2012/11/29 Peter Kroon > --begin this > fails-- > v_holder = execute 'select id from '||v_table||' order by random() > limit 1'; > --end this > fails--- >
[GENERAL] set value var via execute
Is it possible to set the value of a var via execute? drop table if exists __test; create unlogged table __test( id int ); DO $$ DECLARE v_holder int; v_table text = 'table'; v_record_0 text[]; v_id int; BEGIN execute ' insert into __test(id) select id from '||v_table||' order by random() limit 2 '; v_id = (select id from __test limit 1); --begin this fails-- v_holder = execute 'select id from '||v_table||' order by random() limit 1'; --end this fails--- v_record_0 := array( SELECT id FROM table order by random() --limit 2 ); raise notice '%', v_record_0; END; $$ LANGUAGE plpgsql;
Re: [GENERAL] Npgsql
>Do you have more than one .net installation? Yes 2 and 4. I've tried with both pools and get same result. >Also, can you try to add a bin folder in the root of your web app and put npgsql.dll and mono.security there? You mean create a folder called bin in: C:\inetpub\wwwroot ? 2012/11/26 Francisco Figueiredo Jr. > > Hm, this is very strange. Somehow your vb. Net isn't able to find > npgsql in the gac. Do you have more than one .net installation? > > Also, can you try to add a bin folder in the root of your web app and put > npgsql.dll and mono.security there? > > Sorry for not being very helpful. > Em 26/11/2012 18:51, "Peter Kroon" escreveu: > > Installed as well. >> >> C:\Program Files\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools>gacutil >> -l mono.security >> Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.17626 >> Copyright (c) Microsoft Corporation. All rights reserved. >> >> The Global Assembly Cache contains the following assemblies: >> mono.security, Version=2.0.0.0, Culture=neutral, >> PublicKeyToken=0738eb9f132ed7 >> 56, processorArchitecture=MSIL >> >> Number of items = 1 >> >> >> 2012/11/26 Francisco Figueiredo Jr. >> >>> >>> Ok, and what about mono.security? Npgsql depends on it and it should be >>> in the gac as well. >>> Em 26/11/2012 18:36, "Peter Kroon" escreveu: >>> >>> It appears to be installed. >>>> >>>> C:\Program Files\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 >>>> Tools>gacutil -l Npgsql >>>> Microsoft (R) .NET Global Assembly Cache Utility. Version >>>> 4.0.30319.17626 >>>> Copyright (c) Microsoft Corporation. All rights reserved. >>>> >>>> The Global Assembly Cache contains the following assemblies: >>>> Npgsql, Version=2.0.12.0, Culture=neutral, >>>> PublicKeyToken=5d8b90d52f46fda7, processorArchitecture=MSIL >>>> >>>> Number of items = 1 >>>> >>>> >>>> 2012/11/26 Francisco Figueiredo Jr. >>>> >>>>> gac -l Npgsql >>>> >>>> >>>> >>
Re: [GENERAL] Npgsql
Installed as well. C:\Program Files\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools>gacutil -l mono.security Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.17626 Copyright (c) Microsoft Corporation. All rights reserved. The Global Assembly Cache contains the following assemblies: mono.security, Version=2.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed7 56, processorArchitecture=MSIL Number of items = 1 2012/11/26 Francisco Figueiredo Jr. > > Ok, and what about mono.security? Npgsql depends on it and it should be in > the gac as well. > Em 26/11/2012 18:36, "Peter Kroon" escreveu: > > It appears to be installed. >> >> C:\Program Files\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools>gacutil >> -l Npgsql >> Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.17626 >> Copyright (c) Microsoft Corporation. All rights reserved. >> >> The Global Assembly Cache contains the following assemblies: >> Npgsql, Version=2.0.12.0, Culture=neutral, >> PublicKeyToken=5d8b90d52f46fda7, processorArchitecture=MSIL >> >> Number of items = 1 >> >> >> 2012/11/26 Francisco Figueiredo Jr. >> >>> gac -l Npgsql >> >> >>
Re: [GENERAL] Npgsql
It appears to be installed. C:\Program Files\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools>gacutil -l Npgsql Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.17626 Copyright (c) Microsoft Corporation. All rights reserved. The Global Assembly Cache contains the following assemblies: Npgsql, Version=2.0.12.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7, processorArchitecture=MSIL Number of items = 1 2012/11/26 Francisco Figueiredo Jr. > gac -l Npgsql
Re: [GENERAL] create table in memory
Could you provide an example? Fo me: Drop/Creat/populating tables inside a function are slow. Creating tables outside a function and populating he table inside a function is fast.. 2012/11/24 Craig Ringer > On 11/24/2012 02:15 AM, Peter Kroon wrote: > > I found out that declaring tables outside of functions increases the > > execution time of the function. > Strictly, what's probably happening is that creating a table in the same > transaction as populating it is a lot faster than creating it, > committing, and populating it in a new transaction. In the 1st case WAL > logging for the heap can be avoided if you aren't using replication or > PITR (ie wal_level is minimal). > > Functions are automatically wrapped in a transaction if you don't open > one explicitly so doing a CREATE TABLE inside a function will be > quicker. The same result should be achieved by beginning a transaction, > creating the table, then calling the function. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > >
Re: [GENERAL] ERROR: query has no destination for result data
>Reinterpreting the question and taking the pseudocode semi-literally is >the following closer to what was asked? No, I don't want to use/create a function. Best, Peter 2012/11/24 Gavan Schneider > On Friday, November 23, 2012 at 21:36, Peter Kroon wrote: > > Hello, >> >> I wish to return the SELECT statement. >> Ho can I achieve this? >> >> DO $$ > > DECLARE v_some_id int=14; > > BEGIN > /* > more queries here... > */ > SELECT 'this is text'; > END > $$ LANGUAGE plpgsql; > >> >> Best, >> Peter Kroon >> >> Reinterpreting the question and taking the pseudocode semi-literally is > the following closer to what was asked? > ref. 39.2. Structure of PL/pgSQL > <http://www.postgresql.org/**docs/9.2/static/plpgsql-** > structure.html<http://www.postgresql.org/docs/9.2/static/plpgsql-structure.html> > > > > pendari=> CREATE FUNCTION somefunc() RETURNS text AS $$ > pendari$> > pendari$> DECLARE > pendari$> v_some_id int=14; > pendari$> BEGIN > pendari$> /* > pendari$> more queries here... > pendari$> */ > pendari$> RETURN 'this is text'::text; > pendari$> END; > pendari$> $$ LANGUAGE plpgsql; > CREATE FUNCTION > pendari=> select somefunc(); >somefunc > -- > this is text > (1 row) > > pendari=> > > Regards > Gavan > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> >
Re: [GENERAL] Npgsql
The actual error message was in Dutch. In my previous post I've added: "It's unable to find/load dependencies." That's the translation. I don't have a project. I have a vb.net page, like -> Default.aspx I used the stackbuilder to install Npgsql and altered nothing else. 2012/11/26 Glyn Astill > An actual error message would be useful, but did you add a reference to > the assembly in your project? > > > -- > *From:* Peter Kroon > *To:* "pgsql-general@postgresql.org" > *Sent:* Friday, 23 November 2012, 18:13 > *Subject:* [GENERAL] Npgsql > > I've installed Npgsql via Application stack builder without a problem(I > guess, no error messages seen). > > http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html > The projectpage tells me I have to add: > > <%@ Assembly name="System.Data" %> > <%@ Assembly name="Npgsql" %> > > > When I do this I get an error: > > Regel 1: <%@ Page Language="VB" Debug="true" %> > Regel 2: <%@ Import Namespace="System.Data.Odbc" %>Regel 3: <%@ Assembly > name="Npgsql" %>Regel 4: <% > Regel 5: > > It's unable to find/load dependencies. > Has anyone ever encountered this problem? > If so, what was your solution. > > Thanks, > > Peter > > > >
Re: [GENERAL] Prevent x005F from xml
I came up with this.
select replace( xmlelement(name my_xslt_tag,(select xmlparse(content
'')))::text ,'_x005F','')::xml;
It works but it's not very fast.
2012/11/23 Peter Kroon
> When I run this query:
> SELECT
> xmlelement(name my_xslt_tag,
> (SELECT XMLPARSE (CONTENT ''))
> )
>
> I get this result:
>
> ""
>
> Running soemthing similar in mssql prevents the x005F
> SELECT 'data' AS p,'data' AS k
> FOR XML RAW('xmlst')
>
> How do I prevent the x005F in the tagname without changing the tagname?
>
> Best,
> Peter Kroon
>
[GENERAL] alter sequence
ALTER SEQUENCE (select pg_get_serial_sequence('table', 'id')) RESTART WITH
1;
The query fails:
ALTER SEQUENCE (select pg_get_serial_sequence('table...
it's because of the (
I guess I'm using the wrong syntax.
It is possible this way? Would be great!
Best,
Peter
Re: [GENERAL] create table in memory
I found out that declaring tables outside of functions increases the execution time of the function. And CREATE UNLOGGED TABLE is very fast. 2012/11/23 Peter Kroon > I've put up a small test case for creating TEMP and UNLOGGED tables. > DROP TABLE IF EXISTS test CASCADE; > CREATE TEMP TABLE test( > id serial, > the_value text > ); > Exec time: 54ms > > DROP TABLE IF EXISTS test CASCADE; > CREATE UNLOGGED TABLE test( > id serial, > the_value text > ); > Exec time: 198ms > > There is a significant difference. > > Also I need those tables per session, so creating and dropping with TEMP > tables appear to be faster. > > Best, > Peter KRoon > > > > 2012/11/23 Raghavendra > >> >> On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon wrote: >> >>> I've converted some mssql functions and they appear to be slower in >>> pgsql. >>> I use a lot of declared tables in mssql as they are created in memory. >>> Which makes it very fast. >>> >>> 2012/11/23 Peter Kroon >>> >>>> Is a temp table created to memory(ram) or disk? >>>> I've converted some msssq >>>> >>> >>> >> Not exactly as MS Sql declare tables. >> In PostgreSQL, TEMP tables are session-private. These tables are session >> based and stored in a special schema and visible only to the backend which >> has created. Memory management is controlled with temp_buffer(shared by all >> backends) in postgresql.conf. >> >> You should check UNLOGGED tables of same family, these tables are visible >> to all the backends and data shared across backends. >> Since, data is not written to WAL's you should get better performance. >> >> --- >> Regards, >> Raghavendra >> EnterpriseDB Corporation >> Blog: http://raghavt.blogspot.com/ >> > >
[GENERAL] Npgsql
I've installed Npgsql via Application stack builder without a problem(I guess, no error messages seen). http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html The projectpage tells me I have to add: <%@ Assembly name="System.Data" %> <%@ Assembly name="Npgsql" %> When I do this I get an error: Regel 1: <%@ Page Language="VB" Debug="true" %> Regel 2: <%@ Import Namespace="System.Data.Odbc" %>Regel 3: <%@ Assembly name="Npgsql" %>Regel 4: <% Regel 5: It's unable to find/load dependencies. Has anyone ever encountered this problem? If so, what was your solution. Thanks, Peter
Re: [GENERAL] ERROR: query has no destination for result data
OK, but how do I run some SQL in pgAdmin with declared variables? 2012/11/23 Pavel Stehule > 2012/11/23 Peter Kroon : > > So this means it's unable to return data? > > yes, it means :( > > DO "is" void function, so you cannot to return anything > > Regards > > Pavel Stehule > > > > > > > 2012/11/23 Bartosz Dmytrak > >> > >> Hi, > >> according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html > >> DO returns void: > >> "The code block is treated as though it were the body of a function with > >> no parameters, returning void." > >> > >> Regars > >> Bartek > >> > >> Pozdrawiam, > >> Bartek > >> > >> > >> > >> 2012/11/23 Peter Kroon > >>> > >>> Hello, > >>> > >>> I wish to return the SELECT statement. > >>> Ho can I achieve this? > >>> > >>> DO $$ > >>> > >>> DECLARE v_some_id int=14; > >>> > >>> BEGIN > >>> /* > >>> more queries here... > >>> */ > >>> SELECT 'this is text'; > >>> END > >>> $$ LANGUAGE plpgsql; > >>> > >>> Best, > >>> Peter Kroon > >>> > >> > > >
Re: [GENERAL] ERROR: query has no destination for result data
Thanks, I'll have a look at this. 2012/11/23 Pavel Stehule > 2012/11/23 Peter Kroon : > > OK, but how do I run some SQL in pgAdmin with declared variables? > > pgAdmin has own client language similar to T-SQL > http://www.pgadmin.org/docs/dev/pgscript.html - but it is client side > language > > if you like server side code, then you have to write table function. > > Regards > > Pavel > > > > > > > > 2012/11/23 Pavel Stehule > >> > >> 2012/11/23 Peter Kroon : > >> > So this means it's unable to return data? > >> > >> yes, it means :( > >> > >> DO "is" void function, so you cannot to return anything > >> > >> Regards > >> > >> Pavel Stehule > >> > >> > > >> > > >> > 2012/11/23 Bartosz Dmytrak > >> >> > >> >> Hi, > >> >> according to doc: > http://www.postgresql.org/docs/9.2/static/sql-do.html > >> >> DO returns void: > >> >> "The code block is treated as though it were the body of a function > >> >> with > >> >> no parameters, returning void." > >> >> > >> >> Regars > >> >> Bartek > >> >> > >> >> Pozdrawiam, > >> >> Bartek > >> >> > >> >> > >> >> > >> >> 2012/11/23 Peter Kroon > >> >>> > >> >>> Hello, > >> >>> > >> >>> I wish to return the SELECT statement. > >> >>> Ho can I achieve this? > >> >>> > >> >>> DO $$ > >> >>> > >> >>> DECLARE v_some_id int=14; > >> >>> > >> >>> BEGIN > >> >>> /* > >> >>> more queries here... > >> >>> */ > >> >>> SELECT 'this is text'; > >> >>> END > >> >>> $$ LANGUAGE plpgsql; > >> >>> > >> >>> Best, > >> >>> Peter Kroon > >> >>> > >> >> > >> > > > > > >
Re: [GENERAL] ERROR: query has no destination for result data
Yes, but this means I have to create a function which is something I don't want. I just want to debug some of my code in the related function. So what I want is in pgAdmin declare some vars and run the sql and get the result. 2012/11/23 John R Pierce > On 11/23/12 2:53 AM, Peter Kroon wrote: > >> I get another error: >> ERROR: cannot use RETURN QUERY in a non-SETOF function >> >> > what is your function deined to return? > > a query returns a set of records, even if that set is 1 record of 1 field > (like, select 'some text';) > > you could declare a record variable, and use SELECT ... INTO myrecordvar > [FROM ...]; > http://www.postgresql.org/**docs/current/static/plpgsql-** > statements.html#PLPGSQL-**STATEMENTS-SQL-ONEROW<http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW> > then return a field of that record variable. > > see this example... > http://www.postgresql.org/**docs/current/static/plpgsql-** > control-structures.html#**PLPGSQL-RECORDS-ITERATING<http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING> > ... for how you would loop through query results > > > -- > john r pierceN 37, W 122 > santa cruz ca mid-left coast > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> >
[GENERAL] Prevent x005F from xml
When I run this query:
SELECT
xmlelement(name my_xslt_tag,
(SELECT XMLPARSE (CONTENT ''))
)
I get this result:
""
Running soemthing similar in mssql prevents the x005F
SELECT 'data' AS p,'data' AS k
FOR XML RAW('xmlst')
How do I prevent the x005F in the tagname without changing the tagname?
Best,
Peter Kroon
Re: [GENERAL] ERROR: query has no destination for result data
When using plain SQL I get this message: ERROR: language "sql" does not support inline code execution When removing the BEGIN+END block statements the message persists. 2012/11/23 Craig Ringer > On 11/23/2012 06:53 PM, Peter Kroon wrote: > > When using: > RETURN QUERY( > SELECT 'this is text' > ); > > I get another error: > ERROR: cannot use RETURN QUERY in a non-SETOF function > > > Use a plain SQL function instead of PL/PgSQL, or use `SELECT INTO` and > ordinary `RETURN`. > > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > >
Re: [GENERAL] ERROR: query has no destination for result data
So this means it's unable to return data? 2012/11/23 Bartosz Dmytrak > Hi, > according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html > DO returns void: > *"The code block is treated as though it were the body of a function with > no parameters, returning void."* > * > * > Regars > Bartek > > Pozdrawiam, > Bartek > > > > 2012/11/23 Peter Kroon > >> Hello, >> >> I wish to return the SELECT statement. >> Ho can I achieve this? >> >> DO $$ >> >> DECLARE v_some_id int=14; >> >> BEGIN >> /* >> more queries here... >> */ >> SELECT 'this is text'; >> END >> $$ LANGUAGE plpgsql; >> >> Best, >> Peter Kroon >> >> >
Re: [GENERAL] ERROR: query has no destination for result data
When using: RETURN QUERY( SELECT 'this is text' ); I get another error: ERROR: cannot use RETURN QUERY in a non-SETOF function 2012/11/23 Craig Ringer > On 11/23/2012 06:36 PM, Peter Kroon wrote: > > Hello, > > I wish to return the SELECT statement. > Ho can I achieve this? > > > RETURN QUERY. > > See > http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html<http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html> > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > >
[GENERAL] ERROR: query has no destination for result data
Hello, I wish to return the SELECT statement. Ho can I achieve this? DO $$ DECLARE v_some_id int=14; BEGIN /* more queries here... */ SELECT 'this is text'; END $$ LANGUAGE plpgsql; Best, Peter Kroon
Re: [GENERAL] create table in memory
I've put up a small test case for creating TEMP and UNLOGGED tables. DROP TABLE IF EXISTS test CASCADE; CREATE TEMP TABLE test( id serial, the_value text ); Exec time: 54ms DROP TABLE IF EXISTS test CASCADE; CREATE UNLOGGED TABLE test( id serial, the_value text ); Exec time: 198ms There is a significant difference. Also I need those tables per session, so creating and dropping with TEMP tables appear to be faster. Best, Peter KRoon 2012/11/23 Raghavendra > > On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon wrote: > >> I've converted some mssql functions and they appear to be slower in pgsql. >> I use a lot of declared tables in mssql as they are created in memory. >> Which makes it very fast. >> >> 2012/11/23 Peter Kroon >> >>> Is a temp table created to memory(ram) or disk? >>> I've converted some msssq >>> >> >> > Not exactly as MS Sql declare tables. > In PostgreSQL, TEMP tables are session-private. These tables are session > based and stored in a special schema and visible only to the backend which > has created. Memory management is controlled with temp_buffer(shared by all > backends) in postgresql.conf. > > You should check UNLOGGED tables of same family, these tables are visible > to all the backends and data shared across backends. > Since, data is not written to WAL's you should get better performance. > > --- > Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ >
[GENERAL] w7 vs linux
Is pgsql faster on linux? Currently I've made an installation on W7 and the converted queries are about 3 times slower then on mssql. There's still some optimization to do tho...but the current results don't look to good.
Re: [GENERAL] create table in memory
I've converted some mssql functions and they appear to be slower in pgsql. I use a lot of declared tables in mssql as they are created in memory. Which makes it very fast. 2012/11/23 Peter Kroon > Is a temp table created to memory(ram) or disk? > I've converted some msssq >
[GENERAL] create table in memory
Is a temp table created to memory(ram) or disk? I've converted some msssq
[GENERAL] output inserted
How to I output the insert in PostgreSQL? DROP TABLE IF EXISTS a_001; CREATE TEMP TABLE a_001( vl text ); DROP TABLE IF EXISTS a_002; CREATE TEMP TABLE a_002( vl text ); INSERT INTO a_001 OUTPUT INSERTED.* INTO a_002 --mssql SELECT 'text for insertion'; SELECT vl FROM a_002;
Re: [GENERAL] declare variable in udf
So, multiple DECLARE sections are not allowed? 2012/11/21 Raymond O'Donnell > On 21/11/2012 11:42, Peter Kroon wrote: > > Hello, > > > > How do I declare a variable after BEGIN? > > I want to declare it in the if statement. > > > > DROP FUNCTION IF EXISTS tmp_test(integer); > > CREATE FUNCTION tmp_test( > > p_id integer > > ) > > RETURNS text > > AS $$ > > DECLARE the_return_value text; > > BEGIN > > DROP TABLE IF EXISTS temp_test_table; > > CREATE TEMP TABLE temp_test_table( > > some_value text > > ); > > INSERT INTO temp_test_table > > SELECT data FROM table WHERE id=p_id; > > SELECT INTO the_return_value some_value FROM temp_test_table; > > > > IF 1=1 THEN > > --how do I declare a variable here? it this possible? > > RAISE NOTICE 'this is a notice'; > > END IF; > > > > RETURN the_return_value; > > > > END; > > $$ language plpgsql > > I don't think that is possible - you have to declare all your variables > in the DECLARE section. > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie >
[GENERAL] declare variable in udf
Hello, How do I declare a variable after BEGIN? I want to declare it in the if statement. DROP FUNCTION IF EXISTS tmp_test(integer); CREATE FUNCTION tmp_test( p_id integer ) RETURNS text AS $$ DECLARE the_return_value text; BEGIN DROP TABLE IF EXISTS temp_test_table; CREATE TEMP TABLE temp_test_table( some_value text ); INSERT INTO temp_test_table SELECT data FROM table WHERE id=p_id; SELECT INTO the_return_value some_value FROM temp_test_table; IF 1=1 THEN --how do I declare a variable here? it this possible? RAISE NOTICE 'this is a notice'; END IF; RETURN the_return_value; END; $$ language plpgsql Best, Peter
[GENERAL] get source of udf
Hi list, I would like to get the source of a udf. In mssql I run this query: SELECT OBJECT_DEFINITION(OBJECT_ID) FROM sys.objects WHERE name='function_name'; And I get the entire source of the function. How must I do this in PostgreSQL? Thanks, Peter
