[GENERAL] Please help me debug regular segfaults on 8.3.10
Hi,
one of our pgsql instances recently started to segfault multiple times a
week. I tried a couple of things to pin it down to a certain query
or job but failed to find any pattern. All I can offer is some notes
and a set of similar looking back traces.
Thanks in advance.
Machine details
---
* CentOS release 5.4 (Final)
* Linux 2.6.18-164.15.1.el5 #1 SMP Wed Mar 17 11:30:06 EDT 2010 x86_64
x86_64 x86_64 GNU/Linux
* 4x Quad-Core AMD Opteron 8354
* 64GB RAM (ECC)
PostgreSQL packages
---
* postgresql-8.3.10-2PGDG.el5
* postgresql-contrib-8.3.10-2PGDG.el5
* postgresql-devel-8.3.10-2PGDG.el5
* postgresql-libs-8.3.10-2PGDG.el5
* postgresql-plperl-8.3.10-2PGDG.el5
* postgresql-plpython-8.3.10-2PGDG.el5
* postgresql-pltcl-8.3.10-2PGDG.el5
* postgresql-server-8.3.10-2PGDG.el5
Environment
---
* Multiple databases with a total of 1TB in size
* So far the back traces show three different databases
* Some larger hash indexes exist (requiring reindex after each crash)
* The only loaded PL is pl/pgsql
* The system is doing around 3000 TPS constantly
Things that didn't make any change
--
* Updated from 8.3.7 to 8.3.10
* Updated OS kernel
2010-05-04 | core.21207
---
Core was generated by `postgres: ('.
Program terminated with signal 11, Segmentation fault.
[New process 21207]
#0 0x0066acae in pfree ()
(gdb) bt
#0 0x0066acae in pfree ()
#1 0x00648c6e in ?? ()
#2 0x00648f34 in ?? ()
#3 0x006493d4 in RelationCacheInvalidateEntry ()
#4 0x00644fcd in ?? ()
#5 0x00644882 in ?? ()
#6 0x006448be in CommandEndInvalidationMessages ()
#7 0x00472993 in CommandCounterIncrement ()
#8 0x005342ea in ?? ()
#9 0x00534543 in SPI_execute_plan ()
#10 0x2ad2863f0148 in ?? () from /usr/lib64/pgsql/plpgsql.so
#11 0x2ad2863f1a26 in ?? () from /usr/lib64/pgsql/plpgsql.so
#12 0x2ad2863f3372 in ?? () from /usr/lib64/pgsql/plpgsql.so
#13 0x2ad2863f3ce5 in plpgsql_exec_function () from
/usr/lib64/pgsql/plpgsql.so
#14 0x2ad2863ea7be in plpgsql_call_handler () from
/usr/lib64/pgsql/plpgsql.so
#15 0x0052023e in ExecMakeFunctionResult ()
#16 0x0051d1f3 in ExecProject ()
#17 0x0052df13 in ExecResult ()
#18 0x0051cc66 in ExecProcNode ()
#19 0x0051bedf in ExecutorRun ()
#20 0x005b1481 in ?? ()
#21 0x005b2689 in PortalRun ()
#22 0x005ae3b0 in ?? ()
#23 0x005af038 in PostgresMain ()
#24 0x005856a7 in ?? ()
#25 0x0058632b in PostmasterMain ()
#26 0x0053eece in main ()
2010-04-29 | core.20832
---
Core was generated by `postgres: ('.
Program terminated with signal 11, Segmentation fault.
[New process 20832]
#0 0x0066acae in pfree ()
(gdb) bt
#0 0x0066acae in pfree ()
#1 0x00648c6e in ?? ()
#2 0x00648f34 in ?? ()
#3 0x006493d4 in RelationCacheInvalidateEntry ()
#4 0x00644fcd in ?? ()
#5 0x00644882 in ?? ()
#6 0x006448be in CommandEndInvalidationMessages ()
#7 0x00472993 in CommandCounterIncrement ()
#8 0x005342ea in ?? ()
#9 0x00534543 in SPI_execute_plan ()
#10 0x2b41879e1148 in ?? () from /usr/lib64/pgsql/plpgsql.so
#11 0x2b41879e2a26 in ?? () from /usr/lib64/pgsql/plpgsql.so
#12 0x2b41879e4372 in ?? () from /usr/lib64/pgsql/plpgsql.so
#13 0x2b41879e4ce5 in plpgsql_exec_function () from
/usr/lib64/pgsql/plpgsql.so
#14 0x2b41879db7be in plpgsql_call_handler () from
/usr/lib64/pgsql/plpgsql.so
#15 0x0052023e in ExecMakeFunctionResult ()
#16 0x0051d1f3 in ExecProject ()
#17 0x0052df13 in ExecResult ()
#18 0x0051cc66 in ExecProcNode ()
#19 0x0051bedf in ExecutorRun ()
#20 0x005b1481 in ?? ()
#21 0x005b2689 in PortalRun ()
#22 0x005ae3b0 in ?? ()
#23 0x005af038 in PostgresMain ()
#24 0x005856a7 in ?? ()
#25 0x0058632b in PostmasterMain ()
#26 0x0053eece in main ()
2010-04-27 | core.25421
---
Core was generated by `postgres: ('.
Program terminated with signal 11, Segmentation fault.
[New process 25421]
#0 0x0066acae in pfree ()
(gdb) bt
#0 0x0066acae in pfree ()
#1 0x00648c6e in ?? ()
#2 0x00648f34 in ?? ()
#3 0x006493d4 in RelationCacheInvalidateEntry ()
#4 0x00644fcd in ?? ()
#5 0x00644882 in ?? ()
#6 0x006448be in CommandEndInvalidationMessages ()
#7 0x00472993 in CommandCounterIncrement ()
#8 0x005342ea in ?? ()
#9 0x00534543 in SPI_execute_plan ()
#10 0x2b41879e1148 in ?? () from /usr/lib64/pgsql/plpgsql.so
#11 0x2b41879e2a26 in ?? () from /usr/lib64/pgsql/plpgsql.so
#12 0x2b41879e4372 in ?? () from /usr/lib64/pgsql/plpgsql.so
#13 0x2b41879e4ce5 in p
Re: [GENERAL] Please help me debug regular segfaults on 8.3.10
Alvaro Herrera wrote: pgsql wrote: Hi, one of our pgsql instances recently started to segfault multiple times a week. I tried a couple of things to pin it down to a certain query or job but failed to find any pattern. All I can offer is some notes and a set of similar looking back traces. Please install the debuginfo package(s). Have you got some external module installed? ..for whatever reason I cannot get the postmaster provided by postgresql-debuginfo-8.3.10-2PGDG.el5.x86_64.rpm to run; it immediately causes a segfault: ld-linux-x86-64[4286] general protection rip:3b2ca06471 rsp:7fff9a5077c0 error:0 However I just build 8.3.10 from source with debug enabled. As soon as it crashes I'll post the new back trace. Thanks! -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me debug regular segfaults on 8.3.10
Tom Lane wrote: pgsql writes: Looking at debug_query_string in the core dumps would at least show what SQL command is calling the function(s) --- and I wouldn't be surprised if there's exactly one function involved here. Content of debug_query_string: core.21207 $1 = 63106368 core.20832 $1 = 292449712 core.25421 $1 = 292450320 core.23631 $1 = 29245 core.9419 $1 = 284979152 core.16801 $1 = 284978992 core.32242 $1 = 284971248 core.10776 $1 = 284978832 As per Alvaro's suggestion, installing postgresql-debuginfo would make the stack traces a lot more useful, too. Build from source (without the relcache patch) with debug enabled; waiting for the next crash. Thank you -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me debug regular segfaults on 8.3.10
Tom Lane wrote: pgsql writes: Tom Lane wrote: Looking at debug_query_string in the core dumps would at least show what SQL command is calling the function(s) --- and I wouldn't be surprised if there's exactly one function involved here. Content of debug_query_string: core.21207 $1 = 63106368 Um, that's not too helpful, we want to see the string it's pointing at. Sorry about that. All statements are calling one of two pl/pgsql functions. While that information already helps me a lot, it'll take me a while to step through the code. Those functions are outer wrappers calling many other procedures. Thank you very much. -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me debug regular segfaults on 8.3.10
Tom Lane wrote: pgsql writes: Tom Lane wrote: Um, that's not too helpful, we want to see the string it's pointing at. Sorry about that. All statements are calling one of two pl/pgsql functions. While that information already helps me a lot, it'll take me a while to step through the code. Those functions are outer wrappers calling many other procedures. Well, the stack trace you showed previously indicates that the crash is happening in the outermost plpgsql function (ie, one called directly from a client SQL command). However it's certainly true that the crash might be a consequence of something that had been done a bit earlier in another function called by this one. Sorry for the late reply. The only DDL performed is indeed in the outer function and it's a TRUNCATE, immediately followed by an INSERT SELECT to repopulate the truncated table. As mentioned, I build 8.3.10 from source using --enable-debug --enable-cassert. I had issues with this version causing protection faults. Also the backtrace I got from that still doesn't include line numbers and arguments. So I assume I missed something important when doing the build? postgres[7172] general protection rip:44abd8 rsp:7fff9195a060 error:0 Core was generated by `postgres: (51'. Program terminated with signal 11, Segmentation fault. [New process 7172] #0 0x0044abd8 in index_form_tuple () (gdb) bt #0 0x0044abd8 in index_form_tuple () #1 0x0001 in ?? () #2 0x0153d968 in ?? () #3 0x00670634 in tuplesort_performsort () #4 0x in ?? () I decided to strip the debug options (as they somehow seem to cause issue on that system) and instead apply the patch you pointed out. No crashes since then anymore. Thanks for your support. -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error installing postgresq91-python package
Hi. When I run yum install postgresql91-python resul is: # yum install postgresql91-python Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirror.lug.udel.edu * extras: mirror.7x24web.net * updates: centos.mirror.choopa.net Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package postgresql91-python.i686 0:4.0-2PGDG.rhel6 will be installed --> Processing Dependency: mx for package: postgresql91-python-4.0-2PGDG.rhel6.i 686 --> Finished Dependency Resolution Error: Package: postgresql91-python-4.0-2PGDG.rhel6.i686 (pgdg91) Requires: mx You could try using --skip-broken to work around the problem You could try running: rpm -Va --nofiles --nodigest [root@282968 ~]# this is terrible to my. Any help please ? Error: Package: postgresql91-python-4.0-2PGDG.rhel6.i686 (pgdg91) Requires: mx I no find nothing about this. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-installing-postgresq91-python-package-tp5521831p5552193.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error installing postgresq91-python package
Thanks. You can see: root@s1 [/000/yuyuyum/2012/2012]# yum install mx Traceback (most recent call last): File "/usr/bin/yum", line 4, in ? import yum File "/usr/lib/python2.4/site-packages/yum/__init__.py", line 50, in ? import comps File "/usr/lib/python2.4/site-packages/yum/comps.py", line 23, in ? iterparse = cElementTree.iterparse AttributeError: 'module' object has no attribute 'iterparse' root@s1 [/000/yuyuyum/2012/2012]# 2012/3/10, Adrian Klaver-3 [via PostgreSQL] : > > > On 03/09/2012 07:48 PM, PgSQL wrote: >> Hi. >> >> When I run >> >> yum install postgresql91-python >> >> resul is: >> >> # yum install postgresql91-python >> Loaded plugins: fastestmirror >> Loading mirror speeds from cached hostfile >> * base: mirror.lug.udel.edu >> * extras: mirror.7x24web.net >> * updates: centos.mirror.choopa.net >> Setting up Install Process >> Resolving Dependencies >> --> Running transaction check >> ---> Package postgresql91-python.i686 0:4.0-2PGDG.rhel6 will be installed >> --> Processing Dependency: mx for package: >> postgresql91-python-4.0-2PGDG.rhel6.i >> 686 >> --> Finished Dependency Resolution >> Error: Package: postgresql91-python-4.0-2PGDG.rhel6.i686 (pgdg91) >> Requires: mx >> You could try using --skip-broken to work around the problem >> You could try running: rpm -Va --nofiles --nodigest >> [root@282968 ~]# >> >> >> this is terrible to my. >> >> Any help please ? >> >> Error: Package: postgresql91-python-4.0-2PGDG.rhel6.i686 (pgdg91) >> Requires: mx >> >> I no find nothing about this. > > I am guessing related to this: > > http://www.egenix.com/products/python/mxBase/ > > I don't use Fedora but I would assume: > > yum install mx > > would solve the dependency. >> > > > > > > -- > Adrian Klaver > [email protected] > > -- > Sent via pgsql-general mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://postgresql.1045698.n5.nabble.com/Error-installing-postgresq91-python-package-tp5521831p5553349.html > > To unsubscribe from Error installing postgresq91-python package, visit > http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5521831&code=Z2x1cGlhZG9AZ21haWwuY29tfDU1MjE4MzF8MTI1NjAwMzI0MA== -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-installing-postgresq91-python-package-tp5521831p5553375.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Error installing postgresq91-python package
>>>If the OP can identify and download the relevant .rpm file I download this packages: wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/elfutils-0.137-3.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/elfutils-libs-0.137-3.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/elfutils-libelf-0.137-3.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/elfutils-libs-0.137-3.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/expat-1.95.8-8.3.el5_5.3.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/gmp-4.1.4-10.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/libxml2-2.6.26-2.1.12.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/libxml2-python-2.6.26-2.1.12.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/m2crypto-0.16-8.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/python-2.4.3-44.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/python-elementtree-1.2.6-5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/python-iniparse-0.2.3-4.el5.noarch.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/python-sqlite-1.1.7-1.2.1.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/python-urlgrabber-3.1.0-6.el5.noarch.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/readline-5.1-3.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/rpm-4.4.2.3-22.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/rpm-libs-4.4.2.3-22.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/rpm-python-4.4.2.3-22.el5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/sqlite-3.3.6-5.i386.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/yum-3.2.22-37.el5.centos.noarch.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/yum-NetworkManager-dispatcher-1.1.16-16.el5.centos.noarch.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/yum-fastestmirror-1.1.16-16.el5.centos.noarch.rpm wget http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/yum-metadata-parser-1.1.2-3.el5.centos.i386.rpm But YUM conyinue whitout run. 2012/3/10, Martin Gregorie-2 [via PostgreSQL] : > > > On Sat, 2012-03-10 at 13:28 -0800, Adrian Klaver wrote: >> On 03/10/2012 09:59 AM, PgSQL wrote: >> > Thanks. >> > >> > You can see: >> > >> > root@s1 [/000/yuyuyum/2012/2012]# yum install mx >> > Traceback (most recent call last): >> > File "/usr/bin/yum", line 4, in ? >> > import yum >> > File "/usr/lib/python2.4/site-packages/yum/__init__.py", line 50, in ? >> > import comps >> > File "/usr/lib/python2.4/site-packages/yum/comps.py", line 23, in ? >> > iterparse = cElementTree.iterparse >> > AttributeError: 'module' object has no attribute 'iterparse' >> > root@s1 [/000/yuyuyum/2012/2012]# >> >> A yum problem with Python 2.4. A test on my Ubuntu machine confirmed >> that the iterparse function is not included in cElementTree shipped with >> Python 2.4 even though it is supported from 2.2+ In my previous post I >> erroneously said you where on Fedora, not Centos. Might want to file a >> bug report with the CentOS folks. Is there another mechanism to install >> mx, other than yum in the mean time? >> > If the OP can identify and download the relevant .rpm file it should be > possible to install mx with the rpm utility. > > The next time yum is used it will complain that the package database was > updated outside of it but that can be ignored: this happens every time I > upgrade Opera, which is distributed for Fedora as a manually applied > RPM. > > > Martin > > > > -- > Sent via pgsql-general mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://postgresql.1045698.n5.nabble.com/Error-installing-postgresq91-python-package-tp5521831p5553745.html > > To unsubscribe from Error installing postgresq91-python package, visit > http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5521831&code=Z2x1cGlhZG9AZ21haWwuY29tfDU1MjE4MzF8MTI1NjAwMzI0MA== -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-installing-postgresq91-python-package-tp5521831p5553759.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Error installing postgresq91-python package
Thanks.. >>>Is there another mechanism to install mx, other than yum in the mean time? I unknow if exist. But I need YUM in my CentOs 5.7 :'( Any command, any form please??? 2012/3/10, Adrian Klaver-3 [via PostgreSQL] : > > > On 03/10/2012 09:59 AM, PgSQL wrote: >> Thanks. >> >> You can see: >> >> root@s1 [/000/yuyuyum/2012/2012]# yum install mx >> Traceback (most recent call last): >> File "/usr/bin/yum", line 4, in ? >> import yum >> File "/usr/lib/python2.4/site-packages/yum/__init__.py", line 50, in ? >> import comps >> File "/usr/lib/python2.4/site-packages/yum/comps.py", line 23, in ? >> iterparse = cElementTree.iterparse >> AttributeError: 'module' object has no attribute 'iterparse' >> root@s1 [/000/yuyuyum/2012/2012]# > > A yum problem with Python 2.4. A test on my Ubuntu machine confirmed > that the iterparse function is not included in cElementTree shipped with > Python 2.4 even though it is supported from 2.2+ In my previous post I > erroneously said you where on Fedora, not Centos. Might want to file a > bug report with the CentOS folks. Is there another mechanism to install > mx, other than yum in the mean time? > >> > > > -- > Adrian Klaver > [email protected] > > -- > Sent via pgsql-general mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://postgresql.1045698.n5.nabble.com/Error-installing-postgresq91-python-package-tp5521831p5553696.html > > To unsubscribe from Error installing postgresq91-python package, visit > http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5521831&code=Z2x1cGlhZG9AZ21haWwuY29tfDU1MjE4MzF8MTI1NjAwMzI0MA== -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-installing-postgresq91-python-package-tp5521831p5553731.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Error installing postgresq91-python package
Thanks Martin. Sure!, I download this packages to same folder, but I used: rpm -Uvh *.rpm to install all this packages. PD: mi ingles es pobre, mi lenguaje es español. Gracias 2012/3/12, Martin Gregorie-2 [via PostgreSQL] : > > > On Sat, 2012-03-10 at 14:08 -0800, PgSQL wrote: >> >>>If the OP can identify and download the relevant .rpm file >> > Have you checked the CentOS bugzilla to see if the yum failure has been > reported? You should raise a bug report if it isn't there: if nobody > reports a bug its unlikely to get fixed. > >> >> I download this packages: >> > When you download rpms manually, you use the rpm command to install > the rpm packages, not yum. > >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/elfutils-0.137-3.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/elfutils-libs-0.137-3.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/elfutils-libelf-0.137-3.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/elfutils-libs-0.137-3.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/expat-1.95.8-8.3.el5_5.3.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/gmp-4.1.4-10.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/libxml2-2.6.26-2.1.12.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/libxml2-python-2.6.26-2.1.12.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/m2crypto-0.16-8.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/python-2.4.3-44.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/python-elementtree-1.2.6-5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/python-iniparse-0.2.3-4.el5.noarch.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/python-sqlite-1.1.7-1.2.1.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/python-urlgrabber-3.1.0-6.el5.noarch.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/readline-5.1-3.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/rpm-4.4.2.3-22.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/rpm-libs-4.4.2.3-22.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/rpm-python-4.4.2.3-22.el5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/sqlite-3.3.6-5.i386.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/yum-3.2.22-37.el5.centos.noarch.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/yum-NetworkManager-dispatcher-1.1.16-16.el5.centos.noarch.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/yum-fastestmirror-1.1.16-16.el5.centos.noarch.rpm >> wget >> http://mirror.centos.org/centos-5/5.7/os/i386/CentOS/yum-metadata-parser-1.1.2-3.el5.centos.i386.rpm >> > Why did you download these? Only five of them appear to have anything to > do with Python and four to be connected with yum. > > Which, if any, of them contain mx? > > Hint: try using "rpm -qip filename.rpm" or "rpm -qilp filename.rpm" to > see what is in them. > > In my Fedora15 installation the mx package is in the mx and mx-devel > packages: running "yum info '*mx*'" told me that. > > > Martin > > > > -- > Sent via pgsql-general mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://postgresql.1045698.n5.nabble.com/Error-installing-postgresq91-python-package-tp5521831p5557665.html > > To unsubscribe from Error installing postgresq91-python package, visit > http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5521831&code=Z2x1cGlhZG9AZ21haWwuY29tfDU1MjE4MzF8MTI1NjAwMzI0MA== -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-installing-postgresq91-python-package-tp5521831p5557909.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
[GENERAL] Security and performance
I'm writing the database backend to a web application. Being paranoid I want to limit the damage/exposure that the application can do. One way would be to create a database user for each application user (i.e. login name) and to create views for each user, not giving them any permissions on sensitive tables but only letting them see their own data through the views. How would that affect the database as the number of users climbs through the hundreds to the thousands? Would the thousands of views slow the database down? Is there an upper limit to the number of views? -- Bruce Bitterly it mathinketh me, that I spent mine wholle lyf in the lists against the ignorant. -- Roger Bacon, "Doctor Mirabilis" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] RE: Security and performance
On Wed, May 02, 2001 at 05:50:49PM -0700, [EMAIL PROTECTED] wrote: > > Instead of making a kajillion views > > I limit what others can see using php scripting: Wrong approach for me. I am not writing the php scripts and I don't want to trust those who are. Even if I were writing the scripts, I would not trust me. Get the security right on the database side and you don't have to worry about mistakes on the application side. If I wanted to trust the application, I'd be using mySQL. -- Bruce It is impolite to tell a man who is carrying you on his shoulders that his head smells. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Using C API
I was trying this example program out of a well known postgresql book and keep
getting errors whenever I try to compile.
Error messages ##make client1
cc -g -I /usr/include -I /usr/include/pgsql -I /usr/include/pgsql/server -c
-o client1.o client1.c
cc -g client1.o -L /usr/lib -L /usr/lib/pgsql -o client1
client1.o: In function `main':
/root/PROGRAMMING/C_API/client1.c:10: undefined reference to `PQconnectdb'
/root/PROGRAMMING/C_API/client1.c:11: undefined reference to `PQfinish'
collect2: ld returned 1 exit status
make: *** [client1] Error 1
-
--
/*
** File: client1.c
*/
#include "libpq-fe.h"
int main( void )
{
PGconn * connection;
connection = PQconnectdb( "dbname='MyTestDB'" );
## I tried the above line with
## connection = PQconnectdb( "" );
## as per the book
PQfinish( connection ) ;
return( 0 );
}
-
## File: Makefile
##
## Rules to create libpq sample applications
##
CPPFLAGS += -I /usr/include -I /usr/include/pgsql -I /usr/include/pgsql/server
CFLAGS += -g
LDFLAGS += -g
LDLIBS += -L /usr/lib -L /usr/lib/pgsql
client1: client1.o
-
following are the rpms I have installed on a RHEL5 system.
postgresql-plperl-8.2.4-1PGDG
postgresql-contrib-8.2.4-1PGDG
postgresql-python-8.1.9-1.el5
postgresql-docs-8.2.4-1PGDG
postgresql-server-8.2.4-1PGDG
postgresql-devel-8.2.4-1PGDG
postgresql-8.2.4-1PGDG
postgresql-plpython-8.2.4-1PGDG
compat-postgresql-libs-4-2PGDG.rhel4
postgresql-libs-8.2.4-1PGDG
postgresql-pltcl-8.2.4-1PGDG
Yes I see the compat one and will install rhel5 if I find one.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] pg_ident mapping Kerberos Usernames
Hi, I'm trying to get pg_ident to map "user1" and "[email protected]" to "user1" in postgres, or vice versa. I'm not picky about which way works. Kerberos authentication works. I've gotten "user1" to login successfully with a Kerberos ticket, but I'm not able to get "[email protected]" to match. Environment: * PostgreSQL 9.6 from PostgreSQL repos * CentOS 7 * FreeIPA for Kerberos, LDAP, etc. * Realm A.DOMAIN.TLD * "user1" database exists * "user1" role exists * Logging into CentOS usernames are configured to drop the domain, so they appear as "user1" rather then "[email protected]". pg_hba.conf: local all postgrespeer hostall all 127.0.0.1/32md5 hostall all ::1/128 md5 hostall all 192.168.1.0/24 gss include_realm=1 map=testnet krb_realm=A.DOMAIN.TLD #This is on one line. Thunderbird is truncating lines. pg_ident.conf: testnet/^([0-9A-Za-z_-]+)@A\.DOMAIN\.TLD$\1 testnet/^([0-9A-Za-z_-]+)$ \1 Regex that works for both in regexr.com: /^([0-9A-Za-z-_]+)(@A\.DOMAIN\.TLD)?$/gm Command and lines from pg_log: $ psql -h db0 # Logged in as user1 with Kerberos ticket < 2017-09-09 19:50:49.376 CDT - 192.168.1.201 [unknown] > LOG: connection received: host=192.168.1.201 port=44918 < 2017-09-09 19:50:49.398 CDT - 192.168.1.201 user1 > LOG: connection authorized: user=user1 database=user1 < 2017-09-09 19:50:50.912 CDT - 192.168.1.201 user1 > LOG: disconnection: session time: 0:00:01.537 user=user1 database=user1 host=192.168.1.201 port=44918 $ psql -h db0 -U [email protected] # Logged in as user1 with Kerberos ticket < 2017-09-09 19:50:54.959 CDT - 192.168.1.201 [unknown] > LOG: connection received: host=192.168.1.201 port=44920 < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 [email protected] > LOG: no match in usermap "testnet" for user "[email protected]" authenticated as "[email protected]" < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 [email protected] > FATAL: GSSAPI authentication failed for user "[email protected]" < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 [email protected] > DETAIL: Connection matched pg_hba.conf line 87: "host all all 192.168.1.0/24 gss include_realm=1 map=testnet krb_realm=A.DOMAIN.TLD" Is this something that is possible, or is it something where I need to pick one way to do it? Thanks in advance, Ryan -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_ident mapping Kerberos Usernames
On 09/10/2017 02:39 AM, Magnus Hagander wrote: On Sat, Sep 9, 2017 at 6:44 PM, <mailto:[email protected]>> wrote: Hi, I'm trying to get pg_ident to map "user1" and "[email protected]" to "user1" in postgres, or vice versa. I'm not picky about which way works. Kerberos authentication works. I've gotten "user1" to login successfully with a Kerberos ticket, but I'm not able to get "[email protected]" to match. Environment: * PostgreSQL 9.6 from PostgreSQL repos * CentOS 7 * FreeIPA for Kerberos, LDAP, etc. * Realm A.DOMAIN.TLD * "user1" database exists * "user1" role exists * Logging into CentOS usernames are configured to drop the domain, so they appear as "user1" rather then "[email protected]". pg_hba.conf: local all postgrespeer hostall all 127.0.0.1/32 <http://127.0.0.1/32> md5 hostall all ::1/128 md5 hostall all 192.168.1.0/24 <http://192.168.1.0/24> gss include_realm=1 map=testnet krb_realm=A.DOMAIN.TLD #This is on one line. Thunderbird is truncating lines. pg_ident.conf: testnet/^([0-9A-Za-z_-]+)@A\.DOMAIN\.TLD$\1 testnet/^([0-9A-Za-z_-]+)$ \1 Regex that works for both in regexr.com <http://regexr.com>: /^([0-9A-Za-z-_]+)(@A\.DOMAIN\.TLD)?$/gm Command and lines from pg_log: $ psql -h db0 # Logged in as user1 with Kerberos ticket < 2017-09-09 19:50:49.376 CDT - 192.168.1.201 [unknown] > LOG: connection received: host=192.168.1.201 port=44918 < 2017-09-09 19:50:49.398 CDT - 192.168.1.201 user1 > LOG: connection authorized: user=user1 database=user1 < 2017-09-09 19:50:50.912 CDT - 192.168.1.201 user1 > LOG: disconnection: session time: 0:00:01.537 user=user1 database=user1 host=192.168.1.201 port=44918 $ psql -h db0 -U [email protected] # Logged in as user1 with Kerberos ticket < 2017-09-09 19:50:54.959 CDT - 192.168.1.201 [unknown] > LOG: connection received: host=192.168.1.201 port=44920 < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 [email protected] > LOG: no match in usermap "testnet" for user "[email protected]" authenticated as "[email protected]" < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 [email protected] > FATAL: GSSAPI authentication failed for user "[email protected]" < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 [email protected] > DETAIL: Connection matched pg_hba.conf line 87: "host all all 192.168.1.0/24 <http://192.168.1.0/24> gss include_realm=1 map=testnet krb_realm=A.DOMAIN.TLD" Is this something that is possible, or is it something where I need to pick one way to do it? This looks like you are trying to connect with the actual username user1¡A.DOMAIN.TLD. pg_ident only sets what you are allowed to log in as, not what it will attempt. If you are using psql, you are probably doing something like "psql -h myserver". You need to add the user, so "psql -h myserver -U user1", to instruct it of which username to actually use for the login. -- Magnus Hagander Me: https://www.hagander.net/ <http://www.hagander.net/> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/> Hi Magnus, Yes, the system username is "user1", per the default ipa-client-install SSSD setup, and the map is working for that. Without the map, I have to specify the full Kerberos username, [email protected], in the psql command. Works with map: $ psql -h db0 #Implied -U user1 -d user1 $ psql -h db0 -U user1 -d user1 Does not work with map: $ psql -h db0 -U [email protected] -d user1 Works without map (provided I have a role created): $ psql -h db0 -U [email protected] -d user1 Does not work without map: $ psql -h db0 #Implied -U user1 -d user1 $ psql -h db0 -U user1 -d user1 I can get one style or the other to work, but I just can't get both to work a the same time. If this is something that can't be done, I understand, but it looks like it should be possible per the documentation. Thanks, Ryan -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_ident mapping Kerberos Usernames
On 09/09/2017 09:28 PM, rob stone wrote: On Sat, 2017-09-09 at 20:44 -0500, [email protected] wrote: Hi, I'm trying to get pg_ident to map "user1" and "[email protected]" to "user1" in postgres, or vice versa. I'm not picky about which way works. Kerberos authentication works. I've gotten "user1" to login successfully with a Kerberos ticket, but I'm not able to get "[email protected]" to match. Environment: * PostgreSQL 9.6 from PostgreSQL repos * CentOS 7 * FreeIPA for Kerberos, LDAP, etc. * Realm A.DOMAIN.TLD * "user1" database exists * "user1" role exists * Logging into CentOS usernames are configured to drop the domain, so they appear as "user1" rather then "[email protected]". pg_hba.conf: local all postgrespeer hostall all 127.0.0.1/32md5 hostall all ::1/128 md5 hostall all 192.168.1.0/24 gss include_realm=1 map=testnet krb_realm=A.DOMAIN.TLD #This is on one line. Thunderbird is truncating lines. pg_ident.conf: testnet/^([0-9A-Za-z_-]+)@A\.DOMAIN\.TLD$\1 testnet/^([0-9A-Za-z_-]+)$ \1 Regex that works for both in regexr.com: /^([0-9A-Za-z-_]+)(@A\.DOMAIN\.TLD)?$/gm Command and lines from pg_log: $ psql -h db0 # Logged in as user1 with Kerberos ticket < 2017-09-09 19:50:49.376 CDT - 192.168.1.201 [unknown] > LOG: connection received: host=192.168.1.201 port=44918 < 2017-09-09 19:50:49.398 CDT - 192.168.1.201 user1 > LOG: connection authorized: user=user1 database=user1 < 2017-09-09 19:50:50.912 CDT - 192.168.1.201 user1 > LOG: disconnection: session time: 0:00:01.537 user=user1 database=user1 host=192.168.1.201 port=44918 $ psql -h db0 -U [email protected] # Logged in as user1 with Kerberos ticket < 2017-09-09 19:50:54.959 CDT - 192.168.1.201 [unknown] > LOG: connection received: host=192.168.1.201 port=44920 < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 [email protected] > LOG: no match in usermap "testnet" for user "[email protected]" authenticated as "[email protected]" < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 [email protected] > FATAL: GSSAPI authentication failed for user "[email protected]" < 2017-09-09 19:50:55.023 CDT - 192.168.1.201 [email protected] > DETAIL: Connection matched pg_hba.conf line 87: "host all all 192.168.1.0/24 gss include_realm=1 map=testnet krb_realm=A.DOMAIN.TLD" Is this something that is possible, or is it something where I need to pick one way to do it? Thanks in advance, Ryan Hello, I think you need a line in your pg_hba.conf file along the lines of:- testnet all all 192.168.1.0/24 gss as the error message says it can't find this relationship. HTH, Rob Hi Rob, How would that work? I was under the impression the first column was for socket type and limited to local, host, hostssl, and hostnossl? Thunderbird's config has been fixed, so here is the line from pg_hba.conf line without the formatting issues: host all all 192.168.1.0/24 gss include_realm=1 map=testnet krb_realm=A.DOMAIN.TLD Thanks, Ryan -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_ident mapping Kerberos Usernames
ed -U user1 -d user1 $ psql -h db0 -U user1 -d user1 Does not work with map: $ psql -h db0 -U [email protected] -d user1 If you want that to work with the map, then you need to change the map to add the domain, rather than removing it, which is what you currently do. But it is hard to figure out what it is you actually want. You listed some cases that work and some that don't, but haven't said which ones you want to work and which you want not to work. (Presumably if you want **all** cases to work, you would just use 'trust' and be done with it.) GSSAPI is the authentication mechanism of choice, and it's working fine. Here is what I'm trying to accomplish. 'user1' == 'user1' and '[email protected]' == 'user1'. From reading the docs, this is done via the pg_ident.conf file, and from reading the logs, there is a problem with my map. Hmm... Interesting thought. *testing* It sort of works. Setting the maps below maps the users straight across. 'user1' == 'user1' and '[email protected]' == '[email protected]', so it's partially working. pg_indent.conf: testnet /^([0-9A-Za-z_-]+)@A\.DOMAIN\.TLD$ \1 testnet /^([0-9A-Za-z_-]+)@A\.DOMAIN\.TLD$ \[email protected] If it's not possible, that's fine. I'm just wondering if it can be done. I might be misunderstanding the docs or expecting too much. I'm not quite sure which it is, but it does seem like this should be possible. Let me know if I can clear anything else up. Ryan -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_ident mapping Kerberos Usernames
On 09/11/2017 08:51 AM, rob stone wrote: Hi Rob, How would that work? I was under the impression the first column was for socket type and limited to local, host, hostssl, and hostnossl? Thunderbird's config has been fixed, so here is the line from pg_hba.conf line without the formatting issues: host all all 192.168.1.0/24 gss include_realm=1 map=testnet krb_realm=A.DOMAIN.TLD Thanks, Ryan Hello Ryan, I'm probably incorrect about this as I don't use pg_ident but my understanding is that each line in pg_ident consists of three fields being:- mask-name external-credentials internal-credentials so that the external log-on is converted to its Postgres log-on and then the mask-name is used to find a line in pg_hba.conf to verify that the external-credentials were submitted from an allowable IP address. Maybe somebody more knowledgeable than myself could provide a better example. Cheers, Rob Hi Rob, That's my understanding of the syntax and workflow from the reading and small experiments I've done as well. There's two of us, so we're on our way to a consensus. :) Thanks! Ryan -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [email protected] 37% OFF on Pfizer!
http://groups.yahoo.com/group/syrilalwinl/message n Bayern 646 Ludwig III. von Bayern: Gesuch Hitlers an L. 179 Lueger, Dr. Karl, BegrunderderChristlich-sozialen Partei (s. diese): L. und die Christlich-soziale Partei 58. Burgermeister von Wien 74, 107, 108, 133 Madchenerziehung im volkischen Staat 454. Vgl. Erziehung Madchenhandel und Judentum 63 Marx, Karl, Begrunder des Marxismus 234, 420, 532. Staatslehre 434 Marxismus: Verkennen 184. Kern 351. Kulturzerstorer 69. Von der westlichen Demokratie gefordert 85. M. und Demokratie 412. M. und Judentum 350 f., 352, 498. Staatsauffassung 420. V -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table bloat in 8.3
I am somewhat new to Postgresql and am trying to figure out if I have a problem here. I have several tables that when I run VACUUM FULL on, they are under 200k, but after a day of records getting added they grow to 10 to 20 megabytes. They get new inserts and a small number of deletes and updates. A normal VACUUM does not shrink the table size, but FULL does, or dumping and restoring the database to a test server. I know that some extra space is useful so disk blocks don't need to be allocated for every insert, but this seems excessive. My question is... should I be worrying about this or is this expected behaviour? I can run a daily VACUUM but if this is indicating a configuration problem I'd like to know. Here is an example table. The disk size is reported at 14,049,280 bytes. pg_stat_user_tables for the live db... table size is 14,049,280 bytes. seq_scan | 32325 seq_tup_read | 39428832 idx_scan | 6590219 idx_tup_fetch| 7299318 n_tup_ins| 2879 n_tup_upd| 6829984 n_tup_del| 39 n_tup_hot_upd| 420634 n_live_tup | 2815 n_dead_tup | 0 And after it is dumped and restored... size is now 188,416 bytes. seq_scan | 8 seq_tup_read | 22520 idx_scan | 0 idx_tup_fetch| 0 n_tup_ins| 2815 n_tup_upd| 0 n_tup_del| 0 n_tup_hot_upd| 0 n_live_tup | 2815 n_dead_tup | 0 I checked for outstanding transactions and there are none. Thanks! -- Ian Smith -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table bloat in 8.3
On Thu, 13 Nov 2008, Scott Marlowe wrote: > On Thu, Nov 13, 2008 at 1:09 PM, David Wilson <[EMAIL PROTECTED]> wrote: > > On Thu, Nov 13, 2008 at 2:03 PM, <[EMAIL PROTECTED]> wrote: > >> I have several tables that when I run VACUUM FULL on, they are under 200k, > >> but after a day of records getting added they grow to 10 to 20 megabytes. > >> They get new inserts and a small number of deletes and updates. > > > > Can you define "small number of deletes and updates"? The stats above > > would disagree with "small". Remember that every update creates a new, > > updated version of the row, which is where the increase is coming > > from. > > And don't forget to look into failed inserts. Those too create dead tuples. I finally figured it out. I have three tables, A with child B who has a child C. I had a query that would count all the rows in C and update the total to the rows they linked to in B, and then do the same with B to A. It basicly updated every row in A and B whenever it ran. It was supposed to only run if the counts got out of sync but a copy slipped into code that got run a LOT and so that was causing those tables to grow out of control. With that query removed I am no longer seeing the db expand. Thanks for the hints! -- Ian Smith www.ian.org -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] looking for alternative to MySQL's GROUP_CONCAT function
Hi all, I'm looking into PostgreSQL. Coming from a MySQL background, I have made heavy use of its very useful GROUP_CONCAT function. You can read about the function here: http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html Is there a PostgreSQL alternative to GROUP_CONCAT? Thanks, Ken ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Rule, update and aggregate functions
Hi, I got stuck with the following problem and strangely couldn't find anything similar in the list: Assume two tables: table: warehouse, columns: id, incoming, outgoing table: articles, columns: id, articles_in_stock All I want is to create a rule, that everytime when articles leave or get in the warehouse, the sum of articles is updated in articles database. Tried : CREATE RULE upd_articles AS ON UPDATE TO warehouse DO ALSO UPDATE articles SET articles_in_stock =SUM(NEW.incoming)-SUM (NEW.outgoing) WHERE articles.id=OLD.id; All I get is a "aggregate functions not allowed on update". So I tried to wrap it in a SELECT: CREATE RULE upd_articles AS ON UPDATE TO warehouse DO ALSO UPDATE articles SET articles_in_stock =(SELECT SUM(NEW.incoming)- SUM(NEW.outgoing) FROM warehouse WHERE NEW.id=OLD.id) WHERE articles.id=OLD.id; with the same result. What is the right way to achieve my goal? Or are rules the wrong instrument for it? Any help is kindly appreciated, Thanks, Sebastian ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] immutable stable volatile
hello, I have read the documentation couple of times and I still can not figure out the following aspects. if a function does insert/update/delete it needs to be stable or volatile ? if a immutable function executes 'nextval' should itself be also volatile ? thanks, Razvan Radu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Connection specific information - Temporary table used in Sybase to store information
Hello everybody, Since I am new to PostgreSQL mailing lists, I am not sure which mailing list to use for my problem, I issue it to this group. If there is a more appropriate list, please let me know and I will happily post there. The situation: While porting an existing application from Sybase ASA to PostgreSQL 7.4 I need to access connection specific login data (user data). In the Sybase solution I used a temporary table (CREATE TEMPORARY TABLE). Each connection than had the table on connect; each client could then perform the INSERT statement without creating table. PostgreSQL differs from the behaviour as documented. A lot of (very) different clients rely upon this this standard behaviour. The question: Is there any feature/technique that a) can create temporary tables as in the SQL Standard defined b) something like "ON CONNECT DO BEGIN END"? So that the temporary table can be automatically created upon connect? Thanks in advance, Kind regards, Ralf ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Online PostgreSQL Book, Administration Section
Hello, We have been putting a lot of work into Part IV of the book. We would appreciate some fresh feedback on the chapters 9 and 10. As always, the link: http://www.opendocspublishing.com/entry.lxp?lxpe=92 Thanks! OpenDocs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Databases compared at zend.com
Hello, Heh... I wrote that :) J On Sat, 2 Jun 2001, Michael wrote: > http://www.zend.com/zend/art/databases.php > > Needless to say Postgresql comes out looking pretty good. Good to have > links like this to show the advantages of Postgresql when I'm trying to > convert others to using it. > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Elephant Book - Removed Software
Hello, We have decided that we will not be including GreatBridge PostgreSQL and instead will be focussing on the community released version. As always updated information on the book can be found on the web at: http://www.opendocspublishing.com/entry.lxp?lxpe=92 Joshua Drake ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] INSERT INTO...RETURNING with partitioned table based on trigger function
Hi - Issue: How to return a sequence value generated upon INSERT of records into a partitioned table using trigger functions (without having to insert into the child table directly). Current implementation: The master table of the partitioned table uses a trigger function to alter an incoming record on INSERT by calling `nextval()` on a sequence. The trigger function, in turn, calls a helper function that identifies the child table in which to insert the incoming records (along with the newly minted sequence value). Since the trigger function attached to the master table must return NULL when TG_OP=BEFORE (to avoid duplicating the insertion of records into the master table and the child table) and the return value is ignored when TG_OP=AFTER, the sequence value generated by the trigger function on the master table is lost (from the perspective of the external application performing the INSERT). Rationale for implementation: The partitioned table into which the external application inserts the first set of records contains summary information (i.e. aggregated data used in a report summary), which uses the sequence value as something akin to a report ID. After the external application inserts the summary data, it needs to insert detailed information underlying the summary report into a separate table (to permit drill-down reporting), which uses the sequence value generated as a foreign key against the partitioned table containing the summary information. In other words, the sequence value generated is an identifier that links the detailed report information to the report summary. Other notations/ questions: 1) it is highly preferable that the application inserting into the database not be aware of the child tables (table partitioning is somewhat fluid as new hardware becomes available, and patching the external application is best avoided). 2) multiple instances of the application may be running, so generation of the sequence number in the application is not feasible (moreover, the application is multi-threaded and additional summary data insertions may occur between the insertion of summary data and detailed data in the two partitioned tables. 3) is there a technical reason as to why the return values of trigger functions are ignored when TG_OP=AFTER for INSERT/ UPDATE operations, or is this a valid feature request for an upstream versions? -- please advise if this is already addressed in a post-v8.3 version of Postgres. Environment: Postgres v8.3 Linux 2.6.27.41-170.2.117.fc10.x86_64 Thanks.
Re: [GENERAL] INSERT INTO...RETURNING with partitioned table based on trigger function
> On 2010-12-16, pgsql(dot)30(dot)miller_2555(at)spamgourmet(dot)com > wrote: > > --0015174c1e4aaf077604977d7e62 > > Content-Type: text/plain; charset=ISO-8859-1 > > > > Hi - > > > > Issue: > > How to return a sequence value generated upon INSERT of records into a > > partitioned table using trigger functions (without having to insert into > > the > > child table directly). > > partitioning doesn't work with "INSERT ... RETURNING ..." and trigger > based partitioning. > > use a rule instead have the rule FOR EACH ROW DO INSTEAD > > SELECT insertfunc(NEW) > > and have insertfunc do the insert and return the id column. > > for declaring the function the type of NEW is table_name%ROWTYPE > Thanks. I had attempted to use rules prior to the trigger implementation, but opted for the trigger-based implementation due to easier maintenance (specific to this particular database, at least). > > 2) multiple instances of the application may be running, so generation > > of the sequence number in the application is not feasible (moreover, the > > application is multi-threaded and additional summary data insertions may > > occur between the insertion of summary data and detailed data in the two > > partitioned tables. > > another option is the application could call nextval itself or call > lastval after the insert. both of these SQL functions are thread safe. This is a great idea, and the one I'll probably end up implementing. Many thanks for the suggestion! > > > 3) is there a technical reason as to why the return values of trigger > > functions are ignored when TG_OP=AFTER for INSERT/ UPDATE operations, > > because you can't change history. > I agree that handling the return value of a trigger function when TG_OP=AFTER in such a way that alters the database itself does not make sense on its face, though I think that allowing trigger-defined return values to pass back to the trigger caller (and so on trough the call stack back to the external application) is a reasonable behaviour.
[GENERAL] [pgsql-general] Daily digest v1.8033 (18 messages)
Message Digest Volume 1 : Issue 8033 : "index" Format Messages in this Issue: 200803/1382: Re: Using tables in other PostGreSQL database 200803/1383: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1384: Re: Timezones in 8.2.7 200803/1385: Re: Timezones in 8.2.7 200803/1386: Re: pg_standby observation 200803/1387: Re: page is uninitialized --- fixing 200803/1388: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1389: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1390: pl/pgsql and controling loops 200803/1391: Re: pl/pgsql and controling loops 200803/1392: dunction issue 200803/1393: Re: pl/pgsql and controling loops 200803/1394: Re: pl/pgsql and controling loops 200803/1395: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1396: Re: pl/pgsql and controling loops 200803/1397: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1398: Re: dunction issue 200803/1399: table of US states' neighbours -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [pgsql-general] Daily digest v1.8030 (22 messages)
Message Digest Volume 1 : Issue 8030 : "index" Format Messages in this Issue: 200803/1324: Re: casting from integer to boolean 200803/1325: Re: casting from integer to boolean 200803/1326: Re: casting from integer to boolean 200803/1327: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1328: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1329: psql /usr/lib/libgcc dependency in pg 8.3 200803/1330: Re: psql /usr/lib/libgcc dependency in pg 8.3 200803/1331: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1332: Re: IBM investing in EnterpriseDB 200803/1333: Re: psql /usr/lib/libgcc dependency in pg 8.3 200803/1334: Re: IBM investing in EnterpriseDB 200803/1335: Re: IBM investing in EnterpriseDB 200803/1336: driver version 200803/1337: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1338: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1339: Re: casting from integer to boolean 200803/1340: Re: psql /usr/lib/libgcc dependency in pg 8.3 200803/1341: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1342: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1343: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1344: Re: [Mono-list] NauckIT PostgreSQL provider working with Mono? 200803/1345: Using tables in other PostGreSQL database -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [pgsql-general] Daily digest v1.8032 (18 messages)
Message Digest Volume 1 : Issue 8032 : "index" Format Messages in this Issue: 200803/1364: very delayed autovacuum on certain tables 200803/1365: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1366: Re: Performance of update 200803/1367: Re: casting from integer to boolean 200803/1368: Re: Using tables in other PostGreSQL database 200803/1369: Re: returned value from SQL statement 200803/1370: Re: page is uninitialized --- fixing 200803/1371: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1372: Re: page is uninitialized --- fixing 200803/1373: Re: page is uninitialized --- fixing 200803/1374: Re: returned value from SQL statement 200803/1375: Re: casting from integer to boolean 200803/1376: Re: Timezones in 8.2.7 200803/1377: Re: page is uninitialized --- fixing 200803/1378: Re: Timezones in 8.2.7 200803/1379: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1380: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1381: Re: Using tables in other PostGreSQL database -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [pgsql-general] Daily digest v1.8031 (18 messages)
Message Digest Volume 1 : Issue 8031 : "index" Format Messages in this Issue: 200803/1346: Re: RULES and QUALIFICATION for INSERT 200803/1347: Re: How to "use" database? 200803/1348: Re: RULES and QUALIFICATION for INSERT 200803/1349: Performance of update 200803/1350: Re: Performance of update 200803/1351: returned value from SQL statement 200803/1352: Re: Using tables in other PostGreSQL database 200803/1353: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1354: Re: returned value from SQL statement 200803/1355: Re: returned value from SQL statement 200803/1356: Re: returned value from SQL statement 200803/1357: Re: returned value from SQL statement 200803/1358: Re: returned value from SQL statement 200803/1359: Re: returned value from SQL statement 200803/1360: Re: Performance of update 200803/1361: Re: Performance of update 200803/1362: Re: returned value from SQL statement 200803/1363: Re: Using tables in other PostGreSQL database -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [pgsql-general] Daily digest v1.8034 (21 messages)
Message Digest Volume 1 : Issue 8034 : "index" Format Messages in this Issue: 200803/1400: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1401: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1402: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1403: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1404: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1405: Re: table of US states' neighbours 200803/1406: Re: table of US states' neighbours 200803/1407: Re: table of US states' neighbours 200803/1408: Re: dunction issue 200803/1409: Re: dunction issue 200803/1410: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1411: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1412: trouble selecting from array 200803/1413: Re: dunction issue 200803/1414: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1415: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1416: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1417: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1418: Re: trouble selecting from array 200803/1419: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1420: Re: Survey: renaming/removing script binaries (createdb, createuser...) -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [pgsql-general] Daily digest v1.8036 (18 messages)
Message Digest Volume 1 : Issue 8036 : "index" Format Messages in this Issue: 200803/1440: Re: Need help on how to backup a table 200803/1441: Persistent user-defined functions 200803/1442: Merge Joins and Views 200803/1443: Re: Persistent user-defined functions 200803/1444: PL/pgSQL Documentation, biblio, etc 200803/1445: Re: PL/pgSQL Documentation, biblio, etc 200803/1446: Re: Need help on how to backup a table 200803/1447: Re: Need help on how to backup a table 200803/1448: Re: PL/pgSQL Documentation, biblio, etc 200803/1449: Re: Need help on how to backup a table 200803/1450: Re: Need help on how to backup a table 200803/1451: Re: dunction issue 200803/1452: Re: Need help on how to backup a table 200803/1453: Re: Need help on how to backup a table 200803/1454: Re: Need help on how to backup a table 200803/1455: Re: dunction issue 200803/1456: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1457: SELECT DISTINCT ON and ORDER BY -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [pgsql-general] Daily digest v1.8035 (19 messages)
Message Digest Volume 1 : Issue 8035 : "index" Format Messages in this Issue: 200803/1421: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1422: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1423: Re: Using tables in other PostGreSQL database 200803/1424: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1425: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1426: Re: trouble selecting from array 200803/1427: pl/pgsql debugger rpms? 200803/1428: extract day from interval 200803/1429: Re: extract day from interval 200803/1430: Re: extract day from interval 200803/1431: Re: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1432: Reminder: Google Summer of Code deadline 72 hours away! 200803/1433: Re: Using tables in other PostGreSQL database 200803/1434: Need help on how to backup a table 200803/1435: Re: table of US states' neighbours 200803/1436: Re: Need help on how to backup a table 200803/1437: Re: Need help on how to backup a table 200803/1438: Re: dunction issue 200803/1439: Re: Using tables in other PostGreSQL database -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [pgsql-general] Daily digest v1.8037 (17 messages)
Message Digest Volume 1 : Issue 8037 : "index" Format Messages in this Issue: 200803/1458: Delete after trigger fixing the key of row numbers 200803/1459: Re: SELECT DISTINCT ON and ORDER BY 200803/1460: Re: SELECT DISTINCT ON and ORDER BY 200803/1461: Re: SELECT DISTINCT ON and ORDER BY 200803/1462: Re: Delete after trigger fixing the key of row numbers 200803/1463: Re: SELECT DISTINCT ON and ORDER BY 200803/1464: Re: Need help on how to backup a table 200803/1465: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1466: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1467: Users, groups and inheritance questions 200803/1468: Re: Merge Joins and Views 200803/1469: creating a trigger to access another postgres database? 200803/1470: Re: Users, groups and inheritance questions 200803/1471: Re: table of US states' neighbours 200803/1472: Re: Survey: renaming/removing script binaries (createdb, createuser...) 200803/1473: Re: Persistent user-defined functions 200803/1474: Re: Users, groups and inheritance questions -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Nulls in timestamps
Where does PostgreSQL rank nulls when sorting a column of timestamps, is this behaviour deterministic, and can I rely on it not changing in the future? Apologies if this shows up as a repost, I've had gateway problems at this end. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Nulls in timestamps
Many thanks Tom. Inconvenient from the point of view of the application but still useful information. The situation is that I've got a query with numerous subselects, each of which has to return exactly one row so I was doing a union with a nulled record then selecting the most recent: obviously I need to see bona-fide data if it's there. What I'll do is teach the app or the users to ignore a specified early data, e.g. 1970-01-01. Again, many thanks (and boy, is it good to have that gateway working properly :-) Tom Lane wrote: > > [EMAIL PROTECTED] writes: > > Where does PostgreSQL rank nulls when sorting a column of timestamps, is > > this > > behaviour deterministic, and can I rely on it not changing in the future? > > Nulls sort high (in any datatype, not only timestamps). It's possible > that we'd offer an option to make them sort low in the future, but I > can't imagine that we'd change the default behavior. > > regression=# (select 1 as x union select null) order by x; > x > --- > 1 > > (2 rows) > > regression=# (select 1 as x union select null) order by x desc; > x > --- > > 1 > (2 rows) > > regression=# > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Asking advice on speeding up a big table
On Sat, Apr 15, 2006 at 10:31:26AM -0400, Francisco Reyes wrote: > [EMAIL PROTECTED] writes: > > >Usage is to match data from the key and val tables to fetch the data > >value from the sid table. > > What is the relation between key and val tables? > Will key.id and val.id be equal? This benchmark explores an idea for a simple berkeley-db-like lookup, but faster and allowing ranges, and specialized for looking up info in some other tables I have. The key table data is table.column, and 1000 is a rough guess on how many unique column names there might be. The val table is the contents of those columns, and 100K is nother rough guess. The end result, the sid table, is a generic ID I have, coudl be anything, like lat/lon, room-bookshelf-shelf-book, etc. key.id and val.id have no bearing on each other. I have made some minor changes and speeded things up to around 15-20 lookups/sec, good enough, but not exciting :-) and in the process, come across some odd misbehavior. I have a writeup, almost ready to post, but I want to make sure I cxross my Is and dot my Ts properly. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Query runs fast or slow
I have a benchmark test which runs a query very slowly under certain
circumstances. I used Ethereal to capture the packet traffic, and
also enabled debug5 logging out of curiousity. While the slow query
is in progress, there is no log or packet activity, but the cpu is
busy. These packets are below; look for SLOW PACKETS HERE to skip all
this explanatory drudge.
This WHERE clause is fast under all conditions:
... AND (val.data = $2) AND ...
This WHERE clause is fast as a simple query, but is excruciatingly
slow as prepare / execute / fetch:
... AND (val.data > $2 AND val.data < $3) AND ...
My test program is in Perl and uses DBI/DBD::Pg. Postgresql version
is 8.0.3 on a dual core dual opteron with 2G of RAM. DBI is version
1.48. DBD::Pg is version 1.42. The OS is rPath Linux 2.6.15.
The test runs each SQL statement three times, first as a simple query
to preload caches, then as prepare / execute / fetch, and lastly as a
simple query again.
$sth = $dbh->prepare(sql_with_placeholders);
$dbh->selectall_arrayref(sql_with_values_substituted);
$sth->execute(@values);
$sth->fetchall_arrayref();
$dbh->selectall_arrayref(sql_with_values_substituted);
I captured packet traffic and tailed the log while these were running.
Everything is fine except one query, which took 75 seconds to run,
when the others took 3 milliseconds. During this 75 seconds, the
postmaster log showed no activity, but top showed the postmaster quite
busy.
75 seconds! That's an eternity. I can't imagine any circumstances
where it makes sense. EXPLAIN ANALYZE doesn't show the slow timing
because it requires values, not $n placeholders, and it is the prepare
/ execute operation which is so slow. I will be glad to send the log,
the packet capture file, the test program itself, and anything else
which helps. Here are the EXPLAIN statements in case it helps.
EXPLAIN for the equality WHERE clause:
felix=> explain analyze SELECT sid.data, glue.key, glue.val, glue.sid FROM
key, val, sid, glue WHERE (key.data =
'x6ATArB_k1cgLp1mD5x2nzVVf2DQw4Lw1-Ow5NCzzs5Pupg6K' AND key.id = glue.key) AND
(val.data = 357354306) AND val.id = glue.val AND glue.sid = sid.id;
QUERY
PLAN
Nested Loop (cost=5.82..1119.29 rows=1 width=60) (actual
time=2.271..36.184 rows=1 loops=1)
-> Hash Join (cost=5.82..1116.27 rows=1 width=16) (actual
time=2.079..35.976 rows=1 loops=1)
Hash Cond: ("outer"."key" = "inner".id)
-> Nested Loop (cost=0.00..1105.43 rows=1001 width=16) (actual
time=0.315..31.820 rows=1000 loops=1)
-> Index Scan using val_data_key on val (cost=0.00..6.01
rows=1 width=4) (actual time=0.119..0.123 rows=1 loops=1)
Index Cond: (data = 357354306)
-> Index Scan using glue_val_idx on glue
(cost=0.00..702.58 rows=31747 width=16) (actual time=0.181..24.438 rows=1000
loops=1)
Index Cond: ("outer".id = glue.val)
-> Hash (cost=5.82..5.82 rows=1 width=4) (actual
time=0.292..0.292 rows=0 loops=1)
-> Index Scan using key_data_key on "key" (cost=0.00..5.82
rows=1 width=4) (actual time=0.266..0.271 rows=1 loops=1)
Index Cond: (data =
'x6ATArB_k1cgLp1mD5x2nzVVf2DQw4Lw1-Ow5NCzzs5Pupg6K'::text)
-> Index Scan using sid_pkey on sid (cost=0.00..3.01 rows=1 width=52)
(actual time=0.179..0.183 rows=1 loops=1)
Index Cond: ("outer".sid = sid.id)
Total runtime: 37.880 ms
(14 rows)
EXPLAIN for the range WHERE clause:
felix=> explain analyze SELECT sid.data, glue.key, glue.val, glue.sid FROM
key, val, sid, glue WHERE (key.data = 'kOSkZ5iN6sz-KqGo51aTwqZnvCKQRUH2SZ8k'
AND key.id = glue.key) AND (val.data > 183722006 AND val.data < 183722206) AND
val.id = glue.val AND glue.sid = sid.id;
QUERY
PLAN
---
Nested Loop (cost=5.82..1119.30 rows=1 width=60) (actual
time=15.016..15.525 rows=1 loops=1)
-> Hash Join (cost=5.82..1116.27 rows=1 width=16) (actual
time=14.879..15.374 rows=1 loops=1)
Hash Cond: ("outer"."key" = "inner".id)
-> Nested Loop (cost=0.00..1105.43 rows=1001 width=16) (actual
time=0.211..11.666 rows=1000 loops=1)
-> Index Scan using val_data_key on val (cost=0.00..6.01
rows=1 width=4) (actual time=0.071..0.076 rows=1 loops=1)
Index Cond:
Re: [GENERAL] 2 gig file size limit
Can a single database be split over multiple filesystems, or does the filesystem size under e.g. Linux (whatever it is these days) constrain the database size? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] 2 gig file size limit
Ian Willis wrote: > > Postgresql transparently breaks the db into 1G chunks. Yes, but presumably these are still in the directory tree that was created by initdb, i.e. normally on a single filesystem. > The main concern is during dumps. A 10G db can't be dumped if the > filesustem has a 2G limit. Which is why somebody suggested piping into tar or whatever. > Linus no longer has a filesystem file size limit ( or at least on > that you'll hit easily) I'm not concerned with "easily". Telling one of our customers that we chose a particular server becuase they won't easily hit limits is a non-starter. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 2 gig file size limit
Marco Colombo wrote: > BTW, the document contains some limits. FS size limit is reported to be > 2TB (block-device size limit). I don't know if it applies to LVM logical > volumes. Ext2 FS size limit is 16TB (for 4kB blocks FS). You need to put > some effort to place 16TB on a single FS. Thanks for that, interesting :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 2 gig file size limit
Martijn van Oosterhout wrote: > What the limit on NT? I'm told 2^64 bytes. Frankly, I'd be surprised if MS has tested it :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] html to postgres...
I'm successfully storing -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] html to postgres...
Tony Grant wrote: > > These are film and director pages in a movie site. I am looking at > HTML->XML tools then with a parser I should be able to create a tab > delimited text file. I'm successfully storing scripts in tables which are pulled and executed on a client system, works well so far except that if you are using Win-32 ODBC you must have the latest version since otherwise each will be chopped at 8K without warning. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Migration
Justin Clift wrote: > > Did you get any feedback on this? :-) Unfortunately not, but I live in hope. [If you live in Hope, you'll die in Caergwrle]. I can afford very little time to play with it at present, however I'll come back to it as soon as I can since one of our ongoing issues is trying to get Win-32 Lotus 1-2-3 working and we'd quite happily pay for an ODBC driver that did this reliably. > \> Does anybody know at what point they've forked from the existing > \> driver? Their site implies 7.01.0003 but it's obviously > \> interesting to know whether they continue to track parallel > \> development. > \> > \> Specifically, the latest version of the current driver (7.01.0006) > \> handles varchars >8K properly which the previous one doesn't, and > \> I'd far rather not take a step backwards now that I've got some of > \> my apps working properly via ODBC. > \> > \> Also it would be lovely if GB's site didn't nadger Netscape > \> (possibly other browsers) so that once you're in the FAQ area you > \> can't use "Back" to get out of it... anybody listening? :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] set-level update fails with unique constraint violation
Hello, this fails with "duplicate key value": CREATE TABLE x ( i INT NOT NULL UNIQUE ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; are there any plans to make this work? -- Roman Neuhauser -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
