Re: [GENERAL] DBD::Pg 2.15.1 compilation failed
Hi Greg, hi all,
Sorry for the mail sent in HTML only...
I work with AIX 5.3 TL9 and I have installed PostgreSQL 8.3.8 from the
source postgresql-8.3.8.tar.gz.
libpq seems to have been installed during the build.
I check the README of DBD::Pg and :
1. pg_config is available and returns :
$ pg_config
BINDIR = /usr/local/pgsql/bin
DOCDIR = /usr/local/pgsql/doc
INCLUDEDIR = /usr/local/pgsql/include
PKGINCLUDEDIR = /usr/local/pgsql/include
INCLUDEDIR-SERVER = /usr/local/pgsql/include/server
LIBDIR = /usr/local/pgsql/lib
PKGLIBDIR = /usr/local/pgsql/lib
LOCALEDIR =
MANDIR = /usr/local/pgsql/man
SHAREDIR = /usr/local/pgsql/share
SYSCONFDIR = /usr/local/pgsql/etc
PGXS = /usr/local/pgsql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = 'CFLAGS=-maix64' 'LDFLAGS=-maix64 -Wl,-bbigtoc'
CC = gcc
CPPFLAGS =
CFLAGS = -maix64 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
CFLAGS_SL =
LDFLAGS = -maix64 -Wl,-bbigtoc
-Wl,-blibpath:/usr/local/pgsql/lib:/usr/lib:/lib
LDFLAGS_SL = -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE
LIBS = -lpgport -lz -lreadline -lld -lm
VERSION = PostgreSQL 8.3.8
2. libpq seems to be available on my server :
# find . -name 'libpq*' -print
./usr/local/pgsql/doc/html/libpq-async.html
./usr/local/pgsql/doc/html/libpq-build.html
./usr/local/pgsql/doc/html/libpq-cancel.html
./usr/local/pgsql/doc/html/libpq-connect.html
./usr/local/pgsql/doc/html/libpq-control.html
./usr/local/pgsql/doc/html/libpq-copy.html
./usr/local/pgsql/doc/html/libpq-envars.html
./usr/local/pgsql/doc/html/libpq-example.html
./usr/local/pgsql/doc/html/libpq-exec.html
./usr/local/pgsql/doc/html/libpq-fastpath.html
./usr/local/pgsql/doc/html/libpq-ldap.html
./usr/local/pgsql/doc/html/libpq-misc.html
./usr/local/pgsql/doc/html/libpq-notice-processing.html
./usr/local/pgsql/doc/html/libpq-notify.html
./usr/local/pgsql/doc/html/libpq-pgpass.html
./usr/local/pgsql/doc/html/libpq-pgservice.html
./usr/local/pgsql/doc/html/libpq-ssl.html
./usr/local/pgsql/doc/html/libpq-status.html
./usr/local/pgsql/doc/html/libpq-threading.html
./usr/local/pgsql/doc/html/libpq.html
./usr/local/pgsql/lib/libpq.a
./usr/local/pgsql/include/libpq
./usr/local/pgsql/include/libpq/libpq-fs.h
./usr/local/pgsql/include/internal/libpq
./usr/local/pgsql/include/internal/libpq-int.h
./usr/local/pgsql/include/server/libpq
./usr/local/pgsql/include/server/libpq/libpq-be.h
./usr/local/pgsql/include/server/libpq/libpq-fs.h
./usr/local/pgsql/include/server/libpq/libpq.h
./usr/local/pgsql/include/libpq-fe.h
BUT I don't have any libpq.so file as I am on an AIX system...
3. I tried to specify POSTGRES_INCLUDE and POSTGRES_LIB but the same
errors occur during the make process :
$ make
cp lib/Bundle/DBD/Pg.pm blib/lib/Bundle/DBD/Pg.pm
cp Pg.pm blib/lib/DBD/Pg.pm
/usr/bin/perl -e 'use ExtUtils::Mksymlists; Mksymlists(NAME
= DBD::Pg, DL_FUNCS = { }, FUNCLIST = [], DL_VARS = []);'
/usr/bin/perl -p -e s/~DRIVER~/Pg/g; s/^do\(/dontdo\(/
/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI/Driver.xst
Pg.xsi
/usr/bin/perl /usr/opt/perl5/lib/5.8.2/ExtUtils/xsubpp -typemap
/usr/opt/perl5/lib/5.8.2/ExtUtils/typemap Pg.xs Pg.xsc mv Pg.xsc Pg.c
cc_r -c -I/usr/local/pgsql/include
-I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI
-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384
-qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32
-D_LARGE_FILES -qlonglong -DPGLIBVERSION=80308 -DPGDEFPORT=5432 -O
-DPERL_EXTMALLOC_DEF -Dmalloc=Perl_malloc -Dfree=Perl_mfree
-Drealloc=Perl_realloc -Dcalloc=Perl_calloc -DVERSION=\2.15.1\
-DXS_VERSION=\2.15.1\
-I/usr/opt/perl5/lib/5.8.2/aix-thread-multi/CORE Pg.c
cc_r -c -I/usr/local/pgsql/include
-I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI
-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384
-qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32
-D_LARGE_FILES -qlonglong -DPGLIBVERSION=80308 -DPGDEFPORT=5432 -O
-DPERL_EXTMALLOC_DEF -Dmalloc=Perl_malloc -Dfree=Perl_mfree
-Drealloc=Perl_realloc -Dcalloc=Perl_calloc -DVERSION=\2.15.1\
-DXS_VERSION=\2.15.1\
-I/usr/opt/perl5/lib/5.8.2/aix-thread-multi/CORE dbdimp.c
cc_r -c -I/usr/local/pgsql/include
-I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI
-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384
-qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32
-D_LARGE_FILES -qlonglong -DPGLIBVERSION=80308 -DPGDEFPORT=5432 -O
-DPERL_EXTMALLOC_DEF -Dmalloc=Perl_malloc -Dfree=Perl_mfree
-Drealloc=Perl_realloc -Dcalloc=Perl_calloc -DVERSION=\2.15.1\
-DXS_VERSION=\2.15.1\
-I/usr/opt/perl5/lib/5.8.2/aix-thread-multi/CORE quote.c
quote.c, line 334.16: 1506-068 (W) Operation between types char* and
const char* is not allowed.
cc_r -c -I/usr/local/pgsql/include
-I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI
-D_ALL_SOURCE
Re: [GENERAL] Returning bigint from C extension
On Mon, Nov 23, 2009 at 09:58:21AM +0200, Jason Armstrong wrote: How can I return a bigint value from a C extension function? I have a table with a bytea column: CREATE TABLE mydata(data bytea); I would like to select and index based on a subset of the data: CREATE FUNCTION get_key(bytea) returns BIGINT AS '/lib/data.so' LANGUAGE C IMMUTABLE; CREATE INDEX mydata_key_idx on mydata(get_key(data)); For a bigint you need to use the right return type: PG_RETURN_INT64() (And, as a side note, is the above code the correct way to use bytea data from C?) Looks ok. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] ora2pg and DBD::Pg
Hi all, And what about on the fly please ? As I encounter compilation problem on AIX 5.3, I am wondering if DBD::Pg is necessary to use ora2pg... Thank you for your help, Regards, Alexandra Stephen Frost a écrit : * Alexandra Roy (alexandra@bull.net) wrote: Does someone can explain me that is under 'on the fly' please ? Concerning the documentation of ora2pg, is it the good link ? It worked well for me, using it mainly to copy table structures and data. I was doing a one-time move to PG though, not trying to keep Oracle and PG in sync. I don't recall it being of much help with moving functions and the like, but that's a much more complicated problem. Stephen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Updating column on row update
2009/11/23 Tom Lane t...@sss.pgh.pa.us CREATE OR REPLACE has got far safer semantics from the viewpoint of a script that wants to bull through without having any actual error handling (which is more or less the scenario we are arguing here, no?) After successful execution of the command you know exactly what properties the object has got. Whether it would be sensible to have CREATE OR REPLACE semantics for a language is something I'm not very clear on. It seems like changing any of the properties of a pg_language entry could be rather fatal from the viewpoint of an existing function using the language. [ thinks for awhile... ] Actually, CREATE LANGUAGE is unique among creation commands in that the common cases have no parameters, at least not since we added pg_pltemplate. So you could imagine defining CINE for a language as disallowing any parameters and having these semantics: * language not present - create from template * language present, matches template - OK, do nothing * language present, does not match template - report error This would meet the objection of not being sure what the state is after successful execution of the command. It doesn't scale to any other object type, but is it worth doing for this one type? regards, tom lane Actually, I prefer CREATE OR REPLACE over CINE, at least for the majority of the creations, especially since it would be more consistent with what we have for functions. If there must be an exception for languages, it would make sense from what you describe above. As for having plpgsql installed by default, are there any security implications? If not, I can only see it as an advantage. At the moment we're having to resort to a bit of a hack using a CASE statement in a plain SQL function as mentioned earlier in this thread. Thom
Re: [GENERAL] DBD::Pg 2.15.1 compilation failed
Hi all,
I am still trying to build DBD::Pg but I have another question.
Is it possible to do a 64-build of DBD::Pg ?
I ask this because PostgreSQL 8.3.8 has been compiled in 64 bits mode
and if DBD::Pg expects to find 32 bits library, this can explain my
problem...
Any idea ?
Thanks,
Regards,
Alexandra
Alexandra Roy a écrit :
Hi Greg, hi all,
Sorry for the mail sent in HTML only...
I work with AIX 5.3 TL9 and I have installed PostgreSQL 8.3.8 from the
source postgresql-8.3.8.tar.gz.
libpq seems to have been installed during the build.
I check the README of DBD::Pg and :
1. pg_config is available and returns :
$ pg_config
BINDIR = /usr/local/pgsql/bin
DOCDIR = /usr/local/pgsql/doc
INCLUDEDIR = /usr/local/pgsql/include
PKGINCLUDEDIR = /usr/local/pgsql/include
INCLUDEDIR-SERVER = /usr/local/pgsql/include/server
LIBDIR = /usr/local/pgsql/lib
PKGLIBDIR = /usr/local/pgsql/lib
LOCALEDIR =
MANDIR = /usr/local/pgsql/man
SHAREDIR = /usr/local/pgsql/share
SYSCONFDIR = /usr/local/pgsql/etc
PGXS = /usr/local/pgsql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = 'CFLAGS=-maix64' 'LDFLAGS=-maix64 -Wl,-bbigtoc'
CC = gcc
CPPFLAGS =
CFLAGS = -maix64 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
CFLAGS_SL =
LDFLAGS = -maix64 -Wl,-bbigtoc
-Wl,-blibpath:/usr/local/pgsql/lib:/usr/lib:/lib
LDFLAGS_SL = -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE
LIBS = -lpgport -lz -lreadline -lld -lm
VERSION = PostgreSQL 8.3.8
2. libpq seems to be available on my server :
# find . -name 'libpq*' -print
./usr/local/pgsql/doc/html/libpq-async.html
./usr/local/pgsql/doc/html/libpq-build.html
./usr/local/pgsql/doc/html/libpq-cancel.html
./usr/local/pgsql/doc/html/libpq-connect.html
./usr/local/pgsql/doc/html/libpq-control.html
./usr/local/pgsql/doc/html/libpq-copy.html
./usr/local/pgsql/doc/html/libpq-envars.html
./usr/local/pgsql/doc/html/libpq-example.html
./usr/local/pgsql/doc/html/libpq-exec.html
./usr/local/pgsql/doc/html/libpq-fastpath.html
./usr/local/pgsql/doc/html/libpq-ldap.html
./usr/local/pgsql/doc/html/libpq-misc.html
./usr/local/pgsql/doc/html/libpq-notice-processing.html
./usr/local/pgsql/doc/html/libpq-notify.html
./usr/local/pgsql/doc/html/libpq-pgpass.html
./usr/local/pgsql/doc/html/libpq-pgservice.html
./usr/local/pgsql/doc/html/libpq-ssl.html
./usr/local/pgsql/doc/html/libpq-status.html
./usr/local/pgsql/doc/html/libpq-threading.html
./usr/local/pgsql/doc/html/libpq.html
./usr/local/pgsql/lib/libpq.a
./usr/local/pgsql/include/libpq
./usr/local/pgsql/include/libpq/libpq-fs.h
./usr/local/pgsql/include/internal/libpq
./usr/local/pgsql/include/internal/libpq-int.h
./usr/local/pgsql/include/server/libpq
./usr/local/pgsql/include/server/libpq/libpq-be.h
./usr/local/pgsql/include/server/libpq/libpq-fs.h
./usr/local/pgsql/include/server/libpq/libpq.h
./usr/local/pgsql/include/libpq-fe.h
BUT I don't have any libpq.so file as I am on an AIX system...
3. I tried to specify POSTGRES_INCLUDE and POSTGRES_LIB but the same
errors occur during the make process :
$ make
cp lib/Bundle/DBD/Pg.pm blib/lib/Bundle/DBD/Pg.pm
cp Pg.pm blib/lib/DBD/Pg.pm
/usr/bin/perl -e 'use ExtUtils::Mksymlists; Mksymlists(NAME
= DBD::Pg, DL_FUNCS = { }, FUNCLIST = [], DL_VARS = []);'
/usr/bin/perl -p -e s/~DRIVER~/Pg/g; s/^do\(/dontdo\(/
/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI/Driver.xst
Pg.xsi
/usr/bin/perl /usr/opt/perl5/lib/5.8.2/ExtUtils/xsubpp
-typemap /usr/opt/perl5/lib/5.8.2/ExtUtils/typemap Pg.xs Pg.xsc
mv Pg.xsc Pg.c
cc_r -c -I/usr/local/pgsql/include
-I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI
-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384
-qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32
-D_LARGE_FILES -qlonglong -DPGLIBVERSION=80308 -DPGDEFPORT=5432 -O
-DPERL_EXTMALLOC_DEF -Dmalloc=Perl_malloc -Dfree=Perl_mfree
-Drealloc=Perl_realloc -Dcalloc=Perl_calloc -DVERSION=\2.15.1\
-DXS_VERSION=\2.15.1\
-I/usr/opt/perl5/lib/5.8.2/aix-thread-multi/CORE Pg.c
cc_r -c -I/usr/local/pgsql/include
-I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI
-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384
-qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32
-D_LARGE_FILES -qlonglong -DPGLIBVERSION=80308 -DPGDEFPORT=5432 -O
-DPERL_EXTMALLOC_DEF -Dmalloc=Perl_malloc -Dfree=Perl_mfree
-Drealloc=Perl_realloc -Dcalloc=Perl_calloc -DVERSION=\2.15.1\
-DXS_VERSION=\2.15.1\
-I/usr/opt/perl5/lib/5.8.2/aix-thread-multi/CORE dbdimp.c
cc_r -c -I/usr/local/pgsql/include
-I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI
-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384
-qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32
-D_LARGE_FILES -qlonglong -DPGLIBVERSION=80308 -DPGDEFPORT=5432 -O
-DPERL_EXTMALLOC_DEF -Dmalloc=Perl_malloc -Dfree=Perl_mfree
-Drealloc=Perl_realloc
[GENERAL] Backup Postgre server
Hello, I have a pg8.3 server (an 8.4 upgrade is scheduled) on Windows. I would like an other pg server in backup in case of hardware problem on my first server. I use read/write queries and I would like the second pg become master without any human intervention. I saw Slony, but it seems that the backup server is a read-only server which is not good for me. I also saw pgpool. Is it a good solution for me? Does it work alone or with Slony? Is a Windows version exist? Finally, I saw others products like pgcluster or Bucardo, but I don't understand the differences between all those products... Can somebody help me? :) Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup Postgre server
On Mon, Nov 23, 2009 at 03:19:17AM -0800, AnthonyV wrote: Hello, I have a pg8.3 server (an 8.4 upgrade is scheduled) on Windows. I would like an other pg server in backup in case of hardware problem on my first server. I use read/write queries and I would like the second pg become master This part can be done if you separate the read queries from the ones that write and/or must be current as of the most recent commit on the master. Slony works for doing a manual failover. without any human intervention. This part is generally a very bad idea. What happens if there is a bug in the crash detection system? In broad generality, a sensor attached directly to an actuator is a booby trap, and this case is no exception. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup Postgre server
2009/11/23 AnthonyV avequ...@gmail.com: Hello, I have a pg8.3 server (an 8.4 upgrade is scheduled) on Windows. I would like an other pg server in backup in case of hardware problem on my first server. I use read/write queries and I would like the second pg become master without any human intervention. ugh. risky (see David's post) I saw Slony, but it seems that the backup server is a read-only server which is not good for me. hmm, half-true. with slony-I, the slave server is read only as long as the replication is taking place. when you detect that your master server is dead, you can issue a FAILOVER and the slave will be fully operational (R/W). see http://slony.info/documentation/failover.html I also saw pgpool. Is it a good solution for me? Does it work alone or with Slony? Is a Windows version exist? Pgpool can work alone (in so-called replication mode), but it has some important limitations (volatile functions). Is it good for you? depends on your db usage patterns (what types of queries are used) AFAIK, most real world postgres clustering setups use a combination of a load balancer (eg pgpool) and replication tool (eg Slony-I) Finally, I saw others products like pgcluster or Bucardo, but I don't understand the differences between all those products... pgcluster is (at best) and early beta. bucardo is similar to slony but claims to be asynchronous multi master (not sure how it's possible :-) did you see http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling ? did you see http://www.postgresql.org/docs/8.4/static/continuous-archiving.html ? cheers, -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup Postgre server
On 23 nov, 13:20, plk.zu...@gmail.com (Filip Rembiałkowski) wrote: 2009/11/23 AnthonyV avequ...@gmail.com: Hello, I have a pg8.3 server (an 8.4 upgrade is scheduled) on Windows. I would like an other pg server in backup in case of hardware problem on my first server. I use read/write queries and I would like the second pg become master without any human intervention. ugh. risky (see David's post) I saw Slony, but it seems that the backup server is a read-only server which is not good for me. hmm, half-true. with slony-I, the slave server is read only as long as the replication is taking place. when you detect that your master server is dead, you can issue a FAILOVER and the slave will be fully operational (R/W). see http://slony.info/documentation/failover.html I also saw pgpool. Is it a good solution for me? Does it work alone or with Slony? Is a Windows version exist? Pgpool can work alone (in so-called replication mode), but it has some important limitations (volatile functions). Is it good for you? depends on your db usage patterns (what types of queries are used) AFAIK, most real world postgres clustering setups use a combination of a load balancer (eg pgpool) and replication tool (eg Slony-I) Finally, I saw others products like pgcluster or Bucardo, but I don't understand the differences between all those products... pgcluster is (at best) and early beta. bucardo is similar to slony but claims to be asynchronous multi master (not sure how it's possible :-) did you seehttp://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connecti... ? did you seehttp://www.postgresql.org/docs/8.4/static/continuous-archiving.html ? cheers, -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.comhttp://filip.rembialkowski.net/ -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Thank you for your advices and your reactivity. I'll try to use Slony-I. Once again thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error in Loading DLL using Postgres function
Hi All, I am trying to load a dll using the Postgres function but whenever I am calling it is giving me the below error message. I am using Win32 machine and compiled and created the dll using MSVC2005. ERROR: could not load library C:/Program Files/PostgreSQL/8.3/lib/watchlist.dll: This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. Appreciate any help on this. Many thanks in advance
Re: [HACKERS] [GENERAL] Updating column on row update
On Sun, Nov 22, 2009 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Nov 22, 2009 at 6:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: CREATE IF NOT EXISTS has been proposed and rejected before, more than once. Please see the archives. Search for CINE to find the discussions. This is a good place to start: http://archives.postgresql.org/pgsql-hackers/2009-05/msg00252.php Despite Tom's assertions to the contrary, I am unable to find a clear consensus against this feature in the archives, I think you didn't look back far enough --- that issue was settled years ago. IIRC the killer argument is that after CINE you do not know the state of the object: it exists, yes, but what properties has it got? If it already existed then it's still got its old definition, which might or might not be what you're expecting. CREATE OR REPLACE has got far safer semantics from the viewpoint of a script that wants to bull through without having any actual error handling (which is more or less the scenario we are arguing here, no?) After successful execution of the command you know exactly what properties the object has got. Sure. I think that CINE only makes sense for objects for which COR can't be implemented - things that have internal substructure, like tables or tablespaces. I agree that there are pitfalls for the unwary but I still think it's better than nothing. I understand that you disagree. Whether it would be sensible to have CREATE OR REPLACE semantics for a language is something I'm not very clear on. It seems like changing any of the properties of a pg_language entry could be rather fatal from the viewpoint of an existing function using the language. [ thinks for awhile... ] Actually, CREATE LANGUAGE is unique among creation commands in that the common cases have no parameters, at least not since we added pg_pltemplate. So you could imagine defining CINE for a language as disallowing any parameters and having these semantics: * language not present - create from template * language present, matches template - OK, do nothing * language present, does not match template - report error This would meet the objection of not being sure what the state is after successful execution of the command. It doesn't scale to any other object type, but is it worth doing for this one type? CREATE OR REPLACE seems like a better fit in this case. For example, it seems plausible that someone might want to add an inline handler to a procedural language that didn't have one without dropping and recreating the language. Even changing the call handler seems like it could be potentially useful in an upgrade scenario. ...Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Updating column on row update
Thom Brown thombr...@gmail.com writes: As for having plpgsql installed by default, are there any security implications? Well, that's pretty much exactly the question --- are there? It would certainly make it easier for someone to exploit any other security weakness they might find. I believe plain SQL plus SQL functions is Turing-complete, but that doesn't mean it's easy or fast to write loops etc in it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Updating column on row update
2009/11/23 Tom Lane t...@sss.pgh.pa.us Thom Brown thombr...@gmail.com writes: As for having plpgsql installed by default, are there any security implications? Well, that's pretty much exactly the question --- are there? It would certainly make it easier for someone to exploit any other security weakness they might find. I believe plain SQL plus SQL functions is Turing-complete, but that doesn't mean it's easy or fast to write loops etc in it. regards, tom lane I personally find it more important to gracefully add plpgsql if it doesn't already exist than to rely on it already being there. In a way it wouldn't solve this problem as someone could have still removed it. Other procedural languages could benefit from some sort of check too. Thom
Re: [HACKERS] [GENERAL] Updating column on row update
Tom == Tom Lane t...@sss.pgh.pa.us writes: Thom Brown thombr...@gmail.com writes: As for having plpgsql installed by default, are there any security implications? Tom Well, that's pretty much exactly the question --- are there? It Tom would certainly make it easier for someone to exploit any other Tom security weakness they might find. I believe plain SQL plus SQL Tom functions is Turing-complete, but that doesn't mean it's easy or Tom fast to write loops etc in it. Now that we have recursive CTEs, plain SQL is turing-complete without requiring functions. (Yes, I did actually prove this a while back, by implementing one of the known-Turing-complete tag system automata as a single recursive query. This proof is pretty boring, though, because you wouldn't actually _use_ that approach in practice.) Loops in plain SQL are no problem: see generate_series. The last time we discussed this I demonstrated reasonably straightforward SQL examples of how to do things like password-cracking (and that was long before we had CTEs, so it would be even easier now); my challenge to anyone to produce examples of malicious plpgsql code that couldn't be reproduced in plain SQL went unanswered. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Updating column on row update
Tom Lane wrote: Thom Brown thombr...@gmail.com writes: As for having plpgsql installed by default, are there any security implications? Well, that's pretty much exactly the question --- are there? It would certainly make it easier for someone to exploit any other security weakness they might find. I believe plain SQL plus SQL functions is Turing-complete, but that doesn't mean it's easy or fast to write loops etc in it. That's a bit harder argument to sustain now we have recursive queries, ISTM. cheers andrew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Updating column on row update
Andrew Gierth and...@tao11.riddles.org.uk writes: Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom Well, that's pretty much exactly the question --- are there? It Tom would certainly make it easier for someone to exploit any other Tom security weakness they might find. Loops in plain SQL are no problem: see generate_series. The last time we discussed this I demonstrated reasonably straightforward SQL examples of how to do things like password-cracking (and that was long before we had CTEs, so it would be even easier now); my challenge to anyone to produce examples of malicious plpgsql code that couldn't be reproduced in plain SQL went unanswered. The fact remains though that the looping performance of anything you can cons up in straight SQL will be an order of magnitude worse than in plpgsql; and it's a notation the average script kiddie will find pretty unfamiliar. So I think this still does represent some barrier. Whether it's enough of a barrier to justify keeping plpgsql out of the default install is certainly debatable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How is the right query for this condition ?
In article 5a9699850911222009j272071fbi1dd0c40dfdf62...@mail.gmail.com, Brian Modra epai...@googlemail.com writes: 2009/11/23 Bino Oetomo b...@indoakses-online.com: Dear All Suppose I created a database with single table like this : --start-- CREATE DATABASE bino; CREATE TABLE myrecords(record text); --end and I fill myrecords with this : --start-- COPY myrecords (record) FROM stdin; 1 12 123 1234 \. --end In my bash script, I have variable called 'vseek', that will be use for query parameter. How to query the table , for (i.e): a. If vseek = '127' , I want the result is == '12' b. if vseek = '123987' , I want the result is == '123' c. if vseek = '14789' , I want the result is == '1' Kindly please give me any enlightment You can use a plpgsql to do that e.g. create or replace function getMatchingRecord(vseek text) ... For larger tables where an index search would be useful, check out pgfoundry.org/projects/prefix: CREATE TABLE myrecords ( record prefix_range NOT NULL, PRIMARY KEY (record) ); COPY myrecords (record) FROM stdin; 1 12 123 1234 \. SELECT id, record FROM myrecords WHERE record @ '127' ORDER BY length(record::text) DESC LIMIT 1; SELECT id, record FROM myrecords WHERE record @ '123987' ORDER BY length(record::text) DESC LIMIT 1; SELECT id, record FROM myrecords WHERE record @ '14789' ORDER BY length(record::text) DESC LIMIT 1; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Comprehensive operator list
Hi all, Does anyone know if there's a full list of operators and their usage anywhere? What I'm after is a combination of what is provided in separate sections in the documentation and what the pg_operator catalog lists. Or even a generic operator list. For example: @ generally means left is contained in right. Thanks Thom
[GENERAL] Fwd: PG_STANDBY ISSUE
Hi All -
Can you please help me with the issue when you get some time
regards
-- Forwarded message --
From: akp geek akpg...@gmail.com
Date: Fri, Nov 20, 2009 at 3:38 PM
Subject: PG_STANDBY ISSUE
To: pgsql-general@postgresql.org
Hi experts -
I am running into issue with pg_standby. May be my
understanding is not correct. Please help. here is what I did .
1. I made changes in the postgresql.conf ( archive_mode = on
,archive_command = 'cp -i %p /opt/postgres/archive/%f' , archive_timeout =
60s )
2. pg_ctl start -D $PGDATA -l /opt/postgres/logfile
3.postgres=# select pg_start_backup('BKP_LBL');
4.I have done the base backup and I have used the following command
for restore in the recovery.conf
5. restore_command = 'pg_standby -d -s 3 -t
/tmp/pg_standby.trigger.5432 /opt/postgres/archive/%f %p %r'
6. postgres=# select pg_stop_backup();
7. Now I have made some changes to the master database and I waited for
30 minutes
8. I have stopped the master database
9. I have started the slave. But I did not find the changes I have done
after I issued the command elect pg_stop_backup();
10. The log files have shipped to archive folder
Is there some thing wrong with the I am doing the procedure. Please help
Regards
Re: [GENERAL] Comprehensive operator list
On Mon, Nov 23, 2009 at 11:06 AM, Thom Brown thombr...@gmail.com wrote: Hi all, Does anyone know if there's a full list of operators and their usage anywhere? What I'm after is a combination of what is provided in separate sections in the documentation and what the pg_operator catalog lists. Or even a generic operator list. For example: @ generally means left is contained in right. from psql, \do \doS (which one to call depends on version) gives a lot of info. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Comprehensive operator list
2009/11/23 Merlin Moncure mmonc...@gmail.com On Mon, Nov 23, 2009 at 11:06 AM, Thom Brown thombr...@gmail.com wrote: Hi all, Does anyone know if there's a full list of operators and their usage anywhere? What I'm after is a combination of what is provided in separate sections in the documentation and what the pg_operator catalog lists. Or even a generic operator list. For example: @ generally means left is contained in right. from psql, \do \doS (which one to call depends on version) gives a lot of info. merlin Thanks Merlin. That looks useful :) Thom
Re: [GENERAL] Error in Loading DLL using Postgres function
On Mon, Nov 23, 2009 at 9:10 AM, a.bhattacha...@sungard.com wrote: Hi All, I am trying to load a dll using the Postgres function but whenever I am calling it is giving me the below error message. I am using Win32 machine and compiled and created the dll using MSVC2005. ERROR: could not load library C:/Program Files/PostgreSQL/8.3/lib/watchlist.dll: This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. I don't know for sure, but I bet it's a problem with manifest files. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG_STANDBY ISSUE
akp geek wrote:
Hi experts -
I am running into issue with pg_standby. May be my
understanding is not correct. Please help. here is what I did .
1. I made changes in the postgresql.conf ( archive_mode = on
,archive_command = 'cp -i %p /opt/postgres/archive/%f' , archive_timeout =
60s )
2. pg_ctl start -D $PGDATA -l /opt/postgres/logfile
3.postgres=# select pg_start_backup('BKP_LBL');
4.I have done the base backup and I have used the following command
for restore in the recovery.conf
5. restore_command = 'pg_standby -d -s 3 -t
/tmp/pg_standby.trigger.5432 /opt/postgres/archive/%f %p %r'
From memory, do you not want a space between .../archive/ and %f - I
think they're separate parameters.
Also, you'll want to redirect STDERR to a file - add the following to
the end of the command: 2/tmp/standby.log
That way we'll be able to see what's happening.
6. postgres=# select pg_stop_backup();
7. Now I have made some changes to the master database and I waited for
30 minutes
Or just generate lots of changes so WAL files get filled.
8. I have stopped the master database
9. I have started the slave. But I did not find the changes I have done
after I issued the command elect pg_stop_backup();
10. The log files have shipped to archive folder
Good. That's a useful fact. Let's see if anything shows up in our
standby logfile once we have it.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Updating column on row update
Tom == Tom Lane t...@sss.pgh.pa.us writes: Loops in plain SQL are no problem: see generate_series. The last time we discussed this I demonstrated reasonably straightforward SQL examples of how to do things like password-cracking (and that was long before we had CTEs, so it would be even easier now); my challenge to anyone to produce examples of malicious plpgsql code that couldn't be reproduced in plain SQL went unanswered. Tom The fact remains though that the looping performance of anything Tom you can cons up in straight SQL will be an order of magnitude Tom worse than in plpgsql; Well, let's see. How about generating all possible strings of 6 characters from A-Z? We'll just count the results for now: select count(chr(65+(i/676))||chr(65+(i/26)%26)||chr(65+i%26) ||chr(65+(j/676))||chr(65+(j/26)%26)||chr(65+j%26)) from generate_series(0,17575) i, generate_series(0,17575) j; count --- 308915776 (1 row) Time: 462570.563 ms create function foo() returns bigint language plpgsql as $f$ declare c bigint := 0; s text; begin for i in 0..17575 loop for j in 0..17575 loop s := chr(65+(i/676))||chr(65+(i/26)%26)||chr(65+i%26) ||chr(65+(j/676))||chr(65+(j/26)%26)||chr(65+j%26); c := c + 1; end loop; end loop; return c; end; $f$; select foo(); foo --- 308915776 (1 row) Time: 624809.671 ms plpgsql comes out 35% _slower_, not an order of magnitude worse. -- Andrew. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG_STANDBY ISSUE
Thanks a lot Richard. The culprit in my case is the restore command , I
modified it as follows per your instructions , it is fine now
restore_command = 'pg_standby -d -s 5 -t /tmp/pg_standby.trigger.5432
/opt/postgres/archive %f %p %r 2/tmp/standby.log'
One small doubt I have is , do we have to keep the backup_label on the
standby, I deleted it before I start the restore process , it still worked
.. Just curious
appreicate your help
Regards
On Mon, Nov 23, 2009 at 1:04 PM, Richard Huxton d...@archonet.com wrote:
akp geek wrote:
Hi experts -
I am running into issue with pg_standby. May be my
understanding is not correct. Please help. here is what I did .
1. I made changes in the postgresql.conf ( archive_mode = on
,archive_command = 'cp -i %p /opt/postgres/archive/%f' ,
archive_timeout =
60s )
2. pg_ctl start -D $PGDATA -l /opt/postgres/logfile
3.postgres=# select pg_start_backup('BKP_LBL');
4.I have done the base backup and I have used the following
command
for restore in the recovery.conf
5. restore_command = 'pg_standby -d -s 3 -t
/tmp/pg_standby.trigger.5432 /opt/postgres/archive/%f %p %r'
From memory, do you not want a space between .../archive/ and %f - I
think they're separate parameters.
Also, you'll want to redirect STDERR to a file - add the following to
the end of the command: 2/tmp/standby.log
That way we'll be able to see what's happening.
6. postgres=# select pg_stop_backup();
7. Now I have made some changes to the master database and I waited
for
30 minutes
Or just generate lots of changes so WAL files get filled.
8. I have stopped the master database
9. I have started the slave. But I did not find the changes I have
done
after I issued the command elect pg_stop_backup();
10. The log files have shipped to archive folder
Good. That's a useful fact. Let's see if anything shows up in our
standby logfile once we have it.
--
Richard Huxton
Archonet Ltd
Re: [GENERAL] PG_STANDBY ISSUE
akp geek wrote: One small doubt I have is , do we have to keep the backup_label on the standby, I deleted it before I start the restore process , it still worked .. Just curious The backup label is strictly for you to read, to help make sure you got all of the archived segments necessary for the backup to be useful. The standby doesn't actually use it for anything, it's more of a helper for a human trying to figure out what's going on. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange performance degradation
Tom Lane wrote: Lorenzo Allegrucci lorenzo.allegru...@forinicom.it writes: So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? Are you killing off any long-running transactions when you restart? After three days of patient waiting it looks like the common 'IDLE in transaction' problem.. [sorry for 80 cols] 19329 ?S 15:54 /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3/main -c config_file=/etc/postgresql/8.3/main/postgresql.conf 19331 ?Ss 3:40 \_ postgres: writer process 19332 ?Ss 0:42 \_ postgres: wal writer process 19333 ?Ss15:01 \_ postgres: stats collector process 19586 ?Ss 114:00 \_ postgres: forinicom weadmin [local] idle 20058 ?Ss 0:00 \_ postgres: forinicom weadmin [local] idle 13136 ?Ss 0:00 \_ postgres: forinicom weadmin 192.168.4.253(43721) idle in transaction My app is a Django webapp, maybe there's some bug in the Django+psycopg2 stack? Anyway, how can I get rid those idle in transaction processes? Can I just kill -15 them or is there a less drastic way to do it? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange performance degradation
In response to Lorenzo Allegrucci lorenzo.allegru...@forinicom.it: Tom Lane wrote: Lorenzo Allegrucci lorenzo.allegru...@forinicom.it writes: So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? Are you killing off any long-running transactions when you restart? After three days of patient waiting it looks like the common 'IDLE in transaction' problem.. [sorry for 80 cols] 19329 ?S 15:54 /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3/main -c config_file=/etc/postgresql/8.3/main/postgresql.conf 19331 ?Ss 3:40 \_ postgres: writer process 19332 ?Ss 0:42 \_ postgres: wal writer process 19333 ?Ss15:01 \_ postgres: stats collector process 19586 ?Ss 114:00 \_ postgres: forinicom weadmin [local] idle 20058 ?Ss 0:00 \_ postgres: forinicom weadmin [local] idle 13136 ?Ss 0:00 \_ postgres: forinicom weadmin 192.168.4.253(43721) idle in transaction My app is a Django webapp, maybe there's some bug in the Django+psycopg2 stack? Anyway, how can I get rid those idle in transaction processes? Can I just kill -15 them or is there a less drastic way to do it? Connections idle in transaction do not cause performance problems simply by being there, at least not when there are so few. If you -TERM them, any uncommitted data will be rolled back, which may not be what you want. Don't -KILL them, that will upset the postmaster. My answer to your overarching question is that you need to dig deeper to find the real cause of your problem, you're just starting to isolate it. Try turning full query logging on and track what those connections are actually doing. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] obtaining ARRAY position for a given match
Regarding this thread... I've been away for a while... But thank you to all who replied! :) BR, Pedro On 11/19/2009 07:03 PM, Sam Mason wrote: On Thu, Nov 19, 2009 at 10:47:02AM -0800, Scott Bailey wrote: Sam Mason wrote: Is idx really the best name for this? Well I used idx() because there was already a idx(int[], int) function with the _int contrib module. I don't remember ever using that before, hence my question. In other languages, it is assumed you are looking for the first index. Huh, they seem to don't they, even my old stalwart of pedantry, Haskell, follows form here. Not sure why I'd never noticed before, idx is looking more and more sensible! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] C++ and Postgis
Hi All, First off sorry for cross-posting. I'm hoping for a bigger audience... :] As for my question: How does one deal with Postgis geom fields in C++ ? Google hasn't offered much help so far. I know I can make a SELECT astext(my_geom) FROM my_table and after parsing the resulting text. This seems a bit awkward and time-consuming... Is there a better way to deal with it? Already thankful for any ideas, BR, Pedro. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange performance degradation
Bill Moran wmo...@potentialtech.com writes: In response to Lorenzo Allegrucci lorenzo.allegru...@forinicom.it: Tom Lane wrote: Are you killing off any long-running transactions when you restart? Anyway, how can I get rid those idle in transaction processes? Can I just kill -15 them or is there a less drastic way to do it? Connections idle in transaction do not cause performance problems simply by being there, at least not when there are so few. The idle transaction doesn't eat resources in itself. What it does do is prevent VACUUM from reclaiming dead rows that are recent enough that they could still be seen by the idle transaction. The described behavior sounds to me like other transactions are wasting lots of cycles scanning through dead-but-not-yet-reclaimed rows. There are some other things that also get slower as the window between oldest and newest active XID gets wider. (8.4 alleviates this problem in many cases, but the OP said he was running 8.3.) If you -TERM them, any uncommitted data will be rolled back, which may not be what you want. Don't -KILL them, that will upset the postmaster. -TERM isn't an amazingly safe thing either in 8.3. Don't you have a way to kill the client-side sessions? My answer to your overarching question is that you need to dig deeper to find the real cause of your problem, you're just starting to isolate it. Agreed, what you really want to do is find and fix the transaction leak on the client side. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] I need help creating a composite type with some sort of constraints.
I can create one or more domains, and use those to create the composite type from. But each domain can only be constrained in its own value, I can't constrain element #1's value based on what element #2's value is. If I create a domain from a composite, it will complain with a ERROR: cdt.instant_message is not a valid base type for a domain error. I can't create a trigger for a composite either (technically it's not a table, but I was hoping it might be treated as one internally). Is there any way to do this? Are there any plans to change or expand this functionality in the future? Thanks, John O. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] C++ and Postgis
Pedro Doria Meunier wrote: Hi All, First off sorry for cross-posting. I'm hoping for a bigger audience... :] As for my question: How does one deal with Postgis geom fields in C++ ? Google hasn't offered much help so far. I know I can make a SELECT astext(my_geom) FROM my_table and after parsing the resulting text. This seems a bit awkward and time-consuming... Is there a better way to deal with it? perhaps http://postgis.refractions.net/documentation/manual-1.4/ST_AsBinary.html ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] C++ and Postgis
anytime you see a codebase filled with a plethora of function pointers, LockObject and miles of code to achieve simple keyword scope (public/protected/private) then you know the product is ready to be rewritten to OO Language My suggestion would be Java (so we could use maven for version specific build) also because I havent seen anything from C++ that comes close to version specific builds Also with the Maven-nar-plugin you can incorporate native binaries if you decide for a C++ refactor.. I guess what I'm saying if you're going to bite the bullet to rewrite to C++ why not just take the extra step to rewrite to Java..yes it doesnt support multiple inheritance of classes but you *can implement multiple interfaces* Comments? Martin Gainty __ WARNING: Information contained may be deemed unsuitable for marketing, liberal-arts or business majors. Date: Mon, 23 Nov 2009 21:19:44 + From: pdo...@netmadeira.com To: postgis-us...@postgis.refractions.net CC: pgsql-general@postgresql.org Subject: [GENERAL] C++ and Postgis Hi All, First off sorry for cross-posting. I'm hoping for a bigger audience... :] As for my question: How does one deal with Postgis geom fields in C++ ? Google hasn't offered much help so far. I know I can make a SELECT astext(my_geom) FROM my_table and after parsing the resulting text. This seems a bit awkward and time-consuming... Is there a better way to deal with it? Already thankful for any ideas, BR, Pedro. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Windows 7: I wanted simpler, now it's simpler. I'm a rock star. http://www.microsoft.com/Windows/windows-7/default.aspx?h=myidea?ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_myidea:112009
[GENERAL] get a log of queries that take up a lot of CPU or take a very long time.
Is there a way I can get a list of the top 10 longest running queries for the day/week/month or the top 10 queries that took the most CPU? select * from pg_stat_activity only shows the current status. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] get a log of queries that take up a lot of CPU or take a very long time.
On Monday 23 November 2009, Tim Uckun timuc...@gmail.com wrote: Is there a way I can get a list of the top 10 longest running queries for the day/week/month or the top 10 queries that took the most CPU? select * from pg_stat_activity only shows the current status. You can enable query logging, store the logs, and parse them. -- No animals were harmed in the recording of this episode. We tried but that damn monkey was just too fast. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I need help creating a composite type with some sort of constraints.
John Oyler wrote: I can create one or more domains, and use those to create the composite type from. But each domain can only be constrained in its own value, I can't constrain element #1's value based on what element #2's value is. If I create a domain from a composite, it will complain with a ERROR: cdt.instant_message is not a valid base type for a domain error. I can't create a trigger for a composite either (technically it's not a table, but I was hoping it might be treated as one internally). Is there any way to do this? Are there any plans to change or expand this functionality in the future? Thanks, John O. For now you have to put all checks in custom constructor functions. Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multicolumn index including tsvector.
Hi everyone,
In the documentation and past messages to this list, it sounds like the
btree_gist and btree_gin modules included in contrib/ in 8.4 should give me
the functionality I'm looking for, but I don't seem to be getting the
behavior I want.
I've made an example table representing something like a simplified version
of a web discussion board.
Table public.example
Column | Type | Modifiers
-+--+---
body| text |
vectors | tsvector |
user_id | bigint |
I've got btree_gin and btree_gist installed, so I can make a composite index
on vectors and user_id (which is a bigint).
create index index_examples_gin on example using gist (user_id,vectors);
create index index_examples_gist on example using gist (user_id,vectors);
So what I'm expecting here is that it'll be able to use one of those
composite indexes to satisfy both the user_id and the vectors constraints.
That doesn't seem to be the case, based on this query plan:
explain analyze select body from example where user_id=1 and vectors @@
to_tsquery('simple', 'when') limit 10;
QUERY
PLAN
-
Limit (cost=1786.87..10510.47 rows=10 width=5) (actual
time=203.155..244.987 rows=10 loops=1)
- Bitmap Heap Scan on example (cost=1786.87..38425.99 rows=42 width=5)
(actual time=203.153..244.980 rows=10 loops=1)
Recheck Cond: (vectors @@ '''when'''::tsquery)
Filter: (user_id = 1)
- Bitmap Index Scan on index_examples_gist (cost=0.00..1786.86
rows=26535 width=0) (actual time=186.711..186.711 rows=27477 loops=1)
Index Cond: (vectors @@ '''when'''::tsquery)
Total runtime: 245.062 ms
(7 rows)
So it seems to be using the index only to satisfy the tsquery part of the
where clause, and then applying the user_id filter to the rows it fetches.
Ideally, I'd want to see it using both columns as part of the index
condition, and not using a filter at all. Is what I'm trying to achieve
possible?
Thanks in advance.
Kris
Re: [GENERAL] Multicolumn index including tsvector.
Kris Gale krisg...@gmail.com writes:
So what I'm expecting here is that it'll be able to use one of those
composite indexes to satisfy both the user_id and the vectors constraints.
That doesn't seem to be the case, based on this query plan:
explain analyze select body from example where user_id=1 and vectors @@
to_tsquery('simple', 'when') limit 10;
Try coercing the '1' to a bigint. I don't believe the btree_gist
opclasses have any support for cross-type operators.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multicolumn index including tsvector.
explain analyze select body from example where user_id=1 and vectors @@
to_tsquery('simple', 'when') limit 10;
Try coercing the '1' to a bigint. I don't believe the btree_gist
opclasses have any support for cross-type operators.
Perfect! Thanks, Tom. The query plan now shows it considering both columns
in the index condition.
Kris
Re: [HACKERS] [GENERAL] Updating column on row update
On 23/11/2009 11:35 PM, Tom Lane wrote: Andrew Gierth and...@tao11.riddles.org.uk writes: Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom Well, that's pretty much exactly the question --- are there? It Tom would certainly make it easier for someone to exploit any other Tom security weakness they might find. Loops in plain SQL are no problem: see generate_series. The last time we discussed this I demonstrated reasonably straightforward SQL examples of how to do things like password-cracking (and that was long before we had CTEs, so it would be even easier now); my challenge to anyone to produce examples of malicious plpgsql code that couldn't be reproduced in plain SQL went unanswered. The fact remains though that the looping performance of anything you can cons up in straight SQL will be an order of magnitude worse than in plpgsql; and it's a notation the average script kiddie will find pretty unfamiliar. So I think this still does represent some barrier. Whether it's enough of a barrier to justify keeping plpgsql out of the default install is certainly debatable. the average script kiddie doesn't write their own exploits. They tend to use proofs of concept created by very, very smart people involved in active security research (be it malicious or not) that've been wrapped up in easy-to-use click to exploit tools. They'll no more learn SQL to explot Pg than they learn x86 asm to write their payload injectors, or learn details about the Linux kernel to use a local root exploit. Just making it a bit harder doesn't stop determined attackers, such as security researchers, criminals seeking confidential information (credit card databases etc) or commercially-motivated black hats seeking to build botnets. Once the determined attackers find a way, the script kiddies and the botnet builders tend to follow. Any attack relying on the presence of PL/PgSQL will have to be an attack by an already-authenticated user* with an established connection. Mass botnet- or worm- style exploits are out. That said, PL/PgSQL does undeniably increase the available attack surface for a rogue authorized user, simply by being more code that has to be free of security issues. An authenticated user might seek to escalate to DB superuser priveleges, gain access to other DBs, or execute code as the postgres user to trigger a local root exploit on the hosting machine. So there is some concern, since not all authenticated users are necessarily fully trusted. It's for that reason that I proposed it being made available by default only to superusers and to the owner of the current database. If either of those are executing malicious code, you're already well and truly screwed. Thinking about it some more, though, there's nothing that'll let the DB owner (unlike the superuser) execute supplied code as the postgres user or break into other DBs, so an exploit against PL/PgSQL might still give them a way to escalate priveleges. I don't see making PL/PgSQL available by default only to a superuser as particularly useful. Anything else does increase the attack surface available for potential exploit. So the question becomes is that increase a sufficient worry to block the installation of PL/PgSQL by default ? Personally, I think not. Default installing PL/PgSQL doesn't increase the risk of a worm, which would be my main worry about enabling a feature by default. PL/PgSQL is so widely used that any security issue with it is already about as critical as it can be. Those few with significant databases who do NOT use it can drop it if they are concerned, but I sincerely doubt there are many significant production DBs out there without it installed and in use, as it's effectively a requirement for the use of triggers. So - I say go ahead and install it by default, available to all users. It's meant to be a trusted language, it's basically required for triggers, it's nearly universal anyway, and it's a pain not having it installed. If it's not to be installed by default, then a cleaner way to ensure it's installed it would be strongly desirable. (I'm also honestly not sure what relevance performance has here. If it takes an attacker 10 minutes to exploit a server rather than 1 minute, is it any less cracked? Performance is only an issue if it renders an attack impossible due to memory/storage requirements, or non-linear computation time growth. Anyway, I frequently seek to avoid PL/PgSQL, using pure SQL loops etc instead, because it's *faster* that way.) * It could, I guess, be a hijacked connection, but if you have connection hijacking going on you've already lost and have bigger things to worry about. Otherwise it's going to be a stolen username/password, or an authorized user gone rogue. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How identify a long running transaction
Using 8.4.1. How can I 1) Get the transaction id of the oldest serializable transaction and the next transaction? 2) A list of all active transactions I have searched the docs and tried Google but I must not be using the right terminology. Also, am I correct in assuming that vacuuming is not blocked by a long running read committed transaction but only by the oldest serializable transaction? Any references to relevant documentation would be appreciated. Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
